SQL Script to verify Dynamics GP GL batch control totals

By Steve Endow

I have a customer who is importing GL batches into Dynamics GP, and they explained that they a bug in their integration that is causing the batch control totals to be incorrect. So they may import 50 transactions into GP, but the batch control total will show 85 transactions.  I believe that this discrepancy is causing some issues.

So I quickly threw together this SQL query to check the number of GL transactions and JE totals and compare those numbers to the batch control totals in SY00500.

There are probably several different ways to accomplish this, but I happened to pull this version together in a few minutes using a CTE.


WITH glTotals (BACHNUMB, BatchCount, BatchTotal)
AS (
SELECT glh.BACHNUMB, COUNT(glh.JRNENTRY) AS BatchCount, SUM(dtGLTotals.JEAmount) AS BatchTotal FROM GL10000 glh
JOIN (SELECT JRNENTRY, SUM(DEBITAMT + CRDTAMNT) AS JEAmount FROM GL10001 GROUP BY JRNENTRY) AS dtGLTotals ON dtGLTotals.JRNENTRY = glh.JRNENTRY
GROUP BY glh.BACHNUMB
)

SELECT b.BACHNUMB, b.NUMOFTRX, b.BCHTOTAL, glTotals.BatchCount, glTotals.BatchTotal, 
CASE WHEN b.NUMOFTRX <> glTotals.BatchCount THEN 'Count Diff' ELSE '' END AS CountDiff, 
CASE WHEN b.BCHTOTAL <> glTotals.BatchTotal THEN 'Amount Diff' ELSE '' END AS AmountDiff 
FROM TWO..SY00500 b 
JOIN glTotals ON glTotals.BACHNUMB = b.BACHNUMB
WHERE b.BCHSOURC = 'GL_Normal' AND b.BACHNUMB LIKE 'TEST%'


Here's what the results look like:

If there is a discrepancy in the transaction count or the total amount, it will display a note in the CountDiff or the AmountDiff columns.

Next, I will likely need to write a SQL script that will correct the control totals in SY00500.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com