Receiving can be a one-step or two-steps or three steps process and the process is governed by the Receipt Routing Method in the Purchase Order. Receipt Routing Methods are
- Direct Delivery: Items are received and put away in the same transaction, typically used when goods are directly delivered to their final destination.All the drop ship and expense items are setup as direct delivery.
- Standard Receipt: A two steps process where items are received into a receiving location/dock first, then put away in a separate transaction
- Inspection Required: A three-step process where items are received into a receiving location/dock first, then inspected, and then put away in separate transactions.
Inventory Purchase Orders are received first into the warehouse/receiving dock, then put away into the stock. Here is the SQL Query to get all the PO received, But not yet put into the stock (final destination). The report is very useful for the warehouse supervisor or inventory managers to see their inventory.
SELECT poh.segment1 PO_NUMBER,
pol.line_num,
esi.item_number,
NVL (esi.description, pol.item_description) item_description,
pol.quantity quantity_ordered,
iodv.organization_name RCV_ORG,
rsh.RECEIPT_NUM,
SUM (rsl.QUANTITY_SHIPPED) QUANTITY_SHIPPED,
SUM (rsl.QUANTITY_RECEIVED) QUANTITY_RECEIVED,
SUM (rsl.QUANTITY_DELIVERED) QUANTITY_DELIVERED,
SUM (rsl.QUANTITY_RETURNED) QUANTITY_RETURNED,
SUM (rsl.QUANTITY_ACCEPTED) QUANTITY_ACCEPTED,
SUM (rsl.QUANTITY_REJECTED) QUANTITY_REJECTED,
NVL ((TRUNC (SYSDATE) – TRUNC (rct.transaction_date)), 0) Ageing,
TO_CHAR (rsh.creation_Date, ‘MM/DD/YYYY’) receipt_date
FROM po_headers_all poh,
po_distributions_all pda,
po_lines_all pol,
po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
INV_ORGANIZATION_DEFINITIONS_V IODV,
egp_system_items esi,
rcv_transactions rct,
po_line_types_b pltb
WHERE poh.po_header_id = pda.po_header_id
AND pol.po_header_id = poh.po_header_id
AND pll.po_header_id(+) = poh.po_header_id
AND pll.po_line_id(+) = pol.po_line_id
AND rsl.po_header_id(+) = poh.po_header_id
AND rsl.PO_LINE_ID(+) = pol.PO_LINE_ID
AND rsh.SHIPMENT_HEADER_ID(+) = rsl.SHIPMENT_HEADER_ID
AND pol.po_line_id = pda.po_line_id
AND pda.line_location_id(+) = pll.line_location_id
AND pll.ship_to_organization_id = iodv.organization_id(+)
AND rsl.po_line_location_id(+) = pll.line_location_id
AND pol.item_id = esi.inventory_item_id(+)
AND pll.ship_to_organization_id = esi.organization_id(+)
AND rsh.shipment_header_id = rct.shipment_header_id(+)
AND rsl.shipment_line_id = rct.shipment_line_id(+)
–and rct.transaction_type = ‘RECEIVE’
— AND iodv.organization_name = NVL (‘003’, iodv.organization_name)
–AND TRUNC (rsh.creation_date) BETWEEN NVL(:p_creation_date_from, TRUNC(rsh.creation_date)) AND NVL(:p_creation_date_to, TRUNC(rsh.creation_date))
AND rsl.quantity_received > 0
AND rsl.quantity_delivered < rsl.quantity_received
AND poh.document_status NOT IN (‘ON HOLD’, ‘CANCELED’, ‘INCOMPLETE’)
AND pol.line_status NOT IN (‘ON HOLD’, ‘CANCELED’)
AND pltb.line_type_id = pol.line_type_id
AND pltb.order_type_lookup_code = ‘QUANTITY’
GROUP BY poh.segment1,
rsh.RECEIPT_NUM,
rsh.RECEIPT_SOURCE_CODE,
iodv.organization_name,
pol.line_num,
esi.item_number,
NVL (esi.description, pol.item_description),
rct.transaction_date,
pol.quantity,
TO_CHAR (rsh.creation_Date, ‘MM/DD/YYYY’)
Sample Report Output:
