This PO Open Purchase Orders by Buyer query displays open purchase order and shipment details by buyer and operating unit, including PO status and monthly period grouping, helping users monitor order fulfillment and manage procurement activities efficiently.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Open purchase orders and shipment details by buyer and operating unit.
HAOU.NAME OPERATING_UNIT,
PPNF.FULL_NAME BUYER,
-- PO Header Info
PHA.SEGMENT1 PURCHASE_ORDER_NUMBER,
PHA.CURRENCY_CODE CURRENCY,
PHA.DOCUMENT_STATUS PURCHASE_ORDER_STATUS,
-- Supplier Info
PSV.VENDOR_NAME SUPPLIER_NAME,
-- PO Line Info
PLA.LINE_NUM LINE_NUMBER,
PLA.ITEM_REVISION REVISION,
PLA.ITEM_DESCRIPTION DESCRIPTION,
PLT.LINE_TYPE,
-- Item Info
ESIB.ITEM_NUMBER,
-- Shipment Info
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
NVL(PLLA.PROMISED_DATE, PLLA.NEED_BY_DATE) PROMISED_DATE,
TO_CHAR(NVL(PLLA.PROMISED_DATE, PLLA.NEED_BY_DATE), 'YYYY-MM') PERIOD_NAME,
PLLA.PRICE_OVERRIDE UNIT_PRICE,
-- Quantity/Amount Info
DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', PLLA.AMOUNT,
'FIXED PRICE', PLLA.AMOUNT,
PLLA.QUANTITY
) QUANTITY_AMOUNT_ORDERED,
DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', PLLA.AMOUNT_RECEIVED,
'FIXED PRICE', PLLA.AMOUNT_RECEIVED,
PLLA.QUANTITY_RECEIVED
) QUANTITY_AMOUNT_RECEIVED,
DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', PLLA.AMOUNT_BILLED,
'FIXED PRICE', PLLA.AMOUNT_BILLED,
PLLA.QUANTITY_BILLED
) QUANTITY_AMOUNT_BILLED,
(
DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE',
(PLLA.AMOUNT - NVL(PLLA.AMOUNT_RECEIVED, 0)) /
DECODE(NVL(PLLA.AMOUNT, 0), 0, 1, PLLA.AMOUNT),
'FIXED PRICE',
(PLLA.AMOUNT - NVL(PLLA.AMOUNT_RECEIVED, 0)) /
DECODE(NVL(PLLA.AMOUNT, 0), 0, 1, PLLA.AMOUNT),
(NVL(PLLA.QUANTITY, 0) - NVL(PLLA.QUANTITY_RECEIVED, 0)) /
DECODE(NVL(PLLA.QUANTITY, 0), 0, 1, PLLA.QUANTITY)
) * 100
) PERCENT_DUE
FROM
PO_LINE_LOCATIONS_ALL PLLA,
EGP_SYSTEM_ITEMS_B ESIB,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
PO_LINE_TYPES PLT,
POZ_SUPPLIERS_V PSV,
PER_ALL_PEOPLE_F PAPF,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_PERSON_NAMES_F PPNF
WHERE
1 = 1
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLA.ITEM_ID = ESIB.INVENTORY_ITEM_ID(+)
AND NVL(PLLA.CANCEL_FLAG, 'N') = 'N'
AND NVL(PLA.CANCEL_FLAG, 'N') = 'N'
AND NVL(PHA.CANCEL_FLAG, 'N') = 'N'
AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND PSV.VENDOR_ID = PHA.VENDOR_ID
AND PHA.TYPE_LOOKUP_CODE IN ('STANDARD', 'BLANKET', 'PLANNED')
AND PHA.AGENT_ID = PAPF.PERSON_ID
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PHA.PRC_BU_ID = HAOU.ORGANIZATION_ID
AND PPNF.NAME_TYPE = 'GLOBAL';