[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uoo_id, relation_type
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = cp_unit_cd
AND version_number = cp_ver_num
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_seq_num
AND unit_class = cp_unit_class
AND location_cd = cp_location_cd;
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.location_cd,
sca.attendance_mode,
sca.attendance_type,
sca.course_attempt_status,
sca.commencement_dt,
sca.coo_id,
sca.funding_source
FROM igs_en_stdnt_ps_att sca
WHERE
sca.course_cd = p_course_cd
AND
((sca.person_id in (select person_id from IGS_PE_PRSID_GRP_MEM WHERE
group_id = p_group_id) AND
p_group_id is NOT NULL) OR
(p_group_id is NULL))
AND
(p_version_number IS NULL OR
p_version_number = igs_en_spa_terms_api.get_spat_program_version
(sca.person_id, sca.course_cd, p_term_cal_type, p_term_sequence_number)) AND
(p_acad_cal_type = igs_en_spa_terms_api.get_spat_acad_cal_type
(sca.person_id, sca.course_cd, p_term_cal_type, p_term_sequence_number)) AND
(p_location_cd IS NULL OR
p_location_cd = igs_en_spa_terms_api.get_spat_location
(sca.person_id, sca.course_cd, p_term_cal_type, p_term_sequence_number)) AND
(p_attendance_mode IS NULL OR
p_attendance_mode= igs_en_spa_terms_api.get_spat_att_mode
(sca.person_id, sca.course_cd, p_term_cal_type, p_term_sequence_number)) AND
(p_attendance_type IS NULL OR
p_attendance_type= igs_en_spa_terms_api.get_spat_att_type
(sca.person_id, sca.course_cd, p_term_cal_type, p_term_sequence_number)) AND
sca.course_attempt_status IN (
p_course_attempt_status1,
NVL(p_course_attempt_status2, p_course_attempt_status1),
NVL(p_course_attempt_status3, p_course_attempt_status1),
NVL(p_course_attempt_status4, p_course_attempt_status1),
NVL(p_course_attempt_status5, p_course_attempt_status1),
NVL(p_course_attempt_status6, p_course_attempt_status1))
ORDER BY
sca.person_id,
sca.course_cd;
sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
call as a part of bug 3544927.
stutta 03-Nov-2004 Added call igs_en_val_sca.enrp_val_chgo_alwd to log a
warning message. bug#3959306
jtmathew 05-Nov-2004 Modifications as per HESA DLD for bug 3985220
*****************************************************************************/
BEGIN -- enrpl_upd_sca_coo
-- Local procedure to process the transfer of course offering option.
DECLARE
CURSOR c_coo IS
SELECT coo.coo_id
FROM IGS_PS_OFR_OPT coo
WHERE coo.course_cd = p_course_cd AND
coo.version_number = p_to_crs_version_number AND
coo.cal_type = p_to_acad_cal_type AND
coo.location_cd = p_to_location_cd AND
coo.attendance_type = p_to_attendance_type AND
coo.attendance_mode = p_to_attendance_mode AND
coo.delete_flag = 'N';
SELECT cs.s_course_status,
cv.expiry_dt
FROM IGS_PS_VER cv,
IGS_PS_STAT cs
WHERE cv.course_cd = p_course_cd AND
cv.version_number = p_to_crs_version_number AND
cs.COURSE_STATUS = cv.COURSE_STATUS;
SELECT susa.unit_set_cd,
susa.us_version_number
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL;
SELECT 'x' FROM
IGS_PS_OFR_UNIT_SET cous,
IGS_PS_OFR_OPT coo
WHERE
cous.unit_set_cd = cp_unit_set_cd and
cous.us_version_number = cp_us_version_number and
coo.coo_id = cp_coo_id and
coo.course_cd = cous.course_cd and
coo.version_number = cous.crv_version_number and
coo.CAL_TYPE = cous.CAL_TYPE and
NOT EXISTS (select course_cd from IGS_PS_OF_OPT_UNT_ST coous
where
coous.course_cd = cous.course_cd and
coous.crv_version_number = cous.crv_version_number and
coous.CAL_TYPE = cous.CAL_TYPE and
coous.unit_set_cd = cous.unit_set_cd and
coous.us_version_number = cous.us_version_number)
UNION ALL
SELECT 'x' FROM
IGS_PS_OF_OPT_UNT_ST coous
WHERE
coous.unit_set_cd = cp_unit_set_cd and
coous.us_version_number = cp_us_version_number and
coous.coo_id = cp_coo_id ;
SELECT rowid,IGS_EN_STDNT_PS_ATT.*
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_course_cd
FOR UPDATE OF
version_number,
cal_type,
location_cd,
attendance_type,
attendance_mode,
coo_id NOWAIT;
SELECT spa.rowid , spa.*
FROM igs_he_st_spa_all spa
WHERE spa.person_id = p_person_id AND
spa.course_cd = p_course_cd
FOR UPDATE NOWAIT;
v_update_coo BOOLEAN := TRUE;
v_update_coo := TRUE;
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_upd_exists.rowid,
X_PERSON_ID => v_sca_upd_exists.PERSON_ID,
X_COURSE_CD => v_sca_upd_exists.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_upd_exists.ADVANCED_STANDING_IND,
X_FEE_CAT => v_sca_upd_exists.FEE_CAT,
X_CORRESPONDENCE_CAT => v_sca_upd_exists.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => v_sca_upd_exists.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_upd_exists.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_sca_upd_exists.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => v_sca_upd_exists.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => v_sca_upd_exists.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => p_to_crs_version_number,
X_CAL_TYPE => p_to_acad_cal_type,
X_LOCATION_CD => p_to_location_cd,
X_ATTENDANCE_MODE => p_to_attendance_mode,
X_ATTENDANCE_TYPE => p_to_attendance_type,
X_COO_ID => v_coo_rec.coo_id,
X_STUDENT_CONFIRMED_IND => v_sca_upd_exists.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => v_sca_upd_exists.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => v_sca_upd_exists.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => v_sca_upd_exists.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_upd_exists.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_upd_exists.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_sca_upd_exists.PROVISIONAL_IND,
X_DISCONTINUED_DT => v_sca_upd_exists.DISCONTINUED_DT,
X_DISCONTINUATION_REASON_CD => v_sca_upd_exists.DISCONTINUATION_REASON_CD,
X_LAPSED_DT => v_sca_upd_exists.LAPSED_DT,
X_FUNDING_SOURCE => v_sca_upd_exists.EXAM_LOCATION_CD,
X_EXAM_LOCATION_CD => v_sca_upd_exists.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_upd_exists.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_upd_exists.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_upd_exists.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => v_sca_upd_exists.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => v_sca_upd_exists.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_upd_exists.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_upd_exists.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_upd_exists.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_exists.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_exists.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_exists.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_upd_exists.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
x_last_date_of_attendance => v_sca_upd_exists.last_date_of_attendance,
x_dropped_by => v_sca_upd_exists.dropped_by ,
x_igs_pr_class_std_id => v_sca_upd_exists.igs_pr_class_std_id , --Enhancement Bug 1877222, pmarada
-- Added next four parameters as per the Career Impact Build Bug# 2027984
x_primary_program_type => v_sca_upd_exists.primary_program_type,
x_primary_prog_type_source => v_sca_upd_exists.primary_prog_type_source,
x_catalog_cal_type => v_sca_upd_exists.catalog_cal_type,
x_catalog_seq_num => v_sca_upd_exists.catalog_seq_num,
x_key_program => v_sca_upd_exists.key_program,
-- The following two parameters were added as part of the build EN015. Bug# 2158654 - pradhakr
x_override_cmpl_dt => v_sca_upd_exists.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_sca_upd_exists.manual_ovr_cmpl_dt_ind,
-- added by ckasu as part of bug # 3544927
X_ATTRIBUTE_CATEGORY => v_sca_upd_exists.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sca_upd_exists.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_upd_exists.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_upd_exists.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_upd_exists.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_upd_exists.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_upd_exists.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_upd_exists.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_upd_exists.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_upd_exists.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_upd_exists.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_upd_exists.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_upd_exists.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_upd_exists.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_upd_exists.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_upd_exists.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_upd_exists.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_upd_exists.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_upd_exists.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_upd_exists.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sca_upd_exists.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_exists.FUTURE_DATED_TRANS_FLAG);
igs_en_spa_terms_api.create_update_term_rec(
p_person_id => v_sca_upd_exists.PERSON_ID,
p_program_cd => v_sca_upd_exists.COURSE_CD,
p_term_cal_type => p_term_cal_type,
p_term_sequence_number => p_term_sequence_number,
p_coo_id => v_coo_rec.coo_id,
p_ripple_frwrd => TRUE,
p_message_name => l_message_name,
p_update_rec => TRUE);
IGS_HE_ST_SPA_ALL_PKG.UPDATE_ROW (
X_ROWID => v_he_spa_upd_rec.rowid ,
X_HESA_ST_SPA_ID => v_he_spa_upd_rec.hesa_st_spa_id ,
X_ORG_ID => v_he_spa_upd_rec.org_id ,
X_PERSON_ID => v_he_spa_upd_rec.person_id ,
X_COURSE_CD => v_he_spa_upd_rec.course_cd ,
X_VERSION_NUMBER => p_to_crs_version_number ,
X_FE_STUDENT_MARKER => v_he_spa_upd_rec.fe_student_marker ,
X_DOMICILE_CD => v_he_spa_upd_rec.domicile_cd ,
X_INST_LAST_ATTENDED => v_he_spa_upd_rec.inst_last_attended ,
X_YEAR_LEFT_LAST_INST => v_he_spa_upd_rec.year_left_last_inst ,
X_HIGHEST_QUAL_ON_ENTRY => v_he_spa_upd_rec.highest_qual_on_entry ,
X_DATE_QUAL_ON_ENTRY_CALC => v_he_spa_upd_rec.date_qual_on_entry_calc ,
X_A_LEVEL_POINT_SCORE => v_he_spa_upd_rec.a_level_point_score ,
X_HIGHERS_POINTS_SCORES => v_he_spa_upd_rec.highers_points_scores ,
X_OCCUPATION_CODE => v_he_spa_upd_rec.occupation_code ,
X_COMMENCEMENT_DT => v_he_spa_upd_rec.commencement_dt ,
X_SPECIAL_STUDENT => v_he_spa_upd_rec.special_student ,
X_STUDENT_QUAL_AIM => v_he_spa_upd_rec.student_qual_aim ,
X_STUDENT_FE_QUAL_AIM => v_he_spa_upd_rec.student_fe_qual_aim ,
X_TEACHER_TRAIN_PROG_ID => v_he_spa_upd_rec.teacher_train_prog_id ,
X_ITT_PHASE => v_he_spa_upd_rec.itt_phase ,
X_BILINGUAL_ITT_MARKER => v_he_spa_upd_rec.bilingual_itt_marker ,
X_TEACHING_QUAL_GAIN_SECTOR => v_he_spa_upd_rec.teaching_qual_gain_sector ,
X_TEACHING_QUAL_GAIN_SUBJ1 => v_he_spa_upd_rec.teaching_qual_gain_subj1 ,
X_TEACHING_QUAL_GAIN_SUBJ2 => v_he_spa_upd_rec.teaching_qual_gain_subj2 ,
X_TEACHING_QUAL_GAIN_SUBJ3 => v_he_spa_upd_rec.teaching_qual_gain_subj3 ,
X_STUDENT_INST_NUMBER => v_he_spa_upd_rec.student_inst_number ,
X_DESTINATION => v_he_spa_upd_rec.destination ,
X_ITT_PROG_OUTCOME => v_he_spa_upd_rec.itt_prog_outcome ,
X_HESA_RETURN_NAME => v_he_spa_upd_rec.hesa_return_name ,
X_HESA_RETURN_ID => v_he_spa_upd_rec.hesa_return_id ,
X_HESA_SUBMISSION_NAME => v_he_spa_upd_rec.hesa_submission_name ,
X_ASSOCIATE_UCAS_NUMBER => v_he_spa_upd_rec.associate_ucas_number ,
X_ASSOCIATE_SCOTT_CAND => v_he_spa_upd_rec.associate_scott_cand ,
X_ASSOCIATE_TEACH_REF_NUM => v_he_spa_upd_rec.associate_teach_ref_num ,
X_ASSOCIATE_NHS_REG_NUM => v_he_spa_upd_rec.associate_nhs_reg_num ,
X_NHS_FUNDING_SOURCE => v_he_spa_upd_rec.nhs_funding_source ,
X_UFI_PLACE => v_he_spa_upd_rec.ufi_place ,
X_POSTCODE => v_he_spa_upd_rec.postcode ,
X_SOCIAL_CLASS_IND => v_he_spa_upd_rec.social_class_ind ,
X_OCCCODE => v_he_spa_upd_rec.occcode ,
X_TOTAL_UCAS_TARIFF => v_he_spa_upd_rec.total_ucas_tariff ,
X_NHS_EMPLOYER => v_he_spa_upd_rec.nhs_employer ,
X_RETURN_TYPE => v_he_spa_upd_rec.return_type ,
X_QUAL_AIM_SUBJ1 => v_he_spa_upd_rec.qual_aim_subj1 ,
X_QUAL_AIM_SUBJ2 => v_he_spa_upd_rec.qual_aim_subj2 ,
X_QUAL_AIM_SUBJ3 => v_he_spa_upd_rec.qual_aim_subj3 ,
X_QUAL_AIM_PROPORTION => v_he_spa_upd_rec.qual_aim_proportion ,
X_DEPENDANTS_CD => v_he_spa_upd_rec.dependants_cd ,
X_IMPLIED_FUND_RATE => v_he_spa_upd_rec.implied_fund_rate ,
X_GOV_INITIATIVES_CD => v_he_spa_upd_rec.gov_initiatives_cd ,
X_UNITS_FOR_QUAL => v_he_spa_upd_rec.units_for_qual ,
X_DISADV_UPLIFT_ELIG_CD => v_he_spa_upd_rec.disadv_uplift_elig_cd ,
X_FRANCH_PARTNER_CD => v_he_spa_upd_rec.franch_partner_cd,
X_UNITS_COMPLETED => v_he_spa_upd_rec.units_completed,
X_FRANCH_OUT_ARR_CD => v_he_spa_upd_rec.franch_out_arr_cd,
X_EMPLOYER_ROLE_CD => v_he_spa_upd_rec.employer_role_cd,
X_DISADV_UPLIFT_FACTOR => v_he_spa_upd_rec.disadv_uplift_factor,
X_ENH_FUND_ELIG_CD => v_he_spa_upd_rec.enh_fund_elig_cd,
X_MODE => 'R'
) ;
SELECT attendance_type, attendance_mode, location_cd
FROM igs_en_spa_terms
WHERE
person_id = p_sca_person_id AND
program_cd = p_sca_course_cd AND
term_cal_type = p_term_cal_type AND
term_sequence_number = p_term_sequence_number;
IS SELECT party_number FROM hz_parties
WHERE party_id = cp_person_id;
CURSOR c_sca IS SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.coo_id
FROM IGS_EN_STDNT_PS_ATT sca
WHERE -- person_id matches
(p_group_id IS NULL OR
EXISTS (
SELECT 'X'
FROM IGS_PE_PRSID_GRP_MEM pig
WHERE pig.group_id = p_group_id AND
pig.person_id = sca.person_id AND
(pig.start_date IS NULL OR pig.start_date <= TRUNC(SYSDATE)) AND
(pig.end_date IS NULL OR pig.end_date >= TRUNC(SYSDATE))
))
AND sca.person_id = NVL (p_person_id,sca.person_id )
AND sca.course_cd like p_course_cd
AND match_term_sca_params (
sca.person_id,
sca.course_cd,
NULL,
sca.attendance_type,
sca.attendance_mode,
sca.location_cd,
p_course_cd,
NULL,
p_attendance_type,
p_attendance_mode,
p_location_cd,
p_load_cal_type,
p_load_cal_seq )='Y'
AND sca.course_attempt_status IN (cst_inactive,cst_enrolled,cst_intermit,cst_complete)
AND (p_unit_cd IS NULL OR
EXISTS (
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = sca.person_id AND
sua.unit_cd = p_unit_cd AND
sua.version_number = NVL(p_uv_version_number, sua.version_number) AND
sua.location_cd = NVL(p_unit_loc_cd,sua.location_cd) AND
sua.unit_class = NVL(p_unit_class,sua.unit_class) AND
sua.cal_type = p_teach_cal_type AND
sua.ci_sequence_number = p_teach_ci_sequence_number))
AND (igs_en_spa_terms_api.get_spat_primary_prg(sca.person_id,sca.course_cd,
p_load_cal_type,p_load_cal_seq) = 'PRIMARY'
OR NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N') = 'N')
ORDER BY sca.person_id,
sca.course_cd
FOR UPDATE NOWAIT;
SELECT meaning
FROM igs_lookup_values v1
WHERE v1.lookup_Type = 'VS_EN_ACT_UNIT' AND
v1.lookup_code=cp_code;
SELECT uoo_id,relation_type
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = cp_unit_cd
AND version_number = cp_ver_num
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_seq_num
AND unit_class = cp_unit_class
AND location_cd = cp_location_cd;
SELECT unit_attempt_status
FROM igs_en_su_attempt
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT '1'
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = cp_teach_cal_type
AND teach_ci_sequence_number = cp_teach_seq_num
AND load_cal_type = cp_load_cal_type
AND load_ci_sequence_number = cp_load_seq_num;
SELECT sua.uoo_id,sua.unit_attempt_status,uoo.relation_type
FROM igs_en_su_attempt sua,
igs_ps_unit_ofr_opt uoo
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.unit_cd = cp_unit_cd
AND sua.version_number = NVL(cp_ver_num,sua.version_number)
AND sua.cal_type = cp_cal_type
AND sua.ci_sequence_number = cp_ci_seq_num
AND sua.unit_class = NVL(cp_unit_class, sua.unit_class)
AND sua.location_cd = NVL(cp_location_cd, sua.location_cd)
AND sua.uoo_id = uoo.uoo_id;
igs_ss_en_wrappers.insert_into_enr_worksheet(p_person_number =>l_person_number,
p_course_cd =>rec_sca.course_cd,
p_uoo_id =>l_uoo_id,
p_waitlist_ind =>l_waitlist_ind,
p_session_id =>NULL,
p_return_status =>l_ret_stat,
p_message =>l_message_name,
p_cal_type =>p_load_cal_type,
p_ci_sequence_number =>p_load_cal_seq,
p_audit_requested =>p_no_assessment_ind,
p_enr_method =>p_enr_method,
p_override_cp =>null,
p_subtitle =>null,
p_gradsch_cd =>null,
p_gs_version_num =>null,
p_core_indicator_code =>l_core_indicator_code, -- ptandon, Prevent Dropping Core Units build
p_calling_obj =>'JOB'
);
Select suar.reference_code_id,
suar.reference_cd_type,
suar.reference_cd,
suar.applied_course_cd
From IGS_AS_SUA_REF_CDS suar
Where suar.person_id = cp_person_id
And suar.course_cd = cp_course_cd
And suar.uoo_id = cp_uoo_id
And suar.deleted_date IS NULL;
igs_as_sua_ref_cds_pkg.insert_row (
x_rowid => l_rowid,
x_suar_id => l_suarid,
x_person_id => suar_table(l_count).person_id,
x_course_cd => suar_table(l_count).course_cd,
x_uoo_id => p_to_uoo_id,
x_reference_code_id => suar_table(l_count).reference_code_id,
x_reference_cd_type => suar_table(l_count).reference_cd_type,
x_reference_cd => suar_table(l_count).reference_cd,
x_applied_course_cd => suar_table(l_count).applied_course_cd,
x_deleted_date => NULL);
SELECT sua.person_id,
sua.course_cd,
sua.location_cd,
sua.unit_class,
sua.unit_attempt_status,
sua.enrolled_dt,
sca.coo_id,
sua.version_number,
sca.version_number program_version_number,
sua.uoo_id
FROM
igs_en_su_attempt sua,
igs_en_stdnt_ps_att sca
WHERE (
(p_course_cd IS NULL OR sca.course_cd LIKE p_course_cd) AND
(sca.course_attempt_status NOT IN ('LAPSED','DISCONTIN')) AND
(p_location_cd IS NULL OR
p_location_cd = igs_en_spa_terms_api.get_spat_location(
sca.person_id, sca.course_cd, cp_load_cal_type, cp_load_ci_sequence_number)) AND
(p_attendance_mode IS NULL OR
p_attendance_mode = igs_en_spa_terms_api.get_spat_att_mode(
sca.person_id, sca.course_cd, cp_load_cal_type, cp_load_ci_sequence_number)) AND
(p_attendance_type IS NULL OR
p_attendance_type = igs_en_spa_terms_api.get_spat_att_type(
sca.person_id, sca.course_cd, cp_load_cal_type, cp_load_ci_sequence_number)) AND
sca.person_id = sua.person_id AND
sca.course_cd = sua.course_cd AND
sua.unit_cd = p_from_unit_cd AND
(p_from_uv_version_number IS NULL OR
sua.version_number = p_from_uv_version_number) AND
sua.cal_type = p_teach_cal_type AND
sua.ci_sequence_number = p_teach_ci_sequence_number AND
(p_from_location_cd IS NULL OR
sua.location_cd = p_from_location_cd) AND
(p_from_unit_class IS NULL OR
sua.unit_class = p_from_unit_class) AND
sua.unit_attempt_status IN (
p_unit_attempt_status1,
NVL(p_unit_attempt_status2, p_unit_attempt_status1),
NVL(p_unit_attempt_status3, p_unit_attempt_status1)) AND
(p_group_id IS NULL OR
EXISTS (
SELECT 'X'
FROM igs_pe_prsid_grp_mem pigm
WHERE
pigm.group_id = p_group_id AND
pigm.person_id = sca.person_id AND
(pigm.end_date IS NULL OR pigm.end_date >= trunc(sysdate)) AND
(pigm.start_date IS NULL OR pigm.start_date <= trunc(sysdate))
)))
ORDER BY
sua.unit_cd, sua.enrolled_dt;
SELECT sle.key,
sle.message_name,
sle.text
FROM IGS_GE_S_LOG_ENTRY sle
WHERE sle.s_log_type = cst_enr_blk_uo AND
sle.creation_dt = cp_rule_creation_dt
ORDER BY sle.sequence_number;
SELECT rowid,'x'
FROM IGS_GE_S_LOG_ENTRY
WHERE s_log_type = cst_enr_blk_uo AND
creation_dt = cp_rule_creation_dt
FOR UPDATE OF LAST_UPDATED_BY NOWAIT;
SELECT rowid,'x'
FROM IGS_GE_S_LOG
WHERE s_log_type = cst_enr_blk_uo AND
creation_dt = cp_rule_creation_dt
FOR UPDATE OF LAST_UPDATED_BY NOWAIT;
SELECT tl.load_cal_type ,
tl.load_ci_sequence_number
FROM igs_ca_teach_to_load_v tl,
IGS_CA_INST ci,
IGS_CA_STAT cs
WHERE tl.teach_cal_type = p_teach_cal_type
AND tl.teach_ci_sequence_number = p_teach_ci_sequence_number
AND ci.cal_type = tl.load_cal_type
AND ci.sequence_number = tl.load_ci_sequence_number
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE'
ORDER BY load_start_dt ASC;
SELECT uoo.uoo_id, uoo.reserved_seating_allowed
FROM IGS_PS_UNIT_OFR_OPT uoo
WHERE uoo.unit_cd = p_from_unit_cd AND
uoo.version_number = p_to_uv_version_number AND
uoo.cal_type = p_teach_cal_type AND
uoo.ci_sequence_number = p_teach_ci_sequence_number AND
uoo.location_cd = p_to_location_cd AND
uoo.unit_class = p_to_unit_class AND
uoo.offered_ind = 'Y';
SELECT us.s_unit_status,
uv.expiry_dt
FROM IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_STAT us
WHERE uv.unit_cd = p_from_unit_cd AND
uv.version_number = p_to_uv_version_number AND
us.UNIT_STATUS = uv.UNIT_STATUS;
SELECT
grading_schema_code,
gs_version_number,
override_enrolled_cp,
no_assessment_ind
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_from_uoo_id;
SELECT
NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) enrolled_credit_points
FROM IGS_PS_UNIT_OFR_OPT uoo,
IGS_PS_UNIT_VER uv,
IGS_PS_USEC_CPS cps
WHERE uoo.uoo_id = cps.uoo_id(+) AND
uoo.unit_cd = uv.unit_cd AND
uoo.version_number = uv.version_number AND
uoo.uoo_id = cp_uoo_id;
IS SELECT * FROM igs_ps_usec_as
WHERE uoo_id = cp_uoo_id;
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_d_uoo_id AND
sua.unit_attempt_status <> 'DROPPED';
SELECT sua.rowid, sua.*
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_from_uoo_id
FOR UPDATE OF
sua.version_number,
sua.location_cd,
sua.unit_class,
sua.uoo_id NOWAIT;
SELECT grading_schema_code, grd_schm_version_number, default_flag
FROM igs_ps_usec_grd_schm
WHERE uoo_id = l_uoo_id
AND default_flag = 'Y';
SELECT grading_schema_code, grd_schm_version_number, default_flag
FROM igs_ps_usec_grd_schm
WHERE uoo_id = l_uoo_id;
SELECT grading_schema_code, grd_schm_version_number
FROM igs_ps_unit_grd_schm
WHERE unit_code = l_unit_cd
AND unit_version_number = l_unit_version
AND default_flag = 'Y';
SELECT grading_schema_code, grd_schm_version_number
FROM igs_ps_unit_grd_schm
WHERE unit_code = l_unit_cd
AND unit_version_number = l_unit_version
AND grading_schema_code = p_grading_schema_code
AND grd_schm_version_number = p_grd_schm_version_number
AND default_flag = 'Y';
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = l_uoo_id ;
SELECT NVL(relation_type,'NONE'),sup_uoo_id
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id ;
SELECT NVL(spat.coo_id, spa.coo_id) coo_id
FROM igs_en_stdnt_ps_att spa,
igs_en_spa_terms spat
WHERE
spa.course_cd = cp_course_cd AND
spa.person_id = cp_person_id AND
spat.term_cal_type(+) = cp_load_cal_type AND
spat.term_sequence_number(+)= cp_load_sequence_number AND
spat.person_id(+) = spa.person_id AND
spat.program_cd(+) = spa.course_cd;
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_destination_uoo_id;
SELECT NVL(MAX(administrative_priority),0)+1
FROM igs_en_su_attempt
WHERE uoo_id=cp_uoo_id
AND waitlist_dt<=cp_waitlist_dt
AND unit_attempt_status='WAITLISTED';
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id FOR UPDATE;
v_update_uoo BOOLEAN ;
v_update_uoo := TRUE;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF v_update_uoo THEN
IF ((rec_f_unit.grading_schema_code IS NOT NULL) AND (rec_f_unit.gs_version_number IS NOT NULL)) THEN
IF NOT igs_ss_en_wrappers.enr_val_grad_usec(
p_destination_uoo_id,
rec_f_unit.grading_schema_code,
rec_f_unit.gs_version_number) THEN
IGS_GE_INS_SLE.genp_set_log_entry(
cst_enr_blk_uo,
cst_blk_uoo,
v_key,
'IGS_EN_MISMATH_GRAD', -- Failed to transfer.
cst_error || '|GRAD_SCH');
v_update_uoo := FALSE;
v_update_uoo := FALSE;
END IF; -- v_update_uoo
IF v_update_uoo AND rec_f_unit.override_enrolled_cp IS NOT NULL AND rec_f_unit.no_assessment_ind <> 'Y' THEN
IF l_override_enrolled_cp IS NOT NULL THEN
IF l_override_enrolled_cp <> rec_f_unit.override_enrolled_cp THEN
IGS_GE_INS_SLE.genp_set_log_entry(
cst_enr_blk_uo,
cst_blk_uoo,
v_key,
'IGS_EN_MISMATH_ENR_CP', -- Failed to transfer.
cst_error || '|OVER_EN_CP');
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y')) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y') ) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y') ) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y') ) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y')) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y') AND p_unit_attempt_status NOT IN (cst_unconfirm,cst_waitlisted)) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF v_update_uoo AND l_source_uoo_rel='SUPERIOR' THEN
l_msg_name:=NULL;
v_update_uoo := FALSE;
IF v_update_uoo THEN
-- Calculate the Total Enrolled Credit Points before Transferring the Unit from one Unit section to another .
l_eftsu_total := igs_en_prc_load.enrp_clc_eftsu_total(
p_person_id => p_person_id,
p_course_cd => p_course_cd ,
p_acad_cal_type => p_acad_cal_type,
p_acad_sequence_number => p_acad_seq_number,
p_load_cal_type => p_load_cal_type,
p_load_sequence_number => p_load_seq_number,
p_truncate_ind => 'N',
p_include_research_ind => 'Y' ,
p_key_course_cd => NULL ,
p_key_version_number => NULL ,
p_credit_points => l_total_credit_points );
igs_en_sua_api.update_unit_attempt(
X_ROWID => v_sua_upd_exists.rowid,
X_PERSON_ID => v_sua_upd_exists.PERSON_ID,
X_COURSE_CD => v_sua_upd_exists.COURSE_CD ,
X_UNIT_CD => v_sua_upd_exists.UNIT_CD,
X_CAL_TYPE => v_sua_upd_exists.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_sua_upd_exists.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => v_sua_upd_exists.version_number ,
X_LOCATION_CD => v_sua_upd_exists.location_cd,
X_UNIT_CLASS => v_sua_upd_exists.unit_class,
X_CI_START_DT => v_sua_upd_exists.CI_START_DT,
X_CI_END_DT => v_sua_upd_exists.CI_END_DT,
X_UOO_ID => v_sua_upd_exists.uoo_id,
X_ENROLLED_DT => v_sua_upd_exists.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_upd_exists.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => v_sua_upd_exists.administrative_priority,
X_DISCONTINUED_DT => nvl(v_sua_upd_exists.discontinued_dt,trunc(SYSDATE)),
X_DCNT_REASON_CD => v_sua_upd_exists.DCNT_REASON_CD ,
X_RULE_WAIVED_DT => v_sua_upd_exists.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => v_sua_upd_exists.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => v_sua_upd_exists.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => v_sua_upd_exists.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => v_sua_upd_exists.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => v_sua_upd_exists.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => v_sua_upd_exists.ALTERNATIVE_TITLE ,
X_OVERRIDE_ENROLLED_CP => v_sua_upd_exists.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => v_sua_upd_exists.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_upd_exists.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_upd_exists.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => v_sua_upd_exists.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => v_sua_upd_exists.waitlist_dt,
X_MODE => 'R' ,
--bug#1832130 enrollment processes dld added following columns smaddali
X_GS_VERSION_NUMBER => v_sua_upd_exists.gs_version_number,
--Updating the enrollment method type
X_ENR_METHOD_TYPE => v_sua_upd_exists.enr_method_type,
X_FAILED_UNIT_RULE => v_sua_upd_exists.failed_unit_rule ,
X_CART => v_sua_upd_exists.cart ,
X_RSV_SEAT_EXT_ID => v_sua_upd_exists.rsv_seat_ext_id,
X_ORG_UNIT_CD => v_sua_upd_exists.org_unit_cd,
--session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => v_sua_upd_exists.session_id,
-- aiyer Added the column grading_schema_code as a part of the bug 2037897
X_GRADING_SCHEMA_CODE => v_sua_upd_exists.grading_schema_code,
X_DEG_AUD_DETAIL_ID => v_sua_upd_exists.deg_aud_detail_id,
X_SUBTITLE => v_sua_upd_exists.subtitle,
X_STUDENT_CAREER_TRANSCRIPT =>v_sua_upd_exists.student_career_transcript ,
X_STUDENT_CAREER_STATISTICS => v_sua_upd_exists.student_career_statistics,
X_ATTRIBUTE_CATEGORY => v_sua_upd_exists.attribute_category,
X_ATTRIBUTE1 => v_sua_upd_exists.attribute1,
X_ATTRIBUTE2 => v_sua_upd_exists.attribute2,
X_ATTRIBUTE3 => v_sua_upd_exists.attribute3,
X_ATTRIBUTE4 => v_sua_upd_exists.attribute4,
X_ATTRIBUTE5 => v_sua_upd_exists.attribute5,
X_ATTRIBUTE6 => v_sua_upd_exists.attribute6,
X_ATTRIBUTE7 => v_sua_upd_exists.attribute7,
X_ATTRIBUTE8 => v_sua_upd_exists.attribute8,
X_ATTRIBUTE9 => v_sua_upd_exists.attribute9,
X_ATTRIBUTE10 => v_sua_upd_exists.attribute10,
X_ATTRIBUTE11 => v_sua_upd_exists.attribute11,
X_ATTRIBUTE12 => v_sua_upd_exists.attribute12,
X_ATTRIBUTE13 => v_sua_upd_exists.attribute13,
X_ATTRIBUTE14 => v_sua_upd_exists.attribute14,
X_ATTRIBUTE15 => v_sua_upd_exists.attribute15,
X_ATTRIBUTE16 => v_sua_upd_exists.attribute16,
X_ATTRIBUTE17 => v_sua_upd_exists.attribute17,
X_ATTRIBUTE18 => v_sua_upd_exists.attribute18,
X_ATTRIBUTE19 => v_sua_upd_exists.attribute19,
X_ATTRIBUTE20 => v_sua_upd_exists.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_upd_exists.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109.
X_WLST_PRIORITY_WEIGHT_NUM => NULL,
X_WLST_PREFERENCE_WEIGHT_NUM => NULL,
X_CORE_INDICATOR_CODE => v_sua_upd_exists.core_indicator_code
);
v_update_uoo := FALSE;
IF v_update_uoo AND l_unit_attempt_status
NOT IN ('UNCONFIRM','DUPLICATE') THEN
enrp_copy_suar(p_destination_uoo_id);
IF v_update_uoo AND
l_unit_attempt_status in ('ENROLLED','WAITLISTED', 'DISCONTIN','COMPLETED') THEN
--copy assessment items from source to destination
igs_en_gen_010.enrp_ins_suai_trnsfr(
p_person_id => p_person_id,
p_source_course_cd => p_course_cd,
p_dest_course_cd => p_course_cd,
p_source_uoo_id => p_from_uoo_id,
p_dest_uoo_id => p_destination_uoo_id,
p_delete_source => TRUE);
p_delete_source => TRUE);
v_update_uoo := FALSE;
IF (v_update_uoo) AND l_dest_uoo_rel='SUPERIOR' THEN
l_sub_success:=NULL;l_sub_waitlist:=NULL;l_sub_failed:=NULL;
IF ((v_update_uoo) AND (p_enforce_val = 'Y')) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y') AND p_unit_attempt_status NOT IN (cst_unconfirm,cst_waitlisted)) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF (v_update_uoo) THEN
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
END IF; -- IF (v_update_uoo)
IF (v_update_uoo) THEN
-- Validate Cross Location credit points
--Igs_Ss_Enr_Details.get_notification returns NULL means that Program step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
END IF; -- IF (v_update_uoo)
IF (v_update_uoo) THEN
-- Validate Cross Mode credit points
--Igs_Ss_Enr_Details.get_notification returns NULL means that Program step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
END IF; -- IF (v_update_uoo)
IF (v_update_uoo) THEN
-- Validate Cross Faculty credit points
--Igs_Ss_Enr_Details.get_notification returns NULL means that Program step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
END IF; -- IF (v_update_uoo)
IF ((v_update_uoo) AND (p_enforce_val = 'Y') AND (p_unit_attempt_status <> cst_unconfirm)) THEN
-- getting the notification flag for evaluate COREQ unit validation
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF ((v_update_uoo) AND (p_enforce_val = 'Y') ) THEN
-- getting the notification flag for evaluate Pre-REQ unit validation
--Igs_Ss_Enr_Details.get_notification returns NULL means that unit step is not defined.
v_message_name := NULL;
v_update_uoo := FALSE;
v_update_uoo := FALSE;
IF v_update_uoo THEN
-- Log that the IGS_PS_UNIT has been updated.
IGS_GE_INS_SLE.genp_set_log_entry(
cst_enr_blk_uo,
cst_blk_uoo,
v_key,
'IGS_EN_STUD_SUCCESS_TRSF_UOO', -- Successfully transferred.
cst_information || '|' || cst_transferred);
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(
v_sle_del_exists.rowid);
IGS_GE_S_LOG_PKG.DELETE_ROW(
v_sl_del_exists.rowid);
SELECT rowid, IGS_PE_CRS_GRP_EXCL.*
FROM IGS_PE_CRS_GRP_EXCL
WHERE
person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
pen_start_dt = p_pen_start_dt AND
s_encmb_effect_type = p_effect_type AND
pee_start_dt = p_pee_start_dt AND
pee_sequence_number = p_sequence_number AND
(expiry_dt IS NULL OR
expiry_dt > p_expiry_dt)
FOR UPDATE OF expiry_dt NOWAIT;
SELECT rowid,
IGS_PE_COURSE_EXCL.*
FROM IGS_PE_COURSE_EXCL
WHERE
person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
pen_start_dt = p_pen_start_dt AND
s_encmb_effect_type = p_effect_type AND
pee_start_dt = pee_start_dt AND
pee_sequence_number = p_sequence_number AND
(expiry_dt IS NULL OR
expiry_dt > p_expiry_dt)
FOR UPDATE OF expiry_dt NOWAIT;
SELECT rowid,IGS_PE_PERS_UNT_EXCL.*
FROM IGS_PE_PERS_UNT_EXCL
WHERE
person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
pen_start_dt = p_pen_start_dt AND
s_encmb_effect_type = p_effect_type AND
pee_start_dt = pee_start_dt AND
pee_sequence_number = p_sequence_number AND
(expiry_dt IS NULL OR
expiry_dt > p_expiry_dt)
FOR UPDATE OF expiry_dt NOWAIT;
SELECT rowid,igs_pe_fund_excl.*
FROM igs_pe_fund_excl
WHERE
person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
pen_start_dt = p_pen_start_dt AND
s_encmb_effect_type = p_effect_type AND
pee_start_dt = pee_start_dt AND
pee_sequence_number = p_sequence_number AND
(expiry_dt IS NULL OR
expiry_dt > p_expiry_dt)
FOR UPDATE OF expiry_dt NOWAIT;
SELECT rowid,IGS_PE_UNT_REQUIRMNT.*
FROM IGS_PE_UNT_REQUIRMNT
WHERE
person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
pen_start_dt = p_pen_start_dt AND
s_encmb_effect_type = p_effect_type AND
pee_start_dt = pee_start_dt AND
pee_sequence_number = p_sequence_number AND
(expiry_dt IS NULL OR
expiry_dt > p_expiry_dt)
FOR UPDATE OF expiry_dt NOWAIT;
SELECT DECODE(GREATEST(v_pcge_rec.pcge_start_dt, p_expiry_dt),v_pcge_rec.pcge_start_dt, v_pcge_rec.pcge_start_dt, p_expiry_dt)
INTO l_dcd_date
FROM DUAL;
IGS_PE_CRS_GRP_EXCL_PKG.UPDATE_ROW(
X_ROWID => v_pcge_rec.ROWID ,
X_PERSON_ID => v_pcge_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_pcge_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pcge_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT ,
X_EXPIRY_DT => l_dcd_date,
X_MODE => 'R' );
SELECT DECODE(GREATEST(v_pce_rec.pce_start_dt, p_expiry_dt),v_pce_rec.pce_start_dt, v_pce_rec.pce_start_dt,p_expiry_dt)
INTO l_dcd_date
FROM DUAL;
IGS_PE_COURSE_EXCL_PKG.UPDATE_ROW(
X_ROWID => v_pce_rec.ROWID ,
X_PERSON_ID => v_pce_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_pce_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_pce_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER ,
X_COURSE_CD => v_pce_rec.COURSE_CD ,
X_PCE_START_DT => v_pce_rec.PCE_START_DT ,
X_EXPIRY_DT => l_dcd_date ,
X_MODE => 'R');
SELECT DECODE(GREATEST(v_pue_rec.pue_start_dt, p_expiry_dt),
v_pue_rec.pue_start_dt, v_pue_rec.pue_start_dt,
p_expiry_dt)
INTO l_dcd_date
FROM DUAL;
IGS_PE_PERS_UNT_EXCL_PKG.UPDATE_ROW(
X_ROWID => v_pue_rec.ROWID ,
X_PERSON_ID => v_pue_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_pue_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_pue_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER ,
X_UNIT_CD => v_pue_rec.UNIT_CD ,
X_PUE_START_DT => v_pue_rec.PUE_START_DT ,
X_EXPIRY_DT => l_dcd_date,
X_MODE => 'R'
);
SELECT DECODE(GREATEST(v_pur_rec.pur_start_dt, p_expiry_dt),v_pur_rec.pur_start_dt, v_pur_rec.pur_start_dt,p_expiry_dt)
INTO l_dcd_date
FROM DUAL;
IGS_PE_UNT_REQUIRMNT_PKG.UPDATE_ROW(
X_ROWID => v_pur_rec.ROWID ,
X_PERSON_ID => v_pur_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_pur_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_pur_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER ,
X_UNIT_CD => v_pur_rec.UNIT_CD ,
X_PUR_START_DT => v_pur_rec.PUR_START_DT ,
X_EXPIRY_DT => l_dcd_date ,
X_MODE => 'R');
SELECT DECODE(GREATEST(fund_exclusion_rec.pfe_start_dt, p_expiry_dt),fund_exclusion_rec.pfe_start_dt, fund_exclusion_rec.pfe_start_dt,p_expiry_dt)
INTO l_dcd_date
FROM DUAL;
IGS_PE_FUND_EXCL_PKG.UPDATE_ROW(
X_ROWID => fund_exclusion_rec.ROWID ,
X_FUND_EXCL_ID => fund_exclusion_rec.FUND_EXCL_ID,
X_PERSON_ID => fund_exclusion_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => fund_exclusion_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => fund_exclusion_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => fund_exclusion_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => fund_exclusion_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => fund_exclusion_rec.PEE_SEQUENCE_NUMBER ,
X_FUND_CODE => fund_exclusion_rec.FUND_CODE ,
X_PFE_START_DT => fund_exclusion_rec.PFE_START_DT ,
X_EXPIRY_DT => l_dcd_date ,
X_MODE => 'R');
|| ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
|| pkpatel 3-OCT-2002 Bug No: 2600842
|| Added the parameter x_auth_resp_id in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
|| (reverse chronological order - newest change first)
*/
BEGIN -- enrp_set_pen_expry
-- Set the expiry date for parent IGS_PE_PERS_ENCUMB record of
-- the nominated pee when expiry_dt of the effect is set
-- and no other active effects remain.
DECLARE
e_resource_busy EXCEPTION;
SELECT 'x'
FROM IGS_PE_PERSENC_EFFCT
WHERE person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
pen_start_dt = p_pen_start_dt AND
sequence_number <> p_sequence_number AND
expiry_dt IS NULL;
SELECT rowid, IGS_PE_PERS_ENCUMB.*
FROM IGS_PE_PERS_ENCUMB
WHERE person_id = p_person_id AND
encumbrance_type = p_encumbrance_type AND
start_dt = p_pen_start_dt
FOR UPDATE OF expiry_dt NOWAIT;
SELECT MAX(pee.expiry_dt)
FROM IGS_PE_PERSENC_EFFCT pee
WHERE pee.person_id = p_person_id AND
pee.encumbrance_type = p_encumbrance_type AND
pee.pen_start_dt = p_pen_start_dt AND
pee.sequence_number <> p_sequence_number;
IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW(
X_ROWID => v_pe_rec.ROWID ,
X_PERSON_ID => v_pe_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_pe_rec.ENCUMBRANCE_TYPE ,
X_START_DT => v_pe_rec.START_DT ,
X_EXPIRY_DT => v_EXPIRY_DT ,
X_AUTHORISING_PERSON_ID => v_pe_rec.AUTHORISING_PERSON_ID ,
X_COMMENTS => v_pe_rec.COMMENTS ,
X_SPO_COURSE_CD => v_pe_rec.SPO_COURSE_CD,
X_SPO_SEQUENCE_NUMBER => v_pe_rec.SPO_SEQUENCE_NUMBER,
X_AUTH_RESP_ID => v_pe_rec.auth_resp_id,
X_EXTERNAL_REFERENCE => v_pe_rec.external_reference,
X_MODE => 'R' );
SELECT acaiv.person_id,
acaiv.admission_appl_number,
acaiv.nominated_course_cd,
acaiv.sequence_number,
acaiv.adm_offer_resp_status
FROM IGS_AD_PS_APPL_INST acaiv
WHERE acaiv.person_id = p_person_id AND
acaiv.admission_appl_number = p_adm_admission_appl_number AND
acaiv.nominated_course_cd = p_adm_nominated_course_cd AND
acaiv.sequence_number = p_adm_sequence_number AND
IGS_EN_GEN_002.enrp_get_acai_offer(
acaiv.adm_outcome_status,
acaiv.adm_offer_resp_status) = 'Y' AND
IGS_EN_GEN_014.enrs_get_acai_cndtnl(
acaiv.adm_cndtnl_offer_status,
acaiv.cndtnl_offer_must_be_stsfd_ind) = 'Y'
ORDER BY
acaiv.offer_dt DESC;
SELECT acaiv.person_id,
acaiv.admission_appl_number,
acaiv.nominated_course_cd,
acaiv.sequence_number,
acaiv.adm_offer_resp_status
FROM IGS_AD_PS_APPL_INST acaiv
WHERE acaiv.person_id = p_person_id AND
acaiv.course_cd = p_course_cd AND
IGS_EN_GEN_002.enrp_get_acai_offer(
acaiv.adm_outcome_status,
acaiv.adm_offer_resp_status) = 'Y' AND
IGS_EN_GEN_014.enrs_get_acai_cndtnl(
acaiv.adm_cndtnl_offer_status,
acaiv.cndtnl_offer_must_be_stsfd_ind) = 'Y'
ORDER BY
acaiv.offer_dt DESC;
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,
acai.actual_response_dt NOWAIT;
IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW(
X_ROWID => v_acai_exists.ROWID ,
X_PERSON_ID => v_acai_exists.PERSON_ID ,
X_ADMISSION_APPL_NUMBER => v_acai_exists.ADMISSION_APPL_NUMBER ,
X_NOMINATED_COURSE_CD => v_acai_exists.NOMINATED_COURSE_CD ,
X_SEQUENCE_NUMBER => v_acai_exists.SEQUENCE_NUMBER ,
X_ADM_CAL_TYPE => v_acai_exists.ADM_CAL_TYPE ,
X_ADM_CI_SEQUENCE_NUMBER => v_acai_exists.ADM_CI_SEQUENCE_NUMBER ,
X_COURSE_CD => v_acai_exists.COURSE_CD ,
X_CRV_VERSION_NUMBER => v_acai_exists.CRV_VERSION_NUMBER ,
X_LOCATION_CD => v_acai_exists.LOCATION_CD ,
X_ATTENDANCE_MODE => v_acai_exists.ATTENDANCE_MODE ,
X_ATTENDANCE_TYPE => v_acai_exists.ATTENDANCE_TYPE ,
X_UNIT_SET_CD => v_acai_exists.UNIT_SET_CD ,
X_US_VERSION_NUMBER => v_acai_exists.US_VERSION_NUMBER ,
X_PREFERENCE_NUMBER => v_acai_exists.PREFERENCE_NUMBER ,
X_ADM_DOC_STATUS => v_acai_exists.ADM_DOC_STATUS ,
X_ADM_ENTRY_QUAL_STATUS => v_acai_exists.ADM_ENTRY_QUAL_STATUS ,
X_LATE_ADM_FEE_STATUS => v_acai_exists.LATE_ADM_FEE_STATUS ,
X_ADM_OUTCOME_STATUS => v_acai_exists.ADM_OUTCOME_STATUS ,
X_ADM_OTCM_STAT_AUTH_PER_ID => v_acai_exists.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
X_ADM_OUTCOME_STATUS_AUTH_DT => v_acai_exists.ADM_OUTCOME_STATUS_AUTH_DT ,
X_ADM_OUTCOME_STATUS_REASON => v_acai_exists.ADM_OUTCOME_STATUS_REASON ,
X_OFFER_DT => v_acai_exists.OFFER_DT ,
X_OFFER_RESPONSE_DT => v_acai_exists.OFFER_RESPONSE_DT ,
X_PRPSD_COMMENCEMENT_DT => v_acai_exists.PRPSD_COMMENCEMENT_DT ,
X_ADM_CNDTNL_OFFER_STATUS => v_acai_exists.ADM_CNDTNL_OFFER_STATUS ,
X_CNDTNL_OFFER_SATISFIED_DT => v_acai_exists.CNDTNL_OFFER_SATISFIED_DT ,
X_CNDNL_OFR_MUST_BE_STSFD_IND => v_acai_exists.CNDTNL_OFFER_MUST_BE_STSFD_IND,
X_ADM_OFFER_RESP_STATUS => v_accepted_status ,
X_ACTUAL_RESPONSE_DT => SYSDATE ,
X_ADM_OFFER_DFRMNT_STATUS => v_acai_exists.ADM_OFFER_DFRMNT_STATUS ,
X_DEFERRED_ADM_CAL_TYPE => v_acai_exists.DEFERRED_ADM_CAL_TYPE ,
X_DEFERRED_ADM_CI_SEQUENCE_NUM => v_acai_exists.DEFERRED_ADM_CI_SEQUENCE_NUM ,
X_DEFERRED_TRACKING_ID => v_acai_exists.DEFERRED_TRACKING_ID ,
X_ASS_RANK => v_acai_exists.ASS_RANK ,
X_SECONDARY_ASS_RANK => v_acai_exists.SECONDARY_ASS_RANK ,
X_INTR_ACCEPT_ADVICE_NUM => v_acai_exists.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
X_ASS_TRACKING_ID => v_acai_exists.ASS_TRACKING_ID ,
X_FEE_CAT => v_acai_exists.FEE_CAT ,
X_HECS_PAYMENT_OPTION => v_acai_exists.HECS_PAYMENT_OPTION ,
X_EXPECTED_COMPLETION_YR => v_acai_exists.EXPECTED_COMPLETION_YR ,
X_EXPECTED_COMPLETION_PERD => v_acai_exists.EXPECTED_COMPLETION_PERD ,
X_CORRESPONDENCE_CAT => v_acai_exists.CORRESPONDENCE_CAT ,
X_ENROLMENT_CAT => v_acai_exists.ENROLMENT_CAT ,
X_FUNDING_SOURCE => v_acai_exists.FUNDING_SOURCE ,
X_APPLICANT_ACPTNCE_CNDTN => v_acai_exists.APPLICANT_ACPTNCE_CNDTN ,
X_CNDTNL_OFFER_CNDTN => v_acai_exists.CNDTNL_OFFER_CNDTN ,
X_ATTRIBUTE_CATEGORY => v_acai_exists.attribute_Category,
X_ATTRIBUTE1 => v_acai_exists.ATTRIBUTE1 ,
X_ATTRIBUTE2 => v_acai_exists.ATTRIBUTE2 ,
X_ATTRIBUTE3 => v_acai_exists.ATTRIBUTE3 ,
X_ATTRIBUTE4 => v_acai_exists.ATTRIBUTE4 ,
X_ATTRIBUTE5 => v_acai_exists.ATTRIBUTE5 ,
X_ATTRIBUTE6 => v_acai_exists.ATTRIBUTE6 ,
X_ATTRIBUTE7 => v_acai_exists.ATTRIBUTE7 ,
X_ATTRIBUTE8 => v_acai_exists.ATTRIBUTE8 ,
X_ATTRIBUTE9 => v_acai_exists.ATTRIBUTE9 ,
X_ATTRIBUTE10 => v_acai_exists.ATTRIBUTE10 ,
X_ATTRIBUTE11 => v_acai_exists.ATTRIBUTE11 ,
X_ATTRIBUTE12 => v_acai_exists.ATTRIBUTE12 ,
X_ATTRIBUTE13 => v_acai_exists.ATTRIBUTE13 ,
X_ATTRIBUTE14 => v_acai_exists.ATTRIBUTE14 ,
X_ATTRIBUTE15 => v_acai_exists.ATTRIBUTE15 ,
X_ATTRIBUTE16 => v_acai_exists.ATTRIBUTE16 ,
X_ATTRIBUTE17 => v_acai_exists.ATTRIBUTE17 ,
X_ATTRIBUTE18 => v_acai_exists.ATTRIBUTE18 ,
X_ATTRIBUTE19 => v_acai_exists.ATTRIBUTE19 ,
X_ATTRIBUTE20 => v_acai_exists.ATTRIBUTE20 ,
X_ACADEMIC_INDEX => v_acai_exists.ACADEMIC_INDEX,
X_APP_FILE_LOCATION => v_acai_exists.APP_FILE_LOCATION,
X_APPLY_FOR_FINAID => v_acai_exists.APPLY_FOR_FINAID,
X_ATTENT_OTHER_INST_CD => v_acai_exists.ATTENT_OTHER_INST_CD,
X_DECISION_DATE => v_acai_exists.DECISION_DATE,
X_DECISION_MAKE_ID => v_acai_exists.DECISION_MAKE_ID,
X_DECISION_NOTES => v_acai_exists.DECISION_NOTES,
X_DECISION_REASON_ID => v_acai_exists.DECISION_REASON_ID,
X_DEFICIENCY_IN_PREP => v_acai_exists.DEFICIENCY_IN_PREP,
X_EDU_GOAL_PRIOR_ENROLL_ID => v_acai_exists.EDU_GOAL_PRIOR_ENROLL_ID,
X_FINAID_APPLY_DATE => v_acai_exists.FINAID_APPLY_DATE,
X_PENDING_REASON_ID => v_acai_exists.PENDING_REASON_ID,
X_PREDICTED_GPA => v_acai_exists.PREDICTED_GPA,
X_SPL_CONSIDER_COMMENTS => v_acai_exists.SPL_CONSIDER_COMMENTS,
X_WAITLIST_RANK => v_acai_exists.WAITLIST_RANK ,
X_WAITLIST_STATUS => v_acai_exists.WAITLIST_STATUS ,
X_APP_SOURCE_ID => v_acai_exists.APP_SOURCE_ID,
X_MODE => 'R' ,
X_SS_APPLICATION_ID => v_acai_exists.SS_APPLICATION_ID,
X_SS_PWD => v_acai_exists.SS_PWD,
X_AUTHORIZED_DT => v_acai_exists.AUTHORIZED_DT, -- BUG Enh No : 1891835 Added two columns
X_AUTHORIZING_PERS_ID => v_acai_exists.AUTHORIZING_PERS_ID, -- BUG Enh No : 1891835 Added two columns
X_ENTRY_STATUS => v_acai_exists.ENTRY_STATUS, -- BUG Enh No : 1905651 . Added three columns in teh table IGS_AD_PS_APPL_INST_ALL
X_ENTRY_LEVEL => v_acai_exists.ENTRY_LEVEL,-- BUG Enh No :1905651 Added three columns in teh table IGS_AD_PS_APPL_INST_ALL
X_SCH_APL_TO_ID => v_acai_exists.SCH_APL_TO_ID, -- BUG Enh No : 1905651 Added three columns in teh table IGS_AD_PS_APPL_INST_ALL
X_FUT_ACAD_CAL_TYPE => v_acai_exists.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
X_FUT_ACAD_CI_SEQUENCE_NUMBER => v_acai_exists.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
X_FUT_ADM_CAL_TYPE => v_acai_exists.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
X_FUT_ADM_CI_SEQUENCE_NUMBER => v_acai_exists.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
X_PREV_TERM_ADM_APPL_NUMBER => v_acai_exists.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_PREV_TERM_SEQUENCE_NUMBER => v_acai_exists.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_FUT_TERM_ADM_APPL_NUMBER => v_acai_exists.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
X_FUT_TERM_SEQUENCE_NUMBER => v_acai_exists.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
X_DEF_ACAD_CAL_TYPE => v_acai_exists.DEF_ACAD_CAL_TYPE, --Bug 2395510
X_DEF_ACAD_CI_SEQUENCE_NUM => v_acai_exists.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
X_DEF_PREV_TERM_ADM_APPL_NUM => v_acai_exists.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_PREV_APPL_SEQUENCE_NUM => v_acai_exists.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
X_DEF_TERM_ADM_APPL_NUM => v_acai_exists.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
X_DEF_APPL_SEQUENCE_NUM => v_acai_exists.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
X_IDX_CALC_DATE => v_acai_exists.IDX_CALC_DATE,
X_ATTRIBUTE21 => v_acai_exists.ATTRIBUTE21,
X_ATTRIBUTE22 => v_acai_exists.ATTRIBUTE22,
X_ATTRIBUTE23 => v_acai_exists.ATTRIBUTE23,
X_ATTRIBUTE24 => v_acai_exists.ATTRIBUTE24,
X_ATTRIBUTE25 => v_acai_exists.ATTRIBUTE25,
X_ATTRIBUTE26 => v_acai_exists.ATTRIBUTE26,
X_ATTRIBUTE27 => v_acai_exists.ATTRIBUTE27,
X_ATTRIBUTE28 => v_acai_exists.ATTRIBUTE28,
X_ATTRIBUTE29 => v_acai_exists.ATTRIBUTE29,
X_ATTRIBUTE30 => v_acai_exists.ATTRIBUTE30,
X_ATTRIBUTE31 => v_acai_exists.ATTRIBUTE31,
X_ATTRIBUTE32 => v_acai_exists.ATTRIBUTE32,
X_ATTRIBUTE33 => v_acai_exists.ATTRIBUTE33,
X_ATTRIBUTE34 => v_acai_exists.ATTRIBUTE34,
X_ATTRIBUTE35 => v_acai_exists.ATTRIBUTE35,
X_ATTRIBUTE36 => v_acai_exists.ATTRIBUTE36,
X_ATTRIBUTE37 => v_acai_exists.ATTRIBUTE37,
X_ATTRIBUTE38 => v_acai_exists.ATTRIBUTE38,
X_ATTRIBUTE39 => v_acai_exists.ATTRIBUTE39,
X_ATTRIBUTE40 => v_acai_exists.ATTRIBUTE40,
X_APPL_INST_STATUS => v_acai_exists.appl_inst_status,
x_ais_reason => v_acai_exists.ais_reason,
x_decline_ofr_reason => v_acai_exists.decline_ofr_reason
);
v_dummy IGS_PE_PERS_PREFS.LAST_UPDATED_BY%TYPE;
SELECT person_id
FROM IGS_PE_PERSON
WHERE oracle_username = cp_username;
SELECT LAST_UPDATED_BY
FROM IGS_PE_PERS_PREFS_all
WHERE person_id = cp_person_id;
IGS_PE_PERS_PREFS_PKG.INSERT_ROW(
X_ROWID => l_rowid,
x_PERSON_ID => v_person_id,
x_enr_acad_cal_type=> p_cal_type,
x_enr_acad_sequence_number=> p_sequence_number,
x_enr_enrolment_cat=> p_enrolment_cat,
x_enr_enr_method_type=> p_enr_method_type,
X_ADM_ACAD_CAL_TYPE => NULL,
X_ADM_ACAD_CI_SEQUENCE_NUMBER => NULL,
X_ADM_ADM_CAL_TYPE => NULL,
X_ADM_ADM_CI_SEQUENCE_NUMBER => NULL,
X_ADM_ADMISSION_CAT => NULL,
X_ADM_S_ADMISSION_PROCESS_TYPE => NULL,
X_ENQ_ACAD_CAL_TYPE => NULL,
X_ENQ_ACAD_CI_SEQUENCE_NUMBER => NULL,
X_ENQ_ADM_CAL_TYPE => NULL,
X_ENQ_ADM_CI_SEQUENCE_NUMBER => NULL,
X_SERVER_PRINTER_DFLT => NULL,
X_ALLOW_STND_REQ_IND => 'N',
x_org_id => l_org_id
);
SELECT rowid,
ppp.*
FROM IGS_PE_PERS_PREFS_all ppp
WHERE person_id = v_person_id;
IGS_PE_PERS_PREFS_PKG.UPDATE_ROW(
X_ROWID => v_pe_prefs_rec.ROWID ,
X_PERSON_ID => v_pe_prefs_rec.PERSON_ID ,
X_ENR_ACAD_CAL_TYPE => p_cal_type ,
X_ENR_ACAD_SEQUENCE_NUMBER => p_sequence_number ,
X_ENR_ENROLMENT_CAT => p_enrolment_cat ,
X_ENR_ENR_METHOD_TYPE => p_enr_method_type,
X_ADM_ACAD_CAL_TYPE => v_pe_prefs_rec.ADM_ACAD_CAL_TYPE ,
X_ADM_ACAD_CI_SEQUENCE_NUMBER => v_pe_prefs_rec.ADM_ACAD_CI_SEQUENCE_NUMBER ,
X_ADM_ADM_CAL_TYPE => v_pe_prefs_rec.ADM_ADM_CAL_TYPE ,
X_ADM_ADM_CI_SEQUENCE_NUMBER => v_pe_prefs_rec.ADM_ADM_CI_SEQUENCE_NUMBER ,
X_ADM_ADMISSION_CAT => v_pe_prefs_rec.ADM_ADMISSION_CAT ,
X_ADM_S_ADMISSION_PROCESS_TYPE => v_pe_prefs_rec.ADM_S_ADMISSION_PROCESS_TYPE ,
X_ENQ_ACAD_CAL_TYPE => v_pe_prefs_rec.ENQ_ACAD_CAL_TYPE ,
X_ENQ_ACAD_CI_SEQUENCE_NUMBER => v_pe_prefs_rec.ENQ_ACAD_CI_SEQUENCE_NUMBER ,
X_ENQ_ADM_CAL_TYPE => v_pe_prefs_rec.ENQ_ADM_CAL_TYPE ,
X_ENQ_ADM_CI_SEQUENCE_NUMBER => v_pe_prefs_rec.ENQ_ADM_CI_SEQUENCE_NUMBER ,
X_SERVER_PRINTER_DFLT => v_pe_prefs_rec.SERVER_PRINTER_DFLT ,
X_ALLOW_STND_REQ_IND => v_pe_prefs_rec.ALLOW_STND_REQ_IND ,
X_MODE => 'R'
);