[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cdcruz feb18 bug 2217104 Admit to future term Enhancement,updated tbh call for
new columns being added to IGS_AD_PS_APPL_INST
nshee 29-Aug-2002 Bug 2395510 DEferments build Added 6 columns in update row call
rboddu 13-FEB-2003 removed PROCEDURE Admp_Upd_Eap_Avail. Moved this to
igs_rc_gen_001 package. Bug:2664699
********************************************************************/
FUNCTION Admp_Upd_Acai_Comm(
p_person_id IN NUMBER ,
p_admission_appl_number IN NUMBER ,
p_nominated_course_cd IN VARCHAR2 ,
p_acai_sequence_number IN NUMBER ,
p_prpsd_commencement_dt IN DATE ,
p_message_name OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN IS
-- This module updates the IGS_AD_PS_APPL_INST.prpsd_commencement_dt.
v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
v_update_non_enrol_detail_ind VARCHAR2(1);
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
FOR UPDATE OF acai.prpsd_commencement_dt NOWAIT;
SELECT aa.adm_appl_status
FROM IGS_AD_APPL aa
WHERE aa.person_id = p_person_id AND
aa.admission_appl_number = p_admission_appl_number;
IF IGS_AD_VAL_ACAI.admp_val_acai_update (
v_adm_appl_status,
p_person_id,
p_admission_appl_number,
p_nominated_course_cd,
p_acai_sequence_number,
v_message_name,
v_update_non_enrol_detail_ind) = FALSE THEN
-- begin apadegal adtd001 igs.m
-- PRPSD_COMMENCEMENT_DT can be udpated in proceed phase
IF ( v_message_name = 'IGS_AD_APPL_INST_COMPL')
THEN
IF igs_ad_gen_002.check_adm_appl_inst_stat( p_person_id => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
p_admission_appl_number => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
p_nominated_course_cd => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
p_sequence_number => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
p_updateable => 'Y'
)='N' --- not updateable.. so throw error
THEN
p_message_name := 'IGS_AD_APPL_INST_COMPL';
IGS_AD_PS_APPL_Inst_Pkg.UPDATE_ROW (
X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
X_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.ADM_CAL_TYPE ,
X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
X_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADM_CI_SEQUENCE_NUMBER ,
X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
X_LATE_ADM_FEE_STATUS => Rec_IGS_AD_PS_APPL_Inst.LATE_ADM_FEE_STATUS ,
X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
X_ADM_OUTCOME_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS ,
X_ADM_OTCM_STAT_AUTH_PER_ID => Rec_IGS_AD_PS_APPL_Inst.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
X_ADM_OUTCOME_STATUS_AUTH_DT => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_AUTH_DT ,
X_ADM_OUTCOME_STATUS_REASON => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_REASON ,
X_OFFER_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_DT ,
X_OFFER_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_RESPONSE_DT ,
X_PRPSD_COMMENCEMENT_DT => p_prpsd_commencement_dt ,
X_ADM_CNDTNL_OFFER_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_CNDTNL_OFFER_STATUS ,
X_CNDTNL_OFFER_SATISFIED_DT => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_SATISFIED_DT ,
X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
X_ADM_OFFER_RESP_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_RESP_STATUS ,
X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
X_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.ASS_RANK ,
X_SECONDARY_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.SECONDARY_ASS_RANK ,
X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
X_ASS_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.ASS_TRACKING_ID ,
X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
X_EXPECTED_COMPLETION_YR => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_YR ,
X_EXPECTED_COMPLETION_PERD => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_PERD ,
X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
X_CNDTNL_OFFER_CNDTN => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_CNDTN ,
X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
X_WAITLIST_STATUS => Rec_IGS_AD_PS_APPL_Inst.WAITLIST_STATUS, -- BUG # 2097333
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Bug # 1905651
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Bug # 1905651
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Bug # 1905651
X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
X_MODE => 'R',
X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
);
CURSOR c_update_acai IS
SELECT acai.ROWID, acai.*
FROM IGS_AD_PS_APPL_INST acai
WHERE EXISTS (SELECT 'x' from igs_ad_ou_stat aos
WHERE aos.adm_outcome_status = acai.adm_outcome_status
AND aos.s_adm_outcome_status IN ( cst_offer, cst_cond_offer))
AND EXISTS (SELECT 'x' FROM igs_ad_ofr_resp_stat aors
WHERE aors.adm_offer_resp_status = acai.adm_offer_resp_status
AND aors.s_adm_offer_resp_status = cst_pending
AND acai.offer_response_dt < TRUNC(SYSDATE))
FOR UPDATE OF acai.adm_offer_resp_status NOWAIT;
FOR Rec_IGS_AD_PS_APPL_Inst IN c_update_acai LOOP
BEGIN
IGS_AD_PS_APPL_Inst_Pkg.UPDATE_ROW (
X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
X_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.ADM_CAL_TYPE ,
X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
X_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADM_CI_SEQUENCE_NUMBER ,
X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
X_Waitlist_Status => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Status,
X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
X_LATE_ADM_FEE_STATUS => Rec_IGS_AD_PS_APPL_Inst.LATE_ADM_FEE_STATUS ,
X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
X_ADM_OUTCOME_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS ,
X_ADM_OTCM_STAT_AUTH_PER_ID => Rec_IGS_AD_PS_APPL_Inst.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
X_ADM_OUTCOME_STATUS_AUTH_DT => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_AUTH_DT ,
X_ADM_OUTCOME_STATUS_REASON => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_REASON ,
X_OFFER_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_DT ,
X_OFFER_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_RESPONSE_DT ,
X_PRPSD_COMMENCEMENT_DT => Rec_IGS_AD_PS_APPL_Inst.Prpsd_Commencement_Dt,
X_ADM_CNDTNL_OFFER_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_CNDTNL_OFFER_STATUS ,
X_CNDTNL_OFFER_SATISFIED_DT => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_SATISFIED_DT ,
X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
X_ADM_OFFER_RESP_STATUS => v_adm_offer_resp_status,
X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
X_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.ASS_RANK ,
X_SECONDARY_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.SECONDARY_ASS_RANK ,
X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
X_ASS_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.ASS_TRACKING_ID ,
X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
X_EXPECTED_COMPLETION_YR => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_YR ,
X_EXPECTED_COMPLETION_PERD => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_PERD ,
X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
X_CNDTNL_OFFER_CNDTN => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_CNDTN ,
X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Bug # 1905651
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Bug # 1905651
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Bug # 1905651
X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
X_MODE => 'R',
X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
);
fnd_file.put_line(fnd_file.log, 'Failed to update Offer Response Status to LAPSED for Person ID: '|| Rec_IGS_AD_PS_APPL_Inst.PERSON_ID || '; Admission Application Number: ' ||
IF c_update_acai%ISOPEN THEN
CLOSE c_update_acai;
IF c_update_acai%ISOPEN THEN
CLOSE c_update_acai;
SELECT daiv.cal_type,
daiv.ci_sequence_number,
ci.start_dt
FROM IGS_CA_DA_INST_V daiv,
IGS_CA_INST ci,
IGS_AD_CAL_CONF sacco
WHERE sacco.s_control_num = 1 AND
daiv.dt_alias = sacco.initialise_adm_perd_dt_alias AND
daiv.alias_val = TRUNC(SYSDATE) AND
daiv.cal_type = ci.cal_type AND
daiv.ci_sequence_number = ci.sequence_number
ORDER BY
ci.start_dt;
SELECT aa.admission_cat,
aa.s_admission_process_type,
aa.person_id,
aa.admission_appl_number,
aa.appl_dt,
aca.nominated_course_cd
FROM IGS_AD_PS_APPL aca,
IGS_AD_APPL aa
WHERE aa.person_id = aca.person_id
AND aa.admission_appl_number = aca.admission_appl_number
AND aca.req_for_reconsideration_ind = 'Y'
AND EXISTS ( SELECT 'x' FROM igs_ad_prd_ad_prc_ca apapc
WHERE apapc.adm_cal_type = cp_adm_cal_type
AND apapc.adm_ci_sequence_number = cp_adm_ci_sequence_number
AND apapc.admission_cat = aa.admission_cat
AND apapc.s_admission_process_type = aa.s_admission_process_type
AND apapc.closed_ind = 'N');
SELECT ROWID, aca.*
FROM IGS_AD_PS_APPL aca
WHERE aca.person_id = cp_person_id AND
aca.admission_appl_number = cp_admission_appl_number AND
aca.nominated_course_cd = cp_nominated_course_cd
FOR UPDATE OF aca.req_for_reconsideration_ind NOWAIT;
SELECT acaiv.person_id,
acaiv.admission_appl_number,
acaiv.nominated_course_cd,
acaiv.sequence_number,
acaiv.course_cd,
acaiv.crv_version_number,
aa.acad_cal_type,
aa.acad_ci_sequence_number,
acaiv.attendance_type,
acaiv.attendance_mode,
acaiv.location_cd
FROM igs_ad_ps_appl_inst acaiv,
igs_ad_appl aa
WHERE aa.person_id = acaiv.person_id
AND aa.admission_appl_number = acaiv.admission_appl_number
AND acaiv.person_id = cp_person_id
AND acaiv.admission_appl_number = cp_admission_appl_number
AND acaiv.nominated_course_cd = cp_nominated_course_cd
AND EXISTS (SELECT 'x' FROM igs_ad_ou_stat aos
WHERE aos.adm_outcome_status = acaiv.adm_outcome_status
AND aos.s_adm_outcome_status IN (cst_rejected, cst_no_quota))
AND EXISTS (SELECT 'x' FROM igs_ca_inst ci
WHERE ci.cal_type = NVL (acaiv.adm_cal_type,aa.adm_cal_type)
AND ci.sequence_number = NVL (acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number)
AND ci.start_dt < cp_start_dt);
SELECT acai.ROWID, acai.*
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 = cp_nominated_course_cd AND
acai.sequence_number = cp_sequence_number
FOR UPDATE OF
acai.adm_cal_type,
acai.adm_ci_sequence_number,
acai.late_adm_fee_status,
acai.adm_outcome_status,
acai.offer_dt,
acai.offer_response_dt,
acai.adm_cndtnl_offer_status,
acai.cndtnl_offer_satisfied_dt,
acai.cndtnl_offer_cndtn,
acai.ass_tracking_id,
acai.ass_rank,
acai.secondary_ass_rank,
acai.adm_otcm_status_auth_person_id,
acai.adm_outcome_status_auth_dt,
acai.adm_outcome_status_reason,
acai.expected_completion_perd,
acai.expected_completion_yr NOWAIT;
v_acai_updated VARCHAR2(1) DEFAULT 'N';
v_acai_updated := 'N';
IGS_AD_PS_APPL_Inst_Pkg.UPDATE_ROW (
X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
X_Adm_Cal_Type => v_daiv_rec.cal_type,
X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
X_Adm_Ci_Sequence_Number => v_daiv_rec.ci_sequence_number,
X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
X_Waitlist_Status => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Status,
X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
X_Late_Adm_Fee_Status => v_late_adm_fee_status,
X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
X_Adm_Outcome_Status => v_adm_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 => Rec_IGS_AD_PS_APPL_Inst.prpsd_commencement_dt ,
X_Adm_Cndtnl_Offer_Status => v_adm_cndtnl_offer_status,
X_Cndtnl_Offer_Satisfied_Dt => NULL,
X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
X_ADM_OFFER_RESP_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_RESP_STATUS ,
X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
X_ASS_RANK => NULL,
X_SECONDARY_ASS_RANK => NULL,
X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
X_ASS_TRACKING_ID => NULL,
X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
X_EXPECTED_COMPLETION_YR => V_EXPECTED_COMPLETION_YR ,
X_EXPECTED_COMPLETION_PERD => V_EXPECTED_COMPLETION_PERD ,
X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
X_CNDTNL_OFFER_CNDTN => NULL ,
X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Bug # 1905651
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Bug # 1905651
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Bug # 1905651
X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
X_MODE => 'R',
X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
);
v_acai_updated := 'Y';
IF v_acai_updated = 'Y' THEN
IGS_AD_PS_APPL_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => Rec_IGS_AD_PS_APPL.RowId,
X_Person_Id => Rec_IGS_AD_PS_APPL.Person_Id,
X_Admission_Appl_Number => Rec_IGS_AD_PS_APPL.Admission_Appl_Number,
X_Nominated_Course_Cd => Rec_IGS_AD_PS_APPL.Nominated_Course_Cd,
X_Transfer_Course_Cd => Rec_IGS_AD_PS_APPL.Transfer_Course_Cd,
X_Basis_For_Admission_Type => Rec_IGS_AD_PS_APPL.Basis_For_Admission_Type,
X_Admission_Cd => Rec_IGS_AD_PS_APPL.Admission_Cd,
X_Course_Rank_Set => Rec_IGS_AD_PS_APPL.Course_Rank_Set,
X_Course_Rank_Schedule => Rec_IGS_AD_PS_APPL.Course_Rank_Schedule,
X_Req_For_Reconsideration_Ind => 'N',
X_Req_For_Adv_Standing_Ind => Rec_IGS_AD_PS_APPL.Req_For_Adv_Standing_Ind
);
SELECT ROWID, pp.*
FROM IGS_PE_PERS_PREFS_ALL pp
WHERE pp.person_id = cp_pp_per_id
FOR UPDATE OF enr_acad_cal_type NOWAIT;
IGS_PE_PERS_PREFS_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => Rec_IGS_PE_PERS_PREFS.RowId,
X_Person_Id => Rec_IGS_PE_PERS_PREFS.Person_Id,
X_Enr_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Cal_Type,
X_Enr_Acad_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Sequence_Number,
X_Enr_Enrolment_Cat => Rec_IGS_PE_PERS_PREFS.Enr_Enrolment_Cat,
X_Enr_Enr_Method_Type => Rec_IGS_PE_PERS_PREFS.Enr_Enr_Method_Type,
X_Adm_Acad_Cal_Type => p_adm_acad_cal_type,
X_Adm_Acad_Ci_Sequence_Number => p_adm_acad_ci_sequence_number,
X_Adm_Adm_Cal_Type => p_adm_adm_cal_type,
X_Adm_Adm_Ci_Sequence_Number => p_adm_adm_ci_sequence_number,
X_Adm_Admission_Cat => p_adm_admission_cat,
X_Adm_S_Admission_Process_Type => p_adm_s_admission_process_type,
X_Enq_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enq_Acad_Cal_Type,
X_Enq_Acad_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enq_Acad_Ci_Sequence_Number,
X_Enq_Adm_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enq_Adm_Cal_Type,
X_Enq_Adm_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enq_Adm_Ci_Sequence_Number,
X_Server_Printer_Dflt => Rec_IGS_PE_PERS_PREFS.Server_Printer_Dflt,
X_Allow_Stnd_Req_Ind => Rec_IGS_PE_PERS_PREFS.Allow_Stnd_Req_Ind
);
IGS_PE_PERS_PREFS_Pkg.Insert_Row (
X_Mode => 'R',
X_Org_Id => l_org_id,
X_RowId => lv_rowid,
X_Person_Id => v_person_id,
X_Enr_Acad_Cal_Type => Null,
X_Enr_Acad_Sequence_Number => Null,
X_Enr_Enrolment_Cat => Null,
X_Enr_Enr_Method_Type => Null,
X_Adm_Acad_Cal_Type => p_adm_acad_cal_type,
X_Adm_Acad_Ci_Sequence_Number => p_adm_acad_ci_sequence_number,
X_Adm_Adm_Cal_Type => p_adm_adm_cal_type,
X_Adm_Adm_Ci_Sequence_Number => p_adm_adm_ci_sequence_number,
X_Adm_Admission_Cat => p_adm_admission_cat,
X_Adm_S_Admission_Process_Type => p_adm_s_admission_process_type,
X_Enq_Acad_Cal_Type => Null,
X_Enq_Acad_Ci_Sequence_Number => Null,
X_Enq_Adm_Cal_Type => Null,
X_Enq_Adm_Ci_Sequence_Number => Null,
X_Server_Printer_Dflt => Null,
X_Allow_Stnd_Req_Ind => 'N'
);
SELECT ROWID, pp.*
FROM IGS_PE_PERS_PREFS_ALL pp
WHERE pp.person_id = cp_pp_per_id
FOR UPDATE OF enr_acad_cal_type NOWAIT;
IGS_PE_PERS_PREFS_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => Rec_IGS_PE_PERS_PREFS.RowId,
X_Person_Id => Rec_IGS_PE_PERS_PREFS.Person_Id,
X_Enr_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Cal_Type,
X_Enr_Acad_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Sequence_Number,
X_Enr_Enrolment_Cat => Rec_IGS_PE_PERS_PREFS.Enr_Enrolment_Cat,
X_Enr_Enr_Method_Type => Rec_IGS_PE_PERS_PREFS.Enr_Enr_Method_Type,
X_Adm_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.adm_acad_cal_type,
X_Adm_Acad_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.adm_acad_ci_sequence_number,
X_Adm_Adm_Cal_Type => Rec_IGS_PE_PERS_PREFS.adm_adm_cal_type,
X_Adm_Adm_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.adm_adm_ci_sequence_number,
X_Adm_Admission_Cat => Rec_IGS_PE_PERS_PREFS.adm_admission_cat,
X_Adm_S_Admission_Process_Type => Rec_IGS_PE_PERS_PREFS.adm_s_admission_process_type,
X_Enq_Acad_Cal_Type => p_enq_acad_cal_type,
X_Enq_Acad_Ci_Sequence_Number => p_enq_acad_ci_sequence_number,
X_Enq_Adm_Cal_Type => p_enq_adm_cal_type,
X_Enq_Adm_Ci_Sequence_Number => p_enq_adm_ci_sequence_number,
X_Server_Printer_Dflt => Rec_IGS_PE_PERS_PREFS.Server_Printer_Dflt,
X_Allow_Stnd_Req_Ind => Rec_IGS_PE_PERS_PREFS.Allow_Stnd_Req_Ind
);
IGS_PE_PERS_PREFS_Pkg.Insert_Row (
X_Mode => 'R',
X_org_id => l_org_id,
X_RowId => lv_rowid,
X_Person_Id => v_person_id,
X_Enr_Acad_Cal_Type => Null,
X_Enr_Acad_Sequence_Number => Null,
X_Enr_Enrolment_Cat => Null,
X_Enr_Enr_Method_Type => Null,
X_Adm_Acad_Cal_Type => Null,
X_Adm_Acad_Ci_Sequence_Number => Null,
X_Adm_Adm_Cal_Type => Null,
X_Adm_Adm_Ci_Sequence_Number => Null,
X_Adm_Admission_Cat => Null,
X_Adm_S_Admission_Process_Type => Null,
X_Enq_Acad_Cal_Type => p_enq_acad_cal_type,
X_Enq_Acad_Ci_Sequence_Number => p_enq_acad_ci_sequence_number,
X_Enq_Adm_Cal_Type => p_enq_adm_cal_type,
X_Enq_Adm_Ci_Sequence_Number => p_enq_adm_ci_sequence_number,
X_Server_Printer_Dflt => Null,
X_Allow_Stnd_Req_Ind => 'N'
);
SELECT
group_cd
FROM
igs_pe_persid_group
WHERE
group_cd = p_group_cd;