[Home] [Help]
3:
4: /*** Enrolling Persons - Called in loop for Validated filtered student PL/SQL tables **/
5: PROCEDURE Enroll_Persons(p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
6: p_person_id igs_pe_person.person_id%TYPE,
7: p_course_cd igs_en_su_attempt.course_cd%TYPE,
8: p_waitlist_actual IN OUT NOCOPY igs_ps_unit_ofr_opt.waitlist_actual%TYPE,
9: p_enrollment_actual IN OUT NOCOPY igs_ps_unit_ofr_opt.enrollment_actual%TYPE,
10: p_max_quota IN igs_ps_usec_lim_wlst.enrollment_maximum%TYPE,
11: p_max_stdnts_per_wlst igs_ps_usec_lim_wlst.max_students_per_waitlist%TYPE,
9: p_enrollment_actual IN OUT NOCOPY igs_ps_unit_ofr_opt.enrollment_actual%TYPE,
10: p_max_quota IN igs_ps_usec_lim_wlst.enrollment_maximum%TYPE,
11: p_max_stdnts_per_wlst igs_ps_usec_lim_wlst.max_students_per_waitlist%TYPE,
12: p_enrolled_yn OUT NOCOPY VARCHAR2 ,
13: p_unit_cd igs_en_su_attempt.unit_cd%TYPE,
14: p_version_number igs_en_su_attempt.version_number%TYPE,
15: p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
16: ) AS
17:
10: p_max_quota IN igs_ps_usec_lim_wlst.enrollment_maximum%TYPE,
11: p_max_stdnts_per_wlst igs_ps_usec_lim_wlst.max_students_per_waitlist%TYPE,
12: p_enrolled_yn OUT NOCOPY VARCHAR2 ,
13: p_unit_cd igs_en_su_attempt.unit_cd%TYPE,
14: p_version_number igs_en_su_attempt.version_number%TYPE,
15: p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
16: ) AS
17:
18:
39:
40: CURSOR c_sua
41: IS
42: SELECT sua.ROWID, sua.*
43: FROM IGS_EN_SU_ATTEMPT sua
44: WHERE uoo_id=p_uoo_id
45: AND unit_attempt_status = 'WAITLISTED'
46: AND person_id = p_person_id
47: AND course_cd = p_course_cd;
235: p_version_number IN igs_ps_unit_ofr_opt.version_number%TYPE,
236: p_cal_type IN igs_ps_unit_ofr_opt.cal_type%TYPE,
237: p_ci_sequence_number IN igs_ps_unit_ofr_opt.ci_sequence_number%TYPE,
238: p_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
239: p_person_id IN igs_en_su_attempt.person_id%TYPE,
240: p_unit_class IN igs_ps_unit_ofr_opt.unit_class%TYPE,
241: p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
242: p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE,
243: p_deny_warn OUT NOCOPY VARCHAR2,
240: p_unit_class IN igs_ps_unit_ofr_opt.unit_class%TYPE,
241: p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
242: p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE,
243: p_deny_warn OUT NOCOPY VARCHAR2,
244: p_course_cd IN igs_en_su_attempt.course_cd%TYPE)
245: RETURN BOOLEAN AS
246: /******************************************************************
247: Created By :Syam
248: Date Created By :
256: sarakshi 24-Feb-2003 Enh#2797116,modified cursor igs_ps_ofr_opt_cur to include delete_flag in
257: the where clause
258: ayedubat 30-MAY-2002 Added a new parameter,p_message_name to the Function:Enrp_Get_Rec_Window
259: call and dsiplaying the returning message for the bug fix:2337161.
260: kkillams 28-04-2003 modified igs_en_su_attempt_cur cursor where clause and impacted object,
261: due to change in Igs_En_Val_Sua.enrp_val_sua_dupl function signature
262: w.r.t. bug number 2829262
263: kkillams 19-June-03 Modified the validate_unit to add additional parameter p_course_cd, as existing cursor may fetch more than one program
264: and also modified the cursor igs_en_su_attempt_cur for bug 2937182
260: kkillams 28-04-2003 modified igs_en_su_attempt_cur cursor where clause and impacted object,
261: due to change in Igs_En_Val_Sua.enrp_val_sua_dupl function signature
262: w.r.t. bug number 2829262
263: kkillams 19-June-03 Modified the validate_unit to add additional parameter p_course_cd, as existing cursor may fetch more than one program
264: and also modified the cursor igs_en_su_attempt_cur for bug 2937182
265: ******************************************************************/
266:
267: CURSOR igs_en_su_attempt_cur(cp_person_id igs_en_su_attempt.person_id%TYPE,
268: cp_course_cd igs_en_su_attempt.course_cd%TYPE,
263: kkillams 19-June-03 Modified the validate_unit to add additional parameter p_course_cd, as existing cursor may fetch more than one program
264: and also modified the cursor igs_en_su_attempt_cur for bug 2937182
265: ******************************************************************/
266:
267: CURSOR igs_en_su_attempt_cur(cp_person_id igs_en_su_attempt.person_id%TYPE,
268: cp_course_cd igs_en_su_attempt.course_cd%TYPE,
269: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
270: IS
271: SELECT unit_attempt_status
264: and also modified the cursor igs_en_su_attempt_cur for bug 2937182
265: ******************************************************************/
266:
267: CURSOR igs_en_su_attempt_cur(cp_person_id igs_en_su_attempt.person_id%TYPE,
268: cp_course_cd igs_en_su_attempt.course_cd%TYPE,
269: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
270: IS
271: SELECT unit_attempt_status
272: FROM IGS_EN_SU_ATTEMPT
265: ******************************************************************/
266:
267: CURSOR igs_en_su_attempt_cur(cp_person_id igs_en_su_attempt.person_id%TYPE,
268: cp_course_cd igs_en_su_attempt.course_cd%TYPE,
269: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
270: IS
271: SELECT unit_attempt_status
272: FROM IGS_EN_SU_ATTEMPT
273: WHERE person_id = cp_person_id
268: cp_course_cd igs_en_su_attempt.course_cd%TYPE,
269: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
270: IS
271: SELECT unit_attempt_status
272: FROM IGS_EN_SU_ATTEMPT
273: WHERE person_id = cp_person_id
274: AND course_cd = cp_course_cd
275: AND uoo_id = cp_uoo_id;
276:
305: AND delete_flag = 'N';
306:
307: igs_ps_ofr_opt_cur_rec igs_ps_ofr_opt_cur%ROWTYPE;
308:
309: v_duplicate_course_cd igs_en_su_attempt.course_cd%TYPE;
310:
311: BEGIN
312:
313: FOR igs_en_su_attempt_cur_rec IN igs_en_su_attempt_cur(p_person_id,p_course_cd,p_uoo_id) LOOP
309: v_duplicate_course_cd igs_en_su_attempt.course_cd%TYPE;
310:
311: BEGIN
312:
313: FOR igs_en_su_attempt_cur_rec IN igs_en_su_attempt_cur(p_person_id,p_course_cd,p_uoo_id) LOOP
314:
315: FOR igs_en_stdnt_ps_att_cur_rec IN igs_en_stdnt_ps_att_cur(p_course_cd,
316: p_person_id) LOOP
317:
409: p_unit_cd,
410: p_version_number,
411: p_cal_type,
412: p_ci_sequence_number,
413: IGS_EN_SU_ATTEMPT_cur_rec.unit_attempt_status,
414: v_duplicate_course_cd,
415: p_message_name,
416: p_uoo_id) THEN
417: p_deny_warn := 'DENY';
436: END validate_unit;
437:
438: /* Function to validate programs-New Validations - */
439:
440: FUNCTION validate_prog (p_person_id igs_en_su_attempt.person_id%TYPE,
441: p_cal_type igs_ca_inst.cal_type%TYPE, --load calendar
442: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, --load calendar
443: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
444: p_course_cd igs_en_su_attempt.course_cd%TYPE,
440: FUNCTION validate_prog (p_person_id igs_en_su_attempt.person_id%TYPE,
441: p_cal_type igs_ca_inst.cal_type%TYPE, --load calendar
442: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, --load calendar
443: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
444: p_course_cd igs_en_su_attempt.course_cd%TYPE,
445: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
446: p_message_name OUT NOCOPY VARCHAR2,
447: p_deny_warn OUT NOCOPY VARCHAR2)
448: RETURN BOOLEAN AS
441: p_cal_type igs_ca_inst.cal_type%TYPE, --load calendar
442: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, --load calendar
443: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
444: p_course_cd igs_en_su_attempt.course_cd%TYPE,
445: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
446: p_message_name OUT NOCOPY VARCHAR2,
447: p_deny_warn OUT NOCOPY VARCHAR2)
448: RETURN BOOLEAN AS
449:
564: END IF;
565:
566: END validate_prog;
567:
568: FUNCTION validate_unit_steps (p_person_id igs_en_su_attempt.person_id%TYPE,
569: p_cal_type igs_ca_inst.cal_type%TYPE,
570: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
571: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
572: p_course_cd igs_en_su_attempt.course_cd%TYPE,
568: FUNCTION validate_unit_steps (p_person_id igs_en_su_attempt.person_id%TYPE,
569: p_cal_type igs_ca_inst.cal_type%TYPE,
570: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
571: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
572: p_course_cd igs_en_su_attempt.course_cd%TYPE,
573: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
574: p_message_name OUT NOCOPY VARCHAR2,
575: p_deny_warn OUT NOCOPY VARCHAR2,
576: p_calling_obj IN VARCHAR2
569: p_cal_type igs_ca_inst.cal_type%TYPE,
570: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
571: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
572: p_course_cd igs_en_su_attempt.course_cd%TYPE,
573: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
574: p_message_name OUT NOCOPY VARCHAR2,
575: p_deny_warn OUT NOCOPY VARCHAR2,
576: p_calling_obj IN VARCHAR2
577: )
691: END validate_unit_steps;
692:
693: /**** Main function for validating prg , unit , unit steps ***/
694:
695: FUNCTION finalize_unit (p_person_id igs_en_su_attempt.person_id%TYPE,
696: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
697: p_called_from_wlst VARCHAR2,
698: p_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
699: p_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
700: p_cal_type igs_ca_inst.cal_type%TYPE,
701: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
702: p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
703: p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
704: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
705: p_course_cd igs_en_su_attempt.course_cd%TYPE,
706: p_rsv_seat_ext_id igs_en_su_attempt.rsv_seat_ext_id%TYPE,
707: p_message_name OUT NOCOPY VARCHAR2)
708:
701: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
702: p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
703: p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
704: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
705: p_course_cd igs_en_su_attempt.course_cd%TYPE,
706: p_rsv_seat_ext_id igs_en_su_attempt.rsv_seat_ext_id%TYPE,
707: p_message_name OUT NOCOPY VARCHAR2)
708:
709:
702: p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
703: p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
704: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
705: p_course_cd igs_en_su_attempt.course_cd%TYPE,
706: p_rsv_seat_ext_id igs_en_su_attempt.rsv_seat_ext_id%TYPE,
707: p_message_name OUT NOCOPY VARCHAR2)
708:
709:
710: RETURN BOOLEAN AS
770: v_rsv_ext_rec c_rsv_ext%ROWTYPE;
771:
772: /** Cursor for UPDATE actual enrolment in igs_ps_rsv_ext **/
773:
774: /** Cursor for Delete record from igs_en_su_attempt**/
775:
776: CURSOR c_del_sua IS
777: SELECT ROWID
778: FROM igs_en_su_attempt
774: /** Cursor for Delete record from igs_en_su_attempt**/
775:
776: CURSOR c_del_sua IS
777: SELECT ROWID
778: FROM igs_en_su_attempt
779: WHERE uoo_id = p_uoo_id
780: AND person_id = p_person_id
781: AND course_cd = p_course_cd;
782:
781: AND course_cd = p_course_cd;
782:
783: v_del_sua_rec c_del_sua%ROWTYPE;
784:
785: /** Cursor for Delete record from igs_en_su_attempt**/
786:
787: CURSOR c_find_person_no IS
788: SELECT party_number
789: FROM hz_parties
1193: END;
1194: /*** UPDATE for IGS_PS_RSV_EXT -For actual seat enrolled - 1 **/
1195:
1196:
1197: /**** DELETE from igs_en_su_attempt for uoo_id***/
1198:
1199: OPEN c_del_sua ;
1200: FETCH c_del_sua INTO v_del_sua_rec;
1201: CLOSE c_del_sua;
1200: FETCH c_del_sua INTO v_del_sua_rec;
1201: CLOSE c_del_sua;
1202:
1203: BEGIN
1204: Igs_En_Su_Attempt_Pkg.delete_row(v_del_sua_rec.ROWID);
1205: END;
1206:
1207: /**** DELETE from igs_en_su_attempt for uoo_id***/
1208:
1203: BEGIN
1204: Igs_En_Su_Attempt_Pkg.delete_row(v_del_sua_rec.ROWID);
1205: END;
1206:
1207: /**** DELETE from igs_en_su_attempt for uoo_id***/
1208:
1209: /*** update igs_ps_unit_ofr inq_not_wlst by 1 **/
1210:
1211: OPEN c_unit_ofr_opt;
1302:
1303:
1304: /** For Combined validations for Unit Steps and Units - Not used in this package for external use**/
1305: FUNCTION validate_combined_unit (
1306: p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1307: p_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
1308: p_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
1309: p_cal_type igs_ca_inst.cal_type%TYPE, -- load calendar
1310: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, -- load calendar
1310: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, -- load calendar
1311: p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
1312: p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
1313: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
1314: p_course_cd igs_en_su_attempt.course_cd%TYPE,
1315: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
1316: p_message_name OUT NOCOPY VARCHAR2,
1317: p_deny_warn OUT NOCOPY VARCHAR2,
1318: p_calling_obj IN VARCHAR2
1311: p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
1312: p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
1313: p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
1314: p_course_cd igs_en_su_attempt.course_cd%TYPE,
1315: p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
1316: p_message_name OUT NOCOPY VARCHAR2,
1317: p_deny_warn OUT NOCOPY VARCHAR2,
1318: p_calling_obj IN VARCHAR2
1319: )
1485: administrative_priority,
1486: waitlist_dt,
1487: rsv_seat_ext_id,
1488: enr_method_type
1489: FROM igs_en_su_attempt
1490: WHERE uoo_id=p_uoo_id
1491: AND NVL(administrative_priority,0) <> 0
1492: AND unit_attempt_status = 'WAITLISTED'
1493: ORDER BY administrative_priority;
1935:
1936: END get_message_count;
1937:
1938: PROCEDURE ss_eval_min_or_max_cp(
1939: p_person_id IN igs_en_su_attempt.person_id%TYPE,
1940: p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
1941: p_load_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1942: p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
1943: p_program_cd IN igs_en_su_attempt.course_cd%TYPE,
1939: p_person_id IN igs_en_su_attempt.person_id%TYPE,
1940: p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
1941: p_load_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1942: p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
1943: p_program_cd IN igs_en_su_attempt.course_cd%TYPE,
1944: p_step_type IN igs_en_cpd_ext.s_enrolment_step_type%TYPE,
1945: p_credit_points IN NUMBER,
1946: p_message_name OUT NOCOPY VARCHAR2,
1947: p_deny_warn OUT NOCOPY VARCHAR2,