Epicor introduced the Pre-SQL hook as a new feature for the DynaChange Rule in version 12.17.   This feature extends the reach of a DynaChange Rule to insert business logic, including modifying the SQL statement itself, before the statement executes.  This can be a slippery slope.  P21 is a complex application, and tinkering too much can have unintended consequences.  Having said that, this feature does open a new range of possibilities for companies on Prophet 21.

Watch Now: DynaChange Rule –  Pre-SQL Hooks in Prophet 21

http://https://youtu.be/ro3PeVga9Z0

Setting up a Pre-SQL hook is not necessarily difficult, but there are a few steps that you need to follow if you hope to have success.  Unfortunately, there is just not a lot of documentation on the subject right now.  Hopefully this guide will help you get on the right track.

Find the SQL Statement…

1. Use SQL Profiler to set up a trace on your database.   This is a required step.  Part of setting up the hook is knowing the id or name of the SQL statement you are trying to modify.  The often look like DS d_ds_some_name_goes_here.
2. With the trace running, run Prophet 21 to make it fire the SQL statement you want to modify.  One this happens, you can stop the trace.
3. Start looking through the trace to try and find the SQL statement you want to modify.  I would recommend copying the statement over into Management Studio and running it to be sure that it is the right one.
4. Once identified, work out your plan for how you want to modify it.  I highly recommend only using this feature to modify WHERE and ORDER BY clauses.  Anything more can have an unintended effect on P21.  Be very careful about what you modify and to what degree.

Write the DynaChange Rule…

1. Set up a blank DynaChange rule in Visual Studio, or whatever other editor you use.  There is a template shown in the video.
2. Next, declare a string variable, and set it to: Data.Fields["sql_statement"].FieldValue
3. There are several ways to affect the SQL statement from here.  In the video, I get the integer value of the point in the statement I want to affect.  Then I use an insert to put in the additional code.  Any string manipulation you like should work just fine.
4. Finally, pass the new SQL statement back to Prophet 21 via Data.Fields["sql_statement"].FieldValue

Set up the New DynaChange Rule…

1. Once you are happy with the code, build the DLL and copy it over to your DynaChange Rules Folder.
2. Open Prophet 21, and then open the DynaChange Rules Organizer.
3. Then click the Create Pre-SQL rule button, a dialog box will open.
4. In the dialog, enter the DS d_ds_some_name_goes_here name of the SQL statement you modified.
5. Next, select the name of the business rule using the drop down box.
6. Leave the run type alone.  It is always Synchronous.
7. Click ok, save the rule, and then test it out.  Hopefully everything worked out OK.

Conclusion

Pre-SQL hooks are a powerful feature in Prophet 21.  They can produce unexpected results if you are not super-careful, so test in Play until you are confident the rule is working right.  I hope you have lots of luck developing your next Pre-SQL hook and make P21 work even better for your company!

Read More about Prophet 21 in our Knowledge Base.

The DynaChange Rule is part of the Epicor Prophet 21 ERP System