Weekly Dynamic: Compare AR Subledger to GL Balance in Dynamics GP

Ok this is the last of my comparison tips for balancing. Next week we’re off on to something else.

For this week, I’ve got SQL code to compare the AR subledger to GL to see if they match. You’ll need to adjust the natural account number and segment for you specific chart.

/*Set AR Type and get AP Balance. Change the Checkbook in Red for your company */
SELECT    'AR' as SubType,SUM(custblnc) AS SubBal, 0 AS GLBAL
into #SubtoGLCompare
FROM         RM00103

/*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   'AR' as SubType,  0 AS SubBal, SUM(Perdblnc) AS GLBAL
FROM         GL11110
WHERE     actnumbr_2 = '1271' -- 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