Following Query would get all the PO Numbers, Item ID, Quantity Order, Received, Canceled etc. The same information can be found in the PO order form from the Front end Application.
SELECT
POL.ITEM_ID “Inventory Item ID”,
POLL.SHIP_TO_LOCATION_ID “Ship to Location ID”,
poll.quantity “Quantity”,
poll.Quantity_received “Quantity Received”,
poll.QUANTITY_cancelled “Quantity Canceled”,
POH.SEGMENT1 “PO Order Number”
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id=pol.po_header_id
AND pol.po_line_id=poll.po_line_id
AND poll.po_header_id=pol.po_header_id
AND poh.authorization_status=’APPROVED’
AND poh.type_lookup_code=’STANDARD’
AND NVL(poh.cancel_flag,’N’) =’N’
AND POL.ITEM_ID IS NOT NULL
AND NVL (poll.quantity, 0) > (NVL (Quantity_received,0) + NVL (Quantity_cancelled,0))
Leave a Reply
Want to join the discussion?Feel free to contribute!