Entries goes into mtl_material_transactions table when you receive a PO however you would have created and received a expense PO ,
Please check the DESTINATION_TYPE_CODE in the PO_distributions_all table the value would be EXPENSE for your PO .
i have checked the DESTINATION_TYPE_CODE. they are having value as EXPENSE but one of my PO is not showing having DESTINATION_TYPE_CODE= INVENTORY
i am writing a query to show the receiving against PO's, should i query MTL_MATERIAL_TRANSACTIONS table for non EXPENSE PO or just query the RCV_HEADERS, LINES and TRANSACTIONS table to show net quantity?
Ok , then you can also check few more things , like you can check the item setup of the item which you have received in the PO , if the item is expense item then also no entry will be created in the inventory tables (mtl_material_transactions) , also if the Receipt Routing is "Direct Delivery" of the PO then also entry will not go to the inventory .
now if you are identifying the net quantity received against a PO then you can use the RCV_headers the below table .
select QUANTITY_RECEIVED from po_Line_locations_all
routing is STANDARD RECEIPT. i could not find the item whether it is expense item or not.
i have checked the quantity_received from po_line_locations_all. it does not exclude the quantity which is in RECEIVING. i need DELIVERED - RETURN
can you also please check in the RCV_TRANSACTIONS table where TRANSACTION_TYPE='RETURN TO VENDOR' and join this table also .
PO receiving records go to MTL_MATERIAL_TRANSACTIONS table only upon DELIVERY. Please perform the DELIVER transaction after RECEIPT is created.
i am picking up the records against DELIVER transactions from RCV_TRANSACTIONS table joined with RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES.
if i pick RCV_TRANSACTIONS.QUANTITY, it does not exclude RETURN TO VENDOR / RETURN TO RECEIVING.
and if i pick RCV_SHIPMENT_LINES.QUANTITY_RECEIVED, it shows duplicate quantity in case of partial receiving. e.g( RECEIVE 20 and DELIVER 10 first time. then DELIVER 10 another time). QUANTITY_RECEIVED will show 20 and then again 20.