Last week we looked at comparing the AP subledger to the GL via SQL. This week I want to give you some SQL code to compare the Bank subledger to GL. Yes, this is actually very easy to do inside the GP interface, easier than AP or AR but since you’ll have those two via SQL, why not have everything in one place.
The code is below and you’ll need to change the bank in the code to the bank name and the account to the correct natural account number.
/*Compare Bank Subledger balance to corresponding GL account balance.*/
--Set Bank Type and get Bank Balance. Change the Checkbook ID for your company
Select 'Bank' as SubType, CURRBLNC as SubBal, 0 as GLBal
Into #SubtoGLCompare
From CM00100
Where Chekbkid='FIRST BANK' -- Change this
/*Get the GL total for this Account. Change the account below or optionally add additional
actnumbr parameters to tighten or broaden the account*/
INSERT #SubtoGLCompare (SubType,SubBal,GLBAL)
SELECT 'Bank' as SubType, 0 as SubBal, SUM(Perdblnc) AS GLBal
FROM GL11110
WHERE actnumbr_2 = '1100' -- Change this
Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference,
'Difference is Subledger minus GL.' as Instructions
from #SubtoGLCompare
Group by SubType
Drop Table #SubtoGLCompare
You, Me and Dynamics GP
The premier information site for Microsoft Dynamics GP
