[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROWID, IGS_PE_PERSENC_EFFCT.*
FROM IGS_PE_PERSENC_EFFCT
WHERE person_id = cp_person_id AND
encumbrance_type = cp_encumbrance_type AND
pen_start_dt = cp_start_dt AND
(expiry_dt IS NULL OR
expiry_dt > cp_expiry_dt)
FOR UPDATE OF IGS_PE_PERSENC_EFFCT.person_id NOWAIT;
SELECT PCGE.ROWID, PCGE.*
FROM IGS_PE_CRS_GRP_EXCL PCGE,
IGS_PE_PERSENC_EFFCT PEE
WHERE PCGE.person_id = cp_person_id AND
PCGE.encumbrance_type = cp_encumbrance_type AND
PCGE.pen_start_dt = cp_start_dt AND
PCGE.s_encmb_effect_type = PEE.s_encmb_effect_type AND
PCGE.pee_start_dt = PEE.pee_start_dt AND
(PCGE.expiry_dt IS NULL OR
PCGE.expiry_dt > cp_expiry_dt)
FOR UPDATE OF PCGE.person_id NOWAIT;
SELECT PCE.ROWID, PCE.*
FROM IGS_PE_COURSE_EXCL PCE,
IGS_PE_PERSENC_EFFCT PEE
WHERE PCE.person_id = cp_person_id AND
PCE.encumbrance_type = cp_encumbrance_type AND
PCE.pen_start_dt = cp_start_dt AND
PCE.s_encmb_effect_type = PEE.s_encmb_effect_type AND
PCE.pee_start_dt = PEE.pee_start_dt AND
(PCE.expiry_dt IS NULL OR
PCE.expiry_dt > cp_expiry_dt)
FOR UPDATE OF PCE.person_id NOWAIT;
SELECT PUE.ROWID,
PUE.*
FROM IGS_PE_PERS_UNT_EXCL PUE,
IGS_PE_PERSENC_EFFCT PEE
WHERE PUE.person_id = cp_person_id AND
PUE.encumbrance_type = cp_encumbrance_type AND
PUE.pen_start_dt = cp_start_dt AND
PUE.s_encmb_effect_type = PEE.s_encmb_effect_type AND
PUE.pee_start_dt = PEE.pee_start_dt AND
(PUE.expiry_dt IS NULL OR
PUE.expiry_dt > cp_expiry_dt)
FOR UPDATE OF PUE.person_id NOWAIT;
SELECT PUR.ROWID,
PUR.*
FROM IGS_PE_UNT_REQUIRMNT PUR,
IGS_PE_PERSENC_EFFCT PEE
WHERE PUR.person_id = cp_person_id AND
PUR.encumbrance_type = cp_encumbrance_type AND
PUR.pen_start_dt = cp_start_dt AND
PUR.s_encmb_effect_type = PEE.s_encmb_effect_type AND
PUR.pee_start_dt = PEE.pee_start_dt AND
(PUR.expiry_dt IS NULL OR
PUR.expiry_dt > cp_expiry_dt)
FOR UPDATE OF PUR.person_id NOWAIT;
SELECT fun.ROWID,
fun.*
FROM IGS_PE_FUND_EXCL fun,
IGS_PE_PERSENC_EFFCT PEE
WHERE fun.person_id = cp_person_id AND
fun.encumbrance_type = cp_encumbrance_type AND
fun.pen_start_dt = cp_start_dt AND
fun.s_encmb_effect_type = PEE.s_encmb_effect_type AND
fun.pee_start_dt = PEE.pee_start_dt AND
fun.person_id = pee.person_id AND
fun.encumbrance_type = pee.encumbrance_type AND
fun.pen_start_dt = pee.pee_start_dt AND
fun.pee_sequence_number = pee.sequence_number AND
(fun.expiry_dt IS NULL OR
fun.expiry_dt > cp_expiry_dt)
FOR UPDATE OF fun.person_id NOWAIT;
Igs_Pe_Persenc_Effct_Pkg.UPDATE_ROW(
X_ROWID => v_psn_encmb_eff_rec.ROWID ,
X_PERSON_ID => v_psn_encmb_eff_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_encmb_eff_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_encmb_eff_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_encmb_eff_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_encmb_eff_rec.PEE_START_DT ,
X_SEQUENCE_NUMBER => v_psn_encmb_eff_rec.SEQUENCE_NUMBER ,
X_EXPIRY_DT => v_psn_encmb_eff_rec.pee_start_dt ,
X_COURSE_CD => v_psn_encmb_eff_rec.COURSE_CD ,
X_RESTRICTED_ENROLMENT_CP => v_psn_encmb_eff_rec.RESTRICTED_ENROLMENT_CP ,
X_RESTRICTED_ATTENDANCE_TYPE => v_psn_encmb_eff_rec.RESTRICTED_ATTENDANCE_TYPE ,
X_MODE => 'R'
);
Igs_Pe_Persenc_Effct_Pkg.UPDATE_ROW(
X_ROWID => v_psn_encmb_eff_rec.ROWID ,
X_PERSON_ID => v_psn_encmb_eff_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_encmb_eff_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_encmb_eff_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_encmb_eff_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_encmb_eff_rec.PEE_START_DT ,
X_SEQUENCE_NUMBER => v_psn_encmb_eff_rec.SEQUENCE_NUMBER ,
X_EXPIRY_DT => p_expiry_dt ,
X_COURSE_CD => v_psn_encmb_eff_rec.COURSE_CD ,
X_RESTRICTED_ENROLMENT_CP => v_psn_encmb_eff_rec.RESTRICTED_ENROLMENT_CP ,
X_RESTRICTED_ATTENDANCE_TYPE => v_psn_encmb_eff_rec.RESTRICTED_ATTENDANCE_TYPE ,
X_MODE => 'R'
);
Igs_Pe_Crs_Grp_Excl_Pkg.UPDATE_ROW(
X_ROWID => v_psn_crs_grp_excl_rec.ROWID ,
X_PERSON_ID => v_psn_crs_grp_excl_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_crs_grp_excl_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_crs_grp_excl_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_crs_grp_excl_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_crs_grp_excl_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_psn_crs_grp_excl_rec.PEE_SEQUENCE_NUMBER ,
X_COURSE_GROUP_CD => v_psn_crs_grp_excl_rec.COURSE_GROUP_CD ,
X_PCGE_START_DT => v_psn_crs_grp_excl_rec.PCGE_START_DT ,
X_EXPIRY_DT => v_psn_crs_grp_excl_rec.PCGE_START_DT ,
X_MODE => 'R'
);
Igs_Pe_Crs_Grp_Excl_Pkg.UPDATE_ROW(
X_ROWID => v_psn_crs_grp_excl_rec.ROWID ,
X_PERSON_ID => v_psn_crs_grp_excl_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_crs_grp_excl_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_crs_grp_excl_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_crs_grp_excl_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_crs_grp_excl_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_psn_crs_grp_excl_rec.PEE_SEQUENCE_NUMBER ,
X_COURSE_GROUP_CD => v_psn_crs_grp_excl_rec.COURSE_GROUP_CD ,
X_PCGE_START_DT => v_psn_crs_grp_excl_rec.PCGE_START_DT ,
X_EXPIRY_DT => p_expiry_dt ,
X_MODE => 'R'
);
Igs_Pe_Course_Excl_Pkg.UPDATE_ROW(
X_ROWID => v_psn_crs_excl_rec.ROWID ,
X_PERSON_ID => v_psn_crs_excl_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_crs_excl_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_crs_excl_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_crs_excl_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_crs_excl_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_psn_crs_excl_rec.PEE_SEQUENCE_NUMBER ,
X_COURSE_CD => v_psn_crs_excl_rec.COURSE_CD ,
X_PCE_START_DT => v_psn_crs_excl_rec.PCE_START_DT ,
X_EXPIRY_DT => v_psn_crs_excl_rec.pce_start_dt ,
X_MODE => 'R'
);
Igs_Pe_Course_Excl_Pkg.UPDATE_ROW(
X_ROWID => v_psn_crs_excl_rec.ROWID ,
X_PERSON_ID => v_psn_crs_excl_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_crs_excl_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_crs_excl_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_crs_excl_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_crs_excl_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_psn_crs_excl_rec.PEE_SEQUENCE_NUMBER ,
X_COURSE_CD => v_psn_crs_excl_rec.COURSE_CD ,
X_PCE_START_DT => v_psn_crs_excl_rec.PCE_START_DT ,
X_EXPIRY_DT => p_expiry_dt,
X_MODE => 'R'
);
Igs_Pe_Pers_Unt_Excl_Pkg.UPDATE_ROW(
X_ROWID => v_psn_unit_excl_rec.ROWID ,
X_PERSON_ID => v_psn_unit_excl_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_unit_excl_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_unit_excl_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_unit_excl_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_unit_excl_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_psn_unit_excl_rec.PEE_SEQUENCE_NUMBER ,
X_UNIT_CD => v_psn_unit_excl_rec.UNIT_CD ,
X_PUE_START_DT => v_psn_unit_excl_rec.PUE_START_DT ,
X_EXPIRY_DT => v_psn_unit_excl_rec.pue_start_dt,
X_MODE => 'R'
);
Igs_Pe_Pers_Unt_Excl_Pkg.UPDATE_ROW(
X_ROWID => v_psn_unit_excl_rec.ROWID ,
X_PERSON_ID => v_psn_unit_excl_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE => v_psn_unit_excl_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT => v_psn_unit_excl_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE => v_psn_unit_excl_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT => v_psn_unit_excl_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER => v_psn_unit_excl_rec.PEE_SEQUENCE_NUMBER ,
X_UNIT_CD => v_psn_unit_excl_rec.UNIT_CD ,
X_PUE_START_DT => v_psn_unit_excl_rec.PUE_START_DT ,
X_EXPIRY_DT => p_expiry_dt,
X_MODE => 'R'
);
Igs_Pe_Unt_Requirmnt_Pkg.UPDATE_ROW(
X_ROWID =>v_psn_unit_rqmnt_rec.ROWID ,
X_PERSON_ID =>v_psn_unit_rqmnt_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE =>v_psn_unit_rqmnt_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT =>v_psn_unit_rqmnt_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE =>v_psn_unit_rqmnt_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT =>v_psn_unit_rqmnt_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER =>v_psn_unit_rqmnt_rec.PEE_SEQUENCE_NUMBER ,
X_UNIT_CD =>v_psn_unit_rqmnt_rec.UNIT_CD ,
X_PUR_START_DT =>v_psn_unit_rqmnt_rec.PUR_START_DT ,
X_EXPIRY_DT => v_psn_unit_rqmnt_rec.pur_start_dt,
X_MODE => 'R');
Igs_Pe_Unt_Requirmnt_Pkg.UPDATE_ROW(
X_ROWID =>v_psn_unit_rqmnt_rec.ROWID ,
X_PERSON_ID =>v_psn_unit_rqmnt_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE =>v_psn_unit_rqmnt_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT =>v_psn_unit_rqmnt_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE =>v_psn_unit_rqmnt_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT =>v_psn_unit_rqmnt_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER =>v_psn_unit_rqmnt_rec.PEE_SEQUENCE_NUMBER ,
X_UNIT_CD =>v_psn_unit_rqmnt_rec.UNIT_CD ,
X_PUR_START_DT =>v_psn_unit_rqmnt_rec.PUR_START_DT ,
X_EXPIRY_DT => p_expiry_dt,
X_MODE => 'R');
igs_pe_fund_excl_pkg.update_row(
X_ROWID =>fund_rec.ROWID ,
X_FUND_EXCL_ID =>fund_rec.FUND_EXCL_ID,
X_PERSON_ID =>fund_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE =>fund_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT =>fund_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE =>fund_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT =>fund_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER =>fund_rec.PEE_SEQUENCE_NUMBER ,
X_FUND_CODE =>fund_rec.FUND_CODE ,
X_PFE_START_DT =>fund_rec.PFE_START_DT ,
X_EXPIRY_DT =>fund_rec.pfe_start_dt,
X_MODE => 'R');
igs_pe_fund_excl_pkg.update_row(
X_ROWID =>fund_rec.ROWID ,
X_FUND_EXCL_ID =>fund_rec.FUND_EXCL_ID,
X_PERSON_ID =>fund_rec.PERSON_ID ,
X_ENCUMBRANCE_TYPE =>fund_rec.ENCUMBRANCE_TYPE ,
X_PEN_START_DT =>fund_rec.PEN_START_DT ,
X_S_ENCMB_EFFECT_TYPE =>fund_rec.S_ENCMB_EFFECT_TYPE ,
X_PEE_START_DT =>fund_rec.PEE_START_DT ,
X_PEE_SEQUENCE_NUMBER =>fund_rec.PEE_SEQUENCE_NUMBER ,
X_FUND_CODE =>fund_rec.FUND_CODE ,
X_PFE_START_DT =>fund_rec.PFE_START_DT ,
X_EXPIRY_DT =>p_expiry_dt,
X_MODE => 'R');
|| sarakshi 19-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.
|| smaddali 04-jul-03 Bug No: 3035523
|| Added the logic for updating the version number in hesa program attempt details belonging to this program attempt
|| (reverse chronological order - newest change first)
|| svanukur 17-feb-2004 Added logic to trap the exception IGS_RE_ATND_HIST_STRT_CRS_ATM since this should not be
|| displayed as an error as per bug 3297473
*/
v_other_detail VARCHAR2(255);
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 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;
Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
X_ROWID => c_IGS_EN_STDNT_PS_ATT_rec.ROWID,
X_PERSON_ID => c_IGS_EN_STDNT_PS_ATT_rec.PERSON_ID,
X_COURSE_CD => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => c_IGS_EN_STDNT_PS_ATT_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => c_IGS_EN_STDNT_PS_ATT_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => c_IGS_EN_STDNT_PS_ATT_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => c_IGS_EN_STDNT_PS_ATT_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => c_IGS_EN_STDNT_PS_ATT_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => c_IGS_EN_STDNT_PS_ATT_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => c_IGS_EN_STDNT_PS_ATT_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => c_IGS_EN_STDNT_PS_ATT_rec.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => p_version_number,
X_CAL_TYPE => p_cal_type,
X_LOCATION_CD => p_location_cd,
X_ATTENDANCE_MODE => p_attendance_mode,
X_ATTENDANCE_TYPE => p_attendance_type,
X_COO_ID => v_coo_id,
X_STUDENT_CONFIRMED_IND => c_IGS_EN_STDNT_PS_ATT_rec.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => c_IGS_EN_STDNT_PS_ATT_rec.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => c_IGS_EN_STDNT_PS_ATT_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => c_IGS_EN_STDNT_PS_ATT_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => c_IGS_EN_STDNT_PS_ATT_rec.DISCONTINUED_DT,
X_DISCONTINUATION_REASON_CD => c_IGS_EN_STDNT_PS_ATT_rec.DISCONTINUATION_REASON_CD,
X_LAPSED_DT => c_IGS_EN_STDNT_PS_ATT_rec.LAPSED_DT,
X_FUNDING_SOURCE => c_IGS_EN_STDNT_PS_ATT_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => c_IGS_EN_STDNT_PS_ATT_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => c_IGS_EN_STDNT_PS_ATT_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => c_IGS_EN_STDNT_PS_ATT_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => c_IGS_EN_STDNT_PS_ATT_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => c_IGS_EN_STDNT_PS_ATT_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => c_IGS_EN_STDNT_PS_ATT_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => c_IGS_EN_STDNT_PS_ATT_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => c_IGS_EN_STDNT_PS_ATT_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => c_IGS_EN_STDNT_PS_ATT_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => c_IGS_EN_STDNT_PS_ATT_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => c_IGS_EN_STDNT_PS_ATT_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => c_IGS_EN_STDNT_PS_ATT_rec.IGS_PR_CLASS_STD_ID,
-- Added next four parameters as per the Career Impact Build Bug# 2027984
x_primary_program_type => c_IGS_EN_STDNT_PS_ATT_rec.primary_program_type,
x_primary_prog_type_source => c_IGS_EN_STDNT_PS_ATT_rec.primary_prog_type_source,
x_catalog_cal_type => c_IGS_EN_STDNT_PS_ATT_rec.catalog_cal_type,
x_catalog_seq_num => c_IGS_EN_STDNT_PS_ATT_rec.catalog_seq_num,
x_key_program => c_IGS_EN_STDNT_PS_ATT_rec.key_program,
-- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
x_override_cmpl_dt => c_IGS_EN_STDNT_PS_ATT_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => c_IGS_EN_STDNT_PS_ATT_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as part of bug # 3544927
X_ATTRIBUTE_CATEGORY => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => c_IGS_EN_STDNT_PS_ATT_rec.FUTURE_DATED_TRANS_FLAG);
IGS_HE_ST_SPA_ALL_PKG.update_row (
x_rowid => c_upd_spa_rec.rowid ,
x_hesa_st_spa_id => c_upd_spa_rec.hesa_st_spa_id ,
x_org_id => c_upd_spa_rec.org_id ,
x_person_id => c_upd_spa_rec.person_id ,
x_course_cd => c_upd_spa_rec.course_cd ,
x_version_number => p_version_number , -- updated
x_fe_student_marker => c_upd_spa_rec.fe_student_marker ,
x_domicile_cd => c_upd_spa_rec.domicile_cd ,
x_inst_last_attended => c_upd_spa_rec.inst_last_attended ,
x_year_left_last_inst => c_upd_spa_rec.year_left_last_inst ,
x_highest_qual_on_entry => c_upd_spa_rec.highest_qual_on_entry ,
x_date_qual_on_entry_calc => c_upd_spa_rec.date_qual_on_entry_calc ,
x_a_level_point_score => c_upd_spa_rec.a_level_point_score ,
x_highers_points_scores => c_upd_spa_rec.highers_points_scores ,
x_occupation_code => c_upd_spa_rec.occupation_code ,
x_commencement_dt => c_upd_spa_rec.commencement_dt ,
x_special_student => c_upd_spa_rec.special_student ,
x_student_qual_aim => c_upd_spa_rec.student_qual_aim ,
x_student_fe_qual_aim => c_upd_spa_rec.student_fe_qual_aim ,
x_teacher_train_prog_id => c_upd_spa_rec.teacher_train_prog_id ,
x_itt_phase => c_upd_spa_rec.itt_phase ,
x_bilingual_itt_marker => c_upd_spa_rec.bilingual_itt_marker ,
x_teaching_qual_gain_sector => c_upd_spa_rec.teaching_qual_gain_sector ,
x_teaching_qual_gain_subj1 => c_upd_spa_rec.teaching_qual_gain_subj1 ,
x_teaching_qual_gain_subj2 => c_upd_spa_rec.teaching_qual_gain_subj2 ,
x_teaching_qual_gain_subj3 => c_upd_spa_rec.teaching_qual_gain_subj3 ,
x_student_inst_number => c_upd_spa_rec.student_inst_number ,
x_destination => c_upd_spa_rec.destination ,
x_itt_prog_outcome => c_upd_spa_rec.itt_prog_outcome ,
x_hesa_return_name => c_upd_spa_rec.hesa_return_name ,
x_hesa_return_id => c_upd_spa_rec.hesa_return_id ,
x_hesa_submission_name => c_upd_spa_rec.hesa_submission_name ,
x_associate_ucas_number => c_upd_spa_rec.associate_ucas_number ,
x_associate_scott_cand => c_upd_spa_rec.associate_scott_cand ,
x_associate_teach_ref_num => c_upd_spa_rec.associate_teach_ref_num ,
x_associate_nhs_reg_num => c_upd_spa_rec.associate_nhs_reg_num ,
x_nhs_funding_source => c_upd_spa_rec.nhs_funding_source ,
x_ufi_place => c_upd_spa_rec.ufi_place ,
x_postcode => c_upd_spa_rec.postcode ,
x_social_class_ind => c_upd_spa_rec.social_class_ind ,
x_occcode => c_upd_spa_rec.occcode ,
x_total_ucas_tariff => c_upd_spa_rec.total_ucas_tariff ,
x_nhs_employer => c_upd_spa_rec.nhs_employer ,
x_return_type => c_upd_spa_rec.return_type ,
x_qual_aim_subj1 => c_upd_spa_rec.qual_aim_subj1 ,
x_qual_aim_subj2 => c_upd_spa_rec.qual_aim_subj2 ,
x_qual_aim_subj3 => c_upd_spa_rec.qual_aim_subj3 ,
x_qual_aim_proportion => c_upd_spa_rec.qual_aim_proportion ,
x_mode => 'R' ,
x_dependants_cd => c_upd_spa_rec.dependants_cd ,
x_implied_fund_rate => c_upd_spa_rec.implied_fund_rate ,
x_gov_initiatives_cd => c_upd_spa_rec.gov_initiatives_cd ,
x_units_for_qual => c_upd_spa_rec.units_for_qual ,
x_disadv_uplift_elig_cd => c_upd_spa_rec.disadv_uplift_elig_cd ,
x_franch_partner_cd => c_upd_spa_rec.franch_partner_cd ,
x_units_completed => c_upd_spa_rec.units_completed ,
x_franch_out_arr_cd => c_upd_spa_rec.franch_out_arr_cd ,
x_employer_role_cd => c_upd_spa_rec.employer_role_cd ,
x_disadv_uplift_factor => c_upd_spa_rec.disadv_uplift_factor ,
x_enh_fund_elig_cd => c_upd_spa_rec.enh_fund_elig_cd
) ;
SELECT load_cal_type, load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = p_teach_cal_type
AND teach_ci_sequence_number = p_teach_ci_sequence_number
ORDER BY LOAD_START_DT ASC;
SELECT meaning
FROM IGS_LOOKUPS_VIEW
WHERE lookup_type = 'CRS_ATTEMPT_STATUS'
AND lookup_CODE = 'DISCONTIN';
|| sarakshi 19-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.
|| kkillams 21-03-2003 Added new parameter p_transf_course_cd to the function.
|| Which will distigush the from where this function was called.
|| Value will be passed if function is invoked from program transfer
|| else null value wil come w.r.t bug 2863707
|| kkillams 28-04-2003 Modified c_suao_check,c_sua_drop and c_igs_en_su_attempt cursors in this function
|| due to change in pk of student unit attempt w.r.t. bug number 2829262
|| rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
|| and IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW, added as part of Prevent Dropping Core Units. Enh Bug# 3052432
|| ptandon 05-DEC-2003 Modified the cursor c_sua to include the order by clause to select the subordinate units first
|| for discontinuation. Placements build. Bug# 3052438.
|| vkarthik 19-Apr-2004 Modified exception handling section to handle no_ausl_record_found
|| for bug 3526251
|| amuthu 23-Dec-2004 Corrected the logic for setting the program as non-key based on the parameter rather than the sct record
*/
RETURN BOOLEAN AS
BEGIN
DECLARE
v_discontinuation_reason_cd IGS_EN_STDNT_PS_ATT.discontinuation_reason_cd%TYPE;
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.ci_start_dt,
sua.enrolled_dt,
sua.unit_attempt_status,
sua.uoo_id
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.unit_attempt_status IN (cst_enrolled,cst_waitlisted)
ORDER BY sup_unit_cd ASC
FOR UPDATE NOWAIT;
SELECT suao.person_id
FROM IGS_AS_SU_STMPTOUT suao
WHERE suao.person_id = cp_person_id AND
suao.course_cd = cp_course_cd AND
suao.uoo_id = cp_uoo_id;
SELECT sca.course_attempt_status, primary_program_type
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd;
SELECT 'X'
FROM igs_en_stdnt_ps_att spa, igs_ps_ver pv1, igs_ps_ver pv2
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_transf_course_cd -- dest course cd
AND pv1.course_cd = spa.course_cd
AND pv1.version_number = spa.version_number
AND pv2.course_cd = p_course_cd
AND pv2.version_number = p_version_number
AND pv1.course_type = pv2.course_type;
SELECT DISCONTINUATION_REASON_CD
FROM IGS_EN_DCNT_REASONCD
WHERE S_DISCONTINUATION_REASON_TYPE = 'UNIT_TRANS'
AND DCNT_UNIT_IND = 'Y'
AND SYS_DFLT_IND = 'Y'
AND CLOSED_IND = 'N';
SELECT discontinuation_reason_cd
FROM igs_en_dcnt_reasoncd
WHERE NVL(closed_ind,'N') ='N'
AND dflt_ind ='Y'
AND dcnt_unit_ind ='Y'
AND s_discontinuation_reason_type IS NULL;
SELECT sua.ROWID, sua.*
FROM IGS_EN_SU_ATTEMPT sua
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
uoo_id = v_sua.uoo_id;
SELECT 1
FROM IGS_EN_SU_ATTEMPT sua
WHERE person_id = p_person_id
AND course_cd = p_transf_course_cd
AND uoo_id = cp_uoo_id
AND unit_Attempt_status <> 'DROPPED';
igs_en_sua_api.update_unit_attempt(
X_ROWID => sua_drop_rec.ROWID,
X_PERSON_ID => sua_drop_rec.PERSON_ID,
X_COURSE_CD => sua_drop_rec.COURSE_CD ,
X_UNIT_CD => sua_drop_rec.UNIT_CD,
X_CAL_TYPE => sua_drop_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => sua_drop_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => sua_drop_rec.VERSION_NUMBER ,
X_LOCATION_CD => sua_drop_rec.LOCATION_CD,
X_UNIT_CLASS => sua_drop_rec.UNIT_CLASS ,
X_CI_START_DT => sua_drop_rec.CI_START_DT,
X_CI_END_DT => sua_drop_rec.CI_END_DT,
X_UOO_ID => sua_drop_rec.UOO_ID ,
X_ENROLLED_DT => sua_drop_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_dropped, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => sua_drop_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => sua_drop_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => nvl(sua_drop_rec.discontinued_dt,trunc(SYSDATE)),
X_DCNT_REASON_CD => l_dflt_disc_code,
X_RULE_WAIVED_DT => sua_drop_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => sua_drop_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => sua_drop_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => sua_drop_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => sua_drop_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => sua_drop_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => sua_drop_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => sua_drop_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => sua_drop_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => sua_drop_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => sua_drop_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => sua_drop_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => sua_drop_rec.waitlist_dt,
X_MODE => 'R',
-- Added 5 columns as part of Enroll Process build - amuthu
X_GS_VERSION_NUMBER => sua_drop_rec.gs_version_number,
X_ENR_METHOD_TYPE => sua_drop_rec.enr_method_type,
X_FAILED_UNIT_RULE => sua_drop_rec.FAILED_UNIT_RULE,
X_CART => sua_drop_rec.CART,
X_RSV_SEAT_EXT_ID => sua_drop_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => sua_drop_rec.org_unit_cd ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => sua_drop_rec.session_id,
-- Added the column grading schema as a part of the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => sua_drop_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => sua_drop_rec.deg_aud_detail_id,
X_SUBTITLE => sua_drop_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => sua_drop_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => sua_drop_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => sua_drop_rec.attribute_category,
X_ATTRIBUTE1 => sua_drop_rec.attribute1,
X_ATTRIBUTE2 => sua_drop_rec.attribute2,
X_ATTRIBUTE3 => sua_drop_rec.attribute3,
X_ATTRIBUTE4 => sua_drop_rec.attribute4,
X_ATTRIBUTE5 => sua_drop_rec.attribute5,
X_ATTRIBUTE6 => sua_drop_rec.attribute6,
X_ATTRIBUTE7 => sua_drop_rec.attribute7,
X_ATTRIBUTE8 => sua_drop_rec.attribute8,
X_ATTRIBUTE9 => sua_drop_rec.attribute9,
X_ATTRIBUTE10 => sua_drop_rec.attribute10,
X_ATTRIBUTE11 => sua_drop_rec.attribute11,
X_ATTRIBUTE12 => sua_drop_rec.attribute12,
X_ATTRIBUTE13 => sua_drop_rec.attribute13,
X_ATTRIBUTE14 => sua_drop_rec.attribute14,
X_ATTRIBUTE15 => sua_drop_rec.attribute15,
X_ATTRIBUTE16 => sua_drop_rec.attribute16,
X_ATTRIBUTE17 => sua_drop_rec.attribute17,
X_ATTRIBUTE18 => sua_drop_rec.attribute18,
X_ATTRIBUTE19 => sua_drop_rec.attribute19,
X_ATTRIBUTE20 => sua_drop_rec.attribute20,
X_WAITLIST_MANUAL_IND => sua_drop_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => sua_drop_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => sua_drop_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => sua_drop_rec.core_indicator_code
);
IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW(
X_ROWID => sua_drop_rec.ROWID,
X_PERSON_ID => sua_drop_rec.PERSON_ID,
X_COURSE_CD => sua_drop_rec.COURSE_CD ,
X_UNIT_CD => sua_drop_rec.UNIT_CD,
X_CAL_TYPE => sua_drop_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => sua_drop_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => sua_drop_rec.VERSION_NUMBER ,
X_LOCATION_CD => sua_drop_rec.LOCATION_CD,
X_UNIT_CLASS => sua_drop_rec.UNIT_CLASS ,
X_CI_START_DT => sua_drop_rec.CI_START_DT,
X_CI_END_DT => sua_drop_rec.CI_END_DT,
X_UOO_ID => sua_drop_rec.UOO_ID ,
X_ENROLLED_DT => sua_drop_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_dropped, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => sua_drop_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => sua_drop_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => nvl(sua_drop_rec.discontinued_dt,SYSDATE),
X_DCNT_REASON_CD => l_unt_disc_code,
X_RULE_WAIVED_DT => sua_drop_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => sua_drop_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => sua_drop_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => sua_drop_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => sua_drop_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => sua_drop_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => sua_drop_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => sua_drop_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => sua_drop_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => sua_drop_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => sua_drop_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => sua_drop_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => sua_drop_rec.waitlist_dt,
X_MODE => 'R',
-- Added 5 columns as part of Enroll Process build - amuthu
X_GS_VERSION_NUMBER => sua_drop_rec.gs_version_number,
X_ENR_METHOD_TYPE => sua_drop_rec.enr_method_type,
X_FAILED_UNIT_RULE => sua_drop_rec.FAILED_UNIT_RULE,
X_CART => sua_drop_rec.CART,
X_RSV_SEAT_EXT_ID => sua_drop_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => sua_drop_rec.org_unit_cd ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => sua_drop_rec.session_id,
-- Added the column grading schema as a part of the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => sua_drop_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => sua_drop_rec.deg_aud_detail_id,
X_SUBTITLE => sua_drop_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => sua_drop_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => sua_drop_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => sua_drop_rec.attribute_category,
X_ATTRIBUTE1 => sua_drop_rec.attribute1,
X_ATTRIBUTE2 => sua_drop_rec.attribute2,
X_ATTRIBUTE3 => sua_drop_rec.attribute3,
X_ATTRIBUTE4 => sua_drop_rec.attribute4,
X_ATTRIBUTE5 => sua_drop_rec.attribute5,
X_ATTRIBUTE6 => sua_drop_rec.attribute6,
X_ATTRIBUTE7 => sua_drop_rec.attribute7,
X_ATTRIBUTE8 => sua_drop_rec.attribute8,
X_ATTRIBUTE9 => sua_drop_rec.attribute9,
X_ATTRIBUTE10 => sua_drop_rec.attribute10,
X_ATTRIBUTE11 => sua_drop_rec.attribute11,
X_ATTRIBUTE12 => sua_drop_rec.attribute12,
X_ATTRIBUTE13 => sua_drop_rec.attribute13,
X_ATTRIBUTE14 => sua_drop_rec.attribute14,
X_ATTRIBUTE15 => sua_drop_rec.attribute15,
X_ATTRIBUTE16 => sua_drop_rec.attribute16,
X_ATTRIBUTE17 => sua_drop_rec.attribute17,
X_ATTRIBUTE18 => sua_drop_rec.attribute18,
X_ATTRIBUTE19 => sua_drop_rec.attribute19,
X_ATTRIBUTE20 => sua_drop_rec.attribute20,
X_WAITLIST_MANUAL_IND => sua_drop_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => sua_drop_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => sua_drop_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => sua_drop_rec.core_indicator_code,
X_UPD_AUDIT_FLAG => sua_drop_rec.upd_audit_flag,
X_SS_SOURCE_IND => sua_drop_rec.ss_source_ind
);
SELECT ROWID, IGS_EN_SU_ATTEMPT.*
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
uoo_id = v_sua.uoo_id;
igs_en_sua_api.update_unit_attempt(
X_ROWID => c_IGS_EN_SU_ATTEMPT_rec.ROWID,
X_PERSON_ID => c_IGS_EN_SU_ATTEMPT_rec.PERSON_ID,
X_COURSE_CD => c_IGS_EN_SU_ATTEMPT_rec.COURSE_CD ,
X_UNIT_CD => c_IGS_EN_SU_ATTEMPT_rec.UNIT_CD,
X_CAL_TYPE => c_IGS_EN_SU_ATTEMPT_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.VERSION_NUMBER ,
X_LOCATION_CD => c_IGS_EN_SU_ATTEMPT_rec.LOCATION_CD,
X_UNIT_CLASS => c_IGS_EN_SU_ATTEMPT_rec.UNIT_CLASS ,
X_CI_START_DT => c_IGS_EN_SU_ATTEMPT_rec.CI_START_DT,
X_CI_END_DT => c_IGS_EN_SU_ATTEMPT_rec.CI_END_DT,
X_UOO_ID => c_IGS_EN_SU_ATTEMPT_rec.UOO_ID ,
X_ENROLLED_DT => c_IGS_EN_SU_ATTEMPT_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_discontinue, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => v_administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => c_IGS_EN_SU_ATTEMPT_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => p_discontinued_dt,
X_DCNT_REASON_CD => NULL, -- unable to insert value in to this field
X_RULE_WAIVED_DT => c_IGS_EN_SU_ATTEMPT_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => c_IGS_EN_SU_ATTEMPT_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => c_IGS_EN_SU_ATTEMPT_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => c_IGS_EN_SU_ATTEMPT_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => c_IGS_EN_SU_ATTEMPT_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => c_IGS_EN_SU_ATTEMPT_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => c_IGS_EN_SU_ATTEMPT_rec.waitlist_dt,
X_MODE => 'R',
-- Added 5 cols as part of Enrollement Process Build -- amuthu
X_GS_VERSION_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.gs_version_number,
X_ENR_METHOD_TYPE => c_IGS_EN_SU_ATTEMPT_rec.enr_method_type,
X_FAILED_UNIT_RULE => c_IGS_EN_SU_ATTEMPT_rec.FAILED_UNIT_RULE,
X_CART => c_IGS_EN_SU_ATTEMPT_rec.cart,
X_RSV_SEAT_EXT_ID => c_IGS_EN_SU_ATTEMPT_rec.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => c_IGS_EN_SU_ATTEMPT_rec.ORG_UNIT_CD,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => c_IGS_EN_SU_ATTEMPT_rec.SESSION_ID,
-- Added the column grading schema as a part of the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => c_IGS_EN_SU_ATTEMPT_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => c_IGS_EN_SU_ATTEMPT_rec.deg_aud_detail_id,
X_SUBTITLE => c_IGS_EN_SU_ATTEMPT_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => c_IGS_EN_SU_ATTEMPT_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => c_IGS_EN_SU_ATTEMPT_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => c_IGS_EN_SU_ATTEMPT_rec.attribute_category,
X_ATTRIBUTE1 => c_IGS_EN_SU_ATTEMPT_rec.attribute1,
X_ATTRIBUTE2 => c_IGS_EN_SU_ATTEMPT_rec.attribute2,
X_ATTRIBUTE3 => c_IGS_EN_SU_ATTEMPT_rec.attribute3,
X_ATTRIBUTE4 => c_IGS_EN_SU_ATTEMPT_rec.attribute4,
X_ATTRIBUTE5 => c_IGS_EN_SU_ATTEMPT_rec.attribute5,
X_ATTRIBUTE6 => c_IGS_EN_SU_ATTEMPT_rec.attribute6,
X_ATTRIBUTE7 => c_IGS_EN_SU_ATTEMPT_rec.attribute7,
X_ATTRIBUTE8 => c_IGS_EN_SU_ATTEMPT_rec.attribute8,
X_ATTRIBUTE9 => c_IGS_EN_SU_ATTEMPT_rec.attribute9,
X_ATTRIBUTE10 => c_IGS_EN_SU_ATTEMPT_rec.attribute10,
X_ATTRIBUTE11 => c_IGS_EN_SU_ATTEMPT_rec.attribute11,
X_ATTRIBUTE12 => c_IGS_EN_SU_ATTEMPT_rec.attribute12,
X_ATTRIBUTE13 => c_IGS_EN_SU_ATTEMPT_rec.attribute13,
X_ATTRIBUTE14 => c_IGS_EN_SU_ATTEMPT_rec.attribute14,
X_ATTRIBUTE15 => c_IGS_EN_SU_ATTEMPT_rec.attribute15,
X_ATTRIBUTE16 => c_IGS_EN_SU_ATTEMPT_rec.attribute16,
X_ATTRIBUTE17 => c_IGS_EN_SU_ATTEMPT_rec.attribute17,
X_ATTRIBUTE18 => c_IGS_EN_SU_ATTEMPT_rec.attribute18,
X_ATTRIBUTE19 => c_IGS_EN_SU_ATTEMPT_rec.attribute19,
X_ATTRIBUTE20 => c_IGS_EN_SU_ATTEMPT_rec.attribute20,
X_WAITLIST_MANUAL_IND => c_IGS_EN_SU_ATTEMPT_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => c_IGS_EN_SU_ATTEMPT_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => c_IGS_EN_SU_ATTEMPT_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => c_IGS_EN_SU_ATTEMPT_rec.core_indicator_code
);
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;
Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
X_ROWID => c_si_en_stnd_ps_att_rec.ROWID,
X_PERSON_ID => c_si_en_stnd_ps_att_rec.PERSON_ID,
X_COURSE_CD => c_si_en_stnd_ps_att_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => c_si_en_stnd_ps_att_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => c_si_en_stnd_ps_att_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => c_si_en_stnd_ps_att_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => c_si_en_stnd_ps_att_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => c_si_en_stnd_ps_att_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => c_si_en_stnd_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => c_si_en_stnd_ps_att_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => c_si_en_stnd_ps_att_rec.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => c_si_en_stnd_ps_att_rec.version_number,
X_CAL_TYPE => c_si_en_stnd_ps_att_rec.cal_type,
X_LOCATION_CD => c_si_en_stnd_ps_att_rec.location_cd,
X_ATTENDANCE_MODE => c_si_en_stnd_ps_att_rec.attendance_mode,
X_ATTENDANCE_TYPE => c_si_en_stnd_ps_att_rec.attendance_type,
X_COO_ID => c_si_en_stnd_ps_att_rec.coo_id,
X_STUDENT_CONFIRMED_IND => c_si_en_stnd_ps_att_rec.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => c_si_en_stnd_ps_att_rec.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => cst_discontinue, --c_si_en_stnd_ps_att_rec.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => c_si_en_stnd_ps_att_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => c_si_en_stnd_ps_att_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => c_si_en_stnd_ps_att_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => c_si_en_stnd_ps_att_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => p_discontinued_dt,
X_DISCONTINUATION_REASON_CD => p_discontinuation_reason_cd,
X_LAPSED_DT => c_si_en_stnd_ps_att_rec.LAPSED_DT,
X_FUNDING_SOURCE => c_si_en_stnd_ps_att_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => c_si_en_stnd_ps_att_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => c_si_en_stnd_ps_att_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => c_si_en_stnd_ps_att_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => c_si_en_stnd_ps_att_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => c_si_en_stnd_ps_att_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => c_si_en_stnd_ps_att_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => c_si_en_stnd_ps_att_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => c_si_en_stnd_ps_att_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => c_si_en_stnd_ps_att_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => c_si_en_stnd_ps_att_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => c_si_en_stnd_ps_att_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => c_si_en_stnd_ps_att_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => c_si_en_stnd_ps_att_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => c_si_en_stnd_ps_att_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => c_si_en_stnd_ps_att_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => c_si_en_stnd_ps_att_rec.IGS_PR_CLASS_STD_ID,
-- Added next four parameters as per the Career Impact Build Bug# 2027984
x_primary_program_type => c_si_en_stnd_ps_att_rec.primary_program_type,
x_primary_prog_type_source => c_si_en_stnd_ps_att_rec.primary_prog_type_source,
x_catalog_cal_type => c_si_en_stnd_ps_att_rec.catalog_cal_type,
x_catalog_seq_num => c_si_en_stnd_ps_att_rec.catalog_seq_num,
x_key_program => c_si_en_stnd_ps_att_rec.key_program,
-- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
x_override_cmpl_dt => c_si_en_stnd_ps_att_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => c_si_en_stnd_ps_att_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as a part of bug # 3544927
X_ATTRIBUTE_CATEGORY => c_si_en_stnd_ps_att_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_si_en_stnd_ps_att_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_si_en_stnd_ps_att_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_si_en_stnd_ps_att_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_si_en_stnd_ps_att_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_si_en_stnd_ps_att_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_si_en_stnd_ps_att_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_si_en_stnd_ps_att_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_si_en_stnd_ps_att_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_si_en_stnd_ps_att_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_si_en_stnd_ps_att_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_si_en_stnd_ps_att_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_si_en_stnd_ps_att_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_si_en_stnd_ps_att_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_si_en_stnd_ps_att_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_si_en_stnd_ps_att_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => c_si_en_stnd_ps_att_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => c_si_en_stnd_ps_att_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => c_si_en_stnd_ps_att_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => c_si_en_stnd_ps_att_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => c_si_en_stnd_ps_att_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => c_si_en_stnd_ps_att_rec.FUTURE_DATED_TRANS_FLAG
);
sarakshi 19-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.
*****************************************************************************/
-- enrp_upd_sca_lapse
-- Update students who's enrolment has lapsed and set the lapse date in
-- their IGS_PS_COURSE attempt, which in turn will set their IGS_PS_COURSE attempt status
-- to lapsed.
-- IGS_GE_NOTE: This job is run from the report ENRR05E0 which handles the
-- parameter processing and running the report subsequent to this job
-- committing the log entry records.
DECLARE
cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
SELECT secc.lapse_dt_alias
FROM IGS_EN_CAL_CONF secc
WHERE secc.s_control_num = 1;
SELECT ci.start_dt,
ci.end_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_acad_cal_type AND
ci.sequence_number = p_acad_sequence_number;
SELECT daiv.absolute_val alias_val
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.cal_type = p_cal_type AND
daiv.ci_sequence_number = p_seq_num AND
daiv.dt_alias = v_lapse_dt_alias
ORDER BY daiv.absolute_val; -- first row found is the earliest date
SELECT sca.person_id,
sca.course_cd
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE sca.cal_type = p_acad_cal_type AND
sca.course_cd LIKE p_course_cd AND
sca.course_attempt_status = cst_inactive AND
crv.course_cd = sca.course_cd AND
crv.version_number = sca.version_number AND
(crv.responsible_org_unit_cd LIKE p_org_unit_cd OR
EXISTS (
SELECT 'X'
FROM IGS_OR_INST_ORG_BASE_V ou,
IGS_OR_STATUS os
WHERE ou.PARTY_NUMBER LIKE p_org_unit_cd AND
os.org_status = ou.org_status AND
os.s_org_status = cst_active AND
Igs_Or_Gen_001.orgp_get_within_ou(ou.PARTY_NUMBER,
ou.start_dt,
crv.responsible_org_unit_cd,
crv.responsible_ou_start_dt,
'N')
= 'Y')) AND
(p_enrolment_cat = '%' OR
EXISTS (
SELECT 'X'
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE scae.person_id = sca.person_id AND
scae.course_cd = sca.course_cd AND
Igs_En_Gen_014.enrs_get_within_ci(p_acad_cal_type,
p_acad_sequence_number,
scae.cal_type,
scae.ci_sequence_number,
'Y')
= 'Y' AND
scae.enrolment_cat LIKE p_enrolment_cat));
SELECT scae.person_id,
scae.course_cd,
scae.cal_type,
scae.ci_sequence_number,
scae.enr_form_due_dt
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci
WHERE scae.person_id = cp_person_id AND
scae.course_cd = cp_course_cd AND
ci.cal_type = scae.cal_type AND
ci.sequence_number = scae.ci_sequence_number AND
ci.end_dt > SYSDATE
ORDER BY ci.start_dt DESC; -- for latest date
SELECT ci.alternate_code,
sua.cal_type,
sua.ci_sequence_number
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST ci
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.unit_attempt_status IN (cst_enrolled,cst_discontin,cst_completed) AND
sua.ci_start_dt <= TRUNC(SYSDATE) AND
ci.cal_type = sua.cal_type AND
ci.sequence_number = sua.ci_sequence_number
ORDER BY ci_start_dt DESC;
SELECT ROWID,sca.*
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd
FOR UPDATE NOWAIT;
SELECT start_dt, end_dt FROM IGS_CA_INST_ALL
WHERE cal_type = cp_cal_type AND
sequence_number = cp_ci_sequence_number;
Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
X_ROWID => v_sca_exists.ROWID,
X_PERSON_ID => v_sca_exists.PERSON_ID,
X_COURSE_CD => v_sca_exists.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_exists.ADVANCED_STANDING_IND,
X_FEE_CAT => v_sca_exists.FEE_CAT,
X_CORRESPONDENCE_CAT => v_sca_exists.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => v_sca_exists.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_exists.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_sca_exists.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => v_sca_exists.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => v_sca_exists.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => v_sca_exists.version_number,
X_CAL_TYPE => v_sca_exists.cal_type,
X_LOCATION_CD => v_sca_exists.location_cd,
X_ATTENDANCE_MODE => v_sca_exists.attendance_mode,
X_ATTENDANCE_TYPE => v_sca_exists.attendance_type,
X_COO_ID => v_sca_exists.coo_id,
X_STUDENT_CONFIRMED_IND => v_sca_exists.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => v_sca_exists.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS =>v_sca_exists.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => v_sca_exists.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_exists.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_exists.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_sca_exists.PROVISIONAL_IND,
X_DISCONTINUED_DT => v_sca_exists.discontinued_dt,
X_DISCONTINUATION_REASON_CD => v_sca_exists.discontinuation_reason_cd,
X_LAPSED_DT => TRUNC(SYSDATE),
X_FUNDING_SOURCE => v_sca_exists.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => v_sca_exists.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_exists.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_exists.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_exists.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => v_sca_exists.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => v_sca_exists.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_exists.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_exists.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_exists.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_exists.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_exists.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_exists.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_exists.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => v_sca_exists.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => v_sca_exists.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => v_sca_exists.IGS_PR_CLASS_STD_ID,
-- Added next four parameters as per the Career Impact Build Bug# 2027984
x_primary_program_type => v_sca_exists.primary_program_type,
x_primary_prog_type_source => v_sca_exists.primary_prog_type_source,
x_catalog_cal_type => v_sca_exists.catalog_cal_type,
x_catalog_seq_num => v_sca_exists.catalog_seq_num,
x_key_program => v_sca_exists.key_program,
-- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
x_override_cmpl_dt => v_sca_exists.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_sca_exists.manual_ovr_cmpl_dt_ind,
-- added by ckasu as part of bug # 3544927
X_ATTRIBUTE_CATEGORY => v_sca_exists.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sca_exists.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_exists.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_exists.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_exists.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_exists.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_exists.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_exists.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_exists.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_exists.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_exists.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_exists.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_exists.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_exists.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_exists.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_exists.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_exists.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_exists.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_exists.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_exists.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sca_exists.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_exists.FUTURE_DATED_TRANS_FLAG
);
SELECT 'X'
FROM igs_pe_typ_instances_all PTI,
IGS_PE_PERSON_TYPES PPT
WHERE PPT.system_type = 'STUDENT' AND
ppt.person_type_code = pti.person_type_code AND
pti.COURSE_CD = v_sca_exists.COURSE_CD AND
pti.PERSON_ID = v_sca_exists.PERSON_ID;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'FORMER_STUDENT';
SELECT ROWID, ti.*
FROM igs_pe_typ_instances_all ti
WHERE course_cd = v_sca_exists.COURSE_CD AND
person_id = v_sca_exists.PERSON_ID;
Igs_Pe_Typ_Instances_Pkg.update_row(
-- X_ROWID =>l_rowid, -- Old
X_ROWID =>c2_rec.ROWID, -- New due to bug no#1516658
X_PERSON_ID =>c2_rec.PERSON_ID,
X_COURSE_CD =>c2_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>c2_rec.TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE =>c2_rec.PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER =>c2_rec.CC_VERSION_NUMBER,
X_FUNNEL_STATUS =>c2_rec.FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER =>c2_rec.ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD =>c2_rec.NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c2_rec.NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER =>c2_rec.SEQUENCE_NUMBER,
X_START_DATE =>c2_rec.START_DATE,
X_END_DATE => SYSDATE,
X_CREATE_METHOD =>c2_rec.CREATE_METHOD,
X_ENDED_BY =>c2_rec.ENDED_BY,
X_END_METHOD =>'PRG_ATTMPT_ST_INACTIVE',
X_MODE =>'R',
X_EMPLMNT_CATEGORY_CODE => c2_rec.emplmnt_category_code);
Igs_Pe_Typ_Instances_Pkg.insert_row(
X_ROWID =>l_rowid,
X_PERSON_ID => v_sca_exists.PERSON_ID,
X_COURSE_CD =>v_sca_exists.COURSE_CD,
X_TYPE_INSTANCE_ID =>l_pk,
X_PERSON_TYPE_CODE =>l_person_type_code,
X_CC_VERSION_NUMBER =>NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER=>
v_sca_exists.ADM_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD =>
v_sca_exists.ADM_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>v_sca_exists.VERSION_NUMBER,
X_SEQUENCE_NUMBER =>NULL,
X_START_DATE => SYSDATE,
X_END_DATE =>NULL,
X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
X_ENDED_BY =>NULL,
X_END_METHOD =>NULL,
X_MODE =>'R',
x_org_id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => null);
END;-- End for Update Process
Igs_Pe_Typ_Instances_Pkg.insert_row(
X_ROWID =>l_rowid,
X_PERSON_ID => v_sca_exists.PERSON_ID,
X_COURSE_CD =>v_sca_exists.COURSE_CD,
X_TYPE_INSTANCE_ID =>l_pk,
X_PERSON_TYPE_CODE =>l_person_type_code,
X_CC_VERSION_NUMBER =>NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER
=>v_sca_exists.ADM_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD
=>v_sca_exists.ADM_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>v_sca_exists.VERSION_NUMBER,
X_SEQUENCE_NUMBER =>NULL,
X_START_DATE => SYSDATE,
X_END_DATE =>NULL,
X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
X_ENDED_BY =>NULL,
X_END_METHOD =>NULL,
X_MODE =>'R',
x_org_id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => null);
SELECT sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
sarakshi 19-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.
*****************************************************************************/
BEGIN
p_message_name := NULL;
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 NOWAIT;
Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
X_ROWID => c_ps_att_rec.ROWID,
X_PERSON_ID => c_ps_att_rec.PERSON_ID,
X_COURSE_CD => c_ps_att_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => c_ps_att_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => c_ps_att_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => c_ps_att_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => c_ps_att_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => c_ps_att_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => c_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => c_ps_att_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => c_ps_att_rec.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => c_ps_att_rec.version_number,
X_CAL_TYPE => c_ps_att_rec.cal_type,
X_LOCATION_CD => c_ps_att_rec.location_cd,
X_ATTENDANCE_MODE => c_ps_att_rec.attendance_mode,
X_ATTENDANCE_TYPE => c_ps_att_rec.attendance_type,
X_COO_ID => c_ps_att_rec.coo_id,
X_STUDENT_CONFIRMED_IND => c_ps_att_rec.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => c_ps_att_rec.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => v_new_course_attempt_status,
X_PROGRESSION_STATUS => c_ps_att_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => c_ps_att_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => c_ps_att_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => c_ps_att_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => c_ps_att_rec.discontinued_dt,
X_DISCONTINUATION_REASON_CD => c_ps_att_rec.discontinuation_reason_cd,
X_LAPSED_DT =>c_ps_att_rec.lapsed_dt,
X_FUNDING_SOURCE => c_ps_att_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => c_ps_att_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => c_ps_att_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => c_ps_att_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => c_ps_att_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => c_ps_att_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => c_ps_att_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => c_ps_att_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => c_ps_att_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => c_ps_att_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => c_ps_att_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => c_ps_att_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => c_ps_att_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => c_ps_att_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => c_ps_att_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => c_ps_att_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => c_ps_att_rec.IGS_PR_CLASS_STD_ID,
-- Added next four parameters as per the Career Impact Build Bug# 2027984
x_primary_program_type => c_ps_att_rec.primary_program_type,
x_primary_prog_type_source => c_ps_att_rec.primary_prog_type_source,
x_catalog_cal_type => c_ps_att_rec.catalog_cal_type,
x_catalog_seq_num => c_ps_att_rec.catalog_seq_num,
x_key_program => c_ps_att_rec.key_program,
-- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
x_override_cmpl_dt => c_ps_att_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => c_ps_att_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as part of bug # 3544927
X_ATTRIBUTE_CATEGORY => c_ps_att_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_ps_att_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_ps_att_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_ps_att_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_ps_att_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_ps_att_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_ps_att_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_ps_att_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_ps_att_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_ps_att_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_ps_att_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_ps_att_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_ps_att_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_ps_att_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_ps_att_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_ps_att_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => c_ps_att_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => c_ps_att_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => c_ps_att_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => c_ps_att_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => c_ps_att_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => c_ps_att_rec.FUTURE_DATED_TRANS_FLAG
);
SELECT 'X'
FROM igs_pe_typ_instances_all PTI,
IGS_PE_PERSON_TYPES PPT
WHERE PPT.system_type = 'STUDENT' AND
ppt.person_type_code = pti.person_type_code
AND pti.COURSE_CD = c_ps_att_rec.COURSE_CD
AND pti.PERSON_ID = c_ps_att_rec.PERSON_ID;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'FORMER_STUDENT';
SELECT ROWID, ti.*
FROM igs_pe_typ_instances_all ti
WHERE course_cd = c_ps_att_rec.COURSE_CD
AND person_id = c_ps_att_rec.PERSON_ID;
Igs_Pe_Typ_Instances_Pkg.update_row(
-- previously l_rowid was being passed to the update row
-- changed it to c2_rec.rowid
-- amuthu 27-Jul-2001
X_ROWID =>c2_rec.ROWID,
X_PERSON_ID =>c2_rec.PERSON_ID,
X_COURSE_CD =>c2_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>c2_rec.TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE =>c2_rec.PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER =>c2_rec.CC_VERSION_NUMBER,
X_FUNNEL_STATUS =>c2_rec.FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER =>c2_rec.ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD =>c2_rec.NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c2_rec.NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER =>c2_rec.SEQUENCE_NUMBER,
X_START_DATE =>c2_rec.START_DATE,
X_END_DATE => SYSDATE,
X_CREATE_METHOD =>c2_rec.CREATE_METHOD,
X_ENDED_BY =>c2_rec.ENDED_BY,
X_END_METHOD =>'PRG_ATTMPT_ST_INACTIVE',
X_MODE =>'R',
X_EMPLMNT_CATEGORY_CODE => c2_rec.emplmnt_category_code);
Igs_Pe_Typ_Instances_Pkg.insert_row(
X_ROWID =>l_rowid,
X_PERSON_ID => c_ps_att_rec.PERSON_ID,
X_COURSE_CD =>c_ps_att_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>l_pk,
X_PERSON_TYPE_CODE =>l_person_type_code,
X_CC_VERSION_NUMBER =>NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER=>
c_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD =>
c_ps_att_rec.ADM_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c_ps_att_rec.VERSION_NUMBER,
X_SEQUENCE_NUMBER =>NULL,
X_START_DATE => SYSDATE,
X_END_DATE =>NULL,
X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
X_ENDED_BY =>NULL,
X_END_METHOD =>NULL,
X_MODE =>'R',
x_org_id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => null);
END;-- End for Update Process
Igs_Pe_Typ_Instances_Pkg.insert_row(
X_ROWID =>l_rowid,
X_PERSON_ID => c_ps_att_rec.PERSON_ID,
X_COURSE_CD =>c_ps_att_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>l_pk,
X_PERSON_TYPE_CODE =>l_person_type_code,
X_CC_VERSION_NUMBER =>NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER
=>c_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD
=>c_ps_att_rec.ADM_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c_ps_att_rec.VERSION_NUMBER,
X_SEQUENCE_NUMBER =>NULL,
X_START_DATE => SYSDATE,
X_END_DATE =>NULL,
X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
X_ENDED_BY =>NULL,
X_END_METHOD =>NULL,
X_MODE =>'R',
x_org_id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => null);
v_updates_done NUMBER;
SELECT sca.person_id,
sca.course_cd,
sca.course_attempt_status
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit,
IGS_EN_STDNT_PS_ATT sca
WHERE sci.start_dt <= trunc(SYSDATE) AND
sci.end_dt >= trunc(SYSDATE) AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
sca.person_id = sci.person_id AND
sca.course_cd = sci.course_cd AND
eit.intermission_type=sci.intermission_type AND
((eit.appr_reqd_ind ='Y' AND sci.approved='Y') OR (eit.appr_reqd_ind='N')) AND
sca.course_attempt_status NOT IN ('INTERMIT',
'DISCONTIN',
'DELETED',
'COMPLETED');
SELECT sca.person_id,
sca.course_cd,
sca.course_attempt_status,
sci.start_dt,
sci.logical_delete_date,
cond_return_flag
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit,
IGS_EN_STDNT_PS_ATT sca
WHERE sci.end_dt < TRUNC(SYSDATE) AND
sca.person_id = sci.person_id AND
sca.course_cd = sci.course_cd AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
eit.intermission_type=sci.intermission_type AND
((eit.appr_reqd_ind ='Y' AND sci.approved='Y') OR (eit.appr_reqd_ind='N')) AND
sca.course_attempt_status = 'INTERMIT';
SELECT sca.person_id, sca.course_cd, sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.course_attempt_status = 'ENROLLED' AND
(
sca.discontinued_dt IS NULL OR
sca.discontinued_dt > SYSDATE
) AND
NOT EXISTS ( SELECT 'x'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = sca.person_id AND
sua.unit_attempt_status = 'ENROLLED' AND
sua.ci_start_dt <= SYSDATE) AND
NOT EXISTS ( SELECT 'x'
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE scae.person_id = sca.person_id AND
scae.course_cd = sca.course_cd AND
Igs_En_Gen_004.ENRP_GET_SCAE_DUE(
scae.person_id,
scae.course_cd,
scae.cal_type,
scae.ci_sequence_number,
'Y',
scae.enr_form_due_dt) > SYSDATE );
SELECT sca.person_id,
sca.course_cd,
sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.course_attempt_status IN ('INACTIVE','LAPSED') AND
EXISTS (
SELECT 'x'
FROM IGS_EN_SU_ATTEMPT sua
WHERE person_id = sca.person_id AND
course_cd = sca.course_cd AND
unit_attempt_status = 'ENROLLED' AND
ci_start_dt <= SYSDATE);
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.course_attempt_status,
sca.commencement_dt,
sca.discontinued_dt,
sca.discontinuation_reason_cd
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.discontinued_dt IS NOT NULL AND
sca.discontinued_dt <= SYSDATE AND
sca.course_attempt_status NOT IN ('DISCONTIN',
'DELETED');
SELECT hz.party_number
FROM igs_en_spi_rconds rc, hz_parties hz
WHERE rc.person_id = hz.party_id
AND rc.person_id =cp_person_id
AND rc.course_cd =cp_course_cd
AND rc.start_dt =cp_start_dt
AND rc.logical_delete_date =cp_logical_del_dt
AND rc.status_code IN('FAILED','PENDING');
SELECT PARTY_NUMBER
FROM HZ_PARTIES
WHERE party_id = cp_person_id;
sarakshi 19-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.
*****************************************************************************/
BEGIN -- enrpl_upd_get_status
-- This local function calls the function enrp_get_sca_status.
-- If the return value is different to the current
-- student_course_attempt_status the status is updated and TRUE
-- is returned otherwise FALSE is returned.
-- If a locked record is encountered the offending record is recorded
-- and FALSE is returned allowing processing to continue.
OPEN c_person_number(p_person_id);
NULL); -- logical_delete_dt
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 NOWAIT;
Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
X_ROWID => c_enps_att_rec.ROWID,
X_PERSON_ID => c_enps_att_rec.PERSON_ID,
X_COURSE_CD => c_enps_att_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => c_enps_att_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => c_enps_att_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => c_enps_att_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => c_enps_att_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => c_enps_att_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => c_enps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => c_enps_att_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => c_enps_att_rec.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => c_enps_att_rec.version_number,
X_CAL_TYPE => c_enps_att_rec.cal_type,
X_LOCATION_CD => c_enps_att_rec.location_cd,
X_ATTENDANCE_MODE => c_enps_att_rec.attendance_mode,
X_ATTENDANCE_TYPE => c_enps_att_rec.attendance_type,
X_COO_ID => c_enps_att_rec.coo_id,
X_STUDENT_CONFIRMED_IND => c_enps_att_rec.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => c_enps_att_rec.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => v_new_course_attempt_status,
X_PROGRESSION_STATUS => c_enps_att_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => c_enps_att_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => c_enps_att_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => c_enps_att_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => c_enps_att_rec.discontinued_dt,
X_DISCONTINUATION_REASON_CD => c_enps_att_rec.discontinuation_reason_cd,
X_LAPSED_DT =>c_enps_att_rec.lapsed_dt,
X_FUNDING_SOURCE => c_enps_att_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => c_enps_att_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => c_enps_att_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => c_enps_att_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => c_enps_att_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => c_enps_att_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => c_enps_att_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => c_enps_att_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => c_enps_att_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => c_enps_att_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => c_enps_att_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => c_enps_att_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => c_enps_att_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => c_enps_att_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => c_enps_att_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => c_enps_att_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => c_enps_att_rec.IGS_PR_CLASS_STD_ID,
-- Added next four parameters as per the Career Impact Build Bug# 2027984
x_primary_program_type => c_enps_att_rec.primary_program_type,
x_primary_prog_type_source => c_enps_att_rec.primary_prog_type_source,
x_catalog_cal_type => c_enps_att_rec.catalog_cal_type,
x_catalog_seq_num => c_enps_att_rec.catalog_seq_num ,
x_key_program => c_enps_att_rec.key_program,
-- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
x_override_cmpl_dt => c_enps_att_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => c_enps_att_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as part of bug # 3544927
X_ATTRIBUTE_CATEGORY => c_enps_att_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_enps_att_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_enps_att_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_enps_att_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_enps_att_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_enps_att_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_enps_att_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_enps_att_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_enps_att_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_enps_att_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_enps_att_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_enps_att_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_enps_att_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_enps_att_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_enps_att_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_enps_att_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => c_enps_att_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => c_enps_att_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => c_enps_att_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => c_enps_att_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => c_enps_att_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => c_enps_att_rec.FUTURE_DATED_TRANS_FLAG);
SELECT 'X'
FROM igs_pe_typ_instances_all PTI,
IGS_PE_PERSON_TYPES PPT
WHERE PPT.system_type = 'STUDENT' AND
ppt.person_type_code = pti.person_type_code
AND pti.COURSE_CD = c_enps_att_rec.COURSE_CD
AND pti.PERSON_ID = c_enps_att_rec.PERSON_ID;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'FORMER_STUDENT';
SELECT ROWID, ti.*
FROM igs_pe_typ_instances_all ti
WHERE course_cd = c_enps_att_rec.COURSE_CD
AND person_id = c_enps_att_rec.PERSON_ID;
Igs_Pe_Typ_Instances_Pkg.update_row(
-- previously l_rowid was being passed to the update row
-- changed it to c2_rec.rowid
-- amuthu 27-Jul-2001
X_ROWID =>c2_rec.ROWID,
X_PERSON_ID =>c2_rec.PERSON_ID,
X_COURSE_CD =>c2_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>c2_rec.TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE =>c2_rec.PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER =>c2_rec.CC_VERSION_NUMBER,
X_FUNNEL_STATUS =>c2_rec.FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER =>c2_rec.ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD =>c2_rec.NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c2_rec.NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER =>c2_rec.SEQUENCE_NUMBER,
X_START_DATE =>c2_rec.START_DATE,
X_END_DATE => SYSDATE,
X_CREATE_METHOD =>c2_rec.CREATE_METHOD,
X_ENDED_BY =>c2_rec.ENDED_BY,
X_END_METHOD =>'PRG_ATTMPT_ST_INACTIVE',
X_MODE =>'R',
X_EMPLMNT_CATEGORY_CODE => c2_rec.emplmnt_category_code);
Igs_Pe_Typ_Instances_Pkg.insert_row(
X_ROWID =>l_rowid,
X_PERSON_ID => c_enps_att_rec.PERSON_ID,
X_COURSE_CD =>c_enps_att_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>l_pk,
X_PERSON_TYPE_CODE =>l_person_type_code,
X_CC_VERSION_NUMBER =>NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER=>
c_enps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD =>
c_enps_att_rec.ADM_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c_enps_att_rec.VERSION_NUMBER,
X_SEQUENCE_NUMBER =>NULL,
X_START_DATE => SYSDATE,
X_END_DATE =>NULL,
X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
X_ENDED_BY =>NULL,
X_END_METHOD =>NULL,
X_MODE =>'R',
x_org_id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => null);
END;-- End for Update Process
Igs_Pe_Typ_Instances_Pkg.insert_row(
X_ROWID =>l_rowid,
X_PERSON_ID => c_enps_att_rec.PERSON_ID,
X_COURSE_CD =>c_enps_att_rec.COURSE_CD,
X_TYPE_INSTANCE_ID =>l_pk,
X_PERSON_TYPE_CODE =>l_person_type_code,
X_CC_VERSION_NUMBER =>NULL,
X_FUNNEL_STATUS => NULL,
X_ADMISSION_APPL_NUMBER
=>c_enps_att_rec.ADM_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD
=>c_enps_att_rec.ADM_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER =>c_enps_att_rec.VERSION_NUMBER,
X_SEQUENCE_NUMBER =>NULL,
X_START_DATE => SYSDATE,
X_END_DATE =>NULL,
X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
X_ENDED_BY =>NULL,
X_END_METHOD =>NULL,
X_MODE =>'R',
x_org_id => l_org_id,
X_EMPLMNT_CATEGORY_CODE => null);
v_updates_done := 0;
v_updates_done := v_updates_done + 1;
FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
v_updates_done := 0;
v_sci_end.logical_delete_date);
v_updates_done := v_updates_done + 1;
FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
v_updates_done := 0;
v_updates_done := v_updates_done + 1;
FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
v_updates_done := 0;
v_updates_done := v_updates_done + 1;
FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
SELECT
sua.person_id,
sua.course_cd,
-- Add the following one line of code. From Callista 2.0 18-May-2000
sca.version_number sca_version_number,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.ci_end_dt,
sua.location_cd,
sua.unit_class,
sua.enrolled_dt,
sua.uoo_id,
Igs_En_Gen_004.enrp_get_rule_cutoff(
sua.cal_type,
sua.ci_sequence_number,
cst_enrolled) enrolled_cutoff_date
FROM IGS_EN_SU_ATTEMPT sua,
IGS_EN_STDNT_PS_ATT sca
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status = cst_invalid AND
sca.person_id = sua.person_id AND
sca.course_cd = sua.course_cd;
SELECT ROWID,
IGS_EN_SU_ATTEMPT.*
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = v_sua_sca_rec.person_id AND
course_cd = v_sua_sca_rec.course_cd AND
uoo_id = v_sua_sca_rec.uoo_id
FOR UPDATE NOWAIT;
igs_en_sua_api.update_unit_attempt(
x_rowid => c_assu_rec.rowid,
x_person_id => c_assu_rec.person_id,
x_course_cd => c_assu_rec.course_cd ,
x_unit_cd => c_assu_rec.unit_cd,
x_cal_type => c_assu_rec.cal_type,
x_ci_sequence_number => c_assu_rec.ci_sequence_number ,
x_version_number => c_assu_rec.version_number ,
x_location_cd => c_assu_rec.location_cd,
x_unit_class => c_assu_rec.unit_class ,
x_ci_start_dt => c_assu_rec.ci_start_dt,
x_ci_end_dt => c_assu_rec.ci_end_dt,
x_uoo_id => c_assu_rec.uoo_id ,
x_enrolled_dt => c_assu_rec.enrolled_dt,
x_unit_attempt_status => cst_enrolled,
x_administrative_unit_status => c_assu_rec.administrative_unit_status,
x_administrative_priority => c_assu_rec.administrative_priority,
x_discontinued_dt => c_assu_rec.discontinued_dt,
x_dcnt_reason_cd => c_assu_rec.dcnt_reason_cd,
x_rule_waived_dt => c_assu_rec.rule_waived_dt ,
x_rule_waived_person_id => c_assu_rec.rule_waived_person_id ,
x_no_assessment_ind => c_assu_rec.no_assessment_ind,
x_sup_unit_cd => c_assu_rec.sup_unit_cd ,
x_sup_version_number => c_assu_rec.sup_version_number,
x_exam_location_cd => c_assu_rec.exam_location_cd,
x_alternative_title => c_assu_rec.alternative_title,
x_override_enrolled_cp => c_assu_rec.override_enrolled_cp,
x_override_eftsu => c_assu_rec.override_eftsu ,
x_override_achievable_cp => c_assu_rec.override_achievable_cp,
x_override_outcome_due_dt => c_assu_rec.override_outcome_due_dt,
x_override_credit_reason => c_assu_rec.override_credit_reason,
x_waitlist_dt => c_assu_rec.waitlist_dt,
x_mode => 'R',
-- Added as part of Enroll Process build - amuthu
x_gs_version_number => c_assu_rec.gs_version_number,
x_enr_method_type => c_assu_rec.enr_method_type,
x_failed_unit_rule => NULL, -- since the rule has succeeded now
x_cart => c_assu_rec.cart,
x_rsv_seat_ext_id => c_assu_rec.RSV_SEAT_EXT_ID,
x_org_unit_cd => c_assu_rec.org_unit_cd,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id => c_assu_rec.session_id,
-- Added the column grading schema as a part of the bug 2037897. - aiyer
x_grading_schema_code => c_assu_rec.grading_schema_code,
x_deg_aud_detail_id => c_assu_rec.deg_aud_detail_id,
x_subtitle => c_assu_rec.subtitle,
x_student_career_transcript => c_assu_rec.student_career_transcript,
x_student_career_statistics => c_assu_rec.student_career_statistics,
x_attribute_category => c_assu_rec.attribute_category,
x_attribute1 => c_assu_rec.attribute1,
x_attribute2 => c_assu_rec.attribute2,
x_attribute3 => c_assu_rec.attribute3,
x_attribute4 => c_assu_rec.attribute4,
x_attribute5 => c_assu_rec.attribute5,
x_attribute6 => c_assu_rec.attribute6,
x_attribute7 => c_assu_rec.attribute7,
x_attribute8 => c_assu_rec.attribute8,
x_attribute9 => c_assu_rec.attribute9,
x_attribute10 => c_assu_rec.attribute10,
x_attribute11 => c_assu_rec.attribute11,
x_attribute12 => c_assu_rec.attribute12,
x_attribute13 => c_assu_rec.attribute13,
x_attribute14 => c_assu_rec.attribute14,
x_attribute15 => c_assu_rec.attribute15,
x_attribute16 => c_assu_rec.attribute16,
x_attribute17 => c_assu_rec.attribute17,
x_attribute18 => c_assu_rec.attribute18,
x_attribute19 => c_assu_rec.attribute19,
x_attribute20 => c_assu_rec.attribute20,
x_waitlist_manual_ind => c_assu_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
x_wlst_priority_weight_num => c_assu_rec.wlst_priority_weight_num,
x_wlst_preference_weight_num => c_assu_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
x_core_indicator_code => c_assu_rec.core_indicator_code
);
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
Igs_En_Gen_004.enrp_get_rule_cutoff(
sua.cal_type,
sua.ci_sequence_number,
cst_invalid) invalid_cutoff_date
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status = cst_enrolled AND
sua.rule_waived_dt IS NULL;
SELECT sca.version_number
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT ROWID,
IGS_EN_SU_ATTEMPT.*
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = v_sua_rec.person_id AND
course_cd = v_sua_rec.course_cd AND
uoo_id = v_sua_rec.uoo_id
FOR UPDATE NOWAIT;
igs_en_sua_api.update_unit_attempt(
X_ROWID => c_suatt_rec.ROWID,
X_PERSON_ID => c_suatt_rec.PERSON_ID,
X_COURSE_CD => c_suatt_rec.COURSE_CD ,
X_UNIT_CD => c_suatt_rec.UNIT_CD,
X_CAL_TYPE => c_suatt_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => c_suatt_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => c_suatt_rec.VERSION_NUMBER ,
X_LOCATION_CD => c_suatt_rec.LOCATION_CD,
X_UNIT_CLASS => c_suatt_rec.UNIT_CLASS ,
X_CI_START_DT => c_suatt_rec.CI_START_DT,
X_CI_END_DT => c_suatt_rec.CI_END_DT,
X_UOO_ID => c_suatt_rec.UOO_ID ,
X_ENROLLED_DT => c_suatt_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_invalid,
X_ADMINISTRATIVE_UNIT_STATUS => c_suatt_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => c_suatt_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => c_suatt_rec.discontinued_dt,
X_DCNT_REASON_CD => c_suatt_rec.DCNT_REASON_CD ,
X_RULE_WAIVED_DT => c_suatt_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => c_suatt_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => c_suatt_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => c_suatt_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => c_suatt_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => c_suatt_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => c_suatt_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => c_suatt_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => c_suatt_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => c_suatt_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => c_suatt_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => c_suatt_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => c_suatt_rec.waitlist_dt,
X_MODE => 'R',
-- Added as part of Enroll Process build - amuthu
X_GS_VERSION_NUMBER => c_suatt_rec.gs_version_number,
X_ENR_METHOD_TYPE => c_suatt_rec.enr_method_type,
X_FAILED_UNIT_RULE => lv_rule_failed,
X_CART => c_suatt_rec.cart,
X_RSV_SEAT_EXT_ID => c_suatt_rec.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => c_suatt_rec.ORG_UNIT_CD,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => c_suatt_rec.session_id,
-- Added the column grading schema as a part of the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => c_suatt_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => c_suatt_rec.deg_aud_detail_id,
X_SUBTITLE => c_suatt_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => c_suatt_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => c_suatt_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => c_suatt_rec.attribute_category,
X_ATTRIBUTE1 => c_suatt_rec.attribute1,
X_ATTRIBUTE2 => c_suatt_rec.attribute2,
X_ATTRIBUTE3 => c_suatt_rec.attribute3,
X_ATTRIBUTE4 => c_suatt_rec.attribute4,
X_ATTRIBUTE5 => c_suatt_rec.attribute5,
X_ATTRIBUTE6 => c_suatt_rec.attribute6,
X_ATTRIBUTE7 => c_suatt_rec.attribute7,
X_ATTRIBUTE8 => c_suatt_rec.attribute8,
X_ATTRIBUTE9 => c_suatt_rec.attribute9,
X_ATTRIBUTE10 => c_suatt_rec.attribute10,
X_ATTRIBUTE11 => c_suatt_rec.attribute11,
X_ATTRIBUTE12 => c_suatt_rec.attribute12,
X_ATTRIBUTE13 => c_suatt_rec.attribute13,
X_ATTRIBUTE14 => c_suatt_rec.attribute14,
X_ATTRIBUTE15 => c_suatt_rec.attribute15,
X_ATTRIBUTE16 => c_suatt_rec.attribute16,
X_ATTRIBUTE17 => c_suatt_rec.attribute17,
X_ATTRIBUTE18 => c_suatt_rec.attribute18,
X_ATTRIBUTE19 => c_suatt_rec.attribute19,
X_ATTRIBUTE20 => c_suatt_rec.attribute20,
X_WAITLIST_MANUAL_IND => c_suatt_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => c_suatt_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => c_suatt_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => c_suatt_rec.core_indicator_code
);
SELECT scho.tax_file_number
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = cp_person_id AND
scho.tax_file_number IS NOT NULL AND
scho.tax_file_invalid_dt IS NULL
ORDER BY scho.start_dt DESC;
SELECT scho.ROWID,
scho.*
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = p_person_id AND
scho.course_cd = p_course_cd AND
scho.start_dt = p_start_dt
FOR UPDATE NOWAIT;
Igs_En_Stdntpshecsop_Pkg.UPDATE_ROW(
X_ROWID => c_hecs_opt_rec.ROWID,
X_PERSON_ID => c_hecs_opt_rec.PERSON_ID,
X_COURSE_CD => c_hecs_opt_rec.COURSE_CD,
X_START_DT => c_hecs_opt_rec.START_DT,
X_END_DT => c_hecs_opt_rec.end_dt,
X_HECS_PAYMENT_OPTION => c_hecs_opt_rec.HECS_PAYMENT_OPTION,
X_DIFFERENTIAL_HECS_IND => c_hecs_opt_rec.DIFFERENTIAL_HECS_IND,
X_DIFF_HECS_IND_UPDATE_WHO => c_hecs_opt_rec.DIFF_HECS_IND_UPDATE_WHO,
X_DIFF_HECS_IND_UPDATE_ON => c_hecs_opt_rec.DIFF_HECS_IND_UPDATE_ON ,
X_OUTSIDE_AUS_RES_IND => c_hecs_opt_rec.OUTSIDE_AUS_RES_IND,
X_NZ_CITIZEN_IND => c_hecs_opt_rec.NZ_CITIZEN_IND,
X_NZ_CITIZEN_LESS2YR_IND => c_hecs_opt_rec.NZ_CITIZEN_LESS2YR_IND,
X_NZ_CITIZEN_NOT_RES_IND => c_hecs_opt_rec.NZ_CITIZEN_NOT_RES_IND,
X_SAFETY_NET_IND => c_hecs_opt_rec.SAFETY_NET_IND,
X_TAX_FILE_NUMBER => v_tax_file_number,
X_TAX_FILE_NUMBER_COLLECTED_DT => c_hecs_opt_rec.TAX_FILE_NUMBER_COLLECTED_DT,
X_TAX_FILE_INVALID_DT => c_hecs_opt_rec.TAX_FILE_INVALID_DT,
X_TAX_FILE_CERTIFICATE_NUMBER => c_hecs_opt_rec.TAX_FILE_CERTIFICATE_NUMBER,
X_DIFF_HECS_IND_UPDATE_COMMENT => c_hecs_opt_rec.DIFF_HECS_IND_UPDATE_COMMENTs,
X_MODE => 'R'
);