1 PACKAGE BODY ENI_UPGRADE_VSET AS
2 /* $Header: ENIVSTUB.pls 120.0 2005/05/26 19:34:16 appldev noship $ */
3
4 g_catset_id NUMBER;
5 g_struct_id NUMBER;
6 g_value_set_id NUMBER;
7 g_default_cat_id NUMBER;
8
9 FUNCTION ENI_VALIDATE_SETUP return NUMBER IS
10
11 l_cnt NUMBER;
12 l_report_error NUMBER;
13 l_catg VARCHAR2(40);
14
15 -- this cursor selects all records with more than one parent
16 -- in the value set hierarchy
17 CURSOR C_DUP IS
18 SELECT CHILD_CODE, COUNT(PARENT_CODE) COUNT
19 FROM ENI_VSET_HRCHY_TEMP
20 WHERE HRCHY_FLAG = 'Y'
21 GROUP BY CHILD_CODE
22 HAVING COUNT(PARENT_CODE) > 1;
23
24 -- Cursor that selects all parent categories having item assignments
25
26 CURSOR c_parent_item_assgn(g_struct_id NUMBER, g_catset_id NUMBER) IS
27 SELECT B.SEGMENT1, COUNT(INVENTORY_ITEM_ID) NUMBER_ITEMS
28 FROM MTL_ITEM_CATEGORIES A, MTL_CATEGORIES_B B, ENI_VSET_HRCHY_TEMP C
29 WHERE A.CATEGORY_SET_ID = g_catset_id
30 AND A.CATEGORY_ID = b.category_id
31 AND B.STRUCTURE_ID = g_struct_id
32 AND B.SEGMENT1 = C.PARENT_CODE
33 AND C.HRCHY_FLAG = 'Y'
34 GROUP BY B.SEGMENT1;
35
36 -- Cursor that selects all nodes in the value set whose corresponding
37 -- categories do not exist
38
39 CURSOR c_new_nodes(g_struct_id NUMBER) IS
40 SELECT CHILD_CODE FROM ENI_VSET_HRCHY_TEMP
41 WHERE HRCHY_FLAG = 'Y'
42 MINUS
43 SELECT SEGMENT1 FROM MTL_CATEGORIES_B
44 WHERE STRUCTURE_ID = g_struct_id;
45
46 BEGIN
47
48 -- Check for multiple parent
49
50 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
51 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking if any nodes have multiple parents');
52 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------------------------- ');
53 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR : CATEGORIES WITH MULTIPLE PARENTS');
54 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------------------------- ');
55 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code');
56 FND_FILE.PUT_LINE(FND_FILE.LOG, '--------------');
57
58 l_cnt := 0;
59
60 FOR i IN C_DUP LOOP
61 FND_FILE.PUT_LINE(FND_FILE.LOG, i.child_code);
62 l_cnt := 1;
63 END LOOP;
64 IF l_cnt = 0 THEN
65 FND_FILE.PUT_LINE(FND_FILE.LOG,' --- None --- ');
66 ELSE
67 l_report_error := 1;
68 END IF;
69
70 -- Check for item assignments
71
72 l_cnt := 0;
73 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
74 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking for any parent nodes having item assignments');
75 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------ ');
76 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR : PARENT CATEGORIES WITH ITEM ASSIGNMENTS');
77 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------ ');
78 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code ');
79 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
80
81 FOR i in c_parent_item_assgn(g_struct_id, g_catset_id) LOOP
82 FND_FILE.PUT_LINE(FND_FILE.LOG, i.segment1);
83 l_cnt := 1;
84 END LOOP;
85 IF l_cnt = 0 THEN
86 FND_FILE.PUT_LINE(FND_FILE.LOG,' --- None --- ');
87 ELSE
88 l_report_error := 1;
89 END IF;
90
91 -- Report any categories which is a parent node in the value set
92 -- hierarchy but is also a default category of the default category
93 -- set. This will not be allowed:
94
95 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
96 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking if default category lies outside the hierarchy');
97 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------ ');
98 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR : DEFAULT CATEGORY IS NOT UNDER THE TOP NODE');
99 FND_FILE.PUT_LINE(FND_FILE.LOG,'IN THE VALUE SET, IT WILL BE PLACED AS AS INDEPENDENT');
100 FND_FILE.PUT_LINE(FND_FILE.LOG,'TOP LEVEL CATEGORY');
101 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------ ');
102 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code ');
103 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
104
105 BEGIN
106 SELECT A.segment1 INTO l_catg
107 FROM mtl_categories_B A,
108 mtl_category_sets_b B -- ,ENI_VSET_HRCHY_TEMP C
109 WHERE A.category_id = B.default_category_id
110 AND A.structure_id = B.structure_id
111 AND B.CATEGORY_SET_ID = g_catset_id
112 AND NOT EXISTS (SELECT child_code FROM eni_vset_hrchy_temp
113 WHERE child_code = a.segment1
114 AND hrchy_flag = 'Y');
115
116 FND_FILE.PUT_LINE(FND_FILE.LOG, l_catg);
117
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120 FND_FILE.PUT_LINE(FND_FILE.LOG, ' --- None --- ');
121 END;
122
123
124 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
125 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking if any parent node is specified as default category');
126
127 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------ ');
128 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR : DEFAULT CATEGORY CANNOT BE CONVERTED TO A PARENT ');
129 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------ ');
130 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code ');
131 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
132
133 BEGIN
134 SELECT a.segment1 INTO l_catg
135 FROM mtl_categories_b a,
136 mtl_category_sets_b b, eni_vset_hrchy_temp c
137 WHERE a.category_id = b.default_category_id
138 AND a.structure_id = b.structure_id
139 AND b.category_set_id = g_catset_id
140 AND a.segment1 = c.parent_code
141 AND c.hrchy_flag = 'Y'
142 AND ROWNUM = 1;
143
144 FND_FILE.PUT_LINE(FND_FILE.LOG, l_catg);
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 FND_FILE.PUT_LINE(FND_FILE.LOG, ' --- None --- ');
149 END;
150
151 -- Report any categories that is new in the value set and should be
152 -- created
153 l_cnt := 0;
154 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
155 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking for new nodes in the value set hierarchy');
156 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
157 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: NEW NODES IN THE VALUE SET THAT DO NOT HAVE CORRESPONDING CATEGORIES');
158 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
159 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Flex Values');
160 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
161
162 FOR i in c_new_nodes(g_struct_id) LOOP
163 FND_FILE.PUT_LINE(FND_FILE.LOG, i.child_code);
164 l_cnt := 1;
165 END LOOP;
166
167 IF l_cnt = 0 THEN
168 FND_FILE.PUT_LINE(FND_FILE.LOG,' --- None --- ');
169 ELSE
170 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please run concurrent program: Create item categories from value set to create categories for the new nodes');
171 l_report_error := 1;
172 END IF;
173
174 RETURN l_report_error;
175 END ENI_VALIDATE_SETUP;
176
177
178 PROCEDURE UPDATE_CATSET_FROM_VSET (
179 errbuf OUT NOCOPY VARCHAR2,
180 retcode OUT NOCOPY VARCHAR2,
181 p_top_node IN VARCHAR2,
182 p_validation_mode IN VARCHAR2) IS
183
184 l_cnt NUMBER;
185 l_error BOOLEAN;
186 l_insert NUMBER;
187 l_update NUMBER;
188 l_catg NUMBER;
189 l_catg_id NUMBER;
190
191 l_return_status VARCHAR2(2000);
192 l_errorcode NUMBER;
193 l_msg_count NUMBER;
194 l_msg_data VARCHAR2(15000);
195 l_msg_index_out VARCHAR2(10000);
196 l_data VARCHAR2(10000);
197 l_schema VARCHAR2(10);
198
199 -- Cursor that indicates all categories those are to be deleted
200 -- from valid cats. These categories do not fall below the top node
201 -- and do not have item assignments
202
203 CURSOR c_out_hrchy(g_struct_id NUMBER, g_catset_id NUMBER) IS
204 SELECT segment1, a.category_id, b.category_set_id
205 FROM mtl_categories_b a, mtl_category_set_valid_cats b, mtl_category_sets_b c
206 WHERE a.structure_id = g_struct_id
207 AND b.category_set_id = g_catset_id
208 AND a.category_id = b.category_id
209 AND a.structure_id = c.structure_id
210 AND b.category_set_id = c.category_set_id
211 AND a.category_id <> c.default_category_id
212 AND a.category_id NOT IN (SELECT category_id
213 FROM mtl_item_categories
214 WHERE category_id = a.category_id
215 AND category_set_id = b.category_set_id
216 AND ROWNUM = 1)
217 AND NOT EXISTS(SELECT child_code FROM eni_vset_hrchy_temp
218 WHERE a.segment1 = child_code
219 AND hrchy_flag = 'Y'
220 AND child_code <> p_top_node);
221
222 -- Cursor that indicates the categories that do not fall under the
223 -- top node but has item assignments. These categories will
224 -- remain as stray categories under the product catalog
225
226 CURSOR c_stray_catg(g_struct_id NUMBER, g_catset_id NUMBER) IS
227 SELECT segment1, a.category_id --, b.category_set_id
228 FROM mtl_categories_b a
229 WHERE structure_id = g_struct_id
230 AND EXISTS (SELECT 'X' FROM mtl_item_categories b
231 WHERE a.category_id = b.category_id
232 AND b.category_set_id = g_catset_id)
233 AND NOT EXISTS(SELECT child_code FROM eni_vset_hrchy_temp
234 WHERE A.segment1 = child_code
235 AND hrchy_flag = 'Y')
236 AND p_validation_mode = 'Y';
237
238 -- This Cursor will only run when validation mode = 'N'. This
239 -- will update all nodes that have item assignments to a standalone
240 -- node i.e. where parent_id is null. The last part of union all
241 -- will select default_category if it doesnt have any item assignment
242 -- Bug 3779274
243
244
245 CURSOR c_item_assign(g_struct_id NUMBER, g_catset_id NUMBER) IS
246 SELECT a.category_id, a.segment1, flag
247 FROM (
248 SELECT a.category_id, segment1, 1 flag -- create in valid cats
249 FROM mtl_categories_b a
250 WHERE a.structure_id = g_struct_id
251 AND NOT EXISTS(
252 SELECT child_code FROM eni_vset_hrchy_temp
253 WHERE hrchy_flag = 'Y'
254 AND child_code = a.segment1)
255 AND NOT EXISTS(
256 SELECT category_id FROM mtl_category_set_valid_cats
257 WHERE a.category_id = category_id
258 AND category_set_id = g_catset_id)
259 UNION ALL
260 SELECT a.category_id, b.segment1, 2 flag -- update in valid cats
261 FROM mtl_category_set_valid_cats a, mtl_categories_b b
262 WHERE a.category_set_id = g_catset_id
263 AND a.category_id = b.category_id
264 AND b.structure_id = g_struct_id
265 -- AND NOT EXISTS(
266 -- SELECT child_code FROM eni_vset_hrchy_temp
267 -- WHERE hrchy_flag = 'Y'
268 -- AND child_code = b.segment1)
269 ) a
270 WHERE EXISTS(
271 SELECT category_id FROM mtl_item_categories b
272 WHERE a.category_id = b.category_id
273 AND b.category_set_id = g_catset_id)
274 UNION ALL
275 SELECT category_id, segment1,2 flag
276 FROM
277 mtl_categories_b
278 WHERE
279 category_id = g_default_cat_id
280 AND NOT EXISTS (
281 SELECT b.category_id
282 FROM
283 mtl_item_categories b
284 WHERE b.category_id = g_default_cat_id
285 AND ROWNUM = 1
286 );
287
288 -- SELECT SEGMENT1, A.CATEGORY_ID, B.CATEGORY_SET_ID
289 -- FROM MTL_CATEGORIES_B A, MTL_CATEGORY_SET_VALID_CATS B
290 -- WHERE STRUCTURE_ID = g_struct_id
291 -- AND B.CATEGORY_SET_ID = g_catset_id
292 -- AND A.CATEGORY_ID = B.CATEGORY_ID
293 -- AND A.CATEGORY_ID IN (SELECT CATEGORY_ID
294 -- FROM MTL_ITEM_CATEGORIES
295 -- WHERE CATEGORY_ID = A.CATEGORY_ID
296 -- AND CATEGORY_SET_ID = B.CATEGORY_SET_ID
297 -- AND ROWNUM = 1)
298 -- AND p_validation_mode = 'N';
299
300 CURSOR c_check_temp(segment VARCHAR2) IS
301 SELECT 1 exist_flag FROM DUAL
302 WHERE NOT EXISTS(SELECT child_code
303 FROM eni_vset_hrchy_temp
304 WHERE child_code = segment
305 AND hrchy_flag = 'Y'
306 AND rownum = 1)
307 AND p_validation_mode = 'N';
308
309 -- Cursor to select all the new nodes defined in the value set
310 -- hierarchy (Insert into valid cats) + all the nodes that have
311 -- been moved in the hierarchy (update in valid cats)
312
313 CURSOR C_INS_UPD(l_catg number) IS
314 SELECT
315 v.category_id VSET_CHILD_ID,
316 v.segment1 VSET_CHILD_CODE,
317 DECODE(v1.category_id,l_catg,NULL, v1.category_id) VSET_PARENT_ID,
318 h.category_id CAT_CHILD_ID,
319 h.parent_category_id CAT_PARENT_ID,
320 g_catset_id CATEGORY_SET_ID
321 FROM eni_vset_hrchy_temp f, mtl_categories_b v,
322 mtl_categories_b v1, mtl_category_set_valid_cats h
323 WHERE v.structure_id = g_struct_id
324 AND v1.structure_id(+) = g_struct_id
325 AND f.child_code = v.segment1
326 AND f.parent_code = v1.segment1(+)
327 AND f.hrchy_flag = 'Y'
328 AND h.category_set_id(+) = g_catset_id
329 AND h.category_id(+) = v.category_id
330 AND V.segment1 <> p_top_node;
331
332 BEGIN
333
334 l_error:= FALSE;
335 l_schema := 'ENI';
336
337 -- Validating structure
338 l_error := ENI_VALUESET_CATEGORY.ENI_VALIDATE_STRUCTURE;
339
340 IF l_error THEN
341 errbuf:= 'ERROR: in structure/ segment validation';
342 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: in structure/ segment validation');
343 retcode := 2;
344 RAISE_APPLICATION_ERROR(-20009, 'ERROR: in structure/ segment validation');
345 END IF;
346
347 -- Get category set associated with the Product Functional Area
348 g_catset_id := ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID;
349
350 -- Get the value set that is associated with the structure
351 g_value_set_id := ENI_VALUESET_CATEGORY.GET_FLEX_VALUE_SET_ID('401','MCAT',g_catset_id);
352
353 IF g_value_set_id IS NULL THEN
354 errbuf := 'ERROR: There is no value set associated with the default category set structure. Aborting....';
355 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: There is no value set associated with the default category set structure. ');
356 retcode := 2;
357 RAISE_APPLICATION_ERROR(-20009, 'Error: No value set is associated with the structure');
358 END IF;
359
360 -- Get structure id for the default category set for Product FA
361
362 SELECT STRUCTURE_ID,DEFAULT_CATEGORY_ID INTO g_struct_id,g_default_cat_id
363 FROM MTL_CATEGORY_SETS_B
364 WHERE CATEGORY_SET_ID = g_catset_id;
365
366 -- Call Validate_structure from this procedure to do all the
367 -- validations. If everything is alright then move ahead.
368
369 -- populating the entire value set into the temp table
370 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Truncating the temp table ... ');
371 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ENI_VSET_HRCHY_TEMP';
372
373 INSERT INTO ENI_VSET_HRCHY_TEMP(
374 CHILD_CODE,
375 PARENT_CODE,
376 HRCHY_FLAG)
377 SELECT
378 FLEX_VALUE CHILD_CODE,
379 PARENT_FLEX_VALUE PARENT_CODE,
380 'N'
381 FROM FND_FLEX_VALUE_CHILDREN_V
382 WHERE FLEX_VALUE_SET_ID = g_value_set_id
383 AND FLEX_VALUE <> p_top_node
384 UNION ALL
385 SELECT p_top_node, NULL, 'N' FROM DUAL;
386
387 -- populating the hierarchy from only the top node down
388 -- into the temp table
389
390 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Populating hierarchy in temp table under the top node');
391
392 INSERT INTO ENI_VSET_HRCHY_TEMP (
393 CHILD_CODE,
394 PARENT_CODE,
395 HRCHY_FLAG)
396 SELECT CHILD_CODE, PARENT_CODE, 'Y'
397 FROM ENI_VSET_HRCHY_TEMP H
398 CONNECT BY PRIOR CHILD_CODE = PARENT_CODE
399 START WITH CHILD_CODE = p_top_node;
400
401 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted: ' || sql%rowcount);
402
403 COMMIT;
404
405 IF p_validation_mode = 'Y' THEN
406
407 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
408 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Running in Validation Mode');
409 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
410
411 -- Calling function to validate setup. Erroring out if any error
412 -- is reported.
413 l_cnt := ENI_VALIDATE_SETUP;
414
415 -- Report any categories that will be removed from the category set
416 -- as they do not exist in the value set hierarchy
417
418 l_cnt := 0;
419 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
420 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking for categories that do not exist in the value set hierarchy');
421 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
422 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INFORMATION: THE FOLLOWING CATEGORIES ARE NOT UNDER THE TOP NODE ');
423 FND_FILE.PUT_LINE(FND_FILE.LOG,'IN THE VALUE SET AND HAVE NO ITEM ASSIGNMENTS, HENCE WILL BE REMOVED');
424 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------- ');
425 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code ');
426 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
427
428 FOR i in c_out_hrchy(g_struct_id, g_catset_id) LOOP
429 FND_FILE.PUT_LINE(FND_FILE.LOG, i.SEGMENT1);
430 l_cnt := 1;
431 END LOOP;
432 IF l_cnt = 0 THEN
433 FND_FILE.PUT_LINE(FND_FILE.LOG,' --- None --- ');
434 END IF;
435
436
437 -- Report any categories that will be left in the valid cats table
438 -- as stray categories. These categories have item assignments
439
440 l_cnt := 0;
441 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
442 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking for categories that do not exist in the value set hierarchy but has item assignments');
443 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
444 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INFORMATION: THE FOLLOWING CATEGORIES ARE NOT UNDER THE ');
445 FND_FILE.PUT_LINE(FND_FILE.LOG,'TOP NODE IN THE VALUE SET BUT HAS ITEM ASSIGNMENTS.');
446 FND_FILE.PUT_LINE(FND_FILE.LOG, 'HENCE WILL BE PLACED AS INDEPENDENT TOP LEVEL CATEGORIES');
447 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
448 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code ');
449 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
450
451 FOR i in c_stray_catg(g_struct_id, g_catset_id) LOOP
452 FND_FILE.PUT_LINE(FND_FILE.LOG, i.SEGMENT1);
453 l_cnt := 1;
454 END LOOP;
455 IF l_cnt = 0 THEN
456 FND_FILE.PUT_LINE(FND_FILE.LOG,' --- None --- ');
457 END IF;
458
459 ELSIF p_validation_mode = 'N' then
460
461 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
462 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Running in Upgrade Mode');
463 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
464
465 -- Calling function to validate hierarchy setup.
466 -- Error out if setup fails
467
468 l_cnt := ENI_VALIDATE_SETUP;
469
470 IF l_cnt = 1 THEN
471 errbuf := 'ERROR: Setup Error. Aborting....';
472 retcode := 2;
473 RAISE_APPLICATION_ERROR(-20009, 'Setup/ Data Error');
474 END IF;
475
476
477 -- Update all the nodes that do not belong in the hierarchy
478 -- but has item assignments. These nodes will not be deleted
479 -- but will be made as standalone nodes. To do this, the
480 -- parent_id of the node will be set to null.
481 -- If there are other categories that have item assignments
482 -- as well but do not belong in mtl valid cats, then those
483 -- categories will be created as a standalone node in the
484 -- valid categories table
485 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
486 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking for categories that do not exist in the value set hierarchy but has item assignments');
487 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
488 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INFORMATION: CATEGORIES NOT UNDER THE TOP NODE BUT HAS ITEM ');
489 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ASSIGNMENTS. WILL REMAIN AS INDEPENDENT TOP LEVEL CATEGORIES');
490 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------ ');
491 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code ');
492 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------- ');
493
494 FOR i IN c_item_assign(g_struct_id, g_catset_id) LOOP
495
496 IF i.flag = 1 THEN
497
498 INV_ITEM_CATEGORY_PVT.Create_Valid_Category(
499 p_api_version => 1,
500 p_category_set_id => g_catset_id,
501 p_category_id => i.category_id,
502 p_parent_category_id => null,
503 x_return_status => l_return_status,
504 x_errorcode => l_errorcode,
505 x_msg_count => l_msg_count,
506 x_msg_data => l_msg_data
507 );
508
509 ELSE
510
511 INV_ITEM_CATEGORY_PVT.Update_Valid_Category(
512 p_api_version => 1,
513 p_category_set_id => g_catset_id,
514 p_category_id => i.category_id,
515 p_parent_category_id => null,
516 x_return_status => l_return_status,
517 x_errorcode => l_errorcode,
518 x_msg_count => l_msg_count,
519 x_msg_data => l_msg_data
520 );
521
522 END IF;
523
524
525 IF l_return_status <> 'S' THEN
526 FND_MSG_PUB.Get(p_msg_index=>fnd_msg_pub.G_LAST, p_encoded=>FND_API.G_FALSE, p_msg_index_out=>l_msg_index_out, p_data=>l_data);
527 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while updating category: '||to_char(i.category_id)||' from product hierarchy');
528 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: '||l_data);
529 errbuf := 'Error :' || l_data;
530 retcode := 2;
531 goto end_block;
532 ELSE
533
534 -- The following loop will only execute when a node is
535 -- has item assignments but is not in the hierarchy under
536 -- the top node
537
538 FOR j IN c_check_temp(i.segment1) LOOP
539 FND_FILE.PUT_LINE(FND_FILE.LOG, i.SEGMENT1);
540 END LOOP;
541
542 END IF;
543 END LOOP;
544
545 l_cnt := 0;
546 FOR i IN c_out_hrchy(g_struct_id, g_catset_id) LOOP
547
548 if l_cnt = 0 then
549 -- After the nodes with item assignments are updated as standalone
550 -- the remaining nodes in the branch can be deleted (similar to the
551 -- UI behaviour). Here we will delete records from product hierarchy,
552 -- which are not a part of VB hierarchy, starting from the top node.
553
554 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
555 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------');
556 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DELETED CATEGORIES: Removing categories from the default category ');
557 FND_FILE.PUT_LINE(FND_FILE.LOG, 'set, which no longer belongs under the top node');
558 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------');
559
560 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code');
561 FND_FILE.PUT_LINE(FND_FILE.LOG, '--------------');
562
563 l_cnt := 1;
564 END IF;
565
566 -- The foll. SQL is written to prevent the Delete_valid_category
567 -- API from failing. The way the Delete API works is, when a
568 -- parent node is passed as a category id, it will delete itself
569 -- and all its children under it. So, next time when we pass the
570 -- children's category id, the API will fail since by then the
571 -- child has already been deleted.
572
573 SELECT COUNT(CATEGORY_ID) INTO l_catg
574 FROM MTL_CATEGORY_SET_VALID_CATS
575 WHERE CATEGORY_SET_ID = i.CATEGORY_SET_ID
576 AND CATEGORY_ID = i.CATEGORY_ID;
577
578 IF l_catg <> 0 THEN
579
580 INV_ITEM_CATEGORY_PUB.Delete_Valid_Category(
581 p_api_version => 1,
582 p_category_set_id => i.category_set_id,
583 p_category_id => i.category_id,
584 x_return_status => l_return_status,
585 x_errorcode => l_errorcode,
586 x_msg_count => l_msg_count,
587 x_msg_data => l_msg_data
588 );
589
590 IF l_return_status <> 'S' THEN
591 FND_MSG_PUB.Get(p_msg_index=>fnd_msg_pub.G_LAST, p_encoded=>FND_API.G_FALSE, p_msg_index_out=>l_msg_index_out, p_data=>l_data);
592 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while deleting '||to_char(i.category_id)||' from product hierarchy');
593 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: '||l_data);
594 errbuf := 'Error :' || l_data;
595 retcode := 2;
596 goto end_block;
597 ELSE
598 FND_FILE.PUT_LINE(FND_FILE.LOG, i.SEGMENT1);
599 END IF;
600 END IF;
601 END LOOP;
602
603 IF l_cnt = 0 THEN
604 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- None ---');
605 END IF;
606
607 -- inserting new categories, which do not exist
608 -- under the default category set. The cursor would
609 -- also update if the parent-child relationship in the
610 -- hierarchy has changed
611
612 l_insert := 0;
613 l_update := 0;
614 l_catg := 0;
615
616 -- Getting the category id for the top node
617 SELECT category_id INTO l_catg
618 FROM mtl_categories_b
619 WHERE structure_id = g_struct_id
620 AND segment1 = p_top_node;
621
622 FOR i IN C_INS_UPD(l_catg) LOOP
623
624 -- if the top node is the parent, then update it will null
625 -- This is based on the new requirement where the top node
626 -- should not be brought into the hierarchy
627
628 -- IF (i.vset_parent_id = l_catg) THEN
629 -- l_catg_id := '';
630 -- ELSE
631 -- l_catg_id := i.vset_parent_id;
632 -- END IF;
633
634 IF i.cat_child_id IS NULL THEN
635 IF l_insert = 0 THEN
636 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
637 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------');
638 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEW CATEGORIES: Creating category under the default category set');
639 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------');
640
641 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code');
642 FND_FILE.PUT_LINE(FND_FILE.LOG, '--------------');
643
644 l_insert := 1;
645 END IF;
646
647
648 INV_ITEM_CATEGORY_PVT.Create_Valid_Category(
649 p_api_version => 1,
650 p_category_set_id => i.category_set_id,
651 p_category_id => i.vset_child_id,
652 p_parent_category_id => i.vset_parent_id,
653 x_return_status => l_return_status,
654 x_errorcode => l_errorcode,
655 x_msg_count => l_msg_count,
656 x_msg_data => l_msg_data
657 );
658
659 IF l_return_status <> 'S' THEN
660 FND_MSG_PUB.Get(p_msg_index=>fnd_msg_pub.G_LAST, p_encoded=>FND_API.G_FALSE, p_msg_index_out=>l_msg_index_out, p_data=>l_data);
661 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while inserting '||i.vset_child_code||' into product hierarchy');
662 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: '||l_data);
663 errbuf := 'ERROR :' || l_data;
664 retcode := 2;
665 goto end_block;
666 ELSE
667 FND_FILE.PUT_LINE(FND_FILE.LOG, i.vset_child_code);
668 END IF;
669
670 -- Will update the parent-child relationship in
671 -- mtl_category_set_valid_cats table if such a change
672 -- is detected
673 ELSIF NVL(i.vset_parent_id, -1) <> NVL(i.cat_parent_id, -1) THEN
674 IF l_update = 0 then
675 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
676 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------');
677 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UPDATED CATEGORIES: Updating categories with the new parent-child relationship in the value set');
678 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------------------------');
679
680 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Code');
681 FND_FILE.PUT_LINE(FND_FILE.LOG, '--------------');
682
683 l_update := 1;
684
685 END IF;
686
687
688 INV_ITEM_CATEGORY_PVT.Update_Valid_Category(
689 p_api_version => 1,
690 p_category_set_id => i.category_set_id,
691 p_category_id => i.vset_child_id,
692 p_parent_category_id => i.vset_parent_id,
693 x_return_status => l_return_status,
694 x_errorcode => l_errorcode,
695 x_msg_count => l_msg_count,
696 x_msg_data => l_msg_data
697 );
698
699 IF l_return_status <> 'S' THEN
700 FND_MSG_PUB.Get(p_msg_index=>fnd_msg_pub.G_LAST, p_encoded=>FND_API.G_FALSE, p_msg_index_out=>l_msg_index_out, p_data=>l_data);
701 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while updating '||i.vset_child_code||' of product hierarchy');
702 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR: '||l_data);
703 errbuf := 'ERROR :' || l_data;
704 retcode := 2;
705 goto end_block;
706 ELSE
707 FND_FILE.PUT_LINE(FND_FILE.LOG, i.vset_child_code);
708 END IF;
709
710 END IF; -- IF i.cat_child_id IS NULL THEN
711 END LOOP;
712
713 END IF; -- IF Validation_mode = 'N' THEN
714
715 <<end_block>>
716 NULL;
717
718 EXCEPTION
719 WHEN NO_DATA_FOUND THEN
720 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
721 errbuf := 'No data found ' || sqlerrm;
722 retcode := 2;
723 ROLLBACK;
724 RAISE;
725 WHEN OTHERS THEN
726 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: ' || sqlerrm || '. Transaction will be rolled back');
727 errbuf := 'Error :' || sqlerrm;
728 retcode := 2;
729 ROLLBACK;
730 RAISE;
731 END;
732
733 END ENI_UPGRADE_VSET;
[Home] [Help]