This PO Purchase Order Lines Report query displays detailed purchase order line information for standard purchase orders, helping users track ordered quantities, amounts, supplier and buyer details, and delivery status to support procurement and financial decision-making.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Shows purchase order line details for standard POs to aid procurement tracking.
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
PU2.USERNAME BUYER_USERNAME,
PHA.SEGMENT1 PURCHASE_ORDER_NUMBER,
PHA.TYPE_LOOKUP_CODE DOCUMENT_TYPE,
PLA.LINE_NUM LINE_NUMBER,
PLT.LINE_TYPE,
ECB1.SEGMENT1 CATEGORY,
PLA.ITEM_DESCRIPTION,
PLA.QUANTITY ORDERED_QUANTITY,
PHA.CURRENCY_CODE CURRENCY,
PLA.UNIT_PRICE,
PLA.LIST_PRICE_PER_UNIT LIST_PRICE,
PLA.NOT_TO_EXCEED_PRICE PRICE_LIMIT,
PLA.CANCEL_DATE,
PLA.CREATION_DATE,
PHC.HAZARD_CLASS,
PA.TOTAL_AMT_FUNC,
PA.TOTAL_AMT_BASE,
PA.QUANTITY_BILLED,
PA.QUANTITY_DELIVERED,
PA.QUANTITY_CANCELLED,
PA.QUANTITY_ORDERED,
PA.QUANTITY_ORDERED - (PA.QUANTITY_CANCELLED + PA.QUANTITY_DELIVERED) QUANTITY_DUE,
PA.AMOUNT_BILLED,
PA.AMOUNT_ORDERED_AMT - (PA.AMOUNT_CANCELLED + PA.AMOUNT_DELIVERED) AMOUNT_DUE,
-- Dates
PA.PROMISED_DATE
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_TYPES PLT,
EGP_CATEGORIES_B ECB1,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PER_USERS PU2,
PO_HAZARD_CLASSES PHC,
(
SELECT
PDA.PO_HEADER_ID,
PDA.PO_LINE_ID,
PLLA.PROMISED_DATE,
NVL(SUM(DECODE(
PDA.QUANTITY_ORDERED,
NULL, (NVL(PDA.AMOUNT_ORDERED, 0) - NVL(PDA.AMOUNT_CANCELLED, 0)),
((NVL(PDA.QUANTITY_ORDERED, 0) - NVL(PDA.QUANTITY_CANCELLED, 0)) * NVL(PLLA.PRICE_OVERRIDE, 0))
) * NVL(PDA.RATE, 1)), 0) TOTAL_AMT_FUNC,
NVL(SUM(DECODE(
PDA.QUANTITY_ORDERED,
NULL, (NVL(PDA.AMOUNT_ORDERED, 0) - NVL(PDA.AMOUNT_CANCELLED, 0)),
((NVL(PDA.QUANTITY_ORDERED, 0) - NVL(PDA.QUANTITY_CANCELLED, 0)) * NVL(PLLA.PRICE_OVERRIDE, 0))
)), 0) TOTAL_AMT_BASE,
SUM(NVL(PDA.QUANTITY_BILLED, 0)) QUANTITY_BILLED,
SUM(NVL(PDA.QUANTITY_CANCELLED, 0)) QUANTITY_CANCELLED,
SUM(NVL(PDA.QUANTITY_DELIVERED, 0)) QUANTITY_DELIVERED,
SUM(NVL(PDA.QUANTITY_ORDERED, 0)) QUANTITY_ORDERED,
SUM(NVL(PDA.AMOUNT_BILLED, 0)) AMOUNT_BILLED,
SUM(NVL(PDA.AMOUNT_CANCELLED, 0)) AMOUNT_CANCELLED,
SUM(NVL(PDA.AMOUNT_DELIVERED, 0)) AMOUNT_DELIVERED,
SUM(NVL(PDA.AMOUNT_ORDERED, 0)) AMOUNT_ORDERED_AMT,
SUM(NVL(PDA.AMOUNT_BILLED, 0) * NVL(PDA.RATE, 1)) AMOUNT_BILLED_AMT_FUNC,
SUM(NVL(PDA.AMOUNT_CANCELLED, 0) * NVL(PDA.RATE, 1)) AMOUNT_CANCELLED_FUNC,
SUM(NVL(PDA.AMOUNT_DELIVERED, 0) * NVL(PDA.RATE, 1)) AMOUNT_DELIVERED_FUNC,
SUM(NVL(PDA.AMOUNT_ORDERED, 0) * NVL(PDA.RATE, 1)) AMOUNT_ORDERED_FUNC
FROM
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA
WHERE
PLLA.SHIPMENT_TYPE = 'STANDARD'
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
GROUP BY
PDA.PO_HEADER_ID,
PDA.PO_LINE_ID,
PLLA.PROMISED_DATE
) PA
WHERE
PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLA.CATEGORY_ID = ECB1.CATEGORY_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND PHA.AGENT_ID = PU2.PERSON_ID
AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
AND PA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PA.PO_LINE_ID = PLA.PO_LINE_ID
AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
ORDER BY PSV.VENDOR_NAME ,
PHA.SEGMENT1 ,
PLA.LINE_NUM