[Home] [Help]
6122: jbaber 19-Jan-06 Exclude flagged units for HE305 - Extract Improvements
6123: ***************************************************************/
6124:
6125: -- smaddali restructured the cursor for performance issue Bug#2839289
6126: -- smaddali replaced igs_as_suaai_v with igs_as_su_stmptout and added condition finalised_ind='Y' , Bug#2717755
6127: CURSOR c_mod IS
6128: SELECT DISTINCT a.unit_cd,
6129: a.version_number,
6130: d.prop_of_teaching_in_welsh,
6154: FROM igs_en_su_attempt_all a,
6155: igs_ps_unit_ver_all b,
6156: igs_he_st_spa_all c,
6157: igs_he_st_unt_vs_all d,
6158: igs_as_su_stmptout e,
6159: igs_ps_usec_cps cps
6160: WHERE a.unit_cd = b.unit_cd
6161: AND a.version_number = b.version_number
6162: AND c.person_id = a.person_id
6184: sua.unit_attempt_status,
6185: sua.no_assessment_ind,
6186: suo.outcome_dt,
6187: sua.uoo_id
6188: FROM igs_as_su_stmptout suo,
6189: igs_en_su_attempt_all sua,
6190: igs_he_st_spa_all spa
6191: WHERE suo.person_id = sua.person_id
6192: AND suo.course_cd = sua.course_cd
7531: -- than or equal to the reporting period end date.
7532: CURSOR c_unit_count IS
7533: SELECT count(*)
7534: FROM
7535: igs_en_su_attempt_all su, igs_as_su_stmptout_all suo1,
7536: igs_he_st_spa spa1, igs_he_st_spa spa2, igs_as_grd_sch_grade gsg
7537: WHERE su.person_id = suo1.person_id
7538: AND su.course_cd = suo1.course_cd
7539: AND su.unit_cd = suo1.unit_cd
7550: AND gsg.grade = suo1.grade
7551: AND gsg.version_number = suo1.version_number
7552: AND suo1.outcome_dt IN
7553: (SELECT MAX(suo2.outcome_dt)
7554: FROM igs_as_su_stmptout_all suo2
7555: WHERE suo2.person_id = suo1.person_id
7556: AND suo2.course_cd = suo1.course_cd
7557: AND suo2.finalised_outcome_ind = 'Y'
7558: AND suo2.unit_cd = suo1.unit_cd);