This PO Blanket Order With Usage Report query displays detailed blanket and planned PO line data for each supplier and buyer, helping users to track procurement commitments and analyze agreement fulfillment.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve detailed blanket and planned PO line and supplier info.
HAOU.NAME OPERATING_UNIT,
PHA.SEGMENT1 PO_NUMBER,
PLA.LINE_NUM PO_LINE_NUMBER,
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PPNF.FULL_NAME BUYER_NAME,
PLA.ITEM_DESCRIPTION,
PLA.ITEM_REVISION,
PLA.UOM_CODE UNIT_OF_MEASURE,
PHA.CURRENCY_CODE CURRENCY,
PLA.LIST_PRICE_PER_UNIT,
PLA.BASE_MODEL_PRICE,
PLA.COMMITTED_AMOUNT BLANKET_PO_AMOUNT,
NVL (PLA.UNIT_PRICE * PLA.QUANTITY_COMMITTED,0) EXTENDED_AMOUNT,
PLA.COMMITTED_AMOUNT * NVL (PHA.RATE,1) BLANKET_PO_AMOUNT_BASE,
NVL (PLA.UNIT_PRICE * NVL (PHA.RATE,1) * PLA.QUANTITY_COMMITTED,0) EXTENDED_AMOUNT_BASE,
PLA.UNIT_PRICE * NVL (PHA.RATE,1) UNIT_PRICE_BASE,
NVL (PLA.QUANTITY_COMMITTED, 0) QUANTITY,
PLA.UNIT_PRICE UNIT_PRICE,
PLA.CANCEL_DATE,
PLA.CANCEL_REASON,
PLA.CLOSED_DATE,
PLA.CLOSED_REASON
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND HAOU.ORGANIZATION_ID = PHA.PRC_BU_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID(+)
AND NVL (PLLA.CANCEL_FLAG, 'N') = 'N'
AND PHA.TYPE_LOOKUP_CODE ='BLANKET'