1 PACKAGE BODY igs_ru_gen_001 AS
2 /* $Header: IGSRU01B.pls 120.7 2006/04/28 05:48:32 sepalani ship $ */
3 ------------------------------------------------------------------
4 --Known limitations/enhancements and/or remarks:
5 --
6 --Change History:
7 --Who When What
8 --smadathi 07-JUN-2001 The functions ref_set, perid_chk, plc_chk,stg_set added . The length of
9 -- some of the variables were changed . The changes are as per enhancement bug No. 1775394
10 --nalkumar 18-JUN-2001 The function rupl_get_alwd_cp added. The changes are as per enhancement bug No. 1830175
11 --Prajeesh 21-apr-2002 In Log error Instead of directly raising
12 -- and exception put it in when others
13 -- rghosh 3-Jan-2002 bug# 2507447 :Removed the reference to|
14 -- dual
15 -------------------------------------------------------------------
16 /*************************************************************
17 Created By : nsinha
18 Date Created By :
19 Purpose :
20 Know limitations, enhancements or remarks
21 Change History
22 Who When What
23 stutta 11-Apr-2006 Changed expand_uoo to create one pl/sql table record for a unit, version instance(only
24 for PREREQ, COREQ rules). Added fnd_logging when rules errors out with an exception. bug#5154191
25 bdeviset 13-DEC-2005 Modified the cursor in function student to exclude the unit which the prereq or coreq is set if it not null.
26 Bug# 4304688
27 bdeviset 05-OCT-2005 Modified functions adv_attribute,adv_relative_grade,sua_credit_points,asul_attribute
28 advanced_standing and advanced_stadning_unit_level for bug# 4480311
29 bannamal 08-Jul-2005 Enh#3392088. added new turing functions.
30 smaddali 11-may-05 Modified for bug#3723860. added new turin functions
31 smaddali 11-apr-05 Modified for bug#4283221. Advance standing functionality has been modified
32 stutta 15-Jul-2004 Build # 3381339; Modified sua_attribute and added p_final_ind as a new parameter.
33 Added handling for turning functions 'finlresult' and 'finalgrade'.
34 ijeddy 03-Nov-2003 Build# 3181938; Modified this object as per Summary Measurement Of Attainment FD.
35 knaraset 28-May-2003 Modified the functions/procedure/record group for MUS build bug 2829262
36 smvk 28-Aug-2002 Removed the existing code in the function get_fdfr_rule and it is modified to return NULL always.
37 as per SFCR005_Cleanup_Build TD (Enhancement Bug # 2531390). Removed Default values of the parameters
38 in the function rulp_val_senna to avoid gscc warning 'File.Pkg.22'
39 smanglm 10-05-2002 modified expand_gsch_set to segrgate grading schema and grad as both are stored together in unit_cd of igs_ru_set_member as per fix of bug 2287084
40 smanglm 19-Apr-2002 Modified the cursor cur_gscd_set_members in the function
41 expand_gsch_set to include DISTINCT in select query as the
42 rule got modified as per bug 2287084
43 svenkata 12-Dec-2001 Added prgp_cal_cp(), pr_rul_msg() Functions. Modified turing() Function
44 w.r.t Progression Rules Enhancement DLD, Bug No: 2146547.
45 rbezawad 12-Dec-2001 Added prgpl_chk_gsch_exists(), prgp_cal_cp_gsch(), expand_gsch_set() Functions.
46 Modified expand_set() Function w.r.t Progression Rules Enhancement DLD, Bug No: 2146547.
47 smadathi 07-JUN-2001 The functions ref_set, perid_chk, plc_chk,stg_set added . The length of
48 some of the variables were changed . The changes are as per enhancement bug No. 1775394
49 nalkumar 18-JUN-2001 The function rupl_get_alwd_cp added. The changes are as per enhancement bug No. 1830175
50 Navin 27-Aug-2001 Added a package variable igs_ru_gen_001.p_evaluated_part
51 Created a turing function 'ifthen' and modified the logic of
52 Rulp_Val_Senna as per the requirement of Bug# : 1899513.
53 (reverse chronological order - newest change first)
54 ***************************************************************/
55
56 g_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
57
58 Function Rulp_Val_Senna(
59 p_rule_call_name IN VARCHAR2,
60 p_person_id IN NUMBER,
61 p_course_cd IN VARCHAR2,
62 p_course_version IN NUMBER,
63 p_unit_cd IN VARCHAR2,
64 p_unit_version IN NUMBER,
65 p_cal_type IN VARCHAR2,
66 p_ci_sequence_number IN NUMBER,
67 p_message OUT NOCOPY VARCHAR2,
68 p_rule_number NUMBER,
69 p_param_1 VARCHAR2,
70 p_param_2 VARCHAR2,
71 p_param_3 VARCHAR2,
72 p_param_4 VARCHAR2,
73 p_param_5 VARCHAR2,
74 p_param_6 VARCHAR2,
75 p_param_7 VARCHAR2,
76 p_param_8 VARCHAR2, -- This parameter is used for passing uoo_id for prereq and coreq rules (eval_prereq and eval_coreq functions in igs_en_elgbl_unit)
77 p_param_9 VARCHAR2,
78 p_param_10 VARCHAR2,
79 p_param_11 VARCHAR2,
80 p_param_12 VARCHAR2,
81 p_param_13 VARCHAR2,
82 p_param_14 VARCHAR2,
83 p_param_15 VARCHAR2,
84 p_param_16 VARCHAR2,
85 p_param_17 VARCHAR2,
86 p_param_18 VARCHAR2,
87 p_param_19 VARCHAR2,
88 p_param_20 VARCHAR2,
89 p_param_21 VARCHAR2,
90 p_param_22 VARCHAR2,
91 p_param_23 VARCHAR2,
92 p_param_24 VARCHAR2,
93 p_param_25 VARCHAR2,
94 p_param_26 VARCHAR2,
95 p_param_27 VARCHAR2,
96 p_param_28 VARCHAR2,
97 p_param_29 VARCHAR2,
98 p_param_30 VARCHAR2,
99 p_param_31 VARCHAR2,
100 p_param_32 VARCHAR2,
101 p_param_33 VARCHAR2,
102 p_param_34 VARCHAR2,
103 p_param_35 VARCHAR2,
104 p_param_36 VARCHAR2,
105 p_param_37 VARCHAR2,
106 p_param_38 VARCHAR2,
107 p_param_39 VARCHAR2,
108 p_param_40 VARCHAR2,
109 p_param_41 VARCHAR2,
110 p_param_42 VARCHAR2,
111 p_param_43 VARCHAR2,
112 p_param_44 VARCHAR2,
113 p_param_45 VARCHAR2,
114 p_param_46 VARCHAR2,
115 p_param_47 VARCHAR2,
116 p_param_48 VARCHAR2,
117 p_param_49 VARCHAR2,
118 p_param_50 VARCHAR2,
119 p_param_51 VARCHAR2,
120 p_param_52 VARCHAR2,
121 p_param_53 VARCHAR2,
122 p_param_54 VARCHAR2,
123 p_param_55 VARCHAR2,
124 p_param_56 VARCHAR2,
125 p_param_57 VARCHAR2,
126 p_param_58 VARCHAR2,
127 p_param_59 VARCHAR2,
128 p_param_60 VARCHAR2,
129 p_param_61 VARCHAR2,
130 p_param_62 VARCHAR2,
131 p_param_63 VARCHAR2,
132 p_param_64 VARCHAR2,
133 p_param_65 VARCHAR2,
134 p_param_66 VARCHAR2,
135 p_param_67 VARCHAR2,
136 p_param_68 VARCHAR2,
137 p_param_69 VARCHAR2,
138 p_param_70 VARCHAR2,
139 p_param_71 VARCHAR2,
140 p_param_72 VARCHAR2,
141 p_param_73 VARCHAR2,
142 p_param_74 VARCHAR2,
143 p_param_75 VARCHAR2,
144 p_param_76 VARCHAR2,
145 p_param_77 VARCHAR2,
146 p_param_78 VARCHAR2,
147 p_param_79 VARCHAR2,
148 p_param_80 VARCHAR2,
149 p_param_81 VARCHAR2,
150 p_param_82 VARCHAR2,
151 p_param_83 VARCHAR2,
152 p_param_84 VARCHAR2,
153 p_param_85 VARCHAR2,
154 p_param_86 VARCHAR2,
155 p_param_87 VARCHAR2,
156 p_param_88 VARCHAR2,
157 p_param_89 VARCHAR2,
158 p_param_90 VARCHAR2,
159 p_param_91 VARCHAR2,
160 p_param_92 VARCHAR2,
161 p_param_93 VARCHAR2,
162 p_param_94 VARCHAR2,
163 p_param_95 VARCHAR2,
164 p_param_96 VARCHAR2,
165 p_param_97 VARCHAR2,
166 p_param_98 VARCHAR2,
167 p_param_99 VARCHAR2 )
168 RETURN VARCHAR2 IS
169 ------------------------------------------------------------------
170 --Known limitations/enhancements and/or remarks:
171 --
172 --Change History:
173 --Who When What
174 --smvk 28-AUG-2002 Removed the default value of parameters to avoid gscc warning 'File.Pkg.22'
175 --svenkata 12-Dec-2001 Added Global variable g_grad_sch_set_no w.r.t Progression Rules Enhancement DLD, Bug No: 2146547
176 --smadathi 07-JUN-2001 Length of the variable r_member.f1 changed to varchar2(30) from varchar2(10) .
177 -- Length of the variable r_tds.unit_cd changed to varchar2(30) from varchar2(10) .
178 -------------------------------------------------------------------
179
180 /*
181 RECORDS
182 Set member record
183 */
184
185 TYPE r_member IS RECORD (
186 f1 VARCHAR2(30),
187 f2 VARCHAR2(10),
188 f3 VARCHAR2(10),
189 f4 VARCHAR2(10),
190 next BINARY_INTEGER,
191 f5 VARCHAR2(10)
192 );
193 /*
194 Set record
195 */
196 TYPE r_set IS RECORD (
197 first BINARY_INTEGER,
198 last_insert BINARY_INTEGER
199 );
200
201 /*
202 turing data structure (data that turing may require)
203 this data is used in subsequent turing calls
204 mainly used by the set looping functions (select_set,sum_func,for_expand)
205 */
206
207 TYPE r_tds IS RECORD (
208 set_number BINARY_INTEGER,
209 member_index BINARY_INTEGER, /* added 31/08/99 */
210 unit_cd VARCHAR2(30),
211 unit_version VARCHAR2(10),
212 cal_type VARCHAR2(10),
213 ci_sequence_number VARCHAR2(10),
214 rule_outcome IGS_RU_ITEM.value%TYPE,
215 uoo_id VARCHAR2(10)
216 );
217
218 /*
219 STRUCTURES
220 */
221 TYPE t_member IS TABLE OF
222 r_member
223 INDEX BY BINARY_INTEGER;
224 TYPE t_set IS TABLE OF
225 r_set
226 INDEX BY BINARY_INTEGER;
227 TYPE t_stack IS TABLE OF
228 IGS_RU_ITEM.value%TYPE
229 INDEX BY BINARY_INTEGER;
230
231
232 /*
233 USER EXCEPTIONS
234 */
235
236 rule_error EXCEPTION;
237 /*
238 CONSTANTS
239 */
240 cst_space CONSTANT VARCHAR2(1) := Fnd_Global.Local_Chr(31);
241 /*
242 GLOBALS
243 for debuging recursive code
244 */
245 gv_turing_level NUMBER(3);
246
247 /*
248 GLOBAL Variable for getting the value of the Grading schema .
249 Will be used when a call is made to the function p_grad_sch_set_no in the function pr_rul_msg.
250 This is added w.r.t Progression Rules Enhancement DLD, Bug No: 2146547
251 */
252 g_grad_sch_set_no NUMBER;
253
254 /*
255 WORK SETS
256 instance table of sets
257 */
258 gv_set t_set;
259 gv_set_index BINARY_INTEGER;
260 /*
261 instance table of set members
262 */
263 gv_member t_member;
264 gv_member_index BINARY_INTEGER;
265 /*
266 turing engine stack
267 */
268 gt_rule_stack t_stack;
269 gv_stack_index BINARY_INTEGER;
270
271 /*
272 message stack
273 */
274 gt_empty_message_stack t_stack;
275 gt_message_stack t_stack;
276 gv_message_stack_index BINARY_INTEGER;
277 /*
278 rule to message rule variable(s) table
279 */
280 gt_variable t_stack;
281 /*
282 student sets, these are only gotten once per senna call
283 */
284 gv_sua_set BINARY_INTEGER := NULL;
285 gv_asu_set BINARY_INTEGER := NULL;
286 gv_asul_set BINARY_INTEGER := NULL;
287 gv_susa_set BINARY_INTEGER := NULL;
288
289 -- Variable to hold set of grades obtained by student.
290 gv_grade_set BINARY_INTEGER := NULL;
291
292 /*
293 variables to capture the param values for subsequent calls to function turing
294 */
295
296 -- TYPE t_params_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
297 --t_params t_params_table;
298 v_index BINARY_INTEGER;
299
300 /*
301 FORWARD REFERENCE DECLARATIONS
302 */
303 FUNCTION turing (
304 p_rule_number IN NUMBER,
305 p_tds IN r_tds
306 ) RETURN VARCHAR2;
307 /*
308 MISCELLANEOUS FUNCTIONS
309 display all non null parameters
310 */
311 PROCEDURE log_error(
312 p_function_name IN VARCHAR2,
313 p_message IN VARCHAR2
314 ) IS
315 v_error_text VARCHAR2(2000);
316 BEGIN
317 IF p_rule_call_name IS NOT NULL
318 THEN
319 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_rule_call_name=>'||p_rule_call_name;
320 END IF;
321 IF p_rule_number IS NOT NULL
322 THEN
323 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_rule_number=>'||p_rule_number;
324 END IF;
325 IF p_person_id IS NOT NULL
326 THEN
327 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_person_id=>'||p_person_id;
328 END IF;
329 IF p_course_cd IS NOT NULL
330 THEN
331 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_course_cde=>'||p_course_cd;
332 END IF;
333 IF p_course_version IS NOT NULL
334 THEN
335 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_course_version=>'||p_course_version;
336 END IF;
337 IF p_unit_cd IS NOT NULL
338 THEN
339 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_unit_cd=>'||p_unit_cd;
340 END IF;
341 IF p_unit_version IS NOT NULL
342 THEN
343 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_unit_version=>'||p_unit_version;
344 END IF;
345 IF p_cal_type IS NOT NULL
346 THEN
347 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_cal_type=>'||p_cal_type;
348 END IF;
349 IF p_ci_sequence_number IS NOT NULL
350 THEN
351 v_error_text := v_error_text||Fnd_Global.Local_Chr(10)||'p_ci_sequence_number=>'||
352 p_ci_sequence_number;
353 END IF;
354 IF p_param_1 IS NOT NULL
355 THEN
356 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_1=>'''||p_param_1||'''';
357 END IF;
358 IF p_param_2 IS NOT NULL
359 THEN
360 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_2=>'''||p_param_2||'''';
361 END IF;
362 IF p_param_3 IS NOT NULL
363 THEN
364 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_3=>'''||p_param_3||'''';
365 END IF;
366 IF p_param_4 IS NOT NULL
367 THEN
368 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_4=>'''||p_param_4||'''';
369 END IF;
370 IF p_param_5 IS NOT NULL
371 THEN
372 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_5=>'''||p_param_5||'''';
373 END IF;
374 IF p_param_6 IS NOT NULL
375 THEN
376 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_6=>'''||p_param_6||'''';
377 END IF;
378 IF p_param_7 IS NOT NULL
379 THEN
380 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_7=>'''||p_param_7||'''';
381 END IF;
382 IF p_param_8 IS NOT NULL
383 THEN
384 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_8=>'''||p_param_8||'''';
385 END IF;
386 IF p_param_9 IS NOT NULL
387 THEN
388 v_error_text := v_error_text||','||Fnd_Global.Local_Chr(10)||'p_param_9=>'''||p_param_9||'''';
389 END IF;
390 /* more room 99/06/25
391 IF p_param_10 IS NOT NULL
392 THEN
393 v_error_text := v_error_text||
394 ','||Fnd_Global.Local_Chr(10)||'p_param_10=>'''||p_param_10||'''';
395 END IF;
396 IF p_param_11 IS NOT NULL
397 THEN
398 v_error_text := v_error_text||
399 ','||Fnd_Global.Local_Chr(10)||'p_param_11=>'''||p_param_11||'''';
400 END IF;
401 IF p_param_12 IS NOT NULL
402 THEN
403 v_error_text := v_error_text||
404 ','||Fnd_Global.Local_Chr(10)||'p_param_12=>'''||p_param_12||'''';
405 END IF;
406 IF p_param_13 IS NOT NULL
407 THEN
408 v_error_text := v_error_text||
409 ','||Fnd_Global.Local_Chr(10)||'p_param_13=>'''||p_param_13||'''';
410 END IF;
411 IF p_param_14 IS NOT NULL
412 THEN
413 v_error_text := v_error_text||
414 ','||Fnd_Global.Local_Chr(10)||'p_param_14=>'''||p_param_14||'''';
415 END IF;
416 IF p_param_15 IS NOT NULL
417 THEN
418 v_error_text := v_error_text||
419 ','||Fnd_Global.Local_Chr(10)||'p_param_15=>'''||p_param_15||'''';
420 END IF;
421 IF p_param_16 IS NOT NULL
422 THEN
423 v_error_text := v_error_text||
424 ','||Fnd_Global.Local_Chr(10)||'p_param_16=>'''||p_param_16||'''';
425 END IF;
426 IF p_param_17 IS NOT NULL
427 THEN
428 v_error_text := v_error_text||
429 ','||Fnd_Global.Local_Chr(10)||'p_param_17=>'''||p_param_17||'''';
430 END IF;
431 IF p_param_18 IS NOT NULL
432 THEN
433 v_error_text := v_error_text||
434 ','||Fnd_Global.Local_Chr(10)||'p_param_18=>'''||p_param_18||'''';
435 END IF;
436 IF p_param_19 IS NOT NULL
437 THEN
438 v_error_text := v_error_text||
439 ','||Fnd_Global.Local_Chr(10)||'p_param_19=>'''||p_param_19||'''';
440 END IF;
441 /* need room hrt
442 IF p_param_20 IS NOT NULL
443 THEN
444 v_error_text := v_error_text||
445 ','||Fnd_Global.Local_Chr(10)||'p_param_20=>'''||p_param_20||'''';
446 END IF;
447 IF p_param_21 IS NOT NULL
448 THEN
449 v_error_text := v_error_text||
450 ','||Fnd_Global.Local_Chr(10)||'p_param_21=>'''||p_param_21||'''';
451 END IF;
452 IF p_param_22 IS NOT NULL
453 THEN
454 v_error_text := v_error_text||
455 ','||Fnd_Global.Local_Chr(10)||'p_param_22=>'''||p_param_22||'''';
456 END IF;
457 IF p_param_23 IS NOT NULL
458 THEN
459 v_error_text := v_error_text||
460 ','||Fnd_Global.Local_Chr(10)||'p_param_23=>'''||p_param_23||'''';
461 END IF;
462 IF p_param_24 IS NOT NULL
463 THEN
464 v_error_text := v_error_text||
465 ','||Fnd_Global.Local_Chr(10)||'p_param_24=>'''||p_param_24||'''';
466 END IF;
467 IF p_param_25 IS NOT NULL
468 THEN
469 v_error_text := v_error_text||
470 ','||Fnd_Global.Local_Chr(10)||'p_param_25=>'''||p_param_25||'''';
471 END IF;
472 IF p_param_26 IS NOT NULL
473 THEN
474 v_error_text := v_error_text||
475 ','||Fnd_Global.Local_Chr(10)||'p_param_26=>'''||p_param_26||'''';
476 END IF;
477 IF p_param_27 IS NOT NULL
478 THEN
479 v_error_text := v_error_text||
480 ','||Fnd_Global.Local_Chr(10)||'p_param_27=>'''||p_param_27||'''';
481 END IF;
482 IF p_param_28 IS NOT NULL
483 THEN
484 v_error_text := v_error_text||
485 ','||Fnd_Global.Local_Chr(10)||'p_param_28=>'''||p_param_28||'''';
486 END IF;
487 IF p_param_29 IS NOT NULL
488 THEN
489 v_error_text := v_error_text||
490 ','||Fnd_Global.Local_Chr(10)||'p_param_29=>'''||p_param_29||'''';
491 END IF;
492 IF p_param_30 IS NOT NULL
493 THEN
494 v_error_text := v_error_text||
495 ','||Fnd_Global.Local_Chr(10)||'p_param_30=>'''||p_param_30||'''';
496 END IF;
497 IF p_param_31 IS NOT NULL
498 THEN
499 v_error_text := v_error_text||
500 ','||Fnd_Global.Local_Chr(10)||'p_param_31=>'''||p_param_31||'''';
501 END IF;
502 IF p_param_32 IS NOT NULL
503 THEN
504 v_error_text := v_error_text||
505 ','||Fnd_Global.Local_Chr(10)||'p_param_32=>'''||p_param_32||'''';
506 END IF;
507 IF p_param_33 IS NOT NULL
508 THEN
509 v_error_text := v_error_text||
510 ','||Fnd_Global.Local_Chr(10)||'p_param_33=>'''||p_param_33||'''';
511 END IF;
512 IF p_param_34 IS NOT NULL
513 THEN
514 v_error_text := v_error_text||
515 ','||Fnd_Global.Local_Chr(10)||'p_param_34=>'''||p_param_34||'''';
516 END IF;
517 IF p_param_35 IS NOT NULL
518 THEN
519 v_error_text := v_error_text||
520 ','||Fnd_Global.Local_Chr(10)||'p_param_35=>'''||p_param_35||'''';
521 END IF;
522 IF p_param_36 IS NOT NULL
523 THEN
524 v_error_text := v_error_text||
525 ','||Fnd_Global.Local_Chr(10)||'p_param_36=>'''||p_param_36||'''';
526 END IF;
527 IF p_param_37 IS NOT NULL
528 THEN
529 v_error_text := v_error_text||
530 ','||Fnd_Global.Local_Chr(10)||'p_param_37=>'''||p_param_37||'''';
531 END IF;
532 IF p_param_38 IS NOT NULL
533 THEN
534 v_error_text := v_error_text||
535 ','||Fnd_Global.Local_Chr(10)||'p_param_38=>'''||p_param_38||'''';
536 END IF;
537 IF p_param_39 IS NOT NULL
538 THEN
539 v_error_text := v_error_text||
540 ','||Fnd_Global.Local_Chr(10)||'p_param_39=>'''||p_param_39||'''';
541 END IF;
542 IF p_param_40 IS NOT NULL
543 THEN
544 v_error_text := v_error_text||
545 ','||Fnd_Global.Local_Chr(10)||'p_param_40=>'''||p_param_40||'''';
546 END IF;
547 IF p_param_41 IS NOT NULL
548 THEN
549 v_error_text := v_error_text||
550 ','||Fnd_Global.Local_Chr(10)||'p_param_41=>'''||p_param_41||'''';
551 END IF;
552 IF p_param_42 IS NOT NULL
553 THEN
554 v_error_text := v_error_text||
555 ','||Fnd_Global.Local_Chr(10)||'p_param_42=>'''||p_param_42||'''';
556 END IF;
557 IF p_param_43 IS NOT NULL
558 THEN
559 v_error_text := v_error_text||
560 ','||Fnd_Global.Local_Chr(10)||'p_param_43=>'''||p_param_43||'''';
561 END IF;
562 IF p_param_44 IS NOT NULL
563 THEN
564 v_error_text := v_error_text||
565 ','||Fnd_Global.Local_Chr(10)||'p_param_44=>'''||p_param_44||'''';
566 END IF;
567 IF p_param_45 IS NOT NULL
568 THEN
569 v_error_text := v_error_text||
570 ','||Fnd_Global.Local_Chr(10)||'p_param_45=>'''||p_param_45||'''';
571 END IF;
572 IF p_param_46 IS NOT NULL
573 THEN
574 v_error_text := v_error_text||
575 ','||Fnd_Global.Local_Chr(10)||'p_param_46=>'''||p_param_46||'''';
576 END IF;
577 IF p_param_47 IS NOT NULL
578 THEN
579 v_error_text := v_error_text||
580 ','||Fnd_Global.Local_Chr(10)||'p_param_47=>'''||p_param_47||'''';
581 END IF;
582 IF p_param_48 IS NOT NULL
583 THEN
584 v_error_text := v_error_text||
585 ','||Fnd_Global.Local_Chr(10)||'p_param_48=>'''||p_param_48||'''';
586 END IF;
587 IF p_param_49 IS NOT NULL
588 THEN
589 v_error_text := v_error_text||
590 ','||Fnd_Global.Local_Chr(10)||'p_param_49=>'''||p_param_49||'''';
591 END IF;
592 IF p_param_50 IS NOT NULL
593 THEN
594 v_error_text := v_error_text||
595 ','||Fnd_Global.Local_Chr(10)||'p_param_50=>'''||p_param_50||'''';
596 END IF;
597 IF p_param_51 IS NOT NULL
598 THEN
599 v_error_text := v_error_text||
600 ','||Fnd_Global.Local_Chr(10)||'p_param_51=>'''||p_param_51||'''';
601 END IF;
602 IF p_param_52 IS NOT NULL
603 THEN
604 v_error_text := v_error_text||
605 ','||Fnd_Global.Local_Chr(10)||'p_param_52=>'''||p_param_52||'''';
606 END IF;
607 IF p_param_53 IS NOT NULL
608 THEN
609 v_error_text := v_error_text||
610 ','||Fnd_Global.Local_Chr(10)||'p_param_53=>'''||p_param_53||'''';
611 END IF;
612 IF p_param_54 IS NOT NULL
613 THEN
614 v_error_text := v_error_text||
615 ','||Fnd_Global.Local_Chr(10)||'p_param_54=>'''||p_param_54||'''';
616 END IF;
617 IF p_param_55 IS NOT NULL
618 THEN
619 v_error_text := v_error_text||
620 ','||Fnd_Global.Local_Chr(10)||'p_param_55=>'''||p_param_55||'''';
621 END IF;
622 IF p_param_56 IS NOT NULL
623 THEN
624 v_error_text := v_error_text||
625 ','||Fnd_Global.Local_Chr(10)||'p_param_56=>'''||p_param_56||'''';
626 END IF;
627 IF p_param_57 IS NOT NULL
628 THEN
629 v_error_text := v_error_text||
630 ','||Fnd_Global.Local_Chr(10)||'p_param_57=>'''||p_param_57||'''';
631 END IF;
632 IF p_param_58 IS NOT NULL
633 THEN
634 v_error_text := v_error_text||
635 ','||Fnd_Global.Local_Chr(10)||'p_param_58=>'''||p_param_58||'''';
636 END IF;
637 IF p_param_59 IS NOT NULL
638 THEN
639 v_error_text := v_error_text||
640 ','||Fnd_Global.Local_Chr(10)||'p_param_59=>'''||p_param_59||'''';
641 END IF;
642 */
643 EXCEPTION
644 WHEN OTHERS THEN
645 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
646 IGS_GE_MSG_STACK.ADD;
647 APP_EXCEPTION.RAISE_EXCEPTION ;
648 END log_error;
649
650 /*
651 convert plsql BOOLEAN to turing BOOLEAN ('true','false')
652 */
653 FUNCTION b_to_t (
654 p_boolean BOOLEAN )
655 RETURN VARCHAR2 IS
656 BEGIN
657 IF p_boolean
658 THEN
659 RETURN 'true';
660 ELSE
661 RETURN 'false';
662 END IF;
663 END b_to_t;
664 /*
665 Get unit rule number
666 If no rule return NULL
667 */
668 FUNCTION get_unit_rule(
669 p_rule_call_name IN VARCHAR2,
670 p_unit_cd IN VARCHAR2,
671 p_version IN NUMBER
672 ) RETURN NUMBER IS
673 v_rule_number NUMBER;
674 BEGIN
675 SELECT rul_sequence_number
676 INTO v_rule_number
677 FROM IGS_PS_UNIT_VER_RU
678 WHERE unit_cd = p_unit_cd
679 AND version_number = p_version
680 AND s_rule_call_cd = p_rule_call_name;
681 RETURN v_rule_number;
682 EXCEPTION
683 WHEN NO_DATA_FOUND THEN
684 RETURN NULL;
685 END get_unit_rule;
686 /*
687 Get course rule number
688 If no rule return NULL
689 */
690 FUNCTION get_course_rule(
691 p_rule_call_name IN VARCHAR2,
692 p_course_cd IN VARCHAR2,
693 p_version IN NUMBER
694 ) RETURN VARCHAR2 IS
695 v_rule_number NUMBER;
696 BEGIN
697 SELECT rul_sequence_number
698 INTO v_rule_number
699 FROM IGS_PS_VER_RU
700 WHERE course_cd = p_course_cd
701 AND version_number = p_version
702 AND s_rule_call_cd = p_rule_call_name;
703 RETURN v_rule_number;
704 EXCEPTION
705 WHEN NO_DATA_FOUND THEN
706 RETURN NULL;
707 END get_course_rule;
708 /*
709 Get unit set rule number
710 If no rule return NULL
711 */
712 FUNCTION get_us_rule(
713 p_rule_call_name IN VARCHAR2,
714 p_unit_set_cd IN VARCHAR2,
715 p_version IN NUMBER
716 ) RETURN VARCHAR2 IS
717 v_rule_number NUMBER;
718 BEGIN
719 SELECT rul_sequence_number
720 INTO v_rule_number
721 FROM IGS_EN_UNIT_SET_RULE
722 WHERE unit_set_cd = p_unit_set_cd
723 AND version_number = p_version
724 AND s_rule_call_cd = p_rule_call_name;
725 RETURN v_rule_number;
726 EXCEPTION
727 WHEN NO_DATA_FOUND THEN
728 RETURN NULL;
729 END get_us_rule;
730 /*
731 Get fee disbursement formula rule number
732 If no rule return NULL
733 History
734 who when what
735 */
736 FUNCTION get_fdfr_rule (
737 p_rule_call_name IN VARCHAR2,
738 p_fee_type IN IGS_FI_F_TYP_CA_INST_ALL.fee_type%TYPE,
739 p_fee_cal_type IN IGS_FI_F_TYP_CA_INST_ALL.fee_cal_type%TYPE,
740 p_fee_ci_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.fee_ci_sequence_number%TYPE,
741 p_formula_number IN NUMBER
742 )
743 /*----------------------------------------------------------------------------
744 || Created By :
745 || Created On :
746 || Purpose :
747 || Known limitations, enhancements or remarks :
748 || Change History :
749 || Who When What
750 || (reverse chronological order - newest change first)
751 || smvk 02-Sep-2002 Modified the IN parameters of type IGS_FI_FEE_DSB_FM_RU as
752 || parameters of type IGS_FI_F_TYP_CA_INST_ALL as IGS_FI_FEE_DSB_FM_RU is getting obsolete
753 || IGS_FI_FEE_DSB_FM_RU.FORMULA_NUMBER is modified as Number.
754 || As per SFCR005_Cleanup_Build TD (Enhancement Bug # 2531390)
755 || smvk 28-Aug-2002 Removed the existing code and the function is modified to return NULL always.
756 || As per SFCR005_Cleanup_Build TD (Enhancement Bug # 2531390)
757 ----------------------------------------------------------------------------*/
758 RETURN VARCHAR2 IS
759 BEGIN
760 /* code has been removed and this function always returns null, as per SFCR005_Cleanup_Build TD (Enhancement Bug # 2531390) */
761 RETURN NULL;
762 END get_fdfr_rule;
763 /*
764 Get course stage rule number
765 if no rule return NULL
766 */
767 FUNCTION get_crs_stg_rule(
768 p_rule_call_name IN VARCHAR2,
769 p_course_cd IN VARCHAR2,
770 p_course_version IN NUMBER,
771 p_cst_sequence_number IN NUMBER
772 ) RETURN VARCHAR2 IS
773 v_rule_number NUMBER;
774 BEGIN
775 SELECT rul_sequence_number
776 INTO v_rule_number
777 FROM IGS_PS_STAGE_RU
778 WHERE course_cd = p_course_cd
779 AND version_number = p_course_version
780 AND cst_sequence_number = p_cst_sequence_number
781 AND s_rule_call_cd = p_rule_call_name;
782 RETURN v_rule_number;
783 EXCEPTION
784 WHEN NO_DATA_FOUND THEN
785 RETURN NULL;
786 END get_crs_stg_rule;
787 /*
788 get the called rule number using called rule name
789 */
790 FUNCTION get_called_rule (
791 p_called_rule_cd IGS_RU_CALL_RULE.called_rule_cd%TYPE
792 ) RETURN NUMBER IS
793 BEGIN
794 DECLARE
795 v_rule_number IGS_RU_RULE.sequence_number%TYPE;
796 BEGIN
797 SELECT nr_rul_sequence_number
798 INTO v_rule_number
799 FROM IGS_RU_CALL_RULE
800 WHERE called_rule_cd = p_called_rule_cd;
801 RETURN v_rule_number;
802 EXCEPTION
803 WHEN NO_DATA_FOUND THEN
804 log_error('get_called_rule', 'Invalid callable rule ('||p_called_rule_cd||')');
805 END;
806 END get_called_rule;
807 /*
808 Do course stage rule
809 */
810 FUNCTION do_course_stage (
811 p_course_stage_type VARCHAR2
812 ) RETURN VARCHAR2 IS
813 v_cst_sequence_number IGS_PS_STAGE.sequence_number%TYPE;
814 BEGIN
815 /*
816 get sequence number for stage type
817 */
818 SELECT cst.sequence_number
819 INTO v_cst_sequence_number
820 FROM IGS_PS_STAGE cst
821 WHERE cst.course_cd = p_course_cd
822 AND cst.version_number = p_course_version
823 AND cst.course_stage_type = p_course_stage_type;
824 /*
825 get alternative stage completion method
826 */
827 RETURN igs_pr_gen_002.prgp_get_stg_comp (
828 p_person_id,
829 p_course_cd,
830 p_course_version,
831 v_cst_sequence_number
832 );
833 EXCEPTION
834 WHEN NO_DATA_FOUND THEN
835 /*
836 stage type does not exist for course.version, default is 'Y'
837 */
838 RETURN 'Y';
839 END do_course_stage;
840 /*
841 NUMERIC FUNCTIONS
842 */
843 FUNCTION divide (
844 p_num1 NUMBER,
845 p_num2 NUMBER )
846 RETURN NUMBER IS
847 BEGIN
848 IF p_num2 = 0
849 THEN
850 /*
851 divide by zero
852 */
853 RETURN NULL;
854 ELSE
855 RETURN p_num1/p_num2;
856 END IF;
857 END divide;
858
859
860 FUNCTION max_func (
861 p_num1 NUMBER,
862 p_num2 NUMBER )
863 RETURN NUMBER IS
864 BEGIN
865 IF p_num1 > p_num2
866 THEN
867 RETURN p_num1;
868 ELSE
869 RETURN p_num2;
870 END IF;
871 END max_func;
872
873 FUNCTION min_func (
874 p_num1 NUMBER,
875 p_num2 NUMBER )
876 RETURN NUMBER IS
877 BEGIN
878 IF p_num1 < p_num2
879 THEN
880 RETURN p_num1;
881 ELSE
882 RETURN p_num2;
883 END IF;
884 END min_func;
885
886 /*
887 TARGET UNIT ATTRIBUTE FUNCTIONS
888 unit version attributes
889 */
890 FUNCTION uv_attribute (
891 p_attribute VARCHAR2,
892 p_tds r_tds )
893 RETURN VARCHAR2 IS
894 v_repeatable_ind IGS_PS_UNIT_VER.repeatable_ind%TYPE;
895 BEGIN
896 SELECT repeatable_ind
897 INTO v_repeatable_ind
898 FROM IGS_PS_UNIT_VER
899 WHERE unit_cd = p_tds.unit_cd
900 AND version_number = p_tds.unit_version;
901 IF p_attribute = 'rep_ind'
902 THEN
903 RETURN v_repeatable_ind;
904 ELSE
905 log_error('uv_attribute',
906 'Invalid attribute ('||p_attribute||')');
907 END IF;
908 EXCEPTION
909 WHEN NO_DATA_FOUND THEN
910 RETURN NULL;
911 END uv_attribute;
912 /*
913 duplicates within a set (by unit_cd)
914 */
915 FUNCTION duplicate_count (
916 p_tds IN r_tds )
917 RETURN NUMBER IS
918 v_member BINARY_INTEGER;
919 v_count NUMBER := 0;
920 BEGIN
921 v_member := gv_set(p_tds.set_number).first;
922 LOOP
923 /*
924 check if last (must do check for null)
925 */
926 IF v_member IS NULL
927 THEN
928 EXIT;
929 END IF;
930 IF gv_member(v_member).f1 = p_tds.unit_cd
931 THEN
932 v_count := v_count + 1;
933 END IF;
934 /*
935 next member
936 */
937 v_member := gv_member(v_member).next;
938 END LOOP;
939 RETURN v_count;
940 END duplicate_count;
941 /*
942 unit version credit points
943 */
944 FUNCTION uv_credit_points (
945 p_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
946 p_unit_version IGS_PS_UNIT_VER.version_number%TYPE )
947 RETURN NUMBER IS
948 v_achievable_credit_points IGS_PS_UNIT_VER.achievable_credit_points%TYPE;
949 BEGIN
950 SELECT NVL(achievable_credit_points,enrolled_credit_points)
951 INTO v_achievable_credit_points
952 FROM IGS_PS_UNIT_VER
953 WHERE unit_cd = p_unit_cd
954 AND version_number = p_unit_version;
955 RETURN v_achievable_credit_points;
956 END uv_credit_points;
957
958 FUNCTION us_uv_credit_points( p_uoo_id IN NUMBER)
959 RETURN NUMBER IS
960 l_n_credit_points IGS_PS_UNIT_VER.achievable_credit_points%TYPE;
961 BEGIN
962 SELECT NVL(cps.achievable_credit_points,NVL(uv.achievable_credit_points,NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)))
963 INTO l_n_credit_points
964 FROM IGS_PS_UNIT_VER uv,
965 IGS_PS_UNIT_OFR_OPT uoo,
966 IGS_PS_USEC_CPS cps
967 WHERE uoo.uoo_id=cps.uoo_id(+) AND
968 uoo.uoo_id=p_uoo_id AND
969 uoo.unit_cd = uv.unit_cd AND
970 uoo.version_number= uv.version_number;
971 RETURN l_n_credit_points;
972 END us_uv_credit_points;
973
974
975 /*
976 student unit attempt credit points achievable
977 */
978 FUNCTION sua_credit_points (
979 p_member_index BINARY_INTEGER )
980 RETURN NUMBER IS
981 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
982 v_override_achievable_cp IGS_EN_SU_ATTEMPT.override_achievable_cp%TYPE;
983 BEGIN
984 -- added logic to obtain the advance standing credit points when the coo_id is null , bug#4283221
985
986 -- added logic to obtain the advance standing credit points when the coo_id is null , bug#4283221
987 -- Modified the below query to get the the sum of the credit points of all the records fetched.
988 -- This is done because we should consider the credit points of all the advanced standing units against the same unit
989 -- for bug# 4480311
990 IF gv_member(p_member_index).f5 IS NULL THEN
991 SELECT SUM(nvl(achievable_credit_points,0))
992 INTO v_override_achievable_cp
993 FROM IGS_AV_STND_UNIT
994 WHERE as_course_cd = p_course_cd
995 AND person_id = p_person_id
996 AND unit_cd = gv_member(p_member_index).f1
997 AND version_number = gv_member(p_member_index).f2
998 AND s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
999 AND s_adv_stnd_recognition_type = 'CREDIT'
1000 AND (igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,unit_cd,version_number,'BOTH',NULL) ='TRUE');
1001
1002
1003 RETURN v_override_achievable_cp;
1004
1005 ELSE
1006 SELECT unit_attempt_status,
1007 override_achievable_cp
1008 INTO v_unit_attempt_status,
1009 v_override_achievable_cp
1010 FROM IGS_EN_SU_ATTEMPT
1011 WHERE person_id = p_person_id
1012 AND course_cd = p_course_cd
1013 AND uoo_id = gv_member(p_member_index).f5;
1014 END IF;
1015
1016 IF (v_override_achievable_cp IS NOT NULL)
1017 THEN
1018 RETURN v_override_achievable_cp;
1019 ELSE
1020 RETURN us_uv_credit_points(gv_member(p_member_index).f5);
1021 END IF;
1022 EXCEPTION
1023 WHEN NO_DATA_FOUND THEN
1024 /*
1025 assume no sua record!
1026 */
1027 RETURN us_uv_credit_points(gv_member(p_member_index).f5);
1028 END sua_credit_points;
1029 /*
1030 student unit attempt status/result/grade/mark of target unit
1031 */
1032 FUNCTION sua_attribute (
1033 p_attribute IN VARCHAR2,
1034 p_member_index IN BINARY_INTEGER,
1035 p_final_ind IN VARCHAR2)
1036 RETURN VARCHAR2 IS
1037 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
1038 v_override_achievable_cp IGS_EN_SU_ATTEMPT.override_achievable_cp%TYPE;
1039 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
1040 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1041 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1042 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1043 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
1044 v_original_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
1045 v_result VARCHAR2(30);
1046 BEGIN
1047 SELECT unit_attempt_status,
1048 override_achievable_cp
1049 INTO v_unit_attempt_status,
1050 v_override_achievable_cp
1051 FROM IGS_EN_SU_ATTEMPT
1052 WHERE person_id = p_person_id
1053 AND course_cd = p_course_cd
1054 AND uoo_id = gv_member(p_member_index).f5;
1055
1056 IF p_attribute = 'status'
1057 THEN
1058 RETURN v_unit_attempt_status;
1059 END IF;
1060 v_result := IGS_AS_GEN_003.assp_get_sua_outcome (
1061 p_person_id,
1062 p_course_cd,
1063 gv_member(p_member_index).f1,
1064 gv_member(p_member_index).f3,
1065 gv_member(p_member_index).f4,
1066 v_unit_attempt_status,
1067 p_final_ind, /* finalised indicator (make parameter ??) */
1068 v_outcome_dt,
1069 v_grading_schema_cd,
1070 v_gs_version_number,
1071 v_grade,
1072 v_mark,
1073 v_original_course_cd,
1074 gv_member(p_member_index).f5,
1075 --added by LKAKI---
1076 'N');
1077 IF p_attribute = 'result'
1078 THEN
1079 RETURN v_result;
1080 ELSIF p_attribute = 'grd_sch'
1081 THEN
1082 RETURN v_grading_schema_cd;
1083 ELSIF p_attribute = 'grade'
1084 THEN
1085 RETURN v_grade;
1086 ELSIF p_attribute = 'mark'
1087 THEN
1088 RETURN v_mark;
1089 ELSIF p_attribute = 'outcome_dt'
1090 THEN
1091 RETURN v_outcome_dt;
1092 ELSIF p_attribute = 'pass_conceded'
1093 THEN
1094 RETURN b_to_t(igs_as_gen_002.assp_get_gsg_cncd(v_grading_schema_cd,
1095 v_gs_version_number,
1096 v_grade) = 'Y');
1097 ELSE
1098 log_error('sua_attribute',
1099 'Invalid attribute ('||p_attribute||')');
1100 END IF;
1101 EXCEPTION
1102 WHEN NO_DATA_FOUND THEN
1103 RETURN NULL;
1104 END sua_attribute;
1105
1106
1107 -- smaddali added this function for the new turin function added for bug#4304688
1108
1109 -- modified the query which gets grading schema code and grade to get grading schema code
1110 -- and grade of the least ranked record (highest grade) for bug# 4580311
1111 -- This query may return multiple records but we should consider only the least ranked record.
1112
1113 FUNCTION adv_attribute (p_attribute IN VARCHAR2, p_member_index IN BINARY_INTEGER)
1114 RETURN VARCHAR2 IS
1115 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1116 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1117
1118 CURSOR c_grad_sch IS
1119 SELECT av.grading_schema_cd, av.grade
1120 FROM igs_av_stnd_unit av , igs_as_grd_sch_grade grad
1121 WHERE av.as_course_cd = p_course_cd
1122 AND av.person_id = p_person_id
1123 AND av.unit_cd = gv_member(p_member_index).f1
1124 AND av.version_number = gv_member(p_member_index).f2
1125 AND av.s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
1126 AND av.s_adv_stnd_recognition_type = 'CREDIT'
1127 AND (igs_av_val_asu.granted_adv_standing(av.person_id,av.as_course_cd,av.as_version_number,av.unit_cd,av.version_number,'BOTH',NULL) ='TRUE')
1128 AND grad.grading_schema_cd = av.grading_schema_cd
1129 AND grad.version_number = av.grd_sch_version_number
1130 AND grad.grade = av.grade
1131 ORDER BY grad.rank ASC;
1132 BEGIN
1133 OPEN c_grad_sch;
1134 FETCH c_grad_sch INTO v_grading_schema_cd,v_grade;
1135 CLOSE c_grad_sch;
1136
1137 IF p_attribute = 'grd_sch' THEN
1138 RETURN v_grading_schema_cd;
1139 ELSIF p_attribute = 'grade' THEN
1140 RETURN v_grade;
1141 END IF;
1142
1143 EXCEPTION
1144 WHEN NO_DATA_FOUND THEN
1145 RETURN NULL;
1146 END adv_attribute;
1147
1148
1149
1150
1151 /*
1152 determine if target unit satisfies the attribute within the given set
1153 there can be more than one 'last' unit (same period/same credit points)
1154 all though it trys to resolve cp's by selecting latest (for duplicates)
1155 */
1156 FUNCTION sua_select_unique(
1157 p_attribute IN VARCHAR2,
1158 p_member_index IN BINARY_INTEGER,
1159 p_set1 IN NUMBER )
1160 RETURN VARCHAR2 IS
1161 /*
1162 'true' or 'false'
1163 */
1164 v_member BINARY_INTEGER;
1165 v_target_cp NUMBER;
1166 v_duplicate_cp NUMBER;
1167 v_rel_time VARCHAR2(30);
1168 BEGIN
1169 IF p_attribute = 'maxcp' OR p_attribute = 'mincp'
1170 THEN
1171 /*
1172 cp of target unit
1173 */
1174 v_target_cp := sua_credit_points(p_member_index);
1175 ELSIF p_attribute <> 'first' AND p_attribute <> 'last'
1176 THEN
1177 log_error('sua_select_unique',
1178 'Invalid attribute ('||p_attribute||')');
1179 END IF;
1180 /*
1181 for all members within given set
1182 */
1183 v_member := gv_set(p_set1).first;
1184 LOOP
1185 /*
1186 check if last (must do check for null)
1187 */
1188 IF v_member IS NULL
1189 THEN
1190 EXIT;
1191 END IF;
1192 v_rel_time := IGS_CA_GEN_001.CALP_GET_RLTV_TIME(gv_member(v_member).f3,
1193 gv_member(v_member).f4,
1194 gv_member(p_member_index).f3,
1195 gv_member(p_member_index).f4);
1196 IF v_rel_time = 'AFTER'
1197 THEN
1198 /*
1199 target unit is after, therefore cannot be first
1200 */
1201 IF p_attribute = 'first'
1202 THEN
1203 RETURN 'false';
1204 END IF;
1205 ELSIF v_rel_time = 'BEFORE'
1206 THEN
1207 /*
1208 target unit is before, therefore cannot be last
1209 */
1210 IF p_attribute = 'last'
1211 THEN
1212 RETURN 'false';
1213 END IF;
1214 END IF;
1215 IF p_attribute = 'maxcp' OR p_attribute = 'mincp'
1216 THEN
1217 /*
1218 cp of unit
1219 */
1220 v_duplicate_cp := sua_credit_points(v_member);
1221 IF v_duplicate_cp > v_target_cp
1222 THEN
1223 /*
1224 target unit not highest cp, therefore cannot be maxcp
1225 */
1226 IF p_attribute = 'maxcp'
1227 THEN
1228 RETURN 'false';
1229 END IF;
1230 ELSIF v_duplicate_cp < v_target_cp
1231 THEN
1232 /*
1233 target unit not lowest cp, therefore cannot be mincp
1234 */
1235 IF p_attribute = 'mincp'
1236 THEN
1237 RETURN 'false';
1238 END IF;
1239 ELSIF v_rel_time = 'BEFORE'
1240 THEN
1241 /*
1242 target unit with equal cp but not last (default)
1243 */
1244 RETURN 'false';
1245 END IF;
1246 END IF;
1247 /*
1248 next member
1249 */
1250 v_member := gv_member(v_member).next;
1251 END LOOP;
1252 /*
1253 target unit must be first/last/maxcp/mincp
1254 */
1255 RETURN 'true';
1256 END sua_select_unique;
1257 /*
1258
1259 compare grades according to comparitor string operator
1260 assumes the highest rank has the lowest numerical value
1261
1262 */
1263 FUNCTION sua_relative_grade (
1264 p_lhs_gs IN VARCHAR2,
1265 p_lhs_grade IN VARCHAR2,
1266 p_comparitor IN VARCHAR2,
1267 p_rhs_gs IN VARCHAR2,
1268 p_rhs_grade IN VARCHAR2,
1269 p_tds IN r_tds )
1270 RETURN VARCHAR2 IS
1271 /*
1272 'true' or 'false'
1273 */
1274 v_lhs_rank IGS_AS_GRD_SCH_GRADE.rank%TYPE;
1275 v_rhs_rank IGS_AS_GRD_SCH_GRADE.rank%TYPE;
1276 v_location_cd IGS_EN_SU_ATTEMPT.location_cd%TYPE;
1277 v_unit_class IGS_EN_SU_ATTEMPT.unit_class%TYPE;
1278 v_grading_schema IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1279 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1280 BEGIN
1281 /*
1282 if grading schema not same then only <> is true
1283 */
1284 IF p_lhs_gs <> p_rhs_gs
1285 THEN
1286 IF p_comparitor = '<>'
1287 THEN
1288 RETURN 'true';
1289 END IF;
1290 RETURN 'false';
1291 END IF;
1292 /*
1293 get gs_version_number
1294 */
1295 SELECT location_cd,
1296 unit_class
1297 INTO v_location_cd,
1298 v_unit_class
1299 FROM IGS_EN_SU_ATTEMPT
1300 WHERE person_id = p_person_id
1301 AND course_cd = p_course_cd
1302 AND uoo_id = p_tds.uoo_id;
1303 IF IGS_AS_GEN_003.ASSP_GET_SUA_GS(p_person_id,
1304 p_course_cd,
1305 p_tds.unit_cd,
1306 p_tds.unit_version,
1307 p_tds.cal_type,
1308 p_tds.ci_sequence_number,
1309 v_location_cd,
1310 v_unit_class,
1311 v_grading_schema,
1312 v_gs_version_number ) = NULL
1313 THEN
1314 log_error('sua_relative_grade',
1315 'IGS_AS_GEN_003.ASSP_GET_SUA_GS');
1316 END IF;
1317 /*
1318 grading_schema must be same???
1319 */
1320 IF v_grading_schema <> p_lhs_gs
1321 THEN
1322 IF p_comparitor = '<>'
1323 THEN
1324 RETURN 'true';
1325 END IF;
1326 RETURN 'false';
1327 END IF;
1328 /*
1329 determine rank
1330 */
1331 BEGIN
1332 SELECT rank
1333 INTO v_lhs_rank
1334 FROM IGS_AS_GRD_SCH_GRADE
1335 WHERE grading_schema_cd = v_grading_schema
1336 AND version_number = v_gs_version_number
1337 AND grade = p_lhs_grade;
1338 SELECT rank
1339 INTO v_rhs_rank
1340 FROM IGS_AS_GRD_SCH_GRADE
1341 WHERE grading_schema_cd = v_grading_schema
1342 AND version_number = v_gs_version_number
1343 AND grade = p_rhs_grade;
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 RETURN 'false';
1347 END;
1348 /*
1349 Note the higher the rank the lower the grade
1350 */
1351 IF p_comparitor = '<'
1352 THEN
1353 IF v_lhs_rank > v_rhs_rank
1354 THEN
1355 RETURN 'true';
1356 END IF;
1357 ELSIF p_comparitor = '<='
1358 THEN
1359 IF v_lhs_rank >= v_rhs_rank
1360 THEN
1361 RETURN 'true';
1362 END IF;
1363 ELSIF p_comparitor = '='
1364 THEN
1365 IF v_lhs_rank = v_rhs_rank
1366 THEN
1367 RETURN 'true';
1368 END IF;
1369 ELSIF p_comparitor = '<>'
1370 THEN
1371 IF v_lhs_rank <> v_rhs_rank
1372 THEN
1373 RETURN 'true';
1374 END IF;
1375 ELSIF p_comparitor = '>='
1376 THEN
1377 IF v_lhs_rank <= v_rhs_rank
1378 THEN
1379 RETURN 'true';
1380 END IF;
1381 ELSIF p_comparitor = '>'
1382 THEN
1383 IF v_lhs_rank < v_rhs_rank
1384 THEN
1385 RETURN 'true';
1386 END IF;
1387 ELSE
1388 log_error('sua_relative_grade',
1389 'Invalid comparitor ('||p_comparitor||')');
1390 END IF;
1391 RETURN 'false';
1392 END sua_relative_grade;
1393
1394
1395 -- smaddali added this function for the new turin function added for bug#4304688
1396
1397 -- modified the query which gets grading schema code and version number to get grading schema code
1398 -- and grade of the least ranked record (highest grade) for bug# 4580311
1399 -- This query may return multiple records but we should consider only the least ranked record.
1400
1401 FUNCTION adv_relative_grade (
1402 p_lhs_gs IN VARCHAR2,
1403 p_lhs_grade IN VARCHAR2,
1404 p_comparitor IN VARCHAR2,
1405 p_rhs_gs IN VARCHAR2,
1406 p_rhs_grade IN VARCHAR2,
1407 p_tds IN r_tds )
1408 RETURN VARCHAR2 IS
1409 /*
1410 'true' or 'false'
1411 */
1412 v_lhs_rank IGS_AS_GRD_SCH_GRADE.rank%TYPE;
1413 v_rhs_rank IGS_AS_GRD_SCH_GRADE.rank%TYPE;
1414 v_location_cd IGS_EN_SU_ATTEMPT.location_cd%TYPE;
1415 v_unit_class IGS_EN_SU_ATTEMPT.unit_class%TYPE;
1416 v_grading_schema IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1417 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1418
1419 CURSOR c_grad_sch IS
1420 SELECT av.grading_schema_cd, av.grd_sch_version_number
1421 FROM IGS_AV_STND_UNIT av , igs_as_grd_sch_grade grad
1422 WHERE av.as_course_cd = p_course_cd
1423 AND av.person_id = p_person_id
1424 AND av.unit_cd = p_tds.unit_cd
1425 AND av.version_number = p_tds.unit_version
1426 AND av.s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
1427 AND av.s_adv_stnd_recognition_type = 'CREDIT'
1428 AND (igs_av_val_asu.granted_adv_standing(av.person_id,av.as_course_cd,av.as_version_number,av.unit_cd,av.version_number,'BOTH',NULL) ='TRUE')
1429 AND grad.grading_schema_cd = av.grading_schema_cd
1430 AND grad.version_number = av.grd_sch_version_number
1431 AND grad.grade = av.grade
1432 ORDER BY grad.rank ASC;
1433 BEGIN
1434 /*
1435 if grading schema not same then only <> is true
1436 */
1437 IF p_lhs_gs <> p_rhs_gs THEN
1438 IF p_comparitor = '<>'
1439 THEN
1440 RETURN 'true';
1441 END IF;
1442 RETURN 'false';
1443 END IF;
1444 /*
1445 get gs_version_number
1446 */
1447 BEGIN
1448 OPEN c_grad_sch;
1449 FETCH c_grad_sch INTO v_grading_schema, v_gs_version_number;
1450 CLOSE c_grad_sch;
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 RETURN 'false';
1454 END ;
1455 /*
1456 grading_schema must be same???
1457 */
1458 IF v_grading_schema <> p_lhs_gs
1459 THEN
1460 IF p_comparitor = '<>'
1461 THEN
1462 RETURN 'true';
1463 END IF;
1464 RETURN 'false';
1465 END IF;
1466 /*
1467 determine rank
1468 */
1469 BEGIN
1470 SELECT rank
1471 INTO v_lhs_rank
1472 FROM IGS_AS_GRD_SCH_GRADE
1473 WHERE grading_schema_cd = v_grading_schema
1474 AND version_number = v_gs_version_number
1475 AND grade = p_lhs_grade;
1476 SELECT rank
1477 INTO v_rhs_rank
1478 FROM IGS_AS_GRD_SCH_GRADE
1479 WHERE grading_schema_cd = v_grading_schema
1480 AND version_number = v_gs_version_number
1481 AND grade = p_rhs_grade;
1482 EXCEPTION
1483 WHEN NO_DATA_FOUND THEN
1484 RETURN 'false';
1485 END;
1486 /*
1487 Note the higher the rank the lower the grade
1488 */
1489 IF p_comparitor = '<'
1490 THEN
1491 IF v_lhs_rank > v_rhs_rank
1492 THEN
1493 RETURN 'true';
1494 END IF;
1495 ELSIF p_comparitor = '<='
1496 THEN
1497 IF v_lhs_rank >= v_rhs_rank
1498 THEN
1499 RETURN 'true';
1500 END IF;
1501 ELSIF p_comparitor = '='
1502 THEN
1503 IF v_lhs_rank = v_rhs_rank
1504 THEN
1505 RETURN 'true';
1506 END IF;
1507 ELSIF p_comparitor = '<>'
1508 THEN
1509 IF v_lhs_rank <> v_rhs_rank
1510 THEN
1511 RETURN 'true';
1512 END IF;
1513 ELSIF p_comparitor = '>='
1514 THEN
1515 IF v_lhs_rank <= v_rhs_rank
1516 THEN
1517
1518 RETURN 'true';
1519 END IF;
1520 ELSIF p_comparitor = '>'
1521 THEN
1522 IF v_lhs_rank < v_rhs_rank
1523 THEN
1524 RETURN 'true';
1525 END IF;
1526 ELSE
1527 log_error('adv_relative_grade',
1528 'Invalid comparitor ('||p_comparitor||')');
1529 END IF;
1530 RETURN 'false';
1531 END adv_relative_grade;
1532
1533
1534 /*
1535
1536 UNIT SET ATTEMPT ATTRIBUTES
1537
1538 get unit set status/psi for target unit set
1539
1540 */
1541 FUNCTION susa_attribute (
1542 p_attribute IN VARCHAR2,
1543 p_tds IN r_tds )
1544 RETURN VARCHAR2 IS
1545 v_student_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
1546 v_end_dt IGS_AS_SU_SETATMPT.end_dt%TYPE;
1547 v_rqrmnts_complete_ind IGS_AS_SU_SETATMPT.rqrmnts_complete_ind%TYPE;
1548 v_primary_set_ind IGS_AS_SU_SETATMPT.primary_set_ind%TYPE;
1549 BEGIN
1550 SELECT student_confirmed_ind,
1551 end_dt,
1552 rqrmnts_complete_ind,
1553 primary_set_ind
1554 INTO v_student_confirmed_ind,
1555 v_end_dt,
1556 v_rqrmnts_complete_ind,
1557 v_primary_set_ind
1558 FROM IGS_AS_SU_SETATMPT
1559 WHERE person_id = p_person_id
1560 AND course_cd = p_course_cd
1561 AND unit_set_cd = p_tds.unit_cd
1562 AND us_version_number = p_tds.unit_version
1563 AND sequence_number = p_tds.cal_type;
1564 IF p_attribute = 'status'
1565 THEN
1566 IF v_student_confirmed_ind = 'N'
1567 THEN
1568 RETURN 'UNCONFIRMED';
1569 ELSIF v_student_confirmed_ind = 'Y'
1570 AND v_end_dt IS NULL
1571 AND v_rqrmnts_complete_ind = 'N'
1572 THEN
1573 RETURN 'SELECTED';
1574 ELSIF v_student_confirmed_ind = 'Y'
1575 AND v_end_dt IS NOT NULL
1576 THEN
1577 RETURN 'ENDED';
1578 ELSIF v_rqrmnts_complete_ind = 'Y'
1579 THEN
1580 RETURN 'COMPLETE';
1581 END IF;
1582 RETURN NULL;
1583 ELSIF p_attribute = 'psi'
1584 THEN
1585 RETURN b_to_t(v_primary_set_ind = 'Y');
1586 END IF;
1587 EXCEPTION
1588 WHEN NO_DATA_FOUND THEN
1589 RETURN NULL;
1590 END susa_attribute;
1591 /*
1592
1593 US ATTRIBUTES
1594
1595 return unit set attribute value of target unit set
1596
1597 */
1598 FUNCTION us_attribute (
1599 p_attribute IN VARCHAR2,
1600 p_tds IN r_tds )
1601 RETURN VARCHAR2 IS
1602 v_unit_set_cat IGS_EN_UNIT_SET.unit_set_cat%TYPE;
1603 BEGIN
1604 SELECT unit_set_cat
1605 INTO v_unit_set_cat
1606 FROM IGS_EN_UNIT_SET
1607 WHERE unit_set_cd = p_tds.unit_cd
1608 AND version_number = p_tds.unit_version;
1609 IF p_attribute = 'usc'
1610 THEN
1611 RETURN v_unit_set_cat;
1612 END IF;
1613 END us_attribute;
1614 /*
1615
1616 ASUL ATTRIBUTES
1617
1618 return advanced standing unit level attribute value of a target unit
1619
1620 */
1621
1622 -- Modified the query which gets credit points to get the sum of credit points of
1623 -- all the records and added where clause s_adv_stnd_granting_status IN ('GRANTED').
1624 FUNCTION asul_attribute (
1625 p_attribute IN VARCHAR2,
1626 p_tds IN r_tds )
1627 RETURN VARCHAR2 IS
1628 v_credit_points IGS_AV_STND_UNIT_LVL.credit_points%TYPE;
1629 BEGIN
1630 IF p_attribute = 'level'
1631 THEN
1632 RETURN p_tds.unit_version;
1633 END IF;
1634 SELECT SUM(credit_points)
1635 INTO v_credit_points
1636 FROM IGS_AV_STND_UNIT_LVL
1637 WHERE person_id = p_person_id
1638 AND as_course_cd = p_course_cd
1639 AND s_adv_stnd_granting_status IN ('GRANTED')
1640 /*
1641 AND as_version_number = p_course_version
1642 */
1643 AND s_adv_stnd_type = p_tds.unit_cd
1644 AND unit_level = p_tds.unit_version
1645 AND crs_group_ind = p_tds.cal_type
1646 AND exemption_institution_cd = p_tds.ci_sequence_number;
1647 IF p_attribute = 'credit_points'
1648 THEN
1649 RETURN v_credit_points;
1650 ELSE
1651 log_error('asul_attribute',
1652 'Invalid attribute ('||p_attribute||')');
1653 END IF;
1654 EXCEPTION
1655 WHEN NO_DATA_FOUND THEN
1656 RETURN NULL;
1657 END asul_attribute;
1658 /*
1659
1660 get WAM weighting for target unit
1661
1662 */
1663 FUNCTION wam_weighting (
1664 p_member_index IN BINARY_INTEGER )
1665 RETURN NUMBER IS
1666 v_wam_weighting IGS_PS_UNIT_LEVEL.wam_weighting%TYPE;
1667 v_unit_level IGS_PS_UNIT_LEVEL.unit_level%TYPE;
1668 BEGIN
1669 IF p_course_version IS NULL
1670 THEN
1671 RETURN NULL;
1672 END IF;
1673 /*
1674 determine unit level
1675 */
1676 v_unit_level := IGS_PS_GEN_002.CRSP_GET_UN_LVL(gv_member(p_member_index).f1,
1677 gv_member(p_member_index).f2,
1678 p_course_cd,
1679 p_course_version);
1680 BEGIN
1681 SELECT wam_weighting
1682 INTO v_wam_weighting
1683 FROM IGS_PS_UNIT_LVL
1684 WHERE unit_cd = gv_member(p_member_index).f1
1685 AND version_number = gv_member(p_member_index).f2
1686 AND course_cd = p_course_cd
1687 AND version_number = p_course_version
1688 AND unit_level = v_unit_level;
1689 IF v_wam_weighting IS NOT NULL
1690 THEN
1691 /*
1692 course_unit_level overrides unit_level
1693 */
1694 RETURN v_wam_weighting;
1695 END IF;
1696 EXCEPTION
1697 WHEN NO_DATA_FOUND
1698 THEN
1699 NULL;
1700 END;
1701 BEGIN
1702 SELECT wam_weighting
1703 INTO v_wam_weighting
1704 FROM IGS_PS_UNIT_LEVEL
1705 WHERE unit_level = v_unit_level;
1706 IF v_wam_weighting IS NOT NULL
1707 THEN
1708 RETURN v_wam_weighting;
1709 END IF;
1710 EXCEPTION
1711 WHEN NO_DATA_FOUND
1712 THEN
1713 NULL;
1714 END;
1715 /*
1716 default value is 1
1717 */
1718 RETURN 1;
1719 END wam_weighting;
1720 /*
1721
1722 THE MESSAGING SYSTEM
1723
1724
1725 get message variable
1726
1727 */
1728 FUNCTION get_variable (
1729 p_variable_no NUMBER )
1730 RETURN VARCHAR2 IS
1731 BEGIN
1732 RETURN gt_variable(p_variable_no);
1733 EXCEPTION
1734 WHEN NO_DATA_FOUND
1735 THEN
1736 log_error('get_variable',
1737 'Undeclared variable:'||p_variable_no);
1738 END get_variable;
1739
1740 /*
1741
1742 push message to message stack
1743
1744 */
1745 PROCEDURE push_message (
1746 p_message VARCHAR2 )
1747 IS
1748 BEGIN
1749 gv_message_stack_index := gv_message_stack_index + 1;
1750 gt_message_stack(gv_message_stack_index) := p_message;
1751 END push_message;
1752 /*
1753
1754 pop from message stack
1755
1756 */
1757 FUNCTION pop_message
1758 RETURN VARCHAR2 IS
1759 BEGIN
1760 gv_message_stack_index := gv_message_stack_index - 1;
1761 RETURN gt_message_stack(gv_message_stack_index + 1);
1762 EXCEPTION
1763 WHEN NO_DATA_FOUND
1764 THEN
1765 RETURN 'ERROR:EMPTY MESSAGE STACK';
1766 END pop_message;
1767 /*
1768
1769 return all messages on the stack
1770 default is return all messages on stack (one message per line)
1771
1772 */
1773 FUNCTION get_message
1774 RETURN VARCHAR2 IS
1775 v_message IGS_RU_ITEM.value%TYPE;
1776 v_pop_message IGS_RU_ITEM.value%TYPE;
1777 BEGIN
1778 /*
1779 there will be one or more messages on the stack
1780 */
1781 WHILE gv_message_stack_index > 0
1782 LOOP
1783 v_pop_message := pop_message;
1784 IF v_pop_message IS NOT NULL
1785 THEN
1786 IF LENGTH(v_message) > 0
1787 THEN
1788 v_message := v_message||Fnd_Global.Local_Chr(10);
1789 END IF;
1790 v_message := v_message||v_pop_message;
1791 END IF;
1792 END LOOP;
1793 RETURN v_message;
1794 END get_message;
1795 /*
1796
1797 get and do message rule
1798
1799 */
1800 PROCEDURE do_message (
1801 p_rule_number IGS_RU_RULE.sequence_number%TYPE,
1802 p_rule_outcome IGS_RU_ITEM.value%TYPE,
1803 p_tds r_tds )
1804 IS
1805 v_message_rule IGS_RU_RULE.sequence_number%TYPE;
1806 v_tds r_tds;
1807 BEGIN
1808 /*
1809 get message rule number
1810 */
1811 SELECT message_rule
1812 INTO v_message_rule
1813 FROM IGS_RU_NAMED_RULE
1814 WHERE rul_sequence_number = p_rule_number;
1815 IF v_message_rule IS NOT NULL
1816 THEN
1817 v_tds := p_tds;
1818 v_tds.rule_outcome := p_rule_outcome;
1819 push_message(turing(v_message_rule,v_tds));
1820 END IF;
1821 EXCEPTION
1822 WHEN NO_DATA_FOUND
1823 THEN
1824 null;
1825 END do_message;
1826 /*
1827
1828 return set string
1829
1830 */
1831 FUNCTION display_set (
1832 p_set_number IN BINARY_INTEGER)
1833 RETURN VARCHAR2 IS
1834 ------------------------------------------------------------------
1835 --Known limitations/enhancements and/or remarks:
1836 --
1837 --Change History:
1838 --Who When What
1839 --smvk 29-Nov-2004 File.Sql.35 warning fix.
1840 --smadathi 07-JUN-2001 The length of the variable v_prev_f1 changed to varchar2(30) from varchar2(10) .
1841 -------------------------------------------------------------------
1842 v_member BINARY_INTEGER;
1843 v_prev_f1 VARCHAR2(30) ;
1844 v_more BOOLEAN;
1845 v_set IGS_RU_ITEM.value%TYPE;
1846 BEGIN
1847 v_prev_f1 := '@$%!*(|' ;
1848 v_more := FALSE;
1849 v_set := '{';
1850 v_member := gv_set(p_set_number).first;
1851 LOOP
1852 /*
1853 check if last (must do check for null)
1854 */
1855 IF v_member IS NULL
1856 THEN
1857 /*
1858 yep, last member
1859 */
1860 EXIT;
1861 END IF;
1862 IF gv_member(v_member).f1 <> v_prev_f1
1863 THEN
1864 IF v_more
1865 THEN
1866 v_set := v_set||', ';
1867 END IF;
1868 v_set := v_set||gv_member(v_member).f1;
1869 v_more := TRUE;
1870 v_prev_f1 := gv_member(v_member).f1;
1871 END IF;
1872 /*
1873 next member
1874 */
1875 v_member := gv_member(v_member).next;
1876 END LOOP;
1877 v_set := v_set||'}';
1878 RETURN v_set;
1879 END display_set;
1880 /*
1881
1882 STACK FUNCTIONS
1883
1884 Push value to stack
1885
1886 */
1887 PROCEDURE push(
1888 p_value IN VARCHAR2 )
1889 IS
1890 BEGIN
1891 gv_stack_index := gv_stack_index + 1;
1892
1893 gt_rule_stack(gv_stack_index) := p_value;
1894 END push;
1895 /*
1896
1897 Return value from stack
1898
1899 */
1900 FUNCTION pop
1901 RETURN VARCHAR2 IS
1902 BEGIN
1903 gv_stack_index := gv_stack_index - 1;
1904 RETURN gt_rule_stack(gv_stack_index + 1);
1905 EXCEPTION
1906 WHEN NO_DATA_FOUND
1907 THEN
1908 log_error('pop',
1909 'Stack empty');
1910 END pop;
1911 /*
1912
1913 push parameters to stack for sub-function call
1914
1915 */
1916 PROCEDURE push_params (
1917 p_start_stack IN NUMBER,
1918 p_no_parameters IN NUMBER)
1919 IS
1920 BEGIN
1921 FOR v_params IN 1 .. p_no_parameters
1922 LOOP
1923 push(gt_rule_stack(p_start_stack - p_no_parameters + v_params));
1924 END LOOP;
1925 END push_params;
1926 /*
1927
1928 get parameter from stack
1929
1930 */
1931 FUNCTION get_parameter (
1932 p_parameter_no NUMBER )
1933 RETURN VARCHAR2 IS
1934 BEGIN
1935 RETURN gt_rule_stack(p_parameter_no);
1936 EXCEPTION
1937 WHEN NO_DATA_FOUND
1938 THEN
1939 log_error('get_parameter',
1940 'Invalid parameter:'||p_parameter_no);
1941 END get_parameter;
1942 /*
1943 Clean up stack after function call
1944 */
1945 PROCEDURE clean_up_stack (
1946 p_no_parameters IN NUMBER )
1947 IS
1948 BEGIN
1949 /*
1950 reset index
1951 */
1952 gv_stack_index := gv_stack_index - p_no_parameters;
1953 END clean_up_stack;
1954 /*
1955
1956 COMPARISON FUNCTIONS
1957
1958 equal function
1959
1960 */
1961 FUNCTION eq(
1962 p_val1 IN VARCHAR2,
1963 p_val2 IN VARCHAR2 )
1964 RETURN VARCHAR2 IS
1965 BEGIN
1966 IF p_val1 IS NULL AND p_val2 IS NULL
1967 THEN
1968 RETURN ('true');
1969 END IF;
1970 IF IGS_GE_NUMBER.TO_NUM(p_val1) = IGS_GE_NUMBER.TO_NUM(p_val2)
1971 THEN
1972 RETURN ('true');
1973 ELSE
1974 RETURN ('false');
1975 END IF;
1976 EXCEPTION
1977 WHEN VALUE_ERROR
1978 THEN
1979 IF RTRIM(p_val1) = RTRIM(p_val2)
1980 THEN
1981 RETURN ('true');
1982 ELSE
1983 RETURN ('false');
1984 END IF;
1985 END eq;
1986 /*
1987
1988 not equal function
1989
1990 */
1991 FUNCTION neq(
1992 p_val1 IN VARCHAR2,
1993 p_val2 IN VARCHAR2 )
1994 RETURN VARCHAR2 IS
1995 BEGIN
1996 IF p_val1 IS NULL AND p_val2 IS NOT NULL
1997 OR p_val1 IS NOT NULL AND p_val2 IS NULL
1998 THEN
1999 RETURN ('true');
2000 END IF;
2001 IF IGS_GE_NUMBER.TO_NUM(p_val1) <> IGS_GE_NUMBER.TO_NUM(p_val2)
2002 THEN
2003 RETURN ('true');
2004 ELSE
2005 RETURN ('false');
2006 END IF;
2007 EXCEPTION
2008 WHEN VALUE_ERROR
2009 THEN
2010 IF RTRIM(p_val1) <> RTRIM(p_val2)
2011 THEN
2012 RETURN ('true');
2013 ELSE
2014 RETURN ('false');
2015 END IF;
2016 END neq;
2017 /*
2018
2019 compare dates
2020
2021 */
2022 FUNCTION date_compare (
2023 p_lhs IN VARCHAR2,
2024 p_comparitor IN VARCHAR2,
2025 p_rhs IN VARCHAR2 )
2026 RETURN VARCHAR2 IS
2027 v_date_format VARCHAR2(30);
2028 v_lhs DATE;
2029 v_rhs DATE;
2030 BEGIN
2031 IF IGS_GE_GEN_004.jbsp_get_dt_picture(p_lhs,v_date_format)
2032 THEN
2033 v_lhs := TO_DATE(p_lhs,v_date_format);
2034 END IF;
2035 IF IGS_GE_GEN_004.jbsp_get_dt_picture(p_rhs,v_date_format)
2036 THEN
2037 v_rhs := TO_DATE(p_rhs,v_date_format);
2038 END IF;
2039 IF p_comparitor = '<'
2040 THEN
2041 RETURN b_to_t(v_lhs < v_rhs);
2042 ELSIF p_comparitor = '<='
2043 THEN
2044 RETURN b_to_t(v_lhs <= v_rhs);
2045 ELSIF p_comparitor = '='
2046 THEN
2047 RETURN b_to_t(v_lhs = v_rhs);
2048 ELSIF p_comparitor = '<>'
2049 THEN
2050 RETURN b_to_t(v_lhs <> v_rhs);
2051 ELSIF p_comparitor = '>='
2052 THEN
2053 RETURN b_to_t(v_lhs >= v_rhs);
2054 ELSIF p_comparitor = '>'
2055 THEN
2056 RETURN b_to_t(v_lhs > v_rhs);
2057 END IF;
2058 END date_compare;
2059 /*
2060
2061 CONJUNCTION FUNCTIONS
2062
2063 And function
2064
2065 */
2066 FUNCTION and_func(
2067 p_val1 IN VARCHAR2,
2068 p_val2 IN VARCHAR2 )
2069 RETURN VARCHAR2 IS
2070 BEGIN
2071 IF RTRIM(p_val1) = 'true' AND RTRIM(p_val2) = 'true'
2072 THEN
2073 RETURN 'true';
2074 ELSIF RTRIM(p_val1) = 'false' OR RTRIM(p_val2) = 'false'
2075 THEN
2076 RETURN 'false';
2077 ELSE
2078 log_error('and_func',
2079 'Invalid arguments ('||RTRIM(p_val1)||','||RTRIM(p_val2)||')');
2080 END IF;
2081 END and_func;
2082 /*
2083
2084 Or function
2085
2086 */
2087 FUNCTION or_func(
2088 p_val1 IN VARCHAR2,
2089 p_val2 IN VARCHAR2 )
2090 RETURN VARCHAR2 IS
2091 BEGIN
2092 IF RTRIM(p_val1) = 'true' OR RTRIM(p_val2) = 'true'
2093 THEN
2094 RETURN 'true';
2095 ELSIF RTRIM(p_val1) = 'false' AND RTRIM(p_val2) = 'false'
2096 THEN
2097 RETURN 'false';
2098 ELSE
2099 log_error('or_func',
2100 'Invalid arguments ('||RTRIM(p_val1)||','||RTRIM(p_val2)||')');
2101 END IF;
2102 END or_func;
2103 /*
2104 SET FUNCTIONS
2105
2106 Create new set
2107 */
2108 FUNCTION new_set
2109 RETURN BINARY_INTEGER IS
2110 BEGIN
2111 gv_set_index := gv_set_index + 1;
2112 /*
2113 make new set
2114 */
2115 gv_set(gv_set_index).first := NULL;
2116 gv_set(gv_set_index).last_insert := NULL;
2117 RETURN gv_set_index;
2118 END new_set;
2119 /*
2120 Compare members (char)
2121 left < right RETURN -1
2122 left = right RETURN 0
2123 left > right RETURN 1
2124 */
2125 FUNCTION compare_members(
2126 l_index BINARY_INTEGER,
2127 r_index BINARY_INTEGER )
2128 RETURN NUMBER IS
2129 BEGIN
2130 IF gv_member(l_index).f1 < gv_member(r_index).f1
2131 THEN
2132 RETURN -1;
2133 ELSIF gv_member(l_index).f1 > gv_member(r_index).f1
2134 THEN
2135 RETURN 1;
2136 ELSIF gv_member(l_index).f2 < gv_member(r_index).f2
2137 THEN
2138 RETURN -1;
2139 ELSIF gv_member(l_index).f2 > gv_member(r_index).f2
2140 THEN
2141 RETURN 1;
2142 ELSIF gv_member(l_index).f3 < gv_member(r_index).f3
2143 THEN
2144 RETURN -1;
2145 ELSIF gv_member(l_index).f3 > gv_member(r_index).f3
2146 THEN
2147 RETURN 1;
2148 ELSIF gv_member(l_index).f4 < gv_member(r_index).f4
2149 THEN
2150 RETURN -1;
2151 ELSIF gv_member(l_index).f4 > gv_member(r_index).f4
2152 THEN
2153 RETURN 1;
2154 ELSIF gv_member(l_index).f5 < gv_member(r_index).f5
2155 THEN
2156 RETURN -1;
2157 ELSIF gv_member(l_index).f5 > gv_member(r_index).f5
2158 THEN
2159 RETURN 1;
2160 ELSE
2161 RETURN 0;
2162 END IF;
2163 END compare_members;
2164 /*
2165 Add member to set, ordered ascending (char), no duplicates.
2166 Most efficient if successive members are in ascending order.
2167 It is a simple linked list with the exception that a last insert pointer is
2168 maintained so that successive members can be added without searching the
2169 entire structure.
2170 */
2171 FUNCTION add_member(
2172 p_set_number IN BINARY_INTEGER,
2173 p_f1 IN VARCHAR2,
2174 p_f2 IN VARCHAR2,
2175 p_f3 IN VARCHAR2,
2176 p_f4 IN VARCHAR2,
2177 p_f5 IN VARCHAR2)
2178 RETURN BINARY_INTEGER IS
2179 v_current_ptr BINARY_INTEGER;
2180 v_compare NUMBER;
2181 BEGIN
2182 /*
2183 make new member
2184 */
2185 gv_member(gv_member_index).f1 := p_f1;
2186 gv_member(gv_member_index).f2 := p_f2;
2187 gv_member(gv_member_index).f3 := p_f3;
2188 gv_member(gv_member_index).f4 := p_f4;
2189 gv_member(gv_member_index).f5 := p_f5;
2190 /*
2191 add member to structure (not duplicates)
2192 */
2193 v_current_ptr := gv_set(p_set_number).last_insert;
2194 IF v_current_ptr IS NULL
2195 THEN
2196 /*
2197 first member, virgin set
2198 */
2199 gv_set(p_set_number).first := gv_member_index;
2200 gv_set(p_set_number).last_insert := gv_member_index;
2201 gv_member_index := gv_member_index + 1;
2202 RETURN p_set_number;
2203 END IF;
2204 v_compare := compare_members(gv_member_index, v_current_ptr);
2205 IF v_compare = -1
2206 THEN
2207 /*
2208 go back and search from start
2209 */
2210 v_current_ptr := gv_set(p_set_number).first;
2211 /*
2212 check the first member
2213 */
2214 v_compare := compare_members(gv_member_index, v_current_ptr);
2215 IF v_compare = -1
2216 THEN
2217 /*
2218 new first member
2219 */
2220 gv_member(gv_member_index).next := v_current_ptr;
2221 gv_set(p_set_number).first := gv_member_index;
2222 gv_set(p_set_number).last_insert := gv_member_index;
2223 gv_member_index := gv_member_index + 1;
2224 RETURN p_set_number;
2225 END IF;
2226 END IF;
2227 /*
2228 check for duplicates from either of the above cases
2229 */
2230 IF v_compare = 0
2231 THEN
2232 /*
2233 duplicate member
2234 */
2235 gv_set(p_set_number).last_insert := v_current_ptr;
2236 RETURN p_set_number;
2237 END IF;
2238 LOOP
2239 IF gv_member(v_current_ptr).next IS NULL
2240 THEN
2241 /*
2242 new last member
2243 */
2244 gv_member(v_current_ptr).next := gv_member_index;
2245 gv_set(p_set_number).last_insert := gv_member_index;
2246 gv_member_index := gv_member_index + 1;
2247 RETURN p_set_number;
2248 END IF;
2249 v_compare := compare_members(gv_member_index, gv_member(v_current_ptr).next);
2250 IF v_compare = -1
2251 THEN
2252 /*
2253 insert new member
2254 */
2255 gv_member(gv_member_index).next := gv_member(v_current_ptr).next;
2256 gv_member(v_current_ptr).next := gv_member_index;
2257 gv_set(p_set_number).last_insert := gv_member_index;
2258 gv_member_index := gv_member_index + 1;
2259 RETURN p_set_number;
2260 ELSIF v_compare = 0
2261 THEN
2262 /*
2263 duplicate member
2264 */
2265 gv_set(p_set_number).last_insert := v_current_ptr;
2266 RETURN p_set_number;
2267 ELSE
2268 /*
2269 get next member
2270 */
2271 v_current_ptr := gv_member(v_current_ptr).next;
2272 END IF;
2273 END LOOP;
2274 END add_member;
2275 /*
2276
2277 intersection of two sets
2278
2279 */
2280 FUNCTION set_intersect(
2281 p_set1 IN BINARY_INTEGER,
2282 p_set2 IN BINARY_INTEGER )
2283 RETURN BINARY_INTEGER IS
2284 v_set3 BINARY_INTEGER;
2285 v_l_index BINARY_INTEGER;
2286 v_r_index BINARY_INTEGER;
2287 v_compare NUMBER;
2288 BEGIN
2289 v_set3 := new_set;
2290 v_l_index := gv_set(p_set1).first;
2291 v_r_index := gv_set(p_set2).first;
2292 LOOP
2293 IF v_l_index IS NULL OR
2294 v_r_index IS NULL
2295 THEN
2296 /*
2297 last member in either set terminates intersect
2298 */
2299 RETURN v_set3;
2300 END IF;
2301 v_compare := compare_members(v_l_index,v_r_index);
2302 IF v_compare = -1
2303 THEN
2304 /*
2305 get next left member
2306 */
2307 v_l_index := gv_member(v_l_index).next;
2308 ELSIF v_compare = 0
2309 THEN
2310 /*
2311 add the equal member
2312 */
2313 v_set3 := add_member(v_set3,
2314 gv_member(v_l_index).f1,
2315 gv_member(v_l_index).f2,
2316 gv_member(v_l_index).f3,
2317 gv_member(v_l_index).f4,
2318 gv_member(v_l_index).f5);
2319 v_l_index := gv_member(v_l_index).next;
2320 v_r_index := gv_member(v_r_index).next;
2321 ELSE
2322 /*
2323 get next right member
2324 */
2325 v_r_index := gv_member(v_r_index).next;
2326 END IF;
2327 END LOOP;
2328 RETURN v_set3;
2329 END set_intersect;
2330 /*
2331
2332 union of two sets
2333
2334 */
2335 FUNCTION set_union(
2336 p_set1 IN BINARY_INTEGER,
2337 p_set2 IN BINARY_INTEGER )
2338 RETURN BINARY_INTEGER IS
2339 v_set3 BINARY_INTEGER;
2340 v_l_index BINARY_INTEGER;
2341 v_r_index BINARY_INTEGER;
2342 v_compare NUMBER;
2343 BEGIN
2344 v_set3 := new_set;
2345 v_l_index := gv_set(p_set1).first;
2346 v_r_index := gv_set(p_set2).first;
2347 LOOP
2348 IF v_l_index IS NULL AND
2349 v_r_index IS NULL
2350 THEN
2351 RETURN v_set3;
2352 END IF;
2353 IF v_l_index IS NULL
2354 THEN
2355 /*
2356 no more left, add all remaining right members
2357 */
2358 v_compare := 1;
2359 ELSIF v_r_index IS NULL
2360 THEN
2361 /*
2362 no more right, add all remaining left members
2363 */
2364 v_compare := -1;
2365 ELSE
2366 /*
2367 use compare to do ordered insert of members
2368 */
2369 v_compare := compare_members(v_l_index,v_r_index);
2370 END IF;
2371 IF v_compare = -1
2372 THEN
2373 /*
2374 add left member, get next left
2375 */
2376 v_set3 := add_member(v_set3,
2377 gv_member(v_l_index).f1,
2378 gv_member(v_l_index).f2,
2379 gv_member(v_l_index).f3,
2380 gv_member(v_l_index).f4,
2381 gv_member(v_l_index).f5);
2382 v_l_index := gv_member(v_l_index).next;
2383 ELSIF v_compare = 0
2384 THEN
2385 /*
2386 equal, add one member, get next members
2387 */
2388 v_set3 := add_member(v_set3,
2389 gv_member(v_l_index).f1,
2390 gv_member(v_l_index).f2,
2391 gv_member(v_l_index).f3,
2392 gv_member(v_l_index).f4,
2393 gv_member(v_l_index).f5);
2394 v_l_index := gv_member(v_l_index).next;
2395 v_r_index := gv_member(v_r_index).next;
2396 ELSE
2397 /*
2398 add right member, get next right
2399 */
2400 v_set3 := add_member(v_set3,
2401 gv_member(v_r_index).f1,
2402 gv_member(v_r_index).f2,
2403 gv_member(v_r_index).f3,
2404 gv_member(v_r_index).f4,
2405 gv_member(v_r_index).f5 );
2406 v_r_index := gv_member(v_r_index).next;
2407 END IF;
2408 END LOOP;
2409 RETURN v_set3;
2410 END set_union;
2411 /*
2412
2413 set1 minus set2
2414
2415 */
2416 FUNCTION set_minus(
2417 p_set1 IN BINARY_INTEGER,
2418 p_set2 IN BINARY_INTEGER )
2419 RETURN BINARY_INTEGER IS
2420 v_set3 BINARY_INTEGER;
2421 v_l_index BINARY_INTEGER;
2422 v_r_index BINARY_INTEGER;
2423 v_compare NUMBER;
2424 BEGIN
2425 v_set3 := new_set;
2426 v_l_index := gv_set(p_set1).first;
2427 v_r_index := gv_set(p_set2).first;
2428 LOOP
2429 IF v_l_index IS NULL
2430 THEN
2431 /*
2432 no more left, end minus
2433 */
2434 RETURN v_set3;
2435 END IF;
2436 IF v_r_index IS NULL
2437 THEN
2438 /*
2439 no more right, add all remaining left members
2440 */
2441 v_compare := -1;
2442 ELSE
2443 /*
2444 determine which members to add
2445 */
2446 v_compare := compare_members(v_l_index,v_r_index);
2447 END IF;
2448 IF v_compare = -1
2449 THEN
2450 /*
2451 add left member, get next left
2452 */
2453 v_set3 := add_member(v_set3,
2454 gv_member(v_l_index).f1,
2455 gv_member(v_l_index).f2,
2456 gv_member(v_l_index).f3,
2457 gv_member(v_l_index).f4,
2458 gv_member(v_l_index).f5);
2459 v_l_index := gv_member(v_l_index).next;
2460 ELSIF v_compare = 0
2461 THEN
2462 /*
2463 equal, get next left and right
2464 */
2465 v_l_index := gv_member(v_l_index).next;
2466 v_r_index := gv_member(v_r_index).next;
2467 ELSE
2468 /*
2469 get next right
2470 */
2471 v_r_index := gv_member(v_r_index).next;
2472 END IF;
2473 END LOOP;
2474 RETURN v_set3;
2475 END set_minus;
2476 /*
2477 count the set members by processing the linked list
2478 NOTE this count is UNIQUE on f1
2479 */
2480 FUNCTION members (
2481 p_set_number IN BINARY_INTEGER )
2482 RETURN NUMBER IS
2483 v_member BINARY_INTEGER;
2484 v_count NUMBER := 0;
2485 v_prev_f1 VARCHAR2(10);
2486 BEGIN
2487 v_prev_f1 := '@$%!*(|'; --smvk 29-Nov-2004 File.Sql.35 warning fix.
2488 v_member := gv_set(p_set_number).first;
2489 LOOP
2490 /*
2491 check if last (must do check for null)
2492 */
2493 IF v_member IS NULL
2494 THEN
2495 /*
2496 yep, last member
2497 */
2498 EXIT;
2499 END IF;
2500 IF gv_member(v_member).f1 <> v_prev_f1
2501 THEN
2502 v_count := v_count + 1;
2503 v_prev_f1 := gv_member(v_member).f1;
2504 END IF;
2505 /*
2506 next member
2507 */
2508 v_member := gv_member(v_member).next;
2509 END LOOP;
2510 RETURN v_count;
2511 END members;
2512 /*
2513 test if LHS exists in the set
2514 */
2515 FUNCTION in_set (
2516 p_lhs VARCHAR2,
2517 p_set_number IN BINARY_INTEGER )
2518 RETURN VARCHAR2 IS
2519 v_member BINARY_INTEGER;
2520 BEGIN
2521 v_member := gv_set(p_set_number).first;
2522 LOOP
2523 /*
2524 check if last (must do check for null)
2525 */
2526 IF v_member IS NULL
2527 THEN
2528 /*
2529 yep, last member
2530 */
2531 EXIT;
2532 END IF;
2533 IF p_lhs = gv_member(v_member).f1
2534 THEN
2535 RETURN 'true';
2536 END IF;
2537 v_member := gv_member(v_member).next;
2538 END LOOP;
2539 RETURN 'false';
2540 END in_set;
2541 /*
2542 show unit set members
2543 */
2544 FUNCTION show_set(
2545 p_set_number IN BINARY_INTEGER )
2546 RETURN BINARY_INTEGER IS
2547 v_member BINARY_INTEGER;
2548 v_wam_type VARCHAR2(10);
2549 BEGIN
2550 v_wam_type := 'COURSE'; --smvk 29-Nov-2004 File.Sql.35 warning fix.
2551 /*
2552 nks debug_display('Set '||p_set_number);
2553 */
2554 IF p_cal_type IS NOT NULL
2555 THEN
2556 v_wam_type := 'PERIOD';
2557 END IF;
2558
2559 v_member := gv_set(p_set_number).first;
2560 LOOP
2561 /*
2562 check if last (must do check for null)
2563 */
2564 IF v_member IS NULL
2565 THEN
2566 /*
2567 yep, last member
2568 */
2569 EXIT;
2570 END IF;
2571
2572 /*
2573 next member
2574 */
2575 v_member := gv_member(v_member).next;
2576 END LOOP;
2577 RETURN (p_set_number);
2578 END show_set;
2579 /*
2580 show unit set set members
2581 */
2582 FUNCTION show_us_set(
2583 p_set_number IN BINARY_INTEGER )
2584 RETURN BINARY_INTEGER IS
2585 v_member BINARY_INTEGER;
2586 v_tds r_tds;
2587 BEGIN
2588 v_member := gv_set(p_set_number).first;
2589 LOOP
2590 /*
2591 check if last (must do check for null)
2592 */
2593 IF v_member IS NULL
2594 THEN
2595 /*
2596 yep, last member
2597 */
2598 EXIT;
2599 END IF;
2600 v_tds.member_index := v_member;
2601 v_tds.unit_cd := gv_member(v_member).f1;
2602 v_tds.unit_version := gv_member(v_member).f2;
2603 v_tds.cal_type := gv_member(v_member).f3;
2604 v_tds.ci_sequence_number := gv_member(v_member).f4;
2605 v_tds.uoo_id := gv_member(v_member).f5;
2606
2607 /*
2608 next member
2609 */
2610 v_member := gv_member(v_member).next;
2611 END LOOP;
2612 RETURN (p_set_number);
2613 END show_us_set;
2614 /*
2615 duplicates (by unit code) within the given set (not including self)
2616 only used in sua_select_unique call
2617 */
2618 FUNCTION sua_duplicate_set (
2619 p_tds IN r_tds )
2620 RETURN BINARY_INTEGER IS
2621 v_member BINARY_INTEGER;
2622 v_set1 BINARY_INTEGER;
2623 BEGIN
2624 v_set1 := new_set;
2625 v_member := gv_set(p_tds.set_number).first;
2626 LOOP
2627 /*
2628 check if last (must do check for null)
2629 */
2630 IF v_member IS NULL
2631 THEN
2632 /*
2633 yep, last member
2634 */
2635 EXIT;
2636 END IF;
2637 IF gv_member(v_member).f1 = p_tds.unit_cd AND
2638 NOT (gv_member(v_member).f2 = p_tds.unit_version AND
2639 gv_member(v_member).f3 = p_tds.cal_type AND
2640 gv_member(v_member).f4 = p_tds.ci_sequence_number AND
2641 gv_member(v_member).f5 = p_tds.uoo_id)
2642 THEN
2643 v_set1 := add_member(v_set1,
2644 gv_member(v_member).f1,
2645 gv_member(v_member).f2,
2646 gv_member(v_member).f3,
2647 gv_member(v_member).f4,
2648 gv_member(v_member).f5);
2649 END IF;
2650 /*
2651 next member
2652 */
2653 v_member := gv_member(v_member).next;
2654 END LOOP;
2655 RETURN v_set1;
2656 END sua_duplicate_set;
2657 /*
2658 STUDENT RELATED SETS
2659
2660 Select student units
2661 */
2662 FUNCTION student
2663 RETURN BINARY_INTEGER IS
2664 BEGIN
2665 IF gv_sua_set IS NOT NULL
2666 THEN
2667 RETURN gv_sua_set;
2668 END IF;
2669 gv_sua_set := new_set;
2670 FOR student_unit_attempts IN (
2671 SELECT unit_cd,
2672 version_number,
2673 cal_type,
2674 ci_sequence_number,
2675 uoo_id
2676 FROM IGS_EN_SU_ATTEMPT
2677 WHERE person_id = p_person_id
2678 AND course_cd = p_course_cd
2679 AND (p_param_8 IS NULL OR p_param_8 <> uoo_id)
2680 -- AND sup_unit_cd IS NULL 99/05/04 hrt
2681 ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number),
2682 cal_type,IGS_GE_NUMBER.TO_CANN(ci_sequence_number) )
2683 LOOP
2684 gv_sua_set := add_member(gv_sua_set,
2685 student_unit_attempts.unit_cd,
2686 student_unit_attempts.version_number,
2687 student_unit_attempts.cal_type,
2688 student_unit_attempts.ci_sequence_number,
2689 student_unit_attempts.uoo_id);
2690 END LOOP;
2691 RETURN gv_sua_set;
2692 END student;
2693
2694 /*
2695 Student Admission test type sets.
2696 */
2697 FUNCTION test_grade (cp_c_grade IN VARCHAR2) RETURN BINARY_INTEGER IS
2698 --Gets the grades assigned to the student (person_id) for the admission test (cp_c_grade)
2699 CURSOR c_tst_grade (cp_n_person_id IN NUMBER, cp_c_grade IN VARCHAR2) IS
2700 SELECT DISTINCT B.NAME GRADE
2701 FROM IGS_AD_TEST_RESULTS A,
2702 IGS_AD_CODE_CLASSES B
2703 WHERE A.PERSON_ID = cp_n_person_id
2704 AND A.ADMISSION_TEST_TYPE = cp_c_grade
2705 AND A.GRADE_ID IS NOT NULL
2706 AND A.ACTIVE_IND = 'Y'
2707 AND A.GRADE_ID = B.CODE_ID ;
2708 BEGIN
2709 -- if the grade set is already defined then return grade set number;
2710 IF gv_grade_set IS NOT NULL THEN
2711 RETURN gv_grade_set;
2712 END IF;
2713 -- creates a new set.
2714 gv_grade_set := new_set;
2715
2716 FOR rec_tst_grade IN c_tst_grade(p_person_id,cp_c_grade) LOOP
2717 -- Adds the grade values to structure f1 value.
2718 gv_grade_set := add_member(gv_grade_set,
2719 rec_tst_grade.grade,
2720 '','','','');
2721 END LOOP;
2722 -- Return grades set number
2723 RETURN gv_grade_set;
2724 END test_grade;
2725
2726 /*
2727 test if LHS grade exists in the grading set
2728 */
2729 FUNCTION not_in_grd (
2730 p_set_number IN BINARY_INTEGER,
2731 p_lhs VARCHAR2 )
2732 RETURN VARCHAR2 IS
2733 v_member BINARY_INTEGER;
2734 BEGIN
2735 -- get the first member in the set.
2736 v_member := gv_set(p_set_number).first;
2737 LOOP
2738 /*
2739 check if last (must do check for null)
2740 */
2741 IF v_member IS NULL
2742 THEN
2743 /*
2744 yep, last member
2745 */
2746 EXIT;
2747 END IF;
2748 -- check if the LHS grade is not equal to student grade in the set then return true
2749 IF p_lhs <> gv_member(v_member).f1
2750 THEN
2751 RETURN 'true';
2752 END IF;
2753 v_member := gv_member(v_member).next;
2754 END LOOP;
2755 RETURN 'false';
2756 END not_in_grd;
2757
2758
2759 /*
2760 Select advanced standing units
2761 */
2762
2763 -- Added distinct to the query which gets advanced standing unit level so that the same records
2764 -- are not processed later.
2765 FUNCTION advanced_standing
2766 RETURN BINARY_INTEGER IS
2767 v_cal_type IGS_CA_TYPE.cal_type%TYPE;
2768 v_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
2769 BEGIN
2770 IF gv_asu_set IS NOT NULL
2771 THEN
2772 RETURN gv_asu_set;
2773 END IF;
2774 gv_asu_set := new_set;
2775 /*
2776 get ONE earliest calander for student to use in advanced standing set
2777 */
2778 FOR ASU IN (
2779 SELECT DISTINCT unit_cd, version_number
2780 FROM IGS_AV_STND_UNIT
2781 WHERE person_id = p_person_id
2782 AND as_course_cd = p_course_cd
2783 AND s_adv_stnd_granting_status IN ('APPROVED','GRANTED')
2784 AND s_adv_stnd_recognition_type = 'CREDIT'
2785 AND (igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,
2786 unit_cd,version_number,'BOTH',NULL) ='TRUE')
2787 ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
2788 LOOP
2789 /*
2790 get ONE earliest calander for student to use in advanced standing set
2791 modified this logic to be based on program offering options instead of unit attempts,bug#4283221
2792 */
2793 FOR cur_ec IN (
2794 SELECT a.cal_type,
2795 a.ci_sequence_number,
2796 ci.start_dt
2797 FROM IGS_PS_UNIT_OFR_OPT A, igs_ca_inst ci
2798 WHERE a.unit_cd = asu.unit_cd
2799 AND a.version_number = asu.version_number
2800 AND a.cal_type = ci.cal_type
2801 AND a.ci_sequence_number = ci.sequence_number
2802 ORDER BY ci.start_dt )
2803 LOOP
2804 v_cal_type := cur_ec.cal_type;
2805 v_ci_sequence_number := cur_ec.ci_sequence_number;
2806 EXIT; /* only get first (earliest) */
2807 END LOOP;
2808
2809 gv_asu_set := add_member(gv_asu_set,
2810 ASU.unit_cd,
2811 ASU.version_number,
2812 v_cal_type,
2813 v_ci_sequence_number,'');
2814 END LOOP;
2815 RETURN gv_asu_set;
2816 END advanced_standing;
2817 /*
2818 Select advanced standing unit level set
2819 */
2820
2821 -- Added distinct to the query which gets advanced standing unit level so that the same records
2822 -- are not processed later.
2823 FUNCTION advanced_standing_unit_level
2824 RETURN BINARY_INTEGER IS
2825 BEGIN
2826 IF gv_asul_set IS NOT NULL
2827 THEN
2828 RETURN gv_asul_set;
2829 END IF;
2830 gv_asul_set := new_set;
2831 FOR ASUL IN (
2832 SELECT DISTINCT s_adv_stnd_type, unit_level, crs_group_ind, exemption_institution_cd
2833 FROM IGS_AV_STND_UNIT_LVL
2834 WHERE person_id = p_person_id
2835 AND as_course_cd = p_course_cd
2836 AND s_adv_stnd_granting_status IN ('GRANTED')
2837 ORDER BY s_adv_stnd_type,unit_level,crs_group_ind,exemption_institution_cd )
2838 LOOP
2839 gv_asul_set := add_member(gv_asul_set,
2840 ASUL.s_adv_stnd_type, /* VARCHAR2(10) */
2841 ASUL.unit_level, /* VARCHAR2(10) */
2842 ASUL.crs_group_ind, /* VARCHAR2(10) */
2843 ASUL.exemption_institution_cd, /* VARCHAR2(10) */
2844 ''); /* VARCHAR2(10) */
2845 END LOOP;
2846 RETURN gv_asul_set;
2847 END advanced_standing_unit_level;
2848 /*
2849 student unit sets
2850 */
2851 FUNCTION student_us
2852 RETURN BINARY_INTEGER IS
2853 BEGIN
2854 IF gv_susa_set IS NOT NULL
2855 THEN
2856 RETURN gv_susa_set;
2857 END IF;
2858 gv_susa_set := new_set;
2859 FOR SUSA IN (
2860 SELECT unit_set_cd,
2861 us_version_number,
2862 sequence_number
2863 FROM IGS_AS_SU_SETATMPT
2864 WHERE person_id = p_person_id
2865 AND course_cd = p_course_cd
2866 ORDER BY unit_set_cd,us_version_number,sequence_number )
2867 LOOP
2868 gv_susa_set := add_member(gv_susa_set,
2869 SUSA.unit_set_cd,
2870 SUSA.us_version_number,
2871 SUSA.sequence_number,
2872 '','');
2873 END LOOP;
2874 RETURN gv_susa_set;
2875 END student_us;
2876 /*
2877 evaluate the sql string and return the set number
2878 */
2879 FUNCTION make_set (
2880 p_select_string IN VARCHAR2 )
2881 RETURN BINARY_INTEGER IS
2882 v_cursor INTEGER;
2883 v_rows INTEGER;
2884 v_f1 VARCHAR2(10);
2885 v_f2 VARCHAR2(10);
2886 v_f3 VARCHAR2(10);
2887 v_f4 VARCHAR2(10);
2888 v_set1 BINARY_INTEGER;
2889 BEGIN
2890 v_cursor := DBMS_SQL.OPEN_CURSOR;
2891 /*
2892 only select allowed
2893 */
2894 DBMS_SQL.PARSE(v_cursor,
2895 'SELECT'||SUBSTR(LTRIM(p_select_string),7),
2896 dbms_sql.v7);
2897 DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_f1,10);
2898 DBMS_SQL.DEFINE_COLUMN(v_cursor,2,v_f2,10);
2899 DBMS_SQL.DEFINE_COLUMN(v_cursor,3,v_f3,10);
2900 DBMS_SQL.DEFINE_COLUMN(v_cursor,4,v_f4,10);
2901 v_rows := DBMS_SQL.EXECUTE(v_cursor);
2902 v_set1 := new_set;
2903 WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
2904 LOOP
2905 DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_f1);
2906 DBMS_SQL.COLUMN_VALUE(v_cursor,2,v_f2);
2907 DBMS_SQL.COLUMN_VALUE(v_cursor,3,v_f3);
2908 DBMS_SQL.COLUMN_VALUE(v_cursor,4,v_f4);
2909 -- passing null string for f5, as the make_set rule is not used in the context of unit attempt
2910 v_set1 := add_member(v_set1,v_f1,v_f2,v_f3,v_f4,'');
2911 END LOOP;
2912 DBMS_SQL.CLOSE_CURSOR(v_cursor);
2913 RETURN v_set1;
2914 END make_set;
2915 /*
2916 if version number exists in version string
2917 version string form, 1-5,8,10
2918 version 10 and beyond is represented as 10-
2919 all versions upto 10 is represented as -10
2920 */
2921 FUNCTION in_versions(
2922 p_version_number IN NUMBER,
2923 p_versions IN VARCHAR )
2924 RETURN BOOLEAN IS
2925 v_prev_comma NUMBER := 1; /* previous position of comma in string */
2926 v_curr_comma NUMBER; /* current position of comma in string */
2927 v_sub_str VARCHAR2(30); /* sub-string between comma's */
2928 v_dash NUMBER; /* position of '-' in sub-string */
2929 v_lower NUMBER;
2930 v_upper NUMBER;
2931 BEGIN
2932 IF p_versions IS null
2933 THEN
2934 RETURN (TRUE);
2935 END IF;
2936 LOOP
2937 /*
2938 find next comma (if exists)
2939 */
2940 v_curr_comma := INSTR(p_versions, ',', v_prev_comma);
2941 IF v_curr_comma = 0
2942 THEN
2943 /*
2944 all/rest of string
2945 */
2946 v_sub_str := SUBSTR(p_versions, v_prev_comma);
2947 ELSE
2948 /*
2949 part of string, previous comma to this comma
2950 */
2951 v_sub_str := SUBSTR(p_versions, v_prev_comma, v_curr_comma - v_prev_comma);
2952 END IF;
2953 v_prev_comma := v_curr_comma + 1;
2954 v_dash := INSTR(v_sub_str, '-');
2955 IF v_dash = 0
2956 THEN
2957 v_lower := IGS_GE_NUMBER.TO_NUM(v_sub_str);
2958 v_upper := v_lower;
2959 ELSE
2960 /*
2961 dash in sub-string, split lower and upper
2962 */
2963 v_lower := IGS_GE_NUMBER.TO_NUM(SUBSTR(v_sub_str, 1, v_dash - 1));
2964 v_upper := IGS_GE_NUMBER.TO_NUM(SUBSTR(v_sub_str, v_dash + 1));
2965 END IF;
2966 IF (p_version_number >= v_lower OR v_lower IS NULL) AND
2967 (p_version_number <= v_upper OR v_upper IS NULL)
2968 THEN
2969 RETURN (TRUE);
2970 END IF;
2971 IF v_curr_comma = 0
2972 THEN
2973 RETURN (FALSE);
2974 END IF;
2975 END LOOP;
2976 END in_versions;
2977
2978 /******** this is for the reference set *************/
2979
2980 FUNCTION ref_set(p_set1 IN BINARY_INTEGER) RETURN VARCHAR2 IS
2981 ------------------------------------------------------------------
2982 --Created by : Sanil Madathil, Oracle IDC
2983 --Date created: 07-JUN-2001
2984 --
2985 --Purpose: As per enhancement bug no.1775394 . This function does the
2986 -- evaluation of rules set for Person reference Types
2987 --
2988 --
2989 --
2990 --Known limitations/enhancements and/or remarks:
2991 --
2992 --Change History:
2993 --Who When What
2994 --kdande 25-Nov-2002 Removed entire code from the function and
2995 -- replaced it with true since the view
2996 -- igs_pe_person_refs_v is obsolete
2997 -------------------------------------------------------------------
2998 BEGIN
2999 RETURN 'true';
3000 END ref_set ; /** end of function ref_set **/
3001
3002 /******** this is for the person id group *************/
3003
3004 FUNCTION perid_chk(p_set1 IN BINARY_INTEGER) RETURN VARCHAR2 IS
3005 ------------------------------------------------------------------
3006 --Created by : Sanil Madathil, Oracle IDC
3007 --Date created: 07-JUN-2001
3008 --
3009 --Purpose: As per enhancement bug no.1775394 . This function does the
3010 -- evaluation of rules set for Person Id group
3011 --
3012 --
3013 --
3014 --
3015 --Known limitations/enhancements and/or remarks:
3016 --
3017 --Change History:
3018 --Who When What
3019 -------------------------------------------------------------------
3020 l_member BINARY_INTEGER ;
3021 l_result VARCHAR2(100) ;
3022 l_tds r_tds ;
3023
3024 CURSOR c_persid IS
3025 SELECT peg.group_cd
3026 FROM igs_pe_persid_group peg,igs_pe_prsid_grp_mem pegm
3027 WHERE peg.group_id = pegm.group_id
3028 AND pegm.person_id = p_person_id ;
3029
3030 BEGIN
3031 l_result := 'false' ; -- set the return result as false
3032 l_member := gv_set(p_set1).FIRST ; -- get the first member defined as part of the rule set
3033 LOOP -- loop thru all the members and exit when there are no records
3034 IF l_member IS NULL THEN
3035 EXIT ;
3036 END IF;
3037 l_tds.member_index := l_member ; -- get the index for pl/sql table
3038 l_tds.unit_cd := gv_member(l_member).f1 ; -- get the value of code defined in rules in local variable
3039 FOR rec_reference IN c_persid
3040 LOOP
3041 IF rec_reference.group_cd = l_tds.unit_cd THEN -- if match is found then rule is evaluated to true else move to next reference code for the student
3042 RETURN 'true';
3043 END IF;
3044 END LOOP ;
3045 l_member := gv_member(l_member).NEXT ;
3046 END LOOP ;
3047 RETURN l_result ;
3048 END perid_chk ; /** end of function perid_chk **/
3049
3050 /******** this is for the unit placement set *************/
3051
3052 FUNCTION plc_chk(p_set1 IN BINARY_INTEGER) RETURN VARCHAR2 IS
3053 ------------------------------------------------------------------
3054 --Created by : Sanil Madathil, Oracle IDC
3055 --Date created: 07-JUN-2001
3056 --
3057 --Purpose: As per enhancement bug no.1775394 . This function does the
3058 -- evaluation of rules set for Unit placement set
3059 --
3060 --
3061 --
3062 --
3063 --Known limitations/enhancements and/or remarks:
3064 --
3065 --Change History:
3066 --Who When What
3067 --kkillams 31-10-2003 Modified the business validation as per
3068 -- new requirements w.r.t. bug 3111609
3069 -------------------------------------------------------------------
3070 l_member BINARY_INTEGER ;
3071 l_result VARCHAR2(100) ;
3072 l_found BOOLEAN;
3073 l_tds r_tds ;
3074
3075 CURSOR c_plc_chk IS
3076 SELECT d.unit_cd
3077 FROM igs_ad_up_detail d
3078 WHERE EXISTS (
3079 SELECT 'x'
3080 FROM igs_ad_test_results atr,
3081 igs_ad_up_header h
3082 WHERE atr.person_id = p_person_id
3083 AND atr.admission_test_type = h.admission_test_type
3084 AND h.up_header_id = d.up_header_id);
3085 PROCEDURE val_placement (pp_unit_cd IN IGS_PS_UNIT_VER.UNIT_CD%TYPE,
3086 pp_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE ,
3087 pp_found OUT NOCOPY BOOLEAN)IS
3088
3089 l_dummy VARCHAR2(1);
3090
3091 --Gets the unit placement details
3092 CURSOR cur_st_test(cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE) IS
3093 SELECT admission_test_type,
3094 test_segment_id,
3095 definition_level,
3096 min_score,
3097 max_score
3098 FROM igs_ad_up_detail det,
3099 igs_ad_up_header hed
3100 WHERE det.up_header_id = hed.up_header_id
3101 AND det.unit_cd = cp_unit_cd
3102 AND det.closed_ind ='N';
3103 CURSOR cur_test(cp_person_id igs_pe_person.person_id%TYPE,
3104 cp_admission_test_type igs_ad_up_header.admission_test_type%TYPE,
3105 cp_min_score igs_ad_up_header.min_score%TYPE,
3106 cp_max_score igs_ad_up_header.max_score%TYPE ) IS
3107 SELECT '1' FROM IGS_AD_TEST_RESULTS
3108 WHERE person_id = cp_person_id
3109 AND admission_test_type = cp_admission_test_type
3110 AND NVL(comp_test_score,0) BETWEEN cp_min_score AND cp_max_score;
3111 --
3112 CURSOR cur_seg (cp_person_id igs_pe_person.person_id%TYPE,
3113 cp_admission_test_type igs_ad_up_header.admission_test_type%TYPE,
3114 cp_test_segment_id igs_ad_up_header.test_segment_id%TYPE,
3115 cp_min_score igs_ad_up_header.min_score%TYPE,
3116 cp_max_score igs_ad_up_header.max_score%TYPE ) IS
3117 SELECT '1' FROM IGS_AD_TEST_RESULTS TST,
3118 IGS_AD_TST_RSLT_DTLS TDTL
3119 WHERE tst.person_id = cp_person_id
3120 AND tst.admission_test_type = cp_admission_test_type
3121 AND tst.test_results_id = tdtl.test_results_id
3122 AND tdtl.test_segment_id = cp_test_segment_id
3123 AND tdtl.test_score BETWEEN cp_min_score AND cp_max_score;
3124 BEGIN
3125 l_found :=FALSE;
3126 FOR rec_st_test IN cur_st_test(pp_unit_cd)
3127 LOOP
3128 --Segment level verification
3129 IF rec_st_test.definition_level ='S' THEN
3130 OPEN cur_seg(pp_person_id,rec_st_test.admission_test_type, rec_st_test.test_segment_id,rec_st_test.min_score,rec_st_test.max_score);
3131 FETCH cur_seg INTO l_dummy;
3132 IF cur_seg%FOUND THEN
3133 pp_found := TRUE;
3134 CLOSE cur_seg;
3135 EXIT;
3136 END IF;
3137 CLOSE cur_seg;
3138 --Test type level verification
3139 ELSIF rec_st_test.definition_level ='T' THEN
3140 OPEN cur_test(pp_person_id,rec_st_test.admission_test_type,rec_st_test.min_score,rec_st_test.max_score);
3141 FETCH cur_test INTO l_dummy;
3142 IF cur_test%FOUND THEN
3143 pp_found := TRUE;
3144 CLOSE cur_test;
3145 EXIT;
3146 END IF;
3147 CLOSE cur_test;
3148 END IF;
3149 END LOOP;
3150 END val_placement;
3151 BEGIN
3152 l_result := 'false' ; -- set the return result as false
3153 l_member := gv_set(p_set1).FIRST ; -- get the first member defined as part of the rule set
3154 LOOP -- loop thru all the members and exit when there are no records
3155 IF l_member IS NULL THEN
3156 EXIT ;
3157 END IF;
3158 l_tds.member_index := l_member ; -- get the index for pl/sql table
3159 l_tds.unit_cd := gv_member(l_member).f1 ; -- get the value of code defined in rules in local variable
3160 val_placement(l_tds.unit_cd,p_person_id,l_found);
3161 IF l_found THEN
3162 RETURN 'true';
3163 END IF;
3164 l_member := gv_member(l_member).NEXT ;
3165 END LOOP ;
3166 RETURN l_result ;
3167 END plc_chk ; /** end of function plc_chk **/
3168
3169 /******** this is for the program stage completion evaluation *************/
3170
3171 FUNCTION stg_set(p_set1 IN BINARY_INTEGER) RETURN VARCHAR2 IS
3172 ------------------------------------------------------------------
3173 --Created by : Sanil Madathil, Oracle IDC
3174 --Date created: 07-JUN-2001
3175 --
3176 --Purpose: As per enhancement bug no.1775394 . This function does the
3177 -- evaluation of rules set for Program Stage completion
3178 --
3179 --
3180 --
3181 --
3182 --Known limitations/enhancements and/or remarks:
3183 --
3184 --Change History:
3185 --Who When What
3186 --sepalani 4/27/2006 Bug 5076203.
3187 -------------------------------------------------------------------
3188 l_member BINARY_INTEGER ;
3189 l_result VARCHAR2(100) ;
3190 l_tds r_tds ;
3191
3192 CURSOR c_igs_ps_stage(lc_course_cd igs_ps_stage.course_cd%TYPE,
3193 lc_version_number igs_ps_stage.version_number%TYPE,
3194 lc_course_stage_type igs_ps_stage.course_stage_type%TYPE) IS
3195 SELECT cst.sequence_number
3196 FROM igs_ps_stage cst
3197 WHERE cst.course_cd = lc_course_cd
3198 AND cst.version_number = lc_version_number
3199 AND cst.course_stage_type = lc_course_stage_type ;
3200
3201 l_cst_sequence_number igs_ps_stage.sequence_number%TYPE ;
3202
3203 v_rule_text VARCHAR2(2000);
3204
3205 CURSOR c_csr (lc_course_cd igs_ps_stage.course_cd%TYPE,
3206 lc_version_number igs_ps_stage.version_number%TYPE,
3207 lc_cst_sequence_number igs_ps_stage.sequence_number%TYPE,
3208 lc_course_stage_type igs_ps_stage.course_stage_type%TYPE) IS
3209 SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
3210 FROM igs_ps_stage_ru
3211 WHERE course_cd = lc_course_cd
3212 AND version_number = lc_version_number
3213 AND cst_sequence_number = lc_cst_sequence_number
3214 AND s_rule_call_cd = lc_course_stage_type;
3215
3216 BEGIN
3217 l_result := 'true'; -- set the return result as true
3218 l_member := gv_set(p_set1).FIRST ; -- get the first member defined as part of the rule set
3219 LOOP -- loop thru all the members and exit when there are no records
3220 IF l_member IS NULL THEN
3221 EXIT ;
3222 END IF;
3223 l_tds.member_index := l_member ; -- get the index for pl/sql table
3224 l_tds.unit_cd := gv_member(l_member).f1 ; -- get the value of code defined in rules in local variable
3225
3226 -- Checking if the Stage is attached to the Program
3227 OPEN c_igs_ps_stage(p_course_cd, p_course_version,l_tds.unit_cd);
3228 FETCH c_igs_ps_stage INTO l_cst_sequence_number;
3229 IF c_igs_ps_stage%NOTFOUND THEN
3230 CLOSE c_igs_ps_stage;
3231 RETURN 'false';
3232 END IF;
3233 CLOSE c_igs_ps_stage;
3234
3235 -- Checking if the Stage attached to the Program has a Rule
3236 OPEN c_csr(p_course_cd, p_course_version,l_cst_sequence_number,'STG-COMP');
3237 FETCH c_csr INTO v_rule_text;
3238 IF c_csr%NOTFOUND THEN
3239 CLOSE c_csr;
3240 RETURN 'false';
3241 END IF;
3242 CLOSE c_csr;
3243
3244 --Checking Rule Status
3245 IF IGS_PR_GEN_002.PRGP_GET_STG_COMP(p_person_id => p_person_id,
3246 p_course_cd => p_course_cd,
3247 p_crv_version_number => p_course_version,
3248 p_course_stage_type => l_cst_sequence_number) = 'N' THEN
3249 l_result := 'false';
3250 END IF;
3251 l_member := gv_member(l_member).NEXT ;
3252
3253 END LOOP ;
3254 RETURN l_result ;
3255 END stg_set ; /** end of function stg_set **/
3256
3257
3258 FUNCTION rulp_get_alwd_cp(
3259 p_percentage IN NUMBER,
3260 p_course_cd IN VARCHAR2,
3261 p_course_version IN NUMBER )
3262 RETURN NUMBER IS
3263 ------------------------------------------------------------------
3264 --Created by : Nalin Kumar, Oracle IDC
3265 --Date created: 18-JUN-2001
3266 --
3267 --Purpose: As per enhancement Bug# 1830175.
3268 -- Function rulp_get_alwd_cp added as per the requirement of
3269 -- Enrolment eligibility and Validations DLD (For form IGSEN058).
3270 --
3271 --
3272 --
3273 --Known limitations/enhancements and/or remarks:
3274 --
3275 --Change History:
3276 --Who When What
3277 -------------------------------------------------------------------
3278 CURSOR cur_c1 (
3279 p_course_cd VARCHAR2,
3280 p_course_version NUMBER )IS
3281 SELECT max_cp_per_teaching_period
3282 FROM IGS_PS_VER
3283 WHERE course_cd = p_course_cd AND
3284 version_number = p_course_version;
3285
3286 l_max_cp IGS_PS_VER.max_cp_per_teaching_period%Type;
3287
3288 BEGIN
3289
3290 OPEN cur_c1(p_course_cd,
3291 p_course_version);
3292 FETCH cur_c1 INTO l_max_cp;
3293 CLOSE cur_c1;
3294 RETURN l_max_cp * (p_percentage/100);
3295
3296 END rulp_get_alwd_cp; /* End of rulp_get_alwd_cp function. */
3297
3298
3299 /*
3300 Expand a set member to all version_number/cal_type/ci_sequence_number
3301 */
3302 FUNCTION expand_uoo(
3303 p_set1 IN BINARY_INTEGER,
3304 p_unit_cd IN VARCHAR2,
3305 p_versions IN VARCHAR2 )
3306 RETURN BINARY_INTEGER IS /* set number */
3307 v_set1 BINARY_INTEGER;
3308 BEGIN
3309 /*
3310 order by char because add_member expects char order
3311 */
3312 FOR uv IN (
3313 SELECT unit_cd,
3314 version_number
3315 FROM IGS_PS_UNIT_VER
3316 WHERE unit_cd LIKE (p_unit_cd)
3317 ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
3318 LOOP
3319 IF in_versions(uv.version_number,
3320 p_versions) = TRUE
3321 THEN
3322 IF p_person_id IS NULL OR p_rule_call_name IN ('PREREQ','COREQ')
3323 THEN
3324 /*
3325 no student, no cal_type
3326 */
3327 v_set1 := add_member(p_set1,
3328 uv.unit_cd,
3329 uv.version_number,
3330 '','','');
3331 ELSE
3332 /*
3333 this assumes a ENROLLED student has units/student has advance standing in this unit
3334 removed the check with unit attempts for bug#4283221,
3335 because advance standing units needn't depend on student unit attempt teaching calendar
3336 */
3337 /*
3338 this loop is added as part of MUS build bug 2829262
3339 it will fetch all unit sections under given unit and the teach cals.
3340 */
3341 FOR uoo IN (
3342 SELECT unt_oo.cal_type,unt_oo.ci_sequence_number,unt_oo.uoo_id
3343 FROM IGS_PS_UNIT_OFR_OPT unt_oo
3344 WHERE unt_oo.unit_cd = uv.unit_cd AND
3345 unt_oo.version_number = uv.version_number
3346 ORDER BY unt_oo.cal_type, IGS_GE_NUMBER.TO_CANN(unt_oo.ci_sequence_number)
3347 )
3348 LOOP
3349 v_set1 := add_member(p_set1,
3350 uv.unit_cd,
3351 uv.version_number,
3352 uoo.cal_type,
3353 uoo.ci_sequence_number,
3354 uoo.uoo_id);
3355 END LOOP;/* uoo */
3356
3357 END IF;
3358 END IF;
3359 END LOOP;
3360 RETURN p_set1;
3361 END expand_uoo;
3362 /*
3363 Get SNARTS (inverse of TRANS sets) and expand uoo
3364 The TRANS rule MUST BE a plain vanila type SET
3365 */
3366 FUNCTION get_snarts (
3367 p_unit_cd IN VARCHAR2,
3368 p_unit_version IN NUMBER )
3369 RETURN BINARY_INTEGER IS /* set number */
3370 v_set1 BINARY_INTEGER;
3371 BEGIN
3372 v_set1 := new_set;
3373 FOR SNART IN (
3374 SELECT rsm.unit_cd rms_unit_cd,
3375 rsm.versions rms_versions,
3376 uvr.unit_cd uvr_unit_cd,
3377 uvr.version_number uvr_version_number
3378 FROM IGS_PS_UNIT_VER_RU uvr,
3379 igs_ru_item rui,
3380 igs_ru_set_member rsm
3381 WHERE rsm.unit_cd = p_unit_cd
3382 AND uvr.s_rule_call_cd = 'TRANS'
3383 AND rui.rul_sequence_number = uvr.rul_sequence_number
3384 AND rsm.rs_sequence_number = rui.set_number
3385 ORDER BY uvr.unit_cd,IGS_GE_NUMBER.TO_CANN(uvr.version_number) )
3386 LOOP
3387 IF in_versions(p_unit_version,SNART.rms_versions)
3388 THEN
3389 FOR UOO IN (
3390 SELECT UNIQUE
3391 unit_cd,
3392 version_number,
3393 cal_type,
3394 ci_sequence_number,
3395 uoo_id
3396 FROM IGS_PS_UNIT_OFR_OPT
3397 WHERE unit_cd = SNART.uvr_unit_cd
3398 AND version_number = SNART.uvr_version_number
3399 ORDER BY unit_cd,IGS_GE_NUMBER.TO_CANN(version_number),
3400 cal_type,IGS_GE_NUMBER.TO_CANN(ci_sequence_number) )
3401 LOOP
3402 v_set1 := add_member(v_set1,
3403 UOO.unit_cd,
3404 UOO.version_number,
3405 UOO.cal_type,
3406 UOO.ci_sequence_number,
3407 UOO.uoo_id);
3408 END LOOP;
3409 END IF;
3410 END LOOP;
3411 RETURN v_set1;
3412 END get_snarts;
3413 /*
3414 Expand rule set to work set for a set of units
3415 */
3416 FUNCTION expand_unit_set(
3417 p_set1 IN NUMBER )
3418 RETURN NUMBER IS
3419 v_set2 BINARY_INTEGER;
3420 BEGIN
3421 v_set2 := new_set;
3422 FOR set_members IN (
3423 SELECT unit_cd,
3424 versions
3425 FROM IGS_RU_SET_MEMBER
3426 WHERE rs_sequence_number = p_set1
3427 ORDER BY unit_cd )
3428 LOOP
3429 /*
3430 expand to all unit offering options (version and cal)
3431 */
3432 v_set2 := expand_uoo(v_set2,
3433 set_members.unit_cd,
3434 set_members.versions);
3435 END LOOP;
3436 RETURN v_set2;
3437 END expand_unit_set;
3438 /*
3439 Expand rule set to work set for a set of unit sets
3440 */
3441 FUNCTION expand_us_set(
3442 p_set1 IN NUMBER )
3443 RETURN BINARY_INTEGER IS
3444 v_set2 BINARY_INTEGER;
3445 BEGIN
3446 v_set2 := new_set;
3447 FOR set_members IN (
3448 SELECT unit_cd,
3449 versions
3450 FROM IGS_RU_SET_MEMBER
3451 WHERE rs_sequence_number = p_set1
3452 ORDER BY unit_cd )
3453 LOOP
3454 FOR US IN (
3455 SELECT unit_set_cd,
3456 version_number
3457 FROM IGS_EN_UNIT_SET
3458 WHERE unit_set_cd LIKE (set_members.unit_cd)
3459 ORDER BY unit_set_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
3460 LOOP
3461 IF in_versions(US.version_number,
3462 set_members.versions) = TRUE
3463 THEN
3464 /*
3465 has big potential for error (sequence_number) hrt
3466 */
3467 v_set2 := add_member(v_set2,
3468 US.unit_set_cd,
3469 US.version_number,
3470 '','','');
3471 END IF;
3472 END LOOP;
3473 END LOOP;
3474 RETURN v_set2;
3475 END expand_us_set;
3476 /*
3477 Expand rule set to work set for a set of courses
3478 */
3479 FUNCTION expand_crs_set(
3480 p_set1 IN NUMBER )
3481 RETURN BINARY_INTEGER IS
3482 v_set2 BINARY_INTEGER;
3483 BEGIN
3484 v_set2 := new_set;
3485 FOR set_members IN (
3486 SELECT unit_cd,
3487 versions
3488 FROM IGS_RU_SET_MEMBER
3489 WHERE rs_sequence_number = p_set1
3490 ORDER BY unit_cd )
3491 LOOP
3492 FOR CRS IN (
3493 SELECT course_cd,
3494 version_number
3495 FROM IGS_PS_VER
3496 WHERE course_cd LIKE (set_members.unit_cd)
3497 ORDER BY course_cd,IGS_GE_NUMBER.TO_CANN(version_number) )
3498 LOOP
3499 IF in_versions(CRS.version_number,
3500 set_members.versions) = TRUE
3501 THEN
3502 v_set2 := add_member(v_set2,
3503 CRS.course_cd,
3504 CRS.version_number,
3505 '','','');
3506 END IF;
3507 END LOOP;
3508 END LOOP;
3509 RETURN v_set2;
3510 END expand_crs_set;
3511
3512 FUNCTION expand_gsch_set(
3513 p_set1 IN NUMBER )
3514 RETURN NUMBER IS
3515 /**********************************************************************************************
3516 Created By : rbezawad
3517 Date Created By : 30-Nov-2001
3518 Purpose : To add members(i.e., [Grading_Schema_Code.Grade] pairs ) for the Grading Schema Code.
3519 Created as part of Progression Rules Enhancement Build, Bug No: 2146547.
3520
3521 Known limitations,enhancements,remarks:
3522 Change History
3523 Who When What
3524 *************************************************************************************************/
3525
3526 -- Get the Grading Schema Code given in the Rule Definition
3527 CURSOR cur_rul_set_member IS
3528 SELECT unit_cd
3529 FROM igs_ru_set_member
3530 WHERE rs_sequence_number = p_set1
3531 ORDER BY unit_cd ;
3532
3533 -- Get All the Grading Schema Code, Grade pairs for the Grading Schema Code given in Rule Definition.
3534 /* added DISTINCT to select query as the rule 34420 has DISTINCT in its query */
3535
3536 CURSOR cur_gscd_set_members(cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
3537 cp_grade igs_as_grd_sch_grade.grade%TYPE) IS
3538 SELECT DISTINCT grading_schema_cd, grade
3539 FROM igs_as_grd_sch_grade
3540 WHERE grading_schema_cd = cp_grading_schema_cd
3541 AND grade = cp_grade
3542 ORDER BY grading_schema_cd;
3543
3544 l_set2 BINARY_INTEGER;
3545 l_unit_cd igs_ru_set_member.unit_cd%TYPE;
3546
3547 BEGIN
3548
3549 l_set2 := new_set;
3550
3551 -- To Get the Grading Schema Code
3552 FOR rec_rul_set_member IN cur_rul_set_member LOOP
3553
3554 -- Get All the Grading Schema Code, Grade pairs
3555 -- pass segregated value of grading schema from unit_cd
3556 FOR l_gscd_set_members_rec IN cur_gscd_set_members(
3557 SUBSTR(rec_rul_set_member.unit_cd,1,instr(rec_rul_set_member.unit_cd,'/')-1),
3558 SUBSTR(rec_rul_set_member.unit_cd,INSTR(rec_rul_set_member.unit_cd,'/')+1 )
3559 ) LOOP
3560 -- Add the Grading Schema Member to the Set.
3561 l_set2 := add_member(l_set2,
3562 l_gscd_set_members_rec.grading_schema_cd,
3563 l_gscd_set_members_rec.grade,'','','');
3564 END LOOP;
3565 END LOOP;
3566 RETURN l_set2;
3567
3568 END expand_gsch_set;
3569
3570
3571 /*
3572 Expand rule set to work set according to type
3573 */
3574 FUNCTION expand_set(
3575 p_set1 IN NUMBER )
3576 RETURN NUMBER IS
3577 /**********************************************************************************************
3578 Created By :
3579 Date Created By :
3580 Purpose : To add set Members
3581
3582 Known limitations,enhancements,remarks:
3583 Change History
3584 Who When What
3585 rbezawad 30-Nov-2001 Added Condition to check for Set Type GSCH_SET and call expand_gsch_set() function.
3586 Modifications done W.r.t Progression Rules Enhancement Build, Bug No: 2146547.
3587 *************************************************************************************************/
3588
3589 v_set_type IGS_RU_SET.set_type%TYPE;
3590 v_set2 BINARY_INTEGER;
3591 BEGIN
3592 SELECT set_type
3593 INTO v_set_type
3594 FROM IGS_RU_SET
3595 WHERE sequence_number = p_set1;
3596 IF v_set_type = 'UNIT_SET'
3597 THEN
3598 RETURN expand_unit_set(p_set1);
3599 ELSIF v_set_type = 'US_SET'
3600 THEN
3601 RETURN expand_us_set(p_set1);
3602 ELSIF v_set_type = 'CC_SET'
3603 THEN
3604 RETURN expand_crs_set(p_set1);
3605 ELSIF v_set_type = 'GSCH_SET' THEN
3606 RETURN expand_gsch_set(p_set1);
3607 ELSE
3608 /*
3609 all other sets (generic) just copy across to work set
3610 */
3611 v_set2 := new_set;
3612 FOR set_members IN (
3613 SELECT unit_cd
3614 FROM IGS_RU_SET_MEMBER
3615 WHERE rs_sequence_number = p_set1
3616 ORDER BY unit_cd )
3617 LOOP
3618 v_set2 := add_member(v_set2,
3619 set_members.unit_cd
3620 ,'','','','');
3621 END LOOP;
3622 RETURN v_set2;
3623 END IF;
3624 END expand_set;
3625 /*
3626 LOOPING FUNCTIONS
3627
3628 Select members which satisfy the attribute rule criteria
3629 */
3630 FUNCTION select_set(
3631 p_set1 BINARY_INTEGER,
3632 p_where_rule NUMBER )
3633 RETURN BINARY_INTEGER IS
3634 v_member BINARY_INTEGER;
3635 v_set2 BINARY_INTEGER;
3636 v_result IGS_RU_ITEM.value%TYPE;
3637 v_tds r_tds;
3638 BEGIN
3639 /*
3640 populate structure, target set number
3641 */
3642 v_tds.set_number := p_set1;
3643 v_set2 := new_set;
3644 v_member := gv_set(p_set1).first;
3645 LOOP
3646 /*
3647 check if last (must do check for null)
3648 */
3649 IF v_member IS NULL
3650 THEN
3651 /*
3652 yep, last member
3653 */
3654 EXIT;
3655 END IF;
3656 /*
3657 populate structure, target set member
3658 */
3659 v_tds.member_index := v_member;
3660 v_tds.unit_cd := gv_member(v_member).f1;
3661 v_tds.unit_version := gv_member(v_member).f2;
3662 v_tds.cal_type := gv_member(v_member).f3;
3663 v_tds.ci_sequence_number := gv_member(v_member).f4;
3664 v_tds.uoo_id := gv_member(v_member).f5;
3665 /*
3666 evaluate attribute criteria rule
3667 */
3668 v_result := RTRIM(turing(p_where_rule,v_tds));
3669 IF v_result = 'true'
3670 THEN
3671 v_set2 := add_member(v_set2,
3672 gv_member(v_member).f1,
3673 gv_member(v_member).f2,
3674 gv_member(v_member).f3,
3675 gv_member(v_member).f4,
3676 gv_member(v_member).f5);
3677 ELSIF v_result = 'false'
3678 THEN
3679 null;
3680 ELSE
3681 log_error('select_set',
3682 'Invalid condition rule return ('||v_result||')'||Fnd_Global.Local_Chr(10)||
3683 'rule='||p_where_rule);
3684 END IF;
3685 /*
3686 next member
3687 */
3688 v_member := gv_member(v_member).next;
3689 END LOOP;
3690 RETURN v_set2;
3691 END select_set;
3692 /*
3693 Select N members from an ordered set
3694 */
3695 FUNCTION select_N_members (
3696 p_members IN NUMBER,
3697 p_set1 IN BINARY_INTEGER,
3698 p_order_by_rule IN NUMBER )
3699 RETURN BINARY_INTEGER IS /* set number */
3700 v_member BINARY_INTEGER;
3701 v_set2 BINARY_INTEGER;
3702 v_set3 BINARY_INTEGER;
3703 v_result IGS_RU_ITEM.value%TYPE;
3704 v_tds r_tds;
3705 v_count NUMBER := 0;
3706 BEGIN
3707 IF p_members >= members(p_set1)
3708 THEN
3709 /*
3710 don't bother trying
3711 */
3712 RETURN p_set1;
3713 END IF;
3714 v_set2 := new_set;
3715 v_set3 := p_set1;
3716 FOR v_ii IN 1 .. p_members /* can never be more than p_member times */
3717 LOOP
3718 /*
3719 populate structure, target set number
3720 */
3721 v_tds.set_number := v_set3;
3722 v_member := gv_set(v_set3).first;
3723 LOOP
3724 /*
3725 check if last (must do check for null)
3726 */
3727 IF v_member IS NULL
3728 THEN
3729 /*
3730 yep, last member
3731 */
3732 EXIT;
3733 END IF;
3734 /*
3735 populate structure, target set member
3736 */
3737 v_tds.member_index := v_member;
3738 v_tds.unit_cd := gv_member(v_member).f1;
3739 v_tds.unit_version := gv_member(v_member).f2;
3740 v_tds.cal_type := gv_member(v_member).f3;
3741 v_tds.ci_sequence_number := gv_member(v_member).f4;
3742 v_tds.uoo_id := gv_member(v_member).f5;
3743 /*
3744 evaluate attribute criteria rule
3745 */
3746 v_result := RTRIM(turing(p_order_by_rule,v_tds));
3747 IF v_result = 'true'
3748 THEN
3749 v_set2 := add_member(v_set2,
3750 gv_member(v_member).f1,
3751 gv_member(v_member).f2,
3752 gv_member(v_member).f3,
3753 gv_member(v_member).f4,
3754 gv_member(v_member).f5);
3755 v_count := v_count + 1;
3756 IF v_count >= p_members
3757 THEN
3758 RETURN v_set2;
3759 END IF;
3760 ELSIF v_result = 'false'
3761 THEN
3762 null;
3763 ELSE
3764 log_error('select_N_set',
3765 'Invalid condition rule return ('||v_result||')'||Fnd_Global.Local_Chr(10)||
3766 'rule='||p_order_by_rule);
3767 END IF;
3768 /*
3769 next member
3770 */
3771 v_member := gv_member(v_member).next;
3772 END LOOP;
3773 /*
3774 Remove selected members from target set
3775 */
3776 v_set3 := set_minus(v_set3,v_set2);
3777 END LOOP;
3778 RETURN v_set2;
3779 END select_N_members;
3780 /*
3781 Sum according to attribute rule
3782 */
3783 FUNCTION sum_func(
3784 p_set1 IN BINARY_INTEGER,
3785 p_attribute_rule IN NUMBER )
3786 RETURN BINARY_INTEGER IS
3787 v_member BINARY_INTEGER;
3788 v_result NUMBER;
3789 v_tds r_tds;
3790 BEGIN
3791 v_result := 0;
3792 v_member := gv_set(p_set1).first;
3793 LOOP
3794 /*
3795 check if last (must do check for null)
3796 */
3797 IF v_member IS NULL
3798 THEN
3799 /*
3800 yep, last member
3801 */
3802 EXIT;
3803 END IF;
3804 /*
3805 populate structure, target set member
3806 */
3807 v_tds.member_index := v_member;
3808 v_tds.unit_cd := gv_member(v_member).f1;
3809 v_tds.unit_version := gv_member(v_member).f2;
3810 v_tds.cal_type := gv_member(v_member).f3;
3811 v_tds.ci_sequence_number := gv_member(v_member).f4;
3812 v_tds.uoo_id := gv_member(v_member).f5;
3813 /*
3814 sum evaluated attribute rule
3815 */
3816 v_result := v_result + turing(p_attribute_rule,v_tds);
3817 /*
3818 next member
3819 */
3820 v_member := gv_member(v_member).next;
3821 END LOOP;
3822 RETURN v_result;
3823 END sum_func;
3824 /*
3825 append set2 to set1
3826 */
3827 FUNCTION append_to_set (
3828 p_set1 BINARY_INTEGER,
3829 p_set2 BINARY_INTEGER )
3830 RETURN BINARY_INTEGER IS
3831 v_member BINARY_INTEGER;
3832 v_set1 BINARY_INTEGER;
3833 BEGIN
3834 v_member := gv_set(p_set2).first;
3835 LOOP
3836 /*
3837 check if last (must do check for null)
3838 */
3839 IF v_member IS NULL
3840 THEN
3841 /*
3842 yep, last member
3843 */
3844 EXIT;
3845 END IF;
3846 v_set1 := add_member(p_set1,
3847 gv_member(v_member).f1,
3848 gv_member(v_member).f2,
3849 gv_member(v_member).f3,
3850 gv_member(v_member).f4,
3851 gv_member(v_member).f5);
3852 /*
3853 next member
3854 */
3855 v_member := gv_member(v_member).next;
3856 END LOOP;
3857 RETURN p_set1;
3858 END append_to_set;
3859 /*
3860 for all members of the set create an expanded set according to the rule
3861 */
3862 FUNCTION for_expand(
3863 p_set1 IN BINARY_INTEGER,
3864 p_rule_number IN NUMBER )
3865 RETURN BINARY_INTEGER IS
3866 v_member BINARY_INTEGER;
3867 v_set1 BINARY_INTEGER;
3868 v_tds r_tds;
3869 v_prev_f1 VARCHAR2(10) ;
3870 v_prev_f2 VARCHAR2(10) ;
3871 BEGIN
3872 --smvk 29-Nov-2004 File.Sql.35 warning fix.
3873 v_prev_f1 := '@$%!*(|';
3874 v_prev_f2 := '@$%!*(|';
3875
3876 v_set1 := new_set;
3877 /*
3878 the expansion of a set is only meaninful for unit and version 99/06/07
3879 */
3880 v_member := gv_set(p_set1).first;
3881 LOOP
3882 /*
3883 check if last (must do check for null)
3884 */
3885 IF v_member IS NULL
3886 THEN
3887 /*
3888 yep, last member
3889 */
3890 EXIT;
3891 END IF;
3892 IF NOT (gv_member(v_member).f1 = v_prev_f1 AND
3893 gv_member(v_member).f2 = v_prev_f2)
3894 THEN
3895 /*
3896 populate stucture, target set member
3897 */
3898 v_tds.member_index := v_member;
3899 v_tds.unit_cd := gv_member(v_member).f1;
3900 v_tds.unit_version := gv_member(v_member).f2;
3901 v_tds.cal_type := gv_member(v_member).f3;
3902 v_tds.ci_sequence_number := gv_member(v_member).f4;
3903 v_tds.uoo_id := gv_member(v_member).f5;
3904 /*
3905 append evaluated set rule
3906 */
3907 v_set1 := append_to_set(v_set1,turing(p_rule_number,v_tds));
3908 v_prev_f1 := gv_member(v_member).f1;
3909 v_prev_f2 := gv_member(v_member).f2;
3910 END IF;
3911 /*
3912 next member
3913 */
3914 v_member := gv_member(v_member).next;
3915 END LOOP;
3916 RETURN v_set1;
3917 END for_expand;
3918 /*
3919 expand set and add original set
3920 */
3921
3922 /* out NOCOPY not used 99/06/28
3923 FUNCTION for_union(
3924 p_set1 IN BINARY_INTEGER,
3925 p_rule_number IN NUMBER )
3926 RETURN NUMBER IS
3927 v_set1 BINARY_INTEGER;
3928 BEGIN
3929 v_set1 := for_expand(p_set1,p_rule_number);
3930 v_set1 := set_union(v_set1,p_set1);
3931 RETURN v_set1;
3932 END for_union;
3933 */
3934 /*
3935 repeat expand until nothing left to expand
3936 */
3937 FUNCTION cascade_expand (
3938 p_expanded IN BINARY_INTEGER, /* previously expanded set */
3939 p_expand IN BINARY_INTEGER, /* set to expand */
3940 p_rule_number IN NUMBER ) /* the rule by which to expand */
3941 RETURN BINARY_INTEGER IS
3942 v_expanded BINARY_INTEGER; /* expanded set */
3943 v_tot_expanded BINARY_INTEGER; /* total expanded set */
3944 v_unexpanded BINARY_INTEGER; /* unexpanded set */
3945 BEGIN
3946 v_expanded := for_expand(p_expand,p_rule_number);
3947 v_tot_expanded := set_union(p_expanded,p_expand);
3948 v_unexpanded := set_minus(v_expanded,v_tot_expanded);
3949 IF members(v_unexpanded) > 0
3950 THEN
3951 RETURN cascade_expand(v_tot_expanded,v_unexpanded,p_rule_number);
3952 END IF;
3953 RETURN v_tot_expanded;
3954 END cascade_expand;
3955 /*
3956 return the specified (1-4) field value of the first member of the set
3957 this is used for recursive set manipulation
3958 */
3959 FUNCTION set_field_val (
3960 p_member IN BINARY_INTEGER,
3961 p_field IN NUMBER )
3962 RETURN VARCHAR2 IS
3963 BEGIN
3964 IF p_field = 1 THEN RETURN gv_member(p_member).f1;
3965 ELSIF p_field = 2 THEN RETURN gv_member(p_member).f2;
3966 ELSIF p_field = 3 THEN RETURN gv_member(p_member).f3;
3967 ELSIF p_field = 4 THEN RETURN gv_member(p_member).f4;
3968 ELSIF p_field = 5 THEN RETURN gv_member(p_member).f5;
3969 END IF;
3970 RETURN 'NO FIELD '||p_field;
3971 END set_field_val;
3972 /*
3973 IF THEN ELSE FUNCTIONS
3974
3975 return the first member of the set
3976 this is used for recursive set manipulation
3977 */
3978 FUNCTION first (
3979 p_set1 IN BINARY_INTEGER )
3980 RETURN BINARY_INTEGER IS
3981 v_member BINARY_INTEGER;
3982 BEGIN
3983 v_member := gv_set(p_set1).first;
3984 RETURN add_member(new_set,
3985 gv_member(v_member).f1,
3986 gv_member(v_member).f2,
3987 gv_member(v_member).f3,
3988 gv_member(v_member).f4,
3989 gv_member(v_member).f5);
3990 END first;
3991 /*
3992 if then else
3993 */
3994 FUNCTION ifthenelse(
3995 p_conditional IN VARCHAR2,
3996 p_then_rule IN NUMBER,
3997 p_else_rule IN NUMBER,
3998 p_tds IN r_tds )
3999 RETURN VARCHAR2 IS
4000 /*************************************************************
4001 Created By : nsinha
4002 Date Created By :
4003 Purpose :
4004 Know limitations, enhancements or remarks
4005 Change History
4006 Who When What
4007 Navin 27-Aug-2001 Added a package variable igs_ru_gen_001.p_evaluated_part
4008 as part of Bug# : 1899513.
4009 (reverse chronological order - newest change first)
4010 ***************************************************************/
4011
4012 v_count NUMBER;
4013 BEGIN
4014 IF RTRIM(p_conditional) = 'true'
4015 THEN
4016 igs_ru_gen_001.p_evaluated_part := 'IF';
4017 RETURN (turing(p_then_rule,p_tds));
4018 ELSIF RTRIM(p_conditional) = 'false'
4019 THEN
4020 igs_ru_gen_001.p_evaluated_part := 'ELSE';
4021 RETURN (turing(p_else_rule,p_tds));
4022 ELSE
4023 log_error('ifthenelse',
4024 'Invalid condition ('||p_conditional||')');
4025 END IF;
4026 END ifthenelse;
4027
4028 /*
4029 if then
4030 */
4031 FUNCTION ifthen(
4032 p_conditional IN VARCHAR2,
4033 p_then_rule IN NUMBER,
4034 p_tds IN r_tds )
4035 RETURN VARCHAR2 IS
4036 /*************************************************************
4037 Created By : nsinha
4038 Date Created By : 27-Aug-2001
4039 Purpose :
4040 Know limitations, enhancements or remarks
4041 Change History
4042 Who When What
4043 Navin 27-Aug-2001 Added this function to support a rule of
4044 type IF <condition> THEN <action>.
4045 This change is as per requirement of Bug# : 1899513.
4046 (reverse chronological order - newest change first)
4047 ***************************************************************/
4048 BEGIN
4049 IF RTRIM(p_conditional) = 'true'
4050 THEN
4051 igs_ru_gen_001.p_evaluated_part := 'IF';
4052 RETURN (turing(p_then_rule,p_tds));
4053 ELSIF RTRIM(p_conditional) = 'false'
4054 THEN
4055 igs_ru_gen_001.p_evaluated_part := 'ELSE';
4056 RETURN NULL;
4057 ELSE
4058 log_error('ifthen',
4059 'Invalid condition (' || p_conditional|| ')');
4060 END IF;
4061 END ifthen;
4062
4063 FUNCTION prgpl_chk_gsch_exists (
4064 p_grading_schema_cd IN VARCHAR2,
4065 p_grade IN VARCHAR2,
4066 p_set_number IN BINARY_INTEGER
4067 ) RETURN VARCHAR2 IS
4068 /**********************************************************************************************
4069 Created By : rbezawad
4070 Date Created By : 20-Nov-2001
4071 Purpose : Local function to check if a supplied grading schema and grade exists in the rule set.
4072 Created W.r.t Progression Rules Enhancement Build, Bug No: 2146547.
4073
4074 Known limitations,enhancements,remarks:
4075 Change History
4076 Who When What
4077 *************************************************************************************************/
4078
4079 l_member BINARY_INTEGER;
4080
4081 BEGIN
4082
4083 -- Get the first member defined as part of the rule set
4084 l_member := gv_set(p_set_number).first;
4085
4086 -- Loop thru all the members in gv_set and exit when there are no records in the
4087 -- global plsql record group gv_member(l_member).f1 ( grading schema code ).
4088 LOOP
4089 -- Check if the member is last or not.
4090 IF l_member IS NULL THEN
4091 -- If it is Last member then Exit loop and return FALSE.
4092 EXIT;
4093 END IF;
4094
4095 -- check if a supplied grading schema and grade exists in the rule set
4096 IF p_grading_schema_cd ||'.'||p_grade = gv_member(l_member).F1||'.'||gv_member(l_member).f2 THEN
4097 RETURN 'true';
4098 END IF;
4099
4100 -- Get Next member
4101 l_member := gv_member(l_member).NEXT;
4102 END LOOP;
4103
4104 RETURN 'false';
4105
4106 END prgpl_chk_gsch_exists;
4107
4108 ------------------------Gjha Added for Class Rank Build ---------------------------------------
4109
4110 FUNCTION prgp_chk_chrt_exists (
4111 p_prg_stat IN VARCHAR2,
4112 p_set_number IN BINARY_INTEGER
4113 ) RETURN VARCHAR2 IS
4114
4115 l_member BINARY_INTEGER;
4116
4117 BEGIN
4118 -- Get the first member defined as part of the rule set
4119 l_member := gv_set(p_set_number).first;
4120 -- Loop thru all the members in PRG_STAT and exit when there are no records in the
4121 -- global plsql record group gv_member(l_member).f1 ( grading schema code ).
4122 LOOP
4123 -- Check if the member is last or not.
4124 IF l_member IS NULL THEN
4125 -- If it is Last member then Exit loop and return FALSE.
4126 EXIT;
4127 END IF;
4128
4129 -- check if a supplied grading schema and grade exists in the rule set
4130 IF p_prg_stat = gv_member(l_member).F1 THEN
4131 RETURN 'true';
4132 END IF;
4133
4134 -- Get Next member
4135 l_member := gv_member(l_member).NEXT;
4136 END LOOP;
4137 RETURN 'false';
4138
4139 END prgp_chk_chrt_exists;
4140 ------------------------Gjha Added for Class Rank Build ---------------------------------------
4141
4142 --
4143 -- Function to calculate the statistics credit points based on the rules defined in the system
4144 --
4145 FUNCTION sua_credit_points_st (
4146 p_rule_type IN VARCHAR2,
4147 p_statistic_type IN VARCHAR2,
4148 p_number IN NUMBER,
4149 p_progression_period IN NUMBER
4150 ) RETURN VARCHAR2 IS
4151 ------------------------------------------------------------------------------
4152 --Known limitations/enhancements and/or remarks:
4153 --
4154 --Change History:
4155 -- Who When What
4156 -- smvk 29-Nov-2004 File.Sql.35 warning fix.
4157 -- kdande 11-Nov-2002 Created the function as part of Progression Rules
4158 -- Enhancement for Financial Aid for Jan 2003 Release
4159 -- ijeddy 11/23/2005 Bug 4754335, modified cur_sua
4160 ------------------------------------------------------------------------------
4161
4162 CURSOR cur_sua IS
4163 SELECT sua.unit_cd,
4164 sua.version_number,
4165 sua.cal_type,
4166 sua.ci_sequence_number,
4167 sua.uoo_id
4168 FROM igs_en_su_attempt sua,
4169 igs_ca_inst_rel cir,
4170 igs_ca_type cat,
4171 igs_ca_inst ci1,
4172 igs_ca_inst ci2
4173 WHERE sua.person_id = p_person_id
4174 AND sua.course_cd = p_course_cd
4175 AND sua.cal_type = cir.sub_cal_type
4176 AND sua.ci_sequence_number = cir.sub_ci_sequence_number
4177 AND ci1.cal_type = cir.sup_cal_type
4178 AND ci1.sequence_number = cir.sup_ci_sequence_number
4179 AND ci1.cal_type = cat.cal_type
4180 AND cat.s_cal_cat = 'PROGRESS'
4181 AND ci2.cal_type = p_cal_type
4182 AND ci2.sequence_number = p_ci_sequence_number
4183 AND ci1.start_dt <= ci2.start_dt;
4184
4185 --
4186 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
4187 v_override_achievable_cp IGS_EN_SU_ATTEMPT.override_achievable_cp%TYPE;
4188 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
4189 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
4190 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
4191 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
4192 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
4193 v_original_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
4194 v_result VARCHAR2(30);
4195 l_progression_period NUMBER ;
4196 l_failed_cp NUMBER := 0;
4197 l_attempted_cp NUMBER := 0;
4198 l_earned_cp NUMBER := 0;
4199 l_total_attempted_cp NUMBER := 0;
4200 l_flag VARCHAR2(1);
4201 l_return_status VARCHAR2(1);
4202 l_msg_count NUMBER;
4203 l_msg_data VARCHAR2(2000);
4204 --
4205 BEGIN
4206 l_progression_period := NVL (p_progression_period, 0);
4207 l_flag := 'Y';
4208 --
4209 FOR sua_rec IN cur_sua
4210 LOOP
4211 l_flag := 'Y';
4212 IF (p_rule_type IN ('NCPCPP', 'NCPPNPP')) THEN
4213 l_flag := igs_pr_gen_002.prgp_get_sua_prg_num (
4214 p_cal_type,
4215 p_ci_sequence_number,
4216 p_progression_period + 1,
4217 p_person_id,
4218 p_course_cd,
4219 sua_rec.unit_cd,
4220 sua_rec.cal_type,
4221 sua_rec.ci_sequence_number,
4222 sua_rec.uoo_id
4223 );
4224 END IF;
4225 IF (l_flag = 'Y') THEN
4226 igs_pr_cp_gpa.get_sua_cp (
4227 p_person_id => p_person_id,
4228 p_course_cd => p_course_cd,
4229 p_unit_cd => sua_rec.unit_cd,
4230 p_unit_version_number => sua_rec.version_number,
4231 p_teach_cal_type => sua_rec.cal_type,
4232 p_teach_ci_sequence_number => sua_rec.ci_sequence_number,
4233 p_stat_type => p_statistic_type,
4234 p_system_stat => 'PROGRESSION',
4235 p_earned_cp => l_earned_cp,
4236 p_attempted_cp => l_attempted_cp ,
4237 p_return_status => l_return_status,
4238 p_msg_count => l_msg_count,
4239 p_msg_data => l_msg_data,
4240 p_uoo_id => sua_rec.uoo_id
4241 );
4242 -- Calculate the total cp attempted:
4243 l_total_attempted_cp := NVL (l_total_attempted_cp, 0) + NVL (l_attempted_cp, 0);
4244 -- Get the unit attempt status
4245 SELECT unit_attempt_status,
4246 override_achievable_cp
4247 INTO v_unit_attempt_status,
4248 v_override_achievable_cp
4249 FROM igs_en_su_attempt
4250 WHERE person_id = p_person_id
4251 AND course_cd = p_course_cd
4252 AND uoo_id = sua_rec.uoo_id;
4253 -- Get the outcome of the unit attempt
4254 v_result := igs_as_gen_003.assp_get_sua_outcome (
4255 p_person_id,
4256 p_course_cd,
4257 sua_rec.unit_cd,
4258 sua_rec.cal_type,
4259 sua_rec.ci_sequence_number,
4260 v_unit_attempt_status,
4261 'N', /* finalised indicator (make parameter ??) */
4262 v_outcome_dt,
4263 v_grading_schema_cd,
4264 v_gs_version_number,
4265 v_grade,
4266 v_mark,
4267 v_original_course_cd,
4268 sua_rec.uoo_id,
4269 ---added by LKAKI---
4270 'N');
4271 -- If the student has failed the unit, increment the Failed points.
4272 IF (v_result = 'FAIL') THEN
4273 l_failed_cp := NVL (l_failed_cp, 0) + NVL (l_attempted_cp, 0);
4274 END IF;
4275 END IF;
4276 END LOOP;
4277 -- Total attempted and failed CPs are calculated : See the rule
4278 IF ((NVL (l_failed_cp, 0)/NVL (l_total_attempted_cp, 0)) * 100 > p_number) THEN
4279 return 'true';
4280 ELSE
4281 return 'false';
4282 END IF;
4283 EXCEPTION
4284 WHEN NO_DATA_FOUND THEN
4285 RETURN NULL;
4286 WHEN ZERO_DIVIDE THEN
4287 RETURN 'true';
4288 END sua_credit_points_st;
4289 --
4290 -- Function to calculate the statistics based on the rules defined in the system
4291 --
4292 FUNCTION prgp_cal_stat (
4293 p_rule_type IN VARCHAR2,
4294 p_statistic_type IN VARCHAR2,
4295 p_statistic_element IN VARCHAR2,
4296 p_number IN NUMBER
4297 ) RETURN VARCHAR2 IS
4298 ------------------------------------------------------------------------------
4299 --Known limitations/enhancements and/or remarks:
4300 --
4301 --Change History:
4302 -- Who When What
4303 -- kdande 11-Nov-2002 Created the function as part of Progression Rules
4304 -- Enhancement for Financial Aid for Jan 2003 Release
4305 ------------------------------------------------------------------------------
4306 --Cursor to fetch the Load calendar corresponding to the Progression Calendar.
4307 CURSOR c_cir (cp_prg_cal_type igs_ca_inst.cal_type%TYPE,
4308 cp_prg_sequence_number igs_ca_inst.sequence_number%TYPE) IS
4309 SELECT cir.sub_cal_type cal_type, cir.sub_ci_sequence_number ci_sequence_number
4310 FROM IGS_CA_INST ci ,
4311 IGS_CA_INST_REL cir,
4312 IGS_CA_TYPE cat,
4313 IGS_CA_STAT cs
4314 WHERE cir.sup_cal_type = cp_prg_cal_type
4315 AND cir.sup_ci_sequence_number = cp_prg_sequence_number
4316 AND ci.cal_type = cir.sub_cal_type
4317 AND ci.sequence_number = cir.sub_ci_sequence_number
4318 AND cat.cal_type = ci.cal_type
4319 AND cat.s_cal_cat = 'LOAD'
4320 AND cs.CAL_STATUS = ci.CAL_STATUS
4321 AND cs.s_CAL_STATUS = 'ACTIVE';
4322 rec_cir c_cir%ROWTYPE;
4323 l_return_status VARCHAR2(1);
4324 l_msg_count NUMBER;
4325 l_msg_data VARCHAR2(2000);
4326 v_gpa_value NUMBER;
4327 v_gpa_cp NUMBER;
4328 v_gpa_quality_points NUMBER;
4329 v_earned_cp NUMBER;
4330 v_attempted_cp NUMBER;
4331 l_cumulative_ind VARCHAR2(1);
4332 BEGIN
4333 IF p_rule_type IN ('PLT','PMT') THEN
4334 l_cumulative_ind := 'N';
4335 ELSE
4336 l_cumulative_ind := 'Y';
4337 END IF;
4338 IF (p_statistic_element IN ('GPA', 'GPA CP', 'GPA QP')) THEN
4339 -- Start of new code added to fix Bug# 3103892; nalkumar; 20-Aug-2003
4340 --
4341 OPEN c_cir(p_cal_type, p_ci_sequence_number);
4342 FETCH c_cir INTO rec_cir;
4343 CLOSE c_cir;
4344 --
4345 -- End of new code added to fix Bug# 3103892; nalkumar; 20-Aug-2003
4346 igs_pr_cp_gpa.get_gpa_stats (
4347 p_person_id => p_person_id, -- Person ID passed as the parameter for rulp_val_senna,
4348 p_course_cd => p_course_cd, -- Course code passed as the parameter for rulp_val_senna,
4349 p_stat_type => p_statistic_type, -- Statistic type defined in the rule.
4350 p_load_cal_type => rec_cir.cal_type, -- Load Calendar drived by the above cursor
4351 p_load_ci_sequence_number => rec_cir.ci_sequence_number, -- Load Calendar Instance drived by the above cursor
4352 p_system_stat => NULL, -- System Statistics Type
4353 p_cumulative_ind => l_cumulative_ind,
4354 p_gpa_value => v_gpa_value,
4355 p_gpa_cp => v_gpa_cp,
4356 p_gpa_quality_points => v_gpa_quality_points,
4357 p_return_status => l_return_status,
4358 p_msg_count => l_msg_count,
4359 p_msg_data => l_msg_data
4360 );
4361 IF p_rule_type IN ('PLT', 'CLT') THEN
4362 IF (p_statistic_element = 'GPA') THEN
4363 IF (v_gpa_value < p_number) THEN
4364 RETURN 'true';
4365 ELSE
4366 RETURN 'false';
4367 END IF;
4368 ELSIF (p_statistic_element = 'GPA CP') THEN
4369 IF (v_gpa_cp < p_number) THEN
4370 RETURN 'true';
4371 ELSE
4372 RETURN 'false';
4373 END IF;
4374 ELSIF (p_statistic_element = 'GPA QP') THEN
4375 IF (v_gpa_quality_points < p_number) THEN
4376 RETURN 'true';
4377 ELSE
4378 RETURN 'false';
4379 END IF;
4380 END IF;
4381 ELSIF p_rule_type IN ('PMT', 'CMT') THEN
4382 IF (p_statistic_element = 'GPA') THEN
4383 IF (v_gpa_value > p_number) THEN
4384 RETURN 'true';
4385 ELSE
4386 RETURN 'false';
4387 END IF;
4388 ELSIF (p_statistic_element = 'GPA CP') THEN
4389 IF (v_gpa_cp > p_number) THEN
4390 RETURN 'true';
4391 ELSE
4392 RETURN 'false';
4393 END IF;
4394 ELSIF (p_statistic_element = 'GPA QP') THEN
4395 IF (v_gpa_quality_points > p_number) THEN
4396 RETURN 'true';
4397 ELSE
4398 RETURN 'false';
4399 END IF;
4400 END IF;
4401 END IF;
4402 ELSIF (p_statistic_element IN ('CP_ATTEMPTED', 'CP_EARNED')) THEN
4403 -- Start of new code added to fix Bug# 3103892; nalkumar; 20-Aug-2003
4404 --
4405 OPEN c_cir(p_cal_type, p_ci_sequence_number);
4406 FETCH c_cir INTO rec_cir;
4407 CLOSE c_cir;
4408 --
4409 -- End of new code added to fix Bug# 3103892; nalkumar; 20-Aug-2003
4410 igs_pr_cp_gpa.get_cp_stats (
4411 p_person_id => p_person_id, -- Person ID passed as the parameter for rulp_val_senna,
4412 p_course_cd => p_course_cd, -- Course code passed as the parameter for rulp_val_senna,
4413 p_stat_type => p_statistic_type, -- Statistic type defined in the rule.
4414 p_load_cal_type => rec_cir.cal_type, -- Load Calendar drived by the above cursor
4415 p_load_ci_sequence_number => rec_cir.ci_sequence_number, -- Load Calendar Instance drived by the above cursor
4416 p_system_stat => NULL, -- System Statistics Type
4417 p_cumulative_ind => l_cumulative_ind,
4418 p_earned_cp => v_earned_cp,
4419 p_attempted_cp => v_attempted_cp,
4420 p_return_status => l_return_status,
4421 p_msg_count => l_msg_count,
4422 p_msg_data => l_msg_data
4423 );
4424 IF p_rule_type IN ('PLT', 'CLT') THEN
4425 IF (p_statistic_element = 'CP_ATTEMPTED') THEN
4426 IF (v_attempted_cp < p_number) THEN
4427 RETURN 'true';
4428 ELSE
4429 RETURN 'false';
4430 END IF;
4431 ELSIF (p_statistic_element = 'CP_EARNED') THEN
4432 IF (v_earned_cp < p_number) THEN
4433 RETURN 'true';
4434 ELSE
4435 RETURN 'false';
4436 END IF;
4437 END IF;
4438 ELSIF p_rule_type IN ('PMT', 'CMT') THEN
4439 IF (p_statistic_element = 'CP_ATTEMPTED') THEN
4440 IF (v_attempted_cp > p_number) THEN
4441 RETURN 'true';
4442 ELSE
4443 RETURN 'false';
4444 END IF;
4445 ELSIF (p_statistic_element = 'CP_EARNED') THEN
4446 IF (v_earned_cp > p_number) THEN
4447 RETURN 'true';
4448 ELSE
4449 RETURN 'false';
4450 END IF;
4451 END IF;
4452 END IF;
4453 END IF;
4454 RETURN 'false';
4455 END prgp_cal_stat;
4456 --
4457 FUNCTION prgp_get_chrt ( p_rule IN VARCHAR2,
4458 p_set_number IN BINARY_INTEGER
4459 ) RETURN VARCHAR2 IS
4460 -- Cursor to get the Progression status of the student This will return only one row: since there is a join based on person ID and Course Code
4461 -- nalkumar ** CURSOR CUR_PRG_STAT IS SELECT PV.RESPONSIBLE_ORG_UNIT_CD, PA.PROGRESSION_STATUS FROM
4462 CURSOR cur_prg_stat IS
4463 SELECT pa.progression_status, pv.responsible_org_unit_cd
4464 FROM igs_en_stdnt_ps_att pa, igs_ps_ver pv
4465 WHERE pv.course_cd = pa.course_cd and pv.version_number = pa.version_number and pa.person_id = p_person_id and pa.course_cd = p_course_cd;
4466
4467
4468 -- CURSOR TO SELECT THE PERSON id GROUPS TO WHICH THE STUDENT BELONGS
4469 CURSOR CUR_PID IS SELECT G.GROUP_CD, PERSON_ID from IGS_PE_PRSID_GRP_MEM M, IGS_PE_PERSID_GROUP G WHERE
4470 G.GROUP_ID = M.GROUP_ID AND SYSDATE BETWEEN M.START_DATE AND NVL(M.END_DATE, SYSDATE+1)
4471 AND M.PERSON_ID = P_PERSON_ID ;
4472
4473 --Cursor to get all the unit sets attempted by the student
4474 CURSOR USET_CUR IS SELECT UNIT_SET_CD FROM IGS_AS_SU_SETATMPT WHERE PERSON_ID = P_PERSON_ID AND COURSE_CD = P_COURSE_CD;
4475
4476 --Cursor to get the ceremony rounds of the student
4477 -- nalkumar ** CURSOR CUR_GRD_RND IS select rpad(GRD_CAL_TYPE,10)||rpad(GRD_CI_SEQUENCE_NUMBER,6) GRAD_ROUND from IGS_GR_AWD_CRMN WHERE PERSON_ID = P_PERSON_ID ;
4478 CURSOR CUR_GRD_RND IS select GRD_CAL_TYPE||GRD_CI_SEQUENCE_NUMBER GRAD_ROUND from IGS_GR_AWD_CRMN WHERE PERSON_ID = P_PERSON_ID ;
4479
4480
4481 lv_Group_id IGS_PE_PERSID_GROUP.GROUP_CD%TYPE;
4482 lv_progression_status IGS_EN_STDNT_PS_ATT_ALL.PROGRESSION_STATUS%TYPE;
4483 lv_org_unit IGS_PS_VER.RESPONSIBLE_ORG_UNIT_CD%TYPE;
4484 lv_Unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE;
4485 lv_att_type VARCHAR2(30);
4486 lv_class_standing varchar2(30);
4487
4488
4489 BEGIN
4490 -------------------------------------------------
4491 IF p_rule = 'PRGST' OR p_rule = 'NPRGST' Then
4492 OPEN CUR_PRG_STAT;
4493 FETCH CUR_PRG_STAT INTO lv_progression_status, lv_org_unit ;
4494 CLOSE CUR_PRG_STAT;
4495 IF prgp_chk_chrt_exists(lv_progression_status, p_set_number) = 'true' THEN
4496 IF p_rule = 'PRGST' Then
4497 RETURN 'true';
4498 ELSE
4499 RETURN 'false';
4500 END IF;
4501 ELSE
4502 IF p_rule = 'NPRGST' Then --**
4503 RETURN 'true'; --**
4504 END IF; --**
4505 RETURN 'false';
4506 END IF;
4507
4508 ELSIF (p_rule = 'ORG' OR p_rule = 'NORG') THEN
4509 OPEN CUR_PRG_STAT;
4510 FETCH CUR_PRG_STAT INTO lv_progression_status, lv_org_unit ;
4511 CLOSE CUR_PRG_STAT;
4512
4513 IF prgp_chk_chrt_exists(lv_org_unit, p_set_number) = 'true' THEN
4514 IF p_rule = 'ORG' THEN
4515 RETURN 'true';
4516 ELSE
4517 RETURN 'false';
4518 END IF;
4519 ELSE
4520 IF p_rule = 'NORG' THEN --**
4521 RETURN 'true'; --**
4522 END IF; --**
4523 RETURN 'false';
4524 END IF;
4525
4526 ELSIF (p_rule = 'CRS' OR p_rule = 'NCRS') Then
4527 IF prgp_chk_chrt_exists(p_course_cd, p_set_number) = 'true' THEN
4528 IF p_rule = 'CRS' THEN
4529 RETURN 'true';
4530 ELSE
4531 RETURN 'false';
4532 END IF;
4533 ELSE
4534 IF p_rule = 'NCRS' Then --**
4535 RETURN 'true'; --**
4536 END IF; --**
4537 RETURN 'false';
4538 END IF;
4539
4540 ELSIF (p_rule = 'PIDST' OR p_rule = 'NPIDST') Then
4541 -- Get the person ID group of the student and Loop
4542 FOR PID_REC IN CUR_PID LOOP
4543 IF prgp_chk_chrt_exists(PID_REC.GROUP_CD, p_set_number) = 'true' THEN
4544 IF p_rule = 'PIDST' THEN
4545 return 'true';
4546 ELSIF p_rule ='NPIDST' THEN
4547 return 'false';
4548 END IF;
4549 END IF;
4550 END LOOP;
4551 IF p_rule = 'PIDST' THEN
4552 return 'false';
4553 ELSE
4554 RETURN 'true';
4555 END IF;
4556
4557 ELSIF p_rule = 'UNST' OR p_rule = 'NUNST' Then
4558 -- Get all the unit sets attempted by the students for the given program attempt and Loop
4559 FOR USET_REC IN USET_CUR LOOP
4560 IF prgp_chk_chrt_exists(USET_REC.UNIT_SET_CD, p_set_number) = 'true' THEN
4561 IF p_rule = 'UNST' THEN
4562 return 'true';
4563 ELSIF p_rule ='NUNST' THEN
4564 return 'false';
4565 END IF;
4566 END IF;
4567 END LOOP;
4568 IF p_rule = 'UNST' then
4569 return 'false';
4570 ELSE
4571 RETURN 'true';
4572 END IF;
4573
4574
4575 ELSIF p_rule = 'GRDN' OR p_rule = 'NGRDN' Then
4576
4577 -- Get all the Graduation round of the student and Loop
4578 FOR GRDN_REC IN CUR_GRD_RND LOOP
4579 IF prgp_chk_chrt_exists(GRDN_REC.GRAD_ROUND, p_set_number) = 'true' THEN
4580 IF p_rule = 'GRDN' THEN
4581 return 'true';
4582 ELSIF p_rule ='NGRDN' THEN
4583 return 'false';
4584 END IF;
4585 END IF;
4586 END LOOP;
4587 IF p_rule = 'GRDN' then
4588 return 'false';
4589 ELSE
4590 RETURN 'true';
4591 END IF;
4592
4593 ELSIF p_rule = 'ATTTYP' OR p_rule = 'NATTTYP' Then
4594 --Get the attendance Type of the student for a program attempt into l_att_type
4595
4596 lv_att_type := igs_en_prc_load.ENRP_GET_PRG_ATT_TYPE
4597 (
4598 P_PERSON_ID => P_PERSON_ID,
4599 P_COURSE_CD => P_COURSE_CD,
4600 P_CAL_TYPE => P_CAL_TYPE,
4601 P_SEQUENCE_NUMBER => P_CI_SEQUENCE_NUMBER
4602 );
4603
4604 IF prgp_chk_chrt_exists( lv_att_type, p_set_number) = 'true' THEN
4605 IF p_rule = 'ATTTYP' THEN
4606 RETURN 'true';
4607 ELSE
4608 RETURN 'false';
4609 END IF;
4610 ELSE
4611 IF p_rule = 'NATTTYP' THEN
4612 RETURN 'true';
4613 END IF;
4614 RETURN 'false';
4615 END IF ;
4616
4617 ELSIF p_rule = 'CLSSTD' OR p_rule = 'NCLSSTD' Then
4618 -- Get the class standing of the student for a program attempt into lv_class standing . Make use of the function igs_pr_get_class_std.get_class_standing.
4619
4620 lv_class_standing := igs_pr_get_class_std.GET_CLASS_STANDING
4621 (
4622 P_PERSON_ID => P_PERSON_ID,
4623 P_COURSE_CD => P_COURSE_CD,
4624 P_PREDICTIVE_IND => 'Y',
4625 P_EFFECTIVE_DT => NULL,
4626 P_LOAD_CAL_TYPE => P_CAL_TYPE,
4627 P_LOAD_CI_SEQUENCE_NUMBER => P_CI_SEQUENCE_NUMBER
4628 );
4629
4630 IF prgp_chk_chrt_exists( lv_class_standing, p_set_number) = 'true' THEN
4631 IF p_rule = 'CLSSTD' THEN
4632 RETURN 'true';
4633 ELSE
4634 RETURN 'false';
4635 END IF;
4636 ELSE
4637 IF p_rule = 'NCLSSTD' THEN
4638 RETURN 'true';
4639 END IF;
4640 RETURN 'false';
4641 END IF ;
4642 END IF; -- End If for Rule codes..
4643
4644 END prgp_get_chrt;
4645 ----------------------------------Gjha Added For Class Rank-------------------------------------
4646
4647 FUNCTION prgp_cal_cp_gsch (
4648 p_rule_type IN VARCHAR2,
4649 p_person_id IN NUMBER,
4650 p_course_cd IN VARCHAR2,
4651 p_course_version IN NUMBER,
4652 p_prg_cal_type IN VARCHAR2,
4653 p_prg_ci_sequence_number IN NUMBER,
4654 p_grad_sch_set_no IN NUMBER,
4655 p_cp_in_rule IN NUMBER,
4656 p_cp_result OUT NOCOPY NUMBER,
4657 p_stat_type IN VARCHAR2
4658 ) RETURN VARCHAR2 IS
4659
4660 /**********************************************************************************************
4661 Created By : rbezawad
4662 Date Created By : 20-Nov-2001
4663 Purpose : This procedure is used to calculate the credit points or units for
4664 a student in context for the rules evaluation.
4665 Created W.r.t Progression Rules Enhancement Build, Bug No: 2146547.
4666 Known limitations,enhancements,remarks:
4667 Change History
4668 Who When What
4669 smvk 29-Nov-2004 File.Sql.35 warning fix.
4670 smvk 02-Sep-2002 Default keyword is replaced by assignment operator (:=), to overcome File.Pkg.22 warning
4671 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390).
4672 kdande 11-Nov-2002 Changed the code to handle the addition of Statistic Type for the Rules as
4673 part of Progression Rules Enhancement for Financial Aid for Jan 2003 Release
4674 *************************************************************************************************/
4675
4676 -- Get all the Student Unit Attempts for teaching periods subordinate
4677 -- to all Progression periods upto and including the current progression Period.
4678 CURSOR cur_sua_all_prd IS
4679 SELECT sua.unit_cd,
4680 sua.version_number,
4681 sua.cal_type,
4682 sua.ci_sequence_number,
4683 sua.unit_attempt_status,
4684 uoo_id
4685 FROM igs_en_su_attempt sua,
4686 igs_ca_inst ci_cur,
4687 igs_ca_inst_rel cir,
4688 igs_ca_inst ci,
4689 igs_ca_type ct
4690 WHERE sua.person_id = p_person_id
4691 AND sua.course_cd = p_course_cd
4692 AND sua.unit_attempt_status = 'COMPLETED'
4693 AND sua.cal_type = cir.sub_cal_type
4694 AND sua.ci_sequence_number = cir.sub_ci_sequence_number
4695 AND ci.cal_type = cir.sup_cal_type
4696 AND ci.sequence_number = cir.sup_ci_sequence_number
4697 AND ci.cal_type = ct.cal_type
4698 AND ct.s_cal_cat = 'PROGRESS'
4699 AND ci_cur.cal_type = p_prg_cal_type
4700 AND ci_cur.sequence_number = p_prg_ci_sequence_number
4701 AND ci.end_dt <= ci_cur.end_dt;
4702
4703 -- Get all the Student Unit Attempts for teaching periods
4704 -- subordinate to only the Current Progression period.
4705 CURSOR cur_sua_curr_prd IS
4706 SELECT sua.unit_cd,
4707 sua.version_number,
4708 sua.cal_type,
4709 sua.ci_sequence_number,
4710 sua.unit_attempt_status,
4711 uoo_id
4712 FROM igs_en_su_attempt sua,
4713 igs_ca_inst_rel cir
4714 WHERE sua.person_id = p_person_id
4715 AND sua.course_cd = p_course_cd
4716 AND sua.unit_attempt_status = 'COMPLETED'
4717 AND sua.cal_type = cir.sub_cal_type
4718 AND sua.ci_sequence_number = cir.sub_ci_sequence_number
4719 AND cir.sup_cal_type = p_prg_cal_type
4720 AND cir.sup_ci_sequence_number = p_prg_ci_sequence_number;
4721
4722 l_return_status VARCHAR2(1);
4723 l_msg_count NUMBER;
4724 l_msg_data VARCHAR2(2000);
4725 l_earned_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
4726 l_attempted_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
4727 l_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
4728
4729 --OUT varibales to be passed to igs_as_gen_003.assp_get_sua_outcomes Function
4730 l_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE;
4731 l_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE;
4732 l_version_number igs_as_grd_sch_grade.version_number%TYPE;
4733 l_grade igs_as_grd_sch_grade.grade%TYPE;
4734 l_mark igs_as_su_stmptout.mark%TYPE;
4735 l_origin_course_cd igs_en_su_attempt.course_cd%TYPE;
4736
4737 l_total_attempted_cp NUMBER := 0; -- Replaced Default by :=
4738 l_total_attempted_units NUMBER := 0; -- Replaced Default by :=
4739 l_set_attempted_cp NUMBER := 0; -- Replaced Default by :=
4740 l_set_attempted_units NUMBER := 0; -- Replaced Default by :=
4741 l_stat_type VARCHAR2(30);
4742 BEGIN
4743 l_stat_type := p_stat_type;
4744 --
4745 IF p_rule_type IN ('ALL_PERD_PM', 'ALL_PERD_PL', 'ALL_PERDM', 'ALL_PERDL', 'ALL_PERD_UPM', 'ALL_PERD_UPL', 'ALL_PERD_UM', 'ALL_PERD_UL',
4746 'ALL_PERD_PM_ST', 'ALL_PERD_PL_ST', 'ALL_PERDM_ST', 'ALL_PERDL_ST') THEN
4747 -- All Period Rules (Cumulative Rules)
4748 FOR l_sua_all_prd_rec IN cur_sua_all_prd LOOP
4749 -- To get the Earned and Attempted CP values for the Student Unit Attempt
4750 igs_pr_cp_gpa.get_sua_cp (
4751 p_person_id => p_person_id,
4752 p_course_cd => p_course_cd,
4753 p_unit_cd => l_sua_all_prd_rec.unit_cd,
4754 p_unit_version_number => l_sua_all_prd_rec.version_number,
4755 p_teach_cal_type => l_sua_all_prd_rec.cal_type,
4756 p_teach_ci_sequence_number => l_sua_all_prd_rec.ci_sequence_number,
4757 p_stat_type => l_stat_type,
4758 p_system_stat => 'PROGRESSION',
4759 p_earned_cp => l_earned_cp,
4760 p_attempted_cp => l_attempted_cp,
4761 p_return_status => l_return_status,
4762 p_msg_count => l_msg_count,
4763 p_msg_data => l_msg_data,
4764 p_uoo_id => l_sua_all_prd_rec.uoo_id
4765 );
4766 -- To get the Grading Schema Code for the Student Unit Attempt.
4767 l_result_type := igs_as_gen_003.assp_get_sua_outcome (
4768 p_person_id,
4769 p_course_cd,
4770 l_sua_all_prd_rec.unit_cd,
4771 l_sua_all_prd_rec.cal_type,
4772 l_sua_all_prd_rec.ci_sequence_number,
4773 l_sua_all_prd_rec.unit_attempt_status,
4774 'Y',
4775 l_outcome_dt,
4776 l_grading_schema_cd,
4777 l_version_number,
4778 l_grade,
4779 l_mark,
4780 l_origin_course_cd,
4781 l_sua_all_prd_rec.uoo_id,
4782 --added by LKAKI---
4783 'N');
4784 l_total_attempted_cp := l_total_attempted_cp + NVL(l_attempted_cp,0);
4785 l_total_attempted_units := l_total_attempted_units + 1;
4786 IF prgpl_chk_gsch_exists(l_grading_schema_cd, l_grade, p_grad_sch_set_no) = 'true' THEN
4787 -- If Grading Schema Code of the unit attempt exists in the Grading Schema Set of the
4788 -- Grading Schema Code defined in the rule then add the Unit Attempt CP to the Set Attempted CP
4789 -- and increment the Set Attempted Units.
4790 l_set_attempted_cp := l_set_attempted_cp + NVL(l_attempted_cp,0);
4791 l_set_attempted_units := l_set_attempted_units + 1;
4792 END IF;
4793 END LOOP;
4794 ELSIF p_rule_type IN ('CUR_PERD_PM', 'CUR_PERD_PL', 'CUR_PERDM', 'CUR_PERDL', 'CUR_PERD_UPM', 'CUR_PERD_UPL', 'CUR_PERD_UM', 'CUR_PERD_UL',
4795 'CUR_PERD_PM_ST', 'CUR_PERD_PL_ST', 'CUR_PERDM_ST', 'CUR_PERDL_ST') THEN
4796 -- Current Period Rules
4797 FOR l_sua_curr_prd_rec IN cur_sua_curr_prd LOOP
4798 -- To get the Earned and Attempted CP values for the Student Unit Attempt.
4799 igs_pr_cp_gpa.get_sua_cp (
4800 p_person_id => p_person_id,
4801 p_course_cd => p_course_cd,
4802 p_unit_cd => l_sua_curr_prd_rec.unit_cd,
4803 p_unit_version_number => l_sua_curr_prd_rec.version_number,
4804 p_teach_cal_type => l_sua_curr_prd_rec.cal_type,
4805 p_teach_ci_sequence_number => l_sua_curr_prd_rec.ci_sequence_number,
4806 p_stat_type => l_stat_type,
4807 p_system_stat => 'PROGRESSION',
4808 p_earned_cp => l_earned_cp,
4809 p_attempted_cp => l_attempted_cp,
4810 p_return_status => l_return_status,
4811 p_msg_count => l_msg_count,
4812 p_msg_data => l_msg_data,
4813 p_uoo_id => l_sua_curr_prd_rec.uoo_id );
4814 -- To get the Grading Schema Code for the Student Unit Attempt.
4815 l_result_type := igs_as_gen_003.assp_get_sua_outcome (
4816 p_person_id,
4817 p_course_cd,
4818 l_sua_curr_prd_rec.unit_cd,
4819 l_sua_curr_prd_rec.cal_type,
4820 l_sua_curr_prd_rec.ci_sequence_number,
4821 l_sua_curr_prd_rec.unit_attempt_status,
4822 'Y',
4823 l_outcome_dt,
4824 l_grading_schema_cd,
4825 l_version_number,
4826 l_grade,
4827 l_mark,
4828 l_origin_course_cd,
4829 l_sua_curr_prd_rec.uoo_id,
4830 ---added by LKAKI----
4831 'N');
4832 l_total_attempted_cp := l_total_attempted_cp + NVL(l_attempted_cp,0);
4833 l_total_attempted_units := l_total_attempted_units + 1;
4834 IF prgpl_chk_gsch_exists(l_grading_schema_cd, l_grade, p_grad_sch_set_no) = 'true' THEN
4835 -- If Grading Schema Code of the unit attempt exists in the Grading Schema Set of the
4836 -- Grading Schema Code defined in the rule then add the Unit Attempt CP to the Set Attempted CP
4837 -- and increment the Set Attempted Units.
4838 l_set_attempted_cp := l_set_attempted_cp + NVL(l_attempted_cp,0);
4839 l_set_attempted_units := l_set_attempted_units + 1;
4840 END IF;
4841 END LOOP;
4842 END IF; -- p_rule_type check condition
4843 --
4844 -- CP Based Rules
4845 --
4846 IF p_rule_type IN ('ALL_PERD_PM', 'ALL_PERD_PM_ST', 'CUR_PERD_PM', 'CUR_PERD_PM_ST') THEN
4847 -- All/Current Period, Percent CP More Than.
4848 p_cp_result := (l_set_attempted_cp/l_total_attempted_cp)*100;
4849 IF ((l_set_attempted_cp/l_total_attempted_cp)*100) > p_cp_in_rule THEN
4850 RETURN 'true';
4851 ELSE
4852 RETURN 'false';
4853 END IF;
4854 ELSIF p_rule_type IN ('ALL_PERD_PL', 'ALL_PERD_PL_ST', 'CUR_PERD_PL', 'CUR_PERD_PL_ST') THEN
4855 -- All/Current Period, Percent CP Less Than.
4856 p_cp_result := (l_set_attempted_cp/l_total_attempted_cp)*100;
4857 IF ((l_set_attempted_cp/l_total_attempted_cp)*100) < p_cp_in_rule THEN
4858 RETURN 'true';
4859 ELSE
4860 RETURN 'false';
4861 END IF;
4862 ELSIF p_rule_type IN ('ALL_PERDM', 'ALL_PERDM_ST', 'CUR_PERDM', 'CUR_PERDM_ST') THEN
4863 -- All/Current Period, Number CP More Than.
4864 p_cp_result := l_set_attempted_cp;
4865 IF ( l_set_attempted_cp > p_cp_in_rule ) THEN
4866 RETURN 'true';
4867 ELSE
4868 RETURN 'false';
4869 END IF;
4870 ELSIF p_rule_type IN ('ALL_PERDL', 'ALL_PERDL_ST', 'CUR_PERDL', 'CUR_PERDL_ST') THEN
4871 -- All/Current Period, Number CP Less Than.
4872 p_cp_result := l_set_attempted_cp;
4873 IF ( l_set_attempted_cp < p_cp_in_rule ) THEN
4874 RETURN 'true';
4875 ELSE
4876 RETURN 'false';
4877 END IF;
4878 --
4879 -- Unit Based Rules
4880 --
4881 ELSIF p_rule_type IN ('ALL_PERD_UPM', 'CUR_PERD_UPM') THEN
4882 -- All/Current Period, Percent Units More Than.
4883 p_cp_result := (l_set_attempted_units/l_total_attempted_units)*100;
4884 IF ((l_set_attempted_units/l_total_attempted_units)*100) > p_cp_in_rule THEN
4885 RETURN 'true';
4886 ELSE
4887 RETURN 'false';
4888 END IF;
4889 ELSIF p_rule_type IN ('ALL_PERD_UPL', 'CUR_PERD_UPL') THEN
4890 -- All/Current Period, Percent Units Less Than.
4891 p_cp_result := (l_set_attempted_units/l_total_attempted_units)*100;
4892 IF ((l_set_attempted_units/l_total_attempted_units)*100) < p_cp_in_rule THEN
4893 RETURN 'true';
4894 ELSE
4895 RETURN 'false';
4896 END IF;
4897 ELSIF p_rule_type IN ('ALL_PERD_UM', 'CUR_PERD_UM') THEN
4898 -- All/Current Period, Number Units More Than.
4899 p_cp_result := l_set_attempted_units;
4900 IF l_set_attempted_units > p_cp_in_rule THEN
4901 RETURN 'true';
4902 ELSE
4903 RETURN 'false';
4904 END IF;
4905 ELSIF p_rule_type IN ('ALL_PERD_UL', 'CUR_PERD_UL') THEN
4906 -- All/Current Period, Number Units Less Than.
4907 p_cp_result := l_set_attempted_units;
4908 IF l_set_attempted_units < p_cp_in_rule THEN
4909 RETURN 'true';
4910 ELSE
4911 RETURN 'false';
4912 END IF;
4913 END IF; -- p_rule_type check condition
4914 RETURN 'true';
4915 EXCEPTION
4916 WHEN ZERO_DIVIDE THEN
4917 IF p_rule_type IN ('ALL_PERD_PM', 'CUR_PERD_PM', 'ALL_PERD_PM_ST', 'CUR_PERD_PM_ST', 'ALL_PERD_UPM', 'CUR_PERD_UPM') THEN
4918 RETURN 'false';
4919 ELSIF p_rule_type IN ('ALL_PERD_PL', 'CUR_PERD_PL', 'ALL_PERD_PL_ST', 'CUR_PERD_PL_ST', 'ALL_PERD_UPL', 'CUR_PERD_UPL') THEN
4920 RETURN 'true';
4921 END IF;
4922 WHEN OTHERS THEN
4923 RETURN 'false';
4924 END prgp_cal_cp_gsch;
4925
4926 FUNCTION prgp_cal_cp(
4927 p_rule_type IN VARCHAR2,
4928 p_person_id IN NUMBER,
4929 p_course_cd IN VARCHAR2,
4930 p_course_version IN NUMBER,
4931 p_cal_type IN VARCHAR2,
4932 p_ci_sequence_number IN NUMBER)
4933 RETURN NUMBER AS
4934 /**********************************************************************************************
4935 Created By : svenkata
4936 Date Created By : 21-Nov-2001
4937 Purpose : The function calculates the credit points for a student in
4938 context .This FUNCTION accepts a string AS the first input
4939 parameter TO identify the rule for which the credit points
4940 need to be calculated. The relevant parameters required to
4941 calculate the GPA/CP/Unit Grades are also added to this function.
4942 Bug No: 2146547.
4943 Known limitations,enhancements,remarks:
4944 Change History
4945 Who When What
4946 smvk 02-Sep-2002 Default keyword is replaced by assignment operator (:=), to overcome File.Pkg.22 warning
4947 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390).
4948 *************************************************************************************************/
4949
4950 l_earned_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
4951 l_attempted_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
4952 l_total_earned_cp igs_ps_unit_ver.achievable_credit_points%TYPE := 0 ; -- Replaced Default by :=
4953 l_total_attempted_cp igs_ps_unit_ver.achievable_credit_points%TYPE := 0 ; -- Replaced Default by :=
4954 l_return_status VARCHAR2(1);
4955 l_msg_count NUMBER;
4956 l_msg_data VARCHAR2(2000);
4957
4958 CURSOR c_sua_accp IS
4959 SELECT sua.unit_cd,
4960 sua.version_number,
4961 sua.cal_type,
4962 sua.ci_sequence_number,
4963 sua.uoo_id
4964 FROM igs_en_su_attempt sua,
4965 igs_ca_inst ci_cur,
4966 igs_ca_inst_rel cir,
4967 igs_ca_inst ci,
4968 igs_ca_type ct
4969 WHERE sua.person_id = p_person_id
4970 AND sua.course_cd = p_course_cd
4971 AND sua.unit_attempt_status = 'COMPLETED'
4972 AND sua.cal_type = cir.sub_cal_type
4973 AND sua.ci_sequence_number = cir.sub_ci_sequence_number
4974 AND ci.cal_type = cir.sup_cal_type
4975 AND ci.sequence_number = cir.sup_ci_sequence_number
4976 AND ci.cal_type = ct.cal_type
4977 AND ct.s_cal_cat = 'PROGRESS'
4978 AND ci_cur.cal_type = p_cal_type
4979 AND ci_cur.sequence_number = p_ci_sequence_number
4980 AND ci.end_dt <= ci_cur.end_dt;
4981
4982 CURSOR c_sua_apcp IS
4983 SELECT sua.unit_cd,
4984 sua.version_number,
4985 sua.cal_type,
4986 sua.ci_sequence_number,
4987 sua.uoo_id
4988 FROM igs_en_su_attempt sua,
4989 igs_ca_inst_rel cir
4990 WHERE sua.person_id = p_person_id
4991 AND sua.course_cd = p_course_cd
4992 AND sua.unit_attempt_status = 'COMPLETED'
4993 AND sua.cal_type = cir.sub_cal_type
4994 AND sua.ci_sequence_number = cir.sub_ci_sequence_number
4995 AND p_cal_type = cir.sup_cal_type
4996 AND p_ci_sequence_number = cir.sup_ci_sequence_number;
4997
4998 l_attempt_accp c_sua_accp%ROWTYPE;
4999 l_attempt_apcp c_sua_apcp%ROWTYPE;
5000
5001 BEGIN
5002 -- If the Rule Type involves Attempted Cumulative Credit Point, or
5003 -- Earned Cumulative Credit Point the following condition is evaluated .
5004 -- Cumulative is taken to mean all progression periods up to and including
5005 -- the current progression. The cursor finds all student unit attempts
5006 -- where the teaching period is related to a progression period with an
5007 -- end date on or before the end date of the current progression period.
5008
5009 IF p_rule_type = 'ACCP' OR p_rule_type = 'ECCP' THEN
5010 FOR l_sua_rec IN c_sua_accp LOOP
5011 Igs_Pr_Cp_Gpa.Get_Sua_Cp(
5012 p_person_id => p_person_id,
5013 p_course_cd => p_course_cd,
5014 p_unit_cd => l_sua_rec.unit_cd,
5015 p_unit_version_number => l_sua_rec.version_number,
5016 p_teach_cal_type => l_sua_rec.cal_type,
5017 p_teach_ci_sequence_number => l_sua_rec.ci_sequence_number,
5018 p_stat_type => NULL,
5019 p_system_stat => 'PROGRESSION',
5020 p_earned_cp => l_earned_cp,
5021 p_attempted_cp => l_attempted_cp,
5022 p_return_status => l_return_status,
5023 p_msg_count => l_msg_count,
5024 p_msg_data => l_msg_data,
5025 p_uoo_id => l_sua_rec.uoo_id);
5026
5027 l_total_attempted_cp := l_total_attempted_cp + NVL(l_attempted_cp, 0);
5028 l_total_earned_cp := l_total_earned_cp + NVL(l_earned_cp, 0);
5029 END LOOP ;
5030
5031 IF P_RULE_TYPE = 'ACCP' THEN
5032 RETURN l_total_attempted_cp;
5033 ELSE
5034 RETURN l_total_earned_cp;
5035 END IF;
5036
5037 -- If the Rule Type involves Attempted Period Credit Point, or
5038 -- Earned Period Credit Point the following condition is evaluated .
5039 -- Period is taken to mean units taken in teaching periods related TO
5040 -- the current progression period. The cursor finds all student units
5041 -- attempts where the teaching period is related to the current
5042 -- progression period.
5043 ELSIF p_rule_type = 'APCP' OR p_rule_type = 'EPCP' THEN
5044 FOR l_sua_rec IN c_sua_apcp LOOP
5045 Igs_Pr_Cp_Gpa.Get_Sua_Cp(
5046 p_person_id => p_person_id,
5047 p_course_cd => p_course_cd,
5048 p_unit_cd => l_sua_rec.unit_cd,
5049 p_unit_version_number => l_sua_rec.version_number,
5050 p_teach_cal_type => l_sua_rec.cal_type,
5051 p_teach_ci_sequence_number => l_sua_rec.ci_sequence_number,
5052 p_stat_type => NULL,
5053 p_system_stat => 'PROGRESSION',
5054 p_earned_cp => l_earned_cp,
5055 p_attempted_cp => l_attempted_cp,
5056 p_return_status => l_return_status,
5057 p_msg_count => l_msg_count,
5058 p_msg_data => l_msg_data,
5059 p_uoo_id => l_sua_rec.uoo_id);
5060
5061 l_total_attempted_cp := l_total_attempted_cp + NVL(l_attempted_cp, 0);
5062 l_total_earned_cp := l_total_earned_cp + NVL(l_earned_cp, 0);
5063 END LOOP;
5064
5065 IF P_RULE_TYPE = 'APCP' THEN
5066 RETURN l_total_attempted_cp;
5067 ELSE
5068 RETURN l_total_earned_cp;
5069 END IF;
5070
5071 END IF;
5072
5073 END prgp_cal_cp;
5074
5075
5076 FUNCTION pr_rul_msg( p_rule_type IN VARCHAR2)
5077 RETURN VARCHAR2 IS
5078 /**********************************************************************************************
5079 Created By : svenkata
5080 Date Created By : 21-Nov-2001
5081 Purpose : This turing function displays the message text along with the rule when
5082 the rule is being validated. This is done by popping the Message name that
5083 has been registered for the rule and therafter using that to resolve the
5084 reference to the rule type . Once the required calculation is made
5085 ( GPA / Credit Points ) either for the Current period or for All periods,
5086 the appropriate message along with the calculated figures is displayed to the
5087 user. All Messages are hard coded instead of using messgae dictionary in
5088 simillar lines with existing messgae rules as a known NLS issue with all rules .
5089 Bug No: 2146547.
5090 Known limitations,enhancements,remarks:
5091 Change History
5092 Who When What
5093 *************************************************************************************************/
5094
5095 l_message VARCHAR2(5000);
5096 l_gpa_cp_calc NUMBER ;
5097 l_rule_type VARCHAR2(100);
5098 l_outcome VARCHAR2(100);
5099
5100 BEGIN
5101
5102 IF p_rule_type = 'ACCPM' THEN
5103 l_rule_type := 'ACCP' ;
5104 l_gpa_cp_calc := prgp_cal_cp(
5105 l_rule_type,
5106 p_person_id,
5107 p_course_cd,
5108 p_course_version,
5109 p_cal_type,
5110 p_ci_sequence_number);
5111 l_message := 'Attempted Cumulative Credit Points is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5112 ELSIF p_rule_type = 'APCPM' THEN
5113 l_rule_type := 'APCP' ;
5114 l_gpa_cp_calc := prgp_cal_cp(
5115 l_rule_type,
5116 p_person_id,
5117 p_course_cd,
5118 p_course_version,
5119 p_cal_type,
5120 p_ci_sequence_number);
5121 l_message := 'Attempted Period Credit Points is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5122 ELSIF p_rule_type = 'ECCPM' THEN
5123 l_rule_type := 'ECCP' ;
5124 l_gpa_cp_calc := prgp_cal_cp(
5125 l_rule_type,
5126 p_person_id,
5127 p_course_cd,
5128 p_course_version,
5129 p_cal_type,
5130 p_ci_sequence_number);
5131 l_message := 'Earned Cumulative Credit Points is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5132 ELSIF p_rule_type = 'EPCPM' THEN
5133 l_rule_type := 'EPCP' ;
5134 l_gpa_cp_calc := prgp_cal_cp(
5135 l_rule_type,
5136 p_person_id,
5137 p_course_cd,
5138 p_course_version,
5139 p_cal_type,
5140 p_ci_sequence_number);
5141 l_message := 'Earned Period Credit Points is ' || IGS_GE_NUMBER.TO_CANN(l_gpa_cp_calc) ;
5142 ELSIF p_rule_type IN ('MACPGSAPM', 'MACPGSAPM_ST') THEN
5143 l_rule_type := 'ALL_PERD_PM' ;
5144 -- kdande; Changed the following call to include statistic type as part of FA112
5145 l_outcome := prgp_cal_cp_gsch (
5146 l_rule_type,
5147 p_person_id,
5148 p_course_cd,
5149 p_course_version,
5150 p_cal_type,
5151 p_ci_sequence_number,
5152 g_grad_sch_set_no,
5153 0,
5154 l_gpa_cp_calc,
5155 NULL);
5156 l_message := 'Percentage Attempted credit Points in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5157 ELSIF p_rule_type IN ('MACPGSCPM', 'MACPGSCPM_ST') THEN
5158 l_rule_type := 'CUR_PERD_PM' ;
5159 -- kdande; Changed the following call to include statistic type as part of FA112
5160 l_outcome := prgp_cal_cp_gsch (
5161 l_rule_type,
5162 p_person_id,
5163 p_course_cd,
5164 p_course_version,
5165 p_cal_type,
5166 p_ci_sequence_number,
5167 g_grad_sch_set_no,
5168 0,
5169 l_gpa_cp_calc,
5170 NULL);
5171 l_message := 'Percentage Attempted Credit Points in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5172 ELSIF p_rule_type IN ('LACPGSAPM', 'LACPGSAPM_ST') THEN
5173 l_rule_type := 'ALL_PERD_PL' ;
5174 -- kdande; Changed the following call to include statistic type as part of FA112
5175 l_outcome := prgp_cal_cp_gsch (
5176 l_rule_type,
5177 p_person_id,
5178 p_course_cd,
5179 p_course_version,
5180 p_cal_type,
5181 p_ci_sequence_number,
5182 g_grad_sch_set_no,
5183 0,
5184 l_gpa_cp_calc,
5185 NULL);
5186 l_message := 'Percentage Attempted Credit Points in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5187 ELSIF p_rule_type IN ('LACPGSCPM', 'LACPGSCPM_ST') THEN
5188 l_rule_type := 'CUR_PERD_PL' ;
5189 -- kdande; Changed the following call to include statistic type as part of FA112
5190 l_outcome := prgp_cal_cp_gsch (
5191 l_rule_type,
5192 p_person_id,
5193 p_course_cd,
5194 p_course_version,
5195 p_cal_type,
5196 p_ci_sequence_number,
5197 g_grad_sch_set_no,
5198 0,
5199 l_gpa_cp_calc,
5200 NULL);
5201 l_message := 'Percentage Attempted Credit Points in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5202 ELSIF p_rule_type IN ('MACPGSAM', 'MACPGSAM_ST') THEN
5203 l_rule_type := 'ALL_PERDM' ;
5204 -- kdande; Changed the following call to include statistic type as part of FA112
5205 l_outcome := prgp_cal_cp_gsch (
5206 l_rule_type,
5207 p_person_id,
5208 p_course_cd,
5209 p_course_version,
5210 p_cal_type,
5211 p_ci_sequence_number,
5212 g_grad_sch_set_no,
5213 0,
5214 l_gpa_cp_calc,
5215 NULL);
5216 l_message := 'Attempted credit Points in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5217 ELSIF p_rule_type IN ('MACPGSCM', 'MACPGSCM_ST') THEN
5218 l_rule_type := 'CUR_PERDM' ;
5219 -- kdande; Changed the following call to include statistic type as part of FA112
5220 l_outcome := prgp_cal_cp_gsch (
5221 l_rule_type,
5222 p_person_id,
5223 p_course_cd,
5224 p_course_version,
5225 p_cal_type,
5226 p_ci_sequence_number,
5227 g_grad_sch_set_no,
5228 0,
5229 l_gpa_cp_calc,
5230 NULL);
5231 l_message := 'Attempted credit Points in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5232 ELSIF p_rule_type IN ('LACPGSCM', 'LACPGSCM_ST') THEN
5233 l_rule_type := 'CUR_PERDL' ;
5234 -- kdande; Changed the following call to include statistic type as part of FA112
5235 l_outcome := prgp_cal_cp_gsch (
5236 l_rule_type,
5237 p_person_id,
5238 p_course_cd,
5239 p_course_version,
5240 p_cal_type,
5241 p_ci_sequence_number,
5242 g_grad_sch_set_no,
5243 0,
5244 l_gpa_cp_calc,
5245 NULL);
5246 l_message := 'Attempted credit Points in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5247 ELSIF p_rule_type IN ('LACPGSAM', 'LACPGSAM_ST') THEN
5248 l_rule_type := 'ALL_PERDL' ;
5249 -- kdande; Changed the following call to include statistic type as part of FA112
5250 l_outcome := prgp_cal_cp_gsch (
5251 l_rule_type,
5252 p_person_id,
5253 p_course_cd,
5254 p_course_version,
5255 p_cal_type,
5256 p_ci_sequence_number,
5257 g_grad_sch_set_no,
5258 0,
5259 l_gpa_cp_calc,
5260 NULL);
5261 l_message := 'Attempted credit Points in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5262 ELSIF p_rule_type = 'MAUGSAPM' THEN
5263 l_rule_type := 'ALL_PERD_UPM' ;
5264 -- kdande; Changed the following call to include statistic type as part of FA112
5265 l_outcome := prgp_cal_cp_gsch (
5266 l_rule_type,
5267 p_person_id,
5268 p_course_cd,
5269 p_course_version,
5270 p_cal_type,
5271 p_ci_sequence_number,
5272 g_grad_sch_set_no,
5273 0,
5274 l_gpa_cp_calc,
5275 NULL);
5276 l_message := 'Percentage Attempted units in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5277 ELSIF p_rule_type = 'MAUGSCPM' THEN
5278 l_rule_type := 'CUR_PERD_UPM' ;
5279 -- kdande; Changed the following call to include statistic type as part of FA112
5280 l_outcome := prgp_cal_cp_gsch (
5281 l_rule_type,
5282 p_person_id,
5283 p_course_cd,
5284 p_course_version,
5285 p_cal_type,
5286 p_ci_sequence_number,
5287 g_grad_sch_set_no,
5288 0,
5289 l_gpa_cp_calc,
5290 NULL);
5291 l_message := 'Percentage Attempted units in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5292 ELSIF p_rule_type = 'LAUGSAPM' THEN
5293 l_rule_type := 'ALL_PERD_UPL' ;
5294 -- kdande; Changed the following call to include statistic type as part of FA112
5295 l_outcome := prgp_cal_cp_gsch (
5296 l_rule_type,
5297 p_person_id,
5298 p_course_cd,
5299 p_course_version,
5300 p_cal_type,
5301 p_ci_sequence_number,
5302 g_grad_sch_set_no,
5303 0,
5304 l_gpa_cp_calc,
5305 NULL);
5306 l_message := 'Percentage Attempted units in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5307 ELSIF p_rule_type = 'LAUGSCPM' THEN
5308 l_rule_type := 'CUR_PERD_UPL' ;
5309 -- kdande; Changed the following call to include statistic type as part of FA112
5310 l_outcome := prgp_cal_cp_gsch (
5311 l_rule_type,
5312 p_person_id,
5313 p_course_cd,
5314 p_course_version,
5315 p_cal_type,
5316 p_ci_sequence_number,
5317 g_grad_sch_set_no,
5318 0,
5319 l_gpa_cp_calc,
5320 NULL);
5321 l_message := 'Percentage Attempted units in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5322 ELSIF p_rule_type = 'MAUGSAM' THEN
5323 l_rule_type := 'ALL_PERD_UM' ;
5324 -- kdande; Changed the following call to include statistic type as part of FA112
5325 l_outcome := prgp_cal_cp_gsch (
5326 l_rule_type,
5327 p_person_id,
5328 p_course_cd,
5329 p_course_version,
5330 p_cal_type,
5331 p_ci_sequence_number,
5332 g_grad_sch_set_no,
5333 0,
5334 l_gpa_cp_calc,
5335 NULL);
5336 l_message := 'Attempted units in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5337 ELSIF p_rule_type = 'MAUGSCM' THEN
5338 l_rule_type := 'CUR_PERD_UM' ;
5339 -- kdande; Changed the following call to include statistic type as part of FA112
5340 l_outcome := prgp_cal_cp_gsch (
5341 l_rule_type,
5342 p_person_id,
5343 p_course_cd,
5344 p_course_version,
5345 p_cal_type,
5346 p_ci_sequence_number,
5347 g_grad_sch_set_no,
5348 0,
5349 l_gpa_cp_calc,
5350 NULL);
5351 l_message := 'Attempted units in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5352 ELSIF p_rule_type = 'LAUGSAM' THEN
5353 l_rule_type := 'ALL_PERD_UL' ;
5354 -- kdande; Changed the following call to include statistic type as part of FA112
5355 l_outcome := prgp_cal_cp_gsch (
5356 l_rule_type,
5357 p_person_id,
5358 p_course_cd,
5359 p_course_version,
5360 p_cal_type,
5361 p_ci_sequence_number,
5362 g_grad_sch_set_no,
5363 0,
5364 l_gpa_cp_calc,
5365 NULL);
5366 l_message := 'Attempted units in grading schemas in ALL Periods is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5367 ELSIF p_rule_type = 'LAUGSCM' THEN
5368 l_rule_type := 'CUR_PERD_UL' ;
5369 -- kdande; Changed the following call to include statistic type as part of FA112
5370 l_outcome := prgp_cal_cp_gsch (
5371 l_rule_type,
5372 p_person_id,
5373 p_course_cd,
5374 p_course_version,
5375 p_cal_type,
5376 p_ci_sequence_number,
5377 g_grad_sch_set_no,
5378 0,
5379 l_gpa_cp_calc,
5380 NULL);
5381 l_message := 'Attempted units in grading schemas in CURRENT Period is ' || IGS_GE_NUMBER.TO_CANN(ROUND(l_gpa_cp_calc)) ;
5382 END IF;
5383
5384 RETURN l_message ;
5385
5386 END pr_rul_msg;
5387
5388 /*
5389 evaluate the sql string and return the (last) value (only one)
5390 */
5391 FUNCTION evaluate_sql (
5392 p_select_string IN VARCHAR2 )
5393 RETURN VARCHAR2 IS
5394 v_cursor INTEGER;
5395 v_rows INTEGER;
5396 v_value VARCHAR2(4000);
5397 BEGIN
5398 v_cursor := DBMS_SQL.OPEN_CURSOR;
5399 /*
5400 only select allowed
5401 */
5402 DBMS_SQL.PARSE(v_cursor,
5403 'SELECT'||SUBSTR(LTRIM(p_select_string),7),
5404 dbms_sql.native);
5405 DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_value,4000);
5406 v_rows := DBMS_SQL.EXECUTE(v_cursor);
5407 WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
5408 LOOP
5409 DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_value);
5410 END LOOP;
5411 DBMS_SQL.CLOSE_CURSOR(v_cursor);
5412 RETURN v_value;
5413 END evaluate_sql;
5414 /*
5415
5416 TURING - the IGS_RU_RULE engine
5417
5418 */
5419 FUNCTION turing(
5420 p_rule_number IN NUMBER,
5421 p_tds IN r_tds )
5422 RETURN VARCHAR2 IS
5423 ------------------------------------------------------------------
5424 --Known limitations/enhancements and/or remarks:
5425 --
5426 --Change History:
5427 --Who When What
5428 --bannamal 08-Jul-2005 Enh# 3392088. New Turing functions are added.
5429 --svenkata 12-Dec-2001 Modifications are done w.r.t. Progression Rules Enhancement DLD. Bug No: 2146547.
5430 --smadathi 07-JUN-2001 The changes are as per enhancement bug No. 1775394 . The turin functions for person reference set evaluation,
5431 -- unit placement set evaluation, program stage set evaluation, person id group set evaluation added .
5432 --nalkumar 18-JUN-2001 This function has been modified as per the requirement of Enrolment eligibility and Validations DLD(Bug# 1830175).
5433 -- The turin functions for pct_mx_ald added.
5434 --svanukur 30-jul-2003 replaced the function IGS_PR_GEN_002.PRGP_GET_SUA_GPA_VAL that calculates the gpa with the new function
5435 -- igs_pr_cp_gpa.get_sua_all as per bug 3031749
5436 --svanukur 08-aug-2003 removed the cursor to get the unit version number since it is already available in gv_member(p_tds.member_index).f2
5437 --svanukur 18-AUG-2003 Modified the '_ER' case to pop the parameters into a PL/SQL table,t_params declared in the main function rulp_val_senna.
5438 -- Also modified the cases , p_param_1 and p_param_2 to push these into the stack. BUG# 3049903
5439 --rvangala 06-Apr-2004 Modified the '_ER' case to remove code which pushes parameters into PL/SQL table (t_params)
5440 -- modified cases , p_param_1 and p_param_2 to removes references to t_params. BUG# 3285358
5441 --stutta 21-Sep-2004 Passing p_final_ind as 'Y' in call to sua_attribute for turing funtion 'sua.pc', so that only
5442 -- finalized outcomes are picked for Conseded pass grades.Bug #3654871
5443 --swaghmar 15-Sep-2005 Bug# 4491456
5444 -------------------------------------------------------------------
5445 v_start_stack BINARY_INTEGER;
5446 v_result IGS_RU_ITEM.value%TYPE;
5447 v_no_parameters NUMBER;
5448 v_set IGS_RU_SET.sequence_number%TYPE;
5449 v_conditional IGS_RU_ITEM.value%TYPE;
5450 v_then IGS_RU_RULE.sequence_number%TYPE;
5451 v_variable_no NUMBER;
5452 v_number NUMBER;
5453 v_message_stack_index BINARY_INTEGER;
5454 l_rule_type VARCHAR2(50);
5455 l_cp_in_rule NUMBER;
5456 l_cp_result NUMBER;
5457 l_progression_period NUMBER;
5458 l_stat_type VARCHAR2(30);
5459 l_element VARCHAR2(30);
5460
5461 l_earned_cp NUMBER;
5462 l_attempted_cp NUMBER;
5463 l_gpa_value NUMBER;
5464 l_gpa_cp NUMBER;
5465 l_gpa_quality_points NUMBER;
5466 l_return_status VARCHAR2(30);
5467 l_msg_count NUMBER(2);
5468 l_msg_data VARCHAR2(2000);
5469 l_version_number igs_ps_unit_ofr_opt.version_number%TYPE;
5470 l_best_worst VARCHAR2(3);
5471 l_recommended VARCHAR2(3);
5472
5473 l_n_grd_set IGS_RU_SET.sequence_number%TYPE; -- Variable to hold grading schema set.
5474 l_c_temp igs_ru_item.value%TYPE; -- Variable to hold test type
5475
5476 BEGIN
5477 gv_turing_level := gv_turing_level + 1;
5478 /*
5479 mark the start of the stack
5480 */
5481 v_start_stack := gv_stack_index;
5482 FOR rule_items IN (
5483 SELECT rul_sequence_number,
5484 item,
5485 turin_function,
5486 named_rule,
5487 rule_number,
5488 derived_rule,
5489 set_number,
5490 value
5491 FROM IGS_RU_ITEM
5492 WHERE rul_sequence_number = p_rule_number
5493 ORDER BY item DESC )
5494 LOOP
5495 IF rule_items.turin_function IS NOT NULL
5496 THEN
5497 /*
5498 turing functions
5499 if then else
5500 */
5501 IF rule_items.turin_function = 'ifthenelse'
5502 THEN
5503 v_conditional := pop;
5504 v_no_parameters := pop;
5505 v_then := pop;
5506 clean_up_stack(v_no_parameters);
5507 v_no_parameters := pop;
5508 igs_ru_gen_001.p_evaluated_part := NULL;
5509 v_result := ifthenelse(v_conditional,v_then,pop,p_tds);
5510 clean_up_stack(v_no_parameters);
5511 push(v_result);
5512 /*
5513 * turing functions
5514 * if then
5515 * Navin 27-Aug-2001
5516 * Added as part of Bug# : 1899513.
5517 */
5518 ELSIF rule_items.turin_function = 'ifthen'
5519 THEN
5520 v_conditional := pop;
5521 v_no_parameters := pop;
5522 v_then := pop;
5523 clean_up_stack(v_no_parameters);
5524 igs_ru_gen_001.p_evaluated_part := NULL;
5525 v_result := ifthen(v_conditional,v_then,p_tds);
5526 push(v_result);
5527 /*
5528 BOOLEAN FUNCTIONS
5529 */
5530 ELSIF rule_items.turin_function = 'true'
5531 THEN
5532 push('true');
5533 ELSIF rule_items.turin_function = 'false'
5534 THEN
5535 push('false');
5536 /*
5537 numeric comparison functions
5538 */
5539 ELSIF rule_items.turin_function = 'lt'
5540 THEN
5541 push(b_to_t(IGS_GE_NUMBER.TO_NUM(pop) < IGS_GE_NUMBER.TO_NUM(pop)));
5542 ELSIF rule_items.turin_function = 'lte'
5543 THEN
5544 push(b_to_t(IGS_GE_NUMBER.TO_NUM(pop) <= IGS_GE_NUMBER.TO_NUM(pop)));
5545 ELSIF rule_items.turin_function = 'eq'
5546 THEN
5547 push(eq(pop,pop));
5548 ELSIF rule_items.turin_function = 'neq'
5549 THEN
5550 push(neq(pop,pop));
5551 ELSIF rule_items.turin_function = 'gte'
5552 THEN
5553 push(b_to_t(IGS_GE_NUMBER.TO_NUM(pop) >= IGS_GE_NUMBER.TO_NUM(pop)));
5554 ELSIF rule_items.turin_function = 'gt'
5555 THEN
5556 push(b_to_t(IGS_GE_NUMBER.TO_NUM(pop) > IGS_GE_NUMBER.TO_NUM(pop)));
5557 /*
5558 string comparisons
5559 */
5560 ELSIF rule_items.turin_function = 'slt'
5561 THEN
5562 push(b_to_t(pop < pop));
5563 ELSIF rule_items.turin_function = 'slte'
5564 THEN
5565 push(b_to_t(pop <= pop));
5566 ELSIF rule_items.turin_function = 'seq'
5567 THEN push(b_to_t(pop = pop));
5568 ELSIF rule_items.turin_function = 'sneq'
5569 THEN
5570 push(b_to_t(pop <> pop));
5571 ELSIF rule_items.turin_function = 'sgte'
5572 THEN
5573 push(b_to_t(pop >= pop));
5574 ELSIF rule_items.turin_function = 'sgt'
5575 THEN
5576 push(b_to_t(pop > pop));
5577 /*
5578 date comparisons
5579 */
5580 ELSIF rule_items.turin_function = 'dlt'
5581 THEN
5582 push(date_compare(pop,'<',pop));
5583 ELSIF rule_items.turin_function = 'dlte'
5584 THEN
5585 push(date_compare(pop,'<=',pop));
5586 ELSIF rule_items.turin_function = 'deq'
5587 THEN
5588 push(date_compare(pop,'=',pop));
5589 ELSIF rule_items.turin_function = 'dneq'
5590 THEN
5591 push(date_compare(pop,'<>',pop));
5592 ELSIF rule_items.turin_function = 'dgte'
5593 THEN
5594 push(date_compare(pop,'>=',pop));
5595 ELSIF rule_items.turin_function = 'dgt'
5596 THEN
5597 push(date_compare(pop,'>',pop));
5598 /*
5599 grade comparisons
5600 */
5601 ELSIF rule_items.turin_function = 'grd_lt'
5602 THEN
5603 push(sua_relative_grade(pop,pop,'<',pop,pop,p_tds));
5604 ELSIF rule_items.turin_function = 'grd_lte'
5605 THEN
5606 push(sua_relative_grade(pop,pop,'<=',pop,pop,p_tds));
5607 ELSIF rule_items.turin_function = 'grd_eq'
5608 THEN
5609 push(sua_relative_grade(pop,pop,'=',pop,pop,p_tds));
5610 ELSIF rule_items.turin_function = 'grd_neq'
5611 THEN
5612 push(sua_relative_grade(pop,pop,'<>',pop,pop,p_tds));
5613 ELSIF rule_items.turin_function = 'grd_gte'
5614 THEN
5615 push(sua_relative_grade(pop,pop,'>=',pop,pop,p_tds));
5616 -- smaddali added this code for the new turin function added for bug#4304688
5617 ELSIF rule_items.turin_function = 'adv_gte' THEN
5618 push(adv_relative_grade(pop,pop,'>=',pop,pop,p_tds));
5619
5620 ELSIF rule_items.turin_function = 'grd_gt'
5621 THEN
5622 push(sua_relative_grade(pop,pop,'>',pop,pop,p_tds));
5623 ELSIF rule_items.turin_function = 'isnull'
5624 THEN
5625 push(b_to_t(pop IS NULL));
5626 ELSIF rule_items.turin_function = 'isnotnull'
5627 THEN
5628 push(b_to_t(pop IS NOT NULL));
5629 /*
5630 boolean set functions
5631 */
5632 ELSIF rule_items.turin_function = 'null_set'
5633 THEN
5634 push(b_to_t(gv_set(pop).first IS NULL));
5635 ELSIF rule_items.turin_function = 'in_set'
5636 THEN
5637 push(in_set(pop,pop));
5638 ELSIF rule_items.turin_function = 'not_in_set'
5639 THEN
5640 push(b_to_t(in_set(pop,pop) = 'false'));
5641
5642 ELSIF rule_items.turin_function = 'test_grade'
5643 THEN
5644 l_c_temp := pop; -- holds the test type value.
5645 push(l_c_temp); -- push back the test type value ( it's required as clear_parameter will try to remove the parameter from the stack.
5646 push(test_grade(l_c_temp)); -- push the return value (set number) into the stack.
5647
5648 ELSIF rule_items.turin_function = 'in_grd'
5649 THEN
5650 l_n_grd_set := pop; -- holds the grading schema set number;
5651 push(in_set(pop,l_n_grd_set)); -- push the return type (boolean) into the stack
5652
5653 ELSIF rule_items.turin_function = 'not_in_grd'
5654 THEN
5655 push(not_in_grd(pop,pop)); -- push the return type (boolean) into the stack
5656
5657 /*
5658 conjunction functions
5659 */
5660 ELSIF rule_items.turin_function = 'and'
5661 THEN
5662 push(and_func(pop,pop));
5663 ELSIF rule_items.turin_function = 'or'
5664 THEN
5665 push(or_func(pop,pop));
5666 /*
5667 parent rule result (used in message rule)
5668 */
5669 ELSIF rule_items.turin_function = 'outcome'
5670 THEN
5671 push(p_tds.rule_outcome);
5672 /*
5673 VALUE FUNCTIONS
5674 numeric functions
5675 */
5676 ELSIF rule_items.turin_function = 'plus'
5677 THEN
5678 push(pop + pop);
5679 ELSIF rule_items.turin_function = 'subtract'
5680 THEN
5681 push(pop - pop);
5682 ELSIF rule_items.turin_function = 'mult'
5683 THEN
5684 push(pop * pop);
5685 ELSIF rule_items.turin_function = 'divide'
5686 THEN
5687 push(divide(pop,pop));
5688 ELSIF rule_items.turin_function = 'round'
5689 THEN
5690 push(ROUND(pop,pop));
5691 ELSIF rule_items.turin_function = 'trunc'
5692 THEN
5693 push(TRUNC(pop,pop));
5694 ELSIF rule_items.turin_function = 'max'
5695 THEN
5696 push(max_func(pop,pop));
5697 ELSIF rule_items.turin_function = 'min'
5698 THEN
5699 push(min_func(pop,pop));
5700 ELSIF rule_items.turin_function = 'total_cp'
5701 THEN
5702 push(IGS_EN_GEN_001.enrp_clc_sca_pass_cp(p_person_id,p_course_cd,TRUNC(SYSDATE)));
5703 ELSIF rule_items.turin_function = 'stage_comp'
5704 THEN
5705 push(do_course_stage(pop));
5706 /*
5707 SET FUNCTIONS
5708 return number
5709 */
5710 ELSIF rule_items.turin_function = 'members'
5711 THEN
5712 push(members(pop));
5713 /*
5714 return set
5715 */
5716 ELSIF rule_items.turin_function = 'new_set'
5717 THEN
5718 push(new_set);
5719 ELSIF rule_items.turin_function = 'output'
5720 THEN
5721 push(new_set);
5722 ELSIF rule_items.turin_function = 'show_set'
5723 THEN
5724 push(show_set(pop));
5725 ELSIF rule_items.turin_function = 'show_us'
5726 THEN
5727 push(show_us_set(pop));
5728 ELSIF rule_items.turin_function = 'intersect'
5729 THEN
5730 push(set_intersect(pop,pop));
5731 ELSIF rule_items.turin_function = 'union'
5732 THEN
5733 push(set_union(pop,pop));
5734 ELSIF rule_items.turin_function = 'minus'
5735 THEN
5736 push(set_minus(pop,pop));
5737 ELSIF rule_items.turin_function = 'setelement'
5738 THEN
5739 push(add_member(new_set,pop,pop,'','',''));
5740 ELSIF rule_items.turin_function = 'exp_unit'
5741 THEN
5742 push(expand_uoo(new_set,pop,pop));
5743 ELSIF rule_items.turin_function = 'snarts'
5744 THEN
5745 push(get_snarts(pop,pop));
5746 /*
5747 student sets
5748 */
5749 ELSIF rule_items.turin_function = 'student'
5750 THEN
5751 push(student);
5752 ELSIF rule_items.turin_function = 'adv_stnd'
5753 THEN
5754 push(advanced_standing);
5755 ELSIF rule_items.turin_function = 'asul_set'
5756 THEN
5757 push(advanced_standing_unit_level);
5758 ELSIF rule_items.turin_function = 'stdnt_us'
5759 THEN
5760 push(student_us);
5761 ELSIF rule_items.turin_function = 'make_set'
5762 THEN
5763 push(make_set(pop));
5764 /*
5765 LOOP THROUGH SET APPLYING RULE
5766 return number
5767 */
5768 ELSIF rule_items.turin_function = 'sum'
5769 THEN
5770 v_set := pop;
5771 v_no_parameters := pop;
5772 v_result := sum_func(v_set,pop);
5773 clean_up_stack(v_no_parameters);
5774 push(v_result);
5775 /*
5776 return set
5777 */
5778 ELSIF rule_items.turin_function = 'for_expand'
5779 THEN
5780 v_set := pop;
5781 v_no_parameters := pop;
5782 v_result := for_expand(v_set,pop);
5783 clean_up_stack(v_no_parameters);
5784 push(v_result);
5785 ELSIF rule_items.turin_function = 'cascade'
5786 THEN
5787 v_set := pop;
5788 v_no_parameters := pop;
5789 v_result := cascade_expand(new_set,v_set,pop);
5790 clean_up_stack(v_no_parameters);
5791 push(v_result);
5792 ELSIF rule_items.turin_function = 'select'
5793 THEN
5794 v_set := pop;
5795 v_no_parameters := pop;
5796 v_result := select_set(v_set,pop);
5797 clean_up_stack(v_no_parameters);
5798 push(v_result);
5799 ELSIF rule_items.turin_function = 'selectn'
5800 THEN
5801 v_number := pop;
5802 v_set := pop;
5803 v_no_parameters := pop;
5804 v_result := select_N_members(v_number,v_set,pop);
5805 clean_up_stack(v_no_parameters);
5806 push(v_result);
5807 /*
5808 TARGET MEMBER ATTRIBUTE VALUES
5809 member values (set in loop functions)
5810 */
5811 ELSIF rule_items.turin_function = 'g_unit_cd'
5812 THEN
5813 push(gv_member(p_tds.member_index).f1);
5814 ELSIF rule_items.turin_function = 'g_unit_ver'
5815 THEN
5816 push(gv_member(p_tds.member_index).f2);
5817 ELSIF rule_items.turin_function = 'g_cal_type'
5818 THEN
5819 push(gv_member(p_tds.member_index).f3);
5820 ELSIF rule_items.turin_function = 'g_ci_seq'
5821 THEN
5822 push(gv_member(p_tds.member_index).f4);
5823 ELSIF rule_items.turin_function = 'g_uoo_id'
5824 THEN
5825 push(gv_member(p_tds.member_index).f5);
5826 ELSIF rule_items.turin_function = 'setfldval'
5827 THEN
5828 push(set_field_val(pop,pop));
5829 /*
5830 member index functions
5831 */
5832 ELSIF rule_items.turin_function = 'setmbridx'
5833 THEN
5834 push(gv_set(pop).first);
5835 ELSIF rule_items.turin_function = 'mbridx'
5836 THEN
5837 push(p_tds.member_index);
5838 /*
5839 SET MEMBER ATTRIBUTES
5840 unit attributes
5841 */
5842 ELSIF rule_items.turin_function = 'rep_ind'
5843 THEN
5844 push(uv_attribute('rep_ind',p_tds));
5845 ELSIF rule_items.turin_function = 'level'
5846 THEN
5847 push(IGS_PS_GEN_002.CRSP_GET_UN_LVL(gv_member(p_tds.member_index).f1,
5848 gv_member(p_tds.member_index).f2,
5849 p_course_cd,
5850 p_course_version));
5851 ELSIF rule_items.turin_function = 'uv_cp'
5852 THEN
5853 push(uv_credit_points(gv_member(p_tds.member_index).f1,
5854 gv_member(p_tds.member_index).f2));
5855 /*
5856 student unit attempt attributes
5857 */
5858 ELSIF rule_items.turin_function = 'status'
5859 THEN
5860 push(sua_attribute('status',p_tds.member_index,'N'));
5861 ELSIF rule_items.turin_function = 'sua.status'
5862 THEN
5863 push(sua_attribute('status',pop,'N'));
5864 ELSIF rule_items.turin_function = 'result'
5865 THEN
5866 push(sua_attribute('result',p_tds.member_index,'N'));
5867 ELSIF rule_items.turin_function = 'sua_grade'
5868 THEN
5869 push(sua_attribute('grade',p_tds.member_index,'N'));
5870 push(sua_attribute('grd_sch',p_tds.member_index,'N'));
5871 -- smaddali added this code for the new turin function added for bug#4304688
5872 ELSIF rule_items.turin_function = 'adv_grd' THEN
5873 push(adv_attribute('grade',p_tds.member_index));
5874 push(adv_attribute('grd_sch',p_tds.member_index));
5875
5876 ELSIF rule_items.turin_function = 'sua_mark'
5877 THEN
5878 push(sua_attribute('mark',p_tds.member_index,'N'));
5879 ELSIF rule_items.turin_function = 'outcome_dt'
5880 THEN
5881 push(sua_attribute('outcome_dt',p_tds.member_index,'N'));
5882 ELSIF rule_items.turin_function = 'finlresult'
5883 THEN
5884 push(sua_attribute('result',p_tds.member_index,'Y'));
5885 ELSIF rule_items.turin_function = 'finalgrade'
5886 THEN
5887 push(sua_attribute('grade',p_tds.member_index,'Y'));
5888 push(sua_attribute('grd_sch',p_tds.member_index,'Y'));
5889
5890 ELSIF rule_items.turin_function = 'sua.pc'
5891 THEN
5892 push(sua_attribute('pass_conceded',p_tds.member_index,'Y'));
5893 ELSIF rule_items.turin_function = 'credit_pts'
5894 THEN
5895 push(sua_credit_points(p_tds.member_index));
5896 ELSIF rule_items.turin_function = 'gpa'
5897 THEN
5898
5899
5900 l_gpa_value := NULL;
5901 l_best_worst := pop;
5902 l_recommended := pop;
5903
5904 igs_pr_cp_gpa.get_sua_all (
5905 p_person_id => p_person_id,
5906 p_course_cd => p_course_cd,
5907 p_unit_cd => gv_member(p_tds.member_index).f1,
5908 p_unit_version_number => gv_member(p_tds.member_index).f2,
5909 p_teach_cal_type => gv_member(p_tds.member_index).f3,
5910 p_teach_ci_sequence_number => gv_member(p_tds.member_index).f4,
5911 p_stat_type => NULL,
5912 p_system_stat => NULL,
5913 p_earned_cp =>l_earned_cp,
5914 p_attempted_cp =>l_attempted_cp,
5915 p_gpa_value =>l_gpa_value,
5916 p_gpa_cp =>l_gpa_cp ,
5917 p_gpa_quality_points=>l_gpa_quality_points,
5918 p_init_msg_list => fnd_api.g_true ,
5919 p_return_status => l_return_status ,
5920 p_msg_count =>l_msg_count ,
5921 p_msg_data => l_msg_data ,
5922 p_uoo_id => gv_member(p_tds.member_index).f5) ;
5923
5924 push(l_gpa_value);
5925
5926 ELSIF rule_items.turin_function = 'prg_prd'
5927 THEN
5928 push(b_to_t(IGS_PR_GEN_002.prgp_get_sua_prg_num(p_cal_type,
5929 p_ci_sequence_number,
5930 pop + 1, /* 1 is current period, i like 0 */
5931 p_person_id,
5932 p_course_cd,
5933 gv_member(p_tds.member_index).f1,
5934 gv_member(p_tds.member_index).f3,
5935 gv_member(p_tds.member_index).f4,
5936 gv_member(p_tds.member_index).f5) = 'Y'));
5937 ELSIF rule_items.turin_function = 'period'
5938 THEN
5939 push(IGS_CA_GEN_001.CALP_GET_RLTV_TIME(p_cal_type,
5940 p_ci_sequence_number,
5941 gv_member(p_tds.member_index).f3,
5942 gv_member(p_tds.member_index).f4));
5943 ELSIF rule_items.turin_function = 'wam_wyt'
5944 THEN
5945 push(wam_weighting(p_tds.member_index));
5946 ELSIF rule_items.turin_function = 'wam'
5947 THEN
5948 push(igs_pr_gen_002.prgp_get_sua_wam(p_person_id,
5949 p_course_cd,
5950 gv_member(p_tds.member_index).f1,
5951 gv_member(p_tds.member_index).f3,
5952 gv_member(p_tds.member_index).f4,
5953 pop, /* use recomended */
5954 pop, /* abort when missing */
5955 pop,
5956 gv_member(p_tds.member_index).f5)); /* wam type */
5957 /*
5958 sua order attributes
5959 */
5960 ELSIF rule_items.turin_function = 's_latest'
5961 THEN
5962 push(sua_select_unique('last',p_tds.member_index,p_tds.set_number));
5963 ELSIF rule_items.turin_function = 's_earliest'
5964 THEN
5965 push(sua_select_unique('first',p_tds.member_index,p_tds.set_number));
5966 ELSIF rule_items.turin_function = 's_maxcpm'
5967 THEN
5968 push(sua_select_unique('maxcp',p_tds.member_index,p_tds.set_number));
5969 ELSIF rule_items.turin_function = 's_mincpm'
5970 THEN
5971 push(sua_select_unique('mincp',p_tds.member_index,p_tds.set_number));
5972 /*
5973 sua attributes of duplicates within a set
5974 */
5975 ELSIF rule_items.turin_function = 'dup_cnt'
5976 THEN
5977 push(duplicate_count(p_tds));
5978 ELSIF rule_items.turin_function = 'latest'
5979 THEN
5980 push(sua_select_unique('last',p_tds.member_index,
5981 sua_duplicate_set(p_tds)));
5982 ELSIF rule_items.turin_function = 'earliest'
5983 THEN
5984 push(sua_select_unique('first',p_tds.member_index,
5985 sua_duplicate_set(p_tds)));
5986 ELSIF rule_items.turin_function = 'maxcpm'
5987 THEN
5988 push(sua_select_unique('maxcp',p_tds.member_index,
5989 sua_duplicate_set(p_tds)));
5990 ELSIF rule_items.turin_function = 'mincpm'
5991 THEN
5992 push(sua_select_unique('mincp',p_tds.member_index,
5993 sua_duplicate_set(p_tds)));
5994 /*
5995 advanced standing unit level attributes
5996 */
5997 ELSIF rule_items.turin_function = 'asul_level'
5998 THEN
5999 push(asul_attribute('level',p_tds));
6000 ELSIF rule_items.turin_function = 'asul_cp'
6001 THEN
6002 push(asul_attribute('credit_points',p_tds));
6003
6004 -- for person reference set evaluation
6005 ELSIF rule_items.turin_function = 'ref_set' THEN
6006 push(ref_set(gv_stack_index)) ;
6007 -- for unit placement set evaluation
6008 ELSIF rule_items.turin_function = 'plc_chk' THEN
6009 push(plc_chk(gv_stack_index)) ;
6010 -- for program stage set evaluation
6011 ELSIF rule_items.turin_function = 'stg_set' THEN
6012 push(stg_set(gv_stack_index)) ;
6013 -- for person id group set evaluation
6014 ELSIF rule_items.turin_function = 'perid_chk' THEN
6015 push(perid_chk(gv_stack_index)) ;
6016
6017 --Next ELSIF part is added as per the requirement of
6018 --Enrolment eligibility and Validations DLD (For form IGSEN058) BUG# 1830175.
6019 ELSIF rule_items.turin_function = 'pct_mx_ald'
6020 THEN
6021 push(rulp_get_alwd_cp(pop,p_param_1,p_param_2));
6022
6023 /*
6024 student UNIT set attempt attributes
6025 */
6026 ELSIF rule_items.turin_function = 'us_status'
6027 THEN
6028 push(susa_attribute('status',p_tds));
6029 ELSIF rule_items.turin_function = 'susa.psi'
6030 THEN
6031 push(susa_attribute('psi',p_tds));
6032 /*
6033 unit set attributes
6034 */
6035 ELSIF rule_items.turin_function = 'us.usc'
6036 THEN
6037 push(us_attribute('usc',p_tds));
6038 /*
6039 special set functions
6040 select the first member from a set (returns set with one member)
6041 */
6042 ELSIF rule_items.turin_function = 'first'
6043 THEN
6044 push(first(pop));
6045 /*
6046 date
6047 */
6048 ELSIF rule_items.turin_function = 'date'
6049 THEN
6050 null;
6051 /*
6052 string functions
6053 */
6054 ELSIF rule_items.turin_function = 'string'
6055 THEN
6056 push(REPLACE(pop,cst_space,' '));
6057 ELSIF rule_items.turin_function = 'concat'
6058 THEN
6059 push(pop||pop);
6060 ELSIF rule_items.turin_function = 'to_string'
6061 THEN
6062 null;
6063 ELSIF rule_items.turin_function = 'displayset'
6064 THEN
6065 push(display_set(pop));
6066 ELSIF rule_items.turin_function = 'pop_mess'
6067 THEN
6068 push(pop_message);
6069 /*
6070 SPECIAL FUNCTIONS
6071 evaluate an sql statment
6072 */
6073 ELSIF rule_items.turin_function = 'sql'
6074 THEN
6075 push(evaluate_sql(pop));
6076 /*
6077 evaluate a RULE
6078 */
6079 ELSIF rule_items.turin_function = 'turing'
6080 THEN
6081 push(turing(pop,p_tds));
6082 ELSIF rule_items.turin_function = 'turing_fm'
6083 THEN
6084 v_message_stack_index := gv_message_stack_index;
6085 push(turing(pop,p_tds));
6086 gv_message_stack_index := v_message_stack_index;
6087 /*
6088 get derived RULE number
6089 */
6090 ELSIF rule_items.turin_function = 'unit_rule'
6091 THEN
6092 push(get_unit_rule(pop,pop,pop));
6093 ELSIF rule_items.turin_function = 'crs_rule'
6094 THEN
6095 push(get_course_rule(pop,pop,pop));
6096 ELSIF rule_items.turin_function = 'us_rule'
6097 THEN
6098 push(get_us_rule(pop,pop,pop));
6099 ELSIF rule_items.turin_function = 'fdfr_rule'
6100 THEN
6101 push(get_fdfr_rule(pop,pop,pop,pop,pop));
6102 ELSIF rule_items.turin_function = 'cs_rule'
6103 THEN
6104 push(get_crs_stg_rule(pop,pop,pop,pop));
6105 /*
6106 item.subitem
6107 */
6108 ELSIF rule_items.turin_function = 'dot'
6109 THEN
6110 null;
6111
6112 /*
6113 type coercion
6114 */
6115 ELSIF rule_items.turin_function = 'coerce'
6116 THEN
6117 null;
6118 /*
6119 global variables (per senna call)
6120 */
6121 ELSIF rule_items.turin_function = 'set_var'
6122 THEN
6123 v_variable_no := pop;
6124 gt_variable(v_variable_no) := pop;
6125 push(gt_variable(v_variable_no));
6126 ELSIF rule_items.turin_function = 'get_var'
6127 THEN
6128 push(get_variable(pop));
6129 /*
6130 FUNDAMENTAL TURING FUNCTIONS
6131 function parameters
6132 */
6133 ELSIF rule_items.turin_function = '$'
6134 THEN
6135 /*
6136 fetch and push parameter
6137 */
6138 push(get_parameter(v_start_stack - pop + 1));
6139 /*
6140 invisible functions
6141 */
6142 ELSIF rule_items.turin_function = '_ER'
6143 THEN
6144 /*
6145 evaluate RULE
6146 */
6147 v_no_parameters := pop;
6148
6149 v_result := turing(pop,p_tds);
6150 clean_up_stack(v_no_parameters);
6151 push(v_result);
6152
6153
6154 ELSIF rule_items.turin_function = 'pr_rul_msg' THEN
6155 -- This turing function check has been included as part of Progression Rules Enhancement DLD
6156 -- to display messages to the user when the rule is evaluated. Bug No: 2146547 by svenkata
6157 l_rule_type := pop ;
6158 push(pr_rul_msg(l_rule_type));
6159
6160 ELSIF rule_items.turin_function = 'pr_rul_cp' THEN
6161 -- This turing function check has been included as part of Progression Rules Enhancement DLD
6162 -- to evaluate Progression rules. Bug No: 2146547 by svenkata
6163 l_rule_type := pop ;
6164 push ( prgp_cal_cp( l_rule_type,
6165 p_person_id,
6166 p_course_cd,
6167 p_course_version,
6168 p_cal_type,
6169 p_ci_sequence_number));
6170
6171 ELSIF rule_items.turin_function = 'pr_rul_gcp' THEN
6172 -- This turing function check has been included as part of Progression Rules Enhancement DLD
6173 -- to evaluate Progression rules. Bug No: 2146547 by svenkata
6174 l_rule_type := pop; -- this will get the unique code defined
6175 l_cp_in_rule := pop; -- To get the set number for the grading schema defined in the rule
6176 IF (INSTR (l_rule_type, '_ST') > 0) THEN
6177 l_stat_type := pop;
6178 END IF;
6179 g_grad_sch_set_no := pop; -- units of grade percentage or number defined in the rule,
6180
6181 -- The following code will balance the stack since we have poped two values for this rule.
6182 IF (INSTR (l_rule_type, '_ST') > 0) THEN
6183 gv_stack_index := gv_stack_index + 3;
6184 ELSE
6185 gv_stack_index := gv_stack_index + 2;
6186 END IF;
6187 -- kdande; Changed the following call to include statistic type as part of FA112
6188 push( prgp_cal_cp_gsch( l_rule_type,
6189 p_person_id,
6190 p_course_cd,
6191 p_course_version,
6192 p_cal_type,
6193 p_ci_sequence_number,
6194 g_grad_sch_set_no,
6195 l_cp_in_rule,
6196 l_cp_result,
6197 l_stat_type));
6198 /*
6199 INPUT PARAMETERS
6200 */
6201 ELSIF rule_items.turin_function = 'chrt_rul' THEN
6202 l_rule_type := pop; -- this will get the unique code defined for the kind of rule used
6203 g_grad_sch_set_no := pop; -- Set ID for progression status
6204 gv_stack_index := gv_stack_index + 1;
6205 Push( prgp_get_chrt( l_rule_type,
6206 g_grad_sch_set_no
6207 ));
6208 -- kdande; Added the following ELSIF for stcl as part of FA112
6209 ELSIF rule_items.turin_function = 'stcl' THEN
6210 l_rule_type := pop;
6211 l_stat_type := pop;
6212 l_element := pop;
6213 l_cp_in_rule := pop;
6214 gv_stack_index := gv_stack_index + 3;
6215 push (prgp_cal_stat (l_rule_type, l_stat_type, l_element, l_cp_in_rule));
6216 -- kdande; Added the following ELSIF for calcstcp as part of FA112
6217 ELSIF rule_items.turin_function = 'calcstcp' THEN
6218 l_rule_type := pop;
6219 l_cp_in_rule := pop;
6220 l_stat_type := pop;
6221 IF (l_rule_type = 'NCPPNPP') THEN
6222 l_progression_period := pop;
6223 gv_stack_index := gv_stack_index + 3;
6224 ELSIF (l_rule_type = 'NCPCPP') THEN
6225 l_progression_period := 0;
6226 gv_stack_index := gv_stack_index + 2;
6227 ELSE
6228 gv_stack_index := gv_stack_index + 2;
6229 END IF;
6230 push (sua_credit_points_st (l_rule_type, l_stat_type, l_cp_in_rule, l_progression_period));
6231 ELSIF rule_items.turin_function = 'p_rule_cd'
6232 THEN
6233 push(p_rule_call_name);
6234 ELSIF rule_items.turin_function = 'p_prsn_id'
6235 THEN
6236 push(p_person_id);
6237 ELSIF rule_items.turin_function = 'p_crs_cd'
6238 THEN
6239 push(p_course_cd);
6240 ELSIF rule_items.turin_function = 'p_crs_ver'
6241 THEN
6242 push(p_course_version);
6243 ELSIF rule_items.turin_function = 'p_unit_cd'
6244 THEN
6245 push(p_unit_cd);
6246 ELSIF rule_items.turin_function = 'p_unit_ver'
6247 THEN
6248 push(p_unit_version);
6249 ELSIF rule_items.turin_function = 'p_cal_type'
6250 THEN
6251 push(p_cal_type);
6252 ELSIF rule_items.turin_function = 'p_ci_seq'
6253 THEN
6254 push(p_ci_sequence_number);
6255 ELSIF rule_items.turin_function = 'p_param_1'
6256 THEN
6257 push(p_param_1);
6258 ELSIF rule_items.turin_function = 'p_param_2'
6259 THEN
6260 push(p_param_2);
6261 ELSIF rule_items.turin_function = 'p_param_3'
6262 THEN
6263 push(p_param_3);
6264 ELSIF rule_items.turin_function = 'p_param_4'
6265 THEN
6266 push(p_param_4);
6267 ELSIF rule_items.turin_function = 'p_param_5'
6268 THEN
6269 push(p_param_5);
6270 ELSIF rule_items.turin_function = 'p_param_6'
6271 THEN
6272 push(p_param_6);
6273 ELSIF rule_items.turin_function = 'p_param_7'
6274 THEN
6275 push(p_param_7);
6276 ELSIF rule_items.turin_function = 'p_param_8'
6277 THEN
6278 push(p_param_8);
6279 ELSIF rule_items.turin_function = 'p_param_9'
6280 THEN
6281 push(p_param_9);
6282 ELSIF rule_items.turin_function = 'p_param_10'
6283 THEN
6284 push(p_param_10);
6285 ELSIF rule_items.turin_function = 'p_param_11'
6286 THEN
6287 push(p_param_11);
6288 ELSIF rule_items.turin_function = 'p_param_12'
6289 THEN
6290 push(p_param_12);
6291 ELSIF rule_items.turin_function = 'p_param_13'
6292 THEN
6293 push(p_param_13);
6294 ELSIF rule_items.turin_function = 'p_param_14'
6295 THEN
6296 push(p_param_14);
6297 ELSIF rule_items.turin_function = 'p_param_15'
6298 THEN
6299 push(p_param_15);
6300 ELSIF rule_items.turin_function = 'p_param_16'
6301 THEN
6302 push(p_param_16);
6303 ELSIF rule_items.turin_function = 'p_param_17'
6304 THEN
6305 push(p_param_17);
6306 ELSIF rule_items.turin_function = 'p_param_18'
6307 THEN
6308 push(p_param_18);
6309 ELSIF rule_items.turin_function = 'p_param_19'
6310 THEN
6311 push(p_param_19);
6312 ELSIF rule_items.turin_function = 'p_param_20'
6313 THEN
6314 push(p_param_20);
6315 ELSIF rule_items.turin_function = 'p_param_21'
6316 THEN
6317 push(p_param_21);
6318 ELSIF rule_items.turin_function = 'p_param_22'
6319 THEN
6320 push(p_param_22);
6321 ELSIF rule_items.turin_function = 'p_param_23'
6322 THEN
6323 push(p_param_23);
6324 ELSIF rule_items.turin_function = 'p_param_24'
6325 THEN
6326 push(p_param_24);
6327 ELSIF rule_items.turin_function = 'p_param_25'
6328 THEN
6329 push(p_param_25);
6330 ELSIF rule_items.turin_function = 'p_param_26'
6331 THEN
6332 push(p_param_26);
6333 ELSIF rule_items.turin_function = 'p_param_27'
6334 THEN
6335 push(p_param_27);
6336 ELSIF rule_items.turin_function = 'p_param_28'
6337 THEN
6338 push(p_param_28);
6339 ELSIF rule_items.turin_function = 'p_param_29'
6340 THEN
6341 push(p_param_29);
6342 ELSIF rule_items.turin_function = 'p_param_30'
6343 THEN
6344 push(p_param_30);
6345 ELSIF rule_items.turin_function = 'p_param_31'
6346 THEN
6347 push(p_param_31);
6348 ELSIF rule_items.turin_function = 'p_param_32'
6349 THEN
6350 push(p_param_32);
6351 ELSIF rule_items.turin_function = 'p_param_33'
6352 THEN
6353 push(p_param_33);
6354 ELSIF rule_items.turin_function = 'p_param_34'
6355 THEN
6356 push(p_param_34);
6357 ELSIF rule_items.turin_function = 'p_param_35'
6358 THEN
6359 push(p_param_35);
6360 ELSIF rule_items.turin_function = 'p_param_36'
6361 THEN
6362 push(p_param_36);
6363 ELSIF rule_items.turin_function = 'p_param_37'
6364 THEN
6365 push(p_param_37);
6366 ELSIF rule_items.turin_function = 'p_param_38'
6367 THEN
6368 push(p_param_38);
6369 ELSIF rule_items.turin_function = 'p_param_39'
6370 THEN
6371 push(p_param_39);
6372 ELSIF rule_items.turin_function = 'p_param_40'
6373 THEN
6374 push(p_param_40);
6375 ELSIF rule_items.turin_function = 'p_param_41'
6376 THEN
6377 push(p_param_41);
6378 ELSIF rule_items.turin_function = 'p_param_42'
6379 THEN
6380 push(p_param_42);
6381 ELSIF rule_items.turin_function = 'p_param_43'
6382 THEN
6383 push(p_param_43);
6384 ELSIF rule_items.turin_function = 'p_param_44'
6385 THEN
6386 push(p_param_44);
6387 ELSIF rule_items.turin_function = 'p_param_45'
6388 THEN
6389 push(p_param_45);
6390 ELSIF rule_items.turin_function = 'p_param_46'
6391 THEN
6392 push(p_param_46);
6393 ELSIF rule_items.turin_function = 'p_param_47'
6394 THEN
6395 push(p_param_47);
6396 ELSIF rule_items.turin_function = 'p_param_48'
6397 THEN
6398 push(p_param_48);
6399 ELSIF rule_items.turin_function = 'p_param_49'
6400 THEN
6401 push(p_param_49);
6402 ELSIF rule_items.turin_function = 'p_param_50'
6403 THEN
6404 push(p_param_50);
6405 ELSIF rule_items.turin_function = 'p_param_51'
6406 THEN
6407 push(p_param_51);
6408 ELSIF rule_items.turin_function = 'p_param_52'
6409 THEN
6410 push(p_param_52);
6411 ELSIF rule_items.turin_function = 'p_param_53'
6412 THEN
6413 push(p_param_53);
6414 ELSIF rule_items.turin_function = 'p_param_54'
6415 THEN
6416 push(p_param_54);
6417
6418 ELSIF rule_items.turin_function = 'fiuntcd'
6419 THEN
6420 Push(p_param_1);
6421 ELSIF rule_items.turin_function = 'fiprgtypl'
6422 THEN
6423 Push(p_param_2);
6424 ELSIF rule_items.turin_function = 'fiorgunt'
6425 THEN
6426 Push(p_param_3);
6427 ELSIF rule_items.turin_function = 'fiuntmode'
6428 THEN
6429 Push(p_param_4);
6430 ELSIF rule_items.turin_function = 'fiuntcls'
6431 THEN
6432 Push(p_param_5);
6433
6434 /* need room 99/06/29
6435 ELSIF rule_items.turin_function = 'p_param_55'
6436 THEN
6437 push(p_param_55);
6438 ELSIF rule_items.turin_function = 'p_param_56'
6439 THEN
6440 push(p_param_56);
6441 ELSIF rule_items.turin_function = 'p_param_57'
6442 THEN
6443 push(p_param_57);
6444 ELSIF rule_items.turin_function = 'p_param_58'
6445 THEN
6446 push(p_param_58);
6447 ELSIF rule_items.turin_function = 'p_param_59'
6448 THEN
6449 push(p_param_59);
6450 ELSIF rule_items.turin_function = 'p_param_60'
6451 THEN
6452 push(p_param_60);
6453 ELSIF rule_items.turin_function = 'p_param_61'
6454 THEN
6455 push(p_param_61);
6456 ELSIF rule_items.turin_function = 'p_param_62'
6457 THEN
6458 push(p_param_62);
6459 ELSIF rule_items.turin_function = 'p_param_63'
6460 THEN
6461 push(p_param_63);
6462 ELSIF rule_items.turin_function = 'p_param_64'
6463 THEN
6464 push(p_param_64);
6465 ELSIF rule_items.turin_function = 'p_param_65'
6466 THEN
6467 push(p_param_65);
6468 ELSIF rule_items.turin_function = 'p_param_66'
6469 THEN
6470 push(p_param_66);
6471 ELSIF rule_items.turin_function = 'p_param_67'
6472 THEN
6473 push(p_param_67);
6474 ELSIF rule_items.turin_function = 'p_param_68'
6475 THEN
6476 push(p_param_68);
6477 ELSIF rule_items.turin_function = 'p_param_69'
6478 THEN
6479 push(p_param_69);
6480 ELSIF rule_items.turin_function = 'p_param_70'
6481 THEN
6482 push(p_param_70);
6483 ELSIF rule_items.turin_function = 'p_param_71'
6484 THEN
6485 push(p_param_71);
6486 ELSIF rule_items.turin_function = 'p_param_72'
6487 THEN
6488 push(p_param_72);
6489 ELSIF rule_items.turin_function = 'p_param_73'
6490 THEN
6491 push(p_param_73);
6492 ELSIF rule_items.turin_function = 'p_param_74'
6493 THEN
6494 push(p_param_74);
6495 ELSIF rule_items.turin_function = 'p_param_75'
6496 THEN
6497 push(p_param_75);
6498 ELSIF rule_items.turin_function = 'p_param_76'
6499 THEN
6500 push(p_param_76);
6501 ELSIF rule_items.turin_function = 'p_param_77'
6502 THEN
6503 push(p_param_77);
6504 ELSIF rule_items.turin_function = 'p_param_78'
6505 THEN
6506 push(p_param_78);
6507 ELSIF rule_items.turin_function = 'p_param_79'
6508 THEN
6509 push(p_param_79);
6510 ELSIF rule_items.turin_function = 'p_param_80'
6511 THEN
6512 push(p_param_80);
6513 ELSIF rule_items.turin_function = 'p_param_81'
6514 THEN
6515 push(p_param_81);
6516 ELSIF rule_items.turin_function = 'p_param_82'
6517 THEN
6518 push(p_param_82);
6519 ELSIF rule_items.turin_function = 'p_param_83'
6520 THEN
6521 push(p_param_83);
6522 ELSIF rule_items.turin_function = 'p_param_84'
6523 THEN
6524 push(p_param_84);
6525 ELSIF rule_items.turin_function = 'p_param_85'
6526 THEN
6527 push(p_param_85);
6528 ELSIF rule_items.turin_function = 'p_param_86'
6529 THEN
6530 push(p_param_86);
6531 ELSIF rule_items.turin_function = 'p_param_87'
6532 THEN
6533 push(p_param_87);
6534 ELSIF rule_items.turin_function = 'p_param_88'
6535 THEN
6536 push(p_param_88);
6537 ELSIF rule_items.turin_function = 'p_param_89'
6538 THEN
6539 push(p_param_89);
6540 ELSIF rule_items.turin_function = 'p_param_90'
6541 THEN
6542 push(p_param_90);
6543 ELSIF rule_items.turin_function = 'p_param_91'
6544 THEN
6545 push(p_param_91);
6546 ELSIF rule_items.turin_function = 'p_param_92'
6547 THEN
6548 push(p_param_92);
6549 ELSIF rule_items.turin_function = 'p_param_93'
6550 THEN
6551 push(p_param_93);
6552 ELSIF rule_items.turin_function = 'p_param_94'
6553 THEN
6554 push(p_param_94);
6555 ELSIF rule_items.turin_function = 'p_param_95'
6556 THEN
6557 push(p_param_95);
6558 ELSIF rule_items.turin_function = 'p_param_96'
6559 THEN
6560 push(p_param_96);
6561 ELSIF rule_items.turin_function = 'p_param_97'
6562 THEN
6563 push(p_param_97);
6564 ELSIF rule_items.turin_function = 'p_param_98'
6565 THEN
6566 push(p_param_98);
6567 ELSIF rule_items.turin_function = 'p_param_99'
6568 THEN
6569 push(p_param_99);
6570 */
6571 ELSE
6572 log_error('turing',
6573 'Unknown turing function:'||
6574 rule_items.turin_function||
6575 ': rule='||p_rule_number);
6576 END IF;
6577 ELSIF rule_items.named_rule IS NOT NULL
6578 THEN
6579 push(rule_items.named_rule);
6580 /*
6581 parameter count
6582 */
6583 push(NVL(rule_items.value,0));
6584 ELSIF rule_items.rule_number IS NOT NULL
6585 THEN
6586 /*
6587 repush the parameters
6588 */
6589 push_params(v_start_stack,NVL(rule_items.value,0));
6590 push(rule_items.rule_number);
6591 /*
6592 parameter count
6593 */
6594 push(NVL(rule_items.value,0));
6595 ELSIF rule_items.set_number IS NOT NULL
6596 THEN
6597 push(expand_set(rule_items.set_number));
6598 ELSE
6599 /*
6600 rule_items.value can be null (null string)
6601 */
6602 push(rule_items.value);
6603 END IF;
6604 END LOOP;
6605 v_result := pop;
6606 /*
6607 check stack
6608 */
6609
6610 IF v_start_stack <> gv_stack_index
6611 THEN
6612 log_error('turing',
6613 'Unbalanced stack:Start='||
6614 v_start_stack||' Current='||
6615 gv_stack_index||Fnd_Global.Local_Chr(10)||
6616 'rule='||p_rule_number);
6617 END IF;
6618 do_message(p_rule_number,v_result,p_tds);
6619 gv_turing_level := gv_turing_level - 1;
6620 RETURN (v_result);
6621 END turing;
6622 /*
6623 rulp_val_senna - the IGS_RU_RULE engine driver
6624 */
6625 BEGIN DECLARE
6626 v_rule_number NUMBER;
6627 v_turing_return IGS_RU_ITEM.value%TYPE;
6628 v_tds r_tds;
6629 v_before_t DATE;
6630 v_before_rb DATE;
6631 BEGIN
6632 v_before_t := sysdate;
6633 /*
6634 initialise globals
6635 */
6636 gv_turing_level := 0;
6637 gv_stack_index := 0;
6638 gv_set_index := 0;
6639 gv_member_index := 1;
6640 /*
6641 delete any existing messages, reset index
6642 */
6643 gt_message_stack := gt_empty_message_stack;
6644 gv_message_stack_index := 0;
6645 /*
6646 if defined use p_rule_number
6647 */
6648 IF p_rule_number IS NOT NULL
6649 THEN
6650 v_rule_number := p_rule_number;
6651 ELSIF p_rule_call_name IS NOT NULL
6652 THEN
6653 v_rule_number := get_called_rule(p_rule_call_name);
6654 ELSE
6655 log_error('main',
6656 'Invalid rule call');
6657 END IF;
6658 /*
6659 evaluate RULE
6660 */
6661 v_turing_return := RTRIM(turing(v_rule_number,v_tds));
6662 /*
6663 * Who : Navin When : 27-Aug-2001
6664 * What : Following IF condition is added as part of Bug# : 1899513.
6665 */
6666 IF p_rule_call_name = 'AD-TRK-SET' AND v_turing_return IS NOT NULL THEN
6667 v_turing_return := display_set ( v_turing_return );
6668 END IF;
6669
6670 p_message := get_message;
6671 v_before_rb := sysdate;
6672 RETURN v_turing_return;
6673 EXCEPTION
6674 WHEN rule_error THEN
6675 p_message := 'Rule Error while processing rule ';
6676 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
6677 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.rules.error :',p_message||':'||SQLERRM);
6678 END IF;
6679 RETURN NULL;
6680 WHEN OTHERS THEN
6681 log_error('main',
6682 'Error while processing rule');
6683 p_message := 'Error while processing rule ';
6684 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
6685 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.rules.error :',p_message||':'||SQLERRM);
6686 END IF;
6687 RETURN NULL;
6688 END;
6689
6690 END rulp_val_senna;
6691
6692 END igs_ru_gen_001;
[Home] [Help]