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