R12 Mapping Between Subledger Tables, SLA and GL Tables

SOURCE PO TABLE

Table relationships between PO tables and rcv_transactions and rcv_receiving_sub_ledger remain the same as in R11i. The following reference fields are used to capture and 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 that populates this table is RCV_CreateAccounting_PVT (RCVVACCB.pls)

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

XLA_TABLES

SLA-Subledger Accounting, has introduced new SLA tables :

* XLA_AE_HEADERS
* XLA_AE_LINES
* XLA_DISTRIBUTION_LINKS

RCV_RECEIVING_SUB_LEDGER is not directly linked to table GL_JE_LINES, instead it is linked through the subledger tables :

* XLA_AE_HEADERS
* XLA_AE_LINES
* XLA_DISTRIBUTION_LINKS

Accounting entries in RCV_RECEIVING_SUB_LEDGER can be retrieved using SLA tables and GL_IMPORT_REFERENCES table after running Create Accounting.

RCV_RECEIVING_SUB_LEDGER is linked to XLA Tables by RCV_SUB_LEDGER_ID

Run the 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_LINKS stores the link between transactions and subledger journal entry lines.
The tables XLA_DISTRIBUTION_LINKS and RCV_RECEIVING_SUB_LEDGER are 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 (' <>')
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 PO Distibution, 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;

The tables PO_BC_DISTRIBUTIONS and XLA_AE_HEADERS are linked with the column 'event_id'.

The tables XLA_AE_HEADERS and GL_BC_PACKETS are linked with the column 'ae_header_id'.

XLA_DISTRIBUTION_LINKS and GL_BC_PACKETS are linked by 'source_distribution_type' and 'source_distribution_id_num_1' . On Reserving and Approving the PO, Encumbrance entries get generated in table GL_BC_PACKETS with column 'source_distribution_type' as po_distributions_all and column 'source_distribution_id_num_1' as po_distribution_id.

The tables XLA_AE_HEADERS and XLA_AE_LINES are linked with the column 'ae_header_id'.

The tables XLA_DISTRIBUTION_LINKS and MTL_TRANSACTION_ACCOUNTS are linked by

* source_distribution_id_num_1 = inv_sub_ledger_id
* source_distribution_type = MTL_TRANSACTION_ACCOUNTS

GL_TABLES

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 COLLECTION SQL SCRIPTS

DATA FROM PO TABLES
======================

1.
SELECT * FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

2.
SELECT * FROM PO_LINES_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

3.
SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

3a.
SELECT * FROM po_line_locations_archive_all
WHERE PO_HEADER_ID = &&po_header_id

4.
SELECT * FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID

5.
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 ()

select * from mtl_material_transactions where rcv_transaction_id in ()

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 ()

select * from xla_ae_lines where ae_header_id in ()

select * from xla_distribution_links where ae_header_id in ()

select * from xla_distribution_links where source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER' and source_distribution_id_num_1 in ()

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 ()

select * from mtl_material_transactions where rcv_transaction_id in ()

select * from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in ()
References
NOTE:461103.1 - Mapping Between PO Tables And General Ledger Table