This content has been marked as final. Show 4 replies
You can look into rcv_transactions for those po_line_ids that have a record with a transaction_type of RECEIVE but don't have record with a transaction_type of accept.
Keep in mind that for a received quantity of 10, you may have delivered 3 first and then 1 later.
So you need to add up all quantities with transaction type of ACCEPT and then compare to the RECEIVE quantities.
Also keep in mind that corrections will add complexity to your query.
SELECT rct.transaction_id parent_transaction_Id,
FROM apps.po_headers_all poh,
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.po_line_location_id =
AND rsl.po_line_location_id = pll.Line_location_id
AND rct.po_line_location_id = pll.Line_location_id
AND poh.po_header_id = pol.po_header_id
AND poh.po_header_id = rct.po_header_id
AND pol.po_line_id = rct.po_line_id
AND rct.transaction_type IN ('RECEIVE')
AND NOT EXISTS
FROM apps.rcv_transactions rct1
WHERE poh.po_header_id = rct1.po_header_id
AND pol.po_line_id = rct1.po_line_id
AND rct1.transaction_type =
Dipanjan's query will more or less serve your purpose. But the query doesn't consider the receipt routing type, which means it will give those records also where the routing was either "Standard" or "Direct". Also as Sandeep said, you have to consider the other scenarios also like when the item is being returned or receipt is being corrected or multiple receiving has been done as these records may give you repeated records for receipt number.
Did the SQL meet your Scenario?