PO Cancelled Requisitions (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Purchase Order (PO-Fusion-SQL's)
1

This PO Cancel Requisition Report query displays details of cancelled requisitions by requisition type, line, item, and requester, helping procurement teams track cancellation reasons and responsible users.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- Displays cancelled requisition details with reasons for cancellation.
  HAOU.NAME 				OPERATING_UNIT_NAME,
  -- Requisition Info
  PRHA.REQUISITION_NUMBER,
  PRHA.DESCRIPTION 			REQUISITION_DESCRIPTION,
  PDTAT.TYPE_NAME 			REQUISITION_TYPE,
  PRHA.CREATION_DATE 		REQUISITION_CREATION_DATE,
  PRHA.APPROVED_DATE 		REQUISITION_APPROVED_DATE,
  PRHA.SUBMISSION_DATE 		REQUISITION_SUBMISSION_DATE,
  PPNF1.FULL_NAME           REQUESTER,
  -- Line/Item Info
  PRLA.LINE_NUMBER,
  PRLA.ITEM_REVISION,
  PRLA.ITEM_DESCRIPTION 	DESCRIPTION,
  PRLA.UOM_CODE 			UOM,
  PRLA.SOURCE_DOCUMENT_TYPE,
  -- Action History / Cancellation Details
  PAH.NOTE CANCEL_REASON_NOTE,
  PAH.ACTION_DATE 			CANCEL_DATE,
  PAH.ACTION_CODE,
  PAH.SEQUENCE_NUM,
  PPNF2.FULL_NAME  			CANCELLED_BY,
  PAH.OBJECT_REVISION_NUM,
  PAH.OBJECT_SUB_TYPE_CODE,
  -- Amount Info
  PRLA.QUANTITY,
  PRLA.UNIT_PRICE,
  PRLA.CURRENCY_CODE 		CURRENCY,
  PRLA.AMOUNT 				REQUISITION_LINE_AMOUNT
FROM
  POR_REQUISITION_HEADERS_ALL	PRHA,
  POR_REQUISITION_LINES_ALL 	PRLA,
  PO_ACTION_HISTORY 			PAH,
  PER_ALL_PEOPLE_F 				PAPF1,
  PER_ALL_PEOPLE_F 				PAPF2,
  PER_PERSON_NAMES_F            PPNF1,
  PER_PERSON_NAMES_F            PPNF2,
  HR_ALL_ORGANIZATION_UNITS 	HAOU,
  FINANCIALS_SYSTEM_PARAMS_ALL 	FSPA,
  PO_DOCUMENT_TYPES_ALL_TL 		PDTAT
WHERE
  1 = 1
  AND PRHA.REQUISITION_HEADER_ID 	= PRLA.REQUISITION_HEADER_ID
  AND PAH.OBJECT_ID 				= PRHA.REQUISITION_HEADER_ID
  AND PAH.OBJECT_TYPE_CODE 			= 'REQ'
  AND PAH.ACTION_CODE 				= 'CANCEL'
  AND PAPF1.PERSON_ID 				= PRHA.PREPARER_ID
  AND PAPF2.PERSON_ID 				= PAH.PERFORMER_ID
  AND PAPF1.PERSON_ID               = PPNF1.PERSON_ID AND PPNF1.NAME_TYPE = 'GLOBAL'
  AND PAPF2.PERSON_ID               = PPNF2.PERSON_ID AND PPNF2.NAME_TYPE = 'GLOBAL'
  AND HAOU.ORGANIZATION_ID 			= PRHA.REQ_BU_ID
  AND HAOU.ORGANIZATION_ID 			= FSPA.ORG_ID
  AND PDTAT.DOCUMENT_SUBTYPE 		= PRLA.SOURCE_DOCUMENT_TYPE
  AND PDTAT.LANGUAGE 				= 'US'
  AND PDTAT.PRC_BU_ID 				= PRHA.REQ_BU_ID
  AND PAPF1.PERSON_NUMBER IS NOT NULL
  AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE 
  AND PAPF2.PERSON_NUMBER IS NOT NULL
  AND SYSDATE BETWEEN PAPF2.EFFECTIVE_START_DATE AND PAPF2.EFFECTIVE_END_DATE;