[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rpad(lookup_code,10)||meaning LINEX from igs_lookups_view where lookup_code = p_err_cd
and lookup_type = cp_lookup_type;
SELECT 'X'
FROM igs_lookup_values where
lookup_code = cp_lookup_code AND
lookup_type = cp_lookup_type AND
enabled_flag = cp_enabled_flag;
select 'X' from
igs_or_org_inst_type where --ssawhney, view to table
institution_type = cp_institution_type
and close_ind = cp_close_ind;
cp_close_ind igs_or_inst_stat.closed_ind%TYPE) is select 'X' from
igs_or_inst_stat where
institution_status = cp_institution_stat
and closed_ind = cp_close_ind;
select 'X' from
igs_ad_code_classes acc --ssawhney, view to table
where class = cp_class and
NVL(closed_ind,cp_closed_ind)=cp_closed_ind and
code_id= cp_sec_school_location_id;
Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master
which includes the existing institution cd and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
***************************************************************/
CURSOR c_inst_cur (cp_data_source VARCHAR2,
cp_batch_id NUMBER,
cp_status IGS_OR_INST_INT.STATUS%TYPE) IS
SELECT IO.*
FROM IGS_OR_INST_INT IO
WHERE IO.STATUS = cp_status AND
IO.DATA_SOURCE_ID = cp_data_source AND
IO.BATCH_ID = TO_NUMBER(cp_batch_id) ;
SELECT orcv.crosswalk_id, orcv.crosswalk_dtl_id, orcv.inst_code
FROM IGS_OR_CWLK_V ORCV
WHERE ORCV.ALT_ID_TYPE = cp_data_source AND
ORCV.ALT_ID_VALUE = cp_data_src_val ;
SELECT 'Y'
FROM igs_pe_hz_parties
WHERE oss_org_unit_cd = cp_inst_code;
SELECT party_id
FROM igs_pe_hz_parties
WHERE oss_org_unit_cd = cp_inst_code;
SELECT institution_code, crosswalk_id
FROM IGS_OR_CWLK
WHERE institution_code = cp_inst_code;
UPDATE IGS_OR_INST_INT
SET ERROR_CODE = 'E001',ERROR_TEXT = NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(l_cwlkinst_rec.inst_code, v_inst_rec,l_Errind,l_error_code,l_error_text);
/* Rollback if there was an error during Update Institutions */
IF l_Errind = 'Y' THEN
ROLLBACK TO s_point;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code,
status = '3'
WHERE interface_id = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL , STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
/* Update the Crosswalk Master with the newly created Institution Code if no error has occured
during creation of new institution. If an error has occured then rollback to savepoint s_point */
IF l_Errind = 'Y' THEN
ROLLBACK TO s_point;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,l_Newinstcd,l_Errind);
UPDATE IGS_OR_INST_INT
SET error_code = 'E040', error_text= NULL , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET ERROR_CODE = l_error_code,
ERROR_TEXT =NULL,
STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET ERROR_CODE = 'E006',error_text = NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
/* Update the Crosswalk Table if the Previous update is successful, else rollback to savepoint */
IF l_Errind = 'Y' THEN
ROLLBACK TO s_point;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Crosswalk_master(l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
IGS_OR_INST_IMP_002.Update_Crosswalk_master(l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
UPDATE IGS_OR_INST_INT
SET ERROR_CODE = 'E040', ERROR_TEXT=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET ERROR_CODE = l_error_code,
ERROR_TEXT =NULL,
STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET ERROR_CODE = 'E004',error_text =NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007',error_text=NULL , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code,
error_text=NULL ,
status = '3'
WHERE interface_id = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E006', error_text=NULL , STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E004' , error_text =NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET ERROR_code = 'E007', error_text = NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code,
error_text = NULL,
status = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
delete_log_int_rec(p_batch_id);
Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master
which includes the existing institution cd and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
***************************************************************/
CURSOR c_inst_cur(cp_status IGS_OR_INST_INT.STATUS%TYPE,
cp_data_source VARCHAR2,
cp_ds_match VARCHAR2,
cp_batch_id NUMBER) IS
SELECT *
FROM IGS_OR_INST_INT IO
WHERE IO.STATUS = cp_status AND
IO.DATA_SOURCE_ID = cp_data_source AND
cp_ds_match = NVL(IO.ALT_ID_TYPE,cp_ds_match) AND
IO.BATCH_ID = cp_batch_id ;
SELECT crosswalk_id, crosswalk_dtl_id, inst_code
FROM IGS_OR_CWLK_V ORCV
WHERE ORCV.ALT_ID_TYPE = p_data_source AND
ORCV.ALT_ID_VALUE = p_data_src_val ;
SELECT 'Y'
FROM igs_pe_hz_parties
WHERE oss_org_unit_cd = cp_inst_code;
SELECT crosswalk_id
FROM IGS_OR_CWLK
WHERE institution_code = cp_inst_cd;
SELECT party_id
FROM igs_pe_hz_parties
WHERE oss_org_unit_cd = cp_inst_code;
SELECT institution_code, crosswalk_id
FROM IGS_OR_CWLK
WHERE institution_code = cp_inst_code;
UPDATE IGS_OR_INST_INT
SET error_code = 'E001', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(l_cwlkinst_rec.inst_code, v_inst_rec,l_Errind,l_error_code,l_error_text);
/* Create a Record if the above Update is Successful , else rollback to the savepoint */
IF l_Errind = 'Y' THEN
ROLLBACK TO s_point;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007' , error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code,error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,l_Newinstcd,l_Errind);
UPDATE IGS_OR_INST_INT
SET error_code = 'E040', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E006',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
UPDATE IGS_OR_INST_INT
SET error_code = 'E040', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E041', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E042', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
delete_log_int_rec(p_batch_id);
Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master
which includes the existing institution cd and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
In the Exception section checked for the invalid_number exception, as if the alternate_id_value
is not neumeric we need to log a message stating that it should be neumeric only.
Created a new cursor c_neumeric_test to check whether the Alternate Id value is neumeric or not.
In cursor c_record_found and c_inst_code removed the to_number function.
gmaheswa 24 March 2006 Bug 3370808 Update interface record with E043 only when error code is null.
***************************************************************/
CURSOR c_inst_cur(cp_status IGS_OR_INST_INT.STATUS%TYPE,
cp_data_source VARCHAR2,
cp_ds_match VARCHAR2,
cp_batch_id VARCHAR2) IS
SELECT *
FROM IGS_OR_INST_INT IO
WHERE IO.STATUS = cp_status AND
IO.DATA_SOURCE_ID = cp_data_source AND
cp_ds_match = NVL(IO.ALT_ID_TYPE,cp_ds_match) AND
IO.BATCH_ID = cp_batch_id;
SELECT crosswalk_id, crosswalk_dtl_id,inst_code, ALT_ID_VALUE
FROM IGS_OR_CWLK_V ORCV
WHERE ORCV.ALT_ID_TYPE = cp_data_source AND
ORCV.ALT_ID_VALUE like '%' || cp_data_src_val;
SELECT 'Y'
FROM igs_pe_hz_parties
WHERE oss_org_unit_cd = cp_inst_code;
SELECT crosswalk_id
FROM IGS_OR_CWLK
WHERE institution_code = cp_inst_cd;
SELECT party_id
FROM igs_pe_hz_parties
WHERE oss_org_unit_cd = cp_inst_code;
SELECT institution_code, crosswalk_id
FROM IGS_OR_CWLK
WHERE institution_code = cp_inst_code;
SELECT to_number(cp_data_src_val) FROM DUAL;
UPDATE IGS_OR_INST_INT
SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E052', error_text=NULL, STATUS =3
WHERE INTERFACE_ID = v_inst_record.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E001',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(l_cwlkinst_rec.inst_code, v_inst_rec,l_Errind,l_error_code,l_error_text);
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,l_Newinstcd,l_Errind);
UPDATE IGS_OR_INST_INT
SET error_code = 'E040' ,error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code,error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E001',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code , error_text= l_error_text,STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
UPDATE IGS_OR_INST_INT
SET error_code = 'E040',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = 'E007',error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_error_code, error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = NULL,error_text=NULL, STATUS = '1'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
UPDATE IGS_OR_INST_INT
SET error_code = l_val_err, error_text= NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
IF l_error_code IS NULL THEN -- gmaheswa 3370808 Update interface record only when error code is null.
--Log a message to the Log File in the Conc Manager that the record for numeric match is not found in the crosswalk dtl table
IF gb_write_exception_log1 = TRUE THEN
igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E043');
UPDATE IGS_OR_INST_INT
SET error_code = 'E043', error_text=NULL, STATUS = '3'
WHERE INTERFACE_ID = v_inst_rec.interface_id;
delete_log_int_rec(p_batch_id);
PROCEDURE delete_log_int_rec(p_batch_id IN IGS_OR_INST_INT.BATCH_ID%TYPE) AS
/*************************************************************
Created By :ssaleem
Date Created By : 19-SEP-2003
Purpose : This procedure deletes all the completed records
from the INT tables and updates the status
of master table appropriately. Also it takes
statistics of the operations and logs them.
Know limitations, enhancements or remarks
Remarks:
* If IGS_OR_INST_INT has more than one error in one record,
say for eg one record having both erroneous contact phone
and erroneous statistics details, the record in
IGS_OR_INST_INT will be updated with status 4 - Warning
and with any one of the error code that is first processed,
In the above case it will be E055.
Change History
Who When What
***************************************************************/
CURSOR inst_lookup_cur(cp_lookup_type IGS_LOOKUP_VALUES.LOOKUP_TYPE%TYPE) IS
SELECT lookup_code,meaning
FROM IGS_LOOKUP_VALUES
WHERE
LOOKUP_TYPE = cp_lookup_type;
SELECT RPAD(INTERFACE_ID,12) || ' ' || LPAD(STATUS,7) || ' ' || ERROR_CODE EREC
FROM IGS_OR_INST_INT
WHERE BATCH_ID = cp_batch_id AND
(STATUS = cp_status_error OR STATUS = cp_status_warn);
SELECT COUNT(1) CNT,STATUS STAT
FROM IGS_OR_INST_INT
WHERE BATCH_ID = cp_batch_id
GROUP BY STATUS;
SELECT COUNT(1) CNT,NT.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_INST_NTS_INT NT
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = NT.INTERFACE_ID
GROUP BY NT.STATUS;
SELECT COUNT(1) CNT,STAT.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_INST_STAT_INT STAT
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = STAT.INTERFACE_ID
GROUP BY STAT.STATUS;
SELECT COUNT(1) CNT,SDTL.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_INST_STAT_INT STAT,
IGS_OR_INST_SDTL_INT SDTL
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = STAT.INTERFACE_ID AND
STAT.INTERFACE_INST_STAT_ID = SDTL.INTERFACE_INST_STAT_ID
GROUP BY SDTL.STATUS;
SELECT COUNT(1) CNT,CON.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_INST_CON_INT CON
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = CON.INTERFACE_ID
GROUP BY CON.STATUS;
SELECT COUNT(1) CNT,CPHN.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_INST_CON_INT CON,
IGS_OR_INST_CPHN_INT CPHN
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = CON.INTERFACE_ID AND
CON.INTERFACE_CONTACTS_ID = CPHN.INTERFACE_CONT_ID
GROUP BY CPHN.STATUS;
SELECT COUNT(1) CNT,ADR.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_ADR_INT ADR
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = ADR.INTERFACE_ID
GROUP BY ADR.STATUS;
SELECT COUNT(1) CNT,ADRU.STATUS STAT
FROM IGS_OR_INST_INT INST,
IGS_OR_ADR_INT ADR,
IGS_OR_ADRUSGE_INT ADRU
WHERE INST.BATCH_ID = cp_batch_id AND
INST.INTERFACE_ID = ADR.INTERFACE_ID AND
ADR.INTERFACE_ADDR_ID = ADRU.INTERFACE_ADDR_ID
GROUP BY ADRU.STATUS;
DELETE FROM IGS_OR_INST_CPHN_INT WHERE STATUS = '1';
DELETE FROM IGS_OR_INST_CON_INT CON
WHERE STATUS = '1' AND
NOT EXISTS (SELECT 1
FROM IGS_OR_INST_CPHN_INT CPHN
WHERE CON.INTERFACE_CONTACTS_ID = CPHN.INTERFACE_CONT_ID AND
CPHN.STATUS = '3');
UPDATE IGS_OR_INST_INT INST
SET STATUS = '4',ERROR_CODE = 'E055'
WHERE STATUS = '1' AND
EXISTS (SELECT 1
FROM IGS_OR_INST_CON_INT CON
WHERE CON.INTERFACE_ID = INST.INTERFACE_ID);
DELETE FROM IGS_OR_INST_SDTL_INT WHERE STATUS = '1';
DELETE FROM IGS_OR_INST_STAT_INT STAT
WHERE STATUS = '1' AND
NOT EXISTS (SELECT 1
FROM IGS_OR_INST_SDTL_INT SDTL
WHERE STAT.INTERFACE_INST_STAT_ID = SDTL.INTERFACE_INST_STAT_ID AND
SDTL.STATUS = '3');
UPDATE IGS_OR_INST_INT INST
SET STATUS = '4',ERROR_CODE = 'E056'
WHERE STATUS = '1' AND
EXISTS (SELECT 1
FROM IGS_OR_INST_STAT_INT STAT
WHERE STAT.INTERFACE_ID = INST.INTERFACE_ID);
DELETE FROM IGS_OR_ADRUSGE_INT WHERE STATUS = '1';
DELETE FROM IGS_OR_ADR_INT ADR
WHERE STATUS = '1' AND
NOT EXISTS (SELECT 1
FROM IGS_OR_ADRUSGE_INT ADU
WHERE ADR.INTERFACE_ADDR_ID = ADU.INTERFACE_ADDR_ID AND
ADU.STATUS = '3');
UPDATE IGS_OR_INST_INT INST
SET STATUS = '4',ERROR_CODE = 'E057'
WHERE STATUS = '1' AND
EXISTS (SELECT 1
FROM IGS_OR_ADR_INT ADR
WHERE ADR.INTERFACE_ID = INST.INTERFACE_ID);
DELETE FROM IGS_OR_INST_NTS_INT WHERE STATUS = '1';
UPDATE IGS_OR_INST_INT INST
SET STATUS = '4',ERROR_CODE='E058'
WHERE STATUS = '1' AND
EXISTS (SELECT 1
FROM IGS_OR_INST_NTS_INT NTS
WHERE NTS.INTERFACE_ID = INST.INTERFACE_ID);
DELETE FROM IGS_OR_INST_INT WHERE STATUS = '1';
'igs.plsql.igs_or_inst_imp_001.delete_log_int_rec.others',
SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
END delete_log_int_rec;
|| Purpose : Compares crosswalk inst code with interface table and updates the status accordingly.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
return_value BOOLEAN;
UPDATE igs_or_inst_int
SET status='3',error_code='E059'
WHERE interface_id = p_interface_id;