[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1.Insert Admission Application , proc: insert_adm_appl
2.Insert Admission Program Application , proc: insert_adm_appl_prog
3.Insert Admission Program Application Instance , proc: insert_adm_appl_prog_inst
Known limitations,enhancements,remarks:
Change History
Who When What
rrengara 17-DEC-2002 When we are creating appl , appl program and instance in when other displayed
the SQLERRM in the log file
cdcruz 18-feb-2002 Bug 2217104 Admit to future term Enhancement,updated tbh call for
new columns being added to IGS_AD_PS_APPL_INST
rbezawad 30-Oct-2004 Added logic to properly handle the security Policy errors IGS_SC_POLICY_EXCEPTION
and IGS_SC_POLICY_UPD_DEL_EXCEP w.r.t. bug fix 3919112.
******************************************************************/
--Fwd Declarations
PROCEDURE logHeader(p_proc_name VARCHAR2
,p_mode VARCHAR2 );
FUNCTION insert_adm_appl(
p_person_id IN NUMBER ,
p_appl_dt IN DATE ,
p_acad_cal_type IN VARCHAR2 ,
p_acad_ci_sequence_number IN NUMBER ,
p_adm_cal_type IN VARCHAR2 ,
p_adm_ci_sequence_number IN NUMBER ,
p_admission_cat IN VARCHAR2 ,
p_s_admission_process_type IN VARCHAR2 ,
p_adm_appl_status IN VARCHAR2 ,
p_adm_fee_status IN OUT NOCOPY VARCHAR2 ,
p_tac_appl_ind IN VARCHAR2,
p_adm_appl_number OUT NOCOPY NUMBER ,
p_message_name OUT NOCOPY VARCHAR2,
p_spcl_grp_1 IN NUMBER ,
p_spcl_grp_2 IN NUMBER ,
p_common_app IN VARCHAR2 ,
p_application_type IN VARCHAR2 ,
p_choice_number IN NUMBER ,
p_routeb_pref IN VARCHAR2 ,
p_alt_appl_id IN VARCHAR2,
p_appl_fee_amt IN NUMBER DEFAULT NULL,
p_log IN VARCHAR2 DEFAULT 'Y'
)
RETURN BOOLEAN IS
/*****************************************************************************************
Created By: [email protected]
Date Created : 12-27-2001
Purpose: 1.This Functions inserts an admission application
2.Inserts record into igs_ad_appl table after validations
3.Returns boolean true if the record is inserted, boolean false if the proc fails.
4.Flow/Calls used in the procedure :
|-->IGS_AD_VAL_AA.admp_val_aa_insert (IGSAD76B.pls)
|-->IGS_AD_VAL_AA.admp_val_aa_acad_cal
|-->IGS_AD_VAL_AA.admp_val_aa_adm_cal
|-->IGS_AD_VAL_AA.admp_val_aa_adm_cat
|-->IGS_AD_VAL_AA.admp_val_aa_appl_dt
|-->IGS_AD_VAL_AA.admp_val_aa_aas
|-->IGS_AD_VAL_AA.admp_val_aa_afs
|-->IGS_AD_VAL_AA.admp_val_aa_tac_appl
|-->IGS_AD_APPL_PKG.insert_row
Known limitations,enhancements,remarks:
Change History
Who When What
rrengara 8-jul-2002 Added UK Parameters choice_number and routre pref to insert_adm_appl procedure for bug 2448262 and also added to igs_ad_appl_pkg.insert_row call
rboddu 10-OCT-2002 Added the check for NULL Application Type. Bug: 2599457
rghosh 14-nov-2002 Added UK Parameters alt_appl_id to insert_adm_appl procedure for bug 2664410 and also added to igs_ad_appl_pkg.insert_row call
*****************************************************************************************/
--Local variables to check if the Security Policy exception already set or not. Ref: Bug 3919112
l_sc_encoded_text VARCHAR2(4000);
--Procedure inserts a new IGS_AD_APPL record. It uses an
--output parameter to pass back the new admission_appl_number used
DECLARE
v_dummy CHAR;
SELECT s_admission_step_type
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_cat = cp_admission_cat AND
s_admission_process_type = cp_s_admission_process_type AND
step_group_type <> 'TRACK'; --2402377
SELECT NVL(MAX(admission_appl_number),0) + 1
FROM IGS_AD_APPL /*Replaced as part of Bug 3150054*/
WHERE person_id = p_person_id;
SELECT a.admission_application_type
FROM igs_ad_ss_appl_typ a,
igs_ad_prcs_cat_v b
WHERE a.closed_ind = 'N'
AND a.admission_cat = b.admission_cat
AND a.s_admission_process_type = b.s_admission_process_type
AND b.closed_ind ='N'
AND a.admission_application_type = p_admission_application_type;
-- Validate insert of the admission application record.
--
IF IGS_AD_VAL_AA.admp_val_aa_insert (
p_person_id,
p_adm_cal_type,
p_adm_ci_sequence_number,
p_s_admission_process_type,
v_person_encmb_chk_ind,
p_appl_dt,
v_title_required_ind,
v_birth_dt_required_ind,
v_message_name,
v_return_type) = FALSE THEN
IF NVL(v_return_type, '-1') = cst_error THEN
p_message_name := v_message_name;
--Now insert the new record
--Populate the org id
l_org_id := igs_ge_gen_003.get_org_id;
IGS_AD_APPL_PKG.insert_row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_Person_Id,
X_Admission_Appl_Number => v_adm_appl_number,
X_Appl_Dt => p_Appl_Dt,
X_Acad_Cal_Type => p_Acad_Cal_Type,
X_Acad_Ci_Sequence_Number => p_Acad_Ci_Sequence_Number,
X_Adm_Cal_Type => p_Adm_Cal_Type,
X_Adm_Ci_Sequence_Number => p_Adm_Ci_Sequence_Number,
X_Admission_Cat => p_Admission_Cat,
X_S_Admission_Process_Type => p_S_Admission_Process_Type,
X_Adm_Appl_Status => p_Adm_Appl_Status,
X_Adm_Fee_Status => p_Adm_Fee_Status,
X_Tac_Appl_Ind => p_Tac_Appl_Ind,
X_Org_Id => l_org_id,
X_Spcl_Grp_1 => p_spcl_grp_1,
X_Spcl_Grp_2 => p_spcl_grp_2,
X_Common_App => p_common_app,
x_application_type => p_application_type,
x_choice_number => p_choice_number,
x_routeb_pref => p_routeb_pref,
x_alt_appl_id => p_alt_appl_id,
x_appl_fee_amt => NVL(p_appl_fee_amt,0)
);
Fnd_Message.Set_Token('NAME','IGS_AD_GEN_014.insert_adm_appl -'||SQLERRM);
END insert_adm_appl;
FUNCTION insert_adm_appl_prog(
p_person_id IN igs_pe_person.person_id%type ,
p_adm_appl_number IN NUMBER ,
p_nominated_course_cd IN VARCHAR2 ,
p_transfer_course_cd IN VARCHAR2,
p_basis_for_admission_type IN VARCHAR2 ,
p_admission_cd IN VARCHAR2 ,
p_req_for_reconsideration_ind IN VARCHAR2 ,
p_req_for_adv_standing_ind IN VARCHAR2 ,
p_message_name out NOCOPY VARCHAR2,
p_log IN VARCHAR2 DEFAULT 'Y')
RETURN BOOLEAN IS
/*************************************************************************************************************************
Created By: [email protected]
Date Created : 12-27-2001
Purpose: 1.This Functions inserts an admission application Program
2.Inserts record into igs_ad_ps_appl table after validations
3.Returns boolean true if the record is inserted, boolean false if the proc fails.
4.Flow/Calls used in the procedure :
|-->IGS_AD_GEN_002.admp_get_aa_dtl
|-->IGS_AD_VAL_ACA.admp_val_pref_limit(Validate preference limit) -IGSAD21B.pls
|-->IGS_AD_VAL_ACAI.admp_val_acai_course(Validate the nominated COURSE code) -IGSAD22B.pls
|-->IGS_AD_VAL_ACAI.admp_val_aca_sca(Validate against current student COURSE attempt) -IGSAD22B.pls
|-->Validate basis for admission type closed indicator
|-->Validate admission code closed indicator
|-->IGS_AD_VAL_ACA.admp_val_aca_req_rec -IGSAD21B.pls
|-->IGS_AD_VAL_ACA.admp_val_aca_req_adv -IGSAD21B.pls
|-->IGS_AD_VAL_ACA.admp_val_aca_trnsfr(validation for course transfer)
|-->IGS_AD_PS_APPL_PKG.insert_row(Insert the record after all validation has been performed) -IGSAI16B.pls
Known limitations,enhancements,remarks:
Change History
Who When What
***************************************************************************************************************************/
e_integrity_exception EXCEPTION;
BEGIN -- insert_adm_appl --Main Loop Begin
-- This module validate IGS_AD_PS_APLINSTUNT unit version.
logheader('insert_adm_appl',null);
SELECT s_admission_step_type,
step_type_restriction_num
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_cat = cp_admission_cat AND
s_admission_process_type = cp_s_admission_process_type AND
step_group_type <> 'TRACK'; -- 2402377
SELECT tab.* --multiorg table , so rowid need not be selected explicitly
FROM IGS_AD_PS_APPL tab
WHERE person_id = cp_person_id AND
admission_appl_number = cp_appl_no AND
nominated_course_cd = cp_nominated_course_cd;
-- Insert the record after all validation has been performed
l_org_id := igs_ge_gen_003.get_org_id;
logDetail('Before Call to Igs_Ad_Ps_Appl_Pkg.Insert_Row',null);
IGS_AD_PS_APPL_PKG.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Admission_Appl_Number => p_adm_appl_number,
X_Nominated_Course_Cd => p_nominated_course_cd,
X_Transfer_Course_Cd => p_transfer_course_cd,
X_Basis_For_Admission_Type => p_basis_for_admission_type,
X_Admission_Cd => p_admission_cd,
X_Course_Rank_Set => NULL,
X_Course_Rank_Schedule => NULL,
X_Req_For_Reconsideration_Ind => p_req_for_reconsideration_ind,
X_Req_For_Adv_Standing_Ind => p_req_for_adv_standing_ind,
X_Org_Id => l_org_id
);
IGS_AD_PS_APPL_PKG.update_Row (
X_RowId => c_prg_exists_rec.row_id,
X_Person_Id => NVL(p_person_id,c_prg_exists_rec.person_id),
X_Admission_Appl_Number => NVL(p_adm_appl_number,c_prg_exists_rec.admission_appl_number),
X_Nominated_Course_Cd => NVL(p_nominated_course_cd,c_prg_exists_rec.nominated_course_cd),
X_Transfer_Course_Cd => NVL(p_transfer_course_cd,c_prg_exists_rec.transfer_course_cd),
X_Basis_For_Admission_Type => NVL(p_basis_for_admission_type,c_prg_exists_rec.basis_for_admission_type),
X_Admission_Cd => NVL(p_admission_cd,c_prg_exists_rec.admission_cd),
X_Course_Rank_Set => c_prg_exists_rec.Course_Rank_Set,
X_Course_Rank_Schedule => c_prg_exists_rec.Course_Rank_Schedule,
X_Req_For_Reconsideration_Ind => NVL(p_req_for_reconsideration_ind,c_prg_exists_rec.req_for_reconsideration_ind),
X_Req_For_Adv_Standing_Ind => NVL(p_req_for_adv_standing_ind,c_prg_exists_rec.req_for_adv_standing_ind),
X_Mode => lv_mode -- enable security for Admin
);
END insert_adm_appl_prog;
FUNCTION insert_adm_appl_prog_inst(
p_person_id IN igs_pe_person.person_id%type ,
p_admission_appl_number IN NUMBER ,
p_acad_cal_type IN VARCHAR2 ,
p_acad_ci_sequence_number IN NUMBER ,
p_adm_cal_type IN VARCHAR2 ,
p_adm_ci_sequence_number IN NUMBER ,
p_admission_cat IN VARCHAR2 ,
p_s_admission_process_type IN VARCHAR2,
p_appl_dt IN DATE ,
p_adm_fee_status IN VARCHAR2 ,
p_preference_number IN NUMBER ,
p_offer_dt IN DATE ,
p_offer_response_dt IN DATE ,
p_course_cd IN VARCHAR2 ,
p_crv_version_number IN NUMBER ,
p_location_cd IN VARCHAR2 ,
p_attendance_mode IN VARCHAR2 ,
p_attendance_type IN VARCHAR2 ,
p_unit_set_cd IN VARCHAR2 ,
p_us_version_number IN NUMBER ,
p_fee_cat IN VARCHAR2 ,
p_correspondence_cat IN VARCHAR2 ,
p_enrolment_cat IN VARCHAR2 ,
p_funding_source IN VARCHAR2,
p_edu_goal_prior_enroll IN NUMBER,
p_app_source_id IN NUMBER,
p_apply_for_finaid IN VARCHAR2,
p_finaid_apply_date IN DATE,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_attribute21 IN VARCHAR2,
p_attribute22 IN VARCHAR2,
p_attribute23 IN VARCHAR2,
p_attribute24 IN VARCHAR2,
p_attribute25 IN VARCHAR2,
p_attribute26 IN VARCHAR2,
p_attribute27 IN VARCHAR2,
p_attribute28 IN VARCHAR2,
p_attribute29 IN VARCHAR2,
p_attribute30 IN VARCHAR2,
p_attribute31 IN VARCHAR2,
p_attribute32 IN VARCHAR2,
p_attribute33 IN VARCHAR2,
p_attribute34 IN VARCHAR2,
p_attribute35 IN VARCHAR2,
p_attribute36 IN VARCHAR2,
p_attribute37 IN VARCHAR2,
p_attribute38 IN VARCHAR2,
p_attribute39 IN VARCHAR2,
p_attribute40 IN VARCHAR2,
p_ss_application_id IN VARCHAR2,
p_sequence_number OUT NOCOPY NUMBER,
p_return_type OUT NOCOPY VARCHAR2 ,
p_error_code OUT NOCOPY VARCHAR2,
p_message_name OUT NOCOPY VARCHAR2,
p_entry_status IN NUMBER,
p_entry_level IN NUMBER,
p_sch_apl_to_id IN NUMBER,
p_hecs_payment_option IN VARCHAR2,
p_log IN VARCHAR2 DEFAULT 'Y')
RETURN BOOLEAN IS
/*************************************************************************************************************************
Created By: [email protected]
Date Created : 12-27-2001
Purpose: 1.This Functions inserts an admission application Program Instance
2.Inserts record into igs_ad_ps_appl_inst table after validations
3.Returns boolean true if the record is inserted, boolean false if the proc fails.
4.Flow/Calls used in the procedure :
|-->IGS_AD_GEN_004.admp_get_apcs_val(Get the admission process category steps.) -IGSAD04B.pls
|-->IGS_AD_VAL_ACAI.admp_val_acai_fc(Set fee category) -IGSAD22B.pls
|-->IGS_AD_GEN_005.admp_get_dflt_ecm(Set enrollment category) -IGSAD05B.pls
|-->IGS_AD_VAL_ACAI.admp_val_acai_cc(Set correspondence category) -IGSAD22B.pls
|-->Set admission COURSE application instance statuses for PENDING outcome
|-->validate_unit_sets(Validate the admission COURSE offering IGS_PS_UNIT set)
|-->admp_get_adm_step_values(get if location code, attendance mode and attendance type indicators are set)
|-->IGS_AD_VAL_ACAI.admp_val_acai_cop(Validate program offering patterns) -IGSAD22B.pls
|-->IGS_AD_PS_APPL_INST_PKG.insert_row(Insert an admission program application instance with PENDING outcome) -IGSAI18B.pls
Known limitations,enhancements,remarks:
Change History
Who When What
vdixit 19-Feb-2002 Added the logic to raise the business event in work flow
when a Self Service Admission Application is created enh : 2229679
nshee 29-Aug-2002 Added the six columns in the insert row for Deferments build - 2395510
tray 10-Sep-2002 Added Call to IGS_AD_GEN_003.get_entr_doc_apc for ADCR040 build - 2395510
pbondugu 28-Mar-2003 Added funding_source parameter in spec and body.
***************************************************************************************************************************/
l_sc_encoded_text VARCHAR2(4000);
-- This procedure inserts a new IGS_AD_PS_APPL_INST record.
logHeader('insert_adm_appl_prog_inst',null);
SELECT NVL(MAX(sequence_number), 0) + 1
FROM IGS_AD_PS_APPL_INST
WHERE
person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_course_cd;
SELECT ROWID, APAI.*
FROM IGS_AD_PS_APPL_INST APAI
WHERE
person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_course_cd AND
sequence_number = v_acai_sequence_number
FOR UPDATE OF person_id NOWAIT;
SELECT system_status
FROM igs_ad_code_classes
WHERE code_id = p_app_source_id AND
class = 'SYS_APPL_SOURCE';
SELECT s_admission_step_type,
step_type_restriction_num
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_cat = cp_admission_cat AND
s_admission_process_type = cp_s_admission_process_type AND
step_group_type <> 'TRACK'; -- 2402377
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type=l_system_type;
SELECT 'X'
INTO l_var
FROM
DUAL
WHERE
NVL(l_location_cd_ind,'N') IN ('Y','N')
AND NVL(l_attendance_mode_ind,'N') IN ('Y','N')
AND NVL(l_attendance_type_ind,'N')IN ('Y','N');
logDetail('insert_adm_appl_prog_inst'|| 'Validation Failed for attendance_mode_ind or location_cd_ind or attendance_type_ind',null );
-- Validate if there is any hold preventing the insert of the unitset selected -- igsm arvsrini
------------------------------------------------------------------------------
IF v_unit_set_appl_ind = 'Y' THEN
IF v_check_course_encumb = 'Y' THEN
IF NOT IGS_AD_VAL_ACAI.admp_val_us_encmb (
p_person_id,
p_course_cd,
p_unit_set_cd,
p_us_version_number,
p_adm_cal_type,
p_adm_ci_sequence_number,
v_check_course_encumb,
'Y', -- Offer indicator,
p_message_name,
v_return_type) THEN
IF v_return_type = cst_error THEN
IF IGS_AD_TI_COMP.G_CALLED_FROM = 'S' THEN
p_message_name:= 'IGS_AD_SS_USET_HOLD_EXISTS';
-- Insert an admission program application instance with PENDING outcome
--------------------------------------------------------------------------
l_org_id := igs_ge_gen_003.get_org_id;
logDetail('Before Call to Igs_Ad_Ps_Appl_Inst_Pkg.Insert_Row',null);
IGS_AD_PS_APPL_INST_PKG.Insert_Row (
X_ROWID => lv_rowid,
X_PERSON_ID => p_Person_Id,
X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
X_NOMINATED_COURSE_CD =>p_course_cd,
X_SEQUENCE_NUMBER => v_acai_sequence_number,
X_PREDICTED_GPA => NULL,
X_ACADEMIC_INDEX => NULL,
X_ADM_CAL_TYPE => p_adm_cal_type,
X_APP_FILE_LOCATION => NULL,
X_ADM_CI_SEQUENCE_NUMBER => p_adm_ci_sequence_number,
X_COURSE_CD => p_Course_Cd,
X_APP_SOURCE_ID => p_app_source_id,
X_CRV_VERSION_NUMBER => p_Crv_Version_Number,
X_WAITLIST_RANK => NULL,
X_LOCATION_CD => p_Location_Cd,
X_ATTENT_OTHER_INST_CD =>NULL,
X_ATTENDANCE_MODE => p_Attendance_Mode,
X_EDU_GOAL_PRIOR_ENROLL_ID => p_edu_goal_prior_enroll,
X_ATTENDANCE_TYPE => p_Attendance_Type,
X_DECISION_MAKE_ID => NULL,
X_UNIT_SET_CD => p_Unit_Set_Cd,
X_DECISION_DATE =>NULL,
X_ATTRIBUTE_CATEGORY =>p_attribute_category ,
X_ATTRIBUTE1=>P_ATTRIBUTE1,
X_ATTRIBUTE2=>P_ATTRIBUTE2,
X_ATTRIBUTE3=>P_ATTRIBUTE3,
X_ATTRIBUTE4=>P_ATTRIBUTE4,
X_ATTRIBUTE5=>P_ATTRIBUTE5,
X_ATTRIBUTE6=>P_ATTRIBUTE6,
X_ATTRIBUTE7=>P_ATTRIBUTE7,
X_ATTRIBUTE8=>P_ATTRIBUTE8,
X_ATTRIBUTE9=>P_ATTRIBUTE9,
X_ATTRIBUTE10=>P_ATTRIBUTE10,
X_ATTRIBUTE11=>P_ATTRIBUTE11,
X_ATTRIBUTE12=>P_ATTRIBUTE12,
X_ATTRIBUTE13=>P_ATTRIBUTE13,
X_ATTRIBUTE14=>P_ATTRIBUTE14,
X_ATTRIBUTE15=>P_ATTRIBUTE15,
X_ATTRIBUTE16=>P_ATTRIBUTE16,
X_ATTRIBUTE17=>P_ATTRIBUTE17,
X_ATTRIBUTE18=>P_ATTRIBUTE18,
X_ATTRIBUTE19=>P_ATTRIBUTE19,
X_ATTRIBUTE20=>P_ATTRIBUTE20,
X_WAITLIST_STATUS => NULL,
X_ATTRIBUTE21=>P_ATTRIBUTE21,
X_ATTRIBUTE22=>P_ATTRIBUTE22,
X_ATTRIBUTE23=>P_ATTRIBUTE23,
X_ATTRIBUTE24=>P_ATTRIBUTE24,
X_ATTRIBUTE25=>P_ATTRIBUTE25,
X_ATTRIBUTE26=>P_ATTRIBUTE26,
X_ATTRIBUTE27=>P_ATTRIBUTE27,
X_ATTRIBUTE28=>P_ATTRIBUTE28,
X_ATTRIBUTE29=>P_ATTRIBUTE29,
X_ATTRIBUTE30=>P_ATTRIBUTE30,
X_ATTRIBUTE31=>P_ATTRIBUTE31,
X_ATTRIBUTE32=>P_ATTRIBUTE32,
X_ATTRIBUTE33=>P_ATTRIBUTE33,
X_ATTRIBUTE34=>P_ATTRIBUTE34,
X_ATTRIBUTE35=>P_ATTRIBUTE35,
X_ATTRIBUTE36=>P_ATTRIBUTE36,
X_ATTRIBUTE37=>P_ATTRIBUTE37,
X_ATTRIBUTE38=>P_ATTRIBUTE38,
X_ATTRIBUTE39=>P_ATTRIBUTE39,
X_ATTRIBUTE40=>P_ATTRIBUTE40,
X_SS_APPLICATION_ID=>P_SS_APPLICATION_ID,
X_SS_PWD=> NULL,
X_DECISION_REASON_ID=>NULL,
X_US_VERSION_NUMBER => p_Us_Version_Number,
X_DECISION_NOTES=>NULL,
X_PENDING_REASON_ID=>NULL,
X_PREFERENCE_NUMBER => p_Preference_Number,
X_ADM_DOC_STATUS=> v_Adm_Doc_Status,
X_ADM_ENTRY_QUAL_STATUS=> v_Adm_Entry_Qual_Status,
X_DEFICIENCY_IN_PREP=>NULL,
X_LATE_ADM_FEE_STATUS => v_Late_Adm_Fee_Status,
X_SPL_CONSIDER_COMMENTS=>NULL,
X_APPLY_FOR_FINAID =>p_apply_for_finaid,
X_FINAID_APPLY_DATE=>l_finaid_apply_date,
X_ADM_OUTCOME_STATUS => v_adm_pending_outcome_status,
X_ADM_OTCM_STAT_AUTH_PER_ID=>NULL,
X_ADM_OUTCOME_STATUS_AUTH_DT=> NULL,
X_ADM_OUTCOME_STATUS_REASON =>NULL,
X_OFFER_DT => NULL,
X_OFFER_RESPONSE_DT => NULL,
X_PRPSD_COMMENCEMENT_DT => NULL,
X_ADM_CNDTNL_OFFER_STATUS => v_adm_cndtnl_offer_status,
X_CNDTNL_OFFER_SATISFIED_DT => NULL,
X_CNDNL_OFR_MUST_BE_STSFD_IND => 'N',
X_ADM_OFFER_RESP_STATUS => v_adm_offer_resp_status,
X_ACTUAL_RESPONSE_DT => NULL,
X_ADM_OFFER_DFRMNT_STATUS => v_adm_offer_dfrmnt_status,
X_DEFERRED_ADM_CAL_TYPE => NULL,
X_DEFERRED_ADM_CI_SEQUENCE_NUM => NULL,
X_DEFERRED_TRACKING_ID => NULL,
X_ASS_RANK => NULL,
X_SECONDARY_ASS_RANK => NULL,
X_INTR_ACCEPT_ADVICE_NUM => NULL,
X_ASS_TRACKING_ID => NULL,
X_FEE_CAT=> v_Fee_Cat,
X_HECS_PAYMENT_OPTION => v_Hecs_Payment_Option,
X_EXPECTED_COMPLETION_YR => NULL,
X_EXPECTED_COMPLETION_PERD => NULL,
X_CORRESPONDENCE_CAT => v_Correspondence_Cat,
X_ENROLMENT_CAT => v_Enrolment_Cat,
X_FUNDING_SOURCE => v_funding_source,
X_APPLICANT_ACPTNCE_CNDTN => NULL,
X_CNDTNL_OFFER_CNDTN => NULL,
X_AUTHORIZED_DT => NULL,
X_AUTHORIZING_PERS_ID => NULL,
X_IDX_CALC_DATE => NULL,
X_MODE =>lv_mode, -- enable security for Admin
X_FUT_ACAD_CAL_TYPE => NULL , -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => NULL ,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => NULL , -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => NULL, -- Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => NULL,-- Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
X_Org_Id => l_org_id,
X_ENTRY_STATUS => p_entry_status,
X_ENTRY_LEVEL => p_entry_level,
X_SCH_APL_TO_ID => p_sch_apl_to_id);
logDetail('Before Call to Igs_Ad_Ps_Appl_Inst_Pkg.Insert_Row, returning TRUE',null);
IGS_PE_TYP_INSTANCES_PKG.INSERT_ROW(
X_MODE => lv_mode, -- enable security for Admin
X_RowId => lv_rowid2,
X_TYPE_INSTANCE_ID => lv_type_instance_id,
X_PERSON_TYPE_CODE => l_person_type_code,
X_PERSON_ID => p_Person_Id,
X_COURSE_CD => NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
X_NOMINATED_COURSE_CD => p_course_cd,
X_SEQUENCE_NUMBER => v_acai_sequence_number,
X_START_DATE => lv_sysdate,
X_END_DATE => NULL,
X_CREATE_METHOD => 'CREATE_APPL_INSTANCE',
X_ENDED_BY => NULL,
X_END_METHOD => NULL,
X_CC_VERSION_NUMBER => NULL,
X_NCC_VERSION_NUMBER => p_Crv_Version_Number,
X_Org_Id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => NULL
);
logDetail('insert_adm_appl_prog_inst'|| 'Exception from insert_adm_appl_prog_inst: '||SQLERRM,null);
Fnd_Message.Set_Token('NAME','IGS_AD_GEN_014.insert_adm_appl_prog_inst -'||SQLERRM);
END insert_adm_appl_prog_inst;
SELECT uss.s_unit_set_status,
us.expiry_dt
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_STAT uss
WHERE us.unit_set_cat IN
( SELECT usc.unit_set_cat
FROM igs_en_unit_set_cat usc
WHERE (fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND' ) <> 'Y'
OR usc.s_unit_set_cat = 'PRENRL_YR') )
AND us.UNIT_SET_STATUS = uss.UNIT_SET_STATUS
AND us.unit_set_cd = cp_unit_set_cd
AND us.version_number = cp_us_version_number;
SELECT 1
FROM IGS_PS_OFR_OPT_UNIT_SET_V psusv
WHERE psusv.course_cd = cp_course_cd
AND psusv.crv_version_number = cp_crv_version_number
AND psusv.unit_set_cd = cp_unit_set_cd
AND psusv.us_version_number = cp_us_version_number
AND psusv.cal_type = cp_acad_cal_type
AND psusv.location_cd = NVL(cp_location_cd, psusv.location_cd)
AND psusv.attendance_mode = NVL(cp_attendance_mode, psusv.attendance_mode)
AND psusv.attendance_type = NVL(cp_attendance_type, psusv.attendance_type)
AND NOT EXISTS
(SELECT 1
FROM igs_ps_coo_ad_unit_s psus
WHERE psus.course_cd = psusv.course_cd
AND psus.crv_version_number = psusv.crv_version_number
AND psus.cal_type = psusv.cal_type
AND psus.location_cd = psusv.location_cd
AND psus.attendance_mode = psusv.attendance_mode
AND psus.attendance_type = psusv.attendance_type
AND psus.admission_cat = cp_admission_cat
)
AND psusv.UNIT_SET_STATUS IN
(SELECT unit_set_status
FROM igs_en_unit_set_stat uss
WHERE psusv.unit_set_status = uss.unit_set_status
AND uss.s_unit_set_status <> 'INACTIVE'
)
AND psusv.unit_set_cat IN
(SELECT usc.unit_set_cat
FROM igs_en_unit_set_cat usc
WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
OR usc.s_unit_set_cat = 'PRENRL_YR'))
)
AND psusv.expiry_dt IS NULL
UNION
SELECT 1
FROM igs_ps_coo_ad_unit_s psus,
igs_en_unit_set us
WHERE us.unit_set_cd = psus.unit_set_cd
AND us.version_number = psus.us_version_number
AND psus.unit_set_cd = cp_unit_set_cd
AND psus.us_version_number = cp_us_version_number
AND psus.course_cd = cp_course_cd
AND psus.crv_version_number = cp_crv_version_number
AND psus.cal_type = cp_acad_cal_type
AND psus.location_cd = NVL(cp_location_cd, psus.location_cd)
AND psus.attendance_mode = NVL(cp_attendance_mode, psus.attendance_mode)
AND psus.attendance_type = NVL(cp_attendance_type, psus.attendance_type)
AND psus.admission_cat = cp_admission_cat
AND us.unit_set_status IN
(SELECT unit_set_status
FROM igs_en_unit_set_stat uss
WHERE us.unit_set_status = uss.unit_set_status
AND uss.s_unit_set_status <> 'INACTIVE'
)
AND us.unit_set_cat IN
(SELECT usc.unit_set_cat
FROM igs_en_unit_set_cat usc
WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
OR usc.s_unit_set_cat = 'PRENRL_YR'))
)
AND us.expiry_dt IS NULL;
SELECT coacus.unit_set_cd,
coacus.us_version_number,
coacus.location_cd,
coacus.attendance_mode,
coacus.attendance_type
FROM IGS_PS_COO_AD_UNIT_S coacus
WHERE coacus.course_cd = cp_course_cd AND
coacus.crv_version_number = cp_crv_version_number AND
coacus.cal_type = cp_acad_cal_type AND
coacus.admission_cat = cp_admission_cat AND
coacus.location_cd = cp_location_cd AND
coacus.attendance_mode = cp_attendance_mode AND
coacus.attendance_type = cp_attendance_type;
SELECT 'X'
INTO L_VAR
FROM
IGS_AD_PRCS_CAT_STEP
WHERE
S_ADMISSION_STEP_TYPE = 'UN-CRS-LOC'
AND ADMISSION_CAT = p_admission_cat
AND S_ADMISSION_PROCESS_TYPE = p_s_admission_process_type
AND step_group_type <> 'TRACK'; --2404377
SELECT 'X'
INTO L_VAR
FROM
IGS_AD_PRCS_CAT_STEP
WHERE
S_ADMISSION_STEP_TYPE = 'UN-CRS-MOD'
AND ADMISSION_CAT = p_admission_cat
AND S_ADMISSION_PROCESS_TYPE = p_s_admission_process_type
AND step_group_type <> 'TRACK' ; --2402377
SELECT 'X'
INTO L_VAR
FROM
IGS_AD_PRCS_CAT_STEP
WHERE
S_ADMISSION_STEP_TYPE = 'UN-CRS-MOD'
AND ADMISSION_CAT = p_admission_cat
AND S_ADMISSION_PROCESS_TYPE = p_s_admission_process_type
AND STEP_GROUP_TYPE <> 'TRACK'; -- 2402377
SELECT ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE FROM IGS_AD_APPL_ALL
WHERE PERSON_ID = cp_person_id AND ADMISSION_APPL_NUMBER = cp_admission_Appl_number;
SELECT '1' FROM IGS_AD_PRCS_CAT_STEP_ALL
WHERE ADMISSION_CAT = cp_admission_cat
AND S_ADMISSION_PROCESS_TYPE =cp_adm_pro_type
AND S_ADMISSION_STEP_TYPE = cp_admission_step_type;
SELECT ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE FROM IGS_AD_APPL_ALL
WHERE PERSON_ID = cp_person_id AND ADMISSION_APPL_NUMBER = cp_admission_Appl_number;
SELECT QUALIFYING_TYPE_CODE
FROM IGS_AD_QUAL_TYPE
WHERE ADMISSION_CAT = cp_admission_cat AND S_ADMISSION_PROCESS_TYPE = cp_admission_process_type
AND CLOSED_FLAG = 'N';
SELECT CODE_ID FROM IGS_AD_CODE_CLASSES
WHERE CLASS=cp_qual_type AND CLASS_TYPE_CODE = 'IGS_AD_QUAL_TYPE'
AND SYSTEM_DEFAULT = 'Y';
igs_ad_appqual_code_pkg.INSERT_ROW
(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_admission_appl_number => p_admission_appl_number,
x_nominated_course_cd => p_course_cd,
x_sequence_number => p_sequence_number,
x_qualifying_type_code => l_apc_qual_type_rec.QUALIFYING_TYPE_CODE,
x_qualifying_code_id => l_def_qual_code,
x_qualifying_value => NULL,
x_mode => 'R'
);