[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
abhiskum 25-Aug-2005 Added procedures DELETE_PERSTMT_ATTACHMENT_UP, ADD_PERSTMT_ATTACHMENT_UP for
Update Submitted Applications Page in SS Admin Flow; and
DELETE_PERSTMT_ATTACHMENT, ADD_PERSTMT_ATTACHMENT for Supporting Evidence Page
in SS Applicant Floe, for the IGS.M build
abhiskum 21-Mar-2005 Removed call to Update_Appl_Ofres_Inst() in process_OneStop2 for Bug 4234911.
pathipat 17-Jun-2003 Enh 2831587 FI210 Credit Card Fund Transfer build
Modified procedure update_ad_offer_resp_and_fee() and call to
igs_ad_app_req_pkg.insert_row in insert_application_fee()
smadathi 29-Nov-2002 Enh#2584986.Modifications done in procedures update_ad_offer_resp_and_fee.
vvutukur 26-Nov-2002 Enh#2584986.Modifications done in procedures update_ad_offer_resp_and_fee,
insert_application_fee.
nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build
knag 29-OCT-2002 Bug 2647482 removed local procedure insert_acad_honors and its calls
stammine 10-Jun-2005 Added procedures at for IGS.M build
******************************************************************/
g_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE,
p_nominated_course_cd IN igs_pe_typ_instances_all.nominated_course_cd%TYPE,
p_person_id IN igs_pe_typ_instances_all.person_id%TYPE,
p_adm_appl_number IN igs_pe_typ_instances_all.admission_appl_number%TYPE);
PROCEDURE insert_othinst(p_person_id IN NUMBER,
p_adm_appl_id IN NUMBER,
p_admission_appl_number IN NUMBER);
PROCEDURE insert_unit_set_dtls(p_sequence_number IN igs_ad_unit_sets.sequence_number%TYPE,
p_nominated_course_cd IN igs_ad_unit_sets.nominated_course_cd%TYPE,
p_person_id IN igs_ad_unit_sets.person_id%TYPE,
p_adm_appl_number IN igs_ad_unit_sets.admission_appl_number%TYPE,
p_ss_adm_appl_id IN igs_ss_app_pgm_stg.ss_adm_appl_id%TYPE);
PROCEDURE insert_acad_interest(p_person_id IN igs_ad_acad_interest.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_acadin_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_acad_interest.admission_appl_number%TYPE);
PROCEDURE insert_applicant_intent(p_person_id IN igs_ad_app_intent.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_appint_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_app_intent.admission_appl_number%TYPE);
PROCEDURE insert_spl_talent(p_person_id IN igs_ad_spl_talents.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_spltal_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_spl_talents.admission_appl_number%TYPE);
PROCEDURE insert_special_interest(p_person_id IN igs_ad_spl_interests.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_splint_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_spl_interests.admission_appl_number%TYPE);
PROCEDURE insert_edugoal_details(p_person_id IN igs_ad_edugoal.person_id%TYPE,
p_adm_appl_number IN igs_ad_edugoal.admission_appl_number%TYPE,
p_ss_adm_appl_id IN igs_ss_app_pgm_stg.ss_adm_appl_id%TYPE,
p_nominated_course_cd IN igs_ad_edugoal.nominated_course_cd%TYPE,
p_sequence_number IN igs_ad_edugoal.sequence_number%TYPE); --this procedure has been modified, nshee Bug 2622488
PROCEDURE insert_application_fee(p_person_id IN igs_ad_app_req.person_id%TYPE,
p_adm_appl_id IN igs_ss_app_req_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_app_req.admission_appl_number%TYPE);
4.On Successful Transfer of data , deletes the Staging table Data.
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
l_message_name VARCHAR2(2000);
SELECT *
FROM igs_ss_adm_appl_stg
WHERE person_id = x_person_id
AND ss_adm_appl_id = x_application_id;
/* delete_ss_appl_stg(x_message_name => l_message_name,
x_return_status => l_return_status,
p_adm_appl_number => x_application_id,
p_person_id => x_person_id);
b.Insert admission application, using Common API Call IGS_AD_GEN_014.insert_adm_appl
Known limitations,enhancements,remarks:
Change History
Who When What
rrengara 11-jul-2002 Added UK Parameters choice_number and routre pref to insert_adm_appl procedure for bug 2448262 (D) and 2455053 (P)
knag 21-Nov-2002 Added alt_appl_id param to call to insert_adm_appl for bug 2664410
pbondugu 28-Mar-2003 Passed funding_source as NULL to procedure call IGS_AD_GEN_014.insert_adm_appl_prog_inst
*****************************************************************************************/
--Local Var Declaration
l_message_name VARCHAR2(2000);
logDetail('Before call to IGS_AD_GEN_014.insert_adm_appl', 'S');
IF IGS_AD_GEN_014.insert_adm_appl( -- IF :1
p_person_id => p_appl_rec.person_id,
p_appl_dt => p_appl_rec.appl_date,
p_acad_cal_type => p_appl_rec.acad_cal_type,
p_acad_ci_sequence_number => p_appl_rec.acad_cal_seq_number,
p_adm_cal_type => p_appl_rec.adm_cal_type,
p_adm_ci_sequence_number => p_appl_rec.adm_cal_seq_number,
p_admission_cat => p_appl_rec.admission_cat,
p_s_admission_process_type => p_appl_rec.s_adm_process_type,
p_adm_appl_status => l_adm_appl_status,
p_adm_fee_status => l_adm_fee_status, --IN/OUT
p_tac_appl_ind => 'N',
p_adm_appl_number => l_admission_appl_number, --OUT
p_message_name => l_message_name, --OUT
p_spcl_grp_1 => p_appl_rec.spcl_grp_1,
p_spcl_grp_2 => p_appl_rec.spcl_grp_2,
p_common_app => NULL,
p_application_type => p_appl_rec.admission_application_type,
p_choice_number => null,
p_routeb_pref => NULL,
p_alt_appl_id => NULL,
p_appl_fee_amt => p_appl_rec.appl_fee_amt) =
FALSE THEN
ROLLBACK TO sp_save_point1;
logDetail('IGS_AD_GEN_014.insert_adm_appl Failed,Returned with FALSE and Message: ' ||
l_message_name,
'S');
a.Insert Admission Program Application (IGS_AD_GEN_014.insert_adm_appl_prog)
b.validate descriptive flexfield columns.
c.Insert Admission Program Application Instance (IGS_AD_GEN_014.insert_adm_appl_prog_inst)
d.Change Person Type To Applicant (update_person_type- Local Procedure Call)
e.Insert Unit Set Details (Major First Choice/Major Second Choice) (insert_unit_set_dtls- Local Procedure Call)
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
--Local Variable Declarations
l_message_name VARCHAR2(2000);
SELECT d.*, m.app_source_id
FROM igs_ss_adm_appl_stg m, IGS_SS_APP_PGM_STG d
WHERE m.ss_adm_appl_id = d.ss_adm_appl_id
AND m.ss_adm_appl_id = p_appl_rec.ss_adm_appl_id
AND m.person_id = p_appl_rec.person_id;
logDetail('Before call to IGS_AD_GEN_014.insert_adm_appl_prog', 'S');
IF IGS_AD_GEN_014.insert_adm_appl_prog(p_person_id => p_appl_rec.person_id,
p_adm_appl_number => p_adm_appl_number,
p_nominated_course_cd => c_admappl_pgm_rec.nominated_course_cd,
p_transfer_course_cd => NULL,
p_basis_for_admission_type => NULL,
p_admission_cd => NULL,
p_req_for_reconsideration_ind => 'N', -- (request for reconsideration indicator)
p_req_for_adv_standing_ind => 'N', -- (request for advanced standing indicator)
p_message_name => l_message_name) =
FALSE THEN
ROLLBACK TO sp_save_point1;
logDetail('IGS_AD_GEN_014.insert_adm_appl_prog Failed,Returned with FALSE and Message: ' ||
l_message_name,
'S');
logDetail('before call to local proc insert_adm_appl_prog_inst', 'S');
IF IGS_AD_GEN_014.insert_adm_appl_prog_inst(p_person_id => p_appl_rec.person_id,
p_admission_appl_number => p_adm_appl_number,
p_acad_cal_type => p_appl_rec.acad_cal_type,
p_acad_ci_sequence_number => p_appl_rec.acad_cal_seq_number,
p_adm_cal_type => p_appl_rec.adm_cal_type,
p_adm_ci_sequence_number => p_appl_rec.adm_cal_seq_number,
p_admission_cat => p_appl_rec.admission_cat,
p_s_admission_process_type => p_appl_rec.s_adm_process_type,
p_appl_dt => p_appl_rec.appl_date,
p_adm_fee_status => l_adm_fee_status,
p_preference_number => c_admappl_pgm_rec.preference_number,
p_offer_dt => NULL,
p_offer_response_dt => NULL,
p_course_cd => c_admappl_pgm_rec.nominated_course_cd,
p_crv_version_number => c_admappl_pgm_rec.crv_version_number,
p_location_cd => c_admappl_pgm_rec.location_cd,
p_attendance_mode => c_admappl_pgm_rec.attendance_mode,
p_attendance_type => c_admappl_pgm_rec.attendance_type,
p_unit_set_cd => c_admappl_pgm_rec.final_unit_set_cd, --earlier passed as null, build 2622488 nshee
p_us_version_number => c_admappl_pgm_rec.final_unit_set_cd_ver, --earlier passed as null, build 2622488 nshee
p_fee_cat => NULL,
p_correspondence_cat => NULL,
p_enrolment_cat => NULL,
p_funding_source => NULL,
p_edu_goal_prior_enroll => c_admappl_pgm_rec.edu_goal_prior_enroll,
p_app_source_id => c_admappl_pgm_rec.app_source_id,
p_apply_for_finaid => c_admappl_pgm_rec.apply_for_finaid,
p_finaid_apply_date => c_admappl_pgm_rec.finaid_apply_date,
p_attribute_category => c_admappl_pgm_rec.attribute_category,
p_attribute1 => c_admappl_pgm_rec.attribute1,
p_attribute2 => c_admappl_pgm_rec.attribute2,
p_attribute3 => c_admappl_pgm_rec.attribute3,
p_attribute4 => c_admappl_pgm_rec.attribute4,
p_attribute5 => c_admappl_pgm_rec.attribute5,
p_attribute6 => c_admappl_pgm_rec.attribute6,
p_attribute7 => c_admappl_pgm_rec.attribute7,
p_attribute8 => c_admappl_pgm_rec.attribute8,
p_attribute9 => c_admappl_pgm_rec.attribute9,
p_attribute10 => c_admappl_pgm_rec.attribute10,
p_attribute11 => c_admappl_pgm_rec.attribute11,
p_attribute12 => c_admappl_pgm_rec.attribute12,
p_attribute13 => c_admappl_pgm_rec.attribute13,
p_attribute14 => c_admappl_pgm_rec.attribute14,
p_attribute15 => c_admappl_pgm_rec.attribute15,
p_attribute16 => c_admappl_pgm_rec.attribute16,
p_attribute17 => c_admappl_pgm_rec.attribute17,
p_attribute18 => c_admappl_pgm_rec.attribute18,
p_attribute19 => c_admappl_pgm_rec.attribute19,
p_attribute20 => c_admappl_pgm_rec.attribute20,
p_attribute21 => c_admappl_pgm_rec.attribute21,
p_attribute22 => c_admappl_pgm_rec.attribute22,
p_attribute23 => c_admappl_pgm_rec.attribute23,
p_attribute24 => c_admappl_pgm_rec.attribute24,
p_attribute25 => c_admappl_pgm_rec.attribute25,
p_attribute26 => c_admappl_pgm_rec.attribute26,
p_attribute27 => c_admappl_pgm_rec.attribute27,
p_attribute28 => c_admappl_pgm_rec.attribute28,
p_attribute29 => c_admappl_pgm_rec.attribute29,
p_attribute30 => c_admappl_pgm_rec.attribute30,
p_attribute31 => c_admappl_pgm_rec.attribute31,
p_attribute32 => c_admappl_pgm_rec.attribute32,
p_attribute33 => c_admappl_pgm_rec.attribute33,
p_attribute34 => c_admappl_pgm_rec.attribute34,
p_attribute35 => c_admappl_pgm_rec.attribute35,
p_attribute36 => c_admappl_pgm_rec.attribute36,
p_attribute37 => c_admappl_pgm_rec.attribute37,
p_attribute38 => c_admappl_pgm_rec.attribute38,
p_attribute39 => c_admappl_pgm_rec.attribute39,
p_attribute40 => c_admappl_pgm_rec.attribute40,
p_ss_application_id => NULL,
p_sequence_number => v_sequence_number,
p_return_type => l_return_type,
p_error_code => l_error_code,
p_message_name => l_message_name,
p_entry_status => c_admappl_pgm_rec.entry_status,
p_entry_level => c_admappl_pgm_rec.entry_level,
p_sch_apl_to_id => c_admappl_pgm_rec.sch_apl_to_id) =
FALSE THEN
ROLLBACK TO sp_save_point1;
logDetail('insert_adm_appl_prog_inst Failed,Returned with FALSE and Message: ' ||
l_message_name,
'S');
insert_unit_set_dtls(p_sequence_number =>v_sequence_number
,p_nominated_course_cd =>c_admappl_pgm_rec.nominated_course_cd
,p_person_id =>p_appl_rec.person_id
,p_adm_appl_number =>p_adm_appl_number
,p_unit_set_cd =>c_admappl_pgm_rec.unit_set_1
,p_ver_no =>c_admappl_pgm_rec.unit_set_1_ver_number);
insert_unit_set_dtls(p_sequence_number =>v_sequence_number
,p_nominated_course_cd =>c_admappl_pgm_rec.nominated_course_cd
,p_person_id =>p_appl_rec.person_id
,p_adm_appl_number =>p_adm_appl_number
,p_unit_set_cd =>c_admappl_pgm_rec.unit_set_2
,p_ver_no =>c_admappl_pgm_rec.unit_set_2_ver_number);
insert_edugoal_details(p_person_id => p_appl_rec.person_id,
p_adm_appl_number => p_adm_appl_number,
p_ss_adm_appl_id => c_admappl_pgm_rec.ss_adm_appl_id,
p_nominated_course_cd => c_admappl_pgm_rec.nominated_course_cd,
p_sequence_number => v_sequence_number);
insert_unit_set_dtls(p_person_id => p_appl_rec.person_id,
p_adm_appl_number => p_adm_appl_number,
p_nominated_course_cd => c_admappl_pgm_rec.nominated_course_cd,
p_sequence_number => v_sequence_number,
p_ss_adm_appl_id => c_admappl_pgm_rec.ss_adm_appl_id); --added this call build 2622488
END IF; -- insert_adm_appl_prog_inst IF ends.
PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE,
p_nominated_course_cd IN igs_pe_typ_instances_all.nominated_course_cd%TYPE,
p_person_id IN igs_pe_typ_instances_all.person_id%TYPE,
p_adm_appl_number IN igs_pe_typ_instances_all.admission_appl_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose:
1. Updates Person Type to Applicant on Successful Import/Transfer of the Application/Prog/Prog Inst
2. Flow:
a.IGS_PE_TYP_INSTANCES_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
npalanis 10-JUN-2003 Bug:2923413 igs_pe_typ_instances_pkg.update_row call
modified for the new employment category column added in the table
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = l_system_type;
logDetail('Before call to IGS_PE_TYP_INSTANCES_PKG.insert_row', 'S');
IGS_PE_TYP_INSTANCES_PKG.insert_row(x_rowid => l_rowid,
x_org_id => l_org_id,
x_person_id => p_person_id,
x_course_cd => NULL,
x_type_instance_id => l_type_instance_id,
x_person_type_code => l_person_type_code,
x_cc_version_number => NULL,
x_funnel_status => NULL,
x_admission_appl_number => p_adm_appl_number,
x_nominated_course_cd => p_nominated_course_cd, --c_admappl_pgm_rec.nominated_course_cd,
x_ncc_version_number => NULL,
x_sequence_number => p_sequence_number,
x_start_date => SYSDATE,
x_end_date => NULL,
x_create_method => 'CREATE_APPL_INSTANCE',
x_ended_by => NULL,
x_end_method => NULL,
x_mode => lv_mode, -- enable security for Admin
x_emplmnt_category_code => null);
logDetail('inside update_person_type' ||
'Exception from IGS_PE_TYP_INSTANCES_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.update_person_type -'||SQLERRM);
END update_person_type;
PROCEDURE insert_unit_set_dtls(p_sequence_number IN igs_ad_unit_sets.sequence_number%TYPE,
p_nominated_course_cd IN igs_ad_unit_sets.nominated_course_cd%TYPE,
p_person_id IN igs_ad_unit_sets.person_id%TYPE,
p_adm_appl_number IN igs_ad_unit_sets.admission_appl_number%TYPE,
p_ss_adm_appl_id IN igs_ss_app_pgm_stg.ss_adm_appl_id%TYPE) AS
-- ,p_unit_set_cd IN igs_ad_unit_sets.unit_set_cd%TYPE
-- ,p_ver_no IN igs_ad_unit_sets.version_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts Unit Set Details (Major1/Major2)
FLOW: IGS_AD_UNIT_SETS_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
nshee 29-OCT-2002 Modified it, now it inserts data from desired unit sets and not the major1/major2.
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT MAX(rank), 0) + 1
FROM igs_ad_unit_sets
WHERE
person_id = p_person_id AND
admission_appl_number = p_adm_appl_number AND
nominated_course_cd = p_nominated_course_cd;
SELECT unit_set_cd, version_number, rank
FROM igs_ss_ad_unitse_stg
WHERE ss_admappl_pgm_id =
(SELECT ss_admappl_pgm_id
FROM igs_ss_app_pgm_stg
WHERE ss_adm_appl_id = p_ss_adm_appl_id --p_adm_appl_number
AND nominated_course_cd = p_nominated_course_cd
AND person_id = p_person_id);
logHeader('insert_unit_set_dtls', 'S');
logDetail('Before call to IGS_AD_UNIT_SETS_PKG.insert_row', 'S');
IGS_AD_UNIT_SETS_PKG.insert_row(x_rowid => l_rowid,
x_unit_set_id => l_unit_set_id,
x_person_id => p_person_id,
x_admission_appl_number => p_adm_appl_number,
x_nominated_course_cd => p_nominated_course_cd,
x_sequence_number => p_sequence_number,
x_unit_set_cd => c_des_unit_sets_rec.unit_set_cd,
x_version_number => c_des_unit_sets_rec.version_number,
x_rank => c_des_unit_sets_rec.rank,
x_mode => lv_mode -- enable security for Admin
);
logDetail('Inside insert_unit_set_dtls' ||
'Exception from IGS_AD_UNIT_SETS_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_unit_set_dtls -'||SQLERRM);
END insert_unit_set_dtls;
PROCEDURE insert_acad_interest(p_person_id IN igs_ad_acad_interest.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_acadin_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_acad_interest.admission_appl_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts Academic Honors Details (For an Application)
FLOW: IGS_AD_ACAD_HONORS_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT field_of_study
FROM igs_ss_ad_acadin_stg
WHERE ss_adm_appl_id = p_adm_appl_id;
logHeader('insert_acad_interest', 'S');
logDetail('Before call to IGS_AD_ACAD_INTEREST_PKG.insert_row',
'S');
IGS_AD_ACAD_INTEREST_PKG.insert_row(x_rowid => l_rowid,
x_acad_interest_id => l_acad_interest_id,
x_person_id => p_person_id,
x_admission_appl_number => p_adm_appl_number,
x_field_of_study => c_acad_interest_rec.field_of_study,
x_mode => lv_mode -- enable security for Admin
);
logDetail('Inside insert_acad_interest' ||
'Exception from IGS_AD_ACAD_INTEREST_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_acad_interest -'||SQLERRM);
END insert_acad_interest;
PROCEDURE insert_applicant_intent(p_person_id IN igs_ad_app_intent.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_appint_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_app_intent.admission_appl_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts Academic Honors Details (For an Application)
FLOW: IGS_AD_ACAD_HONORS_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT intent_type_id
FROM igs_ss_ad_appint_stg
WHERE ss_adm_appl_id = p_adm_appl_id;
logHeader('insert_applicant_intent', 'S');
logDetail('Before call to IGS_AD_APP_INTENT_PKG.insert_row', 'S');
IGS_AD_APP_INTENT_PKG.insert_row(x_rowid => l_rowid,
x_app_intent_id => l_app_intent_id,
x_person_id => p_person_id,
x_admission_appl_number => p_adm_appl_number,
x_intent_type_id => c_applicant_intent_rec.intent_type_id,
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_mode => lv_mode -- enable security for Admin
);
logDetail('Inside insert_applicant_intent' ||
'Exception from IGS_AD_APP_INTENT_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_applicant_intent -'||SQLERRM);
END insert_applicant_intent;
PROCEDURE insert_spl_talent(p_person_id IN igs_ad_spl_talents.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_spltal_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_spl_talents.admission_appl_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts Academic Honors Details (For an Application)
FLOW: IGS_AD_ACAD_HONORS_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT special_talent_type_id
FROM igs_ss_ad_spltal_stg
WHERE ss_adm_appl_id = p_adm_appl_id;
logHeader('insert_spl_talent', 'S');
logDetail('Before call to IGS_AD_SPL_TALENTS_PKG.insert_row', 'S');
IGS_AD_SPL_TALENTS_PKG.insert_row(x_rowid => l_rowid,
x_spl_talent_id => l_spl_talent_id,
x_person_id => p_person_id,
x_admission_appl_number => p_adm_appl_number,
x_special_talent_type_id => c_spl_talent_rec.special_talent_type_id,
x_mode => lv_mode -- enable security for Admin
);
logDetail('Inside insert_spl_talent' ||
'Exception from IGS_AD_SPL_TALENTS_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_spl_talent -'||SQLERRM);
END insert_spl_talent;
PROCEDURE insert_special_interest(p_person_id IN igs_ad_spl_interests.person_id%TYPE,
p_adm_appl_id IN igs_ss_ad_splint_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_spl_interests.admission_appl_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts Academic Honors Details (For an Application)
FLOW: IGS_AD_ACAD_HONORS_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT special_interest_type_id
FROM igs_ss_ad_splint_stg
WHERE ss_adm_appl_id = p_adm_appl_id;
logHeader('insert_special_interest', 'S');
logDetail('Before call to IGS_AD_SPL_INTERESTS_PKG.insert_row',
'S');
IGS_AD_SPL_INTERESTS_PKG.insert_row(x_rowid => l_rowid,
x_spl_interest_id => l_spl_interest_id,
x_person_id => p_person_id,
x_admission_appl_number => p_adm_appl_number,
x_special_interest_type_id => c_special_interest_rec.special_interest_type_id,
x_mode => lv_mode -- enable security for Admin
);
logDetail('Inside insert_special_interest' ||
'Exception from IGS_AD_SPL_INTERESTS_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_special_interest -'||SQLERRM);
END insert_special_interest;
PROCEDURE insert_othinst(p_person_id IN NUMBER,
p_adm_appl_id IN NUMBER,
p_admission_appl_number IN NUMBER) AS
l_rowid VARCHAR2(25);
SELECT othinst.ss_othins_id,
othinst.ss_adm_appl_id,
othinst.institution_code,
i.name,
othinst.new_institution
FROM igs_ss_ad_othins_stg othinst,
igs_ss_adm_appl_stg appl,
igs_or_institution i,
igs_pe_hz_parties php
WHERE othinst.ss_adm_appl_id = appl.ss_adm_appl_id
AND othinst.institution_code = i.institution_cd
AND othinst.institution_code = php.oss_org_unit_cd
AND appl.person_id = p_person_id
AND othinst.ss_adm_appl_id = p_adm_appl_id; -- p_admission_appl_number;
IGS_AD_OTHER_INST_PKG.insert_row(x_rowid => l_rowid,
x_other_inst_id => l_other_inst_id,
x_person_id => p_person_id,
x_admission_appl_number => p_admission_appl_number,
x_institution_code => c_othinst_rec.institution_code,
x_mode => lv_mode, -- enable security for Admin
x_new_institution => c_othinst_rec.new_institution);
logDetail('Inside insert_othinst' ||
'Exception from IGS_AD_OTHER_INST_PKG.insert_row ' ||
SQLERRM || 'person_id : ' ||
IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_othinst -'||SQLERRM);
END insert_othinst;
PROCEDURE insert_edugoal_details(p_person_id IN igs_ad_edugoal.person_id%TYPE,
p_adm_appl_number IN igs_ad_edugoal.admission_appl_number%TYPE,
p_ss_adm_appl_id IN igs_ss_app_pgm_stg.ss_adm_appl_id%TYPE,
p_nominated_course_cd IN igs_ad_edugoal.nominated_course_cd%TYPE,
p_sequence_number IN igs_ad_edugoal.sequence_number%TYPE) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts Post Educational Goal Details (For an Application Program Instance)
FLOW: IGS_AD_EDUGOAL_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
nshee 29-oct-2002 modified due to design change in build 2622488
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT edu_goal_id
FROM igs_ss_ad_edugoa_stg
WHERE ss_admappl_pgm_id =
(SELECT ss_admappl_pgm_id
FROM igs_ss_app_pgm_stg
WHERE ss_adm_appl_id = p_ss_adm_appl_id -- p_adm_appl_number
AND nominated_course_cd = p_nominated_course_cd
AND person_id = p_person_id);
logHeader('insert_edugoal_details', 'S');
logDetail('Before call to IGS_AD_EDUGOAL_PKG.insert_row', 'S');
IGS_AD_EDUGOAL_PKG.insert_row(X_ROWID => l_rowid,
X_POST_EDUGOAL_ID => l_post_edugoal_id,
X_PERSON_ID => p_person_id,
X_ADMISSION_APPL_NUMBER => p_adm_appl_number,
X_NOMINATED_COURSE_CD => p_nominated_course_cd,
X_SEQUENCE_NUMBER => p_sequence_number,
X_EDU_GOAL_ID => c_postenroll_edu_goal_rec.edu_goal_id,
x_mode => lv_mode-- enable security for Admin
);
logDetail('insert_edugoal_details' ||
'Exception from IGS_AD_EDUGOAL_PKG.insert_row' || SQLERRM ||
'person_id : ' || IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_edugoal_details -'||SQLERRM);
END insert_edugoal_details;
PROCEDURE insert_ss_appl_stg(x_message_name OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_person_id IN NUMBER,
p_application_type IN VARCHAR2,
p_adm_appl_number IN NUMBER,
p_admission_cat IN VARCHAR2,
p_s_adm_process_type IN VARCHAR2,
p_login_id IN NUMBER,
p_app_source_id IN NUMBER) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Inserts record from Self Service Admissions form (New Application Screen)
Known limitations,enhancements,remarks:
Change History
Who When What
rboddu 17-FEB-2002 Added the parameter p_app_source_id. The same is inserted into the table.
Bug : 2224624
*****************************************************************************************/
BEGIN
BEGIN
INSERT INTO igs_ss_adm_appl_stg
(ss_adm_appl_id,
person_id,
appl_date,
admission_application_type,
admission_cat,
s_adm_process_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
app_source_id)
VALUES
(p_adm_appl_number,
p_person_id,
SYSDATE,
p_application_type,
p_admission_cat,
p_s_adm_process_type,
SYSDATE,
p_login_id,
SYSDATE,
p_login_id,
p_login_id,
p_app_source_id);
logDetail('insert igs_ss_adm_appl_stg' || SQLERRM ||
'person_id : ' || IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_ss_appl_stg -'||SQLERRM);
END insert_ss_appl_stg;
PROCEDURE insert_ss_appl_perstat_stg(p_return_status OUT NOCOPY VARCHAR2,
p_message_data OUT NOCOPY VARCHAR2,
p_person_id IN NUMBER,
p_adm_appl_id IN NUMBER,
p_admission_application_type IN VARCHAR2,
p_user_id IN NUMBER,
p_date_received IN DATE)
/*****************************************************************************************
Created By:
Date Created :
Purpose: 1. Inserts record from Self Service Admissions form (New Application Screen)
Known limitations,enhancements,remarks:
Change History
Who When What
rboddu 17-FEB-2002 Added the parameter p_date_received. The same is inserted into the table.
Bug : 2224624
rboddu 05-mar-2003 Inserting the Date_Received as TRUNC(p_date_received). Bug: 2731445
*****************************************************************************************/
AS
CURSOR c_perstat_types(l_admission_application_type IN VARCHAR2) IS
SELECT persl_stat_type
From igs_ad_aptyp_pestat
WHERE admission_application_type = l_admission_application_type;
SELECT COUNT(ss_perstat_id)
INTO l_stmt_count
FROM igs_ss_appl_perstat
WHERE person_id = p_person_id
AND ss_adm_appl_id = p_adm_appl_id;
INSERT INTO igs_ss_appl_perstat
(ss_perstat_id,
person_id,
ss_adm_appl_id,
admission_application_type,
persl_stat_type,
date_received,
created_by,
creation_date,
attach_exists,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(igs_ss_perstat_id_s.NEXTVAL,
p_person_id,
p_adm_appl_id,
p_admission_application_type,
c_perstat_types_data.persl_stat_type,
TRUNC(p_date_received),
p_user_id,
SYSDATE,
'N',
p_user_id,
SYSDATE,
p_user_id);
logDetail('insert igs_ss_appl_perstat' || SQLERRM ||
'person_id : ' || IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_ss_appl_perstat_stg -'||SQLERRM);
END insert_ss_appl_perstat_stg;
PROCEDURE delete_ss_appl_stg(x_message_name OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_adm_appl_number IN NUMBER,
p_person_id IN NUMBER) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 11-DEC-2001
Purpose: 1. Deletes record from staging tables if the Application Creation is successful
2. Also CAlled from SS Application Form to Delete an already Created Application
Known limitations,enhancements,remarks:
Change History
Who When What
tray 16-APR-02 added DML delete for deleting data from igs_ss_ad_sec_stat
after the application is transferred.
*****************************************************************************************/
l_message_name VARCHAR2(2000);
DELETE FROM igs_ss_ad_sec_stat
WHERE person_id = p_person_id
AND ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_app_req_stg
WHERE ss_adm_appl_id = p_adm_appl_number
AND person_id = p_person_id;
DELETE FROM igs_ss_appl_perstat
WHERE ss_adm_appl_id = p_adm_appl_number
AND person_id = p_person_id;
DELETE FROM igs_ss_ad_unitse_stg
WHERE ss_admappl_pgm_id IN
(SELECT ss_admappl_pgm_id
FROM igs_ss_app_pgm_stg
WHERE ss_adm_appl_id = p_adm_appl_number
AND person_id = p_person_id);
DELETE FROM igs_ss_ad_edugoa_stg
WHERE ss_admappl_pgm_id IN
(SELECT ss_admappl_pgm_id
FROM igs_ss_app_pgm_stg
WHERE ss_adm_appl_id = p_adm_appl_number
AND person_id = p_person_id);
DELETE FROM igs_ss_ad_acadin_stg
WHERE ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_ad_appint_stg
WHERE ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_ad_splint_stg
WHERE ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_ad_spltal_stg
WHERE ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_ad_othins_stg
WHERE ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_app_pgm_stg
WHERE person_id = p_person_id
AND ss_adm_appl_id = p_adm_appl_number;
DELETE FROM igs_ss_adm_appl_stg
WHERE ss_adm_appl_id = p_adm_appl_number
AND person_id = p_person_id;
logDetail('Exception from delete_ss_appl_stg, ' || SQLERRM, 'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.delete_ss_appl_stg -'||SQLERRM);
END delete_ss_appl_stg;
Purpose: 1. Deletes record from staging tables if the Application Creation is successful
2. Also CAlled from SS Application Form to Delete an already Created Application
Known limitations,enhancements,remarks:
Change History
Who When What
rboddu 17-FEB-2002 in IGS_AD_APPL_PERSTAT_PKG.Insert_Row, date_received is passed
NVL(c_ss_appl_perstat_rec.date_received,SYSDATE). Earlier it was just SYSDATE
Bug : 2224624
rboddu 05-MAR-2003 Passing Received_Date as TRUNC(SYSDATE) if it's null in the SS Table. Bug: 2731445
*****************************************************************************************/
l_rowid VARCHAR2(25);
SELECT ps.*
FROM igs_ss_appl_perstat ps
WHERE ps.ss_adm_appl_id = p_ss_adm_appl_id
AND ps.person_id = p_person_id;
SELECT APPL_PERSTAT_ID
FROM igs_ad_appl_perstat
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number;
IGS_AD_APPL_PERSTAT_PKG.Insert_Row(x_rowid => l_rowid,
x_appl_perstat_id => l_appl_perstat_id,
x_person_id => c_ss_appl_perstat_rec.person_id,
x_admission_appl_number => p_admission_appl_number,
x_persl_stat_type => c_ss_appl_perstat_rec.persl_stat_type,
x_date_received => NVL(c_ss_appl_perstat_rec.date_received,
TRUNC(SYSDATE)), --If the date_received passed from Self Service is null then insert SYSDATE into OSS Table
x_mode => lv_mode -- enable security for Admin
);
/* If insert is successful copy and delete the attachments to the IGS_AD_APPL_PERSTAT table */
BEGIN
-- bug 2407148 fix (transferring multiple personal statements)
/*
OPEN c_ad_appl_perstat;
X_last_update_login => NULL,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => 'N');
fnd_attached_documents2_pkg.delete_attachments(X_entity_name => 'IGS_SS_APPL_PERSTAT',
X_pk1_value => l_from_pk1_value,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_delete_document_flag => 'N',
X_automatically_added_flag => 'N');
/* If copy and delete is NOT successful */
x_return_status := 'E';
/* If insert is NOT successful */
x_return_status := 'E';
DELETE FROM igs_ss_appl_perstat
WHERE ss_perstat_id = c_ss_appl_perstat_rec.SS_PERSTAT_ID;
SELECT ci_adm.sequence_number adm_sequence_number,
ci_acad.cal_type acad_cal_type,
ci_acad.sequence_number acad_sequence_number
FROM igs_ca_type ct_adm,
igs_ca_inst ci_adm,
igs_ca_stat cs,
igs_ca_inst_rel cir,
igs_ca_inst ci_acad,
igs_ca_type ct_acad
WHERE ct_adm.cal_type = cp_cal_type
AND ct_adm.cal_type = ci_adm.cal_type
AND SYSDATE <= ci_adm.end_dt
AND ct_adm.s_cal_cat = 'ADMISSION'
AND ci_adm.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ci_adm.cal_type = cir.sub_cal_type
AND ci_adm.sequence_number = cir.sub_ci_sequence_number
AND ct_acad.cal_type = ci_acad.cal_type
AND ci_acad.cal_type = cir.sup_cal_type
AND ci_acad.sequence_number = cir.sup_ci_sequence_number
AND ct_acad.s_cal_cat = 'ACADEMIC'
ORDER BY ci_adm.end_dt;
SELECT inq_cal_type FROM igs_ad_cal_conf;
Select 'X'
Into l_accept
From igs_ad_prcs_cat_step
Where S_ADMISSION_STEP_TYPE = 'AUTO-ACCEPT'
And ADMISSION_CAT = p_admission_cat
And S_ADMISSION_PROCESS_TYPE = p_admission_process_type
And STEP_GROUP_TYPE <> 'TRACK'; --2402377
Select 'X'
Into l_offer
From igs_ad_prcs_cat_step
Where S_ADMISSION_STEP_TYPE = 'AUTO-OFFER'
And ADMISSION_CAT = p_admission_cat
And S_ADMISSION_PROCESS_TYPE = p_admission_process_type
And STEP_GROUP_TYPE <> 'TRACK'; -- 2402377
Select NOMINATED_COURSE_CD, SEQUENCE_NUMBER
From IGS_AD_PS_APPL_INST_ALL
Where PERSON_ID = p_person_id
And ADMISSION_APPL_NUMBER = p_admission_appl_number;
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.Before_check_update_aeps_acs';
l_debug_str := 'Beofore IGS_AD_SS_APPL_FEE_PKG.check_update_aeps_acs';
IGS_AD_SS_APPL_FEE_PKG.check_update_aeps_acs(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_msg_count,
l_msg_data);
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_check_update_aeps_acs_ret_Status';
l_debug_str := 'After check_update_aeps_acs l_return_status: ' || l_return_status;
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_check_update_aeps_acs_msg_Data';
l_debug_str := 'After check_update_aeps_acs l_msg_data: ' || l_msg_data;
IF l_return_status = 'E' THEN ----check_update_aeps_acs return status
IF p_role <> 'ADMIN' THEN
l_msg_data := 'IGS_AD_APPL_PRC_FAILED';
Update_Appl_Eqdo_Inst(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_message_data);
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.after_Update_Appl_Eqdo_Inst_ret_Stat';
l_debug_str := 'After Update_Appl_Eqdo_Inst l_return_status: ' || l_return_status;
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_Update_Appl_Eqdo_Inst_msg_Data';
l_debug_str := 'After Update_Appl_Eqdo_Inst l_message_data: ' || l_message_data;
IF l_return_status = 'E' THEN --- Update_Appl_Eqdo_Inst return status
IF p_role <> 'ADMIN' THEN
IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
IGS_AD_SS_APPL_FEE_PKG.check_offer_update(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_msg_count,
l_msg_data);
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_check_offer_update_return_Stat';
l_debug_str := 'After check_offer_update l_return_status: ' || l_return_status;
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_check_offer_update_Data_msg_Data';
l_debug_str := 'After check_offer_update l_msg_data: ' || l_msg_data;
IF l_return_status = 'E' THEN -- check_offer_update return status
IF p_role <> 'ADMIN' THEN
IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
Update_Appl_Ofr_Inst(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_message_data);
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_Update_Appl_Ofr_Inst_ret_Stat';
l_debug_str := 'After Update_Appl_Ofr_Inst l_return_status: ' || l_return_status;
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop.After_Update_Appl_Ofr_Inst_msg_Data';
l_debug_str := 'After Update_Appl_Ofr_Inst l_message_data: ' || l_message_data;
IF l_return_status = 'E' THEN -- Update_Appl_Ofr_Inst return status
IF p_role <> 'ADMIN' THEN
IGS_GE_MSG_STACK.INITIALIZE; -- Remove all other messages from stack
END IF; -- Update_Appl_Ofr_Inst return status
END IF; -- check_offer_update return status
END IF; --- Update_Appl_Eqdo_Inst return status
END IF; ----check_update_aeps_acs return status
PROCEDURE update_ad_offer_resp_and_fee(p_person_id IN NUMBER,
p_admission_appl_number IN NUMBER,
p_one_stop_ind IN VARCHAR2,
p_app_fee_amt IN NUMBER,
p_authorization_number IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_credit_card_code IN VARCHAR2,
p_credit_card_holder_name IN VARCHAR2,
p_credit_card_number IN VARCHAR2,
p_credit_card_expiration_date IN DATE,
p_gl_date IN DATE,
p_rev_gl_ccid IN NUMBER,
p_cash_gl_ccid IN NUMBER,
p_rev_account_cd IN VARCHAR2,
p_cash_account_cd IN VARCHAR2,
p_credit_card_tangible_cd IN VARCHAR2) AS
/******************************************************************
Created By:
Date Created By:
Purpose:
Known limitations,enhancements,remarks:
Change History
Who When What
pathipat 17-Jun-2003 Enh 2831587 Credit Card Fund Transfer build
Added new parameter p_credit_card_tangible_cd
smadathi 26-Nov-2002 Enh#2584986. Passed system date to parameter gl_date in the call to igs_ad_ss_appl_fee_pkg.upd_fee_details
vvutukur 26-Nov-2002 Enh#2584986.Added 9 new parameters to this procedure(attributing to credit card details, Accounting information and the GL_DATE) as part of GL Interface Build.Passed sysdate
if gl_date parameter is null in the call to igs_ad_ss_appl_fee_pkg.upd_fee_details.
schodava 24-Jul-2002 Bug # 2467918
Application fee cannot be submitted
knag 28-Oct-2002 Called func igs_ad_gen_003.get_core_or_optional_unit for bug 2647482
******************************************************************/
CURSOR c_course IS
Select NOMINATED_COURSE_CD, SEQUENCE_NUMBER
From IGS_AD_PS_APPL_INST_ALL
Where PERSON_ID = p_person_id
And ADMISSION_APPL_NUMBER = p_admission_appl_number;
SELECT acad_cal_type,
acad_ci_sequence_number,
admission_cat,
s_admission_process_type
FROM IGS_AD_APPL
WHERE person_id = cp_person_id
AND admission_appl_number = cp_adm_appl_number;
SELECT enrolment_cat
FROM igs_en_cat_mapping
WHERE admission_cat = cp_adm_cat
AND dflt_cat_ind = 'Y';
Select ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE
Into l_adm_cat, l_adm_proc_type
From IGS_AD_APPL_ALL
Where person_id = p_person_id
And admission_appl_number = p_admission_appl_number;
IGS_AD_SS_APPL_FEE_PKG.check_offer_resp_update(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_msg_count,
l_msg_data);
Update_Appl_Ofres_Inst(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_message_data);
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.update_ad_offer_resp_and_fee -'||SQLERRM);
END update_ad_offer_resp_and_fee;
Select NOMINATED_COURSE_CD, SEQUENCE_NUMBER
From IGS_AD_PS_APPL_INST_ALL
Where PERSON_ID = p_person_id
And ADMISSION_APPL_NUMBER = p_admission_appl_number;
SELECT acad_cal_type,
acad_ci_sequence_number,
admission_cat,
s_admission_process_type
FROM IGS_AD_APPL
WHERE person_id = cp_person_id
AND admission_appl_number = cp_adm_appl_number;
SELECT enrolment_cat
FROM igs_en_cat_mapping
WHERE admission_cat = cp_adm_cat
AND dflt_cat_ind = 'Y';
FND_LOG.STRING(fnd_log.level_Statement, 'igs.patch.115.sql.igs_ad_ss_gen_001.Process_OneStop2 :', 'Before IGS_AD_SS_APPL_FEE_PKG.check_offer_resp_update');
IGS_AD_SS_APPL_FEE_PKG.check_offer_resp_update(p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_msg_count,
l_msg_data);
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop2.after_check_offer_resp_update_ret_Stat';
l_debug_str := 'After IGS_AD_SS_APPL_FEE_PKG.check_offer_resp_update: l_return_status' || l_return_status;
l_label := 'igs.plsql.igs_ad_ss_gen_001.Process_OneStop2.after_check_offer_resp_update_msg_data';
l_debug_str := 'After IGS_AD_SS_APPL_FEE_PKG.check_offer_resp_update: l_msg_data' || l_msg_data;
IF l_return_status = 'E' THEN ----check_offer_resp_update return status
IF p_role <> 'ADMIN' THEN
l_msg_data := 'IGS_AD_APPL_PRC_FAILED';
Update_Appl_Ofres_Inst( p_person_id,
p_admission_appl_number,
c_course_rec.nominated_course_cd,
c_course_rec.sequence_number,
l_return_status,
l_message_data );*/
END IF; --- check_offer_resp_update return status
PROCEDURE Update_Appl_Eqdo_Inst(p_person_id IN NUMBER,
p_admission_appl_number IN NUMBER,
p_nominated_course_cd IN VARCHAR2,
p_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
CURSOR c_upd_acai_eqdo(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 ROWID, acai.*
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = cp_person_id
AND acai.admission_appl_number = cp_admission_appl_number
AND acai.nominated_course_cd = cp_nominated_course_cd
AND acai.sequence_number = cp_sequence_number
FOR UPDATE OF acai.adm_doc_status, acai.adm_entry_qual_status NOWAIT;
SELECT s_admission_process_type
FROM igs_ad_appl_all
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_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 => l_application_status,
X_ADM_ENTRY_QUAL_STATUS => l_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 => 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,
X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID,
X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS,
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL,
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID,
X_MODE => lv_mode, -- enable security for Admin
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_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE,
X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE,
X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER,
X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER,
X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER,
X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER,
X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER,
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_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
);
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.Update_Appl_Eqdo_Inst -'||SQLERRM);
END Update_Appl_Eqdo_Inst;
PROCEDURE Update_Appl_Ofr_Inst(p_person_id IN NUMBER,
p_admission_appl_number IN NUMBER,
p_nominated_course_cd IN VARCHAR2,
p_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
CURSOR c_upd_acai_outcm(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 ROWID, acai.*
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = cp_person_id
AND acai.admission_appl_number = cp_admission_appl_number
AND acai.nominated_course_cd = cp_nominated_course_cd
AND acai.sequence_number = cp_sequence_number
FOR UPDATE OF acai.adm_outcome_status NOWAIT;
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 => l_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 => SYSDATE,
X_Offer_Response_Dt => SYSDATE,
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 => l_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,
X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID,
X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS,
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL,
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID,
X_MODE => lv_mode, -- enable security for Admin
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_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE,
X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE,
X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER,
X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER,
X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER,
X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER,
X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER,
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_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
);
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.Update_Appl_Ofr_Inst -'||SQLERRM);
END Update_Appl_Ofr_Inst;
PROCEDURE Update_Appl_Ofres_Inst(p_person_id IN NUMBER,
p_admission_appl_number IN NUMBER,
p_nominated_course_cd IN VARCHAR2,
p_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
CURSOR c_upd_acai_resp(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 ROWID, acai.*
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = cp_person_id
AND acai.admission_appl_number = cp_admission_appl_number
AND acai.nominated_course_cd = cp_nominated_course_cd
AND acai.sequence_number = cp_sequence_number
FOR UPDATE OF acai.ADM_OFFER_RESP_STATUS NOWAIT;
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 => l_offer_resp_status,
X_Actual_Response_Dt => SYSDATE,
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,
X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID,
X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS,
X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL,
X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID,
X_MODE => lv_mode, -- enable security for Admin
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_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE,
X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE,
X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER,
X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER,
X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER,
X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER,
X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER,
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_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
);
END Update_Appl_ofres_Inst;
PROCEDURE insert_appl_section_stat(x_message_name OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_person_id IN NUMBER,
p_adm_appl_number IN NUMBER,
p_login_id IN NUMBER) AS
/*****************************************************************************************
Created By: [email protected]
Date Created : 16-APR-2002
Purpose: 1. Inserts record from Self Service Admissions form (New Application Screen).
2. These Records are to be used in review and submit applications page as checklist items.
Known limitations,enhancements,remarks:
Change History
Who When What
stammine 10-June-05 Modified the Cursor of Checklist values.
*****************************************************************************************/
CURSOR c_chklist(cp_ApplNum igs_ss_Adm_appl_stg.SS_ADM_APPL_ID%TYPE, cp_person_id igs_ss_Adm_appl_stg.PERSON_ID%TYPE) IS
SELECT pgs.page_name section
FROM igs_ad_ss_appl_pgs pgs
WHERE pgs.admission_application_type =
(SELECT admission_application_type
FROM igs_ss_adm_appl_stg
WHERE ss_adm_appl_id = cp_ApplNum)
AND pgs.include_ind = 'Y'
AND NOT EXISTS ( SELECT 'x'
FROM igs_ss_ad_sec_stat
WHERE person_id = cp_person_id
AND ss_adm_appl_id = cp_ApplNum
AND section = pgs.page_name);
INSERT INTO IGS_SS_AD_SEC_STAT
(SS_ADM_APPL_ID,
PERSON_ID,
SECTION,
COMPLETION_STATUS,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(P_ADM_APPL_NUMBER,
P_PERSON_ID,
c_chklist_values_rec.section,
'NOTSTARTED',
P_LOGIN_ID,
SYSDATE,
SYSDATE,
P_LOGIN_ID,
P_LOGIN_ID);
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_appl_section_stat -'||SQLERRM);
END insert_appl_section_stat;
SELECT s_admission_step_type, step_type_restriction_num
FROM igs_ad_prcs_cat_step
WHERE admission_cat = p_admission_cat
AND s_admission_process_type = p_s_admission_process_type
AND step_group_type <> 'TRACK';
PROCEDURE insert_application_fee(p_person_id IN igs_ad_app_req.person_id%TYPE,
p_adm_appl_id IN igs_ss_app_req_stg.ss_adm_appl_id%TYPE,
p_adm_appl_number IN igs_ad_app_req.admission_appl_number%TYPE) AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| pathipat 17-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
|| Added 3 new params in call to igs_ad_app_req_pkg.insert_row
||vvutukur 26-Nov-2002 Enh#2584986.Added 11 new parameters to the call to igs_ad_app_req_pkg.insert_row
|| (attributing to credit card details,Accounting information and the GL_DATE)
|| as part of GL Interface Build and passed NULL to all of them.
----------------------------------------------------------------------------*/
l_rowid VARCHAR2(25);
SELECT ss_app_req_id,
applicant_fee_type,
applicant_fee_status,
fee_date,
fee_payment_method,
fee_amount,
reference_num
FROM IGS_SS_APP_REQ_STG
WHERE person_id = p_person_id
AND ss_adm_appl_id = p_adm_appl_id;
logHeader('insert_application_fee', 'S');
logDetail('Before call to IGS_AD_APP_REQ_PKG.insert_row', 'S');
igs_ad_app_req_pkg.insert_row(x_rowid => l_rowid,
x_app_req_id => l_app_req_id,
x_person_id => p_person_id,
x_admission_appl_number => p_adm_appl_number,
x_applicant_fee_type => c_appl_fee_rec.applicant_fee_type,
x_applicant_fee_status => c_appl_fee_rec.applicant_fee_status,
x_fee_date => c_appl_fee_rec.FEE_DATE,
X_FEE_PAYMENT_METHOD => c_appl_fee_rec.FEE_PAYMENT_METHOD,
X_FEE_AMOUNT => c_appl_fee_rec.FEE_AMOUNT,
X_REFERENCE_NUM => c_appl_fee_rec.REFERENCE_NUM,
x_mode => lv_mode, -- enable security for Admin
x_credit_card_code => NULL,
x_credit_card_holder_name => NULL,
x_credit_card_number => NULL,
x_credit_card_expiration_date => NULL,
x_rev_gl_ccid => NULL,
x_cash_gl_ccid => NULL,
x_rev_account_cd => NULL,
x_cash_account_cd => NULL,
x_gl_date => NULL,
x_gl_posted_date => NULL,
x_posting_control_id => NULL,
x_credit_card_tangible_cd => NULL,
x_credit_card_payee_cd => NULL,
x_credit_card_status_code => NULL);
logDetail('Inside insert_application_fee' ||
'Exception from IGS_AD_APP_REQ_PKG.insert_row ' || SQLERRM ||
'person_id : ' || IGS_GE_NUMBER.TO_CANN(p_person_id),
'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.insert_application_fee -'||SQLERRM);
END insert_application_fee;
SELECT u.title
FROM igs_ad_unit_sets us, igs_en_unit_set u
WHERE us.person_id = p_person_id
AND us.admission_appl_number = p_admission_appl_number
AND us.nominated_course_cd = p_nominated_course_cd
AND us.sequence_number = p_sequence_number
AND us.rank = p_rank
AND us.unit_set_cd = u.unit_set_cd
AND us.version_number = u.version_number;
SELECT api_person_id, person_id_type
FROM igs_pe_alt_pers_id api
WHERE api.pe_person_id = cp_pe_person_id
AND Sysdate between api.start_dt and nvl(api.end_dt, sysdate);
SELECT application_id
FROM igs_ad_appl a
WHERE a.person_id = cp_person_id;
/* Added procedure which will update the checklist w.r.t Application type configuration. */
PROCEDURE update_appl_section_stat(p_person_id IN NUMBER,
p_adm_appl_number IN NUMBER,
p_page_Name IN VARCHAR2,
p_Appl_Type IN VARCHAR2,
x_message_name OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_mand_incomplete OUT NOCOPY VARCHAR2) AS
/*****************************************************************************************
Created By: stammine
Date Created : 10-Jun-2005
Purpose: Procedure to update the Checklist Status as per application Type configuration for an Application
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
-- Cursor for Each Component to check for existance of the respective record
--Personal Details { Basic Information - BASIC_INFO, Address- ADDRESS, Phone- PHONE, Email- EMAIL, Identification- ALT_ID
--Address Details { Other Names- OTHER_NAMES, Biographic- BIOGRAPHIC, Special Needs- SPECIAL_NEEDS,Privacy- PRIVACY
--Further Details { Relationships- RELATIONSHIPS, Felony- FELONY, Housing Status- HOUSING_STATUS, Health- HEALTH_INFO}
-- Residency and Citizenship {Country of Residence- COUNTRY_RESIDENCE, Citizenship- CITIZENSHIP,Language- LANGUAGE, Domicile- DOMICILE}
-- International Details {Country of Citizenship- COUNTRY_CITIZENSHIP, Visa- VISA, Passport- PASSPORT, Financial Verification- FIN_VER}
-- Application Details {Program Preferences- PROGRAM_PREF, Program Preference Details- PROG_PREF_DTLS,
-- Desired Majors/Minors- DESIRED_UNITS, Educational Goals- EDUC_GOALS, Financial Aid- FIN_AID}
-- Education {High School- SECONDARY, College/University- POST_SECONDARY, Admission Test Results- ADM_TEST_RESULTS,
-- Qualification Details- QUALIFICATION, Academic Honors- ACAD_HONORS}
-- Supporting Information {Personal Statements- PERSONAL_STMTS, Supporting Information- SUPPORTING_INFO,
-- Other Institutions Applied- OTHER_INST, Academic Interest- ACAD_INTEREST, Special Talents- SPECIAL_TALENTS,
-- Extracurricular Activities- EXTRACUR_ACTIVITIES, Employment History- EMPLOYMENT_HIST}
-- Basic Information - BASIC_INFO
-- No validation
-- Address - ADDRESS
CURSOR c_addr(cp_person_id igs_ad_appl.person_id%TYPE, cp_ApplType igs_ad_appl.APPLICATION_TYPE%TYPE) IS
SELECT 'x'
FROM IGS_PE_ADDR_V addr, IGS_PE_PARTYSITEUSE_V use
WHERE addr.PERSON_ID = cp_person_id
AND addr.STATUS = 'A'
AND addr.party_site_id = use.party_site_id
AND use.site_use_type IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'SITE_USE_CODE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y');
SELECT 'x'
FROM hz_contact_points
WHERE CONTACT_POINT_TYPE = 'PHONE'
AND OWNER_TABLE_NAME = 'HZ_PARTIES'
AND STATUS = 'A'
AND PHONE_LINE_TYPE IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'PHONE_LINE_TYPE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y')
AND OWNER_TABLE_ID = cp_person_id;
SELECT 'x'
FROM hz_contact_points
WHERE CONTACT_POINT_TYPE = 'EMAIL'
AND OWNER_TABLE_NAME = 'HZ_PARTIES'
AND STATUS = 'A'
AND OWNER_TABLE_ID = cp_person_id;
SELECT 'x'
FROM IGS_PE_ALT_PERS_ID
WHERE PE_PERSON_ID = cp_person_id
AND PERSON_ID_TYPE IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'PERSON_ID_TYPE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y')
AND (END_DT IS NULL OR START_DT <> END_DT);
SELECT 'x'
FROM IGS_PE_PERSON_ALIAS_V pon
WHERE pon.PERSON_ID = cp_person_id
AND ALIAS_TYPE IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'PE_ALIAS_TYPE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y');
SELECT 'x'
FROM IGS_PE_RACE race
WHERE race.PERSON_ID = cp_person_id;
SELECT 'x' FROM IGS_PE_PERS_DISABLTY WHERE person_id = cp_person_id
AND SYSDATE <= NVL(END_DATE,SYSDATE);
SELECT 'x'
FROM IGS_PE_PRIV_LEVEL priv
WHERE PERSON_ID = cp_person_id
AND to_char(DATA_GROUP_ID) IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'PRIVACY_PREF_DATA_GROUP'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y');
SELECT 'x'
FROM IGS_PE_HZ_REL_V rel
WHERE rel.SUBJECT_ID = cp_person_id
AND rel.SUBJECT_TYPE = 'PERSON'
AND rel.OBJECT_TYPE = 'PERSON'
AND rel.STATUS = 'A'
AND SYSDATE BETWEEN rel.START_DATE AND NVL(rel.END_DATE, SYSDATE)
AND RELATIONSHIP_CODE IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'PARTY_RELATIONS_TYPE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y');
SELECT FELONY_CONVICTED_FLAG
FROM IGS_PE_HZ_PARTIES
WHERE PARTY_ID = cp_person_id;
SELECT 'x'
FROM IGS_PE_TEACH_PERIODS_ALL
WHERE person_id = cp_person_id;
SELECT 'x' FROM IGS_PE_HLTH_INS_ALL
WHERE person_id = cp_person_id
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
SELECT 'x' FROM IGS_PE_IMMU_DTLS WHERE person_id = cp_person_id
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
SELECT 'x'
FROM IGS_PE_EIT T
WHERE T.INFORMATION_TYPE = 'PE_STAT_RES_COUNTRY'
AND Person_id = cp_person_id;
SELECT 'x'
FROM HZ_PERSON_LANGUAGE lang
WHERE PARTY_ID = cp_person_id
AND STATUS = 'A';
SELECT 'x'
FROM IGS_PE_EIT
WHERE INFORMATION_TYPE = 'PE_STAT_RES_STATE'
AND Person_id = cp_person_id;
SELECT 'x' FROM IGS_PE_VOTE_INFO_ALL WHERE Person_id = cp_person_id;
SELECT 'x' FROM IGS_PE_INCOME_TAX_ALL WHERE Person_id = cp_person_id;
SELECT 'x' FROM IGS_PE_MIL_SERVICES_ALL WHERE Person_id = cp_person_id;
SELECT 'x'
FROM HZ_CITIZENSHIP cz
WHERE cz.PARTY_ID = cp_person_id
AND cz.STATUS = 'A';
SELECT 'x'
FROM IGS_PE_VISIT_HISTRY_V visa
WHERE visa.PERSON_ID = cp_person_id;
SELECT 'x'
FROM IGS_PE_PASSPORT pport
WHERE pport.PERSON_ID = cp_person_id;
SELECT SS_ADMAPPL_PGM_ID, ATTENDANCE_TYPE,
ATTENDANCE_MODE,
NOMINATED_COURSE_CD
FROM IGS_SS_APP_PGM_STG
WHERE ss_adm_appl_id = cp_adm_appl_number;
SELECT SS_ADMAPPL_PGM_ID,
ENTRY_STATUS,
ENTRY_LEVEL,
FINAL_UNIT_SET_CD,
NOMINATED_COURSE_CD,
SCH_APL_TO_ID
FROM IGS_SS_APP_PGM_STG
WHERE ss_adm_appl_id = cp_adm_appl_number;
SELECT 'x'
FROM IGS_SS_AD_UNITSE_STG
WHERE ss_admappl_pgm_id = cp_ss_admappl_pgm;
SELECT 'x'
FROM IGS_SS_AD_EDUGOA_STG
WHERE ss_admappl_pgm_id = cp_ss_admappl_pgm;
SELECT APPLY_FOR_FINAID, FINAID_APPLY_DATE
FROM IGS_SS_APP_PGM_STG
WHERE ss_adm_appl_id = cp_adm_appl_number;
SELECT 'x'
FROM HZ_EDUCATION HE
WHERE HE.PARTY_ID = cp_person_id
AND HE.STATUS = 'A'
AND EXISTS (SELECT 'X'
FROM IGS_OR_ORG_INST_TYPE_ALL
WHERE INSTITUTION_TYPE = DECODE (HE.SCHOOL_PARTY_ID, NULL, NULL,
(SELECT OI_INSTITUTION_TYPE
FROM IGS_PE_HZ_PARTIES
WHERE HE.SCHOOL_PARTY_ID = PARTY_ID
AND INST_ORG_IND = 'I'
)
)
AND SYSTEM_INST_TYPE = 'SECONDARY');
SELECT 'x'
FROM HZ_EDUCATION HE
WHERE HE.PARTY_ID = cp_person_id
AND HE.STATUS = 'A'
AND EXISTS (SELECT 'X'
FROM IGS_OR_ORG_INST_TYPE_ALL
WHERE INSTITUTION_TYPE = DECODE (HE.SCHOOL_PARTY_ID, NULL, NULL, (SELECT OI_INSTITUTION_TYPE
FROM IGS_PE_HZ_PARTIES
WHERE HE.SCHOOL_PARTY_ID = PARTY_ID
AND INST_ORG_IND = 'I') )
AND SYSTEM_INST_TYPE IN ('POST-SECONDARY', 'OTHER')
);
SELECT 'x'
FROM igs_en_stdnt_ps_att_all psattmpt
WHERE psattmpt.person_id = cp_person_id
AND psattmpt.course_attempt_status <> 'UNCONFIRM'
AND((NVL(fnd_profile.VALUE('CAREER_MODEL_ENABLED'), 'N') = 'N')
OR(fnd_profile.VALUE('CAREER_MODEL_ENABLED') = 'Y' AND psattmpt.primary_program_type = 'PRIMARY'));
SELECT 'x'
FROM IGS_AD_TEST_RESULTS
WHERE PERSON_ID = cp_person_id
AND ADMISSION_TEST_TYPE IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'ADMISSION_TEST_TYPE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y');
SELECT 'x' FROM IGS_UC_QUAL_DETS WHERE person_id = cp_person_id;
SELECT 'x' FROM IGS_PE_ACAD_HONORS WHERE person_id = cp_person_id;
SELECT stg.persl_stat_type statement,
DECODE(NVL(mandatory,'N'),'N','No','Yes') required,
group_number,
stg.SS_PERSTAT_ID SS_PERSTAT_ID,
stg.attach_exists
FROM IGS_SS_APPL_PERSTAT stg,
IGS_AD_APTYP_PESTAT aptypperstat
WHERE stg.persl_Stat_type = aptypperstat.persl_Stat_type
AND stg.admission_application_type = aptypperstat.admission_application_type
AND stg.ss_Adm_appl_id = cp_adm_appl_number
AND stg.PERSON_ID = cp_person_id
AND group_number = cp_group_number ;
SELECT
group_name statement,
DECODE(NVL(group_required_flag,'N'),'N','No','Yes') required,
group_number ,
0 SS_PERSTAT_ID,
null attach_exists,
group_min
FROM IGS_AD_PESTAT_GROUP
WHERE group_number IN
(SELECT distinct group_number FROM IGS_SS_APPL_PERSTAT stg, IGS_AD_APTYP_PESTAT setup
WHERE setup.persl_Stat_type = stg.persl_Stat_type
AND setup.admission_application_type = stg.admission_application_type
AND stg.ss_Adm_appl_id = cp_adm_appl_number AND stg.PERSON_ID = cp_person_id )
AND admission_application_type = cp_ApplType
AND NVL(GROUP_REQUIRED_FLAG,'N') = 'Y'
UNION ALL
SELECT
stg.persl_stat_type statement,
DECODE(NVL(mandatory,'N'),'N','No','Yes') required,
-1 group_number,
stg.SS_PERSTAT_ID SS_PERSTAT_ID,
stg.attach_exists,
0 group_min
FROM IGS_SS_APPL_PERSTAT stg,
IGS_AD_APTYP_PESTAT aptypperstat
WHERE stg.persl_Stat_type = aptypperstat.persl_Stat_type
AND stg.admission_application_type = aptypperstat.admission_application_type
AND group_number IS NULL
AND stg.ss_Adm_appl_id = cp_adm_appl_number
AND stg.PERSON_ID = cp_person_id ;
SELECT 'x'
FROM IGS_PE_CREDENTIALS
WHERE person_id = cp_person_id
AND to_char(CREDENTIAL_TYPE_ID) IN
(SELECT SS_Lookup_code
FROM igs_ad_ss_lookups
WHERE ss_lookup_type = 'CREDENTIAL_TYPE'
AND admission_application_type = cp_ApplType
AND Closed_flag <> 'Y');
SELECT 'x'
FROM IGS_SS_AD_OTHINS_STG
WHERE SS_ADM_APPL_ID = cp_Appl_Num;
SELECT 'x'
FROM IGS_SS_AD_ACADIN_STG
WHERE SS_ADM_APPL_ID = cp_Appl_Num;
SELECT 'x'
FROM IGS_SS_AD_SPLTAL_STG
WHERE SS_ADM_APPL_ID = cp_Appl_Num;
SELECT 'x'
FROM HZ_PERSON_INTEREST PI, IGS_AD_HZ_EXTRACURR_ACT HEA
WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID
AND PI.PARTY_ID = cp_person_id;
SELECT 'x' FROM HZ_EMPLOYMENT_HISTORY WHERE party_id = cp_person_id;
SELECT Admission_application_type ApplType,
page_name PageName,
component_code Component,
comp_disp_name ComponentDesc,
required_flag
FROM IGS_AD_SS_APPL_COMPS
WHERE Admission_application_type = cp_Appl_Type
AND page_name = cp_page_Name
AND include_flag = 'Y';
SELECT 'x'
FROM IGS_AD_SS_APPL_COMPS
WHERE Admission_application_type = cp_Appl_Type
AND page_name = cp_page_Name
AND include_flag = 'Y'
AND required_flag = 'Y';
l_prog_label := 'igs.plsql.igs_ad_ss_gen_001.update_appl_section_stat';
l_label := 'igs.plsql.igs_ad_ss_gen_001.update_appl_section_stat.start';
UPDATE IGS_SS_AD_SEC_STAT
SET COMPLETION_STATUS = l_comp_status
WHERE SS_ADM_APPL_ID = p_adm_appl_number
AND PERSON_ID = p_person_id
AND SECTION = p_page_Name;
logDetail( 'Exception from update of Application Status, ' || SQLERRM,'S');
Fnd_Message.Set_Token('NAME','IGS_AD_SS_GEN_001.update_appl_section_stat -'||SQLERRM);
END update_appl_section_stat;
Purpose: 1. Inserts records from Self Service Admissions Application Type Configuration
if any discrepancy exists with existing Status data and setup.
2. Update the COMPLETION_STATUS with COMPLETE if COMPLETION_STATUS = ERROR.
3. Returns the No. of Pages/Sections included for the Application Type.
This is used to set the sMaxSection Session Variable - Used throughout the Application Create/Update flow.
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
CURSOR c_NoPgs(cp_ApplType igs_ss_Adm_appl_stg.ADMISSION_APPLICATION_TYPE%TYPE) IS
SELECT count(*)
FROM igs_ad_ss_appl_pgs
WHERE admission_application_type = cp_ApplType
AND include_ind = 'Y';
insert_appl_section_stat(x_message_name,
x_return_status,
p_person_id,
p_adm_appl_number,
p_login_id);
UPDATE IGS_SS_AD_SEC_STAT
SET COMPLETION_STATUS = 'COMPLETE'
WHERE SS_ADM_APPL_ID = p_adm_appl_number
AND PERSON_ID = p_person_id
AND COMPLETION_STATUS = 'ERROR';
SELECT mandatory_step_ind --, s_admission_step_type --, step_group_type
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_Cat = cp_admission_cat
AND s_admission_process_type = cp_s_admission_process_type
AND s_admission_step_type = (SELECT apc_step FROM igs_ad_ss_appl_conf
WHERE page_code = cp_page_code
AND component_code = cp_comp_code);
SELECT
DISTINCT conf.page_code,
lkv.meaning page_desc
FROM igs_ad_ss_appl_conf conf,
igs_lookup_values lkv
WHERE
lkv.lookup_type = 'SS_APPL_SELFSERVICE_PAGES'
AND lkv.lookup_code = conf.page_code
AND lkv.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE) ;
SELECT conf.page_code, conf.component_code,
lkv.meaning component_desc,
conf.include_flag, conf.apc_step
FROM IGS_AD_SS_APPL_CONF conf,
igs_lookup_values lkv
WHERE
lkv.lookup_type = 'IGS_AD_SS_PG_COMPS'
AND lkv.lookup_code = conf.component_code
AND conf.page_code = cp_page_code
AND lkv.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE);
SELECT page_name FROM igs_ad_ss_appl_pgs WHERE
admission_application_type = cp_appl_type;
SELECT 'x'
FROM IGS_AD_SS_APPL_COMPS
WHERE Admission_application_type = cp_p_Appl_type
AND Page_name = cp_page_code
AND component_code = cp_comp_code
AND Include_flag = 'Y';
PROCEDURE update_assign_pgs_comps( x_message_name OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_appl_type IN VARCHAR2 DEFAULT NULL,
p_admission_cat IN VARCHAR2,
p_s_admission_process_type IN VARCHAR2
) AS
/*****************************************************************************************
Created By: stammine
Date Created : 10-Jun-2005
Purpose: procedure which will update the records in following areas when Application Category
is Changed for an Application Type:
Application Type pages
Application Type page Components
Terms and Conditions.
Invoked when existing application type is updated.
Known limitations,enhancements,remarks:
Change History
Who When What
*****************************************************************************************/
CURSOR cpcs(cp_admission_cat IGS_AD_PRCS_CAT_STEP_ALL.admission_Cat%TYPE,
cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP_ALL.s_admission_process_type%TYPE,
cp_page_code IGS_AD_SS_APPL_CONF.page_code%TYPE,
cp_comp_code IGS_AD_SS_APPL_CONF.component_code%TYPE) IS
SELECT mandatory_step_ind, s_admission_step_type, step_group_type
FROM IGS_AD_PRCS_CAT_STEP_ALL
WHERE admission_Cat = cp_admission_cat
AND s_admission_process_type = cp_s_admission_process_type
AND s_admission_step_type =
(SELECT apc_step
FROM IGS_AD_SS_APPL_CONF
WHERE page_code = cp_page_code
AND component_code = cp_comp_code
AND apc_step IS NOT NULL);
SELECT Admission_application_type, Page_name, Include_Ind
FROM IGS_AD_SS_APPL_PGS
WHERE Admission_application_type = cp_Admission_application_type;
SELECT Admission_application_type, Page_name,component_code, Include_flag, Required_flag
FROM IGS_AD_SS_APPL_COMPS
WHERE Admission_application_type = cp_Admission_application_type
AND Page_name = cp_page_code;
SELECT admission_application_type
FROM IGS_AD_SS_APPL_TYP
WHERE
admission_application_type = p_appl_type
AND admission_cat = p_admission_cat
AND s_admission_process_type = p_s_admission_process_type;
SELECT admission_application_type
FROM IGS_AD_SS_APPL_TYP
WHERE
admission_cat = p_admission_cat
AND s_admission_process_type = p_s_admission_process_type;
UPDATE igs_ad_ss_appl_comps
SET include_flag = 'Y' , required_flag = 'Y'
WHERE Admission_application_type =
appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE igs_ad_ss_appl_comps
SET required_flag = 'Y'
WHERE Admission_application_type =
appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE igs_ad_ss_appl_comps
SET include_flag = 'Y' , required_flag = 'Y'
WHERE Admission_application_type =
appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE igs_ad_ss_appl_comps
SET required_flag = 'Y'
WHERE Admission_application_type =
appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE igs_ad_ss_appl_comps
SET include_flag = 'Y' , required_flag = 'Y'
WHERE Admission_application_type =
appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE igs_ad_ss_appl_comps
SET include_flag = 'Y' , required_flag = 'N'
WHERE Admission_application_type = appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE igs_ad_ss_appl_comps
SET include_flag = 'Y' , required_flag = 'Y'
WHERE Admission_application_type = appl_pg_comps_rec.Admission_application_type
AND page_name = appl_pg_comps_rec.page_name
AND component_code = appl_pg_comps_rec.component_code;
UPDATE IGS_AD_SS_APPL_PGS pgs SET Include_ind = 'Y' WHERE
pgs.ADMISSION_APPLICATION_TYPE = appl_type_table(i) AND
EXISTS (SELECT 'x' FROM IGS_AD_SS_APPL_COMPS comps WHERE
comps.ADMISSION_APPLICATION_TYPE = appl_type_table(i)
AND comps.Page_name = pgs.page_name
AND comps.Include_flag = 'Y');
END update_assign_pgs_comps;
SELECT person_id, acad_cal_type, acad_cal_seq_number, adm_cal_type,
adm_cal_seq_number , admission_cat, s_adm_process_type
FROM igs_ss_adm_appl_stg
WHERE ss_adm_appl_id = cp_ss_adm_appl_id;
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 s_admission_step_type = cp_step_type
AND step_group_type <> 'TRACK';
SELECT 'x'
FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
WHERE aav.person_id = acaiv.person_id AND
aav.admission_appl_number = acaiv.admission_appl_number AND
acaiv.person_id = cp_person_id AND
acaiv.course_cd = cp_course_cd AND
NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
NVL(acaiv.adm_cal_type, aav.adm_cal_type) = cp_adm_cal_type AND
NVL(acaiv.adm_ci_sequence_number, aav.adm_ci_sequence_number)
= cp_adm_ci_sequence_number AND
-- Check for CANCELLED added for bug 2678766
NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status),'x') <> 'CANCELLED';
SELECT person_id, acad_cal_type, acad_cal_seq_number, adm_cal_type,
adm_cal_seq_number , admission_cat, s_adm_process_type
FROM igs_ss_adm_appl_stg
WHERE ss_adm_appl_id = cp_ss_adm_appl_id;
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 s_admission_step_type = cp_step_type
AND step_group_type <> 'TRACK';
PROCEDURE DELETE_PERSTMT_ATTACHMENT(p_document_id IN NUMBER,
p_ss_perstat_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) AS
-- This method will be used to delete the Personal Statement Attachment record
-- from the tables FND_ATTCHED_DOCUMENTS, FND_DOCUMENTS and FND_LOBS.
BEGIN
x_return_status := 'S';
FND_DOCUMENTS_PKG.DELETE_ROW (
x_document_id => p_document_id,
x_datatype_id => 6,
delete_ref_Flag => 'Y'
);
Update IGS_SS_APPL_PERSTAT SET attach_exists ='N' WHERE SS_PERSTAT_ID = p_ss_perstat_id;
END DELETE_PERSTMT_ATTACHMENT;
SELECT category_id
FROM fnd_document_categories_tl
WHERE name = cp_name;
SELECT FND_LOBS_S.NEXTVAL INTO l_fileid FROM dual;
INSERT INTO FND_LOBS(
FILE_ID,
FILE_NAME,
FILE_CONTENT_TYPE,
UPLOAD_DATE,
EXPIRATION_DATE,
PROGRAM_NAME,
PROGRAM_TAG,
LANGUAGE,
ORACLE_CHARSET,
FILE_FORMAT) VALUES
(
l_fileid,
p_file_name,
p_file_content_type,
SYSDATE,
NULL,
NULL,
NULL,
USERENV('LANG'),
NULL,
p_file_format
);
Update IGS_SS_APPL_PERSTAT SET attach_exists ='Y' WHERE SS_PERSTAT_ID = P_SS_PERSTAT_ID;
PROCEDURE DELETE_PERSTMT_ATTACHMENT_UP(p_document_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) AS
-- This method will be used to delete the Personal Statement Attachment record
-- from the tables FND_ATTCHED_DOCUMENTS, FND_DOCUMENTS and FND_LOBS.
BEGIN
x_return_status := 'S';
FND_DOCUMENTS_PKG.DELETE_ROW (
x_document_id => p_document_id,
x_datatype_id => 6,
delete_ref_Flag => 'Y'
);
END DELETE_PERSTMT_ATTACHMENT_UP;
SELECT category_id
FROM fnd_document_categories_tl
WHERE name = cp_name;
SELECT FND_LOBS_S.NEXTVAL INTO l_fileid FROM dual;
INSERT INTO FND_LOBS(
FILE_ID,
FILE_NAME,
FILE_CONTENT_TYPE,
UPLOAD_DATE,
EXPIRATION_DATE,
PROGRAM_NAME,
PROGRAM_TAG,
LANGUAGE,
ORACLE_CHARSET,
FILE_FORMAT) VALUES
(
l_fileid,
p_file_name,
p_file_content_type,
SYSDATE,
NULL,
NULL,
NULL,
USERENV('LANG'),
NULL,
p_file_format
);
SELECT al.meaning lookup_desc
FROM AR_LOOKUPS al
WHERE al.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
AND al.ENABLED_FLAG = 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.SS_LOOKUP_TYPE = 'SITE_USE_CODE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = al.lookup_code AND
sslkps.closed_flag <> 'Y');
SELECT lv.meaning lookup_desc
FROM FND_LOOKUP_VALUES lv
WHERE lv.LOOKUP_TYPE = 'PHONE_LINE_TYPE'
AND lv.LANGUAGE(+) = USERENV('LANG')
AND lv.VIEW_APPLICATION_ID = 222
AND lv.SECURITY_GROUP_ID = 0
AND lv.ENABLED_FLAG = 'Y'
AND EXISTS
(SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.SS_LOOKUP_TYPE = 'PHONE_LINE_TYPE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = lv.lookup_code AND
sslkps.closed_flag <> 'Y');
SELECT pit.description lookup_desc
FROM IGS_PE_PERSON_ID_TYP pit
WHERE pit.closed_ind <> 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.SS_LOOKUP_TYPE = 'PERSON_ID_TYPE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = pit.person_id_type AND
sslkps.closed_flag <> 'Y');
SELECT lv.meaning lookup_desc
FROM FND_LOOKUP_VALUES lv,
HZ_RELATIONSHIP_TYPES hz
WHERE HZ.FORWARD_REL_CODE=lv.lookup_code
AND hz.subject_type = 'PERSON'
AND hz.object_type = 'PERSON'
AND hz.status = 'A'
AND lv.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND lv.LANGUAGE(+) = USERENV('LANG')
AND lv.VIEW_APPLICATION_ID = 222
AND lv.SECURITY_GROUP_ID = 0
AND lv.ENABLED_FLAG = 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.SS_LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = lv.lookup_code AND
sslkps.closed_flag <> 'Y');
SELECT pdg.description lookup_desc
FROM IGS_PE_DATA_GROUPS_ALL pdg
WHERE pdg.closed_ind <> 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.ss_lookup_type = 'PRIVACY_PREF_DATA_GROUP' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = to_char(pdg.data_group_id) AND
sslkps.closed_flag <> 'Y');
SELECT lv.meaning lookup_desc
FROM igs_lookup_values lv
WHERE lv.LOOKUP_TYPE = 'PE_ALIAS_TYPE'
AND lv.ENABLED_FLAG = 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.SS_LOOKUP_TYPE = 'PE_ALIAS_TYPE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = lv.lookup_code AND
sslkps.closed_flag <> 'Y');
SELECT act.description lookup_desc
FROM IGS_AD_CRED_TYPES act
WHERE act.closed_ind <> 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.ss_lookup_type = 'CREDENTIAL_TYPE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = to_char(act.credential_type_id) AND
sslkps.closed_flag <> 'Y');
SELECT att.description lookup_desc
FROM IGS_AD_TEST_TYPE att
WHERE att.closed_ind <> 'Y'
AND EXISTS (SELECT 1 FROM IGS_AD_SS_LOOKUPS sslkps
WHERE sslkps.SS_LOOKUP_TYPE = 'ADMISSION_TEST_TYPE' AND
sslkps.admission_application_type = p_application_type AND
sslkps.ss_lookup_code = att.admission_test_type AND
sslkps.closed_flag <> 'Y');
SELECT ROWID
FROM IGS_AD_PS_APPL_INST_ALL
WHERE person_id = cp_person_id
AND ADMISSION_APPL_NUMBER = cp_admission_appl_number;
l_select_access BOOLEAN;
l_select_access := FALSE;
l_select_access := TRUE;
IF l_select_access THEN
x_return_status := 'S';
fnd_message.set_token('OPERATION','SELECT');
insert_acad_interest(p_person_id => p_person_id,
p_adm_appl_id => p_ss_adm_appl_number,
p_adm_appl_number => p_adm_appl_number);
insert_applicant_intent(p_person_id => p_person_id,
p_adm_appl_id => p_ss_adm_appl_number,
p_adm_appl_number => p_adm_appl_number);
insert_spl_talent(p_person_id => p_person_id,
p_adm_appl_id => p_ss_adm_appl_number,
p_adm_appl_number => p_adm_appl_number);
insert_special_interest(p_person_id => p_person_id,
p_adm_appl_id => p_ss_adm_appl_number,
p_adm_appl_number => p_adm_appl_number);
insert_othinst(p_person_id => p_person_id,
p_adm_appl_id => p_ss_adm_appl_number,
p_admission_appl_number => p_adm_appl_number);
logheader('before inserting application_fee into IGS tables', 'S');
insert_application_fee(p_person_id => p_person_id,
p_adm_appl_id => p_ss_adm_appl_number,
p_adm_appl_number => p_adm_appl_number);
SELECT FND_PROFILE.VALUE('IGS_AD_PRESUB_APPL_UHK') value
FROM dual;