PO Flow

Stage 1: PO Creation
PO_HEADERS_ALL

select po_header_id
from po_headers_all
where segment1 =;

select *
from po_headers_all
where po_header_id =;
PO_LINES_ALL
select *
from po_lines_all
where po_header_id =;
PO_LINE_LOCATIONS_ALL

select *
from po_line_locations_all
where po_header_id =;

PO_DISTRIBUTIONS_ALL

select *
from po_distributions_all
where po_header_id =;

PO_RELEASES_ALL

SELECT *
FROM po_releases_all
WHERE po_header_id =;

Stage 2: Once PO is received, data is moved to respective receving tables and inventory tables

select *
from rcv_shipment_headers
where shipment_header_id in
(select shipment_header_id
from rcv_shipment_lineswhere po_header_id =);

RCV_SHIPMENT_LINES

select *
from rcv_shipment_lines
where po_header_id =;

RCV_TRANSACTIONS

select *
from rcv_transactions
where po_header_id =;

RCV_ACCOUNTING_EVENTS

SELECT *
FROM rcv_Accounting_Events
WHERE rcv_transaction_id IN
(select transaction_id
from rcv_transactionswhere po_header_id =);

RCV_RECEIVING_SUB_LEDGER

select
* from
rcv_receiving_sub_ledger
where rcv_transaction_id IN
(select transaction_id
from rcv_transactions
where po_header_id =);

RCV_SUB_LEDGER_DETAILS

select *
from rcv_sub_ledger_details
where rcv_transaction_id IN
(select transaction_id
from rcv_transactions
where po_header_id =);

MTL_MATERIAL_TRANSACTIONS
select *
from mtl_material_transactions
where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS
select *
from mtl_transaction_accounts
where transaction_id IN
(select transaction_id
from mtl_material_transactions
where transaction_source_id =);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select *
from ap_invoice_distributions_all
where po_distribution_id in
(select po_distribution_id
from po_distributions_all
where po_header_id =);

AP_INVOICES_ALL

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 =));

Stage 4 : Mostly there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select *
from pa_expenditure_items_all peia
where peia.orig_transaction_reference IN
(select to_char(transaction_id)
from mtl_material_transactions
where transaction_source_id = );

Stage 5 : General Ledger

GL_INTERFACE

select * from gl_interface gli
where user_je_source_name =’Purchasing’
and gl_sl_link_table =’RSL’
and reference21=’PO’
and exists (select 1
from rcv_receiving_sub_ledger rrsl
where gli.reference22 =RRSL.reference2
and GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactionswhere po_header_id ));

GL_IMPORT_REFERENCES

SELECT
*FROM gl_import_references GLIR
WHERE reference_1=’PO’
AND gl_sl_link_table =’RSL’
AND EXISTS ( SELECT 1 FROM
rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id
from rcv_transactions
where po_header_id =))