CREATE TABLE RS_DW_ML_WK_001_0_01_SG PARALLEL(DEGREE 4 INSTANCES 1) NOLOGGING PCTFREE 0 AS ( -- Accts that are CLOSED SELECT DISTINCT T1.ACC_I ACC_I ,T1.ACC_I LATST_ACC_I ,T1.ACC_CLS_D ,NULL ACC_CONVN_D ,T1.ACC_STAT_C ,'CLOSE' STATUS FROM ACCT_DIMN_CURR T1, ACCT_E99_MESSAGE_FACT E9 WHERE T1.ACC_I = E9.ACC_I AND T1.ACC_OPEN_D >= TO_DATE('&1') - 60 AND T1.ACC_CLS_D BETWEEN TRUNC(TO_DATE('&1'),'D') - 1 AND TO_DATE('&1') AND T1.BRN_I <> '1X' AND E9.E99_MSG_C IN ('K12','KY0','HU1','7H6','GK7', 'GL5','GT5', 'GS7','F03','NP3','LS8','MQ4','MR2','NA4') UNION -- Accts converted to 1X SELECT DISTINCT AD.ACC_I ,T2.ACC_TO_I LATST_ACC_I ,AD.ACC_CLS_D ,T2.ACC_CONVN_D ,AD.ACC_STAT_C ,'1X' STATUS FROM ACCT_CONVN_DIMN T2, ACCT_E99_MESSAGE_FACT E9, ACCT_DIMN_CURR AD WHERE AD.ACC_I = T2.ACC_FROM_I AND AD.ACC_OPEN_D >= TO_DATE('&1') - 60 AND T2.ACC_CONVN_D BETWEEN TRUNC(TO_DATE('&1'),'D') - 1 AND TO_DATE('&1') AND E9.E99_MSG_C IN ('K12','KY0','HU1','7H6','GK7', 'GL5','GT5', 'GS7','F03','NP3','LS8','MQ4','MR2','NA4') AND (E9.ACC_I = T2.ACC_FROM_I OR E9.ACC_I = T2.ACC_TO_I) )