[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
knag 21-Nov-2002 Added new parameters to admp_ins_adm_appl for bug 2664410
and new parameters to call to igs_ad_appl_pkg.insert_row
pkpatel 01-DEC-2002 Bug NO: 2599109 (Sevis DLD)
Modified the the signatute of call to TBH IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW
anilk 18-FEB-2003 Bug#2784198
Added closed_ind = 'N' to where clause for cursor c_hl
asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row and update_row changed
gmaheswa 19-Nov-2003 3227107: Modified cursor c_pa in admp_ins_person_addr to select records having active status.
******************************************************************/
--
-- Get the admission category for this COURSE offering option
FUNCTION admp_get_ac_cooac(
p_coo_id IN NUMBER ,
p_admission_cat IN OUT NOCOPY VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN IS
BEGIN -- admp_get_ac_cooac
-- This module retrieves the admission category from the IGS_PS_COURSE
-- offering option admission category table
DECLARE
v_x VARCHAR2(1) DEFAULT NULL;
SELECT admission_cat
FROM IGS_PS_OF_OPT_AD_CAT
WHERE coo_id = p_coo_id AND
system_default_ind = 'Y';
SELECT 'x'
FROM IGS_PS_OF_OPT_AD_CAT
WHERE coo_id = p_coo_id AND
admission_cat = p_admission_cat;
-- Select default admission category
OPEN c_cooac;
-- Select anything from course_offering_option_adm_cat
OPEN c_cooac2;
-- This procedure inserts a new IGS_AD_AUS_SEC_EDU record
DECLARE
v_secondary_school_cd IGS_AD_AUS_SEC_ED_SC.secondary_school_cd%TYPE := NULL;
SELECT secondary_school_cd
FROM IGS_AD_AUS_SEC_ED_SC
WHERE secondary_school_cd = p_secondary_school_cd AND
closed_ind = 'N' AND
state_cd = p_state_cd;
SELECT aus_scndry_edu_ass_type,
state_cd
FROM IGS_AD_AUSE_ED_AS_TY
WHERE aus_scndry_edu_ass_type = p_aus_scndry_edu_ass_type OR
p_aus_scndry_edu_ass_type IS NULL OR
tac_aus_scndry_edu_ass_type = p_aus_scndry_edu_ass_type;
SELECT NVL(MAX(ase_sequence_number), 0) +1
FROM IGS_AD_AUS_SEC_ED_SU
WHERE person_id = p_person_id;
-- Insert the record
Igs_Ad_Aus_Sec_Edu_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Sequence_Number => v_ase_sequence_number,
X_State_Cd => p_state_cd,
X_Result_Obtained_Yr => p_result_obtained_yr,
X_Score => p_score,
X_Aus_Scndry_Edu_Ass_Type => v_aus_scndry_edu_ass_type,
X_Candidate_Number => p_candidate_number,
X_Secondary_School_Cd => v_secondary_school_cd
);
-- This procedure inserts a new IGS_AD_AUS_SEC_ED_SC record
DECLARE
CURSOR c_aseat IS
SELECT aus_scndry_edu_ass_type
FROM IGS_AD_AUSE_ED_AS_TY
WHERE aus_scndry_edu_ass_type = p_subject_ass_type OR
tac_aus_scndry_edu_ass_type = p_subject_ass_type;
Igs_Ad_Aus_Sec_Ed_Su_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Ase_Sequence_Number => p_ase_sequence_number,
X_Subject_Result_Yr => p_subject_result_yr,
X_Subject_Cd => p_subject_cd,
X_Subject_Desc => p_Subject_Desc,
X_Subject_Mark => p_Subject_Mark,
X_Subject_Mark_Level => p_Subject_Mark_Level,
X_Subject_Weighting => p_Subject_Weighting,
X_Subject_Ass_Type => p_Subject_Ass_Type,
X_Notes => NULL
);
SELECT teloc.tertiary_edu_lvl_comp
FROM IGS_AD_TER_ED_LV_COM teloc
WHERE teloc.tac_level_of_comp = p_tac_level_of_comp AND
teloc.closed_ind = 'N';
--Procedure inserts a new IGS_AD_APPL record. It uses an
--output parameter to pass back the new admission_appl_number used
DECLARE
v_dummy CHAR;
SELECT s_admission_step_type
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_cat = cp_admission_cat AND
s_admission_process_type = cp_s_admission_process_type
AND step_group_type <> 'TRACK';
SELECT NVL(MAX(admission_appl_number),0) + 1
FROM igs_ad_appl
WHERE person_id = p_person_id;
SELECT admission_application_type
FROM igs_ad_ss_appl_typ
WHERE admission_cat = cp_adm_cat
AND S_admission_process_type = cp_s_adm_prc_typ
AND System_default = 'Y'
AND NVL(closed_ind, 'N') <> 'Y';
-- Validate insert of the admission application record.
--
IF Igs_Ad_Val_Aa.admp_val_aa_insert (
p_person_id,
p_adm_cal_type,
p_adm_ci_sequence_number,
p_s_admission_process_type,
v_person_encmb_chk_ind,
p_appl_dt,
v_title_required_ind,
v_birth_dt_required_ind,
v_message_name,
v_return_type) = FALSE THEN
IF NVL(v_return_type, '-1') = cst_error THEN
p_message_name := v_message_name;
--Now insert the new record
--Populate the org id
l_org_id := igs_ge_gen_003.get_org_id;
Igs_Ad_Appl_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_Person_Id,
X_Admission_Appl_Number => v_adm_appl_number,
X_Appl_Dt => p_Appl_Dt,
X_Acad_Cal_Type => p_Acad_Cal_Type,
X_Acad_Ci_Sequence_Number => p_Acad_Ci_Sequence_Number,
X_Adm_Cal_Type => p_Adm_Cal_Type,
X_Adm_Ci_Sequence_Number => p_Adm_Ci_Sequence_Number,
X_Admission_Cat => p_Admission_Cat,
X_S_Admission_Process_Type => p_S_Admission_Process_Type,
X_Adm_Appl_Status => p_Adm_Appl_Status,
X_Adm_Fee_Status => p_Adm_Fee_Status,
X_Tac_Appl_Ind => p_Tac_Appl_Ind,
X_Org_Id => l_org_id,
X_Spcl_Grp_1 => p_spcl_grp_1, -- bug# 1964478, parameter added
X_Spcl_Grp_2 => p_spcl_grp_2, -- bug# 1964478, parameter added
X_Common_App => p_common_app, -- bug# 1964478, parameter added
x_application_type => l_application_type, -- Added as part of 2599457
x_choice_number => p_choice_number,
x_routeb_pref => p_routeb_pref,
x_alt_appl_id => p_alt_appl_id -- Added for bug 2664410
);
p_insert_outcome_ind IN VARCHAR2 ,
p_pre_enrol_ind IN VARCHAR2 ,
p_return_type OUT NOCOPY VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN IS
/******************************************************************
Created By:
Date Created By:
Purpose:
Known limitations,enhancements,remarks:
Change History
Who When What
knag 28-Oct-2002 Called func igs_ad_gen_003.get_core_or_optional_unit for bug 2647482
******************************************************************/
BEGIN -- admp_ins_tac_acai
-- This procedure inserts a new IGS_AD_PS_APPL_INST record for the TAC
-- offer load process.
DECLARE
e_resource_busy EXCEPTION;
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_course_cd;
SELECT ROWID, APAI.*
FROM IGS_AD_PS_APPL_INST APAI
WHERE
person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_course_cd AND
sequence_number = v_acai_sequence_number
FOR UPDATE OF person_id NOWAIT;
-- Insert an admission COURSE application instance with PENDING outcome
--------------------------------------------------------------------------
l_org_id := igs_ge_gen_003.get_org_id;
Igs_Ad_Ps_Appl_Inst_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_course_cd,
X_Sequence_Number => v_acai_sequence_number,
X_PREDICTED_GPA => NULL,
X_ACADEMIC_INDEX => NULL,
X_Adm_Cal_Type => NULL,
X_APP_FILE_LOCATION => NULL,
X_Adm_Ci_Sequence_Number => NULL,
X_Course_Cd => p_Course_Cd,
X_APP_SOURCE_ID => NULL,
X_Crv_Version_Number => p_Crv_Version_Number,
X_WAITLIST_RANK => NULL,
X_Location_Cd => p_Location_Cd,
X_ATTENT_OTHER_INST_CD => NULL,
X_Attendance_Mode => p_Attendance_Mode,
X_EDU_GOAL_PRIOR_ENROLL_ID => NULL,
X_Attendance_Type => p_Attendance_Type,
X_DECISION_MAKE_ID => NULL,
X_Unit_Set_Cd => p_Unit_Set_Cd,
X_DECISION_DATE => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_DECISION_REASON_ID => NULL,
X_Us_Version_Number => p_Us_Version_Number,
X_DECISION_NOTES => NULL,
X_PENDING_REASON_ID => NULL,
X_Preference_Number => p_Preference_Number,
X_Adm_Doc_Status => v_Adm_Doc_Status,
X_Adm_Entry_Qual_Status => v_Adm_Entry_Qual_Status,
X_DEFICIENCY_IN_PREP => NULL,
X_Late_Adm_Fee_Status => v_Late_Adm_Fee_Status,
X_SPL_CONSIDER_COMMENTS => NULL,
X_APPLY_FOR_FINAID => NULL,
X_FINAID_APPLY_DATE => NULL,
X_Adm_Outcome_Status => v_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 => v_adm_cndtnl_offer_status,
X_Cndtnl_Offer_Satisfied_Dt => NULL,
X_CNDNL_OFR_MUST_BE_STSFD_IND => 'N',
X_Adm_Offer_Resp_Status => v_adm_offer_resp_status,
X_Actual_Response_Dt => NULL,
X_Adm_Offer_Dfrmnt_Status => v_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 => NULL,
X_Secondary_Ass_Rank => NULL,
X_INTR_ACCEPT_ADVICE_NUM => NULL,
X_Ass_Tracking_Id => NULL,
X_Fee_Cat => v_Fee_Cat,
X_Hecs_Payment_Option => v_Hecs_Payment_Option,
X_Expected_Completion_Yr => NULL,
X_Expected_Completion_Perd => NULL,
X_Correspondence_Cat => v_Correspondence_Cat,
X_Enrolment_Cat => v_Enrolment_Cat,
X_Funding_Source => NULL,
X_Applicant_Acptnce_Cndtn => NULL,
X_Cndtnl_Offer_Cndtn => NULL,
X_ss_application_id => NULL,
X_ss_pwd => NULL,
X_AUTHORIZED_DT => NULL, -- BUG ENH NO : 1891835 Added this column in table
X_AUTHORIZING_PERS_ID => NULL, -- BUG ENH NO : 1891835 Added this column in table
X_IDX_CALC_DATE => NULL,
X_Org_id => l_org_id,
x_entry_status => NULL, -- Enh Bug#1964478 added three parameters
x_entry_level => NULL, -- Enh Bug#1964478 added three parameters
x_sch_apl_to_id => NULL, -- Enh Bug#1964478 added three parameters
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_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL,
X_ATTRIBUTE25 => NULL,
X_ATTRIBUTE26 => NULL,
X_ATTRIBUTE27 => NULL,
X_ATTRIBUTE28 => NULL,
X_ATTRIBUTE29 => NULL,
X_ATTRIBUTE30 => NULL,
X_ATTRIBUTE31 => NULL,
X_ATTRIBUTE32 => NULL,
X_ATTRIBUTE33 => NULL,
X_ATTRIBUTE34 => NULL,
X_ATTRIBUTE35 => NULL,
X_ATTRIBUTE36 => NULL,
X_ATTRIBUTE37 => NULL,
X_ATTRIBUTE38 => NULL,
X_ATTRIBUTE39 => NULL,
X_ATTRIBUTE40 => NULL
);
-- Update admission COURSE application instance with OFFER
------------------------------------------------------------
BEGIN
OPEN c_upd_acai;
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 => v_offer_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 => p_offer_dt,
X_OFFER_RESPONSE_DT => v_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_new_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_MODE => 'R',
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Enh Bug#1964478 added three parameters
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Enh Bug#1964478 added three parameters
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Enh Bug#1964478 added three parameters
X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
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_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
);
-- check for locking on update, although this should never occur
WHEN e_resource_busy THEN
p_message_name := 'IGS_AD_APPL_NOTSET_LOCKING';
-- Insert outcome letter
--------------------------
IF p_insert_outcome_ind = 'Y' AND
v_apcs_outcome_lt_ind = 'Y' THEN
-- Insert offer letter
IF Igs_Ad_Gen_010.ADMP_INS_AAL(
p_person_id,
p_admission_appl_number,
'OUTCOME-LT',
p_admission_cat,
v_s_admission_process_type,
v_message_name) = FALSE THEN
-- Set variable to indicate that the letter could not be inserted
v_offer_letter_ins := FALSE;
p_insert_outcome_letter_ind IN VARCHAR2,
p_pre_enrol_ind IN VARCHAR2 ,
p_course_cd OUT NOCOPY VARCHAR2 ,
p_tac_course_match_ind OUT NOCOPY VARCHAR2 ,
p_return_type OUT NOCOPY VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN IS
BEGIN -- admp_ins_tac_course
-- This process insert an admission COURSE application from a
-- TAC offer load process.
DECLARE
e_resource_busy EXCEPTION;
SELECT COUNT(*),
acai.admission_appl_number,
acai.nominated_course_cd,
acai.sequence_number,
acai.hecs_payment_option
FROM
IGS_AD_APPL aa,
IGS_AD_PS_APPL_INST acai
WHERE
aa.person_id = acai.person_id AND
aa.admission_appl_number = acai.admission_appl_number AND
acai.person_id = p_person_id AND
acai.nominated_course_cd = cp_course_cd AND
aa.acad_cal_type = p_acad_cal_type AND
aa.acad_ci_sequence_number = p_acad_ci_sequence_number AND
aa.adm_cal_type = p_adm_cal_type AND
aa.adm_ci_sequence_number = p_adm_ci_sequence_number AND
aa.tac_appl_ind = 'Y'
GROUP BY
acai.admission_appl_number,
acai.nominated_course_cd,
acai.sequence_number,
acai.hecs_payment_option;
SELECT ROWID, acai.*
FROM IGS_AD_PS_APPL_INST acai
WHERE
acai.person_id = p_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 hecs_payment_option NOWAIT;
p_insert_outcome_letter_ind IS NULL OR
p_pre_enrol_ind IS NULL THEN
p_message_name := 'IGS_AD_PARAMETERS_NOT_SUFFICI';
-- Validate that a matching admission COURSE has not already been inserted for
-- this IGS_PE_PERSON via the TAC process in this admission period (this may be a
-- result of different payment options)
-------------------------------------------------------------------------------
OPEN c_aa_acai (v_course_cd);
-- Update existing HECS payment option, this should only be specified
-- when forcing payment with an offer.
BEGIN
OPEN c_upd_acai (
v_acai_admission_appl_number,
v_acai_nominated_course_cd,
v_acai_sequence_number);
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 => 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 => NULL,
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_MODE => 'R',
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Enh Bug#1964478 added three parameters
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Enh Bug#1964478 added three parameters
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Enh Bug#1964478 added three parameters
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_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
);
-- Insert admission application
----------------------------------
SAVEPOINT sp_save_point1;
-- Insert admission COURSE application
----------------------------------------
IF Igs_Ad_Prc_Tac_Offer.admp_ins_adm_crs_app(
p_person_id,
v_admission_appl_number,
v_course_cd,
p_basis_for_admission_type,
p_admission_cd,
'N', -- (request for reconsideration indicator)
'N', -- (request for advanced standing indicator)
v_message_name) = FALSE THEN
ROLLBACK TO sp_save_point1;
-- Insert admission COURSE application instance
IF Igs_Ad_Prc_Tac_Offer.admp_ins_tac_acai (
p_person_id,
v_admission_appl_number,
p_acad_cal_type,
p_acad_ci_sequence_number,
p_adm_cal_type,
p_adm_ci_sequence_number,
v_admission_cat,
p_appl_dt,
v_adm_fee_status,
p_preference_number,
SYSDATE, -- offer date
NULL, -- offer response date
v_course_cd,
v_version_number,
v_location_cd,
v_attendance_mode,
v_attendance_type,
v_unit_set_cd,
v_us_version_number,
p_fee_cat,
v_hecs_payment_option,
p_correspondence_cat,
p_enrolment_cat,
p_insert_outcome_letter_ind,
p_pre_enrol_ind,
v_return_type,
v_message_name) = FALSE THEN
IF v_return_type = 'E' THEN
ROLLBACK TO sp_save_point1;
p_inserted_ind OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN IS
BEGIN --admp_ins_tert_edu
-- This function inserts a new IGS_AD_TER_EDU record.
DECLARE
CURSOR c_ins IS
SELECT institution_cd,
local_institution_ind
FROM IGS_OR_INSTITUTION
WHERE govt_institution_cd = p_institution_cd OR
institution_cd = p_institution_cd;
SELECT DISTINCT(language_cd) language_cd
FROM IGS_PE_LANGUAGE_CD
WHERE govt_language_cd = cp_language_cd OR
language_cd = cp_language_cd;
SELECT DISTINCT(field_of_study) field_of_study
FROM IGS_PS_FLD_OF_STUDY
WHERE govt_field_of_study = p_field_of_study OR
field_of_study = p_field_of_study;
SELECT DISTINCT(country_cd) country_cd
FROM IGS_PE_COUNTRY_CD
WHERE govt_country_cd = p_country_cd OR
country_cd = p_country_cd;
SELECT DISTINCT(honours_level) honours_level
FROM IGS_GR_HONOURS_LEVEL
WHERE ( govt_honours_level = p_honours_level OR
honours_level = p_honours_level ) AND
closed_ind = 'N';
v_inserted_ind VARCHAR2(1) DEFAULT 'N';
SELECT IGS_AD_TER_EDU_SEQ_NUM_S.NEXTVAL FROM DUAL;
p_inserted_ind := 'N';
-- Do NOT insert tertiary education details,
-- these are available elsewhere in the database
RETURN TRUE;
Igs_Ad_Ter_Edu_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Sequence_Number => lv_NextVal,
X_Tertiary_Edu_Lvl_Comp => v_tertiary_edu_lvl_comp,
X_Exclusion_Ind => p_exclusion_ind,
X_Institution_Cd => v_institution_cd,
X_Institution_Name => v_institution_name,
X_Enrolment_First_Yr => p_enrolment_first_yr,
X_Enrolment_Latest_Yr => v_enrolment_latest_yr,
X_Course_Cd => p_course_cd,
X_Course_Title => p_course_title,
X_Field_Of_Study => v_field_of_study,
X_Language_Component => v_language_component,
X_Student_Id => p_student_id,
X_Equiv_Full_Time_Yrs_Enr => p_equiv_full_time_yrs_enr,
X_Tertiary_Edu_Lvl_Qual => v_tertiary_edu_lvl_qual,
X_Qualification => p_qualification,
X_Honours_Level => v_honours_level,
X_Level_Of_Achievement_Type => p_level_of_achievement_type,
X_Grade_Point_Average => p_grade_point_average,
X_Language_Of_Tuition => v_language_of_tuition,
X_State_Cd => p_state_cd,
X_Country_Cd => v_country_cd,
X_Notes => p_notes
);
p_inserted_ind := 'Y';
SELECT api.api_person_id
FROM IGS_PE_ALT_PERS_ID api
WHERE pe_person_id = p_person_id AND
api_person_id = p_alt_person_id AND
person_id_type = p_alt_person_id_type;
IGS_PE_ALT_PERS_ID_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Pe_Person_Id => p_person_id,
X_Api_Person_Id => p_alt_person_id,
X_Person_Id_Type => p_alt_person_id_type,
X_Start_Dt => TRUNC(p_start_dt),
X_End_Dt => TRUNC(p_end_dt) ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_REGION_CD => NULL
);
SELECT 'x'
FROM IGS_PE_ALT_PERS_ID
WHERE pe_person_id = p_person_id AND
api_person_id = p_tac_person_id AND
person_id_type = p_alt_person_id_type;
-- Get fields for insert of new PERSON details
IF p_sex <> 'M' AND
p_sex <> 'F' THEN
v_new_sex := 'U';
IGS_PE_PERSON_PKG.Insert_Row(
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
X_RETURN_STATUS => l_return_status,
X_ROWID => lv_rowId,
X_PERSON_ID => v_new_person_id,
X_PERSON_NUMBER => v_new_person_number,
X_SURNAME => p_surname,
X_MIDDLE_NAME => NULL,
X_GIVEN_NAMES => p_given_names,
X_SEX => l_sex,
X_TITLE => NULL,
X_STAFF_MEMBER_IND => 'N',
X_DECEASED_IND => 'N',
X_SUFFIX => NULL,
X_PRE_NAME_ADJUNCT => NULL,
X_ARCHIVE_EXCLUSION_IND => 'N',
X_ARCHIVE_DT => NULL,
X_PURGE_EXCLUSION_IND => 'N',
X_PURGE_DT => NULL,
X_DECEASED_DATE => NULL,
X_PROOF_OF_INS => NULL,
X_PROOF_OF_IMMU => NULL,
X_BIRTH_DT => P_BIRTH_DT,
X_SALUTATION => NULL,
X_ORACLE_USERNAME => NULL,
X_PREFERRED_GIVEN_NAME => NULL,
X_EMAIL_ADDR => NULL,
X_LEVEL_OF_QUAL_ID => NULL,
X_MILITARY_SERVICE_REG=> NULL,
X_VETERAN=> NULL,
X_HZ_PARTIES_OVN => l_object_version_number,
X_ATTRIBUTE_CATEGORY=> NULL,
X_ATTRIBUTE1=> NULL,
X_ATTRIBUTE2=> NULL,
X_ATTRIBUTE3=> NULL,
X_ATTRIBUTE4=> NULL,
X_ATTRIBUTE5=> NULL,
X_ATTRIBUTE6=> NULL,
X_ATTRIBUTE7=> NULL,
X_ATTRIBUTE8=> NULL,
X_ATTRIBUTE9=> NULL,
X_ATTRIBUTE10=> NULL,
X_ATTRIBUTE11=> NULL,
X_ATTRIBUTE12=> NULL,
X_ATTRIBUTE13=> NULL,
X_ATTRIBUTE14=> NULL,
X_ATTRIBUTE15=> NULL,
X_ATTRIBUTE16=> NULL,
X_ATTRIBUTE17=> NULL,
X_ATTRIBUTE18=> NULL,
X_ATTRIBUTE19=> NULL,
X_ATTRIBUTE20=> NULL,
X_PERSON_ID_TYPE=> NULL,
X_API_PERSON_ID=> NULL,
X_ATTRIBUTE21=> NULL,
X_ATTRIBUTE22=> NULL,
X_ATTRIBUTE23=> NULL,
X_ATTRIBUTE24=> NULL
);
END IF; -- insert of new IGS_PE_PERSON
--Procedure inserts a new PERSON address record
DECLARE
CURSOR c_pa IS
-- SELECT pa.start_dt
SELECT pa.*
FROM IGS_PE_PERSON_ADDR_V pa
WHERE pa.person_id = p_person_id AND
pa.end_dt IS NULL AND
pa.status = 'A' AND
pa.addr_type IN ( SELECT adt.addr_type
FROM IGS_PE_PERSON_ADDR adt
WHERE adt.correspondence_ind = 'Y');
Also, the update will not take place since l_location_id is null */
v_pa_rec c_pa%ROWTYPE;
l_LAST_UPDATE_DATE DATE;
IGS_PE_PERSON_ADDR_PKG.Update_Row(
P_ACTION => 'U',
P_ROWID => lv_RowId,
P_LOCATION_ID => l_location_Id, -- This is wrong: location_id should not be null
P_START_DT => Rec_IGS_PE_PERSON_Addr.Start_Dt,
P_END_DT => TRUNC(p_start_dt - 1),
P_COUNTRY => Rec_IGS_PE_PERSON_Addr.COUNTRY,
P_ADDRESS_STYLE => NULL,
P_ADDR_LINE_1 => Rec_IGS_PE_PERSON_Addr.Addr_Line_1,
P_ADDR_LINE_2=> Rec_IGS_PE_PERSON_Addr.Addr_Line_2,
P_ADDR_LINE_3 => Rec_IGS_PE_PERSON_Addr.Addr_Line_3,
P_ADDR_LINE_4 => Rec_IGS_PE_PERSON_Addr.Addr_Line_4,
P_DATE_LAST_VERIFIED => Rec_IGS_PE_PERSON_Addr.DATE_LAST_VERIFIED,
P_CORRESPONDENCE => Rec_IGS_PE_PERSON_Addr.CORRESPONDENCE_ind,
P_CITY => Rec_IGS_PE_PERSON_Addr.CITY,
P_STATE => Rec_IGS_PE_PERSON_Addr.STATE,
P_PROVINCE => Rec_IGS_PE_PERSON_Addr.PROVINCE,
P_COUNTY => Rec_IGS_PE_PERSON_Addr.COUNTY,
P_POSTAL_CODE => Rec_IGS_PE_PERSON_Addr.postal_code,
P_ADDRESS_LINES_PHONETIC => NULL,
P_DELIVERY_POINT_CODE => Rec_IGS_PE_PERSON_Addr.DELIVERY_POINT_CODE,
P_OTHER_DETAILS_1 => Rec_IGS_PE_PERSON_Addr.other_details_1,
P_OTHER_DETAILS_2 => Rec_IGS_PE_PERSON_Addr.other_details_2,
P_OTHER_DETAILS_3 => Rec_IGS_PE_PERSON_Addr.other_details_3,
L_RETURN_STATUS => l_return_status,
L_MSG_DATA => l_msg_data,
P_PARTY_ID => Rec_IGS_PE_PERSON_Addr.Person_Id,
P_PARTY_SITE_ID => l_party_site_id,
P_PARTY_TYPE => l_party_type,
P_LAST_UPDATE_DATE => l_last_update_date,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => Rec_IGS_PE_PERSON_Addr.status
);
IGS_PE_PERSON_ADDR_PKG.Insert_Row(
P_ACTION => 'I',
P_ROWID => lv_RowId,
P_LOCATION_ID => l_location_Id,
P_START_DT => NULL,
P_END_DT => TRUNC(p_start_dt - 1),
P_COUNTRY => Rec_IGS_PE_PERSON_Addr.COUNTRY,
P_ADDRESS_STYLE => NULL,
P_ADDR_LINE_1 => P_Addr_Line_1,
P_ADDR_LINE_2=> P_Addr_Line_2,
P_ADDR_LINE_3 => P_Addr_Line_3,
P_ADDR_LINE_4 => P_Addr_Line_4,
P_DATE_LAST_VERIFIED => Rec_IGS_PE_PERSON_Addr.DATE_LAST_VERIFIED,
P_CORRESPONDENCE => Rec_IGS_PE_PERSON_Addr.CORRESPONDENCE_ind,
P_CITY => Rec_IGS_PE_PERSON_Addr.CITY,
P_STATE => Rec_IGS_PE_PERSON_Addr.STATE,
P_PROVINCE => Rec_IGS_PE_PERSON_Addr.PROVINCE,
P_COUNTY => Rec_IGS_PE_PERSON_Addr.COUNTY,
P_POSTAL_CODE => Rec_IGS_PE_PERSON_Addr.postal_code,
P_ADDRESS_LINES_PHONETIC => NULL,
P_DELIVERY_POINT_CODE => Rec_IGS_PE_PERSON_Addr.DELIVERY_POINT_CODE,
P_OTHER_DETAILS_1 => Rec_IGS_PE_PERSON_Addr.other_details_1,
P_OTHER_DETAILS_2 => Rec_IGS_PE_PERSON_Addr.other_details_2,
P_OTHER_DETAILS_3 => Rec_IGS_PE_PERSON_Addr.other_details_3,
L_RETURN_STATUS => l_return_status,
L_MSG_DATA => l_msg_data,
P_PARTY_ID => Rec_IGS_PE_PERSON_Addr.Person_Id,
P_PARTY_SITE_ID => l_party_site_id,
P_PARTY_TYPE => l_party_type,
p_last_update_date => l_last_update_date,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => Rec_IGS_PE_PERSON_Addr.status
);
SELECT apcs.s_admission_step_type,
step_type_restriction_num
FROM IGS_AD_PRCS_CAT_STEP apcs
WHERE apcs.admission_cat = cp_admission_cat AND
apcs.s_admission_process_type = 'COURSE' AND
apcs.step_group_type <> 'TRACK'; --2402377
-- Insert the record after all validation has been performed
l_org_id := igs_ge_gen_003.get_org_id;
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_adm_appl_number,
X_Nominated_Course_Cd => p_nominated_course_cd,
X_Transfer_Course_Cd => NULL,
X_Basis_For_Admission_Type => p_basis_for_admission_type,
X_Admission_Cd => p_admission_cd,
X_Course_Rank_Set => NULL,
X_Course_Rank_Schedule => NULL,
X_Req_For_Reconsideration_Ind => p_req_for_reconsideration_ind,
X_Req_For_Adv_Standing_Ind => p_req_for_adv_standing_ind,
X_Org_Id => l_org_id
);