[Home] [Help]
25: AS
26:
27: CURSOR usec_upd_enr_act(p_uoo_id NUMBER) IS
28: SELECT ROWID,uoo.*
29: FROM igs_ps_unit_ofr_opt uoo
30: WHERE uoo_id = p_uoo_id
31: FOR UPDATE;
32:
33: usec_row usec_upd_enr_act%ROWTYPE;
38: -- the unit attempt is successful.
39: -- The updation takes place if the parameter upd_act_enr is sent as 'Y'
40: -- For response to bug 152583, a private procedure that updates the row
41: -- and also updaes the value that populate into the actual enrolment field
42: -- of IGS_PS_UNIT_OFR_OPT table. This procedure is called from the
43: -- upd_enrollment_max procedure
44:
45: PROCEDURE local_update_unit_section( p_action IN VARCHAR2,
46: old_references EN_SUA_REC_TYPE%TYPE,
53: Purpose :
54: Know limitations, enhancements or remarks
55: Change History
56: Who When What
57: sommukhe 28-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter abort_flag.
58: stutta 23-Aug-2004 Bug#3803790, passed course_cd as parameter to igs_en_gen_015.get_usec_status
59: sarakshi 13-Jul-2004 Bug#3729462, Added predicate DELETE_FLAG='N' to the cursor c_max_std_per_wait_uofr_pat,c_wait_allow_unit_offering .
60: sarakshi 22-Sep-2003 Enh#3052452, Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include
61: new parameters sup_uoo_id,relation_type,default_enroll_flag.
56: Who When What
57: sommukhe 28-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter abort_flag.
58: stutta 23-Aug-2004 Bug#3803790, passed course_cd as parameter to igs_en_gen_015.get_usec_status
59: sarakshi 13-Jul-2004 Bug#3729462, Added predicate DELETE_FLAG='N' to the cursor c_max_std_per_wait_uofr_pat,c_wait_allow_unit_offering .
60: sarakshi 22-Sep-2003 Enh#3052452, Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include
61: new parameters sup_uoo_id,relation_type,default_enroll_flag.
62: vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the calls to igs_ps_unit_ofr_opt_pkg.update_row to
63: include new parameter not_multiple_section_flag.
64: (reverse chronological order - newest change first)
58: stutta 23-Aug-2004 Bug#3803790, passed course_cd as parameter to igs_en_gen_015.get_usec_status
59: sarakshi 13-Jul-2004 Bug#3729462, Added predicate DELETE_FLAG='N' to the cursor c_max_std_per_wait_uofr_pat,c_wait_allow_unit_offering .
60: sarakshi 22-Sep-2003 Enh#3052452, Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include
61: new parameters sup_uoo_id,relation_type,default_enroll_flag.
62: vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the calls to igs_ps_unit_ofr_opt_pkg.update_row to
63: include new parameter not_multiple_section_flag.
64: (reverse chronological order - newest change first)
65: ***************************************************************/
66:
63: include new parameter not_multiple_section_flag.
64: (reverse chronological order - newest change first)
65: ***************************************************************/
66:
67: CURSOR c_usec_lim (cp_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
68: SELECT NVL (usec.enrollment_maximum, NVL(uv.enrollment_maximum,999999) ) enrollment_maximum
69: FROM igs_ps_usec_lim_wlst usec,
70: igs_ps_unit_ver uv,
71: igs_ps_unit_ofr_opt uoo
67: CURSOR c_usec_lim (cp_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
68: SELECT NVL (usec.enrollment_maximum, NVL(uv.enrollment_maximum,999999) ) enrollment_maximum
69: FROM igs_ps_usec_lim_wlst usec,
70: igs_ps_unit_ver uv,
71: igs_ps_unit_ofr_opt uoo
72: WHERE uoo.unit_cd = uv.unit_cd
73: AND uoo.version_number = uv.version_number
74: AND uoo.uoo_id = usec.uoo_id (+)
75: AND uoo.uoo_id = cp_uoo_id;
76:
77: --
78: -- The following three cursors added as part of the bug 2375362. pmarada
79: -- getting maximum students per waitlist in the unit section level.
80: CURSOR c_max_std_per_wait_usec(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
81: SELECT max_students_per_waitlist FROM igs_ps_usec_lim_wlst_v
82: WHERE uoo_id = cp_uoo_id;
83: l_max_std_per_wait_usec igs_ps_usec_lim_wlst_v.max_students_per_waitlist%TYPE;
84:
82: WHERE uoo_id = cp_uoo_id;
83: l_max_std_per_wait_usec igs_ps_usec_lim_wlst_v.max_students_per_waitlist%TYPE;
84:
85: -- Getting the maximum students per waitlist from unit offering pattern level.
86: CURSOR c_max_std_per_wait_uofr_pat (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
87: cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
88: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
89: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
90: SELECT max_students_per_waitlist FROM igs_ps_unit_ofr_pat
83: l_max_std_per_wait_usec igs_ps_usec_lim_wlst_v.max_students_per_waitlist%TYPE;
84:
85: -- Getting the maximum students per waitlist from unit offering pattern level.
86: CURSOR c_max_std_per_wait_uofr_pat (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
87: cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
88: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
89: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
90: SELECT max_students_per_waitlist FROM igs_ps_unit_ofr_pat
91: WHERE unit_cd = cp_unit_cd
84:
85: -- Getting the maximum students per waitlist from unit offering pattern level.
86: CURSOR c_max_std_per_wait_uofr_pat (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
87: cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
88: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
89: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
90: SELECT max_students_per_waitlist FROM igs_ps_unit_ofr_pat
91: WHERE unit_cd = cp_unit_cd
92: AND version_number = cp_version_number
85: -- Getting the maximum students per waitlist from unit offering pattern level.
86: CURSOR c_max_std_per_wait_uofr_pat (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
87: cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
88: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
89: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
90: SELECT max_students_per_waitlist FROM igs_ps_unit_ofr_pat
91: WHERE unit_cd = cp_unit_cd
92: AND version_number = cp_version_number
93: AND cal_type = cp_cal_type
95: AND delete_flag='N';
96: l_max_std_per_wait_uofr_pat igs_ps_unit_ofr_pat.max_students_per_waitlist%TYPE;
97:
98: -- Getting the maximum students per waitlist from organization level.
99: CURSOR c_max_std_per_wait_org (cp_org_unit_cd igs_ps_unit_ofr_opt.owner_org_unit_cd%TYPE,
100: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
101: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
102: SELECT max_stud_per_wlst FROM igs_en_or_unit_wlst_v
103: WHERE org_unit_cd = cp_org_unit_cd
96: l_max_std_per_wait_uofr_pat igs_ps_unit_ofr_pat.max_students_per_waitlist%TYPE;
97:
98: -- Getting the maximum students per waitlist from organization level.
99: CURSOR c_max_std_per_wait_org (cp_org_unit_cd igs_ps_unit_ofr_opt.owner_org_unit_cd%TYPE,
100: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
101: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
102: SELECT max_stud_per_wlst FROM igs_en_or_unit_wlst_v
103: WHERE org_unit_cd = cp_org_unit_cd
104: AND cal_type = cp_cal_type
97:
98: -- Getting the maximum students per waitlist from organization level.
99: CURSOR c_max_std_per_wait_org (cp_org_unit_cd igs_ps_unit_ofr_opt.owner_org_unit_cd%TYPE,
100: cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
101: cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
102: SELECT max_stud_per_wlst FROM igs_en_or_unit_wlst_v
103: WHERE org_unit_cd = cp_org_unit_cd
104: AND cal_type = cp_cal_type
105: AND sequence_number = cp_ci_sequence_number ;
106: l_max_std_per_wait_org igs_en_or_unit_wlst_v.max_stud_per_wlst%TYPE;
107:
108: -- end of the added three cursors. pmarada
109: -- Cursor to Check if Waitlisting is allowed at the unit section level .
110: CURSOR c_wait_allow_unit_section ( cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
111: SELECT waitlist_allowed
112: FROM IGS_PS_USEC_LIM_WLST
113: WHERE uoo_id = cp_uoo_id ;
114: --
113: WHERE uoo_id = cp_uoo_id ;
114: --
115: -- Cursor Check if Waitlisting is allowed at the unit offering level .
116: --
117: CURSOR c_wait_allow_unit_offering ( cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
118: SELECT waitlist_allowed
119: FROM IGS_PS_UNIT_OFR_PAT
120: WHERE delete_flag='N' AND (unit_cd , version_number , cal_type , ci_sequence_number ) IN
121: (SELECT unit_cd , version_number , cal_type, ci_sequence_number
118: SELECT waitlist_allowed
119: FROM IGS_PS_UNIT_OFR_PAT
120: WHERE delete_flag='N' AND (unit_cd , version_number , cal_type , ci_sequence_number ) IN
121: (SELECT unit_cd , version_number , cal_type, ci_sequence_number
122: FROM igs_ps_unit_ofr_opt
123: WHERE uoo_id = cp_uoo_id);
124:
125: -- Cursor to check whether the unit section belongs to any cross listed group or not.
126: CURSOR c_cross_listed (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
122: FROM igs_ps_unit_ofr_opt
123: WHERE uoo_id = cp_uoo_id);
124:
125: -- Cursor to check whether the unit section belongs to any cross listed group or not.
126: CURSOR c_cross_listed (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
127: SELECT grp.max_enr_group, grpmem.usec_x_listed_group_id
128: FROM igs_ps_usec_x_grpmem grpmem,
129: igs_ps_usec_x_grp grp
130: WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
130: WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
131: AND grpmem.uoo_id = l_uoo_id;
132:
133: -- Cursor to check whether the unit section belongs to any Meet With Class or not.
134: CURSOR c_meet_with_cls (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
135: SELECT grp.max_enr_group, ucm.class_meet_group_id
136: FROM igs_ps_uso_clas_meet ucm,
137: igs_ps_uso_cm_grp grp
138: WHERE grp.class_meet_group_id = ucm.class_meet_group_id
140:
141:
142: -- Cursor to get the Actual enrollment of all the unit sections that
143: -- belong to the class listed group.
144: CURSOR c_actual_enr_crs_lst(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
145: l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
146: SELECT SUM(enrollment_actual)
147: FROM igs_ps_unit_ofr_opt uoo,
148: igs_ps_usec_x_grpmem ugrp
143: -- belong to the class listed group.
144: CURSOR c_actual_enr_crs_lst(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
145: l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
146: SELECT SUM(enrollment_actual)
147: FROM igs_ps_unit_ofr_opt uoo,
148: igs_ps_usec_x_grpmem ugrp
149: WHERE uoo.uoo_id = ugrp.uoo_id
150: AND ugrp.uoo_id <> l_uoo_id
151: AND ugrp.usec_x_listed_group_id = l_usec_x_listed_group_id;
152:
153:
154: -- Cursor to get the Actual enrollment of all the unit sections that
155: -- belong to the Meet With Class.
156: CURSOR c_actual_enr_meet_cls(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
157: l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE) IS
158: SELECT SUM(enrollment_actual)
159: FROM igs_ps_unit_ofr_opt uoo,
160: igs_ps_uso_clas_meet ucls
155: -- belong to the Meet With Class.
156: CURSOR c_actual_enr_meet_cls(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
157: l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE) IS
158: SELECT SUM(enrollment_actual)
159: FROM igs_ps_unit_ofr_opt uoo,
160: igs_ps_uso_clas_meet ucls
161: WHERE uoo.uoo_id = ucls.uoo_id
162: AND ucls.uoo_id <> l_uoo_id
163: AND ucls.class_meet_group_id = l_class_meet_group_id;
163: AND ucls.class_meet_group_id = l_class_meet_group_id;
164:
165:
166: -- Cursor to get the unit section details that belongs to the cross listed group.
167: CURSOR c_cross_lst_details(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
168: l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE,
169: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
170: SELECT uoo.rowid, uoo.*
171: FROM igs_ps_unit_ofr_opt uoo,
165:
166: -- Cursor to get the unit section details that belongs to the cross listed group.
167: CURSOR c_cross_lst_details(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
168: l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE,
169: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
170: SELECT uoo.rowid, uoo.*
171: FROM igs_ps_unit_ofr_opt uoo,
172: igs_ps_usec_x_grpmem ugrp
173: WHERE uoo.uoo_id = ugrp.uoo_id
167: CURSOR c_cross_lst_details(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
168: l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE,
169: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
170: SELECT uoo.rowid, uoo.*
171: FROM igs_ps_unit_ofr_opt uoo,
172: igs_ps_usec_x_grpmem ugrp
173: WHERE uoo.uoo_id = ugrp.uoo_id
174: AND ugrp.uoo_id <> l_uoo_id
175: AND ugrp.usec_x_listed_group_id = l_usec_x_listed_group_id
176: AND uoo.unit_section_status <> l_unit_section_status;
177:
178:
179: -- Cursor to get the unit section details that belongs to the Meet with class.
180: CURSOR c_meet_with_cls_details(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
181: l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE,
182: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
183: SELECT uoo.rowid, uoo.*
184: FROM igs_ps_unit_ofr_opt_all uoo,
178:
179: -- Cursor to get the unit section details that belongs to the Meet with class.
180: CURSOR c_meet_with_cls_details(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
181: l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE,
182: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
183: SELECT uoo.rowid, uoo.*
184: FROM igs_ps_unit_ofr_opt_all uoo,
185: igs_ps_uso_clas_meet ucls
186: WHERE uoo.uoo_id = ucls.uoo_id
180: CURSOR c_meet_with_cls_details(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
181: l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE,
182: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
183: SELECT uoo.rowid, uoo.*
184: FROM igs_ps_unit_ofr_opt_all uoo,
185: igs_ps_uso_clas_meet ucls
186: WHERE uoo.uoo_id = ucls.uoo_id
187: AND ucls.uoo_id <> l_uoo_id
188: AND ucls.class_meet_group_id = l_class_meet_group_id
190:
191:
192: v_enr_max igs_ps_usec_lim_wlst.enrollment_maximum%TYPE;
193: v_max_std_wlst igs_ps_usec_lim_wlst.max_students_per_waitlist%TYPE;
194: lv_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE ;
195: l_waitlist_allowed IGS_PS_UNIT_OFR_PAT.waitlist_allowed%TYPE ;
196: l_cross_listed_row c_cross_listed%ROWTYPE;
197: l_meet_with_cls_row c_meet_with_cls%ROWTYPE;
198: l_usec_partof_group BOOLEAN;
196: l_cross_listed_row c_cross_listed%ROWTYPE;
197: l_meet_with_cls_row c_meet_with_cls%ROWTYPE;
198: l_usec_partof_group BOOLEAN;
199: v_grp_max igs_ps_usec_x_grp.max_enr_group%TYPE;
200: v_grp_actual igs_ps_unit_ofr_opt.enrollment_actual%TYPE;
201: l_setup_found NUMBER;
202: l_dir_enr igs_ps_unit_ofr_opt_all.dir_enrollment%TYPE;
203: l_enr_from_wlst igs_ps_unit_ofr_opt_all.enr_from_wlst%TYPE ;
204:
198: l_usec_partof_group BOOLEAN;
199: v_grp_max igs_ps_usec_x_grp.max_enr_group%TYPE;
200: v_grp_actual igs_ps_unit_ofr_opt.enrollment_actual%TYPE;
201: l_setup_found NUMBER;
202: l_dir_enr igs_ps_unit_ofr_opt_all.dir_enrollment%TYPE;
203: l_enr_from_wlst igs_ps_unit_ofr_opt_all.enr_from_wlst%TYPE ;
204:
205: BEGIN
206: -- In this procedure the enrollment maximum and waitlist maximum are passed
199: v_grp_max igs_ps_usec_x_grp.max_enr_group%TYPE;
200: v_grp_actual igs_ps_unit_ofr_opt.enrollment_actual%TYPE;
201: l_setup_found NUMBER;
202: l_dir_enr igs_ps_unit_ofr_opt_all.dir_enrollment%TYPE;
203: l_enr_from_wlst igs_ps_unit_ofr_opt_all.enr_from_wlst%TYPE ;
204:
205: BEGIN
206: -- In this procedure the enrollment maximum and waitlist maximum are passed
207: -- throught the usec_row record ( after updating the record itself with the
384: l_dir_enr := usec_row.DIR_ENROLLMENT ;
385: END IF;
386: END IF;
387:
388: IGS_PS_UNIT_OFR_OPT_PKG.UPDATE_ROW (
389: X_ROWID => usec_row.ROWID ,
390: x_UNIT_CD => usec_row.UNIT_CD ,
391: x_VERSION_NUMBER => usec_row.VERSION_NUMBER ,
392: x_CAL_TYPE => usec_row.CAL_TYPE ,
440: -- Update the unit sections status that belong to the cross listed group with the derived value.
441: FOR unit_sec in c_cross_lst_details(usec_row.uoo_id, l_cross_listed_row.usec_x_listed_group_id, lv_unit_section_status) LOOP
442:
443: -- Added auditable_ind and audit_permission_ind parameters as part of Bug# 2636716
444: IGS_PS_UNIT_OFR_OPT_PKG.UPDATE_ROW (
445: X_ROWID => unit_sec.ROWID ,
446: x_UNIT_CD => unit_sec.UNIT_CD ,
447: x_VERSION_NUMBER => unit_sec.VERSION_NUMBER ,
448: x_CAL_TYPE => unit_sec.CAL_TYPE ,
497: -- Update the unit sections status that belong to the group with the derived value.
498: FOR usec_meet_with in c_meet_with_cls_details(usec_row.uoo_id, l_meet_with_cls_row.class_meet_group_id, lv_unit_section_status) LOOP
499:
500: -- Added auditable_ind and audit_permission_ind parameters as part of Bug# 2636716
501: IGS_PS_UNIT_OFR_OPT_PKG.UPDATE_ROW (
502: X_ROWID => usec_meet_with.ROWID ,
503: x_UNIT_CD => usec_meet_with.UNIT_CD ,
504: x_VERSION_NUMBER => usec_meet_with.VERSION_NUMBER ,
505: x_CAL_TYPE => usec_meet_with.CAL_TYPE ,
574: AND teach_ci_sequence_number = p_teach_ci_sequence_number
575: ORDER BY LOAD_START_DT ASC;
576:
577: --Cursor to check the maximum audit enrollments for a unit section
578: CURSOR c_max_auditors_allowed(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
579: SELECT max_auditors_allowed
580: FROM igs_ps_usec_lim_wlst
581: WHERE uoo_id = cp_uoo_id;
582: l_max_auditors_allowed igs_ps_usec_lim_wlst.max_auditors_allowed%TYPE;
581: WHERE uoo_id = cp_uoo_id;
582: l_max_auditors_allowed igs_ps_usec_lim_wlst.max_auditors_allowed%TYPE;
583:
584: --Cursor to get the count of audit attempts
585: CURSOR c_audit_attempts_count(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
586: SELECT count(*)
587: FROM igs_en_su_attempt
588: WHERE no_assessment_ind='Y'
589: AND uoo_id = cp_uoo_id
590: AND unit_attempt_status in (cst_enrolled,cst_completed,cst_invalid,cst_unconfirm);
591: l_audit_attempts_count NUMBER;
592:
593: rec_teach_to_load cur_teach_to_load%ROWTYPE;
594: l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE;
595: l_waitlist_ind VARCHAR2(10);
596:
597: -- cursor that gets version number given person_id and course_cd
598: CURSOR cur_get_prog_ver(cp_person_id igs_en_su_attempt.person_id%TYPE,
670: FROM igs_En_or_unit_wlst
671: WHERE cal_type = cp_load_cal_type AND
672: closed_flag = 'N' AND
673: org_unit_cd = (SELECT nvl(uoo.owner_org_unit_Cd, uv.owner_org_unit_cd)
674: FROM igs_ps_unit_ofr_opt uoo,
675: igs_ps_unit_ver uv
676: WHERE uoo.uoo_id = cp_uoo_id AND
677: uv.unit_cd = uoo.unit_cd AND
678: uv.version_number = uoo.version_number);
1317: (reverse chronological order - newest change first)
1318: ***************************************************************/
1319: CURSOR cur_rel_type IS
1320: SELECT relation_type
1321: FROM igs_ps_unit_ofr_opt
1322: WHERE uoo_id = p_uoo_Id;
1323:
1324: l_rel_type igs_ps_unit_ofr_opt.relation_type%TYPE;
1325:
1320: SELECT relation_type
1321: FROM igs_ps_unit_ofr_opt
1322: WHERE uoo_id = p_uoo_Id;
1323:
1324: l_rel_type igs_ps_unit_ofr_opt.relation_type%TYPE;
1325:
1326: l_sub_uoo igs_ps_unit_ofr_opt.uoo_id%TYPE;
1327:
1328: CURSOR cur_sub_uoo IS
1322: WHERE uoo_id = p_uoo_Id;
1323:
1324: l_rel_type igs_ps_unit_ofr_opt.relation_type%TYPE;
1325:
1326: l_sub_uoo igs_ps_unit_ofr_opt.uoo_id%TYPE;
1327:
1328: CURSOR cur_sub_uoo IS
1329: SELECT sua.uoo_id sub_uoo_id, sua.unit_attempt_status sub_uoo_status
1330: FROM igs_en_su_attempt sua, igs_ps_unit_ofr_opt uoo
1326: l_sub_uoo igs_ps_unit_ofr_opt.uoo_id%TYPE;
1327:
1328: CURSOR cur_sub_uoo IS
1329: SELECT sua.uoo_id sub_uoo_id, sua.unit_attempt_status sub_uoo_status
1330: FROM igs_en_su_attempt sua, igs_ps_unit_ofr_opt uoo
1331: WHERE uoo.sup_uoo_id = p_uoo_id
1332: AND uoo.relation_type = 'SUBORDINATE'
1333: AND sua.uoo_id = uoo.uoo_id
1334: AND sua.person_id = p_person_id
1338: l_sub_uoo_status igs_en_su_attempt.unit_attempt_status%TYPE;
1339:
1340: CURSOR cur_sup_uoo IS
1341: SELECT uoo.sup_uoo_id sup_uoo_id, sua.unit_attempt_status sup_uoo_status
1342: FROM igs_en_su_attempt sua, igs_ps_unit_ofr_opt uoo
1343: WHERE uoo.uoo_id = p_uoo_id
1344: AND uoo.sup_uoo_id = sua.uoo_id
1345: AND sua.person_id = p_person_id
1346: AND sua.course_cd = p_course_cd;
1344: AND uoo.sup_uoo_id = sua.uoo_id
1345: AND sua.person_id = p_person_id
1346: AND sua.course_cd = p_course_cd;
1347:
1348: l_sup_uoo_Id igs_ps_unit_ofr_opt.uoo_id%TYPE;
1349: l_sup_attempt_status igs_en_su_attempt.unit_attempt_status%TYPE;
1350:
1351:
1352: BEGIN
1526: */
1527:
1528: CURSOR cur_chk_sup IS
1529: SELECT relation_type
1530: FROM igs_ps_unit_ofr_opt
1531: WHERE uoo_id = p_uoo_Id;
1532:
1533: CURSOR cur_sub IS
1534: SELECT sua.unit_attempt_status
1531: WHERE uoo_id = p_uoo_Id;
1532:
1533: CURSOR cur_sub IS
1534: SELECT sua.unit_attempt_status
1535: FROM igs_ps_unit_ofr_opt uoo,
1536: igs_en_su_attempt sua
1537: WHERE uoo.sup_uoo_id = p_uoo_id
1538: AND uoo.relation_type = 'SUPERIOR'
1539: AND sua.uoo_id = uoo.uoo_id
1541: AND sua.course_cd = p_course_cd
1542: AND sua.unit_attempt_status NOT IN ('DROPPED','DUPLICATE');
1543:
1544: v_sub_status igs_en_su_attempt.unit_attempt_status%TYPE;
1545: v_sup igs_ps_unit_ofr_opt.relation_type%TYPE;
1546:
1547: BEGIN
1548:
1549: OPEN cur_chk_sup;