This PO Blanket PO Lines query retrieves Blanket Purchase Order details, buyer/vendor info, amounts, and approval status to support purchase validation and tracking.
#sqlquery
SELECT
-- SQL4FUSION (AN ORBIT ANALYTICS PROJECT)
-- Blanket PO details with buyer, supplier, amounts, and approval status.
HAOU.NAME OPERATING_UNIT,
PPNF.FULL_NAME BUYER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PHA.SEGMENT1 PO_NUMBER,
PHA.DOCUMENT_STATUS APPROVAL_STATUS,
PLA.LINE_NUM,
PLA.ITEM_DESCRIPTION,
PLA.ITEM_REVISION,
NVL(PLA.QUANTITY_COMMITTED, 0) AGREED_QUANTITY,
NVL((
SELECT SUM(PLLA.QUANTITY_RECEIVED)
FROM PO_LINE_LOCATIONS_ALL PLLA
WHERE PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
), 0) QTY_RELEASED,
NVL((
SELECT SUM(PLLA.QUANTITY_RECEIVED * PLLA.PRICE_OVERRIDE)
FROM PO_LINE_LOCATIONS_ALL PLLA
WHERE PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
), 0) AMOUNT_RELEASED,
PLA.UOM_CODE,
PLA.PRICE_TYPE_LOOKUP_CODE,
PLA.LIST_PRICE_PER_UNIT ,
(PLA.LIST_PRICE_PER_UNIT * NVL(PHA.RATE,
DECODE(PHA.CURRENCY_CODE, GL.CURRENCY_CODE, 1, NULL)
)) LIST_PRICE_PER_UNIT_BASE,
GL.CURRENCY_CODE BASE_CURRENCY_CODE,
PLA.COMMITTED_AMOUNT AGREED_BLANKET_PO_AMOUNT,
(PLA.COMMITTED_AMOUNT * NVL(PHA.RATE,
DECODE(PHA.CURRENCY_CODE, GL.CURRENCY_CODE, 1, NULL)
)) AGREED_BLANKET_PO_AMOUNT_BASE,
ATT.NAME TERMS,
NVL(PLA.ALLOW_PRICE_OVERRIDE_FLAG, 'Y') ALLOW_PRICE_OVERRIDE_FLAG,
(PLA.UNIT_PRICE * NVL(PHA.RATE,
DECODE(PHA.CURRENCY_CODE, GL.CURRENCY_CODE, 1, NULL)
)) AGREED_UNIT_PRICE_BASE,
PLA.CANCEL_DATE,
NVL(PLA.CANCEL_FLAG, 'N') CANCEL_FLAG,
PLA.CANCEL_REASON ,
NVL(PLA.CAPITAL_EXPENSE_FLAG, 'N') CAPITAL_EXPENSE_FLAG,
PHA.CURRENCY_CODE ,
PLA.CREATION_DATE LINE_CREATION_DATE,
PLA.LINE_TYPE_ID LINE_TYPE,
(PLA.MARKET_PRICE * NVL(PHA.RATE,
DECODE(PHA.CURRENCY_CODE, GL.CURRENCY_CODE, 1, NULL)
)) MARKET_PRICE_BASE,
NVL(PLA.NEGOTIATED_BY_PREPARER_FLAG, 'N') NEGOTIATED_BY_PREPARER_FLAG,
PLA.NOTE_TO_VENDOR ,
PLA.QUANTITY ORDERED_QUANTITY,
NVL(PLA.OVER_TOLERANCE_ERROR_FLAG, 'N') OVER_TOLERANCE_ERROR_FLAG,
NVL(PLA.UNORDERED_FLAG, 'N') UNORDERED_FLAG
FROM
POZ_SUPPLIERS_V PSV,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
AP_TERMS_TL ATT,
GL_LEDGERS GL,
PER_ALL_PEOPLE_F PAPF,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_PERSON_NAMES_F PPNF
WHERE
1 = 1
AND PSV.VENDOR_ID = PHA.VENDOR_ID
AND PHA.PRC_BU_ID = HAOU.ORGANIZATION_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND NVL(PHA.PRC_BU_ID, -9999) = NVL(FSPA.ORG_ID(+), -9999)
AND FSPA.SET_OF_BOOKS_ID = GL.LEDGER_ID(+)
AND PHA.TERMS_ID = ATT.TERM_ID(+)
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PHA.TYPE_LOOKUP_CODE IN ('BLANKET', 'PLANNED')