[Home] [Help]
PACKAGE BODY: APPS.ENI_ITEMS_STAR_PKG
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENI_ITEMS_STAR_PKG';
1 Package Body ENI_ITEMS_STAR_PKG AS
2 /* $Header: ENIIDBCB.pls 120.13.12020000.3 2013/02/28 11:44:03 gnookala ship $ */
3
5
6 --5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
7 G_INSTALL_PHASE NUMBER := 0;
8
9 --G_SYNC_STAR_ITEMS VARCHAR2(15) := 'NOT_CHECKED';
10
11 --**************************************************
12 -- Creates De-normalized Item STAR table
13 --**************************************************
14
15 PROCEDURE Create_Star_Table(errbuf out NOCOPY varchar2, retcode out NOCOPY varchar2)
16 IS
17
18 CURSOR get_po_catset IS
19 SELECT category_set_id
20 FROM mtl_default_category_sets
21 WHERE functional_area_id = 2;
22
23 CURSOR c IS
24 SELECT category_set_id
25 FROM mtl_default_category_sets
26 WHERE functional_area_id = 1;
27
28 CURSOR c_mult_item_assgn(l_inv_category_set NUMBER, l_vbh_category_set NUMBER, l_po_category_set NUMBER)
29 IS
30 SELECT
31 cat.inventory_item_id,
32 cat.organization_id,
33 cat.category_set_id,
34 mti.concatenated_segments,
35 -- mtp.organization_code,
36 COUNT(category_id)
37 FROM
38 mtl_system_items_kfv mti,
39 -- mtl_parameters mtp,
40 mtl_item_categories cat
41 WHERE
42 mti.inventory_item_id = cat.inventory_item_id AND
43 mti.organization_id = cat.organization_id AND
44 -- mtp.organization_id = mti.organization_id AND
45 category_set_id IN (l_vbh_category_set, l_inv_category_set, l_po_category_set)
46 GROUP BY
47 cat.category_set_id,
48 cat.inventory_item_id,
49 cat.organization_id,
50 mti.concatenated_segments
51 -- mtp.organization_code
52 HAVING COUNT(category_id) > 1;
53
54 -- Cursor to figure out the items having same name with diff. ids
55 CURSOR c_mult_item IS
56 SELECT
57 mti.concatenated_segments,
58 mti.organization_id,
59 COUNT(mti.inventory_item_id)
60 FROM
61 mtl_system_items_kfv mti
62 GROUP BY
63 mti.concatenated_segments,
64 mti.organization_id
65 HAVING COUNT(inventory_item_id) > 1;
66
67 -- This cursor is dependent on cursor c_mult_item. This
68 -- will only print out the item ids that have the same name
69 CURSOR c_item_id(l_name varchar2) IS
70 SELECT
71 inventory_item_id,
72 organization_id
73 FROM
74 mtl_system_items_kfv
75 WHERE
76 concatenated_segments = l_name;
77
78 CURSOR c_non_flex_item IS
79 SELECT
80 count(mti.inventory_item_id),
81 mti.organization_id,
82 mti.concatenated_segments
83 FROM
84 mtl_system_items_kfv mti
85 WHERE
86 mti.concatenated_segments = 'X'
87 GROUP BY mti.concatenated_segments, mti.organization_id
88 HAVING count(inventory_item_id) > 1;
89
90 l_inv_category_set NUMBER;
91 l_vbh_category_set NUMBER;
92 l_po_category_set NUMBER;
93 l_prev_po_catset NUMBER;
94 l_record_count NUMBER;
95 l_dummy NUMBER;
96 l_table_schema VARCHAR2(4) ;
97 l_batch_size NUMBER ;
98 l_rows_inserted NUMBER; -- Bug#2662318 --
99 l_temp VARCHAR2(1);
100 l_top_node NUMBER;
101 l_exist_flag VARCHAR2(240);
102 l_errors NUMBER;
103 l_unique_viol NUMBER;
104 l_prev_inv_catset NUMBER;
105 l_prev_vbh_catset NUMBER;
106 l_full_refresh VARCHAR2(1);
107 l_schema VARCHAR2(10);
108
109 snp_not_found EXCEPTION;
110 PRAGMA EXCEPTION_INIT(snp_not_found, -12002);
111
112 type recstartyp is table of ENI_OLTP_ITEM_STAR%ROWTYPE;
113 item_star_record recstartyp;
114
115 unique_cons_violation EXCEPTION;
116 PRAGMA EXCEPTION_INIT(unique_cons_violation,-1);
117
118 BEGIN
119
120 l_dummy :=0;
121 l_rows_inserted := 0;
122 l_schema := 'ENI';
123
124 --
125 -- ENI Obsolescence
126 --
127 -- If BIS_COLLECTION_UTILITIES.SETUP(p_object_name => 'ENI_OLTP_ITEM_STAR')=false then
128 -- RAISE_APPLICATION_ERROR(-20000,errbuf);
129 -- End if;
130
131 -- Calculating batchsize
132 -- -- BIS_COLLECTION_UTILITIES.log('Push Size: '||to_char(FND_PROFILE.value('EDW_PUSH_SIZE')));
133 -- -- BIS_COLLECTION_UTILITIES.log('Complexity: '||to_char(BIS_COMMON_PARAMETERS.MEDIUM));
134
135 -- Setting hash_area_size and sort_area_size for this session
136 -- BIS_COLLECTION_UTILITIES.log('Altering hash area and sort area size ');
137
138 EXECUTE IMMEDIATE 'alter session set hash_area_size = 200000000';
139 EXECUTE IMMEDIATE 'alter session set sort_area_size = 50000000';
140
141 -- BIS_COLLECTION_UTILITIES.log('Fetching default INV category set...');
142 OPEN c;
143 FETCH c into l_inv_category_set;
144 IF c%NOTFOUND THEN l_inv_category_set := null; END IF;
145 CLOSE c;
146
147 -- BIS_COLLECTION_UTILITIES.log('Fetching VBH category set...');
148 l_vbh_category_set := ENI_DENORM_HRCHY.get_category_set_id;
149
150 -- BIS_COLLECTION_UTILITIES.log('Fetching PO category set...');
151 OPEN get_po_catset;
152 FETCH get_po_catset INTO l_po_category_set;
153 CLOSE get_po_catset;
154
155 -- BIS_COLLECTION_UTILITIES.log('Default category sets are:' );
156 -- BIS_COLLECTION_UTILITIES.log(' Product functional area ==> ' || to_char(l_vbh_category_set));
157 -- BIS_COLLECTION_UTILITIES.log(' Inventory functional area ==> ' || to_char(l_inv_category_set));
161
158 -- BIS_COLLECTION_UTILITIES.log(' Purchasing functional area ==> ' || to_char(l_po_category_set));
159
160 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ENI_ITEM_STAR_VALID_ERR';
162 -- BIS_COLLECTION_UTILITIES.log('');
163 -- BIS_COLLECTION_UTILITIES.log('Validation Checks');
164 -- BIS_COLLECTION_UTILITIES.log('----------------------');
165 -- BIS_COLLECTION_UTILITIES.log('');
166 -- BIS_COLLECTION_UTILITIES.log('');
167
168
169 l_temp := 'N';
170
171 -- BIS_COLLECTION_UTILITIES.log('----------------------------------------------');
172 -- BIS_COLLECTION_UTILITIES.log('Checking for multiple item-category assignment');
173 -- BIS_COLLECTION_UTILITIES.log('----------------------------------------------');
174
175 FOR c2 in c_mult_item_assgn(l_inv_category_set, l_vbh_category_set, l_po_category_set)
176 LOOP
177 l_temp := 'Y';
178
179 -- BIS_COLLECTION_UTILITIES.log(c2.concatenated_segments);
180 retcode := 1;
181
182 INSERT INTO ENI_ITEM_STAR_VALID_ERR(
183 inventory_item_id,
184 organization_id,
185 item_name,
186 category_set_id,
187 error_message)
188 VALUES(
189 c2.inventory_item_id,
190 c2.organization_id,
191 c2.concatenated_segments,
192 -- c2.organization_code,
193 c2.category_set_id,
194 'ITEMS WITH MULTIPLE CATEGORY ASSIGNMENT'
195 );
196 END LOOP;
197
198 -- if l_temp = 'N' then
199 -- BIS_COLLECTION_UTILITIES.log(' -- No issues found -- ');
200 -- else
201 -- BIS_COLLECTION_UTILITIES.log('Suggestion: Items can only be assigned to one category');
202 -- BIS_COLLECTION_UTILITIES.log('in the default catalog of Inventory, PO and/ or Product reporting');
203 -- BIS_COLLECTION_UTILITIES.log('functional area. Please ensure that this criteria is met');
204 -- BIS_COLLECTION_UTILITIES.log('for the items failing this test.');
205 -- end if;
206
207
208 l_temp := 'N';
209
210 -- BIS_COLLECTION_UTILITIES.log('');
211 -- BIS_COLLECTION_UTILITIES.log('--------------------------------------------');
212 -- BIS_COLLECTION_UTILITIES.log('Checking if item flexfield has been compiled');
213 -- BIS_COLLECTION_UTILITIES.log('--------------------------------------------');
214
215 FOR c3 in c_non_flex_item LOOP
216 l_temp := 'Y';
217 -- BIS_COLLECTION_UTILITIES.log('Item Flexfield has not been compiled. ');
218 END LOOP;
219
220 if l_temp = 'N' then
221 -- BIS_COLLECTION_UTILITIES.log(' -- No issues found -- ');
222 null;
223 else
224 -- BIS_COLLECTION_UTILITIES.log('Please compile item flexfield and then run the item dimension load');
225 RAISE_APPLICATION_ERROR(-20001, 'ERROR: Item flexfield has not been compiled');
226 end if;
227
228
229 l_temp := 'N';
230 -- BIS_COLLECTION_UTILITIES.log('');
231 -- BIS_COLLECTION_UTILITIES.log('----------------------------------------------');
232 -- BIS_COLLECTION_UTILITIES.log('Checking for multiple items with the same name');
233 -- BIS_COLLECTION_UTILITIES.log('----------------------------------------------');
234
235 FOR c4 in c_mult_item LOOP
236 l_temp := 'Y';
237
238 -- BIS_COLLECTION_UTILITIES.log(c4.concatenated_segments);
239 retcode := 1;
240
241 FOR c5 in c_item_id(c4.concatenated_segments) LOOP
242
243 INSERT INTO ENI_ITEM_STAR_VALID_ERR(
244 inventory_item_id,
245 organization_id,
246 item_name,
247 error_message)
248 VALUES(
249 c5.inventory_item_id,
250 c5.organization_id,
251 c4.concatenated_segments,
252 'MULTIPLE ITEMS WITH SAME NAME'
253 );
254 END LOOP;
255 END LOOP;
256
257 -- if l_temp = 'N' then
258 -- BIS_COLLECTION_UTILITIES.log(' -- No issues found -- ');
259 -- else
260 -- BIS_COLLECTION_UTILITIES.log('Suggestion: Item names need to be unique. Ensure the ');
261 -- BIS_COLLECTION_UTILITIES.log('item names that failed the test are unique in the system. ');
262 -- end if;
263
264
265 -- Deciding if the load has to be fully refreshed or partially refreshed
266 -- Full refresh it if:
267 -- 1. STAR table is empty
268 -- 2. Default category set of INV and VBH functional area has changed
269
270 BEGIN
271 -- Added for Bug 4747510
272 SELECT vbh_category_set_id, inv_category_set_id, po_category_set_id
273 INTO l_prev_vbh_catset, l_prev_inv_catset, l_prev_po_catset
274 FROM eni_oltp_item_star
275 WHERE inventory_item_id = -1
276 AND organization_id = -99
277 AND rownum = 1;
278
279 SELECT vbh_category_set_id, inv_category_set_id, po_category_set_id
280 INTO l_prev_vbh_catset, l_prev_inv_catset, l_prev_po_catset
281 FROM eni_oltp_item_star
282 WHERE inventory_item_id <> -1
283 AND organization_id <> -99
284 AND rownum = 1;
285
286 IF (l_prev_vbh_catset <> l_vbh_category_set OR
287 l_prev_inv_catset <> l_inv_category_set OR
288 NVL(l_prev_po_catset,-1) <> l_po_category_set) THEN
289 l_full_refresh := 'Y';
290 ELSE
291 l_full_refresh := 'N';
292 END IF;
293
294 EXCEPTION
295 WHEN NO_DATA_FOUND THEN
296 l_full_refresh := 'Y';
297
298 END;
299
300 IF l_full_refresh = 'Y' THEN
301
302 -- BIS_COLLECTION_UTILITIES.log('');
303 -- BIS_COLLECTION_UTILITIES.log('Running in full mode');
304 -- BIS_COLLECTION_UTILITIES.log('--------------------');
305 -- BIS_COLLECTION_UTILITIES.log('');
306 -- BIS_COLLECTION_UTILITIES.log('Clearing STAR table');
310 -- Bug 14711987. Removed purge materilized view log
307 -- BIS_COLLECTION_UTILITIES.log('--------------------');
308
309 --
311 -- statement from below truncate command.
312 -- sreharih. Mon Oct 8 14:08:31 PDT 2012
313 --
314 EXECUTE IMMEDIATE 'truncate table ' || l_schema || '.eni_oltp_item_star ';
315
316 -- BIS_COLLECTION_UTILITIES.log('Inserting UNASSIGNED row into STAR table');
317
318 INSERT INTO ENI_OLTP_ITEM_STAR (
319 id
320 , value
321 , organization_code
322 , inventory_item_id
323 , organization_id
324 , po_category_id
325 , po_category_set_id
326 , po_concat_seg
327 , inv_category_id
328 , inv_category_set_id
329 , inv_concat_seg
330 , vbh_category_id
331 , vbh_category_set_id
332 , vbh_concat_seg
333 , master_id
334 , creation_date
335 , last_update_date
336 , item_catalog_group_id
337 , primary_uom_code
338 , unit_weight
339 , unit_volume
340 , weight_uom_code
341 , volume_uom_code
342 , eam_item_type
343 )
344 VALUES ('-1--99',
345 'Product not specified',
346 NULL,
347 -1,
348 -99,
349 -1,
350 l_po_category_set,
351 'Unassigned',
352 -1,
353 l_inv_category_set,
354 'Unassigned',
355 -1,
356 l_vbh_category_set,
357 'Unassigned',
358 NULL,
359 SYSDATE,
360 SYSDATE,
361 -1,
362 NULL,
363 NULL,
364 NULL,
365 NULL,
366 NULL,
367 NULL
368 );
369
370 -- BIS_COLLECTION_UTILITIES.log('Inserted UNASSIGNED item row');
371
372 COMMIT;
373
374 BEGIN
375 -- Main insert of items
376
377 -- BIS_COLLECTION_UTILITIES.log('Inserting all ITEM MASTER items into STAR table');
378
379 INSERT /*+ append parallel */ INTO ENI_OLTP_ITEM_STAR (
380 id
381 , value
382 , organization_code
383 , inventory_item_id
384 , organization_id
385 , po_category_id
386 , po_category_set_id
387 , po_concat_seg
388 , inv_category_id
389 , inv_category_set_id
390 , inv_concat_seg
391 , vbh_category_id
392 , vbh_category_set_id
393 , vbh_concat_seg
394 , master_id
395 , creation_date
396 , last_update_date
397 , item_catalog_group_id
398 , primary_uom_code
399 , unit_weight
400 , unit_volume
401 , weight_uom_code
402 , volume_uom_code
403 , eam_item_type
404 )
405 SELECT /*+ ordered parallel(mti) parallel(mic) parallel(mic1) */
406 mti.inventory_item_id || '-' || mti.organization_id id,
407 mti.CONCATENATED_SEGMENTS || ' (' || mtp.organization_code || ')' value,
408 null organization_code,
409 mti.inventory_item_id inventory_item_id,
410 mti.organization_id organization_id,
411 Nvl(mic2.category_id,-1) po_category_id,
412 Nvl(mic2.category_set_id, l_po_category_set) po_category_set_id,
413 Nvl(kfv2.concatenated_segments,'Unassigned') po_concat_seg,
414 nvl(mic.category_id,-1) inv_category_id,
415 nvl(mic.category_Set_id,l_inv_category_set) inv_category_Set_id,
416 nvl(kfv.concatenated_segments,'Unassigned') inv_concat_seg,
417 nvl(mic1.category_id, -1) vbh_category_id,
418 nvl(mic1.category_set_id, l_vbh_category_set) vbh_category_set_id,
419 nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
420 decode(mti.organization_id,mtp.master_organization_id,null,
421 mti.inventory_item_id || '-' || mtp.master_organization_id)
422 master_id,
423 mti.creation_date creation_date,
424 mti.last_update_date last_update_date,
425 nvl(mti.item_catalog_group_id,-1) item_catalog_group_id,
426 mti.primary_uom_code,
427 mti.unit_weight,
428 mti.unit_volume,
429 mti.weight_uom_code,
430 mti.volume_uom_code,
431 mti.eam_item_type
432 FROM mtl_system_items_b_kfv mti,
433 mtl_parameters mtp,
434 mtl_item_categories mic ,
435 mtl_item_categories mic1 ,
436 mtl_item_categories mic2 ,
437 mtl_categories_b_kfv kfv ,
438 mtl_categories_b_kfv kfv1,
439 mtl_categories_b_kfv kfv2
440 WHERE mtp.organization_id=mti.organization_id
441 AND mic.organization_id(+) = mti.organization_id
442 AND mic.inventory_item_id(+) = mti.inventory_item_id
443 AND mic.category_id = kfv.category_id (+)
444 and mic.category_set_id(+) = l_inv_category_set
445 AND mic1.organization_id(+) = mti.organization_id
446 AND mic1.inventory_item_id(+) = mti.inventory_item_id
447 AND mic1.category_id = kfv1.category_id (+)
448 and mic1.category_set_id(+) = l_vbh_category_set
449 AND mic2.organization_id(+) = mti.organization_id
450 AND mic2.inventory_item_id(+) = mti.inventory_item_id
451 AND mic2.category_id = kfv2.category_id (+)
452 and mic2.category_set_id(+) = l_po_category_set
453 AND NOT EXISTS(select 'X' from eni_item_star_valid_err
454 WHERE inventory_item_id = mti.inventory_item_id
455 AND organization_id = mti.organization_id);
456
460
457 l_rows_inserted := sql%rowcount;
458
459 -- BIS_COLLECTION_UTILITIES.log('Rows inserted into table:'||l_rows_inserted);
461 COMMIT; --Added for Bug 4525918
462
463 EXCEPTION
464 WHEN NO_DATA_FOUND THEN
465 null;
466 WHEN OTHERS THEN
467 -- BIS_COLLECTION_UTILITIES.log(sqlerrm);
468 -- Delete UNASSIGNED row as the main insert of items was not successful.
469 DELETE FROM ENI_OLTP_ITEM_STAR WHERE inventory_item_id = -1 AND organization_id = -99;
470 -- BIS_COLLECTION_UTILITIES.log('Removed UNASSIGNED row as main insert of items was not successful');
471 COMMIT;
472 END;
473
474
475 -- If records exist in the temporary table it means that
476 -- some records got updated from the API calls during the main insert.
477 -- Update those records from the temporary table to the STAR table
478
479 begin
480
481 -- BIS_COLLECTION_UTILITIES.log(' Checking Temp table for any records ');
482 select 1 into l_exist_flag from eni_item_star_temp
483 where rownum = 1;
484
485 if l_exist_flag = 1 then
486 -- BIS_COLLECTION_UTILITIES.log('Updating STAR table with records from temp table');
487
488 UPDATE eni_oltp_item_star a
489 SET ( value
490 , last_update_date
491 , po_category_set_id
492 , po_category_id
493 , po_concat_seg
494 , inv_category_set_id
495 , inv_category_id
496 , inv_concat_seg
497 , vbh_category_set_id
498 , vbh_category_id
499 , vbh_concat_seg
500 , item_catalog_group_id
501 , primary_uom_code
502 , unit_weight
503 , unit_volume
504 , weight_uom_code
505 , volume_uom_code
506 , eam_item_type
507 )=
508 ( SELECT
509 nvl(value, a.value)
510 , nvl(last_update_date, a.last_update_date)
511 , nvl(po_category_set_id, a.po_category_set_id)
512 , nvl(po_category_id, a.po_category_id)
513 , nvl(po_concat_seg, a.po_concat_seg)
514 , nvl(inv_category_set_id, a.inv_category_set_id)
515 , nvl(inv_category_id, a.inv_category_id)
516 , nvl(inv_concat_seg, a.inv_concat_seg)
517 , nvl(vbh_category_set_id, a.vbh_category_set_id)
518 , nvl(vbh_category_id, a.vbh_category_id)
519 , nvl(vbh_concat_seg, a.vbh_concat_seg)
520 , nvl(item_catalog_group_id, a.item_catalog_group_id)
521 , nvl(primary_uom_code, a.primary_uom_code)
522 , nvl(unit_weight, a.unit_weight)
523 , nvl(unit_volume, a.unit_volume)
524 , nvl(weight_uom_code, a.weight_uom_code)
525 , nvl(volume_uom_code, a.volume_uom_code)
526 , nvl(eam_item_type, a.eam_item_type)
527 FROM eni_item_star_temp
528 WHERE a.inventory_item_id = inventory_item_id
529 AND a.organization_id = organization_id )
530 WHERE EXISTS( SELECT 'X' from eni_item_star_temp
531 WHERE a.inventory_item_id = inventory_item_id
532 AND a.organization_id = organization_id );
533
534 -- BIS_COLLECTION_UTILITIES.log('Rows updated from temp table:'||sql%rowcount);
535
536 -- BIS_COLLECTION_UTILITIES.log('Deleting from the temp table');
537 DELETE FROM eni_item_star_temp;
538
539 END IF;
540
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 null;
544 -- WHEN OTHERS THEN
545 -- BIS_COLLECTION_UTILITIES.log(sqlerrm);
546 END;
547
548 END IF; -- l_full_refresh = 'Y'
549
550 IF l_full_refresh = 'N' THEN
551 -- BIS_COLLECTION_UTILITIES.log('');
552 -- BIS_COLLECTION_UTILITIES.log('Running in partial mode');
553 -- BIS_COLLECTION_UTILITIES.log('-----------------------');
554 -- BIS_COLLECTION_UTILITIES.log('');
555
556 INSERT INTO eni_oltp_item_star (
557 id
558 , value
559 , inventory_item_id
560 , organization_id
561 , po_category_id
562 , po_category_set_id
563 , po_concat_seg
564 , inv_category_id
565 , inv_category_set_id
566 , inv_concat_seg
567 , vbh_category_id
568 , vbh_category_set_id
569 , vbh_concat_seg
570 , master_id
571 , item_catalog_group_id
572 , primary_uom_code
573 , unit_weight
574 , unit_volume
575 , weight_uom_code
576 , volume_uom_code
577 , eam_item_type
578 , creation_date
579 , last_update_date
580 )
581 SELECT
582 mti.inventory_item_id || '-' || mti.organization_id,
583 mti.concatenated_segments || '(' || mtp.organization_code || ')',
584 mti.inventory_item_id,
585 mti.organization_id,
586 nvl(mic2.category_id, -1) po_category_id,
587 nvl(mic2.category_set_id, l_po_category_set) po_category_set_id,
588 nvl(kfv2.concatenated_segments, 'Unassigned') po_concat_seg,
589 nvl(mic.category_id,-1) inv_category_id,
590 nvl(mic.category_set_id, l_inv_category_set) inv_category_set_id,
591 nvl(kfv.concatenated_segments,'Unassigned') inv_concat_seg,
592 nvl(mic1.category_id, -1) vbh_category_id,
593 nvl(mic1.category_set_id, l_vbh_category_set) vbh_category_set_id,
597 master_id,
594 nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
595 decode(mti.organization_id,mtp.master_organization_id,null,
596 mti.inventory_item_id || '-' || mtp.master_organization_id)
598 nvl(item_catalog_group_id,-1) item_catalog_group_id,
599 mti.primary_uom_code,
600 mti.unit_weight,
601 mti.unit_volume,
602 mti.weight_uom_code,
603 mti.volume_uom_code,
604 mti.eam_item_type,
605 mti.creation_date,
606 mti.last_update_date
607 FROM
608 mtl_system_items_b_kfv mti,
609 mtl_parameters mtp,
610 mtl_item_categories mic,
611 mtl_item_categories mic1,
612 mtl_item_categories mic2,
613 mtl_categories_b_kfv kfv,
614 mtl_categories_b_kfv kfv1,
615 mtl_categories_b_kfv kfv2
616 WHERE
617 mtp.organization_id = mti.organization_id AND
618 mic.organization_id(+) = mti.organization_id AND
619 mic.inventory_item_id(+) = mti.inventory_item_id AND
620 mic.category_id = kfv.category_id(+) AND
621 mic.category_set_id(+) = l_inv_category_set AND
622 mic1.organization_id(+) = mti.organization_id AND
623 mic1.inventory_item_id(+) = mti.inventory_item_id AND
624 mic1.category_id = kfv1.category_id(+) AND
625 mic1.category_set_id(+) = l_vbh_category_set AND
626 mic2.organization_id(+) = mti.organization_id AND
627 mic2.inventory_item_id(+) = mti.inventory_item_id AND
628 mic2.category_id = kfv2.category_id (+) AND
629 mic2.category_set_id(+) = l_po_category_set AND
630 NOT EXISTS(SELECT 'X' FROM eni_oltp_item_star eni
631 WHERE mti.inventory_item_id = eni.inventory_item_id
632 AND mti.organization_id = eni.organization_id) AND
633 NOT EXISTS(SELECT 'X' FROM eni_item_star_valid_err err
634 WHERE mti.inventory_item_id = err.inventory_item_id
635 AND mti.organization_id = err.organization_id
636 );
637
638 l_rows_inserted := SQL%ROWCOUNT;
639
640 -- BIS_COLLECTION_UTILITIES.log('Records inserted into STAR table: '|| l_rows_inserted);
641
642 END IF; -- if l_full_refresh = 'N'
643
644 -- BIS_COLLECTION_UTILITIES.log('Collection completed successfully.');
645
646 -- BIS_COLLECTION_UTILITIES.log('Gathering statistics on table: ENI_OLTP_ITEM_STAR ');
647 FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_OLTP_ITEM_STAR');
648
649 Exception
650 When no_data_found then
651 -- BIS_COLLECTION_UTILITIES.log(sqlerrm);
652 errbuf := 'Error: No Data Found';
653 retcode := 2;
654 When unique_cons_violation then
655 -- BIS_COLLECTION_UTILITIES.log('Error: ' || sqlerrm );
656 -- BIS_COLLECTION_UTILITIES.log('Could be for one of two possible reasons: ');
657 -- BIS_COLLECTION_UTILITIES.log('1. Items cannot be assigned to multiple categories of a default category set');
658 -- BIS_COLLECTION_UTILITIES.log('2. The item flexfields have not been compiled');
659 errbuf := 'Error: ' || sqlerrm;
660 --dbms_output.put_line('Items cannot be assigned to mul...');
661 retcode := 2;
662 When others then
663 -- BIS_COLLECTION_UTILITIES.log(sqlerrm);
664 --dbms_output.put_line('Error '|| sqlerrm);
665 errbuf := 'Error: ' || sqlerrm;
666 retcode := 2;
667 END Create_Star_Table;
668
669
670 --**********************************************************************
671 -- Check if STAR table should be synchronized with Item Master when there
672 -- are Item Master Updates.
673 --**********************************************************************
674 FUNCTION Sync_Star_Items RETURN BOOLEAN IS
675 l_sync_star_items VARCHAR2(10) := 'NO SYNC';
676 BEGIN
677
678 -- If we've already performed this check within this session,
679 -- just return the cached result
680 -- IF G_SYNC_STAR_ITEMS = 'SYNC' THEN RETURN true;
681 -- ELSIF G_SYNC_STAR_ITEMS = 'NO_SYNC' THEN RETURN false;
682 -- END IF;
683
684 -- Check if UNASSIGNED row exists
685 SELECT 'SYNC'
686 INTO l_sync_star_items
687 FROM eni_oltp_item_star
688 WHERE inventory_item_id = -1
689 AND organization_id = -99;
690
691 -- Cache the result of the above UNASSIGNED row check for the session
692 --G_SYNC_STAR_ITEMS := 'SYNC';
693 RETURN true;
694
695 EXCEPTION
696 WHEN NO_DATA_FOUND THEN
697 -- Cache the result of the above UNASSIGNED row check for the session
698 --G_SYNC_STAR_ITEMS := 'NO_SYNC';
702
699 RETURN false;
700
701 END Sync_Star_Items;
703
704
705 --**********************************************************************
706 -- Maintain STAR table when changes are detected on MTL_SYSTEM_ITEMS
707 --**********************************************************************
708
709 --Start :5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
710 FUNCTION Get_Item_Number(P_Inventory_Item_Id MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE
711 ,P_Organization_Id MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE)
712 RETURN VARCHAR2 IS
713 l_delimiter VARCHAR2(10);
714 l_segs FND_FLEX_EXT.SegmentArray;
715 l_n_segs NUMBER;
716 l_item_exist BOOLEAN;
717 l_concat_segs VARCHAR2(1000);
718
719
720 BEGIN
721 -- Get delimiter
722 l_delimiter := fnd_flex_ext.get_delimiter(application_short_name => 'INV'
723 ,key_flex_code => 'MSTK'
724 ,structure_number => 101);
725 -- Get segments
726 l_item_exist := fnd_flex_ext.get_segments(application_short_name => 'INV'
727 ,key_flex_code => 'MSTK'
728 ,structure_number => 101
729 ,combination_id => P_Inventory_Item_Id
730 ,n_segments => l_n_segs
731 ,segments => l_segs
732 ,data_set => P_Organization_Id);
733 -- Get concatenated segments
734 IF l_item_exist THEN
735 l_concat_segs := fnd_flex_ext.concatenate_segments(n_segments => l_n_segs
736 ,segments => l_segs
737 ,delimiter => l_delimiter);
738 END IF;
739
740 RETURN l_concat_segs;
741 EXCEPTION
742 WHEN OTHERS THEN
743 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
744 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'ENI_ITEMS_STAR_PKG.GET_CONCAT_SEGS', SQLERRM);
745 END IF;
746 RAISE;
747 END Get_Item_Number;
748 --End :5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
749
750 PROCEDURE Insert_Items_In_Star( p_api_version NUMBER
751 , p_init_msg_list VARCHAR2 := 'F'
752 , p_inventory_item_id NUMBER
753 , p_organization_id NUMBER
754 , x_return_status OUT NOCOPY VARCHAR2
755 , x_msg_count OUT NOCOPY NUMBER
756 , x_msg_data OUT NOCOPY VARCHAR2 )
757 IS
758 CURSOR get_po_catset IS
759 SELECT category_set_id
760 FROM mtl_default_category_sets
761 WHERE functional_area_id = 2;
762
763 CURSOR category_rec IS
764 SELECT category_set_id
765 FROM mtl_default_category_sets
766 WHERE functional_area_id = 1;
767
768 l_inv_category_set NUMBER;
769 l_vbh_category_set NUMBER;
770 l_po_category_set NUMBER;
771 l_item_number VARCHAR2(1000);
772 BEGIN
773
774 -- Check if this synchronization should happen; if no, exit; if yes, continue
775 if Sync_Star_Items = false then
776 X_RETURN_STATUS := 'S';
777 return;
778 end if;
779
780 OPEN category_rec;
781 FETCH category_rec into l_inv_category_set;
782
783 IF category_rec%NOTFOUND THEN
784 l_inv_category_set := null;
785 END IF;
786 CLOSE category_rec;
787
788 l_vbh_category_set := ENI_DENORM_HRCHY.get_category_set_id;
789
790 OPEN get_po_catset;
791 FETCH get_po_catset INTO l_po_category_set;
792 CLOSE get_po_catset;
793
794 --5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
795 IF G_INSTALL_PHASE = 0 THEN
796 l_item_number := Get_Item_Number(P_Inventory_Item_Id => p_inventory_item_id
797 ,P_Organization_Id => p_organization_id);
798 END IF;
799
800 -- Insert Item
801
802 --dbms_output.put_line('Inserting into table...');
803 INSERT INTO ENI_OLTP_ITEM_STAR (
804 id
805 , value
806 , inventory_item_id
807 , organization_id
808 , master_id
809 , item_catalog_group_id
810 , primary_uom_code
811 , unit_weight
812 , unit_volume
813 , weight_uom_code
814 , volume_uom_code
815 , eam_item_type
816 , po_category_id
817 , po_category_set_id
818 , po_concat_seg
819 , inv_category_id
820 , inv_category_set_id
821 , inv_concat_seg
822 , vbh_category_id
823 , vbh_category_set_id
824 , vbh_concat_seg
825 , creation_date
826 , last_update_date
827 )
828 SELECT
829 mti.inventory_item_id || '-' || mti.organization_id,
830 DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number || ' (' || mtp.organization_code || ')',mti.concatenated_segments || ' (' || mtp.organization_code || ')'),
831 mti.inventory_item_id,
832 mti.organization_id,
833 decode( mti.organization_id,mtp.master_organization_id, null,
834 mti.inventory_item_id || '-' || mtp.master_organization_id ),
835 nvl(mti.item_catalog_group_id,-1),
836 mti.primary_uom_code,
837 mti.unit_weight,
838 mti.unit_volume,
839 mti.weight_uom_code,
840 mti.volume_uom_code,
841 mti.eam_item_type,
842 -1,
843 l_po_category_set,
847 'Unassigned',
844 'Unassigned',
845 -1,
846 l_inv_category_set,
848 -1,
849 l_vbh_category_set,
850 'Unassigned',
851 mti.creation_date,
852 mti.last_update_date
853 FROM mtl_system_items_b_kfv mti,
854 mtl_parameters mtp
855 WHERE mti.inventory_item_id = p_inventory_item_id
856 AND mti.organization_id = p_organization_id
857 AND mti.organization_id= mtp.organization_id;
858
859 X_RETURN_STATUS := 'S';
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 X_RETURN_STATUS := 'U';
864 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
865 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'INSERT_ITEMS_IN_STAR', SQLERRM);
866 END IF;
867 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
868
869 END Insert_Items_In_Star;
870
871 PROCEDURE Delete_Items_In_Star( p_api_version NUMBER
872 , p_init_msg_list VARCHAR2 := 'F'
873 , p_inventory_item_id NUMBER
874 , p_organization_id NUMBER
875 , x_return_status OUT NOCOPY VARCHAR2
876 , x_msg_count OUT NOCOPY NUMBER
877 , x_msg_data OUT NOCOPY VARCHAR2 )
878 IS
879 BEGIN
880
881 -- Delete Item
882
883 DELETE FROM ENI_OLTP_ITEM_STAR
884 WHERE inventory_item_id = p_inventory_item_id
885 AND organization_id = p_organization_id;
886
887 X_RETURN_STATUS := 'S';
888
889 EXCEPTION
890 WHEN OTHERS THEN
891 X_RETURN_STATUS := 'U';
892 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
893 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'DELETE_ITEMS_IN_STAR', SQLERRM);
894 END IF;
895 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
896
897 END Delete_Items_In_Star;
898
899 /* start fix for bug 16179463*/
900 /* function to check whether item_number field is changed for item or not.*/
901 FUNCTION is_item_number_changed(p_inventory_item_id NUMBER
902 ,p_organization_id NUMBER)
903 RETURN BOOLEAN
904 IS
905 l_prev_item_number eni_oltp_item_star.Value%TYPE;
906 l_pres_item_number mtl_system_items_b_kfv.concatenated_segments%TYPE;
907
908 BEGIN
909
910 BEGIN
911 SELECT msik.concatenated_segments || ' (' || mp.organization_code || ')' Value INTO l_pres_item_number
912 FROM mtl_system_items_b_kfv msik,
913 mtl_parameters mp
914 WHERE msik.inventory_item_id=p_inventory_item_id
915 AND msik.organization_id=mp.organization_id
916 AND mp.organization_id=p_organization_id;
917 EXCEPTION
918 WHEN Others THEN
919 /* If mtl_system_items_b_kfv is not generated properly there could be a chance of exception here*/
920 /* log error raised here */
921 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
922 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'IS_ITEM_NUMBER_CHANGED', SQLERRM);
923 END IF;
924 /* raise exception here to notify the calling procedure */
925 RAISE;
926 END;
927
928 BEGIN
929 SELECT Value INTO l_prev_item_number FROM eni_oltp_item_star
930 WHERE inventory_item_id=p_inventory_item_id
931 AND organization_id=p_organization_id;
932
933 EXCEPTION
934 WHEN No_Data_Found THEN
935 /*It means there is no data exists in ENI_OLTP_ITEM_STAR, may be load truncated the table
936 It is safe to update table ENI_OLTP_ITEM_STAR with values from MTL_SYSTEM_ITEMS_B_KFV
937 */
938 RETURN TRUE ;
939 END;
940
941 IF (l_prev_item_number=l_pres_item_number) THEN
942 RETURN FALSE;
943 ELSE
944 RETURN TRUE ;
945 END IF;
946 END is_item_number_changed;
947
948 /*This procedure will update the Item number for all Remaining master orgs and their hierarchies
949 in which item exists. */
950 PROCEDURE Update_Items_In_Rem_Mstr_Orgs( p_api_version NUMBER
951 , p_init_msg_list VARCHAR2 := 'F'
952 , p_inventory_item_id NUMBER
953 , p_organization_id NUMBER
954 , x_return_status OUT NOCOPY VARCHAR2
955 , x_msg_count OUT NOCOPY NUMBER
956 , x_msg_data OUT NOCOPY VARCHAR2 )
957 IS
958 /* change in item_number should be reflected for items in all orgs.
959 Below logic will update only value field for the items of remaining master orgs and thier hierarchy.
960 */
961 CURSOR c_items_in_master IS
962 SELECT mti.concatenated_segments || ' (' || b.organization_code || ')' value
963 , b.organization_id
964 , mti.last_update_date
965 , nvl(mti.item_catalog_group_id,-1) item_catalog_group_id
966 , mti.primary_uom_code
967 ,mti.unit_weight
968 ,mti.unit_volume
969 ,mti.weight_uom_code
970 ,mti.volume_uom_code
971 FROM mtl_system_items_b_kfv mti
972 , mtl_parameters b
973 WHERE mti.inventory_item_id = p_inventory_item_id
974 AND mti.organization_id = b.organization_id
975 AND b.master_organization_id <> p_organization_id;
976
977 BEGIN
978 FOR c_items_in_master_rec IN c_items_in_master
979 LOOP
980
981 UPDATE ENI_OLTP_ITEM_STAR
985 AND organization_id = c_items_in_master_rec.organization_id;
982 SET VALUE = c_items_in_master_rec.value
983 , LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
984 WHERE inventory_item_id = p_inventory_item_id
986
987 -- dbms_output.put_line('before sql rowcount');
988
989 -- The following block will only be called when an
990 -- user updates an item when the load is running. Since
991 -- the load truncates the table, the update will not go
992 -- thru. So it is stored temporarily into a TEMP table.
993
994 -- There is a separate insert and an update because the
995 -- the user can modify the same item twice while the load
996 -- is running. This would create duplicate records in the
997 -- TEMP table. To avoid duplicacy there is a insert and an
998 -- update.
999
1000 IF sql%rowcount = 0 THEN
1001 UPDATE eni_item_star_temp
1002 SET VALUE = c_items_in_master_rec.value
1003 , LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
1004 WHERE inventory_item_id = p_inventory_item_id
1005 AND organization_id = c_items_in_master_rec.organization_id;
1006
1007 IF sql%rowcount = 0 THEN
1008
1009 INSERT INTO eni_item_star_temp( inventory_item_id
1010 , organization_id
1011 , value
1012 , last_update_date
1013 , item_catalog_group_id
1014 , primary_uom_code
1015 , unit_weight
1016 , unit_volume
1017 ,weight_uom_code
1018 ,volume_uom_code)
1019 VALUES(
1020 p_inventory_item_id
1021 , c_items_in_master_rec.organization_id
1022 , c_items_in_master_rec.value
1023 , c_items_in_master_rec.last_update_date
1024 , c_items_in_master_rec.item_catalog_group_id
1025 , c_items_in_master_rec.primary_uom_code
1026 , c_items_in_master_rec.unit_weight
1027 , c_items_in_master_rec.unit_volume
1028 , c_items_in_master_rec.weight_uom_code
1029 , c_items_in_master_rec.volume_uom_code
1030 );
1031 END IF;
1032 END IF;
1033
1034 END LOOP;
1035
1036 X_RETURN_STATUS := 'S';
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 X_RETURN_STATUS := 'U';
1041 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1042 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'UPDATE_ITEMS_IN_REM_MSTR_ORGS', SQLERRM);
1043 END IF;
1044 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
1045
1046 END Update_Items_In_Rem_Mstr_Orgs;
1047
1048
1049 /* end fix for bug 16179463 */
1050
1051 -- Contains Bug fix 4173443.
1052 /* If organization is master then update items in master org & child org
1053 with entries from MSIB. If the organization is child org then
1054 update all the attributes in Items_star with entries in MISB.
1055 We don't have to find out if the attributes are master controlled or
1056 org controlled as Items takes care of this.
1057 */
1058
1059 PROCEDURE Update_Items_In_Star( p_api_version NUMBER
1060 , p_init_msg_list VARCHAR2 := 'F'
1061 , p_inventory_item_id NUMBER
1062 , p_organization_id NUMBER
1063 , x_return_status OUT NOCOPY VARCHAR2
1064 , x_msg_count OUT NOCOPY NUMBER
1065 , x_msg_data OUT NOCOPY VARCHAR2 )
1066 IS
1067 -- updates to master-level attributes must capture the resulting propagations to child orgs
1068 CURSOR c_items_in_master IS
1069 SELECT mti.concatenated_segments || ' (' || b.organization_code || ')' value
1070 , b.organization_code
1071 , b.organization_id
1072 , mti.last_update_date
1073 , nvl(mti.item_catalog_group_id,-1) item_catalog_group_id
1074 , mti.primary_uom_code
1075 ,mti.unit_weight
1076 ,mti.unit_volume
1077 ,mti.weight_uom_code
1078 ,mti.volume_uom_code
1079 ,mti.eam_item_type
1080 FROM mtl_system_items_b_kfv mti
1081 , mtl_parameters b
1082 WHERE mti.inventory_item_id = p_inventory_item_id
1083 AND mti.organization_id = b.organization_id
1084 AND b.master_organization_id = p_organization_id;
1085 -- updates to child-org-level attributes are confined to that organization
1086
1087 CURSOR c_items_in_child IS
1088 SELECT mti.organization_id
1089 , mti.unit_weight
1090 , mti.unit_volume
1091 , mti.weight_uom_code
1092 , mti.volume_uom_code
1093 , primary_uom_code
1094 , eam_item_type
1095 , mti.last_update_date
1096 FROM mtl_system_items_b mti
1097 WHERE mti.inventory_item_id = p_inventory_item_id
1098 AND mti.organization_id = p_organization_id;
1099
1103
1100 isMasterOrg NUMBER;
1101 l_item_number VARCHAR2(1000);
1102 BEGIN
1104 -- Check if this synchronization should happen; if no, exit; if yes, continue
1105 if Sync_Star_Items = false then
1106 X_RETURN_STATUS := 'S';
1107 return;
1108 end if;
1109
1110 isMasterOrg := 0;
1111
1112 /* start fix for bug 16179463 */
1113 /* If Item number field is changed for the item for given p_inventory_item_id
1114 then call the procedure Update_Items_In_Rem_Mstr_Orgs() to change the item
1115 in all remaining Master orgs and their hierarchies where it is assigned
1116 Eg:
1117 Item123 created in two master orgs V1 and PM.
1118 Item assigned to child orgs M1(child of V1) and P2(child of PM).
1119 If item number changed in V1, then it also need to be changed in V1 hierarchy (i.e M1) and PM hierarchy (i.e P2). */
1120 IF is_item_number_changed(p_inventory_item_id,p_organization_id)=TRUE THEN
1121
1122 Update_Items_In_Rem_Mstr_Orgs(p_api_version => p_api_version
1123 ,p_init_msg_list => p_init_msg_list
1124 ,p_inventory_item_id => p_inventory_item_id
1125 ,p_organization_id => p_organization_id
1126 ,x_return_status => x_return_status
1127 ,x_msg_count => x_msg_count
1128 ,x_msg_data => x_msg_data );
1129 END IF;
1130 /* end fix for bug 16179463 */
1131
1132 SELECT COUNT(master_organization_id) INTO isMasterOrg
1133 FROM mtl_parameters
1134 WHERE master_organization_id = p_organization_id AND ROWNUM < 2;
1135
1136 IF isMasterOrg = 1 THEN
1137 FOR c_items_in_master_rec IN c_items_in_master
1138 LOOP
1139 --5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
1140 --FND API is caches the values..get_item_number should be used only during install phase.
1141 IF G_INSTALL_PHASE = 0 THEN
1142 l_item_number := Get_Item_Number(P_Inventory_Item_Id => p_inventory_item_id
1143 ,P_Organization_Id => c_items_in_master_rec.organization_id);
1144
1145 l_item_number := l_item_number || ' (' || c_items_in_master_rec.organization_code || ')';
1146 END IF;
1147
1148 UPDATE ENI_OLTP_ITEM_STAR
1149 SET VALUE = DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number,c_items_in_master_rec.value)
1150 , ITEM_CATALOG_GROUP_ID = c_items_in_master_rec.item_catalog_group_id
1151 , PRIMARY_UOM_CODE = c_items_in_master_rec.primary_uom_code
1152 , LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
1153 , UNIT_WEIGHT = c_items_in_master_rec.unit_weight
1154 , UNIT_VOLUME = c_items_in_master_rec.unit_volume
1155 , WEIGHT_UOM_CODE = c_items_in_master_rec.weight_uom_code
1156 , VOLUME_UOM_CODE = c_items_in_master_rec.volume_uom_code
1157 , EAM_ITEM_TYPE = c_items_in_master_rec.eam_item_type
1158 WHERE inventory_item_id = p_inventory_item_id
1159 AND organization_id = c_items_in_master_rec.organization_id;
1160
1161 -- The following block will only be called when an
1162 -- user updates an item when the load is running. Since
1163 -- the load truncates the table, the update will not go
1164 -- thru. So it is stored temporarily into a TEMP table.
1165
1166 -- There is a separate insert and an update because the
1167 -- the user can modify the same item twice while the load
1168 -- is running. This would create duplicate records in the
1169 -- TEMP table. To avoid duplicacy there is a insert and an update.
1170
1171 IF sql%rowcount = 0 THEN
1172 UPDATE eni_item_star_temp
1173 SET VALUE = DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number,c_items_in_master_rec.value)
1174 , LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
1175 , ITEM_CATALOG_GROUP_ID = c_items_in_master_rec.item_catalog_group_id
1176 , PRIMARY_UOM_CODE = c_items_in_master_rec.primary_uom_code
1177 , UNIT_WEIGHT = c_items_in_master_rec.unit_weight
1178 , UNIT_VOLUME = c_items_in_master_rec.unit_volume
1179 , WEIGHT_UOM_CODE = c_items_in_master_rec.weight_uom_code
1180 , VOLUME_UOM_CODE = c_items_in_master_rec.volume_uom_code
1181 , EAM_ITEM_TYPE = c_items_in_master_rec.eam_item_type
1182 WHERE inventory_item_id = p_inventory_item_id
1183 AND organization_id = c_items_in_master_rec.organization_id;
1184
1185 IF sql%rowcount = 0 THEN
1186 INSERT INTO eni_item_star_temp(
1187 inventory_item_id
1188 , organization_id
1189 , value
1190 , last_update_date
1191 , item_catalog_group_id
1192 , primary_uom_code
1193 , unit_weight
1194 , unit_volume
1195 ,weight_uom_code
1196 ,volume_uom_code
1197 ,eam_item_type)
1198 VALUES(
1199 p_inventory_item_id
1200 , c_items_in_master_rec.organization_id
1201 , DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number,c_items_in_master_rec.value)
1202 , c_items_in_master_rec.last_update_date
1203 , c_items_in_master_rec.item_catalog_group_id
1204 , c_items_in_master_rec.primary_uom_code
1205 , c_items_in_master_rec.unit_weight
1206 , c_items_in_master_rec.unit_volume
1207 , c_items_in_master_rec.weight_uom_code
1211 END IF;
1208 , c_items_in_master_rec.volume_uom_code
1209 , c_items_in_master_rec.eam_item_type);
1210 END IF;
1212 END LOOP;
1213
1214 ELSE --- Update done in Child Org
1215
1216 FOR c_items_in_child_rec IN c_items_in_child
1217 LOOP
1218 UPDATE eni_oltp_item_star
1219 SET UNIT_WEIGHT = c_items_in_child_rec.unit_weight
1220 , UNIT_VOLUME = c_items_in_child_rec.unit_volume
1221 , WEIGHT_UOM_CODE = c_items_in_child_rec.weight_uom_code
1222 , VOLUME_UOM_CODE = c_items_in_child_rec.volume_uom_code
1223 , LAST_UPDATE_DATE = c_items_in_child_rec.last_update_date
1224 , PRIMARY_UOM_CODE = c_items_in_child_rec.primary_uom_code
1225 , EAM_ITEM_TYPE = c_items_in_child_rec.eam_item_type
1226 WHERE inventory_item_id = p_inventory_item_id
1227 AND organization_id = c_items_in_child_rec.organization_id;
1228
1229 -- The following block will only be called when an
1230 -- user updates an item when the load is running. Since
1231 -- the load truncates the table, the update will not go
1232 -- thru. So it is stored temporarily into a TEMP table.
1233
1234 -- There is a separate insert and an update because the
1235 -- the user can modify the same item twice while the load
1236 -- is running. This would create duplicate records in the
1237 -- TEMP table. To avoid duplicacy there is a insert and an update.
1238
1239 IF sql%rowcount = 0 THEN
1240 UPDATE eni_item_star_temp
1241 SET UNIT_WEIGHT = c_items_in_child_rec.unit_weight
1242 , UNIT_VOLUME = c_items_in_child_rec.unit_volume
1243 , WEIGHT_UOM_CODE = c_items_in_child_rec.weight_uom_code
1244 , VOLUME_UOM_CODE = c_items_in_child_rec.volume_uom_code
1245 , LAST_UPDATE_DATE = c_items_in_child_rec.last_update_date
1246 , PRIMARY_UOM_CODE = c_items_in_child_rec.primary_uom_code
1247 , EAM_ITEM_TYPE = c_items_in_child_rec.eam_item_type
1248 WHERE inventory_item_id = p_inventory_item_id
1249 AND organization_id = c_items_in_child_rec.organization_id;
1250
1251 IF sql%rowcount = 0 THEN
1252 INSERT INTO eni_item_star_temp(
1253 inventory_item_id
1254 , organization_id
1255 , last_update_date
1256 , unit_weight
1257 , unit_volume
1258 , weight_uom_code
1259 , volume_uom_code
1260 , primary_uom_code
1261 , eam_item_type)
1262 VALUES(
1263 p_inventory_item_id
1264 , c_items_in_child_rec.organization_id
1265 , c_items_in_child_rec.last_update_date
1266 , c_items_in_child_rec.unit_weight
1267 , c_items_in_child_rec.unit_volume
1268 , c_items_in_child_rec.weight_uom_code
1269 , c_items_in_child_rec.volume_uom_code
1270 , c_items_in_child_rec.primary_uom_code
1271 , c_items_in_child_rec.eam_item_type);
1272 END IF;
1273 END IF;
1274 END LOOP;
1275 END IF;
1276
1277 X_RETURN_STATUS := 'S';
1278
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 X_RETURN_STATUS := 'U';
1282 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1283 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'UPDATE_ITEMS_IN_STAR', SQLERRM);
1284 END IF;
1285 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
1286
1287 END Update_Items_In_Star;
1288
1289
1290 --**********************************************************************
1291 -- Maintains STAR table when changes are detected on MTL_CATEGORIES
1292 --**********************************************************************
1293
1294 PROCEDURE Update_Categories( p_api_version NUMBER
1295 , p_init_msg_list VARCHAR2 := 'F'
1296 , p_category_id NUMBER
1297 , p_structure_id NUMBER
1298 , x_return_status OUT NOCOPY VARCHAR2
1299 , x_msg_count OUT NOCOPY NUMBER
1300 , x_msg_data OUT NOCOPY VARCHAR2 )
1301 IS
1302 which_category_set VARCHAR2(15);
1303 l_category_set_id number;
1304 BEGIN
1305
1306
1307 -- Check if this synchronization should happen; if no, exit; if yes, continue
1308 if Sync_Star_Items = false then
1309 X_RETURN_STATUS := 'S';
1310 return;
1311 end if;
1312
1313 begin
1314 -- Which category set does assignment belong in ?
1315
1316 SELECT 'INV_CATEGORY' INTO which_category_set
1317 FROM mtl_default_category_sets a, mtl_category_sets_b b
1318 WHERE a.functional_area_id = 1
1319 AND a.category_set_id = b.category_set_id
1320 AND b.structure_id = p_structure_id;
1321
1322 exception
1323 when no_data_found then
1324 begin
1325 l_category_set_id := ENI_DENORM_HRCHY.get_category_set_id;
1326
1327 select 'VBH_CATEGORY' into which_category_set
1328 from mtl_category_Sets_b
1329 where structure_id = p_structure_id
1330 and category_Set_id = l_category_set_id;
1331 exception
1332 when no_data_found then
1333 which_category_set := 'NONE';
1334 end;
1335 end;
1336
1337 -- Update Item-Category Assignment
1341 THEN
1338
1339 /* Commented out as fix for Bug 3600364
1340 IF which_category_set = 'VBH_CATEGORY' and l_category_set_id = 1000000006
1342 UPDATE ENI_OLTP_ITEM_STAR
1343 SET VBH_CATEGORY_ID = -1
1344 ,VBH_CONCAT_SEG = 'Unassigned'
1345 WHERE vbh_category_id = p_category_id
1346 AND VBH_CONCAT_SEG <> (SELECT CONCATENATED_SEGMENTS
1347 FROM MTL_CATEGORIES_KFV
1348 WHERE CATEGORY_ID = p_category_id);
1349 */
1350 IF which_category_set = 'VBH_CATEGORY'
1351 THEN
1352 UPDATE ENI_OLTP_ITEM_STAR
1353 SET VBH_CONCAT_SEG =
1354 (select concatenated_segments
1355 from mtl_categories_b_kfv
1356 where category_id = p_category_id)
1357 WHERE vbh_category_id = p_category_id;
1358 ELSIF which_category_set = 'INV_CATEGORY'
1359 THEN
1360 UPDATE ENI_OLTP_ITEM_STAR
1361 SET INV_CONCAT_SEG =
1362 (select concatenated_segments
1363 from mtl_categories_b_kfv
1364 where category_id = p_category_id)
1365 WHERE inv_category_id = p_category_id;
1366 END IF;
1367
1368 X_RETURN_STATUS := 'S';
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 X_RETURN_STATUS := 'U';
1373 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1374 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'UPDATE_CATEGORIES', SQLERRM);
1375 END IF;
1376 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
1377
1378 END Update_Categories;
1379
1380
1381 --**********************************************************************
1382 -- Maintains STAR table when changes are detected on MTL_ITEM_CATEGORIES
1383 --**********************************************************************
1384
1385 PROCEDURE Sync_Category_Assignments ( p_api_version NUMBER
1386 , p_init_msg_list VARCHAR2 := 'F'
1387 , p_inventory_item_id NUMBER
1388 , p_organization_id NUMBER
1389 , x_return_status OUT NOCOPY VARCHAR2
1390 , x_msg_count OUT NOCOPY NUMBER
1391 , x_msg_data OUT NOCOPY VARCHAR2 )
1392 IS
1393 l_INV_category_set_id number;
1394 l_VBH_category_set_id number;
1395 l_PO_category_set_id number;
1396 l_old_category_id number;
1397 l_new_category_id number;
1398 l_return_status varchar2(1);
1399 l_msg_count number;
1400 l_msg_data varchar2(1000);
1401 l_eni_table_exists number;
1402
1403 cursor c1(p_inventory_item_id number, p_organization_id number) is
1404 SELECT msi.organization_id,
1405 nvl(mic.category_id, -1) inv_category_id,
1406 nvl(kfv.concatenated_segments, 'Unassigned') inv_concat_seg,
1407 nvl(mic.category_Set_id, l_INV_category_set_id) inv_category_Set_id,
1408 nvl(mic1.category_id, -1) vbh_category_id,
1409 nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
1410 nvl(mic1.category_Set_id, l_VBH_category_set_id) vbh_category_set_id,
1411 nvl(mic2.category_id, -1) po_category_id,
1412 nvl(kfv2.concatenated_segments, 'Unassigned') po_concat_seg,
1413 nvl(mic2.category_Set_id, l_PO_category_set_id) po_category_set_id
1414 FROM
1415 mtl_system_items_b msi
1416 , mtl_item_categories mic
1417 , mtl_categories_b_kfv kfv
1418 , mtl_item_categories mic1
1419 , mtl_categories_b_kfv kfv1
1420 , mtl_item_categories mic2
1421 , mtl_categories_b_kfv kfv2
1422 WHERE
1423 msi.inventory_item_id = p_inventory_item_id
1424 AND (msi.organization_id = p_organization_id
1425 or msi.organization_id in (SELECT mp.organization_id
1426 FROM mtl_parameters mp
1427 WHERE
1428 mp.master_organization_id = p_organization_id))
1429 AND mic.inventory_item_id (+) = msi.inventory_item_id
1430 AND mic.organization_id (+) = msi.organization_id
1431 AND mic.category_id = kfv.category_id (+)
1432 AND mic.category_set_id (+) = l_INV_category_set_id
1433 AND mic1.inventory_item_id (+) = msi.inventory_item_id
1434 AND mic1.organization_id (+) = msi.organization_id
1435 AND mic1.category_id = kfv1.category_id (+)
1436 AND mic1.category_set_id (+) = l_VBH_category_set_id
1437 AND mic2.inventory_item_id (+) = msi.inventory_item_id
1438 AND mic2.organization_id (+) = msi.organization_id
1439 AND mic2.category_id = kfv2.category_id (+)
1440 AND mic2.category_set_id (+) = l_PO_category_set_id;
1441
1442 CURSOR get_po_catset IS
1443 SELECT category_set_id
1444 FROM mtl_default_category_sets
1445 WHERE functional_area_id = 2;
1446
1447 BEGIN
1448
1449
1450 -- Check if this synchronization should happen; if no, exit; if yes, continue
1451 if Sync_Star_Items = false then
1452 X_RETURN_STATUS := 'S';
1453 return;
1454 end if;
1455
1456 SELECT category_set_id
1457 INTO l_INV_category_set_id
1458 FROM mtl_default_category_sets
1459 WHERE functional_area_id = 1;
1460
1461 l_VBH_category_set_id := ENI_DENORM_HRCHY.get_category_set_id;
1462
1463 OPEN get_po_catset;
1467 FOR sync_c1 IN C1(p_inventory_item_id, p_organization_id)
1464 FETCH get_po_catset INTO l_po_category_set_id;
1465 CLOSE get_po_catset;
1466
1468 LOOP
1469
1470 UPDATE eni_oltp_item_star
1471 SET
1472 INV_CATEGORY_ID = sync_c1.inv_category_id,
1473 INV_CONCAT_SEG = sync_c1.inv_concat_seg,
1474 INV_CATEGORY_SET_ID = sync_c1.inv_category_set_id,
1475 VBH_CATEGORY_ID = sync_c1.vbh_category_id,
1476 VBH_CONCAT_SEG = sync_c1.vbh_concat_seg,
1477 VBH_CATEGORY_SET_ID = sync_c1.vbh_category_set_id,
1478 PO_CATEGORY_ID = sync_c1.po_category_id,
1479 PO_CONCAT_SEG = sync_c1.po_concat_seg,
1480 PO_CATEGORY_SET_ID = sync_c1.po_category_set_id
1481 WHERE inventory_item_id = p_inventory_item_id
1482 AND organization_id = sync_c1.organization_id;
1483
1484 -- dbms_output.put_line('after update star:'|| to_char(sql%rowcount));
1485 -- rowid = upd_item_star_rec.row_id;
1486
1487 -- This block will only be called when a user is updating
1488 -- an item category assignment when the load is running in
1489 -- parallel.
1490 -- This block will update into the temporary table while
1491 -- the STAR table is empty because of the load. The MERGE
1492 -- statement is written(instead of a single insert) to
1493 -- prevent duplicate records being inserted into the TEMP
1494 -- table. Duplicate records will be inserted if the user
1495 -- makes a change in the category assignment, then makes
1496 -- another change to the same assignment while the load
1497 -- is till running.
1498
1499 if Sql%Rowcount = 0 then
1500
1501 -- dbms_output.put_line('before update temp....');
1502
1503 UPDATE ENI_ITEM_STAR_TEMP
1504 set inv_category_set_id = sync_c1.inv_category_set_id,
1505 inv_category_id = sync_c1.inv_category_id,
1506 inv_concat_seg = sync_c1.inv_concat_seg,
1507 vbh_category_set_id = sync_c1.vbh_category_set_id,
1508 vbh_category_id = sync_c1.vbh_category_id,
1509 vbh_concat_seg = sync_c1.vbh_concat_seg,
1510 po_category_set_id = sync_c1.po_category_set_id,
1511 po_category_id = sync_c1.po_category_id,
1512 po_concat_seg = sync_c1.po_concat_seg
1513 where inventory_item_id = p_inventory_item_id
1514 and organization_id = sync_c1.organization_id;
1515
1516 -- dbms_output.put_line('After update temp..'||to_char(sql%rowcount));
1517
1518 if sql%rowcount = 0 then
1519 INSERT into ENI_ITEM_STAR_TEMP
1520 (inventory_item_id,
1521 organization_id,
1522 inv_category_set_id,
1523 inv_category_id,
1524 inv_concat_seg,
1525 vbh_category_set_id,
1526 vbh_category_id,
1527 vbh_concat_seg,
1528 po_category_set_id,
1529 po_category_id,
1530 po_concat_seg)
1531 VALUES (p_inventory_item_id,
1532 sync_c1.organization_id,
1533 sync_c1.inv_category_set_id,
1534 sync_c1.inv_category_id,
1535 sync_c1.inv_concat_seg,
1536 sync_c1.vbh_category_set_id,
1537 sync_c1.vbh_category_id,
1538 sync_c1.vbh_concat_seg,
1539 sync_c1.po_category_set_id,
1540 sync_c1.po_category_id,
1541 sync_c1.po_concat_seg);
1542
1543 -- dbms_output.put_line('After insert temp..'||to_char(sql%rowcount));
1544
1545 end if;
1546
1547 end if;
1548
1549 END LOOP; -- upd_item_star
1550
1551 -- IF l_eni_table_exists = 0 THEN
1552
1553 -- BEGIN -- Calling Denorm API to set the item_assgn_flag
1554
1555 -- dbms_output.put_line('in denorm API');
1556
1557 -- Select vbh_category_id into l_new_category_id
1558 -- from eni_oltp_item_star
1559 -- where inventory_item_id = p_inventory_item_id
1560 -- and organization_id = p_organization_id
1561 -- and rownum = 1;
1562
1563 -- IF l_old_category_id <> l_new_category_id THEN
1564
1565 -- dbms_output.put_line('Inside if old-new category');
1566
1567 -- ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG
1568 -- (p_new_category_id => l_new_category_id,
1569 -- p_old_category_id => l_old_category_id,
1570 -- x_return_status => l_return_status,
1571 -- x_msg_count => l_msg_count,
1572 -- x_msg_data => l_msg_data);
1573 -- END IF;
1574
1575 -- EXCEPTION
1576 -- WHEN no_data_found THEN
1577 -- null;
1578 -- END;
1579
1580 -- END IF;
1581
1582 if l_return_status = 'U' then
1583 X_RETURN_STATUS := l_return_status;
1584 X_MSG_COUNT := l_msg_count;
1585 X_MSG_DATA := l_msg_data;
1586 else
1587 X_RETURN_STATUS := 'S';
1588 end if;
1589
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 X_RETURN_STATUS := 'U';
1594 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1595 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1596 END IF;
1597 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
1598
1599 End Sync_Category_Assignments;
1600
1604
1601 --**************************************************************************
1602 -- Inserts/Updates De-normalized Item STAR table from Item Open Interface
1603 --**************************************************************************
1605 PROCEDURE Sync_Star_Items_From_IOI(p_api_version NUMBER,
1606 p_init_msg_list VARCHAR2 := 'F',
1607 p_set_process_id NUMBER,
1608 x_return_status OUT NOCOPY VARCHAR2,
1609 x_msg_count OUT NOCOPY NUMBER,
1610 x_msg_data OUT NOCOPY VARCHAR2)
1611 IS
1612
1613 CURSOR get_po_catset IS
1614 SELECT category_set_id
1615 FROM mtl_default_category_sets
1616 WHERE functional_area_id = 2;
1617
1618 l_inv_category_set number;
1619 l_vbh_category_set number;
1620 l_po_category_set number;
1621 l_user_id number;
1622 l_conc_request_id number;
1623 l_prog_appl_id number;
1624 l_conc_program_id number;
1625 l_count number;
1626 l_sql VARCHAR2(32000);
1627 l_rowcount number;
1628 l_child_set_id NUMBER;
1629 BEGIN
1630
1631 -- Check if this synchronization should happen; if no, exit; if yes, continue
1632 if Sync_Star_Items = false then
1633 X_RETURN_STATUS := 'S';
1634 return;
1635 end if;
1636
1637 l_child_set_id := p_set_process_id + 1000000000000;
1638
1639 l_vbh_category_set := ENI_DENORM_HRCHY.get_category_set_id;
1640 OPEN get_po_catset;
1641 FETCH get_po_catset INTO l_po_category_set;
1642 CLOSE get_po_catset;
1643
1644 l_user_id := FND_GLOBAL.USER_ID;
1645 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1646 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
1647 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1648
1649 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1650 FND_MSG_PUB.Initialize ;
1651 END IF;
1652
1653 SELECT category_set_id INTO l_inv_category_set
1654 FROM mtl_default_category_sets
1655 WHERE functional_area_id = 1;
1656
1657 -- Bug : 3671737 made changes to use Bind variables for local variables
1658 -- Bug 10404086 : Added below hint
1659 l_sql := 'MERGE INTO eni_oltp_item_star STAR
1660 USING (SELECT /*+ first_rows index(interface, MTL_SYSTEM_ITEMS_INTERFACE_N3) */
1661 item.inventory_item_id inventory_item_id,
1662 item.organization_id organization_id,
1663 item.CONCATENATED_SEGMENTS|| '' ('' || mtp.organization_code || '')''value,
1664 decode(item.organization_id,mtp.master_organization_id,null,
1665 item.inventory_item_id || ''-'' || mtp.master_organization_id)
1666 master_id,
1667 nvl(mic.category_id,-1) inv_category_id,
1668 nvl(mic.category_set_id, :l_inv_category_set) inv_category_set_id,
1669 nvl(kfv.concatenated_segments,''Unassigned'') inv_concat_seg,
1670 nvl(mic1.category_id,-1) vbh_category_id,
1671 nvl(mic1.category_set_id, :l_vbh_category_set) vbh_category_set_id,
1672 nvl(kfv1.concatenated_segments,''Unassigned'') vbh_concat_seg,
1673 nvl(mic2.category_id,-1) po_category_id,
1674 nvl(mic2.category_set_id, :l_po_category_set) po_category_set_id,
1675 nvl(kfv2.concatenated_segments,''Unassigned'') po_concat_seg,
1676 nvl(item.item_catalog_group_id,-1) item_catalog_group_id,
1677 item.primary_uom_code
1678 , item.unit_weight
1679 , item.unit_volume
1680 , item.weight_uom_code
1681 , item.volume_uom_code
1682 , item.eam_item_type
1683 , item.creation_date
1684 , item.last_update_date
1685 FROM mtl_system_items_interface interface
1686 , mtl_system_items_b_kfv item
1687 , mtl_parameters mtp
1688 , mtl_item_categories mic
1689 , mtl_categories_b_kfv kfv
1690 , mtl_item_categories mic1
1691 , mtl_categories_b_kfv kfv1
1692 , mtl_item_categories mic2
1693 , mtl_categories_b_kfv kfv2
1694 WHERE item.inventory_item_id = interface.inventory_item_id
1695 AND interface.set_process_id = :p_set_process_id
1696 AND interface.process_flag = 7
1697 AND item.organization_id = interface.organization_id
1698 AND item.organization_id= mtp.organization_id
1699 AND mic.organization_id(+) = item.organization_id
1700 AND mic.inventory_item_id(+) = item.inventory_item_id
1701 AND mic.category_id = kfv.category_id (+)
1702 and mic.category_set_id(+) = :l_inv_category_set
1703 AND mic1.organization_id(+) = item.organization_id
1704 AND mic1.inventory_item_id(+) = item.inventory_item_id
1705 AND mic1.category_id = kfv1.category_id (+)
1706 and mic1.category_set_id(+) = :l_vbh_category_set
1707 AND mic2.organization_id(+) = item.organization_id
1708 AND mic2.inventory_item_id(+) = item.inventory_item_id
1709 AND mic2.category_id = kfv2.category_id (+)
1710 and mic2.category_set_id(+) = :l_po_category_set) mti
1711 ON (STAR.inventory_item_id = mti.inventory_item_id
1712 AND STAR.organization_id = mti.organization_id)
1713 WHEN MATCHED THEN
1714 UPDATE SET STAR.value = mti.value
1715 , STAR.po_category_id = mti.po_category_id
1716 , STAR.po_category_set_id = mti.po_category_set_id
1720 , STAR.inv_concat_seg = mti.inv_concat_seg
1717 , STAR.po_concat_seg = mti.po_concat_seg
1718 , STAR.inv_category_id = mti.inv_category_id
1719 , STAR.inv_category_set_id = mti.inv_category_set_id
1721 , STAR.vbh_category_id = mti.vbh_category_id
1722 , STAR.vbh_category_set_id = mti.vbh_category_set_id
1723 , STAR.vbh_concat_seg = mti.vbh_concat_seg
1724 , STAR.master_id = mti.master_id
1725 , STAR.item_catalog_group_id = mti.item_catalog_group_id
1726 , STAR.primary_uom_code = mti.primary_uom_code
1727 , STAR.unit_weight = mti.unit_weight
1728 , STAR.unit_volume = mti.unit_volume
1729 , STAR.weight_uom_code = mti.weight_uom_code
1730 , STAR.volume_uom_code = mti.volume_uom_code
1731 , STAR.eam_item_type = mti.eam_item_type
1732 , STAR.last_update_date = mti.last_update_date
1733 WHEN NOT MATCHED THEN
1734 INSERT (
1735 id,
1736 value,
1737 inventory_item_id,
1738 organization_id,
1739 po_category_id,
1740 po_category_set_id,
1741 po_concat_seg,
1742 inv_category_id,
1743 inv_category_set_id,
1744 inv_concat_seg,
1745 vbh_category_id,
1746 vbh_category_set_id,
1747 vbh_concat_seg,
1748 master_id,
1749 item_catalog_group_id,
1750 primary_uom_code,
1751 unit_weight,
1752 unit_volume,
1753 weight_uom_code,
1754 volume_uom_code,
1755 eam_item_type,
1756 creation_date,
1757 last_update_date)
1758 VALUES(
1759 mti.inventory_item_id || ''-'' || mti.organization_id,
1760 mti.value,
1761 mti.inventory_item_id,
1762 mti.organization_id,
1763 mti.po_category_id,
1764 mti.po_category_set_id,
1765 mti.po_concat_seg,
1766 mti.inv_category_id,
1767 mti.inv_category_set_id,
1768 mti.inv_concat_seg,
1769 mti.vbh_category_id,
1770 mti.vbh_category_set_id,
1771 mti.vbh_concat_seg,
1772 mti.master_id,
1773 mti.item_catalog_group_id,
1774 mti.primary_uom_code,
1775 mti.unit_weight,
1776 mti.unit_volume,
1777 mti.weight_uom_code,
1778 mti.volume_uom_code,
1779 mti.eam_item_type,
1780 mti.creation_date,
1781 mti.last_update_date)';
1782 -- Bug : 3671737
1783
1784 EXECUTE IMMEDIATE l_sql USING l_inv_category_set, l_vbh_category_set, l_po_category_set, p_set_process_id, l_inv_category_set, l_vbh_category_set, l_po_category_set;
1785
1786 /*Bug 4604523 Splitting the merge to process once rows with set_process_id = N
1787 and next with set_process_id = N+1000000000000*/
1788 -- Bug 10404086 : Added below hint
1789 l_sql := 'MERGE INTO eni_oltp_item_star STAR
1790 USING (SELECT /*+ first_rows index(interface, MTL_SYSTEM_ITEMS_INTERFACE_N3) */
1791 item.inventory_item_id inventory_item_id,
1792 item.organization_id organization_id,
1793 item.CONCATENATED_SEGMENTS|| '' ('' || mtp.organization_code || '')''value,
1794 decode(item.organization_id,mtp.master_organization_id,null,
1795 item.inventory_item_id || ''-'' || mtp.master_organization_id)
1796 master_id,
1797 nvl(mic.category_id,-1) inv_category_id,
1798 nvl(mic.category_set_id, :l_inv_category_set) inv_category_set_id,
1799 nvl(kfv.concatenated_segments,''Unassigned'') inv_concat_seg,
1800 nvl(mic1.category_id,-1) vbh_category_id,
1801 nvl(mic1.category_set_id, :l_vbh_category_set) vbh_category_set_id,
1802 nvl(kfv1.concatenated_segments,''Unassigned'') vbh_concat_seg,
1803 nvl(mic2.category_id,-1) po_category_id,
1804 nvl(mic2.category_set_id, :l_po_category_set) po_category_set_id,
1805 nvl(kfv2.concatenated_segments,''Unassigned'') po_concat_seg,
1806 nvl(item.item_catalog_group_id,-1) item_catalog_group_id,
1807 item.primary_uom_code
1808 , item.unit_weight
1809 , item.unit_volume
1810 , item.weight_uom_code
1811 , item.volume_uom_code
1812 , item.eam_item_type
1813 , item.creation_date
1814 , item.last_update_date
1815 FROM mtl_system_items_interface interface
1816 , mtl_system_items_b_kfv item
1817 , mtl_parameters mtp
1818 , mtl_item_categories mic
1819 , mtl_categories_b_kfv kfv
1820 , mtl_item_categories mic1
1821 , mtl_categories_b_kfv kfv1
1822 , mtl_item_categories mic2
1823 , mtl_categories_b_kfv kfv2
1824 WHERE item.inventory_item_id = interface.inventory_item_id
1825 AND interface.set_process_id =
1826 :p_set_process_id
1827 AND interface.process_flag = 7
1828 AND item.organization_id = interface.organization_id
1829 AND item.organization_id= mtp.organization_id
1830 AND mic.organization_id(+) = item.organization_id
1831 AND mic.inventory_item_id(+) = item.inventory_item_id
1832 AND mic.category_id = kfv.category_id (+)
1833 and mic.category_set_id(+) = :l_inv_category_set
1834 AND mic1.organization_id(+) = item.organization_id
1835 AND mic1.inventory_item_id(+) = item.inventory_item_id
1836 AND mic1.category_id = kfv1.category_id (+)
1837 and mic1.category_set_id(+) = :l_vbh_category_set
1838 AND mic2.organization_id(+) = item.organization_id
1839 AND mic2.inventory_item_id(+) = item.inventory_item_id
1840 AND mic2.category_id = kfv2.category_id (+)
1841 and mic2.category_set_id(+) = :l_po_category_set) mti
1842 ON (STAR.inventory_item_id = mti.inventory_item_id
1843 AND STAR.organization_id = mti.organization_id)
1844 WHEN MATCHED THEN
1845 UPDATE SET STAR.value = mti.value
1846 , STAR.po_category_id = mti.po_category_id
1847 , STAR.po_category_set_id = mti.po_category_set_id
1848 , STAR.po_concat_seg = mti.po_concat_seg
1849 , STAR.inv_category_id = mti.inv_category_id
1850 , STAR.inv_category_set_id = mti.inv_category_set_id
1851 , STAR.inv_concat_seg = mti.inv_concat_seg
1852 , STAR.vbh_category_id = mti.vbh_category_id
1853 , STAR.vbh_category_set_id = mti.vbh_category_set_id
1854 , STAR.vbh_concat_seg = mti.vbh_concat_seg
1855 , STAR.master_id = mti.master_id
1856 , STAR.item_catalog_group_id = mti.item_catalog_group_id
1857 , STAR.primary_uom_code = mti.primary_uom_code
1858 , STAR.unit_weight = mti.unit_weight
1859 , STAR.unit_volume = mti.unit_volume
1860 , STAR.weight_uom_code = mti.weight_uom_code
1861 , STAR.volume_uom_code = mti.volume_uom_code
1862 , STAR.eam_item_type = mti.eam_item_type
1863 , STAR.last_update_date = mti.last_update_date
1864 WHEN NOT MATCHED THEN
1865 INSERT (
1866 id,
1867 value,
1868 inventory_item_id,
1869 organization_id,
1870 po_category_id,
1871 po_category_set_id,
1872 po_concat_seg,
1873 inv_category_id,
1874 inv_category_set_id,
1875 inv_concat_seg,
1876 vbh_category_id,
1877 vbh_category_set_id,
1878 vbh_concat_seg,
1879 master_id,
1880 item_catalog_group_id,
1881 primary_uom_code,
1882 unit_weight,
1883 unit_volume,
1884 weight_uom_code,
1885 volume_uom_code,
1886 eam_item_type,
1887 creation_date,
1888 last_update_date)
1889 VALUES(
1890 mti.inventory_item_id || ''-'' || mti.organization_id,
1891 mti.value,
1892 mti.inventory_item_id,
1893 mti.organization_id,
1894 mti.po_category_id,
1895 mti.po_category_set_id,
1896 mti.po_concat_seg,
1897 mti.inv_category_id,
1898 mti.inv_category_set_id,
1899 mti.inv_concat_seg,
1900 mti.vbh_category_id,
1901 mti.vbh_category_set_id,
1902 mti.vbh_concat_seg,
1903 mti.master_id,
1904 mti.item_catalog_group_id,
1905 mti.primary_uom_code,
1906 mti.unit_weight,
1907 mti.unit_volume,
1908 mti.weight_uom_code,
1909 mti.volume_uom_code,
1910 mti.eam_item_type,
1911 mti.creation_date,
1912 mti.last_update_date)';
1913
1914 EXECUTE IMMEDIATE l_sql USING l_inv_category_set, l_vbh_category_set, l_po_category_set, l_child_set_id, l_inv_category_set, l_vbh_category_set, l_po_category_set;
1915
1916 -- Bug: 4917496 Added child_id= default_category_id predicate
1917 -- updating Item Assignment flag for all categories,
1918 -- which have items attached to it
1919 UPDATE eni_denorm_hierarchies B
1920 SET
1921 item_assgn_flag = 'Y',
1922 last_update_date = sysdate,
1923 last_updated_by = l_user_id,
1924 last_update_login = l_user_id,
1925 request_id = l_conc_request_id,
1926 program_application_id = l_prog_appl_id,
1927 program_update_date = sysdate,
1928 program_id = l_conc_program_id
1929 WHERE b.object_type = 'CATEGORY_SET'
1933 FROM mtl_category_sets_b
1930 AND b.object_id = l_vbh_category_set
1931 AND b.item_assgn_flag = 'N'
1932 AND b.child_id = (SELECT DEFAULT_CATEGORY_ID
1934 WHERE category_set_id=l_vbh_category_set)
1935 AND EXISTS (SELECT NULL
1936 FROM mtl_item_categories C
1937 WHERE c.category_set_id = l_vbh_category_set
1938 AND c.category_id = b.child_id);
1939
1940 /** Bug: 4917496
1941 commenting this update as IOI(Item Create) can only result in creation of item assignment
1942 This update statement will always fetch zero rows.
1943
1944 -- updating Item Assignment flag for all categories, which does not have items attached to it
1945 UPDATE eni_denorm_hierarchies b
1946 SET
1947 item_assgn_flag = 'N',
1948 last_update_date = SYSDATE,
1949 last_updated_by = l_user_id,
1950 last_update_login = l_user_id,
1951 request_id = l_conc_request_id,
1952 program_application_id = l_prog_appl_id,
1953 program_update_date = SYSDATE,
1954 program_id = l_conc_program_id
1955 WHERE b.object_type = 'CATEGORY_SET'
1956 AND b.object_id = l_vbh_category_set
1957 AND b.item_assgn_flag = 'Y'
1958 AND b.child_id <> -1
1959 AND NOT EXISTS (SELECT NULL
1960 FROM mtl_item_categories C
1961 WHERE c.category_set_id = l_vbh_category_set
1962 AND c.category_id = b.child_id);
1963 **/
1964 -- Checking Item assignment flag for Unassigned category
1965 -- if all items are attached to some categories within this category set then
1966 -- Item assignment flag for Unassigned node will be 'N'
1967
1968 l_count := 0;
1969
1970 BEGIN
1971 SELECT 1 INTO l_count
1972 FROM ENI_OLTP_ITEM_STAR star
1973 WHERE star.vbh_category_id = -1
1974 AND rownum = 1;
1975
1976 /** Bug 4675565
1977 Replaced with the SQL above
1978 As UNASSIGNED category is only used by DBI
1979 we can rely on ENI_OLTP_ITEM_STAR_TABLE to get this info.
1980 SELECT 1 INTO l_count
1981 FROM mtl_system_items_b IT
1982 WHERE ROWNUM = 1
1983 AND NOT EXISTS (SELECT NULL FROM mtl_item_categories C
1984 WHERE c.category_set_id = l_vbh_category_set
1985 AND c.inventory_item_id = it.inventory_item_id
1986 AND c.organization_id = it.organization_id);
1987 */
1988 EXCEPTION WHEN NO_DATA_FOUND THEN
1989 l_count := 0;
1990 END;
1991
1992 UPDATE eni_denorm_hierarchies b
1993 SET
1994 item_assgn_flag = decode(l_count, 0, 'N', 'Y'),
1995 last_update_date = sysdate,
1996 last_updated_by = l_user_id,
1997 last_update_login = l_user_id,
1998 request_id = l_conc_request_id,
1999 program_application_id = l_prog_appl_id,
2000 program_update_date = sysdate,
2001 program_id = l_conc_program_id
2002 WHERE b.object_type = 'CATEGORY_SET'
2003 AND b.object_id = l_vbh_category_set
2004 AND b.item_assgn_flag = DECODE(l_count, 0, 'Y', 'N')
2005 AND b.child_id = -1
2006 AND b.parent_id = -1;
2007
2008 X_RETURN_STATUS := 'S';
2009
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 X_RETURN_STATUS := 'U';
2013 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2014 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'SYNC_STAR_ITEMS_FROM_IOI',SQLERRM);
2015 END IF;
2016 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
2017
2018
2019 END Sync_star_items_from_IOI;
2020
2021 -- Inserts/Deletes item category assignment from star table from
2022 -- Categories open interface
2023 PROCEDURE Sync_Star_ItemCatg_From_COI(
2024 p_api_version IN NUMBER,
2025 p_init_msg_list IN VARCHAR2 := 'F',
2026 p_set_process_id IN NUMBER,
2027 x_return_status OUT NOCOPY VARCHAR2,
2028 x_msg_count OUT NOCOPY NUMBER,
2029 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
2030
2031 CURSOR get_po_catset IS
2032 SELECT category_set_id
2033 FROM mtl_default_category_sets
2034 WHERE functional_area_id = 2;
2035
2036 l_user_id number;
2037 l_conc_request_id number;
2038 l_prog_appl_id number;
2039 l_conc_program_id number;
2040 l_count number;
2041 l_INV_category_set_id number;
2042 l_VBH_category_set_id number;
2043 l_PO_category_set_id number;
2044 l_process_flag NUMBER;
2045 l_num_updates NUMBER := 0;
2046
2047 CURSOR icoi_csr (p_set_process_id NUMBER) IS
2048 SELECT mici.inventory_item_id
2049 ,mp.organization_id
2050 FROM mtl_item_categories_interface mici
2051 ,mtl_parameters mp
2052 WHERE mici.set_process_id = p_set_process_id
2053 AND mici.request_id = l_conc_request_id
2054 AND mici.process_flag = l_process_flag
2055 AND ( mici.category_set_id = l_INV_category_set_id
2056 OR mici.category_set_id = l_VBH_category_set_id
2057 OR mici.category_set_id = l_PO_category_set_id)
2058 AND ( mici.organization_id = mp.organization_id
2059 OR mici.organization_id = mp.master_organization_id);
2060
2061
2062 BEGIN
2063
2064
2065 -- Check if this synchronization should happen: if no, exit, if yes, continue
2066 if Sync_Star_Items = false then
2067 X_RETURN_STATUS := 'S';
2068 return;
2069 end if;
2070
2071 l_vbh_category_set_id := ENI_DENORM_HRCHY.get_category_set_id;
2072 OPEN get_po_catset;
2073 FETCH get_po_catset INTO l_po_category_set_id;
2074 CLOSE get_po_catset;
2075 l_user_id := FND_GLOBAL.USER_ID;
2076 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2077 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
2078 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
2079 l_process_flag := 7;
2080
2081 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2082 FND_MSG_PUB.Initialize ;
2083 END IF;
2084
2085 SELECT category_set_id INTO l_inv_category_set_id
2086 FROM mtl_default_category_sets
2087 WHERE functional_area_id = 1;
2088
2089
2090 FOR sync_itmcatg IN icoi_csr(
2091 p_set_process_id => p_set_process_id)
2092 LOOP
2093 UPDATE eni_oltp_item_star star
2094 SET (
2095 star.INV_CATEGORY_ID
2096 ,star.INV_CONCAT_SEG
2097 ,star.INV_CATEGORY_SET_ID
2098 ,star.VBH_CATEGORY_ID
2099 ,star.VBH_CONCAT_SEG
2100 ,star.VBH_CATEGORY_SET_ID
2101 ,star.PO_CATEGORY_ID
2102 ,star.PO_CONCAT_SEG
2103 ,star.PO_CATEGORY_SET_ID)
2104 =
2105 ( SELECT
2106 nvl(mic.category_id, -1) inv_category_id
2107 ,nvl(kfv.concatenated_segments, 'Unassigned') inv_concat_seg
2108 ,nvl(mic.category_Set_id, l_INV_category_set_id) inv_category_Set_id
2109 ,nvl(mic1.category_id, -1) vbh_category_id
2110 ,nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg
2111 ,nvl(mic1.category_Set_id, l_VBH_category_set_id) vbh_category_set_id
2112 ,nvl(mic2.category_id, -1) po_category_id
2113 ,nvl(kfv2.concatenated_segments, 'Unassigned') po_concat_seg
2114 ,nvl(mic2.category_Set_id, l_PO_category_set_id) po_category_set_id
2115 FROM
2116 mtl_system_items_b msi
2117 , mtl_item_categories mic
2118 , mtl_categories_b_kfv kfv
2119 , mtl_item_categories mic1
2120 , mtl_categories_b_kfv kfv1
2121 , mtl_item_categories mic2
2122 , mtl_categories_b_kfv kfv2
2123 WHERE
2124 msi.inventory_item_id = star.inventory_item_id
2125 AND msi.organization_id = star.organization_id
2126 AND mic.inventory_item_id (+) = msi.inventory_item_id
2127 AND mic.organization_id (+) = msi.organization_id
2128 AND mic.category_id = kfv.category_id (+)
2129 AND mic.category_set_id (+) = l_INV_category_set_id
2130 AND mic1.inventory_item_id (+) = msi.inventory_item_id
2131 AND mic1.organization_id (+) = msi.organization_id
2132 AND mic1.category_id = kfv1.category_id (+)
2133 AND mic1.category_set_id (+) = l_VBH_category_set_id
2134 AND mic2.inventory_item_id (+) = msi.inventory_item_id
2135 AND mic2.organization_id (+) = msi.organization_id
2136 AND mic2.category_id = kfv2.category_id (+)
2137 AND mic2.category_set_id (+) = l_PO_category_set_id)
2138 WHERE star.inventory_item_id = sync_itmcatg.inventory_item_id
2139 AND star.organization_id = sync_itmcatg.organization_id;
2140 END LOOP;
2141
2142 /**Bug: 4917496
2143 Only update the categories which are modified in this run
2144 Only Create, update can cause an assigment creation
2145 **/
2146
2147 -- updating Item Assignment flag for all categories,
2148 -- which have items attached to it
2149 FOR intf_categories_add IN (SELECT DISTINCT CATEGORY_ID
2150 FROM mtl_item_categories_interface
2151 WHERE process_flag = 7
2152 AND transaction_type IN ('CREATE','UPDATE')
2153 AND set_process_id = p_set_process_id
2154 AND category_set_id = l_vbh_category_set_id)
2155 LOOP
2156 UPDATE eni_denorm_hierarchies B
2157 SET
2158 item_assgn_flag = 'Y',
2159 last_update_date = sysdate,
2160 last_updated_by = l_user_id,
2161 last_update_login = l_user_id,
2162 request_id = l_conc_request_id,
2163 program_application_id = l_prog_appl_id,
2164 program_update_date = sysdate,
2165 program_id = l_conc_program_id
2166 WHERE b.object_type = 'CATEGORY_SET'
2167 AND b.object_id = l_vbh_category_set_id
2168 AND b.item_assgn_flag = 'N'
2169 AND b.child_id = intf_categories_add.category_id
2170 AND EXISTS (SELECT NULL
2171 FROM mtl_item_categories C
2172 WHERE c.category_set_id = l_vbh_category_set_id
2173 AND c.category_id = b.child_id);
2174
2175 l_num_updates := l_num_updates + SQL%ROWCOUNT;
2176 END LOOP;
2177
2178 -- updating Item Assignment flag for all categories, which do not have items attached to it
2179 FOR intf_categories_del IN
2180 (SELECT DISTINCT
2181 Decode(TRANSACTION_TYPE,
2182 'UPDATE',OLD_CATEGORY_ID,
2183 CATEGORY_ID) AS CATEGORY_ID
2184 FROM mtl_item_categories_interface
2185 WHERE process_flag = 7
2186 AND TRANSACTION_TYPE IN ('DELETE','UPDATE')
2187 AND set_process_id = p_set_process_id
2188 AND category_set_id = l_vbh_category_set_id)
2189 LOOP
2190
2191 UPDATE eni_denorm_hierarchies b
2192 SET
2193 item_assgn_flag = 'N',
2194 last_update_date = SYSDATE,
2195 last_updated_by = l_user_id,
2196 last_update_login = l_user_id,
2197 request_id = l_conc_request_id,
2198 program_application_id = l_prog_appl_id,
2199 program_update_date = SYSDATE,
2200 program_id = l_conc_program_id
2201 WHERE b.object_type = 'CATEGORY_SET'
2202 AND b.object_id = l_vbh_category_set_id
2203 AND b.item_assgn_flag = 'Y'
2204 AND b.child_id = intf_categories_del.category_id
2205 AND NOT EXISTS (SELECT NULL
2206 FROM mtl_item_categories C
2207 WHERE c.category_set_id = l_vbh_category_set_id
2208 AND c.category_id = b.child_id);
2209
2210 l_num_updates := l_num_updates + SQL%ROWCOUNT;
2211
2212 END LOOP;
2213
2214
2215 -- Checking Item assignment flag for Unassigned category
2216 -- if all items are attached to some categories within this category set then
2217 -- Item assignment flag for Unassigned node will be 'N'
2218 /** Bug: 4917496
2219 We need to update UNSASSIGNED category only if there is
2220 any upate on ENI_DENORM table in the above two SQLs
2221 **/
2222 IF l_num_updates <> 0 THEN
2223 l_count := 0;
2224
2225 BEGIN
2226 SELECT 1 INTO l_count
2227 FROM ENI_OLTP_ITEM_STAR star
2228 WHERE star.vbh_category_id = -1
2229 AND rownum = 1;
2230
2231 /** Bug 4675565
2232 Replaced with the SQL above
2233 As UNASSIGNED category is only used by DBI
2234 we can rely on ENI_OLTP_ITEM_STAR_TABLE to get this info.
2235 SELECT 1 INTO l_count
2236 FROM mtl_system_items_b IT
2237 WHERE ROWNUM = 1
2238 AND NOT EXISTS (SELECT NULL FROM mtl_item_categories C
2239 WHERE c.category_set_id = l_vbh_category_set_id
2240 AND c.inventory_item_id = it.inventory_item_id
2241 AND c.organization_id = it.organization_id);
2242 */
2243 EXCEPTION WHEN NO_DATA_FOUND THEN
2244 l_count := 0;
2245 END;
2246
2247 UPDATE eni_denorm_hierarchies b
2248 SET
2249 item_assgn_flag = decode(l_count, 0, 'N', 'Y'),
2250 last_update_date = sysdate,
2251 last_updated_by = l_user_id,
2252 last_update_login = l_user_id,
2253 request_id = l_conc_request_id,
2254 program_application_id = l_prog_appl_id,
2255 program_update_date = sysdate,
2256 program_id = l_conc_program_id
2257 WHERE b.object_type = 'CATEGORY_SET'
2258 AND b.object_id = l_vbh_category_set_id
2259 AND b.item_assgn_flag = DECODE(l_count, 0, 'Y', 'N')
2260 AND b.child_id = -1
2261 AND b.parent_id = -1;
2262
2263 X_RETURN_STATUS := 'S';
2264
2265 END IF;
2266
2267 EXCEPTION
2268 WHEN OTHERS THEN
2269 X_RETURN_STATUS := 'U';
2270 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2271 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'SYNC_STAR_ITEMS_FROM_COI',SQLERRM);
2272 END IF;
2273 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
2274
2275 END Sync_Star_ItemCatg_From_COI;
2276
2277 --Start :5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
2278 BEGIN
2279 SELECT NVL(LENGTH(CONCATENATED_SEGMENTS),0) INTO G_INSTALL_PHASE
2280 FROM MTL_SYSTEM_ITEMS_B_KFV
2281 WHERE ROWNUM = 1;
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284 G_INSTALL_PHASE := 0;
2285 --End :5688257 : %Items_In_Star functions to use flex API's to get concatenated segments
2286
2287 End ENI_ITEMS_STAR_PKG;