This PO Encumbrance Detail Report query displays encumbrance amounts, requisition and purchase order details for each account distribution, helping users track committed spend and ensure accurate budget control.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Tracks PO and requisition encumbrances across accounts for budget oversight.
-- SUPPLIER INFO
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
NVL(PRHA.REQUISITION_NUMBER, PHA.SEGMENT1) DOCUMENT_NUMBER,
DECODE(PRHA.REQUISITION_NUMBER, NULL, 'PO', 'REQ') DOCUMENT_TYPE,
-- LINE / DISTRIBUTION LEVEL INFO
PRLA.LINE_NUMBER REQUISITION_LINE_NUMBER,
PRDA.DISTRIBUTION_NUMBER REQUISITION_DISTRIBUTION_NUMBER,
PLA.LINE_NUM PO_LINE_NUMBER,
PDA.DISTRIBUTION_NUM PO_DISTRIBUTION_NUMBER,
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
-- QUANTITY AND PRICE INFO
ROUND(PRLA.QUANTITY, 2) REQUISITION_QUANTITY,
PDA.QUANTITY_ORDERED PO_QUANTITY,
-- PO FLAGS
PDA.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT,
PLLA.SHIPMENT_TYPE,
PLLA.CANCEL_FLAG,
PDA.DESTINATION_TYPE_CODE DESTINATION_TYPE,
-- BLANKET-SPECIFIC INFO
DECODE(PDA.DISTRIBUTION_TYPE, 'AGREEMENT', 'BLANKET', NULL) AGREEMENT_TYPE,
-- ACCOUNTING INFO
(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||
SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6
FROM GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID ) PO_CODE_COMBINATION,
(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||
SEGMENT4||'.'||SEGMENT5 ||'.'||SEGMENT6
FROM GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID = PRDA.CODE_COMBINATION_ID ) REQ_CODE_COMBINATION,
-- CREATION DATES
PRHA.CREATION_DATE REQ_CREATION_DATE,
PHA.CREATION_DATE PO_CREATION_DATE,
-- CURRENCY AND PRICING
PRLA.CURRENCY_CODE CURRENCY,
NVL(PDA.RATE, 1) CURRENCY_RATE,
PRLA.UNIT_PRICE REQ_UNIT_PRICE,
PLLA.PRICE_OVERRIDE PO_UNIT_PRICE,
-- ENCUMBRANCE VALUES
PDA.AMOUNT_TO_ENCUMBER,
ROUND(
NVL(PDA.QUANTITY_ORDERED, 0)
* NVL(PLLA.PRICE_OVERRIDE, 0)
* NVL(PDA.RATE, 1)
- NVL(PDA.UNENCUMBERED_AMOUNT, 0),
2
) ENCUMBERED_AMOUNT
FROM
POR_REQUISITION_HEADERS_ALL PRHA,
POR_REQUISITION_LINES_ALL PRLA,
POR_REQ_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
POZ_SUPPLIERS_V PSV
WHERE
PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PDA.PO_LINE_ID = PLA.PO_LINE_ID
AND PDA.LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND (
PRHA.REQUISITION_NUMBER IS NOT NULL
OR
(
PHA.SEGMENT1 IS NOT NULL
AND (
NVL(PDA.ENCUMBERED_FLAG, 'N') = 'Y'
OR (
NVL(PDA.ENCUMBERED_FLAG, 'N') = 'N'
AND NVL(PLLA.CANCEL_FLAG, 'N') = 'Y'
)
)
AND NVL(PDA.PREVENT_ENCUMBRANCE_FLAG, 'N') = 'N'
)
OR
(
PDA.DISTRIBUTION_TYPE = 'AGREEMENT'
AND NVL(PHA.ENCUMBRANCE_REQUIRED_FLAG, 'N') = 'Y'
AND NVL(PHA.CANCEL_FLAG, 'N') = 'N'
AND NVL(PDA.ENCUMBERED_FLAG, 'N') = 'Y'
AND NVL(PDA.PREVENT_ENCUMBRANCE_FLAG, 'N') = 'N'
AND ROUND(NVL(PDA.AMOUNT_TO_ENCUMBER, 0) * NVL(PDA.RATE, 1), 2) - NVL(PDA.UNENCUMBERED_AMOUNT, 0) > 0
)
)
ORDER BY
DOCUMENT_TYPE,
DOCUMENT_NUMBER,
PO_LINE_NUMBER,
PO_DISTRIBUTION_NUMBER;