1 PACKAGE BODY IGS_RU_GEN_002 AS
2 /* $Header: IGSRU02B.pls 115.19 2003/09/04 08:06:40 rghosh ship $ */
3
4 Function Rulp_Ins_Parser(
5 p_group IN NUMBER ,
6 p_return_type IN VARCHAR2 ,
7 p_rule_description IN VARCHAR2 ,
8 p_rule_processed IN OUT NOCOPY VARCHAR2 ,
9 p_rule_unprocessed IN OUT NOCOPY VARCHAR2 ,
10 p_generate_rule IN BOOLEAN ,
11 p_rule_number IN OUT NOCOPY NUMBER ,
12 p_LOV_number IN OUT NOCOPY NUMBER )
13 RETURN BOOLEAN IS
14 ------------------------------------------------------------------
15 --Created by : nsinha, Oracle India
16 --Date created: 12-Mar-1999
17 --
18 --Purpose: Parses the Rule Text for Syntax Checking.
19 --
20 --Known limitations/enhancements and/or remarks:
21 --
22 --Change History:
23 --Who When What
24 --Nsinha 12-Mar-2002 Bug # 2233951: Changed the reference of
25 -- IGS_RU_GEN_003 and IGS_GE_GEN_004 with IGS_RU_GEN_006
26 --
27 -------------------------------------------------------------------
28 /*
29 STRUCTURES
30 */
31 TYPE t_number IS TABLE OF
32 NUMBER(6)
33 INDEX BY BINARY_INTEGER;
34 TYPE r_LOV IS RECORD (
35 select_item IGS_RU_DESCRIPTION.rule_description%TYPE,
36 description VARCHAR2(2000),
37 selectable VARCHAR2(1) );
38 TYPE t_LOV IS TABLE OF
39 r_LOV
40 INDEX BY BINARY_INTEGER;
41 TYPE t_param_list IS TABLE OF
42 IGS_RU_RET_TYPE.s_return_type%TYPE
43 INDEX BY BINARY_INTEGER;
44
45 /*
46 CONSTANTS
47 */
48 cst_space CONSTANT VARCHAR2(1) := fnd_global.local_chr(31);
49 cst_spacemod CONSTANT VARCHAR2(1) := fnd_global.local_chr(9);
50 /*
51 GLOBALS
52 */
53 gv_level NUMBER(3);
54 gv_set_number IGS_RU_SET.sequence_number%TYPE;
55 gv_min_rule NUMBER;
56 /*
57 when delete rule, keep rule numbers to reuse
58 */
59 gt_rule_numbers t_number;
60 gv_rn_index NUMBER;
61 /*
62 when delete rule, keep set numbers to reuse
63 */
64 gt_set_numbers t_number;
65 gv_sn_index NUMBER;
66 /*
67 table of selectable items
68 */
69 gt_rule_LOV t_LOV;
70 gv_LOV_index NUMBER;
71 /*
72 list of parameter return types
73 */
74 gt_param_list t_param_list;
75 gv_params NUMBER;
76 gv_param_type IGS_RU_RET_TYPE.s_return_type%TYPE;
77 /*
78 string termiate character
79 */
80 gv_string_terminate VARCHAR2(10);
81 /*
82 selected value in LOV's
83 */
84 gv_prev VARCHAR2(100);
85 gv_pprev VARCHAR2(100);
86 gv_select_count NUMBER := 0;
87 /*
88
89 FORWARD REFERENCE DECLARATIONS
90
91 */
92 FUNCTION parse_rule(
93 p_type IN VARCHAR2,
94 p_rule IN OUT NOCOPY VARCHAR2,
95 p_rule_number IN NUMBER,
96 p_item IN OUT NOCOPY NUMBER)
97 RETURN BOOLEAN;
98 /*
99
100 MISCELLANEOUS FUNCTIONS
101
102 */
103
104 /*
105
106 maintain LOV array
107
108 */
109 PROCEDURE make_LOV(
110 p_rule IN VARCHAR2,
111 p_string IN VARCHAR2,
112 p_description IN VARCHAR2,
113 p_selectable IN VARCHAR2)
114 IS
115 BEGIN DECLARE
116 v_rule_length NUMBER;
117 BEGIN
118 /*
119 RETURN;
120 */
121 v_rule_length := LENGTH(p_rule);
122 IF v_rule_length IS NULL
123 THEN
124 v_rule_length := 0;
125 END IF;
126 IF v_rule_length < gv_min_rule
127 THEN
128 /*
129 start again
130 */
131 gv_min_rule := v_rule_length;
132 gv_LOV_index := 0;
133 END IF;
134 IF v_rule_length = gv_min_rule
135 THEN
136 gv_LOV_index := gv_LOV_index + 1;
137 gt_rule_LOV(gv_LOV_index).select_item := REPLACE(p_string,fnd_global.local_chr(10));
138 gt_rule_LOV(gv_LOV_index).description := p_description;
139 gt_rule_LOV(gv_LOV_index).selectable := p_selectable;
140 END IF;
141 END;
142 END make_LOV;
143 /*
144
145 one item, no directives
146 return item or null
147
148 */
149 FUNCTION LOV_item
150 RETURN VARCHAR2 IS
151 v_description IGS_RU_LOV.description%TYPE;
152 v_selectable IGS_RU_LOV.selectable%TYPE;
153 BEGIN
154 SELECT description,
155 selectable
156 INTO v_description,
157 v_selectable
158 FROM IGS_RU_LOV
159 WHERE sequence_number = p_LOV_number;
160 IF v_selectable = 'Y'
161 THEN
162 RETURN v_description;
163 END IF;
164 RETURN NULL;
165 EXCEPTION
166 WHEN TOO_MANY_ROWS THEN
167 RETURN NULL;
168 END LOV_item;
169 /*
170
171 move LOV's to table
172
173 */
174 PROCEDURE insert_LOV_tab
175 IS
176 X_ROWID VARCHAR2(25);
177 v_help_text igs_ru_lov.help_text%TYPE;
178 CURSOR C_RL IS
179 SELECT ROWID, rl.*
180 FROM IGS_RU_LOV rl
181 WHERE SEQUENCE_NUMBER = p_LOV_number ;
182
183 BEGIN
184 IF p_LOV_number IS NULL
185 THEN
186 SELECT IGS_RU_LOV_SEQ_NUM_S.nextval
187 INTO p_LOV_number
188 FROM DUAL;
189 END IF;
190
191 FOR C_RL_REC IN C_RL LOOP
192 IGS_RU_LOV_PKG.DELETE_ROW (X_ROWID => C_RL_REC.ROWID );
193 END LOOP;
194
195
196 FOR v_index IN 1 .. gv_LOV_index
197 LOOP
198 BEGIN
199 IGS_RU_LOV_PKG.INSERT_ROW (
200 X_ROWID => X_ROWID,
201 X_SEQUENCE_NUMBER => p_LOV_number,
202 X_DESCRIPTION => LTRIM(RTRIM(gt_rule_LOV(v_index).select_item)),
203 X_HELP_TEXT => LTRIM(RTRIM(gt_rule_LOV(v_index).description)),
204 X_SELECTABLE => LTRIM(RTRIM(gt_rule_LOV(v_index).selectable)) );
205
206 EXCEPTION
207 WHEN DUP_VAL_ON_INDEX THEN
208 /*
209 NULL;
210 order of descriptions (better this way)
211 */
212 UPDATE igs_ru_lov
213 SET help_text = LTRIM(RTRIM(gt_rule_LOV(v_index).description))
214 WHERE sequence_number = p_LOV_number
215 AND description = LTRIM(RTRIM(gt_rule_LOV(v_index).select_item));
216
217 END;
218 END LOOP;
219 END insert_LOV_tab;
220 /*
221
222 make new rule
223
224 */
225 FUNCTION new_rule
226 ------------------------------------------------------------------
227 --Created by : nsinha, Oracle India
228 --Date created: 12-Mar-2002
229 --
230 --Purpose:
231 --
232 --
233 --Known limitations/enhancements and/or remarks:
234 --
235 --Change History:
236 --Who When What
237 --nsinha 12-Mar-2002 Bug# 2233951: Modified the logic to
238 -- SELECT the next value of the sequence number
239 -- differently when the data is for SEED DB.
240 --
241 -------------------------------------------------------------------
242 RETURN NUMBER IS
243 BEGIN DECLARE
244 v_rule_number NUMBER;
245 X_ROWID VARCHAR2(25);
246
247 CURSOR C_IGS_RU_RULE_SEQ_NUM_S IS
248 SELECT igs_ru_rule_seq_num_s.NEXTVAL
249 FROM DUAL;
250
251 CURSOR cur_max_plus_one IS
252 SELECT (sequence_number + 1) sequence_number
253 FROM igs_ru_rule
254 WHERE sequence_number =
255 (SELECT MAX (sequence_number)
256 FROM igs_ru_rule
257 WHERE sequence_number < 499999)
258 FOR UPDATE OF sequence_number NOWAIT;
259
260 BEGIN
261 IF p_generate_rule = FALSE
262 THEN
263 RETURN NULL;
264 END IF;
265 IF gv_rn_index = 0
266 THEN
267 --
268 -- New description number
269 -- If the User creating this record is DATAMERGE (id = 1) then
270 -- Get the sequence as the existing maximum value + 1
271 -- Else
272 -- Get the next value from the database sequence
273 --
274 IF (fnd_global.user_id = 1) THEN
275 OPEN cur_max_plus_one;
276 FETCH cur_max_plus_one INTO v_rule_number;
277 CLOSE cur_max_plus_one;
278 ELSE
279 OPEN C_IGS_RU_RULE_SEQ_NUM_S;
280 FETCH C_IGS_RU_RULE_SEQ_NUM_S INTO v_rule_number;
281 CLOSE C_IGS_RU_RULE_SEQ_NUM_S;
282 END IF;
283 ELSE
284 /*
285 use deleted rule number
286 */
287 v_rule_number := gt_rule_numbers(gv_rn_index);
288 gv_rn_index := gv_rn_index - 1;
289 END IF;
290
291 IGS_RU_RULE_PKG.INSERT_ROW (
292 X_ROWID => X_ROWID ,
293 X_SEQUENCE_NUMBER => V_RULE_NUMBER );
294
295 RETURN v_rule_number;
296 END;
297 END new_rule;
298 /*
299
300 insert new set
301
302 */
303 FUNCTION new_set (
304 p_set_type IN IGS_RU_SET.set_type%TYPE )
305 RETURN NUMBER IS
306 BEGIN DECLARE
307 v_set_number NUMBER;
308 X_ROWID VARCHAR2(25);
309
310 CURSOR C_IGS_RU_SET_SEQ_NUM_S IS
311 SELECT igs_ru_set_seq_num_s.NEXTVAL
312 FROM DUAL;
313
314 CURSOR cur_max_plus_one IS
315 SELECT (sequence_number + 1) sequence_number
316 FROM igs_ru_set
317 WHERE sequence_number =
318 (SELECT MAX (sequence_number)
319 FROM igs_ru_set
320 WHERE sequence_number < 499999)
321 FOR UPDATE OF sequence_number NOWAIT;
322 BEGIN
323 IF p_generate_rule = FALSE
324 THEN
325 RETURN NULL;
326 END IF;
327 IF gv_sn_index = 0 THEN
328 --
329 -- New description number
330 -- If the User creating this record is DATAMERGE (id = 1) then
331 -- Get the sequence as the existing maximum value + 1
332 -- Else
333 -- Get the next value from the database sequence
334 --
335 IF (fnd_global.user_id = 1) THEN
336 OPEN cur_max_plus_one;
337 FETCH cur_max_plus_one INTO v_set_number;
338 CLOSE cur_max_plus_one;
339 ELSE
340 OPEN C_IGS_RU_SET_SEQ_NUM_S;
341 FETCH C_IGS_RU_SET_SEQ_NUM_S INTO v_set_number;
342 CLOSE C_IGS_RU_SET_SEQ_NUM_S;
343 END IF;
344 ELSE
345 /*
346 use deleted set number
347 */
348 v_set_number := gt_set_numbers(gv_sn_index);
349 gv_sn_index := gv_sn_index - 1;
350 END IF;
351
352 IGS_RU_SET_PKG.INSERT_ROW (
353 X_ROWID => X_ROWID,
354 X_SEQUENCE_NUMBER => v_set_number,
355 X_SET_TYPE => p_set_type );
356
357 RETURN v_set_number;
358 END;
359 END new_set;
360 /*
361
362 consume leading spaces and tabs, return rest of string
363
364 */
365 PROCEDURE consume_leading_spaces(
366 p_rule IN OUT NOCOPY VARCHAR2)
367 IS
368 v_first NUMBER;
369 BEGIN
370 /*
371 skip internal space and tab
372 */
373 v_first := 1;
374 WHILE SUBSTR(p_rule,v_first,1) = cst_space
375 OR SUBSTR(p_rule,v_first,1) = ' '
376 LOOP
377 v_first := v_first + 1;
378 END LOOP;
379 p_rule := SUBSTR(p_rule,v_first);
380 END consume_leading_spaces;
381 /*
382
383 consume unit code
384 make LOV
385
386 */
387 FUNCTION consume_unit_code(
388 p_rule IN OUT NOCOPY VARCHAR2)
389 RETURN BOOLEAN IS
390 v_char VARCHAR2(1);
391 v_count NUMBER;
392 v_no_units BOOLEAN := TRUE;
393 v_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE := '%4$@G!))^';
394 v_max_count NUMBER;
395 BEGIN
396 gv_select_count := 0;
397 consume_leading_spaces(p_rule);
398 FOR v_ii IN 1 .. 200 /*LENGTH(p_rule)*/
399 LOOP
400 v_char := SUBSTR(p_rule,v_ii,1);
401 IF v_char = ',' /* next set member*/
402 OR v_char = '.' /* versions */
403 OR v_char = '}' /* end of set */
404 OR v_char = cst_space /* internal space */
405 OR v_char IS NULL /* end of line */
406 THEN
407 IF v_ii = 1
408 THEN
409 /* invalid first char, no unit*/
410 make_LOV(p_rule,
411 '*** Valid Unit Code ***',
412 'Input part or all of a valid unit code.',
413 'N');
414 RETURN FALSE;
415 ELSE
416 /* count the number of units selected, remember their might be wildcards*/
417 SELECT count(*)
418 INTO v_count
419 FROM IGS_PS_UNIT_VER
420 /*WHERE unit_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1)); --Bug 2395891/2543627 --space not accepted in user defined rules*/
421 WHERE unit_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1)); --nshee
422 IF v_count = 0
423 THEN
424 /*
425 check if to many selected units (1000)
426 */
427 SELECT count(*)
428 INTO v_max_count
429 FROM IGS_PS_UNIT_VER
430 /*WHERE unit_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%'; --Bug 2395891/2543627 --space not accepted in user defined rules*/
431 WHERE unit_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%';--nshee
432 IF v_max_count > 1000
433 THEN
434 make_LOV(p_rule,
435 '*** To many units selected. ***',
436 'Suggest you restrict the select criteria further.',
437 'N');
438 RETURN FALSE;
439 END IF;
440 /*
441 build list of values using the first few chars
442 */
443 FOR uv IN (
444 SELECT unit_cd,
445 version_number,
446 unit_status,
447 short_title
448 FROM IGS_PS_UNIT_VER
449 /*WHERE unit_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%' --Bug 2395891/2543627 --space not accepted in user defined rules*/
450 WHERE unit_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%' --nshee
451 ORDER BY unit_cd,version_number DESC )
452 LOOP
453 v_no_units := FALSE;
454 IF uv.unit_cd <> v_unit_cd
455 THEN
456 /*
457 latest version of new unit code
458 */
459 make_LOV(p_rule,
460 uv.unit_cd,
461 uv.short_title||' (ALL VERSIONS)',
462 'Y');
463 END IF;
464 v_unit_cd := uv.unit_cd;
465 /*
466 list all versions
467 */
468 make_LOV(p_rule,
469 uv.unit_cd||'.'||IGS_GE_NUMBER.TO_CANN(uv.version_number),
470 uv.short_title||' ('||uv.unit_status||')',
471 'Y');
472 END LOOP;
473 IF v_no_units
474 THEN
475 make_LOV(p_rule,
476 '*** Valid Unit Code ***',
477 'Input part or all of a valid unit code.',
478 'N');
479 ELSE
480 gv_select_count := v_ii - 1;
481 END IF;
482 RETURN FALSE;
483 ELSE
484 /*
485 found unit(s)
486 */
487 p_rule := SUBSTR(p_rule,v_ii);
488 RETURN TRUE;
489 END IF;
490 END IF;
491 END IF;
492 END LOOP;
493 /*
494 end of line
495 */
496 p_rule := NULL;
497 RETURN TRUE;
498 END consume_unit_code;
499 /*
500
501 consume unit set code
502
503 */
504 FUNCTION consume_us_code(
505 p_rule IN OUT NOCOPY VARCHAR2)
506 RETURN BOOLEAN IS
507 BEGIN DECLARE
508 v_char VARCHAR2(1);
509 v_count NUMBER;
510 v_no_units BOOLEAN := TRUE;
511 v_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE := '#@*&!Vv9(';
512 v_max_count NUMBER;
513 BEGIN
514 consume_leading_spaces(p_rule);
515 FOR v_ii IN 1 .. 200
516 LOOP
517 v_char := SUBSTR(p_rule,v_ii,1);
518 IF v_char = ',' /* next set member */
519 OR v_char = '.' /* versions */
520 OR v_char = '}' /* end of set */
521 OR v_char = cst_space /* internal space */
522 OR v_char IS NULL /* end of line */
523 THEN
524 IF v_ii = 1
525 THEN
526 /*
527 invalid first char, no unit set
528 */
529 make_LOV(p_rule,'*** Valid Unit Set Code ***',
530 'Input part or all of a valid unit set code.',
531 'N');
532 RETURN FALSE;
533 ELSE
534 /*
535 count the number of UNIT sets selected, remember their might be wildcards
536 */
537 SELECT count(*)
538 INTO v_count
539 FROM IGS_EN_UNIT_SET
540 /* WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1)); commented by nshee as part of fix of bug 2381638 and 2395891 and added the next line */
541 WHERE unit_set_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1));
542 IF v_count = 0
543 THEN
544 /*
545 check if to many selected set members (1000)
546 */
547 SELECT count(*)
548 INTO v_max_count
549 FROM IGS_EN_UNIT_SET
550 /*WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%';--Bug 2395891/2543627 --space not accepted in user defined rules*/
551 WHERE unit_set_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%';--nshee
552 IF v_max_count > 1000
553 THEN
554 make_LOV(p_rule,
555 '*** To many unit sets selected. ***',
556 'Suggest you restrict the select criteria further.',
557 'N');
558 RETURN FALSE;
559 END IF;
560 /*
561 build list of values using the first few chars
562 */
563 FOR us IN (
564 SELECT unit_set_cd,
565 version_number,
566 unit_set_status,
567 short_title
568 FROM IGS_EN_UNIT_SET
569 /*WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%'--Bug 2395891/2543627 --space not accepted in user defined rules*/
570 WHERE unit_set_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%'--nshee
571 ORDER BY unit_set_cd,version_number DESC )
572 LOOP
573 v_no_units := FALSE;
574 IF us.unit_set_cd <> v_unit_set_cd
575 THEN
576 /*
577 latest version of new unit code
578 */
579 make_LOV(p_rule,us.unit_set_cd,
580 us.short_title||' (ALL VERSIONS)',
581 'Y');
582 END IF;
583 v_unit_set_cd := us.unit_set_cd;
584 /*
585 list all versions
586 */
587 make_LOV(p_rule,us.unit_set_cd||'.'||IGS_GE_NUMBER.TO_CANN(us.version_number),
588 us.short_title||' ('||us.unit_set_status||')',
589 'Y');
590 END LOOP;
591 IF v_no_units
592 THEN
593 make_LOV(p_rule,'*** Valid Unit Set Code ***',
594 'Input part or all of a valid unit set code.',
595 'N');
596 END IF;
597 RETURN FALSE;
598 ELSE
599 /*
600 found unit(s)
601 */
602 p_rule := SUBSTR(p_rule,v_ii);
603 RETURN TRUE;
604 END IF;
605 END IF;
606 END IF;
607 END LOOP;
608 /*
609 end of line
610 */
611 p_rule := NULL;
612 RETURN TRUE;
613 END;
614 END consume_us_code;
615 /*
616
617 consume course code
618 make LOV
619
620 */
621 FUNCTION consume_crs_code(
622 p_rule IN OUT NOCOPY VARCHAR2)
623 RETURN BOOLEAN IS
624 v_char VARCHAR2(1);
625 v_count NUMBER;
626 v_no_members BOOLEAN := TRUE;
627 v_member_cd IGS_PS_VER.course_cd%TYPE := '%4@!)^';
628 v_max_count NUMBER;
629 BEGIN
630 consume_leading_spaces(p_rule);
631 FOR v_ii IN 1 .. 200 /*LENGTH(p_rule) */
632 LOOP
633 v_char := SUBSTR(p_rule,v_ii,1);
634 IF v_char = ',' /* next set member */
635 OR v_char = '.' /* versions */
636 OR v_char = '}' /* end of set */
637 OR v_char = cst_space /* internal space */
638 OR v_char IS NULL /* end of line */
639 THEN
640 IF v_ii = 1
641 THEN
642 /*
643 invalid first char, no member(s)
644 */
645 make_LOV(p_rule,
646 '*** Valid Course Code ***',
647 'Input part or all of a valid course code.',
648 'N');
649 RETURN FALSE;
650 ELSE
651 /*
652 count the number of members selected, remember their might be wildcards
653 */
654 SELECT count(*)
655 INTO v_count
656 FROM IGS_PS_VER
657 /*WHERE course_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1));--Bug 2395891/2543627 --space not accepted in user defined rules*/
658 WHERE course_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1));--nshee
659 IF v_count = 0
660 THEN
661 /*
662 check if to many selected set members (1000)
663 */
664 SELECT count(*)
665 INTO v_max_count
666 FROM IGS_PS_VER
667 /*WHERE course_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%';--Bug 2395891/2543627 --space not accepted in user defined rules*/
668 WHERE course_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%';--nshee
669 IF v_max_count > 1000
670 THEN
671 make_LOV(p_rule,
672 '*** To many courses selected. ***',
673 'Suggest you restrict the select criteria further.',
674 'N');
675 RETURN FALSE;
676 END IF;
677 /*
678 build list of values using the first few chars
679 */
680 FOR cv IN (
681 SELECT course_cd,
682 version_number,
683 course_status,
684 short_title
685 FROM IGS_PS_VER
686 /*WHERE course_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%'--Bug 2395891/2543627 --space not accepted in user defined rules*/
687 WHERE course_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%'--nshee
688 ORDER BY course_cd,version_number DESC )
689 LOOP
690 v_no_members := FALSE;
691 IF cv.course_cd <> v_member_cd
692 THEN
693 /*
694 latest version of new member code
695 */
696 make_LOV(p_rule,
697 cv.course_cd,
698 cv.short_title||' (ALL VERSIONS)',
699 'Y');
700 END IF;
701 v_member_cd := cv.course_cd;
702 /*
703 list all versions
704 */
705 make_LOV(p_rule,
706 cv.course_cd||'.'||IGS_GE_NUMBER.TO_CANN(cv.version_number),
707 cv.short_title||' ('||cv.course_status||')',
708 'Y');
709 END LOOP;
710 IF v_no_members
711 THEN
712 make_LOV(p_rule,
713 '*** Valid Course Code ***',
714 'Input part or all of a valid course code.',
715 'N');
716 END IF;
717 RETURN FALSE;
718 ELSE
719 /*
720 found members(s)
721 */
722 p_rule := SUBSTR(p_rule,v_ii);
723 RETURN TRUE;
724 END IF;
725 END IF;
726 END IF;
727 END LOOP;
728 /*
729 end of line
730 */
731 p_rule := NULL;
732 RETURN TRUE;
733 END consume_crs_code;
734 /*
735
736 rationalise versions {AAC101.1,AAC101,2} => {AAC101.[1-2]}
737 order the versions string 10-12,4,7,5 => 4-5,7,10-12
738 versions of form 7-2 are nonsense and will be ignored
739
740 */
741 FUNCTION do_versions (
742 p_current_versions VARCHAR2,
743 p_extra_versions VARCHAR2 )
744 RETURN VARCHAR2 IS
745 v_concat_versions VARCHAR2(200);
746 v_prev_comma NUMBER := 1; /* previous position of comma in string */
747 v_curr_comma NUMBER; /* current position of comma in string */
748 v_sub_str VARCHAR2(30); /* sub-string between comma's */
749 v_dash NUMBER; /* position of '-' in sub-string */
750 v_lower NUMBER;
751 v_upper NUMBER;
752 vt_version t_number; /* array of version numbers */
753 v_max_ver NUMBER := 0;
754 v_new_versions IGS_RU_SET_MEMBER.versions%TYPE;
755 BEGIN
756 IF p_current_versions IS NULL OR
757 p_extra_versions IS NULL
758 THEN
759 /*
760 null mean ALL versions
761 */
762 RETURN NULL;
763 END IF;
764 /*
765 breakdown the versions string and build the versions array
766 */
767 v_concat_versions := p_current_versions||','||p_extra_versions;
768 LOOP
769 /*
770 find next comma (if exists)
771 */
772 v_curr_comma := INSTR(v_concat_versions, ',', v_prev_comma);
773 IF v_curr_comma = 0
774 THEN
775 /*
776 all/rest of string
777 */
778 v_sub_str := SUBSTR(v_concat_versions, v_prev_comma);
779 ELSE
780 /*
781 part of string, previous comma to this comma
782 */
783 v_sub_str := SUBSTR(v_concat_versions, v_prev_comma,
784 v_curr_comma - v_prev_comma);
785 END IF;
786 v_prev_comma := v_curr_comma + 1;
787 /*
788 find next dash in sub-string (if exists)
789 */
790 v_dash := INSTR(v_sub_str, '-');
791 IF v_dash = 0
792 THEN
793 v_lower := IGS_GE_NUMBER.TO_NUM(v_sub_str);
794 v_upper := v_lower;
795 ELSE
796 /*
797 dash in sub-string, split lower and upper
798 */
799 v_lower := IGS_GE_NUMBER.TO_NUM(SUBSTR(v_sub_str, 1, v_dash - 1));
800 v_upper := IGS_GE_NUMBER.TO_NUM(SUBSTR(v_sub_str, v_dash + 1));
801 END IF;
802 IF v_lower IS NOT NULL
803 THEN
804 /*
805 populate array
806 */
807 FOR v_ii IN 1 .. v_upper - v_lower + 1
808 LOOP
809 /*
810 array index and value are the same
811 */
812 vt_version(v_ii + v_lower - 1) := v_ii + v_lower - 1;
813 END LOOP;
814 IF v_max_ver < v_upper
815 THEN
816 /*
817 set max version
818 */
819 v_max_ver := v_upper;
820 END IF;
821 END IF;
822 IF v_curr_comma = 0
823 THEN
824 EXIT;
825 END IF;
826 END LOOP;
827 /*
828 rebuild the versions string from the versions array
829 */
830 v_lower := NULL;
831 FOR v_ii IN 1 .. v_max_ver + 1
832 LOOP
833 BEGIN
834 IF v_lower IS NULL
835 THEN
836 v_lower := vt_version(v_ii);
837 v_upper := vt_version(v_ii);
838 ELSE
839 v_upper := vt_version(v_ii);
840 END IF;
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN
843 IF v_lower IS NOT NULL
844 THEN
845 IF v_new_versions IS NOT NULL
846 THEN
847 /*
848 there's more therefor add comma
849 */
850 v_new_versions := v_new_versions||',';
851 END IF;
852 IF v_lower = v_upper
853 THEN
854 /*
855 single version
856 */
857 v_new_versions := v_new_versions||IGS_GE_NUMBER.TO_CANN(v_lower);
858 ELSE
859 /*
860 range
861 */
862 v_new_versions := v_new_versions||IGS_GE_NUMBER.TO_CANN(v_lower)||'-'||IGS_GE_NUMBER.TO_CANN(v_upper);
863 END IF;
864 END IF;
865 v_lower := NULL;
866 END;
867 END LOOP;
868 RETURN v_new_versions;
869 END do_versions;
870 /*
871
872 insert set member
873
874 */
875 PROCEDURE insert_set_member(
876 p_set_number IN IGS_RU_SET.sequence_number%TYPE,
877 p_unit IN VARCHAR2)
878 IS
879 v_unit VARCHAR2(200);
880 v_dot NUMBER;
881 v_unit_cd IGS_RU_SET_MEMBER.unit_cd%TYPE;
882 v_versions VARCHAR2(100); /* bigger to allow for spaces etc*/
883 v_current_versions IGS_RU_SET_MEMBER.versions%TYPE;
884 v_new_versions IGS_RU_SET_MEMBER.versions%TYPE;
885 X_ROWID VARCHAR2(25);
886
887 CURSOR C_RSMBR IS
888 SELECT ROWID, rsmbr.*
889 FROM IGS_RU_SET_MEMBER rsmbr
890 WHERE rs_sequence_number = p_set_number
891 AND unit_cd = v_unit_cd;
892
893 BEGIN
894 IF p_generate_rule = FALSE
895 THEN
896 RETURN;
897 END IF;
898 /*
899 remove spaces and tabs
900 */
901 v_unit := REPLACE(p_unit,cst_space);
902 v_unit := UPPER(REPLACE(v_unit,cst_spacemod,' '));/*added this line by nshee as fix for bug 2395891*/
903 v_unit := UPPER(REPLACE(v_unit,' '));
904 v_dot := INSTR(v_unit,'.');
905 IF v_dot = 0
906 THEN
907 v_unit_cd := v_unit;
908 ELSE
909 v_unit_cd := RTRIM(SUBSTR(v_unit,1,v_dot - 1));
910 v_versions := SUBSTR(v_unit,v_dot + 1);
911 /*
912 remove brackets '[', ']' (range)
913 */
914 v_versions := REPLACE(v_versions,'[');
915 v_versions := REPLACE(v_versions,']');
916 END IF;
917 IF v_unit_cd IS NOT NULL
918 THEN
919 IGS_RU_SET_MEMBER_PKG.INSERT_ROW (
920 X_ROWID => X_ROWID,
921 X_RS_SEQUENCE_NUMBER => p_set_number,
922 X_UNIT_CD => v_unit_cd,
923 X_VERSIONS => v_versions );
924
925 END IF;
926 EXCEPTION
927 WHEN DUP_VAL_ON_INDEX THEN
928 SELECT versions
929 INTO v_current_versions
930 FROM IGS_RU_SET_MEMBER
931 WHERE rs_sequence_number = p_set_number
932 AND unit_cd = v_unit_cd;
933 /*
934 rationalise new versions with existing versions
935
936 */
937 v_new_versions := do_versions(v_current_versions,v_versions);
938
939 FOR C_RSMBR_REC IN C_RSMBR LOOP
940 IGS_RU_SET_MEMBER_PKG.UPDATE_ROW (
941 X_ROWID => C_RSMBR_REC.ROWID,
942 X_RS_SEQUENCE_NUMBER => C_RSMBR_REC.RS_SEQUENCE_NUMBER,
943 X_UNIT_CD => C_RSMBR_REC.UNIT_CD ,
944 X_VERSIONS => v_new_versions );
945 END LOOP;
946
947 END insert_set_member;
948 /*
949
950 get a number
951
952 */
953 FUNCTION get_number (
954 p_number OUT NOCOPY NUMBER,
955 p_rule IN OUT NOCOPY VARCHAR2 )
956 RETURN BOOLEAN IS
957 v_first NUMBER; /* first non white space */
958 v_count NUMBER; /* count until next non number*/
959 BEGIN
960 IF p_rule IS NULL
961 THEN
962 RETURN FALSE;
963 END IF;
964 /*
965 skip internal space and tab
966 */
967 v_first := 1;
968 WHILE SUBSTR(p_rule,v_first,1) = cst_space
969 OR SUBSTR(p_rule,v_first,1) = ' '
970 LOOP
971 v_first := v_first + 1;
972 END LOOP;
973 /*
974 check until end of line or VALUE_ERROR
975 */
976 v_count := 0;
977 LOOP
978 BEGIN
979 v_count := v_count + 1;
980 IF SUBSTR(p_rule,v_first + v_count - 1) IS NULL
981 THEN
982 EXIT;
983 END IF;
984 /*
985 convert and validate number
986 */
987 p_number := IGS_GE_NUMBER.TO_NUM(SUBSTR(p_rule,v_first,v_count));
988 EXCEPTION
989 WHEN VALUE_ERROR THEN
990 EXIT;
991 END;
992 END LOOP;
993 IF v_count > 1
994 THEN
995 /*
996 count > 1 therefor must have number
997 consume rule string
998 */
999 p_rule := SUBSTR(p_rule,v_first + v_count - 1);
1000 RETURN TRUE;
1001 END IF;
1002 RETURN FALSE;
1003 END get_number;
1004 /*
1005
1006 get string until next quote
1007
1008 */
1009 FUNCTION get_string (
1010 p_string OUT NOCOPY VARCHAR2,
1011 p_rule IN OUT NOCOPY VARCHAR2,
1012 p_terminate IN VARCHAR2 )
1013 RETURN BOOLEAN IS
1014 v_quote NUMBER;
1015 BEGIN
1016 IF p_rule IS NULL
1017 THEN
1018 RETURN FALSE;
1019 END IF;
1020 v_quote := INSTR(p_rule,p_terminate);
1021 IF v_quote = 0
1022 THEN
1023 p_string := p_rule;
1024 p_rule := '';
1025 ELSE
1026 p_string := SUBSTR(p_rule,1,v_quote - 1);
1027 p_rule := SUBSTR(p_rule,v_quote);
1028 END IF;
1029 RETURN TRUE;
1030 END get_string;
1031 /*
1032
1033 get date and validate
1034
1035 */
1036 FUNCTION get_date (
1037 p_string IN OUT NOCOPY VARCHAR2,
1038 p_rule IN OUT NOCOPY VARCHAR2 )
1039 RETURN BOOLEAN IS
1040 v_rule VARCHAR2(2000);
1041 v_date_format VARCHAR2(30);
1042 BEGIN
1043 v_rule := p_rule;
1044 /*
1045 this assumes terminate string is same as commencement string?
1046 */
1047 IF get_string(p_string,p_rule,gv_string_terminate) = TRUE AND
1048 IGS_RU_GEN_006.jbsp_get_dt_picture(REPLACE(p_string,cst_space),v_date_format) = TRUE
1049 THEN
1050 RETURN TRUE;
1051 END IF;
1052 p_rule := v_rule;
1053 RETURN FALSE;
1054 END get_date;
1055 /*
1056
1057 match string to part of rule ignoring spaces,
1058 set ramainder of rule and return TRUE
1059 else FALSE
1060
1061 */
1062 FUNCTION match_string_to_rule(
1063 p_string IN VARCHAR2,
1064 p_rule IN OUT NOCOPY VARCHAR2)
1065 RETURN BOOLEAN IS
1066 BEGIN DECLARE
1067 v_string IGS_RU_DESCRIPTION.rule_description%TYPE;
1068 v_string_length NUMBER;
1069 v_sub_rule IGS_RU_DESCRIPTION.rule_description%TYPE;
1070 BEGIN
1071 IF p_rule IS NULL
1072 THEN
1073 RETURN FALSE;
1074 END IF;
1075 v_string := REPLACE(p_string,' ');
1076 v_string := REPLACE(v_string,fnd_global.local_chr(10));
1077 v_string := REPLACE(v_string,' ');
1078 v_string_length := LENGTH(v_string);
1079 FOR v_ii IN v_string_length .. LENGTH(p_rule)
1080 LOOP
1081 /*
1082 remove internal spaces
1083 */
1084 v_sub_rule := REPLACE(SUBSTR(p_rule,1,v_ii),cst_space);
1085 /*
1086 remove returns
1087 */
1088 v_sub_rule := REPLACE(v_sub_rule,fnd_global.local_chr(10));
1089 /*
1090 remove tabs
1091 */
1092 v_sub_rule := REPLACE(v_sub_rule,' ');
1093 IF LENGTH(v_sub_rule) = v_string_length
1094 THEN
1095 IF v_string = v_sub_rule
1096 THEN
1097 p_rule := LTRIM(SUBSTR(p_rule,v_ii + 1));
1098 RETURN TRUE;
1099 ELSE
1100 RETURN FALSE;
1101 END IF;
1102 END IF;
1103 END LOOP;
1104 RETURN FALSE;
1105 END;
1106 END match_string_to_rule;
1107 /*
1108
1109 convert the description string into token, action
1110 string, action, remainder of description
1111 string, NULL, NULL
1112 NULL, action, remainder of description
1113
1114 return FALSE if description is NULL
1115
1116 */
1117 FUNCTION breakdown_desc (
1118 p_string IN OUT NOCOPY IGS_RU_DESCRIPTION.rule_description%TYPE,
1119 p_action IN OUT NOCOPY IGS_RU_DESCRIPTION.s_return_type%TYPE,
1120 p_description IN OUT NOCOPY IGS_RU_DESCRIPTION.rule_description%TYPE )
1121 RETURN BOOLEAN IS
1122 BEGIN DECLARE
1123 v_hash NUMBER;
1124 BEGIN
1125 /*
1126 check if more to process
1127 */
1128 IF p_description IS NULL
1129 THEN
1130 RETURN FALSE;
1131 END IF;
1132 p_action := NULL;
1133 v_hash := INSTR(p_description, '#');
1134 p_string := SUBSTR(p_description,1,v_hash - 1);
1135 IF v_hash = 0
1136 THEN
1137 p_action := '';
1138 p_string := p_description;
1139 ELSE
1140 FOR return_types IN (
1141 SELECT s_return_type
1142 FROM IGS_RU_RET_TYPE
1143 WHERE s_return_type LIKE SUBSTR(p_description,v_hash + 1,1)||'%'
1144 ORDER BY s_return_type DESC )
1145 LOOP
1146 IF SUBSTR(p_description,v_hash + 1,LENGTH(return_types.s_return_type))
1147 = return_types.s_return_type
1148 THEN
1149 p_action := return_types.s_return_type;
1150 EXIT;
1151 END IF;
1152 END LOOP;
1153 IF p_action IS NULL
1154 THEN
1155 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_OPER');
1156 IGS_GE_MSG_STACK.ADD;
1157 END IF;
1158 END IF;
1159 p_description := SUBSTR(p_description,v_hash + 1 + LENGTH(p_action));
1160 RETURN TRUE;
1161 END;
1162 END breakdown_desc;
1163 /*
1164
1165 count parameters in rule description
1166
1167 */
1168 FUNCTION count_params(
1169 p_rule_description IN VARCHAR2)
1170 RETURN NUMBER IS
1171 BEGIN DECLARE
1172 v_rule_description IGS_RU_DESCRIPTION.rule_description%TYPE;
1173 v_string IGS_RU_DESCRIPTION.rule_description%TYPE;
1174 v_action IGS_RU_RET_TYPE.s_return_type%TYPE;
1175 v_count NUMBER := 0;
1176 BEGIN
1177 v_rule_description := p_rule_description;
1178 WHILE breakdown_desc(v_string,v_action,v_rule_description)
1179 LOOP
1180 IF v_action IS NOT NULL
1181 THEN
1182 v_count := v_count + 1;
1183 END IF;
1184 END LOOP;
1185 RETURN v_count;
1186 END;
1187 END count_params;
1188 /*
1189
1190 set and count the parameter types in RULE description
1191
1192 */
1193 FUNCTION set_params(
1194 p_rule_description IN VARCHAR2)
1195 RETURN NUMBER IS
1196 BEGIN DECLARE
1197 v_rule_description IGS_RU_DESCRIPTION.rule_description%TYPE;
1198 v_string IGS_RU_DESCRIPTION.rule_description%TYPE;
1199 v_action IGS_RU_RET_TYPE.s_return_type%TYPE;
1200 v_count NUMBER := 0;
1201 BEGIN
1202 v_rule_description := p_rule_description;
1203 WHILE breakdown_desc(v_string,v_action,v_rule_description)
1204 LOOP
1205 IF v_action IS NOT NULL
1206 THEN
1207 v_count := v_count + 1;
1208 gt_param_list(v_count) := v_action;
1209 END IF;
1210 END LOOP;
1211 RETURN v_count;
1212 END;
1213 END set_params;
1214 /*
1215
1216 check if this parameter type is allowed
1217
1218 */
1219 FUNCTION valid_param_type (
1220 p_return_type IGS_RU_RET_TYPE.s_return_type%TYPE )
1221 RETURN BOOLEAN IS
1222 BEGIN
1223 FOR v_ii IN 1 .. gv_params
1224 LOOP
1225 IF gt_param_list(v_ii) = p_return_type
1226 THEN
1227 RETURN TRUE;
1228 END IF;
1229 END LOOP;
1230 RETURN FALSE;
1231 END valid_param_type;
1232 /*
1233
1234 check if this parameter is of valid type
1235
1236 */
1237 FUNCTION valid_param (
1238 p_return_type IGS_RU_RET_TYPE.s_return_type%TYPE,
1239 p_param NUMBER )
1240 RETURN BOOLEAN IS
1241 BEGIN
1242 IF gt_param_list(p_param) = p_return_type
1243 THEN
1244 RETURN TRUE;
1245 END IF;
1246 RETURN FALSE;
1247 EXCEPTION
1248 WHEN NO_DATA_FOUND THEN
1249 RETURN FALSE;
1250 END valid_param;
1251 /*
1252
1253 cascade delete RULE items
1254 save RULE and set numbers
1255
1256 */
1257 PROCEDURE delete_rule_items(
1258 p_rule_number IN IGS_RU_RULE.sequence_number%TYPE,
1259 p_item IN IGS_RU_ITEM.item%TYPE)
1260 IS
1261 CURSOR c_rule_items IS
1262 SELECT rowid, ri.*
1263 FROM IGS_RU_ITEM ri
1264 WHERE rul_sequence_number = p_rule_number
1265 AND item >= p_item ;
1266
1267 CURSOR c_rule (p_rule_number IN IGS_RU_ITEM.RULE_NUMBER%TYPE) IS
1268 SELECT rowid, rule.*
1269 FROM IGS_RU_RULE rule
1270 WHERE sequence_number = p_rule_number;
1271
1272
1273 CURSOR c_rule_set_mbr(p_set_number IN IGS_RU_ITEM.SET_NUMBER%TYPE) IS
1274 SELECT rowid, rsmbr.*
1275 FROM IGS_RU_SET_MEMBER rsmbr
1276 WHERE rs_sequence_number =p_set_number;
1277
1278
1279 CURSOR C_rule_set (p_set_number IN IGS_RU_ITEM.SET_NUMBER%TYPE) IS
1280 SELECT rowid, rs.*
1281 FROM IGS_RU_SET rs
1282 WHERE sequence_number = p_set_number;
1283
1284
1285 BEGIN
1286 FOR c_rule_items_REC IN c_rule_items LOOP
1287
1288 IGS_RU_ITEM_PKG.DELETE_ROW (
1289 X_ROWID => C_RULE_ITEMS_REC.ROWID );
1290
1291 IF c_rule_items_rec.rule_number IS NOT NULL
1292 THEN
1293 /*
1294 save RULE number
1295 */
1296 gv_rn_index := gv_rn_index + 1;
1297 gt_rule_numbers(gv_rn_index) := c_rule_items_rec.rule_number;
1298 /*
1299 remove all items of this IGS_RU_RULE
1300 */
1301 delete_rule_items(c_rule_items_rec.rule_number,0);
1302 /*
1303 remove RULE
1304 */
1305 FOR c_rule_rec IN c_rule (c_rule_items_rec.rule_number) LOOP
1306 IGS_RU_RULE_PKG.DELETE_ROW (X_ROWID => C_RULE_REC.ROWID );
1307 END LOOP;
1308
1309 ELSIF c_rule_items_rec.set_number IS NOT NULL
1310 THEN
1311 /*
1312 save set number
1313 */
1314 gv_sn_index := gv_sn_index + 1;
1315 gt_set_numbers(gv_sn_index) := c_rule_items_rec.set_number;
1316 /*
1317 remove set members
1318 */
1319 FOR c_rule_set_mbr_rec IN c_rule_set_mbr(c_rule_items_rec.set_number) LOOP
1320 IGS_RU_SET_MEMBER_PKG.DELETE_ROW (X_ROWID => C_RULE_SET_MBR_REC.ROWID);
1321 END LOOP;
1322
1323 /*
1324 remove set
1325 */
1326 FOR C_RULE_SET_REC IN C_RULE_SET(c_rule_items_rec.set_number) LOOP
1327 IGS_RU_SET_PKG.DELETE_ROW (X_ROWID => C_RULE_SET_REC.ROWID );
1328 END LOOP;
1329
1330 END IF;
1331 END LOOP;
1332 END delete_rule_items;
1333 /*
1334
1335 build and insert RULE item
1336 increment item if used
1337
1338 */
1339 PROCEDURE make_rule_item(
1340 p_from IN VARCHAR2,
1341 p_rule_num IN NUMBER,
1342 p_item IN OUT NOCOPY NUMBER,
1343 p_turin_function IN IGS_RU_ITEM.turin_function%TYPE,
1344 p_rud_seq_num IN IGS_RU_DESCRIPTION.sequence_number%TYPE,
1345 p_rule_number IN IGS_RU_ITEM.rule_number%TYPE,
1346 p_set_number IN IGS_RU_ITEM.set_number%TYPE,
1347 p_value IN IGS_RU_ITEM.value%TYPE)
1348 IS
1349 BEGIN DECLARE
1350 v_named_rule IGS_RU_ITEM.named_rule%TYPE;
1351 v_value IGS_RU_ITEM.value%TYPE;
1352 v_rule_description IGS_RU_DESCRIPTION.rule_description%TYPE;
1353 X_ROWID VARCHAR2(25);
1354 BEGIN
1355 IF p_generate_rule = FALSE
1356 THEN
1357 RETURN;
1358 END IF;
1359 delete_rule_items(p_rule_num,p_item);
1360 v_value := p_value;
1361 IF p_turin_function IS NOT NULL
1362 THEN
1363 v_value := NULL;
1364 ELSIF p_rud_seq_num IS NOT NULL
1365 THEN
1366 BEGIN
1367 /*
1368 match to named RULE
1369 */
1370 SELECT rul_sequence_number
1371 INTO v_named_rule
1372 FROM IGS_RU_NAMED_RULE
1373 WHERE rud_sequence_number = p_rud_seq_num;
1374 /*
1375 set number of parameters for called RULE
1376 */
1377 SELECT rule_description
1378 INTO v_rule_description
1379 FROM IGS_RU_DESCRIPTION
1380 WHERE sequence_number = p_rud_seq_num;
1381 v_value := count_params(v_rule_description);
1382 /*
1383 add evaluate RULE item
1384 */
1385 make_rule_item('1',p_rule_num,p_item,
1386 '_ER',
1387 NULL,NULL,NULL,NULL);
1388 EXCEPTION
1389 WHEN NO_DATA_FOUND THEN
1390 IF p_value IS NULL
1391 THEN
1392 /*
1393 do nothing
1394 */
1395 RETURN;
1396 END IF;
1397 v_named_rule := NULL;
1398 END;
1399 END IF;
1400 IGS_RU_ITEM_PKG.INSERT_ROW (
1401 X_ROWID => X_ROWID,
1402 X_RUL_SEQUENCE_NUMBER => p_rule_num,
1403 X_ITEM => p_item,
1404 X_TURIN_FUNCTION => p_turin_function,
1405 X_NAMED_RULE => v_named_rule,
1406 X_RULE_NUMBER => p_rule_number,
1407 X_SET_NUMBER => p_set_number,
1408 X_VALUE => v_value,
1409 X_DERIVED_RULE => NULL );
1410
1411 /*
1412 increment item
1413 */
1414 p_item := p_item + 1;
1415 END;
1416 END make_rule_item;
1417 /*
1418
1419 from an SQL select string match value or create a list of values
1420
1421 */
1422 FUNCTION do_LOV (
1423 p_rule IN OUT NOCOPY VARCHAR2,
1424 p_rule_number IN NUMBER,
1425 p_item IN OUT NOCOPY NUMBER,
1426 p_select_string IN VARCHAR2 )
1427 RETURN BOOLEAN IS
1428 v_select_string VARCHAR2(2000);
1429 v_cursor INTEGER;
1430 v_rows INTEGER;
1431 v_value VARCHAR2(100);
1432 v_description VARCHAR2(100);
1433 v_count NUMBER := 0;
1434 BEGIN
1435 /*
1436 replace previously selected field(s) with their values
1437 */
1438 v_select_string := REPLACE(p_select_string,'$PREV',gv_prev);
1439 v_select_string := REPLACE(v_select_string,'$PPREV',gv_pprev);
1440 v_cursor := DBMS_SQL.OPEN_CURSOR;
1441 DBMS_SQL.PARSE(v_cursor,v_select_string||' ORDER BY 1 DESC',dbms_sql.native);
1442 DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_value,100);
1443 DBMS_SQL.DEFINE_COLUMN(v_cursor,2,v_description,100);
1444 v_rows := DBMS_SQL.EXECUTE(v_cursor);
1445 WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
1446 LOOP
1447 v_count := v_count + 1;
1448 DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_value);
1449 IF match_string_to_rule(v_value,p_rule)
1450 THEN
1451 /*
1452 add selected value
1453 */
1454 make_rule_item('do_LOV',p_rule_number,p_item,
1455 NULL,NULL,NULL,NULL,
1456 v_value);
1457 /*
1458 set previous selected values
1459 */
1460 gv_pprev := gv_prev;
1461 gv_prev := v_value;
1462 DBMS_SQL.CLOSE_CURSOR(v_cursor);
1463 RETURN TRUE;
1464 END IF;
1465 DBMS_SQL.COLUMN_VALUE(v_cursor,2,v_description);
1466 make_LOV(p_rule,v_value,v_description,'Y');
1467 END LOOP;
1468 DBMS_SQL.CLOSE_CURSOR(v_cursor);
1469 IF v_count = 0
1470 THEN
1471 make_LOV(p_rule,
1472 '*** No values selected ***',
1473 'ERROR:No values selected while attempting '||
1474 'match from a database defined LOV''s.',
1475 'N');
1476 END IF;
1477 RETURN FALSE;
1478 END do_LOV;
1479
1480 /*
1481
1482 from an SQL select string match value, create set member or
1483 create a list of values
1484
1485 */
1486 FUNCTION make_set (
1487 p_rule IN OUT NOCOPY VARCHAR2,
1488 p_rule_number IN NUMBER,
1489 p_item IN OUT NOCOPY NUMBER,
1490 p_select_string IN VARCHAR2 )
1491 RETURN BOOLEAN IS
1492 v_select_string VARCHAR2(2000);
1493 v_cursor INTEGER;
1494 v_rows INTEGER;
1495 v_value VARCHAR2(100);
1496 v_description VARCHAR2(100);
1497 v_count NUMBER := 0;
1498 BEGIN
1499 /*
1500 replace previously selected field(s) with their values
1501 */
1502 v_select_string := REPLACE(p_select_string,'$PREV',gv_prev);
1503 v_select_string := REPLACE(v_select_string,'$PPREV',gv_pprev);
1504 v_cursor := DBMS_SQL.OPEN_CURSOR;
1505 DBMS_SQL.PARSE(v_cursor,v_select_string||' ORDER BY 1 DESC',dbms_sql.v7);
1506 DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_value,100);
1507 DBMS_SQL.DEFINE_COLUMN(v_cursor,2,v_description,100);
1508 v_rows := DBMS_SQL.EXECUTE(v_cursor);
1509 WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
1510 LOOP
1511 v_count := v_count + 1;
1512 DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_value);
1513 IF match_string_to_rule(v_value,p_rule)
1514 THEN
1515 DBMS_SQL.CLOSE_CURSOR(v_cursor);
1516 RETURN TRUE;
1517 END IF;
1518 DBMS_SQL.COLUMN_VALUE(v_cursor,2,v_description);
1519 make_LOV(p_rule,v_value,v_description,'Y');
1520 END LOOP;
1521 DBMS_SQL.CLOSE_CURSOR(v_cursor);
1522 IF v_count = 0
1523 THEN
1524 make_LOV(p_rule,
1525 '*** No values selected ***',
1526 'ERROR:No values selected while attempting '||
1527 'match from a database defined LOV''s.',
1528 'N');
1529 END IF;
1530 RETURN FALSE;
1531 END make_set;
1532 /*
1533
1534 parse RULE stage two
1535
1536 */
1537 FUNCTION parse_rule2(
1538 p_type IN VARCHAR2,
1539 p_rule IN OUT NOCOPY VARCHAR2,
1540 p_rule_number IN NUMBER,
1541 p_item IN OUT NOCOPY NUMBER)
1542 RETURN BOOLEAN IS
1543 BEGIN DECLARE
1544 v_rule VARCHAR2(2000);
1545 v_string IGS_RU_DESCRIPTION.rule_description%TYPE;
1546 v_action IGS_RU_RET_TYPE.s_return_type%TYPE;
1547 v_rule_description IGS_RU_DESCRIPTION.rule_description%TYPE;
1548 v_number NUMBER;
1549 v_item NUMBER;
1550 v_first BOOLEAN;
1551 BEGIN
1552 /*
1553 save RULE
1554 */
1555 v_rule := p_rule;
1556 /*
1557 save index
1558 */
1559 v_item := p_item;
1560 /*
1561 select all descriptions where the first description item <> return type
1562 */
1563 FOR rule_descriptions IN (
1564 SELECT RUD.sequence_number,
1565 s_turin_function,
1566 rule_description,
1567 description
1568 FROM IGS_RU_DESCRIPTION RUD,
1569 IGS_RU_GROUP_SET RGS
1570 WHERE s_return_type = p_type
1571 AND rule_description NOT LIKE '#'||p_type||'%'
1572 AND RUD.sequence_number = RGS.rud_sequence_number
1573 AND RGS.rug_sequence_number = p_group
1574 ORDER BY rule_description DESC )
1575 LOOP
1576 v_rule_description := rule_descriptions.rule_description;
1577 v_first := TRUE;
1578 WHILE breakdown_desc(v_string,v_action,v_rule_description)
1579 LOOP
1580 /*
1581 only do parameter if applicable
1582 */
1583 IF rule_descriptions.s_turin_function = '$'
1584 THEN
1585 IF valid_param_type(p_type)
1586 THEN
1587 gv_param_type := p_type;
1588 ELSE
1589 EXIT; /* while, get next description */
1590 END IF;
1591 END IF;
1592 IF p_type = 'PARAM_NUMS'
1593 THEN
1594 IF NOT valid_param(gv_param_type,v_string)
1595 THEN
1596 EXIT; /* while, get next description */
1597 END IF;
1598 END IF;
1599 /*
1600 do list of values select thingo
1601 */
1602 IF v_action = '[LOV]'
1603 THEN
1604 RETURN do_LOV(p_rule,p_rule_number,
1605 p_item,v_rule_description);
1606 /*
1607 do generic set stuff determined from SQL
1608 */
1609 ELSIF v_action = '[GS_MBR]'
1610 THEN
1611 RETURN make_set(p_rule,p_rule_number,
1612 p_item,v_rule_description);
1613
1614 ELSIF v_action = '[ASCII]' OR v_action = '[DATE]'
1615 THEN
1616 /*
1617 set termiate string character
1618 */
1619 gv_string_terminate := v_string;
1620 END IF;
1621 IF v_string IS NOT NULL
1622 THEN
1623 /*
1624 string [action]
1625 */
1626 IF match_string_to_rule(v_string,p_rule)
1627 THEN
1628 /*
1629 found match, follow this path
1630 */
1631 IF v_first
1632 THEN
1633 IF v_action IS NULL
1634 THEN
1635 /*
1636 turin function, named RULE or value
1637 */
1638 make_rule_item('2',p_rule_number,p_item,
1639 rule_descriptions.s_turin_function,
1640 rule_descriptions.sequence_number,
1641 NULL,NULL,
1642 v_string);
1643 ELSE
1644 /*
1645 turin function, named RULE or intermediate action
1646 */
1647 make_rule_item('3',p_rule_number,p_item,
1648 rule_descriptions.s_turin_function,
1649 rule_descriptions.sequence_number,
1650 NULL,NULL,NULL);
1651 END IF;
1652 END IF;
1653 IF v_action IS NOT NULL
1654 THEN
1655 IF parse_rule(v_action,p_rule,p_rule_number,p_item)
1656 THEN
1657 IF v_rule_description IS NULL
1658 THEN
1659 RETURN TRUE;
1660 END IF;
1661 ELSE
1662 EXIT; /* while, next description */
1663 END IF;
1664 ELSE
1665 /*
1666 no more description
1667 */
1668 RETURN TRUE;
1669 END IF;
1670 ELSE
1671 make_LOV(p_rule,v_string,
1672 rule_descriptions.description,'Y');
1673 EXIT; /* while, next description */
1674 END IF;
1675 ELSIF v_action IS NOT NULL
1676 THEN
1677 /*
1678 action only
1679 lt,lte,eq,neq,gte,gt #ALLOWED_TYPE
1680 */
1681 make_rule_item('4',p_rule_number,p_item,
1682 rule_descriptions.s_turin_function,
1683 rule_descriptions.sequence_number,
1684 NULL,NULL,NULL);
1685 IF parse_rule(v_action,p_rule,p_rule_number,p_item)
1686 THEN
1687 IF v_rule_description IS NULL
1688 THEN
1689 RETURN TRUE;
1690 END IF;
1691 ELSE
1692 /*
1693 allow for number RETURN FALSE;
1694 */
1695 EXIT;
1696 END IF;
1697 END IF;
1698 v_first := FALSE;
1699 END LOOP;
1700 /* restore RULE for next option */
1701 p_rule := v_rule;
1702 /* restore index for next option */
1703 p_item := v_item;
1704 END LOOP;
1705 RETURN FALSE;
1706 END;
1707 END parse_rule2;
1708 /*
1709 parse RULE
1710 */
1711 FUNCTION parse_rule(
1712 p_type IN VARCHAR2,
1713 p_rule IN OUT NOCOPY VARCHAR2,
1714 p_rule_number IN NUMBER,
1715 p_item IN OUT NOCOPY NUMBER)
1716 RETURN BOOLEAN IS
1717 BEGIN DECLARE
1718 v_rule VARCHAR2(2000);
1719 v_string VARCHAR2(2000);
1720 v_action IGS_RU_RET_TYPE.s_return_type%TYPE;
1721 v_rule_description IGS_RU_DESCRIPTION.rule_description%TYPE;
1722 v_first_action BOOLEAN;
1723 v_return BOOLEAN;
1724 v_item NUMBER;
1725 v_new_rule NUMBER;
1726 v_new_item NUMBER;
1727 v_type IGS_RU_RET_TYPE.s_return_type%TYPE;
1728 v_evaluate BOOLEAN;
1729 v_number NUMBER;
1730 v_set_type IGS_RU_SET.set_type%TYPE;
1731 v_next_action IGS_RU_RET_TYPE.s_return_type%TYPE;
1732 BEGIN
1733 /*
1734 special actions
1735 */
1736 IF SUBSTR(p_type,1,5) = '[0-9]'
1737 THEN
1738 IF get_number(v_number,p_rule) = TRUE
1739 THEN
1740 IF SUBSTR(p_type,6) IS NULL OR
1741 SUBSTR(p_type,6) <> ':VERIFY'
1742 THEN
1743 /*
1744 add number
1745 */
1746 make_rule_item('5',p_rule_number,p_item,
1747 NULL,NULL,NULL,NULL,
1748 v_number);
1749 END IF;
1750 RETURN TRUE;
1751 ELSE
1752 make_LOV(p_rule,'*** A Number ***',
1753 'Input a number.','N');
1754 RETURN FALSE;
1755 END IF;
1756 ELSIF SUBSTR(p_type,1,7) = '[ASCII]'
1757 THEN
1758 /*
1759 this assumes terminate string is same as commencement string?
1760 */
1761 IF get_string(v_string,p_rule,gv_string_terminate) = TRUE
1762 THEN
1763 /*
1764 add string
1765 */
1766 make_rule_item('5.1',p_rule_number,p_item,
1767 NULL,NULL,NULL,NULL,
1768 v_string);
1769 RETURN TRUE;
1770 ELSE
1771 make_LOV(p_rule,'*** A String ***',
1772 'Input any text.','N');
1773 RETURN FALSE;
1774 END IF;
1775 ELSIF SUBSTR(p_type,1,6) = '[DATE]'
1776 THEN
1777 IF get_date(v_string,p_rule) = TRUE
1778 THEN
1779 /*
1780 add date
1781 */
1782 make_rule_item('5.1',p_rule_number,p_item,
1783 NULL,NULL,NULL,NULL,
1784 v_string);
1785 RETURN TRUE;
1786 ELSE
1787 make_LOV(p_rule,'*** A Date ***',
1788 'Input a valid date.','N');
1789 RETURN FALSE;
1790 END IF;
1791 /*
1792 SET RELATED CODE
1793 all set types are described as (only change lower case fields to suit)
1794 set_name {#[SET]set_type}
1795 set_type #[MEMBER]set_member
1796 set_type #[MEMBER]set_member,set_type
1797 set_member #[set_cd]
1798 set_member #[set_cd].#VERSIONS
1799 new set
1800 */
1801 ELSIF SUBSTR(p_type,1,5) = '[SET]'
1802 THEN
1803 /*
1804 get set type/next action
1805 set type is restricted to 10 chars
1806 */
1807 v_set_type := SUBSTR(p_type,6);
1808 v_next_action := v_set_type;
1809 gv_set_number := new_set(v_set_type);
1810 IF parse_rule(v_next_action,p_rule,p_rule_number,p_item)
1811 THEN
1812 /*
1813 add set number
1814 */
1815 make_rule_item('6',p_rule_number,p_item,
1816 NULL,NULL,NULL,
1817 gv_set_number,
1818 NULL);
1819 RETURN TRUE;
1820 ELSE
1821 RETURN FALSE;
1822 END IF;
1823 /*
1824 make set member
1825 */
1826 ELSIF SUBSTR(p_type,1,8) = '[MEMBER]'
1827 THEN
1828 v_rule := p_rule;
1829 /*
1830 get next action
1831 */
1832 v_next_action := SUBSTR(p_type,9);
1833 IF parse_rule(v_next_action,p_rule,p_rule_number,p_item)
1834 THEN
1835 insert_set_member(gv_set_number,
1836 SUBSTR(v_rule,1,LENGTH(v_rule) - LENGTH(p_rule)));
1837 RETURN TRUE;
1838 ELSE
1839 RETURN FALSE;
1840 END IF;
1841 /*
1842 consume set member, make LOV's
1843 one per set type
1844 */
1845 ELSIF p_type = '[UNIT_CD]'
1846 THEN
1847 RETURN consume_unit_code(p_rule);
1848 ELSIF p_type = '[US_CD]'
1849 THEN
1850 RETURN consume_us_code(p_rule);
1851 ELSIF p_type = '[COURSE_CD]'
1852 THEN
1853 RETURN consume_crs_code(p_rule);
1854 /*
1855 SUB RULE CODE
1856 */
1857 ELSIF SUBSTR(p_type,1,5) = 'RULE.'
1858 THEN
1859 /*
1860 make new sub-RULE
1861 */
1862 v_type := SUBSTR(p_type,6);
1863 IF SUBSTR(v_type,1,5) = 'EVAL.'
1864 THEN
1865 /*
1866 evaluate RULE
1867 */
1868 v_type := SUBSTR(v_type,6);
1869 v_evaluate := TRUE;
1870 ELSE
1871 v_evaluate := FALSE;
1872 END IF;
1873 v_new_rule := new_rule;
1874 v_new_item := 1;
1875 IF parse_rule(v_type,p_rule,v_new_rule,v_new_item)
1876 THEN
1877 IF v_evaluate
1878 THEN
1879 /*
1880 add evaluate RULE item
1881 */
1882 make_rule_item('7',p_rule_number,p_item,
1883 '_ER',
1884 NULL,NULL,NULL,NULL);
1885 END IF;
1886 make_rule_item('8',p_rule_number,p_item,
1887 NULL,NULL,
1888 v_new_rule,
1889 NULL,gv_params);
1890 RETURN TRUE;
1891 END IF;
1892 RETURN FALSE;
1893 END IF;
1894 gv_level := gv_level + 1;
1895 /*
1896 save RULE
1897 */
1898 v_rule := p_rule;
1899 /*
1900 save index
1901 */
1902 v_item := p_item;
1903 /*
1904 select all descriptions where the first description item = return type
1905 */
1906 FOR rule_descriptions IN (
1907 SELECT RUD.sequence_number,
1908 s_turin_function,
1909 rule_description,
1910 description
1911 FROM IGS_RU_DESCRIPTION RUD,
1912 IGS_RU_GROUP_SET RGS
1913 WHERE s_return_type = p_type
1914 AND rule_description LIKE '#'||p_type||'%'
1915 AND RUD.sequence_number = RGS.rud_sequence_number
1916 AND RGS.rug_sequence_number = p_group
1917 ORDER BY rule_description DESC )
1918 LOOP
1919 v_rule_description := rule_descriptions.rule_description;
1920 v_first_action := TRUE;
1921 WHILE breakdown_desc(v_string,v_action,v_rule_description)
1922 LOOP
1923 IF v_string IS NOT NULL
1924 THEN
1925 /*
1926 string [action]
1927 */
1928 IF match_string_to_rule(v_string,p_rule)
1929 THEN
1930 IF v_action IS NOT NULL
1931 THEN
1932 IF parse_rule(v_action,p_rule,p_rule_number,p_item)
1933 THEN
1934 IF v_rule_description IS NULL
1935 THEN
1936 gv_level := gv_level - 1;
1937 RETURN TRUE;
1938 END IF;
1939 ELSE
1940 gv_level := gv_level - 1;
1941 RETURN FALSE;
1942 END IF;
1943 ELSE
1944 /*
1945 no more description
1946 */
1947 gv_level := gv_level - 1;
1948 RETURN TRUE;
1949 END IF;
1950 ELSE
1951 make_LOV(p_rule,v_string,
1952 rule_descriptions.description,'Y');
1953 EXIT; /* while, next description */
1954 END IF;
1955 ELSIF v_action IS NOT NULL
1956 THEN
1957 /*
1958 first and subsequent action
1959 or,and,divide,sutract,plus,mult #ALLOWED_TYPE
1960 */
1961 make_rule_item('9',p_rule_number,p_item,
1962 rule_descriptions.s_turin_function,
1963 rule_descriptions.sequence_number,
1964 NULL,NULL,NULL);
1965 IF v_first_action
1966 THEN
1967 /*
1968 #THIS_TYPE etc
1969 */
1970 IF parse_rule2(v_action,p_rule,p_rule_number,p_item)
1971 THEN
1972 IF v_rule_description IS NULL
1973 THEN
1974 gv_level := gv_level - 1;
1975 RETURN TRUE;
1976 END IF;
1977 ELSE
1978 gv_level := gv_level - 1;
1979 RETURN FALSE;
1980 END IF;
1981 ELSE
1982 IF parse_rule(v_action,p_rule,p_rule_number,p_item)
1983 THEN
1984 IF v_rule_description IS NULL
1985 THEN
1986 gv_level := gv_level - 1;
1987 RETURN TRUE;
1988 END IF;
1989 ELSE
1990 gv_level := gv_level - 1;
1991 RETURN FALSE;
1992 END IF;
1993 END IF;
1994 END IF;
1995 v_first_action := FALSE;
1996
1997 END LOOP;
1998 /*
1999 restore RULE for next option
2000 */
2001 p_rule := v_rule;
2002 /*
2003 restore index for next option
2004 */
2005 p_item := v_item;
2006 END LOOP;
2007 /*
2008 no success therefor try rest of TYPE
2009 */
2010 v_return := parse_rule2(p_type,p_rule,p_rule_number,p_item);
2011 gv_level := gv_level - 1;
2012 RETURN v_return;
2013 END;
2014 END parse_rule;
2015 /*
2016 Preserve spaces of user defined codes
2017 */
2018 /* This following function has been added by nshee on 24-JUL-2002 as part of fix for bug:2395891
2019 Changes are:
2020 v_unit := UPPER(REPLACE(v_unit,cst_spacemod,' '));added this line by nshee as fix for bug 2395891 to insert back the space at the right places to validate with the DB record.
2021 WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1)); commented by nshee as part of fix of bug 2381638 and 2395891 and added the next line to validate the DB record correctly.
2022 WHERE unit_set_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1));
2023 Earlier, In case the user defined program code or the milestone type has an internal space in the code
2024 itself, it was removing the same too. However, the parsing was succesful since the spaces are removed from the
2025 database record too before the parsing. However, the record was not getting back the spaces later and hence the problem was happening.
2026
2027 The string manipulation done in the function is a little complex in logic. There is a chance that the logic doesn't cater to all complexities that might arise later,
2028 it is best suggested that the user defined setup codes should not have any internal space whatsoever.
2029 In case, one needs to revert it back to the earlier state, here is what you need to do.
2030 1) remove the following line in the procedure unit insert_set_member.
2031 v_unit := UPPER(REPLACE(v_unit,cst_spacemod,' '));added this line by nshee as fix for bug 2395891 to insert back the space at the right places to validate with the DB record.
2032 2) Remove the call to this function below and comment out NOCOPY the entire Function of pres_space begin to end.
2033 3) remove all declarations of cst_spacemod within this package along with the following variables in the declaration section
2034 4) Uncomment the line added in FUNCTION consume_us_code and remove the line added
2035 5) Compile the package in the DB again...enjoy.
2036 */
2037 FUNCTION pres_space (v_str IN VARCHAR2) RETURN VARCHAR2 AS
2038 v_out_str VARCHAR2(2000);
2039 v_desired_str VARCHAR2(2000);
2040 TYPE rec_pos IS RECORD (start_pos NUMBER, end_pos NUMBER);
2041 TYPE tab_str IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
2042 TYPE tab_pos IS TABLE OF rec_pos INDEX BY BINARY_INTEGER;
2043 v_tab_str tab_str;
2044 ctr BINARY_INTEGER := 1;
2045 v_tab_str_f tab_str;
2046 ctr_f BINARY_INTEGER := 1;
2047 v_tab_pos tab_pos;
2048 ctr_pos BINARY_INTEGER := 1;
2049 v_tab_pos_f tab_pos;
2050 ctr_pos_f BINARY_INTEGER := 1;
2051 v_curly NUMBER:=0;
2052 v_start_pos NUMBER;
2053 v_end_pos NUMBER;
2054 v_out_str1 VARCHAR2(4000);
2055 v_start BOOLEAN DEFAULT FALSE;
2056
2057 PROCEDURE putarray (p_chr VARCHAR2, p_curly NUMBER) IS
2058 BEGIN
2059 IF p_curly >= 1 AND p_chr = ' ' THEN
2060 v_tab_str(ctr) := cst_spacemod;
2061 ctr := ctr +1;
2062 ELSE
2063 v_tab_str(ctr) := p_chr;
2064 ctr := ctr +1;
2065 END IF;
2066 END putarray;
2067
2068 BEGIN
2069 FOR i IN 1..LENGTH(v_str) LOOP
2070 IF substr(v_str,i,1) = '{' THEN
2071 v_curly := v_curly + 1;
2072 ELSIF substr(v_str,i,1) = '}' AND v_curly >= 1 THEN
2073 v_curly := v_curly - 1;
2074 END IF;
2075 putarray (substr(v_str,i,1),v_curly);
2076 END LOOP;
2077 -- print the v_tab_str
2078 FOR i IN v_tab_str.FIRST..v_tab_str.LAST LOOP
2079 v_out_str := v_out_str || v_tab_str(i);
2080 END LOOP;
2081 IF instr(v_out_str,cst_spacemod) = 0 THEN
2082 v_desired_str := v_out_str;
2083 RETURN v_desired_str;
2084 END IF;
2085 FOR i IN v_tab_str.FIRST..v_tab_str.LAST LOOP
2086 IF v_tab_str(i) = cst_spacemod THEN
2087 v_tab_str_f (ctr_f) := IGS_GE_NUMBER.TO_CANN(i);
2088 ctr_f := ctr_f+1;
2089 END IF;
2090 END LOOP;
2091 FOR i IN v_tab_str_f.FIRST..v_tab_str_f.LAST LOOP
2092 v_out_str1 := v_out_str1 || v_tab_str_f(i);
2093 END LOOP;
2094 FOR i IN v_tab_str_f.FIRST..v_tab_str_f.LAST LOOP
2095 IF v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_str_f(i))-1) = '{' OR
2096 v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_str_f(i))-1) NOT IN (cst_spacemod) THEN
2097 v_start_pos := IGS_GE_NUMBER.TO_NUM(v_tab_str_f(i));
2098 END IF;
2099 IF v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_str_f(i))+1) NOT IN (cst_spacemod) THEN
2100 v_end_pos := IGS_GE_NUMBER.TO_NUM(v_tab_str_f(i));
2101 END IF;
2102 IF v_start_pos IS NOT NULL OR v_end_pos is NOT NULL THEN
2103 IF v_start_pos IS NOT NULL THEN
2104 v_tab_pos(ctr_pos).start_pos := v_start_pos;
2105 v_start := TRUE;
2106 END IF;
2107 IF v_end_pos IS NOT NULL AND v_start THEN
2108 v_tab_pos(ctr_pos).end_pos := v_end_pos;
2109 v_start := FALSE;
2110 END IF;
2111 IF v_tab_pos(ctr_pos).end_pos IS NOT NULL AND
2112 v_tab_pos(ctr_pos).start_pos IS NOT NULL THEN
2113 ctr_pos := ctr_pos +1;
2114 END IF;
2115 END IF;
2116 v_start_pos := NULL;
2117 v_end_pos := NULL;
2118 END LOOP;
2119 -- filter out NOCOPY unwanted rows from v_tab_pos
2120 FOR i IN v_tab_pos.FIRST..v_tab_pos.LAST LOOP
2121 IF (v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_pos(i).start_pos) - 1) = '{' OR
2122 v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_pos(i).end_pos) +1) = '}') OR
2123 (v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_pos(i).start_pos) - 1) = ',' OR
2124 v_tab_str(IGS_GE_NUMBER.TO_NUM(v_tab_pos(i).end_pos) +1) = ',')THEN
2125 v_tab_pos_f(ctr_pos_f).start_pos := v_tab_pos(i).start_pos;
2126 v_tab_pos_f(ctr_pos_f).end_pos := v_tab_pos(i).end_pos;
2127 ctr_pos_f := ctr_pos_f + 1;
2128 END IF;
2129 END LOOP;
2130 v_desired_str := v_out_str;
2131 -- now loop the tab v_tab_pos_f to get the desired output
2132 IF v_tab_pos_f.COUNT = 0 THEN
2133 RETURN v_desired_str;
2134 END IF;
2135 FOR i IN v_tab_pos_f.FIRST..v_tab_pos_f.LAST LOOP
2136 v_desired_str := substr(v_desired_str,1,v_tab_pos_f(i).start_pos-1)||rpad(' ',(v_tab_pos_f(i).end_pos-v_tab_pos_f(i).start_pos)+1)||substr(v_desired_str,v_tab_pos_f(i).end_pos+1);
2137 END LOOP;
2138 RETURN v_desired_str;
2139 END pres_space;
2140 /*
2141 rulp_ins_parser
2142 */
2143 BEGIN DECLARE
2144 v_input_rule VARCHAR2(2000);
2145 v_rule VARCHAR2(2000);
2146 v_return BOOLEAN;
2147 v_item NUMBER;
2148 v_rule_length NUMBER;
2149 v_loop_count NUMBER := 1;
2150 CURSOR C_RNR IS
2151 SELECT ROWID, rnr.*
2152 FROM IGS_RU_NAMED_RULE rnr
2153 WHERE rul_sequence_number = p_rule_number;
2154
2155 BEGIN
2156 v_input_rule := p_rule_processed||p_rule_unprocessed;
2157 gv_params := set_params(p_rule_description);
2158 IF p_generate_rule = TRUE
2159 THEN
2160 /*
2161 new RULE uses index
2162 */
2163 gv_rn_index := 0;
2164 gv_sn_index := 0;
2165 IF p_rule_number IS NULL
2166 THEN
2167 p_rule_number := new_rule;
2168 END IF;
2169 END IF;
2170 /*
2171 loop until more than 1 item to select
2172 */
2173 LOOP
2174 IF p_generate_rule = TRUE
2175 THEN
2176 gv_rn_index := 0;
2177 gv_sn_index := 0;
2178 delete_rule_items(p_rule_number,0);
2179 END IF;
2180 /*
2181 initialise for parser
2182 */
2183 gv_level := -1;
2184 gv_min_rule := 2000;
2185 gv_LOV_index := 0;
2186 v_item := 1;
2187 /*
2188 replace all real spaces with internal spaces
2189 */
2190 -- v_rule := REPLACE(v_input_rule,' ',cst_space);
2191 /* Start of the call to the function pres_space for bug 2395891 by nshee*/
2192 IF v_input_rule IS NULL THEN
2193 v_rule := REPLACE(v_input_rule,' ',cst_space);
2194 ELSE
2195 v_rule:= REPLACE(pres_space(v_input_rule),' ',cst_space);
2196 END IF;
2197 /* End of the call to the function pres_space for bug 2395891 by nshee*/
2198 v_return := parse_rule(p_return_type,v_rule,p_rule_number,v_item);
2199 IF v_return = TRUE
2200 THEN
2201 make_LOV(v_rule,'*** Parse Successful ***',
2202 'The rule has been successfully completed.','N');
2203 END IF;
2204 /*
2205 limit the number of parses for safety
2206 */
2207 v_loop_count := v_loop_count + 1;
2208 IF v_loop_count > 8
2209 THEN
2210 EXIT;
2211 END IF;
2212 /*
2213 build select from list
2214 */
2215 insert_LOV_tab;
2216 /*
2217 on more than one item EXIT
2218 */
2219 IF LOV_item IS NULL
2220 THEN
2221 EXIT;
2222 END IF;
2223 /*
2224 on error, left over RULE
2225 */
2226 IF gv_min_rule <> 0
2227 THEN
2228 EXIT;
2229 END IF;
2230 /*
2231 append the single item to RULE
2232 */
2233 v_input_rule := v_input_rule||' '||LOV_item;
2234 END LOOP;
2235 /*
2236 separate processed and unprocessed portions of RULE
2237 */
2238 v_rule_length := LENGTH(v_input_rule);
2239 p_rule_processed := SUBSTR(v_input_rule,1,v_rule_length - gv_min_rule);
2240 p_rule_unprocessed := SUBSTR(v_input_rule,v_rule_length - gv_min_rule
2241 + 1 /* + gv_select_count */);
2242 /*
2243 display_LOV;
2244 */
2245 IF p_generate_rule = TRUE
2246 THEN
2247 /*
2248 update RULE text if it exists
2249 */
2250 v_rule := IGS_RU_GEN_006.rulp_get_rule(p_rule_number);
2251
2252 FOR C_RNR_REC IN C_RNR LOOP
2253 IGS_RU_NAMED_RULE_PKG.UPDATE_ROW (
2254 X_ROWID => C_RNR_REC.ROWID,
2255 X_RUL_SEQUENCE_NUMBER => C_RNR_REC.RUL_SEQUENCE_NUMBER,
2256 X_RUD_SEQUENCE_NUMBER => C_RNR_REC.RUD_SEQUENCE_NUMBER,
2257 X_MESSAGE_RULE => C_RNR_REC.MESSAGE_RULE,
2258 X_RUG_SEQUENCE_NUMBER => C_RNR_REC.RUG_SEQUENCE_NUMBER,
2259 X_RULE_TEXT => v_rule );
2260 END LOOP;
2261
2262 END IF;
2263 RETURN v_return;
2264 END;
2265 END rulp_ins_parser;
2266
2267 END IGS_RU_GEN_002;
[Home] [Help]