[Home] [Help]
610: p_person_id IN NUMBER )
611: RETURN VARCHAR2 AS
612: BEGIN -- enrp_get_us_title
613: -- This routine will get the IGS_EN_UNIT_SET title from either the
614: -- IGS_AS_SU_SETATMPT, IGS_PS_OFR_UNIT_SET or IGS_EN_UNIT_SET table,
615: -- taking the lowest level which is set.
616: DECLARE
617: v_override_title igs_as_su_setatmpt.override_title%TYPE;
618: v_cous_override_title igs_ps_ofr_unit_set.override_title%TYPE;
613: -- This routine will get the IGS_EN_UNIT_SET title from either the
614: -- IGS_AS_SU_SETATMPT, IGS_PS_OFR_UNIT_SET or IGS_EN_UNIT_SET table,
615: -- taking the lowest level which is set.
616: DECLARE
617: v_override_title igs_as_su_setatmpt.override_title%TYPE;
618: v_cous_override_title igs_ps_ofr_unit_set.override_title%TYPE;
619: v_us_title igs_en_unit_set.title%TYPE;
620: CURSOR c_susa IS
621: SELECT susa.override_title
618: v_cous_override_title igs_ps_ofr_unit_set.override_title%TYPE;
619: v_us_title igs_en_unit_set.title%TYPE;
620: CURSOR c_susa IS
621: SELECT susa.override_title
622: FROM igs_as_su_setatmpt susa
623: WHERE susa.person_id = p_person_id AND
624: susa.course_cd = p_course_cd AND
625: susa.unit_set_cd = p_unit_set_cd AND
626: susa.sequence_number = p_sequence_number;
1390: CURSOR c_yop_us_st(cp_person_id NUMBER,
1391: cp_course_cd VARCHAR2,
1392: cp_unit_set_cd VARCHAR2)IS
1393: SELECT sca.course_cd,susa.unit_set_cd
1394: FROM IGS_AS_SU_SETATMPT susa,
1395: IGS_EN_STDNT_PS_ATT sca
1396: WHERE sca.person_id =cp_person_id
1397: AND sca.course_cd =cp_course_cd
1398: AND susa.person_id =sca.person_id
2752:
2753: -- get the currently active unit set for the person course attempt
2754: CURSOR c_active_us IS
2755: SELECT susa.selection_dt
2756: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
2757: WHERE susa.person_id = p_person_id AND
2758: susa.course_cd = p_course_cd AND
2759: susa.selection_dt IS NOT NULL AND
2760: susa.end_dt IS NULL AND
2766:
2767: -- find the last active unit set for the person program
2768: CURSOR c_last_us IS
2769: SELECT susa.selection_dt
2770: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
2771: WHERE susa.person_id = p_person_id AND
2772: susa.course_cd = p_course_cd AND
2773: susa.rqrmnts_complete_dt IS NOT NULL AND
2774: susa.unit_set_cd = us.unit_set_cd AND
2777: ORDER BY susa.rqrmnts_complete_dt desc ;
2778:
2779: -- checks the eligibility of the student to be moved to the next year of program (unit set)
2780: -- by checking if there is any outcome preventing the progress of the student program attempt
2781: CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
2782: SELECT pou.decision_dt, pout.s_progression_outcome_type
2783: FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
2784: WHERE pou.person_id = p_person_id AND
2785: pou.course_cd = p_course_cd AND
2789: pou.progression_outcome_type = pout.progression_outcome_type
2790: ORDER BY pou.decision_dt desc ;
2791: c_prog_outcome_rec c_prog_outcome%ROWTYPE;
2792:
2793: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
2794: cst_advance CONSTANT VARCHAR2(30) := 'ADVANCE';
2795: cst_repeatyr CONSTANT VARCHAR2(30) := 'REPEATYR';
2796:
2797: BEGIN