[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sca.course_attempt_status,
sca.primary_program_type,
sca.primary_prog_type_source
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd;
SELECT sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE sca.person_id = p_person_id
AND crv.course_type = p_course_type
AND sca.course_cd = crv.course_Cd
AND sca.version_number = crv.version_number
AND sca.primary_program_type = 'PRIMARY'
AND sca.course_cd = NVL(p_new_primary_course_cd,sca.course_cd);
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
SELECT crv.course_type
FROM igs_ps_ver crv
WHERE course_cd = p_course_cd
and version_number = p_version_number;
SELECT crv.primary_program_rank
FROM igs_ps_type pst,
igs_ps_ver crv
WHERE pst.course_type = cp_course_type
AND crv.course_Cd = p_course_Cd
AND crv.version_number = p_version_number
AND crv.course_type = pst.course_type;
SELECT PRIMARY_PROGRAM_RANK , key_program
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE crv.course_type = cp_course_type
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number
AND sca.person_id = p_person_id
AND sca.course_cd <> p_course_cd
AND sca.primary_program_type = cst_primary;
SELECT 'X'
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id
AND key_program = 'Y';
SELECT count(1)
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE sca.person_id = p_person_id
AND crv.course_type = cp_course_type
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number
AND course_attempt_status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT');
v_update BOOLEAN;
SELECT crv.course_type
FROM igs_ps_ver crv
WHERE course_cd = cp_course_cd
and version_number = cp_version_number;
SELECT sca.*
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE crv.course_type = cp_course_type
and sca.course_cd = crv.course_cd
and sca.version_number = crv.version_number
and sca.person_id = cp_person_id
and sca.course_cd <> cp_course_cd;
SELECT crv.PRIMARY_PROGRAM_RANK, sca.course_Cd
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE crv.course_type = cp_course_type
and sca.course_cd = crv.course_cd
and sca.version_number = crv.version_number
and sca.person_id = cp_person_id
and sca.course_cd <> cp_course_cd
and sca.primary_program_type <> cst_primary
and sca.course_attempt_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT')
order by crv.primary_program_rank asc ; -- ordering it to find the min ranked value first Kamal's idea
SELECT 'X'
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE crv.course_type <> cp_course_type
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number
AND sca.person_id = p_person_id
AND sca.primary_program_type = cst_primary
AND sca.key_program = 'Y';
SELECT 'X'
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE crv.course_type = cp_course_type
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number
AND sca.person_id = p_person_id
AND sca.course_cd <> cp_course_cd
AND sca.primary_program_type = cst_primary ;
v_update := FALSE;
v_update := TRUE;
v_update := TRUE;
v_update := TRUE;
v_update := TRUE;
IF v_update THEN -- update the record only if some thing has changed.
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_upd_rec.row_id,
X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_sca_upd_rec.fee_cat,
X_CORRESPONDENCE_CAT => v_sca_upd_rec.correspondence_cat,
X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_upd_rec.logical_delete_dt,
X_ADM_ADMISSION_APPL_NUMBER => v_sca_upd_rec.adm_admission_appl_number,
X_ADM_NOMINATED_COURSE_CD => v_sca_upd_rec.adm_nominated_course_cd,
X_ADM_SEQUENCE_NUMBER => v_sca_upd_rec.adm_sequence_number,
X_VERSION_NUMBER => v_sca_upd_rec.version_number,
X_CAL_TYPE => v_sca_upd_rec.cal_type,
X_LOCATION_CD => v_sca_upd_rec.location_cd,
X_ATTENDANCE_MODE => v_sca_upd_rec.attendance_mode,
X_ATTENDANCE_TYPE => v_sca_upd_rec.attendance_type,
X_COO_ID => v_sca_upd_rec.coo_id,
X_STUDENT_CONFIRMED_IND => v_sca_upd_rec.student_confirmed_ind,
X_COMMENCEMENT_DT => v_sca_upd_rec.commencement_dt,
X_COURSE_ATTEMPT_STATUS => v_sca_upd_rec.course_attempt_status,
X_PROGRESSION_STATUS => v_sca_upd_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_sca_upd_rec.provisional_ind,
X_DISCONTINUED_DT => v_sca_upd_rec.discontinued_dt,
X_DISCONTINUATION_REASON_CD => v_sca_upd_rec.discontinuation_reason_cd,
X_LAPSED_DT => v_sca_upd_rec.LAPSED_DT,
X_FUNDING_SOURCE => v_sca_upd_rec.funding_source,
X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.nominated_completion_yr,
X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
x_last_date_of_attendance => v_sca_upd_rec.last_date_of_attendance,
x_dropped_by => v_sca_upd_rec.dropped_by,
X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.igs_pr_class_std_id,
x_primary_program_type => v_sca_upd_rec.primary_program_type,
x_primary_prog_type_source => v_sca_upd_rec.primary_prog_type_source,
x_catalog_cal_type => v_sca_upd_rec.catalog_cal_type,
x_catalog_seq_num => v_sca_upd_rec.catalog_seq_num,
x_key_program => v_sca_upd_rec.key_program,
x_override_cmpl_dt => v_sca_upd_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_sca_upd_rec.manual_ovr_cmpl_dt_ind,
X_MODE => 'R',
X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.attribute_category,
X_ATTRIBUTE1 => v_sca_upd_rec.attribute1,
X_ATTRIBUTE2 => v_sca_upd_rec.attribute2,
X_ATTRIBUTE3 => v_sca_upd_rec.attribute3,
X_ATTRIBUTE4 => v_sca_upd_rec.attribute4,
X_ATTRIBUTE5 => v_sca_upd_rec.attribute5,
X_ATTRIBUTE6 => v_sca_upd_rec.attribute6,
X_ATTRIBUTE7 => v_sca_upd_rec.attribute7,
X_ATTRIBUTE8 => v_sca_upd_rec.attribute8,
X_ATTRIBUTE9 => v_sca_upd_rec.attribute9,
X_ATTRIBUTE10 => v_sca_upd_rec.attribute10,
X_ATTRIBUTE11 => v_sca_upd_rec.attribute11,
X_ATTRIBUTE12 => v_sca_upd_rec.attribute12,
X_ATTRIBUTE13 => v_sca_upd_rec.attribute13,
X_ATTRIBUTE14 => v_sca_upd_rec.attribute14,
X_ATTRIBUTE15 => v_sca_upd_rec.attribute15,
X_ATTRIBUTE16 => v_sca_upd_rec.attribute16,
X_ATTRIBUTE17 => v_sca_upd_rec.attribute17,
X_ATTRIBUTE18 => v_sca_upd_rec.attribute18,
X_ATTRIBUTE19 => v_sca_upd_rec.attribute19,
X_ATTRIBUTE20 => v_sca_upd_rec.attribute20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.future_dated_trans_flag
);
SELECT count(primary_program_type)
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE crv.course_type = p_course_type AND
sca.course_cd = crv.course_cd AND
sca.version_number = crv.version_number AND
sca.person_id = p_person_id AND
sca.primary_program_type = 'PRIMARY';
select count(student_confirmed_ind)
from IGS_EN_STDNT_PS_ATT sca, igs_ps_ver crv
where crv.course_type = p_course_type and
sca.course_cd = crv.course_cd and
sca.version_number = crv.version_number and
sca.person_id = p_person_id and
course_attempt_Status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT');