This PO Blanket Purchase query displays Blanket Purchase Order details, buyer and vendor information, and financial metrics, helping users monitor procurement limits, approvals, and released amounts.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Displays Blanket PO, supplier info, and amounts for procurement tracking and approvals.
HAOU.NAME OPERATING_UNIT,
-- Supplier info
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
-- Buyer info
PPNF.FULL_NAME BUYER,
PAPF.PERSON_NUMBER BUYER_NUMBER,
-- PO header info
PHA.SEGMENT1 PO_NUMBER,
PHA.CREATION_DATE PO_CREATION_DATE,
PHA.ACCEPTANCE_DUE_DATE,
NVL(PHA.ACCEPTANCE_REQUIRED_FLAG, 'N') ACCEPTANCE_REQUIRED_FLAG,
NVL(PHA.BLANKET_TOTAL_AMOUNT, 0) AMOUNT_AGREED,
(
SELECT
SUM(
DECODE(
PHA.CURRENCY_CODE,
(PHA.CURRENCY_CODE),
NVL(PLL.PRICE_OVERRIDE, 0) * (NVL(PLL.QUANTITY, 0) - NVL(PLL.QUANTITY_CANCELLED, 0)),
NVL(PLL.PRICE_OVERRIDE, 0) * (NVL(PLL.QUANTITY, 0) - NVL(PLL.QUANTITY_CANCELLED, 0)) * NVL(PHA.RATE, 1) / NVL(PHA.RATE, 1)
)
)
FROM
PO_LINE_LOCATIONS PLL,
PO_LINES PL
WHERE
PLL.PO_LINE_ID = PL.PO_LINE_ID
AND PLL.SHIPMENT_TYPE IN ('BLANKET')
AND PL.PO_HEADER_ID = PHA.PO_HEADER_ID
) RELEASED_AMOUNT,
PHA.AMOUNT_LIMIT ,
(PHA.AMOUNT_LIMIT) * NVL(
PHA.RATE,
DECODE(
PHA.CURRENCY_CODE,
GL.CURRENCY_CODE,
1,
NULL
)
) AMOUNT_LIMIT_BASE,
PHA.MIN_RELEASE_AMOUNT MINIMUM_RELEASE_AMOUNT,
PHA.APPROVED_DATE,
GL.CURRENCY_CODE BASE_CURRENCY_CODE,
PHA.RATE,
PHA.REVISED_DATE,
PHA.REVISION_NUM REVISION_NUMBER,
PHA.FOB_LOOKUP_CODE FOB,
HLA.LOCATION_CODE BILL_TO_LOCATION,
HLA1.LOCATION_CODE SHIP_TO_LOCATION,
ATT.NAME TERMS,
PHA.CLOSED_DATE ,
(NVL(PHA.BLANKET_TOTAL_AMOUNT, 0)) * NVL(
PHA.RATE,
DECODE(
PHA.CURRENCY_CODE,
GL.CURRENCY_CODE,
1,
NULL
)
) AMOUNT_AGREED_BASE,
NVL(PHA.CANCEL_FLAG, 'N') CANCEL_FLAG,
PHA.CONFIRMING_ORDER_FLAG ,
PHA.CURRENCY_CODE CURRENCY_CODE
FROM
AP_TERMS_TL ATT,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PO_HEADERS_ALL PHA,
PER_ALL_PEOPLE_F PAPF,
HR_LOCATIONS_ALL HLA,
HR_LOCATIONS_ALL HLA1,
GL_LEDGERS GL,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_PERSON_NAMES_F PPNF
WHERE
1 = 1
AND PHA.TYPE_LOOKUP_CODE = 'BLANKET'
AND NVL(PHA.PRC_BU_ID, -9999) = NVL(FSPA.ORG_ID, NVL(PHA.PRC_BU_ID, -9999))
AND FSPA.SET_OF_BOOKS_ID = GL.LEDGER_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 PHA.TERMS_ID = ATT.TERM_ID(+)
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND ATT.LANGUAGE = USERENV('LANG')
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PHA.SHIP_TO_LOCATION_ID = HLA1.LOCATION_ID(+)
AND PHA.BILL_TO_LOCATION_ID = HLA.LOCATION_ID(+);