PO到RTV

--POR entered --3 records applied and saved
SELECT rh.authorization_status,
       rh.requisition_header_id,
       rh.*
  FROM po_requisition_headers_all rh
 WHERE segment1 = '1952058'
   AND org_id = 84;
--authorization_status:INCOMPLETE

SELECT *
  FROM po_requisition_lines_all
 WHERE requisition_header_id = 4327744
   AND requisition_line_id = 4409451;

SELECT d.distribution_id,
       d.*
  FROM po_req_distributions_all d
 WHERE requisition_line_id = 4409451;
--distribution_id:4455570

---------------------------------------------------
--submited and go to Workflow User to approve the POR
-------------------------------------------------
--PO approved --4 records applied and saved
SELECT h.wf_item_type,
       h.wf_item_key,
       h.authorization_status,
       h.type_lookup_code,
       h.closed_code,
       h.org_id,
       h.*
  FROM po_headers_all h
 WHERE segment1 = '439825775';
--po_header_id = 1015429 Authorization_status:APPROVED 

SELECT l.po_line_id,
       l.closed_code,
       l.*
  FROM po_lines_all l
 WHERE po_header_id = 1015429;
--po_line_id = 8971344 

SELECT loc.line_location_id,
       loc.ship_to_organization_id,
       loc.approved_flag,
       loc.inspection_required_flag,
       receipt_required_flag,
       loc.closed_code,
       loc.accrue_on_receipt_flag,
       loc.*
  FROM po_line_locations_all loc
 WHERE po_line_id = 8971344;
--Line_location_id:14293816 
--Approved_Flag:Y  
--Receipt_required_flag=Y 
--Closed_code:OPEN
--accrue_on_receipt_flag:Y

SELECT dis.po_distribution_id,
       dis.code_combination_id,
       dis.destination_type_code,
       dis.*
  FROM po_distributions_all dis
 WHERE line_location_id = 14293816;
--po_distribution_id:14224451 
--Code_combination_id:1209 
--Destination_type_code:INVENTORY

-----------------------------------------------------
--PO receipts --2 records applied and saved

SELECT * --shipment_line_id:9655858
  FROM rcv_shipment_lines
 WHERE po_header_id = 1015429
   AND po_line_id = 8971344
   AND po_line_location_id = 14293816;
--shipment_line_id:9655858 
--shipment_header_id = 4081557

SELECT * FROM rcv_shipment_headers WHERE shipment_header_id = 4081557;

SELECT rt.transaction_type,
       rt.interface_source_code,
       rt.source_document_code,
       rt.destination_type_code,
       rt.inspection_status_code,
       rt.*
  FROM rcv_transactions rt
 WHERE po_header_id = 1015429
   AND po_line_id = 8971344
   AND po_line_location_id = 14293816
   AND shipment_line_id = 9655858
   AND shipment_header_id = 4081557;
--transaction_id = 16396290 
--transaction_type:RECEIVE 
--destination_type_code:RECEIVING

SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id:16396290 接受阶段的接受事务处理id
       mo.reference, --PO_LINE_LOCATION_ID
       mo.reference_id, --po_line_location_id:14293816
       mo.*
  FROM inv.mtl_txn_request_lines mo
 WHERE inventory_item_id = 13139193;
--header_id:7098464

SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098464;

-----------------------------------------------------
--PO receiving transactions(DELIVER) --1 records applied and saved

SELECT rt.transaction_type,
       rt.interface_source_code,
       rt.source_document_code,
       rt.destination_type_code,
       rt.inspection_status_code,
       rt.subinventory,
       rt.transfer_lpn_id,
       rt.*
  FROM rcv_transactions rt
 WHERE po_header_id = 1015429
   AND po_line_id = 8971344
   AND po_line_location_id = 14293816
   AND shipment_line_id = 9655858
   AND shipment_header_id = 4081557
   AND po_distribution_id = 14224451;
---transaction_id = 16396310 
--transaction_type = DELIVER 
--destination_type_code = INVENTORY

SELECT mmt.source_code, --RCV
       mmt.source_line_id, --rcv_transaction_id:16396310   接受阶段的接受事务处理id
       transaction_source_id, --po_header_id:1015429
       mmt.*
  FROM inv.mtl_material_transactions mmt
 WHERE rcv_transaction_id = 16396310
   AND transaction_source_id = 1015429; --po_header_id
--Transaction Type:PO Receipt 
--Transaction Action:Receipt into stores 
--Inventory_item_id:13139193

SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id:16396290
       mo.reference, --PO_LINE_LOCATION_ID
       mo.reference_id, --po_line_location_id:14293816
       mo.*
  FROM inv.mtl_txn_request_lines mo
 WHERE txn_source_id = 16396290;
--header_id:7098464

SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098464;

----------------------------------------------------
--PO Return 
---------------
--Return To Receiving --1 records applied and saved

SELECT rt.transaction_type,
       rt.interface_source_code,
       rt.source_document_code,
       rt.destination_type_code,
       rt.inspection_status_code,
       rt.subinventory,
       rt.transfer_lpn_id,
       rt.*
  FROM rcv_transactions rt
 WHERE po_header_id = 1015429
   AND po_line_id = 8971344
   AND po_line_location_id = 14293816
   AND shipment_line_id = 9655858
   AND shipment_header_id = 4081557
   AND po_distribution_id = 14224451
   AND interface_source_code IS NULL;
--Transactiion_type:RETURN TO RECEIVING 
--Source_document_code:PO 
--Destination_type_code = INVENTORY
--Transaction_id:16396330

SELECT mmt.source_code, --RCV
       mmt.source_line_id, --rcv_transaction_id:16396330
       transaction_source_id, --po_header_id:1015429
       mmt.*
  FROM inv.mtl_material_transactions mmt
 WHERE rcv_transaction_id = 16396330
   AND transaction_source_id = 1015429; --po_header_id
--Transaction Type:          Return to Vendor 
--Transaction Action:        Issue from stores 
--Inventory_item_id:         13139193

--0 rows selected
SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id:16396290
       mo.*
  FROM inv.mtl_txn_request_lines mo
 WHERE txn_source_id = 16396330;

------------------
--Return To Supplier --1 records applied and saved
SELECT rt.transaction_type,
       rt.interface_source_code,
       rt.source_document_code,
       rt.destination_type_code,
       rt.inspection_status_code,
       rt.subinventory,
       rt.transfer_lpn_id,
       rt.*
  FROM rcv_transactions rt
 WHERE po_header_id = 1015429
   AND po_line_id = 8971344
   AND po_line_location_id = 14293816
   AND shipment_line_id = 9655858
   AND shipment_header_id = 4081557
      --AND po_distribution_id = 14224451
   AND interface_source_code IS NULL;
--Transactiion_type:RETURN TO VENDOR 
--Source_document_code:PO 
--Destination_type_code = RECEIVING 
--transaction_id:16396350

--0 rows selected
SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id
       mo.*
  FROM inv.mtl_txn_request_lines mo
 WHERE txn_source_id = 16396350;
------------------------------------------------------------

SELECT *
  FROM rcv_receiving_sub_ledger
 WHERE rcv_transaction_id IN (16396290, 16396350);
--RECEIVING, RETURN TO VENDOR
-----------------------------------------------------------

po_requisition_headers_all

po_requisition_lines_all

po_req_distributions_all

po_headers_all

po_lines_all

po_line_locations_all

po_distributions_all

po_releases_all

rcv_shipment_lines

rcv_shipment_headers

rcv_transactions

mtl_txn_request_lines

mtl_txn_request_headers

mtl_material_transactions_temp  

mtl_material_transactions

mtl_onhand_quantities_detail  

rcv_receiving_sub_ledger