1 PACKAGE BODY fnd_flex_hierarchy_compiler AS
2 /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
3
4
5 -- ======================================================================
6 -- Compiler Example
7 -- ======================================================================
8 -- North America Country, State, City Hierarchy.
9 --
10 -- FND_FLEX_VALUE_NORM_HIERARCHY
11 -- ===========================================
12 -- PARENT CHILD_LOW CHILD_HIGH RANGE
13 -- ---------- ---------- ----------- ----------
14 -- CA LA LA C
15 -- CA SF SF C
16 -- CAN Tor Tor C
17 -- US Ch Ch C
18 -- NA CAN CAN P
19 -- NA US US P
20 -- US CA CA P
21 -- US NY NY P
22 --
23 -- FND_FLEX_VALUE_HIERARCHIES
24 -- ===========================================
25 -- PARENT CHILD_LOW CHILD_HIGH COMMENT
26 -- ------- --------- ---------- ----------
27 -- CA LA LA CA:LA-LA
28 -- CA SF SF CA:SF-SF
29 -- CAN Tor Tor CAN:Tor-Tor
30 -- US Ch Ch US:Ch-Ch
31 -- NA Tor Tor NA:CAN-CAN -> CAN:Tor-Tor
32 -- NA Ch Ch NA:US-US -> US:Ch-Ch
33 -- NA LA LA NA:US-US -> US:CA-CA -> CA:LA-LA
34 -- NA SF SF NA:US-US -> US:CA-CA -> CA:SF-SF
35 -- US LA LA US:CA-CA -> CA:LA-LA
36 -- US SF SF US:CA-CA -> CA:SF-SF
37 --
38 -- FND_FLEX_VALUE_HIER_ALL
39 -- ===========================================
40 -- PARENT CHILD_LOW CHILD_HIGH RANGE LEVEL COMMENT
41 -- ------- --------- ---------- ----- ----- ----------
42 -- CA LA LA C 1 CA:LA-LA
43 -- CA SF SF C 1 CA:SF-SF
44 -- CAN Tor Tor C 1 CAN:Tor-Tor
45 -- US Ch Ch C 1 US:Ch-Ch
46 -- NA CAN CAN P 1 NA:CAN-CAN
47 -- NA Tor Tor C 2 NA:CAN-CAN -> CAN:Tor-Tor
48 -- NA US US P 1 NA:US-US
49 -- NA Ch Ch C 2 NA:US-US -> US:Ch-Ch
50 -- NA CA CA P 2 NA:US-US -> US:CA-CA
51 -- NA LA LA C 3 NA:US-US -> US:CA-CA -> CA:LA-LA
52 -- NA SF SF C 3 NA:US-US -> US:CA-CA -> CA:SF-SF
53 -- NA NY NY P 2 NA:US-US -> US:NY-NY
54 -- US CA CA P 1 US:CA-CA
55 -- US LA LA C 2 US:CA-CA -> CA:LA-LA
56 -- US SF SF C 2 US:CA-CA -> CA:SF-SF
57 -- US NY NY P 1 US:NY-NY
58 --
59 -- ======================================================================
60
61 -- ======================================================================
62 -- Package Globals.
63 -- ======================================================================
64 TYPE hierarchy_record IS RECORD
65 (parent_flex_value fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE,
66 child_flex_value_low fnd_flex_value_norm_hierarchy.child_flex_value_low%TYPE,
67 child_flex_value_high fnd_flex_value_norm_hierarchy.child_flex_value_high%TYPE,
68 range_attribute fnd_flex_value_norm_hierarchy.range_attribute%TYPE);
69
70 TYPE hierarchy_array IS TABLE OF hierarchy_record INDEX BY BINARY_INTEGER;
71
72 TYPE varchar2_array IS TABLE OF fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE INDEX BY BINARY_INTEGER;
73
74 --
75 -- Cycle Checker
76 --
77 g_parent_path_values varchar2_array;
78 g_parent_path_value_count NUMBER;
79
80 SUBTYPE vset_type IS fnd_flex_value_sets%ROWTYPE;
81 g_vset vset_type;
82 g_user_id NUMBER := -1;
83 g_insert_count NUMBER := 0;
84 g_commit_size NUMBER := 500;
85 g_message_size NUMBER := 1950;
86 g_debug_on BOOLEAN := FALSE;
87 g_newline VARCHAR2(10);
88 g_exception_depth NUMBER := 0;
89 g_error_message VARCHAR2(32000);
90 g_api_name CONSTANT VARCHAR2(10) := 'HIER';
91 g_date_mask CONSTANT VARCHAR2(100) := 'YYYY/MM/DD HH24:MI:SS';
92
93 --
94 -- Error Codes
95 --
96 error_others NUMBER := -20100;
97 error_no_value_set NUMBER := -20101;
98 error_delete_hierarchies NUMBER := -20102;
99 error_cyclic_hierarchy NUMBER := -20103;
100 error_unknown_range_attribute NUMBER := -20104;
101 error_update_hierarchies NUMBER := -20105;
102
103
104 -- ======================================================================
105 -- PROCEDURE : set_debug
106 -- ======================================================================
107 -- Turns debug setting ON/OFF
108 --
109 PROCEDURE set_debug(p_debug_flag IN VARCHAR2)
110 IS
111 BEGIN
112 IF (Nvl(p_debug_flag, 'N') = 'Y') THEN
113 g_debug_on := TRUE;
114 ELSE
115 g_debug_on := FALSE;
116 END IF;
117 EXCEPTION
118 WHEN OTHERS THEN
119 g_debug_on := FALSE;
120 END set_debug;
121
122 -- ======================================================================
123 -- PROCEDURE : debug
124 -- ======================================================================
125 -- Sends debug messages to Log file.
126 --
127 PROCEDURE debug(p_debug IN VARCHAR2)
128 IS
129 BEGIN
130 IF (g_debug_on) THEN
131 fnd_file.put_line(fnd_file.Log, p_debug);
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 NULL;
136 END debug;
137
138 -- ======================================================================
139 -- PROCEDURE : init_parent_path
140 -- ======================================================================
141 -- Initializes the parent path array.
142 --
143 PROCEDURE init_parent_path
144 IS
145 BEGIN
146 g_parent_path_value_count := 0;
147 END init_parent_path;
148
149 -- ======================================================================
150 -- PROCEDURE : add_to_parent_path
151 -- ======================================================================
152 -- Adds a parent value to the end of the parent path array.
153 --
154 PROCEDURE add_to_parent_path(p_parent_flex_value IN fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE)
155 IS
156 BEGIN
157 g_parent_path_values(g_parent_path_value_count) := p_parent_flex_value;
158 g_parent_path_value_count := g_parent_path_value_count + 1;
159 END add_to_parent_path;
160
161 -- ======================================================================
162 -- PROCEDURE : remove_from_parent_path
163 -- ======================================================================
164 -- Removes the last value from the end of the parent path array.
165 --
166 PROCEDURE remove_from_parent_path
167 IS
168 BEGIN
169 g_parent_path_value_count := g_parent_path_value_count - 1;
170 END remove_from_parent_path;
171
172 -- ======================================================================
173 -- FUNCTION : exists_in_parent_path
174 -- ======================================================================
175 -- Checks if a parent value exists in the parent path array.
176 --
177 FUNCTION exists_in_parent_path(p_parent_flex_value IN fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE)
178 RETURN BOOLEAN
179 IS
180 l_exists BOOLEAN;
181 BEGIN
182 l_exists := FALSE;
183 FOR i IN 0 .. g_parent_path_value_count - 1 LOOP
184 IF (g_parent_path_values(i) = p_parent_flex_value) THEN
185 l_exists := TRUE;
186 EXIT;
187 END IF;
188 END LOOP;
189
190 RETURN l_exists;
191 END exists_in_parent_path;
192
193 -- ======================================================================
194 -- FUNCTION : get_formatted_method_call
195 -- ======================================================================
196 -- Returns formatted method call
197 --
198 -- p_method - name of the method
199 -- p_arg1..5 - method arguments
200 --
201 -- return: formatted method call
202 --
203 FUNCTION get_formatted_method_call(p_method in varchar2,
204 p_arg1 in varchar2 default null,
205 p_arg2 in varchar2 default null,
206 p_arg3 in varchar2 default null,
207 p_arg4 in varchar2 default null,
208 p_arg5 in varchar2 default null)
209 return varchar2
210 is
211 l_method varchar2(32000);
212 begin
213 l_method := p_method || '(';
214
215 if (p_arg1 is not null) then
216 l_method := l_method || p_arg1;
217 end if;
218
219 if (p_arg2 is not null) then
220 l_method := l_method || ', ' || p_arg2;
221 end if;
222
223 if (p_arg3 is not null) then
224 l_method := l_method || ', ' || p_arg3;
225 end if;
226
227 if (p_arg4 is not null) then
228 l_method := l_method || ', ' || p_arg4;
229 end if;
230
231 if (p_arg5 is not null) then
232 l_method := l_method || ', ' || p_arg5;
233 end if;
234
235 l_method := l_method || ')';
236
237 return l_method;
238
239 exception
240 when others then
241 return p_method;
242 end get_formatted_method_call;
243
244 -- ======================================================================
245 -- FUNCTION : get_formatted_lines
246 -- ======================================================================
247 -- Returns formatted error lines.
248 --
249 -- p_line0 - The first line that goes after ORA error number
250 -- p_line1..5 - optional lines to be indented
251 --
252 -- return: formatted error message
253 --
254 FUNCTION get_formatted_lines(p_line0 in varchar2,
255 p_line1 in varchar2 default null,
256 p_line2 in varchar2 default null,
257 p_line3 in varchar2 default null,
258 p_line4 in varchar2 default null,
259 p_line5 in varchar2 default null)
260 return varchar2
261 is
262 l_error_text varchar2(32000);
263 l_newline_indent varchar2(2000);
264 begin
265 l_newline_indent := g_newline || rpad(' ', 11, ' ');
266
267 l_error_text := p_line0;
268
269 if (p_line1 is not null) then
270 l_error_text := l_error_text || l_newline_indent || p_line1;
271 end if;
272
273 if (p_line2 is not null) then
274 l_error_text := l_error_text || l_newline_indent || p_line2;
275 end if;
276
277 if (p_line3 is not null) then
278 l_error_text := l_error_text || l_newline_indent || p_line3;
279 end if;
280
281 if (p_line4 is not null) then
282 l_error_text := l_error_text || l_newline_indent || p_line4;
283 end if;
284
285 if (p_line5 is not null) then
286 l_error_text := l_error_text || l_newline_indent || p_line5;
287 end if;
288
289 return l_error_text;
290
291 end get_formatted_lines;
292
293 -- ======================================================================
294 -- PROCEDURE : raise_formatted_error
295 -- ======================================================================
296 -- Raises formatted application error.
297 --
298 -- p_error_code - error code
299 -- p_error_text - error text
300 --
301 PROCEDURE raise_formatted_error(p_error_code in number,
302 p_error_text in varchar2)
303 is
304 l_error_text varchar2(32000);
305 begin
306 l_error_text := p_error_text || g_newline || dbms_utility.format_error_stack();
307
308 raise_application_error(p_error_code, l_error_text);
309
310 exception
311 when others then
312 --
313 -- Store the root cause of the problem. This will be presented to
314 -- user as the main cause of the exception. Rest of the exception is
315 -- basically the call stack trace.
316 --
317 if (g_exception_depth = 0) then
318 g_error_message := dbms_utility.format_error_stack();
319 end if;
320
321 g_exception_depth := g_exception_depth + 1;
322
323 raise;
324 end raise_formatted_error;
325
326 -- ======================================================================
327 -- PROCEDURE : raise_exception_error
328 -- ======================================================================
329 -- Raises exception by formatting the error lines.
330 --
331 -- p_error_code - error code
332 -- p_line0 - The first line that goes after ORA error number
333 -- p_line1..5 - optional lines to be indented
334 --
335 PROCEDURE raise_exception_error(p_error_code in number,
336 p_line0 in varchar2,
337 p_line1 in varchar2 default null,
338 p_line2 in varchar2 default null,
339 p_line3 in varchar2 default null,
340 p_line4 in varchar2 default null,
341 p_line5 in varchar2 default null)
342 IS
343 l_error_text VARCHAR2(32000);
344 BEGIN
345 l_error_text := get_formatted_lines(p_line0,
346 p_line1,
347 p_line2,
348 p_line3,
349 p_line4,
350 p_line5);
351
352 raise_formatted_error(p_error_code, l_error_text);
353
354 -- No exception handling here
355 END raise_exception_error;
356
357 -- ======================================================================
358 -- PROCEDURE : raise_others_error
359 -- ======================================================================
360 -- Raises formatted error for 'when others then' block
361 --
362 -- p_method - name of the method
363 -- p_arg1..5 - method arguments
364 --
365 PROCEDURE raise_others_error(p_method in varchar2,
366 p_arg1 in varchar2 default null,
367 p_arg2 in varchar2 default null,
368 p_arg3 in varchar2 default null,
369 p_arg4 in varchar2 default null,
370 p_arg5 in varchar2 default null)
371 is
372 l_error_text varchar2(32000);
373 begin
374 l_error_text := get_formatted_method_call(p_method,
375 p_arg1,
376 p_arg2,
377 p_arg3,
378 p_arg4,
379 p_arg5);
380
381 l_error_text := l_error_text || ' raised exception.';
382
383 raise_formatted_error(error_others, l_error_text);
384
385 -- No exception handling here
386 end raise_others_error;
387
388 -- ======================================================================
389 -- FUNCTION : get_vset
390 -- ======================================================================
391 -- Gets Value Set
392 --
393 PROCEDURE get_vset(p_flex_value_set IN VARCHAR2,
394 x_vset OUT nocopy vset_type)
395 IS
396 BEGIN
397 --
398 -- Try it as FLEX_VALUE_SET_ID.
399 --
400 SELECT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
401 *
402 INTO x_vset
403 FROM fnd_flex_value_sets
404 WHERE flex_value_set_id = To_number(p_flex_value_set);
405
406 RETURN;
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 --
411 -- ID didn't work, try it as FLEX_VALUE_SET_NAME.
412 --
413 BEGIN
414 SELECT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
415 *
416 INTO x_vset
417 FROM fnd_flex_value_sets
418 WHERE flex_value_set_name = p_flex_value_set;
419
420 RETURN;
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 --
425 -- NAME didn't work too.
426 --
427 raise_exception_error(error_no_value_set,
428 'No data found in FND_FLEX_VALUE_SETS',
429 'for flex value set ' || p_flex_value_set);
430 END;
431 END get_vset;
432
433 -- ======================================================================
434 -- PROCEDURE : compile_child
435 -- ======================================================================
436 -- Inserts the child range into the de-normalized hierarchy table.
437 --
438 PROCEDURE compile_child(p_root_parent_flex_value IN VARCHAR2,
439 p_child_flex_value_low IN VARCHAR2,
440 p_child_flex_value_high IN VARCHAR2)
441 IS
442 BEGIN
443 INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
444 INTO fnd_flex_value_hierarchies
445 (flex_value_set_id, parent_flex_value,
446 child_flex_value_low, child_flex_value_high,
447 last_update_date, last_updated_by,
448 creation_date, created_by)
449 VALUES
450 (g_vset.flex_value_set_id*(-1), p_root_parent_flex_value,
451 p_child_flex_value_low, p_child_flex_value_high,
452 Sysdate, g_user_id,
453 Sysdate, g_user_id);
454
455 g_insert_count := g_insert_count + 1;
456 IF (g_insert_count = g_commit_size) THEN
457 COMMIT;
458 g_insert_count := 0;
459 END IF;
460 EXCEPTION
461 WHEN OTHERS THEN
462 raise_others_error('compile_child',
463 p_root_parent_flex_value,
464 p_child_flex_value_low,
465 p_child_flex_value_high);
466 END compile_child;
467
468 -- ======================================================================
469 -- PROCEDURE : compile_parent
470 -- ======================================================================
471 -- Recursively compiles the parent ranges.
472 --
473 PROCEDURE compile_parent(p_root_parent_flex_value IN VARCHAR2,
474 p_child_flex_value_low IN VARCHAR2,
475 p_child_flex_value_high IN VARCHAR2,
476 p_debug IN VARCHAR2)
477 IS
478 CURSOR norm_cur(p_child_flex_value_low IN VARCHAR2,
479 p_child_flex_value_high IN VARCHAR2) IS
480 SELECT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
481 parent_flex_value,
482 child_flex_value_low,
483 child_flex_value_high,
484 range_attribute
485 FROM fnd_flex_value_norm_hierarchy
486 WHERE flex_value_set_id = g_vset.flex_value_set_id
487 AND parent_flex_value >= p_child_flex_value_low
488 AND parent_flex_value <= p_child_flex_value_high
489 ORDER BY range_attribute, parent_flex_value,
490 child_flex_value_low, child_flex_value_high;
491
492 l_debug VARCHAR2(32000);
493
494 l_hierarchies hierarchy_array;
495 l_hierarchy_count BINARY_INTEGER;
496 BEGIN
497 l_hierarchy_count := 0;
498 FOR norm_rec IN norm_cur(p_child_flex_value_low,
499 p_child_flex_value_high) LOOP
500 l_hierarchies(l_hierarchy_count).parent_flex_value := norm_rec.parent_flex_value;
501 l_hierarchies(l_hierarchy_count).child_flex_value_low := norm_rec.child_flex_value_low;
502 l_hierarchies(l_hierarchy_count).child_flex_value_high := norm_rec.child_flex_value_high;
503 l_hierarchies(l_hierarchy_count).range_attribute := norm_rec.range_attribute;
504 l_hierarchy_count := l_hierarchy_count + 1;
505 END LOOP;
506
507 FOR i IN 0 .. l_hierarchy_count - 1 LOOP
508 IF (g_debug_on) THEN
509 l_debug := (p_debug || ' -> ' ||
510 l_hierarchies(i).range_attribute || ':' ||
511 l_hierarchies(i).parent_flex_value || ':' ||
512 l_hierarchies(i).child_flex_value_low || '-' ||
513 l_hierarchies(i).child_flex_value_high);
514 debug(l_debug);
515 END IF;
516
517 IF (l_hierarchies(i).range_attribute = 'C') THEN
518 compile_child(p_root_parent_flex_value,
519 l_hierarchies(i).child_flex_value_low,
520 l_hierarchies(i).child_flex_value_high);
521
522 ELSIF (l_hierarchies(i).range_attribute = 'P') THEN
523 --
524 -- Cycle check
525 --
526 IF (exists_in_parent_path(l_hierarchies(i).parent_flex_value)) THEN
527 raise_exception_error(error_cyclic_hierarchy,
528 'Cyclic hierarchy detected.',
529 'Range Attribute : ' || l_hierarchies(i).range_attribute,
530 'Parent Flex Value: ' || l_hierarchies(i).parent_flex_value,
531 'Low Child Value : ' || l_hierarchies(i).child_flex_value_low,
532 'High Child Value : ' || l_hierarchies(i).child_flex_value_high);
533 END IF;
534
535 --
536 -- Recursive call.
537 --
538 add_to_parent_path(l_hierarchies(i).parent_flex_value);
539 compile_parent(p_root_parent_flex_value,
540 l_hierarchies(i).child_flex_value_low,
541 l_hierarchies(i).child_flex_value_high,
542 l_debug);
543 remove_from_parent_path();
544
545 ELSE
546 raise_exception_error(error_unknown_range_attribute,
547 'Unknown Range Attribute detected.',
548 'Range Attribute : ' || l_hierarchies(i).range_attribute,
549 'Parent Flex Value: ' || l_hierarchies(i).parent_flex_value,
550 'Low Child Value : ' || l_hierarchies(i).child_flex_value_low,
551 'High Child Value : ' || l_hierarchies(i).child_flex_value_high);
552 END IF;
553 END LOOP;
554 EXCEPTION
555 WHEN OTHERS THEN
556 raise_others_error('compile_parent',
557 p_root_parent_flex_value,
558 p_child_flex_value_low,
559 p_child_flex_value_high);
560 END compile_parent;
561
562 -- ======================================================================
563 -- PROCEDURE : compile_value_hierarchies
564 -- ======================================================================
565 -- Compiles the flex value hierarchies in FND_FLEX_VALUE_HIERARCHIES table.
566 --
567 PROCEDURE compile_value_hierarchies
568 IS
569 CURSOR main_norm_cur IS
570 SELECT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
571 parent_flex_value,
572 child_flex_value_low,
573 child_flex_value_high,
574 range_attribute
575 FROM fnd_flex_value_norm_hierarchy
576 WHERE flex_value_set_id = g_vset.flex_value_set_id
577 ORDER BY range_attribute, parent_flex_value,
578 child_flex_value_low, child_flex_value_high;
579
580 l_debug VARCHAR2(32000);
581
582 l_hierarchies hierarchy_array;
583 l_hierarchy_count BINARY_INTEGER;
584 BEGIN
585 g_insert_count := 0;
586 l_hierarchy_count := 0;
587 FOR main_norm_rec IN main_norm_cur LOOP
588 l_hierarchies(l_hierarchy_count).parent_flex_value := main_norm_rec.parent_flex_value;
589 l_hierarchies(l_hierarchy_count).child_flex_value_low := main_norm_rec.child_flex_value_low;
590 l_hierarchies(l_hierarchy_count).child_flex_value_high := main_norm_rec.child_flex_value_high;
591 l_hierarchies(l_hierarchy_count).range_attribute := main_norm_rec.range_attribute;
592 l_hierarchy_count := l_hierarchy_count + 1;
593 END LOOP;
594
595 --
596 -- Initialize the cycle checking logic
597 --
598 init_parent_path();
599
600 FOR i IN 0 .. l_hierarchy_count - 1 LOOP
601 IF (g_debug_on) THEN
602 l_debug := (l_hierarchies(i).range_attribute || ':' ||
603 l_hierarchies(i).parent_flex_value || ':' ||
604 l_hierarchies(i).child_flex_value_low || '-' ||
605 l_hierarchies(i).child_flex_value_high);
606 debug(l_debug);
607 END IF;
608
609 IF (l_hierarchies(i).range_attribute = 'C') THEN
610 compile_child(l_hierarchies(i).parent_flex_value,
611 l_hierarchies(i).child_flex_value_low,
612 l_hierarchies(i).child_flex_value_high);
613
614 ELSIF (l_hierarchies(i).range_attribute = 'P') THEN
615 add_to_parent_path(l_hierarchies(i).parent_flex_value);
616 compile_parent(l_hierarchies(i).parent_flex_value,
617 l_hierarchies(i).child_flex_value_low,
618 l_hierarchies(i).child_flex_value_high,
619 l_debug);
620 remove_from_parent_path();
621
622 ELSE
623 raise_exception_error(error_unknown_range_attribute,
624 'Unknown Range Attribute detected.',
625 'Range Attribute : ' || l_hierarchies(i).range_attribute,
626 'Parent Flex Value: ' || l_hierarchies(i).parent_flex_value,
627 'Low Child Value : ' || l_hierarchies(i).child_flex_value_low,
628 'High Child Value : ' || l_hierarchies(i).child_flex_value_high);
629 END IF;
630 END LOOP;
631
632 --
633 -- Commit the remaining rows.
634 --
635 IF (g_insert_count > 0) THEN
636 COMMIT;
637 g_insert_count := 0;
638 END IF;
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 --
643 -- In case of error, delete the inserted rows (kind of rollback).
644 --
645 BEGIN
646 DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
647 FROM fnd_flex_value_hierarchies
648 WHERE flex_value_set_id = g_vset.flex_value_set_id;
649 COMMIT;
650 EXCEPTION
651 WHEN OTHERS THEN
652 NULL;
653 END;
654
655 raise_others_error('compile_value_hierarchies');
656 END compile_value_hierarchies;
657
658 -- ======================================================================
659 -- PROCEDURE : compile_child_all
660 -- ======================================================================
661 -- Inserts the child range into the de-normalized hierarchy table.
662 --
663 PROCEDURE compile_child_all(p_root_parent_flex_value IN VARCHAR2,
664 p_range_attribute IN VARCHAR2,
665 p_child_flex_value_low IN VARCHAR2,
666 p_child_flex_value_high IN VARCHAR2,
667 p_hierarchy_level IN NUMBER)
668 IS
669 BEGIN
670 INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
671 INTO fnd_flex_value_hier_all
672 (flex_value_set_id, parent_flex_value, range_attribute,
673 child_flex_value_low, child_flex_value_high, hierarchy_level,
674 last_update_date, last_updated_by,
675 creation_date, created_by)
676 VALUES
677 (g_vset.flex_value_set_id*(-1), p_root_parent_flex_value, p_range_attribute,
678 p_child_flex_value_low, p_child_flex_value_high, p_hierarchy_level,
679 Sysdate, g_user_id,
680 Sysdate, g_user_id);
681
682 g_insert_count := g_insert_count + 1;
683 IF (g_insert_count = g_commit_size) THEN
684 COMMIT;
685 g_insert_count := 0;
686 END IF;
687 EXCEPTION
688 WHEN OTHERS THEN
689 raise_others_error('compile_child_all',
690 p_root_parent_flex_value,
691 p_range_attribute,
692 p_child_flex_value_low,
693 p_child_flex_value_high,
694 p_hierarchy_level);
695 END compile_child_all;
696
697
698 -- ======================================================================
699 -- PROCEDURE : compile_parent_all
700 -- ======================================================================
701 -- Recursively compiles the parent ranges.
702 --
703 PROCEDURE compile_parent_all(p_root_parent_flex_value IN VARCHAR2,
704 p_child_flex_value_low IN VARCHAR2,
705 p_child_flex_value_high IN VARCHAR2,
706 p_hierarchy_level IN NUMBER)
707 IS
708 CURSOR norm_cur(p_child_flex_value_low IN VARCHAR2,
709 p_child_flex_value_high IN VARCHAR2) IS
710 SELECT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
711 parent_flex_value,
712 child_flex_value_low,
713 child_flex_value_high,
714 range_attribute
715 FROM fnd_flex_value_norm_hierarchy
716 WHERE flex_value_set_id = g_vset.flex_value_set_id
717 AND parent_flex_value >= p_child_flex_value_low
718 AND parent_flex_value <= p_child_flex_value_high
719 ORDER BY range_attribute, parent_flex_value,
720 child_flex_value_low, child_flex_value_high;
721
722 l_hierarchies hierarchy_array;
723 l_hierarchy_count BINARY_INTEGER;
724 BEGIN
725 l_hierarchy_count := 0;
726 FOR norm_rec IN norm_cur(p_child_flex_value_low,
727 p_child_flex_value_high) LOOP
728 l_hierarchies(l_hierarchy_count).parent_flex_value := norm_rec.parent_flex_value;
729 l_hierarchies(l_hierarchy_count).child_flex_value_low := norm_rec.child_flex_value_low;
730 l_hierarchies(l_hierarchy_count).child_flex_value_high := norm_rec.child_flex_value_high;
731 l_hierarchies(l_hierarchy_count).range_attribute := norm_rec.range_attribute;
732 l_hierarchy_count := l_hierarchy_count + 1;
733 END LOOP;
734
735 FOR i IN 0 .. l_hierarchy_count - 1 LOOP
736 IF (l_hierarchies(i).range_attribute = 'C') THEN
737 compile_child_all(p_root_parent_flex_value,
738 l_hierarchies(i).range_attribute,
739 l_hierarchies(i).child_flex_value_low,
740 l_hierarchies(i).child_flex_value_high,
741 p_hierarchy_level + 1);
742
743 ELSIF (l_hierarchies(i).range_attribute = 'P') THEN
744 compile_child_all(p_root_parent_flex_value,
745 l_hierarchies(i).range_attribute,
746 l_hierarchies(i).child_flex_value_low,
747 l_hierarchies(i).child_flex_value_high,
748 p_hierarchy_level + 1);
749
750 --
751 -- Cycle check
752 --
753 IF (exists_in_parent_path(l_hierarchies(i).parent_flex_value)) THEN
754 raise_exception_error(error_cyclic_hierarchy,
755 'Cyclic hierarchy detected.',
756 'Range Attribute : ' || l_hierarchies(i).range_attribute,
757 'Parent Flex Value: ' || l_hierarchies(i).parent_flex_value,
758 'Low Child Value : ' || l_hierarchies(i).child_flex_value_low,
759 'High Child Value : ' || l_hierarchies(i).child_flex_value_high);
760 END IF;
761
762 --
763 -- Recursive call.
764 --
765 add_to_parent_path(l_hierarchies(i).parent_flex_value);
766 compile_parent_all(p_root_parent_flex_value,
767 l_hierarchies(i).child_flex_value_low,
768 l_hierarchies(i).child_flex_value_high,
769 p_hierarchy_level + 1);
770 remove_from_parent_path();
771
772 ELSE
773 raise_exception_error(error_unknown_range_attribute,
774 'Unknown Range Attribute detected.',
775 'Range Attribute : ' || l_hierarchies(i).range_attribute,
776 'Parent Flex Value: ' || l_hierarchies(i).parent_flex_value,
777 'Low Child Value : ' || l_hierarchies(i).child_flex_value_low,
778 'High Child Value : ' || l_hierarchies(i).child_flex_value_high);
779 END IF;
780 END LOOP;
781 EXCEPTION
782 WHEN OTHERS THEN
783 raise_others_error('compile_parent_all',
784 p_root_parent_flex_value,
785 p_child_flex_value_low,
786 p_child_flex_value_high,
787 p_hierarchy_level);
788 END compile_parent_all;
789
790 -- ======================================================================
791 -- PROCEDURE : compile_value_hierarchies_all
792 -- ======================================================================
793 -- Compiles the flex value hierarchies in FND_FLEX_VALUE_HIER_ALL table.
794 --
795 PROCEDURE compile_value_hierarchies_all
796 IS
797 CURSOR main_norm_cur IS
798 SELECT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
799 parent_flex_value,
800 child_flex_value_low,
801 child_flex_value_high,
802 range_attribute
803 FROM fnd_flex_value_norm_hierarchy
804 WHERE flex_value_set_id = g_vset.flex_value_set_id
805 ORDER BY range_attribute, parent_flex_value,
806 child_flex_value_low, child_flex_value_high;
807
808 l_hierarchies hierarchy_array;
809 l_hierarchy_count BINARY_INTEGER;
810 BEGIN
811 g_insert_count := 0;
812
813
814 IF (g_vset.security_enabled_flag = 'H') THEN
815 l_hierarchy_count := 0;
816 FOR main_norm_rec IN main_norm_cur LOOP
817 l_hierarchies(l_hierarchy_count).parent_flex_value := main_norm_rec.parent_flex_value;
818 l_hierarchies(l_hierarchy_count).child_flex_value_low := main_norm_rec.child_flex_value_low;
819 l_hierarchies(l_hierarchy_count).child_flex_value_high := main_norm_rec.child_flex_value_high;
820 l_hierarchies(l_hierarchy_count).range_attribute := main_norm_rec.range_attribute;
821 l_hierarchy_count := l_hierarchy_count + 1;
822 END LOOP;
823
824 --
825 -- Initialize the cycle checking logic
826 --
827 init_parent_path();
828
829 FOR i IN 0 .. l_hierarchy_count - 1 LOOP
830
831 IF (l_hierarchies(i).range_attribute = 'C') THEN
832 compile_child_all(l_hierarchies(i).parent_flex_value,
833 l_hierarchies(i).range_attribute,
834 l_hierarchies(i).child_flex_value_low,
835 l_hierarchies(i).child_flex_value_high,
836 1);
837 ELSIF (l_hierarchies(i).range_attribute = 'P') THEN
838 compile_child_all(l_hierarchies(i).parent_flex_value,
839 l_hierarchies(i).range_attribute,
840 l_hierarchies(i).child_flex_value_low,
841 l_hierarchies(i).child_flex_value_high,
842 1);
843
844 add_to_parent_path(l_hierarchies(i).parent_flex_value);
845 compile_parent_all(l_hierarchies(i).parent_flex_value,
846 l_hierarchies(i).child_flex_value_low,
847 l_hierarchies(i).child_flex_value_high,
848 1);
849 remove_from_parent_path();
850
851 ELSE
852 raise_exception_error(error_unknown_range_attribute,
853 'Unknown Range Attribute detected.',
854 'Range Attribute : ' || l_hierarchies(i).range_attribute,
855 'Parent Flex Value: ' || l_hierarchies(i).parent_flex_value,
856 'Low Child Value : ' || l_hierarchies(i).child_flex_value_low,
857 'High Child Value : ' || l_hierarchies(i).child_flex_value_high);
858 END IF;
859 END LOOP;
860 END IF;
861
862 --
863 -- Commit the remaining rows.
864 --
865 IF (g_insert_count > 0) THEN
866 COMMIT;
867 g_insert_count := 0;
868 END IF;
869 EXCEPTION
870 WHEN OTHERS THEN
871 --
872 -- In case of error, delete the inserted rows (kind of rollback).
873 --
874 BEGIN
875 DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
876 FROM fnd_flex_value_hier_all
877 WHERE flex_value_set_id = g_vset.flex_value_set_id;
878 COMMIT;
879 EXCEPTION
880 WHEN OTHERS THEN
881 NULL;
882 END;
883
884 raise_others_error('compile_value_hierarchies_all');
885 END compile_value_hierarchies_all;
886
887
888 PROCEDURE delete_value_hierarchies
889 --
890 -- Delete the old data.
891 --
892 IS
893 l_row_count NUMBER;
894 BEGIN
895 l_row_count := 1;
896 WHILE (l_row_count > 0) LOOP
897 DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
898 FROM fnd_flex_value_hierarchies
899 WHERE flex_value_set_id = g_vset.flex_value_set_id and
900 rownum < 1000;
901 l_row_count := SQL%rowcount;
902 COMMIT;
903 END LOOP;
904 EXCEPTION
905 WHEN OTHERS THEN
906 raise_exception_error(error_delete_hierarchies,
907 'Unable to delete data in FND_FLEX_VALUE_HIERARCHIES table.');
908 END delete_value_hierarchies;
909
910 PROCEDURE delete_value_hierarchies_all
911 --
912 -- Delete the old data.
913 --
914 IS
915 l_row_count NUMBER;
916 BEGIN
917 l_row_count := 1;
918 WHILE (l_row_count > 0) LOOP
919 DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
920 FROM fnd_flex_value_hier_all
921 WHERE flex_value_set_id = g_vset.flex_value_set_id and
922 rownum < 1000;
923 l_row_count := SQL%rowcount;
924 COMMIT;
925 END LOOP;
926 EXCEPTION
927 WHEN OTHERS THEN
928 raise_exception_error(error_delete_hierarchies,
929 'Unable to delete data in FND_FLEX_VALUE_HIER_ALL table.');
930 END delete_value_hierarchies_all;
931
932
933 PROCEDURE delete_sec_rule_allowed_vals
934 --
935 -- Delete the old data.
936 --
937 IS
938 l_row_count NUMBER;
939 BEGIN
940 l_row_count := 1;
941 WHILE (l_row_count > 0) LOOP
942 DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
943 FROM fnd_flex_sec_rule_allowed_vals
944 WHERE flex_value_set_id = g_vset.flex_value_set_id and
945 rownum < 1000;
946 l_row_count := SQL%rowcount;
947 COMMIT;
948 END LOOP;
949 EXCEPTION
950 WHEN OTHERS THEN
951 raise_exception_error(error_delete_hierarchies,
952 'Unable to delete data in FND_FLEX_SEC_RULE_ALLOWED_VALS table.');
953 END delete_sec_rule_allowed_vals;
954
955
956
957
958
959 PROCEDURE update_value_hierarchies
960 --
961 -- Update the newly compiled data with the vset id.
962 --
963 IS
964 l_row_count NUMBER;
965 BEGIN
966 l_row_count := 1;
967 WHILE (l_row_count > 0) LOOP
968 UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
969 fnd_flex_value_hierarchies
970 SET flex_value_set_id = g_vset.flex_value_set_id
971 WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
972 rownum < 1000;
973 l_row_count := SQL%rowcount;
974 COMMIT;
975 END LOOP;
976 EXCEPTION
977 WHEN OTHERS THEN
978 raise_exception_error(error_update_hierarchies,
979 'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');
980 END update_value_hierarchies;
981
982 PROCEDURE update_value_hierarchies_all
983 --
984 -- Update the newly compiled data with the vset id.
985 --
986 IS
987 l_row_count NUMBER;
988 BEGIN
989 l_row_count := 1;
990 WHILE (l_row_count > 0) LOOP
991 UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
992 fnd_flex_value_hier_all
993 SET flex_value_set_id=g_vset.flex_value_set_id
994 WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
995 rownum < 1000;
996 l_row_count := SQL%rowcount;
997 COMMIT;
998 END LOOP;
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 raise_exception_error(error_update_hierarchies,
1002 'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');
1003 END update_value_hierarchies_all;
1004
1005
1006
1007 PROCEDURE update_sec_rule_allowed_vals
1008 --
1009 -- Update the newly compiled data with the vset id.
1010 --
1011 IS
1012 l_row_count NUMBER;
1013 BEGIN
1014 l_row_count := 1;
1015 WHILE (l_row_count > 0) LOOP
1016 UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
1017 fnd_flex_sec_rule_allowed_vals
1018 SET flex_value_set_id=g_vset.flex_value_set_id
1019 WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
1020 rownum < 1000;
1021 l_row_count := SQL%rowcount;
1022 COMMIT;
1023 END LOOP;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 raise_exception_error(error_update_hierarchies,
1027 'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');
1028 END update_sec_rule_allowed_vals;
1029
1030
1031 PROCEDURE compile_sec_rule_allowed_vals
1032 --
1033 -- Insert all the valid values based on the security rules
1034 --
1035 -- In this new logic created for better performance
1036 -- Only Character and Number Format Types are supported for
1037 -- Hier security. So we only need to worry about Number
1038 -- and Char comparisons. Other Format Types are supported
1039 -- with old logic. It is not feasible to support all
1040 -- format types with new logic.
1041 IS
1042 BEGIN
1043
1044 IF (g_vset.format_type ='N') THEN -- Number format type
1045 INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
1046 into fnd_flex_sec_rule_allowed_vals
1047 (FLEX_VALUE_SET_ID,
1048 FLEX_VALUE_RULE_ID,
1049 FLEX_VALUE,
1050 LAST_UPDATE_DATE,
1051 LAST_UPDATED_BY,
1052 CREATION_DATE,
1053 CREATED_BY)
1054 SELECT
1055 FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID*(-1),
1056 RS.FLEX_VALUE_RULE_ID,
1057 FND_FLEX_VALUES_VL.FLEX_VALUE,
1058 SYSDATE, 0, sysdate, 0
1059 FROM FND_FLEX_VALUES_VL, FND_FLEX_VALUE_RULES RS
1060 WHERE
1061 FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
1062 AND FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = RS.FLEX_VALUE_SET_ID
1063 AND FND_FLEX_VALUES_VL.ENABLED_FLAG = 'Y'
1064 AND NOT EXISTS
1065 (SELECT NULL FROM FND_FLEX_VALUE_RULE_LINES L, FND_FLEX_VALUE_RULES R
1066 WHERE L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
1067 AND L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID
1068 AND R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID
1069 AND L.INCLUDE_EXCLUDE_INDICATOR = 'E'
1070 AND fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE)
1071 BETWEEN
1072 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE))
1073 AND fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE))) AND
1074 NOT EXISTS (SELECT NULL FROM
1075 FND_FLEX_VALUE_HIER_ALL H,
1076 FND_FLEX_VALUE_RULE_LINES L,
1077 FND_FLEX_VALUE_RULES R
1078 WHERE
1079 L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1080 H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
1081 L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1082 R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1083 L.INCLUDE_EXCLUDE_INDICATOR = 'E' AND
1084 fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE)
1085 BETWEEN
1086 fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_LOW) AND
1087 fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_HIGH) AND
1088 fnd_number.canonical_to_number(H.PARENT_FLEX_VALUE)
1089 BETWEEN
1090 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, H.PARENT_FLEX_VALUE)) AND
1091 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE))) AND
1092 (NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_RULES R
1093 WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1094 R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1095 NOT EXISTS (SELECT NULL
1096 FROM FND_FLEX_VALUE_RULE_LINES L WHERE
1097 L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1098 L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1099 L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
1100 fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE)
1101 BETWEEN
1102 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE)) AND
1103 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE))))
1104 OR NOT EXISTS
1105 (SELECT NULL FROM FND_FLEX_VALUE_RULES R
1106 WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1107 R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1108 NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_HIER_ALL H, FND_FLEX_VALUE_RULE_LINES L
1109 WHERE H.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1110 H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
1111 L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1112 L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
1113 fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE) BETWEEN
1114 fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_LOW) AND
1115 fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_HIGH) AND
1116 fnd_number.canonical_to_number(H.PARENT_FLEX_VALUE) BETWEEN
1117 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, H.PARENT_FLEX_VALUE)) AND
1118 fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE)))))
1119 ORDER BY
1120 FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID,
1121 RS.FLEX_VALUE_RULE_ID,
1122 fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE);
1123
1124 ELSE -- Character format type (DATE TIME are not supported)
1125
1126 INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
1127 into fnd_flex_sec_rule_allowed_vals
1128 (FLEX_VALUE_SET_ID,
1129 FLEX_VALUE_RULE_ID,
1130 FLEX_VALUE,
1131 LAST_UPDATE_DATE,
1132 LAST_UPDATED_BY,
1133 CREATION_DATE,
1134 CREATED_BY)
1135 SELECT
1136 FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID*(-1),
1137 RS.FLEX_VALUE_RULE_ID,
1138 FND_FLEX_VALUES_VL.FLEX_VALUE,
1139 SYSDATE, 0, sysdate, 0
1140 FROM FND_FLEX_VALUES_VL, FND_FLEX_VALUE_RULES RS
1141 WHERE
1142 FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
1143 AND FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = RS.FLEX_VALUE_SET_ID
1144 AND FND_FLEX_VALUES_VL.ENABLED_FLAG = 'Y'
1145 AND NOT EXISTS
1146 (SELECT NULL FROM FND_FLEX_VALUE_RULE_LINES L, FND_FLEX_VALUE_RULES R
1147 WHERE L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
1148 AND L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID
1149 AND R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID
1150 AND L.INCLUDE_EXCLUDE_INDICATOR = 'E'
1151 AND FND_FLEX_VALUES_VL.FLEX_VALUE
1152 BETWEEN NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE)
1153 AND NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE)) AND
1154 NOT EXISTS (SELECT NULL FROM
1155 FND_FLEX_VALUE_HIER_ALL H,
1156 FND_FLEX_VALUE_RULE_LINES L,
1157 FND_FLEX_VALUE_RULES R
1158 WHERE
1159 L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1160 H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
1161 L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1162 R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1163 L.INCLUDE_EXCLUDE_INDICATOR = 'E' AND
1164 FND_FLEX_VALUES_VL.FLEX_VALUE
1165 BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH AND
1166 H.PARENT_FLEX_VALUE
1167 BETWEEN NVL(L.FLEX_VALUE_LOW, H.PARENT_FLEX_VALUE) AND
1168 NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE)) AND
1169 (NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_RULES R
1170 WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1171 R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1172 NOT EXISTS (SELECT NULL
1173 FROM FND_FLEX_VALUE_RULE_LINES L WHERE
1174 L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1175 L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1176 L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
1177 FND_FLEX_VALUES_VL.FLEX_VALUE BETWEEN NVL(L.FLEX_VALUE_LOW,
1178 FND_FLEX_VALUES_VL.FLEX_VALUE)
1179 AND NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE)))
1180 OR NOT EXISTS
1181 (SELECT NULL FROM FND_FLEX_VALUE_RULES R
1182 WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1183 R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1184 NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_HIER_ALL H, FND_FLEX_VALUE_RULE_LINES L
1185 WHERE H.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1186 H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
1187 L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1188 L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
1189 FND_FLEX_VALUES_VL.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND
1190 H.CHILD_FLEX_VALUE_HIGH
1191 AND H.PARENT_FLEX_VALUE BETWEEN NVL(L.FLEX_VALUE_LOW,
1192 H.PARENT_FLEX_VALUE)
1193 AND NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE))))
1194 ORDER BY
1195 FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID,
1196 RS.FLEX_VALUE_RULE_ID,
1197 FND_FLEX_VALUES_VL.FLEX_VALUE;
1198
1199 END IF;
1200
1201 COMMIT;
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 raise_exception_error(error_update_hierarchies,
1208
1205 'Unable to insert data in FND_FLEX_SEC_RULE_ALLOWED_VALS table.');
1206 END compile_sec_rule_allowed_vals;
1207
1209
1210 PROCEDURE request_lock(p_lock_name IN VARCHAR2,
1211 x_lock_handle OUT nocopy VARCHAR2)
1212 IS
1213 l_lock_name VARCHAR2(128);
1214 l_lock_handle VARCHAR2(128);
1215 BEGIN
1216 l_lock_name := 'FND.FLEX.VSET.HIERARCHY_COMPILER.' || p_lock_name;
1217 l_lock_handle := null;
1218 fnd_flex_server.request_lock(l_lock_name, l_lock_handle);
1219 x_lock_handle := l_lock_handle;
1220 END request_lock;
1221
1222
1223 PROCEDURE release_lock(p_lock_name IN VARCHAR2,
1224 p_lock_handle IN VARCHAR2)
1225 IS
1226 BEGIN
1227 IF (p_lock_handle IS NOT NULL) THEN
1228 fnd_flex_server.release_lock(p_lock_name, p_lock_handle);
1229 END IF;
1230 END release_lock;
1231
1232
1233 PROCEDURE finish_hierarchy_processing
1234 IS
1235 l_lock_name VARCHAR2(128);
1236 l_lock_handle VARCHAR2(128);
1237 BEGIN
1238
1239 /*
1240 This lock is to stop value validation (per valueset)
1241 while data is being processed. We do not want to
1242 allow values to be validated from a FF or
1243 anyother place until the values are processed.
1244 */
1245 l_lock_name := g_vset.flex_value_set_name;
1246 request_lock(l_lock_name, l_lock_handle);
1247
1248 delete_value_hierarchies();
1249 update_value_hierarchies();
1250 delete_value_hierarchies_all();
1251 update_value_hierarchies_all();
1252
1253 delete_sec_rule_allowed_vals();
1254 update_sec_rule_allowed_vals();
1255
1256 /*
1257 Release the lock once the process of normalizing
1258 the valuset data is complete.
1259 */
1260 release_lock(l_lock_name, l_lock_handle);
1261
1262 COMMIT;
1263
1264 END finish_hierarchy_processing;
1265
1266 -- ======================================================================
1267 -- PROCEDURE : compile_hierarchy
1268 -- ======================================================================
1269 -- Compiles the flex value hierarchy.
1270 --
1271 PROCEDURE compile_hierarchy(p_flex_value_set IN VARCHAR2,
1272 p_debug_flag IN VARCHAR2 DEFAULT 'N',
1273 x_result OUT nocopy VARCHAR2,
1274 x_message OUT nocopy VARCHAR2)
1275 IS
1276 l_message VARCHAR2(32000) := NULL;
1277 l_bes_message VARCHAR2(32000) := NULL;
1278 l_lock_name VARCHAR2(128);
1279 l_lock_handle VARCHAR2(128);
1280 BEGIN
1281 --
1282 -- Set the debug flag
1283 --
1284 set_debug(p_debug_flag);
1285
1286 --
1287 -- Set the global USER_ID variable.
1288 --
1289 BEGIN
1290 g_user_id := fnd_global.user_id;
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 g_user_id := -1;
1294 END;
1295
1296 --
1297 -- Set the global g_vset variable.
1298 --
1299 get_vset(p_flex_value_set, g_vset);
1300
1301 --
1302 -- Debug
1303 --
1304 IF (g_debug_on) THEN
1305 debug('Compiling Hierarchy for Value Set');
1306 debug(' Name : ' || g_vset.flex_value_set_name);
1307 debug(' Id : ' || g_vset.flex_value_set_id);
1308 debug('Security Flag : ' || g_vset.security_enabled_flag);
1309 debug(' ');
1310 debug('Flattened hierarchies : ');
1311 debug(Rpad('=', 80, '='));
1312 END IF;
1313
1314 /*
1315 Request a lock at the beginning of this program so that no
1316 other instance of this program (updating the same valueset)
1317 can overwrite each other. Since the lock_name includes the
1318 valuset name, only other program instances updating the same
1319 valueset will be locked out. If another pogram instance is
1320 updating a different valueset, then there is no problem.
1321 */
1322 l_lock_name := 'FNDFFCHY.' || g_vset.flex_value_set_name;
1323 request_lock(l_lock_name, l_lock_handle);
1324
1325 --
1326 -- Compile semi-denormalized hierarchies.
1327 --
1328 compile_value_hierarchies();
1329
1330 --
1331 -- Compile fully denormalized hierarchies.
1332 --
1333 compile_value_hierarchies_all();
1334
1335 --
1336 -- Compile all valid values based on hier security rules.
1337 --
1338 -- This new function compile_sec_rule_allowed_vals()
1339 -- was created for better performance.
1340 -- Only Char and Number Format Types are supported for
1341 -- Independent and Table Validatin Types.
1342 -- Date/Time are rarely used if ever.
1343 IF ((g_vset.security_enabled_flag = 'H') AND
1344 (g_vset.validation_type = 'I' OR g_vset.validation_type = 'F' ) AND
1345 (g_vset.format_type = 'C' OR g_vset.format_type = 'N')) THEN
1346 compile_sec_rule_allowed_vals();
1347 END IF;
1348
1349 /******************************************************************
1350 Bug 3947152 The code was deleting all the hierarchy rules and then
1351 reinserting them in a normalized state. Hierarchy value rules
1352 are checked by security. The problem is that when
1353 all the hier rules are deleted and before they are reinserted there
1354 is a time frame where there is a security breach. Values that
1355 should be secured, will not be sec in that time frame. We recoded
1356 the logic so that we first insert the hierachy rules with a
1357 vsetid*(-1). Once done with the insert we create a lock so
1358 that no one can access the data, and then we delete the rows
1362 lock is released. At this time the only code that is reading the
1359 with orig vsetid and then we update the vsetid*(-1) with the
1360 orig vsetid. After that is done, we release the lock. If another
1361 process wants to read the hier security data it cannot until the
1363 hier rules data is fnd_flex_server.check_value_security and
1364 this is called in FND_FLEX_SERVER1.check_security. In the function
1365 check_security() we check to see if a lock exists and if there is a
1366 lock we do not process until the lock is released meaning the data
1367 is now updated and correct.
1368 *******************************************************************/
1369 --
1370 -- Finish hierarchy processing.
1371 --
1372 finish_hierarchy_processing();
1373
1374 --
1375 -- SUCCESS message.
1376 --
1377 BEGIN
1378 fnd_message.set_name('FND', 'FLEX-HIERARCHY COMP DONE');
1379 fnd_message.set_token('VSID', (To_char(g_vset.flex_value_set_id) ||
1380 '/''' ||
1381 g_vset.flex_value_set_name || ''''));
1382 l_message := fnd_message.get;
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 l_message := ('The value hierarchy associated with ' ||
1386 'value set ' || g_vset.flex_value_set_id ||
1387 '/''' || g_vset.flex_value_set_name ||
1388 ''' has been compiled successfully.');
1389 END;
1390
1391 -- Raise BES Event: oracle.apps.fnd.flex.vst.hierarchy.compiled
1392
1393 DECLARE
1394 l_parameters wf_parameter_list_t := wf_parameter_list_t();
1395 BEGIN
1396 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_ID',
1397 p_value => g_vset.flex_value_set_id,
1398 p_parameterlist => l_parameters);
1399
1400 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_NAME',
1401 p_value => g_vset.flex_value_set_name,
1402 p_parameterlist => l_parameters);
1403
1404 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.vst.hierarchy.compiled',
1405 p_event_key => g_vset.flex_value_set_name,
1406 p_event_data => NULL,
1407 p_parameters => l_parameters,
1408 p_send_date => Sysdate);
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 l_bes_message := 'Workflow: Business Event System raised exception.' ||
1412 g_newline || dbms_utility.format_error_stack();
1413 END;
1414
1415 /*
1416 Release the lock of this program instance.
1417 */
1418 release_lock(l_lock_name, l_lock_handle);
1419
1420 IF (l_bes_message IS NULL) THEN
1421 --
1422 -- Return SUCCESS
1423 --
1424 x_result := 'SUCCESS';
1425 ELSE
1426 --
1427 -- Return WARNING
1428 --
1429 x_result := 'WARNING';
1430 l_message := l_message || g_newline || g_newline || l_bes_message;
1431 END IF;
1432
1433 x_message := Substr(l_message, 1, g_message_size);
1434
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 BEGIN
1438
1439 release_lock(l_lock_name, l_lock_handle);
1440
1441 raise_others_error('compile_hierarchy',
1442 p_flex_value_set,
1443 p_debug_flag,
1444 g_vset.flex_value_set_id,
1445 g_vset.flex_value_set_name);
1446 EXCEPTION
1447 WHEN OTHERS THEN
1448 --
1449 -- Present the root cause of the problem
1450 --
1451 l_message := g_error_message || g_newline;
1452
1453 --
1454 -- Add the error message stack
1455 --
1456 l_message := l_message || '----- Error Message Stack -----' || g_newline;
1457 l_message := l_message || dbms_utility.format_error_stack();
1458
1459 --
1460 -- Return FAILURE
1461 --
1462 x_result := 'FAILURE';
1463 x_message := Substr(l_message, 1, g_message_size);
1464
1465 RETURN;
1466 END;
1467 END compile_hierarchy;
1468
1469 -- ======================================================================
1470 PROCEDURE compile_hierarchy(p_flex_value_set IN VARCHAR2,
1471 x_result OUT nocopy VARCHAR2,
1472 x_message OUT nocopy VARCHAR2)
1473 IS
1474 BEGIN
1475 compile_hierarchy(p_flex_value_set, 'N', x_result, x_message);
1476 END compile_hierarchy;
1477
1478
1479 PROCEDURE compile_hierarchy_all(p_flex_value_set IN VARCHAR2,
1480 p_debug_flag IN VARCHAR2 DEFAULT 'N',
1481 x_result OUT nocopy VARCHAR2,
1482 x_message OUT nocopy VARCHAR2)
1483
1484 IS
1485 ----------------------
1486 -- Local definitions -
1487 ----------------------
1488 l_value_set_id_sql VARCHAR2(1000);
1489 l_value_set_id NUMBER;
1490 l_result VARCHAR2(10);
1491 l_message VARCHAR2(2000);
1492 TYPE cursor_type IS REF CURSOR;
1493 l_value_set_id_cur cursor_type;
1494
1495
1496 BEGIN
1497
1498 l_value_set_id_sql :=
1499 ('SELECT /* Header: AFFFSV2B.pls 120.2.12000000.1 2007/01/18 13:18:43 appldev ship $ */ ' ||
1500 ' v.flex_value_set_id ' ||
1501 ' FROM fnd_flex_value_sets v ' ||
1502 ' WHERE EXISTS ' ||
1503 ' (SELECT null ' ||
1504 ' FROM fnd_flex_value_norm_hierarchy h ' ||
1505 ' WHERE h.flex_value_set_id = v.flex_value_set_id)');
1506
1507
1508 OPEN l_value_set_id_cur FOR l_value_set_id_sql;
1509
1510 LOOP
1511 FETCH l_value_set_id_cur INTO l_value_set_id;
1512 EXIT WHEN l_value_set_id_cur%NOTFOUND;
1513 compile_hierarchy(l_value_set_id, l_result, l_message);
1514
1515 IF (l_result <> 'SUCCESS') THEN
1516 x_result := l_result;
1517 x_message := l_message;
1518 RETURN;
1519 END IF;
1520
1521
1522 --
1523 -- Debug
1524 --
1525 IF (g_debug_on) THEN
1526 debug('Compiling Hierarchy for Value Set');
1527 debug(' Name : ' || g_vset.flex_value_set_name);
1528 debug(' Id : ' || g_vset.flex_value_set_id);
1529 debug('Security Flag : ' || g_vset.security_enabled_flag);
1530 debug(' ');
1531 debug('Compile Value Set Hierarchy ALL : ');
1532 debug(Rpad('=', 80, '='));
1533 END IF;
1534
1535 END LOOP;
1536
1537 CLOSE l_value_set_id_cur;
1538
1539 END compile_hierarchy_all;
1540
1541
1542
1543 BEGIN
1544 g_newline := fnd_global.newline();
1545 END fnd_flex_hierarchy_compiler;
[Home] [Help]