[Home] [Help]
538:
539: -- get the currently active unit set for the person course attempt
540: CURSOR c_active_us IS
541: SELECT susa.*
542: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
543: WHERE susa.person_id = p_person_id AND
544: susa.course_cd = p_course_cd AND
545: susa.selection_dt IS NOT NULL AND
546: susa.end_dt IS NULL AND
561: c_next_us_rec c_next_us%ROWTYPE;
562:
563: CURSOR c_us_version_number(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
564: cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
565: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
566: SELECT coous.us_version_number
567: FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
568: WHERE sca.person_id = cp_person_id AND
569: sca.course_cd = cp_course_cd AND
563: CURSOR c_us_version_number(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
564: cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
565: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
566: SELECT coous.us_version_number
567: FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
568: WHERE sca.person_id = cp_person_id AND
569: sca.course_cd = cp_course_cd AND
570: sca.coo_id = coous.coo_id AND
571: coous.unit_set_cd = cp_unit_set_cd AND
571: coous.unit_set_cd = cp_unit_set_cd AND
572: coous.expiry_dt IS NULL AND
573: coous.unit_set_status = uss.unit_set_status AND
574: uss.s_unit_set_status = 'ACTIVE' ;
575: next_us_version igs_en_unit_set.version_number%TYPE;
576:
577: -- Modified the cursor to select always the Unit Set with mapping sequence number of '1'
578: -- not the first unit set in mapping by ayedubat as part of the bug fix:2348709
579: CURSOR c_first_us IS
577: -- Modified the cursor to select always the Unit Set with mapping sequence number of '1'
578: -- not the first unit set in mapping by ayedubat as part of the bug fix:2348709
579: CURSOR c_first_us IS
580: SELECT coou.unit_set_cd, coou.us_version_number
581: FROM igs_ps_ofr_opt_unit_set_v coou , igs_en_unit_set_cat usc
582: WHERE coou.course_cd = p_course_cd AND
583: coou.crv_version_number = v_acaiv_rec.crv_version_number AND
584: coou.cal_type = v_acaiv_rec.acad_cal_type AND
585: coou.location_cd = v_acaiv_rec.location_cd AND
592: WHERE a.sequence_no = 1 );
593:
594: l_first_us c_first_us%ROWTYPE ;
595: l_us_cat VARCHAR2(1);
596: v_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE;
597: l_rowid VARCHAR2(25);
598:
599: CURSOR c_chk_census_dt(cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
600: SELECT sua.*
595: l_us_cat VARCHAR2(1);
596: v_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE;
597: l_rowid VARCHAR2(25);
598:
599: CURSOR c_chk_census_dt(cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
600: SELECT sua.*
601: FROM igs_en_sua_year_v sua
602: WHERE sua.person_id = p_person_id AND
603: sua.course_cd = p_course_cd AND
655: l_load_seq_num igs_ca_inst.sequence_number%TYPE;
656: l_enr_method igs_en_method_type.enr_method_type%TYPE;
657: l_return_status VARCHAR2(20);
658: l_dummy_mesg VARCHAR2(100);
659: FUNCTION prenrl_year (cp_unit_set_cd IN igs_en_unit_set.unit_set_cd%TYPE)
660: RETURN BOOLEAN AS
661: BEGIN
662: DECLARE
663: CURSOR c_us_cat IS
661: BEGIN
662: DECLARE
663: CURSOR c_us_cat IS
664: SELECT 'X'
665: FROM igs_en_unit_set us , igs_en_unit_set_cat usc
666: WHERE us.unit_set_cd = cp_unit_set_cd AND
667: us.unit_set_cat = usc.unit_set_cat AND
668: usc.s_unit_set_cat = 'PRENRL_YR' ;
669: l_us_cat c_us_cat%ROWTYPE ;
2126: susa.us_version_number = v_acaiv_rec.us_version_number AND
2127: susa.end_dt IS NULL;
2128:
2129: -- Bug#2347141
2130: -- This cursor got modified to to include one more table IGS_EN_UNIT_SET_CAT
2131: -- in the from clause to check the System Category.
2132: CURSOR c_us (cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
2133: cp_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
2134: SELECT us.authorisation_rqrd_ind,
2128:
2129: -- Bug#2347141
2130: -- This cursor got modified to to include one more table IGS_EN_UNIT_SET_CAT
2131: -- in the from clause to check the System Category.
2132: CURSOR c_us (cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
2133: cp_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
2134: SELECT us.authorisation_rqrd_ind,
2135: usc.s_unit_set_cat
2136: FROM IGS_EN_UNIT_SET us,
2129: -- Bug#2347141
2130: -- This cursor got modified to to include one more table IGS_EN_UNIT_SET_CAT
2131: -- in the from clause to check the System Category.
2132: CURSOR c_us (cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
2133: cp_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
2134: SELECT us.authorisation_rqrd_ind,
2135: usc.s_unit_set_cat
2136: FROM IGS_EN_UNIT_SET us,
2137: IGS_EN_UNIT_SET_CAT usc
2132: CURSOR c_us (cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
2133: cp_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
2134: SELECT us.authorisation_rqrd_ind,
2135: usc.s_unit_set_cat
2136: FROM IGS_EN_UNIT_SET us,
2137: IGS_EN_UNIT_SET_CAT usc
2138: WHERE us.unit_set_cd = cp_unit_set_cd AND
2139: us.version_number = cp_version_number AND
2140: usc.unit_set_cat = us.unit_set_cat;
2133: cp_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
2134: SELECT us.authorisation_rqrd_ind,
2135: usc.s_unit_set_cat
2136: FROM IGS_EN_UNIT_SET us,
2137: IGS_EN_UNIT_SET_CAT usc
2138: WHERE us.unit_set_cd = cp_unit_set_cd AND
2139: us.version_number = cp_version_number AND
2140: usc.unit_set_cat = us.unit_set_cat;
2141:
3187: App_Exception.Raise_Exception;
3188: END enrpl_copy_param_sua;
3189:
3190: PROCEDURE enrpl_create_pos_sua (
3191: p_unit_set_cd IN igs_en_unit_set.unit_set_cd%TYPE,
3192: p_warn_level OUT NOCOPY VARCHAR2,
3193: p_message_name OUT NOCOPY VARCHAR2,
3194: p_enr_method IN VARCHAR2,
3195: p_lload_cal_type IN VARCHAR2,
4259:
4260: -- get the currently active unit set for the person course attempt
4261: CURSOR c_active_us IS
4262: SELECT susa.*
4263: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
4264: WHERE susa.person_id = p_person_id AND
4265: susa.course_cd = p_course_cd AND
4266: susa.selection_dt IS NOT NULL AND
4267: susa.end_dt IS NULL AND
4282: c_next_us_rec c_next_us%ROWTYPE;
4283:
4284: CURSOR c_us_version_number(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
4285: cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
4286: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
4287: SELECT coous.us_version_number
4288: FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
4289: WHERE sca.person_id = cp_person_id AND
4290: sca.course_cd = cp_course_cd AND
4284: CURSOR c_us_version_number(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
4285: cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
4286: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
4287: SELECT coous.us_version_number
4288: FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
4289: WHERE sca.person_id = cp_person_id AND
4290: sca.course_cd = cp_course_cd AND
4291: sca.coo_id = coous.coo_id AND
4292: coous.unit_set_cd = cp_unit_set_cd AND
4292: coous.unit_set_cd = cp_unit_set_cd AND
4293: coous.expiry_dt IS NULL AND
4294: coous.unit_set_status = uss.unit_set_status AND
4295: uss.s_unit_set_status = 'ACTIVE' ;
4296: next_us_version igs_en_unit_set.version_number%TYPE;
4297:
4298: -- find the last active unit set for the person program
4299: CURSOR c_last_us IS
4300: SELECT susa.unit_set_cd, susa.us_version_number ,susa.sequence_number ,susa.rqrmnts_complete_dt
4298: -- find the last active unit set for the person program
4299: CURSOR c_last_us IS
4300: SELECT susa.unit_set_cd, susa.us_version_number ,susa.sequence_number ,susa.rqrmnts_complete_dt
4301: , susa.selection_dt
4302: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
4303: WHERE susa.person_id = p_person_id AND
4304: susa.course_cd = p_course_cd AND
4305: susa.rqrmnts_complete_dt IS NOT NULL AND
4306: susa.unit_set_cd = us.unit_set_cd AND
4308: usc.s_unit_set_cat = 'PRENRL_YR'
4309: ORDER BY susa.rqrmnts_complete_dt desc ;
4310: l_last_us c_last_us%ROWTYPE ;
4311:
4312: CURSOR c_chk_census_dt(cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
4313: SELECT sua.*
4314: FROM igs_en_sua_year_v sua
4315: WHERE sua.person_id = p_person_id AND
4316: sua.course_cd = p_course_cd AND
6909: v_message_name,
6910: v_message_text);
6911: -- Check v_ret
6912: IF v_ret = FALSE THEN
6913: IF v_message_name is not null and v_message_name <> 'IGS_EN_UNIT_SET_NOT_PARENT_EX' THEN
6914: p_message_name := v_message_name;
6915: RETURN FALSE;
6916: END IF;
6917: END IF;
7942: -- false log entry is made and the insert or update is not carried out for bug 3149133.
7943: ------------------------------------------------------------------------------------------------
7944: CURSOR c_acad_us (cp_new_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
7945: SELECT usm.stream_unit_set_Cd
7946: FROM igs_en_unit_set_map usm,
7947: igs_ps_us_prenr_cfg upc
7948: WHERE upc.unit_set_cd = cp_new_admin_unit_set_cd
7949: AND usm.mapping_set_cd = upc.mapping_set_cd
7950: AND usm.sequence_no = upc.sequence_no;
7950: AND usm.sequence_no = upc.sequence_no;
7951:
7952: CURSOR c_us_version_number(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
7953: cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
7954: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
7955: SELECT coous.us_version_number
7956: FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
7957: WHERE sca.person_id = cp_person_id AND
7958: sca.course_cd = cp_course_cd AND
7952: CURSOR c_us_version_number(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
7953: cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
7954: cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
7955: SELECT coous.us_version_number
7956: FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
7957: WHERE sca.person_id = cp_person_id AND
7958: sca.course_cd = cp_course_cd AND
7959: sca.coo_id = coous.coo_id AND
7960: coous.unit_set_cd = cp_unit_set_cd AND
7975: AND susa.end_dt IS NULL
7976: ORDER BY susa.selection_dt desc;
7977:
7978: vc_susa_upd_rec c_susa_exists%ROWTYPE;
7979: v_us_version_number igs_en_unit_set.version_number%TYPE;
7980: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
7981: vl_sequence_val igs_as_su_setatmpt.SEQUENCE_NUMBER%TYPE;
7982: vl_rowid VARCHAR2(25);
7983: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
8445: ------------------------------------------------------------------------------------------------
8446:
8447: CURSOR c_acad_us (cp_old_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
8448: SELECT usm.stream_unit_set_Cd
8449: FROM igs_en_unit_set_map usm,
8450: igs_ps_us_prenr_cfg upc
8451: WHERE upc.unit_set_cd = cp_old_admin_unit_set_cd
8452: AND usm.mapping_set_cd = upc.mapping_set_cd
8453: AND usm.sequence_no = upc.sequence_no;