Sage Line 500: Debtors Aged Analysis do not balance with Debtors Control Account Trail Balance

Here’s the first test:

SELECT transaction_group, SUM(amount) amnt FROM slitemm WHERE slyear = “2013” AND period = “01” INTO TEMP slitemtmp;

SELECT transaction_group, SUM(journal_amount) amnt FROM nltranm WHERE nlyear = “13” AND trans_period = “01”  AND posting_code = <your Debtors Control Account here> INTO TEMP nltranmtmp;

SELECT s.transaction_group, s.amnt, n.amnt FROM slitemtmp s, nltrantmp n WHERE s.transaction_group = n.transaction_group AND s.amnt <> n.amnt;

Use the resulting transaction group to identify which transactions in slitemm doesn’t match the corresponding transaction in nltranm.  If the above returns nothing, then use slxrefm to check if the allocations was done correctly.

The two should rarely differ.  I had an instance where the system key LOCALDEC was (temporarily) set to 1, then changed back to 2 – resulting in a decimal difference.