Tuesday, 19 June 2012

Oracle-Apps-Technical-Tutorial

Posted by at 5 comments:
To Check debug logging is enabled or disable


SELECT
tl.user_profile_option_name
,substr(decode(a.level_id,10001,'SITE',
10002,'APPLN',10003,'RESP',10004,'USER'),1,5) "Level"
,substr(decode(a.level_id,10001,'Site',10002,c.application_short_name,10003
,b.responsibility_name,10004,d.user_name),1,30) "Level Value"
,nvl(a.profile_option_value,'Is Null') "Profile Value"
FROM fnd_profile_option_values a
,fnd_responsibility_tl b
,fnd_application c
,fnd_user d
,fnd_profile_options e
,fnd_profile_options_tl tl
WHERE tl.profile_option_name= ('AFLOG_ENABLED')
AND e.profile_option_id = a.profile_option_id
AND tl.profile_option_name = e.profile_option_name
AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+)
AND a.level_value = d.user_id (+)
ORDER BY 1, 2, 3, 4 DESC
Posted by at No comments:

Friday, 8 June 2012

To Link XLA Tables With AR Tables To Get Information From GL (General Ledger / Subledger / Receivables)

Trying to link GL, XLA and AR tables but not getting the right data. How can this be done?

Solution

There is no one-to-one mapping between AR, XLA and GL.
The data that actually posts to the GL is the XLA table data, not AR. Depending on the AAD rules you have defined, one row that appears in AR could become 10 in XLA_DISTRIBUTION_LINKS, but when the data is posted into the GL, the accounts of the same type and ccid are merged to a single entry.

For example, a transaction could have 34 gl_dist rows, but 201 rows in ar_distribution_links, but when it actually posts, consolidates to 9 rows in gl_import_references.

The main link to bind information together is the gl_sl_link_id. It will show in the gl_je_lines, gl_import_refernces and xla_ae_lines table.
Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction.
ACTION: COMPLETE TRANSACTION:
RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID
RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1
XLA_TRANSACTION_ENTITIES.ENTITY_ID = XLA_EVENTS.ENTITY_ID


ACTION: RUN CREATE ACCOUNTING:

RA_CUST_TRX_LINE_GL_DIST_ALL.CUST_TRX_LINE_GL_DIST_ID = XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1
XLA_AE_LINES.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID
XLA_AE_HEADERS.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID


ACTION: RUN TRANSFER TO GL:

 XLA_AE_LINES.GL_SL_LINK_ID = GL_JE_LINES.GL_SL_LINK_ID
XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_HEADERS.JE_HEADER_ID



Note: If the data is upgraded data, you may find that in the link id is missing in the
gl_je_lines table. IF so, log a Service Request with Oracle Support to request script upd_gl_sl_link_util_rev.zip to populate the missing data.
Posted by at No comments:

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.
Posted by at No comments:

Which User is Locking the table


SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tabname_blank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';
 

Kill Session

To Find out SID and SERIAL#:

SELECT VA.SQL_TEXT,VS.SID, VS.SERIAL#,process,vs.status, vp.pid
FROM V$SQLAREA VA, V$SESSION VS, v$process vp
WHERE VA.ADDRESS = VS.SQL_ADDRESS
AND VA.HASH_VALUE = VS.SQL_HASH_VALUE
AND VS.PADDR = VP.ADDR
AND upper(sql_text) LIKE '%SEARCHING_TEXT%';

-- to kill the session

ALTER SYSTEM KILL SESSION 'sid,serial#' 
 
Posted by at No comments:

Tables involved with security rules in oracle apps R12 & R11i



                                                                          
In this post, Let us see the tables involved with Security rules and a query to collect the data related to security rules both in R12 and 11i releases.

TEST INSTANCE: R12.1.3
SELECT frt.responsibility_name,
       frt.responsibility_id,
       ifs.id_flex_num Chart_of_accounts_id,
       led.name ledger_name,
       ffv.flex_value_set_name Value_set_name,
       ifs.segment_name,
       ifs.segment_num,
       fvr.flex_value_rule_name,
       DECODE(fvrl.include_exclude_indicator,
               'E', 'Exclude',
               'I', 'Include',
               'Error') Inc_exc,
       fvrl.flex_value_low,
       fvrl.flex_value_high
  FROM fnd_flex_value_rule_usages fvru,
       fnd_flex_value_rules fvr,
       fnd_flex_value_rule_lines fvrl,
       fnd_responsibility_tl frt,
       fnd_id_flex_segments ifs,
       fnd_flex_value_sets  ffv,
       gl_ledgers led
 WHERE fvru.responsibility_id  = frt.responsibility_id
   AND fvru.flex_value_rule_id = fvr.flex_value_rule_id
   AND fvrl.flex_value_rule_id = fvr.flex_value_rule_id
   AND ifs.flex_value_set_id   = fvr.flex_value_set_id
   and fvr.flex_value_set_id   = ffv.flex_value_set_id
   AND ifs.id_flex_num         = led.chart_of_accounts_id
   AND led.name                = 'Vision Operations (USA)'
   AND ifs.id_flex_code        = 'GL#'
 ORDER BY
       frt.responsibility_name,
       ifs.segment_num,
       fvr.flex_value_rule_name,
       fvrl.include_exclude_indicator Desc;

TEST INSTANCE: 11i
SELECT frt.responsibility_name,
       frt.responsibility_id,
       ifs.id_flex_num Chart_of_accounts_id,
       ifs.segment_name,
       ifs.segment_num,
       fvr.flex_value_rule_name,
       DECODE(fvrl.include_exclude_indicator,
               'E', 'Exclude',
               'I', 'Include',
               'Error') Inc_exc,
       fvrl.flex_value_low,
       fvrl.flex_value_high
  FROM fnd_flex_value_rule_usages fvru,
       fnd_flex_value_rules fvr,
       fnd_flex_value_rule_lines fvrl,
       fnd_responsibility_tl frt,
       fnd_id_flex_segments ifs,
       gl_sets_of_books glsob
 WHERE fvru.responsibility_id  = frt.responsibility_id
   AND fvru.flex_value_rule_id = fvr.flex_value_rule_id
   AND fvrl.flex_value_rule_id = fvr.flex_value_rule_id
   AND ifs.flex_value_set_id   = fvr.flex_value_set_id
   AND ifs.id_flex_num         = glsob.chart_of_accounts_id
   AND glsob.name              = 'Vision Operations (USA)'
   AND ifs.id_flex_code        = 'GL#'
 ORDER BY ifs.segment_num,
       fvr.flex_value_rule_name,
       fvrl.include_exclude_indicator Desc,
       frt.responsibility_name ;
Posted by at No comments:
Subscribe to: Posts (Atom)