CREATE MATERIALIZED VIEW STATUS_REP_MV
refresh
 with rowid
 fast
 on commit
   AS 
WITH ALLSR AS (
SELECT  
	V.PROJECT_ID
	,SR.REPORT_DATE STATUS_REPORT_DATE
	,SR.ID STATUS_REPORT_ID
	,SR.NAME
	,SR.CREATED_BY USER_CREATED_SR
	,SR.CREATED_DATE 
	,V.ROLE_ID
	,V.DEL_START
	,V.DEL_FINISH
	,V.SIGNUM
	,V.ROLE_TYPE
	,SR.ERI_EXEC_STAT_SUMRY
	,SR.EGCT_ST_STAT_NEW
	,SR.ERI_ACCOMPLISHMENTS
	,SR.ERI_NEXT_STEPS
	,SR.SCHEDULE_STATUS
	,SR.SCOPE_STATUS
	,SR.COST_EFFORT_STATUS
	,SR.EGCT_SR_QUALITY
	,SR.LAST_UPDATED_DATE
	,SR.SCOPE_CHANGE SCOPE_CHANGE_REQUIRED
	,SR.COST_EFFORT_EXTERNAL PRJ_AFF_BY_EXT_FAC
	,SR.ERI_KPI_SUM_RVW PRJ_GOV_KPI_SUMMARY_REVIEW
	,SR.SCOPE_OBJECTIVE PROJECT_OBJECTIVE_CHANGED
	,SR.SCHEDULE_MILESTONE NEXT_MILESTONE_ON_TRACK
	,SR.ERI_PROJ_PHASE PROJECT_PHASE
	,SR.PHASE CURRENT_PHASE
	,SR.ERI_STEER_DATE
	,SR.SCOPE_DELIVERABLE DELIVERABLE_SCOPE_CHANGED
	,SR.CODE SRID
	,SR.REPORT_UPDATE REPORT_UPDATE
	,SR.EGCT_ST_STAT_NEW OVERALL_STATUS
	,SR.ERI_CONFIRM_STATUS
	,SR.ERI_READY_CONFIRM ERI_READY_CONFIRM
	,SR.ERI_ACCOMPLISH_SH
	,SR.COST_EFF_EXPLAN_ADD
	,SR.EGCT_ESCALATION_SH
	,SR.ERI_EXEC_STAT_SUM_SH
	,SR.ERI_NEXT_STEPS_SH
	,SR.SCHEDULE_EXPLAN_SH
  --,MAX(SR.REPORT_DATE) OVER(PARTITION BY SR.CREATED_DATE ORDER BY SR.CREATED_DATE  DESC)
FROM 
	EGCT_PRJ_ROLES_VIEW V INNER JOIN ODF_CA_CATSPRJSTATUSREP SR ON (V.PROJECT_ID=SR.ODF_PARENT_ID 
	AND V.ROLE_ID=SR.CREATED_BY AND V.ROLE_TYPE IN ('CPM_DELEGATE','PREV_CPM','CPM')
  AND ( TRUNC(SR.CREATED_DATE) >= (
										CASE
										  WHEN V.ROLE_TYPE IN ('CPM_DELEGATE','PREV_CPM')
										  THEN TRUNC(V.DEL_START)
										  ELSE TRUNC(SR.CREATED_DATE)
										END
									)
			AND TRUNC(SR.CREATED_DATE) <= (
											CASE
											  WHEN V.ROLE_TYPE IN ('CPM_DELEGATE','PREV_CPM')
											  THEN TRUNC(V.DEL_FINISH)
											  ELSE TRUNC(SR.CREATED_DATE)
											END)
	)
  )
 --WHERE V.PROJECT_ID=5721143
)
, MAXRP AS
(
SELECT O.PROJECT_ID,MAX(O.STATUS_REPORT_ID) ID FROM ALLSR O WHERE O.STATUS_REPORT_DATE= (SELECT  MAX (I.STATUS_REPORT_DATE) FROM ALLSR I WHERE I.PROJECT_ID= O.PROJECT_ID)
GROUP BY O.PROJECT_ID)
SELECT 
B."PROJECT_ID",B."STATUS_REPORT_DATE",B."STATUS_REPORT_ID",B."NAME",B."USER_CREATED_SR",B."CREATED_DATE",B."ROLE_ID",B."DEL_START",B."DEL_FINISH",B."SIGNUM",B."ROLE_TYPE",B."ERI_EXEC_STAT_SUMRY",B."EGCT_ST_STAT_NEW",B."ERI_ACCOMPLISHMENTS",B."ERI_NEXT_STEPS",B."SCHEDULE_STATUS",B."SCOPE_STATUS",B."COST_EFFORT_STATUS",B."EGCT_SR_QUALITY",B."LAST_UPDATED_DATE",B."SCOPE_CHANGE_REQUIRED",B."PRJ_AFF_BY_EXT_FAC",B."PRJ_GOV_KPI_SUMMARY_REVIEW",B."PROJECT_OBJECTIVE_CHANGED",B."NEXT_MILESTONE_ON_TRACK",B."PROJECT_PHASE",B."CURRENT_PHASE",B."ERI_STEER_DATE",B."DELIVERABLE_SCOPE_CHANGED",B."SRID",B."REPORT_UPDATE",B."OVERALL_STATUS",B."ERI_CONFIRM_STATUS",B."ERI_READY_CONFIRM",B."ERI_ACCOMPLISH_SH",B."COST_EFF_EXPLAN_ADD",B."EGCT_ESCALATION_SH",B."ERI_EXEC_STAT_SUM_SH",B."ERI_NEXT_STEPS_SH",B."SCHEDULE_EXPLAN_SH" 
FROM ALLSR B JOIN MAXRP M ON (B.PROJECT_ID=M.PROJECT_ID AND  B.STATUS_REPORT_ID=M.ID); 

SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

create materialized view log on ODF_CA_CATSPRJSTATUSREP with rowid; 
create materialized view log on INV_INVESTMENTS with rowid; 
create materialized view log on INV_PROJECTS with rowid; 
create materialized view log on SRM_RESOURCES  with rowid; 
create materialized view log on ODF_CA_EGCT_DYN_RIGHT_AUDIT with rowid; 
create materialized view log on ODF_CA_ERI_PSC_FORM with rowid; 

BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
JOB_NAME            => 'REFRESH_MVIEW',
JOB_TYPE            => 'PLSQL_BLOCK',
JOB_ACTION          => 'BEGIN DBMS_MVIEW.REFRESH("STATUS_REP_MV",''F''); END;',
NUMBER_OF_ARGUMENTS => 0,
START_DATE          => SYSTIMESTAMP,
REPEAT_INTERVAL     => 'FREQ=DAILY; INTERVAL=1',
END_DATE            => NULL,
ENABLED             => TRUE,
AUTO_DROP           => FALSE,
COMMENTS            => 'JOB TO REFRESH'
);
END;

BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
JOB_NAME            => 'REFRESH_MVIEW',
JOB_TYPE            => 'PLSQL_BLOCK',
JOB_ACTION          => 'BEGIN DBMS_MVIEW.REFRESH("STATUS_REP_MV",''F''); END;',
NUMBER_OF_ARGUMENTS => 0,
REPEAT_INTERVAL     => 'FREQ=MINUTELY',
START_DATE          => SYSTIMESTAMP + interval '10' second,
END_DATE            => NULL,
ENABLED             => TRUE,
AUTO_DROP           => FALSE,
COMMENTS            => 'JOB TO REFRESH'
);
END;