R12 Mapping Between Subledger Tables, SLA and GL Tables-CSDN博客

Applies to:

OraclePurchasing - Version: 12.0.0 to 12.3
Oracle Inventory Management - Version: 12.0.0 to 12.1.1
Oracle Cost Management - Version: 12.0 to 12.1.1
Information in this document applies to any platform.

Purpose

With the introductionof Subledger Accounting(SLA) it has become very difficult to find therelationship at table level between the subledgers and XLA Tables and GeneralLedger tables. We will examine the Table links of Accounting Data Flow as datamoves from PO tables to RRSL to XLA Tables toGL Tables. We will present some relevant sql scripts to use in collectingsubledger data, SLA data and GL data

Scope and Application

Thispaper will benefit Customers,Consultants and Oracle Support Engineers

Table relationships betweenPO tables and rcv_transactionsand rcv_receiving_sub_ledgerremain the same as in R11i. The following reference fields are used to captureand keep PO information in RCV_RECEIVING_SUB_LEDGER

REFERENCE1	Source (PO or REQ)
REFERENCE2	PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or po_requisition_headers_all.requisition_header_id)
REFERENCE3	Distribution ID (from po_distributions_all.po_distribution_id or po_req_distributions_all.distribution_id)
REFERENCE4	Purchase Order or Requisition number (from po_headers_all.segment1 or po_requistion_headers_all.segment1)
REFERENCE5	(Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)

RCV_TRANSACTION_ID Receiving transaction unique identifier (from rcv_transactions.transactions_id)

The receiving package thatpopulates this table is RCV_CreateAccounting_PVT (RCVVACCB.pls)

Release 11i Mapping Ref Note.461103.1- Mapping Between PO Tables And General Ledger Tables

SLA-SubledgerAccounting, has introduced new SLA tables :

XLA_AE_HEADERS
XLA_AE_LINES
XLA_DISTRIBUTION_LINKS

RCV_RECEIVING_SUB_LEDGER is notdirectly linked to table GL_JE_LINES, instead it islinked through the subledger tables :

XLA_AE_HEADERS
XLA_AE_LINES
XLA_DISTRIBUTION_LINKS

Accounting entries in RCV_RECEIVING_SUB_LEDGERcan be retrieved using SLA tables and GL_IMPORT_REFERENCEStable after running Create Accounting.

RCV_RECEIVING_SUB_LEDGERis linked to XLA Tables by RCV_SUB_LEDGER_ID

Runthe sql below to get RCV_SUB_LEDGER_ID:

Select RCV_SUB_LEDGER_ID from rcv_receiving_sub_ledger
where rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id);

XLA_DISTRIBUTION_LINKSstores the link between transactions and subledger journal entry lines.
The tables XLA_DISTRIBUTION_LINKSand RCV_RECEIVING_SUB_LEDGERare linked by

SOURCE_DISTRIBUTION_ID_NUM_1 = RCV_SUB_LEDGER_ID
SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'

For example:

Select *
FROM XLA_DISTRIBUTION_LINKS XDL
where XDL.SOURCE_DISTRIBUTION_ID_NUM_1 IN (' << list of RRSL.RCV_SUB_LEDGER_ID >>')
And SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
And APPLICATION_ID = 707;

Select * from rcv_receiving_sub_ledger
where rcv_sub_ledger_id in
(select source_dIstribution_id_num_1
FROM XLA_DISTRIBUTION_LINKS
where SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
and APPLICATION_ID=707)

Link the PODistibution, Receipts, Receiving Subledger and XLA Distributions

select rt.po_header_id, pd.po_distribution_id,
rt.transaction_id, rrsl.rcv_sub_ledger_id,
xdl.ae_header_id,xdl.SOURCE_DISTRIBUTION_ID_NUM_1,
xdl.SOURCE_DISTRIBUTION_TYPE
from rcv_receiving_sub_ledger rrsl, rcv_transactions rt,
po_distributions_all pd, xla_distribution_links xdl
where pd.po_distribution_id = rt.PO_DISTRIBUTION_ID
and rt.transaction_id = rrsl.rcv_transaction_id
and rt.PO_DISTRIBUTION_ID = rrsl.reference3
and rrsl.rcv_sub_ledger_id = xdl.SOURCE_DISTRIBUTION_ID_NUM_1
and xdl.SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
and xdl.APPLICATION_ID=707;

Thetables PO_BC_DISTRIBUTIONS and XLA_AE_HEADERS arelinked with the column 'event_id'.

The tables XLA_AE_HEADERS and GL_BC_PACKETS arelinked with the column 'ae_header_id'.

XLA_DISTRIBUTION_LINKS and GL_BC_PACKETS arelinked by 'source_distribution_type' and 'source_distribution_id_num_1' . OnReserving and Approving the PO, Encumbrance entries get generated in table GL_BC_PACKETS withcolumn 'source_distribution_type' as po_distributions_allandcolumn 'source_distribution_id_num_1' as po_distribution_id.

The tables XLA_AE_HEADERS and XLA_AE_LINES arelinked with the column 'ae_header_id'.

The tables XLA_DISTRIBUTION_LINKS and MTL_TRANSACTION_ACCOUNTS arelinked by

source_distribution_id_num_1 = inv_sub_ledger_id
source_distribution_type = MTL_TRANSACTION_ACCOUNTS

XLA_AE_LINES table is mapped to GL_IMPORT_REFERENCES table with the 'gl_sl_link_id'and 'gl_sl_link_table' columns.The linking columns between GL_IMPORT_REFERENCES and GL_JE_LINES tables are 'je_header_id'and 'je_line_num.'

DATA FROM PO TABLES

SELECT * FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

SELECT * FROM PO_LINES_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

SELECT * FROM po_line_locations_archive_all
WHERE PO_HEADER_ID = &&po_header_id

SELECT * FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

SELECT * FROM PO_RELEASES_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID;

RECEIVING AND DELIVERY DATA

select * from rcv_transactions where po_header_id in (<give the po_header_id>)

select * from mtl_material_transactions where rcv_transaction_id in (<give the transaction_id from query above>)

DATA FROM RECEIVING,INVENTORY TABLES and AP TABLES

SELECT * FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID IN
(SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_LINES
WHERE PO_HEADER_ID =&&PO_HEADER_ID );

SELECT * FROM RCV_SHIPMENT_LINES
WHERE PO_HEADER_ID =&&PO_HEADER_ID;

SELECT * FROM RCV_TRANSACTIONS
WHERE PO_HEADER_ID =&&PO_HEADER_ID;

SELECT * FROM RCV_ACCOUNTING_EVENTS
WHERE PO_HEADER_ID =&&PO_HEADER_ID;

SELECT * FROM RCV_RECEIVING_SUB_LEDGER
WHERE RCV_TRANSACTION_ID IN
(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS
WHERE PO_HEADER_ID =&&PO_HEADER_ID);

SELECT * FROM RCV_SUB_LEDGER_DETAILS
WHERE RCV_TRANSACTION_ID IN
(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS
WHERE PO_HEADER_ID =&&PO_HEADER_ID);

SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_ID = &&PO_HEADER_ID;

SELECT * FROM MTL_TRANSACTION_ACCOUNTS
WHERE TRANSACTION_ID IN
( SELECT TRANSACTION_ID FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_ID = &&PO_HEADER_ID )

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE PO_DISTRIBUTION_ID IN
(SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID );

SELECT * FROM AP_INVOICES_ALL
WHERE INVOICE_ID IN
(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE PO_DISTRIBUTION_ID IN
( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID ));

RECEIVING TRANSACTIONS/RRLS and XLA TABLES

RCV

Select * FROM RCV_TRANSACTIONS
where TRANSACTION_ID IN
( Select rcv_transaction_id from rcv_receIving_sub_ledger
where rcv_sub_ledger_id in
(select source_dIstribution_id_num_1
from XLA_DISTRIBUTION_LINKS
where SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
and APPLICATION_ID=707))

RRSL

Select * from rcv_receIving_sub_ledger
where rcv_sub_ledger_id in
(select source_dIstribution_id_num_1
FROM XLA_DISTRIBUTION_LINKS
where SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
and APPLICATION_ID=707)

select * from xla_ae_headers where ae_header_id in (<xxxx,xxxx>)
select * from xla_ae_lines where ae_header_id in (<xxxx,xxxxx>)
select * from xla_distribution_links where ae_header_id in (<xxxx,xxxxx>)
select * from xla_distribution_links where source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER' and source_distribution_id_num_1 in (<give the rcv_sub_ledger_id from result of RRLS query>)

XLA and MTL TABLES
-

SELECT * FROM MTL_MATERIAL_TRASNACTIONS
WHERE TRANSACTION_ID=&TXN_ID

SELECT * FROM MTL_TRANSACTION_ACCOUNTS
WHERE TRASNACTION_ID=&TXN_ID

SELECT * FROM XLA_TRASNACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID
(SOURCE_ID_INT_1 also represnets txn_id)

SELECT * FROM XLA_EVENTS
WHERE ENTITY_ID IN (
SELECT ENTITY_ID FROM XLA_TRANSACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID)

SELECT * FROM XLA_DISTRIBUTION_LINKS
WHERE EVENT_ID IN
(SELECT EVENT_ID FROM XLA_EVENTS
WHERE ENTITY_ID IN (
SELECT ENTITY_ID FROM XLA_TRANSACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID))

SELECT * FROM XLA_AE_HEADERS
WHERE EVENT_ID IN
(SELECT EVENT_ID FROM XLA_EVENTS
WHERE ENTITY_ID IN (
SELECT ENTITY_ID FROM XLA_TRANSACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID))

SELECT * FROM XLA_AE_LINES
WHERE AE_HEADER_ID IN
(SELECT AE_HEADER_ID FROM XLA_AE_HEADERS
WHERE EVENT_ID IN

select * from rcv_transactions where po_header_id in (<give the po_header_id>)

select * from mtl_material_transactions where rcv_transaction_id in (<give the transaction_id from result of query above>)

select * from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (<give the inv_sub_ledger_id from result of query above>)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22816976/viewspace-2135331/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22816976/viewspace-2135331/