PO Requisition Activity Register (Oracle Fusion SQL)

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

This PO Requisition Activity Register query retrieves summary data for requisitions, including the total amounts requested, the preparer who initiated the requisition, and the current status of the requisition document. It helps track the progress and financial scope of requisitions. This data is crucial for procurement oversight and reporting.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves requisition summary data including amounts, preparer, and document status.
  -- Requisition Header Information
  PRHA.REQUISITION_NUMBER 			REQUISITION_NUMBER,
  TRUNC(PRHA.CREATION_DATE) 		REQUISITION_CREATION_DATE,
  PRHA.DESCRIPTION 		,
  PRHA.DOCUMENT_STATUS 				STATUS,
  -- Requisition Lines Information
  PRL.ITEM_DESCRIPTION 				LINE_ITEM_DESCRIPTION,
  PRL.PRODUCT_TYPE,
  PRL.PRODUCT_CATEGORY,
  PRL.LINE_STATUS,  
  PRL.LINE_NUMBER,
  -- Requisition Distribution information
  PRL.CURRENCY_CODE CURRENCY,
  SUM(DECODE(
      PRL.ORDER_TYPE_LOOKUP_CODE,
      'FIXED PRICE', NVL(PRL.AMOUNT, 0),
      'RATE', NVL(PRL.AMOUNT, 0),
      NVL(PRL.QUANTITY, 0) * NVL(PRL.UNIT_PRICE, 0))) 			AMOUNT,
  SUM(NVL(PRL.AMOUNT,0)) 										LINE_AMOUNT,	
  PRDA.DISTRIBUTION_NUMBER,  
  SUM(NVL(PRDA.DISTRIBUTION_AMOUNT,0)) 							DISTRIBUTION_AMOUNT,
  SUM(NVL(PRDA.DISTRIBUTION_AMOUNT,0)) - SUM(NVL(PRL.AMOUNT,0)) REMAINING_DISTRIBUTION_AMOUNT,
  PRDA.FUNDS_STATUS,
  PRDA.BUDGET_DATE,
  -- Audit Infor
  PU.USERNAME PREPARER  
FROM
  POR_REQUISITION_HEADERS_ALL 	PRHA,
  POR_REQUISITION_LINES_ALL 	PRL,
  POR_REQ_DISTRIBUTIONS_ALL 	PRDA,
  PER_USERS 					PU,
  PER_ALL_PEOPLE_F 				PAPF
WHERE
  1=1
  AND PU.PERSON_ID 					= PAPF.PERSON_ID
  AND PAPF.PERSON_ID 				= PRHA.PREPARER_ID
  AND PRHA.DOCUMENT_STATUS 			<> 'SYSTEM_SAVED'
  AND PRL.REQUISITION_HEADER_ID 	= PRHA.REQUISITION_HEADER_ID
  AND NVL(PRL.CANCEL_FLAG, 'N') 	= 'N'
  AND PRDA.REQUISITION_LINE_ID 		= PRL.REQUISITION_LINE_ID
  AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
GROUP BY
 PRHA.REQUISITION_NUMBER 			,
  TRUNC(PRHA.CREATION_DATE) 		,
  PRHA.DESCRIPTION 		            ,
  PRHA.DOCUMENT_STATUS 				,
  -- Requisition Lines Information
  PRL.ITEM_DESCRIPTION 				,
  PRL.PRODUCT_TYPE                  ,
  PRL.PRODUCT_CATEGORY              ,
  PRL.LINE_STATUS                   ,  
  PRL.LINE_NUMBER                   ,
  -- Requisition Distribution information
  PRL.CURRENCY_CODE                 ,	
  PRDA.DISTRIBUTION_NUMBER          ,  
 PRDA.FUNDS_STATUS                  ,
  PRDA.BUDGET_DATE                  ,
  -- Audit Infor
  PU.USERNAME   
  ORDER BY PRHA.REQUISITION_NUMBER