(469) 828-3274

Blog Single

21 Apr

Dynamics SL SQL Query for AP & GL out of balance research

Are you struggling to determine why your Periodic AP Report and GL Balance Report do not tie for the AP account in Dynamics SL? Here is a quick query you can run to help find the vendor that is out of balance:


  • 2005 = AP account
  • Period out of balance was Jan 2017

select id as Vendor

, convert(decimal(19,2), sum(dramt)) as Debit

, convert(decimal(19,2), sum(cramt)) as Credit

, (convert(decimal(19,2), sum(dramt)) – convert(decimal(19,2),sum(cramt))) * -1 as GLBalance

from gltran

where   acct = 2005 and PerPost < 201702 and Module = ‘AP’

group by id

order by id

Compare this query to the Periodic AP Report Summary to find out which vendor is out of balance.

Run the query again for the prior month and compare to see if the same vendors are out of balance.

Research the vendor(s) that are out of balance.  You could have a payment applied to the wrong period, or made some other minor error that was not caught before posting.

For more tips and tricks, subscribe to our newsletter or contact Conexus SG: SLSupport@conexussg.com

Related Posts