1 PACKAGE BODY ENI_PROD_VALUESET AS
2 /* $Header: ENIVSTPB.pls 120.2 2006/03/16 06:36:17 pfarkade noship $ */
3
4 g_catset_id NUMBER;
5
6 PROCEDURE UPDATE_VALUESET_FROM_CATEGORY
7 (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
8
9 l_struct_id NUMBER; -- structure id of default category set associated with Product reporting functional area
10 l_flex_val_set_id NUMBER; -- flex value set id, where hierarchy has to be loaded
11
12 -- Cursor that creates the hierarchy with parent-child relationship
13 CURSOR hierarchy IS
14 SELECT
15 V.CONCATENATED_SEGMENTS PARENT_CODE,
16 V1.CONCATENATED_SEGMENTS CHILD_CODE,
17 DECODE(F.SUMMARY_FLAG, 'Y', 'P', 'C') RANGE_ATTRIBUTE
18 FROM MTL_CATEGORY_SET_VALID_CATS T, MTL_CATEGORIES_KFV V, MTL_CATEGORIES_KFV V1, FND_FLEX_VALUES F
19 WHERE T.CATEGORY_SET_ID = g_catset_id
20 AND T.CATEGORY_ID = V1.CATEGORY_ID
21 AND T.PARENT_CATEGORY_ID = V.CATEGORY_ID
22 AND V1.CONCATENATED_SEGMENTS = F.FLEX_VALUE
23 AND F.FLEX_VALUE_SET_ID = l_flex_val_set_id
24 AND NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_NORM_HIERARCHY H
25 WHERE FLEX_VALUE_SET_ID = F.FLEX_VALUE_SET_ID
26 AND PARENT_FLEX_VALUE = V.CONCATENATED_SEGMENTS
27 AND RANGE_ATTRIBUTE = DECODE(F.SUMMARY_FLAG, 'Y', 'P', 'C')
28 AND CHILD_FLEX_VALUE_LOW = V1.CONCATENATED_SEGMENTS
29 AND CHILD_FLEX_VALUE_HIGH = V1.CONCATENATED_SEGMENTS);
30
31 -- Cursor that creates the hierarchy under the top node, if the
32 -- top node is specifid in the UI
33 CURSOR c_hierarchy_top_node(l_top_node VARCHAR2) IS
34 select l_top_node parent_code,
35 concatenated_segments child_code,
36 decode(f.summary_flag,'Y','P','C') range_attribute
37 from mtl_category_set_valid_cats a,
38 mtl_categories_kfv b, fnd_flex_values f
39 where parent_category_id is null
40 and category_set_id = g_catset_id
41 and a.category_id = b.category_id
42 and b.structure_id = l_struct_id
43 and b.concatenated_segments = f.flex_value
44 and f.flex_value_set_id = l_flex_val_set_id
45 and l_top_node is not null
46 and not exists( select 'X' from fnd_flex_value_norm_hierarchy h
47 where flex_value_set_id = f.flex_value_set_id
48 and parent_flex_value = l_top_node
49 and range_attribute= decode(f.summary_flag,'Y','P','C')
50 and child_flex_value_low = b.concatenated_segments
51 and child_flex_value_high = b.concatenated_segments);
52
53
54 -- Cursor to check for loops in the hierarchy
55 -- The following query has two parts.In the first part, it will
56 -- retrieve all the categories that exist in valid cats.
57 -- In the second part, it will start from the top node and
58 -- traverse up the hierarchy and get all the parents, grandparents
59 -- This is to ensure that a node doesn't get created in the
60 -- value set with a circular reference
61
62 CURSOR c_hierarchy_loop(l_top_node VARCHAR2) IS
63 SELECT b.concatenated_segments nodes
64 FROM mtl_category_set_valid_cats a, mtl_categories_kfv b
65 WHERE a.category_set_id = g_catset_id
66 AND a.category_id = b.category_id
67 AND b.structure_id = l_struct_id
68 INTERSECT
69 SELECT child_code nodes
70 FROM eni_vset_hrchy_temp
71 WHERE hrchy_flag = 'P'
72 -- AND parent_code is not null
73 START with child_code = l_top_node
74 CONNECT BY child_code = prior parent_code;
75
76
77 TYPE ref_cursor IS REF CURSOR;
78 new_values_cursor ref_cursor;
79 existing_values_cursor ref_cursor;
80
81 l_sql_stmt VARCHAR2(32000);
82 l_value_set_name VARCHAR2(1000);
83 l_msg VARCHAR2(2000);
84 l_top_node VARCHAR2(150);
85 l_summary_flag VARCHAR2(1);
86 l_enabled_flag VARCHAR2(1);
87
88 l_flex_value_id NUMBER;
89 l_flex_value FND_FLEX_VALUES.FLEX_VALUE%TYPE;
90 l_new_enabled_flag VARCHAR2(1);
91 l_new_description FND_FLEX_VALUES_TL.DESCRIPTION%TYPE;
92 l_new_start_date DATE;
93 l_new_end_date DATE;
94 l_old_enabled_flag VARCHAR2(1);
95 l_old_start_date DATE;
96 l_old_end_date DATE;
97 l_old_summary_flag VARCHAR2(1);
98 l_old_description FND_FLEX_VALUES_TL.DESCRIPTION%TYPE;
99 l_new_summary_flag VARCHAR2(1);
100 l_schema VARCHAR2(10) := 'ENI';
101
102 table_not_found EXCEPTION;
103 PRAGMA EXCEPTION_INIT(table_not_found, -00942);
104 l_count NUMBER := 0;
105 l_compile BOOLEAN := FALSE;
106
107 BEGIN
108 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start of Loading Product Catalog Hierarchy into Value set');
109 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
110 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting associated Structure...');
111
112 BEGIN
113
114 g_catset_id := ENI_DENORM_HRCHY.get_category_set_id;
115
116 SELECT STRUCTURE_ID INTO l_struct_id
117 FROM MTL_CATEGORY_SETS_B
118 WHERE CATEGORY_SET_ID = g_catset_id;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Product Catalog Not Found');
122 errbuf := 'ERROR: Product Catalog Not Found';
123 retcode := 2;
124 RAISE;
125 END;
126
127 -- CHeck if structure has only one segment enabled
128 BEGIN
129 Select segment_num into l_count
130 from fnd_id_flex_segments
131 where id_flex_num = l_struct_id
132 and enabled_flag = 'Y';
133
134 EXCEPTION
135 WHEN TOO_MANY_ROWS THEN
136 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: Structure of default category set can have only one segment enabled. More than one segment is not supported');
137 errbuf := 'ERROR: Structure of default category set can have only one segment enabled. More than one segment is not supported';
138 retcode := 2;
139 RAISE;
140 WHEN NO_DATA_FOUND THEN
141 null;
142 END;
143
144 -- Get top node --
145
146 BEGIN
147
148 -- Selecting the flex_value_set_name from the flex_value_set_id.
149 -- Flex value set name is needed to pass as a parameter while
150 -- calling the FND packages
151
152 select a.flex_value_set_id, a.top_node, b.flex_value_set_name
153 INTO l_flex_val_set_id, l_top_node, l_value_set_name
154 FROM ego_financial_reporting_agv a, fnd_flex_value_sets b
155 WHERE a.category_set_id = g_catset_id
156 AND a.flex_value_set_id = b.flex_value_set_id
157 AND rownum = 1;
158
159 if l_top_node is not null then
160 BEGIN
161
162 -- Ego view only stores the flex_value_id of the top node.
163 -- Getting the top node name from the id
164
165 SELECT flex_value INTO l_top_node
166 FROM fnd_flex_values
167 WHERE flex_value_set_id = l_flex_val_set_id
168 AND flex_value_id = l_top_node;
169
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 errbuf := 'Please enter a value set before running this program';
173 retcode := 2;
174 RAISE_APPLICATION_ERROR(-20009, 'ERROR: Value set cannot be null');
175
176 END;
177
178 end if;
179
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 errbuf := 'Please enter a value set before running this program';
183 retcode := 2;
184 RAISE_APPLICATION_ERROR(-20009, 'ERROR: Value set cannot be null');
185 END;
186
187
188 -- Storing into the temporary table. This is done so that start-with
189 -- connect-by clause can be used when a top node is selected in the UI.
190 -- When the top node is selected we should only propagate the catalog
191 -- hierarchy to under the top node
192
193 -- The hrchy_flag is set to "P" which related to "propagation"
194 -- Once we figure out if the top node is selected or not, the flag
195 -- is set to "Y". All transactions following that would look at
196 -- hrchy_flag = "Y"
197
198 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ENI_VSET_HRCHY_TEMP';
199
200 INSERT INTO ENI_VSET_HRCHY_TEMP(
201 CHILD_CODE,
202 PARENT_CODE,
203 SUMMARY_FLAG,
204 child_value_id,
205 ENABLED_FLAG,
206 START_DATE_ACTIVE,
207 END_DATE_ACTIVE,
208 HRCHY_FLAG)
209 SELECT
210 a.FLEX_VALUE CHILD_CODE,
211 PARENT_FLEX_VALUE PARENT_CODE,
212 a.SUMMARY_FLAG,
213 b.flex_value_id,
214 b.ENABLED_FLAG,
215 b.START_DATE_ACTIVE,
216 b.END_DATE_ACTIVE,
217 'P'
218 FROM FND_FLEX_VALUE_CHILDREN_V a, fnd_flex_values b
219 WHERE a.FLEX_VALUE_SET_ID = l_flex_val_set_id
220 and a.flex_value_set_id = b.flex_value_set_id
221 and a.flex_value = b.flex_value
222 UNION
223 SELECT FLEX_VALUE,
224 null,
225 SUMMARY_FLAG,
226 flex_value_id,
227 ENABLED_FLAG,
228 START_DATE_ACTIVE,
229 END_DATE_ACTIVE,
230 'P'
231 FROM FND_FLEX_VALUES A
232 WHERE flex_value_set_id = l_flex_val_set_id
233 AND not exists (Select flex_value
234 from fnd_flex_value_children_v
235 where flex_value_set_id = a.flex_value_set_id
236 and flex_value = a.flex_value);
237
238 commit;
239
240 l_count := 0;
241 if l_top_node is not null then
242
243 -- First check if the hierarchy will create a loop in the
244 -- value set
245
246 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
247 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: The following nodes already exist as parent of the top node in the value set.');
248 FND_FILE.PUT_LINE(FND_FILE.LOG,'These nodes cannot be created as child of the top node');
249 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FLEX VALUES ');
250 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------');
251
252 FOR i IN c_hierarchy_loop(l_top_node) LOOP
253 FND_FILE.PUT_LINE(FND_FILE.LOG, i.nodes);
254 l_count := 1;
255 END LOOP;
256
257 IF l_count = 0 THEN
258 FND_FILE.PUT_LINE(FND_FILE.LOG,'-- None --');
259 ELSE
260 errbuf := 'ERROR: Circular reference in the value set is not allowed';
261 retcode := 2;
262 RAISE_APPLICATION_ERROR(-20009, 'ERROR: Data error');
263 END IF;
264
265
266 -- populating the hierarchy from only the top node down
267 -- into the temp table
268
269 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Populating hierarchy in temp table under the top node');
270
271 INSERT INTO ENI_VSET_HRCHY_TEMP (
272 CHILD_CODE,
273 PARENT_CODE,
274 SUMMARY_FLAG,
275 CHILD_VALUE_ID,
276 ENABLED_FLAG,
277 START_DATE_ACTIVE,
278 END_DATE_ACTIVE,
279 HRCHY_FLAG)
280 SELECT CHILD_CODE,
281 PARENT_CODE,
282 summary_flag,
283 child_value_id,
284 ENABLED_FLAG,
285 START_DATE_ACTIVE,
286 END_DATE_ACTIVE,
287 'Y'
288 FROM ENI_VSET_HRCHY_TEMP H
289 CONNECT BY PRIOR CHILD_CODE = PARENT_CODE
290 START WITH CHILD_CODE = l_top_node;
291
292 else
293
294
295 -- if top node is not null, change the hrchy_flag to "Y"
296 -- this is so that with or without the top node, we can look
297 -- at the same where clause
298
299 UPDATE eni_vset_hrchy_temp
300 SET hrchy_flag = 'Y'
301 WHERE hrchy_flag = 'P';
302 end if;
303
304
305 -- the following nodes do not exist in the hierarchy, so will be
306 -- inserted into the value set
307
308 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
309 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEW NODES: New values that will be inserted into the value set');
310 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FLEX VALUES ');
311 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------');
312
313
314 l_sql_stmt := '
315 SELECT
316 V.CONCATENATED_SEGMENTS ,
317 V.ENABLED_FLAG,
318 T.DESCRIPTION,
319 V.START_DATE_ACTIVE,
320 V.DISABLE_DATE,
321 NVL((SELECT ''Y'' FROM MTL_CATEGORY_SET_VALID_CATS C
322 WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
323 AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
324 AND ROWNUM = 1), ''N'') SUMMARY_FLAG
325 FROM MTL_CATEGORIES_KFV V,
326 MTL_CATEGORIES_TL T,
327 MTL_CATEGORY_SET_VALID_CATS H
328 WHERE V.STRUCTURE_ID = :l_struct_id
329 AND V.CATEGORY_ID = T.CATEGORY_ID
330 AND T.LANGUAGE = USERENV(''LANG'')
331 AND V.CATEGORY_ID = H.CATEGORY_ID
332 AND H.CATEGORY_SET_ID = :g_catset_id
333 AND NOT EXISTS
334 (SELECT NULL FROM FND_FLEX_VALUES F
335 WHERE F.FLEX_VALUE = V.CONCATENATED_SEGMENTS
336 AND F.FLEX_VALUE_SET_ID = :l_flex_val_set_id)';
337
338
339 l_count := 0;
340 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Opening Cursor to Insert new values');
341 -- INSERTING NEW VALUES INTO FND_FLEX_VALUES TABLE
342 OPEN new_values_cursor FOR l_sql_stmt USING l_struct_id, g_catset_id, l_flex_val_set_id;
343
344 LOOP
345 FETCH new_values_cursor INTO
346 l_flex_value,
347 l_new_enabled_flag,
348 l_new_description,
349 l_new_start_date,
350 l_new_end_date,
351 l_new_summary_flag;
352
353 EXIT WHEN new_values_cursor%NOTFOUND;
354
355 BEGIN
356 FND_FLEX_VAL_API.CREATE_INDEPENDENT_VSET_VALUE
357 (p_flex_value_set_name => l_value_set_name,
358 p_flex_value => l_flex_value,
359 p_description => l_new_description,
360 p_enabled_flag => l_new_enabled_flag,
361 p_summary_flag => l_new_summary_flag,
362 p_start_date_active => l_new_start_date,
363 p_end_date_active => l_new_end_date,
364 p_hierarchy_level => NULL,
365 x_storage_value => l_msg);
366
367 l_count := 1;
368 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg) ;
369
370 EXCEPTION WHEN OTHERS THEN
371 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while Inserting '||l_flex_value||', '||l_msg);
372 errbuf := 'Error while Inserting '||l_flex_value||', '||l_msg;
373 retcode := 2;
374 RAISE;
375 END;
376
377 END LOOP;
378
379 IF l_count = 0 then
380 FND_FILE.PUT_LINE(FND_FILE.LOG, ' -- none --');
381 END IF;
382
383 IF new_values_cursor%ISOPEN THEN
384 CLOSE new_values_cursor;
385 END IF;
386
387
388 -- The following nodes already exist in the value set and will
389 -- be updated
390
391
392 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
393 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UPDATED NODES: Updating Existing Values');
394 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FLEX VALUES ');
395 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------');
396
397
398 l_sql_stmt := '
399 SELECT
400 X.CHILD_VALUE_ID,
401 X.CHILD_CODE,
402 X.NEW_ENABLED_FLAG,
403 X.NEW_DESCRIPTION,
404 X.NEW_START_DATE,
405 X.NEW_END_DATE,
406 X.NEW_SUMMARY_FLAG
407 FROM
408 (
409 SELECT
410 F.FLEX_VALUE_ID CHILD_VALUE_ID,
411 F.FLEX_VALUE CHILD_CODE,
412 T.DESCRIPTION NEW_DESCRIPTION,
413 NVL((SELECT V.ENABLED_FLAG FROM MTL_CATEGORY_SET_VALID_CATS C
414 WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
415 AND C.CATEGORY_ID = V.CATEGORY_ID
416 AND ROWNUM = 1),''N'') NEW_ENABLED_FLAG,
417 V.START_DATE_ACTIVE NEW_START_DATE,
418 V.DISABLE_DATE NEW_END_DATE,
419 F.ENABLED_FLAG OLD_ENABLED_FLAG,
420 F.START_DATE_ACTIVE OLD_START_DATE,
421 F.END_DATE_ACTIVE OLD_END_DATE,
422 F.SUMMARY_FLAG OLD_SUMMARY_FLAG,
423 FT.DESCRIPTION OLD_DESCRIPTION,
424 NVL((SELECT ''Y'' FROM MTL_CATEGORY_SET_VALID_CATS C
425 WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
426 AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
427 AND ROWNUM = 1), ''N'') NEW_SUMMARY_FLAG
428 FROM MTL_CATEGORIES_KFV V, MTL_CATEGORIES_TL T,
429 FND_FLEX_VALUES F,
430 FND_FLEX_VALUES_TL FT,
431 MTL_CATEGORY_SET_VALID_CATS H
432 WHERE V.STRUCTURE_ID = :l_struct_id
433 AND V.CATEGORY_ID = T.CATEGORY_ID
434 AND T.LANGUAGE = USERENV(''LANG'')
435 AND F.flex_value= V.CONCATENATED_SEGMENTS
436 AND F.flex_value_set_id = :l_flex_val_set_id
437 AND F.flex_VALUE_ID = FT.FLEX_VALUE_ID
438 AND FT.LANGUAGE = USERENV(''LANG'')
439 AND V.CATEGORY_ID = H.CATEGORY_ID(+)
440 AND H.CATEGORY_SET_ID(+) = :g_catset_id) X
441 WHERE X.NEW_ENABLED_FLAG <> X.OLD_ENABLED_FLAG
442 OR X.NEW_SUMMARY_FLAG <> X.OLD_SUMMARY_FLAG
443 OR NVL(X.NEW_DESCRIPTION, ''XX'') <> NVL(X.OLD_DESCRIPTION, ''XX'')
444 OR NVL(X.NEW_START_DATE, SYSDATE) <> NVL(X.OLD_START_DATE, SYSDATE)
445 OR NVL(X.NEW_END_DATE, SYSDATE) <> NVL(X.OLD_END_DATE, SYSDATE)';
446
447
448 -- UPDATING EXISTING VALUES IF CHANGED
449
450 OPEN existing_values_cursor FOR l_sql_stmt USING l_struct_id, l_flex_val_set_id, g_catset_id;
451
452 LOOP
453 FETCH existing_values_cursor INTO
454 l_flex_value_id,
455 l_flex_value,
456 l_new_enabled_flag,
457 l_new_description,
458 l_new_start_date,
459 l_new_end_date,
460 l_new_summary_flag;
461
462 EXIT WHEN existing_values_cursor%NOTFOUND;
463
464 BEGIN
465
466 -- The FND API does not update a value when "null" is passed
467 -- as a value to any of the parameters. When it sees "null"
468 --, the parameter is simply ignored and the column doesn't get
469 -- updated. If you want to update a column to a null value,
470 -- you will need to pass the global constants that they provide
471 -- Hence this if-then clause...
472
473 IF l_new_enabled_flag IS NULL THEN
474 l_new_enabled_flag := fnd_flex_val_api.g_null_varchar2;
475 END IF;
476
477 IF l_new_description IS NULL THEN
478 l_new_description := fnd_flex_val_api.g_null_varchar2;
479 END IF;
480
481 IF l_new_start_date IS NULL THEN
482 l_new_start_date := fnd_flex_val_api.g_null_date;
483 END IF;
484
485 IF l_new_end_date IS NULL or l_new_end_date = '' THEN
486 l_new_end_date := fnd_flex_val_api.g_null_date;
487 END IF;
488
489 IF l_new_summary_flag IS NULL THEN
490 l_new_summary_flag := fnd_flex_val_api.g_null_varchar2;
491 END IF;
492
493
494 FND_FLEX_VAL_API.UPDATE_INDEPENDENT_VSET_VALUE
495 (p_flex_value_set_name => l_value_set_name,
496 p_flex_value => l_flex_value,
497 p_description => l_new_description,
498 p_enabled_flag => l_new_enabled_flag,
499 p_start_date_active => l_new_start_date,
500 p_end_date_active => l_new_end_date,
501 p_summary_flag => l_new_summary_flag,
502 x_storage_value => l_msg);
503
504 l_count := 1;
505 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg);
506
507 EXCEPTION WHEN OTHERS THEN
508 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while updating '||l_flex_value||', '||l_msg);
509 errbuf := 'Error while updating '||l_flex_value||', '||l_msg;
510 retcode := 2;
511 RAISE;
512 END;
513 END LOOP;
514
515 IF l_count = 0 then
516 FND_FILE.PUT_LINE(FND_FILE.LOG, ' -- none --');
517 END IF;
518
519 IF existing_values_cursor%ISOPEN THEN
520 CLOSE existing_values_cursor;
521 END IF;
522
523 IF l_top_node IS NOT NULL THEN
524
525 -- If top node is not null, insert the catalog under the hierarchy
526
527 -- Check if the top node entered is a child node. If it is a
528 -- child node, then first make the node a parent node in the
529 -- value set
530
531 Select summary_flag, enabled_flag
532 into l_summary_flag, l_enabled_flag
533 from fnd_flex_values
534 where flex_value = l_top_node
535 and flex_value_set_id = l_flex_val_set_id; --Bug 5087675
536 -- and rownum = 1; --Bug 5087675
537
538 If l_summary_flag = 'N' OR l_enabled_flag = 'N' then
539
540 begin
541 FND_FLEX_VAL_API.UPDATE_INDEPENDENT_VSET_VALUE
542 (p_flex_value_set_name => l_value_set_name,
543 p_flex_value => l_top_node,
544 p_summary_flag => 'Y',
545 p_enabled_flag => 'Y',
546 x_storage_value => l_msg);
547
548 exception when others then
549 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while updating '||l_top_node||', '||l_msg);
550 errbuf := 'Error while Inserting '||l_flex_value||', '||l_msg;
551 retcode := 2;
552 RAISE;
553
554 end;
555 end if;
556
557 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
558 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Creating Hierarchy: Direct children of the top node ');
559 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parent Code Child Code ');
560 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------- ------------ ');
561
562 -- Inserting new nodes under the top node specified in the UI
563 FOR i in c_hierarchy_top_node(l_top_node) loop
564 BEGIN
565 FND_FLEX_VAL_API.CREATE_VALUE_HIERARCHY(
566 p_flex_value_set_name => l_value_set_name,
567 p_parent_flex_value => i.PARENT_CODE,
568 p_range_attribute => i.RANGE_ATTRIBUTE,
569 p_child_flex_value_low => i.CHILD_CODE,
570 p_child_flex_value_high => i.CHILD_CODE);
571
572 l_count := l_count + 1;
573 FND_FILE.PUT_LINE(FND_FILE.LOG, i.parent_code || ' ' || i.child_code);
574
575 l_compile := TRUE;
576
577 EXCEPTION WHEN OTHERS THEN
578 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while creating hierarchy under the top node '||i.parent_code||', '||l_msg);
579 errbuf := 'Error while creating hierarchy under the top node '||i.parent_code||', '||l_msg;
580 retcode := 2;
581 RAISE;
582 END;
583 END LOOP;
584
585 END IF; -- if top node is not null
586
587
588
589 -- INSERTING THE REST OF THE HIERARCHY
590
591 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
592 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Creating Hierarchy: All the nodes ');
593 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parent Code Child Code ');
594 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------- ------------ ');
595 l_count := 0;
596
597 FOR i IN hierarchy LOOP
598 BEGIN
599 FND_FLEX_VAL_API.CREATE_VALUE_HIERARCHY(
600 p_flex_value_set_name => l_value_set_name,
601 p_parent_flex_value => i.PARENT_CODE,
602 p_range_attribute => i.RANGE_ATTRIBUTE,
603 p_child_flex_value_low => i.CHILD_CODE,
604 p_child_flex_value_high => i.CHILD_CODE);
605
606 l_count := l_count + 1;
607 FND_FILE.PUT_LINE(FND_FILE.LOG, i.parent_code || ' '|| i.child_code);
608
609 l_compile := TRUE;
610 EXCEPTION WHEN OTHERS THEN
611 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while creating hierarchy for child '||i.CHILD_CODE);
612 errbuf := 'Error while creating hierarchy for child '||i.CHILD_CODE;
613 retcode := 2;
614 RAISE;
615 END;
616 END LOOP;
617
618
619
620 -- DELETING NODES WHICH ARE NOT IN HIERARCHY. This is only applicable
621 -- when the top node is specified. In other cases the other nodes
622 -- will remain as is.
623
624 l_count := 0;
625 -- if l_top_node is not null then
626
627 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
628 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Removing hierarchy relationship for nodes that do not exist under the top node');
629
630 -- This will delete all the other children that no longer has any parent-child relationship under the top node
631 -- This delete SQL has two parts:
632 -- The first part of the union selects the existing parent-child
633 -- relationship from the value set hierarchy. If top node is specified
634 -- it will only select the hierarchy under the top node.
635 -- The second part of the union selects all the parent-child relationship
636 -- from the catalog hierarchy
637 -- The minus will eliminate all the records that are present in the
638 -- value set hierarchy but no longer exists in the catalog hierarchy
639
640 -- Changed the first part of the select statement to remove the child flex
641 -- ranges defined in hierarchy
642
643 /* ** Performance fix - see Bug 4960193 ** */
644 delete from fnd_flex_value_norm_hierarchy hrchy
645 where flex_value_set_id = l_flex_val_set_id
646 and exists (
647 select null
648 from fnd_flex_values b
649 where hrchy.flex_value_set_id = b.flex_value_set_id
650 and hrchy.parent_flex_value = b.flex_value
651 and b.enabled_flag = 'Y')
652 and (parent_flex_value,
653 child_flex_value_low,
654 child_flex_value_high,
655 range_attribute)
656 not in (
657 select nvl(a.concatenated_segments,l_top_node),
658 b.concatenated_segments,
659 b.concatenated_segments,
660 NVL((select 'P' from mtl_category_set_valid_cats v
661 where v.category_set_id = c.category_set_id
662 and v.parent_category_id = b.category_id
663 and rownum = 1), 'C')
664 -- decode(b.summary_flag,'Y','P','C')
665 from mtl_categories_kfv a,
666 mtl_categories_kfv b,
667 mtl_category_set_valid_cats c
668 where a.structure_id(+) = l_struct_id
669 and b.structure_id = l_struct_id
670 and c.category_set_id = g_catset_id
671 and c.parent_category_id = a.category_id(+)
672 and c.category_id = b.category_id
673 );
674
675 l_count := SQL%ROWCOUNT;
676
677 -- end if; -- if l_top_node is not null
678 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of records deleted: '||l_count);
679
680 IF l_count > 0 THEN
681 l_compile := TRUE;
682 END IF;
683
684 IF l_compile THEN
685 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
686 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Compiling Value set Hierarchy...');
687
688 FND_GLOBAL.APPS_INITIALIZE(user_id => 0,
689 resp_id => 20420,
690 resp_appl_id => 1);
691
692
693 -- Catch the exception if the compiler fails to compile
694 BEGIN
695
696 FND_FLEX_VAL_API.SUBMIT_VSET_HIERARCHY_COMPILER
697 (p_flex_value_set_name => l_value_set_name,
698 x_request_id => l_msg);
699
700 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submitted request '||l_msg);
701 EXCEPTION
702 WHEN OTHERS THEN
703 l_msg := l_msg || ' ' || dbms_utility.format_error_stack();
704 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submitted request '||l_msg);
705 RAISE;
706 END;
707
708 ELSE
709 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
710 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No changes detected in Hierarchy');
711 END IF;
712
713 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
714 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Successfully completed loading Product Catalog Hierarchy to Value Set');
715
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
719 errbuf := 'No data found ' || sqlerrm;
720 retcode := 2;
721 ROLLBACK;
722 RAISE;
723 WHEN OTHERS THEN
724 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: ' || sqlerrm || ' .Transaction will be rolled back');
725 errbuf := 'Error :' || sqlerrm;
726 retcode := 2;
727 ROLLBACK;
728 RAISE;
729 END UPDATE_VALUESET_FROM_CATEGORY;
730
731 END ENI_PROD_VALUESET;
[Home] [Help]