[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| Modified the the select statements to use bind variables.
|| Modified the logic for NONE special needs record as per jul'03 special need CCR
|| pkpatel 6-JUN-2003 Bug 2975196
|| Modified evaluation date validation in prc_pe_res_dtls
|| skpandey 11-APR-2006 Bug#5110137: Removed call to upd_res_det procedure
|| (reverse chronological order - newest change first)
*/
cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
SELECT mi.*, i.person_id
FROM igs_ad_acadhonor_int_all mi,igs_ad_interface_all i
WHERE mi.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND i.interface_run_id = cp_interface_run_id
AND mi.status = '2';
SELECT rowid,hi.*
FROM igs_pe_acad_honors hi
WHERE hi.person_id = acadhonor_rec.person_id AND
hi.acad_honor_type = acadhonor_rec.acad_honor_type AND
NVL(hi.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(acadhonor_rec.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD'));
SELECT birth_date birth_dt
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
igs_pe_acad_honors_pkg.insert_row (
x_rowid => l_rowid,
x_acad_honor_id => l_acad_hnr_id,
x_person_id => acadhonor_rec.person_id,
x_acad_honor_type => acadhonor_rec.acad_honor_type ,
x_comments => acadhonor_rec.comments ,
x_honor_date => acadhonor_rec.honor_date ,
x_mode => 'R'
);
UPDATE igs_ad_acadhonor_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = cst_stat_val_2
AND interface_run_id = l_interface_run_id;
UPDATE igs_ad_acadhonor_int_all mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD')));
UPDATE igs_ad_acadhonor_int_all
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status = cst_stat_val_2;
UPDATE igs_ad_acadhonor_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND status = cst_stat_val_2
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
UPDATE igs_ad_acadhonor_int_all mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD'))
AND NVL(UPPER(pe.comments),'*!*') = NVL(UPPER(mi.comments),'*!*')
);
UPDATE igs_ad_acadhonor_int_all mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
DUP_ACAD_HONOR_ID = (SELECT pe.acad_honor_id
FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
WHERE mi.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD')) )
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD')));
igs_pe_acad_honors_pkg.update_row (
x_rowid => chk_dup_rec.rowid,
x_acad_honor_id => chk_dup_rec.acad_honor_id,
x_person_id => acadhonor_rec.person_id,
x_acad_honor_type => acadhonor_rec.acad_honor_type ,
x_comments => NVL(acadhonor_rec.comments ,chk_dup_rec.comments),
x_honor_date => NVL(acadhonor_rec.honor_date,chk_dup_rec.honor_date),
x_mode => 'R'
);
l_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls.exception_update1'||'E014';
igs_pe_acad_honors_pkg.update_row (
x_rowid => chk_dup_rec.rowid,
x_acad_honor_id => chk_dup_rec.acad_honor_id,
x_person_id =>acadhonor_rec.person_id,
x_acad_honor_type => acadhonor_rec.acad_honor_type,
x_comments => NVL(acadhonor_rec.comments ,chk_dup_rec.comments),
x_honor_date => NVL(acadhonor_rec.honor_date,chk_dup_rec.honor_date),
x_mode => 'R'
);
l_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls.exception_update2'||'E014';
UPDATE
igs_ad_acadhonor_int_all
SET
status = l_status,
error_code = l_error_code,
match_ind = l_match_ind
WHERE
interface_acadhonor_id = acadhonor_rec.interface_acadhonor_id;
UPDATE
igs_ad_acadhonor_int_all
SET
status = '3',
error_code = 'E518'
WHERE
interface_acadhonor_id = acadhonor_rec.interface_acadhonor_id;
|| ssawhney update positioning when NOT coming from UCAS changed
|| pkpatel 6-JUN-2003 Bug 2975196
|| Reversed the evaluation date validation. Now it cannot be a future date.
|| Modified E184 to E203 when evaluation date with Birth date fails
|| asbala 3-SEP-2003 Build SWCR01,02
|| Altered parameters of chk_dup_cur and c_null_hdlg_res_cur to reflect the
|| changes in unique index
|| pkpatel 9-Nov-2004 Bug 3993967 (Removed Start/End Date. Included Term)
||--------------------------------------------------------------------------------*/
l_status igs_pe_res_dtls_int.status%TYPE;
SELECT mi.*, i.person_id
FROM igs_pe_res_dtls_int mi,igs_ad_interface_all i
WHERE mi.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND i.interface_run_id = cp_interface_run_id
AND mi.status = '2';
SELECT rowid,hi.*
FROM igs_pe_res_dtls_all hi
WHERE hi.person_id = res_dtls_cur.person_id AND
hi.residency_class_cd = res_dtls_cur.residency_class_cd AND
hi.cal_type = res_dtls_cur.cal_type AND
hi.sequence_number = res_dtls_cur.sequence_number;
SELECT birth_date birth_dt
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT 1
FROM igs_ca_inst_all ca,
igs_ca_type typ,
igs_ca_stat stat
WHERE typ.cal_type=ca.cal_type AND
typ.s_cal_cat = 'LOAD' AND
ca.cal_status = STAT.CAL_STATUS AND
stat.s_cal_status = 'ACTIVE' AND
ca.cal_type = cp_cal_type AND
ca.sequence_number = cp_sequence_number;
UPDATE igs_pe_res_dtls_int
SET status = '3',
error_code = p_error_code
WHERE interface_res_id = res_dtls_rec.interface_res_id;
Igs_Pe_Res_Dtls_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => l_rowid,
X_Resident_Details_Id => l_Resident_Details_Id,
X_Person_Id => res_dtl_rec.Person_Id,
X_Residency_Class_cd => res_dtl_rec.Residency_Class_cd,
X_Residency_Status_cd => res_dtl_rec.Residency_Status_cd,
X_Evaluation_Date => res_dtl_rec.Evaluation_Date,
X_Evaluator => res_dtl_rec.Evaluator,
X_Comments => res_dtl_rec.Comments,
X_Attribute_Category => res_dtl_rec.Attribute_Category,
X_Attribute1 => res_dtl_rec.Attribute1,
X_Attribute2 => res_dtl_rec.Attribute2,
X_Attribute3 => res_dtl_rec.Attribute3,
X_Attribute4 => res_dtl_rec.Attribute4,
X_Attribute5 => res_dtl_rec.Attribute5,
X_Attribute6 => res_dtl_rec.Attribute6,
X_Attribute7 => res_dtl_rec.Attribute7,
X_Attribute8 => res_dtl_rec.Attribute8,
X_Attribute9 => res_dtl_rec.Attribute9,
X_Attribute10 => res_dtl_rec.Attribute10,
X_Attribute11 => res_dtl_rec.Attribute11,
X_Attribute12 => res_dtl_rec.Attribute12,
X_Attribute13 => res_dtl_rec.Attribute13,
X_Attribute14 => res_dtl_rec.Attribute14,
X_Attribute15 => res_dtl_rec.Attribute15,
X_Attribute16 => res_dtl_rec.Attribute16,
X_Attribute17 => res_dtl_rec.Attribute17,
X_Attribute18 => res_dtl_rec.Attribute18,
X_Attribute19 => res_dtl_rec.Attribute19,
X_Attribute20 => res_dtl_rec.Attribute20,
X_cal_type => res_dtl_rec.cal_type,
X_sequence_number => res_dtl_rec.sequence_number,
X_ORG_ID => FND_PROFILE.VALUE('ORG_ID')
);
PROCEDURE update_res (c_null_hdlg_res_cur_rec chk_dup_cur%ROWTYPE,
res_dtl_rec res_cur%ROWTYPE) AS
-- there is no need to check for date overlap anymore (SWSCR01,02,04)
l_count NUMBER(5);
igs_pe_res_dtls_pkg.update_row (
x_rowid => c_null_hdlg_res_cur_rec.ROWID,
X_RESIDENT_DETAILS_ID => c_null_hdlg_res_cur_rec.RESIDENT_DETAILS_ID,
x_person_id => c_null_hdlg_res_cur_rec.person_id,
X_RESIDENCY_CLASS_CD => res_dtl_rec.RESIDENCY_CLASS_CD ,
X_RESIDENCY_STATUS_CD => res_dtl_rec.RESIDENCY_STATUS_CD ,
X_EVALUATION_DATE => res_dtl_rec.EVALUATION_DATE,
X_EVALUATOR => res_dtl_rec.EVALUATOR ,
X_COMMENTS => NVL(res_dtl_rec.COMMENTS ,c_null_hdlg_res_cur_rec.COMMENTS),
X_ATTRIBUTE_CATEGORY => NVL(res_dtl_rec.ATTRIBUTE_CATEGORY,c_null_hdlg_res_cur_rec.ATTRIBUTE_CATEGORY),
X_ATTRIBUTE1 => NVL(res_dtl_rec.ATTRIBUTE1, c_null_hdlg_res_cur_rec.ATTRIBUTE1),
X_ATTRIBUTE2 => NVL(res_dtl_rec.ATTRIBUTE2, c_null_hdlg_res_cur_rec.ATTRIBUTE2),
X_ATTRIBUTE3 => NVL(res_dtl_rec.ATTRIBUTE3, c_null_hdlg_res_cur_rec.ATTRIBUTE3),
X_ATTRIBUTE4 => NVL(res_dtl_rec.ATTRIBUTE4, c_null_hdlg_res_cur_rec.ATTRIBUTE4),
X_ATTRIBUTE5 => NVL(res_dtl_rec.ATTRIBUTE5, c_null_hdlg_res_cur_rec.ATTRIBUTE5),
X_ATTRIBUTE6 => NVL(res_dtl_rec.ATTRIBUTE6, c_null_hdlg_res_cur_rec.ATTRIBUTE6),
X_ATTRIBUTE7 => NVL(res_dtl_rec.ATTRIBUTE7, c_null_hdlg_res_cur_rec.ATTRIBUTE7),
X_ATTRIBUTE8 => NVL(res_dtl_rec.ATTRIBUTE8, c_null_hdlg_res_cur_rec.ATTRIBUTE8),
X_ATTRIBUTE9 => NVL(res_dtl_rec.ATTRIBUTE9, c_null_hdlg_res_cur_rec.ATTRIBUTE9),
X_ATTRIBUTE10 => NVL(res_dtl_rec.ATTRIBUTE10, c_null_hdlg_res_cur_rec.ATTRIBUTE10),
X_ATTRIBUTE11 => NVL(res_dtl_rec.ATTRIBUTE11, c_null_hdlg_res_cur_rec.ATTRIBUTE11),
X_ATTRIBUTE12 => NVL(res_dtl_rec.ATTRIBUTE12, c_null_hdlg_res_cur_rec.ATTRIBUTE12),
X_ATTRIBUTE13 => NVL(res_dtl_rec.ATTRIBUTE13, c_null_hdlg_res_cur_rec.ATTRIBUTE13),
X_ATTRIBUTE14 => NVL(res_dtl_rec.ATTRIBUTE14, c_null_hdlg_res_cur_rec.ATTRIBUTE14),
X_ATTRIBUTE15 => NVL(res_dtl_rec.ATTRIBUTE15, c_null_hdlg_res_cur_rec.ATTRIBUTE15),
X_ATTRIBUTE16 => NVL(res_dtl_rec.ATTRIBUTE16, c_null_hdlg_res_cur_rec.ATTRIBUTE16),
X_ATTRIBUTE17 => NVL(res_dtl_rec.ATTRIBUTE17, c_null_hdlg_res_cur_rec.ATTRIBUTE17),
X_ATTRIBUTE18 => NVL(res_dtl_rec.ATTRIBUTE18, c_null_hdlg_res_cur_rec.ATTRIBUTE18),
X_ATTRIBUTE19 => NVL(res_dtl_rec.ATTRIBUTE19, c_null_hdlg_res_cur_rec.ATTRIBUTE19),
X_ATTRIBUTE20 => NVL(res_dtl_rec.ATTRIBUTE20, c_null_hdlg_res_cur_rec.ATTRIBUTE20),
X_cal_type => res_dtl_rec.cal_type,
X_sequence_number => res_dtl_rec.sequence_number,
x_mode => 'R'
);
l_label := 'igs.plsql.igs_ad_imp_011.update_res.exception'||'E014';
l_debug_str := 'IGS_AD_IMP_011.PRC_PE_RES_DTLS.UPDATE_RES ' ||
'STATUS : 3' || 'ERROR CODE : E014 SQLERRM:' || SQLERRM;
END update_res;
UPDATE igs_pe_res_dtls_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = cst_stat_val_2
AND interface_run_id = l_interface_run_id;
UPDATE igs_pe_res_dtls_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND UPPER(mi.residency_class_cd) = pe.residency_class_cd
AND UPPER(mi.cal_type) = pe.cal_type
AND mi.sequence_number = pe.sequence_number);
UPDATE igs_pe_res_dtls_int
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status = cst_stat_val_2;
UPDATE igs_pe_res_dtls_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND status = cst_stat_val_2
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
UPDATE igs_pe_res_dtls_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND pe.residency_class_cd = UPPER(mi.Residency_class_cd)
AND pe.cal_type = UPPER(mi.cal_type)
AND pe.sequence_number = mi.sequence_number
AND pe.residency_status_cd = UPPER(mi.Residency_status_cd)
AND UPPER(pe.evaluator) = UPPER(mi.evaluator)
AND TRUNC(pe.evaluation_date) = TRUNC(mi.evaluation_date)
AND ((UPPER(pe.attribute1) = UPPER(mi.attribute1)) OR (pe.attribute1 IS NULL AND mi.attribute1 IS NULL))
AND ((UPPER(pe.attribute2) = UPPER(mi.attribute2)) OR (pe.attribute2 IS NULL AND mi.attribute2 IS NULL))
AND ((UPPER(pe.attribute3) = UPPER(mi.attribute3)) OR (pe.attribute3 IS NULL AND mi.attribute3 IS NULL))
AND ((UPPER(pe.attribute4) = UPPER(mi.attribute4)) OR (pe.attribute4 IS NULL AND mi.attribute4 IS NULL))
AND ((UPPER(pe.attribute5) = UPPER(mi.attribute5)) OR (pe.attribute5 IS NULL AND mi.attribute5 IS NULL))
AND ((UPPER(pe.attribute6) = UPPER(mi.attribute6)) OR (pe.attribute6 IS NULL AND mi.attribute6 IS NULL))
AND ((UPPER(pe.attribute7) = UPPER(mi.attribute7)) OR (pe.attribute7 IS NULL AND mi.attribute7 IS NULL))
AND ((UPPER(pe.attribute8) = UPPER(mi.attribute8)) OR (pe.attribute8 IS NULL AND mi.attribute8 IS NULL))
AND ((UPPER(pe.attribute9) = UPPER(mi.attribute9)) OR (pe.attribute9 IS NULL AND mi.attribute9 IS NULL))
AND ((UPPER(pe.attribute10) = UPPER(mi.attribute10)) OR (pe.attribute10 IS NULL AND mi.attribute10 IS NULL))
AND ((UPPER(pe.attribute11) = UPPER(mi.attribute11)) OR (pe.attribute11 IS NULL AND mi.attribute11 IS NULL))
AND ((UPPER(pe.attribute12) = UPPER(mi.attribute12)) OR (pe.attribute12 IS NULL AND mi.attribute12 IS NULL))
AND ((UPPER(pe.attribute13) = UPPER(mi.attribute13)) OR (pe.attribute13 IS NULL AND mi.attribute13 IS NULL))
AND ((UPPER(pe.attribute14) = UPPER(mi.attribute14)) OR (pe.attribute14 IS NULL AND mi.attribute14 IS NULL))
AND ((UPPER(pe.attribute15) = UPPER(mi.attribute15)) OR (pe.attribute15 IS NULL AND mi.attribute15 IS NULL))
AND ((UPPER(pe.attribute16) = UPPER(mi.attribute16)) OR (pe.attribute16 IS NULL AND mi.attribute16 IS NULL))
AND ((UPPER(pe.attribute17) = UPPER(mi.attribute17)) OR (pe.attribute17 IS NULL AND mi.attribute17 IS NULL))
AND ((UPPER(pe.attribute18) = UPPER(mi.attribute18)) OR (pe.attribute18 IS NULL AND mi.attribute18 IS NULL))
AND ((UPPER(pe.attribute19) = UPPER(mi.attribute19)) OR (pe.attribute19 IS NULL AND mi.attribute19 IS NULL))
AND ((UPPER(pe.attribute20) = UPPER(mi.attribute20)) OR (pe.attribute20 IS NULL AND mi.attribute20 IS NULL)));
UPDATE igs_pe_res_dtls_int mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND UPPER(mi.residency_class_cd) = pe.residency_class_cd
AND UPPER(mi.cal_type) = pe.cal_type
AND mi.sequence_number = pe.sequence_number);
update_res (chk_dup_rec , res_dtl_rec);
update_res (chk_dup_rec , res_dtl_rec);
UPDATE igs_pe_res_dtls_int
SET status = l_status,
error_code = p_error_code,
match_ind = l_match_ind
WHERE interface_res_id = res_dtl_rec.interface_res_id;
UPDATE igs_pe_res_dtls_int
SET
status = '3',
error_code = 'E518'
WHERE interface_res_id = res_dtl_rec.interface_res_id;
|| pkpatel 20-Sep-2005 Bug 3716764 (Modified the Update to disability_int table under sp_disability_cur loop)
|| (reverse chronological order - newest change first)
*/
l_default_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
SELECT mi.*,i.person_id
FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
WHERE mi.interface_run_id = cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND mi.status = '2';
SELECT mi.*,i.person_id
FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
WHERE mi.interface_run_id = cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND mi.status = '1'
AND mi.match_ind IN (cst_mi_val_23,cst_mi_val_19);
SELECT ai.*
FROM igs_pe_sn_srvce_int ai,
igs_ad_disablty_int_all ad
WHERE ai.interface_run_id = cp_interface_run_id AND
ad.interface_run_id = cp_interface_run_id AND
ai.interface_disablty_id = cp_interface_disablty_id AND
ai.interface_disablty_id = ad.interface_disablty_id AND
ai.status = '2';
SELECT ai.*
FROM igs_pe_sn_conct_int ai,
igs_ad_disablty_int_all ad
WHERE ai.interface_run_id = cp_interface_run_id AND
ad.interface_run_id = cp_interface_run_id AND
ai.interface_disablty_id = cp_interface_disablty_id AND
ai.interface_disablty_id = ad.interface_disablty_id AND
ai.status = '2';
SELECT rowid, pd.*
FROM igs_pe_pers_disablty pd
WHERE pd.disability_type = cp_disability_rec.disability_type AND
pd.person_id = cp_disability_rec.person_id AND
NVL(TRUNC(pd.start_date),l_default_date) = NVL(TRUNC(cp_disability_rec.start_date),l_default_date);
SELECT rowid, sn.*
FROM igs_pe_sn_service sn
WHERE sn.disability_id = cp_disability_id AND
sn.special_service_cd = cp_special_service_cd AND
NVL(TRUNC(sn.start_dt),l_default_date) = NVL(TRUNC(cp_start_dt),l_default_date);
SELECT rowid, sn.*
FROM igs_pe_sn_contact sn
WHERE sn.disability_id = cp_disability_id AND
NVL(sn.contact_name,'~') = NVL(cp_contact_name,'~') AND
NVL(TRUNC(sn.contact_date),l_default_date) = NVL(TRUNC(cp_contact_date),l_default_date);
SELECT 'X'
FROM igs_ad_disbl_type
WHERE disability_type = cp_disability_type AND
govt_disability_type = cp_govt_disability_type;
SELECT 'X'
FROM igs_ad_disbl_type
WHERE disability_type = cp_disability_type AND
closed_ind = cp_closed_ind;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_interviewer_id;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id= p_person_id;
UPDATE igs_ad_disablty_int_all
SET status = '1',
ERROR_CODE = NULL
WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '3',
ERROR_CODE = l_error_code
WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id= cp_person_id;
UPDATE igs_pe_sn_srvce_int
SET status = '1',
ERROR_CODE = NULL
WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
UPDATE igs_pe_sn_srvce_int
SET status = '3',
ERROR_CODE = l_error_code
WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id= cp_person_id;
UPDATE igs_pe_sn_conct_int
SET status = '3',
ERROR_CODE = l_error_code
WHERE interface_sn_contact_id = p_sn_contact_rec.interface_sn_contact_id;
igs_pe_pers_disablty_pkg.insert_row (
x_rowid => l_rowid ,
X_IGS_PE_PERS_DISABLTY_ID => l_disability_id,
x_person_id => p_disability_rec.person_id,
x_disability_type => p_disability_rec.disability_type,
x_contact_ind => NULL,
x_special_allow_cd => p_disability_rec.special_allow_cd,
x_support_level_cd => p_disability_rec.support_level_cd,
x_documented => NULL,
x_special_service_id => NULL,
x_attribute_category => p_disability_rec.attribute_category,
x_attribute1 => p_disability_rec.attribute1,
x_attribute2 => p_disability_rec.attribute2,
x_attribute3 => p_disability_rec.attribute3,
x_attribute4 => p_disability_rec.attribute4,
x_attribute5 => p_disability_rec.attribute5,
x_attribute6 => p_disability_rec.attribute6,
x_attribute7 => p_disability_rec.attribute7,
x_attribute8 => p_disability_rec.attribute8,
x_attribute9 => p_disability_rec.attribute9,
x_attribute10 => p_disability_rec.attribute10,
x_attribute11 => p_disability_rec.attribute11,
x_attribute12 => p_disability_rec.attribute12,
x_attribute13 => p_disability_rec.attribute13,
x_attribute14 => p_disability_rec.attribute14,
x_attribute15 => p_disability_rec.attribute15,
x_attribute16 => p_disability_rec.attribute16,
x_attribute17 => p_disability_rec.attribute17,
x_attribute18 => p_disability_rec.attribute18,
x_attribute19 => p_disability_rec.attribute19,
x_attribute20 => p_disability_rec.attribute20,
x_elig_early_reg_ind => NVL(p_disability_rec.elig_early_reg_ind,'N'),
x_start_date => p_disability_rec.start_date,
x_end_date => p_disability_rec.end_date,
x_info_source => p_disability_rec.info_source,
x_interviewer_id => p_disability_rec.interviewer_id,
x_interviewer_date => p_disability_rec.interviewer_date,
x_mode => 'R'
);
UPDATE igs_ad_disablty_int_all
SET status ='1',
ERROR_CODE = p_error_code
WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status ='3',
ERROR_CODE = p_error_code
WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
PROCEDURE update_disability(p_disability_rec disability_cur%ROWTYPE,
p_dup_chk_disability_rec dup_chk_disability_cur%ROWTYPE)
AS
/*
|| Created By : [email protected]
|| Created On : 2-JUN-2003
|| Purpose : This is a private procedure is for updating Person Disability Record.
Enh Bug: 2986796.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
l_rowid VARCHAR2(25);
igs_pe_pers_disablty_pkg.update_row(
x_rowid => p_dup_chk_disability_rec.rowid,
x_igs_pe_pers_disablty_id => p_dup_chk_disability_rec.igs_pe_pers_disablty_id,
x_person_id => p_dup_chk_disability_rec.person_id,
x_disability_type => p_dup_chk_disability_rec.disability_type,
x_contact_ind => p_dup_chk_disability_rec.contact_ind,
x_special_allow_cd => NVL( p_disability_rec.special_allow_cd, p_dup_chk_disability_rec.special_allow_cd),
x_support_level_cd => NVL( p_disability_rec.support_level_cd, p_dup_chk_disability_rec.support_level_cd),
x_documented => p_dup_chk_disability_rec.documented,
x_special_service_id => p_dup_chk_disability_rec.special_service_id,
x_attribute_category => NVL(p_disability_rec.attribute_category,p_dup_chk_disability_rec.attribute_category),
x_attribute1 => NVL(p_disability_rec.attribute1,p_dup_chk_disability_rec.attribute1),
x_attribute2 => NVL(p_disability_rec.attribute2,p_dup_chk_disability_rec.attribute2),
x_attribute3 => NVL(p_disability_rec.attribute3,p_dup_chk_disability_rec.attribute3),
x_attribute4 => NVL(p_disability_rec.attribute4,p_dup_chk_disability_rec.attribute4),
x_attribute5 => NVL(p_disability_rec.attribute5,p_dup_chk_disability_rec.attribute5),
x_attribute6 => NVL(p_disability_rec.attribute6,p_dup_chk_disability_rec.attribute6),
x_attribute7 => NVL(p_disability_rec.attribute7,p_dup_chk_disability_rec.attribute7),
x_attribute8 => NVL(p_disability_rec.attribute8,p_dup_chk_disability_rec.attribute8),
x_attribute9 => NVL(p_disability_rec.attribute9,p_dup_chk_disability_rec.attribute9),
x_attribute10 => NVL(p_disability_rec.attribute10,p_dup_chk_disability_rec.attribute10),
x_attribute11 => NVL(p_disability_rec.attribute11,p_dup_chk_disability_rec.attribute11),
x_attribute12 => NVL(p_disability_rec.attribute12,p_dup_chk_disability_rec.attribute12),
x_attribute13 => NVL(p_disability_rec.attribute13,p_dup_chk_disability_rec.attribute13),
x_attribute14 => NVL(p_disability_rec.attribute14,p_dup_chk_disability_rec.attribute14),
x_attribute15 => NVL(p_disability_rec.attribute15,p_dup_chk_disability_rec.attribute15),
x_attribute16 => NVL(p_disability_rec.attribute16,p_dup_chk_disability_rec.attribute16),
x_attribute17 => NVL(p_disability_rec.attribute17,p_dup_chk_disability_rec.attribute17),
x_attribute18 => NVL(p_disability_rec.attribute18,p_dup_chk_disability_rec.attribute18),
x_attribute19 => NVL(p_disability_rec.attribute19,p_dup_chk_disability_rec.attribute19),
x_attribute20 => NVL(p_disability_rec.attribute20,p_dup_chk_disability_rec.attribute20),
x_elig_early_reg_ind => NVL(p_disability_rec.elig_early_reg_ind,p_dup_chk_disability_rec.elig_early_reg_ind),
x_start_date => NVL(p_disability_rec.start_date,p_dup_chk_disability_rec.start_date),
x_end_date => NVL(p_disability_rec.end_date,p_dup_chk_disability_rec.end_date),
x_info_source => NVL(p_disability_rec.info_source,p_dup_chk_disability_rec.info_source),
x_interviewer_id => NVL(p_disability_rec.interviewer_id,p_dup_chk_disability_rec.interviewer_id),
x_interviewer_date => NVL(p_disability_rec.interviewer_date,p_dup_chk_disability_rec.interviewer_date),
x_mode => 'R'
);
UPDATE igs_ad_disablty_int_all
SET status =cst_stat_val_1,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status ='3',
error_code = l_error_code
WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
l_label := 'igs.plsql.igs_ad_imp_011.update_disability.exception'||l_error_code;
l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.update_disability '||'Unhandled Exception'
||' for INTERFACE DISABLTY ID :'
|| p_disability_rec.interface_disablty_id|| ' Status : 3'|| ' ErrorCode :' ||
l_error_code||' SQLERRM: '|| SQLERRM;
l_label := 'igs.plsql.igs_ad_imp_011.update_disability.exception'||l_error_code;
l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.update_disability '||
' for INTERFACE DISABLTY ID :'
|| p_disability_rec.interface_disablty_id|| ' Status : 3'||
' ErrorCode :' || l_error_code ||' SQLERRM:'|| SQLERRM;
END update_disability;
igs_pe_sn_service_pkg.insert_row (
x_rowid => l_rowid ,
x_sn_service_id => l_sn_service_id,
x_disability_id => p_disability_id,
x_special_service_cd => p_sn_service_rec.special_service_cd,
x_documented_ind => p_sn_service_rec.documented_ind,
x_start_dt => p_sn_service_rec.start_dt,
x_end_dt => p_sn_service_rec.end_dt,
x_mode => 'R'
);
UPDATE igs_pe_sn_srvce_int
SET status ='1',
error_code = l_error_code
WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
UPDATE igs_pe_sn_srvce_int
SET status ='3',
error_code = l_error_code
WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
||'Unhandled Exception in call to igs_pe_sn_service_pkg.insert_row'
||' for INTERFACE SN SERVICE ID :'
|| p_sn_service_rec.interface_sn_service_id
|| ' Status : 3'
|| ' ErrorCode :' || l_error_code
||' SQLERRM '|| SQLERRM ;
igs_pe_sn_contact_pkg.insert_row (
x_rowid => l_rowid ,
x_sn_contact_id => l_sn_contact_id,
x_disability_id => p_disability_id,
x_contact_name => p_sn_contact_rec.contact_name,
x_contact_date => TRUNC(p_sn_contact_rec.contact_date),
x_comments => p_sn_contact_rec.comments,
x_mode => 'R'
);
UPDATE igs_pe_sn_conct_int
SET status ='1',
error_code = l_error_code
WHERE interface_sn_contact_id = p_sn_contact_rec.interface_sn_contact_id;
UPDATE igs_pe_sn_conct_int
SET status ='3',
error_code = l_error_code
WHERE interface_sn_contact_id = p_sn_contact_rec.interface_sn_contact_id;
||'Unhandled Exception in call to igs_pe_sn_contact_pkg.insert_row'
||' for INTERFACE SN CONTACT ID :'
|| p_sn_contact_rec.interface_sn_contact_id
|| 'Status : 3'
|| 'ErrorCode :' || l_error_code
||' SQLERRM '|| SQLERRM;
UPDATE igs_pe_sn_srvce_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = cst_stat_val_2
AND interface_run_id = l_interface_run_id;
UPDATE igs_pe_sn_srvce_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = cst_stat_val_2
AND mi.interface_disablty_id = p_interface_disability_id
AND EXISTS ( SELECT '1'
FROM igs_pe_sn_service pe
WHERE pe.disability_id = p_disability_id AND
mi.special_service_cd = pe.special_service_cd AND
NVL(TRUNC(mi.start_dt),l_default_date) = NVL(pe.start_dt,l_default_date)
);
UPDATE igs_pe_sn_srvce_int
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status = cst_stat_val_2;
UPDATE igs_pe_sn_srvce_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND status = cst_stat_val_2
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
UPDATE igs_pe_sn_srvce_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND mi.interface_disablty_id = p_interface_disability_id
AND EXISTS ( SELECT '1'
FROM igs_pe_sn_service pe
WHERE pe.disability_id = p_disability_id AND
mi.special_service_cd = pe.special_service_cd AND
UPPER(mi.documented_ind) = UPPER(pe.documented_ind) AND
(TRUNC(mi.start_dt) = TRUNC(pe.start_dt) OR (mi.start_dt IS NULL AND pe.start_dt IS NULL)) AND
(TRUNC(mi.end_dt) = TRUNC(pe.end_dt) OR (mi.end_dt IS NULL AND pe.end_dt IS NULL)));
UPDATE igs_pe_sn_srvce_int mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_sn_service_id = (SELECT sn_service_id
FROM igs_pe_sn_service pe
WHERE pe.disability_id = p_disability_id AND
mi.special_service_cd = pe.special_service_cd AND
NVL(TRUNC(mi.start_dt),l_default_date) = NVL(TRUNC(pe.start_dt),l_default_date))
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND mi.interface_disablty_id = p_interface_disability_id
AND EXISTS (SELECT '1'
FROM igs_pe_sn_service pe
WHERE pe.disability_id = p_disability_id AND
mi.special_service_cd = pe.special_service_cd AND
NVL(TRUNC(mi.start_dt),l_default_date) = NVL(TRUNC(pe.start_dt),l_default_date));
igs_pe_sn_service_pkg.update_row (
x_rowid => dup_chk_sn_service_rec.ROWID,
x_sn_service_id => dup_chk_sn_service_rec.sn_service_id,
x_disability_id => dup_chk_sn_service_rec.disability_id,
x_special_service_cd => sn_service_rec.special_service_cd,
x_documented_ind => sn_service_rec.documented_ind,
x_start_dt => NVL(sn_service_rec.start_dt,dup_chk_sn_service_rec.start_dt),
x_end_dt => NVL(sn_service_rec.end_dt,dup_chk_sn_service_rec.end_dt),
x_mode => 'R'
);
UPDATE igs_pe_sn_srvce_int
SET status =cst_stat_val_1,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
UPDATE igs_pe_sn_srvce_int
SET status ='3',
error_code = l_error_code
WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
||'Unhandled Exception in call to igs_pe_sn_service_pkg.update_row'
||' for INTERFACE SN SERVICE ID :'
|| sn_service_rec.interface_sn_service_id
|| ' Status : 3'
|| ' ErrorCode :' || l_error_code
||' SQLERRM '|| SQLERRM;
igs_pe_sn_service_pkg.update_row (
x_rowid => dup_chk_sn_service_rec.rowid ,
x_sn_service_id => dup_chk_sn_service_rec.sn_service_id,
x_disability_id => p_disability_id,
x_special_service_cd => sn_service_rec.special_service_cd,
x_documented_ind => sn_service_rec.documented_ind,
x_start_dt => nvl(sn_service_rec.start_dt,dup_chk_sn_service_rec.start_dt),
x_end_dt => nvl(sn_service_rec.end_dt,dup_chk_sn_service_rec.end_dt),
x_mode => 'R'
);
UPDATE igs_pe_sn_srvce_int
SET status =cst_stat_val_1,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
UPDATE igs_pe_sn_srvce_int
SET status ='3',
error_code = l_error_code
WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
||'Unhandled Exception in call to igs_pe_sn_service_pkg.update_row'
||' for INTERFACE SN SERVICE ID :'
|| sn_service_rec.interface_sn_service_id
|| ' Status : 3'
|| ' ErrorCode :' || l_error_code
||' SQLERRM '|| SQLERRM ;
UPDATE igs_pe_sn_conct_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = cst_stat_val_2
AND interface_run_id = l_interface_run_id;
UPDATE igs_pe_sn_conct_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = cst_stat_val_2
AND mi.interface_disablty_id = p_interface_disability_id
AND EXISTS ( SELECT '1'
FROM igs_pe_sn_contact pe
WHERE pe.disability_id = p_disability_id AND
NVL(UPPER(mi.contact_name),'~') = NVL(UPPER(pe.contact_name),'~') AND
NVL(TRUNC(mi.contact_date),l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date)
);
UPDATE igs_pe_sn_conct_int
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status = cst_stat_val_2;
UPDATE igs_pe_sn_conct_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND status = cst_stat_val_2
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
UPDATE igs_pe_sn_conct_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND mi.interface_disablty_id = p_interface_disability_id
AND EXISTS ( SELECT '1'
FROM igs_pe_sn_contact pe
WHERE pe.disability_id = p_disability_id AND
NVL(UPPER(mi.contact_name),'*') = NVL(UPPER(pe.contact_name),'*') AND
NVL(TRUNC(mi.contact_date), l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date) AND
NVL(UPPER(mi.comments),'*') = NVL(UPPER(pe.comments), '*'));
UPDATE igs_pe_sn_conct_int mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_sn_contact_id = (SELECT sn_contact_id
FROM igs_pe_sn_contact pe
WHERE pe.disability_id = p_disability_id AND
NVL(UPPER(mi.contact_name),'*') = NVL(UPPER(pe.contact_name),'*') AND
NVL(TRUNC(mi.contact_date), l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date)
)
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND mi.interface_disablty_id = p_interface_disability_id
AND EXISTS (SELECT '1'
FROM igs_pe_sn_contact pe
WHERE pe.disability_id = p_disability_id AND
NVL(UPPER(mi.contact_name),'*') = NVL(UPPER(pe.contact_name),'*') AND
NVL(TRUNC(mi.contact_date), l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date));
igs_pe_sn_contact_pkg.update_row (
x_rowid => dup_chk_sn_contact_rec.rowid ,
x_sn_contact_id => dup_chk_sn_contact_rec.sn_contact_id,
x_disability_id => dup_chk_sn_contact_rec.disability_id,
x_contact_name => NVL(sn_contact_rec.contact_name,dup_chk_sn_contact_rec.contact_name),
x_contact_date => NVL(sn_contact_rec.contact_date,dup_chk_sn_contact_rec.contact_date),
x_comments => NVL(sn_contact_rec.comments, dup_chk_sn_contact_rec.comments),
x_mode => 'R'
);
UPDATE igs_pe_sn_conct_int
SET status =cst_stat_val_1,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
UPDATE igs_pe_sn_conct_int
SET status ='3',
error_code = l_error_code
WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
||'Unhandled Exception in call to igs_pe_sn_contact_pkg.update_row'
||' for INTERFACE SN CONTACT ID :'
|| sn_contact_rec.interface_sn_contact_id
|| ' Status : 3'
|| ' ErrorCode :' || l_error_code
||' SQLERRM '|| SQLERRM ;
igs_pe_sn_contact_pkg.update_row (
x_rowid => dup_chk_sn_contact_rec.rowid ,
x_sn_contact_id => dup_chk_sn_contact_rec.sn_contact_id,
x_disability_id => dup_chk_sn_contact_rec.disability_id,
x_contact_name => NVL(sn_contact_rec.contact_name,dup_chk_sn_contact_rec.contact_name),
x_contact_date => NVL(sn_contact_rec.contact_date,dup_chk_sn_contact_rec.contact_date),
x_comments => NVL(sn_contact_rec.comments, dup_chk_sn_contact_rec.comments),
x_mode => 'R'
);
UPDATE igs_pe_sn_conct_int
SET status =cst_stat_val_1,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
UPDATE igs_pe_sn_conct_int
SET status ='3',
error_code = l_error_code
WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
||'Unhandled Exception in call to igs_pe_sn_contact_pkg.update_row'
||' for INTERFACE SN CONTACT ID :'
|| sn_contact_rec.interface_sn_contact_id
|| ' Status : 3'
|| ' ErrorCode :' || l_error_code
||' SQLERRM '|| SQLERRM ;
-- gmaheswa 21-Sep-2006 Modified Update statement in 5. If rule is R, set duplicated records
-- with no discrepancy to status 1 and match_ind 23 case to reduce shared memory.
--------------------------------------------------------------------------
l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
UPDATE igs_ad_disablty_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = cst_stat_val_2
AND interface_run_id = l_interface_run_id;
UPDATE igs_ad_disablty_int_all mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19,
dup_disability_id = ( SELECT pe.igs_pe_pers_disablty_id
FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
WHERE ii.interface_id = mi.interface_id AND
pe.disability_type = UPPER(mi.disability_type) AND
ROWNUM = 1 AND
ii.person_id = pe.person_id AND
((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL)))
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
WHERE pe.disability_type = UPPER(mi.disability_type) AND
ii.interface_id = mi.interface_id AND
ii.person_id = pe.person_id AND
((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL))
);
UPDATE igs_ad_disablty_int_all
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status = cst_stat_val_2;
UPDATE igs_ad_disablty_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND status = cst_stat_val_2
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
UPDATE igs_ad_disablty_int_all mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23,
dup_disability_id = ( SELECT pe.igs_pe_pers_disablty_id
FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
WHERE ii.person_id = pe.person_id
AND ii.interface_id = mi.interface_id
AND pe.disability_type = UPPER(mi.disability_type)
AND ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL)))
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT 1
FROM igs_pe_pers_disablty pe, igs_AD_interface_all ii
WHERE ii.person_id = pe.person_id
AND ii.interface_id = mi.interface_id
AND UPPER(mi.disability_type) = pe.disability_type
AND NVL(mi.special_allow_cd, -99) = NVL(pe.special_allow_cd, -99) AND
NVL(pe.support_level_cd, -99) = NVL(mi.support_level_cd, -99) AND
NVL(UPPER(pe.elig_early_reg_ind),'N') = NVL(UPPER(mi.elig_early_reg_ind),'N') AND
pe.start_date = TRUNC(mi.start_date) AND
NVL(pe.end_date,l_default_date) = NVL(TRUNC(mi.end_date),l_default_date) AND
NVL(UPPER(pe.info_source),'*') = NVL(UPPER(mi.info_source),'*') AND
NVL(pe.interviewer_id, -99) = NVL(mi.interviewer_id, -99) AND
NVL(TRUNC(pe.interviewer_date), l_default_date) = NVL(TRUNC(mi.interviewer_date), l_default_date)
AND NVL(pe.attribute_category, '*') = NVL(mi.attribute_category, '*')
AND (pe.attribute1||'*'||pe.attribute2||'*'||pe.attribute3||'*'||pe.attribute4||'*'||pe.attribute5||'*'||
pe.attribute6||'*'||pe.attribute7||'*'||pe.attribute8||'*'||pe.attribute9||'*'||pe.attribute10||'*'||pe.attribute11||'*'||
pe.attribute12||'*'||pe.attribute13||'*'||pe.attribute14||'*'||pe.attribute15||'*'||pe.attribute16||'*'||pe.attribute17||'*'||
pe.attribute18||'*'||pe.attribute19||'*'||pe.attribute20||'*') = (mi.attribute1||'*'
||mi.attribute2||'*'||mi.attribute3||'*'||mi.attribute4||'*'||mi.attribute5||'*'||mi.attribute6||'*'||
mi.attribute7||'*'||mi.attribute8||'*'||mi.attribute9||'*'||mi.attribute10||'*'||mi.attribute11||'*'||
mi.attribute12||'*'||mi.attribute13||'*'||mi.attribute14||'*'||mi.attribute15||'*'||mi.attribute16||
'*'||mi.attribute17||'*'||mi.attribute18||'*'||mi.attribute19||'*'||mi.attribute20||'*'));
UPDATE igs_ad_disablty_int_all mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM igs_pe_pers_disablty pe, igs_Ad_interface_all ii
WHERE pe.disability_type = UPPER(mi.disability_type) AND
ii.person_id = pe.person_id AND
ii.interface_id = mi.interface_id AND
((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL))
);
update_disability(disability_rec,dup_chk_disability_rec);
update_disability(disability_rec,dup_chk_disability_rec);
UPDATE igs_pe_sn_srvce_int
SET status ='3',
error_code = 'E271'
WHERE INTERFACE_DISABLTY_ID = disability_rec.interface_disablty_id;
UPDATE igs_pe_sn_conct_int
SET status ='3',
error_code = 'E272'
WHERE INTERFACE_DISABLTY_ID = disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E347'
WHERE interface_disablty_id = disability_rec.interface_disablty_id AND
(EXISTS (SELECT 1 FROM igs_pe_sn_conct_int WHERE interface_disablty_id = disability_rec.interface_disablty_id AND status = '3')
OR EXISTS (SELECT 1 FROM igs_pe_sn_srvce_int WHERE interface_disablty_id = disability_rec.interface_disablty_id AND status = '3'));
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E155'
WHERE interface_disablty_id = disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E148'
WHERE interface_disablty_id = disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E147'
WHERE interface_disablty_id = disability_rec.interface_disablty_id;
UPDATE igs_pe_sn_srvce_int
SET status ='3',
error_code = 'E271'
WHERE INTERFACE_DISABLTY_ID = sp_disability_rec.interface_disablty_id;
UPDATE igs_pe_sn_conct_int
SET status ='3',
error_code = 'E272'
WHERE INTERFACE_DISABLTY_ID = sp_disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E347'
WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id AND
(
EXISTS(SELECT 1 FROM igs_pe_sn_conct_int WHERE
interface_disablty_id = sp_disability_rec.interface_disablty_id AND status = '3')
OR EXISTS (SELECT 1 FROM igs_pe_sn_srvce_int WHERE
interface_disablty_id = sp_disability_rec.interface_disablty_id AND status = '3')
);
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E155'
WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E148'
WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id;
UPDATE igs_ad_disablty_int_all
SET status = '4',
error_code = 'E147'
WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id;