AP Trial balance not matching with Invoice Ageing report

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.

Aging Reports in Oracle Apps Receivables - Quick Overview

Aging reports are used to review information about your open items so as to know how much funds have been held up and with whom. Aging reports display the customers who have outstanding balances and the amount each customer owes us which also helps to expedite the collection process.  Receivables aging reports Do Not include customers with a zero outstanding balance.

These reports can print both detail and summary information about your customer’s current and past due invoices, debit memos, and charge backs.  Receivables also gives the option to see credit memos, on–account credits, unidentified payments, and on–account and unapplied cash amounts.

As mentioned above, Oracle Receivables ages the transactions according to due date. The aging reports however select transactions according to gl date. 

Receivables aging reports includes all open items whose gl_date is before or the same as the “as of date” entered, and whose gl_date_closed is after the “as of date”. The default value for “as of date” is the current date.

 Categories of Aging Reports 

Receivables aging reports are categorized as follows:

• Aging – By Account
• Aging – By Amount
• Aging – By Collector
• Aging – By Salesperson 

 Report Parameters for Aging Reports 

Aging Bucket Name:  

Specify the bucket set from which the report information needs to be printed. The default bucket set is 'Standard'.

As of Date:

Specify the date as of which the transactions need to be aged. Receivables includes all open items whose GL date is before or the same as this date. The default is the current date.

Order By:

The option you want Receivables to use to sort your information. For example, you can sort by:

·        Customer Name (Aging - 4 and 7 Bucket reports)

·         Transaction Type (Aging - 4 and 7 Bucket reports)

·        Balance Due (only for 7 Buckets - By Amount report)

·        Salesperson (only for 7 Buckets - By Salesperson report)

Report Format:

The “Brief” format prints customer name and customer number with item information while the “Detailed” format prints address and contact address as well.

Report Summary:

The “Invoice Summary” option prints information on all customers' debit items.

The “Customer Summary” option prints customers' names with their total debit item balances.

Show on Account:

Specify whether to print credit items for your customers.

·        Do Not Show: Receivables does not display any of your identified or unidentified payments, or on-account credit memos.

·        Age: Receivables ages your credit items and includes the credit amounts in the appropriate aging bucket columns.

·        Summarize: Receivables displays the sum of your credit items in the Customer Credit Memos, Customer Payments, and the Customer Balance rows. This is the default option.

Show Receipts at Risk:

Receipts at Risk are receipts that have either not been cleared or factored receipts that have not been risk eliminated. Select one of the following values for your report:

·        Age:- Include receipts at risk in this report. Receivables displays the receipts at risk with other open receipts in the appropriate bucket and includes them when determining the customer's balance.

·        Summarize:- Receivables displays the sum of your receipts at risk in the Customer Credit Memos, Customer Payments, and the Customer Balance rows.

Do Not Show :- Receipts at risk will not be included in this report. This value is used as the default.