[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cst_insert CONSTANT VARCHAR2(20) := 'INSERT';
cst_update CONSTANT VARCHAR2(20) := 'UPDATE';
cst_partial_update CONSTANT VARCHAR2(20) := 'PARTIAL UPDATE';
SELECT pty.*, i.person_id
FROM igs_pe_type_int pty,
igs_ad_interface_all i
WHERE pty.interface_id = i.interface_id AND
pty.status = '2' AND
pty.interface_run_id = cp_interface_run_id AND
i.interface_run_id = cp_interface_run_id;
SELECT rowid,type_instance_id, end_date,emplmnt_category_code
FROM igs_pe_typ_instances_all
WHERE person_id = cp_person_id AND
UPPER(person_type_code) = UPPER(cp_person_type_code) AND
TRUNC(start_date) = TRUNC(cp_start_date);
SELECT person_type_code
FROM igs_pe_per_type_map_v
WHERE system_type = cp_system_type;
SELECT 'X'
FROM igs_pe_type_int pty
WHERE pty.status = '2' AND
pty.person_type_code = cp_person_type_code AND
pty.interface_run_id = cp_interface_run_id;
SELECT user_name
FROM fnd_user
WHERE user_id = cp_user_id;
SELECT 'Y'
FROM igs_pe_typ_instances_all
WHERE person_id = cp_person_id AND
person_type_code = cp_person_type_code AND
( NVL(cp_end_date,cp_default_date) BETWEEN start_date AND NVL(end_date,cp_default_date)
OR cp_start_date BETWEEN start_date AND NVL(end_date,cp_default_date)
OR ( cp_start_date < start_date AND
NVL(end_date,cp_default_date) < NVL(cp_end_date,cp_default_date)));
SELECT NULL
FROM igs_pe_typ_instances_all typ,igs_pe_person_types sys
WHERE typ.person_id = cp_person_id AND
sys.person_type_code = typ.person_type_code AND
sys.system_type IN ('FACULTY','STAFF') AND
( NVL(cp_end_date,cp_default_date) BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
OR cp_start_date BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
OR ( cp_start_date < typ.start_date AND
NVL(typ.end_date,cp_default_date) < NVL(cp_end_date,cp_default_date))) AND
typ.emplmnt_category_code IS NOT NULL;
UPDATE igs_pe_type_int
SET status = '3',
error_code = 'E295'
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
UPDATE igs_pe_type_int
SET status = '3',
error_code = 'E585'
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
igs_pe_typ_instances_pkg.insert_row
(
x_rowid => lv_rowid,
x_person_id => p_person_type_rec.person_id,
x_course_cd => null,
x_type_instance_id => l_type_instance_id,
x_person_type_code => p_person_type_rec.person_type_code,
x_cc_version_number => null,
x_funnel_status => null,
x_admission_appl_number => null,
x_nominated_course_cd => null,
x_ncc_version_number => null,
x_sequence_number => null,
x_start_date => p_person_type_rec.start_date,
x_end_date => p_person_type_rec.end_date,
x_create_method => 'CREATE_IMPORT',
x_ended_by => l_ended_by,
x_end_method => l_end_method,
x_org_id => null,
x_emplmnt_category_code => p_person_type_rec.emplmnt_category_code
);
UPDATE igs_pe_type_int
SET status = '1'
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
UPDATE igs_pe_type_int
SET status = '3',
error_code = l_error_code
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
PROCEDURE update_person_type(p_person_type_rec IN per_type_cur%ROWTYPE,
p_type_instance_id IN igs_pe_typ_instances_all.type_instance_id%TYPE,
p_rowid IN ROWID,
p_end_date IN igs_pe_typ_instances_all.end_date%TYPE,
p_emplmnt_category_code IN igs_pe_typ_instances_all.emplmnt_category_code%TYPE,
p_system_type IN igs_pe_person_types.system_type%TYPE,
p_default_date IN DATE)
AS
l_ended_by fnd_user.user_id%TYPE;
SELECT user_name
FROM fnd_user
WHERE user_id = cp_user_id;
SELECT 'Y'
FROM igs_pe_typ_instances_all
WHERE person_id = cp_person_id AND
person_type_code = cp_person_type_code AND
start_date <> cp_start_date AND
( NVL(cp_end_date,cp_default_date) BETWEEN start_date AND NVL(end_date,cp_default_date)
OR cp_start_date BETWEEN start_date AND NVL(end_date,cp_default_date)
OR ( cp_start_date < start_date AND
NVL(end_date,cp_default_date) < NVL(cp_end_date,cp_default_date)));
SELECT null FROM igs_pe_typ_instances_all typ,igs_pe_person_types sys
WHERE
typ.person_id = cp_person_id AND
sys.person_type_code = typ.person_type_code AND
sys.system_type in ('FACULTY','STAFF') AND
p_rowid <> typ.rowid AND
( NVL(cp_end_date,cp_default_date) BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
OR cp_start_date BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
OR ( cp_start_date < typ.start_date AND
NVL(typ.end_date,cp_default_date) < NVL(cp_end_date,cp_default_date))) AND
typ.emplmnt_category_code IS NOT NULL;
UPDATE igs_pe_type_int
SET status = '3',
error_code = 'E295'
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
UPDATE igs_pe_type_int
SET status = '3',
error_code = 'E585'
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
igs_pe_typ_instances_pkg.update_row
(
x_rowid => p_rowid,
x_person_id => p_person_type_rec.person_id,
x_course_cd => null,
x_type_instance_id => p_type_instance_id,
x_person_type_code => p_person_type_rec.person_type_code,
x_cc_version_number => null,
x_funnel_status => null,
x_admission_appl_number => null,
x_nominated_course_cd => null,
x_ncc_version_number => null,
x_sequence_number => null,
x_start_date => p_person_type_rec.start_date,
x_end_date => nvl(p_person_type_rec.end_date,p_end_date),
x_create_method => 'CREATE_IMPORT',
x_ended_by => l_ended_by,
x_end_method => l_end_method,
x_emplmnt_category_code => nvl(p_person_type_rec.emplmnt_category_code,p_emplmnt_category_code)
);
UPDATE igs_pe_type_int
SET status = '1',
match_ind = '18'
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
l_label := 'igs.plsql.igs_ad_imp_013.update_person_type.exception1';
UPDATE igs_pe_type_int
SET status = '3',
error_code = l_error_code
WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
END update_person_type;
SELECT system_type
FROM igs_pe_person_types
WHERE person_type_code = cp_person_type_code
AND closed_ind = 'N';
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT NULL FROM IGS_LOOKUP_VALUES
WHERE lookup_type = l_lookup_type AND
lookup_code = l_lookup_code AND
enabled_flag = l_enabled_flag;
UPDATE igs_pe_type_int pti
SET pti.status = '3',
pti.error_code = 'E293'
WHERE person_type_code = l_staff_person_type_code AND
status = '2' AND
pti.interface_run_id = l_interface_run_id;
UPDATE igs_pe_type_int pti
SET pti.status = '3',
pti.error_code = 'E293'
WHERE person_type_code = l_faculty_person_type_code AND
status = '2' AND
interface_run_id = l_interface_run_id;
UPDATE igs_pe_type_int pti
SET status = '3',
error_code = 'E695'
WHERE pti.status = '2' AND
pti.interface_run_id = l_interface_run_id AND
pti.match_ind IS NOT NULL;
UPDATE igs_pe_type_int pti
SET status = '1',
match_ind = '19'
WHERE pti.status = '2' AND
pti.interface_run_id = l_interface_run_id AND
pti.match_ind IS NULL AND
EXISTS (SELECT 1
FROM igs_pe_typ_instances_all pi,
igs_ad_interface_all ai
WHERE pti.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
TRUNC(pti.start_date) = TRUNC(pi.start_date));
UPDATE igs_pe_type_int pti
SET status = '1'
WHERE pti.status = '2' AND
pti.interface_run_id = l_interface_run_id AND
pti.match_ind IN ('18','19','22','23');
UPDATE igs_pe_type_int pti
SET status = '3',
error_code = 'E695'
WHERE pti.status = '2' AND
pti.interface_run_id = l_interface_run_id AND
(pti.match_ind IS NOT NULL AND pti.match_ind NOT IN ('21','25'));
UPDATE igs_pe_type_int pti
SET status = '1',
match_ind = '23'
WHERE pti.status = '2' AND
pti.interface_run_id = l_interface_run_id AND
pti.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_typ_instances_all pi,
igs_ad_interface_all ai
WHERE pti.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
NVL(UPPER(pti.emplmnt_category_code),'*!*') = NVL(UPPER(pi.emplmnt_category_code),'*!*') AND
UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
TRUNC(pti.start_date)= TRUNC(pi.start_date) AND
((pti.end_date IS NULL AND pi.end_date IS NULL)
OR (TRUNC(pti.end_date) = TRUNC(pi.end_date)) ));
UPDATE igs_pe_type_int pti
SET status='3',
match_ind='20',
dup_type_instance_id=(SELECT pi.type_instance_id
FROM igs_pe_typ_instances_all pi,
igs_ad_interface_all ai
WHERE pti.interface_id = ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
UPPER(pti.person_type_code)=UPPER(pi.person_type_code) AND
TRUNC(pti.start_date)=TRUNC(pi.start_date))
WHERE pti.status='2' AND
pti.interface_run_id = l_interface_run_id AND
pti.match_ind IS NULL AND
EXISTS
(SELECT 1
FROM igs_pe_typ_instances_all pi,
igs_ad_interface_all ai
WHERE pti.interface_id=ai.interface_id AND
ai.interface_run_id = l_interface_run_id AND
ai.person_id = pi.person_id AND
UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
TRUNC(pti.start_date) = TRUNC(pi.start_date));
update_person_type(per_type_rec,
dup_per_type_rec.type_instance_id,
dup_per_type_rec.rowid,
dup_per_type_rec.end_date,
dup_per_type_rec.emplmnt_category_code,
l_system_type,
l_default_date);
update_person_type(per_type_rec,
dup_per_type_rec.type_instance_id,
dup_per_type_rec.rowid,
dup_per_type_rec.end_date,
dup_per_type_rec.emplmnt_category_code,
l_system_type,
l_default_date);
-- Validation failed. Update with proper error code.
UPDATE igs_pe_type_int
SET status = '3',
error_code = l_error_code
WHERE interface_person_type_id = per_type_rec.interface_person_type_id;
SELECT cst_insert dmlmode, rowid, a.*
FROM IGS_AD_ACADHIS_INT_ALL a
WHERE a.interface_run_id = p_interface_run_id
AND a.status = '2'
AND ( NOT EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id )
OR ( p_rule = 'R' AND a.match_ind IN ('16', '25') )
)
AND UPDATE_EDUCATION_ID IS NULL
AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
--Exact match
UNION ALL
SELECT cst_update dmlmode, rowid, a.*
FROM IGS_AD_ACADHIS_INT_ALL a
WHERE a.interface_run_id = p_interface_run_id
AND a.status = '2'
AND ( p_rule = 'I' OR (p_rule = 'R' AND a.match_ind = cst_mi_val_21))
AND ( EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
)
OR UPDATE_EDUCATION_ID IS NOT NULL
)
AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
--First record update
-- ( matching instituion code but dates do not match and no partial match
-- ( both start date and end date for all OSS matching records is NULL))
UNION ALL
SELECT cst_first_row dmlmode, rowid, a.*
FROM IGS_AD_ACADHIS_INT_ALL a
WHERE a.interface_run_id = p_interface_run_id
AND a.status = '2'
AND UPDATE_EDUCATION_ID IS NULL
AND NVL(a.start_date,a.end_date) IS NOT NULL
AND EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_id = h1.school_party_id
AND h2.party_number = a.institution_code
AND h1.start_date_attended IS NULL
AND h1.last_date_attended IS NULL
)
AND NOT EXISTS ( SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,
h1.last_date_attended) IS NOT NULL
)
AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
-- Partial match finds single record, hence update if discrepancy rule is 'I'/'R-21' - per bug 3417941
UNION ALL
SELECT cst_partial_update dmlmode, rowid, a.*
FROM IGS_AD_ACADHIS_INT_ALL a
WHERE a.interface_run_id = p_interface_run_id
AND a.status = '2'
AND UPDATE_EDUCATION_ID IS NULL
AND (p_rule = 'I' OR (p_rule = 'R' AND a.match_ind = cst_mi_val_21))
AND 1 = (SELECT count(*) FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_id = h1.school_party_id
AND h2.party_number = a.institution_code
AND NVL(h1.start_date_attended,h1.last_date_attended) IS NOT NULL
AND (TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
OR TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))))
)
AND NOT EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
)
AND UPDATE_EDUCATION_ID IS NULL
AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id;
SELECT ah.*
FROM igs_ad_acad_history_v ah
WHERE
( acad_hist_rec.update_education_id IS NULL
AND person_id = acad_hist_rec.person_id
AND institution_code = acad_hist_rec.institution_code
AND TRUNC(NVL(start_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(acad_hist_rec.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(end_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(acad_hist_rec.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
)
OR (acad_hist_rec.update_education_id IS NOT NULL
AND ah.education_id = acad_hist_rec.update_education_id
);
SELECT ah.*
FROM igs_ad_acad_history_v ah
WHERE person_id = acad_hist_rec.person_id
AND institution_code = acad_hist_rec.institution_code
AND CREATION_DATE =
(SELECT MIN(he.creation_date) FROM hz_education he, hz_parties hz
WHERE he.party_id = acad_hist_rec.person_id
AND hz.party_id = he.school_party_id
AND hz.party_number = acad_hist_rec.institution_code );
SELECT ah.*
FROM igs_ad_acad_history_v ah
WHERE person_id = acad_hist_rec.person_id
AND institution_code = acad_hist_rec.institution_code;
SELECT hp.rowid row_id
FROM
hz_parties p,
igs_pe_hz_parties hp
WHERE hp.party_id = p.party_id
AND hp.inst_org_ind = 'I'
AND p.party_number = person_history_rec.institution_code;
SELECT HP.rowid row_id
FROM HZ_PARTIES P,
IGS_PE_HZ_PARTIES HP,
IGS_OR_ORG_INST_TYPE_ALL OIT
WHERE HP.PARTY_ID = P.PARTY_ID
AND HP.INST_ORG_IND = 'I'
AND p.party_number = person_history_rec.institution_code
AND HP.OI_INSTITUTION_TYPE = OIT.INSTITUTION_TYPE (+)
AND OIT.SYSTEM_INST_TYPE IN ('POST-SECONDARY','SECONDARY');
Igs_Ad_Acad_History_Pkg.Insert_Row (
x_rowid => l_RowId,
x_attribute14 => PERSON_HISTORY_REC.attribute14,
x_attribute15 => PERSON_HISTORY_REC.attribute15,
x_attribute16 => PERSON_HISTORY_REC.attribute16,
x_attribute17 => PERSON_HISTORY_REC.attribute17,
x_attribute18 => PERSON_HISTORY_REC.attribute18,
x_attribute19 => PERSON_HISTORY_REC.attribute19,
x_attribute20 => PERSON_HISTORY_REC.attribute20,
x_attribute13 => PERSON_HISTORY_REC.attribute13,
x_attribute11 => PERSON_HISTORY_REC.attribute11,
x_attribute12 => PERSON_HISTORY_REC.attribute12,
x_education_id => l_education_id,
x_person_id => PERSON_HISTORY_REC.Person_Id,
x_current_inst => PERSON_HISTORY_REC.current_inst,
x_degree_attempted => PERSON_HISTORY_REC.degree_attempted,
x_program_code => PERSON_HISTORY_REC.Program_Code,
x_degree_earned => PERSON_HISTORY_REC.degree_earned,
x_comments => PERSON_HISTORY_REC.Comments,
x_start_date => TRUNC(PERSON_HISTORY_REC.Start_Date),
x_end_date => TRUNC(PERSON_HISTORY_REC.End_Date),
x_planned_completion_date => TRUNC(person_history_rec.planned_completion_date),
x_recalc_total_cp_attempted => NULL,
x_recalc_total_cp_earned => NULL,
x_recalc_total_unit_gp => NULL,
x_recalc_tot_gpa_units_attemp => NULL,--recalc_tot_gpa_units_attemp,
x_recalc_inst_gpa => NULL, --recalc_inst_gpa,
x_recalc_grading_scale_id => NULL,
x_selfrep_total_cp_attempted => PERSON_HISTORY_REC.selfrep_total_cp_attempted,
x_selfrep_total_cp_earned => PERSON_HISTORY_REC.selfrep_total_cp_earned,
x_selfrep_total_unit_gp => NULL, --selfrep_total_unit_gp,
x_selfrep_tot_gpa_uts_attemp => NULL,
x_selfrep_inst_gpa => PERSON_HISTORY_REC.selfrep_inst_gpa,
x_selfrep_grading_scale_id => PERSON_HISTORY_REC.selfrep_grading_scale_id,
x_selfrep_weighted_gpa => PERSON_HISTORY_REC.selfrep_weighted_gpa,
x_selfrep_rank_in_class => PERSON_HISTORY_REC.selfrep_rank_in_class,
x_selfrep_weighed_rank => PERSON_HISTORY_REC.selfrep_weighted_rank,
x_type_of_school => PERSON_HISTORY_REC.type_of_school,
x_institution_code => PERSON_HISTORY_REC.institution_code,
x_attribute_category => PERSON_HISTORY_REC.attribute_category,
x_attribute1 => PERSON_HISTORY_REC.attribute1,
x_attribute2 => PERSON_HISTORY_REC.attribute2,
x_attribute3 => PERSON_HISTORY_REC.attribute3,
x_attribute4 => PERSON_HISTORY_REC.attribute4,
x_attribute5 => PERSON_HISTORY_REC.attribute5,
x_attribute6 => PERSON_HISTORY_REC.attribute6,
x_attribute7 => PERSON_HISTORY_REC.attribute7,
x_attribute8 => PERSON_HISTORY_REC.attribute8,
x_attribute9 => PERSON_HISTORY_REC.attribute9,
x_attribute10 => PERSON_HISTORY_REC.attribute10,
-- Added Class Size As part of the ID Prospective Applicant part 2 of 1
x_selfrep_class_size => PERSON_HISTORY_REC.class_size,
-- Added Transcript Required as a part of DLD_ADSR_IMPORT_TEST_RESULTS
x_transcript_required => NVL(PERSON_HISTORY_REC.transcript_required,'Y'),
x_object_version_number => l_object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R');
UPDATE
IGS_AD_ACADHIS_INT_ALL
SET
error_code = l_error_Code,
error_text = l_error_text,
status = cst_s_val_3,
match_ind = DECODE (
person_history_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
WHERE
INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
UPDATE IGS_AD_ACADHIS_INT_ALL
SET error_code = 'E322',
error_text = l_msg_data,
status = '3',
match_ind = DECODE (
person_history_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
-- updated education_id after successful insert
UPDATE IGS_AD_ACADHIS_INT_ALL
SET status = cst_s_val_1,
error_code = cst_ec_val_NULL,
education_id = l_education_id
WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
UPDATE IGS_AD_ACADHIS_INT_ALL
SET error_code = l_error_code,
error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405),
status = l_status,
match_ind = DECODE (
person_history_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
SAVEPOINT before_update_hist;
Igs_Ad_Acad_History_Pkg.update_row (
x_rowid => c_null_hdlg_acad_hist_cur_rec.row_id,
x_attribute14 => c_null_hdlg_acad_hist_cur_rec.attribute14,
x_attribute15 => c_null_hdlg_acad_hist_cur_rec.attribute15,
x_attribute16 => c_null_hdlg_acad_hist_cur_rec.attribute16,
x_attribute17 => c_null_hdlg_acad_hist_cur_rec.attribute17,
x_attribute18 => c_null_hdlg_acad_hist_cur_rec.attribute18,
x_attribute19 => c_null_hdlg_acad_hist_cur_rec.attribute19,
x_attribute20 => c_null_hdlg_acad_hist_cur_rec.attribute20,
x_attribute13 => c_null_hdlg_acad_hist_cur_rec.attribute13,
x_attribute11 => c_null_hdlg_acad_hist_cur_rec.attribute11,
x_attribute12 => c_null_hdlg_acad_hist_cur_rec.attribute12,
x_education_id => c_null_hdlg_acad_hist_cur_rec.Education_Id,
x_person_id => NVL(PERSON_HISTORY_REC.Person_Id,c_null_hdlg_acad_hist_cur_rec.person_id),
x_current_inst => NVL(PERSON_HISTORY_REC.current_inst,c_null_hdlg_acad_hist_cur_rec.current_inst),
x_degree_attempted => NVL(PERSON_HISTORY_REC.degree_attempted,c_null_hdlg_acad_hist_cur_rec.degree_attempted),
x_program_code => NVL(PERSON_HISTORY_REC.Program_Code,c_null_hdlg_acad_hist_cur_rec.Program_Code),
x_degree_earned => NVL(PERSON_HISTORY_REC.degree_earned,c_null_hdlg_acad_hist_cur_rec.degree_earned),
x_comments => NVL(PERSON_HISTORY_REC.Comments,c_null_hdlg_acad_hist_cur_rec.Comments),
x_start_date => TRUNC(NVL(PERSON_HISTORY_REC.Start_Date,c_null_hdlg_acad_hist_cur_rec.Start_Date)),
x_end_date => TRUNC(NVL(PERSON_HISTORY_REC.End_Date,c_null_hdlg_acad_hist_cur_rec.End_Date)),
x_planned_completion_date => NVL(person_history_rec.planned_completion_date,c_null_hdlg_acad_hist_cur_rec.planned_completion_date),
x_recalc_total_cp_attempted => c_null_hdlg_acad_hist_cur_rec.recalc_total_cp_attempted,
x_recalc_total_cp_earned => c_null_hdlg_acad_hist_cur_rec.recalc_total_cp_earned,
x_recalc_total_unit_gp => c_null_hdlg_acad_hist_cur_rec.recalc_total_unit_gp,
x_recalc_tot_gpa_units_attemp => c_null_hdlg_acad_hist_cur_rec.recalc_total_gpa_units_attemp,
x_recalc_inst_gpa => c_null_hdlg_acad_hist_cur_rec.recalc_inst_gpa,
x_recalc_grading_scale_id => c_null_hdlg_acad_hist_cur_rec.recalc_grading_scale_id,
x_selfrep_total_cp_attempted => NVL(PERSON_HISTORY_REC.selfrep_total_cp_attempted,c_null_hdlg_acad_hist_cur_rec.selfrep_total_cp_attempted),
x_selfrep_total_cp_earned => NVL(PERSON_HISTORY_REC.selfrep_total_cp_earned,c_null_hdlg_acad_hist_cur_rec.selfrep_total_cp_earned),
x_selfrep_total_unit_gp => c_null_hdlg_acad_hist_cur_rec.selfrep_total_unit_gp,
x_selfrep_tot_gpa_uts_attemp => NVL(person_history_rec.selfrep_total_gp_units_attemp,c_null_hdlg_acad_hist_cur_rec.selfrep_total_gpa_units_attemp),
x_selfrep_inst_gpa => NVL(PERSON_HISTORY_REC.selfrep_inst_gpa,c_null_hdlg_acad_hist_cur_rec.selfrep_inst_gpa),
x_selfrep_grading_scale_id => NVL(PERSON_HISTORY_REC.selfrep_grading_scale_id,c_null_hdlg_acad_hist_cur_rec.selfrep_grading_scale_id),
x_selfrep_weighted_gpa => NVL(PERSON_HISTORY_REC.selfrep_weighted_gpa,c_null_hdlg_acad_hist_cur_rec.selfrep_weighted_gpa),
x_selfrep_rank_in_class => NVL(PERSON_HISTORY_REC.selfrep_rank_in_class,c_null_hdlg_acad_hist_cur_rec.selfrep_rank_in_class),
x_selfrep_weighed_rank => NVL(PERSON_HISTORY_REC.selfrep_weighted_rank,c_null_hdlg_acad_hist_cur_rec.selfrep_weighed_rank),
x_type_of_school => NVL(PERSON_HISTORY_REC.type_of_school,c_null_hdlg_acad_hist_cur_rec.type_of_school),
x_institution_code => NVL(PERSON_HISTORY_REC.institution_code,c_null_hdlg_acad_hist_cur_rec.institution_code),
x_attribute_category => c_null_hdlg_acad_hist_cur_rec.attribute_category,
x_attribute1 => c_null_hdlg_acad_hist_cur_rec.attribute1,
x_attribute2 => c_null_hdlg_acad_hist_cur_rec.attribute2,
x_attribute3 => c_null_hdlg_acad_hist_cur_rec.attribute3,
x_attribute4 => c_null_hdlg_acad_hist_cur_rec.attribute4,
x_attribute5 => c_null_hdlg_acad_hist_cur_rec.attribute5,
x_attribute6 => c_null_hdlg_acad_hist_cur_rec.attribute6,
x_attribute7 => c_null_hdlg_acad_hist_cur_rec.attribute7,
x_attribute8 => c_null_hdlg_acad_hist_cur_rec.attribute8,
x_attribute9 => c_null_hdlg_acad_hist_cur_rec.attribute9,
x_attribute10 => c_null_hdlg_acad_hist_cur_rec.attribute10,
-- Added Class Size As part of the ID Prospective Applicant part 2 of 1
x_selfrep_class_size => NVL(PERSON_HISTORY_REC.class_size,c_null_hdlg_acad_hist_cur_rec.SELFREP_CLASS_SIZE),
-- Added Transcript Required as a part of the DLD_ADRS_IMPORT_TEST_RESULTS DLD
x_transcript_required => NVL(PERSON_HISTORY_REC.transcript_required,c_null_hdlg_acad_hist_cur_rec.transcript_required),
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => l_object_version_number,
x_mode => 'R');
ROLLBACK TO before_update_hist;
UPDATE
IGS_AD_ACADHIS_INT_ALL
SET
error_code = l_error_Code,
error_text =l_error_text,
status = '3',
match_ind = DECODE (
person_history_rec.match_ind,
NULL, cst_mi_val_12,
match_ind)
WHERE
INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
UPDATE IGS_AD_ACADHIS_INT_ALL
SET error_code = 'E014',
status = '3',
error_text = l_msg_data,
match_ind = DECODE (
person_history_rec.match_ind,
NULL, cst_mi_val_12,
match_ind)
WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
-- updated education_id after successful update
UPDATE IGS_AD_ACADHIS_INT_ALL
SET match_ind = decode ( person_history_rec.dmlmode,
cst_partial_update, cst_mi_val_12,
decode ( person_history_rec.match_ind ,
NULL, cst_mi_val_18,
person_history_rec.match_ind)),
status = cst_s_val_1,
education_id = c_null_hdlg_acad_hist_cur_rec.Education_Id
WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
UPDATE IGS_AD_ACADHIS_INT_ALL
SET status = cst_s_val_3,
error_code = l_error_code,
error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405),
match_ind = DECODE (
person_history_rec.match_ind,
NULL, cst_mi_val_12,
match_ind),
education_id = c_null_hdlg_acad_hist_cur_rec.Education_Id
WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '3', error_code = 'E711',
error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E711', 8405)
WHERE update_education_id IS NOT NULL
AND NOT EXISTS ( SELECT 1 FROM HZ_EDUCATION
WHERE party_id = acad.person_id
AND education_id = NVL(acad.update_education_id ,education_id)
) ;
UPDATE IGS_AD_ACADHIS_INT_ALL
SET
status = '3'
, error_code = 'E700'
,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') <> '15';
UPDATE IGS_AD_ACADHIS_INT_ALL
SET
status = '1', error_code = NULL
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
UPDATE IGS_AD_ACADHIS_INT_ALL a
SET
status = '3'
, match_ind = '13'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND UPDATE_EDUCATION_ID IS NULL
AND 1 < ( SELECT COUNT (*)
FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
);
UPDATE IGS_AD_ACADHIS_INT_ALL a
SET
status = '3'
, match_ind = '13'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND UPDATE_EDUCATION_ID IS NULL
AND NVL(match_ind, '15') IN ('15', '21')
AND 1 < ( SELECT COUNT (*)
FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
);
UPDATE IGS_AD_ACADHIS_INT_ALL a
SET
status = '1'
, match_ind = '19'
, education_id = update_education_id
WHERE update_education_id IS NOT NULL;
UPDATE IGS_AD_ACADHIS_INT_ALL a
SET
status = '3'
, match_ind = '19'
,error_code = 'E708'
,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E708', 8405)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND 1 < (SELECT count(*) FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
)
AND EXISTS (SELECT 1 FROM igs_ad_txcpt_int
WHERE interface_acadhis_id = a.interface_acadhis_id
AND status = '2');
UPDATE IGS_AD_ACADHIS_INT_ALL a
SET
status = '1'
, match_ind = '19'
, education_id =
( SELECT h1.education_id FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND rownum <= 1 )
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
AND TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) =
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
);
UPDATE IGS_AD_ACADHIS_INT_ALL a
SET
status = '1'
, match_ind = '19'
, education_id =
( SELECT h1.education_id FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,h1.last_date_attended) IS NOT NULL
AND (TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
OR TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))))
)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND 1 = (SELECT count(*) FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = a.person_id
AND h2.party_number = a.institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,h1.last_date_attended) IS NOT NULL
AND (TRUNC(NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
TRUNC(NVL(a.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY')))
OR TRUNC(NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
TRUNC(NVL(a.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))))
);
6. Create / Update the OSS record after validating successfully the interface record
Create
If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
RULE = R and MATCH IND = 16, 25
Update
If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
RULE = R and MATCH IND = 21
UPdate
If all the partilly matched OSS records have both start date and end date NULL THEN
update First OSS record which partilaly matched.
Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
This is done to have one code section for record validation, exception handling and interface table update.
This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
**********************************************************************************/
SELECT COUNT(interface_acadhis_id)
INTO l_count_interface_acadhis_id
FROM IGS_AD_ACADHIS_INT_ALL
WHERE interface_run_id = p_interface_run_id
AND status =2 ;
SELECT
MIN(interface_acadhis_id) , MAX(interface_acadhis_id)
INTO l_min_interface_acadhis_id , l_max_interface_acadhis_id
FROM IGS_AD_ACADHIS_INT_ALL
WHERE interface_run_id = p_interface_run_id
AND status =2
AND rownum < =100;
IF acad_hist_rec.dmlmode = cst_insert THEN
crc_pe_acad_hist(acad_hist_rec);
ELSIF acad_hist_rec.dmlmode = cst_update THEN
dup_cur_rec.education_id := NULL;
ELSIF acad_hist_rec.dmlmode = cst_partial_update THEN
OPEN c_dup_cur_partial(acad_hist_rec);
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '1'
, match_ind = '23'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') = '15'
AND EXISTS (SELECT 1 FROM igs_ad_acad_history_v WHERE
person_id = acad.person_id
AND NVL(current_inst, 'X') = NVL(NVL(acad.current_inst, current_inst), 'X')
AND STATUS = acad.status
AND NVL(degree_attempted, 'X') = NVL(NVL(acad.degree_attempted , degree_attempted ), 'X')
AND NVL(program_code, 'X') = NVL(NVL(acad.program_code, program_code), 'X')
AND NVL(degree_earned, 'X') = NVL(NVL(acad.degree_earned, degree_earned ), 'X')
AND NVL(comments, 'X') = NVL(NVL(acad.comments,comments), 'X')
AND NVL(to_char(start_date,'DDMMYYYY'), '01011900') = NVL(NVL(to_char(acad.start_date,'DDMMYYYY'), to_char(start_date,'DDMMYYYY')),'01011900')
AND NVL(to_char(end_date,'DDMMYYYY'), '01011900') = NVL(NVL(to_char(acad.end_date,'DDMMYYYY'), to_char(end_date,'DDMMYYYY') ), '01011900')
AND NVL(to_char(planned_completion_date,'DDMMYYYY'), '01011900') =
NVL(NVL(to_char(acad.planned_completion_date, 'DDMMYYYY'), to_char(planned_completion_date,'DDMMYYYY') ), '01011900')
AND NVL(selfrep_total_cp_attempted, -1) = NVL(NVL(acad.selfrep_total_cp_attempted, selfrep_total_cp_attempted), -1)
AND NVL(selfrep_total_cp_earned, -1) = NVL(NVL(acad.selfrep_total_cp_earned, selfrep_total_cp_earned), -1)
AND NVL(SELFREP_TOTAL_GPA_UNITS_ATTEMP, -1) = NVL(NVL(acad.SELFREP_TOTAL_GP_UNITS_ATTEMP, SELFREP_TOTAL_GP_UNITS_ATTEMP), -1)
AND NVL(selfrep_inst_gpa, 'X') = NVL(NVL(acad.selfrep_inst_gpa, selfrep_inst_gpa), 'X')
AND NVL(selfrep_grading_scale_id, -1) = NVL(NVL(acad.selfrep_grading_scale_id,selfrep_grading_scale_id), -1)
AND NVL(selfrep_weighted_gpa, 'X') = NVL(NVL(acad.selfrep_weighted_gpa, selfrep_weighted_gpa), 'X')
AND NVL(selfrep_rank_in_class, -1) = NVL(NVL(acad.selfrep_rank_in_class, selfrep_rank_in_class), -1)
AND NVL(selfrep_weighed_rank, 'X') = NVL(NVL(acad.selfrep_weighted_rank, selfrep_weighted_rank), 'X')
AND NVL(type_of_school, 'X') = NVL(NVL(acad.type_of_school, type_of_school), 'X')
AND NVL(ATTRIBUTE_CATEGORY, 'X') = NVL( NVL(acad.ATTRIBUTE_CATEGORY,ATTRIBUTE_CATEGORY), 'X')
AND NVL(ATTRIBUTE1, 'X') = NVL(NVL(acad.ATTRIBUTE1, ATTRIBUTE1), 'X')
AND NVL(ATTRIBUTE2, 'X') = NVL(NVL(acad.ATTRIBUTE2, ATTRIBUTE2),'X')
AND NVL(ATTRIBUTE3, 'X') = NVL(NVL(acad.ATTRIBUTE3,ATTRIBUTE3), 'X')
AND NVL(ATTRIBUTE4, 'X') = NVL(NVL(acad.ATTRIBUTE4,ATTRIBUTE4), 'X')
AND NVL(ATTRIBUTE5, 'X') = NVL(NVL(acad.ATTRIBUTE5,ATTRIBUTE5), 'X')
AND NVL(ATTRIBUTE6, 'X') = NVL(NVL(acad.ATTRIBUTE6,ATTRIBUTE6), 'X')
AND NVL(ATTRIBUTE7, 'X') = NVL(NVL(acad.ATTRIBUTE7, ATTRIBUTE7),'X')
AND NVL(ATTRIBUTE8, 'X') = NVL(NVL(acad.ATTRIBUTE8, ATTRIBUTE8),'X')
AND NVL(ATTRIBUTE9, 'X') = NVL(NVL(acad.ATTRIBUTE9, ATTRIBUTE9),'X')
AND NVL(ATTRIBUTE10, 'X') = NVL(NVL(acad.ATTRIBUTE10, ATTRIBUTE10),'X')
AND NVL(ATTRIBUTE11, 'X') = NVL(NVL(acad.ATTRIBUTE11, ATTRIBUTE11),'X')
AND NVL(ATTRIBUTE12, 'X') = NVL(NVL(acad.ATTRIBUTE12,ATTRIBUTE12), 'X')
AND NVL(ATTRIBUTE13, 'X') = NVL(NVL(acad.ATTRIBUTE13, ATTRIBUTE13),'X')
AND NVL(ATTRIBUTE14, 'X') = NVL(NVL(acad.ATTRIBUTE14, ATTRIBUTE14),'X')
AND NVL(ATTRIBUTE15, 'X') = NVL(NVL(acad.ATTRIBUTE15, ATTRIBUTE15),'X')
AND NVL(ATTRIBUTE16, 'X') = NVL(NVL(acad.ATTRIBUTE16,ATTRIBUTE16), 'X')
AND NVL(ATTRIBUTE17, 'X') = NVL(NVL(acad.ATTRIBUTE17,ATTRIBUTE17), 'X')
AND NVL(ATTRIBUTE18, 'X') = NVL(NVL(acad.ATTRIBUTE18,ATTRIBUTE18), 'X')
AND NVL(ATTRIBUTE19, 'X') = NVL(NVL(acad.ATTRIBUTE19,ATTRIBUTE19), 'X')
AND NVL(ATTRIBUTE20, 'X') = NVL(NVL(acad.ATTRIBUTE20,ATTRIBUTE20), 'X')
-- Added Class Size As part of the ID Prospective Applicant part 2 of 1
AND NVL(selfrep_class_size,-1) = NVL(NVL(acad.class_size, class_size),-1)
);
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '3'
, match_ind = '20'
, dup_acad_history_id = ( SELECT hz_acad_hist_id FROM igs_Ad_Hz_Acad_Hist
WHERE education_id = acad.update_education_id
)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') = '15'
AND update_Education_id IS NOT NULL;
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '3'
, match_ind = '20'
, dup_acad_history_id = ( SELECT hz_acad_hist_id FROM igs_Ad_Hz_Acad_Hist
WHERE education_id =
(SELECT education_id FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = acad.person_id
AND h2.party_number = acad.institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY')) =
NVL(acad.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))
AND NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY')) =
NVL(acad.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))
)
)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') = '15'
AND ( EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = acad.person_id
AND h2.party_number = acad.institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY')) =
NVL(acad.start_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))
AND NVL(h1.last_date_attended,
TO_DATE('01-01-0001','DD-MM-YYYY')) =
NVL(acad.end_date,
TO_DATE('01-01-0001','DD-MM-YYYY'))
)
);
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '3'
, match_ind = '14'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND 1< ( SELECT COUNT(*) FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = acad.person_id
AND h2.party_number = acad.institution_code
AND h2.party_id = h1.school_party_id
);
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '3'
, match_ind = '20'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS ( SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = acad.person_id
AND h2.party_number = acad.institution_code
AND h2.party_id = h1.school_party_id
);
UPDATE IGS_AD_ACADHIS_INT_ALL acad
SET
status = '3'
, error_code = 'E700'
,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND match_ind IS NOT NULL;
SELECT cst_insert dmlmode, cred.rowid, ad.person_id, cred.*
FROM IGS_AD_INTERFACE_ALL ad , IGS_PE_CRED_INT cred
WHERE cred.interface_run_id = p_interface_run_id
AND ad.status IN ('1', '4')
AND cred.interface_id = ad.interface_id
AND cred.status = '2'
AND ( NOT EXISTS (SELECT 1 FROM IGS_PE_CREDENTIALS
WHERE person_id = ad.person_id
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) )
OR ( p_rule = 'R' AND cred.match_ind IN ('16', '25') )
)
UNION ALL
SELECT cst_update dmlmode, cred.rowid, ad.person_id, cred.*
FROM IGS_AD_INTERFACE_ALL ad , IGS_PE_CRED_INT cred
WHERE cred.interface_run_id = p_interface_run_id
AND ad.status IN ('1', '4')
AND cred.interface_id = ad.interface_id
AND cred.status = '2'
AND ( p_rule = 'I' OR (p_rule = 'R' AND cred.match_ind = cst_mi_val_21))
AND EXISTS (SELECT 1 FROM IGS_PE_CREDENTIALS
WHERE person_id = ad.person_id
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
);
SELECT
pcreds.rowid, pcreds.*
FROM
igs_pe_credentials pcreds
WHERE
person_id = cp_pe_cr_rec.person_id
AND credential_type_id = cp_pe_cr_rec.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) = TRUNC(NVL(cp_pe_cr_rec.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')));
SELECT
'X'
FROM
igs_ad_cred_types
WHERE
credential_type_id = cp_pe_cr_rec.credential_type_id
AND closed_ind = 'N';
SELECT
'X'
FROM
igs_lookup_values
WHERE lookup_type = 'PE_CRE_RATING' AND
lookup_code = cp_pe_cr_rec.rating_code AND
enabled_flag = 'Y';
SELECT
'X'
FROM
hz_parties
WHERE
party_id = cp_pe_cr_rec.reviewer_id;
PROCEDURE update_pe_cred(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE, cp_dup_cur_rec c_dup_cur%ROWTYPE) AS
/***********************************************
|| Created By :Praveen Bondugula
|| Date Created By :24-apr-2003
|| Purpose : update person credentials in the existing record
|| Known limitations, enhancements or remarks
|| Change History
|| Who When What
||
**********************************************/
l_status VARCHAR2(1);
l_label := 'igs.plsql.igs_ad_imp_028.update_pe_cred.begin';
l_debug_str := 'igs_ad_imp_028.update_pe_cred';
igs_pe_credentials_pkg.update_row(
x_rowid => dup_cur_rec.rowid,
x_credential_id => dup_cur_rec.credential_id,
x_person_id => cp_dup_cur_rec.person_id,
x_credential_type_id => cp_dup_cur_rec.credential_type_id,
x_date_received => TRUNC (NVL( cp_pe_cr_rec.date_received, cp_dup_cur_rec.date_received)),
x_reviewer_id => NVL(cp_pe_cr_rec.reviewer_id, cp_dup_cur_rec.reviewer_id),
x_reviewer_notes => NVL(cp_pe_cr_rec.reviewer_notes, cp_dup_cur_rec.reviewer_notes),
x_recommender_name => NVL( cp_pe_cr_rec.recommender_name, cp_dup_cur_rec.recommender_name),
x_recommender_title => NVL( cp_pe_cr_rec.recommender_title, cp_dup_cur_rec.recommender_title),
x_recommender_organization=> NVL( cp_pe_cr_rec.recommender_organization, cp_dup_cur_rec.recommender_organization),
x_mode => 'R',
x_rating_code => NVL( cp_pe_cr_rec.rating_code,cp_dup_cur_rec.rating_code)
);
UPDATE igs_pe_cred_int
SET status = cst_s_val_1, error_code = cst_ec_val_NULL, match_ind = cst_mi_val_18
WHERE interface_cred_id = cp_pe_cr_rec.interface_cred_id;
UPDATE igs_pe_cred_int
SET
status = cst_s_val_3
, match_ind = DECODE (
cp_pe_cr_rec.match_ind,
NULL, cst_mi_val_12,
match_ind)
, error_code = l_error_code
WHERE rowid = cp_pe_cr_rec.rowid ;
l_label := 'igs.plsql.igs_ad_imp_028.update_pe_cred.exception '|| l_msg_data;
UPDATE igs_pe_cred_int
SET
status = cst_s_val_3
, match_ind = DECODE (
cp_pe_cr_rec.match_ind,
NULL, cst_mi_val_12,
match_ind)
, error_code = l_error_code
,error_text = l_error_text
WHERE rowid = cp_pe_cr_rec.rowid ;
END update_pe_cred;
PROCEDURE insert_pe_cred(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE) AS
/***********************************************
|| Created By :Praveen Bondugula
|| Date Created By :24-apr-2003
|| Purpose : Inserts the credentials into the OSS table.
|| Known limitations, enhancements or remarks
|| Change History
|| Who When What
||
**********************************************/
l_status VARCHAR2(1);
l_label := 'igs.plsql.igs_ad_imp_028.insert_pe_cred.begin';
l_debug_str := 'igs_ad_imp_028.insert_pe_cred';
igs_pe_credentials_pkg.insert_row(
x_rowid => l_rowid,
x_credential_id => l_credential_id,
x_person_id => cp_pe_cr_rec.person_id,
x_credential_type_id => cp_pe_cr_rec.credential_type_id,
x_date_received => TRUNC (cp_pe_cr_rec.date_received),
x_reviewer_id => cp_pe_cr_rec.reviewer_id,
x_reviewer_notes => cp_pe_cr_rec.reviewer_notes,
x_recommender_name => cp_pe_cr_rec.recommender_name,
x_recommender_title => cp_pe_cr_rec.recommender_title,
x_recommender_organization=> cp_pe_cr_rec.recommender_organization,
x_mode => 'R',
x_rating_code => cp_pe_cr_rec.rating_code);
UPDATE igs_pe_cred_int
SET status = cst_s_val_1,
error_code = cst_ec_val_NULL,
match_ind = cst_mi_val_11
WHERE interface_cred_id = cp_pe_cr_rec.interface_cred_id;
UPDATE igs_pe_cred_int
SET
status = cst_s_val_3
, match_ind = DECODE (
cp_pe_cr_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
, error_code = l_error_code
WHERE rowid = cp_pe_cr_rec.rowid ;
l_label := 'igs.plsql.igs_ad_imp_028.insert_pe_cred.exception'||l_msg_data;
l_label := 'igs.plsql.igs_ad_imp_028.update_pe_cred.exception '||'E322';
UPDATE igs_pe_cred_int
SET
status = cst_s_val_3
, match_ind = DECODE (
cp_pe_cr_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
, error_code = l_error_code
,error_text = l_error_text
WHERE rowid = cp_pe_cr_rec.rowid ;
END insert_pe_cred;
UPDATE IGS_PE_CRED_INT
SET
status = '3'
, error_code = 'E700'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') <> '15';
UPDATE igs_pe_cred_int
SET
status = '1', error_code = NULL
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
UPDATE igs_pe_cred_int cred
SET
status = '3'
, match_ind = '13'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND 1 < ( SELECT COUNT(*)
FROM igs_pe_credentials cred_oss
WHERE person_id = (SELECT person_id FROM igs_ad_interface_all
WHERE interface_id = cred.interface_id)
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
);
UPDATE igs_pe_cred_int cred
SET
status = '3'
, match_ind = '13'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL(match_ind, '15') IN ('15', '21')
AND 1 < ( SELECT COUNT(*)
FROM igs_pe_credentials cred_oss
WHERE person_id = (SELECT person_id FROM igs_ad_interface_all
WHERE interface_id = cred.interface_id)
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
);
UPDATE IGS_PE_CRED_INT cred
SET
status = '1'
, match_ind = '19'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS ( SELECT 1 FROM IGS_PE_CREDENTIALS
WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
);
Create / Update the OSS record after validating successfully the interface record
Create
If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
RULE = R and MATCH IND = 16, 25
Update
If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
RULE = R and MATCH IND = 21
Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
This is done to have one code section for record validation, exception handling and interface table update.
This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
**********************************************************************************/
FOR pe_cr_cur_rec IN c_pe_cr_cur
LOOP
IF pe_cr_cur_rec.dmlmode = cst_insert THEN
insert_pe_cred(pe_cr_cur_rec);
ELSIF pe_cr_cur_rec.dmlmode = cst_update THEN
OPEN c_dup_cur(pe_cr_cur_rec);
update_pe_cred(pe_cr_cur_rec, dup_cur_rec);
ALL updateable column values, if column nullification is not allowed then the 2 DECODE should be replaced by a single NVL*/
IF p_rule = 'R' THEN
UPDATE IGS_PE_CRED_INT cred
SET
status = '1'
, match_ind = '23'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') = '15'
AND EXISTS ( SELECT 'x'
FROM
igs_pe_credentials
WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
WHERE interface_id = cred.interface_id
AND interface_run_id = p_interface_run_id)
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))))
AND NVL(RATING_CODE, '-1') = NVL(cred.rating_code, NVL(RATING_CODE, '-1'))
AND NVL(REVIEWER_ID, -1) = NVL(cred.reviewer_id, NVL(REVIEWER_ID, -1))
AND NVL(REVIEWER_NOTES, '-1') = NVL(cred.reviewer_notes, NVL(REVIEWER_NOTES, '-1'))
AND NVL(RECOMMENDER_NAME, '-1') = NVL(cred.recommender_name, NVL(RECOMMENDER_NAME, '-1'))
AND NVL(RECOMMENDER_TITLE , '-1') = NVL(cred.recommender_title, NVL(RECOMMENDER_TITLE , '-1'))
AND NVL(recommender_organization, '-1') = NVL(cred.recommender_organization,NVL(recommender_organization, '-1'))
);
UPDATE IGS_PE_CRED_INT cred
SET
status = '3'
, match_ind = '20'
, dup_credential_id= (SELECT credential_id FROM igs_pe_credentials
WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))))
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') = '15'
AND EXISTS (SELECT credential_id FROM igs_pe_credentials
WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
AND credential_type_id = cred.credential_type_id
AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))));
UPDATE IGS_PE_CRED_INT cred
SET
status = '3'
, error_code = 'E700'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND match_ind IS NOT NULL;