[Home] [Help]
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);
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
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:
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:
310: -- Bug 14711987. Removed purge materilized view log
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 (
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
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
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:
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
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
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
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
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);
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
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
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';
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:
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
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;
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:
977: BEGIN
978: FOR c_items_in_master_rec IN c_items_in_master
979: LOOP
980:
981: UPDATE ENI_OLTP_ITEM_STAR
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
985: AND 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
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
1338:
1339: /* Commented out as fix for Bug 3600364
1340: IF which_category_set = 'VBH_CATEGORY' and l_category_set_id = 1000000006
1341: THEN
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
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)
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)
1466:
1467: FOR sync_c1 IN C1(p_inventory_item_id, p_organization_id)
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,
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:
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,
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,
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
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
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
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
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