This PO Open Purchase Orders Report By Cost Center query displays open purchase order details by cost center, including quantities, pricing, and supplier information, helping users to monitor outstanding orders and manage procurement efficiently.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Open purchase orders by cost center to track outstanding procurement and supplier details.
-- Supplier and PO header info
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PHA.SEGMENT1 PO_NUMBER,
-- Line/Item details
PLA.LINE_NUM LINE_NUMBER,
ESI.ITEM_NUMBER,
PLA.ITEM_REVISION,
PLA.ITEM_DESCRIPTION,
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
PLLA.PROMISED_DATE,
GCC.SEGMENT1 COMPANY,
GCC.SEGMENT2 COST_CENTER,
-- Locations
HLA1.LOCATION_CODE SHIP_TO_LOCATION,
HLA2.LOCATION_CODE BILL_TO_LOCATION,
-- Currency and pricing info
PHA.CURRENCY_CODE CURRENCY,
DECODE(
PLT.ORDER_TYPE_LOOKUP_CODE,
'AMOUNT', NULL,
PLLA.PRICE_OVERRIDE
) UNIT_PRICE,
-- Quantities and amounts ordered, billed, delivered
ROUND(SUM(DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', PDA.AMOUNT_ORDERED,
'FIXED PRICE', PDA.AMOUNT_ORDERED,
PDA.QUANTITY_ORDERED
)), 2) QUANTITY_AMOUNT_ORDERED,
ROUND(SUM(DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', NVL(PDA.AMOUNT_BILLED, 0),
'FIXED PRICE', NVL(PDA.AMOUNT_BILLED, 0),
NVL(PDA.QUANTITY_BILLED, 0)
)), 2) QUANTITY_AMOUNT_BILLED,
ROUND(SUM(DECODE(
PLA.ORDER_TYPE_LOOKUP_CODE,
'RATE', NVL(PDA.AMOUNT_DELIVERED, 0),
'FIXED PRICE', NVL(PDA.AMOUNT_DELIVERED, 0),
NVL(PDA.QUANTITY_DELIVERED, 0)
)), 2) QUANTITY_AMOUNT_DELIVERED,
-- Calculated % unbilled
(DECODE(
PLT.ORDER_TYPE_LOOKUP_CODE,
'RATE',
(SUM(NVL(PDA.AMOUNT_ORDERED, 0)) - SUM(NVL(PDA.AMOUNT_BILLED, 0))) /
DECODE(SUM(NVL(PDA.AMOUNT_ORDERED, 0)), 0, 1, SUM(NVL(PDA.AMOUNT_ORDERED, 0))),
'FIXED PRICE',
(SUM(NVL(PDA.AMOUNT_ORDERED, 0)) - SUM(NVL(PDA.AMOUNT_BILLED, 0))) /
DECODE(SUM(NVL(PDA.AMOUNT_ORDERED, 0)), 0, 1, SUM(NVL(PDA.AMOUNT_ORDERED, 0))),
(SUM(NVL(PDA.QUANTITY_ORDERED, 0)) - SUM(NVL(PDA.QUANTITY_BILLED, 0))) /
DECODE(SUM(NVL(PDA.QUANTITY_ORDERED, 0)), 0, 1, SUM(NVL(PDA.QUANTITY_ORDERED, 0)))
) * 100 || '%') PERCENT_UNBILLED
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
GL_CODE_COMBINATIONS GCC,
PO_LINE_TYPES PLT,
HR_LOCATIONS_ALL HLA1,
HR_LOCATIONS_ALL HLA2,
POZ_SUPPLIERS_V PSV,
PO_AGENTS_V PAV,
EGP_SYSTEM_ITEMS ESI
WHERE
PHA.PO_HEADER_ID = PLA.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 PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PHA.AGENT_ID = PAV.AGENT_ID
AND PHA.SHIP_TO_LOCATION_ID = HLA1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID = HLA2.LOCATION_ID
AND PLA.ITEM_ID = ESI.INVENTORY_ITEM_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND NVL(PHA.CANCEL_FLAG, 'N') = 'N'
AND NVL(PLA.CANCEL_FLAG, 'N') = 'N'
AND NVL(PLLA.CANCEL_FLAG, 'N')= 'N'
GROUP BY
PSV.SEGMENT1 ,
PSV.VENDOR_NAME ,
PHA.SEGMENT1 ,
PLA.LINE_NUM ,
ESI.ITEM_NUMBER,
PLA.ITEM_REVISION,
PLA.ITEM_DESCRIPTION,
PLLA.SHIPMENT_NUM ,
PLLA.PROMISED_DATE,
GCC.SEGMENT1,
GCC.SEGMENT2,
PHA.CURRENCY_CODE,
DECODE(
PLT.ORDER_TYPE_LOOKUP_CODE,
'AMOUNT', NULL,
PLLA.PRICE_OVERRIDE
),
HLA1.LOCATION_CODE,
HLA2.LOCATION_CODE,
PLT.ORDER_TYPE_LOOKUP_CODE
ORDER BY
PHA.SEGMENT1,
PLA.LINE_NUM,
PLLA.SHIPMENT_NUM,
GCC.SEGMENT1,
GCC.SEGMENT2;