SLA: A TECHNICAL PERSPECTIVE OF THE AP TO GL RECONCILIATION [ID 605707.1]  

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
     1. UNDERSTANDING THE AP-SLA-GL RECONCILIATION
     2. RECONCILING THE AMOUNTS AND UNDERSTANDING THE DATA FLOW BETWEEN SLA AND GL
     Key Tables and Columns used for Reconciliation
     3. ESTABLISHING A BASELINE FOR ADDRESSING 11i UPGRADE ISSUES
     Scenario
     Technical perspective
     Common Reasons why Reconciliation issues are Encountered During the Upgrade
     Analyzing the Difference in the R12 Trial Balance
     How to Analyze the Data Even Further
     4. RECONCILING R12 DATA AND ADDRESSING R12 TRIAL BALANCE-GL ISSUES 
     5. UNDERSTANDING HOW UNDO ACCOUNTING CAN IMPACT RECONCILIATION
     Scenario
     Original Event/Accounting
     How to find events/entities created by Undo Accounting
     6. APPENDIX
  References

---

Applies to:

Oracle Payables - Version: 12.0.0 to 12.0.5
Information in this document applies to any platform.

Purpose

This document offers a technical perspective of the AP-GL Reconcialition. Its purpose is to assist the reader with reconciliation issues. In the simplest terms, AP-GL reconciliation means tallying the amounts in Accounts Payable (i.e., AP) for a CCID/Supplier and reconciling it to the figures in the General Ledger (GL).
This document covers the following topics:

• Understanding the AP-SLA-GL reconciliation
• Reconciling the amounts and understanding the data flow between SLA and GL
• Establishing a baseline for addressing 11i upgrade data related issues.
• Reconciling R12 data and addressing R12 Trial Balance-GL issues
• Understanding how UNDO Accounting can impact Reconciliation
• Appendix 

Last Review Date

December 4, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

1. UNDERSTANDING THE AP-SLA-GL RECONCILIATION

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.

XLA_DISTRIBUTION_LINKS (XDL) Table

XDL table col	Description	Ref to AP table col
SOURCE_DISTRIBUTION_ID_NUM_1	DIST_ID of the current transaction (for invoice ?????? ap_invoice_distributions_all.invoice_distribution_id, for payment ?????? ap_payment_hist_dists.payment_history_id)
SOURCE_DISTRIBUTION_TYPE	AP_PMT_DIST, AP_INV_DIST, AP_PREPAY
APPLIED_TO_ENTITY_ID	ENTITY_ID of the invoice/upstream transaction
APPLIED_TO_SOURCE_ID_NUM_1	Invoice_id in case of LIABILITY line of the payment
APPLIED_TO_DIST_ID_NUM_1	Invoice_distribution_id against which this payment_distribution_id is applied	AP_INVOICE_PAYMENTS_ALL & AP_PAYMENT_HIST_DIST
REF_AE_HEADER_ID	AE_HEADER_ID of the invoice/upstream transaction
REF_TEMP_LINE_NUM	TEMP_LINE_NUM of the invoice/upstream transaction
REF_EVENT_ID	EVENT_ID of the invoice/upstream transaction

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

Key Tables and Columns used for Reconciliation

The following are the key tables and columns that are used for reconciliation purposes:
XLA_AE_HEADERS (XAH) Table

EVENT_TYPE_CODE	ENTITY_ID/AE_HEADER_ID/ APPLICATION_ID	GL_TRANSFER_ STATUS_CODE/ DATE	ACCOUNTING_ ENTRY_ STATUS_ CODE	BALANCE_ TYPE_CODE	ACCOUNTING_ DATE/ PERIOD_NAME	UPG_BATCH_ID	GROUP_ID
INVOICE_VALIDATION (don??????t consider MANUAL ever)	E1/H1/200	Y/null	F	A ??????actual, E-encumbrance	1-JAN-2000/JAN-00	5486 (not null for 11i)	G1
PAYMENT_CREATED (don??????t consider MANUAL ever)	E2/H2/200	Y/null	F	A ??????actual, E-encumbrance	1-JAN-2000/JAN-00	5486 (not null for 11i)	G2

XLA_AE_LINES (XAL) Table

ENTITY_ID /AE_HEADER_ID/ AE_LINE_NUM/ APPLICATION_ID	ACCOUNTED_ CR/DR	ENTERED_ CR/DR	CURRENCY_ CODE	CODE_ COMBINATION_ ID	GL_SL_LINK_ID/ GL_SL_LINK_TABLE	ACCOUNTING_ CLASS_ CODE
E1/H1/L1/200	500/0	500/0	USD	1000	123451/APECL	LIABILITY
E1/H1/L2/200	500/0	500/0	USD	1000	123452/APECL	LIABILITY
E1/H1/L3/200	500/0	500/0	USD	1500	123453/APECL	LIABILITY
E2/H2/L1/200	0/250	0/250	USD	1000	123454/APECL	LIABILITY
E2/H2/L2/200	0/250	0/250	USD	1000	123455/APECL	LIABILITY
E2/H2/L3/200	0/250	0/250	USD	1500	123456/APECL	LIABILITY

XLA_TRIAL BALANCES (XTB) Table

SOURCE_ ENTITY_ID	APPLIED_TO_ ENTITY_ID	ACCOUNTED_ CR/DR	ENTERED_ CR/DR	CURRENCY_ CODE	CODE_ COMBINATION_ ID	EVENT_ CLASS_ CODE	DEFINITION_ CODE
E1	--	1000/0	500/0	USD	1000	AP_INVOICES	AP_200_1
E1	--	500/0	500/0	USD	1500	AP_INVOICES	AP_200_1
E2	E1	0/500	0/500	USD	1000	AP_PAYMENTS	AP_200_1
E2	E1	0/250	0/250	USD	1500	AP_PAYMENTS	AP_200_1

* L1 and L2 are summed into TB, as the CCID and Entity/Header are the same. GL_IMPORT_REFERENCES (GL_IMP_REF) Table

JE_HEADER_ID/JE_LINE_NUM	JE_BATCH_ID	GL_SL_LINK_ID/GL_SL_LINK_TABLE	If summary posting (most probably)! JE_HEADER_ID/JE_LINE_NUM
GH1/GL1	B1	123451/APECL	GH1/GL1
GH1/GL2	B1	123452/APECL	GH1/GL1
GH1/GL3	B1	123453/APECL	GH1/GL2
GH2/GL1	B2	123454/APECL	GH2/GL1
GH2/GL2	B2	123455/APECL	GH2/GL1
GH2/GL3	B2	123456/APECL	GH2/GL2

* GIR and XAL are always one-one. For R12, GL_SL_LINK_TABLE is XLAJEL GL_JE_HEADERS (GJH) Table

JE_HEADER_ID	JE_BATCH_ID	JE_FROM_SLA_FLAG	ACTUAL	POSTED_DATE	REVERSED_JE_HEADER_ID	PERIOD	JE_SOURCE	JE_CATEGORY
GH1	B1	U-upgrade for 11, Y for R12	A/E	--	--	JAN-00	Payables	Purchase Invoices
GH2	B2	U-upgrade for 11, Y for R12S	A/E	--	--	JAN-00	Payables	Payments*

• JE_SOURCE=Payables can have XLA??????s MANUAL due to undo accounting.
• JE_SOURCE=Cost Management ??????has additional checks! --
• JE_source in ('Cost Management','Inventory','Purchasing') and je_category in ('Receiving','MTL','WIP','Inventory')

GL_JE_LINES (GJL) Table -- IF Detailed Posting in GL

JE_HEADER_ID/JE_LINE_NUM	GL_SL_LINK_ID/GL_SL_LINK_TABLE	ENTERED_CR/DR	CURRENCY_CODE	CODE_COMBINATION_ID
GH1/GL1	123451/APECL	500/0	USD	1000
GH1/GL2	123452/APECL	500/0	USD	1000
GH1/GL3	123453/APECL	500/0	USD	1500
GH2/GL1	123454/APECL	0/250	USD	1000
GH2/GL2	123455/APECL	0/250	USD	1000
GH2/GL3	123456/APECL	0/250	USD	1500

• If GL_SL_LINK_ID is populated in GJL then it is Detailed Posting

GL_JE_LINES (GJL) Table -- IF Summary Posting in GL (most probably)

JE_HEADER_ID/JE_LINE_NUM	GL_SL_LINK_ID/GL_SL_LINK_TABLE	ENTERED_CR/DR	CURRENCY_CODE	CODE_COMBINATION_ID
GH1/GL1		1000/0	USD	1000
GH1/GL2		500/0	USD	1500
GH2/GL1		0/500	USD	1000
GH2/GL2		0/250	USD	1500

• If GL_SL_LINK_ID is populated in GJL, then you are using Detailed Posting

3. ESTABLISHING A BASELINE FOR ADDRESSING 11i UPGRADE ISSUES

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.
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.

Scenario

Let us use the following as your Trial Balance, as of the upgrade period Nov 2007

CCID	GL Account	GL	Manual JE	AP	Diff GL - SLA+Man JE
A	B	C	D	E	F=C-E+D
15151	XXX	307778.71	0.00	499899.92	-192121.21
15071	YYY	15067997.42	0.00	15067997.42	0.00
15511	ZZZ	7174644.29	0.00	7174644.29	0.00

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.

Note: The data manager that is run later in the day will not be of any help.

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

Analyzing the Difference in the R12 Trial Balance

If you see differences in the R12 Trial Balance (i.e., the GL and SLA amounts are not the same) as of the upgrade period, then do the following:
( See the example above for the CCID 15151 ) 

NOTE: When reporting the results of the SQL scripts in this document, include the Note number 605707.1 and the SQL Reference--e.g., QR1, QR2, QR3 etc.,

QR1 -- SQL Query Reference 1 Run this SQL script to show the data in the AP Liability table that should
appear on the 11i Trial Balance


create table ap_tb as
select code_combination_id, invoice_id, sum(nvl(accounted_cr,0)) cr, sum(nvl(accounted_dr,0)) dr,sum(nvl(accounted_cr,0)) - sum(nvl(accounted_dr,0)) diff
from ap_liability_balance where set_of_books_id= &ledger_id
and code_combination_id IN (&Give_CCIDs_that_dont_tally)
and accounting_date <= to_Date('&upgrade_period_end_date')
group by code_combination_id, invoice_id
having sum(nvl(accounted_cr,0)) <> sum(nvl(accounted_dr,0));

QR2 - Run this SQL script to show the data in the SLA Trial Balance table that
should show up in the Trial Balance as of the upgrade period create table xla_tb as
select tb.code_combination_id ,xte.transaction_number, xte.source_id_int_1,
tb.diff
from xla.xla_transaction_entities xte,
(select tb.code_combination_id, nvl(tb.applied_to_entity_id,
tb.source_entity_id) entity_id, sum(nvl(tb.acctd_rounded_cr,0)),
sum(nvl(tb.acctd_rounded_dr,0)),sum(nvl(tb.acctd_rounded_cr,0)) -
sum(nvl(tb.acctd_rounded_dr,0)) diff
from xla_trial_balances tb
where tb.ledger_id=&ledger_id and tb.definition_code= '&definition_code'
and tb.gl_date <= to_date('&upgrade_period_end_date')
and tb.code_combination_id IN (&Give_CCIDs_that_dont_tally)
group by tb.code_combination_id, nvl(tb.applied_to_entity_id,
tb.source_entity_id)
having sum(nvl(tb.acctd_rounded_cr,0)) <> sum(nvl(tb.acctd_rounded_dr,0)))tb
where
tb.entity_id=xte.entity_id and
xte.ledger_id=&ledger_id and
xte.application_id=200

Run the following two SQL scripts to provide details about the transactions there are causing the differences.

QR3 - Check for transactions in the SLA Trial Balance (SLA TB) table but
not in the 11i APTB select xla.code_combination_id, sum(xla.diff) from
xla_tb xla where not exists
(select 1 from ap_tb ap
where
ap.invoice_id= xla.source_id_int_1 and xla.code_combination_id=ap.code_combination_id )
group by xla.code_combination_id

QR4 - Check for transactions in 11i APTB table but not in the SLA TB table  select ap.code_combination_id, sum(ap.diff) from
ap_tb ap where not exists
(select 1 from xla_tb xla
where
ap.invoice_id= xla.source_id_int_1 and xla.code_combination_id=ap.code_combination_id )
group by ap.code_combination_id

How to Analyze the Data Even Further

QR5 - Pick up any specific CCID
select xla.source_id_int_1, xla.transaction_number, xla.diff from xla_tb xla where not exists
(select 1 from ap_tb ap where ap.invoice_id= xla.source_id_int_1 and xla.code_combination_id=ap.code_combination_id )
and code_combination_id=&CCID

QR6 - Get the entity of any invoice
select entity_id from xla.xla_transaction_entities where
ledger_id=1 and entity_code='AP_INVOICES' and
nvl(source_id_int_1,-99)=&INVOICE_ID and application_id=200

QR7 - Check whether the invoice is missing or payment is missing in the XTB table
Select * from xla_trial_balances
where definition_code=&DEF_CODE and
nvl(applied_to_entity_id, source_entity_id)=&ENTITY_ID
and gl_date <= to_date('&upgrade_period_end_date')

Using these SQL scripts, you can investigate the particular transaction by comparing the AP accounting data with the SLA accounting data (for ae_header_id, ae_line_id/num).
The following five queries will help you determine if the AE_LINE_ID in AP is the same as that in the SLA tables.

QR8 - 11i - AP Invoice accounting

select al.ae_header_id, al.ae_line_id, al.ae_line_number, ah.accounting_event_id
from ap_accounting_events_all ae, ap_ae_headers_all ah, ap_ae_lines_all al
where ae.accounting_event_id = ah.accounting_event_id
and  ah.ae_header_id = al.ae_header_id
and  ae.source_id = &invoice_id
and  ae.source_table = 'AP_INVOICES'

QR9 - 11i - AP Payment accounting

select al.ae_header_id,al.ae_line_id, al.ae_line_number, ah.accounting_event_id
from ap_accounting_events_all ae, ap_ae_headers_all ah, ap_ae_lines_all al
where ae.accounting_event_id = ah.accounting_event_id
and  ah.ae_header_id = al.ae_header_id
and  ae.source_id = &check_id
and  ae.source_table = 'AP_CHECKS'

QR10 - 11i - AP Liability balance (source for upgrade to R12 XLA TB table)

select ae_header_id, ae_line_id
from ap_liability_balance
where invoice_id = &invoice_id
and set_of_books_id = &ledger_id  

QR11 - R12 - SLA Invoice accounting

SELECT xal.AE_HEADER_ID, xal.ae_line_num, xal.displayed_line_number,
xah.event_id , xah.completion_acct_seq_value
FROM  xla.xla_transaction_entities xte, XLA_AE_HEADERS xah, XLA_AE_LINES xal
where xte.entity_id = xah.entity_id
and   xte.application_id = xah.application_id
and  xah.ae_header_id = xal.ae_header_id
and  xah.application_id = xal.application_id
and  NVL(xte.source_id_int_1,-99) = &invoice_id
and  xte.application_id = 200
and  xte.ledger_id = &ledger_id
AND  XTE.ENTITY_CODE = 'AP_INVOICES'
AND  XAL.ACCOUNTING_CLASS_CODE = 'LIABILITY'

QR12 - R12 - SLA Payment accounting

SELECT xal.AE_HEADER_ID, xal.ae_line_num, xal.displayed_line_number,
xah.event_id , xah.completion_acct_seq_value
FROM  xla.xla_transaction_entities xte, XLA_AE_HEADERS xah, XLA_AE_LINES xal
where xte.entity_id = xah.entity_id
and   xte.application_id = xah.application_id
and  xah.ae_header_id = xal.ae_header_id
and  xah.application_id = xal.application_id
and  NVL(xte.source_id_int_1,-99) = &check_id
and  xte.application_id = 200
and  xte.ledger_id = &ledger_id
AND  XTE.ENTITY_CODE = 'AP_PAYMENTS'
AND  XAL.ACCOUNTING_CLASS_CODE = 'LIABILITY'

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)
XLA_AE_HEADERS (XAH) Table

Trial Balance as of Jan 2008
CCID	GL Account	GL	Manual	AP	Diff GL - SLA+Man JE	Net Impact from Nov-07	Diff as of Jan-08
A	B	C	D	E	F=C-E-D
15151	XXX	188900.93	0.00	380382.98	-191482.05	-192121.21	639.16
15071	YYY	12630248.06	107676.17	12437132.25	85439.64	0.00	85439.64
15511	ZZZ	11850812.71	0.00	11842656.01	8156.70	0.00	8156.70

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.

R12.A) To get the sum from the SLA tables.

QR13 - Sum from the SLA Tables

select /*+ parallel(xal) parallel(xah) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15151   <<change if needed>>
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1                <<change if needed>>
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
and (xah.upg_batch_id is null or xah.upg_batch_id=-9999) -- will help ignore upgraded data
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <<change if needed>>
group by currency_code


R12.B) To get the sum from the GL tables

QR14 - Sum from the GL Tables

select l.code_combination_id ccid,k.concatenated_segments,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) diff,
currency_code
from gl.gl_je_headers h
, gl.gl_je_lines l
,gl_code_combinations_kfv k
where h.currency_code = 'USD'
and l.ledger_id = 1 <<change ledger>>
and l.code_combination_id = k.code_combination_id
and h.je_header_id = l.je_header_id
and h.actual_flag = 'A'
and h.je_from_sla_flag= 'Y' -- will help ingore upgraded data
and l.code_combination_id = 15151 <<change CCID>>
and h.je_source = 'Payables'
and h.period_name in ('MAR-08') <<change period>>
group by l.code_combination_id, k.concatenated_segments, currency_code

R12.A and R12.B should match per CCID, currency within the period. If it does, check per period, per CCID, per currency--This should be the difference between XAL and XTB.

QR15 - To get the difference from SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =15011    -- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by currency_code

Note: In the following SQL query, we have introduced another filter--event_class_code.
This is done in order to remove the UNDO ACCOUNTING datafix entries. Read the section, Understanding how UNDO Accounting can impact Reconciliation, for more details. Such entries will match in SLA-to-GJL lines comparison, but will not match between XAL-to-XTB

QR16 - Get the XTB Difference

select  SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code

The Difference of the sum of Credit-Debit for the CCID/Currency combination in XAL (lines) should match the difference of the Sum Credit-Debit for the CCID/Transaction Currency in XTB, assuming all LIABILITY CCIDs are defined in the Trial Balance Definition.

QR17 - Get the difference from the SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */  sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code, entity_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15011
-- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by entity_id, currency_code,entity_id

QR18 - Get the difference from the XTB table

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code, source_entity_id
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code, source_entity_id


If R12.A and R12.B do not match per CCID and currency within a period, then investigate the batches within that period from SLA and compare the amounts in SLA and GL using the group_id. This may not always work as the group_id sometimes gets deleted from GL.

QR19 - SLA query considering the SLA Manual entries

select /*+ parallel(xal) parallel(xah) leading(xah) */
--xah.entity_id source_entity_id,
sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff , xah.group_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY' and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =52261
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 2
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
group by xah.group_id

QR20 - GL query for amounts per Group ID

select 'GL' Module, b.group_id,
SUM(NVL(accounted_cr,0)) - SUM(NVL(accounted_dr,0)) diff_acc
from apps.gl_je_headers a, apps.gl_je_batches b,apps.gl_je_lines c
where
a.je_header_id=c.je_header_id
and a.je_batch_id=b.je_batch_id
and c.ledger_id=2
and a.posted_date is NOT NULL
and a.je_source ='Payables'
and a.actual_flag='A' and c.code_combination_id=52261
group by b.group_id;

In GL			In SLA			Diff	Comments
2069078	Dec	-13387.5	2069078	Dec	-13387.50	0.00
			2069110	Dec	19409.90	19409.90	SLA Journal Posted in May in GL
	Dec	28919.11				-28919.11	Null Group ID, JE_BATCH_ID = 2581709,2582994,2582995,2588472,2588473,2588475,2588476 (reversal batch in GL)

Null GROUP_ID in GL would generally mean it is a reversal batch in GL itself. There may be multiple batches that were reversed during the month. So in the above example, you see group_id=2067078 that has the same amounts in SLA and GL. The other group_id=2069110 in SLA has a period-Dec, but in GL it is posted in May.
Another possibility is:

In GL			In SLA			Diff	Comments
2069885	Apr	1027997	2069885	Apr	999344.72	-28652.75	In SLA in Jan
			2069885	Jan	28652.75	28652.75	SLA Journal Posted in GL in Apr

A GROUP_ID in SLA has data of in two different periods, but in GL it is posted in one period.
Someone may have edited the journal to change the period in GL.

5. UNDERSTANDING HOW UNDO ACCOUNTING CAN IMPACT RECONCILIATION

Scenario

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.

Original Event/Accounting

XLA_AE_HEADERS (XAH) Table

EVENT_ID	EVENT_TYPE_CODE	ACC_DATE	AE_HEADER_ID	UPG_BATCH_ID	ENTITY_ID
E1	INVOICE_VALIDATE	04-JAN-2001	H1		XT1

XLA_AE_LINES (XAH) Table

AE_HEADER_ID	ACCOUNTING_CLASS_CODE	CCID	DR	CR
H1	ITEM EXPENSE	C1	220
H1	LIABILITY	C2		220

XLA_TRIAL_BALANCES (XTB) Table

AE_HEADER_ID	SOURCE_ENT_ID	DEF_CODE	CCID	DR	CR
H1	XT1	AP_200_X	X1		220

After Undo:
XLA_AE_HEADERS (XAH) Table

EVENT_ID	EVENT_TYPE_CODE	ACC_DATE	AE_HEADER_ID	UPG_BATCH_ID	ENTITY_ID
E1	INVOICE_VALIDATE				XT1
E2	MANUAL	01-FEB-2001	H2	-9999	XT2
E3	MANUAL	04-JAN-2001	H1	-9999	XT3

XLA_AE_LINES (XAL) Table

AE_HEADER_ID	ACCOUNTING_CLASS_CODE	CCID	DR	CR	DESC
H1	ITEM EXPENSE	C1	220		Datafix for Event: E1
H1	LIABILITY	C2		220	Datafix for Event: E1
H2	ITEM EXPENSE	C1		220	Datafix Reversal for Event: E2
H2	LIABILITY	C2	220		Datafix Reversal for Event: E2

XLA_TRIAL_BALANCES (XTB) Table

AE_HEADER_ID	SOURCE_ENT_ID	DEF_CODE	CCID	DR	CR

If you try to reconcile the AP Trial Balance (APTB) as of Jan 2001 with the GL Balance as of
Jan 2001, after the UNDO accounting created the reversal entry in Feb 2001, the AP Trial Balance will not match the GL Balance. If you look at the ??????After Undo?????? example, GL has the E1 event??????s entry but APTB does not because it was converted to MANUAL.

How to find events/entities created by Undo Accounting

Run the following SQL script to find the events and entities created by the UNDO Accounting:

QR21 - Find the events and entries created by UNDO Accounting

select entity_id XT3 from xla.xla_transaction_entities where
ledger_id=&ledger_id and
entity_code='MANUAL' and
application_id=200 and
nvl(source_id_int_1,-99) =&invoice_id/check_id
--this will be the MANUAL entity for the Original event.

The reversal manual entity would be 1 less than this entity. So XT2=XT3-1
Use the following SQL script to determine if the UNDO Accounting correctly reversed all the amounts in a CCID:

QR22 - Determine if the UNDO Accounting correctly reversed
all the amounts in a CCID

select h.event_id, sum(nvl(entered_cr,0)), sum(nvl(entered_dr,0))
,sum(nvl(accounted_dr,0))- sum(nvl(accounted_cr,0))
from xla_ae_headers h, xla_ae_lines l
where h.application_id = 200
and l.application_id = h.application_id
and l.ae_header_id = h.ae_header_id
--and h.accounting_date between :p_period_start_date AND :p_period_end_date
and h.ledger_id = 1
and h.event_type_code='MANUAL'
and h.upg_batch_id =-9999 
and h.balance_type_code='A'
AND h.gl_transfer_status_code='Y'
AND h.accounting_entry_status_code='F'
AND l.code_combination_id=&CCID
and h.description like 'Data fix entry: event_id of%'
and not exists
(select 1 from xla_ae_headers h3
where h3.application_id=200 and h3.ledger_id=1 and h3.upg_batch_id=-9999
and h3.event_type_code='MANUAL' and h3.balance_type_code='A'
and h3.description='Data fix reversal entry: event_id of '||h.event_id)
group by h.event_id
having sum(nvl(accounted_dr,0))- sum(nvl(accounted_cr,0)) <> 0

6. APPENDIX

The following are some useful queries that may help you to further analyze why you have a Reconciliation Issue.

QR23 - Run this SQL script to determine the Segment from a CCID

select concatenated_segments from gl_code_combinations_kfv
where code_combination_id=52261

QR24 - Run this SQL script to determine the Batch ID for a Group

select je_batch_id from gl_je_batches where group_id=2068553

QR25 - Run this SQL script to list the Amounts in the
SLA table for all Manual (YTD) transactions

select /*+ parallel(l) parallel(h) parallel(gl) leading(h) */
gl.concatenated_segments,l.code_combination_id,sum(nvl(entered_cr,0)),
sum(nvl(entered_dr,0)),sum(nvl(entered_cr,0))-sum(nvl(entered_dr,0)), currency_code
from xla_ae_headers h, xla_ae_lines l, gl_code_combinations_kfv gl
where gl.code_combination_id = l.code_combination_id and
h.application_id = 200
and l.application_id = h.application_id
and l.ae_header_id = h.ae_header_id
and h.accounting_date between to_date('01-JUL-2007','DD-MON-YYYY') AND
to_date('31-MAR-2008','DD-MON-YYYY') -- <<depends upon the fiscal year>>
and h.ledger_id = 1  <<replace ledger>>
AND h.gl_transfer_status_code='Y'
AND h.accounting_entry_status_code='F'
AND l.code_combination_id = 112771 <<replace CCID>>
and h.event_type_code='MANUAL'
and h.application_id=200
and h.balance_type_code='A'
group by l.code_combination_id, gl.concatenated_segments, currency_code

QR26 - Run this SQL script to list all transactions that are marked as transferred from SLA, but is not present in the GL

SELECT l.ae_header_id, l.gl_sl_link_id, l.gl_sl_link_table 
FROM xla_ae_lines l, xla_ae_headers h WHERE 
l.application_id=h.application_id AND 
l.ae_header_id=h.ae_header_id AND 
h.application_id=:p_application_id AND 
h.ledger_id= :p_ledger_id AND 
h.upg_batch_id IS NULL AND 
h.gl_transfer_status_code='Y' AND 
h.accounting_entry_status_code='F' AND 
h.accounting_date BETWEEN :p_period_start_date AND :p_period_end_date AND 
h.event_type_code <> ' MANUAL'   
AND NOT EXISTS 
(SELECT 1 FROM gl_import_references ir , gl_je_headers gh
WHERE ir.gl_sl_link_id=l.gl_sl_link_id AND 
ir.gl_sl_link_table=l.gl_sl_link_table AND
ir.je_header_id=gh.je_header_id AND
ir.je_batch_id=gh.je_batch_id AND
gh.ledger_id>0);

QR27 - Run this SQL script periodically to check for multiple postings

select distinct a.gl_sl_link_id, a.gl_sl_link_table
from gl_import_references a
where (a.gl_sl_link_id,a.gl_sl_link_table) in
(select distinct gl_sl_link_id, gl_sl_link_table
from xla_ae_headers xah ,xla_ae_lines xal
where xah.application_id = xal.application_id
and xah.ae_header_id = xal.ae_header_id
and xah.ledger_id= :ledger_id
and xah.application_id =:appl_id -- 200 for AP, 222 for AR etc.
and xah.accounting_entry_status_code='F'
and xah.accounting_date between :p_start and :p_end)
and exists (select 1 from gl_je_headers gh
where gh.je_batch_id =  a.je_batch_id
and   gh.je_header_id = a.je_header_id
and   gh.ledger_id >0
and nvl(gh.accrual_rev_je_header_id,0) =0
)
group by a.gl_sl_link_id, a.gl_sl_link_table
having count(*) > 1

QR28 - Run this SQL script to identify where the GL_SL_LINK_ID
is in GL but not in SLA, for a batch or period.

select imp.gl_sl_link_id
from gl_import_references imp, gl_je_lines gl
where gl.je_header_id=imp.je_header_id and
gl.je_line_num=imp.je_line_num
and code_combination_id=52261
and gl.je_header_id in
(select je_header_id from gl_je_headers where --je_batch_id=2586374
je_source='Payables' and ledger_id=2
and je_source ='Payables' and posted_date is NOT NULL
and actual_flag='A'
and period_name in ('Jan-08','Feb-08','Mar-08','Apr-08') )
and not exists
(select 1 from xla_ae_lines where gl_sl_link_id=imp.gl_sl_link_id
and gl_sl_link_table='XLAJEL') and imp.gl_sl_link_id is not null

QR29 - Run this SQL script to list all the Batches with Null Group IDs:

select je_batch_id from gl_je_batches where group_id is null
and je_batch_id in
(select distinct je_batch_id from gl_je_headers
where ledger_id=2 and je_source='Payables' and status='P'
and period_name='Jan-08')

Reference:
ETRM via Metalink - Access the Electronic Technical Reference Manual via Metalink to obtain detailed information about any of the tables discussed in this Note. The information provided in this document is limited to the columns that are used for reconciliation purposes.