CREATE MATERIALIZED VIEW EGCT_PROJECT_KPI2016_MV
BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND
AS
(
SELECT DISTINCT
	PROJECT_ID
	, PROJECT_NAME
	, ASSIGNMENT_ID
	, PROJECT_CATEGORY
	, STAGE_CODE
	, PROPSC_STAGE
	, PROJECT_TYPE
	, ACTIVE
	, OBS_ID
	, CU
	, REGION_ID
	, REGION
	, COUNTRY_ID
	, COUNTRY
	, EGCT_LEAD_BUS_LINE
	, EGCT_PRJ_KPI_MONTH
	, EGCT_PRJ_KPI_YEAR
	, BUDGET_CCY
	, MANAGER_ID
	, ERI_PSP
	, ERI_CFR
	, ERI_LINE_MANAGER
	, ERI_PMO_MANAGER
	, ERI_PO_MANAGER
	, CPM
	, PSP
	, SPONSOR
	, LINE_MANAGER
	, PROGRAM_MANAGER
	, POM
	, ORDERS_BOOKED_USD
	, ACTUAL
	, PLAN
	, TG2_FULFILLMENT
	, 100*DECODE(TG2_FULFILLMENT,2,1,TG2_FULFILLMENT) AS TG2_FULFILLMENT_PCT
	, DECODE(TG2_FULFILLMENT, 1, 0,2,0, ORDERS_BOOKED_USD) AS TG2_OB_NOT_PSD_PRJ	
	, STATUS_REP_L5WEEKS
	, 100*(DECODE(STATUS_REP_L5WEEKS, 0, 0, 1)) AS SR_FULFILLMENT
	, DECODE(STATUS_REP_L5WEEKS, 0, ORDERS_BOOKED_USD, 0) AS SR_OB_NOT_PSD_PRJ
	, (PRJ_BUDGET_MGTMNT*100) PRJ_BUDGET_MGTMNT
	, CASE WHEN PRJ_BUDGET_MGTMNT IS NULL OR PRJ_BUDGET_MGTMNT >= 0.8 THEN 1 ELSE 0 END AS PRJ_BGT_PSD_PRJ
	, CASE 
		WHEN PRJ_BUDGET_MGTMNT IS NULL THEN 2		
		WHEN PRJ_BUDGET_MGTMNT >= 0.8 THEN 1 
		ELSE 0 
		END AS PRJ_BGT_PSD_STATUS
	, CASE WHEN PRJ_BUDGET_MGTMNT IS NULL OR PRJ_BUDGET_MGTMNT >= 0.8 THEN 0 ELSE ORDERS_BOOKED_USD END AS PRJ_BGT_OB_NOT_PSD_PRJ
	, CASE
		WHEN PRJ_BUDGET_MGTMNT IS NOT NULL AND PRJ_BUDGET_MGTMNT >= 0.9 THEN 1
		WHEN PRJ_BUDGET_MGTMNT IS NOT NULL AND PRJ_BUDGET_MGTMNT >= 0.8 AND PRJ_BUDGET_MGTMNT < 0.9 THEN 2
		WHEN PRJ_BUDGET_MGTMNT IS NOT NULL AND PRJ_BUDGET_MGTMNT >= 0.7 AND PRJ_BUDGET_MGTMNT < 0.8 THEN 3
		WHEN PRJ_BUDGET_MGTMNT IS NOT NULL AND PRJ_BUDGET_MGTMNT < 0.7 THEN 4
		ELSE 0
		END AS PBM_IND
	, PROPSC_COMPLIANCE
	, COMPLIANCE_PCT
	, DECODE(PROPSC_COMPLIANCE, 0, 0, 1) AS PROPSC_PSD_PRJ
	, DECODE(PROPSC_COMPLIANCE, 0, ORDERS_BOOKED_USD, 0) AS PROPSC_OB_NOT_PSD_PRJ
	, CASE
		WHEN COMPLIANCE_PCT IS NULL THEN 0
		WHEN COMPLIANCE_PCT >= 90 THEN 1
		WHEN COMPLIANCE_PCT >= 80 AND COMPLIANCE_PCT < 90 THEN 2
		WHEN COMPLIANCE_PCT >= 70 AND COMPLIANCE_PCT < 80 THEN 3
		WHEN COMPLIANCE_PCT < 70 THEN 4
		ELSE 0
		END AS PC_IND
	, CPL_GENERATION
	, 100*DECODE(CPL_GENERATION,2,1,CPL_GENERATION) CPL_FULFILMENT_PCT
	, DECODE(CPL_GENERATION,0,0,1) AS CPL_GEN_PSD_PRJ
	, DECODE(CPL_GENERATION,0,ORDERS_BOOKED_USD,0) AS CPL_GEN_OB_NOT_PSD_PRJ
	, CASE 
		WHEN COMPLIANCE_PCT IS NULL AND PRJ_BUDGET_MGTMNT IS NULL
		THEN (DECODE(TG2_FULFILLMENT,0,0,1) + DECODE(STATUS_REP_L5WEEKS, 0, 0, 1) + DECODE(CPL_GENERATION, 0, 0, 1))/3
		WHEN COMPLIANCE_PCT IS NULL AND PRJ_BUDGET_MGTMNT IS NOT NULL
		THEN (DECODE(TG2_FULFILLMENT,0,0,1) + DECODE(STATUS_REP_L5WEEKS, 0, 0, 1) + DECODE(CPL_GENERATION, 0, 0, 1) + PRJ_BUDGET_MGTMNT)/4
		WHEN COMPLIANCE_PCT IS NOT NULL AND PRJ_BUDGET_MGTMNT IS NULL
		THEN (DECODE(TG2_FULFILLMENT,0,0,1) + DECODE(STATUS_REP_L5WEEKS, 0, 0, 1) + DECODE(CPL_GENERATION, 0, 0, 1) + (COMPLIANCE_PCT/100))/4
		ELSE (DECODE(TG2_FULFILLMENT,0,0,1) + DECODE(STATUS_REP_L5WEEKS, 0, 0, 1) + DECODE(CPL_GENERATION, 0, 0, 1) + PRJ_BUDGET_MGTMNT + (COMPLIANCE_PCT/100))/5
		END AS GOV_MATURITY
	, CASE 
		WHEN 	(TG2_FULFILLMENT = 1 OR TG2_FULFILLMENT = 2)
		AND		(STATUS_REP_L5WEEKS = 1 OR STATUS_REP_L5WEEKS = 2)
		AND		(PROPSC_COMPLIANCE = 1 OR PROPSC_COMPLIANCE = 2)
		AND		(PRJ_BUDGET_MGTMNT IS NULL OR PRJ_BUDGET_MGTMNT >= 0.8)
		AND		(CPL_GENERATION = 1 OR CPL_GENERATION = 2)
		THEN	1
		ELSE 	0
		END AS PASSED_PROJECTS
	, CASE 
		WHEN 	(TG2_FULFILLMENT = 1 OR TG2_FULFILLMENT = 2)
		AND		(STATUS_REP_L5WEEKS = 1 OR STATUS_REP_L5WEEKS = 2)
		AND		(PROPSC_COMPLIANCE = 1 OR PROPSC_COMPLIANCE = 2)
		AND		(PRJ_BUDGET_MGTMNT IS NULL OR PRJ_BUDGET_MGTMNT >= 0.8)
		AND		(CPL_GENERATION = 1 OR CPL_GENERATION = 2)
		THEN 	0
		ELSE 	1
		END AS NOT_PASSED_PROJECTS
	, CASE 
		WHEN 	(TG2_FULFILLMENT = 1 OR TG2_FULFILLMENT = 2)
		AND		(STATUS_REP_L5WEEKS = 1 OR STATUS_REP_L5WEEKS = 2)
		AND		(PROPSC_COMPLIANCE = 1 OR PROPSC_COMPLIANCE = 2)
		AND		(PRJ_BUDGET_MGTMNT IS NULL OR PRJ_BUDGET_MGTMNT >= 0.8)
		AND		(CPL_GENERATION = 1 OR CPL_GENERATION = 2)
		THEN	0
		ELSE 	ORDERS_BOOKED_USD
		END AS ORDERS_BOOKED_IF_NOT_PASSED
	, CASE 
		WHEN 	(TG2_FULFILLMENT = 1 OR TG2_FULFILLMENT = 2)
		AND		(STATUS_REP_L5WEEKS = 1 OR STATUS_REP_L5WEEKS = 2)
		AND		(PROPSC_COMPLIANCE = 1 OR PROPSC_COMPLIANCE = 2)
		AND		(PRJ_BUDGET_MGTMNT IS NULL OR PRJ_BUDGET_MGTMNT >= 0.8)
		AND		(CPL_GENERATION = 1 OR CPL_GENERATION = 2)
		THEN	ORDERS_BOOKED_USD
		ELSE 	0
		END AS ORDERS_BOOKED_IF_PASSED
	, TG5
	, MS6
	, CASE TG5 + MS6 WHEN 6 THEN 100
		WHEN 5 THEN 75
		WHEN 4 THEN 50
		WHEN 3 THEN 25
		WHEN 2 THEN 0
		ELSE NULL
		END AS SCHEDULE_MANAGEMENT
	, DECODE((TG5 + MS6),6,0,1) SCH_MGMT_NOT_PSD
	, SERVICE_REVENUE
	, HW_SW_REVENUE
	, SERVICE_COST
	, HW_SW_COST
	, RISK_EXPOSURE
	, CASE SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE
		WHEN 0 THEN 100
		WHEN 10 THEN 100
		WHEN 9 THEN 80
		WHEN 8 THEN 60
		WHEN 7 THEN 40
		WHEN 6 THEN 20
		WHEN 5 THEN 0
		END AS FIN_PLAN_MANAGEMENT
	, DECODE((SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE),0,0,10,0,1) FIN_MGMT_NOT_PSD
	, CASE WHEN (SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE) = 10
				OR (SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE) = 0
				THEN BILL_AD
				ELSE
				0 END AS BILLING_ADHERENCE
	, CASE WHEN ((SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE) = 0
			OR (SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE) = 10)
			AND BILL_AD >= 100
			THEN 0
			ELSE 1
			END BILL_AD_NOT_PSD
	, CASE WHEN ((SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE) = 0
			OR (SERVICE_REVENUE + HW_SW_REVENUE + SERVICE_COST + HW_SW_COST + RISK_EXPOSURE) = 10)
			AND BILL_AD >= 100
			THEN 1
			ELSE 0
			END TARGET_LEVEL_BILL_AD
	, ERI_TG2_COMPLETE AS TG2_COMPLETE	
FROM
(
	SELECT
		INV.ID AS PROJECT_ID
		, INV.NAME AS PROJECT_NAME
		, PRJ.ERI_PROJ_NUMBER AS ASSIGNMENT_ID
		, PRJ.ERI_PROJ_CATEGORY AS PROJECT_CATEGORY
		, INV.STAGE_CODE AS STAGE_CODE
        , STG_LKP.NAME AS PROPSC_STAGE
		, CMN.NAME AS PROJECT_TYPE
		, OBS_UNIT_ID.ID AS OBS_ID
		, CU.NAME AS CU
		, REGION.ID AS REGION_ID
		, REGION.NAME AS REGION
		, COUNTRY.ID AS COUNTRY_ID
		, COUNTRY.NAME AS COUNTRY
		, MY.EGCT_PRJ_KPI_MONTH
		, MY.EGCT_PRJ_KPI_YEAR
		, FIN.BUDGET_CCY
		, INV.MANAGER_ID
		, PRJ.ERI_PSP
		, PRJ.ERI_CFR
		, PRJ.ERI_LINE_MANAGER
		, PRJ.ERI_PMO_MANAGER
		, PRJ.ERI_PO_MANAGER
		, CPM.FULL_NAME AS CPM
		, PSP.FULL_NAME AS PSP
		, SPONSOR.FULL_NAME AS SPONSOR
		, LM.FULL_NAME AS LINE_MANAGER
		, PMO.FULL_NAME AS PROGRAM_MANAGER
		, POM.FULL_NAME AS POM
		, INV.IS_ACTIVE ACTIVE
		, NVL(FIN.C_ORDBKD, 0) AS ORDERS_BOOKED_USD
		, NVL(ACTUAL.ACTUAL,0) AS ACTUAL
		, NVL(MAX_P.PL, 0) AS PLAN
		, CASE WHEN INV.IS_ACTIVE = 0
			   THEN NULL
			   WHEN PRJ.ERI_TG5_ACT_DATE IS NOT NULL AND PRJ.ERI_MS6_ACT_DATE IS NULL
			   THEN 3
			   WHEN PRJ.ERI_TG2_ACT_DATE IS NULL OR PRJ.ERI_MS6_ACT_DATE IS NOT NULL
			   THEN 0
			   WHEN TG5_TSK.TG5 IS NULL OR TG5_BAS.TG5 IS NULL
			   THEN 1
			   WHEN TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD') ---t<=p<=b done
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_TSK.TG5,'YYYY-MM-DD')
					AND TO_CHAR(TG5_TSK.TG5,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD')
			   THEN 3
			   WHEN TO_CHAR(SYSDATE,'YYYY-MM-DD') > TO_CHAR(TG5_TSK.TG5,'YYYY-MM-DD')---t>p/b done
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') > TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD')
			   THEN 1
			   WHEN --TO_DATE(TO_CHAR(TG5_TASK.TG5,'YYYY-MM-DD'),'YYYY-MM-DD') > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')---P>B>=T done
					TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD')
					AND TO_CHAR(TG5_TSK.TG5,'YYYY-MM-DD') > TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD')
			   THEN 2
			   WHEN --TO_DATE(TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD'),'YYYY-MM-DD') > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') --B>=T>P done
					 TO_CHAR(TG5_TSK.TG5,'YYYY-MM-DD') < TO_CHAR(SYSDATE,'YYYY-MM-DD')
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD')
			   THEN 2
			   WHEN TO_CHAR(TG5_TSK.TG5,'YYYY-MM-DD') >= TO_CHAR(SYSDATE,'YYYY-MM-DD') --P>=T>B done
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') > TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD')
				THEN 1
		  END AS TG5
		  , CASE  WHEN INV.IS_ACTIVE = 0
			    THEN NULL
				WHEN PRJ.ERI_TG2_ACT_DATE IS NULL OR PRJ.ERI_MS6_ACT_DATE IS NOT NULL
				THEN 0
				WHEN MS6_TSK.MS6 IS NULL OR TG5_BAS.MS6 IS NULL
			    THEN 1
				WHEN TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD') ---t<=p<=b
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(MS6_TSK.MS6,'YYYY-MM-DD')
					AND TO_CHAR(MS6_TSK.MS6,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD')
			    THEN 3
			    WHEN TO_CHAR(SYSDATE,'YYYY-MM-DD') > TO_CHAR(MS6_TSK.MS6,'YYYY-MM-DD')---t>p/b
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') > TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD')
			    THEN 1
			    WHEN --TO_DATE(TO_CHAR(TG5_TASK.TG5,'YYYY-MM-DD'),'YYYY-MM-DD') > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')---P>B>=T
					TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD')
					AND TO_CHAR(MS6_TSK.MS6,'YYYY-MM-DD') > TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD')
			    THEN 2
			    WHEN --TO_DATE(TO_CHAR(TG5_BAS.TG5,'YYYY-MM-DD'),'YYYY-MM-DD') > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') --B>=T>P
					 TO_CHAR(MS6_TSK.MS6,'YYYY-MM-DD') < TO_CHAR(SYSDATE,'YYYY-MM-DD')
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') <= TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD')
			    THEN 2
			    WHEN TO_CHAR(MS6_TSK.MS6,'YYYY-MM-DD') >= TO_CHAR(SYSDATE,'YYYY-MM-DD') --P>=T>B
					AND TO_CHAR(SYSDATE,'YYYY-MM-DD') > TO_CHAR(TG5_BAS.MS6,'YYYY-MM-DD')
				THEN 1
			END AS MS6
		, CASE  WHEN INV.STAGE_CODE = 'PROPS_C_CREATED' OR INV.STAGE_CODE = 'PROPS_C_TG1_APRVD'
				THEN 0
				WHEN NVL(TG5_BAS.SERV_REV,0)/DECODE(PLAN.PLAN_REV,NULL,1,0,1,PLAN.PLAN_REV) >= 0.98 AND NVL(TG5_BAS.SERV_REV,0)/DECODE(PLAN.PLAN_REV,NULL,1,0,1,PLAN.PLAN_REV) <= 1.02 
				THEN 2
				ELSE 1
				END AS SERVICE_REVENUE
		, CASE  WHEN INV.STAGE_CODE = 'PROPS_C_CREATED' OR INV.STAGE_CODE = 'PROPS_C_TG1_APRVD'
				THEN 0
				WHEN NVL(TG5_BAS.HW_SW_REV,0)/DECODE(PLAN.PLAN_REV_HS,NULL,1,0,1,PLAN.PLAN_REV_HS) >= 0.98 AND NVL(TG5_BAS.HW_SW_REV,0)/DECODE(PLAN.PLAN_REV_HS,NULL,1,0,1,PLAN.PLAN_REV_HS) <= 1.02 
				THEN 2
				ELSE 1
				END AS HW_SW_REVENUE
		, CASE  WHEN INV.STAGE_CODE = 'PROPS_C_CREATED' OR INV.STAGE_CODE = 'PROPS_C_TG1_APRVD'
				THEN 0
				WHEN NVL(TG5_BAS.SERV_COS,0)/DECODE(PLAN.PLAN_SERV,NULL,1,0,1,PLAN.PLAN_SERV) >= 0.98 AND NVL(TG5_BAS.SERV_COS,0)/DECODE(PLAN.PLAN_SERV,NULL,1,0,1,PLAN.PLAN_SERV) <= 1.02 
				THEN 2
				ELSE 1
				END AS SERVICE_COST
		, CASE  WHEN INV.STAGE_CODE = 'PROPS_C_CREATED' OR INV.STAGE_CODE = 'PROPS_C_TG1_APRVD'
				THEN 0
				WHEN NVL(TG5_BAS.HW_SW_COS,0)/DECODE(PLAN.PLAN_HWSW,NULL,1,0,1,PLAN.PLAN_HWSW) >= 0.98 AND NVL(TG5_BAS.HW_SW_COS,0)/DECODE(PLAN.PLAN_HWSW,NULL,1,0,1,PLAN.PLAN_HWSW) <= 1.02 
				THEN 2
				ELSE 1
				END AS HW_SW_COST
		, CASE  WHEN INV.STAGE_CODE = 'PROPS_C_CREATED' OR INV.STAGE_CODE = 'PROPS_C_TG1_APRVD'
				THEN 0
				WHEN NVL(TG5_BAS.RSK_RES_BGT,0)/DECODE(PLAN.RSK_EXP,NULL,1,0,1,PLAN.RSK_EXP) >= 0.98 AND NVL(TG5_BAS.RSK_RES_BGT,0)/DECODE(PLAN.RSK_EXP,NULL,1,0,1,PLAN.RSK_EXP) <= 1.02 
				THEN 2
				ELSE 1
				END AS RISK_EXPOSURE
		, CASE 	WHEN INV.IS_ACTIVE = 0
				THEN NULL
				WHEN 
								
				PRJ.ERIC_TG2_APPROVED = 1
			   THEN 1
			   WHEN
					((
						NVL(FIN.BUDGET_CCY,'N/A') = 'USD' 
						AND 
						NVL(
							ROUND(
									(
										(FIN.C_ACT_CST + FIN.C_COMM_CST)/
										(CASE 
											WHEN NVL(FIN.C_BUDGET,0)+NVL(PSF_CSC.TOT_PSF_CSC,0) = 0 THEN 1
											ELSE NVL(FIN.C_BUDGET,0)+NVL(PSF_CSC.TOT_PSF_CSC,0)
										END)
									),2
								)
							,0) 
						< 0.20
					)
					OR
					(
					NVL(FIN.BUDGET_CCY, 'N/A') != 'USD'
					AND 
					NVL
					(ROUND((
						(FIN.P_ACT_CST + FIN.P_COMM_CST)/
						(CASE WHEN NVL(FIN.P_BUDGET,0)+NVL(PSF_CSC.TOT_PSF_CSC,0) = 0 
						THEN 1 ELSE NVL(FIN.P_BUDGET,0)+NVL(PSF_CSC.TOT_PSF_CSC,0) END)
						),2),0) 
					< 0.20)		
				) 
				THEN 2
		ELSE 0 END AS
					TG2_FULFILLMENT
		, CASE 
			WHEN INV.IS_ACTIVE = 0
			THEN NULL
			WHEN PRJ.ERIC_TG2_APPROVED = 0 OR PRJ.ERI_TG5_COMPLETE = 1 THEN 2
			WHEN STATUS_REP.REP_CREATED_DATE IS NOT NULL THEN 1
			ELSE 0 END AS STATUS_REP_L5WEEKS
			, CASE 
				WHEN (PRJ.ERIC_TG2_APPROVED = 0 AND PRJ.ERI_TG2_COMPLETE = 0)
				THEN NULL
				WHEN TG5_BAS.HW_SW_REV != 0 AND TG5_BAS.HW_SW_COS != 0
				THEN NULL
				WHEN FIN.BUDGET_CCY IS NULL OR NVL(FIN.C_PRJ_BUD, 0) < 0
				THEN 0
				WHEN FIN.BUDGET_CCY = 'USD' AND NVL(FIN.C_PLAN_CST, 0) < 0
				THEN 0
				WHEN FIN.BUDGET_CCY != 'USD' AND NVL(FIN.P_PLAN_CST, 0) < 0
				THEN 0
				WHEN FIN.BUDGET_CCY = 'USD' AND FIN.C_PRJ_BUD >= FIN.C_PLAN_CST
				THEN 1
				WHEN FIN.BUDGET_CCY != 'USD' AND FIN.C_PRJ_BUD >= FIN.P_PLAN_CST
				THEN 1
				WHEN FIN.BUDGET_CCY = 'USD' AND FIN.C_PRJ_BUD < FIN.C_PLAN_CST
				THEN (FIN.C_PRJ_BUD/FIN.C_PLAN_CST)
				WHEN FIN.BUDGET_CCY != 'USD' AND FIN.C_PRJ_BUD < FIN.P_PLAN_CST
				THEN (FIN.C_PRJ_BUD/FIN.P_PLAN_CST)
			END AS PRJ_BUDGET_MGTMNT
			, CASE WHEN INV.IS_ACTIVE = 0 THEN NULL ELSE PROPSC_COMPLIANCE.COMPLIANCE_PCT END COMPLIANCE_PCT
		, CASE 
			WHEN PROPSC_COMPLIANCE.COMPLIANCE_PCT IS NULL THEN 2
			WHEN PROPSC_COMPLIANCE.COMPLIANCE_PCT >= 80 THEN 1
			ELSE 0 END AS PROPSC_COMPLIANCE
		,PRJ.EGCT_LEAD_BUS_LINE
		,CASE 
			WHEN INV.IS_ACTIVE = 0 
			THEN NULL
			WHEN PRJ.ERIC_TG2_APPROVED = 0 OR PRJ.ERI_TG5_COMPLETE = 1 
			THEN 2
			WHEN PRJ.EGCT_LEAD_BUS_LINE = 'MS' OR PRJ.EGCT_LEAD_BUS_LINE = 'BMS' OR (PRJ.EGCT_LEAD_BUS_LINE = 'CMS' AND PRJ.ERI_PROJ_CATEGORY = 'A')
					OR (PRJ.EGCT_LEAD_BUS_LINE = 'PRS' AND PRJ.ERI_PROJ_CATEGORY = 'A') OR (PRJ.EGCT_LEAD_BUS_LINE = 'PRS' AND PRJ.ERI_PROJ_CATEGORY = 'B')
			THEN 2
			WHEN PRJ.ERIC_TG2_APPROVED = 1 AND PRJ.ERI_TG2_ACT_DATE < '01-JUL-2015'
			THEN 2
			WHEN FIN_PLAN.CNT > 0 AND OPR_PLAN.CNT > 0
			THEN 1
			ELSE 0 
			END CPL_GENERATION
		, CASE 	WHEN MAX_P.PL IS NULL AND ACTUAL.ACTUAL IS NULL
				THEN 100
				WHEN MAX_P.PL > 0 AND ACTUAL.ACTUAL IS NULL
				THEN 0
				WHEN MAX_P.PL IS NULL AND ACTUAL.ACTUAL >= 0 
				THEN 100
				WHEN MAX_P.PL > 0 AND ACTUAL.ACTUAL = 0
				THEN 0
				WHEN MAX_P.PL > 0 AND ACTUAL.ACTUAL > 0
				THEN ROUND((ACTUAL.ACTUAL/MAX_P.PL)*100,0)
			END AS BILL_AD	
		, PRJ.ERI_TG2_COMPLETE
		FROM			INV_INVESTMENTS 			INV
		INNER JOIN 		INV_PROJECTS 				INVPRJ 			ON 	INV.ID = INVPRJ.PRID 
																	AND INV.ODF_OBJECT_CODE = 'project' 
																	AND INVPRJ.IS_TEMPLATE = 0 
																	AND INVPRJ.IS_PROGRAM = 0
		INNER JOIN 		ODF_CA_PROJECT 				PRJ 			ON 	PRJ.ID = INV.ID 
																	--AND INV.ID = 5411087
		INNER JOIN 		ODF_CA_PROJFINPROPERTIES 	FIN 			ON 	INV.ID = FIN.ID
		INNER JOIN 		(WITH MONTH_YEAR AS
										(
										SELECT 
										EXTRACT(MONTH FROM SYSDATE) ERI_MONTH
										,EXTRACT(YEAR FROM SYSDATE) ERI_YEAR
										, MAX_MONTH
										, MAX_YEAR
										FROM (SELECT MAX(EGCT_PRJ_KPI_MONTH) MAX_MONTH, MAX(EGCT_PRJ_KPI_YEAR) MAX_YEAR FROM ODF_CA_EGCT_PRJ_KPI_2016) MO_YR
										)
										SELECT 
										CASE
										WHEN ERI_YEAR = 2017 AND ERI_MONTH > 1 
										THEN 1
										WHEN ERI_YEAR = 2017 AND ERI_MONTH = 1
										THEN 12
										WHEN MAX_MONTH IS NULL AND ERI_MONTH = 1
										THEN 1
										WHEN MAX_MONTH IS NULL
										THEN ERI_MONTH - 1
										WHEN MAX_MONTH = ERI_MONTH - 1
										THEN ERI_MONTH
										WHEN MAX_MONTH = ERI_MONTH
										THEN ERI_MONTH
										ELSE ERI_MONTH - 1
										END AS EGCT_PRJ_KPI_MONTH
										,CASE
										WHEN ERI_YEAR = 2017 AND ERI_MONTH > 1
										THEN 2017
										ELSE 2016
										END AS EGCT_PRJ_KPI_YEAR
										FROM MONTH_YEAR
										) MY ON 1 = 1
		INNER JOIN
				(
				SELECT 	DISTINCT 
					ASSOCIN.RECORD_ID
					, REG_UNT.ID
					, REG_UNT.NAME
					, REG_UNT.DEPTH
					, MAX(REG_UNT.DEPTH) OVER (PARTITION BY ASSOCIN.RECORD_ID) AS MAX_DEPTH

				FROM		PRJ_OBS_ASSOCIATIONS 	ASSOCIN
				INNER JOIN	PRJ_OBS_UNITS_FLAT 		REG_FLAT	ON	REG_FLAT.UNIT_ID = ASSOCIN.UNIT_ID
																AND	ASSOCIN.TABLE_NAME = 'SRM_PROJECTS'
				INNER JOIN	PRJ_OBS_UNITS 			REG_UNT		ON	REG_UNT.TYPE_ID IN
																			(SELECT ID FROM PRJ_OBS_TYPES WHERE UNIQUE_NAME IN ('EGCT_PRJ_CRG'))
																AND	REG_UNT.ID = REG_FLAT.BRANCH_UNIT_ID				
				)									OBS_UNIT_ID		ON	OBS_UNIT_ID.RECORD_ID = INV.ID
																	AND	OBS_UNIT_ID.DEPTH = OBS_UNIT_ID.MAX_DEPTH
		LEFT OUTER JOIN CMN_LOOKUPS_V 				STG_LKP 		ON	STG_LKP.LANGUAGE_CODE = 'en'
																	AND STG_LKP.LOOKUP_TYPE = 'INV_STAGE_TYPE'
																	AND STG_LKP.LOOKUP_CODE = INV.STAGE_CODE
		LEFT OUTER JOIN	CMN_LOOKUPS_V				CMN				ON	CMN.LANGUAGE_CODE = 'en' 
																	AND CMN.LOOKUP_TYPE = 'EGCT_PROJECT_TYPE' 
																	AND CMN.LOOKUP_CODE = PRJ.EGCT_PROJECT_TYPE
		LEFT OUTER JOIN (SELECT 
								ERI_TG5_FIN_DATE TG5,
								SERV_REV,HW_SW_REV,
								SERV_COS,HW_SW_COS,
								RSK_RES_BGT,
								ERI_PROJ_DELIV_DATE MS6,
								PRJ.PROJECT_ID 
								FROM ODF_CA_BASELINE ODF,(SELECT MAX(ID) ID,PROJECT_ID FROM PRJ_BASELINES WHERE IS_CURRENT = 1 GROUP BY PROJECT_ID)PRJ 
								WHERE PRJ.ID = ODF.ID)TG5_BAS ON TG5_BAS.PROJECT_ID = INV.ID
		LEFT OUTER JOIN (SELECT 
								PRFINISH TG5,
								PRPROJECTID 
								FROM PRTASK WHERE PREXTERNALID = 'tg_5_ms')TG5_TSK ON TG5_TSK.PRPROJECTID = INV.ID
		LEFT OUTER JOIN (SELECT 
								PRFINISH MS6,
								PRPROJECTID 
								FROM PRTASK WHERE PREXTERNALID = 'ms_6_rvw')MS6_TSK ON MS6_TSK.PRPROJECTID = INV.ID
		LEFT OUTER JOIN (SELECT 
								P1.ODF_PARENT_ID,
								P1.PLAN_TOT_REVENUE_SER PLAN_REV,
								P1.PLAN_TOT_REVENUE_HS PLAN_REV_HS,
								P1.PLAN_TOT_SERVICES PLAN_SERV,
								P1.PLAN_TOT_HWSW  PLAN_HWSW,
								P1.ERI_PLAN_TOT_RR RSK_EXP 
								FROM ODF_CA_ERC_OTC_PLANS P1
								INNER JOIN (SELECT MAX(ID) ID,ODF_PARENT_ID FROM ODF_CA_ERC_OTC_PLANS WHERE SET_AS_BASELINE = 1 GROUP BY ODF_PARENT_ID)P2 ON P1.ODF_PARENT_ID = P2.ODF_PARENT_ID AND P1.ID = P2.ID )PLAN ON PLAN.ODF_PARENT_ID = INV.ID
		LEFT OUTER JOIN (SELECT 
								COUNT(1) CNT,
								ODF_PARENT_ID FIN_ID,
								REPORTING_PERIOD
								FROM ODF_CA_ERC_OTC_PLANS 
								WHERE ERI_CPL_READY = 1 
								AND REPORTING_PERIOD IS NOT NULL 
								GROUP BY ODF_PARENT_ID,REPORTING_PERIOD)FIN_PLAN ON FIN_PLAN.FIN_ID = INV.ID 
								AND FIN_PLAN.REPORTING_PERIOD = MY.EGCT_PRJ_KPI_MONTH || '-' || MY.EGCT_PRJ_KPI_YEAR
		LEFT OUTER JOIN (SELECT 
								COUNT(1) CNT,
								ODF_PARENT_ID OPR_ID,
								REPORTING_PERIOD
								FROM ODF_CA_ERI_OPR_PLANS 
								WHERE ERI_CPL_READY = 1 
								AND REPORTING_PERIOD IS NOT NULL 
								GROUP BY ODF_PARENT_ID,REPORTING_PERIOD)OPR_PLAN ON OPR_PLAN.OPR_ID = INV.ID
								AND OPR_PLAN.REPORTING_PERIOD = MY.EGCT_PRJ_KPI_MONTH || '-' || MY.EGCT_PRJ_KPI_YEAR
		LEFT OUTER JOIN (
						SELECT 	P.ODF_PARENT_ID, MAX(P.ID) AS MAX_ID,SUM(SL.SLICE) PL
						FROM 	ODF_CA_ERC_OTC_PLANS P 
						INNER JOIN (SELECT ODF_PARENT_ID,ID FROM ODF_CA_ERC_OTC_PLAN_DETAILS WHERE DETAIL_TYPE = 'planned_revenue_serv' OR DETAIL_TYPE = 'planned_revenue_hs')DET ON P.ID = DET.ODF_PARENT_ID
						INNER JOIN (SELECT PRJ_OBJECT_ID,SLICE FROM ERC_BLOB_SLICES WHERE SLICE_DATE >= '01-JAN-2016' AND SLICE_DATE <= SYSDATE)SL 
						ON SL.PRJ_OBJECT_ID = DET.ID
						WHERE 	P.IS_READ_WRITE = 0 
						AND 	P.SET_AS_BASELINE = 1
						GROUP BY	P.ODF_PARENT_ID
						) MAX_P	ON MAX_P.ODF_PARENT_ID = INV.ID
		INNER JOIN
				(
				SELECT 	DISTINCT 
					ASSOCIN.RECORD_ID
					, REG_UNT.ID
					, REG_UNT.NAME

				FROM		PRJ_OBS_ASSOCIATIONS 	ASSOCIN
				INNER JOIN	PRJ_OBS_UNITS_FLAT 		REG_FLAT	ON	REG_FLAT.UNIT_ID = ASSOCIN.UNIT_ID
																AND	ASSOCIN.TABLE_NAME = 'SRM_PROJECTS'
				INNER JOIN	PRJ_OBS_UNITS 			REG_UNT		ON	REG_UNT.TYPE_ID IN
																			(SELECT ID FROM PRJ_OBS_TYPES WHERE UNIQUE_NAME IN ('EGCT_PRJ_CRG'))
																AND	REG_UNT.ID = REG_FLAT.BRANCH_UNIT_ID
																AND	REG_UNT.DEPTH = 2
				)									REGION			ON	REGION.RECORD_ID = INV.ID AND (REGION.NAME = 'RASO' OR REGION.NAME = 'RNEA' OR REGION.NAME = 'RECA' OR REGION.NAME = 'RINA' OR REGION.NAME = 'RLAM' OR REGION.NAME = 'RMEA' OR REGION.NAME = 'RMED' OR REGION.NAME = 'RNAM' OR REGION.NAME = 'RSSA' OR REGION.NAME = 'RWCE')
		LEFT OUTER JOIN
				(
				SELECT 	DISTINCT 
					ASSOCIN.RECORD_ID
					, REG_UNT.ID
					, REG_UNT.NAME
				FROM		PRJ_OBS_ASSOCIATIONS 	ASSOCIN
				INNER JOIN	PRJ_OBS_UNITS_FLAT 		REG_FLAT	ON	REG_FLAT.UNIT_ID = ASSOCIN.UNIT_ID
																AND	ASSOCIN.TABLE_NAME = 'SRM_PROJECTS'
				INNER JOIN	PRJ_OBS_UNITS 			REG_UNT		ON	REG_UNT.TYPE_ID IN

													(SELECT ID FROM PRJ_OBS_TYPES WHERE UNIQUE_NAME IN ('EGCT_PRJ_CRG'))
																AND	REG_UNT.ID = REG_FLAT.BRANCH_UNIT_ID
																AND	REG_UNT.DEPTH = 3
				)									CU				ON	CU.RECORD_ID = INV.ID
		LEFT OUTER JOIN
				(
				SELECT 	DISTINCT 
					ASSOCIN.RECORD_ID
					, REG_UNT.ID
					, REG_UNT.NAME
				FROM		PRJ_OBS_ASSOCIATIONS 	ASSOCIN
				INNER JOIN	PRJ_OBS_UNITS_FLAT 		REG_FLAT	ON	REG_FLAT.UNIT_ID = ASSOCIN.UNIT_ID
																AND	ASSOCIN.TABLE_NAME = 'SRM_PROJECTS'
				INNER JOIN	PRJ_OBS_UNITS 			REG_UNT		ON	REG_UNT.TYPE_ID IN
																			(SELECT ID FROM PRJ_OBS_TYPES WHERE UNIQUE_NAME IN ('EGCT_LEGAL_CMPNY'))
																AND	REG_UNT.ID = REG_FLAT.BRANCH_UNIT_ID
																AND	REG_UNT.DEPTH = 3
				)									COUNTRY		ON	COUNTRY.RECORD_ID = INV.ID
				
				
				
		LEFT OUTER JOIN	SRM_RESOURCES				CPM			ON	CPM.USER_ID = INV.MANAGER_ID
		LEFT OUTER JOIN	SRM_RESOURCES				PSP			ON	PSP.USER_ID = PRJ.ERI_PSP
		LEFT OUTER JOIN	SRM_RESOURCES				SPONSOR		ON	SPONSOR.ID = PRJ.ERI_CFR
		LEFT OUTER JOIN	SRM_RESOURCES				LM			ON	LM.ID = PRJ.ERI_LINE_MANAGER
		LEFT OUTER JOIN	SRM_RESOURCES				PMO			ON	PMO.ID = PRJ.ERI_PMO_MANAGER
		LEFT OUTER JOIN	SRM_RESOURCES				POM			ON	POM.ID = PRJ.ERI_PO_MANAGER
		LEFT OUTER JOIN 
					(SELECT
							FD.ODF_PARENT_ID
							, SUM(NVL(BI.C_ACT_BILL, 0)) AS ACTUAL
						FROM		(
										SELECT 	ID, ODF_PARENT_ID, NVL(FD_STATUS, 0) FD_STATUS
										FROM 	ODF_CA_SAP_FD
									) 	FD
						INNER JOIN	ODF_CA_SAP_BI 	BI	ON 	FD.ID = BI.ODF_PARENT_ID 
														AND	BI.ACTUAL_DATE IS NOT NULL
														AND BI.ACTUAL_DATE >=
															'01-JAN-2016'
														AND	NVL(BI.BMS_PAST_DUE, 0) = 0
						WHERE	NVL(BI.C_ACT_BILL, 0) > 0
						GROUP BY FD.ODF_PARENT_ID
					)	ACTUAL	ON	ACTUAL.ODF_PARENT_ID = INV.ID 
		LEFT OUTER JOIN 
						(	
							SELECT DISTINCT ODF_PARENT_ID, SUM(BUDGET_INP) TOT_PSF_CSC
							FROM ODF_CA_SAP_FD WHERE ROW_TYPE IN ('PSF', 'CSC') GROUP BY ODF_PARENT_ID
						) PSF_CSC
							ON INV.ID = PSF_CSC.ODF_PARENT_ID
		LEFT OUTER JOIN 
						(	
							SELECT DISTINCT ODF_PARENT_ID, CREATED_BY
							, MAX(CREATED_DATE) OVER (PARTITION BY ODF_PARENT_ID, CREATED_BY) AS REP_CREATED_DATE
							FROM ODF_CA_CATSPRJSTATUSREP WHERE CREATED_DATE >= SYSDATE - 35
						) STATUS_REP ON STATUS_REP.ODF_PARENT_ID = INV.ID AND INV.MANAGER_ID = STATUS_REP.CREATED_BY
		LEFT OUTER JOIN EGCT_CPM_AUDIT_V PROPSC_COMPLIANCE ON PROPSC_COMPLIANCE.PROJECT_ID = INV.ID
		WHERE (INV.IS_ACTIVE = 1 OR (PRJ.ERI_TG2_COMPLETE = 1 AND INV.IS_ACTIVE = 0 AND MAX_P.PL != 0) OR (PRJ.ERI_TG2_COMPLETE = 1 AND INV.IS_ACTIVE = 0 AND ACTUAL.ACTUAL != 0))
)
);

Error at Command Line : 207 Column : 20
Error report -
SQL Error: ORA-12801: error signaled in parallel query server P004
ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.