1 PACKAGE BODY IGS_EN_SUA_API AS
2 /* $Header: IGSENA0B.pls 120.5 2006/04/26 03:26:26 bdeviset ship $ */
3 cst_completed CONSTANT VARCHAR2(30) := 'COMPLETED';
4 cst_discontin CONSTANT VARCHAR2(30) := 'DISCONTIN';
5 cst_dropped CONSTANT VARCHAR2(30) := 'DROPPED';
6 cst_duplicate CONSTANT VARCHAR2(30) := 'DUPLICATE';
7 cst_enrolled CONSTANT VARCHAR2(30) := 'ENROLLED';
8 cst_invalid CONSTANT VARCHAR2(30) := 'INVALID';
9 cst_unconfirm CONSTANT VARCHAR2(30) := 'UNCONFIRM';
10 cst_waitlisted CONSTANT VARCHAR2(30) := 'WAITLISTED';
11
12 l_rowid VARCHAR2(25);
13
14 CURSOR c_sua (CP_ROW_ID VARCHAR2) IS
15 SELECT sua.*
16 FROM IGS_EN_SU_ATTEMPT_ALL sua
17 WHERE ROWID = CP_ROW_ID;
18
19
20 -- For Enhancement Bug 1287292
21 -- Local Procedure added to update enrollment maximum
22 PROCEDURE upd_enrollment_counts( p_action IN VARCHAR2,
23 old_references EN_SUA_REC_TYPE%TYPE,
24 new_references EN_SUA_REC_TYPE%TYPE)
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;
34
35 -- For Enhancement Bug 1287292
36 -- Local Procedure added to update enrollment maximum
37 -- The actual enrollment in the unit section is being updated by 1 when
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,
47 new_references EN_SUA_REC_TYPE%TYPE,
48 usec_row usec_upd_enr_act%ROWTYPE
49 ) AS
50 /*************************************************************
51 Created By :
52 Date Created By :
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.
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
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
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
94 AND ci_sequence_number = cp_ci_sequence_number
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
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 --
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
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
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
139 AND ucm.uoo_id = l_uoo_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
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
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;
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,
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,
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
189 AND uoo.unit_section_status <> l_unit_section_status;
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;
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
208 -- new values) from upd_enrollment_max. Which in turn is called in the after dml
209 -- The code in this procudure calculate the unit section status and updates
210 -- the same in to the unit section table.
211 -- amuthu 09-AUG-2001 Enroll Process DLD
212 --
213 -- svenkata 16-Apr-02 Validations for changing Unit Section Status have been modified so that
214 -- they incorporate all checks before changing status .Bug # 2318942.
215 --
216
217 l_usec_partof_group := FALSE;
218 l_setup_found := 0;
219
220 -- Check whether the unit section belongs to any cross listed group.
221 OPEN c_cross_listed(usec_row.uoo_id);
222 FETCH c_cross_listed INTO l_cross_listed_row ;
223
224 IF c_cross_listed%FOUND THEN
225 -- Get the maximum enrollment limit from the cross listed group level.
226 IF l_cross_listed_row.max_enr_group IS NULL THEN
227 l_usec_partof_group := FALSE;
228 ELSE
229 l_usec_partof_group := TRUE;
230 l_setup_found := 1;
231 v_grp_max := l_cross_listed_row.max_enr_group;
232
233 -- Get the actual enrollment count of all the unit sections that belongs to the cross listed group.
234 OPEN c_actual_enr_crs_lst(usec_row.uoo_id, l_cross_listed_row.usec_x_listed_group_id);
235 FETCH c_actual_enr_crs_lst INTO v_grp_actual;
236 CLOSE c_actual_enr_crs_lst;
237 END IF;
238
239 ELSE
240
241 -- Check whether the unit section belongs to any meet with class group.
242 OPEN c_meet_with_cls(usec_row.uoo_id);
243 FETCH c_meet_with_cls INTO l_meet_with_cls_row ;
244
245 IF c_meet_with_cls%FOUND THEN
246 -- Check whether the maximum enrollment limit is defined in group level.
247 IF l_meet_with_cls_row.max_enr_group IS NULL THEN
248 l_usec_partof_group := FALSE;
249 ELSE
250 l_usec_partof_group := TRUE;
251 l_setup_found := 2;
252 v_grp_max := l_meet_with_cls_row.max_enr_group;
253 -- Get the actual enrollment count of all the unit sections that belongs to
254 -- the meet with class group.
255 OPEN c_actual_enr_meet_cls(usec_row.uoo_id, l_meet_with_cls_row.class_meet_group_id);
256 FETCH c_actual_enr_meet_cls INTO v_grp_actual;
257 CLOSE c_actual_enr_meet_cls;
258 END IF;
259
260 ELSE
261 l_usec_partof_group := FALSE;
262 END IF;
263 CLOSE c_meet_with_cls;
264 END IF;
265 CLOSE c_cross_listed;
266
267 -- Setup is not done in the group level, so get the details from Unit Section / Unit level.
268 IF l_usec_partof_group = FALSE THEN
269
270 OPEN c_usec_lim(usec_row.uoo_id);
271 FETCH c_usec_lim INTO v_enr_max;
272 CLOSE c_usec_lim;
273
274 --
275 -- At the lowest level , waitlist allowed can be set at the Unit Section level . First check if waitlist has been
276 -- allowed at Unit Section Level . If waitlisting is not allowed , then check at the next level - Unit Offering.
277 -- If waitlisting is permitted at the Unit Offering level , return p_waitlist_ind = 'Y'
278 --
279 OPEN c_wait_allow_unit_section(usec_row.uoo_id) ;
280 FETCH c_wait_allow_unit_section INTO l_waitlist_allowed ;
281 IF c_wait_allow_unit_section%NOTFOUND THEN
282 OPEN c_wait_allow_unit_offering(usec_row.uoo_id) ;
283 FETCH c_wait_allow_unit_offering INTO l_waitlist_allowed ;
284 CLOSE c_wait_allow_unit_offering;
285 END IF;
286 CLOSE c_wait_allow_unit_section;
287
288 -- added the following code as part of 2375362, pmarada
289 OPEN c_max_std_per_wait_usec(usec_row.uoo_id );
290 FETCH c_max_std_per_wait_usec INTO l_max_std_per_wait_usec;
291 -- Checking defined any max student per wait list exist at unit section level
295 OPEN c_max_std_per_wait_uofr_pat(usec_row.unit_cd,
292 IF c_max_std_per_wait_usec%FOUND THEN
293 v_max_std_wlst := l_max_std_per_wait_usec;
294 ELSE
296 usec_row.version_number,
297 usec_row.cal_type,
298 usec_row.ci_sequence_number);
299 FETCH c_max_std_per_wait_uofr_pat INTO l_max_std_per_wait_uofr_pat;
300 -- checking defined any max students per wait list exist at unit offering pattern level
301 IF c_max_std_per_wait_uofr_pat%FOUND THEN
302 v_max_std_wlst := l_max_std_per_wait_uofr_pat;
303 ELSE
304 OPEN c_max_std_per_wait_org (usec_row.owner_org_unit_cd,
305 usec_row.cal_type,
306 usec_row.ci_sequence_number);
307 FETCH c_max_std_per_wait_org INTO l_max_std_per_wait_org;
308 -- checking defined any max students oer wait list exist at organization level.
309 IF c_max_std_per_wait_org%FOUND THEN
310 v_max_std_wlst := l_max_std_per_wait_org;
311 END IF;
312 CLOSE c_max_std_per_wait_org;
313 END IF;
314 CLOSE c_max_std_per_wait_uofr_pat ;
315 END IF;
316 CLOSE c_max_std_per_wait_usec;
317 -- end of the code added. pmarada
318
319 IF NVL(l_waitlist_allowed, 'N') = 'N' THEN
320 IF (NVL(usec_row.enrollment_actual,0) >= NVL(v_enr_max,999999) ) THEN
321 lv_unit_section_status :='CLOSED';
322 ELSIF NVL(usec_row.enrollment_actual,0) < NVL(v_enr_max,999999) THEN
323 lv_unit_section_status :='OPEN';
324 END IF;
325 ELSE
326
327 IF (NVL(usec_row.enrollment_actual,0) >= NVL(v_enr_max,999999))
328 AND ( NVL(usec_row.waitlist_actual,0) >= NVL(v_max_std_wlst,999999) )THEN
329 --update the status of the unit section to 'CLOSED'
330 lv_unit_section_status :='CLOSED';
331
332 ELSIF (NVL(usec_row.enrollment_actual,0) < NVL(v_enr_max,999999)
333 AND NVL(usec_row.waitlist_actual,0) > 0 ) THEN
334 --update the status of the unit section to 'HOLD'
335 lv_unit_section_status :='HOLD';
336
337 ELSIF ( (NVL(usec_row.enrollment_actual,0) >= NVL(v_enr_max,999999) )
338 AND NVL(usec_row.waitlist_actual,0) < NVL(v_max_std_wlst,999999)) THEN
339 --update the status of the unit section to 'FULLWAITOK'
340 lv_unit_section_status :='FULLWAITOK';
341
342 ELSIF (NVL(usec_row.enrollment_actual,0) < NVL(v_enr_max,999999)
343 AND NVL(usec_row.waitlist_actual,0) = 0) THEN
344 --update the status of the unit section to 'OPEN'
345 lv_unit_section_status :='OPEN';
346 END IF;
347
348 END IF;
349
350 ELSE
351
352 -- If actual enrollment is greater than the maximim enrollment limit in the group level
353 -- Change the unit section status to 'Closed' and update all the unit sections which
354 -- belong to that group to closed.
355
356 IF (NVL(v_grp_actual,0) + NVL(usec_row.enrollment_actual,0)) >= v_grp_max THEN
357 lv_unit_section_status := 'CLOSED';
358 ELSE
359 lv_unit_section_status := 'OPEN';
360 END IF;
361
362 END IF; -- End if moved here as part of Bug# 2672325
363
364
365 -- The direct enrollment count needs to be incremented when the
366 -- unit attempt is inserted with a unit attempt status of 'ENROLLED
367 -- The enrolled from waitlist count is incremented if a unit attempt
368 -- is moved to 'Enrolled' Status from 'WAitlist' status
369
370 l_enr_from_wlst := usec_row.ENR_FROM_WLST ;
371 l_dir_enr := usec_row.DIR_ENROLLMENT ;
372
373 IF p_action = 'INSERT' THEN
374 IF new_references.UNIT_ATTEMPT_STATUS = cst_enrolled THEN
375 l_dir_enr := NVL(usec_row.DIR_ENROLLMENT,0) + 1 ;
376 l_enr_from_wlst := usec_row.ENR_FROM_WLST ;
377 END IF;
378 ELSIF p_action = 'UPDATE' THEN
379 IF (new_references.UNIT_ATTEMPT_STATUS = cst_enrolled AND
380 old_references.UNIT_ATTEMPT_STATUS = cst_waitlisted ) THEN
381 -- if the student has been enrolled from the waitlist then increment
382 -- counter ENR_FROM_WLST
383 l_enr_from_wlst := NVL(usec_row.ENR_FROM_WLST, 0) + 1;
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 ,
393 x_CI_SEQUENCE_NUMBER => usec_row.CI_SEQUENCE_NUMBER ,
394 x_LOCATION_CD => usec_row.LOCATION_CD ,
395 x_UNIT_CLASS => usec_row.UNIT_CLASS ,
396 x_UOO_ID => usec_row.UOO_ID ,
397 x_IVRS_AVAILABLE_IND => usec_row.IVRS_AVAILABLE_IND ,
398 x_CALL_NUMBER => usec_row.CALL_NUMBER ,
399 x_UNIT_SECTION_STATUS => NVL(lv_unit_section_status,usec_row.UNIT_SECTION_STATUS ),
403 x_WAITLIST_ACTUAL => usec_row.WAITLIST_ACTUAL ,
400 x_UNIT_SECTION_START_DATE => usec_row.UNIT_SECTION_START_DATE ,
401 x_UNIT_SECTION_END_DATE => usec_row.UNIT_SECTION_END_DATE ,
402 x_ENROLLMENT_ACTUAL => usec_row.ENROLLMENT_ACTUAL,
404 x_OFFERED_IND => usec_row.OFFERED_IND ,
405 x_STATE_FINANCIAL_AID => usec_row.STATE_FINANCIAL_AID ,
406 x_GRADING_SCHEMA_PRCDNCE_IND => usec_row.GRADING_SCHEMA_PRCDNCE_IND,
407 x_FEDERAL_FINANCIAL_AID => usec_row.FEDERAL_FINANCIAL_AID ,
408 x_UNIT_QUOTA => usec_row.UNIT_QUOTA ,
409 x_UNIT_QUOTA_RESERVED_PLACES => usec_row.UNIT_QUOTA_RESERVED_PLACES ,
410 x_INSTITUTIONAL_FINANCIAL_AID => usec_row.INSTITUTIONAL_FINANCIAL_AID ,
411 x_UNIT_CONTACT => usec_row.UNIT_CONTACT ,
412 x_GS_VERSION_NUMBER => usec_row.GS_VERSION_NUMBER ,
413 X_MODE => 'R',
414 X_SS_ENROL_IND => usec_row.ss_enrol_ind,
415 X_SS_DISPLAY_IND => usec_row.ss_display_ind,
416 x_owner_org_unit_cd => usec_row.owner_org_unit_cd,
417 x_attendance_required_ind => usec_row.attendance_required_ind,
418 x_reserved_seating_allowed => usec_row.reserved_seating_allowed,
419 x_special_permission_ind => usec_row.special_permission_ind,
420 x_dir_enrollment => l_dir_enr,
421 x_enr_from_wlst => l_enr_from_wlst,
422 x_inq_not_wlst =>usec_row.inq_not_wlst,
423 x_rev_account_cd => usec_row.rev_account_cd ,
424 x_GRADING_SCHEMA_CD => usec_row.GRADING_SCHEMA_CD,
425 X_NON_STD_USEC_IND => usec_row.NON_STD_USEC_IND,
426 X_ANON_UNIT_GRADING_IND => usec_row.anon_unit_grading_ind,
427 X_ANON_ASSESS_GRADING_IND => usec_row.anon_assess_grading_ind,
428 x_auditable_ind => usec_row.auditable_ind,
429 x_audit_permission_ind => usec_row.audit_permission_ind,
430 x_not_multiple_section_flag => usec_row.not_multiple_section_flag,
431 x_sup_uoo_id => usec_row.sup_uoo_id,
432 x_relation_type => usec_row.relation_type,
433 x_default_enroll_flag => usec_row.default_enroll_flag,
434 x_abort_flag => usec_row.abort_flag
435 );
436
437 -- Setup is defined in the cross listed group level.
438 IF l_setup_found = 1 THEN
439
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 ,
449 x_CI_SEQUENCE_NUMBER => unit_sec.CI_SEQUENCE_NUMBER ,
450 x_LOCATION_CD => unit_sec.LOCATION_CD ,
451 x_UNIT_CLASS => unit_sec.UNIT_CLASS ,
452 x_UOO_ID => unit_sec.UOO_ID ,
453 x_IVRS_AVAILABLE_IND => unit_sec.IVRS_AVAILABLE_IND ,
454 x_CALL_NUMBER => unit_sec.CALL_NUMBER ,
455 x_UNIT_SECTION_STATUS => NVL(lv_unit_section_status,unit_sec.UNIT_SECTION_STATUS ),
456 x_UNIT_SECTION_START_DATE => unit_sec.UNIT_SECTION_START_DATE ,
457 x_UNIT_SECTION_END_DATE => unit_sec.UNIT_SECTION_END_DATE ,
458 x_ENROLLMENT_ACTUAL => unit_sec.ENROLLMENT_ACTUAL,
459 x_WAITLIST_ACTUAL => unit_sec.WAITLIST_ACTUAL ,
460 x_OFFERED_IND => unit_sec.OFFERED_IND ,
461 x_STATE_FINANCIAL_AID => unit_sec.STATE_FINANCIAL_AID ,
462 x_GRADING_SCHEMA_PRCDNCE_IND => unit_sec.GRADING_SCHEMA_PRCDNCE_IND,
463 x_FEDERAL_FINANCIAL_AID => unit_sec.FEDERAL_FINANCIAL_AID ,
464 x_UNIT_QUOTA => unit_sec.UNIT_QUOTA ,
465 x_UNIT_QUOTA_RESERVED_PLACES => unit_sec.UNIT_QUOTA_RESERVED_PLACES ,
466 x_INSTITUTIONAL_FINANCIAL_AID => unit_sec.INSTITUTIONAL_FINANCIAL_AID ,
467 x_UNIT_CONTACT => unit_sec.UNIT_CONTACT ,
468 x_GS_VERSION_NUMBER => unit_sec.GS_VERSION_NUMBER ,
469 X_MODE => 'R',
470 X_SS_ENROL_IND => unit_sec.ss_enrol_ind,
471 X_SS_DISPLAY_IND => unit_sec.ss_display_ind,
472 x_owner_org_unit_cd => unit_sec.owner_org_unit_cd,
473 x_attendance_required_ind => unit_sec.attendance_required_ind,
474 x_reserved_seating_allowed => unit_sec.reserved_seating_allowed,
475 x_special_permission_ind => unit_sec.special_permission_ind,
476 x_dir_enrollment =>unit_sec.dir_enrollment,
477 x_enr_from_wlst =>unit_sec.enr_from_wlst,
478 x_inq_not_wlst =>unit_sec.inq_not_wlst,
479 x_rev_account_cd => unit_sec.rev_account_cd ,
480 x_GRADING_SCHEMA_CD => unit_sec.GRADING_SCHEMA_CD,
481 X_NON_STD_USEC_IND => unit_sec.NON_STD_USEC_IND,
482 X_ANON_UNIT_GRADING_IND => unit_sec.anon_unit_grading_ind,
483 X_ANON_ASSESS_GRADING_IND => unit_sec.anon_assess_grading_ind,
484 x_auditable_ind => unit_sec.auditable_ind,
488 x_relation_type => unit_sec.relation_type,
485 x_audit_permission_ind => unit_sec.audit_permission_ind,
486 x_not_multiple_section_flag => unit_sec.not_multiple_section_flag,
487 x_sup_uoo_id => unit_sec.sup_uoo_id,
489 x_default_enroll_flag => unit_sec.default_enroll_flag,
490 x_abort_flag => unit_sec.abort_flag
491 );
492
493 END LOOP;
494
495 ELSIF l_setup_found = 2 THEN
496 -- Setup is done in the Meet with class group.
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 ,
506 x_CI_SEQUENCE_NUMBER => usec_meet_with.CI_SEQUENCE_NUMBER ,
507 x_LOCATION_CD => usec_meet_with.LOCATION_CD ,
508 x_UNIT_CLASS => usec_meet_with.UNIT_CLASS ,
509 x_UOO_ID => usec_meet_with.UOO_ID ,
510 x_IVRS_AVAILABLE_IND => usec_meet_with.IVRS_AVAILABLE_IND ,
511 x_CALL_NUMBER => usec_meet_with.CALL_NUMBER ,
512 x_UNIT_SECTION_STATUS => NVL(lv_unit_section_status,usec_meet_with.UNIT_SECTION_STATUS ),
513 x_UNIT_SECTION_START_DATE => usec_meet_with.UNIT_SECTION_START_DATE ,
514 x_UNIT_SECTION_END_DATE => usec_meet_with.UNIT_SECTION_END_DATE ,
515 x_ENROLLMENT_ACTUAL => usec_meet_with.ENROLLMENT_ACTUAL,
516 x_WAITLIST_ACTUAL => usec_meet_with.WAITLIST_ACTUAL ,
517 x_OFFERED_IND => usec_meet_with.OFFERED_IND ,
518 x_STATE_FINANCIAL_AID => usec_meet_with.STATE_FINANCIAL_AID ,
519 x_GRADING_SCHEMA_PRCDNCE_IND => usec_meet_with.GRADING_SCHEMA_PRCDNCE_IND,
520 x_FEDERAL_FINANCIAL_AID => usec_meet_with.FEDERAL_FINANCIAL_AID ,
521 x_UNIT_QUOTA => usec_meet_with.UNIT_QUOTA ,
522 x_UNIT_QUOTA_RESERVED_PLACES => usec_meet_with.UNIT_QUOTA_RESERVED_PLACES ,
523 x_INSTITUTIONAL_FINANCIAL_AID => usec_meet_with.INSTITUTIONAL_FINANCIAL_AID ,
524 x_UNIT_CONTACT => usec_meet_with.UNIT_CONTACT ,
525 x_GS_VERSION_NUMBER => usec_meet_with.GS_VERSION_NUMBER ,
526 X_MODE => 'R',
527 X_SS_ENROL_IND => usec_meet_with.ss_enrol_ind,
528 X_SS_DISPLAY_IND => usec_meet_with.ss_display_ind,
529 x_owner_org_unit_cd => usec_meet_with.owner_org_unit_cd,
530 x_attendance_required_ind => usec_meet_with.attendance_required_ind,
531 x_reserved_seating_allowed => usec_meet_with.reserved_seating_allowed,
532 x_special_permission_ind => usec_meet_with.special_permission_ind,
533 x_dir_enrollment =>usec_meet_with.dir_enrollment,
534 x_enr_from_wlst =>usec_meet_with.enr_from_wlst,
535 x_inq_not_wlst =>usec_meet_with.inq_not_wlst,
536 x_rev_account_cd => usec_meet_with.rev_account_cd ,
537 x_GRADING_SCHEMA_CD => usec_meet_with.GRADING_SCHEMA_CD,
538 X_NON_STD_USEC_IND => usec_meet_with.NON_STD_USEC_IND,
539 X_ANON_UNIT_GRADING_IND => usec_meet_with.anon_unit_grading_ind,
540 X_ANON_ASSESS_GRADING_IND => usec_meet_with.anon_assess_grading_ind,
541 x_auditable_ind => usec_meet_with.auditable_ind,
542 x_audit_permission_ind => usec_meet_with.audit_permission_ind,
543 x_not_multiple_section_flag => usec_meet_with.not_multiple_section_flag,
544 x_sup_uoo_id => usec_meet_with.sup_uoo_id,
545 x_relation_type => usec_meet_with.relation_type,
546 x_default_enroll_flag => usec_meet_with.default_enroll_flag,
547 x_abort_flag => usec_meet_with.abort_flag
548 );
549 END LOOP;
550 END IF;
551
552 END local_update_unit_section;
553
554 -- This procedure is used to get the status of the Unit Section and Waitlist Indicator,
555 -- which determine whether student can Enroll, Waitlist or will be shown error message.
556 PROCEDURE local_usec_status ( old_references IN EN_SUA_REC_TYPE%TYPE,
557 new_references IN EN_SUA_REC_TYPE%TYPE,
558 usec_row OUT NOCOPY usec_upd_enr_act%ROWTYPE
559 ) AS
560 /*------------------------------------------------------------------
561
562 vkarthik 10-dec-2003 Bug3140571. Added a cursor to pick up version for the given person and course and another to get
563 max_wlst_per_stud given the course and version. Made use of these cursors to include program level
564 EN waitlist
565
566 ------------------------------------------------------------------*/
567
568 -- Cursor to get the load calander for the passed teaching calander.
569 CURSOR cur_teach_to_load( p_teach_cal_type IGS_CA_INST.cal_type%TYPE,
570 p_teach_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
571 SELECT load_cal_type, load_ci_sequence_number
575 ORDER BY LOAD_START_DT ASC;
572 FROM IGS_CA_TEACH_TO_LOAD_V
573 WHERE teach_cal_type = p_teach_cal_type
574 AND teach_ci_sequence_number = p_teach_ci_sequence_number
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;
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,
599 cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
600 SELECT version_number
601 FROM igs_en_stdnt_ps_att
602 WHERE
603 person_id = cp_person_id AND
604 course_cd = cp_course_cd;
605
606 -- cursor that gets max_wlst_stud_ps given course and version
607 CURSOR cur_max_wlst_stud_ps(cp_course_cd igs_ps_ver.course_cd%TYPE,
608 cp_version_number igs_ps_ver.version_number%TYPE) IS
609 SELECT max_wlst_per_stud
610 FROM igs_ps_ver
611 WHERE
612 course_cd = cp_course_cd AND
613 version_number = cp_version_number;
614
615 --cursors to check max waitlist per student and validations for simultaneous wailists
616 --as part of wailist enhancement build , bug# 3052426.
617 CURSOR cur_max_wlst_stud IS
618 SELECT max_waitlists_student_num
619 FROM IGS_EN_INST_WL_STPS;
620
621 CURSOR cur_count_wlsts_stud(cp_person_id igs_en_su_attempt.person_id%TYPE,
622 cp_load_cal_type IGS_CA_INST.cal_type%TYPE,
623 cp_load_seq_num IGS_CA_INST.sequence_number%TYPE) IS
624 SELECT count(*)
625 FROM igs_en_su_attempt
626 WHERE person_id = cp_person_id
627 AND unit_attempt_status =cst_waitlisted
628 AND (cal_type, ci_sequence_number) IN
629 (SELECT teach_cal_type, teach_ci_sequence_number
630 FROM igs_ca_load_to_teach_v
631 WHERE load_cal_type = cp_load_cal_type
632 AND load_ci_sequence_number = cp_load_seq_num);
633
634 CURSOR cur_mus_allwd(cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
635 cp_version_number igs_ps_unit_ver.version_number%TYPE) IS
636 SELECT same_teaching_period
637 FROM igs_ps_unit_ver
638 WHERE unit_cd = cp_unit_cd
639 AND version_number = cp_version_number;
640
641 CURSOR cur_wlst_same_unit(cp_person_id igs_en_su_attempt.person_id%TYPE,
642 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
643 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
644 cp_cal_type IGS_CA_INST.cal_type%TYPE,
645 cp_sequence_number IGS_CA_INST.sequence_number%TYPE,
646 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE ) IS
647 SELECT 'X'
648 FROM igs_en_su_attempt sua
649 WHERE sua.person_id = cp_person_id AND
650 sua.course_cd = cp_course_cd AND
651 sua.unit_cd = cp_unit_cd AND
652 sua.cal_type = cp_cal_type AND
653 sua.ci_sequence_number = cp_sequence_number AND
654 sua.unit_attempt_status = cst_waitlisted AND
655 sua.uoo_id <> cp_uoo_id;
656
657 CURSOR cur_simul_wlst_flag IS
658 SELECT simultaneous_wlst_alwd_flag
659 FROM igs_en_inst_wl_stps;
660
661 CURSOR cur_simul_term_wlst_flag(p_load_cal_type IGS_CA_INST.cal_type%TYPE) IS
662 SELECT 'X'
663 FROM igs_en_inst_wlst_opt
664 WHERE cal_type = p_load_cal_type AND
665 smlnes_waitlist_alwd = 'N';
666
667 CURSOR cur_simul_org_allwd(cp_load_cal_type IGS_CA_INST.cal_type%TYPE,
668 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
669 SELECT smtanus_wlst_unit_enr_alwd
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);
679
680 v_max_wlst_stud IGS_EN_INST_WL_STPS.max_waitlists_student_num%type;
681 v_count_wlst_stud NUMBER;
682 v_mus_allwd igs_ps_unit_ver.same_teaching_period%type;
683 v_simul_wlst_flag VARCHAR(1);
684 v_wlst_same_unit VARCHAR2(1);
685 v_simul_term_wlst_flag VARCHAR2(1);
689
686 v_simul_org_allwd VARCHAR2(1);
687
688 l_prog_version_spat igs_en_stdnt_ps_att.version_number%TYPE;
690 BEGIN
691
692 OPEN usec_upd_enr_act(new_references.uoo_id);
693 FETCH usec_upd_enr_act INTO usec_row;
694 CLOSE usec_upd_enr_act;
695
696
697 --Get the maximum audit enrollments for the unit section
698 OPEN c_max_auditors_allowed(usec_row.uoo_id);
699 FETCH c_max_auditors_allowed INTO l_max_auditors_allowed;
700 CLOSE c_max_auditors_allowed;
701
702 --Get the count of Audit attempts
703 OPEN c_audit_attempts_count(usec_row.uoo_id);
704 FETCH c_audit_attempts_count INTO l_audit_attempts_count;
705 CLOSE c_audit_attempts_count;
706
707 -- Raise an exception in case the enrolled count for audit
708 -- exceeds the maximum allowed number
709 IF (l_audit_attempts_count > l_max_auditors_allowed) THEN
710 FND_MESSAGE.SET_NAME('IGS','IGS_EN_AU_LIM_UNIT_CROSS');
711 IGS_GE_MSG_STACK.ADD;
712 APP_EXCEPTION.RAISE_EXCEPTION;
713 END IF;
714
715 -- Get the Load calander for the passed Teaching calander.
716 OPEN cur_teach_to_load( usec_row.cal_type, usec_row.ci_sequence_number );
717 FETCH cur_teach_to_load INTO rec_teach_to_load;
718 CLOSE cur_teach_to_load;
719
720 igs_en_gen_015.get_usec_status (
721 usec_row.uoo_id,
722 NVL(old_references.person_id,new_references.person_ID),
723 l_unit_section_status,
724 l_waitlist_ind,
725 rec_teach_to_load.load_cal_type,
726 rec_teach_to_load.load_ci_sequence_number,
727 new_references.course_cd
728 );
729
730 IF new_references.UNIT_ATTEMPT_STATUS = cst_waitlisted AND l_waitlist_ind IS NULL THEN
731 usec_row := NULL;
732 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MAX_WAIT_REACH');
733 IGS_GE_MSG_STACK.ADD;
734 APP_EXCEPTION.RAISE_EXCEPTION;
735 ELSIF ((l_waitlist_ind IS NULL OR l_waitlist_ind = 'Y') AND (new_references.UNIT_ATTEMPT_STATUS <> cst_waitlisted)) THEN
736 usec_row := NULL;
737 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MAX_ENR_REACH');
738 IGS_GE_MSG_STACK.ADD;
739 APP_EXCEPTION.RAISE_EXCEPTION;
740 END IF;
741
742 -- get the program version
743 OPEN cur_get_prog_ver(new_references.person_id, new_references.course_cd);
744 FETCH cur_get_prog_ver INTO l_prog_version_spat;
745 CLOSE cur_get_prog_ver;
746
747 -- get the program level max_wlst_per_stud
748 OPEN cur_max_wlst_stud_ps(new_references.course_cd, l_prog_version_spat);
749 FETCH cur_max_wlst_stud_ps INTO v_max_wlst_stud;
750 CLOSE cur_max_wlst_stud_ps;
751
752 -- when program level max_wlst_per_stud is not defined, proceed to insitute level max_wlst_per_stud
753 IF v_max_wlst_stud IS NULL THEN
754 OPEN cur_max_wlst_stud;
755 FETCH cur_max_wlst_stud INTO v_max_wlst_stud;
756 CLOSE cur_max_wlst_stud;
757 END IF;
758
759 OPEN cur_count_wlsts_stud(new_references.person_id,rec_teach_to_load.load_cal_type,rec_teach_to_load.load_ci_sequence_number);
760 FETCH cur_count_wlsts_stud INTO v_count_wlst_stud;
761 CLOSE cur_count_wlsts_stud;
762
763 IF new_references.UNIT_ATTEMPT_STATUS = cst_waitlisted THEN
764 IF nvl(v_count_wlst_Stud,0) > nvl(v_max_wlst_stud,9999) THEN
765 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MAX_WLST_STUD_RCH');
766 IGS_GE_MSG_STACK.ADD;
767 APP_EXCEPTION.RAISE_EXCEPTION;
768 END IF;
769 END IF;
770
771 --simultaneaous wailist validations
772 IF new_references.UNIT_ATTEMPT_STATUS = cst_waitlisted THEN
773 --check if multipls sections allwed in the same unit.
774 OPEN cur_mus_allwd(new_references.unit_cd, new_references.version_number);
775 FETCH cur_mus_allwd INTO v_mus_allwd;
776 CLOSE cur_mus_allwd;
777
778 IF nvl(v_mus_allwd,'N') = 'Y' THEN
779 OPEN cur_wlst_same_unit(new_references.person_ID,new_references.course_cd,new_references.unit_cd,
780 new_references.cal_type, new_references.ci_sequence_number,
781 new_references.uoo_id);
782 FETCH cur_wlst_same_unit INTO v_wlst_same_unit;
783 CLOSE cur_wlst_same_unit;
784
785 IF v_wlst_same_unit IS NOT NULL THEN
786 --implies student is attempting to wailist in more than one section of same unit
787 --hence check if simultaneous wailist allowed at inst level
788 OPEN cur_simul_wlst_flag;
789 FETCH cur_simul_wlst_flag INTO v_simul_wlst_flag;
790 CLOSE cur_simul_wlst_flag;
791
792 IF v_simul_wlst_flag = 'Y' THEN
793
794 --check if restricted at term calendar level
795 OPEN cur_simul_term_wlst_flag(rec_teach_to_load.load_cal_type);
796 FETCH cur_simul_term_wlst_flag INTO v_simul_term_wlst_flag;
797 CLOSE cur_simul_term_wlst_flag;
798
799 IF v_simul_term_wlst_flag IS NOT NULL THEN --restricted hence raise error
800 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SIMULT_WLST_NOT_ALLWD');
801 IGS_GE_MSG_STACK.ADD;
802 APP_EXCEPTION.RAISE_EXCEPTION;
803 ELSE
804 --check at org level if simultaneous waitlist allowed
805 OPEN cur_simul_org_allwd(rec_teach_to_load.load_cal_type,new_references.uoo_id);
809 IF v_simul_org_allwd = 'N' THEN
806 FETCH cur_simul_org_allwd INTO v_simul_org_allwd;
807 CLOSE cur_simul_org_allwd;
808
810 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SIMULT_WLST_NOT_ALLWD');--org level
811 IGS_GE_MSG_STACK.ADD;
812 APP_EXCEPTION.RAISE_EXCEPTION;
813 END IF;
814 END IF;
815
816 ELSE
817 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SIMULT_WLST_NOT_ALLWD');--instituion level
818 IGS_GE_MSG_STACK.ADD;
819 APP_EXCEPTION.RAISE_EXCEPTION;
820 END IF;
821 END IF;
822 END IF;
823 END IF;
824
825
826 END local_usec_status;
827
828 BEGIN ------ begin of upd_enrollment_counts procedure
829
830 IF p_action = 'INSERT' THEN
831 IF (new_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled, cst_invalid, cst_completed)) OR
832 (new_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND new_references.CART = 'N') THEN
833 local_usec_status (old_references, new_references,usec_row);
834 usec_row.ENROLLMENT_ACTUAL := NVL(usec_row.ENROLLMENT_ACTUAL, 0) + 1;
835 local_update_unit_section(p_action, old_references, new_references, usec_row);
836 ELSIF new_references.UNIT_ATTEMPT_STATUS = cst_waitlisted THEN
837 local_usec_status (old_references, new_references,usec_row);
838 usec_row.WAITLIST_ACTUAL := NVL(usec_row.WAITLIST_ACTUAL, 0) + 1;
839 local_update_unit_section(p_action, old_references, new_references, usec_row);
840 END IF;
841
842
843 ELSIF p_action = 'UPDATE' THEN
844 IF (old_references.UNIT_ATTEMPT_STATUS IN (cst_dropped,cst_discontin,cst_duplicate, cst_waitlisted) AND
845 new_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled,cst_invalid,cst_completed) ) OR
846 (old_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND
847 new_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND
848 new_references.CART IN ('S','I','J') AND NVL(old_references.CART,'X') <> 'N') THEN
849
850 local_usec_status (old_references, new_references,usec_row);
851 usec_row.ENROLLMENT_ACTUAL := NVL(usec_row.ENROLLMENT_ACTUAL, 0) + 1;
852 IF old_references.UNIT_ATTEMPT_STATUS = cst_waitlisted THEN
853 usec_row.WAITLIST_ACTUAL := NVL(usec_row.WAITLIST_ACTUAL, 0) - 1;
854 END IF;
855 local_update_unit_section(p_action, old_references, new_references, usec_row);
856
857 ELSIF ( old_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled, cst_invalid,cst_completed, cst_unconfirm ) AND
858 new_references.UNIT_ATTEMPT_STATUS IN (cst_dropped,cst_discontin,cst_duplicate, cst_waitlisted) ) THEN
859
860
861 IF (old_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND
862 new_references.UNIT_ATTEMPT_STATUS = cst_waitlisted) THEN
863 NULL;
864 ELSE
865 OPEN usec_upd_enr_act(new_references.uoo_id);
866 FETCH usec_upd_enr_act INTO usec_row;
867 CLOSE usec_upd_enr_act;
868 usec_row.ENROLLMENT_ACTUAL := NVL(usec_row.ENROLLMENT_ACTUAL, 0) - 1;
869 END IF;
870
871 IF new_references.UNIT_ATTEMPT_STATUS = cst_waitlisted THEN
872 local_usec_status (old_references, new_references,usec_row);
873 usec_row.WAITLIST_ACTUAL := NVL(usec_row.WAITLIST_ACTUAL, 0) + 1;
874 END IF;
875 local_update_unit_section(p_action, old_references, new_references, usec_row);
876
877 ELSIF ( old_references.UNIT_ATTEMPT_STATUS = cst_waitlisted AND
878 new_references.UNIT_ATTEMPT_STATUS <> old_references.UNIT_ATTEMPT_STATUS ) THEN
879 OPEN usec_upd_enr_act(new_references.uoo_id);
880 FETCH usec_upd_enr_act INTO usec_row;
881 CLOSE usec_upd_enr_act;
882 usec_row.WAITLIST_ACTUAL := NVL(usec_row.WAITLIST_ACTUAL, 0) - 1;
883 local_update_unit_section(p_action, old_references, new_references, usec_row);
884 -- End of new code, Added as per the Bug# 2373469.
885 END IF;
886
887 ELSIF p_action = 'DELETE' THEN
888 IF (old_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled,cst_unconfirm,cst_invalid, cst_completed)) THEN
889 OPEN usec_upd_enr_act(old_references.uoo_id);
890 FETCH usec_upd_enr_act INTO usec_row;
891 CLOSE usec_upd_enr_act;
892 usec_row.ENROLLMENT_ACTUAL := NVL(usec_row.ENROLLMENT_ACTUAL, 0) - 1;
893 local_update_unit_section(p_action, old_references, new_references, usec_row);
894 ELSIF (old_references.UNIT_ATTEMPT_STATUS IN (cst_waitlisted)) THEN
895 OPEN usec_upd_enr_act(old_references.uoo_id);
896 FETCH usec_upd_enr_act INTO usec_row;
897 CLOSE usec_upd_enr_act;
898 usec_row.WAITLIST_ACTUAL := NVL(usec_row.WAITLIST_ACTUAL, 0) - 1;
899 local_update_unit_section(p_action, old_references, new_references, usec_row);
900 END IF;
901
902 END IF;
903
904
905
906 END upd_enrollment_counts;
907
908 PROCEDURE CREATE_UNIT_ATTEMPT (
909 X_ROWID IN OUT NOCOPY VARCHAR2,
910 X_PERSON_ID IN NUMBER,
911 X_COURSE_CD IN VARCHAR2,
912 X_UNIT_CD IN VARCHAR2,
913 X_CAL_TYPE IN VARCHAR2,
914 X_CI_SEQUENCE_NUMBER IN NUMBER,
915 X_VERSION_NUMBER IN NUMBER,
916 X_LOCATION_CD IN VARCHAR2,
917 X_UNIT_CLASS IN VARCHAR2,
918 X_CI_START_DT IN DATE,
919 X_CI_END_DT IN DATE,
920 X_UOO_ID IN NUMBER,
921 X_ENROLLED_DT IN DATE,
925 X_RULE_WAIVED_DT IN DATE,
922 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
923 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
924 X_DISCONTINUED_DT IN DATE,
926 X_RULE_WAIVED_PERSON_ID IN NUMBER,
927 X_NO_ASSESSMENT_IND IN VARCHAR2,
928 X_SUP_UNIT_CD IN VARCHAR2,
929 X_SUP_VERSION_NUMBER IN NUMBER,
930 X_EXAM_LOCATION_CD IN VARCHAR2,
931 X_ALTERNATIVE_TITLE IN VARCHAR2,
932 X_OVERRIDE_ENROLLED_CP IN NUMBER,
933 X_OVERRIDE_EFTSU IN NUMBER,
934 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
935 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
936 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
937 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
938 X_WAITLIST_DT IN DATE,
939 X_DCNT_REASON_CD IN VARCHAR2,
940 X_MODE IN VARCHAR2 ,
941 x_org_id IN NUMBER,
942 X_GS_VERSION_NUMBER IN NUMBER ,
943 X_ENR_METHOD_TYPE IN VARCHAR2 ,
944 X_FAILED_UNIT_RULE IN VARCHAR2 ,
945 X_CART IN VARCHAR2 ,
946 X_RSV_SEAT_EXT_ID IN NUMBER ,
947 X_ORG_UNIT_CD IN VARCHAR2 ,
948 X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
949 X_SUBTITLE IN VARCHAR2 ,
950 X_SESSION_ID IN NUMBER ,
951 X_DEG_AUD_DETAIL_ID IN NUMBER ,
952 X_STUDENT_CAREER_TRANSCRIPT IN VARCHAR2 ,
953 X_STUDENT_CAREER_STATISTICS IN VARCHAR2 ,
954 X_WAITLIST_MANUAL_IND IN VARCHAR2 ,
955 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
956 X_ATTRIBUTE1 IN VARCHAR2 ,
957 X_ATTRIBUTE2 IN VARCHAR2 ,
958 X_ATTRIBUTE3 IN VARCHAR2 ,
959 X_ATTRIBUTE4 IN VARCHAR2 ,
960 X_ATTRIBUTE5 IN VARCHAR2 ,
961 X_ATTRIBUTE6 IN VARCHAR2 ,
962 X_ATTRIBUTE7 IN VARCHAR2 ,
963 X_ATTRIBUTE8 IN VARCHAR2 ,
964 X_ATTRIBUTE9 IN VARCHAR2 ,
965 X_ATTRIBUTE10 IN VARCHAR2,
966 X_ATTRIBUTE11 IN VARCHAR2,
967 X_ATTRIBUTE12 IN VARCHAR2,
968 X_ATTRIBUTE13 IN VARCHAR2,
969 X_ATTRIBUTE14 IN VARCHAR2,
970 X_ATTRIBUTE15 IN VARCHAR2,
971 X_ATTRIBUTE16 IN VARCHAR2,
972 X_ATTRIBUTE17 IN VARCHAR2,
973 X_ATTRIBUTE18 IN VARCHAR2,
974 X_ATTRIBUTE19 IN VARCHAR2,
975 x_ATTRIBUTE20 IN VARCHAR2,
976 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER,
977 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER,
978 X_CORE_INDICATOR_CODE IN VARCHAR2
979 ) AS
980 ------------------------------------------------------------------------------------------------
981 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW
982 -- and IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW added as part of Prevent Dropping Core Units. Enh Bug# 3052432
983 ------------------------------------------------------------------------------------------------
984
985 new_references EN_SUA_REC_TYPE%TYPE;
986
987
988 BEGIN
989 SAVEPOINT create_unit_attempt;
990 IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW (
991 X_ROWID,
992 X_PERSON_ID,
993 X_COURSE_CD,
994 X_UNIT_CD,
995 X_CAL_TYPE,
996 X_CI_SEQUENCE_NUMBER,
997 X_VERSION_NUMBER,
998 X_LOCATION_CD,
999 X_UNIT_CLASS,
1000 X_CI_START_DT,
1001 X_CI_END_DT,
1002 X_UOO_ID,
1003 X_ENROLLED_DT,
1004 X_UNIT_ATTEMPT_STATUS,
1005 X_ADMINISTRATIVE_UNIT_STATUS,
1006 X_DISCONTINUED_DT,
1007 X_RULE_WAIVED_DT,
1008 X_RULE_WAIVED_PERSON_ID,
1009 X_NO_ASSESSMENT_IND,
1010 X_SUP_UNIT_CD,
1011 X_SUP_VERSION_NUMBER,
1012 X_EXAM_LOCATION_CD,
1013 X_ALTERNATIVE_TITLE,
1014 X_OVERRIDE_ENROLLED_CP,
1015 X_OVERRIDE_EFTSU,
1016 X_OVERRIDE_ACHIEVABLE_CP,
1017 X_OVERRIDE_OUTCOME_DUE_DT,
1018 X_OVERRIDE_CREDIT_REASON,
1019 X_ADMINISTRATIVE_PRIORITY,
1020 X_WAITLIST_DT,
1021 X_DCNT_REASON_CD,
1022 X_MODE,
1023 x_org_id ,
1024 X_GS_VERSION_NUMBER ,
1025 X_ENR_METHOD_TYPE ,
1026 X_FAILED_UNIT_RULE ,
1027 X_CART ,
1028 X_RSV_SEAT_EXT_ID ,
1029 X_ORG_UNIT_CD ,
1030 X_GRADING_SCHEMA_CODE ,
1031 X_subtitle,
1032 x_session_id,
1033 X_deg_aud_detail_id ,
1034 x_student_career_transcript,
1035 x_student_career_statistics,
1036 X_WAITLIST_MANUAL_IND,
1037 X_ATTRIBUTE_CATEGORY,
1038 X_ATTRIBUTE1,
1039 X_ATTRIBUTE2,
1040 X_ATTRIBUTE3,
1041 X_ATTRIBUTE4,
1042 X_ATTRIBUTE5,
1043 X_ATTRIBUTE6,
1044 X_ATTRIBUTE7,
1045 X_ATTRIBUTE8,
1046 X_ATTRIBUTE9,
1047 X_ATTRIBUTE10,
1048 X_ATTRIBUTE11,
1049 X_ATTRIBUTE12,
1050 X_ATTRIBUTE13,
1051 X_ATTRIBUTE14,
1052 X_ATTRIBUTE15,
1053 X_ATTRIBUTE16,
1054 X_ATTRIBUTE17,
1055 X_ATTRIBUTE18,
1056 X_ATTRIBUTE19,
1057 X_ATTRIBUTE20,
1058 X_WLST_PRIORITY_WEIGHT_NUM ,
1059 X_WLST_PREFERENCE_WEIGHT_NUM,
1060 X_CORE_INDICATOR_CODE,
1061 'N', -- for UPD_AUDIT_IND
1062 'A' -- for SS_SOURCE_IND
1063 );
1064
1065 OPEN C_SUA (X_ROWID);
1066 FETCH C_SUA INTO new_references;
1067 CLOSE C_SUA;
1068
1069 upd_enrollment_counts('INSERT',
1070 NULL,
1071 new_references);
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 ROLLBACK TO create_unit_attempt;
1075 RAISE;
1079 X_ROWID IN VARCHAR2,
1076 END create_unit_attempt;
1077
1078 PROCEDURE UPDATE_UNIT_ATTEMPT (
1080 X_PERSON_ID IN NUMBER,
1081 X_COURSE_CD IN VARCHAR2,
1082 X_UNIT_CD IN VARCHAR2,
1083 X_CAL_TYPE IN VARCHAR2,
1084 X_CI_SEQUENCE_NUMBER IN NUMBER,
1085 X_VERSION_NUMBER IN NUMBER,
1086 X_LOCATION_CD IN VARCHAR2,
1087 X_UNIT_CLASS IN VARCHAR2,
1088 X_CI_START_DT IN DATE,
1089 X_CI_END_DT IN DATE,
1090 X_UOO_ID IN NUMBER,
1091 X_ENROLLED_DT IN DATE,
1092 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
1093 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
1094 X_DISCONTINUED_DT IN DATE,
1095 X_RULE_WAIVED_DT IN DATE,
1096 X_RULE_WAIVED_PERSON_ID IN NUMBER,
1097 X_NO_ASSESSMENT_IND IN VARCHAR2,
1098 X_SUP_UNIT_CD IN VARCHAR2,
1099 X_SUP_VERSION_NUMBER IN NUMBER,
1100 X_EXAM_LOCATION_CD IN VARCHAR2,
1101 X_ALTERNATIVE_TITLE IN VARCHAR2,
1102 X_OVERRIDE_ENROLLED_CP IN NUMBER,
1103 X_OVERRIDE_EFTSU IN NUMBER,
1104 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
1105 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
1106 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
1107 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
1108 X_WAITLIST_DT IN DATE,
1109 X_DCNT_REASON_CD IN VARCHAR2,
1110 X_MODE IN VARCHAR2 ,
1111 X_GS_VERSION_NUMBER IN NUMBER ,
1112 X_ENR_METHOD_TYPE IN VARCHAR2 ,
1113 X_FAILED_UNIT_RULE IN VARCHAR2 ,
1114 X_CART IN VARCHAR2 ,
1115 X_RSV_SEAT_EXT_ID IN NUMBER ,
1116 X_ORG_UNIT_CD IN VARCHAR2 ,
1117 X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
1118 X_SUBTITLE IN VARCHAR2 ,
1119 X_SESSION_ID IN NUMBER ,
1120 X_DEG_AUD_DETAIL_ID IN NUMBER ,
1121 X_STUDENT_CAREER_TRANSCRIPT IN VARCHAR2 ,
1122 X_STUDENT_CAREER_STATISTICS IN VARCHAR2 ,
1123 X_WAITLIST_MANUAL_IND IN VARCHAR2,
1124 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1125 X_ATTRIBUTE1 IN VARCHAR2 ,
1126 X_ATTRIBUTE2 IN VARCHAR2 ,
1127 X_ATTRIBUTE3 IN VARCHAR2 ,
1128 X_ATTRIBUTE4 IN VARCHAR2 ,
1129 X_ATTRIBUTE5 IN VARCHAR2 ,
1130 X_ATTRIBUTE6 IN VARCHAR2 ,
1131 X_ATTRIBUTE7 IN VARCHAR2 ,
1132 X_ATTRIBUTE8 IN VARCHAR2 ,
1133 X_ATTRIBUTE9 IN VARCHAR2 ,
1134 X_ATTRIBUTE10 IN VARCHAR2 ,
1135 X_ATTRIBUTE11 IN VARCHAR2 ,
1136 X_ATTRIBUTE12 IN VARCHAR2 ,
1137 X_ATTRIBUTE13 IN VARCHAR2 ,
1138 X_ATTRIBUTE14 IN VARCHAR2 ,
1139 X_ATTRIBUTE15 IN VARCHAR2 ,
1140 X_ATTRIBUTE16 IN VARCHAR2 ,
1141 X_ATTRIBUTE17 IN VARCHAR2 ,
1142 X_ATTRIBUTE18 IN VARCHAR2 ,
1143 X_ATTRIBUTE19 IN VARCHAR2 ,
1144 X_ATTRIBUTE20 IN VARCHAR2,
1145 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER,
1146 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER,
1147 X_CORE_INDICATOR_CODE IN VARCHAR2
1148 ) AS
1149
1150 old_references EN_SUA_REC_TYPE%TYPE;
1151 new_references EN_SUA_REC_TYPE%TYPE;
1152
1153 -- cursor to get person type
1154 CURSOR cur_per_typ IS
1155 SELECT person_type_code
1156 FROM igs_pe_person_types
1157 WHERE system_type = 'OTHER';
1158 l_cur_per_typ cur_per_typ%ROWTYPE;
1159 lv_person_type igs_pe_person_types.person_type_code%TYPE;
1160
1161 -- cursor tp get system person type
1162 CURSOR cur_sys_pers_type(cp_person_type_code VARCHAR2) IS
1163 SELECT system_type
1164 FROM igs_pe_person_types
1165 WHERE person_type_code = cp_person_type_code;
1166
1167 l_sys_per_type igs_pe_person_types.system_type%TYPE;
1168
1169 -- added for bug 3526251
1170 NO_AUSL_RECORD_FOUND EXCEPTION;
1171 PRAGMA EXCEPTION_INIT(NO_AUSL_RECORD_FOUND , -20010);
1172
1173 BEGIN
1174
1175
1176 OPEN C_SUA (X_ROWID);
1177 FETCH C_SUA INTO old_references;
1178 CLOSE C_SUA;
1179
1180 IF X_UNIT_ATTEMPT_STATUS = 'ENROLLED' AND old_references.SS_SOURCE_IND = 'S' THEN
1181
1182 OPEN cur_per_typ;
1183 FETCH cur_per_typ INTO l_cur_per_typ;
1184 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(X_COURSE_CD),l_cur_per_typ.person_type_code);
1185 CLOSE cur_per_typ;
1186
1187 OPEN cur_sys_pers_type(lv_person_type);
1188 FETCH cur_sys_pers_type INTO l_sys_per_type;
1189 CLOSE cur_sys_pers_type;
1190
1191 IF l_sys_per_type = 'STUDENT' THEN
1192 old_references.SS_SOURCE_IND := 'N';
1193 ELSE
1194 old_references.SS_SOURCE_IND := 'A';
1195 END IF;
1196
1197 END IF;
1198
1199 SAVEPOINT update_unit_attempt;
1200
1201 IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW (
1202 X_ROWID,
1203 X_PERSON_ID,
1204 X_COURSE_CD,
1205 X_UNIT_CD,
1206 X_CAL_TYPE,
1207 X_CI_SEQUENCE_NUMBER,
1208 X_VERSION_NUMBER,
1209 X_LOCATION_CD,
1210 X_UNIT_CLASS,
1211 X_CI_START_DT,
1215 X_UNIT_ATTEMPT_STATUS,
1212 X_CI_END_DT,
1213 X_UOO_ID,
1214 X_ENROLLED_DT,
1216 X_ADMINISTRATIVE_UNIT_STATUS,
1217 X_DISCONTINUED_DT,
1218 X_RULE_WAIVED_DT,
1219 X_RULE_WAIVED_PERSON_ID,
1220 X_NO_ASSESSMENT_IND,
1221 X_SUP_UNIT_CD,
1222 X_SUP_VERSION_NUMBER,
1223 X_EXAM_LOCATION_CD,
1224 X_ALTERNATIVE_TITLE,
1225 X_OVERRIDE_ENROLLED_CP,
1226 X_OVERRIDE_EFTSU,
1227 X_OVERRIDE_ACHIEVABLE_CP,
1228 X_OVERRIDE_OUTCOME_DUE_DT,
1229 X_OVERRIDE_CREDIT_REASON,
1230 X_ADMINISTRATIVE_PRIORITY,
1231 X_WAITLIST_DT,
1232 X_DCNT_REASON_CD,
1233 X_MODE,
1234 X_GS_VERSION_NUMBER,
1235 X_ENR_METHOD_TYPE,
1236 X_FAILED_UNIT_RULE,
1237 X_CART ,
1238 X_RSV_SEAT_EXT_ID ,
1239 X_ORG_UNIT_CD,
1240 X_GRADING_SCHEMA_CODE ,
1241 X_subtitle,
1242 x_session_id,
1243 X_deg_aud_detail_id ,
1244 x_student_career_transcript,
1245 x_student_career_statistics,
1246 X_WAITLIST_MANUAL_IND,
1247 X_ATTRIBUTE_CATEGORY,
1248 X_ATTRIBUTE1,
1249 X_ATTRIBUTE2,
1250 X_ATTRIBUTE3,
1251 X_ATTRIBUTE4,
1252 X_ATTRIBUTE5,
1253 X_ATTRIBUTE6,
1254 X_ATTRIBUTE7,
1255 X_ATTRIBUTE8,
1256 X_ATTRIBUTE9,
1257 X_ATTRIBUTE10,
1258 X_ATTRIBUTE11,
1259 X_ATTRIBUTE12,
1260 X_ATTRIBUTE13,
1261 X_ATTRIBUTE14,
1262 X_ATTRIBUTE15,
1263 X_ATTRIBUTE16,
1264 X_ATTRIBUTE17,
1265 X_ATTRIBUTE18,
1266 X_ATTRIBUTE19,
1267 X_ATTRIBUTE20,
1268 X_WLST_PRIORITY_WEIGHT_NUM,
1269 X_WLST_PREFERENCE_WEIGHT_NUM,
1270 X_CORE_INDICATOR_CODE,
1271 old_references.UPD_AUDIT_FLAG,
1272 old_references.SS_SOURCE_IND
1273 );
1274
1275 -- smaddali 8-dec-2005 added condition to bypass update of seat counts for DROP : bug#4864437
1276 -- this code is being commented here but will be replicated in Drop page
1277 -- StdDropAMImpl.java:dropSubmit
1278 --bdeviset 26-APR-2006 Modified if condition for bug# 5119136
1279 IF X_UNIT_ATTEMPT_STATUS NOT IN ('DROPPED','DISCONTIN' ) OR
1280 NVL(igs_en_su_attempt_pkg.pkg_source_of_drop,'NULL') <>'DROP' THEN
1281 OPEN C_SUA (X_ROWID);
1282 FETCH C_SUA INTO new_references;
1283 CLOSE C_SUA;
1284
1285 upd_enrollment_counts('UPDATE',
1286 old_references,
1287 new_references);
1288 END IF;
1289
1290 EXCEPTION
1291 -- added for bug 3526251
1292 WHEN NO_AUSL_RECORD_FOUND THEN
1293 ROLLBACK TO update_unit_attempt;
1294 RAISE;
1295 WHEN OTHERS THEN
1296 ROLLBACK TO update_unit_attempt;
1297 RAISE;
1298 END update_unit_attempt;
1299
1300 FUNCTION Enr_sua_sup_sub_val(
1301 P_PERSON_ID IN NUMBER,
1302 P_COURSE_CD IN VARCHAR2,
1303 P_UOO_ID IN NUMBER,
1304 P_UNIT_ATTEMPT_STATUS IN VARCHAR2,
1305 P_SUP_SUB_STATUS OUT NOCOPY VARCHAR2
1306 ) RETURN BOOLEAN AS
1307
1308 /*************************************************************
1309 Created By : Satya Vanukuri, IDC
1310 Date Created By :11-OCT-2003
1311 Purpose :validates the context student unit attempt status against superior and subordinate unit section relationships.
1312 Function returns true if unit attempt status is valid otherwise returns false
1313 Know limitations, enhancements or remarks
1314 Change History
1315 Who When What
1316
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
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
1335 AND sua.course_cd = p_course_cd
1336 AND sua.unit_attempt_status <> 'DUPLICATE';
1337
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;
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
1353 --initialize out parameter
1354 P_SUP_SUB_STATUS := NULL;
1355 --get the relation type for unit
1356 OPEN cur_rel_type;
1357 FETCH cur_rel_type INTO l_rel_type;
1358 CLOSE cur_rel_type;
1359 --if NONE implies unit is neither superior nor sub, hence return true
1360 IF nvl(l_rel_type,'NONE') = 'NONE' THEN
1361 RETURN TRUE;
1362 --unit is neither sup nor sub
1363
1367 LOOP
1364 --validate subordinate unit status if context unit is superior
1365 ELSIF l_rel_type = 'SUPERIOR' THEN
1366 OPEN cur_sub_uoo;
1368 --fetch the sub unit sections attempted and their status
1369 FETCH cur_sub_uoo INTO l_sub_uoo,l_sub_uoo_status;
1370 EXIT WHEN cur_sub_uoo%NOTFOUND;
1371
1372 --validate the sub attmept status against the sup attempt status passed as parameter to function
1373 IF p_unit_attempt_status = 'ENROLLED' THEN
1374 RETURN TRUE;
1375
1376 ELSIF p_unit_attempt_status = 'WAITLISTED' THEN
1377 IF l_sub_uoo_status NOT IN ('UNCONFIRM','DROPPED') THEN
1378 p_sup_sub_status := l_sub_uoo_status;
1379 RETURN FALSE;
1380 ELSE
1381 RETURN TRUE;
1382 END IF;
1383 ELSIF p_unit_attempt_status = 'UNCONFIRM' THEN
1384 IF l_sub_uoo_status NOT IN ('UNCONFIRM','DROPPED') THEN
1385 p_sup_sub_status := l_sub_uoo_status;
1386 RETURN FALSE;
1387 ELSE
1388 RETURN TRUE;
1389 END IF;
1390
1391 ELSIF p_unit_attempt_status = 'COMPLETED' THEN
1392 IF l_sub_uoo_status NOT IN ('DISCONTIN', 'COMPLETED', 'DROPPED') THEN
1393 p_sup_sub_status := l_sub_uoo_status;
1394 RETURN FALSE;
1395 ELSE
1396 RETURN TRUE;
1397 END IF;
1398 ELSIF p_unit_attempt_status = 'DROPPED' THEN
1399 IF l_sub_uoo_status NOT IN ('DROPPED','INVALID') THEN
1400 p_sup_sub_status := l_sub_uoo_status;
1401 RETURN FALSE;
1402 ELSE
1403 RETURN TRUE;
1404 END IF;
1405
1406 ELSIF p_unit_attempt_status = 'DISCONTIN' THEN
1407 IF l_sub_uoo_status NOT IN ('DROPPED','INVALID','DISCONTIN','COMPLETED') THEN
1408 p_sup_sub_status := l_sub_uoo_status;
1409 RETURN FALSE;
1410 ELSE
1411 RETURN TRUE;
1412 END IF;
1413 ELSIF p_unit_attempt_status = 'DUPLICATE' THEN
1414 IF l_sub_uoo_status NOT IN ('DROPPED','DISCONTIN') THEN
1415 p_sup_sub_status := l_sub_uoo_status;
1416 RETURN FALSE;
1417 ELSE
1418 RETURN TRUE;
1419 END IF;
1420 ELSIF p_unit_attempt_status = 'INVALID' THEN
1421 IF l_sub_uoo_status NOT IN ('DROPPED','DISCONTIN','INVALID','UNCONFIRM','WAITLISTED','COMPLETED') THEN
1422 p_sup_sub_status := l_sub_uoo_status;
1423 RETURN FALSE;
1424 ELSE
1425 RETURN TRUE;
1426 END IF;
1427 ELSE
1428 RETURN TRUE;
1429 END IF;
1430 END LOOP;
1431 CLOSE cur_sub_uoo;
1432 RETURN TRUE;
1433
1434
1435 --validate superior unit status if context unit is subordinate
1436 ELSIF l_rel_type = 'SUBORDINATE' THEN
1437 --fetch the superior unit attempt status
1438 OPEN cur_sup_uoo;
1439 FETCH cur_sup_uoo INTO l_sup_uoo_Id, l_sup_attempt_status;
1440 CLOSE cur_sup_uoo;
1441 IF l_sup_uoo_Id IS NULL THEN
1442 RETURN FALSE;
1443 END IF;
1444 IF p_unit_attempt_status = 'ENROLLED' THEN
1445 IF l_sup_attempt_status <> 'ENROLLED' THEN
1446 p_sup_sub_status := l_sup_attempt_status;
1447 RETURN FALSE;
1448 ELSE
1449 RETURN TRUE;
1450 END IF;
1451 ELSIF p_unit_attempt_status = 'WAITLISTED' THEN
1452 IF l_sup_attempt_status <> 'ENROLLED' THEN
1453 p_sup_sub_status := l_sup_attempt_status;
1454 RETURN FALSE;
1455 ELSE
1456 RETURN TRUE;
1457 END IF;
1458 ELSIF p_unit_attempt_status = 'UNCONFIRM' THEN
1459 IF l_sup_attempt_status NOT IN('UNCONFIRM','INVALID','WAITLISTED','ENROLLED') THEN
1460 p_sup_sub_status := l_sup_attempt_status;
1461 RETURN FALSE;
1462 ELSE
1463 RETURN TRUE;
1464 END IF;
1465 ELSIF p_unit_attempt_status = 'COMPLETED' THEN
1466 IF l_sup_attempt_status NOT IN ('COMPLETED','ENROLLED' ) THEN
1467 p_sup_sub_status := l_sup_attempt_status;
1468 RETURN FALSE;
1469 ELSE
1470 RETURN TRUE;
1471 END IF;
1472 ELSIF p_unit_attempt_status = 'DROPPED' THEN
1473
1474 RETURN TRUE;
1475
1476 ELSIF p_unit_attempt_status = 'DISCONTIN' THEN
1477 IF l_sup_attempt_status NOT IN ('COMPLETED','ENROLLED', 'DISCONTIN','INVALID') THEN
1478 p_sup_sub_status := l_sup_attempt_status;
1479 RETURN FALSE;
1480 ELSE
1481 RETURN TRUE;
1482 END IF;
1483 ELSIF p_unit_attempt_status = 'DUPLICATE' THEN
1484 IF l_sup_attempt_status <> 'DUPLICATE' THEN
1485 p_sup_sub_status := l_sup_attempt_status;
1486 RETURN FALSE;
1487 ELSE
1488 RETURN TRUE;
1489 END IF;
1490 ELSIF p_unit_attempt_status = 'INVALID' THEN
1491 IF l_sup_attempt_status NOT IN ('DROPPED','ENROLLED', 'DISCONTIN','INVALID') THEN
1492 p_sup_sub_status := l_sup_attempt_status;
1493 RETURN FALSE;
1494 ELSE
1495 RETURN TRUE;
1496 END IF;
1497 ELSE
1498 RETURN TRUE;
1499 END IF;
1500 END IF;
1501
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 IF cur_sup_uoo%ISOPEN THEN
1505 close cur_sup_uoo;
1506 END IF;
1507 IF cur_sub_uoo%ISOPEN THEN
1508 close cur_sub_uoo;
1509 END IF;
1510 RAISE;
1511 END Enr_sua_sup_sub_val;
1512
1513 FUNCTION chk_sup_del_alwd(p_person_id IN NUMBER,
1514 p_course_cd IN VARCHAR2,
1515 p_uoo_id IN NUMBER) RETURN BOOLEAN AS
1516 /*
1517 || Created By : svanukur
1518 || Created On :
1519 || Purpose : to check if deletion of superior is allwd.
1520 || a superior unit cannot be deleted if subordinate is
1521 || in any status other than dropped or discontinued.
1522 || Known limitations, enhancements or remarks :
1523 || Change History :
1524 || Who When What
1525 || (reverse chronological order - newest change first)
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
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
1540 AND sua.person_Id = p_person_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;
1550 FETCH cur_chk_sup INTO v_sup;
1551 CLOSE cur_chk_sup;
1552
1553 IF v_sup = 'SUPERIOR' THEN
1554 --chk subordinate unit attempt status
1555 OPEN cur_sub;
1556 FETCH cur_sub INTO v_sub_status;
1557 CLOSE cur_sub;
1558
1559 IF v_sub_status IS NOT NULL THEN
1560 RETURN FALSE;
1561 END IF;
1562
1563 END IF;
1564 RETURN TRUE ;
1565
1566 EXCEPTION
1567 WHEN others THEN
1568 IF cur_chk_sup%ISOPEN THEN
1569 close cur_chk_sup;
1570 END IF;
1571 IF cur_sub%ISOPEN THEN
1572 close cur_sub;
1573 END IF;
1574 RAISE;
1575 END chk_sup_del_alwd;
1576
1577
1578 END igs_en_sua_api;
[Home] [Help]