Join between rcv_transactions and mtl_material_transactions

Join between rcv_transactions and mtl_material_transactions

Here we will be discuss about SQL Join between rcv_transactions and mtl_material_transactions. rcv_transactions table is uses for Purchase order Receiving. This table store the PO receipt data with different transactions types For example First We receive the PO , then we do Inspection , Then we do deliver the Material to SubInventory( If item is Inventory)  So rcv_transactions will store the three lines for the Same PO receipt with different Transaction Types (Receive, Inspect,Reject,Deliver).

 

Deliver is the Last steps of PO receipt. We can only find the Join between rcv_transactions and mtl_material_transactions table is PO received Item is Inventory Item. For Expense Item , This Link never created because Expense item does not store in Inventory. It consumed instantly when we deliver this. So here is the join between rcv_transactions and mtl_material_transactions for Inventory Items.

 

 

2 Important Join between rcv_transactions and mtl_material_transactions

 

MTL_MATERIAL_TRANSACTIONS ==> RCV_TRANSACTION_ID

 

RCV_TRANSACTIONS ==> TRANSACTION_ID With Transaction Type = "Deliver"

 

 

SQL Query Using Join between rcv_transactions and mtl_material_transactions

 

Select * from rcv_shipment_headers rsh , rcv_shipment_lines rsl,rcv_transactions rt,mtl_material_transactions mtt

where rsh.shipmment_header_id=rsl.shipmment_header_id

and rsl.shipmment_header_id=rt.shipmment_header_id

and rsl.SHIPMENT_LINE_ID=rt.SHIPMENT_LINE_ID

and rt.transaction_id=mtt.RCV_TRANSACTION_ID

and rt.transaction_type='DELIVER'