Team,
I am working on oracle 11.2.0.3 . I have written a Stored Procedure which will load the data into one table by retrieving from multiple tables. 2 tables are located in other Schema and remaining and the current source tables are located in 1 schema. So have applied DRIVING_SITE hint for the tables located in remote DB to access those tables. But still the Procedure is taking very long time to load the data and it is loading duplicate data too. Could you please find the below procedure and advise to tune it. Here am posting DDLs for the tables and Insert statements and Procedure. Please check and suggest me.
<code>
CREATE TABLE RAW.DATA_LOAD_LD_WRK
( SUB_ID VARCHAR2(255 BYTE), JOB_NAME VARCHAR2(255 BYTE),
TRANS_TYP VARCHAR2(255 BYTE), SOURCE_SYS_CD VARCHAR2(255 BYTE),
SOURCE_PROVD_ID VARCHAR2(255 BYTE), PROVD_ID VARCHAR2(255 BYTE),
TAX_ID_NUMBER VARCHAR2(255 BYTE), NIPPO VARCHAR2(255 BYTE),
ENTITY_TYPE VARCHAR2(255 BYTE), FARM_CD VARCHAR2(255 BYTE),
PRIDE_ORG_SPCLTY_CD VARCHAR2(255 BYTE), GROUP_RELATION_NIPPO VARCHAR2(255 BYTE),
GROUP_RELATION_ID VARCHAR2(255 BYTE), GROUP_RELATION_SPECIALTY VARCHAR2(255 BYTE),
PRIDE_EFCTV_DATE DATE, PRIDE_ORG_NM VARCHAR2(255 BYTE),
PRIDE_LAST_NM VARCHAR2(255 BYTE), PRIDE_FRST_NM VARCHAR2(255 BYTE),
PRIDE_MID_NM VARCHAR2(255 BYTE), GNRTNL_SFX_CD VARCHAR2(255 BYTE),
GNDR_CD VARCHAR2(255 BYTE), BRTH_DT DATE,
PRIDE_REPLACE_RESOURCE_CD VARCHAR2(255 BYTE), PRIDE_REPLACE_TYPE_CD VARCHAR2(255 BYTE), PRIDE_REPLACE_TEXT VARCHAR2(255 BYTE), PRIDE_REPLACE_ST VARCHAR2(255 BYTE),
PRIDE_REPLACE_EFCTV_DT DATE, PRIDE_REPLACE_TRMNT_DT DATE,
PRIDE_REPLACE_TRM_RSN_CD VARCHAR2(255 BYTE), TERM_NETWORK_ID VARCHAR2(255 BYTE),
NETWORK_EFFECTIVE_DATE DATE, TERM_NETWORK_DATE DATE,
TERM_NETWORK_REASON VARCHAR2(255 BYTE), NETWORK_ID VARCHAR2(255 BYTE),
NETWORK_PCP_IND VARCHAR2(255 BYTE), NONPAR_ROW VARCHAR2(255 BYTE),
NETWORK_DIR_DISPLAY_IND VARCHAR2(255 BYTE), MAX_MEMBER_COUNT VARCHAR2(255 BYTE),
MIN_AGE_NUMBER VARCHAR2(255 BYTE), MAX_AGE_NUMBER VARCHAR2(255 BYTE),
PATIENT_GENDER_CD VARCHAR2(255 BYTE), ACCEPTING_PATIENTS_IND VARCHAR2(255 BYTE),
CLAIM_TIMELY_FILING_DAYS_NUMBER VARCHAR2(255 BYTE), PROGRAM_ID VARCHAR2(255 BYTE),
REPLACE_RESOURCE_CD VARCHAR2(255 BYTE), REPLACE_TEXT VARCHAR2(255 BYTE),
REPLACE_TYPE_CODE VARCHAR2(255 BYTE), RL_RELATION_ADDR_NW_REPLACE_EFCTV_DT DATE,
RL_RELATION_ADDR_NW_REPLACE_TRM_DT DATE, RL_RELATION_ADDR_NW_REPLACE_TRM_RSN_CD VARCHAR2(255 BYTE), TERM_REIMBURSEMENT_ID VARCHAR2(255 BYTE),
EXISTING_REIMBURSEMENT_EFF_DT DATE, REIMBURSEMENT_TERM_DT DATE,
REIMBURSEMENT_TERM_REASON VARCHAR2(255 BYTE),
REIMBURSEMENT_RESOURCE_CD VARCHAR2(255 BYTE), RA_1_ID VARCHAR2(255 BYTE),
RA_1_TYPE_CD VARCHAR2(255 BYTE), REIMBURSEMENT_EFF_DT DATE,
FFS_CAP_TYPE_CD VARCHAR2(255 BYTE), STNDRDZD_ADRS_ID_SRC VARCHAR2(255 BYTE),
ADDRESS_STREET VARCHAR2(255 BYTE), ADDRESS_CITY VARCHAR2(255 BYTE),
ADDRESS_STATE VARCHAR2(255 BYTE), ADDRESS_ZIP_PLUS_4 VARCHAR2(255 BYTE),
PRL_TYPE_CD VARCHAR2(255 BYTE), PRMRY_PADRS_IND VARCHAR2(255 BYTE),
ADDRESS_EFCTV_DT DATE,
ADDRESS_TRMNTN_DT DATE,
ADDRESS_TRMNTN_RSN_CD VARCHAR2(255 BYTE),
R_ADRS_ID VARCHAR2(255 BYTE),
R_ADDRESS_STREET VARCHAR2(255 BYTE),
R_ADDRESS_CITY VARCHAR2(255 BYTE),
R_ADDRESS_STATE VARCHAR2(255 BYTE),
R_ADDRESS_ZIP_PLUS_4 VARCHAR2(255 BYTE),
RMTNC_NM VARCHAR2(255 BYTE),
PAYMNT_TYPE_CD VARCHAR2(255 BYTE),
RMTNC_MTHD_CD VARCHAR2(255 BYTE),
RMTNC_EFCTV_DT DATE,
RMTNC_TRMNTN_DT DATE,
RMTNC_TRMNTN_RSN_CD VARCHAR2(255 BYTE),
PYMENTY_EFCTV_DT DATE,
PYMENTY_NM VARCHAR2(255 BYTE),
TAX_1099_NM VARCHAR2(255 BYTE),
FED_TAX_ID_TYPE_CD VARCHAR2(255 BYTE),
PADRS_TYPE_CD VARCHAR2(255 BYTE),
PRIDE_CTGRY_CD VARCHAR2(255 BYTE),
PRIDE_CTGRY_ID VARCHAR2(255 BYTE),
NTWK_KEY VARCHAR2(255 BYTE),
MSTR_PRIDE_ID VARCHAR2(255 BYTE),
PYMENTY_ID VARCHAR2(255 BYTE),
RMTNC_ID VARCHAR2(255 BYTE),
STNDRDZD_ADRS_ID VARCHAR2(255 BYTE),
R_STNDRDZD_ADRS_ID VARCHAR2(255 BYTE),
RLTD_MSTR_PRIDE_ID VARCHAR2(255 BYTE),
POA_KEY VARCHAR2(255 BYTE),
POA_NTWK_KEY VARCHAR2(255 BYTE),
RL_RELATION_ADDR_KEY VARCHAR2(255 BYTE),
RL_RELATION_ADDR_NTWK_KEY VARCHAR2(255 BYTE),
PRIDE_ORG_NTWK_KEY VARCHAR2(255 BYTE),
PRIDE_SRC_PK VARCHAR2(255 BYTE),
POA_SRC_PK VARCHAR2(255 BYTE),
PYMENTY_SRC_PK VARCHAR2(255 BYTE),
RMTNC_SRC_PK VARCHAR2(255 BYTE),
STNDRDZD_ADRS_SRC_PK VARCHAR2(255 BYTE),
R_STNDRDZD_ADRS_SRC_PK VARCHAR2(255 BYTE),
REL_PRIDE_SRC_PK VARCHAR2(255 BYTE),
RL_RELATION_ADDR_SRC_PK VARCHAR2(255 BYTE),
RL_RELATION_ADDR_NTWK_SRC_PK VARCHAR2(255 BYTE),
POA_NTWK_SRC_PK VARCHAR2(255 BYTE),
PRIDE_ORG_NTWK_SRC_PK VARCHAR2(255 BYTE),
LAST_UPDT_USER_ID VARCHAR2(255 BYTE),
LAST_UPDT_DT TIMESTAMP(6),
CREAT_USER_ID VARCHAR2(255 BYTE),
CREAT_DT TIMESTAMP(6),
ERR_IND VARCHAR2(255 BYTE),
DRVR_ID NUMBER(38),
DRVR_LOAD_DTM TIMESTAMP(6) NOT NULL,
WORK_LOAD_DTM TIMESTAMP(6) NOT NULL,
BATCH_ID VARCHAR2(255 BYTE),
FILE_NM VARCHAR2(255 BYTE) NOT NULL
)
Insert into RAW.DATA_LOAD_LD_WRK
(SUB_ID, JOB_NAME, TAX_ID_NUMBER, NIPPO, ENTITY_TYPE,
FARM_CD, PRIDE_ORG_SPCLTY_CD, PRIDE_EFCTV_DATE, PRIDE_ORG_NM, NETWORK_ID,
NETWORK_DIR_DISPLAY_IND, STNDRDZD_ADRS_ID_SRC, ADDRESS_STREET, ADDRESS_CITY, ADDRESS_STATE,
ADDRESS_ZIP_PLUS_4, PRL_TYPE_CD, FED_TAX_ID_TYPE_CD, PADRS_TYPE_CD, PRIDE_CTGRY_CD,
PRIDE_CTGRY_ID, NTWK_KEY, MSTR_PRIDE_ID, STNDRDZD_ADRS_ID, POA_KEY,
POA_NTWK_KEY, PRIDE_ORG_NTWK_KEY, PRIDE_SRC_PK, POA_SRC_PK, STNDRDZD_ADRS_SRC_PK,
POA_NTWK_SRC_PK, PRIDE_ORG_NTWK_SRC_PK, LAST_UPDT_USER_ID, LAST_UPDT_DT, CREAT_USER_ID,
CREAT_DT, ERR_IND, DRVR_ID, DRVR_LOAD_DTM, WORK_LOAD_DTM,
BATCH_ID, FILE_NM)
Values
('<User ID>', 'Update Gender Code', '010553777', '1497813844', 'G',
'52', 'P0N612VX1B', TO_DATE('1/1/1901', 'MM/DD/YYYY'), 'U AND M FAMILY EYECARE PC', 'HMX7',
'Y', '320664', '2014 Lawrenceville Suwanee Rd', 'Suwanee', 'GA',
'300242625', '1918', '7496', '178', '182',
'2', '56', '7C19DED88A90B780E6CCE3D06EBF8D30', '8A0661F7B17EC05E49EF4235F5AF5BBB', '1361C7095A0E23241EBA9BF066CE7262D97BF2AE',
'7AE6EFA68A1C480103B5EE6774ADF903', '88DD67AE84EA7576B5C8C61CCFC301A0', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30.1361C7095A0E23241EBA9BF066CE7262D97BF2AE.8A0661F7B17EC05E49EF4235F5AF5BBB', 'SYS.1225.8A0661F7B17EC05E49EF4235F5AF5BBB',
'SYS.1225.2.7AE6EFA68A1C480103B5EE6774ADF903', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30.88DD67AE84EA7576B5C8C61CCFC301A0', 'BLT', TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'BLT',
TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'N', 19725, TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
'141', 'Org-gender_preference_indicators.csv');
Insert into RAW.DATA_LOAD_LD_WRK
(SUB_ID, JOB_NAME, TAX_ID_NUMBER, NIPPO, ENTITY_TYPE,
FARM_CD, PRIDE_ORG_SPCLTY_CD, PRIDE_EFCTV_DATE, PRIDE_ORG_NM, NETWORK_ID,
NETWORK_DIR_DISPLAY_IND, PATIENT_GENDER_CD, STNDRDZD_ADRS_ID_SRC, ADDRESS_STREET, ADDRESS_CITY,
ADDRESS_STATE, ADDRESS_ZIP_PLUS_4, PRL_TYPE_CD, FED_TAX_ID_TYPE_CD, PADRS_TYPE_CD,
PRIDE_CTGRY_CD, PRIDE_CTGRY_ID, NTWK_KEY, MSTR_PRIDE_ID, STNDRDZD_ADRS_ID,
POA_KEY, POA_NTWK_KEY, PRIDE_ORG_NTWK_KEY, PRIDE_SRC_PK, POA_SRC_PK,
STNDRDZD_ADRS_SRC_PK, POA_NTWK_SRC_PK, PRIDE_ORG_NTWK_SRC_PK, LAST_UPDT_USER_ID, LAST_UPDT_DT,
CREAT_USER_ID, CREAT_DT, ERR_IND, DRVR_ID, DRVR_LOAD_DTM,
WORK_LOAD_DTM, BATCH_ID, FILE_NM)
Values
('<User ID>', 'Update Gender Code', '010553777', '1497813844', 'G',
'52', 'P0N612VX1B', TO_DATE('1/1/1901', 'MM/DD/YYYY'), 'U AND M FAMILY EYECARE PC', 'HMX8',
'Y', 'F', '212218', '970 Mansell Rd', 'Roswell',
'GA', '300761506', '1918', '7496', '178',
'182', '2', '72', '7C19DED88A90B780E6CCE3D06EBF8D30', 'C92B42F8624E3DC4F2C0AC7D93E54D46',
'28D9E4B6C1721073C77773329F5F7FD7CF931E62', '3236FF531316E83E0E9C2C07BE5029C3', 'E175725AACAEBCFD70CDD2EB142FC4A7', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30.28D9E4B6C1721073C77773329F5F7FD7CF931E62.C92B42F8624E3DC4F2C0AC7D93E54D46',
'SYS.1225.C92B42F8624E3DC4F2C0AC7D93E54D46', 'SYS.1225.2.3236FF531316E83E0E9C2C07BE5029C3', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30.E175725AACAEBCFD70CDD2EB142FC4A7', 'BLT', TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
'BLT', TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'N', 19730, TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), '141', 'Org-gender_preference_indicators.csv');
Insert into RAW.DATA_LOAD_LD_WRK
(SUB_ID, JOB_NAME, TAX_ID_NUMBER, NIPPO, ENTITY_TYPE,
FARM_CD, PRIDE_ORG_SPCLTY_CD, PRIDE_EFCTV_DATE, PRIDE_ORG_NM, NETWORK_ID,
NETWORK_DIR_DISPLAY_IND, PATIENT_GENDER_CD, STNDRDZD_ADRS_ID_SRC, ADDRESS_STREET, ADDRESS_CITY,
ADDRESS_STATE, ADDRESS_ZIP_PLUS_4, PRL_TYPE_CD, FED_TAX_ID_TYPE_CD, PADRS_TYPE_CD,
PRIDE_CTGRY_CD, PRIDE_CTGRY_ID, NTWK_KEY, MSTR_PRIDE_ID, STNDRDZD_ADRS_ID,
POA_KEY, POA_NTWK_KEY, PRIDE_ORG_NTWK_KEY, PRIDE_SRC_PK, POA_SRC_PK,
STNDRDZD_ADRS_SRC_PK, POA_NTWK_SRC_PK, PRIDE_ORG_NTWK_SRC_PK, LAST_UPDT_USER_ID, LAST_UPDT_DT,
CREAT_USER_ID, CREAT_DT, ERR_IND, DRVR_ID, DRVR_LOAD_DTM,
WORK_LOAD_DTM, BATCH_ID, FILE_NM)
Values
('<User ID>', 'Update Gender Code', '010553777', '1497813844', 'G',
'52', 'P0N612VX1B', TO_DATE('1/1/1901', 'MM/DD/YYYY'), 'U AND M FAMILY EYECARE PC', 'HMX8',
'Y', 'F', '270318', '3245 Lawrenceville Suwanee Rd', 'Suwanee',
'GA', '300246541', '1918', '7496', '178',
'182', '2', '72', '7C19DED88A90B780E6CCE3D06EBF8D30', '155010F13103918686884E841FCE241E',
'F55C856C38E86067CC06DCE38D6228F53DC1FD08', '0E48E535FD5DB8D315E3D760C6180C27', 'E175725AACAEBCFD70CDD2EB142FC4A7', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30.F55C856C38E86067CC06DCE38D6228F53DC1FD08.155010F13103918686884E841FCE241E',
'SYS.1225.155010F13103918686884E841FCE241E', 'SYS.1225.2.0E48E535FD5DB8D315E3D760C6180C27', 'SYS.1225.2.7C19DED88A90B780E6CCE3D06EBF8D30.E175725AACAEBCFD70CDD2EB142FC4A7', 'BLT', TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
'BLT', TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'N', 19731, TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
TO_TIMESTAMP('5/13/2021 10:37:42.761807 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), '141', 'Org-gender_preference_indicators.csv');
COMMIT;
CREATE TABLE REPLICA.PROV
(
MSTR_PRIDE_ID NUMBER(10) NOT NULL,
PRIDE_CTGRY_CD VARCHAR2(10 BYTE) NOT NULL,
PRIDE_ORGNL_EFCTV_DT DATE NOT NULL,
PRIDE_TRMNTN_DT DATE,
PRIDE_TRMNTN_RSN_CD VARCHAR2(10 BYTE),
NIPPO_RQRD_IND VARCHAR2(1 BYTE) NOT NULL,
NIPPO VARCHAR2(10 BYTE),
NIPPO_TYPE_CD VARCHAR2(10 BYTE),
PRIDE_ORG_SPCLTY_CD VARCHAR2(10 BYTE),
FARM_CD VARCHAR2(10 BYTE) NOT NULL,
PRIDE_ORG_NM VARCHAR2(100 BYTE),
PRIDE_CLSFCTN_CD VARCHAR2(10 BYTE) NOT NULL,
PRIDE_FRST_NM VARCHAR2(100 BYTE),
PRIDE_MID_NM VARCHAR2(100 BYTE),
PRIDE_LAST_NM VARCHAR2(100 BYTE),
GNRTNL_SFX_CD VARCHAR2(10 BYTE),
PRIDE_PRFRD_NM VARCHAR2(100 BYTE),
BRTH_DT DATE,
GNDR_CD VARCHAR2(10 BYTE),
CACTUS_TOKEN_ID VARCHAR2(50 BYTE),
PRIDE_DATA_OWNR_CD VARCHAR2(10 BYTE) NOT NULL,
CREAT_DT TIMESTAMP(6) NOT NULL,
CREAT_USER_ID VARCHAR2(10 BYTE) NOT NULL,
LAST_UPDT_DT TIMESTAMP(6) NOT NULL,
LAST_UPDT_USER_ID VARCHAR2(10 BYTE) NOT NULL,
AUDT_UPDT_RSN_CD VARCHAR2(10 BYTE),
AUDT_UPDT_RQST_ID VARCHAR2(20 BYTE),
PRIDE_ORG_FED_TAX_ID VARCHAR2(9 BYTE)
);
Insert into REPLICA.PROV
(MSTR_PRIDE_ID, PRIDE_CTGRY_CD, PRIDE_ORGNL_EFCTV_DT, PRIDE_TRMNTN_DT, NIPPO_RQRD_IND,
NIPPO, NIPPO_TYPE_CD, FARM_CD, PRIDE_CLSFCTN_CD, PRIDE_FRST_NM,
PRIDE_LAST_NM, BRTH_DT, GNDR_CD, PRIDE_DATA_OWNR_CD, CREAT_DT,
CREAT_USER_ID, LAST_UPDT_DT, LAST_UPDT_USER_ID)
Values
(1000092126, '181', TO_DATE('1/1/1901', 'MM/DD/YYYY'), TO_DATE('12/31/9999', 'MM/DD/YYYY'), 'Y',
'1528043528', '1', '52', '8004', 'VESNA',
'SOLHEIM', TO_DATE('2/5/1973', 'MM/DD/YYYY'), 'F', 'BCBSSC', TO_TIMESTAMP('4/12/2018 3:45:10.717000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
'SPS', TO_TIMESTAMP('2/19/2021 2:51:54.676304 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'MDM');
Insert into REPLICA.PROV
(MSTR_PRIDE_ID, PRIDE_CTGRY_CD, PRIDE_ORGNL_EFCTV_DT, PRIDE_TRMNTN_DT, NIPPO_RQRD_IND,
NIPPO, NIPPO_TYPE_CD, FARM_CD, PRIDE_CLSFCTN_CD, PRIDE_FRST_NM,
PRIDE_MID_NM, PRIDE_LAST_NM, BRTH_DT, GNDR_CD, PRIDE_DATA_OWNR_CD,
CREAT_DT, CREAT_USER_ID, LAST_UPDT_DT, LAST_UPDT_USER_ID)
Values
(1000067168, '181', TO_DATE('1/1/1901', 'MM/DD/YYYY'), TO_DATE('12/31/9999', 'MM/DD/YYYY'), 'Y',
'1639242381', '1', '52', '8004', 'THOMAS',
'J', 'RITTINGER', TO_DATE('6/16/1973', 'MM/DD/YYYY'), 'M', 'ANTHEM',
TO_TIMESTAMP('4/12/2018 3:45:11.160000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS', TO_TIMESTAMP('8/11/2020 1:24:44.601839 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPDS154000');
Insert into REPLICA.PROV
(MSTR_PRIDE_ID, PRIDE_CTGRY_CD, PRIDE_ORGNL_EFCTV_DT, PRIDE_TRMNTN_DT, NIPPO_RQRD_IND,
NIPPO, NIPPO_TYPE_CD, FARM_CD, PRIDE_CLSFCTN_CD, PRIDE_FRST_NM,
PRIDE_MID_NM, PRIDE_LAST_NM, BRTH_DT, GNDR_CD, PRIDE_DATA_OWNR_CD,
CREAT_DT, CREAT_USER_ID, LAST_UPDT_DT, LAST_UPDT_USER_ID)
Values
(1000082795, '181', TO_DATE('1/1/1901', 'MM/DD/YYYY'), TO_DATE('12/31/9999', 'MM/DD/YYYY'), 'Y',
'1922004753', '1', '52', '8004', 'AARON',
'M', 'EPSTEIN', TO_DATE('5/1/1963', 'MM/DD/YYYY'), 'M', 'BCBSSC',
TO_TIMESTAMP('4/12/2018 3:45:11.957000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS', TO_TIMESTAMP('2/19/2021 2:51:54.676304 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'MDM');
COMMIT;
CREATE TABLE RAW.PROA_WORK
(
NIPPO VARCHAR2(255 BYTE),
POA_KEY VARCHAR2(255 BYTE),
MSTR_PRIDE_ID VARCHAR2(255 BYTE),
PRIDE_ORG_FED_TAX_ID VARCHAR2(255 BYTE),
PRIDE_ORG_SPCLTY_CD VARCHAR2(255 BYTE),
PRIDE_CTGRY_CD VARCHAR2(255 BYTE),
STNDRDZD_ADRS_SRC_PK VARCHAR2(255 BYTE),
PL_ID VARCHAR2(255 BYTE),
PADRS_TYPE_CD VARCHAR2(255 BYTE),
PADRS_EFCTV_DT DATE,
STNDRDZD_ADRS_ID VARCHAR2(255 BYTE),
POA_ALT_IDFCTN_KEY VARCHAR2(255 BYTE),
REPLACE_RESOURCE_CD VARCHAR2(255 BYTE),
REPLACE_TYPE_CD VARCHAR2(255 BYTE),
POA_ALT_IDFCTN_EFCTV_DT DATE,
POA_AOF_KEY VARCHAR2(255 BYTE),
AOF_CD VARCHAR2(255 BYTE),
AOF_EFCTV_DT DATE,
LCTN_SRVC_CTGRY_CD VARCHAR2(255 BYTE),
POA_CNTCT_KEY VARCHAR2(255 BYTE),
CNTCT_DLVRY_MCHNSM_TYPE_CD VARCHAR2(255 BYTE),
CNTCT_PUR_CD VARCHAR2(255 BYTE),
POA_CNTCT_EFCTV_DT DATE,
POA_NTWK_KEY VARCHAR2(255 BYTE),
PRIDE_ORG_NTWK_KEY VARCHAR2(255 BYTE),
POA_NTWK_EFCTV_DT DATE,
POA_NTWK_CP_KEY VARCHAR2(255 BYTE),
POA_CP_EFCTV_DT DATE,
POA_NTWK_PGM_KEY VARCHAR2(255 BYTE),
POA_NTWK_PGM_EFCTV_DT DATE,
PGM_ID VARCHAR2(255 BYTE),
POA_NTWK_RA_KEY VARCHAR2(255 BYTE),
RA_RESOURCE_CD VARCHAR2(255 BYTE),
RA_1_ID VARCHAR2(255 BYTE),
RA_EFCTV_DT DATE,
POA_PCTR_KEY VARCHAR2(255 BYTE),
POA_PGM_KEY VARCHAR2(255 BYTE),
POA_PGM_EFCTV_DT DATE,
POA_RA_W_KEY VARCHAR2(255 BYTE),
POA_RMTNC_KEY VARCHAR2(255 BYTE),
RMTNC_ID VARCHAR2(255 BYTE),
POA_RMTNC_EFCTV_DT DATE,
POA_SRC_PK VARCHAR2(255 BYTE),
POA_NTWK_RA_SRC_PK VARCHAR2(255 BYTE),
POA_PGM_SRC_PK VARCHAR2(255 BYTE),
POA_ALT_IDFCTN_SRC_PK VARCHAR2(255 BYTE),
POA_CNTCT_SRC_PK VARCHAR2(255 BYTE),
POA_NTWK_SRC_PK VARCHAR2(255 BYTE),
POA_RA_W_SRC_PK VARCHAR2(255 BYTE),
POA_NTWK_PGM_SRC_PK VARCHAR2(255 BYTE),
POA_AOF_SRC_PK VARCHAR2(255 BYTE),
POA_PCTR_SRC_PK VARCHAR2(255 BYTE),
POA_NTWK_CP_SRC_PK VARCHAR2(255 BYTE),
POA_RMTNC_SRC_PK VARCHAR2(255 BYTE),
PRIDE_SRC_PK VARCHAR2(255 BYTE),
PRIDE_ORG_NTWK_SRC_PK VARCHAR2(255 BYTE),
POFC_CHARSTC_SRC_PK VARCHAR2(255 BYTE),
POFC_CHARSTC_KEY VARCHAR2(255 BYTE),
PRIDE_ORG_ACRDTN_KEY VARCHAR2(255 BYTE),
PRIDE_ORG_ACRDTN_SRC_PK VARCHAR2(255 BYTE)
)
SET DEFINE OFF;
Insert into RAW.PROA_WORK
(NIPPO, POA_KEY, MSTR_PRIDE_ID, PRIDE_ORG_FED_TAX_ID, PRIDE_ORG_SPCLTY_CD,
PRIDE_CTGRY_CD, STNDRDZD_ADRS_SRC_PK, PL_ID, PADRS_TYPE_CD, PADRS_EFCTV_DT,
STNDRDZD_ADRS_ID, POA_NTWK_KEY, PRIDE_ORG_NTWK_KEY, POA_NTWK_EFCTV_DT, POA_SRC_PK,
POA_NTWK_SRC_PK, PRIDE_SRC_PK, PRIDE_ORG_NTWK_SRC_PK)
Values
('1003005299', '6000217020', '1100125664', '010566566', '10017',
'182', 'SYS.1225.271277', '100', '178', TO_DATE('1/1/2010', 'MM/DD/YYYY'),
'271277', '1292367', '240029', TO_DATE('1/1/2010', 'MM/DD/YYYY'), 'SYS.1225.2.1100125664.6000217020',
'SYS.1225.2.1100125664.1292367', 'SYS.1225.2.1100125664', 'SYS.1225.2.1100125664.240029');
Insert into RAW.PROA_WORK
(NIPPO, POA_KEY, MSTR_PRIDE_ID, PRIDE_ORG_FED_TAX_ID, PRIDE_ORG_SPCLTY_CD,
PRIDE_CTGRY_CD, STNDRDZD_ADRS_SRC_PK, PL_ID, PADRS_TYPE_CD, PADRS_EFCTV_DT,
STNDRDZD_ADRS_ID, POA_NTWK_KEY, PRIDE_ORG_NTWK_KEY, POA_NTWK_EFCTV_DT, POA_SRC_PK,
POA_NTWK_SRC_PK, PRIDE_SRC_PK, PRIDE_ORG_NTWK_SRC_PK)
Values
('1003005299', '6000217020', '1100125664', '010566566', '10017',
'182', 'SYS.1225.271277', '100', '178', TO_DATE('1/1/2010', 'MM/DD/YYYY'),
'271277', '1292367', '417446', TO_DATE('1/1/2010', 'MM/DD/YYYY'), 'SYS.1225.2.1100125664.6000217020',
'SYS.1225.2.1100125664.1292367', 'SYS.1225.2.1100125664', 'SYS.1225.2.1100125664.417446');
Insert into RAW.PROA_WORK
(NIPPO, POA_KEY, MSTR_PRIDE_ID, PRIDE_ORG_FED_TAX_ID, PRIDE_ORG_SPCLTY_CD,
PRIDE_CTGRY_CD, STNDRDZD_ADRS_SRC_PK, PL_ID, PADRS_TYPE_CD, PADRS_EFCTV_DT,
STNDRDZD_ADRS_ID, POA_NTWK_KEY, PRIDE_ORG_NTWK_KEY, POA_NTWK_EFCTV_DT, POA_SRC_PK,
POA_NTWK_SRC_PK, PRIDE_SRC_PK, PRIDE_ORG_NTWK_SRC_PK)
Values
('1003005299', '6000217020', '1100125664', '010566566', '10017',
'182', 'SYS.1225.271277', '100', '178', TO_DATE('1/1/2010', 'MM/DD/YYYY'),
'271277', '1733734', '240029', TO_DATE('1/1/2010', 'MM/DD/YYYY'), 'SYS.1225.2.1100125664.6000217020',
'SYS.1225.2.1100125664.1733734', 'SYS.1225.2.1100125664', 'SYS.1225.2.1100125664.240029');
COMMIT;
CREATE TABLE REPLICA.RL_RELATION_ADDR
(
RL_RELATION_ADDR_KEY NUMBER(22),
MSTR_PRIDE_ID NUMBER(10) NOT NULL,
RLTD_MSTR_PRIDE_ID NUMBER(10) NOT NULL,
POA_KEY NUMBER(22) NOT NULL,
PRL_TYPE_CD VARCHAR2(10 BYTE) NOT NULL,
RL_RELATION_ADDR_EFCTV_DT DATE NOT NULL,
PRMRY_PADRS_IND VARCHAR2(1 BYTE) NOT NULL,
RL_RELATION_ADDR_TRMNTN_DT DATE,
RL_RELATION_ADDR_TRMNTN_RSN_CD VARCHAR2(10 BYTE),
CREAT_DT TIMESTAMP(6) NOT NULL,
CREAT_USER_ID VARCHAR2(10 BYTE) NOT NULL,
LAST_UPDT_DT TIMESTAMP(6) NOT NULL,
LAST_UPDT_USER_ID VARCHAR2(10 BYTE) NOT NULL,
AUDT_UPDT_RSN_CD VARCHAR2(10 BYTE),
AUDT_UPDT_RQST_ID VARCHAR2(20 BYTE)
)
Insert into REPLICA.RL_RELATION_ADDR
(RL_RELATION_ADDR_KEY, MSTR_PRIDE_ID, RLTD_MSTR_PRIDE_ID, POA_KEY, PRL_TYPE_CD,
RL_RELATION_ADDR_EFCTV_DT, PRMRY_PADRS_IND, RL_RELATION_ADDR_TRMNTN_DT, CREAT_DT, CREAT_USER_ID,
LAST_UPDT_DT, LAST_UPDT_USER_ID)
Values
(1000000043, 1000074131, 1000081421, 1000104552, '1918',
TO_DATE('4/1/2002', 'MM/DD/YYYY'), 'N', TO_DATE('12/31/9999', 'MM/DD/YYYY'), TO_TIMESTAMP('4/12/2018 8:33:05.255000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS',
TO_TIMESTAMP('4/12/2018 8:33:05.255000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS');
Insert into REPLICA.RL_RELATION_ADDR
(RL_RELATION_ADDR_KEY, MSTR_PRIDE_ID, RLTD_MSTR_PRIDE_ID, POA_KEY, PRL_TYPE_CD,
RL_RELATION_ADDR_EFCTV_DT, PRMRY_PADRS_IND, RL_RELATION_ADDR_TRMNTN_DT, CREAT_DT, CREAT_USER_ID,
LAST_UPDT_DT, LAST_UPDT_USER_ID)
Values
(1000000050, 1000074131, 1000094795, 1000104535, '1918',
TO_DATE('4/1/2002', 'MM/DD/YYYY'), 'N', TO_DATE('12/31/9999', 'MM/DD/YYYY'), TO_TIMESTAMP('4/12/2018 8:33:05.700000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS',
TO_TIMESTAMP('4/12/2018 8:33:05.700000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS');
Insert into REPLICA.RL_RELATION_ADDR
(RL_RELATION_ADDR_KEY, MSTR_PRIDE_ID, RLTD_MSTR_PRIDE_ID, POA_KEY, PRL_TYPE_CD,
RL_RELATION_ADDR_EFCTV_DT, PRMRY_PADRS_IND, RL_RELATION_ADDR_TRMNTN_DT, CREAT_DT, CREAT_USER_ID,
LAST_UPDT_DT, LAST_UPDT_USER_ID)
Values
(1000000057, 1000074131, 1000086003, 1000104552, '1918',
TO_DATE('4/1/2002', 'MM/DD/YYYY'), 'N', TO_DATE('12/31/9999', 'MM/DD/YYYY'), TO_TIMESTAMP('4/12/2018 8:33:06.244000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS',
TO_TIMESTAMP('4/12/2018 8:33:06.244000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS');
COMMIT;
CREATE TABLE REPLICA.RL_RELATION_ADDR_NTWK
(
RL_RELATION_ADDR_NTWK_KEY NUMBER(22),
RL_RELATION_ADDR_KEY NUMBER(22) NOT NULL,
POA_NTWK_KEY NUMBER(22) NOT NULL,
RL_RELATION_ADDR_NTWK_EFCTV_DT DATE NOT NULL,
ROLE_CD VARCHAR2(10 BYTE),
DIR_DSPLY_IND VARCHAR2(1 BYTE) NOT NULL,
MAX_MBR_CNT NUMBER(5),
RL_RELATION_ADDR_NTWK_TRMNTN_DT DATE,
RL_RELATION_ADDR_NTWK_TRMNTN_RSN_CD VARCHAR2(10 BYTE),
CREAT_DT TIMESTAMP(6) NOT NULL,
CREAT_USER_ID VARCHAR2(10 BYTE) NOT NULL,
LAST_UPDT_DT TIMESTAMP(6) NOT NULL,
LAST_UPDT_USER_ID VARCHAR2(10 BYTE) NOT NULL,
AUDT_UPDT_RSN_CD VARCHAR2(10 BYTE),
AUDT_UPDT_RQST_ID VARCHAR2(20 BYTE),
CLM_TFD_NBR INTEGER,
MIN_AGE_NBR NUMBER(3),
MAX_AGE_NBR NUMBER(3),
PAT_GNDR_CD VARCHAR2(10 BYTE),
DIR_SPRSN_RSN_CD VARCHAR2(10 BYTE)
)
SET DEFINE OFF;
Insert into REPLICA.RL_RELATION_ADDR_NTWK
(RL_RELATION_ADDR_NTWK_KEY, RL_RELATION_ADDR_KEY, POA_NTWK_KEY, RL_RELATION_ADDR_NTWK_EFCTV_DT, ROLE_CD,
DIR_DSPLY_IND, RL_RELATION_ADDR_NTWK_TRMNTN_DT, RL_RELATION_ADDR_NTWK_TRMNTN_RSN_CD, CREAT_DT, CREAT_USER_ID,
LAST_UPDT_DT, LAST_UPDT_USER_ID, PAT_GNDR_CD)
Values
(1962, 1000001851, 230330, TO_DATE('7/1/2015', 'MM/DD/YYYY'), 'S',
'N', TO_DATE('12/27/2015', 'MM/DD/YYYY'), '8016', TO_TIMESTAMP('4/12/2018 8:33:46.225000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS',
TO_TIMESTAMP('8/13/2020 12:20:29.061000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'MDX', '8002');
Insert into REPLICA.RL_RELATION_ADDR_NTWK
(RL_RELATION_ADDR_NTWK_KEY, RL_RELATION_ADDR_KEY, POA_NTWK_KEY, RL_RELATION_ADDR_NTWK_EFCTV_DT, ROLE_CD,
DIR_DSPLY_IND, RL_RELATION_ADDR_NTWK_TRMNTN_DT, RL_RELATION_ADDR_NTWK_TRMNTN_RSN_CD, CREAT_DT, CREAT_USER_ID,
LAST_UPDT_DT, LAST_UPDT_USER_ID, MIN_AGE_NBR, MAX_AGE_NBR, PAT_GNDR_CD)
Values
(1967, 1000001864, 230598, TO_DATE('8/1/2013', 'MM/DD/YYYY'), 'P',
'Y', TO_DATE('3/14/2018', 'MM/DD/YYYY'), '10054', TO_TIMESTAMP('4/12/2018 8:33:46.285000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS',
TO_TIMESTAMP('8/11/2020 12:07:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPDS143562', 0, 18, '8002');
Insert into REPLICA.RL_RELATION_ADDR_NTWK
(RL_RELATION_ADDR_NTWK_KEY, RL_RELATION_ADDR_KEY, POA_NTWK_KEY, RL_RELATION_ADDR_NTWK_EFCTV_DT, ROLE_CD,
DIR_DSPLY_IND, MAX_MBR_CNT, RL_RELATION_ADDR_NTWK_TRMNTN_DT, CREAT_DT, CREAT_USER_ID,
LAST_UPDT_DT, LAST_UPDT_USER_ID, MIN_AGE_NBR, MAX_AGE_NBR, PAT_GNDR_CD)
Values
(1969, 1000001855, 230322, TO_DATE('3/14/2017', 'MM/DD/YYYY'), 'P',
'Y', 250, TO_DATE('12/31/9999', 'MM/DD/YYYY'), TO_TIMESTAMP('4/12/2018 8:33:46.304000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPS',
TO_TIMESTAMP('8/11/2020 12:07:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'SPDS143562', 0, 999, '8002');
COMMIT;
Have written a Procedure as below which is taking very long time to fetch and load the data.
CREATE OR REPLACE PROCEDURE RAW."SP_RELATION_ADDR_WRK_LD" (
RET_MSG OUT VARCHAR2
) AS
V_PROC_NAME VARCHAR2(200) := 'SP_RELATION_ADDR_WRK_LD';
V_RESOURCE_CD NUMBER := 1225;
V_STEP_NAME VARCHAR2(50) := 'BLTZ_MDMLZ';
V_ZONE_CD VARCHAR2(10) := 'LZ';
V_CRRNT_BTCH_ID VARCHAR2(10);
V_BATCHID_OUT NUMBER;
V_BATCHSTEPID_OUT NUMBER;
V_ERRORCD_OUT NUMBER;
V_ERRORMSG_OUT VARCHAR2(2000);
V_ERRMSG VARCHAR2(5000);
BEGIN
--GET LATEST BATCH ID FOR THIS SOURCE
V_CRRNT_BTCH_ID := SPSMDMRF.BTCH_PRCS.GETCURRENTBTCHID(V_RESOURCE_CD);
-- INSERTING NEW ENTRY INTO BATCH STEP LOG
SPSMDMRF.BTCH_PRCS.LD_BTCH_STEP_LOG(V_RESOURCE_CD, V_STEP_NAME, V_PROC_NAME, V_ZONE_CD, V_BATCHID_OUT,
V_BATCHSTEPID_OUT, V_ERRORCD_OUT, V_ERRORMSG_OUT);
IF V_ERRORCD_OUT <> 0 THEN
V_ERRMSG := 'BTCH_STEP_LOG INSERT FOR '
|| V_PROC_NAME
|| ' FAILED WITH ERROR# '
|| V_ERRORCD_OUT
|| ' AND ERRMSG: "'
|| V_ERRORMSG_OUT
|| '" FOR BATCH# '
|| V_BATCHID_OUT
|| ' AND STEP# '
|| V_BATCHSTEPID_OUT
|| '.';
DBMS\_OUTPUT.PUT\_LINE(V\_ERRMSG);
RAISE\_APPLICATION\_ERROR(-20000, V\_ERRMSG);
ELSE
DBMS_OUTPUT.PUT_LINE('BTCH_STEP_LOG INSERT FOR '
|| V_PROC_NAME
|| ' SUCCEEDED FOR BATCH# '
|| V_BATCHID_OUT
|| ' AND STEP# '
|| V_BATCHSTEPID_OUT
|| '.');
END IF;
EXECUTE IMMEDIATE ( 'TRUNCATE TABLE RAW.RL_RELATION_ADDR_WRK DROP STORAGE' );
COMMIT;
DBMS_OUTPUT.PUT_LINE('insert started');
INSERT /*+ PARALLEL(4) */ INTO RAW.RL_RELATION_ADDR_WRK(
NIPPO
,MSTR_PRIDE_ID
,PRIDE_ORG_FED_TAX_ID
,PRIDE_ORG_SPCLTY_CD
,GROUP_RELATION_NIPPO
,RLTD_MSTR_PRIDE_ID
,RL_RELATION_ADDR_KEY
,RL_RELATION_ADDR_EFCTV_DT
,RL_RELATION_ADDR_NTWK_KEY
,RL_RELATION_ADDR_NTWK_EFCTV_DT
,RL_RELATION_ADDR_SRC_PK
,RL_RELATION_ADDR_NTWK_SRC_PK
,POA_KEY
,POA_NTWK_KEY
,POA_SRC_PK
,POA_NTWK_SRC_PK
,PRIDE_SRC_PK
,REL_PRIDE_SRC_PK
,BATCH_ID
,ERR_IND
,WORK_LOAD_DTM
)
SELECT /*+ PARALLEL(4) */ DISTINCT
X.NIPPO
,X.MSTR_PRIDE_ID
,X.PRIDE_ORG_FED_TAX_ID
,X.PRIDE_ORG_SPCLTY_CD
,X.GROUP_RELATION_NIPPO
,X.RLTD_MSTR_PRIDE_ID
,X.RL_RELATION_ADDR_KEY
,X.RL_RELATION_ADDR_EFCTV_DT
,X.RL_RELATION_ADDR_NTWK_KEY
,X.RL_RELATION_ADDR_NTWK_EFCTV_DT
,X.RL_RELATION_ADDR_SRC_PK
,X.RL_RELATION_ADDR_NTWK_SRC_PK
,X.POA_KEY
,X.POA_NTWK_KEY
,X.POA_SRC_PK
,X.POA_NTWK_SRC_PK
,X.PRIDE_SRC_PK
,X.REL_PRIDE_SRC_PK
,X.BATCH_ID
,X.ERR_IND
,X.WORK_LOAD_DTM
FROM(
SELECT /*+DRIVING_SITE(RL_RELATION_ADDR,RL_RELATION_ADDR_NTWK)*/ DISTINCT
WRK_P.NIPPO ,
WRK_RP.MSTR_PRIDE_ID,
WRK.TAX_ID_NUMBER AS PRIDE_ORG_FED_TAX_ID,
WRK_RP.PRIDE_ORG_SPCLTY_CD,
WRK.GROUP_RELATION_NIPPO,
WRK_P.MSTR_PRIDE_ID AS RLTD_MSTR_PRIDE_ID,
B.RL_RELATION_ADDR_KEY,
B.RL_RELATION_ADDR_EFCTV_DT,
C.RL_RELATION_ADDR_NTWK_KEY,
C.RL_RELATION_ADDR_NTWK_EFCTV_DT,
CASE WHEN B.RL_RELATION_ADDR_KEY IS NULL THEN NULL ELSE 'SYS.1225.2'||WRK_RP.MSTR_PRIDE_ID || '.' || B.RL_RELATION_ADDR_KEY END AS RL_RELATION_ADDR_SRC_PK,
CASE WHEN C.RL_RELATION_ADDR_NTWK_KEY IS NULL THEN NULL ELSE 'SYS.1225.2'||WRK_RP.MSTR_PRIDE_ID || '.' || C.RL_RELATION_ADDR_NTWK_KEY END AS RL_RELATION_ADDR_NTWK_SRC_PK,
WRK_POA.POA_KEY AS POA_KEY,
WRK_POA.POA_NTWK_KEY AS POA_NTWK_KEY,
WRK_POA.POA_SRC_PK,
WRK_POA.POA_NTWK_SRC_PK,
CASE WHEN WRK_P.MSTR_PRIDE_ID IS NULL THEN NULL ELSE TO_CHAR('SYS.1225.1.' || NVL(WRK_P.MSTR_PRIDE_ID,'')) END AS PRIDE_SRC_PK,
CASE WHEN WRK_RP.MSTR_PRIDE_ID IS NULL THEN NULL ELSE 'SYS.1225.1.'||WRK_RP.MSTR_PRIDE_ID END AS REL_PRIDE_SRC_PK,
WRK.ERR_IND AS ERR_IND,
WRK.BATCH_ID AS BATCH_ID,
CURRENT_TIMESTAMP AS WORK_LOAD_DTM
FROM RAW.DATA_LOAD_LD_WRK WRK
LEFT OUTER JOIN (SELECT * FROM REPLICA.PROV WHERE PRIDE_CTGRY_CD=181) WRK_P ON WRK.NIPPO =WRK_P.NIPPO
LEFT OUTER JOIN REPLICA.PROV WRK_RP ON WRK_RP.NIPPO=WRK.GROUP_RELATION_NIPPO
LEFT OUTER JOIN RAW.PROA_WORK WRK_POA ON WRK_POA.MSTR_PRIDE_ID = WRK_RP.MSTR_PRIDE_ID
LEFT OUTER JOIN REPLICA.RL_RELATION_ADDR B ON TO_CHAR(B.POA_KEY)= WRK_POA.POA_KEY AND TO_CHAR(B.MSTR_PRIDE_ID)=WRK_RP.MSTR_PRIDE_ID
LEFT OUTER JOIN REPLICA.RL_RELATION_ADDR_NTWK C ON C.RL_RELATION_ADDR_KEY =B.RL_RELATION_ADDR_KEY
WHERE WRK.ENTITY_TYPE IN ('P')
AND WRK.ERR_IND='N'
AND WRK.BATCH_ID='100'
)X;
COMMIT;
INSERT /*+ parallel(4) */ INTO RAW.RL_RELATION_ADDR_WRK(
NIPPO
,MSTR_PRIDE_ID
,PRIDE_ORG_FED_TAX_ID
,PRIDE_ORG_SPCLTY_CD
,GROUP_RELATION_NIPPO
,RLTD_MSTR_PRIDE_ID
,RL_RELATION_ADDR_KEY
,RL_RELATION_ADDR_EFCTV_DT
,RL_RELATION_ADDR_NTWK_KEY
,RL_RELATION_ADDR_NTWK_EFCTV_DT
,RL_RELATION_ADDR_SRC_PK
,RL_RELATION_ADDR_NTWK_SRC_PK
,POA_KEY
,POA_NTWK_KEY
,POA_SRC_PK
,POA_NTWK_SRC_PK
,PRIDE_SRC_PK
,REL_PRIDE_SRC_PK
,BATCH_ID
,ERR_IND
,WORK_LOAD_DTM
)
SELECT /*+ parallel(4) */ DISTINCT
X.NIPPO
,X.MSTR_PRIDE_ID
,X.PRIDE_ORG_FED_TAX_ID
,X.PRIDE_ORG_SPCLTY_CD
,X.GROUP_RELATION_NIPPO
,X.RLTD_MSTR_PRIDE_ID
,X.RL_RELATION_ADDR_KEY
,X.RL_RELATION_ADDR_EFCTV_DT
,X.RL_RELATION_ADDR_NTWK_KEY
,X.RL_RELATION_ADDR_NTWK_EFCTV_DT
,X.RL_RELATION_ADDR_SRC_PK
,X.RL_RELATION_ADDR_NTWK_SRC_PK
,X.POA_KEY
,X.POA_NTWK_KEY
,X.POA_SRC_PK
,X.POA_NTWK_SRC_PK
,X.PRIDE_SRC_PK
,X.REL_PRIDE_SRC_PK
,X.BATCH_ID
,X.ERR_IND
,X.WORK_LOAD_DTM
FROM(
SELECT /\*+DRIVING\_SITE(RL\_RELATION\_ADDR,RL\_RELATION\_ADDR\_NTWK)\*/ DISTINCT
WRK\_P.NIPPO AS NIPPO,
WRK\_P.MSTR\_PRIDE\_ID AS MSTR\_PRIDE\_ID,
WRK\_P.PRIDE\_ORG\_FED\_TAX\_ID AS PRIDE\_ORG\_FED\_TAX\_ID,
WRK\_P.PRIDE\_ORG\_SPCLTY\_CD,
NULL AS GROUP\_RELATION\_NIPPO,
B.RLTD\_MSTR\_PRIDE\_ID AS RLTD\_MSTR\_PRIDE\_ID,
B.RL\_RELATION\_ADDR\_KEY AS RL\_RELATION\_ADDR\_KEY,
B.RL\_RELATION\_ADDR\_EFCTV\_DT,
C.RL\_RELATION\_ADDR\_NTWK\_KEY AS RL\_RELATION\_ADDR\_NTWK\_KEY ,
C.RL\_RELATION\_ADDR\_NTWK\_EFCTV\_DT,
CASE WHEN TO\_CHAR(B.RL\_RELATION\_ADDR\_KEY) IS NULL THEN NULL ELSE 'SYS.1225.2'||WRK\_P.MSTR\_PRIDE\_ID || '.' || TO\_CHAR(B.RL\_RELATION\_ADDR\_KEY) END AS RL\_RELATION\_ADDR\_SRC\_PK,
CASE WHEN TO\_CHAR(C.RL\_RELATION\_ADDR\_NTWK\_KEY) IS NULL THEN NULL ELSE 'SYS.1225.2'||WRK\_P.MSTR\_PRIDE\_ID || '.' || TO\_CHAR(C.RL\_RELATION\_ADDR\_NTWK\_KEY) END AS RL\_RELATION\_ADDR\_NTWK\_SRC\_PK,
WRK\_POA.POA\_KEY AS POA\_KEY,
WRK\_POA.POA\_NTWK\_KEY AS POA\_NTWK\_KEY,
WRK\_POA.POA\_SRC\_PK,
WRK\_POA.POA\_NTWK\_SRC\_PK,
CASE WHEN WRK\_P.MSTR\_PRIDE\_ID IS NULL THEN NULL ELSE TO\_CHAR('SYS.1225.2.' || NVL(WRK\_P.MSTR\_PRIDE\_ID,'')) END AS PRIDE\_SRC\_PK,
CASE WHEN B.RLTD\_MSTR\_PRIDE\_ID IS NULL THEN NULL ELSE 'SYS.1225.1.'||B.RLTD\_MSTR\_PRIDE\_ID END AS REL\_PRIDE\_SRC\_PK,
WRK.ERR\_IND AS ERR\_IND,
WRK.BATCH\_ID AS BATCH\_ID,
CURRENT\_TIMESTAMP AS WORK\_LOAD\_DTM
FROM RAW.DATA\_LOAD\_LD\_WRK WRK
LEFT OUTER JOIN (SELECT \* FROM REPLICA.PROV WHERE PRIDE\_CTGRY\_CD=182) WRK\_P ON WRK.NIPPO =WRK\_P.NIPPO
LEFT OUTER JOIN RAW.PROA\_WORK WRK\_POA ON WRK\_P.MSTR\_PRIDE\_ID =WRK\_POA.MSTR\_PRIDE\_ID
LEFT OUTER JOIN REPLICA.RL\_RELATION\_ADDR B ON TO\_CHAR(B.MSTR\_PRIDE\_ID)= WRK\_P.MSTR\_PRIDE\_ID AND TO\_CHAR(B.POA\_KEY)=WRK\_POA.POA\_KEY
LEFT OUTER JOIN REPLICA.RL\_RELATION\_ADDR\_NTWK C ON C.RL\_RELATION\_ADDR\_KEY =B.RL\_RELATION\_ADDR\_KEY
WHERE WRK.ENTITY\_TYPE IN ('G','F')
AND WRK.ERR\_IND='N'
AND WRK.BATCH\_ID='100'
)X;
COMMIT;
DBMS_OUTPUT.PUT_LINE('||======INSERT STMNT CMPLTED======||');
COMMIT;
DBMS_OUTPUT.PUT_LINE('||======COMMIT STMNT CMPLTED======||');
--RESETTING BATCH UPDATE OUT PARAMETERS
V_ERRORCD_OUT := NULL;
V_ERRORMSG_OUT := NULL;
V_ERRMSG := NULL;
--UPDATING BATCH STEP LOG TO STATUS COMPLETED
SPSMDMRF.BTCH_PRCS.UPD_BTCH_STEP_LOG(V_RESOURCE_CD, V_STEP_NAME, V_PROC_NAME, V_ZONE_CD, 'C',
V_BATCHID_OUT, V_BATCHSTEPID_OUT, V_ERRORCD_OUT, V_ERRORMSG_OUT);
IF V_ERRORCD_OUT <> 0 THEN
V_ERRMSG := 'BTCH_STEP_LOG UPDATE FOR '
|| V_PROC_NAME
|| ' WITH BTCH_STEP_STTS = C FAILED WITH ERROR# '
|| V_ERRORCD_OUT
|| ' AND ERRMSG: "'
|| V_ERRORMSG_OUT
|| '". PLEASE RESET THE BTCH_STEP_LOG.BTCH_STEP_STTS FOR BATCH# '
|| V_BATCHID_OUT
|| ' AND STEP# '
|| V_BATCHSTEPID_OUT
|| '.';
DBMS\_OUTPUT.PUT\_LINE(V\_ERRMSG);
RAISE\_APPLICATION\_ERROR(-20001, V\_ERRMSG);
ELSE
DBMS_OUTPUT.PUT_LINE('BTCH_STEP_LOG UPDATE FOR '
|| V_PROC_NAME
|| ' WITH BTCH_STEP_STTS = C SUCCEEDED FOR BATCH# '
|| V_BATCHID_OUT
|| ' AND STEP# '
|| V_BATCHSTEPID_OUT
|| '.');
END IF;
RET_MSG := '0';
DBMS_OUTPUT.PUT_LINE(RET_MSG);
--
EXCEPTION
WHEN OTHERS THEN
RET_MSG := SQLERRM;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
/
<code>
Thanks
Amy.