[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(spat.class_standing_id,spa.igs_pr_class_std_id) igs_pr_class_std_id,
css.igs_pr_cs_schdl_id,
css.consider_changes
FROM IGS_EN_STDNT_PS_ATT spa,
IGS_PS_VER pv,
IGS_PS_TYPE pt,
IGS_PR_CS_SCHDL css,
IGS_EN_SPA_TERMS spat
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND spa.course_cd = pv.course_cd
AND spa.version_number = pv.version_number
AND spa.course_attempt_status IN
('ENROLLED','COMPLETED','INTERMIT','INACTIVE')
AND pv.course_type = pt.course_type
AND pt.course_type = css.course_type (+)
AND TRUNC(css.start_dt (+) ) <= TRUNC(SYSDATE)
AND TRUNC(NVL(css.end_dt (+),SYSDATE) ) >= TRUNC(SYSDATE)
AND spat.person_id(+) = spa.person_id
AND spat.program_cd(+) = spa.course_cd
AND spat.term_cal_type(+) = p_load_cal_type
AND spat.term_sequence_number(+) = p_load_ci_sequence_number;
SELECT class_standing
FROM igs_pr_class_std
WHERE igs_pr_class_std_id = cp_igs_pr_class_std_id;
SELECT COUNT(*)
FROM igs_pr_css_class_std
WHERE igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
AND min_cp IS NOT NULL;
SELECT COUNT(*)
FROM igs_pr_css_class_std
WHERE igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
AND acad_year IS NOT NULL;
SELECT COUNT(*)
FROM igs_ca_type ct1,
igs_ca_inst ci1
WHERE ct1.s_cal_cat = 'ACADEMIC'
AND ct1.cal_type = ci1.cal_type
AND 0 < (SELECT COUNT(*)
FROM igs_ca_inst ci2a,
igs_ca_inst ci2b,
igs_ca_type ct2,
igs_ca_inst_rel cir2
WHERE ci2a.cal_type = cp_load_cal_type
AND ci2a.sequence_number = cp_load_ci_sequence_number
AND TRUNC(ci2a.end_dt) >= TRUNC(ci2b.end_dt)
AND cir2.sub_cal_type = ci2b.cal_type
AND cir2.sub_ci_sequence_number = ci2b.sequence_number
AND cir2.sup_cal_type = ci1.cal_type
AND cir2.sup_ci_sequence_number = ci1.sequence_number
AND ci2b.cal_type = ct2.cal_type
AND ct2.s_cal_cat = 'LOAD')
AND 0 < (SELECT COUNT(*)
FROM igs_en_su_attempt sua3,
igs_ca_inst_rel cir3
WHERE sua3.person_id = p_person_id
AND sua3.course_cd = p_course_cd
AND sua3.unit_attempt_status NOT IN
('UNCONFIRM', 'DROPPED','DISCONTIN', 'WAITLISTED')
AND sua3.cal_type = cir3.sub_cal_type
AND sua3.ci_sequence_number = cir3.sub_ci_sequence_number
AND cir3.sup_cal_type = ci1.cal_type
AND cir3.sup_ci_sequence_number = ci1.sequence_number);
/* Cursor to Select Class Standing matching the Period Range */
CURSOR C_Get_Cs_for_acad_yr(cp_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE,
cp_acad_year igs_pr_css_class_std.acad_year%TYPE ) IS
SELECT ipcs.class_standing
FROM igs_pr_css_class_std ipccs,
igs_pr_class_std ipcs
WHERE ipccs.igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
AND ipccs.acad_year = cp_acad_year
AND ipccs.igs_pr_class_std_id = ipcs.igs_pr_class_std_id;
SELECT ci.cal_type,
ci.sequence_number
FROM igs_ca_type ct,
igs_ca_inst ci
WHERE ct.s_cal_cat = 'LOAD'
AND ct.cal_type = ci.cal_type
AND ((cp_consider_changes = 'IMMEDIATELY'
AND ((TRUNC(ci.start_dt) <= TRUNC(NVL(p_effective_dt, SYSDATE))
AND p_load_cal_type IS NULL)
OR (ci.cal_type = p_load_cal_type
AND ci.sequence_number = p_load_ci_sequence_number
AND p_load_cal_type IS NOT NULL)))
OR (cp_consider_changes = 'BYPERIOD'
AND (TRUNC(ci.end_dt) <= TRUNC(NVL(p_effective_dt, SYSDATE))
OR (p_load_cal_type IS NOT NULL
AND ci.cal_type = p_load_cal_type
AND ci.sequence_number = p_load_ci_sequence_number
AND TRUNC(ci.end_dt) <= TRUNC(SYSDATE)))))
ORDER BY ci.end_dt DESC;
SELECT sup_cal_type,
sup_ci_sequence_number
FROM igs_ca_type ct,
igs_ca_inst_rel cir
WHERE cir.sub_cal_type = cp_load_cal_type
AND cir.sub_ci_sequence_number = cp_load_ci_sequence_number
AND cir.sup_cal_type = ct.cal_type
AND ct.s_cal_cat = 'ACADEMIC';
/*Cursor to SELECT Class Standing matching the CP RANGE */
CURSOR C_Get_cs_cp_range( cp_igs_pr_cs_schdl_id igs_pr_cs_schdl.igs_pr_cs_schdl_id%TYPE,
cp_cp igs_pr_css_class_std.min_cp%TYPE ) IS
SELECT ipcs.class_standing
FROM igs_pr_css_class_std ipccs,
igs_pr_class_std ipcs
WHERE ipccs.igs_pr_cs_schdl_id = cp_igs_pr_cs_schdl_id
AND ipccs.min_cp <= cp_cp
AND ipccs.max_cp >= cp_cp
AND ipccs.igs_pr_class_std_id = ipcs.igs_pr_class_std_id;
/* Select Class Standing matching the Period Range */
OPEN C_Get_Cs_for_acad_yr(l_igs_pr_cs_schdl_id, l_acad_year) ;
/* SELECT Class Standing matching the CP RANGE */
OPEN C_Get_cs_cp_range( l_igs_pr_cs_schdl_id, l_cp);