CREATE OR REPLACE PROCEDURE PROC_CP_JCI_CONTRACT_DIM_HIST IS L_FUN_RET_VALUE NUMBER; V_SRC_ID NUMBER; REC_JCI_CONTRACT_DIM JCI_CONTRACT_DIM%ROWTYPE; REC_JCI_CONTRACT_DIM_HIST JCI_CONTRACT_DIM_HIST%ROWTYPE; ERR_ROW VARCHAR2(4000); IND NUMBER(1) := 1; /*************************************************************************************************************************** * PROGRAM NAME : PROC_JCI_CONTRACT_DIM_HIST * AUTHOR : ARUN KUMAR * CREATION DATE : 09-FEB-2010 * DESCRIPTION : STORED PROCEDURE TO LOAD DATA TO CONTRACT HISTORY TABLE FROM THE CONTRACT DIMENSION TABLE * CALLING PROGRAM : NONE * CALLED PROGRAM : NONE * INPUT PARAMETERS : NONE * OUTPUT PARAMETERS : NONE * INPUTS FILE(S) : NONE * OUTPUT FILE(S) : NONE * MODIFICATIONS : NONE * * ***************************************************************************************************************************/ CURSOR CONTRACT_DIM_CUR IS SELECT CONTRACT_ST, BRANCH_NUMBER, STATUS, CONTRACT_ST_TYPE, SEQUENCE_NUMBER, TRUNC(CREATION_DATE) CREATION_DATE, CREATED_BY, TRUNC(LAST_UPDATED_DATE), LAST_UPDATED_BY, ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, COUNTRY, POSTAL_CODE, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, TECH_REPORTING_700, REPORT_GENERATED_DT, FINAL_FLG, NUM_COPIES, NOTARIZED, FEDERAL_ID, JCI_SUB_LABOR, MAIL_ID, TRUNC(PROJECT_START_DATE) PROJECT_START_DATE, STATE_FLG, REGENERATE_FLG, TRUNC(CONTRACT_CLOSE_DT) CONTRACT_CLOSE_DT, SOURCE_APPLICATION_ID, CREATED_DT, CREATED_USER, MODIFIED_DT, MODIFIED_USER FROM JCI_CONTRACT_DIM; FUNCTION FUN_JCI_CONTRACT_DIM_HIST ( IN_CONTRACT_ST IN JCI_CONTRACT_DIM.CONTRACT_ST%TYPE, IN_ADDRESS_1 IN JCI_CONTRACT_DIM.ADDRESS_1%TYPE, IN_ADDRESS_2 IN JCI_CONTRACT_DIM.ADDRESS_2%TYPE, IN_ADDRESS_3 IN JCI_CONTRACT_DIM.ADDRESS_3%TYPE, IN_CITY IN JCI_CONTRACT_DIM.CITY%TYPE, IN_STATE IN JCI_CONTRACT_DIM.STATE%TYPE, IN_COUNTRY IN JCI_CONTRACT_DIM.COUNTRY%TYPE, IN_POSTAL_CODE IN JCI_CONTRACT_DIM.POSTAL_CODE%TYPE, IN_TECH_REPORTING_700 IN JCI_CONTRACT_DIM.TECH_REPORTING_700%TYPE, IN_NUM_COPIES IN JCI_CONTRACT_DIM.NUM_COPIES%TYPE, OP_CONTRACT_KEY OUT NUMBER ) RETURN NUMBER IS BEGIN IF (IND=1) THEN BEGIN JCI_CP_JOB_INSERT('PROC_CP_JCI_CONTRACT_DIM_HIST'); EXCEPTION WHEN OTHERS THEN NULL; END; IND := 0; END IF; SELECT NVL((SELECT SOURCE_APPLICATION_ID FROM JCI_SOURCE_DIM WHERE UPPER(SOURCE_NAME) ='JCI_CONTRACT_DIM'),-999) INTO V_SRC_ID FROM DUAL; SELECT CONTRACT_KEY, CONTRACT_ST, CONTRACT_NAME, PROJECT_START_DT, STATUS, CONTRACT_CLOSE_DT, ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, COUNTRY, POSTAL_CODE, TECH_REPORTING_700, STATE_FLG, NUM_COPIES, REPORT_GENERATED_DT, FINAL_FLG, SEQUENCE_NUMBER, REGENERATE_FLG, CREATED_DT, CREATED_USER, MODIFIED_DT, MODIFIED_USER, BRANCH_NUMBER, SOURCE_APPLICATION_ID, CP_CURRENT_FLG INTO REC_JCI_CONTRACT_DIM_HIST FROM JCI_CONTRACT_DIM_HIST A WHERE A.CONTRACT_ST = IN_CONTRACT_ST AND CP_CURRENT_FLG = 'Y'; IF NVL(UPPER(IN_ADDRESS_1), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.ADDRESS_1), 'NULL') AND NVL(UPPER(IN_ADDRESS_2), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.ADDRESS_2), 'NULL') AND NVL(UPPER(IN_ADDRESS_3), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.ADDRESS_3), 'NULL') AND NVL(UPPER(IN_CITY), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.CITY), 'NULL') AND NVL(UPPER(IN_STATE), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.STATE), 'NULL') AND NVL(UPPER(IN_COUNTRY), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.COUNTRY), 'NULL') AND NVL(UPPER(IN_POSTAL_CODE), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.POSTAL_CODE), 'NULL') AND NVL(UPPER(IN_TECH_REPORTING_700), 'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.TECH_REPORTING_700), 'NULL') AND NVL(UPPER(IN_NUM_COPIES),'NULL') = NVL(UPPER(REC_JCI_CONTRACT_DIM_HIST.NUM_COPIES),'NULL') THEN OP_CONTRACT_KEY := -1; RETURN -1; -- update ELSE -- type2 columns have changed OP_CONTRACT_KEY := REC_JCI_CONTRACT_DIM_HIST.CONTRACT_KEY; --1 will be added while inserting UPDATE JCI_CONTRACT_DIM_HIST B SET B.CP_CURRENT_FLG = 'N', B.MODIFIED_DT = SYSDATE, B.MODIFIED_USER = USER WHERE B.CONTRACT_ST = IN_CONTRACT_ST AND NVL(UPPER(TRIM(B.CP_CURRENT_FLG)),'NA') = 'Y'; COMMIT; RETURN 1; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN JCI_CP_EXCEPTION_TRACK('PROC_CP_JCI_CONTRACT_DIM_HIST',SQLCODE,SQLERRM,'KNOWN',ERR_ROW); OP_CONTRACT_KEY := 0; RETURN 0; --new insert END FUN_JCI_CONTRACT_DIM_HIST; BEGIN FOR REC_JCI_CONTRACT_DIM IN CONTRACT_DIM_CUR LOOP L_FUN_RET_VALUE := FUN_JCI_CONTRACT_DIM_HIST(REC_JCI_CONTRACT_DIM.CONTRACT_ST, REC_JCI_CONTRACT_DIM.ADDRESS_1, REC_JCI_CONTRACT_DIM.ADDRESS_2, REC_JCI_CONTRACT_DIM.ADDRESS_3, REC_JCI_CONTRACT_DIM.CITY, REC_JCI_CONTRACT_DIM.STATE, REC_JCI_CONTRACT_DIM.COUNTRY, REC_JCI_CONTRACT_DIM.POSTAL_CODE, REC_JCI_CONTRACT_DIM.TECH_REPORTING_700, REC_JCI_CONTRACT_DIM.NUM_COPIES, REC_JCI_CONTRACT_DIM_HIST.CONTRACT_KEY); IF L_FUN_RET_VALUE = 0 THEN --new insert INSERT INTO JCI_CONTRACT_DIM_HIST (CONTRACT_KEY ,CONTRACT_ST ,CONTRACT_NAME ,PROJECT_START_DT ,STATUS ,CONTRACT_CLOSE_DT ,ADDRESS_1 ,ADDRESS_2 ,ADDRESS_3 ,CITY ,STATE ,COUNTRY ,POSTAL_CODE ,TECH_REPORTING_700 ,STATE_FLG ,NUM_COPIES ,REPORT_GENERATED_DT ,FINAL_FLG ,SEQUENCE_NUMBER ,REGENERATE_FLG ,BRANCH_NUMBER ,CREATED_USER ,CREATED_DT ,MODIFIED_DT ,MODIFIED_USER ,SOURCE_APPLICATION_ID ,CP_CURRENT_FLG) VALUES (JCI_CONTRACT_DIM_HIST_SEQ.NEXTVAL ,REC_JCI_CONTRACT_DIM.CONTRACT_ST ,REC_JCI_CONTRACT_DIM.ATTRIBUTE1 ,REC_JCI_CONTRACT_DIM.PROJECT_START_DATE ,REC_JCI_CONTRACT_DIM.STATUS ,REC_JCI_CONTRACT_DIM.CONTRACT_CLOSE_DT ,REC_JCI_CONTRACT_DIM.ADDRESS_1 ,REC_JCI_CONTRACT_DIM.ADDRESS_2 ,REC_JCI_CONTRACT_DIM.ADDRESS_3 ,REC_JCI_CONTRACT_DIM.CITY ,REC_JCI_CONTRACT_DIM.STATE ,REC_JCI_CONTRACT_DIM.COUNTRY ,REC_JCI_CONTRACT_DIM.POSTAL_CODE ,REC_JCI_CONTRACT_DIM.TECH_REPORTING_700 ,REC_JCI_CONTRACT_DIM.STATE_FLG ,REC_JCI_CONTRACT_DIM.NUM_COPIES ,NULL ,REC_JCI_CONTRACT_DIM.FINAL_FLG ,REC_JCI_CONTRACT_DIM.SEQUENCE_NUMBER ,REC_JCI_CONTRACT_DIM.REGENERATE_FLG ,REC_JCI_CONTRACT_DIM.BRANCH_NUMBER ,USER ,SYSDATE ,NULL ,NULL ,V_SRC_ID ,'Y'); ELSIF L_FUN_RET_VALUE = -1 THEN --update UPDATE JCI_CONTRACT_DIM_HIST SET CONTRACT_ST = REC_JCI_CONTRACT_DIM.CONTRACT_ST ,CONTRACT_NAME = REC_JCI_CONTRACT_DIM.ATTRIBUTE1 ,PROJECT_START_DT = REC_JCI_CONTRACT_DIM.PROJECT_START_DATE ,STATUS = REC_JCI_CONTRACT_DIM.STATUS ,CONTRACT_CLOSE_DT = REC_JCI_CONTRACT_DIM.CONTRACT_CLOSE_DT ,ADDRESS_1 = REC_JCI_CONTRACT_DIM.ADDRESS_1 ,ADDRESS_2 = REC_JCI_CONTRACT_DIM.ADDRESS_2 ,ADDRESS_3 = REC_JCI_CONTRACT_DIM.ADDRESS_3 ,CITY = REC_JCI_CONTRACT_DIM.CITY ,STATE = REC_JCI_CONTRACT_DIM.STATE ,COUNTRY = REC_JCI_CONTRACT_DIM.COUNTRY ,POSTAL_CODE = REC_JCI_CONTRACT_DIM.POSTAL_CODE ,TECH_REPORTING_700 = REC_JCI_CONTRACT_DIM.TECH_REPORTING_700 ,STATE_FLG = REC_JCI_CONTRACT_DIM.STATE_FLG ,NUM_COPIES = REC_JCI_CONTRACT_DIM.NUM_COPIES ,REPORT_GENERATED_DT = NULL ,FINAL_FLG = REC_JCI_CONTRACT_DIM.FINAL_FLG ,SEQUENCE_NUMBER = REC_JCI_CONTRACT_DIM.SEQUENCE_NUMBER ,REGENERATE_FLG = REC_JCI_CONTRACT_DIM.REGENERATE_FLG ,BRANCH_NUMBER = REC_JCI_CONTRACT_DIM.BRANCH_NUMBER ,SOURCE_APPLICATION_ID = V_SRC_ID ,MODIFIED_DT = SYSDATE ,MODIFIED_USER = USER WHERE CONTRACT_KEY = REC_JCI_CONTRACT_DIM_HIST.CONTRACT_KEY AND UPPER(TRIM(CP_CURRENT_FLG)) = 'Y'; ELSIF L_FUN_RET_VALUE = 1 THEN --type2 insert INSERT INTO JCI_CONTRACT_DIM_HIST (CONTRACT_KEY ,CONTRACT_ST ,CONTRACT_NAME ,PROJECT_START_DT ,STATUS ,CONTRACT_CLOSE_DT ,ADDRESS_1 ,ADDRESS_2 ,ADDRESS_3 ,CITY ,STATE ,COUNTRY ,POSTAL_CODE ,TECH_REPORTING_700 ,STATE_FLG ,NUM_COPIES ,REPORT_GENERATED_DT ,FINAL_FLG ,SEQUENCE_NUMBER ,REGENERATE_FLG ,BRANCH_NUMBER ,CREATED_USER ,CREATED_DT ,MODIFIED_DT ,MODIFIED_USER ,SOURCE_APPLICATION_ID ,CP_CURRENT_FLG) VALUES (JCI_CONTRACT_DIM_HIST_SEQ.NEXTVAL ,REC_JCI_CONTRACT_DIM.CONTRACT_ST ,REC_JCI_CONTRACT_DIM.ATTRIBUTE1 ,REC_JCI_CONTRACT_DIM.PROJECT_START_DATE ,REC_JCI_CONTRACT_DIM.STATUS ,REC_JCI_CONTRACT_DIM.CONTRACT_CLOSE_DT ,REC_JCI_CONTRACT_DIM.ADDRESS_1 ,REC_JCI_CONTRACT_DIM.ADDRESS_2 ,REC_JCI_CONTRACT_DIM.ADDRESS_3 ,REC_JCI_CONTRACT_DIM.CITY ,REC_JCI_CONTRACT_DIM.STATE ,REC_JCI_CONTRACT_DIM.COUNTRY ,REC_JCI_CONTRACT_DIM.POSTAL_CODE ,REC_JCI_CONTRACT_DIM.TECH_REPORTING_700 ,REC_JCI_CONTRACT_DIM.STATE_FLG ,REC_JCI_CONTRACT_DIM.NUM_COPIES ,NULL ,REC_JCI_CONTRACT_DIM.FINAL_FLG ,REC_JCI_CONTRACT_DIM.SEQUENCE_NUMBER ,REC_JCI_CONTRACT_DIM.REGENERATE_FLG ,REC_JCI_CONTRACT_DIM.BRANCH_NUMBER ,USER ,SYSDATE ,NULL ,NULL ,V_SRC_ID ,'Y'); END IF; END LOOP; COMMIT; JCI_CP_JOB_TRACK('PROC_CP_JCI_CONTRACT_DIM_HIST','SUCCESS',0,0); EXECUTE IMMEDIATE 'ANALYZE TABLE JCI_CONTRACT_DIM_HIST ESTIMATE STATISTICS'; EXCEPTION WHEN OTHERS THEN ROLLBACK; JCI_CP_EXCEPTION_TRACK('PROC_CP_JCI_CONTRACT_DIM_HIST',SQLCODE,SQLERRM,'UNKNOWN'); JCI_CP_JOB_TRACK('PROC_CP_JCI_CONTRACT_DIM_HIST','FAILED',0,0); END PROC_CP_JCI_CONTRACT_DIM_HIST;