Tables relationship in SCM Oracle

 Relation between SCM tables

1/ PO_req_distributions_all(distribution_id)
 and 
PO_distributions_all (req_distribution_id)

2/ rcv_shipment_lines(po_header_id,po_line_id)
and 
po_headers_all,po_lines_all,po_line_locations_all

3/ rececipt and Invoice relation is Via PO tables

ap_invoice_distributions_all(po_distribution_id)
and
po_distributions_All (po_distribution_id)

//example
SELECT distinct pha.segment1 po_number
       ,aia.invoice_num invoice_number
       ,rsh.receipt_num receipt_number
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid
       ,ap_invoices_all aia
       ,rcv_shipment_lines rsl
       ,rcv_shipment_headers rsh
WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aia.invoice_id=aid.invoice_id
   AND rsl.po_header_id=pha.po_header_id
   AND rsl.shipment_header_id=rsh.shipment_header_id

4/ AP to XLA table

Source_id_int_1 from xla_transaction_entities and invoice_id from ap_invoices_All

5/   XLA to GL tables

The most important column in GL_IMPORT_REFERENCES is GL_SL_LINK_ID, you also can find this column in table XLA_AE_LINES

Lets see how we can Drilldown from Account Payables Invoice details to GL Journal Detail.



example
//

select * from xla_entity_id_mappings
where entity_code like 'AP_INVOICES';

1. When we create invoices it will hit below given table in account Payables.
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

e.g. :
Invoice Header:
select * from ap_invoices_all
where invoice_num='IN0097158'; -- Copy the value for Invoice_id for future rerfrence 16211228

Invoice Line : 
select * from ap_invoice_lines_all
where invoice_id=16211228;

Invoice Distributions :

select * from ap_invoice_distributions_all
where invoice_id=16211228;

SLA Tables :
select * from XLA.xla_transaction_entities 
where source_id_int_1=16211228
and transaction_number='IN0097158'; -- From this table copy value for Entity id 580427218
--  
SELECT * FROM XLA_AE_HEADERS
WHERE ENTITY_ID=580427218;  --- From this table copy value Event id 582978337 and ae_header_id value 126276903
-- 
select * from xla_events
WHERE EVENT_ID=582978337;
-- 
select * from xla_ae_lines
WHERE AE_HEADER_ID=126276903;
--

SELECT *
from gl_import_references gir
where gir.gl_sl_link_id in
(select gl_sl_link_id from xla_ae_lines
WHERE AE_HEADER_ID=126276903); -- from this table you will get detail for je_header_id, je_line_num, je_batch_id