[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ADD_UNITS_API
Source
1 PACKAGE BODY igs_en_add_units_api AS
2 /* $Header: IGSEN93B.pls 120.39 2006/08/25 13:55:14 bdeviset noship $ */
3
4 --package variables
5 g_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 g_person_type igs_pe_typ_instances.person_type_code%TYPE;
7
8 TYPE l_units_rec IS RECORD (
9 uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
10 ass_ind igs_en_su_attempt.no_assessment_ind%TYPE,
11 grading_schema_cd igs_en_su_attempt.grading_schema_code%TYPE,
12 gs_version_number igs_en_su_attempt. gs_version_number %TYPE,
13 override_enrolled_cp igs_en_su_attempt.override_enrolled_cp%TYPE,
14 spl_perm_step VARCHAR2(200),
15 aud_perm_step VARCHAR2(200),
16 wlst_step VARCHAR2(200),
17 create_wlst VARCHAR2(1)
18 );
19
20 TYPE t_params_table IS TABLE OF l_units_rec INDEX BY BINARY_INTEGER;
21
22
23 FUNCTION set_deny_warn (
24 p_person_id IN igs_en_su_attempt.person_id%TYPE,
25 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
26 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
27 p_load_sequence_number IN igs_ca_inst.sequence_number%TYPE
28 )
29 RETURN VARCHAR2 AS
30 -------------------------------------------------------------------------------------------
31 --Created by : Basanth Kumar D, Oracle IDC
32 --Date created: 29-JUL-2005
33 -- Purpose : returns deny if atleast one deny record exists and warn if only warn records
34 -- else returns null
35 --Change History:
36 --Who When What
37 --ckasu 18-OCT-2005 modfied as a part of bug#4674099 inorder to return 'D' as atleast
38 -- one deny record exists else return warn even if atlest one record
39 -- other than deny exists in warnings table.
40 -------------------------------------------------------------------------------------------
41
42 CURSOR get_deny_warn(cp_msg_icon VARCHAR2) IS
43 SELECT 'X'
44 FROM igs_en_std_warnings
45 WHERE person_id = p_person_id
46 AND course_cd = p_course_cd
47 AND term_cal_type = p_load_cal_type
48 AND term_ci_sequence_number = p_load_sequence_number
49 AND message_icon = cp_msg_icon
50 AND step_type <> 'DROP';
51
52 CURSOR get_error_warn IS
53 SELECT 'X'
54 FROM igs_en_std_warnings
55 WHERE person_id = p_person_id
56 AND course_cd = p_course_cd
57 AND term_cal_type = p_load_cal_type
58 AND term_ci_sequence_number = p_load_sequence_number
59 AND message_icon <> 'D'
60 AND step_type <> 'DROP';
61
62 l_deny_warn VARCHAR2(1);
63 l_msg_icon igs_en_std_warnings.message_icon%TYPE;
64
65 BEGIN
66
67 l_deny_warn := NULL;
68
69 -- if any deny records are found then set deny_warn flag to 'D' and return
70 OPEN get_deny_warn('D');
71 FETCH get_deny_warn INTO l_msg_icon;
72 IF get_deny_warn%FOUND THEN
73 CLOSE get_deny_warn;
74 l_deny_warn := 'D';
75 RETURN l_deny_warn;
76 END IF;
77 CLOSE get_deny_warn;
78
79 -- if any error or warn records are found then set deny_warn flag to 'W' and return
80
81 OPEN get_error_warn;
82 FETCH get_error_warn INTO l_msg_icon;
83 IF get_error_warn%FOUND THEN
84 CLOSE get_error_warn;
85 l_deny_warn := 'W';
86 RETURN l_deny_warn;
87 END IF;
88 CLOSE get_error_warn;
89
90 RETURN NULL;
91
92 EXCEPTION
93 WHEN OTHERS THEN
94
95 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
96 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.set_deny_warn :',SQLERRM);
97 END IF;
98 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
99 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.set_deny_warn');
100 IGS_GE_MSG_STACK.ADD;
101 RAISE;
102 END set_deny_warn;
103
104
105 PROCEDURE check_sua_exists(p_person_id IN NUMBER,
106 p_course_cd IN VARCHAR2,
107 p_load_cal_type IN VARCHAR2,
108 p_load_sequence_number IN NUMBER,
109 p_selected_uoo_ids IN VARCHAR2,
110 p_message OUT NOCOPY VARCHAR2) AS
111 PRAGMA AUTONOMOUS_TRANSACTION;
112
113 -------------------------------------------------------------------------------------------
114 --Created by : Basanth Kumar D, Oracle IDC
115 --Date created: 29-JUL-2005
116 -- Purpose : This procedure checks if the selected uoo id already exists and if so
117 -- returns sua already exists.It also checks if any ps error record unit already exits in
118 -- sua table and if so deletes the ps error record
119 --Change History:
120 --Who When What
121
122 -------------------------------------------------------------------------------------------
123
124 -- cursor to get planning sheet error units
125 CURSOR cur_fetch_ps_err_units IS
126 SELECT ROWID,uoo_id
127 FROM igs_en_plan_units
128 WHERE person_id = p_person_id
129 AND course_cd = p_course_cd
130 AND term_cal_type = p_load_cal_type
131 AND term_ci_sequence_number = p_load_sequence_number
132 AND cart_error_flag = 'Y';
133
134
135
136 CURSOR c_sua_exists(cp_uoo_id NUMBER) IS
137 SELECT 'X'
138 FROM igs_en_su_attempt
139 WHERE person_id = p_person_id
140 AND course_cd = p_course_cd
141 AND uoo_id = cp_uoo_id
142 AND unit_attempt_status <> 'DROPPED';
143
144 l_sua_exists VARCHAR2(1);
145 l_sel_uoo_ids VARCHAR2(1000);
146 l_uoo_id NUMBER;
147
148
149 BEGIN
150
151 l_sel_uoo_ids := p_selected_uoo_ids;
152
153 FOR l_ps_err_rec IN cur_fetch_ps_err_units LOOP
154 l_sua_exists := NULL;
155 OPEN c_sua_exists(l_ps_err_rec.uoo_id);
156 FETCH c_sua_exists INTO l_sua_exists;
157 CLOSE c_sua_exists;
158
159 IF l_sua_exists IS NOT NULL THEN
160
161 IGS_EN_PLAN_UNITS_PKG.DELETE_ROW(x_rowid => l_ps_err_rec.ROWID);
162
163 END IF;
164
165 END LOOP;
166
167 COMMIT;
168 l_sua_exists := NULL;
169
170 WHILE l_sel_uoo_ids IS NOT NULL LOOP
171
172 IF(instr(l_sel_uoo_ids,';',1) = 0) THEN
173 l_sel_uoo_ids := NULL;
174 END IF;
175
176 -- lunit is 1234,Y,P/F,233,343
177 l_uoo_id := substr( l_sel_uoo_ids,1,instr(l_sel_uoo_ids,',')-1);
178
179 OPEN c_sua_exists(l_uoo_id);
180 FETCH c_sua_exists INTO l_sua_exists;
181 CLOSE c_sua_exists;
182
183 IF l_sua_exists IS NOT NULL THEN
184 p_message := 'IGS_EN_SUA_EXISTS'||'*'||get_unit_sec(l_uoo_id);
185 RETURN;
186 END IF;
187
188 -- Remove the uoo id details which is extacted above
189 l_sel_uoo_ids := substr(l_sel_uoo_ids,instr(l_sel_uoo_ids,';',1)+1);
190
191 END LOOP;
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
196 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.chk_sua_exists');
197 IGS_GE_MSG_STACK.ADD;
198 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
199 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.chk_sua_exists :',SQLERRM);
200 END IF;
201 ROLLBACK;
202 RAISE;
203
204 END check_sua_exists;
205
206 PROCEDURE update_warnings_table (p_person_id IN NUMBER,
207 p_course_cd IN VARCHAR2,
208 p_load_cal_type IN VARCHAR2,
209 p_load_sequence_number IN NUMBER,
210 p_calling_obj IN VARCHAR2) AS
211 PRAGMA AUTONOMOUS_TRANSACTION;
212
213 -------------------------------------------------------------------------------------------
214 --Created by : Basanth Kumar D, Oracle IDC
215 --Date created: 29-JUL-2005
216 -- Purpose : This table updates the message icon of deny warnings records of those units which are not in sua table
217 -- and planning sheet (if calling object is not plan then checl for error records only) to 'I'
218 --Change History:
219 --Who When What
220 --bdeviset 27-oct-2005 The deny record which does not have corresponding unit in the cart
221 -- is updated to 'I'(previously it was updated to 'E').Also concatenated
222 -- two message texts with the same message text for the same record
223 -- for bug# 4671726
224
225 -------------------------------------------------------------------------------------------
226
227
228 -- cursor to get distinct uooids in warnings table with message icon as 'D'
229 CURSOR c_dist_uooids IS
230 SELECT DISTINCT uoo_id
231 FROM igs_en_std_warnings
232 WHERE person_id = p_person_id
233 AND course_cd = p_course_cd
234 AND term_cal_type = p_load_cal_type
235 AND term_ci_sequence_number = p_load_sequence_number
236 AND message_icon = 'D'
237 AND step_type <> 'DROP';
238
239 -- cursor to check if sua record exits
240 CURSOR chk_sua_exists (cp_uoo_id NUMBER)IS
241 SELECT 'X'
242 FROM igs_en_su_attempt
243 WHERE person_id = p_person_id
244 AND course_cd = p_course_cd
245 AND uoo_id = cp_uoo_id
246 AND unit_attempt_Status <> 'DROPPED';
247
248 -- cursor to check if planning sheet error record exits
249 CURSOR chk_ps_err_rec_exists (cp_uoo_id NUMBER)IS
250 SELECT 'X'
251 FROM igs_en_plan_units
252 WHERE person_id = p_person_id
253 AND course_cd = p_course_cd
254 AND uoo_id = cp_uoo_id
255 AND cart_error_flag = 'Y';
256
257 CURSOR get_warn_rec (cp_uoo_id NUMBER) IS
258 SELECT ROWID,warn.*
259 FROM igs_en_std_warnings warn
260 WHERE warn.person_id = p_person_id
261 AND warn.course_cd = p_course_cd
262 AND warn.term_cal_type = p_load_cal_type
263 AND warn.term_ci_sequence_number = p_load_sequence_number
264 AND warn.message_icon = 'D'
265 AND warn.step_type <> 'DROP'
266 AND warn.uoo_id = cp_uoo_id;
267
268 l_sua_exists VARCHAR2(1);
269 l_ps_rec_exists VARCHAR2(1);
270 l_message1 VARCHAR2(2000);
271 l_message2 VARCHAR2(2000);
272
273 BEGIN
274 -- if calling object is in PLAN or SUBMITPLAN then dont update the warnings record
275 IF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
276 RETURN;
277 END IF;
278
279 -- get distinct uoo ids for this person course term calendar and term cal sequence number in warnings table having deny records
280 -- check whether record exists in sua or (ps error records or in planning sheet for plan)
281 -- if it does not exists then update the warnings records of those uoo ids having 'D' as 'I'
282
283 -- get the message text to be concatenated to the message text of a record in warnings table
284 -- which does not have a corresponding unit in the cart.
285 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERROR_UNITS');
286 l_message1 := FND_MESSAGE.GET();
287
288 FND_MESSAGE.SET_NAME('IGS','IGS_SS_EN_SEE_ADMIN');
289 l_message2 := FND_MESSAGE.GET();
290
291 FOR dist IN c_dist_uooids LOOP
292 l_sua_exists := NULL;
293 l_ps_rec_exists := NULL;
294
295 OPEN chk_sua_exists(dist.uoo_id);
296 FETCH chk_sua_exists INTO l_sua_exists;
297 CLOSE chk_sua_exists;
298
299 -- if the calling obj is not plan then check for planning sheet error record
300 OPEN chk_ps_err_rec_exists(dist.uoo_id);
301 FETCH chk_ps_err_rec_exists INTO l_ps_rec_exists;
302 CLOSE chk_ps_err_rec_exists;
303
304
305 IF l_sua_exists IS NULL AND l_ps_rec_exists IS NULL THEN
306
307 FOR l_warn_rec IN get_warn_rec(dist.uoo_id) LOOP
308
309 IGS_EN_STD_WARNINGS_PKG.UPDATE_ROW (
310 x_rowid => l_warn_rec.rowid,
311 x_warning_id => l_warn_rec.warning_id,
312 x_person_id => p_person_id,
313 x_course_cd => p_course_cd,
314 x_uoo_id => dist.uoo_id,
315 x_term_cal_type => p_load_cal_type,
316 x_term_ci_sequence_number => p_load_sequence_number,
317 x_message_for => l_warn_rec.message_for,
318 x_message_icon => 'I',
319 x_message_name => l_warn_rec.message_name,
320 x_message_text => l_message1||' '||l_warn_rec.message_text||' '||l_message2,
321 x_message_action => l_warn_rec.message_action,
322 x_destination => l_warn_rec.destination,
323 x_p_parameters => l_warn_rec.p_parameters,
324 x_step_type => l_warn_rec.step_type,
325 x_session_id => igs_en_add_units_api.g_ss_session_id,
326 x_mode => 'R' );
327 END LOOP;
328
329
333 END LOOP;
330 END IF;
331
332
334 COMMIT;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
339 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.update_warnings_table');
340 IGS_GE_MSG_STACK.ADD;
341 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
342 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.update_warnings_table :',SQLERRM);
343 END IF;
344 ROLLBACK;
345 RAISE;
346
347 END update_warnings_table;
348
349
350 FUNCTION get_unit_sec(p_uoo_id IN NUMBER)
351 RETURN VARCHAR2 AS
352
353 -------------------------------------------------------------------------------------------
354 --Created by : Basanth Kumar D, Oracle IDC
355 --Date created: 29-JUL-2005
356 -- Purpose : returns concatenated string of unit_cd and unit class for the passed in uoo_id
357 --Change History:
358 --Who When What
359
360 -------------------------------------------------------------------------------------------
361
362 CURSOR c_get_unit_sec(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
363 SELECT unit_cd||'/'||unit_class unit_sec
364 FROM IGS_PS_UNIT_OFR_OPT
365 WHERE uoo_id = cp_uoo_id;
366
367 l_unit_sec VARCHAR2(50);
368
369 BEGIN
370
371 OPEN c_get_unit_sec(p_uoo_id);
372 FETCH c_get_unit_sec INTO l_unit_sec;
373 CLOSE c_get_unit_sec;
374
375 RETURN l_unit_sec;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379
380 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
381 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.get_unit_sec :',SQLERRM);
382 END IF;
383 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
384 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.get_unit_sec');
385 IGS_GE_MSG_STACK.ADD;
386 RAISE;
387 END get_unit_sec;
388
389 FUNCTION get_person_type (
390 p_course_cd VARCHAR2
391
392 ) RETURN VARCHAR2 AS
393
394 -------------------------------------------------------------------------------------------
395 --Created by : Basanth Kumar D, Oracle IDC
396 --Date created: 29-JUL-2005
397 -- Purpose : returns person type
398 --Change History:
399 --Who When What
400
401 -------------------------------------------------------------------------------------------
402
403
404 -- cursor to get person type
405 CURSOR cur_per_typ IS
406 SELECT person_type_code
407 FROM igs_pe_person_types
408 WHERE system_type = 'OTHER';
409
410 l_cur_per_typ igs_pe_typ_instances.person_type_code%TYPE;
411 lv_person_type igs_pe_typ_instances.person_type_code%TYPE;
412
413 BEGIN
414
415 OPEN cur_per_typ;
416 FETCH cur_per_typ INTO l_cur_per_typ;
417 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(p_course_cd),l_cur_per_typ);
418 CLOSE cur_per_typ;
419
420 RETURN lv_person_type;
421
422 EXCEPTION
423 WHEN OTHERS THEN
424
425 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
426 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.get_person_type :',SQLERRM);
427 END IF;
428 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
429 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.get_person_type');
430 IGS_GE_MSG_STACK.ADD;
431 RAISE;
432 END get_person_type;
433
434
435 PROCEDURE delete_ss_warnings (p_person_id IN NUMBER,
436 p_course_cd IN VARCHAR2,
437 p_load_cal_type IN VARCHAR2,
438 p_load_sequence_number IN NUMBER,
439 p_uoo_id IN NUMBER,
440 p_message_for IN VARCHAR2,
441 p_delete_steps IN VARCHAR2
442 ) AS
443 PRAGMA AUTONOMOUS_TRANSACTION;
444
445 -------------------------------------------------------------------------------------------
446 --Created by : Basanth Kumar D, Oracle IDC
447 --Date created: 29-JUL-2005
448 -- Purpose : deletes the warnings records as per parameters passed
449 -- if p_delete_steps then step type is not considered while deleting
450 -- else it is considered
451 --Change History:
452 --Who When What
453
454 -------------------------------------------------------------------------------------------
455
456 -- -- cursor to get the warnings records against the person,course and uooid/message_for
457 CURSOR c_ss_warn IS
458 SELECT ROWID
459 FROM IGS_EN_STD_WARNINGS
460 WHERE person_id = p_person_id
461 AND course_cd = p_course_cd
462 AND term_cal_type = p_load_cal_type
463 AND term_ci_sequence_number = p_load_sequence_number
464 AND ((p_uoo_id IS NOT NULL AND uoo_id = p_uoo_id) OR
465 message_for = p_message_for)
466 AND step_type <> 'DROP';
467
468 TYPE c_ref_cursor IS REF CURSOR;
469 c_ss_warn_of_steps c_ref_cursor;
470 l_rowid VARCHAR2(30);
471 l_stmt VARCHAR2(1000);
472
473 BEGIN
474
475 -- if p_delete_steps is null then
476 IF p_delete_steps IS NULL THEN
477 OPEN c_ss_warn;
478 WHILE TRUE LOOP
479
483 EXIT;
480 FETCH c_ss_warn INTO l_rowid;
481 IF c_ss_warn%NOTFOUND THEN
482 CLOSE c_ss_warn;
484 END IF;
485
486 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => l_rowid);
487
488
489 END LOOP;
490 ELSE
491 -- dynamic cursor to get the warnings records against the person for given steps
492
493
494 IF p_uoo_id IS NOT NULL THEN
495
496 l_stmt := 'SELECT ROWID
497 FROM IGS_EN_STD_WARNINGS
498 WHERE person_id = :p_person_id
499 AND course_cd = :p_course_cd
500 AND term_cal_type = :p_load_cal_type
501 AND term_ci_sequence_number = :p_load_sequence_number
502 AND step_type IN ( ''' || p_delete_steps ||''')
503 AND uoo_id = :p_uooo_id' ;
504 OPEN c_ss_warn_of_steps FOR l_stmt USING p_person_id,p_course_cd,p_load_cal_type,p_load_sequence_number,p_uoo_id;
505
506 ELSE
507
508 l_stmt := 'SELECT ROWID
509 FROM IGS_EN_STD_WARNINGS
510 WHERE person_id = :p_person_id
511 AND course_cd = :p_course_cd
512 AND term_cal_type = :p_load_cal_type
513 AND term_ci_sequence_number = :p_load_sequence_number
514 AND step_type IN (''' || p_delete_steps || ''')';
515
516 OPEN c_ss_warn_of_steps FOR l_stmt USING p_person_id,p_course_cd,p_load_cal_type,p_load_sequence_number;
517
518 END IF;
519
520 LOOP
521 FETCH c_ss_warn_of_steps INTO l_rowid;
522 IF c_ss_warn_of_steps%NOTFOUND THEN
523 CLOSE c_ss_warn_of_steps;
524 EXIT;
525 END IF;
526
527 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => l_rowid);
528
529
530 END LOOP;
531
532 END IF;
533
534 COMMIT;
535
536 EXCEPTION
537 WHEN OTHERS THEN
538
539 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
540 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.delete_ss_warnings :',SQLERRM);
541 END IF;
542 ROLLBACK;
543 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
544 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.delete_ss_warnings');
545 IGS_GE_MSG_STACK.ADD;
546 RAISE;
547
548 END delete_ss_warnings;
549
550
551 FUNCTION chk_sua_creation_is_valid (
552 p_person_id IN NUMBER,
553 p_course_cd IN VARCHAR2,
554 p_uoo_id IN NUMBER,
555 p_load_cal_type IN VARCHAR2,
556 p_load_sequence_number IN NUMBER,
557 p_unit_params IN t_params_table,
558 p_selected_units IN VARCHAR2
559 ) RETURN BOOLEAN AS
560
561 -------------------------------------------------------------------------------------------
562 --Created by : Basanth Kumar D, Oracle IDC
563 --Date created: 29-JUL-2005
564 -- Purpose : returns true if unit attempt can be created else false
565
566 -- If the unit is not a subordinate then returns true
567 -- if the unit is a subordiante and selected by system and superior has taken a seat then returns true
568 -- else returns false
569 -- if the unit is a subordiante and selected by user and superior exists in 'UNCONFIRM','INVALID','WAITLISTED','ENROLLED'
570 -- statuses then returns true else false
571
572 --Change History:
573 --Who When What
574
575 -------------------------------------------------------------------------------------------
576
577
578
579 -- cursor to check wheter a unit is subordinate
580 CURSOR cur_chk_unit_is_sub(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)IS
581 SELECT sup_uoo_id
582 FROM igs_ps_unit_ofr_opt
583 WHERE uoo_id = cp_uoo_id
584 AND sup_uoo_id IS NOT NULL
585 AND relation_type = 'SUBORDINATE';
586
587
588 -- cursor to check whheter a unit attempt exists in particular statuses
589 CURSOR cur_chk_sup_exists(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
590 SELECT 'X' FROM igs_en_su_attempt
591 WHERE person_id = p_person_id
592 AND course_cd = p_course_cd
593 AND uoo_id = cp_uoo_id
594 AND unit_attempt_status IN ('UNCONFIRM','INVALID','WAITLISTED','ENROLLED');
595
596 l_sup_uoo_id igs_ps_unit_ofr_opt.sup_uoo_id%TYPE;
597 l_create_sub BOOLEAN;
598 l_sup_created BOOLEAN;
599 l_sup_exists VARCHAR2(1);
600 l_unit_sec VARCHAR2(100);
601 BEGIN
602
603
604 OPEN cur_chk_unit_is_sub(p_uoo_id);
605 FETCH cur_chk_unit_is_sub INTO l_sup_uoo_id;
606 CLOSE cur_chk_unit_is_sub;
607
608 -- if the unit is not subordinate then return true
609 IF l_sup_uoo_id IS NULL THEN
610
611 RETURN TRUE;
612
613 -- if this unit is a subordinate unit
614 ELSE
615
616 -- initially set the create subordinate flag to true
617 l_create_sub := TRUE;
618
619
620
621 --check if subordinate unit is in the selected list (if it is then it means it is selected by user else it is means
622 -- it is selected by the system).
623
624 -- check if it is selected by system or not
625 IF instr(p_selected_units,','||p_uoo_id||',') = 0 THEN
626
627 -- if subordinate has been automatically selected by the system, check if the superior has taken a seat/waitlisted.
628 -- if superior has not taken a seat then donot select this subordinate.
629
633 IF p_unit_params(j).uoo_id = l_sup_uoo_id AND
630 -- check if superior has taken a seat or not.
631 l_sup_created := FALSE;
632 FOR j IN 1..p_unit_params.count LOOP
634 p_unit_params(j).spl_perm_step = 'PERM_NREQ' AND
635 p_unit_params(j).aud_perm_step = 'PERM_NREQ' AND
636 p_unit_params(j).wlst_step = 'N' THEN
637 l_sup_created := TRUE;
638 END IF;
639 END LOOP;
640
641 IF NOT l_sup_created THEN
642
643 -- Call method to delete the warning records created for this subordinate uoo_id for this person,
644 -- term, course in context.and dont create subordinate unit
645 l_create_sub := FALSE;
646 delete_ss_warnings( p_person_id,
647 p_course_cd,
648 p_load_cal_type,
649 p_load_sequence_number,
650 p_uoo_id,
651 NULL,
652 'PROGRAM'',''UNIT');
653 END IF; -- IF NOT l_sup_created THEN
654
655 -- subordinate is selected by the user
656 ELSE
657
658 -- check if superior exists in proper status for subordinate to be created in unconfirm status
659 OPEN cur_chk_sup_exists(l_sup_uoo_id);
660 FETCH cur_chk_sup_exists INTO l_sup_exists;
661 CLOSE cur_chk_sup_exists;
662
663 -- if not then log a warning record saying subordinate cannot be added
664 IF l_sup_exists IS NULL THEN
665
666 -- set the create subordinate create flag to false
667 l_create_sub := FALSE;
668
669 l_unit_sec := get_unit_sec(l_sup_uoo_id);
670
671 igs_en_drop_units_api.create_ss_warning(
672 p_person_id => p_person_id,
673 p_course_cd => p_course_cd,
674 p_term_cal_type => p_load_cal_type,
675 p_term_ci_sequence_number => p_load_sequence_number,
676 p_uoo_id => p_uoo_id,
677 p_message_for => l_unit_sec,
678 p_message_icon=> 'D',
679 p_message_name => 'IGS_EN_CANNOT_ADD_SUB',
680 p_message_rule_text => NULL,
681 p_message_tokens => NULL,
682 p_message_action => NULL,
683 p_destination => NULL,
684 p_parameters => NULL,
685 p_step_type => 'UNIT');
686
687 END IF; -- IF NOT l_sup_exists
688
689 END IF; -- IF instr(p_selected_units,','||p_uoo_id||',') = 0
690
691 END IF; -- IF l_sup_uoo_id IS NOT NULL THEN
692
693 RETURN l_create_sub;
694
695 EXCEPTION
696
697 WHEN OTHERS THEN
698 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
699 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.chk_sua_creation_is_valid :',SQLERRM);
700 END IF;
701 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
702 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.chk_sua_creation_is_valid');
703 IGS_GE_MSG_STACK.ADD;
704 RAISE;
705 END chk_sua_creation_is_valid;
706
707 PROCEDURE create_ps_record( p_person_id IN NUMBER,
708 p_course_cd IN VARCHAR2,
709 p_load_cal_type IN VARCHAR2,
710 p_load_sequence_number IN NUMBER,
711 p_uoo_id IN NUMBER,
712 p_sup_uoo_id IN NUMBER,
713 p_cart_error_flag IN VARCHAR2,
714 p_assessment_ind IN VARCHAR2,
715 p_override_enrolled_cp IN NUMBER,
716 p_grading_schema_code IN VARCHAR2,
717 p_gs_version_number IN NUMBER
718 ) AS
719
720 PRAGMA AUTONOMOUS_TRANSACTION;
721
722 -------------------------------------------------------------------------------------------
723 --Created by : Basanth Kumar D, Oracle IDC
724 --Date created: 29-JUL-2005
725 -- Purpose : This procedure calls table handler of the plan table. This is written as an autonomous transaction since the creation of
726 -- planning sheet units must be comitted to the database.Check if unit does not already exist and call insert row of the
727 -- table handler. The cart_error_flag column is a part of the primary key since it is possible to have temporary records in the
728 -- plan table for cart errors. Hence for the same person_id , course_cd and uoo_id it is possible to have two records
729 -- with differing cart_error_ind columns.
730
731 --Change History:
732 --Who When What
733 --bdeviset 24-AUg-2006 Bug# 5487876. Only insertion needs to happen here.Updation is taken care at the page level
734 -------------------------------------------------------------------------------------------
735
736 l_unit_sec VARCHAR2(100);
737 l_row_id VARCHAR2(30);
738 l_enc_message_name VARCHAR2(2000);
739 l_app_short_name VARCHAR2(100);
740 l_msg_index NUMBER;
744 CURSOR c1 IS
741 l_message_name VARCHAR2(4000);
742 l_rec_exists VARCHAR2(1);
743
745 SELECT 'x'
746 FROM igs_en_plan_units
747 WHERE person_id = p_person_id
748 AND course_cd = p_course_cd
749 AND uoo_id = p_uoo_id
750 AND cart_error_flag = p_cart_error_flag;
751
752
753 BEGIN
754
755 OPEN c1;
756 FETCH c1 INTO l_rec_exists;
757 IF (c1%NOTFOUND) THEN
758 CLOSE c1;
759
760 IGS_EN_PLAN_UNITS_PKG.insert_row(
761 x_rowid => l_row_id,
762 x_person_id => p_person_id,
763 x_course_cd => p_course_cd,
764 x_uoo_id => p_uoo_id,
765 x_term_cal_type => p_load_cal_type,
766 x_term_ci_sequence_number => p_load_sequence_number,
767 x_no_assessment_ind => p_assessment_ind, --- need to check
768 x_sup_uoo_id => p_sup_uoo_id,--unit_dtls_rec,
769 x_override_enrolled_cp => p_override_enrolled_cp,--unit_dtls_rec,
770 x_grading_schema_code => p_grading_schema_code,--unit_dtls_rec,
771 x_gs_version_number => p_gs_version_number,--unit_dtls_rec,
772 x_core_indicator_code => NULL,--unit_dtls_rec,
773 x_alternative_title => NULL,--unit_dtls_rec,
774 x_cart_error_flag => p_cart_error_flag,
775 x_session_id => igs_en_add_units_api.g_ss_session_id,
776 x_mode => 'R'
777 );
778 ELSE
779 CLOSE c1;
780 END IF;
781
782 COMMIT ;
783
784 EXCEPTION
785
786
787 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
788 -- get message from stack and insert into warn record.
789 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
790 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
791 ROLLBACK;
792
793 IF l_message_name IS NOT NULL THEN
794
795 l_unit_sec := get_unit_sec(p_uoo_id);
796
797 igs_en_drop_units_api.create_ss_warning (
798 p_person_id => p_person_id,
799 p_course_cd => p_course_cd,
800 p_term_cal_type=>p_load_cal_type,
801 p_term_ci_sequence_number => p_load_sequence_number,
802 p_uoo_id => p_uoo_id,
803 p_message_for => l_unit_sec,
804 p_message_icon=>'D',
805 p_message_name => l_message_name,
806 p_message_rule_text => NULL,
807 p_message_tokens => NULL,
808 p_message_action=> NULL,
809 p_destination =>NULL,
810 p_parameters => NULL,
811 p_step_type => 'UNIT');
812
813 END IF;
814
815 WHEN OTHERS THEN
816
817 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
818 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_ps_record :',SQLERRM);
819 END IF;
820 ROLLBACK;
821 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
822 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_ps_record');
823 IGS_GE_MSG_STACK.ADD;
824 RAISE;
825
826 END create_ps_record;
827
828
829
830 PROCEDURE create_cart_error( p_person_id IN NUMBER,
831 p_course_cd IN VARCHAR2,
832 p_load_cal_type IN VARCHAR2,
833 p_load_sequence_number IN NUMBER,
834 p_uoo_id IN NUMBER,
835 p_sup_uoo_id IN NUMBER,
836 p_cart_error_flag IN VARCHAR2,
837 p_assessment_ind IN VARCHAR2,
838 p_override_enrolled_cp IN NUMBER,
839 p_grading_schema_code IN VARCHAR2,
840 p_gs_version_number IN NUMBER) AS
841
842
843 -------------------------------------------------------------------------------------------
844 --Created by : Basanth Kumar D, Oracle IDC
845 --Date created: 29-JUL-2005
846 -- Purpose : This procedure calls table handler of the plan table. This is written as an autonomous transaction since the creation of
847 -- planning sheet units must be comitted to the database.Check if unit does not already exist and call insert row of the
848 -- table handler. The cart_error_flag column is a part of the primary key since it is possible to have temporary records in the
849 -- plan table for cart errors. Hence for the same person_id , course_cd and uoo_id it is possible to have two records
850 -- with differing cart_error_ind columns.
851
852 --Change History:
853 --Who When What
854 -------------------------------------------------------------------------------------------
855
856 l_unit_sec VARCHAR2(100);
857 l_row_id VARCHAR2(30);
858 l_enc_message_name VARCHAR2(2000);
862
859 l_app_short_name VARCHAR2(100);
860 l_msg_index NUMBER;
861 l_message_name VARCHAR2(4000);
863
864 BEGIN
865
866 -- check if record already exists then update else insert
867
868 IGS_EN_PLAN_UNITS_PKG.add_row(
869 x_rowid => l_row_id,
870 x_person_id => p_person_id,
871 x_course_cd => p_course_cd,
872 x_uoo_id => p_uoo_id,
873 x_term_cal_type => p_load_cal_type,
874 x_term_ci_sequence_number => p_load_sequence_number,
875 x_no_assessment_ind => p_assessment_ind, --- need to check
876 x_sup_uoo_id => p_sup_uoo_id,--unit_dtls_rec,
877 x_override_enrolled_cp => p_override_enrolled_cp,--unit_dtls_rec,
878 x_grading_schema_code => p_grading_schema_code,--unit_dtls_rec,
879 x_gs_version_number => p_gs_version_number,--unit_dtls_rec,
880 x_core_indicator_code => NULL,--unit_dtls_rec,
881 x_alternative_title => NULL,--unit_dtls_rec,
882 x_cart_error_flag => p_cart_error_flag,
883 x_session_id => igs_en_add_units_api.g_ss_session_id,
884 x_mode => 'R'
885 );
886
887
888 EXCEPTION
889
890
891 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
892 -- get message from stack and insert into warn record.
893 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
894 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
895
896 IF l_message_name IS NOT NULL THEN
897
898 l_unit_sec := get_unit_sec(p_uoo_id);
899
900 igs_en_drop_units_api.create_ss_warning (
901 p_person_id => p_person_id,
902 p_course_cd => p_course_cd,
903 p_term_cal_type=>p_load_cal_type,
904 p_term_ci_sequence_number => p_load_sequence_number,
905 p_uoo_id => p_uoo_id,
906 p_message_for => l_unit_sec,
907 p_message_icon=>'D',
908 p_message_name => l_message_name,
909 p_message_rule_text => NULL,
910 p_message_tokens => NULL,
911 p_message_action=> NULL,
912 p_destination =>NULL,
913 p_parameters => NULL,
914 p_step_type => 'UNIT');
915 END IF;
916
917 WHEN OTHERS THEN
918
919 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
920 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_cart_error :',SQLERRM);
921 END IF;
922 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
923 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_cart_error');
924 IGS_GE_MSG_STACK.ADD;
925 RAISE;
926
927 END create_cart_error;
928
929 PROCEDURE create_sua(p_person_id IN NUMBER,
930 p_course_cd IN VARCHAR2,
931 p_uoo_id IN NUMBER,
932 p_load_cal_type IN VARCHAR2,
933 p_load_sequence_number IN NUMBER,
934 p_audit_requested IN VARCHAR2,
935 p_enr_method IN VARCHAR2,
936 p_override_cp IN NUMBER,
937 p_gradsch_cd IN VARCHAR2,
938 p_gs_version_number IN NUMBER,
939 p_calling_obj IN VARCHAR2,
940 p_return_status OUT NOCOPY VARCHAR2,
941 p_message OUT NOCOPY VARCHAR2) AS
942
943 -------------------------------------------------------------------------------------------
944 --Created by : Basanth Kumar D, Oracle IDC
945 --Date created: 29-JUL-2005
946 -- Purpose : creates student unit attempt.
947 --Change History:
948 --Who When What
949 --bdeviset 03-NOV-2005 Modified when others then in exception block for bug# 4706405
950
951 -------------------------------------------------------------------------------------------
952
953 PRAGMA AUTONOMOUS_TRANSACTION;
954
955 -- cursor to get the person number
956 CURSOR c_get_per_num IS
957 SELECT party_number
958 FROM HZ_PARTIES
959 WHERE party_id = p_person_id;
960
961 -- cursor to get the rowid of the plannig units table
962 CURSOR c_get_rowid IS
963 SELECT ROWID
964 FROM igs_en_plan_units
965 WHERE person_id = p_person_id
966 AND course_cd = p_course_cd
967 AND uoo_id = p_uoo_id
968 AND cart_error_flag = 'Y';
969
970 l_core_indicator_code igs_en_su_attempt.core_indicator_code%TYPE;
971 l_person_number hz_parties.party_number%TYPE;
972 l_rowid VARCHAR2(30);
973 l_enc_message_name VARCHAR2(2000);
974 l_app_short_name VARCHAR2(100);
975 l_msg_index NUMBER;
976 l_message_name VARCHAR2(4000);
977 l_unit_sec VARCHAR2(100);
978
979
980 BEGIN
981
982 -- get the core indicator
986 p_uoo_id => p_uoo_id);
983 l_core_indicator_code := igs_en_gen_009.enrp_check_usec_core(
984 p_person_id => p_person_id,
985 p_program_cd => p_course_cd,
987
988 OPEN c_get_per_num;
989 FETCH c_get_per_num INTO l_person_number;
990 CLOSE c_get_per_num;
991
992 igs_ss_en_wrappers.insert_into_enr_worksheet
993 (p_person_number => l_person_number,
994 p_course_cd => p_course_cd ,
995 p_uoo_id => p_uoo_id,
996 p_waitlist_ind => 'N',
997 p_session_id => igs_en_add_units_api.g_ss_session_id,
998 p_return_status => p_return_status,
999 p_message => p_message,
1000 p_cal_type => p_load_cal_type,
1001 p_ci_sequence_number => p_load_sequence_number,
1002 p_audit_requested => p_audit_requested,
1003 p_enr_method => p_enr_method,
1004 p_override_cp => p_override_cp,
1005 p_subtitle => NULL,
1006 p_gradsch_cd => p_gradsch_cd,
1007 p_gs_version_num => p_gs_version_number,
1008 p_core_indicator_code => l_core_indicator_code,
1009 p_calling_obj => p_calling_obj);
1010
1011 IF p_return_status = 'D' AND p_message IS NOT NULL THEN
1012 p_return_status := 'FALSE';
1013 ROLLBACK;
1014 RETURN;
1015 ELSE
1016 p_return_status := NULL;
1017 END IF;
1018
1019 OPEN c_get_rowid;
1020 FETCH c_get_rowid INTO l_rowid;
1021 IF c_get_rowid%FOUND THEN
1022 CLOSE c_get_rowid;
1023
1024 IGS_EN_PLAN_UNITS_PKG.delete_row(x_rowid => l_rowid);
1025
1026 ELSE
1027 CLOSE c_get_rowid;
1028 END IF;
1029
1030 COMMIT;
1031 EXCEPTION
1032
1033
1034 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1035 -- get message from stack and insert into warn record.
1036 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
1037 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
1038 ROLLBACK;
1039 IF l_message_name IS NOT NULL THEN
1040
1041 l_unit_sec := get_unit_sec(p_uoo_id);
1042 p_return_status := 'FALSE'; -- indiacted unit attempt is not created
1043 IF l_message_name IN ('IGS_GE_RECORD_ALREADY_EXISTS','IGS_GE_MULTI_ORG_DUP_REC') THEN
1044 p_message := 'IGS_EN_SUA_EXISTS'||'*'||l_unit_sec;
1045 RETURN;
1046 END IF;
1047
1048 igs_en_drop_units_api.create_ss_warning (
1049 p_person_id => p_person_id,
1050 p_course_cd => p_course_cd,
1051 p_term_cal_type=>p_load_cal_type,
1052 p_term_ci_sequence_number => p_load_sequence_number,
1053 p_uoo_id => p_uoo_id,
1054 p_message_for => l_unit_sec,
1055 p_message_icon=>'D',
1056 p_message_name => l_message_name,
1057 p_message_rule_text => NULL,
1058 p_message_tokens => NULL,
1059 p_message_action=> NULL,
1060 p_destination =>NULL,
1061 p_parameters => NULL,
1062 p_step_type => 'UNIT');
1063
1064
1065
1066 END IF;
1067
1068 WHEN OTHERS THEN
1069
1070 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1071 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_sua :',SQLERRM);
1072 END IF;
1073 ROLLBACK;
1074 IF SQLCODE = -1 THEN
1075 l_unit_sec := get_unit_sec(p_uoo_id);
1076 p_return_status := 'FALSE';
1077 p_message := 'IGS_EN_SUA_EXISTS'||'*'||l_unit_sec;
1078 RETURN;
1079 ELSE
1080 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1081 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_sua');
1082 IGS_GE_MSG_STACK.ADD;
1083 RAISE;
1084 END IF;
1085
1086
1087 END create_sua;
1088
1089 PROCEDURE create_sua_from_plan(p_person_Id IN NUMBER,
1090 p_course_cd IN VARCHAR2,
1091 p_uoo_id IN NUMBER,
1092 p_load_cal_type IN VARCHAR2,
1093 p_load_sequence_number IN NUMBER,
1094 p_audit_requested IN VARCHAR2,
1095 p_waitlist_ind IN VARCHAR2,
1096 p_enr_method IN VARCHAR2,
1097 p_override_cp IN NUMBER,
1098 p_gradsch_cd IN VARCHAR2,
1099 p_gs_version_number IN NUMBER,
1100 p_calling_obj IN VARCHAR2,
1104 --Created by : Basanth Kumar D, Oracle IDC
1101 p_message OUT NOCOPY VARCHAR2,
1102 p_return_status OUT NOCOPY VARCHAR2) AS
1103 -------------------------------------------------------------------------------------------
1105 --Date created: 29-JUL-2005
1106 -- Purpose : creates unit attempt and deletes the record in the planning units table
1107 --Change History:
1108 --Who When What
1109
1110 -------------------------------------------------------------------------------------------
1111
1112
1113 -- cursor to get the person number
1114 CURSOR c_get_per_num IS
1115 SELECT party_number
1116 FROM HZ_PARTIES
1117 WHERE party_id = p_person_id;
1118
1119 -- cursor to get the rowid from the plaannig units table
1120 CURSOR c_get_rowid IS
1121 SELECT ROWID
1122 FROM igs_en_plan_units
1123 WHERE person_id = p_person_id
1124 AND course_cd = p_course_cd
1125 AND uoo_id = p_uoo_id
1126 AND cart_error_flag = 'Y';
1127
1128 l_core_indicator_code igs_en_su_attempt.core_indicator_code%TYPE;
1129 l_person_number hz_parties.party_number%TYPE;
1130 l_rowid VARCHAR2(30);
1131 l_enc_message_name VARCHAR2(2000);
1132 l_app_short_name VARCHAR2(100);
1133 l_msg_index NUMBER;
1134 l_message_name VARCHAR2(4000);
1135 l_unit_sec VARCHAR2(100);
1136 l_message_tokens VARCHAR2(200);
1137
1138 BEGIN
1139
1140 -- get the core indicator
1141 l_core_indicator_code := igs_en_gen_009.enrp_check_usec_core(
1142 p_person_id => p_person_id,
1143 p_program_cd => p_course_cd,
1144 p_uoo_id => p_uoo_id);
1145
1146 SAVEPOINT sp_plan_wrappers;
1147
1148 OPEN c_get_per_num;
1149 FETCH c_get_per_num INTO l_person_number;
1150 CLOSE c_get_per_num;
1151
1152 igs_ss_en_wrappers.insert_into_enr_worksheet
1153 (p_person_number => l_person_number,
1154 p_course_cd => p_course_cd ,
1155 p_uoo_id => p_uoo_id,
1156 p_waitlist_ind => p_waitlist_ind,
1157 p_session_id => igs_en_add_units_api.g_ss_session_id,
1158 p_return_status => p_return_status,
1159 p_message => p_message,
1160 p_cal_type => p_load_cal_type,
1161 p_ci_sequence_number => p_load_sequence_number,
1162 p_audit_requested => p_audit_requested,
1163 p_enr_method => p_enr_method,
1164 p_override_cp => p_override_cp,
1165 p_subtitle => NULL,
1166 p_gradsch_cd => p_gradsch_cd,
1167 p_gs_version_num => p_gs_version_number,
1168 p_core_indicator_code => l_core_indicator_code,
1169 p_calling_obj => p_calling_obj);
1170
1171 IF p_return_status = 'D' AND p_message IS NOT NULL THEN
1172 p_return_status := 'FALSE';
1173 ROLLBACK TO sp_plan_wrappers;
1174 RETURN;
1175 ELSE
1176 p_return_status := NULL;
1177 END IF;
1178
1179 -- if the unit is not waitlisted then only delete the record
1180 IF p_waitlist_ind = 'N' THEN
1181
1182 OPEN c_get_rowid;
1183 FETCH c_get_rowid INTO l_rowid;
1184 IF c_get_rowid%FOUND THEN
1185 CLOSE c_get_rowid;
1186
1187 IGS_EN_PLAN_UNITS_PKG.delete_row(x_rowid => l_rowid);
1188
1189 ELSE
1190 CLOSE c_get_rowid;
1191 END IF;
1192 END IF;
1193
1194 EXCEPTION
1195
1196
1197 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1198 -- get message from stack and insert into warn record.
1199 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
1200 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
1201
1202 IF l_message_name IS NOT NULL THEN
1203
1204 l_message_tokens := NULL;
1205 l_unit_sec := get_unit_sec(p_uoo_id);
1206 ROLLBACK TO sp_plan_wrappers;
1207 IF l_message_name IN ('IGS_GE_RECORD_ALREADY_EXISTS','IGS_GE_MULTI_ORG_DUP_REC') THEN
1208 l_message_name := 'IGS_EN_SUA_EXISTS';
1209 l_message_tokens := 'UNIT_CD'||':'||l_unit_sec||';';
1210 END IF;
1211 igs_en_drop_units_api.create_ss_warning (
1212 p_person_id => p_person_id,
1213 p_course_cd => p_course_cd,
1214 p_term_cal_type=>p_load_cal_type,
1215 p_term_ci_sequence_number => p_load_sequence_number,
1216 p_uoo_id => p_uoo_id,
1217 p_message_for => l_unit_sec,
1218 p_message_icon=>'D',
1219 p_message_name => l_message_name,
1220 p_message_rule_text => NULL,
1224 p_parameters => NULL,
1221 p_message_tokens => l_message_tokens,
1222 p_message_action=> NULL,
1223 p_destination =>NULL,
1225 p_step_type => 'UNIT');
1226
1227
1228 END IF;
1229
1230 WHEN OTHERS THEN
1231
1232 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1233 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_sua_from_plan :',SQLERRM);
1234 END IF;
1235 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1236 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_sua_from_plan');
1237 IGS_GE_MSG_STACK.ADD;
1238 RAISE;
1239
1240 END create_sua_from_plan;
1241
1242
1243 PROCEDURE get_perm_wlst_setup(
1244 p_person_id IN NUMBER,
1245 p_course_cd IN VARCHAR2,
1246 p_load_cal_type IN VARCHAR2,
1247 p_load_sequence_number IN NUMBER ,
1248 p_unit_params IN OUT NOCOPY t_params_table,
1249 p_message OUT NOCOPY VARCHAR2,
1250 p_return_status OUT NOCOPY VARCHAR2,
1251 p_chk_waitlist IN VARCHAR2) AS
1252
1253 -------------------------------------------------------------------------------------------
1254 --Created by : Basanth Kumar D, Oracle IDC
1255 --Date created: 29-JUL-2005
1256 -- Purpose : This procedure loops through all units and sets the waitlist and special/audit permission status of the units.
1257 --Change History:
1258 --Who When What
1259
1260 -------------------------------------------------------------------------------------------
1261
1262 l_message_name VARCHAR2(4000);
1263 l_audit_msg_name VARCHAR2(100);
1264 l_return_status VARCHAR2(100);
1265 l_audit_status VARCHAR2(100);
1266 l_usec_status igs_ps_unit_ofr_opt.unit_section_status%TYPE;
1267 l_waitlist_ind VARCHAR2(10);
1268 l_enc_message_name VARCHAR2(2000);
1269 l_app_short_name VARCHAR2(100);
1270 l_msg_index NUMBER;
1271 l_unit_sec VARCHAR2(100);
1272
1273
1274 BEGIN
1275
1276 p_message := NULL;
1277 p_return_status := NULL;
1278
1279 FOR i IN 1..p_unit_params.COUNT LOOP
1280
1281 BEGIN
1282 igs_en_gen_015.check_spl_perm_exists(
1283 p_person_id => p_person_id,
1284 p_person_type => g_person_type,
1285 p_program_cd => p_course_cd,
1286 p_cal_type => p_load_cal_type,
1287 p_ci_sequence_number => p_load_sequence_number,
1288 p_uoo_id=> p_unit_params(i).uoo_id,
1289 p_check_audit => p_unit_params(i).ass_ind,
1290 p_message_name => l_message_name,
1291 p_return_status => l_return_status,
1292 p_audit_status => l_audit_status,
1293 p_audit_msg_name => l_audit_msg_name
1294 );
1295
1296
1297 IF l_return_status = 'SPL_NREQ' THEN
1298 p_unit_params(i).spl_perm_step := 'PERM_NREQ';
1299
1300 ELSIF l_return_status = 'SPL_REQ' THEN
1301 p_unit_params(i).spl_perm_step := 'SPL_REQ';
1302
1303 ELSIF l_return_status = 'SPL_ERR' THEN
1304 IF l_message_name = 'IGS_SS_EN_INS_MORE_INFO' THEN
1305 --implies more information requested
1306 p_unit_params(i).spl_perm_step := 'SPL_MORE_INFO';
1307
1308 ELSIF l_message_name = 'IGS_SS_EN_STD_MORE_INFO' THEN
1309 --implies request is pending
1310 p_unit_params(i).spl_perm_step := 'SPL_PEND';
1311
1312 ELSIF l_message_name = 'IGS_SS_EN_INS_DENY' THEN
1313 --implies request is denied
1314 p_unit_params(i).spl_perm_step := 'SPL_DENY';
1315 ELSE
1316 p_return_status := 'FALSE';
1317 p_message := l_message_name;
1318 RETURN;
1319 END IF; -- IF l_message_name = 'IGS_SS_EN_INS_MORE_INFO' THEN
1320
1321 END IF;
1322
1323 IF l_audit_status = 'AUDIT_NREQ' THEN
1324 p_unit_params(i).aud_perm_step := 'PERM_NREQ';
1325 ELSIF l_audit_status = 'AUDIT_REQ' THEN
1326 p_unit_params(i).aud_perm_step := 'AUD_REQ';
1327
1328 ELSIF l_audit_status = 'AUDIT_ERR' THEN
1329 IF l_audit_msg_name = 'IGS_EN_AU_INS_MORE_INFO' THEN
1330 --implies more information requested
1331 p_unit_params(i).aud_perm_step := 'AUD_MORE_INFO';
1332
1333 ELSIF l_audit_msg_name = 'IGS_EN_AU_STD_MORE_INFO' THEN
1334 --implies request is pending
1335 p_unit_params(i).aud_perm_step := 'AUD_PEND';
1336
1337 ELSIF l_audit_msg_name = 'IGS_EN_AU_INS_DENY' THEN
1338 --implies request is denied
1339 p_unit_params(i).aud_perm_step := 'AUD_DENY';
1340 ELSE
1341 p_return_status := 'FALSE';
1342 p_message := l_audit_msg_name;
1343 RETURN;
1344 END IF; -- IF l_message_name = 'IGS_EN_AU_INS_MORE_INFO' THEN
1345
1346 END IF;
1347 if p_chk_waitlist = 'Y' then
1348 -- now check if unit can be waitlisted or enrolled
1349 igs_en_gen_015.get_usec_status(
1353 p_load_cal_type => p_load_cal_type,
1350 p_person_id => p_person_id,
1351 p_course_cd => p_course_cd,
1352 p_uoo_id => p_unit_params(i).uoo_id,
1354 p_load_ci_sequence_number => p_load_sequence_number,
1355 p_unit_section_status => l_usec_status ,
1356 p_waitlist_ind => l_waitlist_ind );
1357
1358 IF l_waitlist_ind = 'N' THEN
1359 p_unit_params(i).wlst_step := 'N';
1360
1361 ELSIF l_waitlist_ind = 'Y' THEN
1362 p_unit_params(i).wlst_step := 'Y';
1363 ELSE
1364 p_unit_params(i).wlst_step := 'E';
1365 END IF;
1366 end if;
1367
1368 EXCEPTION
1369
1370 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1371 -- get message from stack and insert into warn record.
1372 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
1373 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
1374
1375 IF l_message_name IS NOT NULL THEN
1376
1377 l_unit_sec := get_unit_sec(p_unit_params(i).uoo_id);
1378
1379 igs_en_drop_units_api.create_ss_warning (
1380 p_person_id => p_person_id,
1381 p_course_cd => p_course_cd,
1382 p_term_cal_type=>p_load_cal_type,
1383 p_term_ci_sequence_number => p_load_sequence_number,
1384 p_uoo_id => p_unit_params(i).uoo_id,
1385 p_message_for => l_unit_sec,
1386 p_message_icon=>'D',
1387 p_message_name => l_message_name,
1388 p_message_rule_text => NULL,
1389 p_message_tokens => NULL,
1390 p_message_action=> NULL,
1391 p_destination =>NULL,
1392 p_parameters => NULL,
1393 p_step_type => 'UNIT');
1394
1395
1396
1397 END IF;
1398
1399 WHEN OTHERS THEN
1400 RAISE;
1401
1402 END; -- end of BEGIN
1403
1404 END LOOP;
1405
1406 EXCEPTION
1407
1408 WHEN OTHERS THEN
1409
1410 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1411 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.get_perm_wlst_setup :',SQLERRM);
1412 END IF;
1413 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1414 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.get_perm_wlst_setup');
1415 IGS_GE_MSG_STACK.ADD;
1416 RAISE;
1417 END get_perm_wlst_setup;
1418
1419 FUNCTION permission_required(
1420 p_person_id IN NUMBER,
1421 p_course_cd IN VARCHAR2,
1422 p_load_cal_type IN VARCHAR2,
1423 p_load_sequence_number IN NUMBER,
1424 p_uoo_id IN NUMBER,
1425 p_spl_perm_step IN VARCHAR2,
1426 p_aud_perm_step IN VARCHAR2,
1427 p_audit_requested IN VARCHAR2,
1428 p_override_cp IN VARCHAR2,
1429 p_gradsch_cd IN VARCHAR2,
1430 p_gs_version_number IN NUMBER,
1431 p_message OUT NOCOPY VARCHAR2,
1432 p_calling_obj IN VARCHAR2
1433 ) RETURN BOOLEAN AS
1434
1435 -------------------------------------------------------------------------------------------
1436 --Created by : Basanth Kumar D, Oracle IDC
1437 --Date created: 29-JUL-2005
1438 -- Purpose :
1439 -- This procedure checks if any unit requires special/audit permission based on the p_perm_step
1440 -- parameter and logs the appropriate warnings record. In addition, a planning sheet record with a
1441 -- cart flag is created since these units need to appear in the cart region even though the unit is
1442 -- not added to the unit attempt table. The procedure returns true if a unit requires special/audit
1443 -- permission and a warning record is created. If a unit does not require permission it returns false,
1444 -- hence the calling procedure can proceed with creating unconfirm unit attempts
1445
1446 --Change History:
1447 --Who When What
1448
1449 -------------------------------------------------------------------------------------------
1450
1451 -- cursor to get the superior unit of a unit
1452 CURSOR c_fetch_sup(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
1453 SELECT sup_uoo_id
1454 FROM IGS_PS_UNIT_OFR_OPT
1455 WHERE uoo_id = cp_uoo_id
1456 AND relation_type = 'SUBORDINATE';
1457
1458 -- cursor to get request id
1459 CURSOR c_get_request_id (cp_request_type IGS_EN_SPL_PERM.request_type%TYPE) IS
1460 SELECT spl_perm_request_id
1461 FROM igs_en_spl_perm
1462 WHERE student_person_id = p_person_id
1463 AND uoo_id = p_uoo_id
1464 AND request_type = cp_request_type;
1465
1466 --cursor to check if unit is preenrolled
1467
1468 CURSOR cur_is_unit_preenroled IS
1469 SELECT 'Y'
1470 FROM IGS_EN_SU_ATTEMPT
1471 WHERE person_id = p_person_id
1472 AND course_cd = p_course_cd
1473 AND uoo_id = p_uoo_id ;
1474
1475
1476 l_sup_uoo_id igs_en_su_attempt.uoo_id%TYPE;
1477 l_unit_sec VARCHAR2(50);
1478 l_message_icon VARCHAR2(1);
1479 l_message_name VARCHAR2(4000);
1480 l_message_action VARCHAR2(100);
1484 l_preenrol_unit VARCHAR2(1);
1481 l_destination VARCHAR2(100);
1482 l_parameters VARCHAR2(1000);
1483 l_request_id IGS_EN_SPL_PERM.spl_perm_request_id%TYPE;
1485
1486 BEGIN
1487
1488 IF ( p_spl_perm_step = 'PERM_NREQ' AND p_aud_perm_step = 'PERM_NREQ')THEN
1489 RETURN FALSE;
1490 END IF;
1491 -- If control reaches beyond the above point, implies step is setup and a unit attempt cannot be created.
1492
1493 l_unit_sec := get_unit_sec(p_uoo_id);
1494
1495 -- Check if calilng page is SWAP, then create an warning record as 'DENY' since this is an error condition in case of swap.
1496 IF p_calling_obj IN ('SWAP','SUBMITSWAP') THEN
1497 --in swap, permission is a deny cgeck whether it is audit or special
1498 if (p_spl_perm_step is not null and p_spl_perm_step = 'PERM_NREQ') then
1499
1500 igs_en_drop_units_api.create_ss_warning (
1501 p_person_id => p_person_id,
1502 p_course_cd => p_course_cd,
1503 p_term_cal_type => p_load_cal_type,
1504 p_term_ci_sequence_number => p_load_sequence_number,
1505 p_uoo_id => p_uoo_id,
1506 p_message_for => l_unit_sec,
1507 p_message_icon => 'D',
1508 p_message_name => 'IGS_EN_SWP_AUD_NOT_ALLOWED',
1509 p_message_rule_text => NULL,
1510 p_message_tokens => NULL,
1511 p_message_action => NULL,
1512 p_destination => NULL,
1513 p_parameters => NULL,
1514 p_step_type => 'UNIT');
1515
1516 end if;
1517 if (p_aud_perm_step is not null and p_aud_perm_step = 'PERM_NREQ') then
1518
1519
1520
1521 igs_en_drop_units_api.create_ss_warning (
1522 p_person_id => p_person_id,
1523 p_course_cd => p_course_cd,
1524 p_term_cal_type => p_load_cal_type,
1525 p_term_ci_sequence_number => p_load_sequence_number,
1526 p_uoo_id => p_uoo_id,
1527 p_message_for => l_unit_sec,
1528 p_message_icon => 'D',
1529 p_message_name => 'IGS_EN_SWP_SPL_NOT_ALLOWED',
1530 p_message_rule_text => NULL,
1531 p_message_tokens => NULL,
1532 p_message_action => NULL,
1533 p_destination => NULL,
1534 p_parameters => NULL,
1535 p_step_type => 'UNIT');
1536 end if;
1537
1538
1539
1540 ELSE
1541 -- create planning sheet error record in case of submit plan
1542 IF p_calling_obj = 'SUBMITPLAN' THEN
1543 -- create planning sheet cart error record in the same transaction
1544 --for all units except preenrolled units.
1545 OPEN cur_is_unit_preenroled;
1546 FETCH cur_is_unit_preenroled into l_preenrol_unit;
1547 CLOSE cur_is_unit_preenroled;
1548
1549 if nvl(l_preenrol_unit,'N') <> 'Y' THEN
1550 create_cart_error( p_person_id,
1551 p_course_cd,
1552 p_load_cal_type,
1553 p_load_sequence_number,
1554 p_uoo_id,
1555 l_sup_uoo_id,
1556 'Y',
1557 p_audit_requested, -- ass ind
1558 p_override_cp,
1559 p_gradsch_cd,
1560 p_gs_version_number
1561 );
1562 end if;
1563 -- dont create any planning sheet record for plan
1564 ELSIF p_calling_obj <> 'PLAN' THEN
1565 create_ps_record(p_person_id,
1566 p_course_cd,
1567 p_load_cal_type,
1568 p_load_sequence_number,
1569 p_uoo_id,
1570 l_sup_uoo_id,
1571 'Y',
1572 p_audit_requested, -- ass ind
1573 p_override_cp,
1574 p_gradsch_cd,
1575 p_gs_version_number
1576 );
1577 END IF;
1578
1579 IF p_spl_perm_step = 'SPL_REQ' THEN
1580
1581 l_message_icon := 'D';
1582 l_message_name := 'IGS_EN_NOSPL_TAB_DENY'; -- message for "submit special perm request" ,
1583 l_parameters := 'SPL_PERM'; -- pass pRequestType
1584 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1585 p_lookup_code => 'REQ_SPL',
1586 p_lookup_type => 'IGS_EN_WARN_LINKS');
1587
1588 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1589 l_destination := 'IGS_EN_CART_SPPERMREQINF_STUD';
1590 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1591 l_destination := 'IGS_EN_PLAN_SPPERMREQINF_STUD';
1592 END IF;
1593
1594
1595 ELSIF p_spl_perm_step = 'SPL_MORE_INFO' THEN
1596
1597 OPEN c_get_request_id('SPL_PERM');
1598 FETCH c_get_request_id INTO l_request_id;
1599 CLOSE c_get_request_id;
1600
1601 l_message_icon := 'D';
1602 l_message_name := 'IGS_EN_MISPL_TAB_DENY'; -- message for "submit more information" , ,
1603 l_parameters := 'SPL_PERM'||','||l_request_id||','||'Y'; -- pass pRequestType,pRequestId,pMoreInfo
1604 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1605 p_lookup_code => 'REQ_SPLADDINFO',
1609 l_destination := 'IGS_EN_CART_SPL_STUD_DETAILS';
1606 p_lookup_type => 'IGS_EN_WARN_LINKS');
1607
1608 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1610 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1611 l_destination := 'IGS_EN_PLAN_SPL_STUD_DETAILS';
1612 END IF;
1613
1614 ELSIF p_spl_perm_step = 'SPL_PEND' THEN
1615
1616 OPEN c_get_request_id('SPL_PERM');
1617 FETCH c_get_request_id INTO l_request_id;
1618 CLOSE c_get_request_id;
1619
1620 l_message_icon := 'P';
1621 l_message_name := 'IGS_EN_PENSPL_TAB_INFO'; -- message for "view special perm request details" ,
1622 l_parameters := 'SPL_PERM'||','||l_request_id||','||'N'; -- pass pRequestType,pRequestId,pMoreInfo
1623 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1624 p_lookup_code => 'SPLPERM_DETS',
1625 p_lookup_type => 'IGS_EN_WARN_LINKS');
1626
1627
1628 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1629 l_destination := 'IGS_EN_CART_SPL_STUD_DETAILS';
1630 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1631 l_destination := 'IGS_EN_PLAN_SPL_STUD_DETAILS';
1632 END IF;
1633
1634 ELSIF p_spl_perm_step = 'SPL_DENY' THEN
1635 OPEN c_get_request_id('SPL_PERM');
1636 FETCH c_get_request_id INTO l_request_id;
1637 CLOSE c_get_request_id;
1638
1639 l_message_icon := 'D';
1640 l_message_name := 'IGS_EN_REJSPL_TAB_DENY'; --message for "request denied" ,
1641 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1642 p_lookup_code => 'SPLPERM_DETS',
1643 p_lookup_type => 'IGS_EN_WARN_LINKS');
1644
1645 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1646 l_destination := 'IGS_EN_CART_SPL_STUD_DETAILS';
1647 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1648 l_destination := 'IGS_EN_PLAN_SPL_STUD_DETAILS';
1649 END IF;
1650
1651 l_parameters := 'SPL_PERM'||','||l_request_id||','||'N'; -- pass pRequestType,pRequestId,pMoreInfo
1652 END IF;
1653
1654 IF p_spl_perm_step <> 'PERM_NREQ' THEN
1655
1656 igs_en_drop_units_api.create_ss_warning (
1657 p_person_id => p_person_id,
1658 p_course_cd => p_course_cd,
1659 p_term_cal_type => p_load_cal_type,
1660 p_term_ci_sequence_number => p_load_sequence_number,
1661 p_uoo_id => p_uoo_id,
1662 p_message_for => l_unit_sec,
1663 p_message_icon => l_message_icon,
1664 p_message_name => l_message_name,
1665 p_message_rule_text => NULL,
1666 p_message_tokens => NULL,
1667 p_message_action => l_message_action,
1668 p_destination => l_destination, -- p_destination
1669 p_parameters => l_parameters, -- p_parameters
1670 p_step_type => 'UNIT');
1671 END IF;
1672
1673
1674 IF p_aud_perm_step = 'AUD_REQ' THEN
1675
1676 l_message_icon := 'D';
1677 l_message_name := 'IGS_EN_NOAUD_TAB_DENY'; -- message for "submit Aud perm request" ,
1678 l_parameters := 'AUDIT_PERM'; -- pass pRequestType
1679 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1680 p_lookup_code => 'REQ_AUD',
1681 p_lookup_type => 'IGS_EN_WARN_LINKS');
1682 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1683 l_destination := 'IGS_EN_CART_SPPERMREQINF_STUD';
1684 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1685 l_destination := 'IGS_EN_PLAN_SPPERMREQINF_STUD';
1686 END IF;
1687
1688 ELSIF p_aud_perm_step = 'AUD_MORE_INFO' THEN
1689
1690 OPEN c_get_request_id('AUDIT_PERM');
1691 FETCH c_get_request_id INTO l_request_id;
1692 CLOSE c_get_request_id;
1693
1694 l_message_icon := 'D';
1695 l_message_name := 'IGS_EN_MIAUD_TAB_DENY'; --message for "submit more information" ,
1696 l_parameters := 'AUDIT_PERM'||','||l_request_id||','||'Y'; -- pass pRequestType,pRequestId,pMoreInfo
1697 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1698 p_lookup_code => 'REQ_AUDADDINFO',
1699 p_lookup_type => 'IGS_EN_WARN_LINKS');
1700 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1701 l_destination := 'IGS_EN_CART_SPL_STUD_DETAILS';
1702 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1703 l_destination := 'IGS_EN_PLAN_SPL_STUD_DETAILS';
1704 END IF;
1705
1706 ELSIF p_aud_perm_step = 'AUD_PEND' THEN
1707
1708 OPEN c_get_request_id('AUDIT_PERM');
1709 FETCH c_get_request_id INTO l_request_id;
1710 CLOSE c_get_request_id;
1711
1712 l_message_icon := 'P';
1713 l_message_name := 'IGS_EN_PENAUD_TAB_INFO'; --message for "request pending , view details" ,
1714 l_parameters := 'AUDIT_PERM'||','||l_request_id||','||'N'; -- pass pRequestType,pRequestId,pMoreInfo
1715 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1719 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1716 p_lookup_code => 'AUDPERM_DETS',
1717 p_lookup_type => 'IGS_EN_WARN_LINKS');
1718
1720 l_destination := 'IGS_EN_CART_SPL_STUD_DETAILS';
1721 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1722 l_destination := 'IGS_EN_PLAN_SPL_STUD_DETAILS';
1723 END IF;
1724
1725 ELSIF p_aud_perm_step = 'AUD_DENY' THEN
1726
1727 l_message_icon := 'D';
1728 l_message_name := 'IGS_EN_REJAUD_TAB_DENY'; --message for "Request denied" ,
1729 l_parameters := 'AUDIT_PERM'||','||l_request_id||','||'N'; -- pass pRequestType,pRequestId,pMoreInfo
1730 l_message_action := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
1731 p_lookup_code => 'AUDPERM_DETS',
1732 p_lookup_type => 'IGS_EN_WARN_LINKS');
1733
1734 IF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
1735 l_destination := 'IGS_EN_CART_SPL_STUD_DETAILS';
1736 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
1737 l_destination := 'IGS_EN_PLAN_SPL_STUD_DETAILS';
1738 END IF;
1739
1740 END IF;
1741
1742 IF p_aud_perm_step <> 'PERM_NREQ' THEN
1743
1744 igs_en_drop_units_api.create_ss_warning (
1745 p_person_id => p_person_id,
1746 p_course_cd => p_course_cd,
1747 p_term_cal_type => p_load_cal_type,
1748 p_term_ci_sequence_number => p_load_sequence_number,
1749 p_uoo_id => p_uoo_id,
1750 p_message_for => l_unit_sec,
1751 p_message_icon => l_message_icon,
1752 p_message_name => l_message_name,
1753 p_message_rule_text => NULL,
1754 p_message_tokens => NULL,
1755 p_message_action => l_message_action,
1756 p_destination => l_destination, -- p_destination
1757 p_parameters => l_parameters, -- p_parameters
1758 p_step_type => 'UNIT');
1759 END IF;
1760
1761 END IF;
1762
1763 RETURN TRUE; -- -- permission reqd
1764
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768
1769 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1770 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.permission_required :',SQLERRM);
1771 END IF;
1772 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1773 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.permission_required');
1774 IGS_GE_MSG_STACK.ADD;
1775 RAISE;
1776 END permission_required;
1777
1778
1779
1780 FUNCTION waitlist_required (
1781 p_person_id IN NUMBER,
1782 p_course_cd IN VARCHAR2,
1783 p_uoo_id IN NUMBER,
1784 p_waitlist_ind IN VARCHAR2,
1785 p_load_cal_type IN VARCHAR2,
1786 p_load_sequence_number IN NUMBER,
1787 p_audit_requested IN VARCHAR2,
1788 p_enr_method IN VARCHAR2,
1789 p_override_cp IN VARCHAR2,
1790 p_subtitle IN VARCHAR2,
1791 p_gradsch_cd IN VARCHAR2,
1792 p_gs_version_number IN NUMBER,
1793 p_create_wlst OUT NOCOPY VARCHAR,
1794 p_calling_obj IN VARCHAR2,
1795 p_message OUT NOCOPY VARCHAR2
1796 ) RETURN BOOLEAN AS
1797 -------------------------------------------------------------------------------------------
1798 --Created by : Basanth Kumar D, Oracle IDC
1799 --Date created: 29-JUL-2005
1800 --Purpose :
1801 --This procedure returns true if a waitlist record was created or if a warning
1802 --record was created. False implies the calling procedure can continue to enroll
1803 --the student into these units. The out paramter p_message is populated only
1804 --in case of setup errors or exceptions.
1805
1806 --Change History:
1807 --Who When What
1808
1809 -------------------------------------------------------------------------------------------
1810
1811 -- cursor to get the person number
1812 CURSOR c_get_per_num IS
1813 SELECT party_number
1814 FROM HZ_PARTIES
1815 WHERE party_id = p_person_id;
1816
1817 l_person_number HZ_PARTIES.party_number%TYPE;
1818
1819 -- cursor to fetch the superior units
1820 CURSOR c_fetch_sup(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
1821 SELECT sup_uoo_id
1822 FROM IGS_PS_UNIT_OFR_OPT
1823 WHERE uoo_id = cp_uoo_id
1824 AND relation_type = 'SUBORDINATE';
1825
1826 l_unit_sec VARCHAR2(50);
1827 l_core_indicator_code igs_en_su_attempt.core_indicator_code%TYPE;
1828 l_sup_uoo_id igs_en_su_attempt.uoo_id%TYPE;
1829 l_return_status VARCHAR2(100);
1830 l_enc_message_name VARCHAR2(2000);
1831 l_app_short_name VARCHAR2(100);
1832 l_msg_index NUMBER;
1833 l_message_name VARCHAR2(100);
1834
1835 BEGIN
1836
1837 l_unit_sec := get_unit_sec(p_uoo_id);
1838
1839 OPEN c_fetch_sup(p_uoo_id);
1840 FETCH c_fetch_sup INTO l_sup_uoo_id;
1844 -- If it is 'Y', implies the unit can be wailisted, "N"
1841 CLOSE c_fetch_sup;
1842
1843 -- p_waitlist_ind indicates the status of the unit section.
1845 -- indicates unit can be enrolled. E indicates an error.
1846
1847 IF p_waitlist_ind = 'N' THEN
1848 RETURN FALSE;
1849
1850 ELSIF p_waitlist_ind = 'E' THEN
1851
1852 igs_en_drop_units_api.create_ss_warning (
1853 p_person_id => p_person_id,
1854 p_course_cd => p_course_cd,
1855 p_term_cal_type => p_load_cal_type,
1856 p_term_ci_sequence_number => p_load_sequence_number,
1857 p_uoo_id => p_uoo_id,
1858 p_message_for => l_unit_sec,
1859 p_message_icon => 'D',
1860 p_message_name => 'IGS_EN_SS_CANNOT_WAITLIST', -- message_name
1861 p_message_rule_text => NULL,
1862 p_message_tokens => NULL,
1863 p_message_action => NULL,
1864 p_destination => NULL, -- p_destination
1865 p_parameters => NULL, -- p_parameters
1866 p_step_type => 'UNIT');
1867
1868 RETURN TRUE;
1869
1870 ELSIF p_waitlist_ind = 'Y' THEN
1871
1872 IF p_calling_obj IN ('SWAP','SUBMITSWAP' ) THEN
1873
1874 --log warning record as error since waitlising is not allowed from swap.
1875 igs_en_drop_units_api.create_ss_warning (
1876 p_person_id => p_person_id,
1877 p_course_cd => p_course_cd,
1878 p_term_cal_type => p_load_cal_type,
1879 p_term_ci_sequence_number => p_load_sequence_number,
1880 p_uoo_id => p_uoo_id,
1881 p_message_for => l_unit_sec,
1882 p_message_icon => 'D',
1883 p_message_name => 'IGS_EN_SWP_WLST_NOT_ALLOWED', -- message_name
1884 p_message_rule_text => NULL,
1885 p_message_tokens => NULL,
1886 p_message_action => NULL,
1887 p_destination => NULL, -- p_destination
1888 p_parameters => NULL, -- p_parameters
1889 p_step_type => 'UNIT');
1890
1891
1892 RETURN TRUE;
1893
1894 ELSE
1895
1896 -- else create a warning record and nsert the record
1897 l_core_indicator_code := igs_en_gen_009.enrp_check_usec_core(
1898 p_person_id => p_person_id,
1899 p_program_cd => p_course_cd,
1900 p_uoo_id => p_uoo_id
1901 );
1902 -- creating warning record for plan
1903 IF p_calling_obj = 'PLAN' THEN
1904
1905 igs_en_drop_units_api.create_ss_warning (
1906 p_person_id => p_person_id,
1907 p_course_cd => p_course_cd,
1908 p_term_cal_type => p_load_cal_type,
1909 p_term_ci_sequence_number => p_load_sequence_number,
1910 p_uoo_id => p_uoo_id,
1911 p_message_for => l_unit_sec,
1912 p_message_icon => 'W',
1913 p_message_name => 'IGS_EN_WLST_AVAIL', -- need to check message_name
1914 p_message_rule_text => NULL,
1915 p_message_tokens => NULL,
1916 p_message_action => NULL,
1917 p_destination => NULL, -- p_destination
1918 p_parameters => NULL, -- p_parameters
1919 p_step_type => 'UNIT');
1920
1921 END IF;
1922
1923 OPEN c_get_per_num;
1924 FETCH c_get_per_num INTO l_person_number;
1925 CLOSE c_get_per_num;
1926
1927 -- incase of submit plan both unoconfirm and waitlisted units cannot be
1928 -- created at the same time as we need to roll back waitlisted units
1929 -- afterwards. So waitlisted units will be created after save point
1930 -- sp_enroll_sua so that we can rollback them
1931 -- incase of plan we will not be creating waitlisted units
1932 IF p_calling_obj NOT IN ( 'SUBMITPLAN','PLAN') THEN
1933
1934 igs_ss_en_wrappers.insert_into_enr_worksheet(
1935 p_person_number => l_person_number,
1936 p_course_cd => p_course_cd ,
1937 p_uoo_id => p_uoo_id,
1938 p_waitlist_ind => 'Y',
1939 p_session_id => igs_en_add_units_api.g_ss_session_id,
1940 p_return_status => l_return_status,
1941 p_message => p_message,
1942 p_cal_type => p_load_cal_type,
1943 p_ci_sequence_number => p_load_sequence_number,
1944 p_audit_requested => p_audit_requested,
1945 p_enr_method => p_enr_method,
1946 p_override_cp => p_override_cp,
1947 p_subtitle => NULL,
1951 p_calling_obj => p_calling_obj);
1948 p_gradsch_cd => p_gradsch_cd,
1949 p_gs_version_num => p_gs_version_number,
1950 p_core_indicator_code =>l_core_indicator_code,
1952
1953 IF l_return_status = 'D' AND p_message IS NOT NULL THEN
1954
1955 RETURN TRUE; -- waitlist is reqd and message is set
1956
1957 END IF;
1958
1959 ELSE
1960 -- set the craete waitlsit flag so that this can be created
1961 -- before enrolling the unit attempts
1962 p_create_wlst := 'Y';
1963
1964 END IF;
1965
1966 -- For submit plan we will be creating planning sheet error record in the same txn
1967 -- for cart,submitcart,schedule and enrolpend we create it in an autonomous txn
1968 IF p_calling_obj IN( 'CART', 'SUBMITCART','SCHEDULE','ENROLPEND') THEN
1969
1970 -- create a warning record only if waiitlsited record has been succesfully created
1971 igs_en_drop_units_api.create_ss_warning (
1972 p_person_id => p_person_id,
1973 p_course_cd => p_course_cd,
1974 p_term_cal_type => p_load_cal_type,
1975 p_term_ci_sequence_number => p_load_sequence_number,
1976 p_uoo_id => p_uoo_id,
1977 p_message_for => l_unit_sec,
1978 p_message_icon => 'W',
1979 p_message_name => 'IGS_EN_WLST_AVAIL', -- need to check message_name
1980 p_message_rule_text => NULL,
1981 p_message_tokens => NULL,
1982 p_message_action => NULL,
1983 p_destination => NULL, -- p_destination
1984 p_parameters => NULL, -- p_parameters
1985 p_step_type => 'UNIT');
1986
1987 --create PS record with cart error flag (autonomous txn)
1988 create_ps_record(p_person_id,
1989 p_course_cd,
1990 p_load_cal_type,
1991 p_load_sequence_number,
1992 p_uoo_id,
1993 l_sup_uoo_id, -- get sup uooid
1994 'Y', -- cart error flag
1995 p_audit_requested,-- ass ind
1996 p_override_cp,
1997 p_gradsch_cd,
1998 p_gs_version_number
1999 );
2000
2001 ELSIF p_calling_obj = 'SUBMITPLAN' THEN
2002
2003 --create cart error record with cart error flag (not an autonomous txn)
2004 -- so that it can rolled back if their is any deny warning record
2005 create_cart_error( p_person_id,
2006 p_course_cd,
2007 p_load_cal_type,
2008 p_load_sequence_number,
2009 p_uoo_id,
2010 l_sup_uoo_id, -- get sup uooid
2011 'Y', -- cart error flag
2012 p_audit_requested,-- ass ind
2013 p_override_cp,
2014 p_gradsch_cd,
2015 p_gs_version_number
2016 );
2017
2018
2019 END IF;
2020
2021 RETURN TRUE;
2022
2023 END IF; -- IF p_calling_obj IN ('SWAP' 'SUBMITSWAP' )
2024
2025
2026 END IF; -- IF p_waitlist_ind = 'N'
2027 RETURN TRUE;
2028
2029 EXCEPTION
2030
2031 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
2032 -- get message from stack and insert into warn record.
2033 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
2034 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
2035
2036 IF l_message_name IS NOT NULL THEN
2037
2038 l_unit_sec := get_unit_sec(p_uoo_id);
2039 igs_en_drop_units_api.create_ss_warning (
2040 p_person_id => p_person_id,
2041 p_course_cd => p_course_cd,
2042 p_term_cal_type=> p_load_cal_type,
2043 p_term_ci_sequence_number => p_load_sequence_number,
2044 p_uoo_id => p_uoo_id,
2045 p_message_for => l_unit_sec,
2046 p_message_icon=> 'D',
2047 p_message_name => l_message_name,
2048 p_message_rule_text => NULL,
2049 p_message_tokens => NULL,
2050 p_message_action=> NULL,
2051 p_destination => NULL,
2052 p_parameters => NULL,
2053 p_step_type => 'UNIT');
2054 END IF;
2055
2056
2057 IF p_waitlist_ind = 'N' THEN
2058 -- indicates the waitlist record is not created
2059 RETURN FALSE;
2060 ELSE
2061 -- waitlist record is created
2062 RETURN TRUE;
2063 END IF;
2064
2065 WHEN OTHERS THEN
2066 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2067 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.waitlist_required :',SQLERRM);
2068 END IF;
2069 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2070 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.waitlist_required');
2071 IGS_GE_MSG_STACK.ADD;
2072 RAISE;
2073 END waitlist_required;
2074
2075
2076 PROCEDURE validate_enr_encmb ( p_person_id IN NUMBER,
2080 p_return_status OUT NOCOPY VARCHAR2,
2077 p_course_cd IN VARCHAR2,
2078 p_load_cal_type IN VARCHAR2,
2079 p_load_sequence_number IN NUMBER,
2081 p_message OUT NOCOPY VARCHAR2
2082 ) AS
2083
2084 -------------------------------------------------------------------------------------------
2085 --Created by : Basanth Kumar D, Oracle IDC
2086 --Date created: 29-JUL-2005
2087 --Purpose :
2088 -- This procedure validates the encumbrance and depending on the message returned
2089 -- throws inline error or logs a warning record
2090
2091 --Change History:
2092 --Who When What
2093
2094 -------------------------------------------------------------------------------------------
2095
2096
2097
2098 l_message_name VARCHAR2(30);
2099 l_message_name2 VARCHAR2(30);
2100 l_return_type VARCHAR2(50);
2101 l_message_for igs_en_std_warnings.message_for%TYPE;
2102
2103 BEGIN
2104 IF NOT IGS_EN_VAL_ENCMB.enrp_val_enr_encmb(p_person_id => p_person_id,
2105 p_course_cd => p_course_cd ,
2106 p_cal_type => p_load_cal_type,
2107 p_ci_sequence_number => p_load_sequence_number,
2108 p_message_name => l_message_name,
2109 p_message_name2 => l_message_name2,
2110 p_return_type => l_return_type,
2111 p_effective_dt => NULL -- default value, it will be calculated internally based on the census date
2112 ) THEN
2113
2114 -- if message is IGS_EN_PERS_HAS_ENCUMB (suspend all services ) and IGS_EN_PRSN_ENCUMB_REVOKING
2115 -- (revoke all services for the person then throw an inline error message
2116 IF l_message_name IN ('IGS_EN_PERS_HAS_ENCUMB','IGS_EN_PRSN_ENCUMB_REVOKING' ) THEN
2117 p_return_status := 'FALSE';
2118 p_message := l_message_name;
2119 RETURN;
2120 ELSIF l_message_name = 'IGS_EN_PRSN_ENR_CRDPOINT' THEN
2121 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
2122 p_lookup_code => 'RSTR_GE_CP',
2123 p_lookup_type => 'ENCMB_EFFECT_TYPE');
2124 ELSIF l_message_name IN ( 'IGS_EN_PRSN_ENR_CRDPNT_VALUE', 'IGS_EN_PRSN_ENRCRDPNT') THEN
2125 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
2126 p_lookup_code => 'RSTR_LE_CP',
2127 p_lookup_type => 'ENCMB_EFFECT_TYPE');
2128 ELSIF l_message_name = 'IGS_EN_PRSN_ATTTYPE_NE_ATT_TY' THEN
2129 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
2130 p_lookup_code => 'RSTR_AT_TY',
2131 p_lookup_type => 'ENCMB_EFFECT_TYPE');
2132 ELSIF l_message_name IS NOT NULL THEN
2133 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
2134 p_lookup_code => 'CRSENCUMB',
2135 p_lookup_type => 'ENROLMENT_STEP_TYPE');
2136 END IF;
2137
2138 IF l_message_name IS NOT NULL THEN
2139 igs_en_drop_units_api.create_ss_warning(
2140 p_person_id => p_person_id,
2141 p_course_cd => p_course_cd,
2142 p_term_cal_type => p_load_cal_type,
2143 p_term_ci_sequence_number => p_load_sequence_number,
2144 p_uoo_id => NULL,
2145 p_message_for => l_message_for,
2146 p_message_icon=> 'D',
2147 p_message_name => l_message_name,
2148 p_message_rule_text => NULL,
2149 p_message_tokens => NULL,
2150 p_message_action => NULL,
2151 p_destination => NULL,
2152 p_parameters => NULL,
2153 p_step_type => 'PROGRAM');
2154 END IF;
2155
2156 l_message_for := NULL;
2157 -- if message is IGS_EN_PERS_HAS_ENCUMB (suspend all services ) and IGS_EN_PRSN_ENCUMB_REVOKING
2158 -- (revoke all services for the person then throw an inline error message
2159 IF l_message_name2 IN ('IGS_EN_PERS_HAS_ENCUMB','IGS_EN_PRSN_ENCUMB_REVOKING') THEN
2160 p_return_status := 'FALSE';
2161 p_message := l_message_name2;
2162 RETURN;
2163 ELSIF l_message_name2 IN ('IGS_EN_PRSN_NOTENR_REQUIRE' ,'IGS_EN_PRSN_DISCONT_REQUNIT') THEN
2164 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
2165 p_lookup_code => 'RQRD_CRS_U',
2166 p_lookup_type => 'ENCMB_EFFECT_TYPE');
2167 ELSIF l_message_name2 IS NOT NULL THEN
2168 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
2169 p_lookup_code => 'CRSENCUMB',
2170 p_lookup_type => 'ENROLMENT_STEP_TYPE');
2171 END IF;
2172 IF l_message_name2 IS NOT NULL THEN
2173 igs_en_drop_units_api.create_ss_warning(
2174 p_person_id => p_person_id,
2175 p_course_cd => p_course_cd,
2176 p_term_cal_type => p_load_cal_type,
2180 p_message_icon=> 'D',
2177 p_term_ci_sequence_number => p_load_sequence_number,
2178 p_uoo_id => NULL,
2179 p_message_for => l_message_for,
2181 p_message_name => l_message_name2,
2182 p_message_rule_text => NULL,
2183 p_message_tokens => NULL,
2184 p_message_action => NULL,
2185 p_destination => NULL,
2186 p_parameters => NULL,
2187 p_step_type => 'PROGRAM');
2188 END IF;
2189 END IF;
2190
2191
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2195 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.validate_enr_encb :',SQLERRM);
2196 END IF;
2197 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2198 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.validate_enr_encb');
2199 IGS_GE_MSG_STACK.ADD;
2200 RAISE;
2201
2202 END validate_enr_encmb;
2203
2204 PROCEDURE reorder_uoo_ids(
2205 p_person_id IN NUMBER,
2206 p_course_cd IN VARCHAR2,
2207 p_uoo_ids IN VARCHAR2,
2208 p_load_cal_type IN VARCHAR2,
2209 p_load_sequence_number IN NUMBER,
2210 p_calling_obj IN VARCHAR2,
2211 p_unit_params OUT NOCOPY t_params_table
2212 ) AS
2213 -------------------------------------------------------------------------------------------
2214 --Created by : Basanth Kumar D, Oracle IDC
2215 --Date created: 29-JUL-2005
2216 --Purpose :
2217 -- This procedure reorders the uooids in the order of superior/subordinate/others
2218 -- it also adds the sub/sup units that needs to be added if not selected by the user
2219
2220 --Change History:
2221 --Who When What
2222 -- smaddali 27-apr-2006 Modified reorder_uooids for bug#4861221
2223 -------------------------------------------------------------------------------------------
2224
2225
2226 t_sup_params t_params_table;
2227 t_sub_params t_params_table;
2228 t_oth_params t_params_table;
2229
2230 v_sup_index BINARY_INTEGER := 1;
2231 v_sub_index BINARY_INTEGER := 1;
2232 v_oth_index BINARY_INTEGER := 1;
2233 v_all_index BINARY_INTEGER := 1;
2234
2235 -- cursor to get uooid relation type of the uooid passed
2236 CURSOR c_rel_type(p_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
2237 SELECT relation_type
2238 FROM igs_ps_unit_ofr_opt
2239 WHERE uoo_id = p_uoo_id;
2240
2241
2242 -- cursor to get planning sheet error units or plan units in case of plan
2243 CURSOR cur_fetch_ps_units IS
2244 SELECT uoo_id,no_assessment_ind,grading_schema_code,gs_version_number,
2245 override_enrolled_cp
2246 FROM igs_en_plan_units
2247 WHERE person_id = p_person_id
2248 AND course_cd = p_course_cd
2249 AND term_cal_type = p_load_cal_type
2250 AND term_ci_sequence_number = p_load_sequence_number
2251 AND( (p_calling_obj in('PLAN','SUBMITPLAN') and cart_error_flag = 'N')OR
2252 (p_calling_obj <> 'PLAN' and cart_error_flag = 'Y'));
2253
2254 -- cursor to fetch the superior unit
2255 CURSOR c_fetch_sup(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
2256 SELECT sup_uoo_id
2257 FROM igs_ps_unit_ofr_opt
2258 WHERE uoo_id = cp_uoo_id
2259 AND relation_type = 'SUBORDINATE';
2260
2261 -- cursor to fetch the subordinate units
2262 CURSOR c_sub_uoo_ids(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
2263 SELECT uoo_id,default_enroll_flag
2264 FROM igs_ps_unit_ofr_opt
2265 WHERE sup_uoo_id = cp_uoo_id
2266 AND relation_type = 'SUBORDINATE' ;
2267
2268 -- cursor which checks wheter a unit attempt exists
2269 CURSOR c_sua_exists(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
2270 SELECT 'X'
2271 FROM igs_en_su_attempt
2272 WHERE person_id = p_person_id
2273 AND course_cd = p_course_cd
2274 AND uoo_id = cp_uoo_id
2275 AND unit_attempt_status NOT IN ('DROPPED');
2276
2277
2278 l_sup_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE;
2279 l_sub_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE;
2280 l_sua_exists VARCHAR2(1);
2284 l_uoo_ids VARCHAR2(10000);
2281 l_unit_sec VARCHAR2(50);
2282 l_sup_exists BOOLEAN;
2283 l_sub_exists BOOLEAN;
2285 l_ps_units VARCHAR2(3000);
2286 l_temp VARCHAR2(200);
2287 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
2288 l_ass_ind igs_en_su_attempt.no_assessment_ind%TYPE;
2289 l_gradsch_cd igs_en_su_attempt.grading_schema_code%TYPE;
2290 l_gs_version_number NUMBER;
2291 l_override_cp NUMBER;
2292 lunit VARCHAR2(500);
2293 l_rel_type IGS_PS_UNIT_OFR_OPT.relation_type%TYPE;
2294 l_message_name VARCHAR2(4000);
2295 l_return_status VARCHAR2(100);
2296 l_enc_message_name VARCHAR2(100);
2297 l_app_short_name VARCHAR2(100);
2298 l_msg_index NUMBER;
2299 l_function VARCHAR2(100);
2300 l_create_sub_link BOOLEAN;
2301 l_valid_pos VARCHAR2(1);
2302
2303 BEGIN
2304
2305 l_uoo_ids := p_uoo_ids;
2306
2307 -- if the calling object is cart,SCHEDULE, ENROLPEND,or submit cart then append cart error uooids also
2308 --in case of plan,submit plan pick up the plan units for the term since the plannig sheet page
2309 --will not pass any parameters to the API except the selected units on the search pages.
2310 IF p_calling_obj IN ( 'CART' ,'SUBMITCART','SCHEDULE','ENROLPEND', 'PLAN','SUBMITPLAN') THEN
2311
2312
2313 FOR ps_units_rec IN cur_fetch_ps_units LOOP
2314
2315 -- if the unit selected by the user already exists in planning sheet error records then dont add it
2316 -- l_uoo_ids will be of the form '5982,N,P/F,3,23;6014,N,F/P,4,33;'
2317 -- so appending ; for the first uoo_id so that it will also be of the same form as other uooids (;uoo_id,)
2318 IF instr(';'||l_uoo_ids,';'||ps_units_rec.uoo_id||',') = 0 THEN
2319
2320
2321 l_uoo_ids := l_uoo_ids||ps_units_rec.uoo_id||','||ps_units_rec.no_assessment_ind||','||
2322 ps_units_rec.grading_schema_code||','|| ps_units_rec.gs_version_number|| ','||ps_units_rec.override_enrolled_cp||';';
2323
2324 --Store the uoo_ids in a variable say l_ps_units to indicate planning sheet error units.
2325
2326 IF l_ps_units IS NULL THEN
2327 l_ps_units := ','||ps_units_rec.uoo_id||',';
2328 ELSE
2329 l_ps_units := l_ps_units||ps_units_rec.uoo_id||',';
2330 END IF;
2331
2332 END IF;-- IF instr(','||l_uoo_ids,','||ps_err_units_rec.uoo_id||',',0) <> 0) THEN
2333
2334 END LOOP;
2335
2336 END IF; -- end of p_calling_obj in ( 'CART' ,'SUBMITCART')
2337
2338
2339
2340 -- Three temporary pl/sql tables are used to reorder the units.
2341 -- Loop through the uoo_ids and extract the values
2342
2343 WHILE l_uoo_ids IS NOT NULL LOOP
2344
2345 lunit := substr(l_uoo_ids,1,instr(l_uoo_ids, ';')-1);
2346
2347 -- Remove the uoo id details which is extacted above
2348 l_uoo_ids := substr(l_uoo_ids,instr(l_uoo_ids,';',1)+1);
2349
2350 IF(instr(l_uoo_ids,';',1) = 0) THEN
2351 l_uoo_ids := NULL;
2352 END IF;
2353
2354 IF lunit IS NOT NULL THEN
2355
2356 -- lunit is 1234,Y,P/F,233,343 or 1234,Y,P/F,233,343,Y
2357 l_uoo_id := substr( lunit,1,instr(lunit,',')-1);
2358 l_ass_ind := substr(lunit, instr(lunit,',')+1, 1);
2359
2360 -- l_temp will be P/F,233
2361 l_temp := substr( lunit, instr(lunit, ',',1,2)+1,( instr(lunit, ',',1,4) - (instr(lunit, ',',1,2)+1) ) );
2362
2363 l_gradsch_cd := substr( l_temp,1,instr(l_temp,',')-1);
2364 l_gs_version_number := to_number( substr( l_temp,instr(l_temp,',',-1)+1));
2365
2366 IF instr(lunit,',',1,5) = 0 THEN
2367 l_override_cp := to_number(substr( lunit,instr(lunit,',',1,4)+1));
2368 l_valid_pos := 'Y';
2369 ELSE
2370 l_override_cp := to_number(substr( lunit,instr(lunit,',',1,4)+1, ( instr(lunit, ',',1,5) - (instr(lunit, ',',1,4)+1) )));
2371 l_valid_pos := substr(lunit, instr(lunit,',',-1)+1, 1);
2372 END IF;
2373
2374 END IF;
2375
2376 --smaddali added this code for bug#4861221
2377 IF l_valid_pos ='N' THEN
2378 -- log warning record for this unit section and donot include for further processing.
2379 l_unit_sec := get_unit_sec(l_uoo_id);
2380
2381 igs_en_drop_units_api.create_ss_warning (
2382 p_person_id => p_person_id,
2383 p_course_cd => p_course_cd,
2384 p_term_cal_type => p_load_cal_type,
2385 p_term_ci_sequence_number => p_load_sequence_number,
2386 p_uoo_id => l_uoo_id,
2387 p_message_for => l_unit_sec,
2388 p_message_icon=> 'D',
2389 p_message_name => 'IGS_EN_SRCH_POS',
2390 p_message_rule_text => NULL,
2391 p_message_tokens => NULL,
2392 p_message_action => NULL,
2393 p_destination => NULL,
2394 p_parameters => NULL,
2395 p_step_type => 'UNIT');
2396
2397 IF p_calling_obj <> 'PLAN' THEN
2398 -- create a planning sheet error record to be shown in the cart
2399 l_message_name := NULL;
2400 l_return_status := NULL;
2404 p_load_sequence_number,
2401 create_ps_record(p_person_id,
2402 p_course_cd,
2403 p_load_cal_type,
2405 l_uoo_id, -- uooid for which plannig sheet record is created
2406 NULL, -- superior uoo id
2407 'Y', -- cart error flag
2408 l_ass_ind, -- assessment ind
2409 NULL,
2410 NULL,
2411 NULL);
2412 END IF;
2413 ELSE
2414
2415 -- Check if unit is superior , subordinate or none, add to 3 different temporary tables.
2416 OPEN c_rel_type(l_uoo_id);
2417 FETCH c_rel_type INTO l_rel_type;
2418 CLOSE c_rel_type;
2419
2420
2421 IF l_rel_type= 'SUPERIOR' THEN
2422
2423 t_sup_params(v_sup_index).uoo_id := l_uoo_id;
2424 t_sup_params(v_sup_index).ass_ind := l_ass_ind;
2425 t_sup_params(v_sup_index).grading_schema_cd := l_gradsch_cd;
2426 t_sup_params(v_sup_index).gs_version_number:= l_gs_version_number;
2427 t_sup_params(v_sup_index).override_enrolled_cp := l_override_cp;
2428 t_sup_params(v_sup_index).spl_perm_step := NULL;
2429 t_sup_params(v_sup_index).aud_perm_step := NULL;
2430 t_sup_params(v_sup_index).wlst_step := NULL;
2431
2432 v_sup_index :=v_sup_index+1;
2433
2434 ELSIF l_rel_type = 'SUBORDINATE' THEN
2435
2436 t_sub_params(v_sub_index).uoo_id := l_uoo_id;
2437 t_sub_params(v_sub_index).ass_ind := l_ass_ind;
2438 t_sub_params(v_sub_index).grading_schema_cd := l_gradsch_cd;
2439 t_sub_params(v_sub_index).gs_version_number:= l_gs_version_number;
2440 t_sub_params(v_sub_index).override_enrolled_cp := l_override_cp;
2441 t_sub_params(v_sub_index).spl_perm_step := NULL;
2442 t_sub_params(v_sub_index).aud_perm_step := NULL;
2443 t_sub_params(v_sub_index).wlst_step := NULL;
2444
2445 v_sub_index := v_sub_index+1;
2446
2447 ELSE
2448
2449 t_oth_params(v_oth_index).uoo_id := l_uoo_id;
2450 t_oth_params(v_oth_index).ass_ind := l_ass_ind;
2451 t_oth_params(v_oth_index).grading_schema_cd := l_gradsch_cd;
2452 t_oth_params(v_oth_index).gs_version_number:= l_gs_version_number;
2453 t_oth_params(v_oth_index).override_enrolled_cp := l_override_cp;
2454 t_oth_params(v_oth_index).spl_perm_step := NULL;
2455 t_oth_params(v_oth_index).aud_perm_step := NULL;
2456 t_oth_params(v_oth_index).wlst_step := NULL;
2457
2458 v_oth_index := v_oth_index+1;
2459 END IF;
2460
2461 END IF; --IF l_valid_pos ='N' THEN
2462 END LOOP;
2463
2464 -- Once the units are ordered in the three pl/sql tables , loop through each table to check
2465 -- if any extra units need to be added and if any warning records need to be logged.
2466
2467
2468 FOR i IN 1 .. t_sub_params.COUNT LOOP
2469 -- First loop through the subordinate uoo_ids table and fetch the superior unit code.
2470 -- Check if the superior unit has been selected / already attempted in sua/ already added to planning sheet
2471 -- if not then add it to the superior units temporary table and create a warning record
2472
2473 OPEN c_fetch_sup(t_sub_params(i).uoo_id);
2474 FETCH c_fetch_sup INTO l_sup_uoo_id;
2475
2476 --check if it has a superior
2477 IF c_fetch_sup%FOUND THEN
2478
2479 CLOSE c_fetch_sup;
2480 --check if it is selected in the list of selected uoo_ids
2481 l_sup_exists := FALSE;
2482 l_sua_exists := NULL;
2483
2484 -- check if it is in unit attempt table
2485 OPEN c_sua_exists(l_sup_uoo_id); -- it should be l_sup_uoo_id
2486 FETCH c_sua_exists INTO l_sua_exists;
2487 CLOSE c_sua_exists;
2488
2489 -- if it is in unit attempt or plannig sheet table then set sup_exists to true
2490 -- else check if it is in selected units
2491 IF l_sua_exists IS NOT NULL THEN
2492 l_sup_exists := TRUE;
2493 ELSE
2494
2495 FOR j IN 1 .. t_sup_params.COUNT LOOP
2496 --Check whether it is selected units
2497
2498 IF l_sup_uoo_id = t_sup_params(j).uoo_id THEN
2499 l_sup_exists := TRUE;
2500 EXIT;
2501 END IF;
2502
2503 END LOOP;
2504 END IF;
2505
2506 IF NOT l_sup_exists THEN
2507 --Create warning record
2508
2509 l_unit_sec := get_unit_sec(l_sup_uoo_id);
2510
2511 igs_en_drop_units_api.create_ss_warning (
2512 p_person_id => p_person_id,
2513 p_course_cd => p_course_cd,
2514 p_term_cal_type => p_load_cal_type,
2515 p_term_ci_sequence_number => p_load_sequence_number,
2516 p_uoo_id => l_sup_uoo_id,
2517 p_message_for => l_unit_sec,
2518 p_message_icon=> 'I',
2519 p_message_name => 'IGS_EN_AUTO_ADD_SUP',
2520 p_message_rule_text => NULL,
2521 p_message_tokens => 'UNIT_CD:'|| get_unit_sec(t_sub_params(i).uoo_id )||';',
2522 p_message_action => NULL,
2523 p_destination => NULL,
2527 --Add uoo_id to the superior uooIds table.
2524 p_parameters => NULL,
2525 p_step_type => 'UNIT');
2526
2528 t_sup_params(v_sup_index).uoo_id := l_sup_uoo_id;
2529 t_sup_params(v_sup_index).ass_ind := 'N';
2530 v_sup_index := v_sup_index+1;
2531
2532 --If called from plan ,add unit to PS table,
2533 -- create_ps_record;
2534 IF p_calling_obj = 'PLAN' THEN
2535
2536 create_ps_record(p_person_id,
2537 p_course_cd,
2538 p_load_cal_type,
2539 p_load_sequence_number,
2540 l_sup_uoo_id, -- uooid for which plannig sheet record is created
2541 NULL, -- superior uoo id
2542 'N', -- cart error flag
2543 'N', -- assessment ind
2544 NULL,
2545 NULL,
2546 NULL);
2547 END IF;
2548
2549
2550 END IF; -- IF NOT l_sup_exists
2551
2552 ELSE
2553 CLOSE c_fetch_sup;
2554 END IF; -- IF fetch_sup%FOUND
2555
2556 END LOOP;
2557
2558 -- Loop through each uoo_id to get all subordinates. If auto enroll, add it \
2559 -- and create a info record else create an action record in the warnings table
2560
2561 FOR i IN 1 .. t_sup_params.COUNT LOOP
2562
2563 --- Do not auto enroll if unit is from the planning sheet table. Check the variable l_ps_units.
2564
2565 IF l_ps_units IS NULL OR instr(l_ps_units,','||t_sup_params(i).uoo_id||',') = 0 THEN
2566
2567 l_create_sub_link := FALSE;
2568 FOR sub_uoo_ids_rec IN c_sub_uoo_ids(t_sup_params(i).uoo_id) LOOP
2569
2570 -- check if the uoo_id is in the selected uoo_ids else add to the
2571 -- subordinate table if not already present in the student unit attempt table.
2572 l_sub_exists := FALSE;
2573 l_sua_exists := NULL;
2574
2575 OPEN c_sua_exists(sub_uoo_ids_rec.uoo_id);
2576 FETCH c_sua_exists INTO l_sua_exists;
2577 CLOSE c_sua_exists;
2578 IF l_sua_exists IS NOT NULL THEN
2579 l_sub_exists := TRUE;
2580 ELSE
2581 FOR j IN 1 .. t_sub_params.COUNT LOOP
2582
2583 IF sub_uoo_ids_rec.uoo_id = t_sub_params(j).uoo_id THEN
2584 l_sub_exists := TRUE;
2585 EXIT;
2586 END IF;
2587
2588 END LOOP;
2589 END IF;
2590
2591 IF NOT l_sub_exists THEN
2592 -- check if auto enroll is 'Y'
2593 IF sub_uoo_ids_rec.default_enroll_flag = 'Y' THEN
2594
2595 --Add to subordinates table.
2596 t_sub_params(v_sub_index).uoo_id := sub_uoo_ids_rec.uoo_id;
2597 t_sub_params(v_sub_index).ass_ind := 'N';
2598 v_sub_index := v_sub_index+1;
2599
2600 l_unit_sec := get_unit_sec(sub_uoo_ids_rec.uoo_id);
2601
2602 -- create warning record.
2603 igs_en_drop_units_api.create_ss_warning (
2604 p_person_id => p_person_id,
2605 p_course_cd => p_course_cd,
2606 p_term_cal_type => p_load_cal_type,
2607 p_term_ci_sequence_number => p_load_sequence_number,
2608 p_uoo_id => sub_uoo_ids_rec.uoo_id,
2609 p_message_for => l_unit_sec,
2610 p_message_icon=> 'I',
2611 p_message_name =>'IGS_EN_AUTO_ADD_SUB',
2612 p_message_rule_text => NULL,
2613 p_message_tokens => 'UNIT_CD:'|| get_unit_sec(t_sup_params(i).uoo_id )||';',
2614 p_message_action => NULL,
2615 p_destination => NULL,
2616 p_parameters => NULL,
2617 p_step_type => 'UNIT');
2618
2619 -- If called from plan, add to the planning sheet table.
2620 IF p_calling_obj = 'PLAN' THEN
2621
2622 l_message_name := NULL;
2623 l_return_status := NULL;
2624 create_ps_record(p_person_id,
2625 p_course_cd,
2626 p_load_cal_type,
2627 p_load_sequence_number,
2628 sub_uoo_ids_rec.uoo_id, -- uooid for which plannig sheet record is created
2629 t_sup_params(i).uoo_id, -- superior uoo id
2630 'N', -- cart error flag
2631 'N', -- assessment ind
2632 NULL,
2633 NULL,
2634 NULL);
2635
2636 --************* Need to check what should be done after getting the return status
2637 END IF;
2638
2639 ELSE
2640 l_create_sub_link := TRUE;
2641
2642
2643 END IF; -- IF sub_uoo_ids_rec.default_enroll_flag = 'Y'
2644
2645 END IF; -- IF NOT l_sub_exists
2646
2647 END LOOP;-- FOR uoo_ids_rec IN
2648
2649 -- if the subordinates have to be manually added then provide a warning record
2650 -- with link to the add subordinates page
2651 IF l_create_sub_link THEN
2655 l_function := 'IGS_EN_CART_COREQ_SUB';
2652 IF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
2653 l_function := 'IGS_EN_PLAN_COREQ_SUB';
2654 ELSIF p_calling_obj IN ('CART', 'SUBMITCART','SCHEDULE','ENROLPEND') THEN
2656 ELSIF p_calling_obj IN ('SWAP','SUBMITSWAP') THEN
2657 l_function := 'IGS_EN_SCH_COREQ_SUB';
2658 END IF;
2659
2660 l_unit_sec := get_unit_sec(t_sup_params(i).uoo_id);
2661 igs_en_drop_units_api.create_ss_warning (
2662 p_person_id => p_person_id,
2663 p_course_cd => p_course_cd,
2664 p_term_cal_type => p_load_cal_type,
2665 p_term_ci_sequence_number => p_load_sequence_number,
2666 p_uoo_id => t_sup_params(i).uoo_id,
2667 p_message_for => l_unit_sec,
2668 p_message_icon=> 'I',
2669 p_message_name =>'IGS_EN_MAN_ADD_SUB',
2670 p_message_rule_text => NULL,
2671 p_message_tokens => NULL,
2672 p_message_action => igs_ss_enroll_pkg.enrf_get_lookup_meaning(p_lookup_code =>'ADD_SUB',p_lookup_type =>'IGS_EN_WARN_LINKS'),
2673 p_destination => l_function,
2674 p_parameters => NULL,
2675 p_step_type => 'UNIT');
2676 END IF;
2677 END IF; -- IF instr(l_ps_units,','||t_sup_params(i).uoo_id||',') = 0
2678
2679 END LOOP;--FOR i IN 1 .. t_sup_params.COUNT
2680
2681 IF t_sup_params.COUNT > 0 THEN
2682
2683 FOR i IN 1 .. t_sup_params.COUNT LOOP
2684
2685 p_unit_params(v_all_index) := t_sup_params(i);
2686 v_all_index := v_all_index + 1;
2687 END LOOP;
2688 END IF;
2689
2690 IF t_sub_params.COUNT > 0 THEN
2691 FOR i IN 1 .. t_sub_params.COUNT LOOP
2692 p_unit_params(v_all_index) := t_sub_params(i);
2693 v_all_index := v_all_index + 1;
2694 END LOOP;
2695 END IF;
2696
2697 IF t_oth_params.COUNT > 0 THEN
2698 FOR i IN 1 .. t_oth_params.COUNT LOOP
2699 p_unit_params(v_all_index) := t_oth_params(i);
2700 v_all_index := v_all_index + 1;
2701 END LOOP;
2702 END IF;
2703 EXCEPTION
2704 WHEN OTHERS THEN
2705 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2706 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.reorder_uoo_ids :',SQLERRM);
2707 END IF;
2708 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2709 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.reorder_uoo_ids');
2710 IGS_GE_MSG_STACK.ADD;
2711 RAISE;
2712
2713 END reorder_uoo_ids;
2714
2715
2716 FUNCTION eval_cart_max(
2717 p_person_id IN NUMBER,
2718 p_enrollment_category IN VARCHAR2,
2719 p_enr_method_type IN VARCHAR2,
2720 p_comm_type IN VARCHAR2,
2721 p_course_cd IN VARCHAR2,
2722 p_course_version IN NUMBER,
2723 p_load_cal_type IN VARCHAR2,
2724 p_load_sequence_number IN NUMBER,
2725 p_unit_params IN t_params_table,
2726 p_message OUT NOCOPY VARCHAR2,
2727 p_calling_obj IN VARCHAR2
2728 ) RETURN BOOLEAN
2729 AS PRAGMA AUTONOMOUS_TRANSACTION;
2730
2731 -------------------------------------------------------------------------------------------
2732 --Created by : Basanth Kumar D, Oracle IDC
2733 --Date created: 29-JUL-2005
2734 --Purpose : evaluates cart max rule
2735 --
2736 --Change History:
2737 --Who When What
2738
2739 -------------------------------------------------------------------------------------------
2740
2741
2742 -- cursor to get the system person type
2743 CURSOR cur_sys_pers_type IS
2744 SELECT system_type
2745 FROM igs_pe_person_types
2746 WHERE person_type_code = g_person_type;
2747
2748 -- cursor to get the unit code and unit class
2749 CURSOR c_sua_exists(cp_uoo_id IGS_PS_UNIT_OFR_OPT.sup_uoo_id%TYPE) IS
2750 SELECT unit_cd||'/'||unit_class
2751 FROM igs_en_su_attempt
2752 WHERE person_id = p_person_id
2753 AND course_cd = p_course_cd
2754 AND uoo_id = cp_uoo_id
2755 AND unit_attempt_status <> 'DROPPED';
2756
2757 -- cursor to get the unit details
2758 CURSOR c_unit_dtls (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
2759 SELECT ofr.unit_cd, ofr.version_number, ofr.cal_type, ofr.ci_sequence_number,
2760 ofr.location_cd, ofr.unit_class, ca.start_dt, ca.end_dt
2761 FROM igs_ps_unit_ofr_opt ofr, igs_ca_inst_all ca
2762 WHERE ofr.uoo_id = cp_uoo_id
2763 AND ofr.cal_type = ca.cal_type
2764 AND ofr.ci_sequence_number = ca.sequence_number;
2765
2766 CURSOR c_sua(cp_person_id igs_en_su_attempt_all.person_id%TYPE,
2767 cp_course_cd igs_en_su_attempt_all.course_cd%TYPE,
2768 cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
2769 SELECT sua.rowid
2770 FROM IGS_EN_SU_ATTEMPT_ALL SUA
2771 WHERE sua.person_id=cp_person_id
2772 AND sua.course_cd=cp_course_cd
2773 AND sua.uoo_id=cp_uoo_id
2774 AND sua.unit_attempt_status='DROPPED';
2775
2776 TYPE c_ref_cursor IS REF CURSOR;
2777 cur_step_def_var c_ref_cursor;
2778
2779 l_unit_dtsl_rec c_unit_dtls%ROWTYPE;
2780 l_enc_message_name VARCHAR2(2000);
2781 l_app_short_name VARCHAR2(100);
2785 l_message_name VARCHAR2(100);
2782 l_msg_index NUMBER;
2783 l_sys_per_type igs_pe_person_types.system_type%TYPE;
2784 l_en_cpd_ext_rec igs_en_cpd_ext%ROWTYPE;
2786 l_deny_warn igs_en_cpd_ext.notification_flag%TYPE;
2787 p_rule_failed BOOLEAN;
2788 l_step_def_query VARCHAR2(1000);
2789 l_unit_sec VARCHAR2(50);
2790 l_message_for VARCHAR2(100);
2791 l_rowid VARCHAR2(25);
2792
2793 BEGIN
2794
2795 OPEN cur_sys_pers_type;
2796 FETCH cur_sys_pers_type INTO l_sys_per_type;
2797 CLOSE cur_sys_pers_type;
2798
2799
2800 IF l_sys_per_type = 'STUDENT' THEN
2801
2802 l_step_def_query := 'SELECT eru.*
2803 FROM igs_en_cpd_ext eru, igs_lookups_view lkv
2804 WHERE eru.s_enrolment_step_type =lkv.lookup_code
2805 AND lkv.lookup_type = ''ENROLMENT_STEP_TYPE_EXT''
2806 AND lkv.step_group_type = ''UNIT''
2807 AND eru.enrolment_cat = :1
2808 AND eru.enr_method_type = :2
2809 AND ( eru.s_student_comm_type = :3 OR
2810 eru.s_student_comm_type = ''ALL'' )
2811 AND eru.s_enrolment_step_type = ''CART_MAX''';
2812
2813 OPEN cur_step_def_var FOR l_step_def_query USING p_enrollment_category, p_enr_method_type, p_comm_type;
2814
2815 ELSE
2816
2817 l_step_def_query := 'SELECT eru.*
2818 FROM igs_en_cpd_ext eru, igs_pe_usr_aval_all uact, igs_lookups_view lkv
2819 WHERE eru.s_enrolment_step_type =lkv.lookup_code
2820 AND lkv.lookup_type = ''ENROLMENT_STEP_TYPE_EXT''
2821 AND lkv.step_group_type = ''UNIT''
2822 AND eru.s_enrolment_step_type = uact.validation(+)
2823 AND uact.person_type(+) = :1
2824 AND NVL(uact.override_ind,''N'') = ''N''
2825 AND eru.enrolment_cat = :2
2826 AND eru.enr_method_type = :3
2827 AND ( eru.s_student_comm_type = :4 OR
2828 eru.s_student_comm_type = ''ALL'')
2829 AND eru.s_enrolment_step_type = ''CART_MAX''';
2830
2831 OPEN cur_step_def_var FOR l_step_def_query USING g_person_type, p_enrollment_category, p_enr_method_type, p_comm_type;
2832
2833 END IF;
2834
2835 FETCH cur_step_def_var INTO l_en_cpd_ext_rec;
2836 CLOSE cur_step_def_var;
2837
2838 IF l_en_cpd_ext_rec.rul_sequence_number IS NULL THEN
2839 ROLLBACK;
2840 RETURN TRUE;
2841
2842 END IF;
2843
2844 p_rule_failed := FALSE;
2845 l_message_name := NULL;
2846 l_deny_warn := igs_ss_enr_details.get_notification(
2847 p_person_type => g_person_type,
2848 p_enrollment_category => l_en_cpd_ext_rec.enrolment_cat,
2849 p_comm_type => l_en_cpd_ext_rec.s_student_comm_type,
2850 p_enr_method_type => l_en_cpd_ext_rec.enr_method_type,
2851 p_step_group_type => 'UNIT',
2852 p_step_type => l_en_cpd_ext_rec.s_enrolment_step_type,
2853 p_person_id => p_person_id,
2854 p_message => l_message_name);
2855 IF l_message_name IS NOT NULL THEN
2856 p_message := l_message_name;
2857 ROLLBACK;
2858 RETURN FALSE;
2859 END IF;
2860
2861 IF p_unit_params.COUNT > 0 THEN
2862
2863 FOR i IN 1.. p_unit_params.COUNT LOOP
2864
2865 BEGIN
2866
2867 --Check that unit attempt does not already exist. If it does then set p_message and return
2868 OPEN c_sua_exists(p_unit_params(i).uoo_id); -- it should be l_sup_uoo_id
2869 FETCH c_sua_exists INTO l_unit_sec;
2870
2871 -- if unit already exists for the person then throw an error
2872 IF c_sua_exists%FOUND THEN
2873
2874 CLOSE c_sua_exists;
2875 -- Incase of submit plan a unit may be in planning sheet
2876 -- and at the same time be in preenroll units. So skipping this check and pass onto next record
2877 IF p_calling_obj <> 'SUBMITPLAN' THEN
2878 p_message := 'IGS_EN_SUA_EXISTS'||'*'||l_unit_sec;
2879 ROLLBACK;
2880 RETURN FALSE;
2881 END IF;
2882
2883 ELSE
2884 CLOSE c_sua_exists;
2885
2886 -- First check if unit can be enrolled i.e it should not have a special/audit permission step error and seats must be available to enroll.
2887
2888 IF p_unit_params(i).spl_perm_step = 'PERM_NREQ' AND
2889 p_unit_params(i).aud_perm_step = 'PERM_NREQ' AND
2890 p_unit_params(i).wlst_step = 'N' THEN
2891
2892 -- create unit attempts by using a dynamic SQL statement if the above procedure call returns 'N'.
2893 OPEN c_unit_dtls(p_unit_params(i).uoo_id);
2894 FETCH c_unit_dtls INTO l_unit_dtsl_rec;
2895 CLOSE c_unit_dtls;
2896
2897 OPEN c_sua(p_person_id,p_course_cd,p_unit_params(i).uoo_id);
2898 FETCH c_sua INTO l_rowid;
2902 CLOSE c_sua;
2899
2900 --unit section was not attempted and dropped, so create new unit attempt
2901 IF c_sua%NOTFOUND THEN
2903
2904 INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
2905 PERSON_ID,
2906 COURSE_CD,
2907 UNIT_CD,
2908 VERSION_NUMBER,
2909 CAL_TYPE,
2910 CI_SEQUENCE_NUMBER,
2911 LOCATION_CD,
2912 UNIT_CLASS,
2913 CI_START_DT,
2914 CI_END_DT,
2915 UOO_ID,
2916 ENROLLED_DT,
2917 UNIT_ATTEMPT_STATUS,
2918 ADMINISTRATIVE_UNIT_STATUS,
2919 DISCONTINUED_DT,
2920 RULE_WAIVED_DT,
2921 RULE_WAIVED_PERSON_ID,
2922 NO_ASSESSMENT_IND,
2923 SUP_UNIT_CD,
2924 SUP_VERSION_NUMBER,
2925 EXAM_LOCATION_CD,
2926 ALTERNATIVE_TITLE,
2927 OVERRIDE_ENROLLED_CP,
2928 OVERRIDE_EFTSU,
2929 OVERRIDE_ACHIEVABLE_CP,
2930 OVERRIDE_OUTCOME_DUE_DT,
2931 OVERRIDE_CREDIT_REASON,
2932 ADMINISTRATIVE_PRIORITY,
2933 WAITLIST_DT,
2934 DCNT_REASON_CD,
2935 CREATION_DATE,
2936 CREATED_BY,
2937 LAST_UPDATE_DATE,
2938 LAST_UPDATED_BY,
2939 LAST_UPDATE_LOGIN,
2940 REQUEST_ID,
2941 PROGRAM_ID,
2942 PROGRAM_APPLICATION_ID,
2943 PROGRAM_UPDATE_DATE,
2944 org_id,
2945 GS_VERSION_NUMBER,
2946 ENR_METHOD_TYPE ,
2947 FAILED_UNIT_RULE ,
2948 CART ,
2949 RSV_SEAT_EXT_ID ,
2950 ORG_UNIT_CD ,
2951 GRADING_SCHEMA_CODE ,
2952 subtitle,
2953 session_id,
2954 deg_aud_detail_id,
2955 student_career_transcript,
2956 student_career_statistics,
2957 waitlist_manual_ind ,
2958 ATTRIBUTE_CATEGORY,
2959 ATTRIBUTE1,
2960 ATTRIBUTE2,
2961 ATTRIBUTE3,
2962 ATTRIBUTE4,
2963 ATTRIBUTE5,
2964 ATTRIBUTE6,
2965 ATTRIBUTE7,
2966 ATTRIBUTE8,
2967 ATTRIBUTE9,
2968 ATTRIBUTE10,
2969 ATTRIBUTE11,
2970 ATTRIBUTE12,
2971 ATTRIBUTE13,
2972 ATTRIBUTE14,
2973 ATTRIBUTE15,
2974 ATTRIBUTE16,
2975 ATTRIBUTE17,
2976 ATTRIBUTE18,
2977 ATTRIBUTE19,
2978 ATTRIBUTE20,
2979 WLST_PRIORITY_WEIGHT_NUM,
2980 WLST_PREFERENCE_WEIGHT_NUM,
2981 CORE_INDICATOR_CODE,
2982 UPD_AUDIT_FLAG,
2983 SS_SOURCE_IND
2984 )
2985 VALUES (p_person_id,
2989 l_unit_dtsl_rec.cal_type, -- cal_type
2986 p_course_cd,
2987 l_unit_dtsl_rec.unit_cd, -- unit_cd
2988 l_unit_dtsl_rec.version_number, -- unit ver no
2990 l_unit_dtsl_rec.ci_sequence_number, -- cal seq num
2991 l_unit_dtsl_rec.location_cd,
2992 l_unit_dtsl_rec.unit_class,
2993 l_unit_dtsl_rec.start_dt,
2994 l_unit_dtsl_rec.end_dt,
2995 p_unit_params(i).uoo_id,
2996 NULL, -- enrolled date
2997 'UNCONFIRM',
2998 NULL, -- ADMINISTRATIVE_UNIT_STATUS
2999 NULL, -- DISCONTINUED_DT
3000 NULL, -- RULE_WAIVED_DT,
3001 NULL, -- RULE_WAIVED_PERSON_ID,
3002 p_unit_params(i).ass_ind, -- NO_ASSESSMENT_IND,
3003 NULL, -- SUP_UNIT_CD,
3004 NULL, -- SUP_VERSION_NUMBER,
3005 NULL, -- EXAM_LOCATION_CD
3006 NULL, -- ALTERNATIVE_TITLE,
3007 p_unit_params(i).override_enrolled_cp, -- OVERRIDE_ENROLLED_CP,
3008 NULL, --- OVERRIDE_EFTSU,
3009 NULL, -- OVERRIDE_ACHIEVABLE_CP,
3010 NULL, -- OVERRIDE_OUTCOME_DUE_DT,
3011 NULL, -- OVERRIDE_CREDIT_REASON,
3012 NULL, -- ADMINISTRATIVE_PRIORITY,
3013 NULL, -- WAITLIST_DT,
3014 NULL, -- DCNT_REASON_CD,
3015 SYSDATE, -- CREATION_DATE,
3016 NVL(FND_GLOBAL.USER_ID,-1),-- CREATED_BY,
3017 SYSDATE, -- LAST_UPDATE_DATE,
3018 NVL(FND_GLOBAL.USER_ID,-1), -- LAST_UPDATED_BY,
3019 NVL(FND_GLOBAL.LOGIN_ID,-1), -- LAST_UPDATE_LOGIN,
3020 NULL, -- REQUEST_ID,
3021 NULL, --PROGRAM_ID,
3022 NULL, --PROGRAM_APPLICATION_ID,
3023 NULL, --PROGRAM_UPDATE_DATE,
3024 NULL, -- org_id,
3025 p_unit_params(i).gs_version_number, -- GS_VERSION_NUMBER,
3026 NULL, -- ENR_METHOD_TYPE ,
3027 NULL, --FAILED_UNIT_RULE ,
3028 NULL, --CART ,
3029 NULL, -- RSV_SEAT_EXT_ID ,
3030 NULL, -- ORG_UNIT_CD ,
3031 p_unit_params(i).grading_schema_cd, -- GRADING_SCHEMA_CODE ,
3032 NULL, -- subtitle,
3033 igs_en_add_units_api.g_ss_session_id, -- session_id,
3034 NULL, -- deg_aud_detail_id,
3035 NULL, -- student_career_transcript,
3036 NULL, -- student_career_statistics,
3037 NULL, -- waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
3038 NULL, -- ATTRIBUTE_CATEGORY,
3039 NULL, -- ATTRIBUTE1,
3040 NULL, -- ATTRIBUTE2,
3041 NULL, -- ATTRIBUTE3,
3042 NULL, -- ATTRIBUTE4,
3043 NULL, -- ATTRIBUTE5,
3044 NULL, -- ATTRIBUTE6,
3045 NULL, -- ATTRIBUTE7,
3046 NULL, -- ATTRIBUTE8,
3047 NULL, -- ATTRIBUTE9,
3048 NULL, -- ATTRIBUTE10,
3049 NULL, -- ATTRIBUTE11,
3050 NULL, -- ATTRIBUTE12,
3051 NULL, -- ATTRIBUTE13,
3052 NULL, -- ATTRIBUTE14,
3053 NULL, -- ATTRIBUTE15,
3054 NULL, -- ATTRIBUTE16,
3058 NULL, -- ATTRIBUTE20,
3055 NULL, -- ATTRIBUTE17,
3056 NULL, -- ATTRIBUTE18,
3057 NULL, -- ATTRIBUTE19,
3059 NULL, -- WLST_PRIORITY_WEIGHT_NUM,
3060 NULL, -- WLST_PREFERENCE_WEIGHT_NUM,
3061 NULL, -- CORE_INDICATOR_CODE
3062 'N', -- upd_audit_ind
3063 'N'); -- ss_source_ind
3064 ELSE
3065 -- unit attempt was attempted and dropped previously, so update that record
3066 CLOSE c_sua;
3067
3068 UPDATE IGS_EN_SU_ATTEMPT_ALL SET
3069 VERSION_NUMBER =l_unit_dtsl_rec.version_number,
3070 LOCATION_CD = l_unit_dtsl_rec.location_cd,
3071 UNIT_CLASS = l_unit_dtsl_rec.unit_class,
3072 CI_START_DT = l_unit_dtsl_rec.start_dt,
3073 CI_END_DT = l_unit_dtsl_rec.end_dt,
3074 ENROLLED_DT = NULL,
3075 UNIT_ATTEMPT_STATUS = 'UNCONFIRM',
3076 ADMINISTRATIVE_UNIT_STATUS = NULL,
3077 DISCONTINUED_DT = NULL,
3078 RULE_WAIVED_DT = NULL,
3079 RULE_WAIVED_PERSON_ID = NULL,
3080 NO_ASSESSMENT_IND = p_unit_params(i).ass_ind,
3081 SUP_UNIT_CD = NULL,
3082 SUP_VERSION_NUMBER = NULL,
3083 EXAM_LOCATION_CD = NULL,
3084 ALTERNATIVE_TITLE = NULL,
3085 OVERRIDE_ENROLLED_CP = p_unit_params(i).override_enrolled_cp,
3086 OVERRIDE_EFTSU = NULL,
3087 OVERRIDE_ACHIEVABLE_CP = NULL,
3088 OVERRIDE_OUTCOME_DUE_DT = NULL,
3089 OVERRIDE_CREDIT_REASON = NULL,
3090 ADMINISTRATIVE_PRIORITY = NULL,
3091 WAITLIST_DT = NULL,
3092 DCNT_REASON_CD = NULL,
3093 LAST_UPDATE_DATE = SYSDATE,
3094 LAST_UPDATED_BY = NVL(FND_GLOBAL.USER_ID,-1),
3095 LAST_UPDATE_LOGIN = NVL(FND_GLOBAL.LOGIN_ID,-1),
3096 REQUEST_ID = NULL,
3097 PROGRAM_ID = NULL,
3098 PROGRAM_APPLICATION_ID = NULL,
3099 PROGRAM_UPDATE_DATE = NULL,
3100 GS_VERSION_NUMBER = p_unit_params(i).gs_version_number,
3101 ENR_METHOD_TYPE = NULL,
3102 FAILED_UNIT_RULE = NULL,
3103 CART = NULL,
3104 RSV_SEAT_EXT_ID = NULL,
3105 ORG_UNIT_CD = NULL,
3106 GRADING_SCHEMA_CODE = p_unit_params(i).grading_schema_cd,
3107 SUBTITLE = NULL,
3108 SESSION_ID = igs_en_add_units_api.g_ss_session_id,
3109 DEG_AUD_DETAIL_ID = NULL,
3110 STUDENT_CAREER_TRANSCRIPT = NULL,
3111 STUDENT_CAREER_STATISTICS = NULL,
3112 WAITLIST_MANUAL_IND = NULL,
3113 ATTRIBUTE_CATEGORY = NULL,
3114 ATTRIBUTE1 = NULL,
3115 ATTRIBUTE2 = NULL,
3116 ATTRIBUTE3 = NULL,
3117 ATTRIBUTE4 = NULL,
3118 ATTRIBUTE5 = NULL,
3119 ATTRIBUTE6 = NULL,
3120 ATTRIBUTE7 = NULL,
3121 ATTRIBUTE8 = NULL,
3122 ATTRIBUTE9 = NULL,
3123 ATTRIBUTE10 = NULL,
3124 ATTRIBUTE11 = NULL,
3125 ATTRIBUTE12 = NULL,
3126 ATTRIBUTE13 = NULL,
3127 ATTRIBUTE14 = NULL,
3128 ATTRIBUTE15 = NULL,
3129 ATTRIBUTE16 = NULL,
3130 ATTRIBUTE17 = NULL,
3131 ATTRIBUTE18 = NULL,
3132 ATTRIBUTE19 = NULL,
3133 ATTRIBUTE20 = NULL,
3134 WLST_PRIORITY_WEIGHT_NUM = NULL,
3135 WLST_PREFERENCE_WEIGHT_NUM = NULL,
3136 CORE_INDICATOR_CODE = NULL,
3137 UPD_AUDIT_FLAG = 'N' ,
3138 SS_SOURCE_IND = 'N'
3139 WHERE ROWID = l_rowid;
3140
3141
3142 END IF;
3143
3144 IF NOT p_rule_failed THEN
3145 -- Call the cart max procedure.
3146 IF NOT igs_En_elgbl_unit.eval_cart_max(
3147 p_person_id => p_person_id,
3148 p_load_cal_type => p_load_cal_type,
3149 p_load_sequence_number => p_load_sequence_number,
3150 p_uoo_id => p_unit_params(i).uoo_id,
3151 p_course_cd => p_course_cd,
3152 p_course_version => p_course_version,
3153 p_message => p_message,
3154 p_deny_warn => l_deny_warn,
3155 p_rule_seq_number => l_en_cpd_ext_rec.rul_sequence_number
3156 ) THEN
3157 p_rule_failed := TRUE;
3158
3159 END IF; -- IF NOT igs_En_elgbl_unit.eval_cart_max(
3160 END IF; -- NOT p_rule_failed
3161 END IF; -- IF p_unit_params.perm_step = 'PERM_NREQ'
3162 END IF; -- IF c_sua_exists%FOUND THEN
3163 EXCEPTION
3164
3165 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
3166 -- get message from stack and insert into warn record.
3167 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
3168 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
3169
3170 IF l_message_name IS NOT NULL THEN
3171
3172 igs_en_drop_units_api.create_ss_warning (
3173 p_person_id => p_person_id,
3174 p_course_cd => p_course_cd,
3175 p_term_cal_type=> p_load_cal_type,
3176 p_term_ci_sequence_number => p_load_sequence_number,
3177 p_uoo_id => p_unit_params(i).uoo_id,
3178 p_message_for => l_unit_dtsl_rec.unit_cd||'/'||l_unit_dtsl_rec.unit_class,
3179 p_message_icon=> 'D',
3180 p_message_name => l_message_name,
3181 p_message_rule_text => NULL,
3182 p_message_tokens => NULL,
3183 p_message_action=> NULL,
3184 p_destination => NULL,
3185 p_parameters => NULL,
3186 p_step_type => 'UNIT');
3187 END IF;
3188
3189 WHEN OTHERS THEN
3190 ROLLBACK;
3191 RAISE;
3192 END;
3193
3194 END LOOP; -- FOR i in 1.. p_unit_params.count LOOP
3195 ROLLBACK;
3196
3197 END IF; -- IF p_unit_params.count > 0 THEN
3198
3199 IF p_rule_failed THEN
3200 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
3201 p_lookup_code => 'CART_MAX',
3202 p_lookup_type => 'ENROLMENT_STEP_TYPE_EXT');
3203 --Check if step is deny or warn
3204 IF l_deny_warn = 'DENY' THEN
3205
3206 IF p_calling_obj = 'SUBMITPLAN' THEN
3207 l_message_name := 'IGS_EN_PLANMAX_TAB_DENY';
3211 p_term_cal_type => p_load_cal_type,
3208 igs_en_drop_units_api.create_ss_warning(
3209 p_person_id => p_person_id,
3210 p_course_cd => p_course_cd,
3212 p_term_ci_sequence_number => p_load_sequence_number,
3213 p_uoo_id => NULL, ---- need tocheck the uooid tp be passed
3214 p_message_for => l_message_for, -- message_for
3215 p_message_icon=> 'D',
3216 p_message_name => l_message_name,
3217 p_message_rule_text => NULL,
3218 p_message_tokens => NULL,
3219 p_message_action => NULL,
3220 p_destination => NULL,
3221 p_parameters => NULL,
3222 p_step_type => 'UNIT');
3223 ROLLBACK;
3224 RETURN TRUE;
3225 ELSE
3226 p_message := 'IGS_EN_CARTMAX_TAB_DENY';
3227 ROLLBACK;
3228 RETURN FALSE;
3229 END IF;
3230
3231
3232 ELSIF l_deny_warn = 'WARN' THEN
3233
3234 IF p_calling_obj = 'SUBMITPLAN' THEN
3235 l_message_name := 'IGS_EN_PLANMAX_TAB_WARN';
3236 ELSE
3237 l_message_name := 'IGS_EN_CARTMAX_TAB_WARN';
3238 END IF;
3239
3240 igs_en_drop_units_api.create_ss_warning(
3241 p_person_id => p_person_id,
3242 p_course_cd => p_course_cd,
3243 p_term_cal_type => p_load_cal_type,
3244 p_term_ci_sequence_number => p_load_sequence_number,
3245 p_uoo_id => NULL, ---- need tocheck the uooid tp be passed
3246 p_message_for => l_message_for, -- message_for
3247 p_message_icon=> 'W',
3248 p_message_name => l_message_name,
3249 p_message_rule_text => NULL,
3250 p_message_tokens => NULL,
3251 p_message_action => NULL,
3252 p_destination => NULL,
3253 p_parameters => NULL,
3254 p_step_type => 'UNIT');
3255 ROLLBACK;
3256 RETURN TRUE;
3257 END IF;
3258 END IF;
3259
3260 ROLLBACK;
3261 RETURN TRUE;
3262 EXCEPTION
3263 -- In case of exception rollback and return false.
3264 WHEN OTHERS THEN
3265 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
3266 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.eval_cart_max :',SQLERRM);
3267 END IF;
3268 ROLLBACK;
3269 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3270 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.eval_cart_max');
3271 IGS_GE_MSG_STACK.ADD;
3272 RAISE;
3273 END eval_cart_max;
3274
3275 PROCEDURE create_planned_sua( p_person_id IN NUMBER,
3276 p_course_cd IN VARCHAR2,
3277 p_course_version IN NUMBER,
3278 p_load_cal_type IN VARCHAR2,
3279 p_load_sequence_number IN NUMBER,
3280 p_unit_params IN t_params_table,
3281 p_enrollment_category IN VARCHAR2,
3282 p_enr_method_type IN VARCHAR2,
3283 p_comm_type IN VARCHAR2,
3284 p_message OUT NOCOPY VARCHAR2,
3285 p_return_status OUT NOCOPY VARCHAR2,
3286 p_calling_obj IN VARCHAR2) AS
3287
3288 -------------------------------------------------------------------------------------------
3289 --Created by : Basanth Kumar D, Oracle IDC
3290 --Date created: 29-JUL-2005
3291 -- Purpose : makes unit abd program step validations for the passed units along with
3292 -- preenroll units (units that are added by admin through preenrollment job or through admin self-service)
3293 --Change History:
3294 --Who When What
3295 --bdeviset 14-NOV-2005 Added cursor c_sua and update igs_en_su_attempt all for bug# 4723960
3296 -- ckasu 27-NOV-2005 modified create_planned_sua by changing direct DML insert,update with
3297 -- INSERT_ROW,UPDATE_ROW of SUA TBH as a part of bug#4666102
3298 -------------------------------------------------------------------------------------------
3299
3300 -- cursor to fetch the unit details
3301 CURSOR c_get_unit_dtls (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
3302 SELECT ofr.unit_cd, ofr.version_number, ofr.cal_type, ofr.ci_sequence_number,
3303 ofr.location_cd, ofr.unit_class, ca.start_dt, ca.end_dt
3304 FROM igs_ps_unit_ofr_opt ofr, igs_ca_inst_all ca
3305 WHERE ofr.uoo_id = cp_uoo_id
3306 AND ofr.cal_type = ca.cal_type
3307 AND ofr.ci_sequence_number = ca.sequence_number;
3308
3309
3310 -- cursor to fetch preenroll units
3311 CURSOR c_get_preenr_units IS
3312 SELECT DISTINCT sua.uoo_id, sua.sup_unit_cd, no_assessment_ind,unit_Attempt_Status,ss_source_ind
3313 FROM igs_en_su_attempt sua,igs_ca_teach_to_load_v load
3314 WHERE sua.person_id = p_person_id
3315 AND sua.course_cd = p_course_cd
3316 AND sua.unit_attempt_status = 'UNCONFIRM'
3317 AND NVL(sua.ss_source_ind ,'N') <> 'S'
3318 AND sua.cal_type = load.teach_cal_type
3319 AND sua.ci_sequence_number = load.teach_ci_sequence_number
3320 AND load_cal_type = p_load_cal_type
3321 AND load_ci_sequence_number = p_load_sequence_number
3325 CURSOR c_get_sua_dtls (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
3322 ORDER BY sua.sup_unit_cd DESC;
3323
3324 -- Get the details of unit attempt
3326 SELECT sua.*
3327 FROM igs_en_su_attempt sua
3328 WHERE person_id = p_person_id
3329 AND course_cd = p_course_cd
3330 AND uoo_id = cp_uoo_id;
3331
3332
3333 NO_AUSL_RECORD_FOUND EXCEPTION;
3334 PRAGMA EXCEPTION_INIT(NO_AUSL_RECORD_FOUND , -20010);
3335
3336 l_rowid VARCHAR2(100);
3337 l_unit_rec c_get_unit_dtls%ROWTYPE;
3338 l_core_indicator_code igs_en_su_attempt.core_indicator_code%TYPE;
3339 l_combined_unit_params t_params_table;
3340 l_return_status VARCHAR2(20);
3341 v_comb_index BINARY_INTEGER := 1;
3342 l_uoo_id igs_en_su_attempt.uoo_id%TYPE;
3343 l_sup_unit_cd igs_en_su_attempt.sup_unit_cd%TYPE;
3344 l_message_name VARCHAR2(4000);
3345 l_deny_warn VARCHAR2(30);
3346 l_deny_warn_max_cp VARCHAR2(10);
3347 l_deny_warn_min_cp VARCHAR2(10);
3348 l_sua_rec igs_en_su_attempt%ROWTYPE;
3349 l_credit_points NUMBER;
3350 l_min_credit_point NUMBER;
3351 l_enc_message_name VARCHAR2(2000);
3352 l_app_short_name VARCHAR2(100);
3353 l_msg_index NUMBER;
3354 l_unit_sec VARCHAR2(100);
3355 l_max_cp_fail BOOLEAN;
3356 l_min_cp_fail BOOLEAN;
3357 l_create_wlst VARCHAR2(1);
3358 l_no_ass_ind igs_en_su_attempt.no_assessment_ind%TYPE;
3359 x_OVERRIDE_ACHIEVABLE_CP NUMBER;
3360 x_OVERRIDE_ENROLLED_CP NUMBER;
3361 lv_message_name VARCHAR2(100);
3362 lv_teach_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE;
3363 lv_teach_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE;
3364 lv_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
3365 lv_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE;
3366 crse_hold_fail BOOLEAN ;
3367 l_message_for VARCHAR2(100);
3368 l_message_tokens VARCHAR2(200);
3369
3370 CURSOR c_teach_cal (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
3371 SELECT cal_type, ci_sequence_number,unit_cd,unit_class
3372 FROM igs_ps_unit_ofr_opt
3373 WHERE uoo_id = cp_uoo_id;
3374
3375 CURSOR c_teach_census_dt(p_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
3376 p_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE)IS
3377 SELECT daiv.alias_val,
3378 ci.start_dt,
3379 ci.end_dt
3380 FROM IGS_CA_DA_INST_V daiv,
3381 IGS_CA_INST ci,
3382 IGS_GE_S_GEN_CAL_CON sgcc
3383 WHERE daiv.cal_type = p_cal_type AND
3384 daiv.ci_sequence_number = p_ci_sequence_number AND
3385 daiv.dt_alias = sgcc.census_dt_alias AND
3386 sgcc.s_control_num = 1 AND
3387 daiv.cal_type = ci.cal_type AND
3388 daiv.ci_sequence_number = ci.sequence_number;
3389
3390 BEGIN
3391 -- loop to insert unit attempts with unit attempt status as planned
3392 crse_hold_fail := FALSE;
3393 FOR i IN 1.. p_unit_params.COUNT LOOP
3394
3395 BEGIN
3396
3397 -- Check if unit requires special/audit permission, if required the procedure creates the appropriate warning record and returns true.
3398 -- If the procedure has returned false, it implies that no permission is required and the API can continue with the creation of unit attempts.
3399 -- The out paramter l_message_name has a value only in case of errors. Hence check for the same and exit out.
3400 l_message_name := NULL;
3401 IF permission_required( p_person_id,
3402 p_course_cd,
3403 p_load_cal_type,
3404 p_load_sequence_number,
3405 p_unit_params(i).uoo_id,
3406 p_unit_params(i).spl_perm_step ,
3407 p_unit_params(i).aud_perm_step ,
3408 p_unit_params(i).ass_ind,
3409 p_unit_params(i).override_enrolled_cp,
3410 p_unit_params(i).grading_schema_cd,
3411 p_unit_params(i).gs_version_number,
3412 l_message_name,
3413 p_calling_obj) THEN
3414
3415 IF l_message_name IS NOT NULL THEN
3416 --implies some unexpected error , set return status and message name and stop processing.
3417
3418 p_return_status := 'FALSE';
3419 p_message := l_message_name;
3420 RETURN;
3421
3422 END IF; -- IF l_message_name IS NOT NULL
3423
3424 ELSE
3425
3426 IF waitlist_required(
3427 p_person_id ,
3428 p_course_cd ,
3429 p_unit_params(i).uoo_id,
3430 p_unit_params(i).wlst_step,
3431 p_load_cal_type,
3432 p_load_sequence_number,
3433 p_unit_params(i).ass_ind,
3434 p_enr_method_type,
3435 p_unit_params(i).override_enrolled_cp,
3439 l_create_wlst,
3436 NULL,
3437 p_unit_params(i).grading_schema_cd,
3438 p_unit_params(i).gs_version_number,
3440 p_calling_obj ,
3441 l_message_name) THEN
3442
3443 IF l_message_name IS NOT NULL THEN
3444 --implies some unexpected error , set return status and message name and stop processing.
3445
3446 p_return_status := 'FALSE';
3447 p_message := l_message_name;
3448 RETURN;
3449
3450 END IF; -- IF l_message_name IS NOT NULL
3451
3452 ELSE
3453 l_core_indicator_code := igs_en_gen_009.enrp_check_usec_core(
3454 p_person_id => p_person_id,
3455 p_program_cd => p_course_cd,
3456 p_uoo_id => p_unit_params(i).uoo_id);
3457
3458 OPEN c_get_unit_dtls(p_unit_params(i).uoo_id);
3459 FETCH c_get_unit_dtls INTO l_unit_rec;
3460 CLOSE c_get_unit_dtls;
3461
3462
3463
3464
3465 lv_message_name := null;
3466 l_message_for := igs_ss_enroll_pkg.enrf_get_lookup_meaning (
3467 p_lookup_code => 'CRSENCUMB',
3468 p_lookup_type => 'ENROLMENT_STEP_TYPE');
3469 OPEN c_teach_Cal(p_unit_params(i).uoo_id);
3470 FETCH c_teach_cal INTO lv_teach_cal_type, lv_teach_ci_sequence_number, lv_unit_cd, lv_unit_class;
3471 CLOSE c_teach_Cal;
3472
3473 FOR v_census_dt_rec IN c_teach_census_dt(lv_teach_cal_type, lv_teach_ci_sequence_number) LOOP
3474 -- Only validate if census date is between ci.start_dt and ci.end_dt.
3475
3476 IF (v_census_dt_rec.alias_val >= v_census_dt_rec.start_dt) AND
3477 (v_census_dt_rec.alias_val <= v_census_dt_rec.end_dt) THEN
3478
3479 lv_message_name := null;
3480 IF IGS_EN_VAL_ENCMB.enrp_val_excld_unit (
3481 p_person_id,
3482 p_course_cd,
3483 lv_unit_cd,
3484 v_census_dt_rec.alias_val,
3485 lv_message_name) = FALSE THEN
3486
3487 igs_en_drop_units_api.create_ss_warning(
3488 p_person_id => p_person_id,
3489 p_course_cd => p_course_cd,
3490 p_term_cal_type => p_load_cal_type,
3491 p_term_ci_sequence_number => p_load_sequence_number,
3492 p_uoo_id => p_unit_params(i).uoo_id,
3493 p_message_for => lv_unit_cd||'/'||lv_unit_class,
3494 p_message_icon=> 'D',
3495 p_message_name => lv_message_name,
3496 p_message_rule_text => NULL,
3497 p_message_tokens => NULL,
3498 p_message_action => NULL,
3499 p_destination => NULL,
3500 p_parameters => NULL,
3501 p_step_type => 'UNIT');
3502
3503 end if;
3504 lv_message_name := null;
3505
3506 -- Validate against person, course and course group exclusions.
3507
3508 IF NOT crse_hold_fail then
3509 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_crs (
3510 p_person_id,
3511 p_course_cd,
3512 v_census_dt_rec.alias_val,
3513 lv_message_name) THEN
3514
3515 crse_hold_fail := TRUE;
3516 igs_en_drop_units_api.create_ss_warning(
3517 p_person_id => p_person_id,
3518 p_course_cd => p_course_cd,
3519 p_term_cal_type => p_load_cal_type,
3520 p_term_ci_sequence_number => p_load_sequence_number,
3521 p_uoo_id => null,
3522 p_message_for => l_message_for,
3523 p_message_icon=> 'D',
3524 p_message_name => lv_message_name,
3525 p_message_rule_text => NULL,
3526 p_message_tokens => NULL,
3527 p_message_action => NULL,
3528 p_destination => NULL,
3529 p_parameters => NULL,
3530 p_step_type => 'PROGRAM');
3531
3532 end if;
3533 END IF;
3534
3535 END IF;
3536 END LOOP;
3537
3538 l_message_tokens := NULL;
3539 IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW (
3540 X_ROWID => l_rowid ,
3544 X_CAL_TYPE => l_unit_rec.cal_type ,
3541 X_PERSON_ID => p_person_id ,
3542 X_COURSE_CD => p_course_cd ,
3543 X_UNIT_CD => l_unit_rec.unit_cd ,
3545 X_CI_SEQUENCE_NUMBER => l_unit_rec.ci_sequence_number ,
3546 X_VERSION_NUMBER => l_unit_rec.version_number ,
3547 X_LOCATION_CD => l_unit_rec.location_cd ,
3548 X_UNIT_CLASS => l_unit_rec.unit_class ,
3549 X_CI_START_DT => l_unit_rec.start_dt ,
3550 X_CI_END_DT => l_unit_rec.end_dt ,
3551 X_UOO_ID => p_unit_params(i).uoo_id ,
3552 X_ENROLLED_DT => NULL ,
3553 X_UNIT_ATTEMPT_STATUS => 'PLANNED' ,
3554 X_ADMINISTRATIVE_UNIT_STATUS => NULL ,
3555 X_DISCONTINUED_DT => NULL ,
3556 X_RULE_WAIVED_DT => NULL ,
3557 X_RULE_WAIVED_PERSON_ID => NULL ,
3558 X_NO_ASSESSMENT_IND => NVL(p_unit_params(i).ass_ind,'N') , -- value passed to indicate that audit is requeted or not
3559 X_SUP_UNIT_CD => NULL ,
3560 X_SUP_VERSION_NUMBER => NULL ,
3561 X_EXAM_LOCATION_CD => NULL ,
3562 X_ALTERNATIVE_TITLE => NULL ,
3563 X_OVERRIDE_ENROLLED_CP => p_unit_params(i).override_enrolled_cp ,
3564 X_OVERRIDE_EFTSU => NULL ,
3565 X_OVERRIDE_ACHIEVABLE_CP => NULL , -- selective values passed based on whether audit is requeted or not
3566 X_OVERRIDE_OUTCOME_DUE_DT => NULL ,
3567 X_OVERRIDE_CREDIT_REASON => NULL ,
3568 X_ADMINISTRATIVE_PRIORITY => NULL ,
3569 X_WAITLIST_DT => NULL ,
3570 X_DCNT_REASON_CD => NULL ,
3571 X_MODE => 'R' ,
3572 X_ORG_ID => NULL ,
3573 X_GS_VERSION_NUMBER => p_unit_params(i).gs_version_number ,
3574 X_ENR_METHOD_TYPE => p_enr_method_type ,
3575 X_FAILED_UNIT_RULE => NULL ,
3576 X_CART => NULL ,
3577 X_RSV_SEAT_EXT_ID => NULL ,
3578 X_ORG_UNIT_CD => NULL ,
3579 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3580 X_SESSION_ID => igs_en_add_units_api.g_ss_session_id ,
3581 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
3582 X_GRADING_SCHEMA_CODE => p_unit_params(i).grading_schema_cd ,
3583 --Added the column Deg_Aud_Detail_Id as part of Degree Audit Interface build. (Bug# 2033208) - pradhakr
3584 X_DEG_AUD_DETAIL_ID => NULL,
3585 X_STUDENT_CAREER_TRANSCRIPT => NULL,
3586 X_STUDENT_CAREER_STATISTICS => NULL,
3587 X_ATTRIBUTE_CATEGORY => NULL,
3588 X_ATTRIBUTE1 => NULL,
3589 X_ATTRIBUTE2 => NULL,
3590 X_ATTRIBUTE3 => NULL,
3591 X_ATTRIBUTE4 => NULL,
3592 X_ATTRIBUTE5 => NULL,
3593 X_ATTRIBUTE6 => NULL,
3594 X_ATTRIBUTE7 => NULL,
3598 X_ATTRIBUTE11 => NULL,
3595 X_ATTRIBUTE8 => NULL,
3596 X_ATTRIBUTE9 => NULL,
3597 X_ATTRIBUTE10 => NULL,
3599 X_ATTRIBUTE12 => NULL,
3600 X_ATTRIBUTE13 => NULL,
3601 X_ATTRIBUTE14 => NULL,
3602 X_ATTRIBUTE15 => NULL,
3603 X_ATTRIBUTE16 => NULL,
3604 X_ATTRIBUTE17 => NULL,
3605 X_ATTRIBUTE18 => NULL,
3606 X_ATTRIBUTE19 => NULL,
3607 X_ATTRIBUTE20 => NULL,
3608 X_WAITLIST_MANUAL_IND => NULL, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.,
3609 X_WLST_PRIORITY_WEIGHT_NUM => NULL,
3610 X_WLST_PREFERENCE_WEIGHT_NUM => NULL,
3611 X_CORE_INDICATOR_CODE => l_core_indicator_code,
3612 X_UPD_AUDIT_FLAG => 'N',
3613 X_SS_SOURCE_IND => 'P'
3614
3615 );
3616
3617
3618 END IF; -- IF waitlist_required
3619
3620 END IF; -- IF permission_required
3621
3622 EXCEPTION
3623
3624 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
3625
3626 -- get message from stack and insert into warn record.
3627 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
3628 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
3629
3630 IF l_message_name IS NOT NULL THEN
3631
3632 -- add to warnings table as error i.e warn_icon is 'D'
3633
3634 IF l_message_name IN ('IGS_GE_RECORD_ALREADY_EXISTS','IGS_GE_MULTI_ORG_DUP_REC') THEN
3635 l_message_name := 'IGS_EN_SUA_EXISTS';
3636 l_message_tokens := 'UNIT_CD'||':'||l_unit_rec.unit_cd||'/'||l_unit_rec.unit_class||';';
3637 END IF;
3638
3639
3640 igs_en_drop_units_api.create_ss_warning(
3641 p_person_id => p_person_id,
3642 p_course_cd => p_course_cd,
3643 p_term_cal_type => p_load_cal_type,
3644 p_term_ci_sequence_number => p_load_sequence_number,
3645 p_uoo_id => p_unit_params(i).uoo_id,
3646 p_message_for => l_unit_rec.unit_cd||'/'||l_unit_rec.unit_class,
3647 p_message_icon=> 'D',
3648 p_message_name => l_message_name,
3649 p_message_rule_text => NULL,
3650 p_message_tokens => l_message_tokens,
3651 p_message_action => NULL,
3652 p_destination => NULL,
3653 p_parameters => NULL,
3654 p_step_type => 'UNIT');
3655
3656 END IF;
3657
3658 WHEN OTHERS THEN
3659
3660 RAISE;
3661
3662 END; -- end of BEGIN block
3663
3664 END LOOP;
3665
3666
3667
3668 -- In addition to units being added, preenrolled units need to be evaluated.
3669 -- These include units that are added by admin through preenrollment job or through admin self-service
3670 -- so add these unit details into l_combined_unit_params
3671
3672 FOR preenr_units_rec in c_get_preenr_units loop
3673
3674 l_combined_unit_params(v_comb_index).uoo_id := preenr_units_rec.uoo_id;
3675 l_combined_unit_params(v_comb_index).ass_ind := preenr_units_rec.no_assessment_ind;
3676 v_comb_index := v_comb_index+1;
3677
3678 END LOOP;
3679
3680 --before doing step validations for all the units, perform permission validations
3681 --for the above preenrolled units.
3682 l_return_status := NULL;
3683
3684 get_perm_wlst_setup(p_person_id,
3685 p_course_cd,
3686 p_load_cal_type,
3687 p_load_sequence_number,
3688 l_combined_unit_params,
3689 p_message,
3690 l_return_status,
3691 'N'); --do not chek waitlist setup
3692 IF l_return_status = 'FALSE' THEN
3693 p_return_status := 'FALSE';
3694 RETURN;
3695 END IF;
3696 FOR i in 1 .. l_combined_unit_params.COUNT loop
3697
3698 l_message_name := NULL;
3699 IF permission_required( p_person_id,
3700 p_course_cd,
3701 p_load_cal_type,
3702 p_load_sequence_number,
3703 l_combined_unit_params(i).uoo_id,
3704 l_combined_unit_params(i).spl_perm_step ,
3705 l_combined_unit_params(i).aud_perm_step ,
3706 l_combined_unit_params(i).ass_ind,
3707 l_combined_unit_params(i).override_enrolled_cp,
3708 l_combined_unit_params(i).grading_schema_cd,
3709 l_combined_unit_params(i).gs_version_number,
3713 IF l_message_name IS NOT NULL THEN
3710 l_message_name,
3711 p_calling_obj) THEN
3712
3714 --implies some unexpected error , set return status and message name and stop processing.
3715
3716 p_return_status := 'FALSE';
3717 p_message := l_message_name;
3718 RETURN;
3719 END IF;
3720 END IF;
3721 END LOOP;
3722
3723
3724 -- fetch the passed unit details into l_combined_unit_params
3725
3726
3727 FOR i IN 1.. p_unit_params.COUNT LOOP
3728
3729 l_combined_unit_params(v_comb_index) := p_unit_params(i);
3730 v_comb_index := v_comb_index+1;
3731
3732 END LOOP;
3733
3734 l_max_cp_fail := FALSE;
3735 l_min_cp_fail := FALSE;
3736
3737 l_message_name := NULL;
3738 l_deny_warn_max_cp := NULL;
3739 l_deny_warn_max_cp := igs_ss_enr_details.get_notification(
3740 p_person_type => g_person_type,
3741 p_enrollment_category => p_enrollment_category,
3742 p_comm_type => p_comm_type,
3743 p_enr_method_type => p_enr_method_type,
3744 p_step_group_type => 'PROGRAM',
3745 p_step_type => 'FMAX_CRDT' ,
3746 p_person_id => p_person_id,
3747 p_message => l_message_name
3748 );
3749 IF l_message_name IS NOT NULL THEN
3750 p_return_status := 'FALSE';
3751 p_message := l_message_name;
3752 RETURN;
3753 END IF;
3754
3755 l_deny_warn_min_cp := NULL;
3756 l_deny_warn_min_cp := igs_ss_enr_details.get_notification(
3757 p_person_type => g_person_type,
3758 p_enrollment_category => p_enrollment_category,
3759 p_comm_type => p_comm_type,
3760 p_enr_method_type => p_enr_method_type,
3761 p_step_group_type => 'PROGRAM',
3762 p_step_type => 'FMIN_CRDT' ,
3763 p_person_id => p_person_id,
3764 p_message => l_message_name
3765 );
3766 IF l_message_name IS NOT NULL THEN
3767 p_return_status := 'FALSE';
3768 p_message := l_message_name;
3769 RETURN;
3770 END IF;
3771
3772 -- loop to make unit,program steps and encumbrance validations
3773 FOR i IN 1.. l_combined_unit_params.COUNT LOOP
3774
3775 BEGIN
3776 l_deny_warn := NULL;
3777 l_message_name := NULL;
3778 IF NOT IGS_EN_ELGBL_UNIT.eval_unit_steps(
3779 p_person_id => p_person_id,
3780 p_person_type => g_person_type,
3781 p_load_cal_type => p_load_cal_type,
3782 p_load_sequence_number => p_load_sequence_number,
3783 p_uoo_id => l_combined_unit_params(i).uoo_id,
3784 p_course_cd => p_course_cd,
3785 p_course_version => p_course_version,
3786 p_enrollment_category => p_enrollment_category,
3787 p_enr_method_type => p_enr_method_type,
3788 p_comm_type => p_comm_type,
3789 p_message => l_message_name, -- out
3790 p_deny_warn => l_deny_warn, -- out
3791 p_calling_obj => p_calling_obj) THEN
3792 IF l_message_name IS NOT NULL THEN
3793 p_message := l_message_name;
3794 p_return_status := 'FALSE';
3795 RETURN;
3796 END IF;
3797 END IF; -- IF NOT IGS_EN_ELGBL_UNIT.eval_unit_steps
3798
3799 IF NOT l_max_cp_fail THEN
3800
3801 IF l_deny_warn_max_cp IS NOT NULL THEN
3802 -- Call igs_en_elgbl_program.eval_max_cp before enrolling the program attempt
3803 IF NOT igs_en_elgbl_program.eval_max_cp(
3804 p_person_id => p_person_id,
3805 p_load_calendar_type => p_load_cal_type,
3806 p_load_cal_sequence_number => p_load_sequence_number,
3807 p_uoo_id => l_combined_unit_params(i).uoo_id,
3808 p_program_cd => p_course_cd,
3809 p_program_version => p_course_version,
3810 p_message => l_message_name,
3811 p_deny_warn => l_deny_warn_max_cp,
3812 p_upd_cp => NULL,
3813 p_calling_obj => p_calling_obj) THEN
3814 l_max_cp_fail := TRUE;
3815 IF l_message_name IS NOT NULL THEN
3816 p_message := l_message_name;
3817 p_return_status := 'FALSE';
3821 END IF; -- end of eval_max_cp
3818 RETURN;
3819 END IF;
3820
3822
3823 END IF; -- IF l_deny_warn IS NOT NULL THEN
3824
3825 END IF;-- IF NOT l_max_cp_fail THEN
3826
3827 OPEN c_get_sua_dtls(l_combined_unit_params(i).uoo_id);
3828 FETCH c_get_sua_dtls INTO l_sua_rec;
3829
3830 IF c_get_sua_dtls%FOUND THEN
3831 CLOSE c_get_sua_dtls;
3832
3833 -- enroll the unit attempts
3834 IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW(
3835 X_ROWID => l_sua_rec.row_id ,
3836 X_PERSON_ID => l_sua_rec.person_id ,
3837 X_COURSE_CD => l_sua_rec.course_cd ,
3838 X_UNIT_CD => l_sua_rec.unit_cd ,
3839 X_CAL_TYPE => l_sua_rec.cal_type ,
3840 X_CI_SEQUENCE_NUMBER => l_sua_rec.ci_sequence_number ,
3841 X_VERSION_NUMBER => l_sua_rec.version_number ,
3842 X_LOCATION_CD => l_sua_rec.location_cd ,
3843 X_UNIT_CLASS => l_sua_rec.unit_class ,
3844 X_CI_START_DT => l_sua_rec.ci_start_dt ,
3845 X_CI_END_DT => l_sua_rec.ci_end_dt ,
3846 X_UOO_ID => l_sua_rec.uoo_id ,
3847 X_ENROLLED_DT => SYSDATE ,
3848 X_UNIT_ATTEMPT_STATUS => 'ENROLLED' ,
3849 X_ADMINISTRATIVE_UNIT_STATUS => l_sua_rec.administrative_unit_status ,
3850 X_DISCONTINUED_DT => l_sua_rec.discontinued_dt ,
3851 X_RULE_WAIVED_DT => l_sua_rec.rule_waived_dt ,
3852 X_RULE_WAIVED_PERSON_ID => l_sua_rec.rule_waived_person_id ,
3853 X_NO_ASSESSMENT_IND => l_sua_rec.no_assessment_ind ,
3854 X_SUP_UNIT_CD => l_sua_rec.sup_unit_cd ,
3855 X_SUP_VERSION_NUMBER => l_sua_rec.sup_version_number ,
3856 X_EXAM_LOCATION_CD => l_sua_rec.exam_location_cd ,
3857 X_ALTERNATIVE_TITLE => l_sua_rec.alternative_title ,
3858 X_OVERRIDE_ENROLLED_CP => l_sua_rec.override_enrolled_cp ,
3859 X_OVERRIDE_EFTSU => l_sua_rec.override_eftsu ,
3860 X_OVERRIDE_ACHIEVABLE_CP => l_sua_rec.override_achievable_cp ,
3861 X_OVERRIDE_OUTCOME_DUE_DT => l_sua_rec.override_outcome_due_dt ,
3862 X_OVERRIDE_CREDIT_REASON => l_sua_rec.override_credit_reason ,
3863 X_ADMINISTRATIVE_PRIORITY => l_sua_rec.administrative_priority ,
3864 X_WAITLIST_DT => l_sua_rec.waitlist_dt ,
3865 X_DCNT_REASON_CD => l_sua_rec.dcnt_reason_cd ,
3866 X_MODE => 'R' ,
3867 X_GS_VERSION_NUMBER => l_sua_rec.gs_version_number ,
3868 X_ENR_METHOD_TYPE => l_sua_rec.enr_method_type ,
3869 X_FAILED_UNIT_RULE => l_sua_rec.failed_unit_rule ,
3870 X_CART => 'N' ,
3871 X_RSV_SEAT_EXT_ID => l_sua_rec.rsv_seat_ext_id ,
3872 X_ORG_UNIT_CD => l_sua_rec.org_unit_cd ,
3873 X_SESSION_ID => l_sua_rec.session_id ,
3874 X_GRADING_SCHEMA_CODE => l_sua_rec.grading_schema_code ,
3875 X_DEG_AUD_DETAIL_ID => l_sua_rec.deg_aud_detail_id ,
3876 X_STUDENT_CAREER_TRANSCRIPT => l_sua_rec.student_career_transcript,
3877 X_STUDENT_CAREER_STATISTICS => l_sua_rec.student_career_statistics,
3878 x_waitlist_manual_ind => l_sua_rec.waitlist_manual_ind ,
3879 X_ATTRIBUTE_CATEGORY => l_sua_rec.attribute_category,
3880 X_ATTRIBUTE1 => l_sua_rec.attribute1,
3881 X_ATTRIBUTE2 => l_sua_rec.attribute2,
3882 X_ATTRIBUTE3 => l_sua_rec.attribute3,
3883 X_ATTRIBUTE4 => l_sua_rec.attribute4,
3884 X_ATTRIBUTE5 => l_sua_rec.attribute5,
3885 X_ATTRIBUTE6 => l_sua_rec.attribute6,
3886 X_ATTRIBUTE7 => l_sua_rec.attribute7,
3887 X_ATTRIBUTE8 => l_sua_rec.attribute8,
3891 X_ATTRIBUTE12 => l_sua_rec.attribute12,
3888 X_ATTRIBUTE9 => l_sua_rec.attribute9,
3889 X_ATTRIBUTE10 => l_sua_rec.attribute10,
3890 X_ATTRIBUTE11 => l_sua_rec.attribute11,
3892 X_ATTRIBUTE13 => l_sua_rec.attribute13,
3893 X_ATTRIBUTE14 => l_sua_rec.attribute14,
3894 X_ATTRIBUTE15 => l_sua_rec.attribute15,
3895 X_ATTRIBUTE16 => l_sua_rec.attribute16,
3896 X_ATTRIBUTE17 => l_sua_rec.attribute17,
3897 X_ATTRIBUTE18 => l_sua_rec.attribute18,
3898 X_ATTRIBUTE19 => l_sua_rec.attribute19,
3899 X_ATTRIBUTE20 => l_sua_rec.attribute20,
3900 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3901 X_WLST_PRIORITY_WEIGHT_NUM => l_sua_rec.wlst_priority_weight_num,
3902 X_WLST_PREFERENCE_WEIGHT_NUM => l_sua_rec.wlst_preference_weight_num,
3903 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3904 X_CORE_INDICATOR_CODE => l_sua_rec.core_indicator_code,
3905 X_UPD_AUDIT_FLAG => l_sua_rec.UPD_AUDIT_FLAG ,
3906 X_SS_SOURCE_IND => l_sua_rec.SS_SOURCE_IND
3907 );
3908
3909
3910 -- Minimum CP validations
3911 -- Check if min_cp has not failed, then call procedure to evaluate min cp.
3912 IF NOT l_min_cp_fail THEN
3913
3914 IF l_deny_warn_min_cp IS NOT NULL THEN
3915
3916 l_credit_points := 0;
3917 IF NOT igs_en_elgbl_program.eval_min_cp(
3918 p_person_id => p_person_id,
3919 p_load_calendar_type => p_load_cal_type,
3920 p_load_cal_sequence_number => p_load_sequence_number,
3921 p_uoo_id => l_combined_unit_params(i).uoo_id,
3922 p_program_cd => p_course_cd,
3923 p_program_version => p_course_version,
3924 p_message => l_message_name,
3925 p_deny_warn => l_deny_warn_min_cp,
3926 p_credit_points => l_credit_points,
3927 p_enrollment_category => p_enrollment_category,
3928 p_comm_type => p_comm_type,
3929 p_method_type => p_enr_method_type,
3930 p_min_credit_point => l_min_credit_point,
3931 p_calling_obj => p_calling_obj) THEN
3932
3933 l_min_cp_fail := TRUE;
3934 IF l_message_name IS NOT NULL THEN
3935 p_message := l_message_name;
3936 p_return_status := 'FALSE';
3937 RETURN;
3938 END IF;
3939
3940 END IF; -- IF NOT igs_en_elgbl_program.eval_min_cp
3941
3942 END IF; -- IF l_deny_warn IS NOT NULL THEN
3943
3944 END IF; -- IF NOT l_min_cp_fail THEN
3945 ELSE
3946 CLOSE c_get_sua_dtls;
3947 END IF; -- IF c_get_sua_dtls%FOUND
3948 EXCEPTION
3949
3950 WHEN NO_AUSL_RECORD_FOUND THEN
3951 IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop := NULL;
3952 p_message := 'IGS_SS_CANTDET_ADM_UNT_STATUS';
3953 p_return_status := 'FALSE';
3954 RETURN;
3955
3956 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
3957
3958 -- get message from stack and insert into warn record.
3959 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
3960 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
3961
3962 IF l_message_name IS NOT NULL THEN
3963
3964 -- add to warnings table as error i.e warn_icon is 'D'
3965 l_unit_sec := get_unit_sec(l_combined_unit_params(i).uoo_id);
3966
3967 igs_en_drop_units_api.create_ss_warning(
3968 p_person_id => p_person_id,
3969 p_course_cd => p_course_cd,
3970 p_term_cal_type => p_load_cal_type,
3971 p_term_ci_sequence_number => p_load_sequence_number,
3972 p_uoo_id => l_combined_unit_params(i).uoo_id,
3973 p_message_for => l_unit_sec,
3974 p_message_icon=> 'D',
3975 p_message_name => l_message_name,
3976 p_message_rule_text => NULL,
3977 p_message_tokens => NULL,
3978 p_message_action => NULL,
3982
3979 p_destination => NULL,
3980 p_parameters => NULL,
3981 p_step_type => 'UNIT');
3983 END IF;
3984
3985 WHEN OTHERS THEN
3986 RAISE;
3987
3988 END;
3989
3990 END LOOP;
3991 FOR i IN 1.. l_combined_unit_params.COUNT LOOP
3992
3993 BEGIN
3994 -- Evaluate program steps
3995 l_deny_warn := NULL;
3996
3997 OPEN c_get_sua_dtls(l_combined_unit_params(i).uoo_id);
3998 FETCH c_get_sua_dtls INTO l_sua_rec;
3999 IF c_get_sua_dtls%FOUND THEN
4000 CLOSE c_get_sua_dtls;
4001
4002
4003 IF NOT igs_en_elgbl_program.eval_program_steps(
4004 p_person_id => p_person_id,
4005 p_person_type => g_person_type,
4006 p_load_calendar_type => p_load_cal_type,
4007 p_load_cal_sequence_number => p_load_sequence_number,
4008 p_uoo_id => l_combined_unit_params(i).uoo_id,
4009 p_program_cd => p_course_cd,
4010 p_program_version => p_course_version,
4011 p_enrollment_category => p_enrollment_category,
4012 p_comm_type => p_comm_type,
4013 p_method_type => p_enr_method_type,
4014 p_message => l_message_name,
4015 p_deny_warn => l_deny_warn,
4016 p_calling_obj => p_calling_obj) THEN
4017 IF l_message_name IS NOT NULL THEN
4018 p_message := l_message_name;
4019 p_return_status := 'FALSE';
4020 RETURN;
4021 END IF;
4022 END IF;
4023
4024 ELSE
4025
4026 CLOSE c_get_sua_dtls;
4027 END IF; -- IF c_get_sua_dtls%FOUND
4028
4029 EXCEPTION
4030
4031 WHEN NO_AUSL_RECORD_FOUND THEN
4032 IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop := NULL;
4033 p_message := 'IGS_SS_CANTDET_ADM_UNT_STATUS';
4034 p_return_status := 'FALSE';
4035 RETURN;
4036
4037 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4038
4039 -- get message from stack and insert into warn record.
4040 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4041 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
4042
4043 IF l_message_name IS NOT NULL THEN
4044
4045 -- add to warnings table as error i.e warn_icon is 'D'
4046 l_unit_sec := get_unit_sec(l_combined_unit_params(i).uoo_id);
4047
4048 igs_en_drop_units_api.create_ss_warning(
4049 p_person_id => p_person_id,
4050 p_course_cd => p_course_cd,
4051 p_term_cal_type => p_load_cal_type,
4052 p_term_ci_sequence_number => p_load_sequence_number,
4053 p_uoo_id => l_combined_unit_params(i).uoo_id,
4054 p_message_for => l_unit_sec,
4055 p_message_icon=> 'D',
4056 p_message_name => l_message_name,
4057 p_message_rule_text => NULL,
4058 p_message_tokens => NULL,
4059 p_message_action => NULL,
4060 p_destination => NULL,
4061 p_parameters => NULL,
4062 p_step_type => 'UNIT');
4063
4064 END IF;
4065
4066 WHEN OTHERS THEN
4067 RAISE;
4068
4069 END;
4070
4071 END LOOP;
4072
4073 l_return_status := NULL;
4074 l_message_name := NULL;
4075 -- The call to encumbrance check procedure does not require a unit code, hence it can be evaluated outside the loop.
4076 validate_enr_encmb( p_person_id,
4077 p_course_cd,
4078 p_load_cal_type,
4079 p_load_sequence_number,
4080 l_return_status,
4081 l_message_name
4082 );
4083 IF l_return_status IS NOT NULL AND l_message_name IS NOT NULL THEN
4084 p_return_status := 'FALSE';
4085 p_message := l_message_name;
4086 RETURN;
4087 END IF;
4088
4089 EXCEPTION
4090 WHEN OTHERS THEN
4091 IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop := NULL;
4092 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
4093 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_planned_sua :',SQLERRM);
4094 END IF;
4095 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
4096 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_planned_sua');
4100 END create_planned_sua;
4097 IGS_GE_MSG_STACK.ADD;
4098 RAISE;
4099
4101
4102
4103 PROCEDURE create_unconfirm_sua(
4104 p_person_id IN NUMBER,
4105 p_course_cd IN VARCHAR2,
4106 p_course_version IN NUMBER,
4107 p_load_cal_type IN VARCHAR2,
4108 p_load_sequence_number IN NUMBER,
4109 p_unit_params IN OUT NOCOPY t_params_table,
4110 p_selected_units IN VARCHAR2,
4111 p_enrollment_category IN VARCHAR2,
4112 p_enr_method_type IN VARCHAR2,
4113 p_comm_type IN VARCHAR2,
4114 p_succ_suas OUT NOCOPY VARCHAR2,
4115 p_message OUT NOCOPY VARCHAR2,
4116 p_return_status OUT NOCOPY VARCHAR2,
4117 p_calling_obj IN VARCHAR2) AS
4118 -------------------------------------------------------------------------------------------
4119 --Created by : Basanth Kumar D, Oracle IDC
4120 --Date created: 29-JUL-2005
4121 --Purpose :
4122 -- This procedure creates unconfirm unit attempts for the passed in list of uoo_ids.
4123 -- The API tries to reserve seats in all cases except when the special/audit persmission step
4124 -- is deny or when no seats are available.
4125 --Change History:
4126 --Who When What
4127
4128 -------------------------------------------------------------------------------------------
4129
4130 -- cursor to check wheter a unit attempt exists
4131 CURSOR chk_sua_exists (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)IS
4132 SELECT 'X'
4133 FROM igs_en_su_attempt
4134 WHERE person_id = p_person_id
4135 AND course_cd = p_course_cd
4136 AND uoo_id = cp_uoo_id
4137 AND unit_attempt_status <> 'DROPPED';
4138
4139
4140 l_out_uoo_ids VARCHAR2(1000);
4141 l_sua_exists VARCHAR2(1);
4142 l_enc_message_name VARCHAR2(2000);
4143 l_message_name VARCHAR2(4000);
4144 l_app_short_name VARCHAR2(100);
4145 l_msg_index NUMBER;
4146 l_unit_sec VARCHAR2(100);
4147 l_return_status VARCHAR2(10);
4148 l_deny_warn VARCHAR2(10);
4149 l_create_sub BOOLEAN;
4150 l_message_action VARCHAR2(100);
4151 l_create_wlst VARCHAR2(1);
4152
4153 BEGIN
4154
4155
4156 FOR i IN 1.. p_unit_params.COUNT LOOP
4157
4158 BEGIN
4159 -- check if the creation of this subordinate unit attempt is valid
4160 -- This cannot be created if system added this subordinate and
4161 -- its superior didnot take a seat
4162 l_create_sub := FALSE;
4163 l_create_sub := chk_sua_creation_is_valid(p_person_id,
4164 p_course_cd,
4165 p_unit_params(i).uoo_id,
4166 p_load_cal_type,
4167 p_load_sequence_number,
4168 p_unit_params,
4169 p_selected_units
4170 );
4171
4172 -- if the unit creation is valid
4173 IF l_create_sub THEN
4174
4175 -- Check if unit requires special/audit permission, if required the procedure creates the appropriate warning record and returns true.
4176 -- If the procedure has returned false, it implies that no permission is required and the API can continue with the creation of unit attempts.
4177 -- The out paramter l_message_name has a value only in case of errors. Hence check for the same and exit out.
4178 l_message_name := NULL;
4179
4180
4181 IF permission_required( p_person_id,
4182 p_course_cd,
4183 p_load_cal_type,
4184 p_load_sequence_number,
4185 p_unit_params(i).uoo_id,
4186 p_unit_params(i).spl_perm_step ,
4187 p_unit_params(i).aud_perm_step ,
4188 p_unit_params(i).ass_ind,
4189 p_unit_params(i).override_enrolled_cp,
4190 p_unit_params(i).grading_schema_cd,
4191 p_unit_params(i).gs_version_number,
4192 l_message_name,
4193 p_calling_obj) THEN
4194
4195 IF l_message_name IS NOT NULL THEN
4196 --implies some unexpected error , set return status and message name and stop processing.
4197
4198 p_return_status := 'FALSE';
4199 p_message := l_message_name;
4200 RETURN;
4201
4202 END IF; -- IF l_message_name IS NOT NULL
4203
4204 ELSE
4205 l_create_wlst := 'N';
4206 IF waitlist_required(
4207 p_person_id ,
4208 p_course_cd ,
4209 p_unit_params(i).uoo_id,
4210 p_unit_params(i).wlst_step,
4211 p_load_cal_type,
4212 p_load_sequence_number,
4213 p_unit_params(i).ass_ind,
4214 p_enr_method_type,
4215 p_unit_params(i).override_enrolled_cp,
4216 NULL,
4220 p_calling_obj ,
4217 p_unit_params(i).grading_schema_cd,
4218 p_unit_params(i).gs_version_number,
4219 l_create_wlst,
4221 l_message_name) THEN
4222
4223 IF l_message_name IS NOT NULL THEN
4224 --implies some unexpected error , set return status and message name and stop processing.
4225
4226 p_return_status := 'FALSE';
4227 p_message := l_message_name;
4228 RETURN;
4229
4230 END IF; -- IF l_message_name IS NOT NULL
4231
4232 -- set this flag so that this unit can be waitlisted
4233 IF p_calling_obj = 'SUBMITPLAN' THEN
4234 p_unit_params(i).create_wlst := l_create_wlst;
4235 END IF;
4236 ELSE
4237
4238 l_return_status := NULL;
4239 IF p_calling_obj = 'SUBMITPLAN' THEN
4240 -- call procedure, since creation of unconfirm unit attempts from PLANSUBMIT is not an autonomous transaction.
4241 create_sua_from_plan(p_person_id,
4242 p_course_cd,
4243 p_unit_params(i).uoo_id,
4244 p_load_cal_type,
4245 p_load_sequence_number,
4246 p_unit_params(i).ass_ind,
4247 'N', --- pass waitlsit ind as 'N'
4248 p_enr_method_type,
4249 p_unit_params(i).override_enrolled_cp,
4250 p_unit_params(i).grading_schema_cd,
4251 p_unit_params(i).gs_version_number,
4252 p_calling_obj,
4253 p_message,
4254 l_return_status) ;
4255 ELSE
4256
4257 IF p_calling_obj = 'SWAP' THEN
4258
4259 IGS_EN_VAL_SUA.validate_mus(p_person_id => p_person_id,
4260 p_course_cd => p_course_cd,
4261 p_uoo_id => p_unit_params(i).uoo_id
4262 );
4263
4264 END IF;
4265
4266 create_sua(p_person_id,
4267 p_course_cd,
4268 p_unit_params(i).uoo_id,
4269 p_load_cal_type,
4270 p_load_sequence_number,
4271 p_unit_params(i).ass_ind,
4272 p_enr_method_type,
4273 p_unit_params(i).override_enrolled_cp,
4274 p_unit_params(i).grading_schema_cd,
4275 p_unit_params(i).gs_version_number,
4276 p_calling_obj,
4277 l_return_status,
4278 p_message) ;
4279
4280
4281 END IF;
4282 -- Check for any messages
4283 IF l_return_status = 'FALSE' AND p_message IS NOT NULL THEN
4284 p_return_status := 'FALSE';
4285 RETURN;
4286 END IF;
4287
4288 -- If unit is sucessfully created add to the list of l_out_uoo_ids.
4289 IF l_return_status IS NULL THEN
4290 IF l_out_uoo_ids IS NULL THEN
4291 l_out_uoo_ids := p_unit_params(i).uoo_id;
4292 ELSE
4293 l_out_uoo_ids :=l_out_uoo_ids||','|| p_unit_params(i).uoo_id;
4294 END IF;
4295 END IF;
4296
4297 END IF;-- IF NOT waitlist_required(
4298
4299 END IF; -- IF NOT permission_required
4300
4301 END IF; -- IF l_sup_uoo_id IS NULL OR l_create_sub THEN
4302
4303 EXCEPTION
4304
4305 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4306
4307 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4308 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
4309
4310 IF l_message_name IS NOT NULL THEN
4311
4312 l_unit_sec := get_unit_sec(p_unit_params(i).uoo_id);
4313 ---- Add to warnings table as error
4314 igs_en_drop_units_api.create_ss_warning(
4315 p_person_id => p_person_id,
4316 p_course_cd => p_course_cd,
4317 p_term_cal_type => p_load_cal_type,
4318 p_term_ci_sequence_number => p_load_sequence_number,
4319 p_uoo_id => p_unit_params(i).uoo_id,
4320 p_message_for => l_unit_sec,
4321 p_message_icon=> 'D',
4322 p_message_name => l_message_name,
4323 p_message_rule_text => NULL,
4324 p_message_tokens => NULL,
4325 p_message_action => NULL,
4326 p_destination => NULL,
4327 p_parameters => NULL,
4331
4328 p_step_type => 'UNIT');
4329
4330 END IF;
4332 WHEN OTHERS THEN
4333 RAISE;
4334 END;
4335 END LOOP;
4336 p_succ_suas := l_out_uoo_ids;
4337
4338 EXCEPTION
4339 WHEN OTHERS THEN
4340
4341 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
4342 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_unconfirm_sua :',SQLERRM);
4343 END IF;
4344 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
4345 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_unconfirm_sua');
4346 IGS_GE_MSG_STACK.ADD;
4347 RAISE;
4348
4349 END create_unconfirm_sua;
4350
4351 PROCEDURE create_enroll_sua( p_person_Id IN NUMBER,
4352 p_course_cd IN VARCHAR2,
4353 p_course_version IN VARCHAR2,
4354 p_uoo_ids IN VARCHAR2,
4355 p_load_cal_type IN VARCHAR2,
4356 p_load_sequence_number IN NUMBER,
4357 p_enrollment_category IN VARCHAR2,
4358 p_enr_meth_type IN VARCHAR2,
4359 p_comm_type IN VARCHAR2,
4360 p_message OUT NOCOPY VARCHAR2,
4361 p_return_status OUT NOCOPY VARCHAR2,
4362 p_calling_obj IN VARCHAR2) AS
4363 -------------------------------------------------------------------------------------------
4364 --Created by : Basanth Kumar D, Oracle IDC
4365 --Date created: 29-JUL-2005
4366 --Purpose : This procedure validates the necessary steps and enrolls the unit attempts for the passed in list of uoo_ids.
4367 --Change History:
4368 --Who When What
4369 --ckasu 14-FEB-2006 Modified as a part of bug#5036954 inorder to resolve string literal issue.
4370 --bdeviset 02-DEC-2005 Modified apps exception block after enroll_cart_unit procedure
4371 -- to get the tokens of message IGS_EN_INVALID_SUP_SUB for bug# 4706395
4372
4373 -------------------------------------------------------------------------------------------
4374
4375 -- cursor to fetch the swap units
4376 CURSOR cur_swap_units IS
4377 SELECT DISTINCT sua.uoo_id, sua.sup_unit_cd, sua.no_assessment_ind
4378 FROM igs_en_su_attempt sua, igs_ca_teach_to_load_v lod
4379 WHERE sua.person_id = p_person_Id
4380 AND sua.course_cd = p_course_cd
4381 AND sua.unit_attempt_status = 'UNCONFIRM'
4382 AND lod.teach_cal_type = sua.cal_type
4383 AND lod.teach_ci_sequence_number = sua.ci_sequence_number
4384 AND lod.load_cal_type = p_load_cal_type
4385 AND lod.load_ci_sequence_number = p_load_sequence_number
4386 AND sua.SS_SOURCE_IND = 'S'
4387 ORDER BY sua.sup_unit_cd DESC;
4388
4389 -- cursor to get the cart units
4390 CURSOR cur_cart_units IS
4391 SELECT DISTINCT sua.uoo_id, sua.sup_unit_cd
4392 FROM igs_en_su_attempt sua, igs_ca_teach_to_load_v lod
4393 WHERE sua.person_id = p_person_Id
4394 AND sua.course_cd = p_course_cd
4395 AND sua.unit_attempt_status = 'UNCONFIRM'
4396 AND sua.SS_SOURCE_IND <> 'S'
4397 AND lod.teach_cal_type = sua.cal_type
4398 AND lod.teach_ci_sequence_number = sua.ci_sequence_number
4399 AND lod.load_cal_type = p_load_cal_type
4400 AND lod.load_ci_sequence_number = p_load_sequence_number
4401 ORDER BY sua.sup_unit_cd DESC;
4402
4403 -- To get student unit attempt and unit code
4404 CURSOR chk_unconfirm_sua (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
4405 SELECT unit_attempt_status,unit_cd,version_number
4406 FROM igs_en_su_attempt
4407 WHERE person_id = p_person_id
4408 AND course_cd = p_course_cd
4409 AND uoo_id = cp_uoo_id;
4410
4411 TYPE c_ref_cursor IS REF CURSOR;
4412 c_get_swap_units c_ref_cursor;
4413
4414 NO_AUSL_RECORD_FOUND EXCEPTION;
4415 PRAGMA EXCEPTION_INIT(NO_AUSL_RECORD_FOUND , -20010);
4416
4417 l_swap_unit_params t_params_table;
4418
4419 l_uoo_ids VARCHAR2(2000);
4420 l_uoo_id igs_en_su_attempt.uoo_id%TYPE;
4421 l_deny_warn_coreq VARCHAR2(10);
4422 l_deny_warn_prereq VARCHAR2(10);
4423 l_deny_warn_min_cp VARCHAR2(10);
4424 l_message_icon VARCHAR2(30);
4425 l_sua_status igs_en_su_attempt.unit_attempt_status%TYPE;
4426 l_message_name VARCHAR2(4000);
4427 l_app_short_name VARCHAR2(100);
4428 l_msg_index NUMBER;
4429 l_unit_sec VARCHAR2(100);
4430 l_enc_message_name VARCHAR2(2000);
4431 l_succ_new_uoo_ids VARCHAR2(1000);
4432 l_unit_ver igs_en_su_attempt.version_number%TYPE;
4433 l_unit_cd igs_en_su_attempt.unit_cd%TYPE;
4434 l_return_status VARCHAR2(30);
4435 l_deny_warn VARCHAR2(30);
4436 l_message_for VARCHAR2(100);
4437
4438 -- min and max cp val status variables
4439 l_max_cp_fail BOOLEAN;
4440 l_min_cp_fail BOOLEAN;
4441 l_new_uoo_ids VARCHAR2(4000);
4442 l_min_credit_point NUMBER;
4443 l_credit_points NUMBER;
4444 v_swap_index BINARY_INTEGER := 1;
4445
4446 l_token1 VARCHAR2(1000);
4447 l_token2 VARCHAR2(1000);
4448 l_token_set VARCHAR2(2100);
4449
4450 BEGIN
4451
4452
4453
4454 l_uoo_ids := p_uoo_ids;
4455 l_new_uoo_ids := NULL;
4456
4457 -- if calling object submitswap then fetch the swap units,
4458 -- i.e units in unconfirm status with ss_cart_ind set to 'S'.
4459 IF p_calling_obj IN ('SWAP', 'SUBMITSWAP') THEN
4463 IF INSTR(','||p_uoo_ids||',',','||swap_records.uoo_id||',',1) = 0 THEN
4460 --first do permission check of existing swap units
4461
4462 FOR swap_records IN cur_swap_units LOOP
4464
4465 l_swap_unit_params(v_swap_index).uoo_id := swap_records.uoo_id;
4466 l_swap_unit_params(v_swap_index).ass_ind := swap_records.no_assessment_ind;
4467 v_swap_index := v_swap_index+1;
4468 end if;
4469 end loop;
4470
4471 --now check permission setup
4472 l_return_status := NULL;
4473 get_perm_wlst_setup(p_person_id,
4474 p_course_cd,
4475 p_load_cal_type,
4476 p_load_sequence_number,
4477 l_swap_unit_params,
4478 p_message,
4479 l_return_status,
4480 'N');
4481 IF l_return_status = 'FALSE' THEN
4482 p_return_status := 'FALSE';
4483 RETURN;
4484 END IF;
4485 FOR i in 1 .. l_swap_unit_params.COUNT loop
4486
4487 l_message_name := NULL;
4488 IF permission_required( p_person_id,
4489 p_course_cd,
4490 p_load_cal_type,
4491 p_load_sequence_number,
4492 l_swap_unit_params(i).uoo_id,
4493 l_swap_unit_params(i).spl_perm_step ,
4494 l_swap_unit_params(i).aud_perm_step ,
4495 l_swap_unit_params(i).ass_ind,
4496 l_swap_unit_params(i).override_enrolled_cp,
4497 l_swap_unit_params(i).grading_schema_cd,
4498 l_swap_unit_params(i).gs_version_number,
4499 l_message_name,
4500 p_calling_obj) THEN
4501
4502 IF l_message_name IS NOT NULL THEN
4503 --implies some unexpected error , set return status and message name and
4504 --stop processing.
4505
4506 p_return_status := 'FALSE';
4507 p_message := l_message_name;
4508 RETURN;
4509 END IF;
4510 END IF;
4511 END LOOP;
4512
4513
4514 FOR swap_records IN cur_swap_units LOOP
4515
4516 IF NOT IGS_EN_ELGBL_UNIT.eval_unit_steps(
4517 p_person_id => p_person_id,
4518 p_person_type => g_person_type,
4519 p_load_cal_type => p_load_cal_type,
4520 p_load_sequence_number => p_load_sequence_number,
4521 p_uoo_id => swap_records.uoo_id,
4522 p_course_cd => p_course_cd,
4523 p_course_version => p_course_version,
4524 p_enrollment_category => p_enrollment_category,
4525 p_enr_method_type => p_enr_meth_type,
4526 p_comm_type => p_comm_type,
4527 p_message => l_message_name, -- out
4528 p_deny_warn => l_deny_warn, -- out
4529 p_calling_obj => p_calling_obj) THEN
4530 IF l_message_name IS NOT NULL THEN
4531 p_message := l_message_name;
4532 p_return_status := 'FALSE';
4533 RETURN;
4534 END IF;
4535
4536 END IF;
4537
4538 -- pickup the existing cart records, but not the newly created unconfirmed cart records
4539 IF l_new_uoo_ids IS NOT NULL THEN
4540 l_new_uoo_ids := l_new_uoo_ids ||','||swap_records.uoo_id;
4541 ELSE
4542 l_new_uoo_ids := swap_records.uoo_id;
4543 END IF;
4544
4545 END LOOP;
4546
4547 ELSIF p_calling_obj IN ('SCHEDULE','ENROLPEND', 'CART','SUBMITCART','SUBMITPLAN' ) THEN
4548 -- cart or schedule then append the units already in cart.
4549
4550
4551 FOR cart_records IN cur_cart_units LOOP
4552 IF INSTR(','||p_uoo_ids||',',','||cart_records.uoo_id||',',1) = 0 THEN
4553 IF NOT IGS_EN_ELGBL_UNIT.eval_unit_steps(
4554 p_person_id => p_person_id,
4555 p_person_type => g_person_type,
4556 p_load_cal_type => p_load_cal_type,
4557 p_load_sequence_number => p_load_sequence_number,
4558 p_uoo_id => cart_records.uoo_id,
4559 p_course_cd => p_course_cd,
4560 p_course_version => p_course_version,
4561 p_enrollment_category => p_enrollment_category,
4562 p_enr_method_type => p_enr_meth_type,
4563 p_comm_type => p_comm_type,
4564 p_message => l_message_name, -- out
4568 p_message := l_message_name;
4565 p_deny_warn => l_deny_warn, -- out
4566 p_calling_obj => p_calling_obj) THEN
4567 IF l_message_name IS NOT NULL THEN
4569 p_return_status := 'FALSE';
4570 RETURN;
4571 END IF;
4572 END IF;
4573 -- pickup the existing cart records, but not the newly created unconfirmed cart records
4574 IF l_new_uoo_ids IS NOT NULL THEN
4575 l_new_uoo_ids := l_new_uoo_ids ||','||cart_records.uoo_id;
4576 ELSE
4577 l_new_uoo_ids := cart_records.uoo_id;
4578 END IF;
4579 END IF;
4580 END LOOP;
4581
4582 END IF; -- IF p_calling_obj = 'SUBMITSWAP' THEN
4583
4584 IF l_new_uoo_ids IS NOT NULL THEN
4585 l_uoo_ids := l_new_uoo_ids || ',' || l_uoo_ids;
4586 END IF;
4587 -- Initialize min and max cp faile as false
4588
4589 l_max_cp_fail := FALSE;
4590 l_min_cp_fail := FALSE;
4591
4592 l_deny_warn := NULL;
4593 l_message_name := NULL;
4594 -- get the notification flag for max cp
4595 l_deny_warn := igs_ss_enr_details.get_notification(
4596 p_person_type => g_person_type,
4597 p_enrollment_category => p_enrollment_category,
4598 p_comm_type => p_comm_type,
4599 p_enr_method_type => p_enr_meth_type,
4600 p_step_group_type => 'PROGRAM',
4601 p_step_type => 'FMAX_CRDT' ,
4602 p_person_id => p_person_id,
4603 p_message => l_message_name
4604 );
4605 IF l_message_name IS NOT NULL THEN
4606 p_return_status := 'FALSE';
4607 p_message := l_message_name;
4608 RETURN;
4609 END IF;
4610 --loop through all the uoo_ids
4611
4612 WHILE l_uoo_ids IS NOT NULL LOOP
4613
4614 BEGIN
4615 l_message_name := NULL;
4616 IF(INSTR(l_uoo_ids,',',1) = 0) THEN
4617 l_uoo_id := TO_NUMBER(l_uoo_ids);
4618 ELSE
4619 l_uoo_id := TO_NUMBER(SUBSTR(l_uoo_ids,0,INSTR(l_uoo_ids,',',1)-1)) ;
4620 END IF;
4621
4622 -- remove the uoo_id that will be processed
4623 IF(instr(l_uoo_ids,',',1) = 0) THEN
4624 l_uoo_ids := NULL;
4625 ELSE
4626 l_uoo_ids := substr(l_uoo_ids,instr(l_uoo_ids,',',1)+1);
4627 END IF;
4628
4629 --Check that unit attempt status is UNCONFIRM .
4630 OPEN chk_unconfirm_sua(l_uoo_id);
4631 FETCH chk_unconfirm_sua INTO l_sua_status,l_unit_cd,l_unit_ver;
4632 CLOSE chk_unconfirm_sua;
4633
4634 IF l_sua_status = 'UNCONFIRM' THEN
4635
4636 -- Evaluate Max Cp check
4637 -- Check if l_max_cp is false then call procedure to evaluate max cp.
4638 -- p_credit_points is deliberately passed as null for the procedure to calculate the existing enrolled CP.
4639 IF NOT l_max_cp_fail THEN
4640
4641
4642 IF l_deny_warn IS NOT NULL THEN
4643 l_message_name := NULL;
4644 IF NOT igs_en_elgbl_program.eval_max_cp(
4645 p_person_id => p_person_id,
4646 p_load_calendar_type => p_load_cal_type,
4647 p_load_cal_sequence_number => p_load_sequence_number,
4648 p_uoo_id => l_uoo_id,
4649 p_program_cd => p_course_cd,
4650 p_program_version => p_course_version,
4651 p_message => l_message_name,
4652 p_deny_warn => l_deny_warn,
4653 p_upd_cp => NULL,
4654 p_calling_obj => p_calling_obj) THEN
4655
4656 l_max_cp_fail := TRUE;
4657 IF l_message_name IS NOT NULL THEN
4658 p_return_status := 'FALSE';
4659 p_message := l_message_name;
4660 RETURN;
4661 END IF; -- IF l_message_name IS NOT NULL
4662
4663
4664 END IF; -- IF NOT igs_en_elgbl_program.eval_max_cp
4665
4666 END IF; -- IF l_deny_warn IS NOT NULL THEN
4667
4668 END IF;-- IF NOT l_max_cp_fail THEN
4669
4670 -- Enroll the unit
4671 igs_ss_en_wrappers.enroll_cart_unit(
4672 p_person_id => p_person_id ,
4673 p_uoo_id => l_uoo_id ,
4674 p_unit_cd => l_unit_cd ,
4675 p_version_number => l_unit_ver ,
4676 p_course_cd => p_course_cd ,
4677 p_unit_attempt_status => 'ENROLLED' ,
4681 IF l_succ_new_uoo_ids IS NULL THEN
4678 p_enrolled_dt => SYSDATE
4679 );
4680 -- Add the unit enrolled successfully
4682 l_succ_new_uoo_ids := l_uoo_id;
4683 ELSE
4684 l_succ_new_uoo_ids := l_succ_new_uoo_ids||','||l_uoo_id;
4685 END IF;
4686
4687 END IF; -- IF l_sua_status = 'UNCONFIRM' THEN
4688
4689 EXCEPTION
4690
4691 -- To handle user defined exception raised when adminstrative unit status cannot be detremined
4692 WHEN NO_AUSL_RECORD_FOUND THEN
4693 RAISE NO_AUSL_RECORD_FOUND;
4694
4695 -- Catch any exceptions here. If unhandled exceptions occur then return otherwise log an error record and continue
4696 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4697
4698 l_message_name := NULL;
4699 l_token_set := NULL;
4700 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4701 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
4702
4703 -- Extract the token values if the retrieved msg is IGS_EN_INVALID_SUP_SUB
4704 IF l_message_name = 'IGS_EN_INVALID_SUP_SUB' THEN
4705 l_token1 := FND_MESSAGE.GET_TOKEN('CONSTAT',NULL);
4706 l_token2 := FND_MESSAGE.GET_TOKEN('SSTAT',NULL);
4707 -- Then prepare the token along with their values in the fromat required
4708 -- token:token_value
4709 IF l_token1 IS NOT NULL AND l_token2 IS NOT NULL THEN
4710 l_token_set := 'CONSTAT' || ':' || l_token1 ||';'||'SSTAT'||':'||l_token2||';' ;
4711 END IF;
4712 END IF;
4713
4714 IF l_message_name IS NOT NULL THEN
4715
4716 l_unit_sec := get_unit_sec(l_uoo_id);
4717 ---- Add to warnings table as error
4718 igs_en_drop_units_api.create_ss_warning(
4719 p_person_id => p_person_id,
4720 p_course_cd => p_course_cd,
4721 p_term_cal_type => p_load_cal_type,
4722 p_term_ci_sequence_number => p_load_sequence_number,
4723 p_uoo_id => l_uoo_id,
4724 p_message_for => l_unit_sec,
4725 p_message_icon=> 'D',
4726 p_message_name => l_message_name,
4727 p_message_rule_text => NULL,
4728 p_message_tokens => l_token_set,
4729 p_message_action => NULL,
4730 p_destination => NULL,
4731 p_parameters => NULL,
4732 p_step_type => 'UNIT');
4733 END IF; -- IF l_message_name IS NOT NULL THEN
4734
4735
4736 WHEN OTHERS THEN
4737 RAISE;
4738 END; --- end of begin
4739
4740 -- END IF; -- IF l_sua_status = 'UNCONFIRM' THEN
4741 END LOOP; -- WHILE l_uoo_ids IS NOT NULL LOOP
4742
4743
4744
4745 l_uoo_ids := l_succ_new_uoo_ids;
4746
4747 --- get the notification flag for min cp
4748 l_deny_warn_min_cp := NULL;
4749 l_message_name := NULL;
4750 l_deny_warn_min_cp := igs_ss_enr_details.get_notification(
4751 p_person_type => g_person_type,
4752 p_enrollment_category => p_enrollment_category,
4753 p_comm_type => p_comm_type,
4754 p_enr_method_type => p_enr_meth_type,
4755 p_step_group_type => 'PROGRAM',
4756 p_step_type => 'FMIN_CRDT' ,
4757 p_person_id => p_person_id,
4758 p_message => l_message_name
4759 );
4760 IF l_message_name IS NOT NULL THEN
4761 p_return_status := 'FALSE';
4762 p_message := l_message_name;
4763 RETURN;
4764 END IF;
4765
4766 -- Loop through the units enrolled and implement the following validations:
4767 WHILE l_succ_new_uoo_ids IS NOT NULL LOOP
4768
4769 BEGIN
4770 l_message_name := NULL;
4771 IF(INSTR(l_succ_new_uoo_ids,',',1) = 0) THEN
4772 l_uoo_id := TO_NUMBER(l_succ_new_uoo_ids);
4773 ELSE
4774 l_uoo_id := TO_NUMBER(SUBSTR(l_succ_new_uoo_ids,0,INSTR(l_succ_new_uoo_ids,',',1)-1)) ;
4775 END IF;
4776
4777 -- remove the uoo id that will be processed
4778 IF(instr(l_succ_new_uoo_ids,',',1) = 0) THEN
4779 l_succ_new_uoo_ids := NULL;
4780 ELSE
4781 l_succ_new_uoo_ids := substr(l_succ_new_uoo_ids,instr(l_succ_new_uoo_ids,',',1)+1);
4782 END IF;
4783
4784 -- Minimum CP validations
4785 -- Check if min_cp has not failed, then call procedure to evaluate min cp.
4786 IF NOT l_min_cp_fail THEN
4787
4788 IF l_deny_warn_min_cp IS NOT NULL THEN
4789
4790 l_credit_points := 0;
4791 l_message_name := NULL;
4792 IF NOT igs_en_elgbl_program.eval_min_cp(
4793 p_person_id => p_person_id,
4797 p_program_cd => p_course_cd,
4794 p_load_calendar_type => p_load_cal_type,
4795 p_load_cal_sequence_number => p_load_sequence_number,
4796 p_uoo_id => l_uoo_id,
4798 p_program_version => p_course_version,
4799 p_message => l_message_name,
4800 p_deny_warn => l_deny_warn_min_cp,
4801 p_credit_points => l_credit_points, -- deliberately passing the value zero since the cp has already been enrolled
4802 p_enrollment_category => p_enrollment_category,
4803 p_comm_type => p_comm_type,
4804 p_method_type => p_enr_meth_type,
4805 p_min_credit_point => l_min_credit_point,
4806 p_calling_obj => p_calling_obj) THEN
4807
4808 l_min_cp_fail := TRUE;
4809 IF l_message_name IS NOT NULL THEN
4810 p_return_status := 'FALSE';
4811 p_message := l_message_name;
4812 RETURN;
4813 END IF; -- IF l_message_name IS NOT NULL
4814
4815
4816 END IF; -- IF NOT igs_en_elgbl_program.eval_min_cp
4817
4818 END IF; -- IF l_deny_warn IS NOT NULL THEN
4819
4820 END IF; -- IF NOT l_min_cp_fail THEN
4821
4822 -- Implement Program validations
4823 l_deny_warn := NULL;
4824 IF NOT igs_en_elgbl_program.eval_program_steps(
4825 p_person_id => p_person_id,
4826 p_person_type => g_person_type,
4827 p_load_calendar_type => p_load_cal_type,
4828 p_load_cal_sequence_number => p_load_sequence_number,
4829 p_uoo_id => l_uoo_id,
4830 p_program_cd => p_course_cd,
4831 p_program_version => p_course_version,
4832 p_enrollment_category => p_enrollment_category,
4833 p_comm_type => p_comm_type,
4834 p_method_type => p_enr_meth_type,
4835 p_message => l_message_name,
4836 p_deny_warn => l_deny_warn,
4837 p_calling_obj => p_calling_obj) THEN
4838
4839 IF l_message_name IS NOT NULL THEN
4840 p_return_status := 'FALSE';
4841 p_message := l_message_name;
4842 RETURN;
4843 END IF;
4844
4845 END IF; -- IF NOT igs_en_enroll_wlst.validate_prog
4846
4847 -- Execute call to the fee assesment procedure.
4848 -- in case of cart swap submit plan no need to call this because
4849 -- beacuse we will be rolling back the enrolled units to
4850 -- unconfirm units and no validations that need to be informed to
4851 -- the user are done here.
4852 IF p_calling_obj NOT IN ('CART','SWAP','SUBMITPLAN') THEN
4853 IGS_SS_EN_WRAPPERS.call_fee_ass (
4854 p_person_id => p_person_id,
4855 p_cal_type => p_load_cal_type,
4856 p_sequence_number => p_load_sequence_number,
4857 p_course_cd => p_course_cd,
4858 p_unit_cd => l_unit_cd,
4859 p_uoo_id => l_uoo_id);
4860
4861 END IF;
4862
4863 EXCEPTION
4864
4865 -- To handle user defined exception raised when adminstrative unit status cannot be detremined
4866 WHEN NO_AUSL_RECORD_FOUND THEN
4867 RAISE NO_AUSL_RECORD_FOUND;
4868
4869
4870 -- Catch any exceptions here. If unhandled exceptions occur then return otherwise log an error record and continue
4871 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4872
4873 l_message_name := NULL;
4874 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4875 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
4876
4877 IF l_message_name IS NOT NULL THEN
4878
4879 l_unit_sec := get_unit_sec(l_uoo_id);
4880 ---- Add to warnings table as error
4881 igs_en_drop_units_api.create_ss_warning(
4882 p_person_id => p_person_id,
4883 p_course_cd => p_course_cd,
4884 p_term_cal_type => p_load_cal_type,
4885 p_term_ci_sequence_number => p_load_sequence_number,
4886 p_uoo_id => l_uoo_id,
4887 p_message_for => l_unit_sec,
4888 p_message_icon=> 'D',
4889 p_message_name => l_message_name,
4890 p_message_rule_text => NULL,
4891 p_message_tokens => NULL,
4892 p_message_action => NULL,
4893 p_destination => NULL,
4894 p_parameters => NULL,
4898
4895 p_step_type => 'UNIT');
4896 END IF; -- IF l_message_name IS NOT NULL THEN
4897
4899 WHEN OTHERS THEN
4900 RAISE;
4901
4902
4903 END;
4904 END LOOP; -- WHILE l_succ_new_uoo_ids IS NOT NULL LOOP
4905
4906 l_message_name := NULL;
4907 l_return_status := NULL;
4908 -- The call to encumbrance check procedure does not require a unit code, hence it can be evaluated outside the loop.
4909 validate_enr_encmb( p_person_id,
4910 p_course_cd,
4911 p_load_cal_type,
4912 p_load_sequence_number,
4913 l_return_status,
4914 l_message_name
4915 ) ;
4916 IF l_return_status IS NOT NULL AND l_message_name IS NOT NULL THEN
4917 p_message := l_message_name;
4918 p_return_status := 'FALSE';
4919 RETURN;
4920 END IF;
4921
4922 -- Check if p_calling_obj is "SWAP", "SUBMITSWAP"
4923 IF p_calling_obj In ( 'SWAP','SUBMITSWAP') THEN
4924
4925 -- Fetch the enrolled units in the term that have not been selected for swap.
4926 -- Use the following ref cursor for the same:
4927
4928 l_message_name := NULL;
4929
4930 l_deny_warn_coreq := NULL;
4931 l_deny_warn_coreq := igs_ss_enr_details.get_notification(
4932 p_person_type => g_person_type,
4933 p_enrollment_category => p_enrollment_category,
4934 p_comm_type => p_comm_type,
4935 p_enr_method_type => p_enr_meth_type,
4936 p_step_group_type => 'UNIT',
4937 p_step_type => 'COREQ' ,
4938 p_person_id => p_person_id,
4939 p_message => l_message_name
4940 );
4941 IF l_message_name IS NOT NULL THEN
4942 p_return_status := 'FALSE';
4943 p_message := l_message_name;
4944 RETURN;
4945 END IF;
4946
4947 l_deny_warn_prereq := NULL;
4948 l_deny_warn_prereq := igs_ss_enr_details.get_notification(
4949 p_person_type => g_person_type,
4950 p_enrollment_category => p_enrollment_category,
4951 p_comm_type => p_comm_type,
4952 p_enr_method_type => p_enr_meth_type,
4953 p_step_group_type => 'UNIT',
4954 p_step_type => 'PREREQ' ,
4955 p_person_id => p_person_id,
4956 p_message => l_message_name
4957 );
4958 IF l_message_name IS NOT NULL THEN
4959 p_return_status := 'FALSE';
4960 p_message := l_message_name;
4961 RETURN;
4962 END IF;
4963
4964 -- modified this cursor as a part of bug#5036954.
4965
4966 OPEN c_get_swap_units FOR
4967 'SELECT SUA.uoo_id
4968 FROM igs_en_su_attempt sua, igs_ca_load_to_teach_v load
4969 WHERE person_id = :p_person_id
4970 AND course_cd = :p_course_cd
4971 AND unit_attempt_status IN (''ENROLLED'',''INVALID'')
4972 AND cal_type = teach_cal_type
4973 AND ci_sequence_number = teach_ci_sequence_number
4974 AND load_cal_type = :p_load_cal_type
4975 AND load_ci_sequence_number = :p_load_sequence_number
4976 AND (uoo_id NOT IN('|| NVL(l_uoo_ids,'-999') ||'))' USING p_person_id,p_course_cd,p_load_cal_type,p_load_sequence_number;
4977
4978 WHILE TRUE LOOP
4979
4980 FETCH c_get_swap_units INTO l_uoo_id;
4981
4982 IF c_get_swap_units%NOTFOUND THEN
4983 CLOSE c_get_swap_units;
4984 EXIT;
4985 END IF;
4986
4987 -- if the uooid exists in the list of the uooids list failed prereq and coreq
4988 -- rules because of swapping the passed units
4989 IF (INSTR(','||igs_en_add_units_api.g_swap_failed_uooids||',' , ','||l_uoo_id||',') <> 0) THEN
4990
4991 l_unit_sec := get_unit_sec(l_uoo_id);
4992 IF l_deny_warn_coreq IS NOT NULL THEN
4993
4994 l_message_name := NULL;
4995 IF NOT IGS_EN_ELGBL_UNIT.eval_coreq( p_person_id => p_person_id,
4996 p_load_cal_type => p_load_cal_type,
4997 p_load_sequence_number => p_load_sequence_number,
4998 p_uoo_id => l_uoo_id,
4999 p_course_cd => p_course_cd,
5000 p_course_version => p_course_version,
5001 p_message => l_message_name,
5002 p_deny_warn => l_deny_warn_coreq,
5003 p_calling_obj => 'JOB') THEN
5004
5005 IF l_deny_warn_coreq = 'DENY' THEN
5006 l_message_icon := 'D';
5007 l_message_name := 'IGS_SS_DENY_COREQ_SWP';
5008 ELSE
5009 l_message_icon := 'W';
5013 igs_en_drop_units_api.create_ss_warning (
5010 l_message_name := 'IGS_SS_WARN_COREQ_SWP';
5011 END IF;
5012
5014 p_person_id => p_person_id,
5015 p_course_cd => p_course_cd,
5016 p_term_cal_type=>p_load_cal_type,
5017 p_term_ci_sequence_number => p_load_sequence_number,
5018 p_uoo_id => l_uoo_id,
5019 p_message_for => l_unit_sec,
5020 p_message_icon=> l_message_icon,
5021 p_message_name => l_message_name,
5022 p_message_rule_text => NULL,
5023 p_message_tokens => NULL,
5024 p_message_action=> NULL ,
5025 p_destination => NULL,
5026 p_parameters => NULL,
5027 p_step_type => 'UNIT');
5028
5029 END IF; -- IF NOT IGS_EN_ELGBL_UNIT.eval_coreq
5030
5031 END IF; -- IF l_deny_warn_coreq IS NOT NULL THEN
5032
5033 IF l_deny_warn_prereq IS NOT NULL THEN
5034 l_message_name := NULL;
5035 IF NOT IGS_EN_ELGBL_UNIT.eval_prereq(
5036 p_person_id => p_person_id,
5037 p_load_cal_type => p_load_cal_type,
5038 p_load_sequence_number => p_load_sequence_number,
5039 p_uoo_id => l_uoo_id,
5040 p_course_cd => p_course_cd,
5041 p_course_version => p_course_version,
5042 p_message => l_message_name,
5043 p_deny_warn => l_deny_warn_prereq,
5044 p_calling_obj => 'JOB') THEN
5045
5046 IF l_deny_warn_prereq = 'DENY' THEN
5047 l_message_icon := 'D';
5048 l_message_name := 'IGS_SS_DENY_PREREQ_SWP';
5049 ELSE
5050 l_message_icon := 'W';
5051 l_message_name := 'IGS_SS_WARN_PREREQ_SWP';
5052 END IF;
5053
5054 igs_en_drop_units_api.create_ss_warning (
5055 p_person_id => p_person_id,
5056 p_course_cd => p_course_cd,
5057 p_term_cal_type=>p_load_cal_type,
5058 p_term_ci_sequence_number => p_load_sequence_number,
5059 p_uoo_id => l_uoo_id,
5060 p_message_for => l_unit_sec,
5061 p_message_icon=> l_message_icon,
5062 p_message_name => l_message_name,
5063 p_message_rule_text => NULL,
5064 p_message_tokens => NULL,
5065 p_message_action=>NULL ,
5066 p_destination =>NULL,
5067 p_parameters =>NULL,
5068 p_step_type => 'UNIT');
5069 END IF; -- IF NOT IGS_EN_ELGBL_UNIT.eval_prereq
5070
5071 END IF; --IF l_deny_warn_prereq IS NOT NULL THEN
5072
5073 END IF; --IF (INSTR(','||igs_en_add_units_api.g_swap_failed_uooids||',' , ','||l_uoo_id||',') = 0) THEN
5074
5075 END LOOP; -- WHILE TRUE LOOP
5076
5077 END IF; -- IF p_calling_obj In ( 'SWAP','SUBMITSWAP') THEN
5078
5079 EXCEPTION
5080
5081 -- To handle user defined exception raised when adminstrative unit status cannot be detremined
5082 WHEN NO_AUSL_RECORD_FOUND THEN
5083 p_message := 'IGS_SS_CANTDET_ADM_UNT_STATUS';
5084 p_return_status := 'FALSE';
5085 RETURN;
5086
5087 WHEN OTHERS THEN
5088 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
5089 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.create_enroll_sua :',SQLERRM);
5090 END IF;
5091 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
5092 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.create_enroll_sua');
5093 IGS_GE_MSG_STACK.ADD;
5094 RAISE;
5095 END create_enroll_sua;
5096
5097
5098 PROCEDURE delete_cart_error_records(p_person_id IN NUMBER,
5099 p_course_cd IN VARCHAR2,
5100 p_load_cal_type IN VARCHAR2,
5101 p_load_sequence_number IN NUMBER
5102 ) AS
5103 -------------------------------------------------------------------------------------------
5104 --Created by : Chandrasekhar Kasu, Oracle IDC
5105 --Date created: 17-OCT-2005
5106 -- Purpose : creates student unit attempt.
5107 --Change History:
5108 --Who When What
5112
5109 --ckasu 18-OCT-2005 created as a part of bug#4674099 inorder to delete the cart error
5110 -- records present in Planning sheet table
5111 -------------------------------------------------------------------------------------------
5113
5114 CURSOR c_get_rowid IS
5115 SELECT ROWID
5116 FROM igs_en_plan_units
5117 WHERE person_id = p_person_id
5118 AND course_cd = p_course_cd
5119 AND term_cal_type = p_load_cal_type
5120 AND term_ci_sequence_number = p_load_sequence_number
5121 AND cart_error_flag = 'Y';
5122
5123 CURSOR c_ss_warn IS
5124 SELECT ROWID
5125 FROM IGS_EN_STD_WARNINGS
5126 WHERE person_id = p_person_id
5127 AND course_cd = p_course_cd
5128 AND term_cal_type = p_load_cal_type
5129 AND term_ci_sequence_number = p_load_sequence_number
5130 AND step_type <> 'DROP';
5131
5132
5133 BEGIN
5134
5135 FOR c_get_plan_err_rec IN c_get_rowid LOOP
5136
5137 IGS_EN_PLAN_UNITS_PKG.delete_row(x_rowid => c_get_plan_err_rec.ROWID);
5138
5139 END LOOP;
5140
5141 FOR c_ss_warn_rec IN c_ss_warn LOOP
5142 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => c_ss_warn_rec.rowid);
5143 END LOOP;
5144
5145
5146 EXCEPTION
5147 WHEN OTHERS THEN
5148 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
5149 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.delete_plan_error_records');
5150 IGS_GE_MSG_STACK.ADD;
5151 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
5152 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.delete_plan_error_records :',SQLERRM);
5153 END IF;
5154 ROLLBACK;
5155 RAISE;
5156
5157 END delete_cart_error_records;
5158
5159
5160 PROCEDURE add_selected_units (
5161 p_person_id IN NUMBER,
5162 p_course_cd IN VARCHAR2,
5163 p_course_version IN NUMBER,
5164 p_load_cal_type IN VARCHAR2,
5165 p_load_sequence_number IN NUMBER,
5166 p_uoo_ids IN VARCHAR2,
5167 p_calling_obj IN VARCHAR2,
5168 p_validate_person_step IN VARCHAR2,
5169 p_return_status OUT NOCOPY VARCHAR2,
5170 p_message OUT NOCOPY VARCHAR2,
5171 p_deny_warn OUT NOCOPY VARCHAR2,
5172 p_ss_session_id IN NUMBER) AS
5173 -------------------------------------------------------------------------------------------
5174 --Created by : Basanth Kumar D, Oracle IDC
5175 --Date created: 29-JUL-2005
5176 --Purpose : This procedure gets uooids passed reordered (superior/subordinate/others units)
5177 -- Then it gets the perm/waitlsit deatils of the units
5178 -- For 'PLAN' it validates the steps required for the passed units
5179 -- For others it creates unocnfirm units which are later updated to enroll units
5180 -- Finally depending on the calling object, return status and p_deny_warn values
5181 -- it is rolled back to diff savepoints if required.
5182 --Change History:
5183 --Who When What
5184
5185 -------------------------------------------------------------------------------------------
5186
5187 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
5188 l_alternate_code igs_ca_inst.alternate_code%TYPE;
5189 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
5190 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
5191 l_acad_start_dt DATE;
5192 l_acad_end_dt DATE;
5193 l_enr_cat igs_ps_type.enrolment_cat%TYPE;
5194 l_enr_cal_type igs_ca_inst.cal_type%TYPE;
5195 l_enr_ci_seq igs_ca_inst.sequence_number%TYPE;
5196 l_enr_categories VARCHAR2(255);
5197 l_enr_comm VARCHAR2(1000);
5198 l_enc_message_name VARCHAR2(2000);
5199 l_app_short_name VARCHAR2(100);
5200 l_msg_index NUMBER;
5201 t_reorder_unit_params t_params_table;
5202 l_message_name VARCHAR2(4000);
5203 l_deny_warn VARCHAR2(20);
5204 l_return_status VARCHAR2(20);
5205 l_unconfirm_suas VARCHAR2(1000);
5206 l_steps VARCHAR2(100);
5207
5208 BEGIN
5209
5210 SAVEPOINT sp_add_units_api;
5211
5212 igs_en_add_units_api.g_ss_session_id := p_ss_session_id;
5213
5214 p_return_status := 'TRUE';
5215 IF p_calling_obj IS NULL OR p_calling_obj NOT IN ('SWAP', 'SUBMITSWAP','CART','SUBMITCART','ENROLPEND','SCHEDULE','PLAN','SUBMITPLAN') THEN
5216 p_message := 'IGS_EN_INVALID_CALLINGOBJ';
5217 p_return_status := 'FALSE';
5218 p_deny_warn := 'D';
5219 igs_en_add_units_api.g_ss_session_id := NULL;
5220 RETURN;
5221 END IF;
5222
5223 IF p_calling_obj NOT IN ('PLAN','SUBMITPLAN') THEN
5224 check_sua_exists( p_person_id,
5225 p_course_cd,
5226 p_load_cal_type,
5227 p_load_sequence_number,
5228 p_uoo_ids,
5229 p_message);
5230 IF p_message IS NOT NULL THEN
5231 p_return_status := 'FALSE';
5232 p_deny_warn := 'D';
5233 igs_en_add_units_api.g_ss_session_id := NULL;
5234 RETURN;
5235 END IF;
5236 END IF; -- end of IF p_calling_obj IS NOT IN ('PLAN','SUBMITPLAN') THEN
5237 -- get the person type
5238 g_person_type := get_person_type(p_course_cd);
5239
5243 p_ret_status => l_return_status
5240 igs_en_gen_017.enrp_get_enr_method(
5241 p_enr_method_type => l_enr_meth_type,
5242 p_error_message => l_message_name,
5244 );
5245
5246 IF l_return_status = 'FALSE' OR l_message_name IS NOT NULL THEN
5247
5248 p_message := l_message_name;
5249 p_return_status := 'FALSE';
5250 p_deny_warn := 'D';
5251 igs_en_add_units_api.g_ss_session_id := NULL;
5252 RETURN;
5253
5254 END IF ;
5255
5256 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
5257 p_cal_type => p_load_cal_type,
5258 p_ci_sequence_number => p_load_sequence_number,
5259 p_acad_cal_type => l_acad_cal_type,
5260 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
5261 p_acad_ci_start_dt => l_acad_start_dt,
5262 p_acad_ci_end_dt => l_acad_end_dt,
5263 p_message_name => l_message_name );
5264
5265 IF l_message_name IS NOT NULL THEN
5266
5267 p_message := l_message_name;
5268 p_return_status := 'FALSE';
5269 p_deny_warn := 'D';
5270 igs_en_add_units_api.g_ss_session_id := NULL;
5271 RETURN;
5272
5273 END IF;
5274
5275 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat(
5276 p_person_id => p_person_id,
5277 p_course_cd => p_course_cd ,
5278 p_cal_type => l_acad_cal_type ,
5279 p_ci_sequence_number => l_acad_ci_sequence_number,
5280 p_session_enrolment_cat => NULL,
5281 p_enrol_cal_type => l_enr_cal_type,
5282 p_enrol_ci_sequence_number => l_enr_ci_seq,
5283 p_commencement_type => l_enr_comm,
5284 p_enr_categories => l_enr_categories
5285 );
5286
5287
5288 IF p_validate_person_step = 'Y' THEN
5289 -- delete all messages for person program,unit steps in warnings table for the person
5290 l_steps := 'PERSON'',''PROGRAM'',''UNIT';
5291 delete_ss_warnings(p_person_id,
5292 p_course_cd,
5293 p_load_cal_type,
5294 p_load_sequence_number,
5295 NULL, -- uoo_id
5296 NULL, -- message_for
5297 l_steps); -- steps to be considered while delete warnings records agsinst the person context
5298
5299 l_message_name := NULL;
5300 IF NOT IGS_EN_ELGBL_PERSON.eval_person_steps(
5301 p_person_id => p_person_id,
5302 p_person_type => g_person_type,
5303 p_load_calendar_type => p_load_cal_type,
5304 p_load_cal_sequence_number => p_load_sequence_number,
5305 p_program_cd => p_course_cd,
5306 p_program_version => p_course_version,
5307 p_enrollment_category => l_enr_cat,
5308 p_comm_type => l_enr_comm,
5309 p_enrl_method => l_enr_meth_type,
5310 p_message => l_message_name,
5311 p_deny_warn => l_deny_warn,
5312 p_calling_obj => p_calling_obj,
5313 p_create_warning => 'Y') THEN
5314 IF l_message_name IS NOT NULL THEN
5315 p_message := l_message_name;
5316 p_return_status := 'FALSE';
5317 p_deny_warn := 'D';
5318 igs_en_add_units_api.g_ss_session_id := NULL;
5319 RETURN;
5320 END IF;
5321
5322 END IF;
5323
5324 ELSE
5325 -- purge unit and program level messages from warnings table
5326 l_steps := 'PROGRAM'',''UNIT';
5327 delete_ss_warnings(p_person_id,
5328 p_course_cd,
5329 p_load_cal_type,
5330 p_load_sequence_number,
5331 NULL, -- uoo_id
5332 NULL, -- message_for
5333 l_steps); -- -- steps to be considered while delete warnings records agsinst the person context
5334
5335 END IF;
5336
5337
5338 -- . Call routine to decode and reorder the uoo_ids
5339 reorder_uoo_ids(p_person_id,
5340 p_course_cd,
5341 p_uoo_ids,
5342 p_load_cal_type,
5343 p_load_sequence_number,
5344 p_calling_obj,
5345 t_reorder_unit_params);
5346
5347 -- Call routine to get the permission status and waitlist status for each of the above units.
5348 l_return_status := NULL;
5349 get_perm_wlst_setup(p_person_id,
5350 p_course_cd,
5351 p_load_cal_type,
5352 p_load_sequence_number,
5353 t_reorder_unit_params,
5354 p_message,
5355 l_return_status,
5356 'Y'); --chk wailist setup also
5360 igs_en_add_units_api.g_ss_session_id := NULL;
5357 IF l_return_status = 'FALSE' THEN
5358 p_return_status := 'FALSE';
5359 p_deny_warn := 'D';
5361 RETURN;
5362 END IF;
5363
5364 IF p_calling_obj = 'PLAN' THEN
5365
5366 SAVEPOINT sp_plan_sua;
5367 l_return_status := NULL;
5368 IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop := 'PLAN';
5369 create_planned_sua( p_person_id,
5370 p_course_cd,
5371 p_course_version,
5372 p_load_cal_type,
5373 p_load_sequence_number,
5374 t_reorder_unit_params,
5375 l_enr_cat,
5376 l_enr_meth_type,
5377 l_enr_comm,
5378 p_message,
5379 l_return_status,
5380 p_calling_obj);
5381 IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop := NULL;
5382 ROLLBACK TO sp_plan_sua; -- needs to be rollbacked as this is just for making validations
5383
5384 IF l_return_status = 'FALSE' THEN
5385 p_return_status := 'FALSE';
5386 p_deny_warn := 'D';
5387 igs_en_add_units_api.g_ss_session_id := NULL;
5388 RETURN;
5389 END IF;
5390
5391 ELSE
5392
5393 -- Cart Max rule is not evaluated for swap and submitswap and submitcart, ENROLPEND
5394
5395 IF p_calling_obj NOT IN ('SWAP', 'SUBMITSWAP','SUBMITCART','ENROLPEND') THEN
5396
5397 IF NOT eval_cart_max(
5398 p_person_id,
5399 l_enr_cat,
5400 l_enr_meth_type,
5401 l_enr_comm,
5402 p_course_cd,
5403 p_course_version,
5404 p_load_cal_type,
5405 p_load_sequence_number,
5406 t_reorder_unit_params,
5407 p_message,
5408 p_calling_obj) THEN
5409
5410 p_return_status := 'FALSE';
5411 p_deny_warn := 'D';
5412 ROLLBACK TO sp_add_units_api;
5413 igs_en_add_units_api.g_ss_session_id := NULL;
5414 RETURN;
5415
5416 END IF; -- IF NOT eval_cart_max(
5417
5418 END IF; -- IF p_calling_obj NOT IN ('SWAP', 'SUBMITSWAP','SUBMITCART','ENROLPEND') THEN
5419
5420 SAVEPOINT sp_unconfirm_sua;
5421 l_return_status := NULL;
5422 create_unconfirm_sua(
5423 p_person_id,
5424 p_course_cd,
5425 p_course_version,
5426 p_load_cal_type,
5427 p_load_sequence_number,
5428 t_reorder_unit_params,
5429 ','||p_uoo_ids||',',
5430 l_enr_cat,
5431 l_enr_meth_type,
5432 l_enr_comm,
5433 l_unconfirm_suas,
5434 p_message,
5435 l_return_status,
5436 p_calling_obj);
5437
5438 IF l_return_status = 'FALSE' THEN
5439 p_return_status := 'FALSE';
5440 p_deny_warn := 'D';
5441 ROLLBACK TO sp_unconfirm_sua;
5442 igs_en_add_units_api.g_ss_session_id := NULL;
5443 RETURN;
5444 END IF;
5445
5446
5447 SAVEPOINT sp_enroll_sua;
5448
5449 -- create waitlisted units after sp_enroll_sua so that
5450 -- they get rolled back once we roll back to this savepoint
5451 -- but unconfirm unit attempts doesnot get rolled back
5452 IF p_calling_obj = 'SUBMITPLAN' THEN
5453
5454 FOR i IN 1.. t_reorder_unit_params.COUNT
5455
5456 LOOP
5457
5458 IF t_reorder_unit_params(i).wlst_step = 'Y' AND
5459 t_reorder_unit_params(i).create_wlst = 'Y' THEN
5460
5461 l_message_name := NULL;
5462 l_return_status := NULL;
5463 create_sua_from_plan( p_person_id,
5464 p_course_cd,
5465 t_reorder_unit_params(i).uoo_id,
5466 p_load_cal_type,
5467 p_load_sequence_number,
5468 t_reorder_unit_params(i).ass_ind,
5469 'Y', --- pass waitlsit ind as 'Y'
5470 l_enr_meth_type,
5471 t_reorder_unit_params(i).override_enrolled_cp,
5472 t_reorder_unit_params(i).grading_schema_cd,
5473 t_reorder_unit_params(i).gs_version_number,
5474 p_calling_obj,
5475 l_message_name,
5476 l_return_status) ;
5477
5478 IF l_return_status = 'FALSE' AND l_message_name IS NOT NULL THEN
5479 p_return_status := 'FALSE';
5480 p_message := l_message_name;
5481 igs_en_add_units_api.g_ss_session_id := NULL;
5482 RETURN;
5483 END IF;
5484
5485 -- create warning record if waitlisted unit has been created succesfully.
5486 igs_en_drop_units_api.create_ss_warning (
5487 p_person_id => p_person_id,
5488 p_course_cd => p_course_cd,
5489 p_term_cal_type => p_load_cal_type,
5490 p_term_ci_sequence_number => p_load_sequence_number,
5494 p_message_name => 'IGS_EN_WLST_AVAIL', -- need to check message_name
5491 p_uoo_id => t_reorder_unit_params(i).uoo_id,
5492 p_message_for => get_unit_sec(t_reorder_unit_params(i).uoo_id),
5493 p_message_icon => 'W',
5495 p_message_rule_text => NULL,
5496 p_message_tokens => NULL,
5497 p_message_action => NULL,
5498 p_destination => NULL, -- p_destination
5499 p_parameters => NULL, -- p_parameters
5500 p_step_type => 'UNIT');
5501
5502
5503 END IF;
5504
5505 END LOOP;
5506
5507 END IF; -- IF p_calling_obj = 'SUBMITPLAN'
5508
5509 -- call routine to create enrolled unit attempt.
5510 l_return_status := NULL;
5511 create_enroll_sua(
5512 p_person_id,
5513 p_course_cd,
5514 p_course_version,
5515 l_unconfirm_suas,
5516 p_load_cal_type,
5517 p_load_sequence_number,
5518 l_enr_cat,
5519 l_enr_meth_type,
5520 l_enr_comm,
5521 p_message,
5522 l_return_status,
5523 p_calling_obj);
5524
5525 -- p_return_status has the status of the creation of enrolled unit attempts.
5526 -- If this is false then it implies that the API cannot proceed any further. Return to the calling page.
5527
5528 update_warnings_table(p_person_id,
5529 p_course_cd ,
5530 p_load_cal_type,
5531 p_load_sequence_number,
5532 p_calling_obj);
5533
5534 p_deny_warn := set_deny_warn(p_person_id,
5535 p_course_cd,
5536 p_load_cal_type,
5537 p_load_sequence_number);
5538
5539 -- to update the deny records of those units not in sua table and planning sheet
5540 -- (planning shhet error records if calling object is not plan) 'E'
5541
5542 IF l_return_status = 'FALSE' THEN
5543 p_return_status := 'FALSE';
5544 p_deny_warn := 'D';
5545 ROLLBACK TO sp_unconfirm_Sua;
5546 igs_en_add_units_api.g_ss_session_id := NULL;
5547 RETURN;
5548 END IF;
5549
5550 -- Incase of cart and swap rollback till sp_unconfirm_sua
5551 -- so that all the waitlisted units will be rolled back
5552 -- and enrolled units will be rolled bcak to unconfirm units ( as unocnfirming units is done
5553 -- in an autonomus transaction for cart and swap)
5554 IF p_calling_obj IN ( 'CART', 'SWAP') THEN
5555
5556 ROLLBACK TO sp_unconfirm_sua;
5557
5558 ELSIF p_calling_obj = 'SUBMITPLAN' THEN
5559
5560 -- in this case if any deny record exists then
5561 -- all the unts are rolled back even uncofirm units as in case
5562 -- of submit plan units are unconfirmed in a normal transaction
5563 IF p_deny_warn='D' THEN
5564
5565 ROLLBACK TO sp_unconfirm_sua;
5566 -- if no deny records exists then enrolled units are rolled back to unocnfirm status
5567 -- waitlisted records created are rolled back
5568 ELSE
5569 ROLLBACK TO sp_enroll_sua;
5570 END IF;
5571
5572 ELSIF p_calling_obj IN ('SUBMITCART','SUBMITSWAP','SCHEDULE','ENROLPEND') THEN
5573
5574 IF p_deny_warn = 'D' OR
5575 (p_calling_obj IN ('SCHEDULE','ENROLPEND') AND p_deny_warn = 'W') THEN
5576 ROLLBACK TO sp_unconfirm_sua;
5577 ELSE
5578 -- added by ckasu as a part of bug#4674099 inorder to delete the cart error
5579 -- record present in Planning sheet table when p_calling object is 'SUBMITCART',
5580 -- or 'SUBMITSWAP'
5581 delete_cart_error_records(p_person_id,
5582 p_course_cd,
5583 p_load_cal_type,
5584 p_load_sequence_number);
5585 END IF;
5586
5587 END IF; -- IF p_calling_obj IN ('SUBMITPLAN', 'CART', 'SWAP')
5588
5589
5590 END IF; -- IF p_calling_obj = 'PLAN' THEN
5591
5592 -- to update the deny records of those units not in sua table and planning sheet
5593 -- (planning sheet error records if calling object is not plan) 'E'
5594 update_warnings_table(p_person_id,
5595 p_course_cd ,
5596 p_load_cal_type,
5597 p_load_sequence_number,
5598 p_calling_obj);
5599 -- p_return_status has the status of the creation of enrolled unit attempts.
5600 -- If this is false then it implies that the API cannot proceed any further. Return to the calling page.
5601 p_deny_warn := set_deny_warn(p_person_id,
5602 p_course_cd,
5603 p_load_cal_type,
5604 p_load_sequence_number);
5605
5606 igs_en_add_units_api.g_ss_session_id := NULL;
5607 EXCEPTION
5608
5609 WHEN OTHERS THEN
5610 igs_en_add_units_api.g_ss_session_id := NULL;
5611 IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop := NULL;
5612 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
5613 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.add_selected_units :',SQLERRM);
5614 END IF;
5615 ROLLBACK TO sp_add_units_api;
5616 p_deny_warn := 'D';
5617 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
5618 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.add_selected_units');
5619 IGS_GE_MSG_STACK.ADD;
5620 RAISE;
5621 END add_selected_units;
5622
5623 PROCEDURE delete_unrelated_warnings(p_person_id IN VARCHAR2,
5624 p_course_cd IN VARCHAR2,
5625 p_load_cal_type IN VARCHAR2,
5626 p_load_sequence_number IN VARCHAR2,
5627 p_delete_message_count OUT NOCOPY NUMBER
5628 ) AS
5629 PRAGMA AUTONOMOUS_TRANSACTION;
5630 CURSOR c_get_warnings IS
5631 SELECT ROWID
5632 FROM IGS_EN_STD_WARNINGS
5633 WHERE person_id = p_person_id
5634 AND course_cd = p_course_cd
5635 AND term_cal_type = p_load_cal_type
5636 AND term_ci_sequence_number = p_load_sequence_number
5637 AND step_type <> 'PERSON';
5638 BEGIN
5639 p_delete_message_count := 0;
5640 FOR c_get_warnings_rec IN c_get_warnings LOOP
5641 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => c_get_warnings_rec.ROWID);
5642 p_delete_message_count := p_delete_message_count + 1;
5643 END LOOP;
5644
5645 COMMIT;
5646 EXCEPTION
5647 WHEN OTHERS THEN
5648 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
5649 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.delete_unrelated_warnings :',SQLERRM);
5650 END IF;
5651 ROLLBACK;
5652 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
5653 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_ADD_UNITS_API.delete_unrelated_warnings');
5654 IGS_GE_MSG_STACK.ADD;
5655 RAISE;
5656 END delete_unrelated_warnings;
5657
5658 END IGS_EN_ADD_UNITS_API;