Purchase Order’s Headers Based Sub Query

Purchase Order’s Headers Based Sub Query

Step 1:

Select a.PO_HEADER_ID From Po_Headers_All a

Where a.Segment1= 40031000048
-----------
Po_Header_id
349

Po_Lines_All

Select * From Po_Lines_All Where Po_Header_Id =349

Po_Line_Locations_All

Select * From Po_Line_Locations_All Where Po_Header_Id =349;

Po_Distributions_All

Select * From Po_Distributions_All Where Po_header_id =349;

Po_Releases_All

SELECT * FROM Po_Releases_All Where Po_Header_Id =349;

Stage 1: Once PURCHASE ORDER is received data is moved to respective RECEIVING tables and INVENTORY tables.

Step 2:

RECEIVING

RCV_SHIPMENT_HEADERS

Select * From Rcv_Shipment_Headers Where Shipment_Header_Id in(Select Shipment_Header_Id From Rcv_Shipment_Lines Where Po_Header_Id =349);

RCV_SHIPMENT_LINES

Select * From Rcv_Shipment_Lines Where Po_Header_Id =349;

RCV_TRANSACTIONS
Select * From Rcv_Transactions Where Po_Header_Id =349;

RCV_ACCOUNTING_EVENT

Select * From Rcv_Accounting_Events Where Rcv_Transaction_Id IN

(Select Transaction_Id From Rcv_Transactions Where Po_Header_Id =349);

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

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

Step 3: INVENTORY

MTL_MATERIAL_TRANSACTIONS

Select * From Mtl_Material_Transactions Where Transaction_Source_Id =349;

MTL_TRANSACTION_ACCOUNTS

Select * From Mtl_Transaction_Accounts Where Transaction_Id in

( Select TRANSACTION_ID From Mtl_Material_Transactions Where Transaction_Source_Id =349);

Stage 2: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 =349);

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

********************** *** ************************