AP Trial balance not matching with Invoice Ageing report

Issue: Oracle AP trial balance not matching with standard "Invoice Ageing" report

First the definition-

1. Accounts Payable Trial Balance

The Accounts Payable Trial Balance shows you a complete list of all unpaid invoices on the system that has been transferred to the General Ledger, irrespective of whether they are due for payment or not. This report will assist with the month end reconciliation process.

This is a period end report. This represents your opening and closing AP Trial Balance.

The closing value per AP control account in this trial Balance should match with the corresponding Account in the General Ledger Trial Balance.

If it does not, then there is an issue.

Following reports can be run in the accounting period that is being in question to find the potential problem.

Posted Invoices Register

Posted Payments Register

The Opening Account Payable Trail Balance (AP TB)

+ The Posted Invoice

- The Posted payments

= The Closing AP TB

This is a key formula to identify the source of potential problems.

Note that in above:

The Aging Invoice Report Total should = The AP Trial Balance Total (from above for posted transactions) + The Total of Swept (Unposted Transactions)

You may wish to perform this reconciliation as a further month end reconciliation check.

2.Invoice Aging Report

This is yet another important report which shows you a complete list of all unpaid invoices on the system irrespective of whether they are due for payment or not, whether they are on hold or not. As soon as you save the Invoice even if you have not validated yet, this report picks up the invoice. Ageing goes by Invoice date. This report groups invoices by how many days they are over due. The grouping of invoices by age past due is dependent on the Aging bucket selected for the report.

Running the Invoice Aging report is like running any other report. The key points to note are that

1. The report name to choose is "Invoice Aging Report"

2. It is recommended that you use the Standard Aging bucket.

3. You can also select invoice level detail to find out the exact issue.

Resolution:

If you see there is any reconciliation issue between AP Trial balance and Invoice Ageing report, then check the following,

1. By running above reports and calculation find out which invoice is causing the problem.

2. If an invoice is in Trial balance but not in AP Ageing report then check the Payment schedule for the invoice. Due to Oracle AP known issues, sometimes the payment schedule could not get created for an invoice and due to that the invoice does not show up in the standard "Invoice Ageing" report. So, if that is the case then manually enter a payment schedule for the invoice as per the invoice payment terms and that should fix the issue.

It may also happen that users might have run the trial balance with a "as of date" which is earlier than the payment accounting date and in which case invoices which were paid and accounted after the "as of date" would appear in the Trial Balance but not in the ageing report. So, it is always a good practice to generate Trial balance after the all the invoices and payment subledger accounting details are sent successfully to GL with "as of date" as the last day of the current period.

3. Run the Invoice Ageing report and now it should show the invoice in the report.