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
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