This PO Purchase Order Detail Report query displays total amounts, quantities, and status of standard purchase orders by supplier, site, and buyer as of today, helping users track procurement performance and ensure accurate financial and logistical planning.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--PO details with amounts, status, supplier, and logistics for financial and procurement tracking.
HAOU.NAME OPERATING_UNIT_NAME,
-- Carrier Org Code
IOP.ORGANIZATION_CODE CARRIER_ORGANIZATION_CODE,
-- PO Header Info
PHA.SEGMENT1 PO_NUMBER,
DECODE (
PHA.TYPE_LOOKUP_CODE,
'BLANKET', 'Blanket',
'BUYER AUCTION', 'Buyer Auction',
'CONTRACT', 'Contract',
'OFFER TO BUY', 'Offer to Buy',
'PLANNED', 'Planned',
'REQUISITION', 'Requisition',
'SCHEDULED', 'Scheduled',
'SOURCING RFI', 'Sourcing RFI',
'SOURCING RFQ', 'Sourcing RFQ',
'STANDARD', 'Standard',
NULL
) PURCHASE_ORDER_TYPE,
PHA.CREATION_DATE CREATED_DATE,
PHA.APPROVED_DATE APPROVAL_DATE,
PHA.CLOSED_DATE CLOSED_DATE,
PHA.START_DATE START_EFFECTIVE_DATE,
PHA.END_DATE END_EFFECTIVE_DATE,
PHA.REVISION_NUM REVISION_NUMBER,
PHA.REVISED_DATE REVISED_DATE,
-- PO Status
CASE
WHEN NVL(PHA.CANCEL_FLAG, 'N') = 'Y' THEN 'Cancelled'
WHEN NVL(PHA.FROZEN_FLAG, 'N') = 'Y' THEN 'Frozen'
WHEN NVL(PHA.APPROVED_FLAG, 'N') = 'Y' THEN 'Approved'
ELSE 'In Progress'
END PO_STATUS,
DECODE (PHA.CANCEL_FLAG, 'N', 'No', 'Y', 'Yes', NULL) CANCEL_FLAG,
DECODE (PHA.FIRM_STATUS_LOOKUP_CODE, 'N', 'No', 'Y', 'Yes', NULL) FIRM_STATUS,
DECODE (PHA.FROZEN_FLAG, 'N', 'No', 'Y', 'Yes', NULL) FROZEN_FLAG,
-- Supplier Info
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE,
PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUMBER,
-- Buyer Info
PPNF.FULL_NAME BUYER,
PPF.PERSON_NUMBER BUYER_EMPLOYEE_NUMBER,
-- Location Info
HLS.LOCATION_CODE SHIP_TO_LOCATION,
HLB.LOCATION_CODE BILL_TO_LOCATION,
-- Currency & Payment Terms
PHA.CURRENCY_CODE CURRENCY_CODE,
PHA.RATE_TYPE CURRENCY_CONVERSION_RATE_TYPE,
AT.NAME PAYMENT_TERMS_NAME,
-- Financial Summary
PHA.BLANKET_TOTAL_AMOUNT AGREED_AMOUNT,
PHA.AMOUNT_LIMIT AMOUNT_LIMIT,
PHA.MIN_RELEASE_AMOUNT MINIMUM_RELEASE_AMOUNT,
-- Quantity & Amounts
PO_AMT.TOTAL_AMT_BASE,
PO_AMT.QUANTITY_ORDERED,
PO_AMT.QUANTITY_CANCELLED,
PO_AMT.QUANTITY_DELIVERED,
PO_AMT.QUANTITY_BILLED,
PO_AMT.QUANTITY_ORDERED - (
PO_AMT.QUANTITY_CANCELLED + PO_AMT.QUANTITY_DELIVERED
) QUANTITY_DUE,
PO_AMT.AMOUNT_BILLED_AMT,
-- Freight Terms
DECODE (
PHA.FREIGHT_TERMS_LOOKUP_CODE,
'AIR', 'AIR',
'Allowed', 'Allowed',
'COD', 'COD',
'COLL', 'COLL',
'Due', 'Due',
'FEDERAL EXPRESS', 'FEDERAL EXPRESS',
'FEDERAL OVERNIGHT', 'FEDERAL OVERNIGHT',
'NONE', 'NONE',
'OPM', 'OPM',
'PPD', 'PPD',
'Paid', 'Paid',
'Prepaid', 'Prepaid',
'RLWY', 'RLWY',
'SHIP', 'SHIP',
'TBD', 'TBD',
NULL
) FREIGHT_TERM,
-- FOB Terms
DECODE (
PHA.FOB_LOOKUP_CODE,
'BUY', 'Buyer',
'CIF', 'Cost, Insurance and Freight',
'CUSTOMER SITE', 'Customer Site',
'DDP', 'Delivered, Duty Paid',
'DDU', 'Delivered, Duty Unpaid',
'DEST', 'DEST',
'Destination', 'Destination',
'EXW', 'Ex-Works',
'FACTORY', 'Factory',
'FOB', 'Free on Board',
'LOAD', 'Loading Dock',
'NONE', 'NONE',
'Origin', 'Origin',
'SEL', 'Seller',
'SHIP', 'SHIP',
'SHIP POINT', 'Shipping Point',
'XXX', 'Other, needs to be specified',
NULL
) FREE_ON_BOARD_POINT,
-- Acceptance & Pay On
DECODE (PHA.ACCEPTANCE_REQUIRED_FLAG, 'N', 'No', 'Y', 'Yes', NULL) ACCEPTANCE_REQUIRED_FLAG,
DECODE (PHA.PAY_ON_CODE,
'RECEIPT', 'Receipt',
'RECEIPT_AND_USE', 'Receipt and Use',
'USE', 'Use',
NULL
) PAY_ON
FROM
PO_HEADERS_ALL PHA,
HR_ALL_ORGANIZATION_UNITS HAOU,
AP_TERMS AT,
FND_CURRENCIES_VL FCV,
GL_DAILY_CONVERSION_TYPES GDCT,
PER_PEOPLE_F PPF,
PER_PERSON_NAMES_F PPNF,
HR_LOCATIONS HLS,
HR_LOCATIONS HLB,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
PO_SYSTEM_PARAMETERS_ALL PSPA,
INV_ORG_PARAMETERS IOP,
-- Inline View: PO Amounts Aggregation
(
SELECT
PDA.PO_HEADER_ID,
NVL(SUM(
DECODE(
PDA.QUANTITY_ORDERED,
NULL, (NVL(PDA.AMOUNT_ORDERED, 0) - NVL(PDA.AMOUNT_CANCELLED, 0)),
((NVL(PDA.QUANTITY_ORDERED, 0) - NVL(PDA.QUANTITY_CANCELLED, 0)) * NVL(PLLA.PRICE_OVERRIDE, 0))
) * NVL(PDA.RATE, 1)
), 0) TOTAL_AMT_FUNC,
NVL(SUM(
DECODE(
PDA.QUANTITY_ORDERED,
NULL, (NVL(PDA.AMOUNT_ORDERED, 0) - NVL(PDA.AMOUNT_CANCELLED, 0)),
((NVL(PDA.QUANTITY_ORDERED, 0) - NVL(PDA.QUANTITY_CANCELLED, 0)) * NVL(PLLA.PRICE_OVERRIDE, 0))
)
), 0) TOTAL_AMT_BASE,
SUM(NVL(PDA.QUANTITY_BILLED, 0)) QUANTITY_BILLED,
SUM(NVL(PDA.QUANTITY_CANCELLED, 0)) QUANTITY_CANCELLED,
SUM(NVL(PDA.QUANTITY_DELIVERED, 0)) QUANTITY_DELIVERED,
SUM(NVL(PDA.QUANTITY_ORDERED, 0)) QUANTITY_ORDERED,
SUM(NVL(PDA.AMOUNT_BILLED, 0)) AMOUNT_BILLED_AMT,
SUM(NVL(PDA.AMOUNT_CANCELLED, 0)) AMOUNT_CANCELLED_AMT,
SUM(NVL(PDA.AMOUNT_DELIVERED, 0)) AMOUNT_DELIVERED_AMT,
SUM(NVL(PDA.AMOUNT_ORDERED, 0)) AMOUNT_ORDERED_AMT,
SUM(NVL(PDA.AMOUNT_BILLED, 0) * NVL(PDA.RATE, 1)) AMOUNT_BILLED_AMT_FUNC,
SUM(NVL(PDA.AMOUNT_CANCELLED, 0) * NVL(PDA.RATE, 1)) AMOUNT_CANCELLED_AMT_FUNC,
SUM(NVL(PDA.AMOUNT_DELIVERED, 0) * NVL(PDA.RATE, 1)) AMOUNT_DELIVERED_AMT_FUNC,
SUM(NVL(PDA.AMOUNT_ORDERED, 0) * NVL(PDA.RATE, 1)) AMOUNT_ORDERED_AMT_FUNC
FROM
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA
WHERE
PLLA.SHIPMENT_TYPE = 'STANDARD'
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
GROUP BY
PDA.PO_HEADER_ID
) PO_AMT
WHERE
PHA.PRC_BU_ID = FSPA.ORG_ID
AND PSPA.PRC_BU_ID = FSPA.ORG_ID
AND PHA.PRC_BU_ID = HAOU.ORGANIZATION_ID
AND PHA.TERMS_ID = AT.TERM_ID
AND PO_AMT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.CURRENCY_CODE = FCV.CURRENCY_CODE
AND PHA.RATE_TYPE = GDCT.CONVERSION_TYPE
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PPNF.PERSON_ID = PPF.PERSON_ID
AND PPF.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PPF.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND PHA.BILL_TO_LOCATION_ID = HLB.LOCATION_ID
AND PHA.SHIP_TO_LOCATION_ID = HLS.LOCATION_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND IOP.BUSINESS_UNIT_ID = PHA.PRC_BU_ID
AND PSPA.LANGUAGE_CODE = 'US'
AND PPNF.NAME_TYPE = 'GLOBAL';