The sum of po_line_locations_all.quantity_received / quantity_accepted. Not sure which one reflects Return to Receiving.
Or try this API, AP_MATCHING_UTILS_PKG.GET_RECEIPT_QUANTITIES It is based on receiving transaction.
Easiest way to refer to the QUANTITY_RECEIVED column in the PO_LINE_LOCATIONS_ALL table to get the actual received quantity.
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.
PO_LINE_LOCATIONS_ALL.QUANTITY_RECEIVED does not exclude RETURN_TO_RECEIVING. obviously it is receiving column. i think if i pick up PO_DISTRIBUTIONS_ALL.QUANTITY_DELIVERED, it shows the actual net quantity delivered excluding all returns.
for others reference can you please post the query as well .
i have finalized my query as below. my target was to get net delivered quantity. (DELIVER - RETURN TO RECEIVING)
If you find any problem in this query, plz let me know.
,(sum(line_qty) / count(line_qty)) po_line_qty
,(select segment1 from po_headers_all where po_header_id = rsl.po_header_id) po_num
,(select quantity from po_lines_all where po_header_id = rsl.po_header_id and po_line_id = rsl.po_line_id) line_qty
,nvl((select nvl(mst.description,msi.description) from mtl_system_items_tl mst where mst.inventory_item_id = msi.inventory_item_id
and mst.organization_id = msi.organization_id and nvl(mst.language,'&LANG') = '&LANG'),rsl.item_description) item_desc
,decode(rt.transaction_type,'DELIVER',rt.quantity,rt.quantity * -1) net_qty_delvr
from rcv_shipment_headers rsh
where rsh.shipment_header_id = rsl.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and rsh.shipment_header_id = rt.shipment_header_id
and rsh.ship_to_org_id = rt.organization_id
and rt.transaction_type in ('DELIVER','RETURN TO RECEIVING')
and rt.source_document_code = 'PO'
and rt.po_release_id is null
and rsl.item_id = msi.inventory_item_id(+)
and nvl(rsl.from_organization_id,7311) = msi.organization_id(+)
and rt.organization_id = 7311
group by receipt_num