SELECT 'Rows updated:',COUNT(1) FROM (SELECT 1 FROM DELTA.PRMCN WHERE ETL_FLAG IN ('A','D')) S;

--重跑：删除已跑入数据

DELETE FROM CCRD.PRMCN WHERE JOB_SEQ_ID= New_JOB_SEQ_ID;

--重跑：从历史表恢复数据

INSERT INTO CCRD.PRMCN(CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID)

select CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID

from ODSHIS.PRMCN WHERE NEW_JOB_SEQ_ID= New_JOB_SEQ_ID;

--重跑：删除已跑入历史数据

DELETE FROM ODSHIS.PRMCN WHERE NEW_JOB_SEQ_ID= New_JOB_SEQ_ID;

--备份数据到历史表

SELECT 'Rows readed:',COUNT(1),'Rows changed:',COUNT(1) FROM (SELECT 1 FROM DELTA.PRMCN WHERE ETL_FLAG IN ('I','A','D')) S;

SELECT 'Rows updated:',COUNT(1) FROM NEW TABLE (

INSERT INTO ODSHIS.PRMCN(CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID,NEW_JOB_SEQ_ID)

select CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID,New_JOB_SEQ_ID

from CCRD.PRMCN T

WHERE T.END_DT='9999-12-31' AND EXISTS ( SELECT 1 FROM DELTA.PRMCN S

WHERE T.CATEGORY=S.CATEGORY )

);

--断链

MERGE INTO CCRD.PRMCN T

USING (SELECT * FROM DELTA.PRMCN WHERE ETL_FLAG IN ('I','D','A')) S

ON T.CATEGORY=S.CATEGORY  AND T.END_DT='9999-12-31'

WHEN MATCHED THEN UPDATE SET

T.END_DT='#DATEOFDATA#', T.JOB_SEQ_ID= New_JOB_SEQ_ID;

--加链

INSERT INTO CCRD.PRMCN(CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID)

select CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,'#DATEOFDATA#','9999-12-31',New_JOB_SEQ_ID

from DELTA.PRMCN where ETL_FLAG in ('A','I');

--保持数据完整性

MERGE INTO CCRD.PRMCN T

USING (SELECT * FROM DELTA.PRMCN WHERE ETL_FLAG = 'D' ) S

ON T.CATEGORY=S.CATEGORY

WHEN NOT MATCHED THEN

INSERT (CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID)

VALUES (CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,'#DATEOFDATA#','#DATEOFDATA#',New_JOB_SEQ_ID);