This CE Purchase Orders Listing query displays open purchase order amounts and currency details for each purchasing business unit, helping users to track remaining commitments and manage procurement budgets effectively.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Lists open purchase orders with remaining amounts and currency details
PHA.SEGMENT1 PO_NUMBER,
PS.SEGMENT1 SUPPLIER_NUMBER,
HP.PARTY_NAME SUPPLIER_NAME,
DECODE (
PLLA.MATCHING_BASIS,
'AMOUNT',
(
GREATEST (
PDA.AMOUNT_ORDERED - NVL (PDA.AMOUNT_DELIVERED, 0) - NVL (PDA.AMOUNT_CANCELLED, 0) + NVL (PDA.NONRECOVERABLE_TAX, 0),
0
)
),
(
GREATEST (
PDA.QUANTITY_ORDERED - NVL (PDA.QUANTITY_BILLED, 0) - NVL (PDA.QUANTITY_CANCELLED, 0),
0
) * (NVL (PLLA.PRICE_OVERRIDE, 0)) + NVL (PDA.NONRECOVERABLE_TAX, 0)
)
) AMOUNT,
DECODE (
PLLA.MATCHING_BASIS,
'AMOUNT',
(
GREATEST (
PDA.AMOUNT_ORDERED - NVL (PDA.AMOUNT_DELIVERED, 0) - NVL (PDA.AMOUNT_CANCELLED, 0) + NVL (PDA.NONRECOVERABLE_TAX, 0),
0
) * NVL (
PDA.RATE,
NVL (PHA.RATE, 1)
)
),
(
GREATEST (
PDA.QUANTITY_ORDERED - NVL (PDA.QUANTITY_BILLED, 0) - NVL (PDA.QUANTITY_CANCELLED, 0),
0
) * (NVL (PLLA.PRICE_OVERRIDE, 0)) + NVL (PDA.NONRECOVERABLE_TAX, 0)
) * NVL (
PDA.RATE,
NVL (PHA.RATE, 1)
)
) BASE_AMOUNT,
NVL (
NVL (
PHA.CURRENCY_CODE,
FSPA.INVOICE_CURRENCY_CODE
),
GL.CURRENCY_CODE
) CURRENCY_CODE,
PLA.LINE_NUM LINE_NUMBER,
PLLA.VALUE_BASIS ORDER_TYPE_LOOKUP_CODE,
PLA.PURCHASE_BASIS PURCHASE_BASIS,
PDA.PO_DISTRIBUTION_ID REFERENCE_ID
FROM
PO_HEADERS_ALL PHA,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PO_DISTRIBUTIONS_ALL PDA,
GL_LEDGERS GL,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DOCUMENT_TYPES_ALL PDTA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND PHA.PRC_BU_ID = PDTA.PRC_BU_ID
AND PLA.PRC_BU_ID = PHA.PRC_BU_ID
AND PLLA.PRC_BU_ID = PLA.PRC_BU_ID
AND PDA.PRC_BU_ID = FSPA.ORG_ID
AND PSSAM.PRC_BU_ID = PHA.PRC_BU_ID
AND PS.PARTY_ID = HP.PARTY_ID
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID(+)
AND PHA.VENDOR_ID = PS.VENDOR_ID(+)
AND GL.LEDGER_ID = PDA.SET_OF_BOOKS_ID
AND GL.object_type_code = 'L'
AND NVL (GL.complete_flag, 'Y') = 'Y'
AND PDA.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID
AND PDA.LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'BLANKET', 'PLANNED', 'SCHEDULED')
AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND NVL (PHA.CANCEL_FLAG, 'N') = 'N'
AND NVL (PHA.FROZEN_FLAG, 'N') = 'N'
AND PHA.TYPE_LOOKUP_CODE = PDTA.DOCUMENT_SUBTYPE
AND PDTA.DOCUMENT_TYPE_CODE IN ('PO', 'PA')
AND NVL (PLA.PURCHASE_BASIS, 'GOODS') NOT IN ('TEMP LABOR')
AND HAOU.ORGANIZATION_ID = PDTA.PRC_BU_ID