step-1)
DELETE FROM SYS_STG_JOIN_MASTER t WHERE t.MAP_REF_NUM=380 and t.col_nm='N_FREQUENCY_KEY' and t.stg_col_nm='N_FREQUENCY_KEY'
/
CALL FSI_CREATE_SEQUENCE('SEQ_DIM_KEY_INDICATOR','DIM_KEY_INDICATOR','N_KI_SKEY')
/
MERGE INTO SYS_TBL_MASTER T 
USING ( 
 SELECT '380' MAP_REF_NUM,'DIM_KEY_INDICATOR' TBL_NM,'VW_KEY_INDICATOR' STG_TBL_NM,'' SRC_PRTY,'33' SRC_PROC_SEQ,'MASTER' SRC_TYP,'0' DT_OFFSET,'' SRC_KEY,'' MERGE_HINT,'' SELECT_HINT,'' SESSION_ENABLE_STATEMENT,'' SESSION_DISABLE_STATEMENT FROM DUAL
 ) S
ON (T.MAP_REF_NUM = S.MAP_REF_NUM)
WHEN MATCHED THEN 
 UPDATE 
 SET T.TBL_NM = S.TBL_NM,
     T.STG_TBL_NM = S.STG_TBL_NM,
     T.SRC_PRTY = S.SRC_PRTY,
     T.SRC_PROC_SEQ = S.SRC_PROC_SEQ,
     T.SRC_TYP = S.SRC_TYP,
     T.DT_OFFSET = S.DT_OFFSET,
     T.SRC_KEY = S.SRC_KEY,
     T.MERGE_HINT = S.MERGE_HINT,
     T.SELECT_HINT = S.SELECT_HINT,
     T.SESSION_ENABLE_STATEMENT = S.SESSION_ENABLE_STATEMENT,
     T.SESSION_DISABLE_STATEMENT = S.SESSION_DISABLE_STATEMENT
WHEN NOT MATCHED THEN 
 INSERT 
  (MAP_REF_NUM,TBL_NM,STG_TBL_NM,SRC_PRTY,SRC_PROC_SEQ,SRC_TYP,DT_OFFSET,SRC_KEY,MERGE_HINT,SELECT_HINT,SESSION_ENABLE_STATEMENT,SESSION_DISABLE_STATEMENT)
 VALUES
  (S.MAP_REF_NUM,S.TBL_NM,S.STG_TBL_NM,S.SRC_PRTY,S.SRC_PROC_SEQ,S.SRC_TYP,S.DT_OFFSET,S.SRC_KEY,S.MERGE_HINT,S.SELECT_HINT,S.SESSION_ENABLE_STATEMENT,S.SESSION_DISABLE_STATEMENT )
/
MERGE INTO SYS_STG_JOIN_MASTER T 
USING ( 
 SELECT '380' MAP_REF_NUM,'D_RECORD_END_DATE' COL_NM,'ED' COL_TYP,'31-DEC-9999' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'DATE' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'D_RECORD_START_DATE' COL_NM,'SD' COL_TYP,'SD' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'DATE' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'FIC_MIS_DATE' COL_NM,'NN' COL_TYP,'D_CREATED_DATE' STG_COL_NM,'2' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'DATE' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'F_LATEST_RECORD_INDICATOR' COL_NM,'LRI' COL_TYP,'Y' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'CHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'N_KI_DEF_KEY' COL_NM,'PK' COL_TYP,'KI_DEFINITION_KEY' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'NUMBER' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'N_KI_SKEY' COL_NM,'SK' COL_TYP,'SEQ_DIM_KEY_INDICATOR.NEXTVAL' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'NUMBER' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'V_COMMENT' COL_NM,'DA' COL_TYP,'V_COMMENT' STG_COL_NM,'2' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'V_KRIDEF_NAME' COL_NM,'DA' COL_TYP,'V_KRIDEF_NAME' STG_COL_NM,'2' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'V_KRI_DESCRIPTION' COL_NM,'DA' COL_TYP,'V_DESCRIPTION' STG_COL_NM,'2' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'V_KRI_TYPE' COL_NM,'DA' COL_TYP,'V_KRI_TYPE' STG_COL_NM,'2' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'V_OWNER' COL_NM,'NN' COL_TYP,'V_OWNER' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 UNION 
 SELECT '380' MAP_REF_NUM,'V_LOOKUP_CELL_ID' COL_NM,'DA' COL_TYP,'V_LOOKUP_CELL_ID' STG_COL_NM,'2' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
  UNION 
 SELECT '380' MAP_REF_NUM,'V_FREQUENCY_CODE' COL_NM,'NN' COL_TYP,'N_FREQUENCY_KEY' STG_COL_NM,'' SCD_TYP_ID,'N' PRTY_LOOKUP_REQD_FLG,'VARCHAR' COL_DATATYPE,'' COL_FORMAT FROM DUAL
 
 ) S
ON (T.MAP_REF_NUM = S.MAP_REF_NUM AND T.COL_NM = S.COL_NM)
WHEN MATCHED THEN 
 UPDATE 
 SET T.COL_TYP = S.COL_TYP,
     T.STG_COL_NM = S.STG_COL_NM,
     T.SCD_TYP_ID = S.SCD_TYP_ID,
     T.PRTY_LOOKUP_REQD_FLG = S.PRTY_LOOKUP_REQD_FLG,
     T.COL_DATATYPE = S.COL_DATATYPE,
     T.COL_FORMAT = S.COL_FORMAT
WHEN NOT MATCHED THEN 
 INSERT 
  (MAP_REF_NUM,COL_NM,COL_TYP,STG_COL_NM,SCD_TYP_ID,PRTY_LOOKUP_REQD_FLG,COL_DATATYPE,COL_FORMAT)
 VALUES
  (S.MAP_REF_NUM,S.COL_NM,S.COL_TYP,S.STG_COL_NM,S.SCD_TYP_ID,S.PRTY_LOOKUP_REQD_FLG,S.COL_DATATYPE,S.COL_FORMAT )
/


step-2)(it is required only in case of upgrade setup)
declare
cursor c1 is select KI_DEFINITION_KEY,N_FREQUENCY_KEY,nvl(V_DESC_ATTR_2,V_DESC_ATTR_4) V_LOOKUP_CELL_ID from fct_kri_def where v_desc_attr_5='2' and V_RECORD_EXCLUDE='N';
begin
for i in c1 loop
update dim_key_indicator set V_LOOKUP_CELL_ID=i.V_LOOKUP_CELL_ID,V_FREQUENCY_CODE=i.N_FREQUENCY_KEY where n_ki_def_key=i.KI_DEFINITION_KEY;
commit;
end loop;
exception
when others then
rollback;
end;
/

commit
/