AP-SLA-GL Reconcilation

AP-SLA-GL Reconcilation

============================

In R12, AP does not store any accounting information. Reconciliation of the posted transactions is done using data in the Subledger Accounting (SLA) tables.

• XLA_TRIAL_BALANCES (XTB) -This is the SLA Trial Balance table. It captures the invoice and payment amounts, with respect to a supplier. It is built using the data from the AP_LIABILITY_BALANCES in 11i and detailed information captured in XDL for R12 data. Only transactions transferred to GL are captured in this table.
• XLA_AE_LINES (XAL) - This is the SLA Detailed Lines table.
• XLA_DISTRIBUTION_LINKS (XDL) - This is the SLA Distributions table. It captures the lowest level granularity of a transaction's accounting. It is important to understand the information captured in XDL. This table stores the details at the distribution level for both source and applied transactions because AP utilizes the business flow feature of SLA. Applied Transaction means a transaction such as Payments, which is applied to an Invoice, or it could also mean a transaction, such as a Prepayment.

2. RECONCILING THE AMOUNTS AND UNDERSTANDING THE DATA FLOW BETWEEN SLA AND GL

In this section, we will discuss how to reconcile the amounts and show you how the data flows between SLA and specific GL tables and columns. It is important to note the following:

1. We only reconcile data that has been transferred to GL.
2. We assume all batches are posted.
3. The reconciliation process ensures that the following three areas are reconciled:
• Amounts in the XLA_AE_LINES (XAL) table correspond to the XLA_TRIAL_BALANCES (XTB) table
• Amounts in the XLA_AE_LINES (XAL) table correspond to the GL_JE_LINES (GJL) table, and
• Amounts in the GL_JE_LINES (GJL) table correspond to GL_BALANCES table

3. ESTABLISHING A BASELINE FOR ADDRESSING 11i UPGRADE ISSUES

1. It is important to establish a baseline when the AP to GL Reconciliation was last completed (with or without known differences). If there are differences that can be explained, then those should be carried forward to the current reconciliation exercise.
2. Always reconcile the AP Trial Balance (APTB) with the GL account balances as of the upgrade period. Run the APTB report in summary mode (option=group by Account Summary) for the upgrade period and identify the differences between the Remaining Amount and GL balances, per CCID. Get the Manual JEs YTD and adjust accordingly.

4. RECONCILING R12 DATA AND ADDRESSING R12 TRIAL BALANCE-GL ISSUES

Run the report again for an R12 month, in summary mode (option=group by Account Summary)

Adjust the difference as of the Upgrade period. Then calculate the new Difference. If there is a difference then:

• First and foremost is to establish that the data in XLA_AE_LINES for application_id=200 per month, per CCID is the same as the data in GL_JE_LINES for the same CCID/source/month (and gl_je_headers.je_from_sla_flag=Y). Adjust the manual entries in GL accordingly.
• Once you have reconciled the amounts in XLA_AE_LINES & GL_JE_LINES, the next step is to verify the amounts in XLA_AE_LINES and XLA_TRIAL_BALANCES (for the CCID/entity_id) assuming that only LIABILITY accounts are defined in the TB definition.

5. UNDERSTANDING HOW UNDO ACCOUNTING CAN IMPACT RECONCILIATION

The following is an example of how undo accounting works.

• You enter an invoice with one distribution of $200, which uses a GL date of 04-JAN-2001.
• It is accounted incorrectly for a $205 LIABILITY, but it is posted to the GL.
• Subsequently, you use the UNDO Accounting API to reverse the transaction as of 1-FEB-2001.
• You pay the full invoice and the payment date is 20-DEC-2000.
• If you submit the R12 Accounts Payable Trial Balance report as of 31-DEC-2000, the invoice is NOT displayed on the report.
• Similarly, if you submit the Accounts Payable Trial Balance report as of 31-JAN-2001, the invoice is still not displayed on the report, as it is fully paid.
• However, the Posted Payment Register as of 31-DEC-2000 reports the Payment and the Posted Invoice Register as of 31-JAN-2001 reports the Invoice.

Technical perspective

AP does not upgrade all data in the XDL table. This implies that the XTB table cannot be constructed from the XAL and XDL tables, for upgraded data.
For upgraded data, XTB is built from the AP_LIABILITY_BALANCE (APLB) table. APLB stores Payment details under the INVOICE_ID only.

The KEY joins from an upgrade perspective are -

AND alb.ae_header_id = xah.completion_acct_seq_value
AND 200 = xah.completion_acct_seq_version_id
AND alb.ae_line_id = xal.ae_line_num
AND xah.upg_source_application_id = 200

( xal-xla_ae_lines, xah-xla_ae_headers, alb-ap_liability_balance )

xal.ae_line_num & xah.completion_acct_seq_value are values upgraded from AP to SLA.

If there is a mismatch between ap_ae_headers_all/ap_ae_lines_all & ap_liability_balance (on ae_header_id, ae_line_id).

OR

if during the upgrade, there is a mismatch between ap_ae_headers_all/ap_ae_lines_all & xla_trial_balances (on completion_acct_seq_value, ae_line_num), then such data is not upgraded to the xla_trial_balances tables.

Common Reasons why Reconciliation issues are Encountered During the Upgrade

• AP upgrade did not upgrade the accounting events correctly so the accounting events exist in AP and not in SLA.
• There are ACCOUNTING_EVENT_IDs in AP that are not stamped back to invoice distributions so the invoice distributions were not upgraded to SLA.
• In 11i, if there is a mismatch in the AE_LINE_ID col in AP_AE_LINES_ALL and AP_LIABILITY_BALANCE, then that data is NOT available in XTB
• If there is data corruption in the 11i AP_LIABILITY_BALANCE table
• If UNDO accounting is done on an 11i accounted event