Payables Trail Balance
Applies to:
Oracle Payables - Version: 11.5.2
Information in this document applies to any platform.
NOTE: This document does not apply to Global Accounting (AX) installations.
AP Trial Balance Report
Payables Account Analysis Report
GL Account Analysis Report
Posted Invoice Register
Posted Payment Register
It is the application user's responsibility to reconcile AP to GL and, when appropriate, to make adjustments to bring
the ledgers into balance. However, support recognizes that there are cases where unbalanced ledgers could be caused by a
bug or where the source of the difference is difficult to find because of the quantity of data. This note sets out the
process for reconciling the ledgers, troubleshooting, and logging a service a request if necessary.
If you have completed the reconciliation and troubleshooting process below and have not found the problem or have
questions about how to correct a problem, you can open a service request by following the steps in
the section labeled Opening a Service Request.
The AP subledger may be out of balance because of inconsistent data created by the
application users, for example, when a GL manual adjustment has been made to an AP liability account. In any case
like this, it will be the application user's responsibility to determine the correct entry and either make an adjustment
in the General Ledger, add a transaction in AP, or both. Oracle Support cannot recommend entries when the resolution
of the problem requires the interpretation of an accounting issue.
This Note does not apply to all situations where an invoice appears incorrectly on the AP Trial Balance Report. In
many of those cases, the information will also be incorrect in the GL. If so, the troubleshooting steps below will not
apply.
Make sure all AP batches are imported from the gl_interface and posted in GL.
Reconcile the current period
Use the following as an example of how to balance. In this example, you are closing your accounting period for April
and you have just posted your final invoice and payment batches to your general ledger system.
To reconcile your accounts payable activity for April, make the following calculation:
"Accounts Payable Trial Balance" as of March 31
+ "Posted Invoice Register" for the period between April 1 and April 30
- "Posted Payment Register" for the period between April 1 and April 30
= "Accounts Payable Trial Balance" as of April 30
You can also compare your AP liability accounts to GL by doing a query of the accounts in GL to identify the account or
accounts out of balance. The trial balance total should be the same as the balance of your GL liability account.
Reconcile prior periods (if necessary)
If the current period does not reconcile, please complete the reconciliation process for all prior periods
from the most recent to the earliest until you get to one that reconciles.
Most of the problems with AP not balancing to GL are caused by the following:
Manual Entries
Manual journal entries in the general ledger that involve an AP liability account will cause the AP Trial
balance not to reconcile to the GL. These entries are not included in the AP subledger so they will not
be reflected on the AP Trial Balance Report.
To check for manual entries:
Run the GL "Account Analysis" report for the liability account and for the date range in question. Look for transactions with a source other
than Payables. This can quickly pinpoint any transactions incorrectly charged to the account.
Any datafix that involves undo with sweep from one period to another.
If you performed a datafix in the past where you used the undo accounting script and swept a transaction
forward from a closed period to reaccount it, this will cause an imbalance between AP and GL. The imbalance
will be corrected in the period in which you made a GL adjustment to account for the fix. This is the
expected result and is not correctable.
If you did not make a GL adjustment to account for the fix, you will have a permanent imbalance that will
need to be corrected with a GL adjustment.
Corrections made during the journal import process
Any correction you make during the journal import process will result in the line being changed in the
general ledger, but not in AP. As a general rule, you should not correct AP transactions during the
journal import process. Instead, they should be corrected in the AP subledger before they are transferred
to GL.
Deletion from the GL Interface
If you have deleted any AP batches or lines from AP batches out of the GL Interface,
this will cause AP and GL to be out of balance. If you have done this, please open a
service request and, if possible, provide details about what was deleted.
AP data should never be deleted from the GL Interface except as directed by AP support
in a service request.
AP batches not imported from the GL Interface
If the AP batch is still in the GL Interface, it will not be reflected in the GL reports and this will
cause a difference between AP and GL.
See step 1 below for a query to check the GL Interface. This will show the payables source group ids
that need to be imported into the GL and posted before the reconciliation process can be performed.
AP batches not posted.
GL information is not included in the reports until it is posted. Any AP batches that are unposted in
GL will cause a difference between AP and GL.
Please run the diagnostic in Note 205624.1 to determine whether unposted AP batches exist.
Consider known bugs that can cause this problem:
Bug 4449844 AP Trial Balance does not honor as of date, as detailed in Note 315147.1
Bug 3385847 Failure In Gl Transfer Program Did Not Rollback All, as detailed in Note 282520.1
If you cannot resolve the reconciliation problem after completing the steps above, you can open a service
request by performing the following steps and uploading the requested information.
********************************
Note the following steps will create some tables in your database. Please be sure you
understand the effects of these steps and have appropriate permissions before proceeding.
********************************
1. First verify that there is no data in the GL Interface that has yet to be processed.
If the following query returns any rows this data must be imported and posted into
the GL before following through with the rest of the action plan.
select x.group_id
, x.batch_name
, x.gl_transfer_run_id
, g.status
, USER_JE_SOURCE_NAME
, sum(g.accounted_dr)
, sum(g.accounted_cr)
from XLA_GL_TRANSFER_BATCHES_ALL x
, GL_INTERFACE g
where x.group_id = g.group_id
and x.application_id = 200
and g.gl_sl_link_table = 'APECL'
group by x.group_id
, x.batch_name
, x.gl_transfer_run_id
, g.status
, USER_JE_SOURCE_NAME;
2. Please upload two Posted Invoice Registers
One for the period that is out of balance.
One for the period prior to the period that is out of balance.
3. Please upload two Posted Payment Registers
One for the period that is out of balance.
One for the period prior to the period that is out of balance.
4. Please upload three Accounts Payable Trial Balances
One for the last day of the period that is out of balance.
One for the last day of the period prior to the period that is out of balance.
One for the last day of the period two periods prior to the period that is out of balance.
5. Please upload three Payables Account Analysis Reports
One for the period that is out of balance.
One for the period prior to the period that is out of balance.
One for the period two periods prior to the period that is out of balance.
6. Create a couple tables to work with to compare AP and GL. When prompted for a period name,
enter the name of the earliest period that is out of balance.
create table Note_344367_1_gl_t as
select l.accounted_dr
, l.accounted_cr
, l.code_combination_id
, nvl(l.gl_sl_link_id, ir.gl_sl_link_id) gl_sl_link_id
, l.set_of_books_id
, h.status
, h.je_batch_id
from gl_je_lines l
, gl_je_headers h
, gl_import_references ir
where l.period_name = '&&Period_Name'
and l.je_header_id = h.je_header_id
and l.je_header_id = ir.je_header_id
and l.je_line_num = ir.je_line_num
and nvl(l.gl_sl_link_table, ir.gl_sl_link_table) = 'APECL';
create table Note_344367_1_ap_t as
select sum(l.accounted_dr) accounted_dr
, sum(l.accounted_cr) accounted_cr
, l.code_combination_id
, l.gl_sl_link_id
, h.set_of_books_id
, l.org_id
, l.ae_line_type_code
from ap_ae_lines_all l
, ap_ae_headers_all h
where l.ae_header_id = h.ae_header_id
and h.period_name = '&&Period_Name'
and h.gl_transfer_flag = 'Y'
group by l.gl_sl_link_id
, h.set_of_books_id
, l.code_combination_id
, l.org_id
, l.ae_line_type_code;
create table Note_344367_1_ap_ccids as
select distinct l.code_combination_id
, l.org_id
, h.set_of_books_id
from ap_ae_lines_all l
, ap_ae_headers_all h
where l.ae_header_id = h.ae_header_id
and h.period_name = '&&Period_Name'
and h.gl_transfer_flag = 'Y'
and l.ae_line_type_code = 'LIABILITY';
7. Create some indexes to get the rest of the queries to go a bit faster
create index Note_344367_1_gl_t_idx on Note_344367_1_gl_t (gl_sl_link_id);
analyze table Note_344367_1_gl_t estimate statistics;
create index Note_344367_1_AP_t_idx on Note_344367_1_AP_t (gl_sl_link_id);
analyze table Note_344367_1_AP_t estimate statistics;
create index Note_344367_1_ap_ccids_idx on Note_344367_1_ap_ccids (set_of_books_id, code_combination_id);
analyze table Note_344367_1_ap_ccids estimate statistics;
create index Note_344367_1_alb1 on AP_LIABILITY_BALANCE (ae_line_id);
create index Note_344367_1_alb2 on AP_LIABILITY_BALANCE (ae_header_id);
|
|
