[Home] [Help]
163: AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
164:
165: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Revisions '||SQL%ROWCOUNT);
166:
167: UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
168: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
169: FROM MTL_SYSTEM_ITEMS_KFV MSIK
170: WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
171: AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
1497: ERRBUF := NULL;
1498: RETURN;
1499: END IF;
1500:
1501: INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
1502: (
1503: INVENTORY_ITEM_ID,
1504: ITEM_NUMBER,
1505: ORGANIZATION_ID,
1522: 'CREATE',
1523: l_pdh_ss_id,
1524: -99
1525: FROM
1526: MTL_ITEM_CATEGORIES_INTERFACE mici,
1527: MTL_SYSTEM_ITEMS_KFV msik
1528: WHERE mici.SET_PROCESS_ID = p_batch_id
1529: AND msik.STYLE_ITEM_ID = mici.INVENTORY_ITEM_ID
1530: AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
1533: AND mici.PROCESS_FLAG = 7
1534: AND msik.STYLE_ITEM_FLAG = 'N'
1535: AND NOT EXISTS (SELECT 1 FROM MTL_DEFAULT_CATEGORY_SETS dcs WHERE dcs.CATEGORY_SET_ID = mici.CATEGORY_SET_ID)
1536: AND NOT EXISTS (SELECT 1
1537: FROM MTL_ITEM_CATEGORIES mic
1538: WHERE mic.CATEGORY_SET_ID = mici.CATEGORY_SET_ID
1539: AND mic.CATEGORY_ID = mici.CATEGORY_ID
1540: AND mic.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
1541: AND mic.ORGANIZATION_ID = msik.ORGANIZATION_ID)
1539: AND mic.CATEGORY_ID = mici.CATEGORY_ID
1540: AND mic.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
1541: AND mic.ORGANIZATION_ID = msik.ORGANIZATION_ID)
1542: AND NOT EXISTS (SELECT NULL
1543: FROM MTL_ITEM_CATEGORIES_INTERFACE mici1
1544: WHERE mici1.SET_PROCESS_ID = p_batch_id
1545: AND mici1.PROCESS_FLAG = 1
1546: AND (mici1.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID OR mici1.ITEM_NUMBER = msik.CONCATENATED_SEGMENTS)
1547: AND mici1.ORGANIZATION_ID = msik.ORGANIZATION_ID
1583: ERRBUF := NULL;
1584: RETURN;
1585: END IF;
1586:
1587: INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
1588: (
1589: INVENTORY_ITEM_ID,
1590: ITEM_NUMBER,
1591: ORGANIZATION_ID,
1606: 'CREATE'
1607: FROM
1608: MTL_SYSTEM_ITEMS_INTERFACE MSII,
1609: MTL_PARAMETERS O,
1610: MTL_ITEM_CATEGORIES STYLE_CATS
1611: WHERE MSII.SET_PROCESS_ID = p_batch_id
1612: AND MSII.PROCESS_FLAG = 1
1613: AND MSII.TRANSACTION_TYPE = 'CREATE'
1614: AND ((NVL(p_skus_only, 'Y') = 'Y' AND MSII.STYLE_ITEM_FLAG = 'N')
1619: AND NOT EXISTS (SELECT 1 FROM MTL_DEFAULT_CATEGORY_SETS DCS WHERE DCS.CATEGORY_SET_ID = STYLE_CATS.CATEGORY_SET_ID)
1620: AND STYLE_CATS.INVENTORY_ITEM_ID = MSII.COPY_ITEM_ID
1621: AND STYLE_CATS.ORGANIZATION_ID = MSII.ORGANIZATION_ID
1622: AND NOT EXISTS (SELECT NULL
1623: FROM MTL_ITEM_CATEGORIES_INTERFACE MICI
1624: WHERE MICI.SET_PROCESS_ID = MSII.SET_PROCESS_ID
1625: AND MICI.PROCESS_FLAG = MSII.PROCESS_FLAG
1626: AND (MICI.ITEM_NUMBER = MSII.ITEM_NUMBER OR MICI.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID)
1627: AND MICI.ORGANIZATION_ID = MSII.ORGANIZATION_ID
3410: IS
3411: BEGIN
3412: -- call defaulting of category assignments here
3413: Debug_Conc_Log('Default_Category_Assignments: Starting');
3414: INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
3415: (
3416: INVENTORY_ITEM_ID,
3417: ITEM_NUMBER,
3418: ORGANIZATION_ID,
3433: 'CREATE'
3434: FROM
3435: MTL_SYSTEM_ITEMS_INTERFACE MSII,
3436: MTL_PARAMETERS O,
3437: MTL_ITEM_CATEGORIES STYLE_CATS
3438: WHERE MSII.SET_PROCESS_ID = p_batch_id
3439: AND MSII.PROCESS_FLAG = 1
3440: AND MSII.TRANSACTION_TYPE = 'CREATE'
3441: AND MSII.STYLE_ITEM_FLAG = 'N'
3830: END IF;
3831:
3832: l_item_detail_tbl := NULL;
3833:
3834: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
3835: SET PROCESS_FLAG = 6,
3836: REQUEST_ID = l_request_id,
3837: PROGRAM_ID = l_prog_id,
3838: PROGRAM_APPLICATION_ID = l_prog_appid,
3852: AND eime.ORGANIZATION_ID = mici.ORGANIZATION_ID
3853: AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
3854: );
3855:
3856: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated MTL_ITEM_CATEGORIES_INTERFACE rowcount='||SQL%ROWCOUNT);
3857:
3858: UPDATE BOM_BILL_OF_MTLS_INTERFACE bbmi
3859: SET PROCESS_FLAG = 6,
3860: REQUEST_ID = l_request_id,
4471: END IF;
4472:
4473: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_REVISIONS_INTERFACE Processed rows='||SQL%ROWCOUNT);
4474:
4475: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
4476: SET PROCESS_FLAG = 10
4477: WHERE SET_PROCESS_ID = p_batch_id
4478: AND PROCESS_FLAG = 1
4479: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
4492:
4493: -- calling this unconditionally, because we will be going through processing SKUs always
4494: EGO_WF_WRAPPER_PVT.Set_Category_Assign_Bus_Event(p_true_false => p_enable_sku_processing);
4495:
4496: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_CATEGORIES_INTERFACE Processed rows='||SQL%ROWCOUNT);
4497:
4498: -- for user defined attributes, all the process statuses are already in use i.e.
4499: -- 0, 1, 2, 3, 4, 5, 6, and 8 and above are in use
4500: -- 7 has a conflict with other interface tables, user may think that record is successful
4646: EGO_WF_WRAPPER_PVT.Set_Rev_Change_Bus_Event(p_true_false => p_enable_sku_processing);
4647: END IF;
4648: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_REVISIONS_INTERFACE Processed rows='||SQL%ROWCOUNT);
4649:
4650: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
4651: SET PROCESS_FLAG = 1
4652: WHERE SET_PROCESS_ID = p_batch_id
4653: AND PROCESS_FLAG = 10;
4654:
4653: AND PROCESS_FLAG = 10;
4654:
4655: --R12C Business Events to be fired only ONCE although Batch Import is called twice for Styles and then SKUs
4656: EGO_WF_WRAPPER_PVT.Set_Category_Assign_Bus_Event(p_true_false => p_enable_sku_processing);
4657: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_CATEGORIES_INTERFACE Processed rows='||SQL%ROWCOUNT);
4658:
4659: UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
4660: SET PROCESS_STATUS = 1
4661: WHERE DATA_SET_ID = p_batch_id
5180: AND SET_PROCESS_ID = p_batch_id
5181: AND SOURCE_SYSTEM_ID = p_ss_id
5182: AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
5183:
5184: UPDATE MTL_ITEM_CATEGORIES_INTERFACE
5185: SET ITEM_NUMBER = p_item_number
5186: WHERE PROCESS_FLAG = 1
5187: AND SET_PROCESS_ID = p_batch_id
5188: AND SOURCE_SYSTEM_ID = p_ss_id
5848: END IF;
5849:
5850: l_item_detail_tbl := NULL;
5851:
5852: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
5853: SET PROCESS_FLAG = 6,
5854: REQUEST_ID = l_request_id,
5855: PROGRAM_ID = l_prog_id,
5856: PROGRAM_APPLICATION_ID = l_prog_appid,
5866: AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
5867: AND mici.BUNDLE_ID = msii.BUNDLE_ID
5868: );
5869:
5870: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MTL_ITEM_CATEGORIES_INTERFACE rowcount='||SQL%ROWCOUNT);
5871: UPDATE BOM_BILL_OF_MTLS_INTERFACE bbmi
5872: SET PROCESS_FLAG = 6,
5873: REQUEST_ID = l_request_id,
5874: PROGRAM_ID = l_prog_id,