CREATE OR replace PACKAGE app_development.security_compare AS PROCEDURE identify_securities(in_effective_date IN DATE, in_security_alias IN NUMBER DEFAULT NULL); PROCEDURE retreive_records; PROCEDURE compare_records; END security_compare; / CREATE OR REPLACE PACKAGE BODY APP_DEVELOPMENT.security_compare AS LC_SQL VARCHAR2(20000); PROCEDURE IDENTIFY_SECURITIES(IN_EFFECTIVE_DATE IN DATE, IN_SECURITY_ALIAS IN NUMBER DEFAULT NULL) IS L_EFF_DATE DATE; L_SEC_ALIAS NUMBER; BEGIN L_EFF_DATE := to_date(IN_EFFECTIVE_DATE); L_SEC_ALIAS := IN_SECURITY_ALIAS; /*Deleting Old Entries*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.SPOKE_SECURITIES'; EXECUTE IMMEDIATE LC_SQL; IF (L_SEC_ALIAS is NULL) THEN /*Inserting Identified Securities*/ INSERT /*+APPEND(U)*/ INTO APP_DEVELOPMENT.SPOKE_SECURITIES U SELECT /*+DRIVING_SITE(R,100)*/ R.PRIMARY_ASSET_ID, R.SECURITY_ALIAS, R.LAST_HELD_DATE, R.PREV_HELD_DATE FROM ADMIN.SPOKE_SECURITIES@REGION_PROD.WORLD R WHERE R.last_held_date is NOT NULL and abs(R.last_held_date - L_EFF_DATE) > 0 and abs(R.last_held_date - L_EFF_DATE) <= 1; COMMIT; ELSE BEGIN INSERT /*+APPEND(U)*/ INTO APP_DEVELOPMENT.SPOKE_SECURITIES U SELECT /*+DRIVING_SITE(R,100)*/ R.PRIMARY_ASSET_ID, R.SECURITY_ALIAS, R.LAST_HELD_DATE, R.PREV_HELD_DATE FROM ADMIN.SPOKE_SECURITIES@REGION_PROD.WORLD R WHERE R.SECURITY_ALIAS = L_SEC_ALIAS; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000, 'Unknown Exception in Selecting Data from ADMIN.SPOKE_SECURITIES'); END; END IF; RETREIVE_RECORDS_FROM_SPOKE(); COMPARE_RECORDS(); END; PROCEDURE retreive_records IS BEGIN /*SECMASTER_HISTORY*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.SECMASTER_HISTORY_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ SECMASTER_HISTORY_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.SECMASTER_HISTORY@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.SECMASTER_HISTORY@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*SECMASTER_HISTORY*/ /*SECURITY_MASTER_DETAIL_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.SEC_MASTER_DETAIL_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ SEC_MASTER_DETAIL_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.SECURITY_MASTER_DETAIL_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.SECURITY_MASTER_DETAIL_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*SECURITY_MASTER_DETAIL_HIST*/ /*SECMASTER_DETAIL_EXT_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.SECMASTER_DTL_EXT_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ SECMASTER_DTL_EXT_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.SECMASTER_DETAIL_EXT_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.SECMASTER_DETAIL_EXT_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*SECMASTER_DETAIL_EXT_HIST*/ /*EQUITY_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.EQUITY_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ EQUITY_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.EQUITY_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.EQUITY_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*EQUITY_HIST*/ /*EQUITY_DETAIL_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.EQUITY_DETAIL_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ EQUITY_DETAIL_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.EQUITY_DETAIL_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.EQUITY_DETAIL_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*EQUITY_DETAIL_HIST*/ /*FIXED_INCOME_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.FIXED_INCOME_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ FIXED_INCOME_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.FIXED_INCOME_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.FIXED_INCOME_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*FIXED_INCOME_HIST*/ /*FIXED_INCOME_DETAIL_EXT_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.FI_DETAIL_EXT_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ FI_DETAIL_EXT_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.FIXED_INCOME_DETAIL_EXT_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.FIXED_INCOME_DETAIL_EXT_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*FIXED_INCOME_DETAIL_EXT_HIST*/ /*DERIVATIVES_HIST*/ LC_SQL := 'TRUNCATE TABLE APP_DEVELOPMENT.DERIVATIVES_HIST_X'; EXECUTE IMMEDIATE LC_SQL; insert into /*+ APPEND(H)*/ DERIVATIVES_HIST_X H SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.DERIVATIVES_HIST@REGION_PROD.WORLD K INNER JOIN APP_DEVELOPMENT.SPOKE_SECURITIES I ON K.SECURITY_ALIAS = I.SECURITY_ALIAS AND K.SRC_INTFC_INST = 140 WHERE K.EFFECTIVE_DATE = (SELECT /*+DRIVING_SITE(H)*/ MAX(H.EFFECTIVE_DATE) FROM SECURITYDBO.DERIVATIVES_HIST@REGION_PROD.WORLD H WHERE K.SECURITY_ALIAS = H.SECURITY_ALIAS AND K.SRC_INTFC_INST = H.SRC_INTFC_INST); commit; /*DERIVATIVES_HIST*/ END; PROCEDURE COMPARE_RECORDS IS l_query VARCHAR2(10000) := ''; CURSOR TBL1 IS SELECT TABLE_NAME, COLUMN_NAME FROM APP_DEVELOPMENT.COLUMNS_TO_COMPARE; TBL1_REC APP_DEVELOPMENT.COLUMNS_TO_COMPARE%rowtype; BEGIN l_query := 'TRUNCATE TABLE COMPARE_REC'; EXECUTE IMMEDIATE l_query; For tbl1_rec in tbl1 loop IF (TBL1_REC.TABLE_NAME = 'SECMASTER_HISTORY') THEN l_query := 'INSERT INTO COMPARE_REC SELECT B.SECURITY_ALIAS, B.PRIMARY_ASSET_ID, A.SECURITY_ALIAS, A.PRIMARY_ASSET_ID,' || '''' || TBL1_REC.TABLE_NAME || '''' || ',' || '''' || TBL1_REC.COLUMN_NAME || '''' || ',' || ' B.' || TBL1_REC.COLUMN_NAME || ',A.' || TBL1_REC.COLUMN_NAME || ',' || ' DECODE (A.' || TBL1_REC.COLUMN_NAME || ', B.' || TBL1_REC.COLUMN_NAME || ',' || q'!' MATCHING '!' || ',' || q'!' NOT '!' || ')' || ' FROM APP_DEVELOPMENT.SECMASTER_HISTORY_X A, SECURITYDBO.' || TBL1_REC.TABLE_NAME || '@PROD1.WORLD B' || ' WHERE A.SRC_INTFC_INST=140' || ' AND B.SRC_INTFC_INST=140' || ' AND A.PRIMARY_ASSET_ID=B.PRIMARY_ASSET_ID' || ' AND B.effective_date=(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.' || TBL1_REC.TABLE_NAME || '@REGION_PROD.WORLD H WHERE' || ' B.PRIMARY_ASSET_ID = H.PRIMARY_ASSET_ID AND B.SRC_INTFC_INST = H.SRC_INTFC_INST)' || ' AND A.' || tbl1_rec.column_name || '= B.' || tbl1_rec.column_name; EXECUTE IMMEDIATE l_query; COMMIT; END IF; end loop; END; END security_compare; /