This PO purchase order distributions headers report displays account distribution details for purchase orders, helping users to monitor and analyze financial allocations across purchase orders for better budget control and audit compliance.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Displays purchase order account distributions to aid financial monitoring and audit.
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE,
PHA.SEGMENT1 PO_NUMBER,
TRUNC(PHA.CREATION_DATE) PO_CREATION_DATE,
PHA.APPROVED_DATE,
PLA.LINE_NUM PO_LINE_NUMBER,
PDA.DISTRIBUTION_NUM DISTRIBUTION_NUMBER, PHA.CURRENCY_CODE CURRENCY,
PDA.QUANTITY_ORDERED,
PLA.UNIT_PRICE,
PDA.TAX_EXCLUSIVE_AMOUNT,
-- GL CODE COMBINATION SEGMENTS
NVL(GCC.SEGMENT1, '0') || '.' ||
NVL(GCC.SEGMENT2, '0') || '.' ||
NVL(GCC.SEGMENT3, '0') || '.' ||
NVL(GCC.SEGMENT4, '0') GL_ACCOUNT
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
GL_CODE_COMBINATIONS GCC,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PER_ALL_PEOPLE_F PAPF
WHERE
PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PDA.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND PHA.AGENT_ID = PAPF.PERSON_ID
AND PLLA.SCHEDULE_STATUS IN ('OPEN', 'ON HOLD', 'INCOMPLETE')
AND NVL(PLLA.CANCEL_FLAG, 'N') <> 'Y'
AND NVL(PLLA.DROP_SHIP_FLAG, 'N') <> 'Y'
AND PHA.FUNDS_STATUS = 'PASSED'
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', PDA.AMOUNT_ORDERED - NVL(PDA.AMOUNT_CANCELLED, 0),
'FIXED PRICE', PDA.AMOUNT_ORDERED - NVL(PDA.AMOUNT_CANCELLED, 0),
PDA.QUANTITY_ORDERED - NVL(PDA.QUANTITY_CANCELLED, 0)
) > 0
ORDER BY PSV.SEGMENT1 ,
PSSAM.VENDOR_SITE_CODE ,
PHA.SEGMENT1,
PLA.LINE_NUM,
PDA.DISTRIBUTION_NUM ;