The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT c.absolute_val absolute_value
FROM IGS_CA_DA_INST c
WHERE
c.cal_type = cp_adm_cal_type
AND c.ci_sequence_number = cp_adm_ci_seq_number
AND c.dt_alias = (select ADM_APPL_COURSE_STRT_DT_ALIAS from IGS_AD_CAL_CONF);
SELECT a.*
FROM igs_ad_ps_appl_inst a
WHERE a.adm_outcome_status IN
( SELECT adm_outcome_status
FROM igs_ad_ou_stat
WHERE s_adm_outcome_status = 'SUSPEND') ;
SELECT a.*
FROM IGS_AD_PS_APPL_INST a,
IGS_AD_APPL_ALL b
WHERE
a.person_id=b.person_id AND
a.admission_appl_number = b.admission_appl_number AND
b.acad_cal_type = cp_acad_cal_type AND
b.acad_ci_sequence_number = cp_acad_ci_sequence_number AND
a.adm_outcome_status IN (SELECT adm_outcome_status
FROM igs_ad_ou_stat
WHERE s_adm_outcome_status = 'SUSPEND');
SELECT a.*
FROM IGS_AD_PS_APPL_INST a, IGS_AD_APPL_ALL b
WHERE a.person_id=b.person_id AND
a.admission_appl_number = b.admission_appl_number AND
b.acad_cal_type = cp_acad_cal_type AND
b.acad_ci_sequence_number = cp_acad_ci_sequence_number AND
b.adm_cal_type = cp_adm_cal_type AND
b.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
a.adm_outcome_status IN (SELECT adm_outcome_status
FROM igs_ad_ou_stat
WHERE s_adm_outcome_status = 'SUSPEND');
SELECT a.*
FROM IGS_AD_PS_APPL_INST a, IGS_AD_APPL_ALL b
WHERE a.person_id=b.person_id AND
a.admission_appl_number = b.admission_appl_number AND
b.acad_cal_type = cp_acad_cal_type AND
b.acad_ci_sequence_number = cp_acad_ci_sequence_number AND
b.adm_cal_type = cp_adm_cal_type AND
b.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
b.admission_cat = cp_adm_cat AND
b.s_admission_process_type = cp_admission_process_type AND
a.adm_outcome_status IN (SELECT adm_outcome_status
FROM igs_ad_ou_stat
WHERE s_adm_outcome_status = 'SUSPEND');
SELECT alternate_code
FROM IGS_CA_INST
WHERE cal_type = cp_cal_type AND
sequence_number = cp_seq_no;
CURSOR c_get_person_number IS SELECT party_number FROM hz_parties
WHERE party_id = p_pid;
PROCEDURE update_appl_inst(cp_all_records_rec c_all_records_case_1%ROWTYPE)
IS
/*****************************************************************************************
Created By: [email protected]
Date Created : 09-16-2002
Purpose: ADCR040 - Change Suspended Applications to Withdrawn: Updates Application instance
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
lv_msg VARCHAR2(2000);
IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW (
X_Mode => 'R',
X_RowId => cp_all_records_rec.row_id,
X_Person_Id => cp_all_records_rec.Person_Id ,
X_Admission_Appl_Number => cp_all_records_rec.Admission_Appl_Number ,
X_Nominated_Course_Cd => cp_all_records_rec.Nominated_Course_Cd ,
X_Sequence_Number => cp_all_records_rec.Sequence_Number ,
X_Predicted_Gpa => cp_all_records_rec.Predicted_Gpa ,
X_Academic_Index => cp_all_records_rec.Academic_Index ,
X_Adm_Cal_Type => cp_all_records_rec.Adm_Cal_Type ,
X_App_File_Location => cp_all_records_rec.App_File_Location ,
X_Adm_Ci_Sequence_Number => cp_all_records_rec.Adm_Ci_Sequence_Number ,
X_Course_Cd => cp_all_records_rec.Course_Cd ,
X_App_Source_Id => cp_all_records_rec.App_Source_Id ,
X_Crv_Version_Number => cp_all_records_rec.Crv_Version_Number ,
X_Waitlist_Rank => cp_all_records_rec.Waitlist_Rank ,
X_Location_Cd => cp_all_records_rec.Location_Cd ,
X_Attent_Other_Inst_Cd => cp_all_records_rec.Attent_Other_Inst_Cd ,
X_Attendance_Mode => cp_all_records_rec.Attendance_Mode ,
X_Edu_Goal_Prior_Enroll_Id => cp_all_records_rec.Edu_Goal_Prior_Enroll_Id ,
X_Attendance_Type => cp_all_records_rec.Attendance_Type ,
X_Decision_Make_Id => cp_all_records_rec.Decision_Make_Id ,
X_Unit_Set_Cd => cp_all_records_rec.Unit_Set_Cd ,
X_Decision_Date => cp_all_records_rec.Decision_Date ,
X_Attribute_Category => cp_all_records_rec.Attribute_Category ,
X_Attribute1 => cp_all_records_rec.Attribute1 ,
X_Attribute2 => cp_all_records_rec.Attribute2 ,
X_Attribute3 => cp_all_records_rec.Attribute3 ,
X_Attribute4 => cp_all_records_rec.Attribute4 ,
X_Attribute5 => cp_all_records_rec.Attribute5 ,
X_Attribute6 => cp_all_records_rec.Attribute6 ,
X_Attribute7 => cp_all_records_rec.Attribute7 ,
X_Attribute8 => cp_all_records_rec.Attribute8 ,
X_Attribute9 => cp_all_records_rec.Attribute9 ,
X_Attribute10 => cp_all_records_rec.Attribute10 ,
X_Attribute11 => cp_all_records_rec.Attribute11 ,
X_Attribute12 => cp_all_records_rec.Attribute12 ,
X_Attribute13 => cp_all_records_rec.Attribute13 ,
X_Attribute14 => cp_all_records_rec.Attribute14 ,
X_Attribute15 => cp_all_records_rec.Attribute15 ,
X_Attribute16 => cp_all_records_rec.Attribute16 ,
X_Attribute17 => cp_all_records_rec.Attribute17 ,
X_Attribute18 => cp_all_records_rec.Attribute18 ,
X_Attribute19 => cp_all_records_rec.Attribute19 ,
X_Attribute20 => cp_all_records_rec.Attribute20 ,
X_Decision_Reason_Id => cp_all_records_rec.Decision_Reason_Id ,
X_Us_Version_Number => cp_all_records_rec.Us_Version_Number ,
X_Decision_Notes => cp_all_records_rec.Decision_Notes ,
X_Pending_Reason_Id => cp_all_records_rec.Pending_Reason_Id ,
X_Preference_Number => cp_all_records_rec.Preference_Number ,
X_Adm_Doc_Status => cp_all_records_rec.Adm_Doc_Status ,
X_Adm_Entry_Qual_Status => cp_all_records_rec.Adm_Entry_Qual_Status ,
X_Deficiency_In_Prep => cp_all_records_rec.Deficiency_In_Prep ,
X_Late_Adm_Fee_Status => cp_all_records_rec.Late_Adm_Fee_Status ,
X_Spl_Consider_Comments => cp_all_records_rec.Spl_Consider_Comments ,
X_Apply_For_Finaid => cp_all_records_rec.Apply_For_Finaid ,
X_Finaid_Apply_Date => cp_all_records_rec.Finaid_Apply_Date ,
X_Adm_Outcome_Status => igs_ad_gen_009.admp_get_sys_aos('WITHDRAWN') ,
X_Adm_Otcm_Stat_Auth_Per_Id => cp_all_records_rec.adm_otcm_status_auth_person_id,
X_Adm_Outcome_Status_Auth_Dt => cp_all_records_rec.Adm_Outcome_Status_Auth_Dt ,
X_Adm_Outcome_Status_Reason => cp_all_records_rec.Adm_Outcome_Status_Reason ,
X_Offer_Dt => cp_all_records_rec.Offer_Dt ,
X_Offer_Response_Dt => cp_all_records_rec.Offer_Response_Dt ,
X_Prpsd_Commencement_Dt => cp_all_records_rec.Prpsd_Commencement_Dt ,
X_Adm_Cndtnl_Offer_Status => cp_all_records_rec.Adm_Cndtnl_Offer_Status ,
X_Cndtnl_Offer_Satisfied_Dt => cp_all_records_rec.Cndtnl_Offer_Satisfied_Dt ,
X_Cndnl_Ofr_Must_Be_Stsfd_Ind => cp_all_records_rec.cndtnl_offer_must_be_stsfd_ind ,
X_Adm_Offer_Resp_Status => cp_all_records_rec.Adm_Offer_Resp_Status ,
X_Actual_Response_Dt => cp_all_records_rec.Actual_Response_Dt ,
X_Adm_Offer_Dfrmnt_Status => cp_all_records_rec.Adm_Offer_Dfrmnt_Status ,
X_Deferred_Adm_Cal_Type => cp_all_records_rec.Deferred_Adm_Cal_Type ,
X_Deferred_Adm_Ci_Sequence_Num => cp_all_records_rec.Deferred_Adm_Ci_Sequence_Num ,
X_Deferred_Tracking_Id => cp_all_records_rec.Deferred_Tracking_Id ,
X_Ass_Rank => cp_all_records_rec.Ass_Rank ,
X_Secondary_Ass_Rank => cp_all_records_rec.Secondary_Ass_Rank ,
X_Intr_Accept_Advice_Num => cp_all_records_rec.intrntnl_acceptance_advice_num ,
X_Ass_Tracking_Id => cp_all_records_rec.Ass_Tracking_Id ,
X_Fee_Cat => cp_all_records_rec.Fee_Cat ,
X_Hecs_Payment_Option => cp_all_records_rec.Hecs_Payment_Option ,
X_Expected_Completion_Yr => cp_all_records_rec.Expected_Completion_Yr ,
X_Expected_Completion_Perd => cp_all_records_rec.Expected_Completion_Perd ,
X_Correspondence_Cat => cp_all_records_rec.Correspondence_Cat ,
X_Enrolment_Cat => cp_all_records_rec.Enrolment_Cat ,
X_Funding_Source => cp_all_records_rec.Funding_Source ,
X_Applicant_Acptnce_Cndtn => cp_all_records_rec.Applicant_Acptnce_Cndtn ,
X_Cndtnl_Offer_Cndtn => cp_all_records_rec.Cndtnl_Offer_Cndtn ,
X_SS_APPLICATION_ID => cp_all_records_rec.SS_APPLICATION_ID ,
X_SS_PWD => cp_all_records_rec.SS_PWD ,
X_AUTHORIZED_DT => cp_all_records_rec.AUTHORIZED_DT ,
X_AUTHORIZING_PERS_ID => cp_all_records_rec.AUTHORIZING_PERS_ID ,
X_ENTRY_STATUS => cp_all_records_rec.ENTRY_STATUS ,
X_ENTRY_LEVEL => cp_all_records_rec.ENTRY_LEVEL ,
X_SCH_APL_TO_ID => cp_all_records_rec.SCH_APL_TO_ID ,
X_IDX_CALC_DATE => cp_all_records_rec.IDX_CALC_DATE ,
X_WAITLIST_STATUS => cp_all_records_rec.WAITLIST_STATUS ,
X_Attribute21 => cp_all_records_rec.Attribute21 ,
X_Attribute22 => cp_all_records_rec.Attribute22 ,
X_Attribute23 => cp_all_records_rec.Attribute23 ,
X_Attribute24 => cp_all_records_rec.Attribute24 ,
X_Attribute25 => cp_all_records_rec.Attribute25 ,
X_Attribute26 => cp_all_records_rec.Attribute26 ,
X_Attribute27 => cp_all_records_rec.Attribute27 ,
X_Attribute28 => cp_all_records_rec.Attribute28 ,
X_Attribute29 => cp_all_records_rec.Attribute29 ,
X_Attribute30 => cp_all_records_rec.Attribute30 ,
X_Attribute31 => cp_all_records_rec.Attribute31 ,
X_Attribute32 => cp_all_records_rec.Attribute32 ,
X_Attribute33 => cp_all_records_rec.Attribute33 ,
X_Attribute34 => cp_all_records_rec.Attribute34 ,
X_Attribute35 => cp_all_records_rec.Attribute35 ,
X_Attribute36 => cp_all_records_rec.Attribute36 ,
X_Attribute37 => cp_all_records_rec.Attribute37 ,
X_Attribute38 => cp_all_records_rec.Attribute38 ,
X_Attribute39 => cp_all_records_rec.Attribute39 ,
X_Attribute40 => cp_all_records_rec.Attribute40 ,
x_fut_acad_cal_type => cp_all_records_rec.future_acad_cal_type ,
x_fut_acad_ci_sequence_number => cp_all_records_rec.future_acad_ci_sequence_number ,
x_fut_adm_cal_type => cp_all_records_rec.future_adm_cal_type ,
x_fut_adm_ci_sequence_number => cp_all_records_rec.future_adm_ci_sequence_number ,
x_prev_term_adm_appl_number => cp_all_records_rec.previous_term_adm_appl_number ,
x_prev_term_sequence_number => cp_all_records_rec.previous_term_sequence_number ,
x_fut_term_adm_appl_number => cp_all_records_rec.future_term_adm_appl_number ,
x_fut_term_sequence_number => cp_all_records_rec.future_term_sequence_number ,
x_def_acad_cal_type => cp_all_records_rec.def_acad_cal_type ,
x_def_acad_ci_sequence_num => cp_all_records_rec.def_acad_ci_sequence_num ,
x_def_prev_term_adm_appl_num => cp_all_records_rec.def_prev_term_adm_appl_num ,
x_def_prev_appl_sequence_num => cp_all_records_rec.def_prev_appl_sequence_num ,
x_def_term_adm_appl_num => cp_all_records_rec.def_term_adm_appl_num ,
x_def_appl_sequence_num => cp_all_records_rec.def_appl_sequence_num,
x_appl_inst_status => cp_all_records_rec.appl_inst_status,
x_ais_reason => cp_all_records_rec.ais_reason,
x_decline_ofr_reason => cp_all_records_rec.decline_ofr_reason
);
END update_appl_inst;
update_appl_inst(c_all_records_case_1_rec);
update_appl_inst(c_all_records_case_2_rec);
update_appl_inst(c_all_records_case_3_rec);
update_appl_inst(c_all_records_case_4_rec);