[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
asbala 21-OCT-2003 Bug 3130316. Import Process - New logic to delete completed records.
***************************************************************/
l_meaning igs_lookup_values.meaning%TYPE;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_type = cp_lookup_type AND
lookup_code = cp_lookup_code;
DELETE FROM igs_ad_imp_near_mtch_all
WHERE interface_id IN
(SELECT interface_id FROM igs_ad_interface_all
WHERE interface_run_id = l_interface_run_id AND
status='2');
UPDATE igs_ad_api_int_all aapi
SET interface_run_id=l_interface_run_id
WHERE aapi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=aapi.interface_id AND
ai.status IN ('1','2') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_ad_stat_int_all adi
SET interface_run_id=l_interface_run_id
WHERE adi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE ai.interface_id=adi.interface_id AND
ai.status IN ('1','2') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_ADDR_INT_ALL ait
SET
interface_run_id=l_interface_run_id
WHERE ait.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=ait.interface_id AND
ai.status IN ('1','2') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_ADDRUSAGE_INT_ALL ait
SET interface_run_id=l_interface_run_id
WHERE ait.status='2' AND
EXISTS (SELECT 1
FROM IGS_AD_ADDR_INT_ALL ai
WHERE
ai.interface_addr_id = ait.interface_addr_id AND
ai.status IN ('1','2') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_INTERFACE_all SET STATUS = '1'
WHERE PERSON_MATCH_IND = '22' AND STATUS = '2'
AND SOURCE_TYPE_ID = P_SOURCE_TYPE_ID
AND BATCH_ID = P_BATCH_ID;
UPDATE IGS_AD_STAT_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_AD_ADDR_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_AD_ADDRUSAGE_INT_all iau SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_AD_API_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_type_int pti
SET interface_run_id=l_interface_run_id
WHERE pti.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pti.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_type_int SET status = '1'
WHERE match_ind = '22' AND status = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_eit_int pei
SET
interface_run_id=l_interface_run_id
WHERE pei.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pei.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_race_int adli
SET
interface_run_id=l_interface_run_id
WHERE adli.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adli.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_eit_int
SET status = '1'
WHERE match_ind = '22' AND
status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_race_int
SET status = '1'
WHERE match_ind = '22' AND
status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_alias_int_all adai
SET
interface_run_id=l_interface_run_id
WHERE adai.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adai.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_ALIAS_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_disablty_int_all adi
SET interface_run_id=l_interface_run_id
WHERE adi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE ai.interface_id=adi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_DISABLTY_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_sn_srvce_int snci
SET interface_run_id=l_interface_run_id
WHERE snci.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
igs_ad_disablty_int_all adi
WHERE ai.interface_id=adi.interface_id AND
adi.INTERFACE_DISABLTY_ID=snci.INTERFACE_DISABLTY_ID AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_sn_srvce_int
SET status = '1'
WHERE match_ind = '22' AND
status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_sn_conct_int psci
SET
interface_run_id=l_interface_run_id
WHERE psci.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
igs_ad_disablty_int_all adi
WHERE
ai.interface_id=adi.interface_id AND
adi.INTERFACE_DISABLTY_ID=psci.INTERFACE_DISABLTY_ID AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_sn_conct_int
SET status = '1'
WHERE match_ind = '22' AND
status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_emp_int_all admpi
SET
interface_run_id=l_interface_run_id
WHERE admpi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=admpi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_EMP_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_PE_VISA_INT pvi
SET
interface_run_id=l_interface_run_id
WHERE pvi.status IN ('1','2') AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pvi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_PE_VISA_INT SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_PE_PASSPORT_INT ppi
SET
interface_run_id=l_interface_run_id
WHERE ppi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=ppi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_PE_PASSPORT_INT SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_PE_VST_HIST_INT pvhi
SET
interface_run_id=l_interface_run_id
WHERE pvhi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
IGS_PE_VISA_INT pi
WHERE
pi.INTERFACE_VISA_ID=pvhi.INTERFACE_VISA_ID AND
ai.interface_id=pi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_PE_VST_HIST_INT SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_PE_EIT_INT pei
SET
interface_run_id=l_interface_run_id
WHERE pei.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pei.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_PE_EIT_INT SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INFORMATION_TYPE = 'PE_INT_PERM_RES'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_citizen_int pci
SET
interface_run_id=l_interface_run_id
WHERE pci.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pci.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_citizen_int SET status = '1'
WHERE match_ind = '22' AND status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_fund_src_int pfsi
SET
interface_run_id=l_interface_run_id
WHERE pfsi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pfsi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_fund_src_int SET status = '1'
WHERE match_ind = '22' AND status = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_immu_dtl_int pidi
SET
interface_run_id=l_interface_run_id
WHERE pidi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pidi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_immu_dtl_int
SET status = '1'
WHERE match_ind = '22' AND
status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_hlth_ins_int_all adhi
SET
interface_run_id=l_interface_run_id
WHERE adhi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adhi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_HLTH_INS_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_military_int_all admi
SET
interface_run_id=l_interface_run_id
WHERE admi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=admi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_MILITARY_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_excurr_int_all adei
SET
interface_run_id=l_interface_run_id
WHERE adei.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adei.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_EXCURR_INT_all SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE IGS_AD_RELATIONS_INT_ALL ari
SET interface_run_id=l_interface_run_id
WHERE ari.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=ari.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_RELATIONS_INT_all iar
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_reladdr_int_all ari1
SET interface_run_id=l_interface_run_id
WHERE ari1.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
IGS_AD_RELATIONS_INT_ALL adi
WHERE
adi.INTERFACE_RELATIONS_ID=ari1.INTERFACE_RELATIONS_ID AND
ai.interface_id=adi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_RELADDR_INT_all iara
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_relemp_int_all ari2
SET interface_run_id=l_interface_run_id
WHERE ari2.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
IGS_AD_RELATIONS_INT_ALL adi
WHERE adi.INTERFACE_RELATIONS_ID=ari2.INTERFACE_RELATIONS_ID AND
ai.interface_id=adi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_RELEMP_INT_all ire
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE Igs_Ad_Relacad_Int_all ari3
SET interface_run_id=l_interface_run_id
WHERE ari3.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
IGS_AD_RELATIONS_INT_ALL adi
WHERE adi.INTERFACE_RELATIONS_ID=ari3.INTERFACE_RELATIONS_ID AND
ai.interface_id=adi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_RELACAD_INT_ALL iara
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_rel_con_int_all ari4
SET
interface_run_id=l_interface_run_id
WHERE ari4.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai,
IGS_AD_RELATIONS_INT_ALL adi
WHERE
adi.INTERFACE_RELATIONS_ID=ari4.INTERFACE_RELATIONS_ID AND
ai.interface_id=adi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_REL_CON_INT_all iarc
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_ath_dtl_int adli
SET
interface_run_id=l_interface_run_id
WHERE adli.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adli.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_ath_dtl_int
SET status = '1'
WHERE match_ind = '22' AND status = '2' AND
INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_ath_prg_int adli
SET
interface_run_id=l_interface_run_id
WHERE adli.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adli.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_ath_prg_int
SET status = '1'
WHERE match_ind = '22' AND status = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_language_int_all adli
SET
interface_run_id=l_interface_run_id
WHERE adli.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adli.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_LANGUAGE_INT_all il
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_contacts_int_all adci
SET
interface_run_id=l_interface_run_id
WHERE adci.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=adci.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_CONTACTS_INT_all ic
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_flny_dtl_int pfi
SET
interface_run_id=l_interface_run_id
WHERE pfi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=pfi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_flny_dtl_int
SET status = '1'
WHERE match_ind = '22' AND status = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_hear_dtl_int phi
SET
interface_run_id=l_interface_run_id
WHERE phi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=phi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_hear_dtl_int
SET status = '1'
WHERE match_ind = '22' AND status = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_housing_int phi
SET
interface_run_id=l_interface_run_id
WHERE phi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=phi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_housing_int
SET status = '1'
WHERE match_ind = '22' AND status = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_ad_acadhonor_int_all ahi
SET
interface_run_id=l_interface_run_id
WHERE ahi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=ahi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE IGS_AD_ACADHONOR_INT_all iah
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_res_dtls_int rdi
SET
interface_run_id=l_interface_run_id
WHERE rdi.status='2' AND
EXISTS (SELECT 1
FROM igs_ad_interface_all ai
WHERE
ai.interface_id=rdi.interface_id AND
ai.status IN ('1','4') AND
ai.interface_run_id=l_interface_run_id);
UPDATE igs_pe_res_dtls_int iah
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
UPDATE igs_pe_Cred_int a
SET interface_run_id = l_interface_run_id
WHERE EXISTS (SELECT 1
FROM igs_ad_interface_all
WHERE interface_run_id = l_interface_run_id
AND interface_id = a.interface_id
AND status IN ('1','4'));
UPDATE igs_ad_acadhis_int_all a
SET interface_run_id = l_interface_run_id,
person_id = (SELECT person_id
FROM igs_ad_interface_all
WHERE interface_id = a.interface_id)
WHERE EXISTS (SELECT 1
FROM igs_ad_interface_all
WHERE interface_run_id = l_interface_run_id
AND interface_id = a.interface_id
AND status IN ('1','4'));
UPDATE igs_pe_privacy_int a
SET interface_run_id = l_interface_run_id
WHERE EXISTS (SELECT 1
FROM igs_ad_interface_all
WHERE interface_run_id = l_interface_run_id
AND interface_id = a.interface_id
AND status IN ('1','4'));
UPDATE igs_pe_privacy_int iah
SET STATUS = '1'
WHERE MATCH_IND = '22' AND STATUS = '2'
AND INTERFACE_RUN_ID = l_interface_run_id;
Purpose : This will delete from all the person related tables as per the record status in the IGS_AD_INTERFACE table.
The delete will happen only if the category for the table is included.
Know limitations, enhancements or remarks
Change History
Who When What
vrathi 08-Jul-2003 Bug:3038248 Delete record from igs_ad_addrusage_int before deleting from igs_ad_addr_int
pkpatel 11-DEC-2003 Bug 2863933 (Removed the individual UPDATE of IGS_AD_INTERFACE_ALL and made it single UPDATE)
Added 3 intermediate COMMIT statements.
nsidana 6/21/2004 Bug 3533035 : First need to update the records in relations_int table to 4 in case any child did not process
successfully. Then we need to delete from relations_int table, the records with status 1.
Previously, the reverse was happening, so the record in parent relations_int table was getting deleted even though
some child errored out.
(reverse chronological order - newest change first)
***************************************************************/
l_prog_label VARCHAR2(4000);
UPDATE igs_ad_interface_all
SET record_status = '1'
WHERE interface_run_id = l_interface_run_id;
DELETE FROM igs_ad_stat_int_all
WHERE status = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_ad_addrusage_int_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_ad_addr_int_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_ad_api_int_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_race_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_eit_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_type_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_ALIAS_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_sn_srvce_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_sn_conct_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_DISABLTY_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_EMP_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_PE_VST_HIST_INT WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
UPDATE igs_pe_visa_int ad
SET status = '4', error_code = 'E347'
WHERE ad.interface_run_id = l_interface_run_id AND
ad.status = '1' AND
EXISTS (SELECT 1 FROM igs_pe_vst_hist_int ai WHERE ad.interface_visa_id = ai.interface_visa_id);
DELETE FROM IGS_PE_VISA_INT WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_PE_PASSPORT_INT WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_citizen_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_fund_src_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_eit_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
-- Delete from the table IGS_AD_HLTH_INS_INT
DELETE FROM IGS_AD_HLTH_INS_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_immu_dtl_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_MILITARY_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_EXCURR_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_PE_RES_DTLS_INT WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_ACADHONOR_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_RELEMP_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_ad_rel_con_int_all WHERE
status = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_ad_reladdr_int_all WHERE
status = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_RELACAD_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
UPDATE IGS_AD_RELATIONS_INT_all ad
SET status = '4', error_code = 'E347'
WHERE ad.interface_run_id = l_interface_run_id AND
ad.status = '1' AND
( EXISTS (SELECT 1 FROM igs_ad_relemp_int_all ai WHERE ad.interface_relations_id = ai.interface_relations_id)
OR EXISTS (SELECT 1 FROM igs_ad_rel_con_int_all ai WHERE ad.interface_relations_id = ai.interface_relations_id)
OR EXISTS (SELECT 1 FROM IGS_AD_RELACAD_INT_all ai WHERE ad.interface_relations_ID = ai.interface_relations_ID)
OR EXISTS (SELECT 1 FROM igs_ad_reladdr_int_all ai WHERE ad.interface_relations_ID = ai.interface_relations_ID )
);
DELETE FROM IGS_AD_RELATIONS_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
-- Delete from the table igs_pe_ath_dtl_int
pe_cat_stats('PERSON_ATHLETICS');
DELETE FROM igs_pe_ath_dtl_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_ath_prg_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
-- Delete from the table igs_pe_housing_int
DELETE FROM igs_pe_housing_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
-- Delete from the table igs_pe_flny_dtl_int
DELETE FROM igs_pe_flny_dtl_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_hear_dtl_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_CONTACTS_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_LANGUAGE_INT_all WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_PE_CRED_INT WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM IGS_AD_ACADHIS_INT_ALL WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
DELETE FROM igs_pe_privacy_int WHERE
STATUS = '1' AND interface_run_id = l_interface_run_id;
UPDATE igs_ad_interface_all ad
SET record_status = '3'
WHERE ad.interface_run_id = l_interface_run_id AND
( EXISTS (SELECT 1 FROM igs_ad_interface_all ai WHERE ad.interface_id = ai.interface_id AND status = '3')
OR EXISTS (SELECT 1 FROM igs_ad_stat_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_addr_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_api_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_race_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_eit_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_type_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_alias_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_disablty_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_emp_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_visa_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_passport_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_citizen_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_fund_src_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_hlth_ins_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_immu_dtl_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_military_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_excurr_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_res_dtls_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_acadhonor_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_relations_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_ath_dtl_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_ath_prg_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_housing_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_flny_dtl_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_hear_dtl_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_contacts_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_language_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_cred_int ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_acadhis_int_all ai WHERE ad.interface_id = ai.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_privacy_int ai WHERE ad.interface_id = ai.interface_id));
Here also the pending records with match_ind 22 are updated to status 1, and this happens as per the
category is included or not.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_prog_label VARCHAR2(4000);
Purpose : This procedure puts the logic for all the person categories to finally update the record status
of the IGS_AD_INTERFACE table. Record Status '1' success and '3' failure.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
-- asbala 15-10-2003 procedure stubbed. the delete logic is implemented differently now. See details
-- in SWS: Import Process Enhancements Build
BEGIN
NULL;
SELECT lookup_type,lookup_code
FROM fnd_lookup_values
WHERE lookup_type = cp_lkup_type AND
view_application_id = cp_application_id AND
security_group_id = cp_security_group_id AND
language = userenv('LANG') AND
enabled_flag = 'Y';
SELECT 'X'
FROM fnd_lookup_values
WHERE lookup_type = cp_lkup_type AND
lookup_code = cp_lkup_code AND
view_application_id = cp_application_id AND
security_group_id = cp_security_group_id AND
language = userenv('LANG') AND
enabled_flag = 'Y';
Purpose : This function is to insert the statistics into igs_ad_imp_stats.
Know limitations, enhancements or remarks
Change History
Who When What
pkpatel 27-Mar-2006 Bug 5114924(Defined variables l_success .. as NUMBER instead of NUMBER(5))
skpandey 25-JAN-2006 Bug#4114660: Used local variable in place of Literals to optimize performance
pkpatel 11-DEC-2003 Bug 2863933 (Added the logic to populate for Credential and Academic History.
Used local variables to populate WHO columns)
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_person_type (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
SELECT count(*) count1,status
FROM IGS_PE_TYPE_INT
WHERE interface_run_id = p_interface_run_id
GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_STAT_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_EIT_INT WHERE interface_run_id = p_interface_run_id AND
information_type IN ('PE_STAT_RES_COUNTRY','PE_STAT_RES_STATE', 'PE_STAT_RES_STATUS') GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_RACE_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_ADDR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1, status FROM IGS_AD_ADDRUSAGE_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_ALIAS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_API_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_DISABLTY_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_SN_SRVCE_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_SN_CONCT_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_EMP_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_VISA_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_PASSPORT_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_VST_HIST_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_EIT_INT WHERE interface_run_id = p_interface_run_id AND
information_type = p_information_type GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_CITIZEN_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_FUND_SRC_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_IMMU_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_HLTH_INS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_MILITARY_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_EXCURR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_RELATIONS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_ATH_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_ATH_PRG_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_LANGUAGE_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_CONTACTS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_FLNY_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_HEAR_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_HOUSING_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_ACADHONOR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_PE_RES_DTLS_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_RELACAD_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_RELADDR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_REL_CON_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_RELEMP_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM IGS_AD_INTERFACE_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM igs_pe_cred_int WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM igs_ad_acadhis_int_all WHERE interface_run_id = p_interface_run_id GROUP BY status;
SELECT count(*) count1,status FROM igs_pe_privacy_int WHERE interface_run_id = p_interface_run_id GROUP BY status;
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
l_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
INSERT INTO IGS_AD_IMP_STATS
(
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES(
l_interface_run_id,
p_source_category,
l_tab,
l_total_rec,
l_warning,
l_success,
l_error,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
);
SELECT api_person_id
FROM IGS_PE_ALT_PERS_ID
WHERE pe_person_id = cp_person_id
AND person_id_type = cp_api_type
AND SYSDATE BETWEEN TRUNC(START_DT) AND NVL(END_DT,SYSDATE);
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = cp_country_code;