[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| pkpatel 25-Jul-2003 3045079 : TRUNC of start_dt for API insert/update
|| gmuralid 4-DEC-2002 SEVIS BUILD - Changed validation for country in
procedure prc_pe_hz_citizenship to validate against fnd territories.
Also made calls to the import processes in package igs_ad_imp_026
in the procedure prc_pe_intl_dtls
gmuralid 29-NOV-2002 SEVIS BUILD removed procedures prc_pe_visa_pass and prc_pe_fund_dep
from both spec and body
Also modified validation for country in procedure prc_pe_hz_citizenship
gmaheswa 10-NOV-2003 Bug 3223043 HZ.K Impact changes
nsidana 6/21/2004 Bug 3541714 : Added validtion to check that the date disowned > date recognized
for citizenship details.
|| gmaheswa 29-Sep-2004 BUG 3787210 Added Closed indicator check for the Alternate Person Id type.
*/
cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
|| Added the DFF validation before insert/update to the oss table, also in
|| the call to insert_row/update_row to the oss table adding the dff columns
|| kumma 23-OCT-2002 Added the parameters for DFF columns to the calls of insert_row and update_row on
|| igs_pe_hlth_ins_pkg and igs_pe_immu_dtls_pkg, #2608360
|| kumma 28-OCT-2002 Replaced MILITARY_TYPE_ID with MILITARY_TYPE_CD in validate_military procedure, #2608360
|| Changed the data type of parameter p_MILITARY_TYPE_CD to VARCHAR2 in procedure CHK_DUP_MILIT
|| kumma 30-OCT-2002 Added the call to igs_ad_imp_018.validate_desc_flex for new flex fields added in health
|| insurance and immunization details
|| pkpatel 6-JAN-2003 Bug No: 2729633
|| Added the UPPER for all VARCHAR2 fileds. Add additional columns for discrepancy. Add NOT NULL
|| check for separation type.
|| (reverse chronological order - newest change first)
*/
CURSOR milt_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
SELECT mi.*,i.person_id
FROM igs_ad_military_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 mi.interface_run_id = cp_interface_run_id
AND mi.status = '2';
IGS_PE_MIL_SERVICES_pkg.INSERT_ROW (
X_ROWID => l_rowid,
X_Org_Id => l_org_id,
x_MILIT_SERVICE_ID => l_milt_id,
x_PERSON_ID => MILITARY_REC.PERSON_ID ,
x_START_DATE => MILITARY_REC.START_DATE ,
x_END_DATE => MILITARY_REC.END_DATE ,
x_ATTRIBUTE_CATEGORY => MILITARY_REC.ATTRIBUTE_CATEGORY ,
x_ATTRIBUTE1 => MILITARY_REC.ATTRIBUTE1 ,
x_ATTRIBUTE2 => MILITARY_REC.ATTRIBUTE2 ,
x_ATTRIBUTE3 => MILITARY_REC.ATTRIBUTE3 ,
x_ATTRIBUTE4 => MILITARY_REC.ATTRIBUTE4 ,
x_ATTRIBUTE5 => MILITARY_REC.ATTRIBUTE5 ,
x_ATTRIBUTE6 => MILITARY_REC.ATTRIBUTE6 ,
x_ATTRIBUTE7 => MILITARY_REC.ATTRIBUTE7 ,
x_ATTRIBUTE8 => MILITARY_REC.ATTRIBUTE8 ,
x_ATTRIBUTE9 => MILITARY_REC.ATTRIBUTE9 ,
x_ATTRIBUTE10 => MILITARY_REC.ATTRIBUTE10,
x_ATTRIBUTE11 => MILITARY_REC.ATTRIBUTE11,
x_ATTRIBUTE12 => MILITARY_REC.ATTRIBUTE12,
x_ATTRIBUTE13 => MILITARY_REC.ATTRIBUTE13,
x_ATTRIBUTE14 => MILITARY_REC.ATTRIBUTE14,
x_ATTRIBUTE15 => MILITARY_REC.ATTRIBUTE15,
x_ATTRIBUTE16 => MILITARY_REC.ATTRIBUTE16,
x_ATTRIBUTE17 => MILITARY_REC.ATTRIBUTE17,
x_ATTRIBUTE18 => MILITARY_REC.ATTRIBUTE18,
x_ATTRIBUTE19 => MILITARY_REC.ATTRIBUTE19,
x_ATTRIBUTE20 => MILITARY_REC.ATTRIBUTE20,
x_MILITARY_TYPE_CD => MILITARY_REC.MILITARY_TYPE_CD ,
x_SEPARATION_TYPE_CD => MILITARY_REC.SEPARATION_TYPE_CD ,
x_ASSISTANCE_TYPE_CD => MILITARY_REC.ASSISTANCE_TYPE_CD ,
x_ASSISTANCE_STATUS_CD => MILITARY_REC.ASSISTANCE_STATUS_CD ,
X_MODE => 'R'
);
SELECT Birth_date
FROM IGS_PE_PERSON_BASE_V
WHERE person_id= p_person_id;
UPDATE igs_ad_military_int_all
SET error_code = p_error_code,
status = '3'
WHERE interface_military_id = military_rec.interface_military_id ;
UPDATE igs_ad_military_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 interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_ad_military_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_mil_services_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.military_type_cd = UPPER(mi.military_type_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date) );
UPDATE igs_ad_military_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_military_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status = cst_stat_val_2;
UPDATE igs_ad_military_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_mil_services_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.military_type_cd = UPPER(mi.military_type_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
AND NVL(UPPER(pe.assistance_type_cd),'*!*')= NVL(UPPER(mi.assistance_type_cd),'*!*')
AND NVL(UPPER(pe.assistance_status_cd),'*!*') = NVL(UPPER(mi.assistance_status_cd),'*!*')
AND NVL(UPPER(pe.separation_type_cd),'*!*') = NVL(UPPER(mi.separation_type_cd),'*!*')
AND NVL(pe.attribute1,'*!*') = NVL(mi.attribute1,'*!*')
AND NVL(pe.attribute2,'*!*') = NVL(mi.attribute2,'*!*')
AND NVL(pe.attribute3,'*!*') = NVL(mi.attribute3,'*!*')
AND NVL(pe.attribute4,'*!*') = NVL(mi.attribute4,'*!*')
AND NVL(pe.attribute5,'*!*') = NVL(mi.attribute5,'*!*')
AND NVL(pe.attribute6,'*!*') = NVL(mi.attribute6,'*!*')
AND NVL(pe.attribute7,'*!*') = NVL(mi.attribute7,'*!*')
AND NVL(pe.attribute8,'*!*') = NVL(mi.attribute8,'*!*')
AND NVL(pe.attribute9,'*!*') = NVL(mi.attribute9,'*!*')
AND NVL(pe.attribute10,'*!*') = NVL(mi.attribute10,'*!*')
AND NVL(pe.attribute11,'*!*') = NVL(mi.attribute11,'*!*')
AND NVL(pe.attribute12,'*!*') = NVL(mi.attribute12,'*!*')
AND NVL(pe.attribute13,'*!*') = NVL(mi.attribute13,'*!*')
AND NVL(pe.attribute14,'*!*') = NVL(mi.attribute14,'*!*')
AND NVL(pe.attribute15,'*!*') = NVL(mi.attribute15,'*!*')
AND NVL(pe.attribute16,'*!*') = NVL(mi.attribute16,'*!*')
AND NVL(pe.attribute17,'*!*') = NVL(mi.attribute17,'*!*')
AND NVL(pe.attribute18,'*!*') = NVL(mi.attribute18,'*!*')
AND NVL(pe.attribute19,'*!*') = NVL(mi.attribute19,'*!*')
AND NVL(pe.attribute20,'*!*') = NVL(mi.attribute20,'*!*')
);
UPDATE igs_ad_military_int_all mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_milit_service_id = (SELECT milit_service_id
FROM igs_pe_mil_services_all 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.military_type_cd = UPPER(mi.military_type_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date))
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_mil_services_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.military_type_cd = UPPER(mi.military_type_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
SELECT rowid,mi.*
FROM igs_pe_mil_services mi
WHERE UPPER(military_type_cd) = UPPER(cp_military_type_cd)
AND person_id = cp_person_id
AND TRUNC(start_date) = TRUNC(cp_start_date);
igs_pe_mil_services_pkg.update_row(
x_rowid=> dup_milit_rec.rowid,
x_milit_service_id=> dup_milit_rec.milit_service_id,
x_person_id=> dup_milit_rec.person_id,
x_start_date=> NVL(TRUNC(military_rec.start_date),dup_milit_rec.start_date),
x_end_date=> NVL(TRUNC(military_rec.end_date),dup_milit_rec.end_date),
x_attribute_category=>NVL(military_rec.attribute_category,dup_milit_rec.attribute_category),
x_attribute1=>NVL(military_rec.attribute1,dup_milit_rec.attribute1),
x_attribute2=>NVL(military_rec.attribute2, dup_milit_rec.attribute2),
x_attribute3=>NVL(military_rec.attribute3,dup_milit_rec.attribute3),
x_attribute4=>NVL(military_rec.attribute4,dup_milit_rec.attribute4),
x_attribute5=>NVL(military_rec.attribute5,dup_milit_rec.attribute5),
x_attribute6=>NVL(military_rec.attribute6,dup_milit_rec.attribute6),
x_attribute7=>NVL(military_rec.attribute7,dup_milit_rec.attribute7),
x_attribute8=>NVL(military_rec.attribute8,dup_milit_rec.attribute8),
x_attribute9=>NVL(military_rec.attribute9,dup_milit_rec.attribute9),
x_attribute10=>NVL(military_rec.attribute10,dup_milit_rec.attribute10),
x_attribute11=>NVL(military_rec.attribute11,dup_milit_rec.attribute11),
x_attribute12=>NVL(military_rec.attribute12,dup_milit_rec.attribute12),
x_attribute13=>NVL(military_rec.attribute13,dup_milit_rec.attribute13),
x_attribute14=>NVL(military_rec.attribute14,dup_milit_rec.attribute14),
x_attribute15=>NVL(military_rec.attribute15,dup_milit_rec.attribute15),
x_attribute16=>NVL(military_rec.attribute16,dup_milit_rec.attribute16),
x_attribute17=>NVL(military_rec.attribute17,dup_milit_rec.attribute17),
x_attribute18=>NVL(military_rec.attribute18,dup_milit_rec.attribute18),
x_attribute19=>NVL(military_rec.attribute19,dup_milit_rec.attribute19),
x_attribute20=> NVL(military_rec.attribute20,dup_milit_rec.attribute20),
x_military_type_cd=>NVL(military_rec.military_type_cd,dup_milit_rec.military_type_cd),
x_separation_type_cd=>NVL(military_rec.separation_type_cd,dup_milit_rec.separation_type_cd),
x_assistance_type_cd=> NVL(military_rec.assistance_type_cd,dup_milit_rec.assistance_type_cd),
x_assistance_status_cd => NVL(military_rec.assistance_status_cd,dup_milit_rec.assistance_status_cd),
x_mode=>'R');
UPDATE igs_ad_military_int_all
SET match_ind =cst_mi_val_18,
status = cst_stat_val_1
WHERE interface_military_id = military_rec.interface_military_id;
UPDATE igs_ad_military_int_all
SET ERROR_CODE = cst_err_val_14,
status = cst_stat_val_3
WHERE interface_military_id= military_rec.interface_military_id;
igs_pe_mil_services_pkg.update_row(
x_rowid=> dup_milit_rec.rowid,
x_milit_service_id=> dup_milit_rec.milit_service_id,
x_person_id=>dup_milit_rec.person_id,
x_start_date=> NVL(TRUNC(military_rec.start_date),dup_milit_rec.start_date),
x_end_date=> NVL(TRUNC(military_rec.end_date),dup_milit_rec.end_date),
x_attribute_category=>NVL(military_rec.attribute_category,dup_milit_rec.attribute_category),
x_attribute1=>NVL(military_rec.attribute1,dup_milit_rec.attribute1),
x_attribute2=>NVL(military_rec.attribute2, dup_milit_rec.attribute2),
x_attribute3=>NVL(military_rec.attribute3,dup_milit_rec.attribute3),
x_attribute4=>NVL(military_rec.attribute4,dup_milit_rec.attribute4),
x_attribute5=>NVL(military_rec.attribute5,dup_milit_rec.attribute5),
x_attribute6=>NVL(military_rec.attribute6,dup_milit_rec.attribute6),
x_attribute7=>NVL(military_rec.attribute7,dup_milit_rec.attribute7),
x_attribute8=>NVL(military_rec.attribute8,dup_milit_rec.attribute8),
x_attribute9=>NVL(military_rec.attribute9,dup_milit_rec.attribute9),
x_attribute10=>NVL(military_rec.attribute10,dup_milit_rec.attribute10),
x_attribute11=>NVL(military_rec.attribute11,dup_milit_rec.attribute11),
x_attribute12=>NVL(military_rec.attribute12,dup_milit_rec.attribute12),
x_attribute13=>NVL(military_rec.attribute13,dup_milit_rec.attribute13),
x_attribute14=>NVL(military_rec.attribute14,dup_milit_rec.attribute14),
x_attribute15=>NVL(military_rec.attribute15,dup_milit_rec.attribute15),
x_attribute16=>NVL(military_rec.attribute16,dup_milit_rec.attribute16),
x_attribute17=>NVL(military_rec.attribute17,dup_milit_rec.attribute17),
x_attribute18=>NVL(military_rec.attribute18,dup_milit_rec.attribute18),
x_attribute19=>NVL(military_rec.attribute19,dup_milit_rec.attribute19),
x_attribute20=> NVL(military_rec.attribute20,dup_milit_rec.attribute20),
x_military_type_cd=>NVL(military_rec.military_type_cd,dup_milit_rec.military_type_cd),
x_separation_type_cd=>NVL(military_rec.separation_type_cd,dup_milit_rec.separation_type_cd),
x_assistance_type_cd=> NVL(military_rec.assistance_type_cd,dup_milit_rec.assistance_type_cd),
x_assistance_status_cd => NVL(military_rec.assistance_status_cd,dup_milit_rec.assistance_status_cd),
x_mode=>'R');
UPDATE igs_ad_military_int_all
SET match_ind =cst_mi_val_18,
status = cst_stat_val_1
WHERE interface_military_id = military_rec.interface_military_id;
UPDATE igs_ad_military_int_all
SET ERROR_CODE = 'E014',
status = '3'
WHERE interface_military_id = military_rec.interface_military_id;
UPDATE igs_ad_military_int_all
SET status = l_status,
ERROR_CODE = l_error_code
WHERE interface_military_id= military_rec.interface_military_id;
SELECT ai.*, i.person_id
FROM igs_pe_immu_dtl_int ai, igs_ad_interface_all i
WHERE ai.interface_run_id = cp_interface_run_id
AND ai.interface_id = i.interface_id
AND ai.interface_run_id = cp_interface_run_id
AND ai.status = '2';
SELECT ROWID, mi.*
FROM igs_pe_immu_dtls mi
WHERE person_id =cp_person_id
AND immunization_code = cp_immu_code
AND TRUNC(start_date) =TRUNC(cp_start_date);
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = p_person_id;
UPDATE igs_pe_immu_dtl_int
SET status = '3',
error_code = p_error_code
WHERE interface_immu_dtls_id = p_health_insur_rec.interface_immu_dtls_id;
igs_pe_immu_dtls_pkg.INSERT_ROW (
x_rowid => l_rowid,
x_immu_details_id => l_immu_details_id,
x_person_id => health_insur_rec.person_id,
x_immunization_code => health_insur_rec.immunization_code,
x_status_code => health_insur_rec.status_code,
x_start_date => health_insur_rec.start_date,
x_end_date => health_insur_rec.end_date,
X_ATTRIBUTE_CATEGORY => health_insur_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => health_insur_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => health_insur_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => health_insur_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => health_insur_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => health_insur_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => health_insur_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => health_insur_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => health_insur_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => health_insur_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => health_insur_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => health_insur_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => health_insur_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => health_insur_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => health_insur_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => health_insur_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => health_insur_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => health_insur_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => health_insur_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => health_insur_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => health_insur_rec.ATTRIBUTE20,
x_MODE => 'R');
UPDATE igs_pe_immu_dtl_int
SET status = '1',
ERROR_CODE = p_error_code
WHERE interface_immu_dtls_id = health_insur_rec.interface_immu_dtls_id;
UPDATE igs_pe_immu_dtl_int
SET status = p_status,
ERROR_CODE = p_error_code
WHERE interface_immu_dtls_id = health_insur_rec.interface_immu_dtls_id;
|| ' Exception from IGS_PE_IMMU_DTLS_PKG.INSERT_ROW '
|| ' Interface Id : '
|| (health_insur_rec.interface_immu_dtls_id)
|| ' Status : 3'|| ' ErrorCode : E159' ||SQLERRM;
UPDATE igs_pe_immu_dtl_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_immu_dtl_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_immu_dtls 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.immunization_code = UPPER(mi.immunization_code)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
UPDATE igs_pe_immu_dtl_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_immu_dtl_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_immu_dtl_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_immu_dtls 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.immunization_code = UPPER(mi.immunization_code)
AND pe.status_code = UPPER(mi.status_code)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
);
UPDATE igs_pe_immu_dtl_int mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_immu_details_id = (SELECT pe.immu_details_id
FROM igs_pe_immu_dtls 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.immunization_code = UPPER(mi.immunization_code)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
)
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_immu_dtls 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.immunization_code = UPPER(mi.immunization_code)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
UPDATE igs_pe_immu_dtl_int
SET ERROR_CODE ='E255',
status = '3'
WHERE INTERFACE_IMMU_DTLS_ID = pe_health_rec.interface_immu_dtls_id;
igs_pe_immu_dtls_pkg.UPDATE_ROW
(
x_rowid => dup_chk_health_rec.ROWID,
x_start_date => NVL(pe_health_rec.start_date,dup_chk_health_rec.start_date),
x_end_date => NVL(pe_health_rec.end_date,dup_chk_health_rec.start_date),
X_ATTRIBUTE_CATEGORY => NVL(pe_health_rec.ATTRIBUTE_CATEGORY, dup_chk_health_rec.ATTRIBUTE_CATEGORY),
X_ATTRIBUTE1 => NVL(pe_health_rec.ATTRIBUTE1, dup_chk_health_rec.ATTRIBUTE1),
X_ATTRIBUTE2 => NVL(pe_health_rec.ATTRIBUTE2, dup_chk_health_rec.ATTRIBUTE2),
X_ATTRIBUTE3 => NVL(pe_health_rec.ATTRIBUTE3, dup_chk_health_rec.ATTRIBUTE3),
X_ATTRIBUTE4 => NVL(pe_health_rec.ATTRIBUTE4, dup_chk_health_rec.ATTRIBUTE4),
X_ATTRIBUTE5 => NVL(pe_health_rec.ATTRIBUTE5, dup_chk_health_rec.ATTRIBUTE5),
X_ATTRIBUTE6 => NVL(pe_health_rec.ATTRIBUTE6, dup_chk_health_rec.ATTRIBUTE6),
X_ATTRIBUTE7 => NVL(pe_health_rec.ATTRIBUTE7, dup_chk_health_rec.ATTRIBUTE7),
X_ATTRIBUTE8 => NVL(pe_health_rec.ATTRIBUTE8, dup_chk_health_rec.ATTRIBUTE8),
X_ATTRIBUTE9 => NVL(pe_health_rec.ATTRIBUTE9, dup_chk_health_rec.ATTRIBUTE9),
X_ATTRIBUTE10 => NVL(pe_health_rec.ATTRIBUTE10, dup_chk_health_rec.ATTRIBUTE10),
X_ATTRIBUTE11 => NVL(pe_health_rec.ATTRIBUTE11, dup_chk_health_rec.ATTRIBUTE11),
X_ATTRIBUTE12 => NVL(pe_health_rec.ATTRIBUTE12, dup_chk_health_rec.ATTRIBUTE12),
X_ATTRIBUTE13 => NVL(pe_health_rec.ATTRIBUTE13, dup_chk_health_rec.ATTRIBUTE13),
X_ATTRIBUTE14 => NVL(pe_health_rec.ATTRIBUTE14, dup_chk_health_rec.ATTRIBUTE14),
X_ATTRIBUTE15 => NVL(pe_health_rec.ATTRIBUTE15, dup_chk_health_rec.ATTRIBUTE15),
X_ATTRIBUTE16 => NVL(pe_health_rec.ATTRIBUTE16, dup_chk_health_rec.ATTRIBUTE16),
X_ATTRIBUTE17 => NVL(pe_health_rec.ATTRIBUTE17, dup_chk_health_rec.ATTRIBUTE17),
X_ATTRIBUTE18 => NVL(pe_health_rec.ATTRIBUTE18, dup_chk_health_rec.ATTRIBUTE18),
X_ATTRIBUTE19 => NVL(pe_health_rec.ATTRIBUTE19, dup_chk_health_rec.ATTRIBUTE19),
X_ATTRIBUTE20 => NVL(pe_health_rec.ATTRIBUTE20, dup_chk_health_rec.ATTRIBUTE20),
x_status_code => NVL(pe_health_rec.status_code, dup_chk_health_rec.status_code),
x_immunization_code => NVL(pe_health_rec.immunization_code,dup_chk_health_rec.immunization_code),
x_IMMU_DETAILS_ID => dup_chk_health_rec.IMMU_DETAILS_ID,
x_PERSON_ID => NVL(pe_health_rec.PERSON_ID,dup_chk_health_rec.PERSON_ID),
x_mode =>'R'
);
UPDATE igs_pe_immu_dtl_int
SET match_ind = cst_mi_val_18,
status = l_status,
ERROR_CODE = l_error_code
WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
l_error_code := 'E160'; -- Could not update Immunization details
UPDATE igs_pe_immu_dtl_int
SET status = l_status,
ERROR_CODE = l_error_code
WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
UPDATE igs_pe_immu_dtl_int
SET ERROR_CODE ='E255',
status = '3'
WHERE INTERFACE_IMMU_DTLS_ID = pe_health_rec.INTERFACE_IMMU_DTLS_ID;
igs_pe_immu_dtls_pkg.UPDATE_ROW
(
x_rowid => dup_chk_health_rec.ROWID,
x_start_date => NVL(pe_health_rec.start_date,dup_chk_health_rec.start_date),
x_end_date => NVL(pe_health_rec.end_date,dup_chk_health_rec.end_date),
X_ATTRIBUTE_CATEGORY => NVL(pe_health_rec.ATTRIBUTE_CATEGORY, dup_chk_health_rec.ATTRIBUTE_CATEGORY),
X_ATTRIBUTE1 => NVL(pe_health_rec.ATTRIBUTE1, dup_chk_health_rec.ATTRIBUTE1),
X_ATTRIBUTE2 => NVL(pe_health_rec.ATTRIBUTE2, dup_chk_health_rec.ATTRIBUTE2),
X_ATTRIBUTE3 => NVL(pe_health_rec.ATTRIBUTE3, dup_chk_health_rec.ATTRIBUTE3),
X_ATTRIBUTE4 => NVL(pe_health_rec.ATTRIBUTE4, dup_chk_health_rec.ATTRIBUTE4),
X_ATTRIBUTE5 => NVL(pe_health_rec.ATTRIBUTE5, dup_chk_health_rec.ATTRIBUTE5),
X_ATTRIBUTE6 => NVL(pe_health_rec.ATTRIBUTE6, dup_chk_health_rec.ATTRIBUTE6),
X_ATTRIBUTE7 => NVL(pe_health_rec.ATTRIBUTE7, dup_chk_health_rec.ATTRIBUTE7),
X_ATTRIBUTE8 => NVL(pe_health_rec.ATTRIBUTE8, dup_chk_health_rec.ATTRIBUTE8),
X_ATTRIBUTE9 => NVL(pe_health_rec.ATTRIBUTE9, dup_chk_health_rec.ATTRIBUTE9),
X_ATTRIBUTE10 => NVL(pe_health_rec.ATTRIBUTE10, dup_chk_health_rec.ATTRIBUTE10),
X_ATTRIBUTE11 => NVL(pe_health_rec.ATTRIBUTE11, dup_chk_health_rec.ATTRIBUTE11),
X_ATTRIBUTE12 => NVL(pe_health_rec.ATTRIBUTE12, dup_chk_health_rec.ATTRIBUTE12),
X_ATTRIBUTE13 => NVL(pe_health_rec.ATTRIBUTE13, dup_chk_health_rec.ATTRIBUTE13),
X_ATTRIBUTE14 => NVL(pe_health_rec.ATTRIBUTE14, dup_chk_health_rec.ATTRIBUTE14),
X_ATTRIBUTE15 => NVL(pe_health_rec.ATTRIBUTE15, dup_chk_health_rec.ATTRIBUTE15),
X_ATTRIBUTE16 => NVL(pe_health_rec.ATTRIBUTE16, dup_chk_health_rec.ATTRIBUTE16),
X_ATTRIBUTE17 => NVL(pe_health_rec.ATTRIBUTE17, dup_chk_health_rec.ATTRIBUTE17),
X_ATTRIBUTE18 => NVL(pe_health_rec.ATTRIBUTE18, dup_chk_health_rec.ATTRIBUTE18),
X_ATTRIBUTE19 => NVL(pe_health_rec.ATTRIBUTE19, dup_chk_health_rec.ATTRIBUTE19),
X_ATTRIBUTE20 => NVL(pe_health_rec.ATTRIBUTE20, dup_chk_health_rec.ATTRIBUTE20),
x_status_code => NVL(pe_health_rec.status_code,dup_chk_health_rec.status_code),
x_immunization_code => NVL(pe_health_rec.immunization_code,dup_chk_health_rec.immunization_code),
x_IMMU_DETAILS_ID => dup_chk_health_rec.immu_details_id,
x_PERSON_ID => NVL(pe_health_rec.PERSON_ID,dup_chk_health_rec.person_id),
x_mode =>'R'
);
UPDATE igs_pe_immu_dtl_int
SET match_ind = cst_mi_val_18,
status = l_status,
ERROR_CODE = l_error_code
WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
l_error_code := 'E160'; -- Could not update Immunization details
UPDATE igs_pe_immu_dtl_int
SET status = l_status,
ERROR_CODE = l_error_code
WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
SELECT hii.*, i.person_id
FROM igs_ad_hlth_ins_int_all hii, igs_ad_interface_all i
WHERE hii.interface_run_id = cp_interface_run_id
AND i.interface_id = hii.interface_id
AND i.interface_run_id = cp_interface_run_id
AND hii.status = '2';
IGS_PE_HLTH_INS_PKG.INSERT_ROW (
x_ROWID => l_rowid,
x_HEALTH_INS_ID => l_hlth_id ,
x_PERSON_ID => HLTH_INS_REC.PERSON_ID ,
x_INSURANCE_CD => HLTH_INS_REC.INSURANCE_CD ,
x_INSURANCE_PROVIDER => HLTH_INS_REC.INSURANCE_PROVIDER ,
x_POLICY_NUMBER => HLTH_INS_REC.POLICY_NUMBER ,
x_START_DATE => HLTH_INS_REC.START_DATE ,
x_END_DATE => HLTH_INS_REC.END_DATE ,
X_ATTRIBUTE_CATEGORY => HLTH_INS_REC.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => HLTH_INS_REC.ATTRIBUTE1,
X_ATTRIBUTE2 => HLTH_INS_REC.ATTRIBUTE2,
X_ATTRIBUTE3 => HLTH_INS_REC.ATTRIBUTE3,
X_ATTRIBUTE4 => HLTH_INS_REC.ATTRIBUTE4,
X_ATTRIBUTE5 => HLTH_INS_REC.ATTRIBUTE5,
X_ATTRIBUTE6 => HLTH_INS_REC.ATTRIBUTE6,
X_ATTRIBUTE7 => HLTH_INS_REC.ATTRIBUTE7,
X_ATTRIBUTE8 => HLTH_INS_REC.ATTRIBUTE8,
X_ATTRIBUTE9 => HLTH_INS_REC.ATTRIBUTE9,
X_ATTRIBUTE10 => HLTH_INS_REC.ATTRIBUTE10,
X_ATTRIBUTE11 => HLTH_INS_REC.ATTRIBUTE11,
X_ATTRIBUTE12 => HLTH_INS_REC.ATTRIBUTE12,
X_ATTRIBUTE13 => HLTH_INS_REC.ATTRIBUTE13,
X_ATTRIBUTE14 => HLTH_INS_REC.ATTRIBUTE14,
X_ATTRIBUTE15 => HLTH_INS_REC.ATTRIBUTE15,
X_ATTRIBUTE16 => HLTH_INS_REC.ATTRIBUTE16,
X_ATTRIBUTE17 => HLTH_INS_REC.ATTRIBUTE17,
X_ATTRIBUTE18 => HLTH_INS_REC.ATTRIBUTE18,
X_ATTRIBUTE19 => HLTH_INS_REC.ATTRIBUTE19,
X_ATTRIBUTE20 => HLTH_INS_REC.ATTRIBUTE20,
X_MODE => 'R',
X_org_id => l_org_id );
'Status : 3' || 'ErrorCode : E322 insert failed ' || SQLERRM;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id= p_person_id;
UPDATE igs_ad_hlth_ins_int
SET error_code = p_error_code,
status = '3'
WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
UPDATE igs_ad_hlth_ins_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_hlth_ins_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_hlth_ins_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.insurance_cd = UPPER(mi.insurance_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date) );
UPDATE igs_ad_hlth_ins_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_hlth_ins_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status=cst_stat_val_2;
UPDATE igs_ad_hlth_ins_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_hlth_ins_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.insurance_cd = UPPER(mi.insurance_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
AND UPPER(pe.insurance_provider) = UPPER(mi.insurance_provider)
AND UPPER(pe.policy_number) = UPPER(mi.policy_number)
AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
AND NVL(UPPER(pe.attribute1),'*!*') = NVL(UPPER(mi.attribute1),'*!*')
AND NVL(UPPER(pe.attribute2),'*!*') = NVL(UPPER(mi.attribute2),'*!*')
AND NVL(UPPER(pe.attribute3),'*!*') = NVL(UPPER(mi.attribute3),'*!*')
AND NVL(UPPER(pe.attribute4),'*!*') = NVL(UPPER(mi.attribute4),'*!*')
AND NVL(UPPER(pe.attribute5),'*!*') = NVL(UPPER(mi.attribute5),'*!*')
AND NVL(UPPER(pe.attribute6),'*!*') = NVL(UPPER(mi.attribute6),'*!*')
AND NVL(UPPER(pe.attribute7),'*!*') = NVL(UPPER(mi.attribute7),'*!*')
AND NVL(UPPER(pe.attribute8),'*!*') = NVL(UPPER(mi.attribute8),'*!*')
AND NVL(UPPER(pe.attribute9),'*!*') = NVL(UPPER(mi.attribute9),'*!*')
AND NVL(UPPER(pe.attribute10),'*!*') = NVL(UPPER(mi.attribute10),'*!*')
AND NVL(UPPER(pe.attribute11),'*!*') = NVL(UPPER(mi.attribute11),'*!*')
AND NVL(UPPER(pe.attribute12),'*!*') = NVL(UPPER(mi.attribute12),'*!*')
AND NVL(UPPER(pe.attribute13),'*!*') = NVL(UPPER(mi.attribute13),'*!*')
AND NVL(UPPER(pe.attribute14),'*!*') = NVL(UPPER(mi.attribute14),'*!*')
AND NVL(UPPER(pe.attribute15),'*!*') = NVL(UPPER(mi.attribute15),'*!*')
AND NVL(UPPER(pe.attribute16),'*!*') = NVL(UPPER(mi.attribute16),'*!*')
AND NVL(UPPER(pe.attribute17),'*!*') = NVL(UPPER(mi.attribute17),'*!*')
AND NVL(UPPER(pe.attribute18),'*!*') = NVL(UPPER(mi.attribute18),'*!*')
AND NVL(UPPER(pe.attribute19),'*!*') = NVL(UPPER(mi.attribute19),'*!*')
AND NVL(UPPER(pe.attribute20),'*!*') = NVL(UPPER(mi.attribute20),'*!*')
);
UPDATE igs_ad_hlth_ins_int_all mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_hlth_ins_id = (SELECT health_ins_id
FROM igs_pe_hlth_ins_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.insurance_cd = UPPER(mi.insurance_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date))
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_hlth_ins_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.insurance_cd = UPPER(mi.insurance_cd)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
SELECT rowid, hi.*
FROM igs_pe_hlth_ins hi
WHERE hi.person_id = cp_person_id
AND UPPER(hi.insurance_cd) = UPPER(cp_insurance_cd)
AND TRUNC(hi.start_date) = TRUNC(cp_start_date);
igs_pe_hlth_ins_pkg.update_row(
x_rowid=>dup_pe_hlthins_rec.rowid,
x_health_ins_id=>dup_pe_hlthins_rec.health_ins_id,
x_person_id=>dup_pe_hlthins_rec.person_id,
x_insurance_provider=> NVL(hlth_ins_rec.insurance_provider,dup_pe_hlthins_rec.insurance_provider),
x_policy_number=> NVL(hlth_ins_rec.policy_number,dup_pe_hlthins_rec.policy_number),
x_start_date=> NVL(hlth_ins_rec.start_date,dup_pe_hlthins_rec.start_date),
x_end_date=> NVL(hlth_ins_rec.end_date,dup_pe_hlthins_rec.end_date),
X_ATTRIBUTE_CATEGORY => NVL(HLTH_INS_REC.ATTRIBUTE_CATEGORY, dup_pe_hlthins_rec.ATTRIBUTE_CATEGORY),
X_ATTRIBUTE1 => NVL(HLTH_INS_REC.ATTRIBUTE1, dup_pe_hlthins_rec.ATTRIBUTE1),
X_ATTRIBUTE2 => NVL(HLTH_INS_REC.ATTRIBUTE2, dup_pe_hlthins_rec.ATTRIBUTE2),
X_ATTRIBUTE3 => NVL(HLTH_INS_REC.ATTRIBUTE3, dup_pe_hlthins_rec.ATTRIBUTE3),
X_ATTRIBUTE4 => NVL(HLTH_INS_REC.ATTRIBUTE4, dup_pe_hlthins_rec.ATTRIBUTE4),
X_ATTRIBUTE5 => NVL(HLTH_INS_REC.ATTRIBUTE5, dup_pe_hlthins_rec.ATTRIBUTE5),
X_ATTRIBUTE6 => NVL(HLTH_INS_REC.ATTRIBUTE6, dup_pe_hlthins_rec.ATTRIBUTE6),
X_ATTRIBUTE7 => NVL(HLTH_INS_REC.ATTRIBUTE7, dup_pe_hlthins_rec.ATTRIBUTE7),
X_ATTRIBUTE8 => NVL(HLTH_INS_REC.ATTRIBUTE8, dup_pe_hlthins_rec.ATTRIBUTE8),
X_ATTRIBUTE9 => NVL(HLTH_INS_REC.ATTRIBUTE9, dup_pe_hlthins_rec.ATTRIBUTE9),
X_ATTRIBUTE10 => NVL(HLTH_INS_REC.ATTRIBUTE10, dup_pe_hlthins_rec.ATTRIBUTE10),
X_ATTRIBUTE11 => NVL(HLTH_INS_REC.ATTRIBUTE11, dup_pe_hlthins_rec.ATTRIBUTE11),
X_ATTRIBUTE12 => NVL(HLTH_INS_REC.ATTRIBUTE12, dup_pe_hlthins_rec.ATTRIBUTE12),
X_ATTRIBUTE13 => NVL(HLTH_INS_REC.ATTRIBUTE13, dup_pe_hlthins_rec.ATTRIBUTE13),
X_ATTRIBUTE14 => NVL(HLTH_INS_REC.ATTRIBUTE14, dup_pe_hlthins_rec.ATTRIBUTE14),
X_ATTRIBUTE15 => NVL(HLTH_INS_REC.ATTRIBUTE15, dup_pe_hlthins_rec.ATTRIBUTE15),
X_ATTRIBUTE16 => NVL(HLTH_INS_REC.ATTRIBUTE16, dup_pe_hlthins_rec.ATTRIBUTE16),
X_ATTRIBUTE17 => NVL(HLTH_INS_REC.ATTRIBUTE17, dup_pe_hlthins_rec.ATTRIBUTE17),
X_ATTRIBUTE18 => NVL(HLTH_INS_REC.ATTRIBUTE18, dup_pe_hlthins_rec.ATTRIBUTE18),
X_ATTRIBUTE19 => NVL(HLTH_INS_REC.ATTRIBUTE19, dup_pe_hlthins_rec.ATTRIBUTE19),
X_ATTRIBUTE20 => NVL(HLTH_INS_REC.ATTRIBUTE20, dup_pe_hlthins_rec.ATTRIBUTE20),
x_mode =>'R',
x_insurance_cd=> NVL(hlth_ins_rec.insurance_cd,dup_pe_hlthins_rec.insurance_cd));
UPDATE igs_ad_hlth_ins_int
SET error_code = NULL,
match_ind = cst_mi_val_18,
status = cst_stat_val_1
WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
UPDATE igs_ad_hlth_ins_int
SET error_code = 'E014',
status = '3'
WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
' Status : 3 ' || 'ErrorCode : E014 Update Failed ' || SQLERRM;
igs_pe_hlth_ins_pkg.update_row(
x_rowid=>dup_pe_hlthins_rec.rowid,
x_health_ins_id=>dup_pe_hlthins_rec.health_ins_id,
x_person_id=>dup_pe_hlthins_rec.person_id,
x_insurance_cd=> NVL(hlth_ins_rec.insurance_cd,dup_pe_hlthins_rec.insurance_cd),
x_insurance_provider=> NVL(hlth_ins_rec.insurance_provider,dup_pe_hlthins_rec.insurance_provider),
x_policy_number=> NVL(hlth_ins_rec.policy_number,dup_pe_hlthins_rec.policy_number),
x_start_date=> NVL(hlth_ins_rec.start_date,dup_pe_hlthins_rec.start_date),
x_end_date=> NVL(hlth_ins_rec.end_date,dup_pe_hlthins_rec.end_date),
X_ATTRIBUTE_CATEGORY => NVL(HLTH_INS_REC.ATTRIBUTE_CATEGORY, dup_pe_hlthins_rec.ATTRIBUTE_CATEGORY),
X_ATTRIBUTE1 => NVL(HLTH_INS_REC.ATTRIBUTE1, dup_pe_hlthins_rec.ATTRIBUTE1),
X_ATTRIBUTE2 => NVL(HLTH_INS_REC.ATTRIBUTE2, dup_pe_hlthins_rec.ATTRIBUTE2),
X_ATTRIBUTE3 => NVL(HLTH_INS_REC.ATTRIBUTE3, dup_pe_hlthins_rec.ATTRIBUTE3),
X_ATTRIBUTE4 => NVL(HLTH_INS_REC.ATTRIBUTE4, dup_pe_hlthins_rec.ATTRIBUTE4),
X_ATTRIBUTE5 => NVL(HLTH_INS_REC.ATTRIBUTE5, dup_pe_hlthins_rec.ATTRIBUTE5),
X_ATTRIBUTE6 => NVL(HLTH_INS_REC.ATTRIBUTE6, dup_pe_hlthins_rec.ATTRIBUTE6),
X_ATTRIBUTE7 => NVL(HLTH_INS_REC.ATTRIBUTE7, dup_pe_hlthins_rec.ATTRIBUTE7),
X_ATTRIBUTE8 => NVL(HLTH_INS_REC.ATTRIBUTE8, dup_pe_hlthins_rec.ATTRIBUTE8),
X_ATTRIBUTE9 => NVL(HLTH_INS_REC.ATTRIBUTE9, dup_pe_hlthins_rec.ATTRIBUTE9),
X_ATTRIBUTE10 => NVL(HLTH_INS_REC.ATTRIBUTE10, dup_pe_hlthins_rec.ATTRIBUTE10),
X_ATTRIBUTE11 => NVL(HLTH_INS_REC.ATTRIBUTE11, dup_pe_hlthins_rec.ATTRIBUTE11),
X_ATTRIBUTE12 => NVL(HLTH_INS_REC.ATTRIBUTE12, dup_pe_hlthins_rec.ATTRIBUTE12),
X_ATTRIBUTE13 => NVL(HLTH_INS_REC.ATTRIBUTE13, dup_pe_hlthins_rec.ATTRIBUTE13),
X_ATTRIBUTE14 => NVL(HLTH_INS_REC.ATTRIBUTE14, dup_pe_hlthins_rec.ATTRIBUTE14),
X_ATTRIBUTE15 => NVL(HLTH_INS_REC.ATTRIBUTE15, dup_pe_hlthins_rec.ATTRIBUTE15),
X_ATTRIBUTE16 => NVL(HLTH_INS_REC.ATTRIBUTE16, dup_pe_hlthins_rec.ATTRIBUTE16),
X_ATTRIBUTE17 => NVL(HLTH_INS_REC.ATTRIBUTE17, dup_pe_hlthins_rec.ATTRIBUTE17),
X_ATTRIBUTE18 => NVL(HLTH_INS_REC.ATTRIBUTE18, dup_pe_hlthins_rec.ATTRIBUTE18),
X_ATTRIBUTE19 => NVL(HLTH_INS_REC.ATTRIBUTE19, dup_pe_hlthins_rec.ATTRIBUTE19),
X_ATTRIBUTE20 => NVL(HLTH_INS_REC.ATTRIBUTE20, dup_pe_hlthins_rec.ATTRIBUTE20),
x_mode =>'R');
UPDATE igs_ad_hlth_ins_int
SET error_code = NULL,
match_ind = cst_mi_val_18,
status = cst_stat_val_1
WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
UPDATE igs_ad_hlth_ins_int
SET error_code = 'E014',
status = '3'
WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
'Status : 3' || 'ErrorCode : E014 update Failed ' || SQLERRM;
UPDATE IGS_AD_HLTH_INS_INT
SET status = l_status,
error_code = l_error_code
WHERE INTERFACE_HLTH_ID = hlth_ins_rec.INTERFACE_HLTH_ID;
|| Added the DFF validation before insert/update to the oss table, also in
|| the call to insert_row/update_row to the oss table adding the dff columns
|| (reverse chronological order - newest change first)
*/
-- Logic for IGS_AD_IMPORT_PERSON_ID_TYPES
-- Create the cursor using the following select statement.
CURSOR API(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
SELECT mi.*, i.person_id
FROM igs_ad_api_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'
AND i.status = '1';
SELECT ROWID,pi.*
FROM igs_pe_alt_pers_id pi
WHERE pe_person_id = api_rec.person_id
AND api_person_id = api_rec.alternate_id
AND UPPER(person_id_type) = UPPER(api_rec.person_id_type)
AND TRUNC(start_dt) = TRUNC(api_rec.start_dt);
SELECT source_type_id
FROM igs_pe_src_types_all
WHERE source_type = cp_source_type;
SELECT format_mask, region_ind
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_person_id_type
AND closed_ind = 'N';
SELECT 'X'
FROM hz_geographies
WHERE GEOGRAPHY_TYPE = cp_geography_type
AND geography_code = cp_geography_cd
AND COUNTRY_CODE = cp_country_cd;
UPDATE igs_ad_api_int_all
SET status = '3',
error_code = l_error_code
WHERE interface_api_id = p_api_rec.interface_api_id;
|| Added the DFF validation before insert to the oss table, also in
|| the call to insert_row to the oss table adding the dff columns
|| pkpatel 25-JUN-2001 Bug no.1834307 :Modeling and Forecasting SDQ DLD
|| Modified code to refer igs_ad_interface_dtl_dscp_v instead of
|| igs_ad_interface due to change in signature of Igs_Ad_Imp_002.Update_Person .
|| (reverse chronological order - newest change first)
*/
l_rowid VARCHAR2(25);
SAVEPOINT before_api_insert;
igs_pe_alt_pers_id_pkg.insert_row(
X_ROWID =>l_rowid,
X_PE_PERSON_ID =>p_api_rec.person_id,
X_API_PERSON_ID =>p_api_rec.alternate_id,
X_PERSON_ID_TYPE =>p_api_rec.person_id_type,
X_START_DT =>p_api_rec.start_dt,
X_END_DT =>p_api_rec.end_dt,
X_MODE =>'R',
X_ATTRIBUTE_CATEGORY =>p_api_rec.attribute_category ,
X_ATTRIBUTE1 =>p_api_rec.attribute1 ,
X_ATTRIBUTE2 =>p_api_rec.attribute2 ,
X_ATTRIBUTE3 =>p_api_rec.attribute3 ,
X_ATTRIBUTE4 =>p_api_rec.attribute4 ,
X_ATTRIBUTE5 =>p_api_rec.attribute5 ,
X_ATTRIBUTE6 =>p_api_rec.attribute6 ,
X_ATTRIBUTE7 =>p_api_rec.attribute7 ,
X_ATTRIBUTE8 =>p_api_rec.attribute8 ,
X_ATTRIBUTE9 =>p_api_rec.attribute9 ,
X_ATTRIBUTE10 =>p_api_rec.attribute10 ,
X_ATTRIBUTE11 =>p_api_rec.attribute11 ,
X_ATTRIBUTE12 =>p_api_rec.attribute12 ,
X_ATTRIBUTE13 =>p_api_rec.attribute13 ,
X_ATTRIBUTE14 =>p_api_rec.attribute14 ,
X_ATTRIBUTE15 =>p_api_rec.attribute15 ,
X_ATTRIBUTE16 =>p_api_rec.attribute16 ,
X_ATTRIBUTE17 =>p_api_rec.attribute17 ,
X_ATTRIBUTE18 =>p_api_rec.attribute18 ,
X_ATTRIBUTE19 =>p_api_rec.attribute19 ,
X_ATTRIBUTE20 =>p_api_rec.attribute20 ,
X_REGION_CD =>p_api_rec.region_cd );
ROLLBACK TO before_api_insert;
UPDATE igs_ad_api_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 interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_ad_api_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_alt_pers_id 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.pe_person_id
AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt) );
UPDATE igs_ad_api_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_api_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status = cst_stat_val_2;
UPDATE igs_ad_api_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_alt_pers_id 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.pe_person_id
AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt)
AND NVL(TRUNC(pe.end_dt),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_dt),igs_ge_date.igsdate('9999/01/01'))
AND NVL(UPPER(pe.attribute_category), '*') = NVL(UPPER(mi.attribute_category), '*')
AND NVL(UPPER(pe.region_cd), '*') = NVL(UPPER(mi.region_cd), '*')
AND NVL(UPPER(pe.attribute1),'*!*') = NVL(UPPER(mi.attribute1),'*!*')
AND NVL(UPPER(pe.attribute2),'*!*') = NVL(UPPER(mi.attribute2),'*!*')
AND NVL(UPPER(pe.attribute3),'*!*') = NVL(UPPER(mi.attribute3),'*!*')
AND NVL(UPPER(pe.attribute4),'*!*') = NVL(UPPER(mi.attribute4),'*!*')
AND NVL(UPPER(pe.attribute5),'*!*') = NVL(UPPER(mi.attribute5),'*!*')
AND NVL(UPPER(pe.attribute6),'*!*') = NVL(UPPER(mi.attribute6),'*!*')
AND NVL(UPPER(pe.attribute7),'*!*') = NVL(UPPER(mi.attribute7),'*!*')
AND NVL(UPPER(pe.attribute8),'*!*') = NVL(UPPER(mi.attribute8),'*!*')
AND NVL(UPPER(pe.attribute9),'*!*') = NVL(UPPER(mi.attribute9),'*!*')
AND NVL(UPPER(pe.attribute10),'*!*') = NVL(UPPER(mi.attribute10),'*!*')
AND NVL(UPPER(pe.attribute11),'*!*') = NVL(UPPER(mi.attribute11),'*!*')
AND NVL(UPPER(pe.attribute12),'*!*') = NVL(UPPER(mi.attribute12),'*!*')
AND NVL(UPPER(pe.attribute13),'*!*') = NVL(UPPER(mi.attribute13),'*!*')
AND NVL(UPPER(pe.attribute14),'*!*') = NVL(UPPER(mi.attribute14),'*!*')
AND NVL(UPPER(pe.attribute15),'*!*') = NVL(UPPER(mi.attribute15),'*!*')
AND NVL(UPPER(pe.attribute16),'*!*') = NVL(UPPER(mi.attribute16),'*!*')
AND NVL(UPPER(pe.attribute17),'*!*') = NVL(UPPER(mi.attribute17),'*!*')
AND NVL(UPPER(pe.attribute18),'*!*') = NVL(UPPER(mi.attribute18),'*!*')
AND NVL(UPPER(pe.attribute19),'*!*') = NVL(UPPER(mi.attribute19),'*!*')
AND NVL(UPPER(pe.attribute20),'*!*') = NVL(UPPER(mi.attribute20),'*!*')
);
UPDATE igs_ad_api_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_alt_pers_id 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.pe_person_id
AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt) );
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = NULL,
STATUS = '1'
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
UPDATE IGS_AD_API_INT_ALL
SET error_code = l_ucas_error_code,
STATUS = '3'
WHERE interface_api_id = api_rec.interface_api_id;
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = l_error_code,
STATUS = l_status
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
SAVEPOINT before_api_update;
igs_pe_alt_pers_id_pkg.update_row(
x_rowid =>check_dur_rec.rowid,
x_pe_person_id =>check_dur_rec.pe_person_id,
x_api_person_id =>check_dur_rec.api_person_id,
x_person_id_type =>NVL(api_rec.person_id_type,check_dur_rec.person_id_type),
x_start_dt => NVL(api_rec.start_dt,check_dur_rec.start_dt),
x_end_dt => NVL(api_rec.end_dt,check_dur_rec.end_dt),
x_mode => 'R',
X_ATTRIBUTE_CATEGORY =>NVL(api_rec.attribute_category ,check_dur_rec.attribute_category),
X_ATTRIBUTE1 =>NVL(api_rec.attribute1 ,check_dur_rec.attribute1),
X_ATTRIBUTE2 =>NVL(api_rec.attribute2 ,check_dur_rec.attribute2),
X_ATTRIBUTE3 =>NVL(api_rec.attribute3 ,check_dur_rec.attribute3),
X_ATTRIBUTE4 =>NVL(api_rec.attribute4 ,check_dur_rec.attribute4),
X_ATTRIBUTE5 =>NVL(api_rec.attribute5 ,check_dur_rec.attribute5),
X_ATTRIBUTE6 =>NVL(api_rec.attribute6 ,check_dur_rec.attribute6),
X_ATTRIBUTE7 =>NVL(api_rec.attribute7 ,check_dur_rec.attribute7),
X_ATTRIBUTE8 =>NVL(api_rec.attribute8 ,check_dur_rec.attribute8),
X_ATTRIBUTE9 =>NVL(api_rec.attribute9 ,check_dur_rec.attribute9),
X_ATTRIBUTE10 =>NVL(api_rec.attribute10 ,check_dur_rec.attribute10),
X_ATTRIBUTE11 =>NVL(api_rec.attribute11 ,check_dur_rec.attribute11),
X_ATTRIBUTE12 =>NVL(api_rec.attribute12 ,check_dur_rec.attribute12),
X_ATTRIBUTE13 =>NVL(api_rec.attribute13 ,check_dur_rec.attribute13),
X_ATTRIBUTE14 =>NVL(api_rec.attribute14 ,check_dur_rec.attribute14),
X_ATTRIBUTE15 =>NVL(api_rec.attribute15 ,check_dur_rec.attribute15),
X_ATTRIBUTE16 =>NVL(api_rec.attribute16 ,check_dur_rec.attribute16),
X_ATTRIBUTE17 =>NVL(api_rec.attribute17 ,check_dur_rec.attribute17),
X_ATTRIBUTE18 =>NVL(api_rec.attribute18 ,check_dur_rec.attribute18),
X_ATTRIBUTE19 =>NVL(api_rec.attribute19 ,check_dur_rec.attribute19),
X_ATTRIBUTE20 =>NVL(api_rec.attribute20 ,check_dur_rec.attribute20),
X_REGION_CD =>NVL(api_rec.region_cd ,check_dur_rec.region_cd));
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = NULL,
MATCH_IND = cst_mi_val_18,
STATUS = cst_stat_val_1
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
ROLLBACK TO before_api_update;
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = l_error_code,
STATUS = '3'
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
SAVEPOINT before_api_update;
igs_pe_alt_pers_id_pkg.update_row(
x_rowid =>check_dur_rec.rowid,
x_pe_person_id =>check_dur_rec.pe_person_id,
x_api_person_id =>check_dur_rec.api_person_id,
x_person_id_type =>NVL(api_rec.person_id_type,check_dur_rec.person_id_type),
x_start_dt => NVL(api_rec.start_dt,check_dur_rec.start_dt),
x_end_dt => NVL(api_rec.end_dt,check_dur_rec.end_dt),
x_mode => 'R',
X_ATTRIBUTE_CATEGORY =>NVL(api_rec.attribute_category ,check_dur_rec.attribute_category),
X_ATTRIBUTE1 =>NVL(api_rec.attribute1 ,check_dur_rec.attribute1),
X_ATTRIBUTE2 =>NVL(api_rec.attribute2 ,check_dur_rec.attribute2),
X_ATTRIBUTE3 =>NVL(api_rec.attribute3 ,check_dur_rec.attribute3),
X_ATTRIBUTE4 =>NVL(api_rec.attribute4 ,check_dur_rec.attribute4),
X_ATTRIBUTE5 =>NVL(api_rec.attribute5 ,check_dur_rec.attribute5),
X_ATTRIBUTE6 =>NVL(api_rec.attribute6 ,check_dur_rec.attribute6),
X_ATTRIBUTE7 =>NVL(api_rec.attribute7 ,check_dur_rec.attribute7),
X_ATTRIBUTE8 =>NVL(api_rec.attribute8 ,check_dur_rec.attribute8),
X_ATTRIBUTE9 =>NVL(api_rec.attribute9 ,check_dur_rec.attribute9),
X_ATTRIBUTE10 =>NVL(api_rec.attribute10 ,check_dur_rec.attribute10),
X_ATTRIBUTE11 =>NVL(api_rec.attribute11 ,check_dur_rec.attribute11),
X_ATTRIBUTE12 =>NVL(api_rec.attribute12 ,check_dur_rec.attribute12),
X_ATTRIBUTE13 =>NVL(api_rec.attribute13 ,check_dur_rec.attribute13),
X_ATTRIBUTE14 =>NVL(api_rec.attribute14 ,check_dur_rec.attribute14),
X_ATTRIBUTE15 =>NVL(api_rec.attribute15 ,check_dur_rec.attribute15),
X_ATTRIBUTE16 =>NVL(api_rec.attribute16 ,check_dur_rec.attribute16),
X_ATTRIBUTE17 =>NVL(api_rec.attribute17 ,check_dur_rec.attribute17),
X_ATTRIBUTE18 =>NVL(api_rec.attribute18 ,check_dur_rec.attribute18),
X_ATTRIBUTE19 =>NVL(api_rec.attribute19 ,check_dur_rec.attribute19),
X_ATTRIBUTE20 =>NVL(api_rec.attribute20 ,check_dur_rec.attribute20),
X_REGION_CD =>NVL(api_rec.region_cd ,check_dur_rec.region_cd));
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = NULL,
MATCH_IND = cst_mi_val_18,
STATUS = cst_stat_val_1
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
ROLLBACK TO before_api_update;
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = l_error_code,
STATUS = '3'
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
UPDATE IGS_AD_API_INT_ALL
SET ERROR_CODE = l_error_code,
STATUS = l_status
WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
SELECT hii.*, i.person_id
FROM igs_pe_citizen_int hii, igs_ad_interface_all i
WHERE hii.interface_run_id = cp_interface_run_id
AND i.interface_id = hii.interface_id
AND i.interface_run_id = cp_interface_run_id
AND hii.status = '2';
l_last_update_date DATE;
l_update_date1 DATE;
l_p_last_update_date DATE;
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = cp_territory_code;
SELECT birth_date FROM
igs_pe_person_base_v WHERE
person_id = cp_person_id;
SELECT count(1) FROM HZ_CITIZENSHIP
WHERE
party_id = PCZ_REC.PERSON_ID AND
UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
( NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
OR
TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
OR
( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
l_last_update DATE;
p_ACTION => 'INSERT',
p_BIRTH_OR_SELECTED => null,
p_COUNTRY_CODE => pcz_rec.country_code,
p_DATE_DISOWNED => pcz_rec.DATE_DISOWNED,
p_DATE_RECOGNIZED => pcz_rec.DATE_RECOGNIZED,
p_DOCUMENT_REFERENCE => pcz_rec.DOCUMENT_REFERENCE,
p_DOCUMENT_TYPE => pcz_rec.DOCUMENT_TYPE,
p_PARTY_ID => pcz_rec.person_ID,
p_END_DATE => pcz_rec.END_DATE,
p_TERRITORY_SHORT_NAME => valid_country_rec.territory_short_name,
p_LAST_UPDATE_DATE => l_last_update_date,
p_CITIZENSHIP_ID => l_citizenship_id,
p_RETURN_STATUS => l_return_status,
p_MSG_COUNT => l_msg_count,
p_MSG_DATA => l_msg_data,
p_object_version_number => l_object_version_number
);
error_code := 'E127'; -- failed in HZ insert
l_debug_str := 'IGS_AD_IMP_007.crt_pe_citizenship Insert into HZ table failed. '
|| 'Interface Citizen Id : '
|| (pcz_rec.interface_citizenship_id)
|| 'Status : 3' || 'ErrorCode : E127'|| l_msg_data;
UPDATE igs_pe_citizen_int
SET status='1'
WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
UPDATE igs_pe_citizen_int
SET error_code = l_error, status ='3'
WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
SELECT 'X'
INTO l_count
FROM hz_citizenship
WHERE party_id = p_person_id
AND country_code = p_country_code
AND TRUNC(date_recognized) = TRUNC(p_date_recognized) ; -- end_date IS NULL check removed.
l_update_date1 DATE;
l_last_update_date DATE;
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = cp_territory_code;
SELECT *
FROM hz_citizenship
WHERE party_id = pcz_rec.person_id
AND country_code =pcz_rec.country_code
AND date_recognized = pcz_rec.date_recognized;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = pcz_rec.person_id;
SELECT COUNT(1)
FROM HZ_CITIZENSHIP
WHERE
party_id = PCZ_REC.PERSON_ID AND
UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
TRUNC(date_recognized) <> TRUNC(PCZ_REC.date_recognized) AND
( NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
OR
TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
OR
( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
p_ACTION => 'UPDATE',
p_BIRTH_OR_SELECTED => null,
p_COUNTRY_CODE => NVL( pcz_rec.country_code,null_hndlg_rec.country_code),
p_DATE_DISOWNED => NVL(pcz_rec.date_disowned,null_hndlg_rec.date_disowned),
p_DATE_RECOGNIZED => NVL(pcz_rec.date_recognized,null_hndlg_rec.date_recognized),
p_DOCUMENT_REFERENCE => NVL(pcz_rec.document_reference,null_hndlg_rec.document_reference),
p_DOCUMENT_TYPE => NVL(pcz_rec.document_type,null_hndlg_rec.document_type),
p_PARTY_ID => NVL(pcz_rec.person_id,null_hndlg_rec.party_id),
p_END_DATE => NVL(pcz_rec.end_date,null_hndlg_rec.end_date),
p_TERRITORY_SHORT_NAME => valid_country_rec.territory_short_name,
p_LAST_UPDATE_DATE => l_last_update_date,
p_CITIZENSHIP_ID => null_hndlg_rec.citizenship_id,
p_RETURN_STATUS => l_return_status,
p_MSG_COUNT => l_msg_count,
p_MSG_DATA => l_msg_data,
p_OBJECT_VERSION_NUMBER => null_hndlg_rec.object_version_number
);
l_debug_str := 'IGS_AD_IMP_007.upd_pe_citizenship Update into HZ table failed. '
|| 'Interface Citizen Id : '
|| (pcz_rec.interface_citizenship_id)
|| ' Status : 3 ' || 'ErrorCode : E128 msg_data: ' || l_msg_data;
UPDATE igs_pe_citizen_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 interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_pe_citizen_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 hz_citizenship 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.party_id
AND UPPER(pe.country_code) = UPPER(mi.country_code)
AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date) );
UPDATE igs_pe_citizen_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_citizen_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status=cst_stat_val_2;
UPDATE igs_pe_citizen_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 hz_citizenship 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.party_id
AND pe.country_code = UPPER(mi.country_code)
AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date)
AND NVL(UPPER(pe.document_reference),'N') = NVL(UPPER(mi.document_reference),'N')
AND NVL(TRUNC(pe.date_disowned),l_default_date) = NVL(TRUNC(mi.date_disowned),l_default_date)
AND NVL(TRUNC(pe.end_date),l_default_date) = NVL(TRUNC(mi.end_date),l_default_date)
AND NVL(UPPER(pe.document_type),'N') =NVL(UPPER(mi.document_type),'N')
);
UPDATE igs_pe_citizen_int mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_citizenship_id = (SELECT citizenship_id
FROM hz_citizenship 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.party_id
AND pe.country_code = UPPER(mi.country_code)
AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),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 EXISTS (SELECT '1'
FROM hz_citizenship 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.party_id
AND pe.country_code = UPPER(mi.country_code)
AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date));
UPDATE igs_pe_citizen_int
SET match_ind = cst_mi_val_18, status = l_status ,error_code = l_error_code
WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
UPDATE igs_pe_citizen_int
SET status = l_status , error_code = l_error_code
WHERE interface_citizenship_id = pcz_rec.interface_citizenship_id;
SELECT ai.*,
i.person_id
FROM igs_pe_fund_src_int ai, igs_ad_interface_all i
WHERE ai.interface_run_id = cp_interface_run_id
AND i.interface_id = ai.interface_id
AND i.interface_run_id = cp_interface_run_id
AND ai.status = '2';
|| Purpose : Local procedure for insert
||
*/
AS
BEGIN
igs_pe_fund_source_pkg.insert_row(
x_rowid => l_rowid,
x_fund_source_id => l_fund_source_id,
x_person_id => pfs_rec.person_id,
x_fund_source_code => pfs_rec.fund_source_code,
x_name => pfs_rec.name,
x_amount => pfs_rec.amount,
x_relationship_code => pfs_rec.relationship_code,
x_document_ind => NVL(pfs_rec.document_ind,'N'),
x_notes => pfs_rec.notes,
x_mode => 'R'
);
|| ' Exception from Igs_Pe_Fund_Source_Pkg.Insert_Row '
|| 'Interface Fund Source Id : '
|| (pfs_rec.interface_fund_source_id)
|| 'Status : 3' || 'ErrorCode : E133 SQLERRM:' || SQLERRM;
UPDATE igs_pe_fund_src_int
SET status = '3',
error_code = l_error
WHERE interface_fund_source_id = pfs_rec.interface_fund_source_id;
UPDATE igs_pe_fund_src_int
SET status = l_status,
error_code = l_error_code
WHERE interface_fund_source_id = pfs_rec.interface_fund_source_id;