This PO Cancelled Report query displays cancelled purchase orders with related vendor, buyer, cancellation details, and amounts, helping procurement teams track order cancellations and associated financial impact.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Displays cancelled purchase orders with vendor, and cancellation details.
--PO Details
PHA.SEGMENT1 PO_NUMBER,
PHA.CREATION_DATE PO_CREATION_DATE,
PHA.CANCEL_FLAG,
PLA.ITEM_DESCRIPTION,
-- Supplier Info
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE,
-- Cancellation Details
PAH.ACTION_DATE CANCELLED_DATE,
PAH.ACTION_CODE,
PAH.NOTE CANCELLATION_REASON,
PAH.SEQUENCE_NUM,
PAH.OBJECT_TYPE_CODE,
PAPF1.CREATED_BY CANCELLED_BY,
PAH.OBJECT_SUB_TYPE_CODE,
PHA.CURRENCY_CODE CURRENCY,
--Amount Infor
SUM(NVL (PLLA.QUANTITY, 0) * NVL (PLLA.PRICE_OVERRIDE, 0)) AMOUNT
FROM
PO_ACTION_HISTORY PAH,
PO_HEADERS_ALL PHA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PER_ALL_PEOPLE_F PAPF1
WHERE 1=1
AND PAH.OBJECT_ID = PHA.PO_HEADER_ID
AND PAH.OBJECT_TYPE_CODE = 'PO'
AND PAH.ACTION_CODE LIKE 'CANCEL%'
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PSV.VENDOR_ID = PHA.VENDOR_ID
AND PSSAM.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND PAPF1.PERSON_ID =PAH.PERFORMER_ID
AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
GROUP BY
PHA.SEGMENT1,
PHA.CREATION_DATE,
PHA.CANCEL_FLAG,
PLA.ITEM_DESCRIPTION,
PSV.VENDOR_NAME,
PSV.SEGMENT1,
PSSAM.VENDOR_SITE_CODE,
PAH.ACTION_DATE ,
PAH.ACTION_CODE,
PAH.SEQUENCE_NUM,
PAPF1.CREATED_BY,
PAH.OBJECT_TYPE_CODE,
PAH.OBJECT_SUB_TYPE_CODE,
PHA.CURRENCY_CODE ,
PAH.NOTE
ORDER BY
PHA.SEGMENT1 ;