[Home] [Help]
51:
52: END research_st ;
53:
54:
55: PROCEDURE coo_type (p_person_id IN igs_pe_person.person_id%TYPE ,
56: p_unit_set_cd IN igs_en_unit_set.unit_set_cd%TYPE,
57: p_us_version_number IN igs_en_unit_set.version_number%TYPE,
58: p_sequence_number IN igs_as_su_setatmpt.sequence_number%TYPE ,
59: p_coo_id IN igs_ps_ofr_opt_all.coo_id%TYPE ,
191:
192: END coo_type ;
193:
194:
195: PROCEDURE fte_type_intensity ( p_person_id IN igs_pe_person.person_id%TYPE ,
196: p_coo_id IN igs_ps_ofr_opt_all.coo_id%TYPE ,
197: p_unit_set_cd IN igs_en_unit_set.unit_set_cd%TYPE ,
198: p_us_version_number IN igs_en_unit_set.version_number%TYPE ,
199: p_sequence_number IN igs_as_su_setatmpt.sequence_number%TYPE ,
674:
675: -- get the student unit attempts for the current program attempt
676: -- jtmathew modified cursor to use unit section override start date (if exists) otherwise teaching period start date
677: -- jchakrab modified for 4251041 - removed redundant ORDER BY clause
678: CURSOR c_sua(cp_person_id igs_pe_person.person_id%TYPE ,
679: cp_course_cd igs_ps_ver.course_cd%TYPE ) IS
680: SELECT sua.unit_cd,
681: sua.version_number,
682: sua.enrolled_dt ,
714: uoo.version_number=uv.version_number AND
715: uoo.uoo_id=cp_uoo_id;
716:
717: -- check if the student has transferred to the current program from other program
718: CURSOR c_trn_to (cp_person_id igs_pe_person.person_id%TYPE ,
719: cp_course_cd igs_ps_ver.course_cd%TYPE) IS
720: SELECT transfer_course_cd
721: FROM igs_ps_stdnt_trn
722: WHERE person_id = cp_person_id AND
723: course_cd = cp_course_cd AND
724: ( transfer_dt BETWEEN p_fte_start_dt AND p_fte_end_dt );
725:
726: -- get all the completed unit attempts of the program transferred from
727: CURSOR c_trn_from_units (cp_person_id igs_pe_person.person_id%TYPE ,
728: cp_course_cd igs_ps_ver.course_cd%TYPE) IS
729: SELECT unit_cd,
730: version_number,
731: ci_start_dt,
742: -- jtmathew modified this cursor to allow for end_dts for HE357 changes
743: -- smaddali modified this cursors where clause for selection and completion dates for bug 2448315
744: -- smaddali modified this cursor to pick up acad_perd field from igs_ps_us_prenr_cfg instead of
745: -- from igs_en_susa_year_v for bug 2452785
746: CURSOR c_crs_year(cp_person_id igs_pe_person.person_id%TYPE ,
747: cp_course_cd igs_ps_ver.course_cd%TYPE,
748: cp_selection_dt_from igs_as_su_setatmpt.selection_dt%TYPE,
749: cp_selection_dt_to igs_as_su_setatmpt.selection_dt%TYPE) IS
750: SELECT usv.unit_set_cd , usv.us_version_number , usv.sequence_number,
765: -- Retrieve the number of student program attempts that have more than one year of program
766: -- within the FTE calculation period
767: -- jtmathew modified this cursor to use optional cp_selection_dt parameters for HE370 changes
768: -- jtmathew created this cursor for HE357 changes
769: CURSOR c_multi_yop(cp_person_id igs_pe_person.person_id%TYPE ,
770: cp_course_cd igs_ps_ver.course_cd%TYPE,
771: cp_selection_dt_from igs_as_su_setatmpt.selection_dt%TYPE,
772: cp_selection_dt_to igs_as_su_setatmpt.selection_dt%TYPE) IS
773: SELECT usv.person_id, usv.course_cd, count(*) multi_yop_count
789: -- jtmathew modified this cursor to use optional cp_selection_dt parameters for HE370 changes
790: -- jtmathew rewrote c_year_cal cursor to select calendar instance
791: -- based on yop selection, completion and end dates for HE357 changes
792: -- smaddali modified the cursor to add DISTINCT ,to eliminate duplicate records for bug 2452785
793: CURSOR c_year_cal (cp_person_id igs_pe_person.person_id%TYPE,
794: cp_course_cd igs_ps_ver.course_cd%TYPE,
795: cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE,
796: cp_selection_dt_from igs_as_su_setatmpt.selection_dt%TYPE,
797: cp_selection_dt_to igs_as_su_setatmpt.selection_dt%TYPE) IS
834: -- either started, completed or ended in the fte period
835: -- jtmathew modified this cursor to use optional cp_selection_dt parameters for HE370 changes
836: -- jtmathew modified this cursor to allow for end_dts for HE357 changes
837: -- smaddali modified this cursors where clause of selection_dt and completion_dt for bug 2448315
838: CURSOR c_year (cp_person_id igs_pe_person.person_id%TYPE,
839: cp_course_cd igs_ps_ver.course_cd%TYPE,
840: cp_selection_dt_from igs_as_su_setatmpt.selection_dt%TYPE,
841: cp_selection_dt_to igs_as_su_setatmpt.selection_dt%TYPE) IS
842: SELECT susa.unit_set_cd , susa.us_version_number , susa.sequence_number
854: c_year_rec c_year%ROWTYPE;
855:
856: -- get the HESA unit set attempt corresponding to the current year of program
857: -- in which to save the calculated fte
858: CURSOR c_susa_upd (cp_person_id igs_pe_person.person_id%TYPE ,
859: cp_course_cd igs_ps_ver.course_cd%TYPE ,
860: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE ,
861: cp_sequence_number igs_as_su_setatmpt.sequence_number%TYPE ) IS
862: SELECT rowid , susa.*
882: closed_ind = 'N';
883: c_fte_prop_rec c_fte_prop%ROWTYPE ;
884:
885: -- check if the program has been intermitted for some part of the fte period
886: CURSOR c_intm_part (cp_person_id igs_pe_person.person_id%TYPE ,
887: cp_course_cd igs_ps_ver.course_cd%TYPE ,
888: cp_start_dt DATE ,
889: cp_end_dt DATE) IS
890: SELECT start_dt , end_dt
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');
1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');
1089: