6-JAN-2021
A client site that uses our MO Receipt Integration (MORI) module recently reported a frequent occurrence of SQL deadlocking from a stored procedure called sopExtPricingPriceBookSetup. Microsoft identified MORI as the source of the problem, however MORI does not use that stored procedure or have any interaction with Extended Pricing.
We found the source of the problem in the Dynamics GP Inventory module. When ANY change is saved to an Item when Extended Pricing is enabled, the sopExtPricingPriceBookSetup stored procedure is called. Posting an MO Receipt (with or without MORI) adds inventory to the system, which results in an update to the item’s Current Cost. Saving that change to the Item Master causes the stored procedure to run…twice.
Viewing an item in Item Maintenance and clicking save (without making any changes) runs the stored procedure…twice. Posting an Inventory Transaction or a PO Receipt also cause the stored procedure to run twice. Any process in GP that makes a change to the Item Master will trigger the stored procedure to run, so it is running very frequently in any business that is inventory oriented.
The stored procedure appears to have been designed with the assumption that it would run only when creating a new item or making changes to an item’s UofM Schedule. It deletes all information from IV00107 (Item Price List Options) and IV00108 (Item Price List) for an item where the Price Level ID is “EXTPRCLVL”, then repopulates the table.
For the SQL minded folks: in addition to locking the tables while it needlessly deletes and repopulates them, it uses two global (rather than locally scoped) SQL cursors, which means it will block itself if two calls are made to the stored procedure at nearly the same time. On the other hand, since the stored procedure begins by deleting from IV00107 and IV00108 you would not want two calls to sopExtPricingPriceBookSetup to both have the ability to repopulate the tables simultaneously.
The issue was particularly noticeable with MORI because the module creates a large number of MO Receipts at a rate not possible if users were manually entering and posting transactions. A site with a large number of users, or other automated integrations that touch inventory, could also be impacted by this Extended Pricing issue.
Fixing this is tricky. The heart of the issue is in the GP source code in a “form” called IV_Item (specifically Commit() of form IV_Item). It should not call sopExtPricingPriceBookSetup for ANY change to the Item Master. It should only execute for changes to the UofM Schedule and a few other fields used by the stored procedure. A second option that is less of a “fix” and more like a patch, would be to modify the stored procedure so it checks if repopulating IV00107 andIV00108 is necessary before it deletes everything. This would technically resolve the issue but would also place an unnecessary load on the system. Think about it this way–the stored procedure only needs to run when the UofM Schedule changes, which virtually never happens, yet it is running twice for every MO Receipt, Inventory Adjustment and PO Receipt.
There is a third option that was available to MORI. Since MORI runs by itself on a dedicated GP client, our “fix” for this (which we released in a build yesterday) was to add switch on the MO Generator window to “Disable Extended Pricing”. MORI now has a way to turn off Extended Pricing only on the GP client running MORI without affecting the rest of the system.
If you are using MORI and Extended Pricing, please download the latest build from www.willoware.com/downloads.