| 123456789101112131415161718192021222324252627282930313233343536373839404142 |
- DROP MATERIALIZED VIEW D1_ACTIVITY_STAT_MV
- /
- CREATE MATERIALIZED VIEW D1_ACTIVITY_STAT_MV
- ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
- NOCOMPRESS LOGGING
- STORAGE( INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
- BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "MDMTS_01"
- PARALLEL
- BUILD DEFERRED
- USING INDEX
- REFRESH COMPLETE ON DEMAND
- USING DEFAULT LOCAL ROLLBACK SEGMENT
- USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
- AS select A.D1_ACTIVITY_ID, A.BUS_OBJ_CD,
- A.BO_STATUS_CD, S.BO_STATUS_COND_FLG, SO.BO_OPT_VAL D1_LOGICAL_STATUS,
- A.ACTIVITY_TYPE_CD, A.FIELD_TASK_TYPE,
- A.CRE_DTTM, A.START_DTTM, A.END_DTTM, A.STATUS_UPD_DTTM,
- MESSAGE_CAT_NBR, MESSAGE_NBR
- from (SELECT A.D1_ACTIVITY_ID,
- max(MESSAGE_CAT_NBR) KEEP (DENSE_RANK LAST ORDER BY SEQNO) MESSAGE_CAT_NBR,
- max(MESSAGE_NBR) KEEP (DENSE_RANK LAST ORDER BY SEQNO) MESSAGE_NBR
- FROM D1_ACTIVITY A
- left join D1_ACTIVITY_LOG L on L.D1_ACTIVITY_ID = A.D1_ACTIVITY_ID
- and L.LOG_ENTRY_TYPE_FLG = 'F1EX'
- where CRE_DTTM between SYSDATE - 365 and SYSDATE
- GROUP BY A.D1_ACTIVITY_ID order by D1_ACTIVITY_ID) L
- inner join D1_ACTIVITY A on A.D1_ACTIVITY_ID = L.D1_ACTIVITY_ID
- inner join F1_BUS_OBJ BO on BO.BUS_OBJ_CD = A.BUS_OBJ_CD
- inner join F1_BUS_OBJ_STATUS S on S.BUS_OBJ_CD = BO.LIFE_CYCLE_BO_CD
- and S.BO_STATUS_CD = A.BO_STATUS_CD
- left join F1_BUS_OBJ_STATUS_OPT SO on SO.BUS_OBJ_CD = S.BUS_OBJ_CD
- and SO.BO_STATUS_CD = S.BO_STATUS_CD
- and SO.BO_OPT_FLG = 'D1LS'
- where (S.BO_STATUS_COND_FLG != 'F1FL' or
- nvl(A.END_DTTM, A.STATUS_UPD_DTTM) between SYSDATE - 30 and SYSDATE)
- /
- CREATE unique index D1T432P0 on D1_ACTIVITY_STAT_MV(D1_ACTIVITY_ID)
- /
|
