[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pee.person_id
FROM IGS_PE_PERSENC_EFFCT pee
WHERE pee.person_id = p_target_person_id AND
pee.s_encmb_effect_type IN (
cst_result_blk,
cst_sus_srvc,
cst_rvk_srvc) AND
SYSDATE BETWEEN pee.pee_start_dt AND
DECODE (pee.expiry_dt,
NULL,
IGS_GE_DATE.IGSDATE('9999/12/31'),--TO_DATE('31/12/9999', 'DD/MM/YYYY'),
pee.expiry_dt);
SELECT sua.person_id
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_target_person_id AND
sua.unit_attempt_status = cst_enrolled;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.last_name = cp_previous_family_name AND
pe.birth_date = p_convert_birth_dt;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.last_name = p_convert_family_name AND
pe.birth_date = p_convert_birth_dt;
SELECT hz.party_id person_id /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
FROM hz_parties hz
WHERE hz.person_last_name = cp_previous_family_name;
SELECT hz.party_id person_id
FROM hz_parties hz
WHERE hz.person_last_name = p_convert_family_name;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.last_name = cp_previous_family_name AND
pe.birth_date = p_convert_birth_dt;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.last_name = p_convert_family_name AND
pe.birth_date = p_convert_birth_dt;
SELECT hz.party_id person_id /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
FROM hz_parties hz
WHERE hz.party_id = p_convert_id;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.person_id = p_convert_id AND
pe.birth_date = p_convert_birth_dt;
SELECT hz.party_id person_id
FROM hz_parties hz /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE hz.party_id = p_convert_id AND
hz.person_last_name = cp_previous_family_name;
SELECT hz.party_id person_id
FROM hz_parties hz /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE hz.party_id = p_convert_id AND
hz.person_last_name = p_convert_family_name;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.person_id = p_convert_id AND
pe.last_name = cp_previous_family_name AND
pe.birth_date = p_convert_birth_dt;
SELECT pe.person_id
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with HZ tables Bug 3150054 */
WHERE pe.person_id = p_convert_id AND
pe.last_name = p_convert_family_name AND
pe.birth_date = p_convert_birth_dt;
SELECT pe.person_id person_id,
pe.last_name surname,
pe.first_name given_names,
pe.birth_date birth_dt
FROM igs_pe_person_base_v pe /* Replaced IGS_PE_PERSON with igs_pe_person_base_v Bug 3150054 */
WHERE pe.person_id = p_matched_id;
SELECT sca.course_rqrmnt_complete_ind,
sca.person_id,
crv.course_cd,
crv.version_number course_version_number,
crv.course_type,
crv.title course_title,
uv.unit_cd,
sua.version_number unit_version_number,
uv.title unit_title,
IGS_EN_GEN_014.enrs_get_acad_alt_cd(sua.cal_type,sua.ci_sequence_number) acad_alternate_code,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
sua.discontinued_dt,
sua.administrative_unit_status,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.unit_attempt_status,
sua.no_assessment_ind
FROM IGS_EN_SU_ATTEMPT sua,
IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv,
IGS_PS_UNIT_VER uv
WHERE sua.person_id = p_matched_id AND
sua.unit_attempt_status IN (
cst_enrolled,
cst_completed,
cst_discontin) AND
sca.person_id = sua.person_id AND
sca.course_cd = sua.course_cd AND
uv.unit_cd = sua.unit_cd AND
uv.version_number = sua.version_number AND
crv.course_cd = sca.course_cd AND
crv.version_number = sca.version_number
ORDER BY sua.course_cd,
sua.ci_start_dt;
SELECT fos.govt_field_of_study
FROM IGS_PS_FLD_OF_STUDY fos,
IGS_PS_FIELD_STUDY cfos
WHERE cfos.course_cd = cp_course_cd AND
cfos.version_number = cp_version_number AND
cfos.major_field_ind = 'Y' AND
fos.field_of_study = cfos.field_of_study;
SELECT cty.govt_course_type
FROM IGS_PS_TYPE cty
WHERE cty.course_type = cp_course_type;
SELECT pee.s_encmb_effect_type
FROM IGS_PE_PERSENC_EFFCT pee
WHERE pee.person_id = cp_person_id AND
pee.s_encmb_effect_type IN (
cst_sus_srvc,
cst_rvk_srvc);
SELECT pce.s_encmb_effect_type
FROM IGS_PE_COURSE_EXCL pce
WHERE pce.person_id = cp_person_id AND
pce.s_encmb_effect_type IN (
cst_exc_course,
cst_sus_course) AND
pce.course_cd = cp_course_cd;
SELECT pcge.s_encmb_effect_type
FROM IGS_PE_CRS_GRP_EXCL pcge,
IGS_PS_GRP cgr,
IGS_PS_GRP_MBR cgm
WHERE pcge.person_id = cp_person_id AND
pcge.s_encmb_effect_type = cst_exc_crs_gp AND
pcge.course_group_cd = cgr.course_group_cd AND
cgr.course_group_cd = cgm.course_group_cd AND
cgm.course_cd = cp_course_cd;
SELECT cat.arts_teaching_cal_type_cd
FROM IGS_CA_TYPE cat
WHERE cat.cal_type = cp_cal_type;
SELECT cir2.sub_cal_type,
cir2.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir1,
IGS_CA_INST_REL cir2,
IGS_CA_TYPE cat1,
IGS_CA_TYPE cat2
WHERE cir1.sup_cal_type = cat1.cal_type AND
cat1.s_cal_cat = cst_academic AND
cir1.sub_cal_type = cp_teach_cal_type AND
cir1.sub_ci_sequence_number = cp_teach_ci_sequence_number AND
cir2.sub_cal_type = cat2.cal_type AND
cat2.s_cal_cat = cst_load AND
cir1.sup_cal_type = cir2.sup_cal_type AND
cir1.sup_ci_sequence_number = cir2.sup_ci_sequence_number;
SELECT di.govt_discipline_group_cd
FROM IGS_PS_DSCP di,
IGS_PS_UNIT_DSCP ud
WHERE di.discipline_group_cd = ud.discipline_group_cd AND
ud.unit_cd = cp_unit_cd AND
ud.version_number = cp_unit_version_number;
SELECT spl_sequence_number
FROM IGS_AD_APPL_LTR
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
correspondence_type = p_correspondence_type AND
sequence_number = p_sequence_number;
SELECT issue_dt
FROM IGS_CO_OU_CO_REF
WHERE person_id = p_person_id AND
correspondence_type = p_correspondence_type AND
s_other_reference_type = cst_spl_seqnum AND
other_reference = IGS_GE_NUMBER.TO_CANN(cp_spl_sequence_number);
SELECT --aca.req_for_reconsideration_ind,
acai.adm_outcome_status,
acai.adm_offer_resp_status,
acai.adm_offer_dfrmnt_status,
acai.appl_inst_status,
acai.def_term_adm_appl_num,
acai.def_appl_sequence_num
FROM --IGS_AD_PS_APPL aca,
IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number;
v_create_who IGS_AD_APPL.LAST_UPDATED_BY%TYPE DEFAULT NULL;
v_create_on IGS_AD_APPL.LAST_UPDATE_DATE%TYPE DEFAULT NULL;
SELECT aah.hist_who,
aah.hist_start_dt
FROM IGS_AD_APPL_HIST aah
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number
ORDER BY
hist_start_dt ASC;
SELECT LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM IGS_AD_APPL
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT aa.admission_cat,
aa.s_admission_process_type,
aa.acad_cal_type,
aa.acad_ci_sequence_number,
aa.adm_cal_type,
aa.adm_ci_sequence_number,
aa.appl_dt,
aa.adm_appl_status,
aa.adm_fee_status
FROM IGS_AD_APPL aa
WHERE aa.person_id = p_person_id AND
aa.admission_appl_number = p_admission_appl_number;
SELECT aa.acad_cal_type
FROM IGS_AD_APPL aa
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT ci.alternate_code, ci.start_dt
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci
WHERE cir.sup_cal_type = cp_acad_cal_type AND
cir.sub_cal_type = cp_adm_cal_type AND
cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
cir.sup_cal_type = ci.cal_type AND
cir.sup_ci_sequence_number = ci.sequence_number
ORDER BY ci.start_dt;
SELECT acai.decision_date
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd = p_nominated_course_cd AND
acai.sequence_number = p_acai_sequence_number AND
acai.adm_outcome_status IS NOT NULL
ORDER BY acai.decision_date DESC;
SELECT application_id
FROM igs_ad_appl_all
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number;
SELECT appl_fee_amt
FROM igs_ad_appl_all
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_type = 'SYS_FEE_STATUS'
AND lookup_code = p_sys_fee_status;
SELECT req.fee_amount,
cc.system_status fee_status
FROM igs_ad_app_req req,
igs_ad_code_classes cc
WHERE req.person_id = p_person_id
AND req.admission_appl_number = p_admission_appl_number
AND cc.CLASS = 'SYS_FEE_STATUS'
AND cc.CODE_ID = req.applicant_fee_status
AND EXISTS (SELECT 'x'
FROM igs_ad_code_classes
WHERE class = 'SYS_FEE_TYPE'
AND system_status = 'APPL_FEE'
AND req.applicant_fee_type = code_id)
ORDER BY req.fee_date;
p_updateable VARCHAR2 DEFAULT 'N' -- apadegal - TD001 - IGS.M.
) IS
/*******************************************************************************
Created by : Kedarnath Nag
Date created: 11 MAR 2003
Purpose:
To check whether the application details can be added/modified/deleted and
raise appropriate error incase of failure
Known limitations/enhancements and/or remarks:
Change History: (who, when, what: )
Who When What
*******************************************************************************/
l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE;
SELECT adm_appl_status
FROM igs_ad_appl
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number;
SELECT adm_outcome_status,
appl_inst_status, --arvsrini
def_term_adm_appl_num,
def_appl_sequence_num
FROM igs_ad_ps_appl_inst
WHERE person_id = cp_person_id
AND admission_appl_number= cp_admission_appl_number
AND nominated_course_cd = cp_nominated_course_cd
AND sequence_number = cp_sequence_number;
( p_updateable = 'N' OR l_offer_inst <> 'Y') -- either not in proceed phase or not offered/cond offered.
THEN
IF (p_nominated_course_cd IS NULL and p_sequence_number IS NULL)
THEN
fnd_message.set_name('IGS','IGS_AD_CANNOT_CHG_APPL_DTL');
SELECT count(*)
FROM igs_ad_ps_appl_inst
WHERE person_id = cp_person_id AND
admission_appl_number =cp_admission_appl_number AND
(( adm_offer_resp_status IN ( SELECT adm_offer_resp_status
FROM igs_ad_ofr_resp_stat
WHERE s_adm_offer_resp_status = 'ACCEPTED')) OR
( adm_offer_resp_status IN ( SELECT adm_offer_resp_status
FROM igs_ad_ofr_resp_stat
WHERE s_adm_offer_resp_status = 'DEFERRAL' ) AND
adm_offer_dfrmnt_status IN (SELECT aods.adm_offer_dfrmnt_status
FROM igs_ad_ofrdfrmt_stat aods
WHERE aods.s_adm_offer_dfrmnt_status ='CONFIRM')));
SELECT person_id, admission_appl_number, appl_fee_amt
FROM igs_ad_appl_all
WHERE application_id = cp_application_id;
SELECT 'X'
FROM igs_ad_app_req
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number;
SELECT ROWID, acai.*
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd= p_nominated_course_cd AND
acai.sequence_number = p_acai_sequence_number;
IGS_AD_PS_APPL_Inst_Pkg.update_row(
X_ROWID => acai_rec.ROWID,
x_PERSON_ID => acai_rec.PERSON_ID,
x_ADMISSION_APPL_NUMBER => acai_rec.ADMISSION_APPL_NUMBER,
x_NOMINATED_COURSE_CD => acai_rec.NOMINATED_COURSE_CD,
x_SEQUENCE_NUMBER => acai_rec.SEQUENCE_NUMBER,
x_PREDICTED_GPA => acai_rec.PREDICTED_GPA,
x_ACADEMIC_INDEX => acai_rec.ACADEMIC_INDEX,
x_ADM_CAL_TYPE => acai_rec.ADM_CAL_TYPE,
x_APP_FILE_LOCATION => acai_rec.APP_FILE_LOCATION,
x_ADM_CI_SEQUENCE_NUMBER => acai_rec.ADM_CI_SEQUENCE_NUMBER,
x_COURSE_CD => acai_rec.COURSE_CD,
x_APP_SOURCE_ID => acai_rec.APP_SOURCE_ID,
x_CRV_VERSION_NUMBER => acai_rec.CRV_VERSION_NUMBER,
x_WAITLIST_RANK => acai_rec.WAITLIST_RANK,
x_LOCATION_CD => acai_rec.LOCATION_CD,
x_ATTENT_OTHER_INST_CD => acai_rec.ATTENT_OTHER_INST_CD,
x_ATTENDANCE_MODE => acai_rec.ATTENDANCE_MODE,
x_EDU_GOAL_PRIOR_ENROLL_ID => acai_rec.EDU_GOAL_PRIOR_ENROLL_ID,
x_ATTENDANCE_TYPE => acai_rec.ATTENDANCE_TYPE,
x_DECISION_MAKE_ID => acai_rec.DECISION_MAKE_ID,
x_UNIT_SET_CD => acai_rec.UNIT_SET_CD,
x_DECISION_DATE => acai_rec.DECISION_DATE,
x_ATTRIBUTE_CATEGORY => acai_rec.ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1 => acai_rec.ATTRIBUTE1,
x_ATTRIBUTE2 => acai_rec.ATTRIBUTE2,
x_ATTRIBUTE3 => acai_rec.ATTRIBUTE3,
x_ATTRIBUTE4 => acai_rec.ATTRIBUTE4,
x_ATTRIBUTE5 => acai_rec.ATTRIBUTE5,
x_ATTRIBUTE6 => acai_rec.ATTRIBUTE6,
x_ATTRIBUTE7 => acai_rec.ATTRIBUTE7,
x_ATTRIBUTE8 => acai_rec.ATTRIBUTE8,
x_ATTRIBUTE9 => acai_rec.ATTRIBUTE9,
x_ATTRIBUTE10 => acai_rec.ATTRIBUTE10,
x_ATTRIBUTE11 => acai_rec.ATTRIBUTE11,
x_ATTRIBUTE12 => acai_rec.ATTRIBUTE12,
x_ATTRIBUTE13 => acai_rec.ATTRIBUTE13,
x_ATTRIBUTE14 => acai_rec.ATTRIBUTE14,
x_ATTRIBUTE15 => acai_rec.ATTRIBUTE15,
x_ATTRIBUTE16 => acai_rec.ATTRIBUTE16,
x_ATTRIBUTE17 => acai_rec.ATTRIBUTE17,
x_ATTRIBUTE18 => acai_rec.ATTRIBUTE18,
x_ATTRIBUTE19 => acai_rec.ATTRIBUTE19,
x_ATTRIBUTE20 => acai_rec.ATTRIBUTE20,
x_DECISION_REASON_ID => acai_rec.DECISION_REASON_ID,
x_US_VERSION_NUMBER => acai_rec.US_VERSION_NUMBER,
x_DECISION_NOTES => acai_rec.DECISION_NOTES,
x_PENDING_REASON_ID => acai_rec.PENDING_REASON_ID,
x_PREFERENCE_NUMBER => acai_rec.PREFERENCE_NUMBER,
x_ADM_DOC_STATUS => acai_rec.ADM_DOC_STATUS,
x_ADM_ENTRY_QUAL_STATUS => acai_rec.ADM_ENTRY_QUAL_STATUS,
x_DEFICIENCY_IN_PREP => acai_rec.DEFICIENCY_IN_PREP,
x_LATE_ADM_FEE_STATUS => acai_rec.LATE_ADM_FEE_STATUS,
x_SPL_CONSIDER_COMMENTS => acai_rec.SPL_CONSIDER_COMMENTS,
x_APPLY_FOR_FINAID => acai_rec.APPLY_FOR_FINAID,
x_FINAID_APPLY_DATE => acai_rec.FINAID_APPLY_DATE,
x_ADM_OUTCOME_STATUS => acai_rec.ADM_OUTCOME_STATUS,
x_adm_otcm_stat_auth_per_id => acai_rec.adm_otcm_status_auth_person_id,
x_ADM_OUTCOME_STATUS_AUTH_DT => acai_rec.ADM_OUTCOME_STATUS_AUTH_DT,
x_ADM_OUTCOME_STATUS_REASON => acai_rec.ADM_OUTCOME_STATUS_REASON,
x_OFFER_DT => acai_rec.OFFER_DT,
x_OFFER_RESPONSE_DT => acai_rec.OFFER_RESPONSE_DT,
x_PRPSD_COMMENCEMENT_DT => acai_rec.PRPSD_COMMENCEMENT_DT,
x_ADM_CNDTNL_OFFER_STATUS => acai_rec.ADM_CNDTNL_OFFER_STATUS,
x_CNDTNL_OFFER_SATISFIED_DT => acai_rec.CNDTNL_OFFER_SATISFIED_DT,
x_cndnl_ofr_must_be_stsfd_ind => acai_rec.cndtnl_offer_must_be_stsfd_ind,
x_ADM_OFFER_RESP_STATUS => acai_rec.ADM_OFFER_RESP_STATUS,
x_ACTUAL_RESPONSE_DT => acai_rec.ACTUAL_RESPONSE_DT,
x_ADM_OFFER_DFRMNT_STATUS => acai_rec.ADM_OFFER_DFRMNT_STATUS,
x_DEFERRED_ADM_CAL_TYPE => acai_rec.DEFERRED_ADM_CAL_TYPE,
x_DEFERRED_ADM_CI_SEQUENCE_NUM => acai_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
x_DEFERRED_TRACKING_ID => acai_rec.DEFERRED_TRACKING_ID,
x_ASS_RANK => acai_rec.ASS_RANK,
x_SECONDARY_ASS_RANK => acai_rec.SECONDARY_ASS_RANK,
x_intr_accept_advice_num => acai_rec.intrntnl_acceptance_advice_num,
x_ASS_TRACKING_ID => acai_rec.ASS_TRACKING_ID,
x_FEE_CAT => acai_rec.FEE_CAT,
x_HECS_PAYMENT_OPTION => acai_rec.HECS_PAYMENT_OPTION,
x_EXPECTED_COMPLETION_YR => acai_rec.EXPECTED_COMPLETION_YR,
x_EXPECTED_COMPLETION_PERD => acai_rec.EXPECTED_COMPLETION_PERD,
x_CORRESPONDENCE_CAT => acai_rec.CORRESPONDENCE_CAT,
x_ENROLMENT_CAT => acai_rec.ENROLMENT_CAT,
x_FUNDING_SOURCE => acai_rec.FUNDING_SOURCE,
x_APPLICANT_ACPTNCE_CNDTN => acai_rec.APPLICANT_ACPTNCE_CNDTN,
x_CNDTNL_OFFER_CNDTN => acai_rec.CNDTNL_OFFER_CNDTN,
X_MODE => 'R',
X_SS_APPLICATION_ID => acai_rec.SS_APPLICATION_ID,
X_SS_PWD => acai_rec.SS_PWD,
X_AUTHORIZED_DT => acai_rec.AUTHORIZED_DT,
X_AUTHORIZING_PERS_ID => acai_rec.AUTHORIZING_PERS_ID,
x_entry_status => acai_rec.entry_status,
x_entry_level => acai_rec.entry_level,
x_sch_apl_to_id => acai_rec.sch_apl_to_id,
x_idx_calc_date => acai_rec.idx_calc_date,
x_waitlist_status => acai_rec.waitlist_status,
x_ATTRIBUTE21 => acai_rec.ATTRIBUTE21,
x_ATTRIBUTE22 => acai_rec.ATTRIBUTE22,
x_ATTRIBUTE23 => acai_rec.ATTRIBUTE23,
x_ATTRIBUTE24 => acai_rec.ATTRIBUTE24,
x_ATTRIBUTE25 => acai_rec.ATTRIBUTE25,
x_ATTRIBUTE26 => acai_rec.ATTRIBUTE26,
x_ATTRIBUTE27 => acai_rec.ATTRIBUTE27,
x_ATTRIBUTE28 => acai_rec.ATTRIBUTE28,
x_ATTRIBUTE29 => acai_rec.ATTRIBUTE29,
x_ATTRIBUTE30 => acai_rec.ATTRIBUTE30,
x_ATTRIBUTE31 => acai_rec.ATTRIBUTE31,
x_ATTRIBUTE32 => acai_rec.ATTRIBUTE32,
x_ATTRIBUTE33 => acai_rec.ATTRIBUTE33,
x_ATTRIBUTE34 => acai_rec.ATTRIBUTE34,
x_ATTRIBUTE35 => acai_rec.ATTRIBUTE35,
x_ATTRIBUTE36 => acai_rec.ATTRIBUTE36,
x_ATTRIBUTE37 => acai_rec.ATTRIBUTE37,
x_ATTRIBUTE38 => acai_rec.ATTRIBUTE38,
x_ATTRIBUTE39 => acai_rec.ATTRIBUTE39,
x_ATTRIBUTE40 => acai_rec.ATTRIBUTE40,
x_fut_acad_cal_type => acai_rec.future_acad_cal_type,
x_fut_acad_ci_sequence_number => acai_rec.future_acad_ci_sequence_number,
x_fut_adm_cal_type => acai_rec.future_adm_cal_type,
x_fut_adm_ci_sequence_number => acai_rec.future_adm_ci_sequence_number,
x_prev_term_adm_appl_number => acai_rec.previous_term_adm_appl_number,
x_prev_term_sequence_number => acai_rec.previous_term_sequence_number,
x_fut_term_adm_appl_number => acai_rec.future_term_adm_appl_number,
x_fut_term_sequence_number => acai_rec.future_term_sequence_number,
x_def_acad_cal_type => acai_rec.def_acad_cal_type,
x_def_acad_ci_sequence_num => acai_rec.def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num => acai_rec.def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num => acai_rec.def_prev_appl_sequence_num,
x_def_term_adm_appl_num => acai_rec.def_term_adm_appl_num,
x_def_appl_sequence_num => acai_rec.def_appl_sequence_num,
x_appl_inst_status => NULL,
x_ais_reason => NULL,
x_decline_ofr_reason => acai_rec.decline_ofr_reason
);
SELECT acai.adm_outcome_status outcome_status
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = cp_person_id AND
acai.admission_appl_number = cp_admission_appl_number AND
acai.nominated_course_cd = NVL(cp_nominated_course_cd,acai.nominated_course_cd) AND
acai.sequence_number= NVL(cp_sequence_number,acai.sequence_number) ;
SELECT acai.adm_outcome_status,
acai.adm_offer_resp_status,
acai.adm_offer_dfrmnt_status,
acai.def_term_adm_appl_num,
acai.def_appl_sequence_num
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd = p_nominated_course_cd AND
acai.sequence_number = p_sequence_number;
SELECT acaiv.adm_cal_type,
acaiv.adm_ci_sequence_number,
acaiv.course_cd,
acaiv.crv_version_number,
acaiv.location_cd,
acaiv.attendance_mode,
acaiv.attendance_type ,
acaiv.future_term_adm_appl_number,
acaiv.future_term_sequence_number,
acaiv.def_term_adm_appl_num,
acaiv.def_appl_sequence_num
FROM igs_ad_ps_appl_inst acaiv
WHERE acaiv.person_id = p_person_id AND
acaiv.admission_appl_number = p_admission_appl_number AND
acaiv.nominated_course_cd = p_nominated_course_cd AND
acaiv.sequence_number = p_sequence_number;
SELECT 1 CNT
FROM igs_ad_ps_appl_inst acaiv
WHERE acaiv.person_id = p_person_id AND
acaiv.admission_appl_number = p_admission_appl_number AND
acaiv.nominated_course_cd = p_nominated_course_cd AND
acaiv.sequence_number <> p_sequence_number AND
IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status) IN ('OFFER','COND-OFFER')
AND ( IGS_AD_GEN_008.ADMP_GET_SAORS(acaiv.adm_offer_resp_status) IN (cst_accepted,cst_rejected,cst_lapsed)
OR
(IGS_AD_GEN_008.ADMP_GET_SAORS(acaiv.adm_offer_resp_status) IN (cst_deferral)
AND
IGS_AD_GEN_008.ADMP_GET_SAODS(acaiv.adm_offer_dfrmnt_status) IN (cst_confirm)
)
)
AND rownum <= 1 ;
SELECT aa.admission_cat,
aa.s_admission_process_type
FROM IGS_AD_APPL aa
WHERE aa.person_id = p_person_id AND
aa.admission_appl_number = p_admission_appl_number;
SELECT '1'
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_cat = cp_admission_cat AND
s_admission_process_type = cp_s_admission_process_type AND
s_admission_step_type = 'RECONSIDER' AND
step_group_type <> 'TRACK' ;
3. Once the update is done, corresponding business event (for outcome/decision change) will be raised.
4. This procedure is invoked from the
a) IGSAD092.pld ( Outcome Form) and
b) IGSPAPPB.pls ( Public API - used in Decision import process) and
c) EnterDecisionDetailsEOImpl.java ( Enter deicsions page - Self Service)
Known limitations/enhancements/remarks:
-
Change History: (who, when, what: NO CREATION RECORDS HERE!)
Who When What
*******************************************************************************/
CURSOR c_aca IS
SELECT rowid,acai.*
FROM IGS_AD_PS_APPL_INST_ALL acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd = p_nominated_course_cd AND
acai.future_term_adm_appl_number IS NULL AND
acai.future_term_sequence_number IS NULL AND
acai.def_term_adm_appl_num IS NULL AND
acai.def_appl_sequence_num IS NULL AND
IGS_AD_GEN_008.ADMP_GET_SAOS(acai.ADM_OUTCOME_STATUS) <> 'PENDING'
FOR UPDATE NOWAIT;
SELECT rowid , IGS_AD_PS_APPL.*
FROM IGS_AD_PS_APPL
WHERE person_id = CUR_person_id AND
admission_appl_number = CUR_admission_appl_number AND
nominated_course_cd = CUR_nominated_course_cd;
SELECT pti.rowid,pti.*
FROM igs_pe_typ_instances_all pti
WHERE pti.person_type_code IN (SELECT pt.person_type_code FROM Igs_pe_person_types pt WHERE pt.system_type = 'APPLICANT')
AND pti.person_id = cp_person_id
AND pti.admission_appl_number = cp_admission_appl_number
AND pti.nominated_course_cd = cp_nominated_course_cd
AND pti.end_date IS NOT NULL
AND pti.end_method = 'CREATE_STUDENT'
FOR UPDATE NOWAIT;
igs_pe_typ_instances_pkg.update_row(
X_ROWID => c_appl_rec.rowid,
X_PERSON_ID => c_appl_rec.PERSON_ID,
X_COURSE_CD => c_appl_rec.COURSE_CD,
X_TYPE_INSTANCE_ID => c_appl_rec.TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE => c_appl_rec.PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER => c_appl_rec.CC_VERSION_NUMBER,
X_FUNNEL_STATUS => c_appl_rec.FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER => c_appl_rec.ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD => c_appl_rec.NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER => c_appl_rec.NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER => c_appl_rec.SEQUENCE_NUMBER,
X_START_DATE => c_appl_rec.START_DATE,
X_END_DATE => NULL, -- nullified this field
X_CREATE_METHOD => c_appl_rec.CREATE_METHOD,
X_ENDED_BY => NULL, -- nullified this field
X_END_METHOD => NULL, -- nullified this field
X_MODE => 'R',
X_EMPLMNT_CATEGORY_CODE => c_appl_rec.EMPLMNT_CATEGORY_CODE);
IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW (X_ROWID => c_aca_rec.ROWID ,
x_PERSON_ID => c_aca_rec.PERSON_ID ,
x_ADMISSION_APPL_NUMBER => c_aca_rec.ADMISSION_APPL_NUMBER ,
x_NOMINATED_COURSE_CD => c_aca_rec.NOMINATED_COURSE_CD ,
x_SEQUENCE_NUMBER => c_aca_rec.SEQUENCE_NUMBER ,
x_PREDICTED_GPA => c_aca_rec.PREDICTED_GPA ,
x_ACADEMIC_INDEX => c_aca_rec.ACADEMIC_INDEX,
x_ADM_CAL_TYPE => c_aca_rec.ADM_CAL_TYPE,
x_APP_FILE_LOCATION => c_aca_rec.APP_FILE_LOCATION,
x_ADM_CI_SEQUENCE_NUMBER => c_aca_rec.ADM_CI_SEQUENCE_NUMBER,
x_COURSE_CD => c_aca_rec.COURSE_CD,
x_APP_SOURCE_ID => c_aca_rec.APP_SOURCE_ID ,
x_CRV_VERSION_NUMBER => c_aca_rec.CRV_VERSION_NUMBER ,
x_WAITLIST_RANK => NULL,
x_LOCATION_CD => c_aca_rec.LOCATION_CD,
x_ATTENT_OTHER_INST_CD => NULL,
x_ATTENDANCE_MODE => c_aca_rec.ATTENDANCE_MODE,
x_EDU_GOAL_PRIOR_ENROLL_ID => c_aca_rec.EDU_GOAL_PRIOR_ENROLL_ID,
x_ATTENDANCE_TYPE => c_aca_rec.ATTENDANCE_TYPE,
x_DECISION_MAKE_ID => NULL,
x_UNIT_SET_CD => c_aca_rec.UNIT_SET_CD,
x_DECISION_DATE => NULL,
x_ATTRIBUTE_CATEGORY => c_aca_rec.ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1 => c_aca_rec.ATTRIBUTE1,
x_ATTRIBUTE2 => c_aca_rec.ATTRIBUTE2,
x_ATTRIBUTE3 => c_aca_rec.ATTRIBUTE3,
x_ATTRIBUTE4 => c_aca_rec.ATTRIBUTE4,
x_ATTRIBUTE5 => c_aca_rec.ATTRIBUTE5,
x_ATTRIBUTE6 => c_aca_rec.ATTRIBUTE6,
x_ATTRIBUTE7 => c_aca_rec.ATTRIBUTE7,
x_ATTRIBUTE8 => c_aca_rec.ATTRIBUTE8,
x_ATTRIBUTE9 => c_aca_rec.ATTRIBUTE9,
x_ATTRIBUTE10 => c_aca_rec.ATTRIBUTE10,
x_ATTRIBUTE11 => c_aca_rec.ATTRIBUTE11,
x_ATTRIBUTE12 => c_aca_rec.ATTRIBUTE12,
x_ATTRIBUTE13 => c_aca_rec.ATTRIBUTE13,
x_ATTRIBUTE14 => c_aca_rec.ATTRIBUTE14,
x_ATTRIBUTE15 => c_aca_rec.ATTRIBUTE15,
x_ATTRIBUTE16 => c_aca_rec.ATTRIBUTE16,
x_ATTRIBUTE17 => c_aca_rec.ATTRIBUTE17,
x_ATTRIBUTE18 => c_aca_rec.ATTRIBUTE18,
x_ATTRIBUTE19 => c_aca_rec.ATTRIBUTE19,
x_ATTRIBUTE20 => c_aca_rec.ATTRIBUTE20,
x_DECISION_REASON_ID => NULL,
x_US_VERSION_NUMBER => c_aca_rec.US_VERSION_NUMBER,
x_DECISION_NOTES => c_aca_rec.DECISION_NOTES,
x_PENDING_REASON_ID => c_aca_rec.PENDING_REASON_ID,
x_PREFERENCE_NUMBER => c_aca_rec.PREFERENCE_NUMBER,
x_ADM_DOC_STATUS => c_aca_rec.ADM_DOC_STATUS,
x_ADM_ENTRY_QUAL_STATUS => c_aca_rec.ADM_ENTRY_QUAL_STATUS,
x_DEFICIENCY_IN_PREP => c_aca_rec.DEFICIENCY_IN_PREP,
x_LATE_ADM_FEE_STATUS => c_aca_rec.LATE_ADM_FEE_STATUS,
x_SPL_CONSIDER_COMMENTS => c_aca_rec.SPL_CONSIDER_COMMENTS,
x_APPLY_FOR_FINAID => c_aca_rec.APPLY_FOR_FINAID,
x_FINAID_APPLY_DATE => c_aca_rec.FINAID_APPLY_DATE,
x_ADM_OUTCOME_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('PENDING'),
x_adm_otcm_stat_auth_per_id => c_aca_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
x_ADM_OUTCOME_STATUS_AUTH_DT => c_aca_rec.ADM_OUTCOME_STATUS_AUTH_DT,
x_ADM_OUTCOME_STATUS_REASON => c_aca_rec.ADM_OUTCOME_STATUS_REASON ,
x_OFFER_DT => NULL,
x_OFFER_RESPONSE_DT => NULL,
x_PRPSD_COMMENCEMENT_DT => NULL,
x_ADM_CNDTNL_OFFER_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_ACOS('NOT-APPLIC'),
x_CNDTNL_OFFER_SATISFIED_DT => NULL,
x_cndnl_ofr_must_be_stsfd_ind => NULL,
x_ADM_OFFER_RESP_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AORS('NOT-APPLIC'),
x_ACTUAL_RESPONSE_DT => NULL,
x_ADM_OFFER_DFRMNT_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AODS('NOT-APPLIC'),
x_DEFERRED_ADM_CAL_TYPE => NULL,
x_DEFERRED_ADM_CI_SEQUENCE_NUM => NULL,
x_DEFERRED_TRACKING_ID => c_aca_rec.DEFERRED_TRACKING_ID,
x_ASS_RANK => c_aca_rec.ASS_RANK,
x_SECONDARY_ASS_RANK => c_aca_rec.SECONDARY_ASS_RANK,
x_intr_accept_advice_num => c_aca_rec.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
x_ASS_TRACKING_ID => c_aca_rec.ASS_TRACKING_ID ,
x_FEE_CAT => c_aca_rec.FEE_CAT,
x_HECS_PAYMENT_OPTION => c_aca_rec.HECS_PAYMENT_OPTION,
x_EXPECTED_COMPLETION_YR => c_aca_rec.EXPECTED_COMPLETION_YR,
x_EXPECTED_COMPLETION_PERD => c_aca_rec.EXPECTED_COMPLETION_PERD,
x_CORRESPONDENCE_CAT => c_aca_rec.CORRESPONDENCE_CAT,
x_ENROLMENT_CAT => c_aca_rec.ENROLMENT_CAT,
x_FUNDING_SOURCE => c_aca_rec.FUNDING_SOURCE,
x_APPLICANT_ACPTNCE_CNDTN => NULL ,
x_CNDTNL_OFFER_CNDTN => NULL,
X_MODE => 'S',
X_SS_APPLICATION_ID => c_aca_rec.SS_APPLICATION_ID,
X_SS_PWD => c_aca_rec.SS_PWD,
X_AUTHORIZED_DT => c_aca_rec.AUTHORIZED_DT,
X_AUTHORIZING_PERS_ID => c_aca_rec.AUTHORIZING_PERS_ID,
x_entry_status => c_aca_rec.entry_status,
x_entry_level => c_aca_rec.entry_level,
x_sch_apl_to_id => c_aca_rec.sch_apl_to_id,
x_idx_calc_date => c_aca_rec.IDX_CALC_DATE,
x_waitlist_status => NULL,
x_ATTRIBUTE21 => c_aca_rec.ATTRIBUTE21,
x_ATTRIBUTE22 => c_aca_rec.ATTRIBUTE22,
x_ATTRIBUTE23 => c_aca_rec.ATTRIBUTE23,
x_ATTRIBUTE24 => c_aca_rec.ATTRIBUTE24,
x_ATTRIBUTE25 => c_aca_rec.ATTRIBUTE25,
x_ATTRIBUTE26 => c_aca_rec.ATTRIBUTE26,
x_ATTRIBUTE27 => c_aca_rec.ATTRIBUTE27,
x_ATTRIBUTE28 => c_aca_rec.ATTRIBUTE28,
x_ATTRIBUTE29 => c_aca_rec.ATTRIBUTE29,
x_ATTRIBUTE30 => c_aca_rec.ATTRIBUTE30,
x_ATTRIBUTE31 => c_aca_rec.ATTRIBUTE31,
x_ATTRIBUTE32 => c_aca_rec.ATTRIBUTE32,
x_ATTRIBUTE33 => c_aca_rec.ATTRIBUTE33,
x_ATTRIBUTE34 => c_aca_rec.ATTRIBUTE34,
x_ATTRIBUTE35 => c_aca_rec.ATTRIBUTE35,
x_ATTRIBUTE36 => c_aca_rec.ATTRIBUTE36,
x_ATTRIBUTE37 => c_aca_rec.ATTRIBUTE37,
x_ATTRIBUTE38 => c_aca_rec.ATTRIBUTE38,
x_ATTRIBUTE39 => c_aca_rec.ATTRIBUTE39,
x_ATTRIBUTE40 => c_aca_rec.ATTRIBUTE40,
x_fut_acad_cal_type => NULL,
x_fut_acad_ci_sequence_number => NULL,
x_fut_adm_cal_type => NULL,
x_fut_adm_ci_sequence_number => NULL,
x_prev_term_adm_appl_number => c_aca_rec.previous_term_adm_appl_number,
x_prev_term_sequence_number => c_aca_rec.previous_term_sequence_number,
x_fut_term_adm_appl_number => c_aca_rec.future_term_adm_appl_number,
x_fut_term_sequence_number => c_aca_rec.future_term_sequence_number,
x_def_acad_cal_type => NULL,
x_def_acad_ci_sequence_num => NULL,
x_def_prev_term_adm_appl_num => c_aca_rec.def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num => c_aca_rec.def_prev_appl_sequence_num,
x_def_term_adm_appl_num => c_aca_rec.def_term_adm_appl_num,
x_def_appl_sequence_num => c_aca_rec.def_appl_sequence_num,
x_appl_inst_status => c_aca_rec.appl_inst_status,
x_ais_reason => c_aca_rec.ais_reason,
x_decline_ofr_reason => NULL
);
SELECT rowid , IGS_AD_PS_APPL.*
FROM IGS_AD_PS_APPL
WHERE person_id = CUR_person_id AND
admission_appl_number = CUR_admission_appl_number AND
nominated_course_cd = CUR_nominated_course_cd;
IGS_AD_PS_APPL_PKG.UPDATE_ROW (
X_ROWID => cur_ad_ps_appl_rec.ROWID ,
X_PERSON_ID => cur_ad_ps_appl_rec.PERSON_ID ,
X_ADMISSION_APPL_NUMBER => cur_ad_ps_appl_rec.ADMISSION_APPL_NUMBER ,
X_NOMINATED_COURSE_CD => cur_ad_ps_appl_rec.NOMINATED_COURSE_CD ,
X_TRANSFER_COURSE_CD => cur_ad_ps_appl_rec.transfer_course_cd,
X_BASIS_FOR_ADMISSION_TYPE => cur_ad_ps_appl_rec.basis_for_admission_type,
X_ADMISSION_CD => cur_ad_ps_appl_rec.admission_cd,
X_COURSE_RANK_SET => cur_ad_ps_appl_rec.course_rank_set,
X_COURSE_RANK_SCHEDULE => cur_ad_ps_appl_rec.course_rank_schedule,
X_REQ_FOR_RECONSIDERATION_IND => 'Y',
X_REQ_FOR_ADV_STANDING_IND => cur_ad_ps_appl_rec.req_for_adv_standing_ind ,
X_MODE => 'S' ) ;
p_updateable VARCHAR2 DEFAULT 'N' -- apadegal - TD001 - IGS.M.
)
RETURN VARCHAR2
IS
l_status VARCHAR2(1):= 'N';
p_updateable);
SELECT rowid,acai.*
FROM igs_ad_ps_appl_inst_all acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd = p_nominated_course_cd AND
acai.future_term_adm_appl_number IS NULL AND
acai.future_term_sequence_number IS NULL AND
acai.def_term_adm_appl_num IS NULL AND
acai.def_appl_sequence_num IS NULL AND
NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(acai.APPL_INST_STATUS),'NULL') <> 'WITHDRAWN' AND
IGS_AD_GEN_008.ADMP_GET_SAOS(acai.ADM_OUTCOME_STATUS) = 'PENDING'
FOR UPDATE NOWAIT;
SELECT last_update_date
FROM igs_ad_ps_appl_inst_all acinst
WHERE acinst.person_id = cp_person_id AND
acinst.admission_appl_number = cp_admission_appl_number AND
acinst.nominated_course_cd = cp_nominated_course_cd AND
acinst.sequence_number = cp_sequence_number;
cp_old_update_on DATE)
IS
SELECT 'x'
FROM IGS_AD_PS_APLINSTHST ahist
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number
AND nominated_course_cd = cp_nominated_course_cd
AND sequence_number = cp_sequence_number
AND hist_start_dt = cp_old_update_on;
SELECT req_for_reconsideration_ind
FROM igs_ad_ps_appl
WHERE person_id = cp_person_id and
admission_appl_number = cp_admission_appl_number and
nominated_course_cd = cp_nominated_course_cd;
IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW
(
X_Mode => 'S',
X_RowId => c_inst_rec.rowid,
X_Person_Id => c_inst_rec.Person_Id,
X_Admission_Appl_Number => c_inst_rec.Admission_Appl_Number,
X_Nominated_Course_Cd => c_inst_rec.Nominated_Course_Cd,
X_Sequence_Number => c_inst_rec.Sequence_Number,
X_Predicted_Gpa => c_inst_rec.Predicted_Gpa,
X_Academic_Index => c_inst_rec.Academic_Index,
X_Adm_Cal_Type => c_inst_rec.Adm_Cal_Type,
X_App_File_Location => c_inst_rec.App_File_Location,
X_Adm_Ci_Sequence_Number => c_inst_rec.Adm_Ci_Sequence_Number,
X_Course_Cd => c_inst_rec.Course_Cd,
X_App_Source_Id => c_inst_rec.App_Source_Id,
X_Crv_Version_Number => c_inst_rec.Crv_Version_Number,
X_Waitlist_Rank => c_inst_rec.Waitlist_Rank,
X_Location_Cd => c_inst_rec.Location_Cd,
X_Attent_Other_Inst_Cd => c_inst_rec.Attent_Other_Inst_Cd,
X_Attendance_Mode => c_inst_rec.Attendance_Mode,
X_Edu_Goal_Prior_Enroll_Id => c_inst_rec.Edu_Goal_Prior_Enroll_Id,
X_Attendance_Type => c_inst_rec.Attendance_Type,
X_Decision_Make_Id => c_inst_rec.Decision_Make_Id,
X_Unit_Set_Cd => c_inst_rec.Unit_Set_Cd,
X_Decision_Date => c_inst_rec.Decision_Date,
X_Attribute_Category => c_inst_rec.Attribute_Category,
X_Attribute1 => c_inst_rec.Attribute1,
X_Attribute2 => c_inst_rec.Attribute2,
X_Attribute3 => c_inst_rec.Attribute3,
X_Attribute4 => c_inst_rec.Attribute4,
X_Attribute5 => c_inst_rec.Attribute5,
X_Attribute6 => c_inst_rec.Attribute6,
X_Attribute7 => c_inst_rec.Attribute7,
X_Attribute8 => c_inst_rec.Attribute8,
X_Attribute9 => c_inst_rec.Attribute9,
X_Attribute10 => c_inst_rec.Attribute10,
X_Attribute11 => c_inst_rec.Attribute11,
X_Attribute12 => c_inst_rec.Attribute12,
X_Attribute13 => c_inst_rec.Attribute13,
X_Attribute14 => c_inst_rec.Attribute14,
X_Attribute15 => c_inst_rec.Attribute15,
X_Attribute16 => c_inst_rec.Attribute16,
X_Attribute17 => c_inst_rec.Attribute17,
X_Attribute18 => c_inst_rec.Attribute18,
X_Attribute19 => c_inst_rec.Attribute19,
X_Attribute20 => c_inst_rec.Attribute20,
X_Decision_Reason_Id => c_inst_rec.Decision_Reason_Id,
X_Us_Version_Number => c_inst_rec.Us_Version_Number,
X_Decision_Notes => c_inst_rec.Decision_Notes,
X_Pending_Reason_Id => c_inst_rec.Pending_Reason_Id,
X_Preference_Number => c_inst_rec.Preference_Number,
X_Adm_Doc_Status => c_inst_rec.Adm_Doc_Status,
X_Adm_Entry_Qual_Status => c_inst_rec.Adm_Entry_Qual_Status,
X_Deficiency_In_Prep => c_inst_rec.Deficiency_In_Prep,
X_Late_Adm_Fee_Status => c_inst_rec.Late_Adm_Fee_Status,
X_Spl_Consider_Comments => c_inst_rec.Spl_Consider_Comments,
X_Apply_For_Finaid => c_inst_rec.Apply_For_Finaid,
X_Finaid_Apply_Date => c_inst_rec.Finaid_Apply_Date,
X_Adm_Outcome_Status => c_inst_rec.Adm_Outcome_Status,
X_Adm_Otcm_Stat_Auth_Per_Id => c_inst_rec.Adm_Otcm_Status_Auth_Person_Id,
X_Adm_Outcome_Status_Auth_Dt => c_inst_rec.Adm_Outcome_Status_Auth_Dt,
X_Adm_Outcome_Status_Reason => c_inst_rec.Adm_Outcome_Status_Reason,
X_Offer_Dt => c_inst_rec.Offer_Dt,
X_Offer_Response_Dt => c_inst_rec.Offer_Response_Dt,
X_Prpsd_Commencement_Dt => c_inst_rec.Prpsd_Commencement_Dt,
X_Adm_Cndtnl_Offer_Status => c_inst_rec.Adm_Cndtnl_Offer_Status,
X_Cndtnl_Offer_Satisfied_Dt => c_inst_rec.Cndtnl_Offer_Satisfied_Dt,
X_Cndnl_Ofr_Must_Be_Stsfd_Ind => c_inst_rec.Cndtnl_Offer_Must_Be_Stsfd_Ind,
X_Adm_Offer_Resp_Status => c_inst_rec.Adm_Offer_Resp_Status,
X_Actual_Response_Dt => c_inst_rec.Actual_Response_Dt,
X_Adm_Offer_Dfrmnt_Status => c_inst_rec.Adm_Offer_Dfrmnt_Status,
X_Deferred_Adm_Cal_Type => c_inst_rec.Deferred_Adm_Cal_Type,
X_Deferred_Adm_Ci_Sequence_Num => c_inst_rec.Deferred_Adm_Ci_Sequence_Num,
X_Deferred_Tracking_Id => c_inst_rec.Deferred_Tracking_Id,
X_Ass_Rank => c_inst_rec.Ass_Rank,
X_Secondary_Ass_Rank => c_inst_rec.Secondary_Ass_Rank,
X_Intr_Accept_Advice_Num => c_inst_rec.Intrntnl_Acceptance_Advice_Num,
X_Ass_Tracking_Id => c_inst_rec.Ass_Tracking_Id,
X_Fee_Cat => c_inst_rec.Fee_Cat,
X_Hecs_Payment_Option => c_inst_rec.Hecs_Payment_Option,
X_Expected_Completion_Yr => c_inst_rec.Expected_Completion_Yr,
X_Expected_Completion_Perd => c_inst_rec.Expected_Completion_Perd,
X_Correspondence_Cat => c_inst_rec.Correspondence_Cat,
X_Enrolment_Cat => c_inst_rec.Enrolment_Cat,
X_Funding_Source => c_inst_rec.Funding_Source,
X_Applicant_Acptnce_Cndtn => c_inst_rec.Applicant_Acptnce_Cndtn,
X_Cndtnl_Offer_Cndtn => c_inst_rec.Cndtnl_Offer_Cndtn,
X_SS_APPLICATION_ID => c_inst_rec.SS_APPLICATION_ID,
X_SS_PWD => c_inst_rec.SS_PWD,
X_AUTHORIZED_DT => c_inst_rec.Authorized_dt,
X_AUTHORIZING_PERS_ID => c_inst_rec.authorizing_pers_id,
X_ENTRY_STATUS => c_inst_rec.entry_status,
X_ENTRY_LEVEL => c_inst_rec.entry_level,
X_SCH_APL_TO_ID => c_inst_rec.sch_apl_to_id,
X_IDX_CALC_DATE => c_inst_rec.IDX_CALC_DATE,
X_WAITLIST_STATUS => c_inst_rec.Waitlist_Status,
X_Attribute21 => c_inst_rec.Attribute21,
X_Attribute22 => c_inst_rec.Attribute22,
X_Attribute23 => c_inst_rec.Attribute23,
X_Attribute24 => c_inst_rec.Attribute24,
X_Attribute25 => c_inst_rec.Attribute25,
X_Attribute26 => c_inst_rec.Attribute26,
X_Attribute27 => c_inst_rec.Attribute27,
X_Attribute28 => c_inst_rec.Attribute28,
X_Attribute29 => c_inst_rec.Attribute29,
X_Attribute30 => c_inst_rec.Attribute30,
X_Attribute31 => c_inst_rec.Attribute31,
X_Attribute32 => c_inst_rec.Attribute32,
X_Attribute33 => c_inst_rec.Attribute33,
X_Attribute34 => c_inst_rec.Attribute34,
X_Attribute35 => c_inst_rec.Attribute35,
X_Attribute36 => c_inst_rec.Attribute36,
X_Attribute37 => c_inst_rec.Attribute37,
X_Attribute38 => c_inst_rec.Attribute38,
X_Attribute39 => c_inst_rec.Attribute39,
X_Attribute40 => c_inst_rec.Attribute40,
x_fut_acad_cal_type => c_inst_rec.future_acad_cal_type,
x_fut_acad_ci_sequence_number => c_inst_rec.future_acad_ci_sequence_number,
x_fut_adm_cal_type => c_inst_rec.future_adm_cal_type,
x_fut_adm_ci_sequence_number => c_inst_rec.future_adm_ci_sequence_number,
x_prev_term_adm_appl_number => c_inst_rec.previous_term_adm_appl_number,
x_prev_term_sequence_number => c_inst_rec.previous_term_sequence_number,
x_fut_term_adm_appl_number => c_inst_rec.future_term_adm_appl_number,
x_fut_term_sequence_number => c_inst_rec.future_term_sequence_number,
x_def_acad_cal_type =>c_inst_rec.def_acad_cal_type,
x_def_acad_ci_sequence_num =>c_inst_rec.def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num =>c_inst_rec.def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num =>c_inst_rec.def_prev_appl_sequence_num,
x_def_term_adm_appl_num =>c_inst_rec.def_term_adm_appl_num,
x_def_appl_sequence_num =>c_inst_rec.def_appl_sequence_num,
x_appl_inst_status =>c_inst_rec.APPL_INST_STATUS, --apadegal adtd001 IGS.m
x_ais_reason =>c_inst_rec.AIS_REASON, --apadegal adtd001 IGS.m
x_decline_ofr_reason =>c_inst_rec.DECLINE_OFR_REASON --apadegal adtd001 IGS.m
);
hist_rec.hist_start_dt := c_inst_rec.last_update_date;
hist_rec.hist_who := c_inst_rec.last_updated_by;
hist_rec.hist_end_dt := c_inst_last_who_rec.last_update_date ;
IGS_AD_PS_APLINSTHST_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => hist_rec.person_id,
X_Admission_Appl_Number => hist_rec.admission_appl_number,
X_Nominated_Course_Cd => hist_rec.nominated_course_cd,
X_Sequence_Number => hist_rec.sequence_number,
X_Hist_Start_Dt => hist_rec.hist_start_dt,
X_Hist_End_Dt => hist_rec.hist_end_dt,
X_Hist_Who => hist_rec.hist_who,
X_Hist_Offer_Round_Number => Null,
X_Adm_Cal_Type => hist_rec.adm_cal_type,
X_Adm_Ci_Sequence_Number => hist_rec.adm_ci_sequence_number,
X_Course_Cd => hist_rec.course_cd,
X_Crv_Version_Number => hist_rec.crv_version_number,
X_Location_Cd => hist_rec.location_cd,
X_Attendance_Mode => hist_rec.attendance_mode,
X_Attendance_Type => hist_rec.attendance_type,
X_Unit_Set_Cd => hist_rec.unit_set_cd,
X_Us_Version_Number => hist_rec.us_version_number,
X_Preference_Number => hist_rec.preference_number,
X_Adm_Doc_Status => hist_rec.adm_doc_status,
X_Adm_Entry_Qual_Status => hist_rec.adm_entry_qual_status,
X_Late_Adm_Fee_Status => hist_rec.late_adm_fee_status,
X_Adm_Outcome_Status => hist_rec.adm_outcome_status,
X_ADM_OTCM_STATUS_AUTH_PER_ID => hist_rec.adm_otcm_status_auth_person_id,
X_Adm_Outcome_Status_Auth_Dt => hist_rec.adm_outcome_status_auth_dt,
X_Adm_Outcome_Status_Reason => hist_rec.adm_outcome_status_reason,
X_Offer_Dt => hist_rec.offer_dt,
X_Offer_Response_Dt => hist_rec.offer_response_dt,
X_Prpsd_Commencement_Dt => hist_rec.prpsd_commencement_dt,
X_Adm_Cndtnl_Offer_Status => hist_rec.adm_cndtnl_offer_status,
X_Cndtnl_Offer_Satisfied_Dt => hist_rec.cndtnl_offer_satisfied_dt,
X_CNDTNL_OFR_MUST_BE_STSFD_IND => hist_rec.cndtnl_offer_must_be_stsfd_ind,
X_Adm_Offer_Resp_Status => hist_rec.adm_offer_resp_status,
X_Actual_Response_Dt => hist_rec.actual_response_dt,
X_Adm_Offer_Dfrmnt_Status => hist_rec.adm_offer_dfrmnt_status,
X_Deferred_Adm_Cal_Type => hist_rec.deferred_adm_cal_type,
X_Deferred_Adm_Ci_Sequence_Num => hist_rec.deferred_adm_ci_sequence_num,
X_Deferred_Tracking_Id => hist_rec.deferred_tracking_id,
X_Ass_Rank => hist_rec.ass_rank,
X_Secondary_Ass_Rank => hist_rec.secondary_ass_rank,
X_INTRNTNL_ACCEPT_ADVICE_NUM => hist_rec.intrntnl_acceptance_advice_num,
X_Ass_Tracking_Id => hist_rec.ass_tracking_id,
X_Fee_Cat => hist_rec.fee_cat,
X_Hecs_Payment_Option => hist_rec.hecs_payment_option,
X_Expected_Completion_Yr => hist_rec.expected_completion_yr,
X_Expected_Completion_Perd => hist_rec.expected_completion_perd,
X_Correspondence_Cat => hist_rec.correspondence_cat,
X_Enrolment_Cat => hist_rec.enrolment_cat,
X_Funding_Source => hist_rec.funding_source,
X_Applicant_Acptnce_Cndtn => hist_rec.applicant_acptnce_cndtn,
X_Cndtnl_Offer_Cndtn => hist_rec.cndtnl_offer_cndtn,
X_Org_Id => igs_ge_gen_003.get_org_id,
X_Appl_inst_status => hist_rec.appl_inst_status,
X_DECISION_DATE => hist_rec.DECISION_DATE,
X_DECISION_MAKE_ID => hist_rec.DECISION_MAKE_ID,
X_DECISION_REASON_ID => hist_rec.DECISION_REASON_ID,
X_PENDING_REASON_ID => hist_rec.PENDING_REASON_ID,
X_WAITLIST_STATUS => hist_rec.WAITLIST_STATUS,
X_WAITLIST_RANK => hist_rec.WAITLIST_RANK,
X_FUTURE_ACAD_CAL_TYPE => hist_rec.FUTURE_ACAD_CAL_TYPE,
X_FUTURE_ACAD_CI_SEQUENCE_NUM => hist_rec.FUTURE_ACAD_CI_SEQUENCE_NUM,
X_FUTURE_ADM_CAL_TYPE => hist_rec.FUTURE_ADM_CAL_TYPE,
X_FUTURE_ADM_CI_SEQUENCE_NUM => hist_rec.FUTURE_ADM_CI_SEQUENCE_NUM,
X_DEF_ACAD_CAL_TYPE => hist_rec.DEF_ACAD_CAL_TYPE,
X_DEF_ACAD_CI_SEQUENCE_NUM => hist_rec.DEF_ACAD_CI_SEQUENCE_NUM,
X_RECONSIDER_FLAG => hist_rec.RECONSIDER_FLAG,
X_DECLINE_OFR_REASON => hist_rec.DECLINE_OFR_REASON
);
SELECT acaiv.future_term_adm_appl_number,
acaiv.future_term_sequence_number,
acaiv.def_term_adm_appl_num,
acaiv.def_appl_sequence_num
FROM igs_ad_ps_appl_inst acaiv
WHERE acaiv.person_id = p_person_id AND
acaiv.admission_appl_number = p_admission_appl_number AND
acaiv.nominated_course_cd = p_nominated_course_cd AND
acaiv.sequence_number = p_sequence_number;