Aged Debtor reports

[redwindow100]

We are running version 7.2.0.0
We are very careful to check our Aged reports to our GL on a monthly basis. We do have a lot of Debtors with 0.01, -0.01 and even 0.00 balances appearing on the reports - this is not an issue as we are required to submit reporting to external sources. "Suddenly" our report went from 43 to 12 pages for the summary and quite a number more for the detailed one (a lot of the missing Debtors on the new reports had zero or 0.01 balances). Both reports have the same total. When comparing those totals to the GL Debtors Control account, the figures are now different. The GL amount is the same, but the Debtors reports are slightly higher. I did a comparison month by month going back to the 31-07-2019 and every month is out, some up to $508 and some a few dollars. I have been assured by our support provider that they have not performed any work. The only explanation they gave is "The only way these figures could change if someone has posted or allocated a transaction back in that date range – then the reports would change". We are very careful to close off a period and the corresponding GL balances for the Debtors Control account have not changed over those months. Any thoughts?

[Mike.Sheen]

Hi There,

It's likely something has changed - if the reported has started behaving differently then something has to have caused that.

Firstly, is the report in question the "DBMGT020 - Debtors Aged Balance - Detailed.rpt" report?

If you run that and there are debtors which used to appear and do not appear, then identify a debtor you think should (used to) appear on the report but currently does not.

Now capture the SQL Query the report is issuing - and run that in SSMS to see the raw results - is the debtor in the list of the results? If not then the query is what is omitting the debtor you expect - but if it does appear in the sql results, then the report is omitting the debtor.

For our standard report the SQL query looks like this:

Code: Select all

"usp_JIWA_Debtors_AgedBalances";1 'Armadale Shire Council', 'Yalgoo Shire Council', '1001', '2008', '14 Day', 'Trade - Level 3', 0, {ts '2021-10-29 00:00:00'}

And the results of running that in SSMS look like this:

If you confirm the stored proc used is usp_JIWA_Debtors_AgedBalances, and it is NOT returning the debtor in the results - then it's pretty certain that those debtors have zero balances - this can be seen in the logic of the stored proc usp_JIWA_Debtors_AgedBalances which excludes debtors with zero balances:

That clause to exclude debtors with zero balances has been there since at least version 6.5.13 - at least 12 years ago.

So, it could be either you're not using our standard report - or maybe you were not before but now are - or some adjustment transactions and allocations have been made.

Let us know what you find!