[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER,
x_ss_application_id IN VARCHAR2,
x_ss_pwd IN VARCHAR2 ,
X_AUTHORIZED_DT IN DATE,
X_AUTHORIZING_PERS_ID IN NUMBER,
x_entry_status IN NUMBER,
x_entry_level IN NUMBER,
x_sch_apl_to_id IN NUMBER,
x_idx_calc_date IN DATE,
x_waitlist_status IN VARCHAR2,
x_attribute21 IN VARCHAR2,
x_attribute22 IN VARCHAR2,
x_attribute23 IN VARCHAR2,
x_attribute24 IN VARCHAR2,
x_attribute25 IN VARCHAR2,
x_attribute26 IN VARCHAR2,
x_attribute27 IN VARCHAR2,
x_attribute28 IN VARCHAR2,
x_attribute29 IN VARCHAR2,
x_attribute30 IN VARCHAR2,
x_attribute31 IN VARCHAR2,
x_attribute32 IN VARCHAR2,
x_attribute33 IN VARCHAR2,
x_attribute34 IN VARCHAR2,
x_attribute35 IN VARCHAR2,
x_attribute36 IN VARCHAR2,
x_attribute37 IN VARCHAR2,
x_attribute38 IN VARCHAR2,
x_attribute39 IN VARCHAR2,
x_attribute40 IN VARCHAR2,
x_fut_acad_cal_type IN VARCHAR2,
x_fut_acad_ci_sequence_number IN NUMBER ,
x_fut_adm_cal_type IN VARCHAR2,
x_fut_adm_ci_sequence_number IN NUMBER ,
x_prev_term_adm_appl_number IN NUMBER ,
x_prev_term_sequence_number IN NUMBER ,
x_fut_term_adm_appl_number IN NUMBER ,
x_fut_term_sequence_number IN NUMBER ,
x_def_acad_cal_type IN VARCHAR2,
x_def_acad_ci_sequence_num IN NUMBER ,
x_def_prev_term_adm_appl_num IN NUMBER ,
x_def_prev_appl_sequence_num IN NUMBER ,
x_def_term_adm_appl_num IN NUMBER ,
x_def_appl_sequence_num IN NUMBER ,
x_appl_inst_status IN VARCHAR2, --arvsrini igsm
x_ais_reason IN VARCHAR2,
x_decline_ofr_reason IN VARCHAR2
) IS
/*************************************************************
Created By : nsinha
Date Created By : Jul 30, 2001
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
smadathi 12-feb-2002 Bug 2217104. Added new columns as mentioned against DLD
nsinha Jul 30, 2001 Bug enh no : 1905651 changes.
Added entry_status, entry_level and sch_apl_to_id
to the procedures
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_AD_PS_APPL_INST_ALL
WHERE rowid = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Close cur_old_ref_values;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
IGS_AD_GEN_001.SET_TOKEN('From Table IGS_AD_PS_APPL_INST_ALL P_action NOT IN INSERT or VALiDATE_INSERT' );
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date;
new_references.last_updated_by := x_last_updated_by;
new_references.last_update_login := x_last_update_login;
PROCEDURE BeforeRowInsertUpdateDelete1(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) IS
v_message_name VARCHAR2(30);
v_update_non_enrol_detail_ind VARCHAR2(1) ;
v_p_inserting_ind VARCHAR2(1) ;
SELECT transfer_course_cd
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;
SELECT s_admission_step_type,
step_type_restriction_num
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'; --2402377
SELECT pti.rowid row_id
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types ppt
WHERE 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.sequence_number = cp_sequence_number
AND ppt.person_type_code = pti.person_type_code
AND ppt.system_type = 'APPLICANT';
SELECT appl_dt FROM igs_ad_appl
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number;
SELECT admprd.single_response_flag
FROM igs_ad_prd_ad_prc_ca admprd,
igs_ad_appl_all appl,
igs_ad_ps_appl_inst_all aplinst
WHERE appl.person_id = new_references.person_id
AND appl.admission_appl_number = new_references.admission_appl_number
AND appl.person_id = aplinst.person_id
AND appl.admission_appl_number = aplinst.admission_appl_number
AND admprd.adm_cal_type = NVL(aplinst.adm_cal_type,appl.adm_cal_type)
AND admprd.adm_ci_sequence_number = NVL(aplinst.adm_ci_sequence_number,appl.adm_ci_sequence_number)
AND admprd.admission_cat = p_admission_cat
AND admprd.s_admission_process_type = p_s_admission_process_type;
SELECT distinct appl.application_id, aplinst.nominated_course_cd
FROM igs_ad_appl_all appl,
igs_ad_ps_appl_inst aplinst,
igs_ad_prd_ad_prc_ca admprd
WHERE appl.person_id = aplinst.person_id
AND appl.admission_appl_number = aplinst.admission_appl_number
AND appl.person_id = new_references.person_id
AND igs_ad_gen_009.admp_get_sys_aors(aplinst.adm_offer_resp_status) = 'ACCEPTED'
AND admprd.adm_cal_type = NVL(aplinst.adm_cal_type,appl.adm_cal_type)
AND admprd.adm_ci_sequence_number = NVL(aplinst.adm_ci_sequence_number,appl.adm_ci_sequence_number)
AND admprd.admission_cat = appl.admission_cat
AND admprd.s_admission_process_type = appl.s_admission_process_type
AND admprd.single_response_flag = 'Y';
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = p_cal_type
AND sequence_number = p_sequence_number;
SELECT req_for_reconsideration_ind
FROM IGS_AD_PS_APPL_ALL apl
WHERE apl.person_id = new_references.person_id
AND apl.admission_appl_number = new_references.admission_appl_number
AND apl.nominated_course_cd = new_references.nominated_course_cd ;
SELECT 1
FROM IGS_LOOKUP_VALUES lkupv
WHERE lookup_type = 'IGS_AD_DECL_OFR_REAS'
AND lkupv.CLOSED_IND <> 'N'
AND LOOKUP_CODE = cp_lookup_code;
v_p_inserting_ind := 'N';
v_update_non_enrol_detail_ind := 'N';
IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) THEN
OPEN c_appl_dt(p_person_id => new_references.person_id,
p_admission_appl_number => new_references.admission_appl_number);
IF NVL(p_inserting,FALSE) THEN
v_p_inserting_ind := 'Y';
IF IGS_AD_VAL_ACAI.admp_val_acai_update (
v_adm_appl_status,
new_references.person_id,
new_references.admission_appl_number,
new_references.nominated_course_cd,
new_references.sequence_number,
v_message_name,
v_update_non_enrol_detail_ind) = FALSE THEN
IF (IGS_AD_GEN_007.ADMP_GET_SAAS(old_references.appl_inst_status)= cst_withdrawn
AND new_references.appl_inst_status IS NULL) THEN -- resubmitting application instance
-- arvsrini igsm
NULL;
NULL; ---need to set the outcomes to pending, let the records get updated.
IF v_update_non_enrol_detail_ind = 'Y' THEN
IF NVL(old_references.adm_cal_type, '-1') <> NVL(new_references.adm_cal_type, '-1') OR
NVL(old_references.adm_ci_sequence_number, -1) <>
NVL(new_references.adm_ci_sequence_number, -1) OR
old_references.course_cd <>
new_references.course_cd OR
old_references.crv_version_number <>
new_references.crv_version_number OR
NVL(old_references.location_cd, '-1') <>
NVL(new_references.location_cd, '-1') OR
NVL(old_references.attendance_mode, '-1') <>
NVL(new_references.attendance_mode, '-1') OR
NVL(old_references.attendance_type, '-1') <>
NVL(new_references.attendance_type, '-1') OR
NVL(old_references.unit_set_cd, '-1') <>
NVL(new_references.unit_set_cd, '-1') OR
NVL(old_references.us_version_number, -1) <>
NVL(new_references.us_version_number, -1) OR
NVL(old_references.preference_number, -1) <>
NVL(new_references.preference_number, -1) OR
NVL(TRUNC(old_references.prpsd_commencement_dt), IGS_GE_DATE.IGSDATE('1900/01/01')) <>
NVL(new_references.prpsd_commencement_dt,
IGS_GE_DATE.IGSDATE('1900/01/01')) OR
NVL(old_references.fee_cat, '-1') <>
NVL(new_references.fee_cat, '-1') OR
NVL(old_references.hecs_payment_option, '-1') <>
NVL(new_references.hecs_payment_option, '-1') OR
NVL(old_references.expected_completion_yr, -1) <>
NVL(new_references.expected_completion_yr, -1) OR
NVL(old_references.expected_completion_perd, '-1') <>
NVL(new_references.expected_completion_perd, '-1') OR
NVL(old_references.correspondence_cat, '-1') <>
NVL(new_references.correspondence_cat, '-1') OR
NVL(old_references.enrolment_cat, '-1') <>
NVL(new_references.enrolment_cat, '-1') THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
OR check_non_updateable_list() THEN -- tried to update the fields which are not supposed to be updated in proced phase
IF IGS_AD_GEN_007.ADMP_GET_SAAS(old_references.appl_inst_status) = cst_withdrawn THEN
FND_MESSAGE.SET_NAME('IGS','IGS_AD_APPL_INST_WITHD');
END IF; -- end if of igs_admp_val_acai_update
IF NVL(p_inserting,FALSE) THEN
-- Validate insert of a preference.
IF v_pref_allowed_ind = 'Y' THEN
IF IGS_AD_VAL_ACAI.admp_val_chg_of_pref (
v_adm_cal_type,
v_adm_ci_sequence_number,
v_admission_cat,
v_s_admission_process_type,
new_references.course_cd,
new_references.crv_version_number,
v_acad_cal_type,
new_references.location_cd,
new_references.attendance_mode,
new_references.attendance_type,
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF IGS_AD_VAL_ACAI_STATUS.admp_val_aos_update (
new_references.person_id,
new_references.admission_appl_number,
new_references.adm_outcome_status,
NVL(old_references.adm_outcome_status, IGS_AD_GEN_009.ADMP_GET_SYS_AOS('PENDING')),
v_message_name,
v_return_type) = FALSE THEN
IF NVL(v_return_type, '-1') = cst_error THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
END IF; -- p_inserting or p_updating
-- Validate delete of the admission IGS_PS_COURSE application instance record.
IF IGS_AD_VAL_ACAI.admp_val_acai_delete (
old_references.person_id,
old_references.admission_appl_number,
old_references.adm_outcome_status,
v_message_name,
v_return_type) = FALSE THEN
IF NVL(v_return_type, '-1') = cst_error THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
igs_pe_typ_instances_pkg.delete_row(x_rowid => c_pti_rec.row_id);
END BeforeRowInsertUpdateDelete1;
PROCEDURE AfterRowInsert1(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) AS
CURSOR c_pti (cp_person_id igs_pe_person.person_id%TYPE) IS
SELECT pti.rowid row_id,pti.*, lv.lookup_code lv_end_method
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types ppt,
igs_lookup_values lv
WHERE pti.person_id = cp_person_id
AND ppt.person_type_code = pti.person_type_code
AND ppt.system_type = 'PROSPECT'
AND pti.end_date IS NULL
AND lv.lookup_type = 'PERSON_TYPE_END_METHOD'
AND lv.lookup_code = 'CREATE_APPLICANT'
AND lv.closed_ind = 'N';
IF NVL(p_inserting,FALSE) THEN
FOR c_pti_rec IN c_pti (new_references.person_id)
LOOP
Igs_Pe_Typ_Instances_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => c_pti_rec.Row_Id,
X_Type_Instance_Id => c_pti_rec.Type_Instance_Id,
X_Person_Type_Code => c_pti_rec.Person_Type_Code,
X_Person_Id => c_pti_rec.Person_Id,
X_Course_Cd => c_pti_rec.Course_Cd,
X_CC_Version_Number => c_pti_rec.CC_Version_Number,
X_Funnel_Status => c_pti_rec.Funnel_Status,
X_Admission_Appl_Number => c_pti_rec.Admission_Appl_Number,
X_Nominated_Course_Cd => c_pti_rec.Nominated_Course_Cd,
X_NCC_Version_Number => c_pti_rec.NCC_Version_Number,
X_Sequence_Number => c_pti_rec.Sequence_Number,
X_Start_Date => c_pti_rec.Start_Date,
X_End_Date => TRUNC(SYSDATE),
X_Create_Method => c_pti_rec.Create_Method,
X_Ended_By => fnd_global.user_id,
X_End_Method => c_pti_rec.lv_end_method,
X_Emplmnt_Category_code => c_pti_rec.emplmnt_category_code
);
END AfterRowInsert1;
PROCEDURE AfterRowUpdate1(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) AS
CURSOR c_pti (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
SELECT pti.rowid row_id,pti.*
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types ppt
WHERE 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.sequence_number = cp_sequence_number
AND ppt.person_type_code = pti.person_type_code
AND ppt.system_type = 'APPLICANT'
AND pti.end_date IS NULL;
SELECT pti.rowid row_id,pti.*
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types ppt
WHERE 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.sequence_number = cp_sequence_number
AND ppt.person_type_code = pti.person_type_code
AND ppt.system_type = 'APPLICANT'
AND pti.end_date IS NOT NULL
AND pti.End_Method = cp_end_method ;
SELECT lv.lookup_code
FROM igs_lookup_values lv
WHERE lv.lookup_type = 'PERSON_TYPE_END_METHOD'
AND lv.lookup_code = 'DELETE_APPLICATION'
AND lv.closed_ind = 'N';
Igs_Pe_Typ_Instances_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => c_pti_rec.Row_Id,
X_Type_Instance_Id => c_pti_rec.Type_Instance_Id,
X_Person_Type_Code => c_pti_rec.Person_Type_Code,
X_Person_Id => c_pti_rec.Person_Id,
X_Course_Cd => c_pti_rec.Course_Cd,
X_CC_Version_Number => c_pti_rec.CC_Version_Number,
X_Funnel_Status => c_pti_rec.Funnel_Status,
X_Admission_Appl_Number => c_pti_rec.Admission_Appl_Number,
X_Nominated_Course_Cd => c_pti_rec.Nominated_Course_Cd,
X_NCC_Version_Number => c_pti_rec.NCC_Version_Number,
X_Sequence_Number => c_pti_rec.Sequence_Number,
X_Start_Date => c_pti_rec.Start_Date,
X_End_Date => TRUNC(SYSDATE),
X_Create_Method => c_pti_rec.Create_Method,
X_Ended_By => fnd_global.user_id,
X_End_Method => lv_end_method,
X_Emplmnt_Category_code => c_pti_rec.emplmnt_category_code
);
Igs_Pe_Typ_Instances_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => c_ptyp_inst_rec.Row_Id,
X_Type_Instance_Id => c_ptyp_inst_rec.Type_Instance_Id,
X_Person_Type_Code => c_ptyp_inst_rec.Person_Type_Code,
X_Person_Id => c_ptyp_inst_rec.Person_Id,
X_Course_Cd => c_ptyp_inst_rec.Course_Cd,
X_CC_Version_Number => c_ptyp_inst_rec.CC_Version_Number,
X_Funnel_Status => c_ptyp_inst_rec.Funnel_Status,
X_Admission_Appl_Number => c_ptyp_inst_rec.Admission_Appl_Number,
X_Nominated_Course_Cd => c_ptyp_inst_rec.Nominated_Course_Cd,
X_NCC_Version_Number => c_ptyp_inst_rec.NCC_Version_Number,
X_Sequence_Number => c_ptyp_inst_rec.Sequence_Number,
X_Start_Date => c_ptyp_inst_rec.Start_Date,
X_End_Date => NULL,
X_Create_Method => c_ptyp_inst_rec.Create_Method,
X_Ended_By => NULL,
X_End_Method => NULL,
X_Emplmnt_Category_code => c_ptyp_inst_rec.emplmnt_category_code
);
END AfterRowUpdate1;
PROCEDURE AfterRowInsertUpdate2(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) IS
CURSOR c_apcs (
cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
cp_s_admission_process_type
IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
SELECT s_admission_step_type,
step_type_restriction_num
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'; --2402377
SELECT adm_appl_status
FROM igs_ad_appl
WHERE person_id = cp_person_id
AND admission_appl_number= cp_admission_appl_number;
IF NVL(p_inserting,FALSE) THEN
-- Save the rowid of the current row.
v_row_validated := TRUE;
IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) THEN
--
-- Get admission application details required for validation.
--
IGS_AD_GEN_002.ADMP_GET_AA_DTL(
new_references.person_id,
new_references.admission_appl_number,
v_admission_cat,
v_s_admission_process_type,
v_acad_cal_type,
v_aa_acad_ci_sequence_number,
v_aa_adm_cal_type,
v_aa_adm_ci_sequence_number,
v_appl_dt,
v_adm_appl_status,
v_adm_fee_status);
-- Bug no 2462198 (D) 2566987 (P) do the validation of duplicate while doing the update also
-- by rrengara on 13-SEP-2002
IF NVL(p_inserting,FALSE) THEN
--Should be called only in case of an insert
-- Validate insert of admission course application instance.
IF IGS_AD_VAL_ACAI.admp_val_acai_insert (
new_references.person_id,
new_references.admission_appl_number,
new_references.nominated_course_cd,
new_references.sequence_number,
new_references.course_cd,
new_references.location_cd,
new_references.attendance_mode,
new_references.attendance_type,
new_references.unit_set_cd,
new_references.us_version_number,
v_adm_cal_type,
v_adm_ci_sequence_number,
v_s_admission_process_type,
v_pref_limit,
FALSE, -- Validate AA Only
v_message_name,
v_return_type) = FALSE THEN
IF NVL(v_return_type, '-1') = cst_error THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) THEN
--
-- Validate admission outcome status.
--
IF IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aos (
new_references.person_id,
new_references.admission_appl_number,
new_references.nominated_course_cd,
new_references.sequence_number,
new_references.course_cd,
new_references.crv_version_number,
new_references.location_cd,
new_references.attendance_mode,
new_references.attendance_type,
new_references.unit_set_cd,
new_references.us_version_number,
v_acad_cal_type,
v_acad_ci_sequence_number,
v_adm_cal_type,
v_adm_ci_sequence_number,
v_admission_cat,
v_s_admission_process_type,
v_appl_dt,
new_references.fee_cat,
new_references.correspondence_cat,
new_references.enrolment_cat,
new_references.adm_outcome_status,
new_references.adm_outcome_status, -- Old
new_references.adm_doc_status,
v_adm_fee_status,
new_references.late_adm_fee_status,
new_references.adm_cndtnl_offer_status,
new_references.adm_entry_qual_status,
new_references.adm_offer_resp_status,
new_references.adm_offer_resp_status, -- Old
new_references.adm_outcome_status_auth_dt,
v_set_outcome_allowed_ind,
v_cond_offer_ass_allowed_ind,
v_cond_offer_fee_allowed_ind,
v_cond_offer_doc_allowed_ind,
v_late_appl_allowed_ind,
v_fees_required_ind,
v_mult_offer_allowed_ind,
v_multi_offer_limit,
v_pref_allowed_ind,
v_unit_set_appl_ind,
v_check_person_encumb,
v_check_course_encumb,
'TRG_AS', -- Called From.
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
END AfterRowInsertUpdate2;
PROCEDURE AfterRowUpdateDelete3(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) IS
v_message_name VARCHAR2(30);
SELECT adm_appl_status
FROM IGS_AD_APPL
WHERE person_id = cp_person_id AND
admission_appl_number = cp_admission_appl_number;
new_references.last_updated_by,
old_references.last_updated_by,
new_references.last_update_date,
old_references.last_update_date,
new_references.applicant_acptnce_cndtn,
old_references.applicant_acptnce_cndtn,
new_references.cndtnl_offer_cndtn,
old_references.cndtnl_offer_cndtn,
new_references.appl_inst_status, --arvsrini igsm
old_references.appl_inst_status,
new_references.DECISION_DATE, -- begin APADEGAL adtd001 igs.m
old_references.DECISION_DATE,
new_references.DECISION_MAKE_ID,
old_references.DECISION_MAKE_ID,
new_references.DECISION_REASON_ID,
old_references.DECISION_REASON_ID,
new_references.PENDING_REASON_ID,
old_references.PENDING_REASON_ID,
new_references.WAITLIST_STATUS,
old_references.WAITLIST_STATUS,
new_references.WAITLIST_RANK,
old_references.WAITLIST_RANK ,
new_references.FUTURE_ACAD_CAL_TYPE,
old_references.FUTURE_ACAD_CAL_TYPE,
new_references.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
old_references.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
new_references.FUTURE_ADM_CAL_TYPE,
old_references.FUTURE_ADM_CAL_TYPE,
new_references.FUTURE_ADM_CI_SEQUENCE_NUMBER,
old_references.FUTURE_ADM_CI_SEQUENCE_NUMBER,
new_references.DEF_ACAD_CAL_TYPE,
old_references.DEF_ACAD_CAL_TYPE,
new_references.def_acad_ci_sequence_num,
old_references.DEF_ACAD_CI_SEQUENCE_NUM,
new_references.DECLINE_OFR_REASON,
old_references.DECLINE_OFR_REASON -- end APADEGAL adtd001 igs.m
);
UPDATE IGS_AD_APPL
SET adm_appl_status = v_derived_adm_appl_status
WHERE person_id = v_person_id AND
admission_appl_number = v_admission_appl_number;
END AfterRowUpdateDelete3;
SELECT 1 FROM USER_OBJECTS
WHERE OBJECT_NAME = cp_tab
AND object_type = 'PACKAGE BODY';
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd
AND sequence_number = x_sequence_number
FOR UPDATE NOWAIT;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd
AND sequence_number = x_sequence_number;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE pending_reason_id = x_code_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE edu_goal_prior_enroll_id = x_code_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE app_source_id = x_code_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE decision_reason_id = x_code_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE entry_status = x_code_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE entry_level = x_code_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE attent_other_inst_cd = x_institution_cd ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE person_id = x_person_id ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE decision_make_id = x_person_id ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE attendance_mode = x_attendance_mode ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE adm_offer_dfrmnt_status = x_adm_offer_dfrmnt_status ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE adm_offer_resp_status = x_adm_offer_resp_status ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE adm_outcome_status = x_adm_outcome_status ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE attendance_type = x_attendance_type ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE correspondence_cat = x_correspondence_cat ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE (deferred_adm_cal_type = x_cal_type
AND deferred_adm_ci_sequence_num = x_sequence_number)
OR (adm_cal_type = x_cal_type
AND adm_ci_sequence_number = x_sequence_number )
OR (future_acad_cal_type = x_cal_type
AND future_acad_ci_sequence_number = x_sequence_number )
OR (future_adm_cal_type = x_cal_type
AND future_adm_ci_sequence_number = x_sequence_number );
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE course_cd = x_course_cd ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE course_cd = x_course_cd
AND crv_version_number = x_version_number ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE enrolment_cat = x_enrolment_cat ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE fee_cat = x_fee_cat ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE funding_source = x_funding_source ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE hecs_payment_option = x_hecs_payment_option ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE location_cd = x_location_cd ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE ass_tracking_id = x_tracking_id
OR deferred_tracking_id = x_tracking_id ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE unit_set_cd = x_unit_set_cd
AND us_version_number = x_version_number ;
SELECT ainst.rowid
FROM IGS_AD_PS_APPL_INST_ALL ainst, IGS_AD_APPL_ALL apl
WHERE ainst.person_id = apl.person_id
AND ainst.admission_appl_number = apl.admission_appl_number
AND ainst.unit_set_cd = x_unit_set_cd
AND ainst.us_version_number = x_version_number
AND ainst.course_cd = x_course_cd
AND ainst.crv_version_number = x_crv_version_number
AND apl.acad_cal_type = x_acad_cal_type ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE adm_cndtnl_offer_status = x_adm_cndtnl_offer_status ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE adm_doc_status = x_adm_doc_status ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE adm_entry_qual_status = x_adm_entry_qual_status ;
SELECT rowid
FROM IGS_AD_PS_APPL_INST_ALL
WHERE late_adm_fee_status = x_adm_fee_status ;
SELECT rowid
FROM igs_ad_ps_appl_inst_all
WHERE sch_apl_to_id = x_sch_apl_to_id;
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER,
x_ss_application_id IN VARCHAR2,
x_ss_pwd IN VARCHAR2,
X_AUTHORIZED_DT IN DATE,
X_AUTHORIZING_PERS_ID IN NUMBER,
x_entry_status IN NUMBER,
x_entry_level IN NUMBER,
x_sch_apl_to_id IN NUMBER,
x_idx_calc_date IN DATE,
x_waitlist_status IN VARCHAR2,
x_attribute21 IN VARCHAR2,
x_attribute22 IN VARCHAR2,
x_attribute23 IN VARCHAR2,
x_attribute24 IN VARCHAR2,
x_attribute25 IN VARCHAR2,
x_attribute26 IN VARCHAR2,
x_attribute27 IN VARCHAR2,
x_attribute28 IN VARCHAR2,
x_attribute29 IN VARCHAR2,
x_attribute30 IN VARCHAR2,
x_attribute31 IN VARCHAR2,
x_attribute32 IN VARCHAR2,
x_attribute33 IN VARCHAR2,
x_attribute34 IN VARCHAR2,
x_attribute35 IN VARCHAR2,
x_attribute36 IN VARCHAR2,
x_attribute37 IN VARCHAR2,
x_attribute38 IN VARCHAR2,
x_attribute39 IN VARCHAR2,
x_attribute40 IN VARCHAR2,
x_fut_acad_cal_type IN VARCHAR2,
x_fut_acad_ci_sequence_number IN NUMBER ,
x_fut_adm_cal_type IN VARCHAR2,
x_fut_adm_ci_sequence_number IN NUMBER ,
x_prev_term_adm_appl_number IN NUMBER ,
x_prev_term_sequence_number IN NUMBER ,
x_fut_term_adm_appl_number IN NUMBER ,
x_fut_term_sequence_number IN NUMBER ,
x_def_acad_cal_type IN VARCHAR2,
x_def_acad_ci_sequence_num IN NUMBER ,
x_def_prev_term_adm_appl_num IN NUMBER ,
x_def_prev_appl_sequence_num IN NUMBER ,
x_def_term_adm_appl_num IN NUMBER ,
x_def_appl_sequence_num IN NUMBER ,
x_appl_inst_status IN VARCHAR2, --arvsrini igsm
x_ais_reason IN VARCHAR2,
x_decline_ofr_reason IN VARCHAR2
) IS
/*************************************************************
Created By : nsinha
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
nsinha Jul 30, 2001 Bug enh no : 1905651 changes.
Added entry_status, entry_level and sch_apl_to_id
to the procedures
pbondugu 02-Apr-2003 Validation is added for checking whether application date
is less than adm_outcome_status_auth_dt,offer_dt,idx_calc_date
rghosh 03-apr-2003 Added the code for getting the new offer response status for an UCAS application
bug# 2860860 (UCAS Conditional Offer build)
pbondugu 23-Apr-2003 Validation for checking whether application date
is less than adm_outcome_status_auth_dt,offer_dt,idx_calc_date
is moved to BeforeRowInsertUpdateDelete1
akadam 06-OCT-2003 BUG: 3160184 Removed check on adm_offer_resp_status for System status
of Accepted
***************************************************************/
--rghosh bug# 2860860 (UCAS Conditional Offer build)
-- cursor to fetch the alt_appl_id and choice number for the current Admission Application
CURSOR c_get_appl_details (cp_person_id igs_ad_appl. person_id %TYPE,
cp_admission_appl_number igs_ad_appl. admission_appl_number%TYPE) IS
SELECT alt_appl_id, choice_number,s_admission_process_type
FROM igs_ad_appl
WHERE person_id = cp_person_id AND
admission_appl_number = cp_admission_appl_number;
SELECT req_for_reconsideration_ind
FROM IGS_AD_PS_APPL_ALL apl
WHERE apl.person_id = new_references.person_id
AND apl.admission_appl_number = new_references.admission_appl_number
AND apl.nominated_course_cd = new_references.nominated_course_cd ;
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_ss_application_id,
x_ss_pwd ,
X_AUTHORIZED_DT,
X_AUTHORIZING_PERS_ID,
x_entry_status,
x_entry_level,
x_sch_apl_to_id,
x_idx_calc_date,
x_waitlist_status,
x_attribute21,
x_attribute22,
x_attribute23,
x_attribute24,
x_attribute25,
x_attribute26,
x_attribute27,
x_attribute28,
x_attribute29,
x_attribute30,
x_attribute31,
x_attribute32,
x_attribute33,
x_attribute34,
x_attribute35,
x_attribute36,
x_attribute37,
x_attribute38,
x_attribute39,
x_attribute40,
x_fut_acad_cal_type,
x_fut_acad_ci_sequence_number,
x_fut_adm_cal_type,
x_fut_adm_ci_sequence_number,
x_prev_term_adm_appl_number,
x_prev_term_sequence_number,
x_fut_term_adm_appl_number,
x_fut_term_sequence_number,
x_def_acad_cal_type,
x_def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num,
x_def_term_adm_appl_num,
x_def_appl_sequence_num,
x_appl_inst_status, --arvsrini igsm
x_ais_reason,
x_decline_ofr_reason
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_Pk_For_Validation(
new_references.person_id,
new_references.admission_appl_number,
new_references.nominated_course_cd,
new_references.sequence_number) THEN
Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
ELSIF (p_action = 'UPDATE') THEN
--- begin apadegal ADTD001 RE-OPEN Build igs.m
OPEN cur_reconsider;
AND (NVL(l_is_inst_reconsidered,'N') <> 'Y') -- APADEGAL (IGS.M) - CANCELLED instance can be updated while reconsideration.
THEN
Fnd_Message.Set_name('IGS','IGS_AD_NOT_UPD_CANCEL_APPLINST');
BeforeRowInsertUpdateDelete1 (p_inserting => FALSE, p_updating => TRUE , p_deleting => FALSE);
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
BeforeRowInsertUpdateDelete1 (p_inserting=> FALSE, p_updating => FALSE , p_deleting => TRUE );
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_Pk_For_Validation(
new_references.person_id,
new_references.admission_appl_number,
new_references.nominated_course_cd,
new_references.sequence_number) THEN
Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
IF NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(new_references.adm_outcome_status), 'NONE') <> 'CANCELLED' THEN
Check_Constraints;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
Check_Child_Existance;
rasahoo 01-Sep-2003 Removed the private procedure IGF_UPDATE_DATA And
Removed the call of IGF_UPDATE_DATA as part of the Build
FA 114(Obsoletion of base record history)
smadathi 19-Feb-2002 Bug 2217104. Added code as per ADM012,ADM016,ADM038.
sjalasut 09-oct-01 added code here to calculate the initial and
most recent term of admit
sjadhav jun 15,2001 this procedure is modified to trigger
a Concurrent Request (IGFAPJ10) which
will create a new record in IGF To do table
Veereshwar.Dixit 07-AUG-2000 Added AfterRowUpdate call
kamohan 8/2/02 Bug 2407628 Modified the logic comments whereever neccessary
knag 23-JAN-2003 Modified population of admittance logic and code indents
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_get_init_recent(p_person_id IGS_PE_PERSON.PERSON_ID%TYPE) IS
SELECT psv.init_cal_type, psv.init_sequence_number,
psv.recent_cal_type, psv.recent_sequence_number,
psv.catalog_cal_type, psv.catalog_sequence_number
FROM igs_pe_stat_details psv
WHERE psv.person_id = p_person_id;
SELECT DISTINCT 1
FROM igs_ad_ps_appl_inst
WHERE row_id <> x_rowid
AND person_id = new_references.person_id
AND igs_ad_gen_008.admp_get_saors (adm_offer_resp_status) IN ('ACCEPTED', 'DEFERRAL');
SELECT acad_cal_type, acad_ci_sequence_number
FROM igs_ad_appl
WHERE admission_appl_number = new_references.admission_appl_number
AND person_id = new_references.person_id;
SELECT cr.sub_cal_type, cr.sub_ci_sequence_number
FROM igs_ca_inst_rel cr, igs_ca_type ct
WHERE ct.s_cal_cat ='TEACHING'
AND cr.sub_cal_type = ct.cal_type
AND cr.sup_cal_type = p_acad_cal_type
AND cr.sup_ci_sequence_number = p_acad_ci_sequence_number;
SELECT TRUNC(load_start_dt) load_start_dt, load_cal_type, load_ci_sequence_number
FROM igs_ca_teach_to_load_v ctl
WHERE ctl.teach_cal_type = p_sub_cal_type
AND ctl.teach_ci_sequence_number = p_sub_ci_sequence_number;
SELECT REQ_FOR_RECONSIDERATION_IND
FROM IGS_AD_PS_APPL_ALL
WHERE PERSON_ID = cp_person_id
AND ADMISSION_APPL_NUMBER = cp_adm_application_num
AND NOMINATED_COURSE_CD = cp_nomintaed_course_code;
v_party_last_update_date hz_person_profiles.last_update_date%TYPE;
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
AfterRowInsertUpdate2 ( p_inserting => TRUE,p_updating => FALSE,p_deleting=> FALSE );
AfterRowInsert1 ( p_inserting => TRUE,p_updating => FALSE,p_deleting=> FALSE );
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
IF NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(new_references.adm_outcome_status), 'NONE') <> 'CANCELLED' THEN
AfterRowInsertUpdate2 ( p_inserting=> FALSE,p_updating => TRUE,p_deleting=> FALSE );
AfterRowUpdateDelete3 ( p_inserting=> FALSE,p_updating => TRUE,p_deleting=> FALSE );
AfterRowUpdate1 ( p_inserting => FALSE,p_updating => TRUE,p_deleting=> FALSE );
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to After Delete.
AfterRowUpdateDelete3 ( p_inserting=> FALSE,p_updating => FALSE, p_deleting => TRUE );
p_action IN ('INSERT','UPDATE') THEN
-- This Funtion Gets the System Value of the Offer Response Status
-- changed for person stats bug 5054301/3958556
IF (igs_ad_gen_008.admp_get_saors (new_references.adm_offer_resp_status) = 'ACCEPTED' AND
igs_ad_gen_008.admp_get_saors (old_references.adm_offer_resp_status) <> 'ACCEPTED')
THEN
-- Offer is Accepted
igs_ad_upd_initialise.update_per_stats( new_references.person_id,
new_references.admission_appl_number,
'A'
);
igs_ad_upd_initialise.update_per_stats( new_references.person_id,
new_references.admission_appl_number,
'R'
);
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_ORG_ID in NUMBER,
x_PERSON_ID IN NUMBER,
x_ADMISSION_APPL_NUMBER IN NUMBER,
x_NOMINATED_COURSE_CD IN VARCHAR2,
x_SEQUENCE_NUMBER IN NUMBER,
x_PREDICTED_GPA IN NUMBER,
x_ACADEMIC_INDEX IN VARCHAR2,
x_ADM_CAL_TYPE IN VARCHAR2,
x_APP_FILE_LOCATION IN VARCHAR2,
x_ADM_CI_SEQUENCE_NUMBER IN NUMBER,
x_COURSE_CD IN VARCHAR2,
x_APP_SOURCE_ID IN NUMBER,
x_CRV_VERSION_NUMBER IN NUMBER,
x_WAITLIST_RANK IN VARCHAR2,
x_LOCATION_CD IN VARCHAR2,
x_ATTENT_OTHER_INST_CD IN VARCHAR2,
x_ATTENDANCE_MODE IN VARCHAR2,
x_EDU_GOAL_PRIOR_ENROLL_ID IN NUMBER,
x_ATTENDANCE_TYPE IN VARCHAR2,
x_DECISION_MAKE_ID IN NUMBER,
x_UNIT_SET_CD IN VARCHAR2,
x_DECISION_DATE IN DATE,
x_ATTRIBUTE_CATEGORY IN VARCHAR2,
x_ATTRIBUTE1 IN VARCHAR2,
x_ATTRIBUTE2 IN VARCHAR2,
x_ATTRIBUTE3 IN VARCHAR2,
x_ATTRIBUTE4 IN VARCHAR2,
x_ATTRIBUTE5 IN VARCHAR2,
x_ATTRIBUTE6 IN VARCHAR2,
x_ATTRIBUTE7 IN VARCHAR2,
x_ATTRIBUTE8 IN VARCHAR2,
x_ATTRIBUTE9 IN VARCHAR2,
x_ATTRIBUTE10 IN VARCHAR2,
x_ATTRIBUTE11 IN VARCHAR2,
x_ATTRIBUTE12 IN VARCHAR2,
x_ATTRIBUTE13 IN VARCHAR2,
x_ATTRIBUTE14 IN VARCHAR2,
x_ATTRIBUTE15 IN VARCHAR2,
x_ATTRIBUTE16 IN VARCHAR2,
x_ATTRIBUTE17 IN VARCHAR2,
x_ATTRIBUTE18 IN VARCHAR2,
x_ATTRIBUTE19 IN VARCHAR2,
x_ATTRIBUTE20 IN VARCHAR2,
x_DECISION_REASON_ID IN NUMBER,
x_US_VERSION_NUMBER IN NUMBER,
x_DECISION_NOTES IN VARCHAR2,
x_PENDING_REASON_ID IN NUMBER,
x_PREFERENCE_NUMBER IN NUMBER,
x_ADM_DOC_STATUS IN VARCHAR2,
x_ADM_ENTRY_QUAL_STATUS IN VARCHAR2,
x_DEFICIENCY_IN_PREP IN VARCHAR2,
x_LATE_ADM_FEE_STATUS IN VARCHAR2,
x_SPL_CONSIDER_COMMENTS IN VARCHAR2,
x_APPLY_FOR_FINAID IN VARCHAR2,
x_FINAID_APPLY_DATE IN DATE,
x_ADM_OUTCOME_STATUS IN VARCHAR2,
x_adm_otcm_stat_auth_per_id IN NUMBER,
x_ADM_OUTCOME_STATUS_AUTH_DT IN DATE,
x_ADM_OUTCOME_STATUS_REASON IN VARCHAR2,
x_OFFER_DT IN DATE,
x_OFFER_RESPONSE_DT IN DATE,
x_PRPSD_COMMENCEMENT_DT IN DATE,
x_ADM_CNDTNL_OFFER_STATUS IN VARCHAR2,
x_CNDTNL_OFFER_SATISFIED_DT IN DATE,
x_cndnl_ofr_must_be_stsfd_ind IN VARCHAR2,
x_ADM_OFFER_RESP_STATUS IN VARCHAR2,
x_ACTUAL_RESPONSE_DT IN DATE,
x_ADM_OFFER_DFRMNT_STATUS IN VARCHAR2,
x_DEFERRED_ADM_CAL_TYPE IN VARCHAR2,
x_DEFERRED_ADM_CI_SEQUENCE_NUM IN NUMBER,
x_DEFERRED_TRACKING_ID IN NUMBER,
x_ASS_RANK IN NUMBER,
x_SECONDARY_ASS_RANK IN NUMBER,
x_intr_accept_advice_num IN NUMBER,
x_ASS_TRACKING_ID IN NUMBER,
x_FEE_CAT IN VARCHAR2,
x_HECS_PAYMENT_OPTION IN VARCHAR2,
x_EXPECTED_COMPLETION_YR IN NUMBER,
x_EXPECTED_COMPLETION_PERD IN VARCHAR2,
x_CORRESPONDENCE_CAT IN VARCHAR2,
x_ENROLMENT_CAT IN VARCHAR2,
x_FUNDING_SOURCE IN VARCHAR2,
x_APPLICANT_ACPTNCE_CNDTN IN VARCHAR2,
x_CNDTNL_OFFER_CNDTN IN VARCHAR2,
X_MODE in VARCHAR2,
X_SS_APPLICATION_ID IN VARCHAR2,
X_SS_PWD IN VARCHAR2,
X_AUTHORIZED_DT IN DATE,
X_AUTHORIZING_PERS_ID IN NUMBER,
x_entry_status IN NUMBER,
x_entry_level IN NUMBER,
x_sch_apl_to_id IN NUMBER,
x_idx_calc_date IN DATE,
x_waitlist_status IN VARCHAR2,
x_ATTRIBUTE21 IN VARCHAR2 ,
x_ATTRIBUTE22 IN VARCHAR2,
x_ATTRIBUTE23 IN VARCHAR2,
x_ATTRIBUTE24 IN VARCHAR2,
x_ATTRIBUTE25 IN VARCHAR2,
x_ATTRIBUTE26 IN VARCHAR2,
x_ATTRIBUTE27 IN VARCHAR2,
x_ATTRIBUTE28 IN VARCHAR2,
x_ATTRIBUTE29 IN VARCHAR2,
x_ATTRIBUTE30 IN VARCHAR2,
x_ATTRIBUTE31 IN VARCHAR2,
x_ATTRIBUTE32 IN VARCHAR2,
x_ATTRIBUTE33 IN VARCHAR2,
x_ATTRIBUTE34 IN VARCHAR2,
x_ATTRIBUTE35 IN VARCHAR2,
x_ATTRIBUTE36 IN VARCHAR2,
x_ATTRIBUTE37 IN VARCHAR2,
x_ATTRIBUTE38 IN VARCHAR2,
x_ATTRIBUTE39 IN VARCHAR2,
x_ATTRIBUTE40 IN VARCHAR2,
x_fut_acad_cal_type IN VARCHAR2,
x_fut_acad_ci_sequence_number IN NUMBER ,
x_fut_adm_cal_type IN VARCHAR2,
x_fut_adm_ci_sequence_number IN NUMBER ,
x_prev_term_adm_appl_number IN NUMBER ,
x_prev_term_sequence_number IN NUMBER ,
x_fut_term_adm_appl_number IN NUMBER ,
x_fut_term_sequence_number IN NUMBER ,
x_def_acad_cal_type IN VARCHAR2,
x_def_acad_ci_sequence_num IN NUMBER ,
x_def_prev_term_adm_appl_num IN NUMBER ,
x_def_prev_appl_sequence_num IN NUMBER ,
x_def_term_adm_appl_num IN NUMBER ,
x_def_appl_sequence_num IN NUMBER ,
x_appl_inst_status IN VARCHAR2, --arvsrini igsm
x_ais_reason IN VARCHAR2,
x_decline_ofr_reason IN VARCHAR2
) IS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
cursor C is select ROWID from IGS_AD_PS_APPL_INST_ALL
where PERSON_ID= X_PERSON_ID
and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_PROGRAM_UPDATE_DATE DATE;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
X_PROGRAM_UPDATE_DATE := NULL;
X_PROGRAM_UPDATE_DATE := SYSDATE;
p_action=>'INSERT',
x_rowid=>X_ROWID,
x_org_id => igs_ge_gen_003.get_org_id,
x_person_id=>X_PERSON_ID,
x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_predicted_gpa=>X_PREDICTED_GPA,
x_academic_index=>X_ACADEMIC_INDEX,
x_adm_cal_type=>X_ADM_CAL_TYPE,
x_app_file_location=>X_APP_FILE_LOCATION,
x_adm_ci_sequence_number=>X_ADM_CI_SEQUENCE_NUMBER,
x_course_cd=>X_COURSE_CD,
x_app_source_id=>X_APP_SOURCE_ID,
x_crv_version_number=>X_CRV_VERSION_NUMBER,
x_waitlist_rank=>X_WAITLIST_RANK,
x_location_cd=>X_LOCATION_CD,
x_attent_other_inst_cd=>X_ATTENT_OTHER_INST_CD,
x_attendance_mode=>X_ATTENDANCE_MODE,
x_edu_goal_prior_enroll_id=>X_EDU_GOAL_PRIOR_ENROLL_ID,
x_attendance_type=>X_ATTENDANCE_TYPE,
x_decision_make_id=>X_DECISION_MAKE_ID,
x_unit_set_cd=>X_UNIT_SET_CD,
x_decision_date=>X_DECISION_DATE,
x_attribute_category=>X_ATTRIBUTE_CATEGORY,
x_attribute1=>X_ATTRIBUTE1,
x_attribute2=>X_ATTRIBUTE2,
x_attribute3=>X_ATTRIBUTE3,
x_attribute4=>X_ATTRIBUTE4,
x_attribute5=>X_ATTRIBUTE5,
x_attribute6=>X_ATTRIBUTE6,
x_attribute7=>X_ATTRIBUTE7,
x_attribute8=>X_ATTRIBUTE8,
x_attribute9=>X_ATTRIBUTE9,
x_attribute10=>X_ATTRIBUTE10,
x_attribute11=>X_ATTRIBUTE11,
x_attribute12=>X_ATTRIBUTE12,
x_attribute13=>X_ATTRIBUTE13,
x_attribute14=>X_ATTRIBUTE14,
x_attribute15=>X_ATTRIBUTE15,
x_attribute16=>X_ATTRIBUTE16,
x_attribute17=>X_ATTRIBUTE17,
x_attribute18=>X_ATTRIBUTE18,
x_attribute19=>X_ATTRIBUTE19,
x_attribute20=>X_ATTRIBUTE20,
x_decision_reason_id=>X_DECISION_REASON_ID,
x_us_version_number=>X_US_VERSION_NUMBER,
x_decision_notes=>X_DECISION_NOTES,
x_pending_reason_id=>X_PENDING_REASON_ID,
x_preference_number=>X_PREFERENCE_NUMBER,
x_adm_doc_status=>X_ADM_DOC_STATUS,
x_adm_entry_qual_status=>X_ADM_ENTRY_QUAL_STATUS,
x_deficiency_in_prep=>X_DEFICIENCY_IN_PREP,
x_late_adm_fee_status=>X_LATE_ADM_FEE_STATUS,
x_spl_consider_comments=>X_SPL_CONSIDER_COMMENTS,
x_apply_for_finaid=>X_APPLY_FOR_FINAID,
x_finaid_apply_date=>X_FINAID_APPLY_DATE,
x_adm_outcome_status=>X_ADM_OUTCOME_STATUS,
x_adm_otcm_stat_auth_per_id=>x_adm_otcm_stat_auth_per_id,
x_adm_outcome_status_auth_dt=>X_ADM_OUTCOME_STATUS_AUTH_DT,
x_adm_outcome_status_reason=>X_ADM_OUTCOME_STATUS_REASON,
x_offer_dt=>X_OFFER_DT,
x_offer_response_dt=>X_OFFER_RESPONSE_DT,
x_prpsd_commencement_dt=>X_PRPSD_COMMENCEMENT_DT,
x_adm_cndtnl_offer_status=>X_ADM_CNDTNL_OFFER_STATUS,
x_cndtnl_offer_satisfied_dt=>X_CNDTNL_OFFER_SATISFIED_DT,
x_cndnl_ofr_must_be_stsfd_ind=>NVL(x_cndnl_ofr_must_be_stsfd_ind,'N' ),
x_adm_offer_resp_status=>X_ADM_OFFER_RESP_STATUS,
x_actual_response_dt=>X_ACTUAL_RESPONSE_DT,
x_adm_offer_dfrmnt_status=>X_ADM_OFFER_DFRMNT_STATUS,
x_deferred_adm_cal_type=>X_DEFERRED_ADM_CAL_TYPE,
x_deferred_adm_ci_sequence_num=>X_DEFERRED_ADM_CI_SEQUENCE_NUM,
x_deferred_tracking_id=>X_DEFERRED_TRACKING_ID,
x_ass_rank=>X_ASS_RANK,
x_secondary_ass_rank=>X_SECONDARY_ASS_RANK,
x_intr_accept_advice_num=>x_intr_accept_advice_num,
x_ass_tracking_id=>X_ASS_TRACKING_ID,
x_fee_cat=>X_FEE_CAT,
x_hecs_payment_option=>X_HECS_PAYMENT_OPTION,
x_expected_completion_yr=>X_EXPECTED_COMPLETION_YR,
x_expected_completion_perd=>X_EXPECTED_COMPLETION_PERD,
x_correspondence_cat=>X_CORRESPONDENCE_CAT,
x_enrolment_cat=>X_ENROLMENT_CAT,
x_funding_source=>X_FUNDING_SOURCE,
x_applicant_acptnce_cndtn=>X_APPLICANT_ACPTNCE_CNDTN,
x_cndtnl_offer_cndtn=>X_CNDTNL_OFFER_CNDTN,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
X_SS_APPLICATION_ID => X_SS_APPLICATION_ID,
X_SS_PWD => X_SS_PWD,
X_AUTHORIZED_DT => X_AUTHORIZED_DT,
X_AUTHORIZING_PERS_ID => X_AUTHORIZING_PERS_ID,
x_entry_status => x_entry_status,
x_entry_level => x_entry_level,
x_sch_apl_to_id => x_sch_apl_to_id,
x_idx_calc_date => x_idx_calc_date,
x_waitlist_status =>x_waitlist_status,
x_attribute21=>X_ATTRIBUTE21,
x_attribute22=>X_ATTRIBUTE22,
x_attribute23=>X_ATTRIBUTE23,
x_attribute24=>X_ATTRIBUTE24,
x_attribute25=>X_ATTRIBUTE25,
x_attribute26=>X_ATTRIBUTE26,
x_attribute27=>X_ATTRIBUTE27,
x_attribute28=>X_ATTRIBUTE28,
x_attribute29=>X_ATTRIBUTE29,
x_attribute30=>X_ATTRIBUTE30,
x_attribute31=>X_ATTRIBUTE31,
x_attribute32=>X_ATTRIBUTE32,
x_attribute33=>X_ATTRIBUTE33,
x_attribute34=>X_ATTRIBUTE34,
x_attribute35=>X_ATTRIBUTE35,
x_attribute36=>X_ATTRIBUTE36,
x_attribute37=>X_ATTRIBUTE37,
x_attribute38=>X_ATTRIBUTE38,
x_attribute39=>X_ATTRIBUTE39,
x_attribute40=>X_ATTRIBUTE40,
x_fut_acad_cal_type => x_fut_acad_cal_type,
x_fut_acad_ci_sequence_number => x_fut_acad_ci_sequence_number ,
x_fut_adm_cal_type => x_fut_adm_cal_type,
x_fut_adm_ci_sequence_number => x_fut_adm_ci_sequence_number,
x_prev_term_adm_appl_number => x_prev_term_adm_appl_number,
x_prev_term_sequence_number => x_prev_term_sequence_number,
x_fut_term_adm_appl_number => x_fut_term_adm_appl_number ,
x_fut_term_sequence_number => x_fut_term_sequence_number,
x_def_acad_cal_type => x_def_acad_cal_type,
x_def_acad_ci_sequence_num =>x_def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num =>x_def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num =>x_def_prev_appl_sequence_num,
x_def_term_adm_appl_num => x_def_term_adm_appl_num,
x_def_appl_sequence_num => x_def_appl_sequence_num,
x_appl_inst_status => x_appl_inst_status, --arvsrini igsm
x_ais_reason => x_ais_reason,
x_decline_ofr_reason => x_decline_ofr_reason
);
insert into IGS_AD_PS_APPL_INST_ALL (
ORG_ID,
PERSON_ID
,ADMISSION_APPL_NUMBER
,NOMINATED_COURSE_CD
,SEQUENCE_NUMBER
,PREDICTED_GPA
,ACADEMIC_INDEX
,ADM_CAL_TYPE
,APP_FILE_LOCATION
,ADM_CI_SEQUENCE_NUMBER
,COURSE_CD
,APP_SOURCE_ID
,CRV_VERSION_NUMBER
,WAITLIST_RANK
,LOCATION_CD
,ATTENT_OTHER_INST_CD
,ATTENDANCE_MODE
,EDU_GOAL_PRIOR_ENROLL_ID
,ATTENDANCE_TYPE
,DECISION_MAKE_ID
,UNIT_SET_CD
,DECISION_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,DECISION_REASON_ID
,US_VERSION_NUMBER
,DECISION_NOTES
,PENDING_REASON_ID
,PREFERENCE_NUMBER
,ADM_DOC_STATUS
,ADM_ENTRY_QUAL_STATUS
,DEFICIENCY_IN_PREP
,LATE_ADM_FEE_STATUS
,SPL_CONSIDER_COMMENTS
,APPLY_FOR_FINAID
,FINAID_APPLY_DATE
,ADM_OUTCOME_STATUS
,ADM_OTCM_STATUS_AUTH_PERSON_ID
,ADM_OUTCOME_STATUS_AUTH_DT
,ADM_OUTCOME_STATUS_REASON
,OFFER_DT
,OFFER_RESPONSE_DT
,PRPSD_COMMENCEMENT_DT
,ADM_CNDTNL_OFFER_STATUS
,CNDTNL_OFFER_SATISFIED_DT
,CNDTNL_OFFER_MUST_BE_STSFD_IND
,ADM_OFFER_RESP_STATUS
,ACTUAL_RESPONSE_DT
,ADM_OFFER_DFRMNT_STATUS
,DEFERRED_ADM_CAL_TYPE
,DEFERRED_ADM_CI_SEQUENCE_NUM
,DEFERRED_TRACKING_ID
,ASS_RANK
,SECONDARY_ASS_RANK
,INTRNTNL_ACCEPTANCE_ADVICE_NUM
,ASS_TRACKING_ID
,FEE_CAT
,HECS_PAYMENT_OPTION
,EXPECTED_COMPLETION_YR
,EXPECTED_COMPLETION_PERD
,CORRESPONDENCE_CAT
,ENROLMENT_CAT
,FUNDING_SOURCE
,APPLICANT_ACPTNCE_CNDTN
,CNDTNL_OFFER_CNDTN
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,SS_APPLICATION_ID
,SS_PWD
,AUTHORIZED_DT
,AUTHORIZING_PERS_ID
,ENTRY_STATUS
,ENTRY_LEVEL
,SCH_APL_TO_ID
,IDX_CALC_DATE
,WAITLIST_STATUS
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
,ATTRIBUTE31
,ATTRIBUTE32
,ATTRIBUTE33
,ATTRIBUTE34
,ATTRIBUTE35
,ATTRIBUTE36
,ATTRIBUTE37
,ATTRIBUTE38
,ATTRIBUTE39
,ATTRIBUTE40
,future_acad_cal_type
,future_acad_ci_sequence_number
,future_adm_cal_type
,future_adm_ci_sequence_number
,previous_term_adm_appl_number
,previous_term_sequence_number
,future_term_adm_appl_number
,future_term_sequence_number
,def_acad_cal_type
,def_acad_ci_sequence_num
,def_prev_term_adm_appl_num
,def_prev_appl_sequence_num
,def_term_adm_appl_num
,def_appl_sequence_num
,appl_inst_status --arvsrini igsm
,ais_reason
,decline_ofr_reason
) values (
NEW_REFERENCES.ORG_ID
,NEW_REFERENCES.PERSON_ID
,NEW_REFERENCES.ADMISSION_APPL_NUMBER
,NEW_REFERENCES.NOMINATED_COURSE_CD
,NEW_REFERENCES.SEQUENCE_NUMBER
,NEW_REFERENCES.PREDICTED_GPA
,NEW_REFERENCES.ACADEMIC_INDEX
,NEW_REFERENCES.ADM_CAL_TYPE
,NEW_REFERENCES.APP_FILE_LOCATION
,NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER
,NEW_REFERENCES.COURSE_CD
,NEW_REFERENCES.APP_SOURCE_ID
,NEW_REFERENCES.CRV_VERSION_NUMBER
,NEW_REFERENCES.WAITLIST_RANK
,NEW_REFERENCES.LOCATION_CD
,NEW_REFERENCES.ATTENT_OTHER_INST_CD
,NEW_REFERENCES.ATTENDANCE_MODE
,NEW_REFERENCES.EDU_GOAL_PRIOR_ENROLL_ID
,NEW_REFERENCES.ATTENDANCE_TYPE
,NEW_REFERENCES.DECISION_MAKE_ID
,NEW_REFERENCES.UNIT_SET_CD
,NEW_REFERENCES.DECISION_DATE
,NEW_REFERENCES.ATTRIBUTE_CATEGORY
,NEW_REFERENCES.ATTRIBUTE1
,NEW_REFERENCES.ATTRIBUTE2
,NEW_REFERENCES.ATTRIBUTE3
,NEW_REFERENCES.ATTRIBUTE4
,NEW_REFERENCES.ATTRIBUTE5
,NEW_REFERENCES.ATTRIBUTE6
,NEW_REFERENCES.ATTRIBUTE7
,NEW_REFERENCES.ATTRIBUTE8
,NEW_REFERENCES.ATTRIBUTE9
,NEW_REFERENCES.ATTRIBUTE10
,NEW_REFERENCES.ATTRIBUTE11
,NEW_REFERENCES.ATTRIBUTE12
,NEW_REFERENCES.ATTRIBUTE13
,NEW_REFERENCES.ATTRIBUTE14
,NEW_REFERENCES.ATTRIBUTE15
,NEW_REFERENCES.ATTRIBUTE16
,NEW_REFERENCES.ATTRIBUTE17
,NEW_REFERENCES.ATTRIBUTE18
,NEW_REFERENCES.ATTRIBUTE19
,NEW_REFERENCES.ATTRIBUTE20
,NEW_REFERENCES.DECISION_REASON_ID
,NEW_REFERENCES.US_VERSION_NUMBER
,NEW_REFERENCES.DECISION_NOTES
,NEW_REFERENCES.PENDING_REASON_ID
,NEW_REFERENCES.PREFERENCE_NUMBER
,NEW_REFERENCES.ADM_DOC_STATUS
,NEW_REFERENCES.ADM_ENTRY_QUAL_STATUS
,NEW_REFERENCES.DEFICIENCY_IN_PREP
,NEW_REFERENCES.LATE_ADM_FEE_STATUS
,NEW_REFERENCES.SPL_CONSIDER_COMMENTS
,NEW_REFERENCES.APPLY_FOR_FINAID
,NEW_REFERENCES.FINAID_APPLY_DATE
,NEW_REFERENCES.ADM_OUTCOME_STATUS
,NEW_REFERENCES.ADM_OTCM_STATUS_AUTH_PERSON_ID
,NEW_REFERENCES.ADM_OUTCOME_STATUS_AUTH_DT
,NEW_REFERENCES.ADM_OUTCOME_STATUS_REASON
,NEW_REFERENCES.OFFER_DT
,NEW_REFERENCES.OFFER_RESPONSE_DT
,NEW_REFERENCES.PRPSD_COMMENCEMENT_DT
,NEW_REFERENCES.ADM_CNDTNL_OFFER_STATUS
,NEW_REFERENCES.CNDTNL_OFFER_SATISFIED_DT
,NEW_REFERENCES.CNDTNL_OFFER_MUST_BE_STSFD_IND
,NEW_REFERENCES.ADM_OFFER_RESP_STATUS
,NEW_REFERENCES.ACTUAL_RESPONSE_DT
,NEW_REFERENCES.ADM_OFFER_DFRMNT_STATUS
,NEW_REFERENCES.DEFERRED_ADM_CAL_TYPE
,NEW_REFERENCES.DEFERRED_ADM_CI_SEQUENCE_NUM
,NEW_REFERENCES.DEFERRED_TRACKING_ID
,NEW_REFERENCES.ASS_RANK
,NEW_REFERENCES.SECONDARY_ASS_RANK
,NEW_REFERENCES.INTRNTNL_ACCEPTANCE_ADVICE_NUM
,NEW_REFERENCES.ASS_TRACKING_ID
,NEW_REFERENCES.FEE_CAT
,NEW_REFERENCES.HECS_PAYMENT_OPTION
,NEW_REFERENCES.EXPECTED_COMPLETION_YR
,NEW_REFERENCES.EXPECTED_COMPLETION_PERD
,NEW_REFERENCES.CORRESPONDENCE_CAT
,NEW_REFERENCES.ENROLMENT_CAT
,NEW_REFERENCES.FUNDING_SOURCE
,NEW_REFERENCES.APPLICANT_ACPTNCE_CNDTN
,NEW_REFERENCES.CNDTNL_OFFER_CNDTN
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_LOGIN
,X_REQUEST_ID
,X_PROGRAM_ID
,X_PROGRAM_APPLICATION_ID
,X_PROGRAM_UPDATE_DATE
,NEW_REFERENCES.SS_APPLICATION_ID
,NEW_REFERENCES.SS_PWD
,NEW_REFERENCES.AUTHORIZED_DT
,NEW_REFERENCES.AUTHORIZING_PERS_ID
,NEW_REFERENCES.ENTRY_STATUS
,NEW_REFERENCES.ENTRY_LEVEL
,NEW_REFERENCES.SCH_APL_TO_ID
,NEW_REFERENCES.IDX_CALC_DATE
,NEW_REFERENCES.WAITLIST_STATUS
,NEW_REFERENCES.ATTRIBUTE21
,NEW_REFERENCES.ATTRIBUTE22
,NEW_REFERENCES.ATTRIBUTE23
,NEW_REFERENCES.ATTRIBUTE24
,NEW_REFERENCES.ATTRIBUTE25
,NEW_REFERENCES.ATTRIBUTE26
,NEW_REFERENCES.ATTRIBUTE27
,NEW_REFERENCES.ATTRIBUTE28
,NEW_REFERENCES.ATTRIBUTE29
,NEW_REFERENCES.ATTRIBUTE30
,NEW_REFERENCES.ATTRIBUTE31
,NEW_REFERENCES.ATTRIBUTE32
,NEW_REFERENCES.ATTRIBUTE33
,NEW_REFERENCES.ATTRIBUTE34
,NEW_REFERENCES.ATTRIBUTE35
,NEW_REFERENCES.ATTRIBUTE36
,NEW_REFERENCES.ATTRIBUTE37
,NEW_REFERENCES.ATTRIBUTE38
,NEW_REFERENCES.ATTRIBUTE39
,NEW_REFERENCES.ATTRIBUTE40
,new_references.future_acad_cal_type
,new_references.future_acad_ci_sequence_number
,new_references.future_adm_cal_type
,new_references.future_adm_ci_sequence_number
,new_references.previous_term_adm_appl_number
,new_references.previous_term_sequence_number
,new_references.future_term_adm_appl_number
,new_references.future_term_sequence_number
,new_references.DEF_ACAD_CAL_TYPE
,new_references.DEF_ACAD_CI_SEQUENCE_NUM
,new_references.DEF_PREV_TERM_ADM_APPL_NUM
,new_references.DEF_PREV_APPL_SEQUENCE_NUM
,new_references.DEF_TERM_ADM_APPL_NUM
,new_references.def_appl_sequence_num
,new_references.appl_inst_status --arvsrini igsm
,new_references.ais_reason
,new_references.decline_ofr_reason
);
p_action => 'INSERT',
x_rowid => X_ROWID );
END INSERT_ROW;
cursor c1 is select
PERSON_ID
, PREDICTED_GPA
, ACADEMIC_INDEX
, ADM_CAL_TYPE
, APP_FILE_LOCATION
, ADM_CI_SEQUENCE_NUMBER
, COURSE_CD
, APP_SOURCE_ID
, CRV_VERSION_NUMBER
, WAITLIST_RANK
, LOCATION_CD
, ATTENT_OTHER_INST_CD
, ATTENDANCE_MODE
, EDU_GOAL_PRIOR_ENROLL_ID
, ATTENDANCE_TYPE
, DECISION_MAKE_ID
, UNIT_SET_CD
, DECISION_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, DECISION_REASON_ID
, US_VERSION_NUMBER
, DECISION_NOTES
, PENDING_REASON_ID
, PREFERENCE_NUMBER
, ADM_DOC_STATUS
, ADM_ENTRY_QUAL_STATUS
, DEFICIENCY_IN_PREP
, LATE_ADM_FEE_STATUS
, SPL_CONSIDER_COMMENTS
, APPLY_FOR_FINAID
, FINAID_APPLY_DATE
, ADM_OUTCOME_STATUS
, ADM_OTCM_STATUS_AUTH_PERSON_ID
, ADM_OUTCOME_STATUS_AUTH_DT
, ADM_OUTCOME_STATUS_REASON
, OFFER_DT
, OFFER_RESPONSE_DT
, PRPSD_COMMENCEMENT_DT
, ADM_CNDTNL_OFFER_STATUS
, CNDTNL_OFFER_SATISFIED_DT
, CNDTNL_OFFER_MUST_BE_STSFD_IND
, ADM_OFFER_RESP_STATUS
, ACTUAL_RESPONSE_DT
, ADM_OFFER_DFRMNT_STATUS
, DEFERRED_ADM_CAL_TYPE
, DEFERRED_ADM_CI_SEQUENCE_NUM
, DEFERRED_TRACKING_ID
, ASS_RANK
, SECONDARY_ASS_RANK
, INTRNTNL_ACCEPTANCE_ADVICE_NUM
, ASS_TRACKING_ID
, FEE_CAT
, HECS_PAYMENT_OPTION
, EXPECTED_COMPLETION_YR
, EXPECTED_COMPLETION_PERD
, CORRESPONDENCE_CAT
, ENROLMENT_CAT
, FUNDING_SOURCE
, APPLICANT_ACPTNCE_CNDTN
, CNDTNL_OFFER_CNDTN
, SS_APPLICATION_ID
, SS_PWD
, AUTHORIZED_DT
, AUTHORIZING_PERS_ID
,ENTRY_STATUS
,ENTRY_LEVEL
,SCH_APL_TO_ID
, IDX_CALC_DATE
, WAITLIST_STATUS
, ATTRIBUTE21
, ATTRIBUTE22
, ATTRIBUTE23
, ATTRIBUTE24
, ATTRIBUTE25
, ATTRIBUTE26
, ATTRIBUTE27
, ATTRIBUTE28
, ATTRIBUTE29
, ATTRIBUTE30
, ATTRIBUTE31
, ATTRIBUTE32
, ATTRIBUTE33
, ATTRIBUTE34
, ATTRIBUTE35
, ATTRIBUTE36
, ATTRIBUTE37
, ATTRIBUTE38
, ATTRIBUTE39
, ATTRIBUTE40
, future_acad_cal_type
, future_acad_ci_sequence_number
, future_adm_cal_type
, future_adm_ci_sequence_number
, previous_term_adm_appl_number
, previous_term_sequence_number
, future_term_adm_appl_number
, future_term_sequence_number
, DEF_ACAD_CAL_TYPE
, DEF_ACAD_CI_SEQUENCE_NUM
, DEF_PREV_TERM_ADM_APPL_NUM
, DEF_PREV_APPL_SEQUENCE_NUM
, DEF_TERM_ADM_APPL_NUM
, def_appl_sequence_num
, appl_inst_status --arvsrini igsm
, ais_reason
, decline_ofr_reason
from IGS_AD_PS_APPL_INST_ALL
where ROWID = X_ROWID
for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Procedure UPDATE_ROW (
X_ROWID in VARCHAR2,
x_PERSON_ID IN NUMBER,
x_ADMISSION_APPL_NUMBER IN NUMBER,
x_NOMINATED_COURSE_CD IN VARCHAR2,
x_SEQUENCE_NUMBER IN NUMBER,
x_PREDICTED_GPA IN NUMBER,
x_ACADEMIC_INDEX IN VARCHAR2,
x_ADM_CAL_TYPE IN VARCHAR2,
x_APP_FILE_LOCATION IN VARCHAR2,
x_ADM_CI_SEQUENCE_NUMBER IN NUMBER,
x_COURSE_CD IN VARCHAR2,
x_APP_SOURCE_ID IN NUMBER,
x_CRV_VERSION_NUMBER IN NUMBER,
x_WAITLIST_RANK IN VARCHAR2,
x_LOCATION_CD IN VARCHAR2,
x_ATTENT_OTHER_INST_CD IN VARCHAR2,
x_ATTENDANCE_MODE IN VARCHAR2,
x_EDU_GOAL_PRIOR_ENROLL_ID IN NUMBER,
x_ATTENDANCE_TYPE IN VARCHAR2,
x_DECISION_MAKE_ID IN NUMBER,
x_UNIT_SET_CD IN VARCHAR2,
x_DECISION_DATE IN DATE,
x_ATTRIBUTE_CATEGORY IN VARCHAR2,
x_ATTRIBUTE1 IN VARCHAR2,
x_ATTRIBUTE2 IN VARCHAR2,
x_ATTRIBUTE3 IN VARCHAR2,
x_ATTRIBUTE4 IN VARCHAR2,
x_ATTRIBUTE5 IN VARCHAR2,
x_ATTRIBUTE6 IN VARCHAR2,
x_ATTRIBUTE7 IN VARCHAR2,
x_ATTRIBUTE8 IN VARCHAR2,
x_ATTRIBUTE9 IN VARCHAR2,
x_ATTRIBUTE10 IN VARCHAR2,
x_ATTRIBUTE11 IN VARCHAR2,
x_ATTRIBUTE12 IN VARCHAR2,
x_ATTRIBUTE13 IN VARCHAR2,
x_ATTRIBUTE14 IN VARCHAR2,
x_ATTRIBUTE15 IN VARCHAR2,
x_ATTRIBUTE16 IN VARCHAR2,
x_ATTRIBUTE17 IN VARCHAR2,
x_ATTRIBUTE18 IN VARCHAR2,
x_ATTRIBUTE19 IN VARCHAR2,
x_ATTRIBUTE20 IN VARCHAR2,
x_DECISION_REASON_ID IN NUMBER,
x_US_VERSION_NUMBER IN NUMBER,
x_DECISION_NOTES IN VARCHAR2,
x_PENDING_REASON_ID IN NUMBER,
x_PREFERENCE_NUMBER IN NUMBER,
x_ADM_DOC_STATUS IN VARCHAR2,
x_ADM_ENTRY_QUAL_STATUS IN VARCHAR2,
x_DEFICIENCY_IN_PREP IN VARCHAR2,
x_LATE_ADM_FEE_STATUS IN VARCHAR2,
x_SPL_CONSIDER_COMMENTS IN VARCHAR2,
x_APPLY_FOR_FINAID IN VARCHAR2,
x_FINAID_APPLY_DATE IN DATE,
x_ADM_OUTCOME_STATUS IN VARCHAR2,
x_adm_otcm_stat_auth_per_id IN NUMBER,
x_ADM_OUTCOME_STATUS_AUTH_DT IN DATE,
x_ADM_OUTCOME_STATUS_REASON IN VARCHAR2,
x_OFFER_DT IN DATE,
x_OFFER_RESPONSE_DT IN DATE,
x_PRPSD_COMMENCEMENT_DT IN DATE,
x_ADM_CNDTNL_OFFER_STATUS IN VARCHAR2,
x_CNDTNL_OFFER_SATISFIED_DT IN DATE,
x_cndnl_ofr_must_be_stsfd_ind IN VARCHAR2,
x_ADM_OFFER_RESP_STATUS IN VARCHAR2,
x_ACTUAL_RESPONSE_DT IN DATE,
x_ADM_OFFER_DFRMNT_STATUS IN VARCHAR2,
x_DEFERRED_ADM_CAL_TYPE IN VARCHAR2,
x_DEFERRED_ADM_CI_SEQUENCE_NUM IN NUMBER,
x_DEFERRED_TRACKING_ID IN NUMBER,
x_ASS_RANK IN NUMBER,
x_SECONDARY_ASS_RANK IN NUMBER,
x_intr_accept_advice_num IN NUMBER,
x_ASS_TRACKING_ID IN NUMBER,
x_FEE_CAT IN VARCHAR2,
x_HECS_PAYMENT_OPTION IN VARCHAR2,
x_EXPECTED_COMPLETION_YR IN NUMBER,
x_EXPECTED_COMPLETION_PERD IN VARCHAR2,
x_CORRESPONDENCE_CAT IN VARCHAR2,
x_ENROLMENT_CAT IN VARCHAR2,
x_FUNDING_SOURCE IN VARCHAR2,
x_APPLICANT_ACPTNCE_CNDTN IN VARCHAR2,
x_CNDTNL_OFFER_CNDTN IN VARCHAR2,
X_MODE in VARCHAR2,
X_SS_APPLICATION_ID IN VARCHAR2,
X_SS_PWD IN VARCHAR2 ,
X_AUTHORIZED_DT DATE,
X_AUTHORIZING_PERS_ID NUMBER,
x_entry_status IN NUMBER,
x_entry_level IN NUMBER,
x_sch_apl_to_id IN NUMBER,
x_idx_calc_date IN DATE,
x_waitlist_status IN VARCHAR2,
x_ATTRIBUTE21 IN VARCHAR2,
x_ATTRIBUTE22 IN VARCHAR2,
x_ATTRIBUTE23 IN VARCHAR2,
x_ATTRIBUTE24 IN VARCHAR2,
x_ATTRIBUTE25 IN VARCHAR2,
x_ATTRIBUTE26 IN VARCHAR2,
x_ATTRIBUTE27 IN VARCHAR2,
x_ATTRIBUTE28 IN VARCHAR2,
x_ATTRIBUTE29 IN VARCHAR2,
x_ATTRIBUTE30 IN VARCHAR2,
x_ATTRIBUTE31 IN VARCHAR2,
x_ATTRIBUTE32 IN VARCHAR2,
x_ATTRIBUTE33 IN VARCHAR2,
x_ATTRIBUTE34 IN VARCHAR2,
x_ATTRIBUTE35 IN VARCHAR2,
x_ATTRIBUTE36 IN VARCHAR2,
x_ATTRIBUTE37 IN VARCHAR2,
x_ATTRIBUTE38 IN VARCHAR2,
x_ATTRIBUTE39 IN VARCHAR2,
x_ATTRIBUTE40 IN VARCHAR2,
x_fut_acad_cal_type IN VARCHAR2,
x_fut_acad_ci_sequence_number IN NUMBER ,
x_fut_adm_cal_type IN VARCHAR2,
x_fut_adm_ci_sequence_number IN NUMBER ,
x_prev_term_adm_appl_number IN NUMBER ,
x_prev_term_sequence_number IN NUMBER ,
x_fut_term_adm_appl_number IN NUMBER ,
x_fut_term_sequence_number IN NUMBER ,
x_def_acad_cal_type IN VARCHAR2,
x_def_acad_ci_sequence_num IN NUMBER ,
x_def_prev_term_adm_appl_num IN NUMBER ,
x_def_prev_appl_sequence_num IN NUMBER ,
x_def_term_adm_appl_num IN NUMBER ,
x_def_appl_sequence_num IN NUMBER ,
x_appl_inst_status IN VARCHAR2, --arvsrini igsm
x_ais_reason IN VARCHAR2,
x_decline_ofr_reason IN VARCHAR2
) IS
/*************************************************************
Created By : nsinha
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
nsinha Jul 30, 2001 Bug enh no : 1905651 changes.
Added entry_status, entry_level and sch_apl_to_id
to the procedures
(reverse chronological order - newest change first)
***************************************************************/
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_PROGRAM_UPDATE_DATE DATE;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
p_action=>'UPDATE',
x_rowid=>X_ROWID,
x_person_id=>X_PERSON_ID,
x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_predicted_gpa=>X_PREDICTED_GPA,
x_academic_index=>X_ACADEMIC_INDEX,
x_adm_cal_type=>X_ADM_CAL_TYPE,
x_app_file_location=>X_APP_FILE_LOCATION,
x_adm_ci_sequence_number=>X_ADM_CI_SEQUENCE_NUMBER,
x_course_cd=>X_COURSE_CD,
x_app_source_id=>X_APP_SOURCE_ID,
x_crv_version_number=>X_CRV_VERSION_NUMBER,
x_waitlist_rank=>X_WAITLIST_RANK,
x_location_cd=>X_LOCATION_CD,
x_attent_other_inst_cd=>X_ATTENT_OTHER_INST_CD,
x_attendance_mode=>X_ATTENDANCE_MODE,
x_edu_goal_prior_enroll_id=>X_EDU_GOAL_PRIOR_ENROLL_ID,
x_attendance_type=>X_ATTENDANCE_TYPE,
x_decision_make_id=>X_DECISION_MAKE_ID,
x_unit_set_cd=>X_UNIT_SET_CD,
x_decision_date=>X_DECISION_DATE,
x_attribute_category=>X_ATTRIBUTE_CATEGORY,
x_attribute1=>X_ATTRIBUTE1,
x_attribute2=>X_ATTRIBUTE2,
x_attribute3=>X_ATTRIBUTE3,
x_attribute4=>X_ATTRIBUTE4,
x_attribute5=>X_ATTRIBUTE5,
x_attribute6=>X_ATTRIBUTE6,
x_attribute7=>X_ATTRIBUTE7,
x_attribute8=>X_ATTRIBUTE8,
x_attribute9=>X_ATTRIBUTE9,
x_attribute10=>X_ATTRIBUTE10,
x_attribute11=>X_ATTRIBUTE11,
x_attribute12=>X_ATTRIBUTE12,
x_attribute13=>X_ATTRIBUTE13,
x_attribute14=>X_ATTRIBUTE14,
x_attribute15=>X_ATTRIBUTE15,
x_attribute16=>X_ATTRIBUTE16,
x_attribute17=>X_ATTRIBUTE17,
x_attribute18=>X_ATTRIBUTE18,
x_attribute19=>X_ATTRIBUTE19,
x_attribute20=>X_ATTRIBUTE20,
x_decision_reason_id=>X_DECISION_REASON_ID,
x_us_version_number=>X_US_VERSION_NUMBER,
x_decision_notes=>X_DECISION_NOTES,
x_pending_reason_id=>X_PENDING_REASON_ID,
x_preference_number=>X_PREFERENCE_NUMBER,
x_adm_doc_status=>X_ADM_DOC_STATUS,
x_adm_entry_qual_status=>X_ADM_ENTRY_QUAL_STATUS,
x_deficiency_in_prep=>X_DEFICIENCY_IN_PREP,
x_late_adm_fee_status=>X_LATE_ADM_FEE_STATUS,
x_spl_consider_comments=>X_SPL_CONSIDER_COMMENTS,
x_apply_for_finaid=>X_APPLY_FOR_FINAID,
x_finaid_apply_date=>X_FINAID_APPLY_DATE,
x_adm_outcome_status=>X_ADM_OUTCOME_STATUS,
x_adm_otcm_stat_auth_per_id=>x_adm_otcm_stat_auth_per_id,
x_adm_outcome_status_auth_dt=>X_ADM_OUTCOME_STATUS_AUTH_DT,
x_adm_outcome_status_reason=>X_ADM_OUTCOME_STATUS_REASON,
x_offer_dt=>X_OFFER_DT,
x_offer_response_dt=>X_OFFER_RESPONSE_DT,
x_prpsd_commencement_dt=>X_PRPSD_COMMENCEMENT_DT,
x_adm_cndtnl_offer_status=>X_ADM_CNDTNL_OFFER_STATUS,
x_cndtnl_offer_satisfied_dt=>X_CNDTNL_OFFER_SATISFIED_DT,
x_cndnl_ofr_must_be_stsfd_ind=>NVL(x_cndnl_ofr_must_be_stsfd_ind,'N' ),
x_adm_offer_resp_status=>X_ADM_OFFER_RESP_STATUS,
x_actual_response_dt=>X_ACTUAL_RESPONSE_DT,
x_adm_offer_dfrmnt_status=>X_ADM_OFFER_DFRMNT_STATUS,
x_deferred_adm_cal_type=>X_DEFERRED_ADM_CAL_TYPE,
x_deferred_adm_ci_sequence_num=>X_DEFERRED_ADM_CI_SEQUENCE_NUM,
x_deferred_tracking_id=>X_DEFERRED_TRACKING_ID,
x_ass_rank=>X_ASS_RANK,
x_secondary_ass_rank=>X_SECONDARY_ASS_RANK,
x_intr_accept_advice_num=>x_intr_accept_advice_num,
x_ass_tracking_id=>X_ASS_TRACKING_ID,
x_fee_cat=>X_FEE_CAT,
x_hecs_payment_option=>X_HECS_PAYMENT_OPTION,
x_expected_completion_yr=>X_EXPECTED_COMPLETION_YR,
x_expected_completion_perd=>X_EXPECTED_COMPLETION_PERD,
x_correspondence_cat=>X_CORRESPONDENCE_CAT,
x_enrolment_cat=>X_ENROLMENT_CAT,
x_funding_source=>X_FUNDING_SOURCE,
x_applicant_acptnce_cndtn=>X_APPLICANT_ACPTNCE_CNDTN,
x_cndtnl_offer_cndtn=>X_CNDTNL_OFFER_CNDTN,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_ss_application_id => x_ss_application_id,
x_ss_pwd => x_ss_pwd,
x_authorized_dt => x_authorized_dt,
x_authorizing_pers_id => x_authorizing_pers_id,
x_entry_status => x_entry_status,
x_entry_level => x_entry_level,
x_sch_apl_to_id => x_sch_apl_to_id,
x_idx_calc_date => x_idx_calc_date,
x_waitlist_status => x_waitlist_status,
x_attribute21=>X_ATTRIBUTE21,
x_attribute22=>X_ATTRIBUTE22,
x_attribute23=>X_ATTRIBUTE23,
x_attribute24=>X_ATTRIBUTE24,
x_attribute25=>X_ATTRIBUTE25,
x_attribute26=>X_ATTRIBUTE26,
x_attribute27=>X_ATTRIBUTE27,
x_attribute28=>X_ATTRIBUTE28,
x_attribute29=>X_ATTRIBUTE29,
x_attribute30=>X_ATTRIBUTE30,
x_attribute31=>X_ATTRIBUTE31,
x_attribute32=>X_ATTRIBUTE32,
x_attribute33=>X_ATTRIBUTE33,
x_attribute34=>X_ATTRIBUTE34,
x_attribute35=>X_ATTRIBUTE35,
x_attribute36=>X_ATTRIBUTE36,
x_attribute37=>X_ATTRIBUTE37,
x_attribute38=>X_ATTRIBUTE38,
x_attribute39=>X_ATTRIBUTE39,
x_attribute40=>X_ATTRIBUTE40,
x_fut_acad_cal_type => x_fut_acad_cal_type,
x_fut_acad_ci_sequence_number => x_fut_acad_ci_sequence_number,
x_fut_adm_cal_type => x_fut_adm_cal_type,
x_fut_adm_ci_sequence_number => x_fut_adm_ci_sequence_number,
x_prev_term_adm_appl_number => x_prev_term_adm_appl_number,
x_prev_term_sequence_number => x_prev_term_sequence_number,
x_fut_term_adm_appl_number => x_fut_term_adm_appl_number,
x_fut_term_sequence_number => x_fut_term_sequence_number,
x_def_acad_cal_type => x_def_acad_cal_type,
x_def_acad_ci_sequence_num => x_def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num => x_def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num => x_def_prev_appl_sequence_num,
x_def_term_adm_appl_num => x_def_term_adm_appl_num,
x_def_appl_sequence_num => x_def_appl_sequence_num,
x_appl_inst_status => x_appl_inst_status, --arvsrini igsm
x_ais_reason => x_ais_reason,
x_decline_ofr_reason => x_decline_ofr_reason
);
X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
X_PROGRAM_UPDATE_DATE := SYSDATE;
update IGS_AD_PS_APPL_INST_ALL set
PREDICTED_GPA = NEW_REFERENCES.PREDICTED_GPA,
ACADEMIC_INDEX = NEW_REFERENCES.ACADEMIC_INDEX,
ADM_CAL_TYPE = NEW_REFERENCES.ADM_CAL_TYPE,
APP_FILE_LOCATION = NEW_REFERENCES.APP_FILE_LOCATION,
ADM_CI_SEQUENCE_NUMBER = NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
COURSE_CD = NEW_REFERENCES.COURSE_CD,
APP_SOURCE_ID = NEW_REFERENCES.APP_SOURCE_ID,
CRV_VERSION_NUMBER = NEW_REFERENCES.CRV_VERSION_NUMBER,
WAITLIST_RANK = NEW_REFERENCES.WAITLIST_RANK,
LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
ATTENT_OTHER_INST_CD = NEW_REFERENCES.ATTENT_OTHER_INST_CD,
ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
EDU_GOAL_PRIOR_ENROLL_ID = NEW_REFERENCES.EDU_GOAL_PRIOR_ENROLL_ID,
ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
DECISION_MAKE_ID = NEW_REFERENCES.DECISION_MAKE_ID,
UNIT_SET_CD = NEW_REFERENCES.UNIT_SET_CD,
DECISION_DATE = NEW_REFERENCES.DECISION_DATE,
ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
DECISION_REASON_ID = NEW_REFERENCES.DECISION_REASON_ID,
US_VERSION_NUMBER = NEW_REFERENCES.US_VERSION_NUMBER,
DECISION_NOTES = NEW_REFERENCES.DECISION_NOTES,
PENDING_REASON_ID = NEW_REFERENCES.PENDING_REASON_ID,
PREFERENCE_NUMBER = NEW_REFERENCES.PREFERENCE_NUMBER,
ADM_DOC_STATUS = NEW_REFERENCES.ADM_DOC_STATUS,
ADM_ENTRY_QUAL_STATUS = NEW_REFERENCES.ADM_ENTRY_QUAL_STATUS,
DEFICIENCY_IN_PREP = NEW_REFERENCES.DEFICIENCY_IN_PREP,
LATE_ADM_FEE_STATUS = NEW_REFERENCES.LATE_ADM_FEE_STATUS,
SPL_CONSIDER_COMMENTS = NEW_REFERENCES.SPL_CONSIDER_COMMENTS,
APPLY_FOR_FINAID = NEW_REFERENCES.APPLY_FOR_FINAID,
FINAID_APPLY_DATE = NEW_REFERENCES.FINAID_APPLY_DATE,
ADM_OUTCOME_STATUS = NEW_REFERENCES.ADM_OUTCOME_STATUS,
ADM_OTCM_STATUS_AUTH_PERSON_ID = NEW_REFERENCES.ADM_OTCM_STATUS_AUTH_PERSON_ID,
ADM_OUTCOME_STATUS_AUTH_DT = NEW_REFERENCES.ADM_OUTCOME_STATUS_AUTH_DT,
ADM_OUTCOME_STATUS_REASON = NEW_REFERENCES.ADM_OUTCOME_STATUS_REASON,
OFFER_DT = NEW_REFERENCES.OFFER_DT,
OFFER_RESPONSE_DT = NEW_REFERENCES.OFFER_RESPONSE_DT,
PRPSD_COMMENCEMENT_DT = NEW_REFERENCES.PRPSD_COMMENCEMENT_DT,
ADM_CNDTNL_OFFER_STATUS = NEW_REFERENCES.ADM_CNDTNL_OFFER_STATUS,
CNDTNL_OFFER_SATISFIED_DT = NEW_REFERENCES.CNDTNL_OFFER_SATISFIED_DT,
CNDTNL_OFFER_MUST_BE_STSFD_IND = NEW_REFERENCES.CNDTNL_OFFER_MUST_BE_STSFD_IND,
ADM_OFFER_RESP_STATUS = NEW_REFERENCES.ADM_OFFER_RESP_STATUS,
ACTUAL_RESPONSE_DT = NEW_REFERENCES.ACTUAL_RESPONSE_DT,
ADM_OFFER_DFRMNT_STATUS = NEW_REFERENCES.ADM_OFFER_DFRMNT_STATUS,
DEFERRED_ADM_CAL_TYPE = NEW_REFERENCES.DEFERRED_ADM_CAL_TYPE,
DEFERRED_ADM_CI_SEQUENCE_NUM = NEW_REFERENCES.DEFERRED_ADM_CI_SEQUENCE_NUM,
DEFERRED_TRACKING_ID = NEW_REFERENCES.DEFERRED_TRACKING_ID,
ASS_RANK = NEW_REFERENCES.ASS_RANK,
SECONDARY_ASS_RANK = NEW_REFERENCES.SECONDARY_ASS_RANK,
INTRNTNL_ACCEPTANCE_ADVICE_NUM = NEW_REFERENCES.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
ASS_TRACKING_ID = NEW_REFERENCES.ASS_TRACKING_ID,
FEE_CAT = NEW_REFERENCES.FEE_CAT,
HECS_PAYMENT_OPTION = NEW_REFERENCES.HECS_PAYMENT_OPTION,
EXPECTED_COMPLETION_YR = NEW_REFERENCES.EXPECTED_COMPLETION_YR,
EXPECTED_COMPLETION_PERD = NEW_REFERENCES.EXPECTED_COMPLETION_PERD,
CORRESPONDENCE_CAT = NEW_REFERENCES.CORRESPONDENCE_CAT,
ENROLMENT_CAT = NEW_REFERENCES.ENROLMENT_CAT,
FUNDING_SOURCE = NEW_REFERENCES.FUNDING_SOURCE,
APPLICANT_ACPTNCE_CNDTN = NEW_REFERENCES.APPLICANT_ACPTNCE_CNDTN,
CNDTNL_OFFER_CNDTN = NEW_REFERENCES.CNDTNL_OFFER_CNDTN,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
SS_APPLICATION_ID = NEW_REFERENCES.SS_APPLICATION_ID,
SS_PWD = NEW_REFERENCES.SS_PWD,
AUTHORIZED_DT = NEW_REFERENCES.AUTHORIZED_DT,
AUTHORIZING_PERS_ID = NEW_REFERENCES.AUTHORIZING_PERS_ID,
ENTRY_STATUS = NEW_REFERENCES.ENTRY_STATUS,
ENTRY_LEVEL = NEW_REFERENCES.ENTRY_LEVEL,
SCH_APL_TO_ID = NEW_REFERENCES.SCH_APL_TO_ID,
IDX_CALC_DATE = NEW_REFERENCES.IDX_CALC_DATE,
WAITLIST_STATUS = NEW_REFERENCES.WAITLIST_STATUS,
ATTRIBUTE21 = NEW_REFERENCES.ATTRIBUTE21,
ATTRIBUTE22 = NEW_REFERENCES.ATTRIBUTE22,
ATTRIBUTE23 = NEW_REFERENCES.ATTRIBUTE23,
ATTRIBUTE24 = NEW_REFERENCES.ATTRIBUTE24,
ATTRIBUTE25 = NEW_REFERENCES.ATTRIBUTE25,
ATTRIBUTE26 = NEW_REFERENCES.ATTRIBUTE26,
ATTRIBUTE27 = NEW_REFERENCES.ATTRIBUTE27,
ATTRIBUTE28 = NEW_REFERENCES.ATTRIBUTE28,
ATTRIBUTE29 = NEW_REFERENCES.ATTRIBUTE29,
ATTRIBUTE30 = NEW_REFERENCES.ATTRIBUTE30,
ATTRIBUTE31 = NEW_REFERENCES.ATTRIBUTE31,
ATTRIBUTE32 = NEW_REFERENCES.ATTRIBUTE32,
ATTRIBUTE33 = NEW_REFERENCES.ATTRIBUTE33,
ATTRIBUTE34 = NEW_REFERENCES.ATTRIBUTE34,
ATTRIBUTE35 = NEW_REFERENCES.ATTRIBUTE35,
ATTRIBUTE36 = NEW_REFERENCES.ATTRIBUTE36,
ATTRIBUTE37 = NEW_REFERENCES.ATTRIBUTE37,
ATTRIBUTE38 = NEW_REFERENCES.ATTRIBUTE38,
ATTRIBUTE39 = NEW_REFERENCES.ATTRIBUTE39,
ATTRIBUTE40 = NEW_REFERENCES.ATTRIBUTE40,
future_acad_cal_type = new_references.future_acad_cal_type,
future_acad_ci_sequence_number = new_references.future_acad_ci_sequence_number,
future_adm_cal_type = new_references.future_adm_cal_type,
future_adm_ci_sequence_number = new_references.future_adm_ci_sequence_number,
previous_term_adm_appl_number = new_references.previous_term_adm_appl_number,
previous_term_sequence_number = new_references.previous_term_sequence_number,
future_term_adm_appl_number = new_references.future_term_adm_appl_number,
future_term_sequence_number = new_references.future_term_sequence_number,
def_acad_cal_type = new_references.def_acad_cal_type,
def_acad_ci_sequence_num = new_references.def_acad_ci_sequence_num,
def_prev_term_adm_appl_num = new_references.def_prev_term_adm_appl_num,
def_prev_appl_sequence_num = new_references.def_prev_appl_sequence_num,
def_term_adm_appl_num = new_references.def_term_adm_appl_num,
def_appl_sequence_num = new_references.def_appl_sequence_num,
appl_inst_status = new_references.appl_inst_status, --arvsrini igsm
ais_reason = new_references.ais_reason,
decline_ofr_reason = new_references.decline_ofr_reason
where ROWID = X_ROWID;
p_action => 'UPDATE' ,
x_rowid => X_ROWID);
end UPDATE_ROW;
cursor c1 is select ROWID from IGS_AD_PS_APPL_INST_ALL
where PERSON_ID= X_PERSON_ID
and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
INSERT_ROW (
X_ROWID,
X_ORG_ID,
X_PERSON_ID,
X_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD,
X_SEQUENCE_NUMBER,
X_PREDICTED_GPA,
X_ACADEMIC_INDEX,
X_ADM_CAL_TYPE,
X_APP_FILE_LOCATION,
X_ADM_CI_SEQUENCE_NUMBER,
X_COURSE_CD,
X_APP_SOURCE_ID,
X_CRV_VERSION_NUMBER,
X_WAITLIST_RANK,
X_LOCATION_CD,
X_ATTENT_OTHER_INST_CD,
X_ATTENDANCE_MODE,
X_EDU_GOAL_PRIOR_ENROLL_ID,
X_ATTENDANCE_TYPE,
X_DECISION_MAKE_ID,
X_UNIT_SET_CD,
X_DECISION_DATE,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_DECISION_REASON_ID,
X_US_VERSION_NUMBER,
X_DECISION_NOTES,
X_PENDING_REASON_ID,
X_PREFERENCE_NUMBER,
X_ADM_DOC_STATUS,
X_ADM_ENTRY_QUAL_STATUS,
X_DEFICIENCY_IN_PREP,
X_LATE_ADM_FEE_STATUS,
X_SPL_CONSIDER_COMMENTS,
X_APPLY_FOR_FINAID,
X_FINAID_APPLY_DATE,
X_ADM_OUTCOME_STATUS,
x_adm_otcm_stat_auth_per_id,
X_ADM_OUTCOME_STATUS_AUTH_DT,
X_ADM_OUTCOME_STATUS_REASON,
X_OFFER_DT,
X_OFFER_RESPONSE_DT,
X_PRPSD_COMMENCEMENT_DT,
X_ADM_CNDTNL_OFFER_STATUS,
X_CNDTNL_OFFER_SATISFIED_DT,
x_cndnl_ofr_must_be_stsfd_ind,
X_ADM_OFFER_RESP_STATUS,
X_ACTUAL_RESPONSE_DT,
X_ADM_OFFER_DFRMNT_STATUS,
X_DEFERRED_ADM_CAL_TYPE,
X_DEFERRED_ADM_CI_SEQUENCE_NUM,
X_DEFERRED_TRACKING_ID,
X_ASS_RANK,
X_SECONDARY_ASS_RANK,
x_intr_accept_advice_num,
X_ASS_TRACKING_ID,
X_FEE_CAT,
X_HECS_PAYMENT_OPTION,
X_EXPECTED_COMPLETION_YR,
X_EXPECTED_COMPLETION_PERD,
X_CORRESPONDENCE_CAT,
X_ENROLMENT_CAT,
X_FUNDING_SOURCE,
X_APPLICANT_ACPTNCE_CNDTN,
X_CNDTNL_OFFER_CNDTN,
X_MODE,
X_SS_APPLICATION_ID,
X_SS_PWD,
X_AUTHORIZED_DT ,
X_AUTHORIZING_PERS_ID,
X_ENTRY_STATUS,
X_ENTRY_LEVEL,
X_SCH_APL_TO_ID,
X_IDX_CALC_DATE,
X_WAITLIST_STATUS,
X_ATTRIBUTE21,
X_ATTRIBUTE22,
X_ATTRIBUTE23,
X_ATTRIBUTE24,
X_ATTRIBUTE25,
X_ATTRIBUTE26,
X_ATTRIBUTE27,
X_ATTRIBUTE28,
X_ATTRIBUTE29,
X_ATTRIBUTE30,
X_ATTRIBUTE31,
X_ATTRIBUTE32,
X_ATTRIBUTE33,
X_ATTRIBUTE34,
X_ATTRIBUTE35,
X_ATTRIBUTE36,
X_ATTRIBUTE37,
X_ATTRIBUTE38,
X_ATTRIBUTE39,
X_ATTRIBUTE40,
x_fut_acad_cal_type ,
x_fut_acad_ci_sequence_number ,
x_fut_adm_cal_type ,
x_fut_adm_ci_sequence_number ,
x_prev_term_adm_appl_number ,
x_prev_term_sequence_number ,
x_fut_term_adm_appl_number ,
x_fut_term_sequence_number ,
x_def_acad_cal_type ,
x_def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num,
x_def_term_adm_appl_num,
x_def_appl_sequence_num,
x_appl_inst_status, --arvsrini igsm
x_ais_reason,
x_decline_ofr_reason
);
UPDATE_ROW (
X_ROWID,
X_PERSON_ID,
X_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD,
X_SEQUENCE_NUMBER,
X_PREDICTED_GPA,
X_ACADEMIC_INDEX,
X_ADM_CAL_TYPE,
X_APP_FILE_LOCATION,
X_ADM_CI_SEQUENCE_NUMBER,
X_COURSE_CD,
X_APP_SOURCE_ID,
X_CRV_VERSION_NUMBER,
X_WAITLIST_RANK,
X_LOCATION_CD,
X_ATTENT_OTHER_INST_CD,
X_ATTENDANCE_MODE,
X_EDU_GOAL_PRIOR_ENROLL_ID,
X_ATTENDANCE_TYPE,
X_DECISION_MAKE_ID,
X_UNIT_SET_CD,
X_DECISION_DATE,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_DECISION_REASON_ID,
X_US_VERSION_NUMBER,
X_DECISION_NOTES,
X_PENDING_REASON_ID,
X_PREFERENCE_NUMBER,
X_ADM_DOC_STATUS,
X_ADM_ENTRY_QUAL_STATUS,
X_DEFICIENCY_IN_PREP,
X_LATE_ADM_FEE_STATUS,
X_SPL_CONSIDER_COMMENTS,
X_APPLY_FOR_FINAID,
X_FINAID_APPLY_DATE,
X_ADM_OUTCOME_STATUS,
x_adm_otcm_stat_auth_per_id,
X_ADM_OUTCOME_STATUS_AUTH_DT,
X_ADM_OUTCOME_STATUS_REASON,
X_OFFER_DT,
X_OFFER_RESPONSE_DT,
X_PRPSD_COMMENCEMENT_DT,
X_ADM_CNDTNL_OFFER_STATUS,
X_CNDTNL_OFFER_SATISFIED_DT,
x_cndnl_ofr_must_be_stsfd_ind,
X_ADM_OFFER_RESP_STATUS,
X_ACTUAL_RESPONSE_DT,
X_ADM_OFFER_DFRMNT_STATUS,
X_DEFERRED_ADM_CAL_TYPE,
X_DEFERRED_ADM_CI_SEQUENCE_NUM,
X_DEFERRED_TRACKING_ID,
X_ASS_RANK,
X_SECONDARY_ASS_RANK,
x_intr_accept_advice_num,
X_ASS_TRACKING_ID,
X_FEE_CAT,
X_HECS_PAYMENT_OPTION,
X_EXPECTED_COMPLETION_YR,
X_EXPECTED_COMPLETION_PERD,
X_CORRESPONDENCE_CAT,
X_ENROLMENT_CAT,
X_FUNDING_SOURCE,
X_APPLICANT_ACPTNCE_CNDTN,
X_CNDTNL_OFFER_CNDTN,
X_MODE,
x_ss_application_id,
x_ss_pwd ,
X_AUTHORIZED_DT ,
X_AUTHORIZING_PERS_ID,
X_ENTRY_STATUS,
X_ENTRY_LEVEL,
X_SCH_APL_TO_ID,
X_IDX_CALC_DATE,
X_WAITLIST_STATUS,
X_ATTRIBUTE21,
X_ATTRIBUTE22,
X_ATTRIBUTE23,
X_ATTRIBUTE24,
X_ATTRIBUTE25,
X_ATTRIBUTE26,
X_ATTRIBUTE27,
X_ATTRIBUTE28,
X_ATTRIBUTE29,
X_ATTRIBUTE30,
X_ATTRIBUTE31,
X_ATTRIBUTE32,
X_ATTRIBUTE33,
X_ATTRIBUTE34,
X_ATTRIBUTE35,
X_ATTRIBUTE36,
X_ATTRIBUTE37,
X_ATTRIBUTE38,
X_ATTRIBUTE39,
X_ATTRIBUTE40,
x_fut_acad_cal_type ,
x_fut_acad_ci_sequence_number ,
x_fut_adm_cal_type ,
x_fut_adm_ci_sequence_number ,
x_prev_term_adm_appl_number ,
x_prev_term_sequence_number ,
x_fut_term_adm_appl_number ,
x_fut_term_sequence_number ,
x_def_acad_cal_type ,
x_def_acad_ci_sequence_num,
x_def_prev_term_adm_appl_num,
x_def_prev_appl_sequence_num,
x_def_term_adm_appl_num,
x_def_appl_sequence_num,
x_appl_inst_status, --arvsrini igsm
x_ais_reason,
x_decline_ofr_reason
);
procedure DELETE_ROW (
X_ROWID in VARCHAR2,
x_mode IN VARCHAR2
) IS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
begin
Before_DML (
p_action => 'DELETE',
x_rowid => X_ROWID
);
delete from IGS_AD_PS_APPL_INST_ALL
where ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
end DELETE_ROW;
SELECT choice_number, alt_appl_id
FROM igs_ad_appl
WHERE person_id = cp_n_person_id
AND admission_appl_number = cp_n_admission_appl_no ;
SELECT party_number person_number
FROM hz_parties hz
WHERE hz.party_id = cp_n_person_id;
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE sysdate BETWEEN start_dt AND NVL(end_dt, sysdate)
AND person_id_type IN ('UCASID', 'SWASID', 'NMASID', 'GTTRID')
AND pe_person_id = cp_n_person_id ;
FUNCTION check_non_updateable_list RETURN BOOLEAN IS --arvsrini igsm
BEGIN
IF NVL(old_references.predicted_gpa,-1) <> NVL(new_references.predicted_gpa,-1)THEN
RETURN TRUE;
IF NVL(TRUNC(old_references.Last_Update_Date),IGS_GE_DATE.IGSDATE('1900/01/01')) <> NVL(TRUNC(new_references.Last_Update_Date),IGS_GE_DATE.IGSDATE('1900/01/01'))THEN
RETURN TRUE;
IF NVL(old_references.Last_Updated_By,-99999) <> NVL(new_references.Last_Updated_By,-99999)THEN
RETURN TRUE;
IF NVL(old_references.Last_Update_Login,-99999) <> NVL(new_references.Last_Update_Login,-99999)THEN
RETURN TRUE;
IF NVL(TRUNC(old_references.Program_Update_Date),IGS_GE_DATE.IGSDATE('1900/01/01')) <> NVL(TRUNC(new_references.Program_Update_Date),IGS_GE_DATE.IGSDATE('1900/01/01'))THEN
RETURN TRUE;
END check_non_updateable_list;