[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
PROCEDURE update_appl_inst(p_appl_inst_id NUMBER, p_status VARCHAR2,
p_error_text VARCHAR2, p_sequence_number NUMBER,p_error_code VARCHAR2,
p_admission_appl_number NUMBER ,
p_match_ind VARCHAR2 ) AS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE igs_ad_ps_appl_inst_int
SET error_text = p_error_text,
status = p_status,
sequence_number = p_sequence_number,
error_code = p_error_code,
admission_appl_number = p_admission_appl_number,
match_ind = p_match_ind
WHERE interface_ps_appl_inst_id = p_appl_inst_id;
END update_appl_inst;
SELECT cst_insert dmlmode, api.rowid, api.*
FROM igs_ad_apl_int api
WHERE api.interface_run_id = p_interface_run_id
AND api.status = '2'
AND ( p_rule = 'R' AND api.match_ind IN ('16', '25')
OR update_adm_appl_number IS NULL )
UNION ALL
SELECT cst_update dmlmode, api.rowid, api.*
FROM igs_ad_apl_int api
WHERE api.interface_run_id = p_interface_run_id
AND api.status = '2'
AND ( p_rule = 'I' OR (p_rule = 'R' AND api.match_ind = '21'))
AND update_adm_appl_number IS NOT NULL ;
SELECT cst_update dmlmode, api.rowid, api.*
FROM igs_ad_apl_int api
WHERE api.interface_run_id = p_interface_run_id
AND interface_appl_id = p_interface_appl_id
AND status = cst_s_val_1;
SELECT
cst_insert dmlmode, aplinst.rowid, aplinst.*
FROM
igs_ad_ps_appl_inst_int aplinst
WHERE aplinst.status = '2'
AND aplinst.interface_run_id = p_interface_run_id
AND ( p_rule = 'R' AND aplinst.match_ind IN ('16', '25')
OR update_adm_seq_number IS NULL )
AND aplinst.interface_appl_id = NVL(cp_interface_appl_id , aplinst.interface_appl_id)
UNION ALL
SELECT
cst_update dmlmode, aplinst.rowid, aplinst.*
FROM
igs_ad_ps_appl_inst_int aplinst
WHERE aplinst.status = '2'
AND aplinst.interface_run_id = p_interface_run_id
AND aplinst.interface_appl_id = NVL(cp_interface_appl_id , aplinst.interface_appl_id)
AND ( p_rule = 'I' OR (p_rule = 'R' AND aplinst.match_ind = '21'))
AND update_adm_seq_number IS NOT NULL ;
SELECT
appl_oss.rowid, appl_oss.*
FROM
IGS_AD_APPL appl_oss
WHERE person_id = appl_Rec.person_id
AND admission_Appl_number = appl_rec.update_adm_appl_number;
SELECT
acai.rowid,acai.*
FROM
igs_ad_ps_appl_inst acai
WHERE
person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = applinst_rec.nominated_course_cd
AND sequence_number = applinst_rec.update_adm_seq_number;
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
SELECT NVL(MAX(admission_appl_number),0) + 1
FROM IGS_AD_APPL
WHERE person_id = p_appl_rec.person_id;
SAVEPOINT before_insert_appl;
IGS_AD_APPL_PKG.insert_row (
X_Mode => 'R',
X_RowId => l_rowid,
X_Person_Id => p_appl_rec.Person_Id,
X_Admission_Appl_Number => l_adm_appl_number,
X_Appl_Dt => p_appl_rec.Appl_Dt,
X_Acad_Cal_Type => p_appl_rec.Acad_Cal_Type,
X_Acad_Ci_Sequence_Number => p_appl_rec.Acad_Ci_Sequence_Number,
X_Adm_Cal_Type => p_appl_rec.Adm_Cal_Type,
X_Adm_Ci_Sequence_Number => p_appl_rec.Adm_Ci_Sequence_Number,
X_Admission_Cat => l_admission_cat,
X_S_Admission_Process_Type => l_s_admission_process_type,
X_Adm_Appl_Status => l_adm_appl_status,
X_Adm_Fee_Status => l_adm_fee_status,
X_Tac_Appl_Ind => p_appl_rec.Tac_Appl_Ind,
X_Org_Id => l_org_id,
X_Spcl_Grp_1 => p_appl_rec.spcl_grp_1,
X_Spcl_Grp_2 => p_appl_rec.spcl_grp_2,
X_Common_App => p_appl_rec.common_app,
X_application_type => l_admission_application_type,
X_choice_number => p_appl_rec.choice_number,
X_routeb_pref => p_appl_rec.routeb_pref,
X_alt_appl_id => p_appl_rec.alt_appl_id,
X_appl_fee_amt => NVL(p_appl_rec.appl_fee_amt,0)
);
ROLLBACK TO before_insert_appl;
UPDATE IGS_AD_APL_INT
SET status = cst_s_val_3,
error_code = l_error_code,
error_text = l_error_text,
match_ind = DECODE (
p_appl_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
WHERE interface_appl_id = p_appl_rec.interface_appl_id;
PROCEDURE update_application_details ( p_appl_rec appl_cur%ROWTYPE, dup_cur_rec c_dup_cur%ROWTYPE
) IS
/*-----------------------------------------------------------------------------------------
Created By: pbondugu
Date Created : 24-Nov-2003
Purpose: Import PRocess enhancements
Known limitations,enhancements,remarks:
Change History
Who When What
-----------------------------------------------------------------------------------------*/
l_rowid VARCHAR2(25);
SAVEPOINT before_update_appl;
IGS_AD_APPL_PKG.update_row (
X_Mode => 'R',
X_RowId => dup_cur_rec.rowid,
X_Person_Id => p_appl_rec.Person_Id,
X_admission_appl_Number => p_appl_rec.update_adm_appl_number,
X_appl_dt => NVL( p_appl_rec.Appl_Dt,dup_cur_rec.Appl_Dt ),
X_Acad_Cal_Type => NVL(p_appl_rec.Acad_Cal_Type, dup_cur_rec.Acad_Cal_Type),
X_Acad_Ci_Sequence_Number => NVL(p_appl_rec.Acad_Ci_Sequence_Number, dup_cur_rec.Acad_Ci_Sequence_Number),
X_Adm_Cal_Type => NVL(p_appl_rec.Adm_Cal_Type, dup_cur_rec.Adm_Cal_Type),
X_Adm_Ci_Sequence_Number => NVL(p_appl_rec.Adm_Ci_Sequence_Number, dup_cur_rec.Adm_Ci_Sequence_Number),
X_Admission_Cat => dup_cur_rec.Admission_Cat ,
X_S_Admission_Process_Type => dup_cur_rec.s_admission_process_type,
X_Adm_Appl_Status => dup_cur_rec.Adm_Appl_Status,
X_Adm_Fee_Status => dup_cur_rec.adm_fee_status,
X_Tac_Appl_Ind => p_appl_rec.Tac_Appl_Ind,
X_Spcl_Grp_1 => NVL(p_appl_rec.spcl_grp_1, dup_cur_rec.spcl_grp_1),
X_Spcl_Grp_2 => NVL(p_appl_rec.spcl_grp_2, dup_cur_rec.spcl_grp_2),
X_Common_App => NVL(p_appl_rec.common_app, dup_cur_rec.common_app),
X_application_type => NVL(p_appl_rec.admission_application_type, dup_cur_rec.application_type),
X_choice_number => NVL(p_appl_rec.choice_number, dup_cur_rec.choice_number),
X_routeb_pref => NVL(p_appl_rec.routeb_pref, dup_cur_rec.routeb_pref),
X_alt_appl_id => NVL(p_appl_rec.alt_appl_id, dup_cur_rec.alt_appl_id),
x_appl_fee_amt => NVL(p_appl_rec.appl_fee_amt, dup_cur_rec.appl_fee_amt)
);
UPDATE
igs_ad_apl_int
SET
status = cst_s_val_1,
admission_Appl_number = p_appl_rec.update_adm_appl_number,
acad_ci_sequence_number = NVL(p_appl_rec.Acad_Ci_Sequence_Number, dup_cur_rec.Acad_Ci_Sequence_Number),
acad_cal_type = NVL(p_appl_rec.Acad_Cal_Type, dup_cur_rec.Acad_Cal_Type),
adm_cal_type = NVL(p_appl_rec.Adm_Cal_Type, dup_cur_rec.Adm_Cal_Type),
Adm_Ci_Sequence_Number = NVL(p_appl_rec.Adm_Ci_Sequence_Number, dup_cur_rec.Adm_Ci_Sequence_Number),
match_ind = DECODE (
p_appl_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
WHERE
interface_appl_id = p_appl_rec.interface_appl_id;
ROLLBACK TO before_update_appl;
l_label :='igs.plsql.igs_ad_imp_004.update_application_details.exception '|| l_msg_data;
UPDATE IGS_AD_APL_INT
SET status = cst_s_val_3,
error_code = l_error_code,
error_text = l_error_text,
match_ind = DECODE (
p_appl_rec.match_ind,
NULL, cst_mi_val_11,
match_ind)
WHERE interface_appl_id = p_appl_rec.interface_appl_id;
END update_application_details;
SELECT tab.* --multiorg table , so rowid need not be selected explicitly
FROM IGS_AD_PS_APPL tab
WHERE person_id = cp_person_id AND
admission_appl_number = cp_appl_no AND
nominated_course_cd = cp_nominated_course_cd;
SELECT adm_cal_type, adm_ci_sequence_number, acad_cal_type , s_admission_process_type
FROM igs_Ad_appl_all
WHERE person_id = cp_person_id
AND admission_Appl_number = cp_appl_no;
SELECT NVL(MAX(sequence_number), 0) + 1
FROM IGS_AD_PS_APPL_INST
WHERE
person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_applinst_rec.nominated_course_cd;
PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE
,p_nominated_course_cd IN igs_pe_typ_instances_all.nominated_course_cd%TYPE
,p_person_id IN igs_pe_typ_instances_all.person_id%TYPE
,p_adm_appl_number IN igs_pe_typ_instances_all.admission_appl_number%TYPE ) AS
l_rowid VARCHAR2(25);
SELECT
person_type_code
FROM
igs_pe_person_types
WHERE
system_type=l_system_type;
IGS_PE_TYP_INSTANCES_PKG.insert_row(
x_rowid=>l_rowid,
x_org_id=>l_org_id,
x_person_id=>p_person_id,
x_course_cd=>NULL,
x_type_instance_id=>l_type_instance_id,
x_person_type_code=>l_person_type_code,
x_cc_version_number=>NULL,
x_funnel_status =>NULL,
x_admission_appl_number=> p_admission_Appl_number,
x_nominated_course_cd=> p_nominated_course_cd,--c_admappl_pgm_rec.nominated_course_cd,
x_ncc_version_number=>NULL,
x_sequence_number =>p_sequence_number,
x_start_date=>SYSDATE,
x_end_date=>NULL,
x_create_method =>'CREATE_APPL_INSTANCE',
x_ended_by => NULL,
x_end_method =>NULL,
x_mode=>'R',
x_emplmnt_category_code =>null);
END update_person_type;
SAVEPOINT before_insert_ps_appl;
IGS_AD_PS_APPL_PKG.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Admission_Appl_Number => p_admission_Appl_number,
X_Nominated_Course_Cd => p_applinst_rec.nominated_course_cd,
X_Transfer_Course_Cd => p_applinst_rec.transfer_course_cd,
X_Basis_For_Admission_Type => p_applinst_rec.basis_for_admission_type,
X_Admission_Cd => NULL,
X_Course_Rank_Set => NULL,
X_Course_Rank_Schedule => NULL,
X_Req_For_Reconsideration_Ind => 'N',
X_Req_For_Adv_Standing_Ind => p_applinst_rec.req_for_adv_standing_ind,
X_Org_Id => l_org_id
);
IGS_AD_PS_APPL_PKG.update_Row (
X_RowId => c_prg_exists_rec.row_id,
X_Person_Id => NVL(p_person_id,c_prg_exists_rec.person_id),
X_Admission_Appl_Number => NVL(p_admission_Appl_number,c_prg_exists_rec.admission_appl_number),
X_Nominated_Course_Cd => NVL(p_applinst_rec.nominated_course_cd,c_prg_exists_rec.nominated_course_cd),
X_Transfer_Course_Cd => NVL(p_applinst_rec.transfer_course_cd,c_prg_exists_rec.transfer_course_cd),
X_Basis_For_Admission_Type => NVL(p_applinst_rec.basis_for_admission_type,c_prg_exists_rec.basis_for_admission_type),
X_Admission_Cd => c_prg_exists_rec.admission_cd,
X_Course_Rank_Set => c_prg_exists_rec.Course_Rank_Set,
X_Course_Rank_Schedule => c_prg_exists_rec.Course_Rank_Schedule,
X_Req_For_Reconsideration_Ind => c_prg_exists_rec.req_for_reconsideration_ind,
X_Req_For_Adv_Standing_Ind => NVL(p_applinst_rec.req_for_adv_standing_ind,c_prg_exists_rec.req_for_adv_standing_ind),
X_Mode => 'R'
);
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => 'E322');
IGS_AD_PS_APPL_INST_PKG.Insert_Row (
X_ROWID => lv_rowid,
X_PERSON_ID => p_Person_Id,
X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
X_NOMINATED_COURSE_CD => p_applinst_rec.nominated_course_cd,
X_SEQUENCE_NUMBER => l_acai_sequence_number,
X_PREDICTED_GPA => NULL,
X_ACADEMIC_INDEX => NULL,
X_ADM_CAL_TYPE => p_appl_rec.adm_cal_type,
X_APP_FILE_LOCATION => NULL,
X_ADM_CI_SEQUENCE_NUMBER => p_appl_rec.adm_ci_sequence_number,
X_COURSE_CD => p_applinst_rec.nominated_course_cd,
X_APP_SOURCE_ID => p_applinst_rec.app_source_id,
X_CRV_VERSION_NUMBER => p_applinst_rec.Crv_Version_Number,
X_WAITLIST_RANK => NULL,
X_LOCATION_CD => p_applinst_rec.Location_Cd,
X_ATTENT_OTHER_INST_CD => NULL,
X_ATTENDANCE_MODE => p_applinst_rec.Attendance_Mode,
X_EDU_GOAL_PRIOR_ENROLL_ID => p_applinst_rec.edu_goal_prior_enroll_id,
X_ATTENDANCE_TYPE => p_applinst_rec.Attendance_Type,
X_DECISION_MAKE_ID => NULL,
X_UNIT_SET_CD => p_applinst_rec.Unit_Set_Cd,
X_DECISION_DATE => NULL,
X_ATTRIBUTE_CATEGORY => p_applinst_rec.attribute_category ,
X_ATTRIBUTE1=> p_applinst_rec.ATTRIBUTE1,
X_ATTRIBUTE2=> p_applinst_rec.ATTRIBUTE2,
X_ATTRIBUTE3=> p_applinst_rec.ATTRIBUTE3,
X_ATTRIBUTE4=>p_applinst_rec.ATTRIBUTE4,
X_ATTRIBUTE5=>p_applinst_rec.ATTRIBUTE5,
X_ATTRIBUTE6=>p_applinst_rec.ATTRIBUTE6,
X_ATTRIBUTE7=>p_applinst_rec.ATTRIBUTE7,
X_ATTRIBUTE8=>p_applinst_rec.ATTRIBUTE8,
X_ATTRIBUTE9=>p_applinst_rec.ATTRIBUTE9,
X_ATTRIBUTE10=>p_applinst_rec.ATTRIBUTE10,
X_ATTRIBUTE11=>p_applinst_rec.ATTRIBUTE11,
X_ATTRIBUTE12=>p_applinst_rec.ATTRIBUTE12,
X_ATTRIBUTE13=>p_applinst_rec.ATTRIBUTE13,
X_ATTRIBUTE14=>p_applinst_rec.ATTRIBUTE14,
X_ATTRIBUTE15=>p_applinst_rec.ATTRIBUTE15,
X_ATTRIBUTE16=>p_applinst_rec.ATTRIBUTE16,
X_ATTRIBUTE17=>p_applinst_rec.ATTRIBUTE17,
X_ATTRIBUTE18=>p_applinst_rec.ATTRIBUTE18,
X_ATTRIBUTE19=>p_applinst_rec.ATTRIBUTE19,
X_ATTRIBUTE20=>p_applinst_rec.ATTRIBUTE20,
X_WAITLIST_STATUS => NULL,
X_ATTRIBUTE21=>p_applinst_rec.ATTRIBUTE21,
X_ATTRIBUTE22=>p_applinst_rec.ATTRIBUTE22,
X_ATTRIBUTE23=>p_applinst_rec.ATTRIBUTE23,
X_ATTRIBUTE24=>p_applinst_rec.ATTRIBUTE24,
X_ATTRIBUTE25=>p_applinst_rec.ATTRIBUTE25,
X_ATTRIBUTE26=>p_applinst_rec.ATTRIBUTE26,
X_ATTRIBUTE27=>p_applinst_rec.ATTRIBUTE27,
X_ATTRIBUTE28=>p_applinst_rec.ATTRIBUTE28,
X_ATTRIBUTE29=>p_applinst_rec.ATTRIBUTE29,
X_ATTRIBUTE30=>p_applinst_rec.ATTRIBUTE30,
X_ATTRIBUTE31=>p_applinst_rec.ATTRIBUTE31,
X_ATTRIBUTE32=>p_applinst_rec.ATTRIBUTE32,
X_ATTRIBUTE33=>p_applinst_rec.ATTRIBUTE33,
X_ATTRIBUTE34=>p_applinst_rec.ATTRIBUTE34,
X_ATTRIBUTE35=>p_applinst_rec.ATTRIBUTE35,
X_ATTRIBUTE36=>p_applinst_rec.ATTRIBUTE36,
X_ATTRIBUTE37=>p_applinst_rec.ATTRIBUTE37,
X_ATTRIBUTE38=>p_applinst_rec.ATTRIBUTE38,
X_ATTRIBUTE39=>p_applinst_rec.ATTRIBUTE39,
X_ATTRIBUTE40=>p_applinst_rec.ATTRIBUTE40,
X_SS_APPLICATION_ID=> NULL,
X_SS_PWD=> NULL,
X_DECISION_REASON_ID=> NULL,
X_US_VERSION_NUMBER => p_applinst_rec.Us_Version_Number,
X_DECISION_NOTES=> NULL,
X_PENDING_REASON_ID=> NULL,
X_PREFERENCE_NUMBER => p_applinst_rec.Preference_Number,
X_ADM_DOC_STATUS=> l_Adm_Doc_Status,
X_ADM_ENTRY_QUAL_STATUS=> l_Adm_Entry_Qual_Status,
X_DEFICIENCY_IN_PREP=> NULL,
X_LATE_ADM_FEE_STATUS => l_Late_Adm_Fee_Status,
X_SPL_CONSIDER_COMMENTS=> NULL,
X_APPLY_FOR_FINAID => p_applinst_rec.apply_for_finaid,
X_FINAID_APPLY_DATE=> l_finaid_apply_date,
X_ADM_OUTCOME_STATUS => l_adm_pending_outcome_status,
X_ADM_OTCM_STAT_AUTH_PER_ID => NULL,
X_ADM_OUTCOME_STATUS_AUTH_DT => NULL,
X_ADM_OUTCOME_STATUS_REASON => NULL,
X_OFFER_DT => NULL,
X_OFFER_RESPONSE_DT => NULL,
X_PRPSD_COMMENCEMENT_DT => NULL,
X_ADM_CNDTNL_OFFER_STATUS => l_adm_cndtnl_offer_status,
X_CNDTNL_OFFER_SATISFIED_DT => NULL,
X_CNDNL_OFR_MUST_BE_STSFD_IND => 'N',
X_ADM_OFFER_RESP_STATUS => l_adm_offer_resp_status,
X_ACTUAL_RESPONSE_DT => NULL,
X_ADM_OFFER_DFRMNT_STATUS => l_adm_offer_dfrmnt_status,
X_DEFERRED_ADM_CAL_TYPE => NULL,
X_DEFERRED_ADM_CI_SEQUENCE_NUM => NULL,
X_DEFERRED_TRACKING_ID => NULL,
X_ASS_RANK => p_applinst_rec.ass_rank,
X_SECONDARY_ASS_RANK => p_applinst_rec.secondary_ass_rank,
X_INTR_ACCEPT_ADVICE_NUM => p_applinst_rec.intrntnl_acceptance_advice_num,
X_ASS_TRACKING_ID => p_applinst_rec.ass_tracking_id,
X_FEE_CAT=> NVL(p_applinst_rec.Fee_Cat,l_fee_cat),
X_HECS_PAYMENT_OPTION => NVL(p_applinst_rec.Hecs_Payment_Option, l_Hecs_Payment_Option),
X_EXPECTED_COMPLETION_YR => NVL(p_applinst_rec.expected_completion_yr, l_expected_completion_yr),
X_EXPECTED_COMPLETION_PERD => NVL(p_applinst_rec.expected_completion_perd, l_expected_completion_perd),
X_CORRESPONDENCE_CAT => NULL,
X_ENROLMENT_CAT => NVL(p_applinst_rec.Enrolment_Cat,l_Enrolment_Cat),
X_FUNDING_SOURCE => NVL(p_applinst_rec.funding_source,l_funding_source),
X_APPLICANT_ACPTNCE_CNDTN => NULL,
X_CNDTNL_OFFER_CNDTN => NULL,
X_AUTHORIZED_DT => NULL,
X_AUTHORIZING_PERS_ID => NULL,
X_IDX_CALC_DATE => NULL,
X_MODE =>'R',
X_FUT_ACAD_CAL_TYPE => NULL , -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => NULL ,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => NULL , -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => NULL, -- Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => NULL,-- Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
X_Org_Id => l_org_id,
X_ENTRY_STATUS => p_applinst_rec.entry_status,
X_ENTRY_LEVEL => p_applinst_rec.entry_level,
X_SCH_APL_TO_ID => p_applinst_rec.sch_apl_to_id);
update_person_type(p_sequence_number=> l_acai_sequence_number
,p_nominated_course_cd => p_applinst_rec.nominated_course_cd
,p_person_id => p_person_id
,p_adm_appl_number => p_admission_Appl_number);
update_appl_inst(p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '1',
p_error_text => NULL, p_sequence_number => l_acai_sequence_number, p_error_code => NULL,
p_admission_appl_number => p_admission_Appl_number,
p_match_ind => NVL(p_applinst_rec.match_ind , cst_mi_val_11) );
ROLLBACK TO before_insert_ps_appl;
update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
UPDATE igs_ad_ps_appl_inst_int aplinst
SET
status = '3'
, error_code = 'E706'
,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E706', 8405)
WHERE interface_run_id = p_interface_run_id
AND status ='2'
AND update_adm_seq_number IS NOT NULL
AND NOT EXISTS ( SELECT 1 FROM igs_ad_ps_appl_inst
WHERE person_id = aplinst.person_id
AND admission_Appl_number = aplinst.admission_Appl_number
AND nominated_course_cd = aplinst.nominated_course_cd
AND sequence_number = aplinst.update_adm_seq_number
) ;
UPDATE igs_ad_ps_appl_inst_int
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_ps_appl_inst_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_ad_ps_appl_inst_int
SET
status = '1'
, match_ind = '19'
, SEQUENCE_NUMBER = update_adm_seq_number
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND update_adm_seq_number IS NOT NULL ;
IF applinst_rec.dmlmode = cst_insert THEN
FOR p_appl_rec IN appl_cur_upd(applinst_rec.interface_appl_id) LOOP
IF l_admission_application_type IS NULL
OR p_appl_rec.admission_application_type <> l_admission_application_type THEN
populate_apc_columns(p_appl_rec.admission_application_type);
ELSIF applinst_rec.dmlmode = cst_update THEN
BEGIN
FOR p_appl_rec IN appl_cur_upd(applinst_rec.interface_appl_id) LOOP
IF l_admission_application_type IS NULL
OR p_appl_rec.admission_application_type <> l_admission_application_type THEN
populate_apc_columns(p_appl_rec.admission_application_type);
SAVEPOINT before_update_appl_inst;
igs_ad_ps_appl_inst_pkg.update_row(
x_rowid => dup_applinst_rec.rowid,
x_person_id =>dup_applinst_rec.person_id,
x_admission_appl_number => dup_applinst_rec.admission_appl_number,
x_nominated_course_cd=> dup_applinst_rec.nominated_course_cd,
x_sequence_number=> dup_applinst_rec.sequence_number,
x_predicted_gpa=> dup_applinst_rec.predicted_gpa,
x_academic_index=>dup_applinst_rec.academic_index,
x_adm_cal_type => dup_applinst_rec.adm_cal_type,
x_app_file_location =>dup_applinst_rec.app_file_location,
x_adm_ci_sequence_number=>dup_applinst_rec.adm_ci_sequence_number,
x_course_cd=>dup_applinst_rec.course_cd,
x_app_source_id=> NVL(applinst_rec.app_source_id,dup_applinst_rec.app_source_id),
x_crv_version_number=>NVL(applinst_rec.crv_version_number, dup_applinst_rec.crv_version_number),
x_waitlist_rank=>dup_applinst_rec.waitlist_rank,
x_waitlist_status=>dup_applinst_rec.waitlist_status,
x_location_cd=> NVL(applinst_rec.location_cd,dup_applinst_rec.location_cd),
x_attent_other_inst_cd=>dup_applinst_rec.attent_other_inst_cd,
x_attendance_mode=>NVL(applinst_rec.attendance_mode,dup_applinst_rec.attendance_mode),
x_edu_goal_prior_enroll_id=> NVL(applinst_rec.edu_goal_prior_enroll_id,dup_applinst_rec.edu_goal_prior_enroll_id),
x_attendance_type=>NVL(applinst_rec.attendance_type,dup_applinst_rec.attendance_type),
x_decision_make_id=>dup_applinst_rec.decision_make_id,
x_unit_set_cd=>NVL(applinst_rec.unit_set_cd,dup_applinst_rec.unit_set_cd),
x_decision_date=>dup_applinst_rec.decision_date,
x_attribute_category=>NVL(applinst_rec.attribute_category,dup_applinst_rec.attribute_category),
x_attribute1=>NVL(applinst_rec.attribute1,dup_applinst_rec.attribute1),
x_attribute2=>NVL(applinst_rec.attribute2,dup_applinst_rec.attribute2),
x_attribute3=>NVL(applinst_rec.attribute3,dup_applinst_rec.attribute3),
x_attribute4=>NVL(applinst_rec.attribute4,dup_applinst_rec.attribute4),
x_attribute5=>NVL(applinst_rec.attribute5,dup_applinst_rec.attribute5),
x_attribute6=>NVL(applinst_rec.attribute6,dup_applinst_rec.attribute6),
x_attribute7=>NVL(applinst_rec.attribute7,dup_applinst_rec.attribute7),
x_attribute8=>NVL(applinst_rec.attribute8,dup_applinst_rec.attribute8),
x_attribute9=>NVL(applinst_rec.attribute9,dup_applinst_rec.attribute9),
x_attribute10=>NVL(applinst_rec.attribute10,dup_applinst_rec.attribute10),
x_attribute11=>NVL(applinst_rec.attribute11,dup_applinst_rec.attribute11),
x_attribute12=>NVL(applinst_rec.attribute12,dup_applinst_rec.attribute12),
x_attribute13=>NVL(applinst_rec.attribute13,dup_applinst_rec.attribute13),
x_attribute14=>NVL(applinst_rec.attribute14,dup_applinst_rec.attribute14),
x_attribute15=>NVL(applinst_rec.attribute15,dup_applinst_rec.attribute15),
x_attribute16=>NVL(applinst_rec.attribute16,dup_applinst_rec.attribute16),
x_attribute17=>NVL(applinst_rec.attribute17,dup_applinst_rec.attribute17),
x_attribute18=>NVL(applinst_rec.attribute18,dup_applinst_rec.attribute18),
x_attribute19=>NVL(applinst_rec.attribute19,dup_applinst_rec.attribute19),
x_attribute20=>NVL(applinst_rec.attribute20,dup_applinst_rec.attribute20),
x_decision_reason_id=>dup_applinst_rec.decision_reason_id,
x_us_version_number=>NVL(applinst_rec.us_version_number,dup_applinst_rec.us_version_number),
x_decision_notes=>dup_applinst_rec.decision_notes,
x_pending_reason_id=>dup_applinst_rec.pending_reason_id,
x_preference_number=>NVL(applinst_rec.preference_number,dup_applinst_rec.preference_number),
x_adm_doc_status=>dup_applinst_rec.adm_doc_status,
x_adm_entry_qual_status=>dup_applinst_rec.adm_entry_qual_status,
x_deficiency_in_prep=>dup_applinst_rec.deficiency_in_prep,
x_late_adm_fee_status=>dup_applinst_rec.late_adm_fee_status,
x_spl_consider_comments=>dup_applinst_rec.spl_consider_comments,
x_apply_for_finaid=> NVL(applinst_rec.apply_for_finaid,dup_applinst_rec.apply_for_finaid),
x_finaid_apply_date=> l_finaid_apply_date,
x_adm_outcome_status=>dup_applinst_rec.adm_outcome_status,
x_adm_otcm_stat_auth_per_id=>dup_applinst_rec.adm_otcm_status_auth_person_id,
x_adm_outcome_status_auth_dt=>dup_applinst_rec.adm_outcome_status_auth_dt,
x_adm_outcome_status_reason=> dup_applinst_rec.adm_outcome_status_reason,
x_offer_dt=>dup_applinst_rec.offer_dt,
x_offer_response_dt=>dup_applinst_rec.offer_response_dt,
x_prpsd_commencement_dt=>dup_applinst_rec.prpsd_commencement_dt,
x_adm_cndtnl_offer_status=>dup_applinst_rec.adm_cndtnl_offer_status,
x_cndtnl_offer_satisfied_dt=> dup_applinst_rec.cndtnl_offer_satisfied_dt,
x_cndnl_ofr_must_be_stsfd_ind=>dup_applinst_rec.cndtnl_offer_must_be_stsfd_ind,
x_adm_offer_resp_status=> dup_applinst_rec.adm_offer_resp_status,
x_actual_response_dt=> dup_applinst_rec.actual_response_dt,
x_adm_offer_dfrmnt_status=> dup_applinst_rec.adm_offer_dfrmnt_status,
x_deferred_adm_cal_type=> dup_applinst_rec.deferred_adm_cal_type,
x_deferred_adm_ci_sequence_num=> dup_applinst_rec.deferred_adm_ci_sequence_num,
x_deferred_tracking_id=> dup_applinst_rec.deferred_tracking_id,
x_ass_rank=> NVL(applinst_rec.ass_rank,dup_applinst_rec.ass_rank),
x_secondary_ass_rank=>NVL(applinst_rec.secondary_ass_rank,dup_applinst_rec.secondary_ass_rank),
x_intr_accept_advice_num=>NVL(applinst_rec.intrntnl_acceptance_advice_num,dup_applinst_rec.intrntnl_acceptance_advice_num),
x_ass_tracking_id=> NVL(applinst_rec.ass_tracking_id,dup_applinst_rec.ass_tracking_id),
x_fee_cat=>NVL(applinst_rec.fee_cat,dup_applinst_rec.fee_cat),
x_hecs_payment_option=>NVL(applinst_rec.hecs_payment_option, dup_applinst_rec.hecs_payment_option),
x_expected_completion_yr=>NVL(applinst_rec.expected_completion_yr, dup_applinst_rec.expected_completion_yr),
x_expected_completion_perd=>NVL(applinst_rec.expected_completion_perd, dup_applinst_rec.expected_completion_perd),
x_correspondence_cat=> dup_applinst_rec.correspondence_cat,
x_enrolment_cat=>NVL(applinst_rec.enrolment_cat,dup_applinst_rec.enrolment_cat),
x_funding_source=> NVL(applinst_rec.funding_source, dup_applinst_rec.funding_source),
x_applicant_acptnce_cndtn=> dup_applinst_rec.applicant_acptnce_cndtn,
x_cndtnl_offer_cndtn=> dup_applinst_rec.cndtnl_offer_cndtn,
x_ss_application_id=>dup_applinst_rec.ss_application_id,
x_ss_pwd=>dup_applinst_rec.ss_pwd, --Bug Enh No : 1891835 Added two columns
x_authorized_dt => dup_applinst_rec.authorized_dt,
--Bug Enh No : 1891835 Added two columns
x_authorizing_pers_id => dup_applinst_rec.authorizing_pers_id,
-- Enh Bug#1964478 added three parameters
x_entry_status => NVL(applinst_rec.entry_status, dup_applinst_rec.entry_status),
x_entry_level => NVL(applinst_rec.entry_level, dup_applinst_rec.entry_level),
x_sch_apl_to_id=> NVL(applinst_rec.sch_apl_to_id, dup_applinst_rec.sch_apl_to_id),
x_idx_calc_date => dup_applinst_rec.idx_calc_date,
x_fut_acad_cal_type => dup_applinst_rec.future_acad_cal_type, -- bug # 2217104
x_fut_acad_ci_sequence_number => dup_applinst_rec.future_acad_ci_sequence_number,-- bug # 2217104
x_fut_adm_cal_type => dup_applinst_rec.future_adm_cal_type, -- bug # 2217104
x_fut_adm_ci_sequence_number => dup_applinst_rec.future_adm_ci_sequence_number, -- bug # 2217104
x_prev_term_adm_appl_number => dup_applinst_rec.previous_term_adm_appl_number, -- bug # 2217104
x_prev_term_sequence_number => dup_applinst_rec.previous_term_sequence_number, -- bug # 2217104
x_fut_term_adm_appl_number => dup_applinst_rec.future_term_adm_appl_number, -- bug # 2217104
x_fut_term_sequence_number => dup_applinst_rec.future_term_sequence_number, -- bug # 2217104
x_def_acad_cal_type => dup_applinst_rec.def_acad_cal_type, --bug 2395510
x_def_acad_ci_sequence_num => dup_applinst_rec.def_acad_ci_sequence_num, --bug 2395510
x_def_prev_term_adm_appl_num => dup_applinst_rec.def_prev_term_adm_appl_num,--bug 2395510
x_def_prev_appl_sequence_num => dup_applinst_rec.def_prev_appl_sequence_num,--bug 2395510
x_def_term_adm_appl_num => dup_applinst_rec.def_term_adm_appl_num,--bug 2395510
x_def_appl_sequence_num => dup_applinst_rec.def_appl_sequence_num,--bug 2395510
x_mode=>'R',
x_appl_inst_status =>dup_applinst_rec.appl_inst_status,
x_ais_reason =>dup_applinst_rec.ais_reason
);
UPDATE igs_ad_ps_appl_inst_int
SET
status = '1',
sequence_number = dup_applinst_rec.sequence_number
WHERE interface_ps_appl_inst_id = applinst_rec.interface_ps_appl_inst_id;
ROLLBACK TO before_update_appl_inst;
UPDATE igs_ad_ps_appl_inst_int
SET
status = '3',
error_code = l_error_code,
error_text = l_error_text
WHERE interface_ps_appl_inst_id = applinst_rec.interface_ps_appl_inst_id;
UPDATE igs_ad_ps_appl_inst_int applinst
SET
status = '1'
, match_ind = '23'
, sequence_number = applinst.update_adm_seq_number
WHERE EXISTS ( SELECT 'X'
FROM igs_ad_ps_appl_inst ai
WHERE ai.person_id = applinst.person_id AND
ai.nominated_course_cd = applinst.nominated_course_cd AND
ai.admission_appl_number = applinst.admission_Appl_number AND
ai.sequence_number = applinst.update_adm_seq_number AND
NVL(ai.crv_version_number,-99)=
NVL(NVL(applinst.crv_version_number, ai.crv_version_number),-99) AND
NVL(ai.location_cd,'*') =
NVL(NVL(applinst.location_cd, ai.location_cd),'*') AND
NVL(ai.attendance_mode,'*')=
NVL(NVL(applinst.attendance_mode, ai.attendance_mode), '*') AND
NVL(ai.edu_goal_prior_enroll_id,-99) =
NVL(NVL(applinst.edu_goal_prior_enroll_id, ai.edu_goal_prior_enroll_id), -99) AND
NVL(ai.app_source_id,-99) =
NVL(NVL(applinst.app_source_id, ai.app_source_id), -99) AND
NVL(ai.attendance_type,'*') =
NVL(NVL(applinst.attendance_type, ai.attendance_type), '*') AND
NVL(ai.unit_set_cd, '*') =
NVL(NVL(applinst.unit_set_cd, ai.unit_set_cd ), '*') AND
NVL(ai.us_version_number,-99)=
NVL(NVL(applinst.us_version_number, ai.us_version_number), -99) AND
NVL(ai.preference_number,-99)=
NVL(NVL(applinst.preference_number, ai.preference_number), -99) AND
NVL(ai.apply_for_finaid,'*') =
NVL(NVL(applinst.apply_for_finaid, ai.apply_for_finaid), '*') AND
NVL(ai.finaid_apply_date,sysdate) =
NVL(NVL(applinst.finaid_apply_date, ai.finaid_apply_date), sysdate) AND
NVL(ai.attribute_category,'*')=
NVL(NVL(applinst.attribute_category, ai.attribute_category), '*') AND
NVL(ai.attribute1,'*') = NVL(NVL(applinst.attribute1, ai.attribute1), '*') AND
NVL(ai.attribute2,'*') = NVL(NVL(applinst.attribute2, ai.attribute2), '*') AND
NVL(ai.attribute3,'*') = NVL(NVL(applinst.attribute3, ai.attribute3), '*') AND
NVL(ai.attribute4,'*') = NVL(NVL(applinst.attribute4, ai.attribute4), '*') AND
NVL(ai.attribute5,'*') = NVL(NVL(applinst.attribute5, ai.attribute5), '*') AND
NVL(ai.attribute6,'*') = NVL(NVL(applinst.attribute6, ai.attribute6), '*') AND
NVL(ai.attribute7,'*') = NVL(NVL(applinst.attribute7, ai.attribute7), '*') AND
NVL(ai.attribute8,'*') = NVL(NVL(applinst.attribute8, ai.attribute8), '*') AND
NVL(ai.attribute9,'*') = NVL(NVL(applinst.attribute9, ai.attribute9), '*') AND
NVL(ai.attribute10,'*') = NVL(NVL(applinst.attribute10, ai.attribute10), '*') AND
NVL(ai.attribute11,'*') = NVL(NVL(applinst.attribute11, ai.attribute11), '*') AND
NVL(ai.attribute12,'*') = NVL(NVL(applinst.attribute12, ai.attribute12), '*') AND
NVL(ai.attribute13,'*') = NVL(NVL(applinst.attribute13, ai.attribute13), '*') AND
NVL(ai.attribute14,'*') = NVL(NVL(applinst.attribute14, ai.attribute14), '*') AND
NVL(ai.attribute15,'*') = NVL(NVL(applinst.attribute15, ai.attribute15), '*') AND
NVL(ai.attribute16,'*') = NVL(NVL(applinst.attribute16, ai.attribute16), '*') AND
NVL(ai.attribute17,'*') = NVL(NVL(applinst.attribute17, ai.attribute17), '*') AND
NVL(ai.attribute18,'*') = NVL(NVL(applinst.attribute18, ai.attribute18), '*') AND
NVL(ai.attribute19,'*') = NVL(NVL(applinst.attribute19, ai.attribute19), '*') AND
NVL(ai.attribute20,'*') = NVL(NVL(applinst.attribute20, ai.attribute20), '*')
);
UPDATE igs_ad_ps_appl_inst_int
SET
status = '3'
, match_ind = '20'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND update_adm_seq_number IS NOT NULL;
UPDATE igs_ad_ps_appl_inst_int
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;
UPDATE igs_ad_apl_int api
SET
status = '3',
error_code = 'E523',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E523', 8405)
WHERE interface_run_id = p_interface_run_id
AND update_adm_appl_number IS NOT NULL
AND NOT EXISTS ( SELECT 1 FROM IGS_AD_APPL appl_oss
WHERE person_id = api.person_id
AND admission_Appl_number =
NVL(api.update_adm_appl_number, appl_oss.admission_Appl_number)
) ;
UPDATE igs_ad_apl_int
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_apl_int
SET
status = '1'
,error_code = NULL
,error_text = 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_apl_int api
SET
status = '1'
, match_ind = '19'
, admission_appl_number = update_adm_appl_number
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND update_adm_appl_number IS NOT NULL;
UPDATE igs_ad_apl_int api
SET
status = '3'
,error_code = 'E677'
,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E677', 8405)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND update_adm_appl_number IS NOT NULL
AND ( EXISTS (SELECT 1 FROM igs_ad_apphist_int WHERE person_id = api.person_id
AND admission_appl_number = api.update_adm_appl_number )
OR EXISTS (SELECT 1 FROM igs_ad_insthist_int WHERE person_id = api.person_id
AND admission_appl_number = api.update_adm_appl_number )
);
UPDATE igs_ad_apl_int api
SET
status = '3'
,error_code = 'E176'
,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E176', 8405)
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NOT EXISTS( SELECT '1'
FROM igs_ad_ss_appl_typ
WHERE admission_application_type = api.admission_application_type);
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 appl_rec IN appl_cur
LOOP
IF l_admission_application_type IS NULL
OR appl_rec.admission_application_type <> l_admission_application_type THEN
populate_apc_columns(appl_rec.admission_application_type);
IF appl_rec.dmlmode = cst_insert THEN
SAVEPOINT before_create_application;
UPDATE
igs_ad_apl_int
SET
status = '3',
error_code = 'E347'
,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE
interface_appl_id = appl_rec.interface_appl_id;
UPDATE
igs_ad_apl_int
SET
status = '4',
error_code = 'E347'
,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE
interface_appl_id = appl_rec.interface_appl_id;
UPDATE
igs_ad_apl_int
SET
status = cst_s_val_1,
admission_Appl_number = l_admission_appl_number
WHERE
interface_appl_id = appl_rec.interface_appl_id;
ELSIF appl_rec.dmlmode = cst_update THEN
OPEN c_dup_cur(appl_rec);
update_application_details(appl_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_ad_apl_int api
SET
status = '1'
, match_ind = '23'
, admission_appl_number = api.update_adm_appl_number
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL (match_ind, '15') = '15'
AND EXISTS ( SELECT 1 FROM IGS_AD_APPL appl_oss
WHERE person_id = api.person_id
AND admission_appl_number = api.update_adm_appl_number
AND TRUNC(appl_dt) = TRUNC(api.appl_dt)
AND tac_appl_ind = api.tac_appl_ind
AND NVL(spcl_grp_1, -1) = NVL( NVL(api.spcl_grp_1, appl_oss.spcl_grp_1), -1)
AND NVL(spcl_grp_2,-1) = NVL( NVL(api.spcl_grp_2, appl_oss.spcl_grp_2), -1)
AND NVL(common_app, '*') = NVL( NVL(api.common_app,appl_oss.common_app),'*')
AND NVL(choice_number, -1) = NVL( NVL(api.choice_number, appl_oss.choice_number), -1)
AND NVL(routeb_pref , '*') = NVL( NVL(api.routeb_pref,appl_oss.routeb_pref), '*')
AND NVL(alt_appl_id , '*') = NVL( NVL(api.alt_appl_id, appl_oss.alt_appl_id) , '*')
AND NVL(adm_cal_type,'*') = NVL(NVL(api.adm_cal_type, appl_oss.adm_cal_type),'*')
AND NVL(acad_cal_type, '*') = NVL(NVL(api.acad_cal_type, appl_oss.acad_cal_type),'*')
AND NVL(api.acad_ci_sequence_number,-99) = NVL(NVL(api.acad_ci_sequence_number, appl_oss.acad_ci_sequence_number),-99)
AND NVL(api.adm_ci_sequence_number,-99) = NVL(NVL(api.adm_ci_sequence_number, appl_oss.adm_ci_sequence_number), -99)
);
UPDATE igs_ad_ps_appl_inst_int a
SET (person_id, admission_appl_number,interface_run_id ) =
( SELECT person_id, admission_appl_number ,interface_run_id
FROM igs_ad_apl_int
WHERE interface_appl_id = a.interface_appl_id
AND status IN ('1','4') )
WHERE status = '2'
AND interface_appl_id IN (SELECT interface_appl_id
FROM igs_ad_apl_int
WHERE interface_run_id = p_interface_run_id
AND update_adm_appl_number IS NOT NULL
AND status IN ('1','4'));
UPDATE igs_ad_apl_int api
SET
status = '3'
, match_ind = '20'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND update_adm_appl_number IS NOT NULL;
UPDATE igs_ad_apl_int api
SET
status = '3'
, error_code = 'E700'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND match_ind IS NOT NULL;