Recently, a colleague asked me how Dynamics AX calculates the data shown in the collections list page (see the next screen-print) and how those data can be reconciled with the available Dynamics AX customer reports. As I could not give him an immediate answer, I did several tests that I included further below because I believe that the one or the other might have come across similar questions on the data shown in the collections list page.
Section 1: Calculation of the collections list page data
In order to check out how the “Amount due balance” is calculated and what kind of transactions are included in this balance, I run the periodic update process “Customer aging snapshot” with the criteria “Transaction date” for the periods ending 31 December 2012, 31 December 2013 and 31 December 2014 (see the following two screenshots)
The result of this first exercise was that I always got the same total balance if I calculated the sum of the amounts shown in the “Amount due balance” column. Please see the next screenshot for this comparison.
Initially, I thought that there must be something wrong with the way how I processed the data. Yet, after repeating the tests in several other applications, having a look at the CustAgingSnapshot class and the following TechNet site, I noticed that the collections list page always shows the total open customer balance amounts irrespective of the date that was selected in the “Aging as of” field of the periodic processing window.
What is more, the collections list page does also include transactions that have been recorded in future periods. To verify this, I posted a free text invoice for 1000 USD with the following date values:
After recording the free text invoice and re-running the periodic update process for 31 December 2015 as illustrated in the next screen-print, …… I noticed that the free text invoice that I posted with a transaction date “01 January 2016” is included in the collections window even though I run the periodic update process for end of December 2015. Please see the following screen-print.
The last test that I did in this respect was running the periodic update job with a different aging period definition as illustrated next.
Yet, also in this case, future transactions were included in the collections list page and the sum of the “Amount due balance” column did not change.
After verifying how the figures shown in the “Amount due balance” column are calculated, I tested what effect the “criteria” and “aging as of date” fields have on the other columns included in the collections list page. In order to realize that, I run the periodic update process several times with different selections made in the “criteria” and “aging as of” fields.
Test run 1:
Initially, I run the periodic job with the following setup:
Outcome test run 1:
As expected and identified before, the free text invoice that I posted 1 January 2016 with a due date of 02 February 2016 and a document date 03 March 2016, is included in the collections list page classified in the “not due” column.
Test run 2:
Thereafter, I run the periodic job with the transaction date criteria as of 31 January 2016, as the free text invoice that I posted would be 30 days overdue on this day.
Outcome test run 2:
As expected, the open balance is now shown in the 30 days column of the collection list page.
Test run 3:
My next test was running the periodic process again with an aging as of date 31 January 2016 but with the “due date” criteria.Outcome test run 3:
Now, the open balance is shown in the “not due” column.
Test run 5:
Within the last test-run, I repeated the previous one with an aging as of date 4 April 2016.Outcome test run 5:
Also here, as one could expect, the open balance is now shown in the 60 days column.
The result of those test-runs is that the criteria selected (transaction date, due date or document date) and the selected “aging as of” date do only have an influence on the aging column in which a certain amount is illustrated. The selection made in those fields do, however, not influence the amounts shown in the “Amount due balance” column which always includes all currently open customer transactions.
Section 2: Reconciliation of the collections list page data
The fact that the collections list page shows all currently open customer transactions and does include transactions that have been posted in future periods makes it impossible to reconcile the amounts shown in the “Amount due balance” column with standard customer reports – such as the customer open transaction report or the customer balance report – as of a specific date in the past.
For that reason, the only way to reconcile the amounts shown in the collections list page is to run those reports as of a (future) date after which no other transactions have been recorded. For an example, please see the following screenshot.
In summary, one has to remember that the collections list page is not able to show open customer transactions as of a certain date in the past if other customer transactions have been recorded after this date.