PA Revise Project Budget To Consumption

SELECT
	HRO.NAME Project_Organization,
	PROJ.Segment1 Project_Number,
	BT.Budget_Type Budget_Type,
	(SELECT 
	SUM(BAL.BUDGET_PERIOD_TO_DATE)
	FROM PA_BC_BALANCES BAL, PA_Implementations IMP
	WHERE BAL.budget_version_id = PBV2.budget_version_id
	AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID
	AND IMP.org_id = PROJ.org_id
	AND BAL.Start_Date IN 
		(SELECT GPS.Start_Date
		FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB
		WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND GPS.APPLICATION_ID = 101
		AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR 
								FROM GL_Period_Statuses GPS2
								WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
								AND GPS2.APPLICATION_ID = 101
								AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD
								AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
								)
		AND GPS.Period_Type = SOB.Accounted_Period_Type						
		AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
		)
	GROUP BY BAL.budget_version_id) Original_Budget_Amount,
	(SELECT 
	SUM(DECODE(BAL.BALANCE_TYPE,'REQ',BAL.ENCUMB_PERIOD_TO_DATE,0))	
	FROM PA_BC_BALANCES BAL, PA_Implementations IMP
	WHERE BAL.budget_version_id = PBV2.budget_version_id
	AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID
	AND IMP.org_id = PROJ.org_id
	AND BAL.Start_Date IN 
		(SELECT GPS.Start_Date
		FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB
		WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND GPS.APPLICATION_ID = 101
		AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR 
								FROM GL_Period_Statuses GPS2
								WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
								AND GPS2.APPLICATION_ID = 101
								AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD
								AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
								)
		AND GPS.Period_Type = SOB.Accounted_Period_Type						
		AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
		)
	GROUP BY BAL.budget_version_id) Commitments,
	(SELECT 
	SUM(DECODE(BAL.BALANCE_TYPE,'PO',BAL.ENCUMB_PERIOD_TO_DATE,0))	
	FROM PA_BC_BALANCES BAL, PA_Implementations IMP
	WHERE BAL.budget_version_id = PBV2.budget_version_id
	AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID
	AND IMP.org_id = PROJ.org_id
	AND BAL.Start_Date IN 
		(SELECT GPS.Start_Date
		FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB
		WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND GPS.APPLICATION_ID = 101
		AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR 
								FROM GL_Period_Statuses GPS2
								WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
								AND GPS2.APPLICATION_ID = 101
								AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD
								AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
								)
		AND GPS.Period_Type = SOB.Accounted_Period_Type						
		AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
		)
	GROUP BY BAL.budget_version_id) Obligations,
	(SELECT 
	SUM(DECODE(BAL.BALANCE_TYPE,'AP',BAL.ENCUMB_PERIOD_TO_DATE,0))	
	FROM PA_BC_BALANCES BAL, PA_Implementations IMP
	WHERE BAL.budget_version_id = PBV2.budget_version_id
	AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID
	AND IMP.org_id = PROJ.org_id
	AND BAL.Start_Date IN 
		(SELECT GPS.Start_Date
		FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB
		WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND GPS.APPLICATION_ID = 101
		AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR 
								FROM GL_Period_Statuses GPS2
								WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
								AND GPS2.APPLICATION_ID = 101
								AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD
								AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
								)
		AND GPS.Period_Type = SOB.Accounted_Period_Type						
		AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
		)
	GROUP BY BAL.budget_version_id) Invoices,
	(SELECT 
	SUM(DECODE(BAL.BALANCE_TYPE,'EXP',BAL.ACTUAL_PERIOD_TO_DATE,0))	
	FROM PA_BC_BALANCES BAL, PA_Implementations IMP
	WHERE BAL.budget_version_id = PBV2.budget_version_id
	AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID
	AND IMP.org_id = PROJ.org_id
	AND BAL.Start_Date IN 
		(SELECT GPS.Start_Date
		FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB
		WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND GPS.APPLICATION_ID = 101
		AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR 
								FROM GL_Period_Statuses GPS2
								WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
								AND GPS2.APPLICATION_ID = 101
								AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD
								AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
								)
		AND GPS.Period_Type = SOB.Accounted_Period_Type						
		AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
		)
	GROUP BY BAL.budget_version_id) Expenditures,
	NVL(BCO.REVISE_BUDGET_AMOUNT,0) Unconsumed_Funds,
	((SELECT 
	SUM(BAL.BUDGET_PERIOD_TO_DATE)
	FROM PA_BC_BALANCES BAL, PA_Implementations IMP
	WHERE BAL.budget_version_id = PBV2.budget_version_id
	AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID
	AND IMP.org_id = PROJ.org_id
	AND BAL.Start_Date IN 
		(SELECT GPS.Start_Date
		FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB
		WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
		AND GPS.APPLICATION_ID = 101
		AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR 
								FROM GL_Period_Statuses GPS2
								WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID
								AND GPS2.APPLICATION_ID = 101
								AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD
								AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
								)
		AND GPS.Period_Type = SOB.Accounted_Period_Type						
		AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y'
		)
	GROUP BY BAL.budget_version_id) - NVL(BCO.REVISE_BUDGET_AMOUNT,0)) Revised_Budget_Amount,	
	CASE
		WHEN PBV1.WF_STATUS_CODE IS NOT NULL 
			THEN (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='WF_STATUS_CODE' AND LOOKUP_CODE = PBV1.WF_STATUS_CODE)
		WHEN PBV1.WF_STATUS_CODE IS NULL AND PBV1.LAST_UPDATE_DATE > PBV2.CREATION_DATE 
			THEN (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='BUDGET STATUS' AND LOOKUP_CODE = PBV1.BUDGET_STATUS_CODE)
		WHEN PBV1.WF_STATUS_CODE IS NULL AND PBV1.LAST_UPDATE_DATE <= PBV2.CREATION_DATE 
			THEN (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='BUDGET STATUS' AND LOOKUP_CODE = PBV2.BUDGET_STATUS_CODE)
		ELSE (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='BUDGET STATUS' AND LOOKUP_CODE = PBV1.BUDGET_STATUS_CODE)
	END Budget_Status_Post_Revision
FROM
  PA_Projects_All		PROJ,
  PA_Budget_Types		BT,
  PA_Budgetary_Control_Options	BCO,
  PA_BUDGET_VERSIONS PBV1,
  PA_BUDGET_VERSIONS PBV2,
  HR_ALL_ORGANIZATION_UNITS HRO
WHERE BCO.REVISE_BUDGET_FLAG   = 'S'
AND BCO.REVISE_BUDGET_REQUEST_ID = :P_Conc_Request_ID
AND PROJ.Carrying_Out_Organization_ID = NVL(:P_Organization_ID, PROJ.Carrying_Out_Organization_ID) 
AND PROJ.Carrying_Out_Organization_ID = HRO.ORGANIZATION_ID 
AND BCO.Project_ID		 = PROJ.Project_ID
AND BCO.Budget_Type_Code = BT.Budget_Type_Code
AND PBV1.PROJECT_ID = PROJ.Project_ID
AND PBV1.Budget_Type_Code = BCO.Budget_Type_Code
AND PBV1.BUDGET_STATUS_CODE IN ('S', 'W')
AND PBV2.PROJECT_ID = PROJ.Project_ID
AND PBV2.Budget_Type_Code = BCO.Budget_Type_Code
AND PBV2.BUDGET_STATUS_CODE = 'B'
AND PBV2.CURRENT_FLAG = 'Y'
order by 1,2