Developing for Microsoft Dynamics GP by David Musgrave (Australia) and the Microsoft Dynamics GP Developer Support Team (USA)Syndicated From: http://blogs.msdn.com/b/DevelopingForDynamicsGP/
Click on the link to see the full About Page.
From the Useful SQL Scripts Series.
I was assisting on a support case recently where we needed to identify transactions in Payables Management history which had missing Apply records. We wanted to add up the total of the Apply records and make sure that they matched the value of the original document amount.
I was given a script which could look for missing Apply records on debit documents only. Using this script as a starting point, I updated it to use local temp tables in the tempdb (which means that mulitple users can execute the code without causing concurrency and deadlock issues), added indexes for performance, set it up to handle debit and credit documents as well as multi-currency realized gain/loss and allowing for a 0.01 exchange rate currency rounding difference per applied multi-currency document.
I worked with my friend Robert Cavill to test the code and fine tuned it to work on live data. The final code was able to identify a few transactions which did have issues due to rounding differences beyond the allowance, without returning a large number of false positives.
/* SQL PM Find Missing Apply Records *//* Updated by David Musgrave, 24-March-2009 */
/* Now handles debit and credit documents and multi-currency (including round errors) */
goif exists (select * from tempdb.dbo.sysobjects where id = Object_id('tempdb..#PMApply') and type = 'U') drop table #PMApply
/* Create the table used to hold PM30200 Info */
/* Create the table used to hold Sum of Apply Records from PM30300 */
/* Get Functional Currency ID */
The script is also available as an attachment at the bottom of this post.
Since writing this script I have obtained another script which performs a similar check. Both scripts are in the attached archive. There are some differences in the scripts, the script I wrote works only with History and has allowances for rounding differences on multi-currency transactions, when the other script works on Open and History but does not handle the rounding differences. I would suggest running both scripts.
Let me know if you find this useful.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics