[Home] [Help]
31: TYPE ITEM_DETAIL_RECORD IS RECORD(
32: TRANSACTION_ID NUMBER,
33: INVENTORY_ITEM_ID NUMBER,
34: ORGANIZATION_ID NUMBER,
35: ITEM_NUMBER MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE,
36: SOURCE_SYSTEM_REFERENCE MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE,
37: SOURCE_SYSTEM_ID NUMBER,
38: DATA_LEVEL_ID NUMBER,
39: SUPPLIER_ID NUMBER,
32: TRANSACTION_ID NUMBER,
33: INVENTORY_ITEM_ID NUMBER,
34: ORGANIZATION_ID NUMBER,
35: ITEM_NUMBER MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE,
36: SOURCE_SYSTEM_REFERENCE MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE,
37: SOURCE_SYSTEM_ID NUMBER,
38: DATA_LEVEL_ID NUMBER,
39: SUPPLIER_ID NUMBER,
40: SUPPLIER_SITE_ID NUMBER,
171: WHERE MSIK.CONCATENATED_SEGMENTS = MIRI.ITEM_NUMBER
172: AND MSIK.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
173: ),
174: (SELECT INVENTORY_ITEM_ID
175: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
176: WHERE (MSII.ITEM_NUMBER = MIRI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE)
177: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
178: AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
179: AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
193: WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
194: AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
195: ),
196: (SELECT INVENTORY_ITEM_ID
197: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
198: WHERE (MSII.ITEM_NUMBER = MICI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE)
199: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
200: AND MSII.ORGANIZATION_ID = MICI.ORGANIZATION_ID
201: AND MSII.SET_PROCESS_ID = MICI.SET_PROCESS_ID
234: ),
235: --Bug 9660659 - Removing the OR in this query, making two queries - Bug 9660659
236: /*
237: (SELECT INVENTORY_ITEM_ID
238: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
239: WHERE (MSII.ITEM_NUMBER = EIUAI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE)
240: AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
241: AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
242: AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
244: AND ROWNUM = 1
245: ))
246: */
247: NVL( (SELECT INVENTORY_ITEM_ID
248: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
249: WHERE (MSII.ITEM_NUMBER = EIUAI.ITEM_NUMBER)
250: AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
251: AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
252: AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
252: AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
253: AND MSII.PROCESS_FLAG = 1
254: AND ROWNUM = 1) ,
255: (SELECT INVENTORY_ITEM_ID
256: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
257: WHERE (MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE)
258: AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
259: AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
260: AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
275: WHERE MSIK.CONCATENATED_SEGMENTS = EIAI.ITEM_NUMBER
276: AND MSIK.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
277: ),
278: (SELECT INVENTORY_ITEM_ID
279: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
280: WHERE (MSII.ITEM_NUMBER = EIAI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE)
281: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
282: AND MSII.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
283: AND MSII.SET_PROCESS_ID = EIAI.BATCH_ID
567: NULL AS PK5_VALUE,
568: NULL AS REVISION_ID, #' ||
569: l_item_dl_id || q'# AS DATA_LEVEL_ID
570: FROM
571: MTL_SYSTEM_ITEMS_INTERFACE MSII
572: WHERE ITEM_CATALOG_GROUP_ID IS NOT NULL
573: AND SET_PROCESS_ID = #' || p_batch_id || q'#
574: AND PROCESS_FLAG = 1
575: UNION ALL #';
586: NULL AS PK5_VALUE,
587: NULL AS REVISION_ID, #' ||
588: l_item_org_dl_id || q'# AS DATA_LEVEL_ID
589: FROM
590: MTL_SYSTEM_ITEMS_INTERFACE MSII
591: WHERE ITEM_CATALOG_GROUP_ID IS NOT NULL
592: AND SET_PROCESS_ID = #' || p_batch_id || q'#
593: AND PROCESS_FLAG = 1
594: UNION ALL #';
604: NULL AS PK5_VALUE,
605: MIRI.REVISION_ID, #' ||
606: l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
607: FROM
608: MTL_SYSTEM_ITEMS_INTERFACE MSII,
609: MTL_ITEM_REVISIONS_INTERFACE MIRI
610: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
611: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
612: AND MSII.PROCESS_FLAG = 1
626: NULL AS PK5_VALUE,
627: NULL AS REVISION_ID, #' ||
628: l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
629: FROM
630: MTL_SYSTEM_ITEMS_INTERFACE MSII
631: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
632: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
633: AND MSII.PROCESS_FLAG = 1
634: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
645: NULL AS PK5_VALUE,
646: NULL AS REVISION_ID, #' ||
647: l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
648: FROM
649: MTL_SYSTEM_ITEMS_INTERFACE MSII
650: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
651: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
652: AND MSII.PROCESS_FLAG = 1
653: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y' #';
719: AND MIRI.SET_PROCESS_ID = #' || p_batch_id || q'#
720: AND MIRI.PROCESS_FLAG = 1
721: AND NOT EXISTS
722: (SELECT NULL
723: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
724: WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
725: AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
726: AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
727: AND MSII.PROCESS_FLAG = MIRI.PROCESS_FLAG)
748: ELSE MIRI.REVISION_ID
749: END) REVISION_ID, #' ||
750: l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
751: FROM
752: MTL_SYSTEM_ITEMS_INTERFACE MSII,
753: MTL_ITEM_REVISIONS_INTERFACE MIRI
754: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
755: AND MSII.TEMPLATE_ID IS NOT NULL
756: AND MIRI.INVENTORY_ITEM_ID(+) = MSII.INVENTORY_ITEM_ID
772: NULL AS PK5_VALUE,
773: NULL AS REVISION_ID, #' ||
774: l_item_dl_id || q'# AS DATA_LEVEL_ID
775: FROM
776: MTL_SYSTEM_ITEMS_INTERFACE MSII
777: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
778: AND MSII.TEMPLATE_ID IS NOT NULL
779: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
780: AND MSII.PROCESS_FLAG = 1
791: NULL AS PK5_VALUE,
792: NULL AS REVISION_ID, #' ||
793: l_item_org_dl_id || q'# AS DATA_LEVEL_ID
794: FROM
795: MTL_SYSTEM_ITEMS_INTERFACE MSII
796: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
797: AND MSII.TEMPLATE_ID IS NOT NULL
798: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
799: AND MSII.PROCESS_FLAG = 1 #';
812: NULL AS PK5_VALUE,
813: NULL AS REVISION_ID, #' ||
814: l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
815: FROM
816: MTL_SYSTEM_ITEMS_INTERFACE MSII
817: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
818: AND MSII.TEMPLATE_ID IS NOT NULL
819: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
820: AND MSII.PROCESS_FLAG = 1
832: NULL AS PK5_VALUE,
833: NULL AS REVISION_ID, #' ||
834: l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
835: FROM
836: MTL_SYSTEM_ITEMS_INTERFACE MSII
837: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
838: AND MSII.TEMPLATE_ID IS NOT NULL
839: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
840: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
972: ext_prod.ATTR_GROUP_ID,
973: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
974: FROM
975: EGO_IMPORT_COPY_OPTIONS eico,
976: MTL_SYSTEM_ITEMS_INTERFACE msii,
977: EGO_MTL_SY_ITEMS_EXT_VL ext_prod
978: WHERE eico.COPY_OPTION = 'COPY_ATTR_GROUP:#' || i.DATA_LEVEL_NAME || q'#'
979: AND eico.BATCH_ID = #' || p_batch_id || q'#
980: AND eico.BATCH_ID = msii.SET_PROCESS_ID
988: SELECT
989: ext_prod.ATTR_GROUP_ID
990: FROM
991: EGO_IMPORT_COPY_OPTIONS eico,
992: MTL_SYSTEM_ITEMS_INTERFACE msii,
993: EGO_MTL_SY_ITEMS_EXT_VL ext_prod
994: WHERE eico.COPY_OPTION = 'COPY_ATTR_GROUP:#' || i.DATA_LEVEL_NAME || q'#'
995: AND eico.BATCH_ID = #' || p_batch_id || q'#
996: AND eico.BATCH_ID = msii.SET_PROCESS_ID
1110: NULL AS PK4_VALUE,
1111: NULL AS PK5_VALUE,
1112: #' || l_item_dl_id || q'# AS DATA_LEVEL_ID
1113: FROM
1114: MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
1115: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1116: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1117: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
1118: AND msii.TRANSACTION_TYPE = 'CREATE'
1134: NULL AS PK4_VALUE,
1135: NULL AS PK5_VALUE,
1136: #' || l_item_org_dl_id || q'# AS DATA_LEVEL_ID
1137: FROM
1138: MTL_SYSTEM_ITEMS_INTERFACE msii
1139: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1140: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1141: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
1142: AND msii.TRANSACTION_TYPE = 'CREATE'
1141: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
1142: AND msii.TRANSACTION_TYPE = 'CREATE'
1143: AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
1144: UNION ALL
1145: SELECT /*+ leading(miri) use_nl_with_index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N1) */
1146: miri.TRANSACTION_ID,
1147: miri.INVENTORY_ITEM_ID,
1148: miri.ORGANIZATION_ID,
1149: miri.REVISION_ID,
1156: NULL AS PK4_VALUE,
1157: NULL AS PK5_VALUE,
1158: #' || l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
1159: FROM
1160: MTL_SYSTEM_ITEMS_INTERFACE msii,
1161: MTL_ITEM_REVISIONS_INTERFACE miri
1162: WHERE miri.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
1163: AND miri.ORGANIZATION_ID = msii.ORGANIZATION_ID
1164: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1232: AND (SELECT VARIANT FROM EGO_FND_DSC_FLX_CTX_EXT EXT
1233: WHERE APPLICATION_ID = 431
1234: AND EIUAI.ATTR_GROUP_INT_NAME = DESCRIPTIVE_FLEX_CONTEXT_CODE
1235: AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP') = 'Y'
1236: AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1237: WHERE MSII.INVENTORY_ITEM_ID = EIUAI.INVENTORY_ITEM_ID
1238: AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
1239: AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
1240: AND NVL(MSII.STYLE_ITEM_FLAG, 'N') = 'Y'
1258: NULL AS PK4_VALUE,
1259: NULL AS PK5_VALUE,
1260: #' || l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
1261: FROM
1262: MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
1263: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1264: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1265: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
1266: AND NVL(msii.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
1283: NULL AS PK4_VALUE,
1284: NULL AS PK5_VALUE,
1285: #' || l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
1286: FROM
1287: MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
1288: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1289: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1290: AND NVL(msii.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
1291: AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
1331: WHERE msib.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
1332: AND msib.ORGANIZATION_ID = eiai.ORGANIZATION_ID
1333: ),
1334: (SELECT ITEM_CATALOG_GROUP_ID
1335: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1336: WHERE msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
1337: AND msii.ORGANIZATION_ID = eiai.ORGANIZATION_ID
1338: AND msii.SET_PROCESS_ID = eiai.BATCH_ID
1339: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
1774: 1 PROCESS_FLAG,
1775: MSII.SET_PROCESS_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
2014: END;
2015:
2016: Debug_Conc_Log('Process_Copy_Options_For_Items: l_copy_org='||l_copy_org);
2017: IF l_copy_org = 'Y' THEN
2018: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
2019: (
2020: SET_PROCESS_ID,
2021: PROCESS_FLAG,
2022: TRANSACTION_TYPE,
2041: MSII.SOURCE_SYSTEM_ID,
2042: MSII.SOURCE_SYSTEM_REFERENCE,
2043: MSII.COPY_ITEM_ID
2044: FROM
2045: MTL_SYSTEM_ITEMS_INTERFACE MSII,
2046: MTL_SYSTEM_ITEMS_B MSI,
2047: MTL_PARAMETERS MP
2048: WHERE MSII.SET_PROCESS_ID = p_batch_id
2049: AND MSII.PROCESS_FLAG = 1
2051: AND MSII.COPY_ITEM_ID = MSI.INVENTORY_ITEM_ID
2052: AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
2053: AND MP.MASTER_ORGANIZATION_ID <> MP.ORGANIZATION_ID
2054: AND NOT EXISTS (SELECT NULL
2055: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
2056: WHERE MSII2.SET_PROCESS_ID = MSII.SET_PROCESS_ID
2057: AND MSII2.PROCESS_FLAG = MSII.PROCESS_FLAG
2058: AND (MSII2.ITEM_NUMBER = MSII.ITEM_NUMBER OR MSII2.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID)
2059: AND MSII2.ORGANIZATION_ID = MSI.ORGANIZATION_ID
2126: Debug_Conc_Log('Process_Copy_Options_For_Items: l_copy_sup,l_copy_sup_site,l_copy_sup_site_org='||l_copy_sup||','||l_copy_sup_site||','||l_copy_sup_site_org);
2127: IF (l_copy_sup = 'Y' OR l_copy_sup_site = 'Y' OR l_copy_sup_site_org = 'Y') THEN
2128: BEGIN
2129: SELECT COPY_ITEM_ID INTO l_src_item_id
2130: FROM MTL_SYSTEM_ITEMS_INTERFACE
2131: WHERE SET_PROCESS_ID = p_batch_id
2132: AND PROCESS_FLAG = 1
2133: AND COPY_ITEM_ID IS NOT NULL
2134: AND ROWNUM = 1;
2313: msii.SOURCE_SYSTEM_REFERENCE,
2314: msii.BUNDLE_ID,
2315: msii.TRANSACTION_ID,
2316: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2317: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2318: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
2319: MTL_PARAMETERS mp
2320: WHERE msii.STYLE_ITEM_FLAG = 'N'
2321: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2334: ) #';
2335: l_ag_sql := q'#
2336: SELECT
2337: ext_prod.ATTR_GROUP_ID
2338: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2339: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
2340: MTL_PARAMETERS mp
2341: WHERE msii.STYLE_ITEM_FLAG = 'N'
2342: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2417: msii.SOURCE_SYSTEM_REFERENCE,
2418: msii.BUNDLE_ID,
2419: msii.TRANSACTION_ID,
2420: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2421: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2422: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
2423: MTL_PARAMETERS mp
2424: WHERE msii.STYLE_ITEM_FLAG = 'N'
2425: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2447:
2448: l_ag_sql := q'#
2449: SELECT
2450: ext_prod.ATTR_GROUP_ID
2451: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2452: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
2453: MTL_PARAMETERS mp
2454: WHERE msii.STYLE_ITEM_FLAG = 'N'
2455: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2553: WHERE INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
2554: AND ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
2555: AND dr =1
2556: )
2557: AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
2558: WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
2559: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
2560: AND msii.TRANSACTION_TYPE = 'CREATE'
2561: AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
2580: WHERE INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
2581: AND ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
2582: AND dr =1
2583: )
2584: AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
2585: WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
2586: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
2587: AND msii.TRANSACTION_TYPE = 'CREATE'
2588: AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
2648: msii.BUNDLE_ID,
2649: msii.TRANSACTION_ID,
2650: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2651: FROM
2652: MTL_SYSTEM_ITEMS_INTERFACE msii,
2653: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
2654: MTL_PARAMETERS mp
2655: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2656: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
2675: l_ag_sql := q'#
2676: SELECT
2677: ext_prod.ATTR_GROUP_ID
2678: FROM
2679: MTL_SYSTEM_ITEMS_INTERFACE msii,
2680: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
2681: MTL_PARAMETERS mp
2682: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2683: AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
2754: );
2755:
2756: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
2757: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT msii.STYLE_ITEM_FLAG , msii.STYLE_ITEM_ID
2758: FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
2759: WHERE msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
2760: AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
2761: AND msii.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
2762: AND msii.SET_PROCESS_ID = eiai.BATCH_ID
2765: WHERE eiai.BATCH_ID = p_batch_id
2766: AND eiai.PROCESS_FLAG = 1
2767: AND eiai.TRANSACTION_TYPE = 'CREATE'
2768: AND EXISTS (SELECT 1
2769: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2, MTL_PARAMETERS mp2
2770: WHERE MSII2.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
2771: AND MSII2.ORGANIZATION_ID = mp2.MASTER_ORGANIZATION_ID
2772: AND eiai.ORGANIZATION_ID = mp2.ORGANIZATION_ID
2773: AND MSII2.SET_PROCESS_ID = eiai.BATCH_ID
3227: ext_intf.SOURCE_SYSTEM_REFERENCE,
3228: ext_intf.BUNDLE_ID,
3229: ext_intf.DATA_LEVEL_ID
3230: FROM
3231: MTL_SYSTEM_ITEMS_INTERFACE msii,
3232: EGO_ITM_USR_ATTR_INTRFC ext_intf,
3233: MTL_PARAMETERS mp,
3234: EGO_FND_DSC_FLX_CTX_EXT ag_ext
3235: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
3622: 1 PROCESS_FLAG,
3623: MSII.SET_PROCESS_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
3877: Debug_Conc_Log('Validate_Timestamp_With_Prod: l_item_master_dl_id='||l_item_master_dl_id);
3878:
3879: l_item_detail_tbl := NULL;
3880:
3881: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
3882: SET PROCESS_FLAG = 6,
3883: REQUEST_ID = l_request_id,
3884: PROGRAM_ID = l_prog_id,
3885: PROGRAM_APPLICATION_ID = l_prog_appid,
3909: BULK COLLECT INTO l_item_detail_tbl;
3910:
3911: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated MSII rowcount='||SQL%ROWCOUNT);
3912: IF l_item_detail_tbl IS NOT NULL THEN
3913: Debug_Conc_Log('Validate_Timestamp_With_Prod: Logging errors for MTL_SYSTEM_ITEMS_INTERFACE');
3914: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3915: ERRBUF => l_err_buf,
3916: p_item_detail_tbl => l_item_detail_tbl,
3917: p_show_prod_timestemp => TRUE,
3914: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3915: ERRBUF => l_err_buf,
3916: p_item_detail_tbl => l_item_detail_tbl,
3917: p_show_prod_timestemp => TRUE,
3918: p_intf_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE',
3919: p_user_id => l_user_id,
3920: p_login_id => l_login_id,
3921: p_prog_appid => l_prog_appid,
3922: p_prog_id => l_prog_id,
3936: LAST_UPDATED_BY = l_user_id
3937: WHERE DATA_SET_ID = p_batch_id
3938: AND PROCESS_STATUS = 1
3939: AND ROW_IDENTIFIER IN (SELECT eiuai2.ROW_IDENTIFIER
3940: FROM EGO_INBOUND_MSG_EXT eime, EGO_ITM_USR_ATTR_INTRFC eiuai2, MTL_SYSTEM_ITEMS_INTERFACE msii
3941: WHERE eiuai2.DATA_SET_ID = eiuai.DATA_SET_ID
3942: AND eiuai2.PROCESS_STATUS = 1
3943: AND eime.SOURCE_SYSTEM_ID = eiuai2.SOURCE_SYSTEM_ID
3944: AND eime.DATA_LEVEL_ID = eiuai2.DATA_LEVEL_ID
3994: LAST_UPDATED_BY = l_user_id
3995: WHERE BATCH_ID = p_batch_id
3996: AND PROCESS_FLAG = 1
3997: AND EXISTS (SELECT NULL
3998: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
3999: WHERE eiai.BATCH_ID = msii.SET_PROCESS_ID
4000: AND msii.PROCESS_FLAG = 6
4001: AND eiai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
4002: AND eiai.ORGANIZATION_ID = msii.ORGANIZATION_ID
4043: LAST_UPDATED_BY = l_user_id
4044: WHERE SET_PROCESS_ID = p_batch_id
4045: AND PROCESS_FLAG = 1
4046: AND EXISTS (SELECT NULL
4047: FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
4048: WHERE mici.SET_PROCESS_ID = msii.SET_PROCESS_ID
4049: AND msii.PROCESS_FLAG = 6
4050: AND mici.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
4051: AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
4067: LAST_UPDATED_BY = l_user_id
4068: WHERE BATCH_ID = p_batch_id
4069: AND PROCESS_FLAG = 1
4070: AND EXISTS (SELECT NULL
4071: FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
4072: WHERE bbmi.BATCH_ID = msii.SET_PROCESS_ID
4073: AND msii.PROCESS_FLAG = 6
4074: AND bbmi.ASSEMBLY_ITEM_ID = msii.INVENTORY_ITEM_ID
4075: AND bbmi.ORGANIZATION_ID = msii.ORGANIZATION_ID
4090: LAST_UPDATED_BY = l_user_id
4091: WHERE BATCH_ID = p_batch_id
4092: AND PROCESS_FLAG = 1
4093: AND EXISTS (SELECT NULL
4094: FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
4095: WHERE bici.BATCH_ID = msii.SET_PROCESS_ID
4096: AND msii.PROCESS_FLAG = 6
4097: AND bici.COMPONENT_ITEM_ID = msii.INVENTORY_ITEM_ID
4098: AND bici.ORGANIZATION_ID = msii.ORGANIZATION_ID
4118: (p_batch_id IN NUMBER)
4119: IS
4120: CURSOR cat_in_batch(p_batch_id NUMBER) IS
4121: SELECT DISTINCT msii.item_catalog_group_id AS item_catalog_group_id
4122: FROM mtl_system_items_interface msii,
4123: mtl_parameters mp
4124: WHERE msii.set_process_id = p_batch_id
4125: AND msii.process_flag = 1
4126: AND msii.transaction_type = 'CREATE'
4146: BEGIN
4147: Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Starting. First set org_code into MSII.');
4148:
4149: -- Fix for bug#9660659 Start
4150: --UPDATE mtl_system_items_interface msii
4151: --SET msii.organization_code = (SELECT mp.organization_code
4152: -- FROM mtl_parameters mp
4153: -- WHERE mp.organization_id = msii.organization_id);
4154: --
4153: -- WHERE mp.organization_id = msii.organization_id);
4154: --
4155: IF p_batch_id is not null THEN
4156:
4157: UPDATE mtl_system_items_interface msii
4158: SET msii.organization_code = (SELECT mp.organization_code
4159: FROM mtl_parameters mp
4160: WHERE mp.organization_id = msii.organization_id)
4161: WHERE SET_PROCESS_ID = P_BATCH_ID;
4161: WHERE SET_PROCESS_ID = P_BATCH_ID;
4162:
4163: ELSE
4164:
4165: UPDATE mtl_system_items_interface msii
4166: SET msii.organization_code = (SELECT mp.organization_code
4167: FROM mtl_parameters mp
4168: WHERE mp.organization_id = msii.organization_id);
4169: END IF;
4360: fnd_global.user_id,
4361: SYSDATE
4362: FROM ego_itm_usr_attr_intrfc a,
4363: /* Bug 10112500 - Start */
4364: mtl_system_items_interface msii,
4365: mtl_parameters mp
4366: /* Bug 10112500 - End */
4367: WHERE NOT EXISTS (SELECT NULL
4368: FROM ego_itm_usr_attr_intrfc b
4469: fnd_global.user_id,
4470: SYSDATE,
4471: fnd_global.user_id,
4472: SYSDATE
4473: FROM mtl_system_items_interface msii,
4474: mtl_parameters mp
4475: WHERE msii.set_process_id = p_batch_id
4476: AND msii.process_flag = 1
4477: AND msii.transaction_type = 'CREATE' -- Bug 10112500
4907: SOURCE_SYSTEM_ID,
4908: ITEM_CATALOG_GROUP_ID,
4909: ITEM_CATALOG_GROUP_NAME,
4910: STYLE_ITEM_NUMBER --bug 14319402, add style item number to check for existing style record in MSII
4911: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4912: WHERE SET_PROCESS_ID = p_batch_id
4913: AND PROCESS_FLAG = 1
4914: AND STYLE_ITEM_FLAG IN ('N', 'Y')
4915: AND UPPER(TRANSACTION_TYPE) IN ('CREATE', 'SYNC')
4927: c_source_system_id AS SOURCE_SYSTEM_ID,
4928: MSI.ITEM_CATALOG_GROUP_ID,
4929: NULL,
4930: NULL
4931: FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
4932: WHERE MSII.SET_PROCESS_ID = p_batch_id
4933: AND MSII.PROCESS_FLAG = 1
4934: AND MSII.STYLE_ITEM_FLAG = 'N'
4935: AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
4965: FOR i IN c_intf_records(l_source_system_id) LOOP
4966: IF i.STYLE_ITEM_FLAG = 'Y' THEN
4967: Debug_Conc_Log('Default_Org_Assignments: Defaulting Orgs from Style to SKU for all existing SKUs, style_item_id, org_id='||i.INVENTORY_ITEM_ID ||','||i.ORGANIZATION_ID);
4968: -- creating org assignment for all existing SKUs
4969: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
4970: (
4971: SET_PROCESS_ID,
4972: PROCESS_FLAG,
4973: ORGANIZATION_ID,
4999: AND MSI.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
5000: AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
5001: AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
5002: AND NOT EXISTS (SELECT 1
5003: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
5004: WHERE MSII.SET_PROCESS_ID = p_batch_id
5005: AND MSII.PROCESS_FLAG = 1
5006: AND MSII.ORGANIZATION_ID = i.ORGANIZATION_ID
5007: AND (MSII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID OR MSII.ITEM_NUMBER = MSI.CONCATENATED_SEGMENTS)
5014: Debug_Conc_Log('Default_Org_Assignments: Done rows processed='||SQL%ROWCOUNT);
5015:
5016: -- creating org assignment for all SKUs to be created in MSII
5017: Debug_Conc_Log('Default_Org_Assignments: Defaulting Orgs from Style to SKU for all new SKUs to be created, style_item_id, org_id='||i.INVENTORY_ITEM_ID ||','||i.ORGANIZATION_ID);
5018: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
5019: (
5020: SET_PROCESS_ID,
5021: PROCESS_FLAG,
5022: ORGANIZATION_ID,
5042: 'CREATE',
5043: MSII.ITEM_CATALOG_GROUP_ID,
5044: MSII.ITEM_CATALOG_GROUP_NAME,
5045: -99
5046: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
5047: WHERE MSII.SET_PROCESS_ID = p_batch_id
5048: AND MSII.PROCESS_FLAG = 1
5049: AND MSII.STYLE_ITEM_FLAG = 'N'
5050: AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
5051: AND MSII.ORGANIZATION_ID = MP.ORGANIZATION_ID
5052: AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
5053: AND MSII.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
5054: AND NOT EXISTS (SELECT 1
5055: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
5056: WHERE MSII2.SET_PROCESS_ID = p_batch_id
5057: AND MSII2.PROCESS_FLAG = 1
5058: AND MSII2.ORGANIZATION_ID = i.ORGANIZATION_ID
5059: AND (MSII2.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID OR MSII2.ITEM_NUMBER = MSII.ITEM_NUMBER)
5061:
5062: Debug_Conc_Log('Default_Org_Assignments: Done rows processed='||SQL%ROWCOUNT);
5063: ELSE -- IF i.STYLE_ITEM_FLAG = 'Y' THEN
5064: Debug_Conc_Log('Default_Org_Assignments: Defaulting Orgs from SKU to Style, SKU_Item_Id, style_item_id, org_id='||i.INVENTORY_ITEM_ID ||','||i.STYLE_ITEM_ID || ',' ||i.ORGANIZATION_ID);
5065: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
5066: (
5067: SET_PROCESS_ID,
5068: PROCESS_FLAG,
5069: ORGANIZATION_ID,
5091: i.ITEM_CATALOG_GROUP_NAME,
5092: -99
5093: FROM DUAL
5094: WHERE NOT EXISTS (SELECT 1
5095: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
5096: WHERE MSII2.SET_PROCESS_ID = p_batch_id
5097: AND MSII2.PROCESS_FLAG = 1
5098: AND MSII2.ORGANIZATION_ID = i.ORGANIZATION_ID
5099: AND (MSII2.INVENTORY_ITEM_ID = i.STYLE_ITEM_ID OR MSII2.ITEM_NUMBER = I.STYLE_ITEM_NUMBER) --bug 14319402, need to check for item number as well, since inventory_item_id may be null now
5133: Debug_Conc_Log('Enable_Disable_SKU_Processing: Starting, p_enable_sku_processing='||p_enable_sku_processing);
5134:
5135: IF p_enable_sku_processing = 'F' THEN
5136: Debug_Conc_Log('Enable_Disable_SKU_Processing: Disabling SKUs');
5137: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5138: SET PROCESS_FLAG = 10
5139: WHERE SET_PROCESS_ID = p_batch_id
5140: AND PROCESS_FLAG = 1
5141: AND STYLE_ITEM_FLAG = 'N';
5144: IF SQL%ROWCOUNT > 0 THEN
5145: EGO_WF_WRAPPER_PVT.Set_Item_Bulkload_Bus_Event(p_true_false => p_enable_sku_processing);
5146: END IF;
5147:
5148: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_SYSTEM_ITEMS_INTERFACE Processed rows='||SQL%ROWCOUNT);
5149:
5150: UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
5151: SET PROCESS_FLAG = 10
5152: WHERE SET_PROCESS_ID = p_batch_id
5150: UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
5151: SET PROCESS_FLAG = 10
5152: WHERE SET_PROCESS_ID = p_batch_id
5153: AND PROCESS_FLAG = 1
5154: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5155: WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
5156: AND msii.PROCESS_FLAG = 10
5157: AND ( (msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = miri.ITEM_NUMBER) OR
5158: (msii.SOURCE_SYSTEM_ID = miri.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = miri.SOURCE_SYSTEM_REFERENCE)
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
5179: WHERE msii.SET_PROCESS_ID = mici.SET_PROCESS_ID
5180: AND msii.PROCESS_FLAG = 10
5181: AND ( (msii.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = mici.ITEM_NUMBER) OR
5182: (msii.SOURCE_SYSTEM_ID = mici.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = mici.SOURCE_SYSTEM_REFERENCE)
5202: UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
5203: SET PROCESS_STATUS = 1.5
5204: WHERE DATA_SET_ID = p_batch_id
5205: AND PROCESS_STATUS = 1
5206: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5207: WHERE msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
5208: AND msii.PROCESS_FLAG = 10
5209: AND ( (msii.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eiuai.ITEM_NUMBER) OR
5210: (msii.SOURCE_SYSTEM_ID = eiuai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiuai.SOURCE_SYSTEM_REFERENCE)
5226: UPDATE EGO_AML_INTF eai
5227: SET PROCESS_FLAG = 10
5228: WHERE DATA_SET_ID = p_batch_id
5229: AND PROCESS_FLAG = 1
5230: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5231: WHERE msii.SET_PROCESS_ID = eai.DATA_SET_ID
5232: AND msii.PROCESS_FLAG = 10
5233: AND ( (msii.INVENTORY_ITEM_ID = eai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eai.ITEM_NUMBER) OR
5234: (msii.SOURCE_SYSTEM_ID = eai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eai.SOURCE_SYSTEM_REFERENCE)
5250: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
5251: SET PROCESS_FLAG = 10
5252: WHERE BATCH_ID = p_batch_id
5253: AND PROCESS_FLAG = 1
5254: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5255: WHERE msii.SET_PROCESS_ID = eiai.BATCH_ID
5256: AND msii.PROCESS_FLAG = 10
5257: AND ( (msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eiai.ITEM_NUMBER) OR
5258: (msii.SOURCE_SYSTEM_ID = eiai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiai.SOURCE_SYSTEM_REFERENCE)
5270: UPDATE EGO_ITEM_PEOPLE_INTF eipi
5271: SET PROCESS_STATUS = 10
5272: WHERE DATA_SET_ID = p_batch_id
5273: AND PROCESS_STATUS = 1
5274: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5275: WHERE msii.SET_PROCESS_ID = eipi.DATA_SET_ID
5276: AND msii.PROCESS_FLAG = 10
5277: AND ( (msii.INVENTORY_ITEM_ID = eipi.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eipi.ITEM_NUMBER) OR
5278: (msii.SOURCE_SYSTEM_ID = eipi.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eipi.SOURCE_SYSTEM_REFERENCE)
5296:
5297: --Maybe set to T if batch contains ONLY Styles, to prevent raising again in such cases
5298: EGO_WF_WRAPPER_PVT.Set_Item_Bulkload_Bus_Event(p_true_false => 'F');
5299:
5300: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5301: SET PROCESS_FLAG = 1
5302: WHERE SET_PROCESS_ID = p_batch_id
5303: AND PROCESS_FLAG = 10
5304: AND STYLE_ITEM_FLAG = 'N';
5312: --R12C Business Events to be fired only ONCE although Batch Import is called twice for Styles and then SKUs
5313: IF SQL%ROWCOUNT > 0 THEN
5314: EGO_WF_WRAPPER_PVT.Set_Item_Bulkload_Bus_Event(p_true_false => p_enable_sku_processing);
5315: END IF;
5316: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_SYSTEM_ITEMS_INTERFACE Processed rows='||SQL%ROWCOUNT);
5317:
5318: IF SQL%ROWCOUNT > 0 THEN
5319: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
5320: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
5315: END IF;
5316: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_SYSTEM_ITEMS_INTERFACE Processed rows='||SQL%ROWCOUNT);
5317:
5318: IF SQL%ROWCOUNT > 0 THEN
5319: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
5320: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
5321: FROM MTL_SYSTEM_ITEMS_KFV MSIK
5322: WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
5323: AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
5332:
5333: /* at the time when the execution reaches this point
5334: * the transaction type SYNC is converted to either CREATE
5335: * or UPDATE already */
5336: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
5337: SET COPY_ITEM_ID = STYLE_ITEM_ID,
5338: COPY_ORGANIZATION_ID = ORGANIZATION_ID,
5339: TEMPLATE_ID = DECODE(TRANSACTION_TYPE,'CREATE',NULL,TEMPLATE_ID), -- change for bug 12376516
5340: TEMPLATE_NAME = DECODE(TRANSACTION_TYPE,'CREATE',NULL,TEMPLATE_NAME) -- change for bug 12376516
5545: grants.GRANTEE_TYPE,
5546: grants.GRANTEE_KEY,
5547: grants.END_DATE
5548: FROM
5549: MTL_SYSTEM_ITEMS_INTERFACE msii,
5550: FND_GRANTS grants,
5551: FND_MENUS menus
5552: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
5553: AND msii.PROCESS_FLAG = 7
5757: AND mirb.ORGANIZATION_ID = msii.ORGANIZATION_ID
5758: AND mirb.EFFECTIVITY_DATE <= SYSDATE
5759: ) AS DEST_REVISION_ID
5760: FROM
5761: MTL_SYSTEM_ITEMS_INTERFACE msii,
5762: MTL_PARAMETERS mp
5763: WHERE msii.SET_PROCESS_ID = p_batch_id
5764: AND msii.PROCESS_FLAG = 7
5765: AND msii.TRANSACTION_TYPE = 'CREATE'
5881: l_err_txt VARCHAR2(4000);
5882: l_ret_code VARCHAR2(100);
5883: BEGIN
5884: -- nulling out all the segments
5885: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5886: SET ITEM_NUMBER = p_item_number,
5887: SEGMENT1 = NULL,
5888: SEGMENT2 = NULL,
5889: SEGMENT3 = NULL,
6075: FROM mtl_item_templates
6076: WHERE template_name = cp_template_name;
6077:
6078: CURSOR c_batch_exists IS
6079: SELECT 1 FROM mtl_system_items_interface
6080: WHERE set_process_id = p_batch_id
6081: AND process_flag = 1;
6082:
6083: l_template_id NUMBER;
6211: AND application_id = 431);
6212:
6213: CURSOR c_item_details
6214: IS
6215: SELECT style_item_flag,mtl_system_items_interface_s.NEXTVAL,
6216: item_catalog_group_id
6217: FROM mtl_system_items_interface
6218: WHERE item_number = p_item_number
6219: AND organization_id = p_organization_id
6213: CURSOR c_item_details
6214: IS
6215: SELECT style_item_flag,mtl_system_items_interface_s.NEXTVAL,
6216: item_catalog_group_id
6217: FROM mtl_system_items_interface
6218: WHERE item_number = p_item_number
6219: AND organization_id = p_organization_id
6220: AND set_process_id = p_batch_id
6221: AND process_flag = 1;
6339: )
6340: VALUES
6341: (
6342: 2
6343: ,MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
6344: ,p_batch_id
6345: ,'SYNC'
6346: ,p_item_number
6347: ,p_organization_id
6383: x_rev_err_table OUT NOCOPY EGO_VARCHAR_TBL_TYPE,
6384: x_uda_err_table OUT NOCOPY EGO_VARCHAR_TBL_TYPE)
6385: IS
6386: TYPE transaction_table_type IS TABLE OF
6387: mtl_system_items_interface.transaction_type%TYPE
6388: INDEX BY BINARY_INTEGER;
6389:
6390: transaction_table transaction_table_type;
6391: l_item_err_table EGO_VARCHAR_TBL_TYPE;
6396: BEGIN
6397: SELECT error_message BULK COLLECT INTO l_item_err_table
6398: FROM mtl_interface_errors
6399: WHERE transaction_id IN ( SELECT transaction_id
6400: FROM mtl_system_items_interface
6401: WHERE set_process_id = p_batch_id
6402: AND process_flag = 3);
6403: EXCEPTION
6404: WHEN no_data_found THEN
6489: RETURN;
6490: END;
6491:
6492: Debug_Conc_Log('Validate_Timestamp_In_Batch: l_item_master_dl_id='||l_item_master_dl_id);
6493: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
6494: SET PROCESS_FLAG = 14
6495: WHERE SET_PROCESS_ID = p_batch_id
6496: AND PROCESS_FLAG = 4
6497: AND BUNDLE_ID = (SELECT MAX(BUNDLE_ID) KEEP (DENSE_RANK FIRST ORDER BY MESSAGE_TIMESTAMP DESC )
6494: SET PROCESS_FLAG = 14
6495: WHERE SET_PROCESS_ID = p_batch_id
6496: AND PROCESS_FLAG = 4
6497: AND BUNDLE_ID = (SELECT MAX(BUNDLE_ID) KEEP (DENSE_RANK FIRST ORDER BY MESSAGE_TIMESTAMP DESC )
6498: FROM MTL_SYSTEM_ITEMS_INTERFACE msii2
6499: WHERE msii2.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
6500: AND msii2.ORGANIZATION_ID = msii.ORGANIZATION_ID
6501: AND msii2.SET_PROCESS_ID = msii.SET_PROCESS_ID
6502: AND msii2.PROCESS_FLAG = msii.PROCESS_FLAG
6502: AND msii2.PROCESS_FLAG = msii.PROCESS_FLAG
6503: );
6504:
6505: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 14, rowcount='||SQL%ROWCOUNT);
6506: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
6507: SET PROCESS_FLAG = DECODE(PROCESS_FLAG, 4, 16, 4)
6508: WHERE SET_PROCESS_ID = p_batch_id
6509: AND PROCESS_FLAG IN ( 4, 14 );
6510:
6517: LAST_UPDATED_BY = l_user_id
6518: WHERE SET_PROCESS_ID = p_batch_id
6519: AND PROCESS_FLAG = 1
6520: AND ROWID <> (SELECT MIN(miri2.ROWID)
6521: FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_ITEM_REVISIONS_INTERFACE miri2
6522: WHERE msii.INVENTORY_ITEM_ID = miri2.INVENTORY_ITEM_ID
6523: AND msii.ORGANIZATION_ID = miri2.ORGANIZATION_ID
6524: AND msii.SET_PROCESS_ID = miri2.SET_PROCESS_ID
6525: AND msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
6544: LAST_UPDATED_BY = l_user_id
6545: WHERE DATA_SET_ID = p_batch_id
6546: AND PROCESS_STATUS = 2
6547: AND EXISTS (SELECT NULL
6548: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
6549: WHERE msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
6550: AND msii.PROCESS_FLAG = 16
6551: AND eiuai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
6552: AND eiuai.ORGANIZATION_ID = msii.ORGANIZATION_ID
6593: LAST_UPDATED_BY = l_user_id
6594: WHERE BATCH_ID = p_batch_id
6595: AND PROCESS_FLAG = 1
6596: AND EXISTS (SELECT NULL
6597: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
6598: WHERE msii.SET_PROCESS_ID = eiai.BATCH_ID
6599: AND msii.PROCESS_FLAG = 16
6600: AND eiai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
6601: AND eiai.ORGANIZATION_ID = msii.ORGANIZATION_ID
6642: LAST_UPDATED_BY = l_user_id
6643: WHERE SET_PROCESS_ID = p_batch_id
6644: AND PROCESS_FLAG = 1
6645: AND EXISTS (SELECT NULL
6646: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
6647: WHERE msii.SET_PROCESS_ID = mici.SET_PROCESS_ID
6648: AND msii.PROCESS_FLAG = 16
6649: AND mici.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
6650: AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
6661: LAST_UPDATED_BY = l_user_id
6662: WHERE BATCH_ID = p_batch_id
6663: AND PROCESS_FLAG = 1
6664: AND EXISTS (SELECT NULL
6665: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
6666: WHERE msii.SET_PROCESS_ID = bbmi.BATCH_ID
6667: AND msii.PROCESS_FLAG = 16
6668: AND bbmi.ASSEMBLY_ITEM_ID = msii.INVENTORY_ITEM_ID
6669: AND bbmi.ORGANIZATION_ID = msii.ORGANIZATION_ID
6680: LAST_UPDATED_BY = l_user_id
6681: WHERE BATCH_ID = p_batch_id
6682: AND PROCESS_FLAG = 1
6683: AND EXISTS (SELECT NULL
6684: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
6685: WHERE msii.SET_PROCESS_ID = bici.BATCH_ID
6686: AND msii.PROCESS_FLAG = 16
6687: AND bici.COMPONENT_ITEM_ID = msii.INVENTORY_ITEM_ID
6688: AND bici.ORGANIZATION_ID = msii.ORGANIZATION_ID
6690: );
6691:
6692: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated BOM_INVENTORY_COMPS_INTERFACE rowcount='||SQL%ROWCOUNT);
6693:
6694: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
6695: SET PROCESS_FLAG = 6,
6696: REQUEST_ID = l_request_id,
6697: PROGRAM_ID = l_prog_id,
6698: PROGRAM_APPLICATION_ID = l_prog_appid,
6714: BULK COLLECT INTO l_item_detail_tbl;
6715:
6716: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 6, rowcount='||SQL%ROWCOUNT);
6717: IF l_item_detail_tbl IS NOT NULL THEN
6718: Debug_Conc_Log('Validate_Timestamp_In_Batch: Logging errors for MTL_SYSTEM_ITEMS_INTERFACE');
6719: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6720: ERRBUF => l_err_buf,
6721: p_item_detail_tbl => l_item_detail_tbl,
6722: p_show_prod_timestemp => FALSE,
6719: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6720: ERRBUF => l_err_buf,
6721: p_item_detail_tbl => l_item_detail_tbl,
6722: p_show_prod_timestemp => FALSE,
6723: p_intf_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE',
6724: p_user_id => l_user_id,
6725: p_login_id => l_login_id,
6726: p_prog_appid => l_prog_appid,
6727: p_prog_id => l_prog_id,
6793: ORGANIZATION_ID,
6794: NULL AS SUPPLIER_ID,
6795: NULL AS SUPPLIER_SITE_ID,
6796: MAX(MESSAGE_TIMESTAMP) AS MESSAGE_TIMESTAMP
6797: FROM MTL_SYSTEM_ITEMS_INTERFACE
6798: WHERE SET_PROCESS_ID = p_batch_id
6799: AND PROCESS_FLAG = 7
6800: AND REQUEST_ID = l_request_id
6801: GROUP BY SOURCE_SYSTEM_ID, l_item_master_dl_id, INVENTORY_ITEM_ID, ORGANIZATION_ID
6807: eiuai.ORGANIZATION_ID,
6808: eiuai.PK1_VALUE AS SUPPLIER_ID,
6809: eiuai.PK2_VALUE AS SUPPLIER_SITE_ID,
6810: MAX(msii.MESSAGE_TIMESTAMP) AS MESSAGE_TIMESTAMP
6811: FROM EGO_ITM_USR_ATTR_INTRFC eiuai, MTL_SYSTEM_ITEMS_INTERFACE msii
6812: WHERE eiuai.DATA_SET_ID = p_batch_id
6813: AND eiuai.PROCESS_STATUS = 4
6814: AND eiuai.REQUEST_ID = l_request_id
6815: AND msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
6887: SELECT msii.ORGANIZATION_ID AS DEST_ORG_ID,
6888: msii.INVENTORY_ITEM_ID AS DEST_ITEM_ID,
6889: msii.COPY_ORGANIZATION_ID AS SOURCE_ORG_ID,
6890: msii.COPY_ITEM_ID AS SOURCE_ITEM_ID
6891: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
6892: MTL_PARAMETERS mp
6893: WHERE msii.SET_PROCESS_ID = p_batch_id
6894: AND msii.PROCESS_FLAG = 7
6895: AND msii.TRANSACTION_TYPE = 'CREATE'
6966: WHERE EXISTS (SELECT NULL
6967: FROM
6968: EGO_FND_DSC_FLX_CTX_EXT ag_ext,
6969: EGO_OBJ_AG_ASSOCS_B assoc,
6970: MTL_SYSTEM_ITEMS_INTERFACE msii
6971: WHERE msii.INVENTORY_ITEM_ID = esvas.SKU_ITEM_ID
6972: AND msii.STYLE_ITEM_ID = esvas.STYLE_ITEM_ID
6973: AND msii.ORGANIZATION_ID = esvas.ORGANIZATION_ID
6974: AND msii.SET_PROCESS_ID = p_batch_id
7002: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE) IS
7003:
7004: CURSOR Items_in_batch(p_batch_id NUMBER) is
7005: select UNIQUE SOURCE_SYSTEM_REFERENCE
7006: from MTL_SYSTEM_ITEMS_INTERFACE msii
7007: WHERE PROCESS_FLAG = 0
7008: AND SET_PROCESS_ID = p_batch_id
7009: AND ORGANIZATION_ID IS NOT NULL;
7010:
7014: from EGO_ITM_USR_ATTR_INTRFC where transaction_id IN(SELECT transaction_id
7015: FROM
7016: ( SELECT
7017: msii.transaction_id
7018: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
7019: WHERE PROCESS_FLAG = 0
7020: AND SET_PROCESS_ID = p_batch_id
7021: AND SOURCE_SYSTEM_REFERENCE =source_system_ref_check
7022: AND ORGANIZATION_ID IS NOT NULL
7077: If I2.rnk=1 then
7078:
7079: -- Exact copy of same item , we can delete it
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:
7087:
7088: -- As we have ordered by LAST_MODIFIED_DATE we can delete rows having rank >1
7089:
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: