[Home] [Help]
186: AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
187:
188: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Revisions '||SQL%ROWCOUNT);
189:
190: UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
191: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
192: FROM MTL_SYSTEM_ITEMS_KFV MSIK
193: WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
194: AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
1667: ERRBUF := NULL;
1668: RETURN;
1669: END IF;
1670:
1671: INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
1672: (
1673: INVENTORY_ITEM_ID,
1674: ITEM_NUMBER,
1675: ORGANIZATION_ID,
1692: 'CREATE',
1693: l_pdh_ss_id,
1694: -99
1695: FROM
1696: MTL_ITEM_CATEGORIES_INTERFACE mici,
1697: MTL_SYSTEM_ITEMS_KFV msik
1698: WHERE mici.SET_PROCESS_ID = p_batch_id
1699: AND msik.STYLE_ITEM_ID = mici.INVENTORY_ITEM_ID
1700: AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
1703: AND mici.PROCESS_FLAG = 7
1704: AND msik.STYLE_ITEM_FLAG = 'N'
1705: AND NOT EXISTS (SELECT 1 FROM MTL_DEFAULT_CATEGORY_SETS dcs WHERE dcs.CATEGORY_SET_ID = mici.CATEGORY_SET_ID)
1706: AND NOT EXISTS (SELECT 1
1707: FROM MTL_ITEM_CATEGORIES mic
1708: WHERE mic.CATEGORY_SET_ID = mici.CATEGORY_SET_ID
1709: AND mic.CATEGORY_ID = mici.CATEGORY_ID
1710: AND mic.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
1711: AND mic.ORGANIZATION_ID = msik.ORGANIZATION_ID)
1709: AND mic.CATEGORY_ID = mici.CATEGORY_ID
1710: AND mic.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
1711: AND mic.ORGANIZATION_ID = msik.ORGANIZATION_ID)
1712: AND NOT EXISTS (SELECT NULL
1713: FROM MTL_ITEM_CATEGORIES_INTERFACE mici1
1714: WHERE mici1.SET_PROCESS_ID = p_batch_id
1715: AND mici1.PROCESS_FLAG = 1
1716: AND (mici1.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID OR mici1.ITEM_NUMBER = msik.CONCATENATED_SEGMENTS)
1717: AND mici1.ORGANIZATION_ID = msik.ORGANIZATION_ID
1753: ERRBUF := NULL;
1754: RETURN;
1755: END IF;
1756:
1757: INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
1758: (
1759: INVENTORY_ITEM_ID,
1760: ITEM_NUMBER,
1761: ORGANIZATION_ID,
1776: 'CREATE'
1777: FROM
1778: MTL_SYSTEM_ITEMS_INTERFACE MSII,
1779: MTL_PARAMETERS O,
1780: MTL_ITEM_CATEGORIES STYLE_CATS
1781: WHERE MSII.SET_PROCESS_ID = p_batch_id
1782: AND MSII.PROCESS_FLAG = 1
1783: AND MSII.TRANSACTION_TYPE = 'CREATE'
1784: AND ((NVL(p_skus_only, 'Y') = 'Y' AND MSII.STYLE_ITEM_FLAG = 'N')
1789: AND NOT EXISTS (SELECT 1 FROM MTL_DEFAULT_CATEGORY_SETS DCS WHERE DCS.CATEGORY_SET_ID = STYLE_CATS.CATEGORY_SET_ID)
1790: AND STYLE_CATS.INVENTORY_ITEM_ID = MSII.COPY_ITEM_ID
1791: AND STYLE_CATS.ORGANIZATION_ID = MSII.ORGANIZATION_ID
1792: AND NOT EXISTS (SELECT NULL
1793: FROM MTL_ITEM_CATEGORIES_INTERFACE MICI
1794: WHERE MICI.SET_PROCESS_ID = MSII.SET_PROCESS_ID
1795: AND MICI.PROCESS_FLAG = MSII.PROCESS_FLAG
1796: AND (MICI.ITEM_NUMBER = MSII.ITEM_NUMBER OR MICI.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID)
1797: AND MICI.ORGANIZATION_ID = MSII.ORGANIZATION_ID
3601: IS
3602: BEGIN
3603: -- call defaulting of non default category assignments here
3604: Debug_Conc_Log('Default_Category_Assignments: Starting');
3605: INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
3606: (
3607: INVENTORY_ITEM_ID,
3608: ITEM_NUMBER,
3609: ORGANIZATION_ID,
3624: 'CREATE'
3625: FROM
3626: MTL_SYSTEM_ITEMS_INTERFACE MSII,
3627: MTL_PARAMETERS O,
3628: MTL_ITEM_CATEGORIES STYLE_CATS
3629: WHERE MSII.SET_PROCESS_ID = p_batch_id
3630: AND MSII.PROCESS_FLAG = p_msii_miri_process_flag -- Bug 12635842
3631: AND MSII.TRANSACTION_TYPE = 'CREATE'
3632: AND MSII.STYLE_ITEM_FLAG = 'N'
4033: END IF;
4034:
4035: l_item_detail_tbl := NULL;
4036:
4037: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
4038: SET PROCESS_FLAG = 6,
4039: REQUEST_ID = l_request_id,
4040: PROGRAM_ID = l_prog_id,
4041: PROGRAM_APPLICATION_ID = l_prog_appid,
4055: AND eime.ORGANIZATION_ID = mici.ORGANIZATION_ID
4056: AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
4057: );
4058:
4059: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated MTL_ITEM_CATEGORIES_INTERFACE rowcount='||SQL%ROWCOUNT);
4060:
4061: UPDATE BOM_BILL_OF_MTLS_INTERFACE bbmi
4062: SET PROCESS_FLAG = 6,
4063: REQUEST_ID = l_request_id,
5170: END IF;
5171:
5172: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_REVISIONS_INTERFACE Processed rows='||SQL%ROWCOUNT);
5173:
5174: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
5175: SET PROCESS_FLAG = 10
5176: WHERE SET_PROCESS_ID = p_batch_id
5177: AND PROCESS_FLAG = 1
5178: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5192: -- calling this unconditionally, because we will be going through processing SKUs always
5193: IF SQL%ROWCOUNT > 0 THEN
5194: EGO_WF_WRAPPER_PVT.Set_Category_Assign_Bus_Event(p_true_false => p_enable_sku_processing);
5195: END IF;
5196: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_CATEGORIES_INTERFACE Processed rows='||SQL%ROWCOUNT);
5197:
5198: -- for user defined attributes, all the process statuses are already in use i.e.
5199: -- 0, 1, 2, 3, 4, 5, 6, and 8 and above are in use
5200: -- 7 has a conflict with other interface tables, user may think that record is successful
5364: EGO_WF_WRAPPER_PVT.Set_Rev_Change_Bus_Event(p_true_false => 'F'); -- Bug #9341964, make sure biz event is fired only once
5365: END IF;
5366: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_REVISIONS_INTERFACE Processed rows='||SQL%ROWCOUNT);
5367:
5368: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
5369: SET PROCESS_FLAG = 1
5370: WHERE SET_PROCESS_ID = p_batch_id
5371: AND PROCESS_FLAG = 10;
5372:
5379: --R12C Business Events to be fired only ONCE although Batch Import is called twice for Styles and then SKUs
5380: IF(SQL%ROWCOUNT > 0) THEN
5381: EGO_WF_WRAPPER_PVT.Set_Category_Assign_Bus_Event(p_true_false => p_enable_sku_processing);
5382: END IF;
5383: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_ITEM_CATEGORIES_INTERFACE Processed rows='||SQL%ROWCOUNT);
5384:
5385: UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
5386: SET PROCESS_STATUS = 1
5387: WHERE DATA_SET_ID = p_batch_id
5947: AND ITEM_NUMBER = p_old_item_number;
5948: END IF;
5949: -- Fix for bug#9660659 End
5950:
5951: UPDATE MTL_ITEM_CATEGORIES_INTERFACE
5952: SET ITEM_NUMBER = p_item_number
5953: WHERE PROCESS_FLAG = 1
5954: AND SET_PROCESS_ID = p_batch_id
5955: AND SOURCE_SYSTEM_ID = p_ss_id
6632: END IF;
6633:
6634: l_item_detail_tbl := NULL;
6635:
6636: UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
6637: SET PROCESS_FLAG = 6,
6638: REQUEST_ID = l_request_id,
6639: PROGRAM_ID = l_prog_id,
6640: PROGRAM_APPLICATION_ID = l_prog_appid,
6650: AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
6651: AND mici.BUNDLE_ID = msii.BUNDLE_ID
6652: );
6653:
6654: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MTL_ITEM_CATEGORIES_INTERFACE rowcount='||SQL%ROWCOUNT);
6655: UPDATE BOM_BILL_OF_MTLS_INTERFACE bbmi
6656: SET PROCESS_FLAG = 6,
6657: REQUEST_ID = l_request_id,
6658: PROGRAM_ID = l_prog_id,
7080:
7081: delete from mtl_system_items_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7082: delete from EGO_ITM_USR_ATTR_INTRFC where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7083: delete from EGO_ITEM_ASSOCIATIONS_INTF where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7084: delete from mtl_item_categories_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7085:
7086: else
7087:
7088: -- As we have ordered by LAST_MODIFIED_DATE we can delete rows having rank >1
7090:
7091: delete from mtl_system_items_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7092: delete from EGO_ITM_USR_ATTR_INTRFC where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7093: delete from EGO_ITEM_ASSOCIATIONS_INTF where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7094: delete from mtl_item_categories_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
7095:
7096:
7097: end if;
7098: else