[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);
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
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:
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:
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 (
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
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
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:
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
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
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
633: END IF; -- if l_full_refresh = 'N'
634:
635: BIS_COLLECTION_UTILITIES.log('Collection completed successfully.');
636:
637: BIS_COLLECTION_UTILITIES.log('Gathering statistics on table: ENI_OLTP_ITEM_STAR ');
638: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_OLTP_ITEM_STAR');
639:
640: Exception
641: When no_data_found then
634:
635: BIS_COLLECTION_UTILITIES.log('Collection completed successfully.');
636:
637: BIS_COLLECTION_UTILITIES.log('Gathering statistics on table: ENI_OLTP_ITEM_STAR ');
638: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_OLTP_ITEM_STAR');
639:
640: Exception
641: When no_data_found then
642: BIS_COLLECTION_UTILITIES.log(sqlerrm);
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
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
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';
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
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
972: , LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
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
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
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)
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)
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,
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:
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,
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,
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
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
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
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
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