[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_HIERARCHY_COMPILER
Source
1 PACKAGE BODY fnd_flex_hierarchy_compiler AS
2 /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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.2.12010000.1 2008/07/25 14:13:37 appldev 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 update_value_hierarchies
934 --
935 -- Update the newly compiled data with the vset id.
936 --
937 IS
938 l_row_count NUMBER;
939 BEGIN
940 l_row_count := 1;
941 WHILE (l_row_count > 0) LOOP
942 UPDATE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
943 fnd_flex_value_hierarchies
944 SET flex_value_set_id = g_vset.flex_value_set_id
945 WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
946 rownum < 1000;
947 l_row_count := SQL%rowcount;
948 COMMIT;
949 END LOOP;
950 EXCEPTION
951 WHEN OTHERS THEN
952 raise_exception_error(error_update_hierarchies,
953 'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');
954 END update_value_hierarchies;
955
956 PROCEDURE update_value_hierarchies_all
957 --
958 -- Update the newly compiled data with the vset id.
959 --
960 IS
961 l_row_count NUMBER;
962 BEGIN
963 l_row_count := 1;
964 WHILE (l_row_count > 0) LOOP
965 UPDATE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
966 fnd_flex_value_hier_all
967 SET flex_value_set_id=g_vset.flex_value_set_id
968 WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
969 rownum < 1000;
970 l_row_count := SQL%rowcount;
971 COMMIT;
972 END LOOP;
973 EXCEPTION
974 WHEN OTHERS THEN
975 raise_exception_error(error_update_hierarchies,
976 'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');
977 END update_value_hierarchies_all;
978
979 PROCEDURE request_lock(p_lock_name IN VARCHAR2,
980 x_lock_handle OUT nocopy VARCHAR2)
981 IS
982 l_lock_name VARCHAR2(128);
983 l_lock_handle VARCHAR2(128);
984 BEGIN
985 l_lock_name := 'FND.FLEX.VSET.HIERARCHY_COMPILER.' || p_lock_name;
986 l_lock_handle := null;
987 fnd_flex_server.request_lock(l_lock_name, l_lock_handle);
988 x_lock_handle := l_lock_handle;
989 END request_lock;
990
991
992 PROCEDURE release_lock(p_lock_name IN VARCHAR2,
993 p_lock_handle IN VARCHAR2)
994 IS
995 BEGIN
996 IF (p_lock_handle IS NOT NULL) THEN
997 fnd_flex_server.release_lock(p_lock_name, p_lock_handle);
998 END IF;
999 END release_lock;
1000
1001
1002 PROCEDURE finish_hierarchy_processing
1003 IS
1004 l_lock_name VARCHAR2(128);
1005 l_lock_handle VARCHAR2(128);
1006 BEGIN
1007
1008 /*
1009 This lock is to stop value validation (per valueset)
1010 while data is being processed. We do not want to
1011 allow values to be validated from a FF or
1012 anyother place until the values are processed.
1013 */
1014 l_lock_name := g_vset.flex_value_set_name;
1015 request_lock(l_lock_name, l_lock_handle);
1016
1017 delete_value_hierarchies();
1018 update_value_hierarchies();
1019 delete_value_hierarchies_all();
1020 update_value_hierarchies_all();
1021
1022 /*
1023 Release the lock once the process of normalizing
1024 the valuset data is complete.
1025 */
1026 release_lock(l_lock_name, l_lock_handle);
1027
1028 COMMIT;
1029
1030 END finish_hierarchy_processing;
1031
1032 -- ======================================================================
1033 -- PROCEDURE : compile_hierarchy
1034 -- ======================================================================
1035 -- Compiles the flex value hierarchy.
1036 --
1037 PROCEDURE compile_hierarchy(p_flex_value_set IN VARCHAR2,
1038 p_debug_flag IN VARCHAR2 DEFAULT 'N',
1039 x_result OUT nocopy VARCHAR2,
1040 x_message OUT nocopy VARCHAR2)
1041 IS
1042 l_message VARCHAR2(32000) := NULL;
1043 l_bes_message VARCHAR2(32000) := NULL;
1044 l_lock_name VARCHAR2(128);
1045 l_lock_handle VARCHAR2(128);
1046 BEGIN
1047 --
1048 -- Set the debug flag
1049 --
1050 set_debug(p_debug_flag);
1051
1052 --
1053 -- Set the global USER_ID variable.
1054 --
1055 BEGIN
1056 g_user_id := fnd_global.user_id;
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 g_user_id := -1;
1060 END;
1061
1062 --
1063 -- Set the global g_vset variable.
1064 --
1065 get_vset(p_flex_value_set, g_vset);
1066
1067 --
1068 -- Debug
1069 --
1070 IF (g_debug_on) THEN
1071 debug('Compiling Hierarchy for Value Set');
1072 debug(' Name : ' || g_vset.flex_value_set_name);
1073 debug(' Id : ' || g_vset.flex_value_set_id);
1074 debug('Security Flag : ' || g_vset.security_enabled_flag);
1075 debug(' ');
1076 debug('Flattened hierarchies : ');
1077 debug(Rpad('=', 80, '='));
1078 END IF;
1079
1080 /*
1081 Request a lock at the beginning of this program so that no
1082 other instance of this program (updating the same valueset)
1083 can overwrite each other. Since the lock_name includes the
1084 valuset name, only other program instances updating the same
1085 valueset will be locked out. If another pogram instance is
1086 updating a different valueset, then there is no problem.
1087 */
1088 l_lock_name := 'FNDFFCHY.' || g_vset.flex_value_set_name;
1089 request_lock(l_lock_name, l_lock_handle);
1090
1091 --
1092 -- Compile semi-denormalized hierarchies.
1093 --
1094 compile_value_hierarchies();
1095
1096 --
1097 -- Compile fully denormalized hierarchies.
1098 --
1099 compile_value_hierarchies_all();
1100
1101 /******************************************************************
1102 Bug 3947152 The code was deleting all the hierarchy rules and then
1103 reinserting them in a normalized state. Hierarchy value rules
1104 are checked by security. The problem is that when
1105 all the hier rules are deleted and before they are reinserted there
1106 is a time frame where there is a security breach. Values that
1107 should be secured, will not be sec in that time frame. We recoded
1108 the logic so that we first insert the hierachy rules with a
1109 vsetid*(-1). Once done with the insert we create a lock so
1110 that no one can access the data, and then we delete the rows
1111 with orig vsetid and then we update the vsetid*(-1) with the
1112 orig vsetid. After that is done, we release the lock. If another
1113 process wants to read the hier security data it cannot until the
1114 lock is released. At this time the only code that is reading the
1115 hier rules data is fnd_flex_server.check_value_security and
1116 this is called in FND_FLEX_SERVER1.check_security. In the function
1117 check_security() we check to see if a lock exists and if there is a
1118 lock we do not process until the lock is released meaning the data
1119 is now updated and correct.
1120 *******************************************************************/
1121 --
1122 -- Finish hierarchy processing.
1123 --
1124 finish_hierarchy_processing();
1125
1126 --
1127 -- SUCCESS message.
1128 --
1129 BEGIN
1130 fnd_message.set_name('FND', 'FLEX-HIERARCHY COMP DONE');
1131 fnd_message.set_token('VSID', (To_char(g_vset.flex_value_set_id) ||
1132 '/''' ||
1133 g_vset.flex_value_set_name || ''''));
1134 l_message := fnd_message.get;
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 l_message := ('The value hierarchy associated with ' ||
1138 'value set ' || g_vset.flex_value_set_id ||
1139 '/''' || g_vset.flex_value_set_name ||
1140 ''' has been compiled successfully.');
1141 END;
1142
1143 -- Raise BES Event: oracle.apps.fnd.flex.vst.hierarchy.compiled
1144
1145 DECLARE
1146 l_parameters wf_parameter_list_t := wf_parameter_list_t();
1147 BEGIN
1148 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_ID',
1149 p_value => g_vset.flex_value_set_id,
1150 p_parameterlist => l_parameters);
1151
1152 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_NAME',
1153 p_value => g_vset.flex_value_set_name,
1154 p_parameterlist => l_parameters);
1155
1156 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.vst.hierarchy.compiled',
1157 p_event_key => g_vset.flex_value_set_name,
1158 p_event_data => NULL,
1159 p_parameters => l_parameters,
1160 p_send_date => Sysdate);
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 l_bes_message := 'Workflow: Business Event System raised exception.' ||
1164 g_newline || dbms_utility.format_error_stack();
1165 END;
1166
1167 /*
1168 Release the lock of this program instance.
1169 */
1170 release_lock(l_lock_name, l_lock_handle);
1171
1172 IF (l_bes_message IS NULL) THEN
1173 --
1174 -- Return SUCCESS
1175 --
1176 x_result := 'SUCCESS';
1177 ELSE
1178 --
1179 -- Return WARNING
1180 --
1181 x_result := 'WARNING';
1182 l_message := l_message || g_newline || g_newline || l_bes_message;
1183 END IF;
1184
1185 x_message := Substr(l_message, 1, g_message_size);
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 BEGIN
1190
1191 release_lock(l_lock_name, l_lock_handle);
1192
1193 raise_others_error('compile_hierarchy',
1194 p_flex_value_set,
1195 p_debug_flag,
1196 g_vset.flex_value_set_id,
1197 g_vset.flex_value_set_name);
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 --
1201 -- Present the root cause of the problem
1202 --
1203 l_message := g_error_message || g_newline;
1204
1205 --
1206 -- Add the error message stack
1207 --
1208 l_message := l_message || '----- Error Message Stack -----' || g_newline;
1209 l_message := l_message || dbms_utility.format_error_stack();
1210
1211 --
1212 -- Return FAILURE
1213 --
1214 x_result := 'FAILURE';
1215 x_message := Substr(l_message, 1, g_message_size);
1216
1217 RETURN;
1218 END;
1219 END compile_hierarchy;
1220
1221 -- ======================================================================
1222 PROCEDURE compile_hierarchy(p_flex_value_set IN VARCHAR2,
1223 x_result OUT nocopy VARCHAR2,
1224 x_message OUT nocopy VARCHAR2)
1225 IS
1226 BEGIN
1227 compile_hierarchy(p_flex_value_set, 'N', x_result, x_message);
1228 END compile_hierarchy;
1229
1230
1231 BEGIN
1232 g_newline := fnd_global.newline();
1233 END fnd_flex_hierarchy_compiler;