This PO Blanket Planned Shipment Details query displays shipment quantities, statuses, and pricing details for blanket and scheduled purchase orders, helping procurement teams track fulfillment progress and manage vendor performance.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Tracks shipment and pricing details for blanket/scheduled POs to support procurement monitoring.
PSUP.SEGMENT1 SUPPLIER_NUMBER,
PSUP.VENDOR_NAME SUPPLIER_NAME,
PHA.SEGMENT1 PURCASE_ORDER_NUMBER,
PHA.CREATION_DATE PO_CREATION_DATE,
-- Line Item Info
PLA.LINE_NUM PO_LINE_NUMBER,
PLA.ITEM_ID,
PLA.ITEM_DESCRIPTION,
PLA.QUANTITY LINE_QUANTITY_ORDERED,
PLA.LINE_STATUS,
PLA.FUNDS_STATUS,
-- Shipment Info
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
PLLA.SHIPMENT_TYPE SHIPMENT_TYPE,
PLLA.SHIP_TO_LOCATION_ID,
HLV.LOCATION_NAME,
PLLA.PROMISED_DATE,
PLLA.NEED_BY_DATE,
PLLA.CANCEL_REASON,
PLLA.CANCEL_DATE,
DECODE(PLLA.CANCEL_FLAG, 'Y', 'Yes', 'N', 'No', NULL) CANCELLED,
-- Shipment Pricing
PHA.CURRENCY_CODE CURRENCY,
PLA.UNIT_PRICE,
PLLA.PRICE_OVERRIDE SHIPMENT_PRICE,
-- Shipment Quantities
ROUND(PLLA.QUANTITY, 1) ORDERED,
ROUND(PLLA.QUANTITY_RECEIVED, 1) QUANTITY_RECEIVED,
ROUND(PLLA.QUANTITY_BILLED, 1) QUANTITY_BILLEDBILLED
FROM
PO_HEADERS_ALL PHA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
PO_LOOKUP_CODES PLC,
POZ_SUPPLIERS_V PSUP,
HR_LOCATIONS_ALL_VL HLV
WHERE
PLLA.FROM_HEADER_ID = PHA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'DOCUMENT_SUBTYPE'
AND PHA.VENDOR_ID = PSUP.VENDOR_ID
AND HLV.LOCATION_ID = PLLA.SHIP_TO_LOCATION_ID
AND PHA.TYPE_LOOKUP_CODE IN ('BLANKET', 'SCHEDULED')