[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,
148: WHERE MSIK.CONCATENATED_SEGMENTS = MIRI.ITEM_NUMBER
149: AND MSIK.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
150: ),
151: (SELECT INVENTORY_ITEM_ID
152: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
153: WHERE (MSII.ITEM_NUMBER = MIRI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE)
154: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
155: AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
156: AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
170: WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
171: AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
172: ),
173: (SELECT INVENTORY_ITEM_ID
174: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
175: WHERE (MSII.ITEM_NUMBER = MICI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE)
176: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
177: AND MSII.ORGANIZATION_ID = MICI.ORGANIZATION_ID
178: AND MSII.SET_PROCESS_ID = MICI.SET_PROCESS_ID
203: WHERE MSIK.CONCATENATED_SEGMENTS = EIUAI.ITEM_NUMBER
204: AND MSIK.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
205: ),
206: (SELECT INVENTORY_ITEM_ID
207: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
208: WHERE (MSII.ITEM_NUMBER = EIUAI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE)
209: AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
210: AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
211: AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
225: WHERE MSIK.CONCATENATED_SEGMENTS = EIAI.ITEM_NUMBER
226: AND MSIK.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
227: ),
228: (SELECT INVENTORY_ITEM_ID
229: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
230: WHERE (MSII.ITEM_NUMBER = EIAI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE)
231: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
232: AND MSII.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
233: AND MSII.SET_PROCESS_ID = EIAI.BATCH_ID
511: NULL AS PK5_VALUE,
512: NULL AS REVISION_ID, #' ||
513: l_item_dl_id || q'# AS DATA_LEVEL_ID
514: FROM
515: MTL_SYSTEM_ITEMS_INTERFACE MSII
516: WHERE ITEM_CATALOG_GROUP_ID IS NOT NULL
517: AND SET_PROCESS_ID = #' || p_batch_id || q'#
518: AND PROCESS_FLAG = 1
519: UNION ALL #';
529: NULL AS PK5_VALUE,
530: MIRI.REVISION_ID, #' ||
531: l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
532: FROM
533: MTL_SYSTEM_ITEMS_INTERFACE MSII,
534: MTL_ITEM_REVISIONS_INTERFACE MIRI
535: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
536: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
537: AND MSII.PROCESS_FLAG = 1
551: NULL AS PK5_VALUE,
552: NULL AS REVISION_ID, #' ||
553: l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
554: FROM
555: MTL_SYSTEM_ITEMS_INTERFACE MSII
556: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
557: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
558: AND MSII.PROCESS_FLAG = 1
559: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
570: NULL AS PK5_VALUE,
571: NULL AS REVISION_ID, #' ||
572: l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
573: FROM
574: MTL_SYSTEM_ITEMS_INTERFACE MSII
575: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
576: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
577: AND MSII.PROCESS_FLAG = 1
578: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y' #';
639: AND MIRI.SET_PROCESS_ID = #' || p_batch_id || q'#
640: AND MIRI.PROCESS_FLAG = 1
641: AND NOT EXISTS
642: (SELECT NULL
643: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
644: WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
645: AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
646: AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
647: AND MSII.PROCESS_FLAG = MIRI.PROCESS_FLAG)
666: ELSE MIRI.REVISION_ID
667: END) REVISION_ID, #' ||
668: l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
669: FROM
670: MTL_SYSTEM_ITEMS_INTERFACE MSII,
671: MTL_ITEM_REVISIONS_INTERFACE MIRI
672: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
673: AND MSII.TEMPLATE_ID IS NOT NULL
674: AND MIRI.INVENTORY_ITEM_ID(+) = MSII.INVENTORY_ITEM_ID
690: NULL AS PK5_VALUE,
691: NULL AS REVISION_ID, #' ||
692: l_item_dl_id || q'# AS DATA_LEVEL_ID
693: FROM
694: MTL_SYSTEM_ITEMS_INTERFACE MSII
695: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
696: AND MSII.TEMPLATE_ID IS NOT NULL
697: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
698: AND MSII.PROCESS_FLAG = 1 #';
711: NULL AS PK5_VALUE,
712: NULL AS REVISION_ID, #' ||
713: l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
714: FROM
715: MTL_SYSTEM_ITEMS_INTERFACE MSII
716: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
717: AND MSII.TEMPLATE_ID IS NOT NULL
718: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
719: AND MSII.PROCESS_FLAG = 1
731: NULL AS PK5_VALUE,
732: NULL AS REVISION_ID, #' ||
733: l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
734: FROM
735: MTL_SYSTEM_ITEMS_INTERFACE MSII
736: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
737: AND MSII.TEMPLATE_ID IS NOT NULL
738: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
739: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
871: ext_prod.ATTR_GROUP_ID,
872: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
873: FROM
874: EGO_IMPORT_COPY_OPTIONS eico,
875: MTL_SYSTEM_ITEMS_INTERFACE msii,
876: EGO_MTL_SY_ITEMS_EXT_VL ext_prod
877: WHERE eico.COPY_OPTION = 'COPY_ATTR_GROUP:#' || i.DATA_LEVEL_NAME || q'#'
878: AND eico.BATCH_ID = #' || p_batch_id || q'#
879: AND eico.BATCH_ID = msii.SET_PROCESS_ID
887: SELECT
888: ext_prod.ATTR_GROUP_ID
889: FROM
890: EGO_IMPORT_COPY_OPTIONS eico,
891: MTL_SYSTEM_ITEMS_INTERFACE msii,
892: EGO_MTL_SY_ITEMS_EXT_VL ext_prod
893: WHERE eico.COPY_OPTION = 'COPY_ATTR_GROUP:#' || i.DATA_LEVEL_NAME || q'#'
894: AND eico.BATCH_ID = #' || p_batch_id || q'#
895: AND eico.BATCH_ID = msii.SET_PROCESS_ID
1000: NULL AS PK4_VALUE,
1001: NULL AS PK5_VALUE,
1002: #' || l_item_dl_id || q'# AS DATA_LEVEL_ID
1003: FROM
1004: MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
1005: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1006: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1007: AND msii.PROCESS_FLAG = 1
1008: AND msii.TRANSACTION_TYPE = 'CREATE'
1024: NULL AS PK4_VALUE,
1025: NULL AS PK5_VALUE,
1026: #' || l_item_org_dl_id || q'# AS DATA_LEVEL_ID
1027: FROM
1028: MTL_SYSTEM_ITEMS_INTERFACE msii
1029: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1030: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1031: AND msii.PROCESS_FLAG = 1
1032: AND msii.TRANSACTION_TYPE = 'CREATE'
1046: NULL AS PK4_VALUE,
1047: NULL AS PK5_VALUE,
1048: #' || l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
1049: FROM
1050: MTL_SYSTEM_ITEMS_INTERFACE msii,
1051: MTL_ITEM_REVISIONS_INTERFACE miri
1052: WHERE miri.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
1053: AND miri.ORGANIZATION_ID = msii.ORGANIZATION_ID
1054: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1101: NULL AS PK4_VALUE,
1102: NULL AS PK5_VALUE,
1103: #' || l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
1104: FROM
1105: MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
1106: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1107: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1108: AND msii.PROCESS_FLAG = 1
1109: AND NVL(msii.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
1126: NULL AS PK4_VALUE,
1127: NULL AS PK5_VALUE,
1128: #' || l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
1129: FROM
1130: MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
1131: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
1132: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
1133: AND NVL(msii.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
1134: AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
1174: WHERE msib.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
1175: AND msib.ORGANIZATION_ID = eiai.ORGANIZATION_ID
1176: ),
1177: (SELECT ITEM_CATALOG_GROUP_ID
1178: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1179: WHERE msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
1180: AND msii.ORGANIZATION_ID = eiai.ORGANIZATION_ID
1181: AND msii.SET_PROCESS_ID = eiai.BATCH_ID
1182: AND msii.PROCESS_FLAG = 1
1604: 1 PROCESS_FLAG,
1605: MSII.SET_PROCESS_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
1844: END;
1845:
1846: Debug_Conc_Log('Process_Copy_Options_For_Items: l_copy_org='||l_copy_org);
1847: IF l_copy_org = 'Y' THEN
1848: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
1849: (
1850: SET_PROCESS_ID,
1851: PROCESS_FLAG,
1852: TRANSACTION_TYPE,
1871: MSII.SOURCE_SYSTEM_ID,
1872: MSII.SOURCE_SYSTEM_REFERENCE,
1873: MSII.COPY_ITEM_ID
1874: FROM
1875: MTL_SYSTEM_ITEMS_INTERFACE MSII,
1876: MTL_SYSTEM_ITEMS_B MSI,
1877: MTL_PARAMETERS MP
1878: WHERE MSII.SET_PROCESS_ID = p_batch_id
1879: AND MSII.PROCESS_FLAG = 1
1881: AND MSII.COPY_ITEM_ID = MSI.INVENTORY_ITEM_ID
1882: AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
1883: AND MP.MASTER_ORGANIZATION_ID <> MP.ORGANIZATION_ID
1884: AND NOT EXISTS (SELECT NULL
1885: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
1886: WHERE MSII2.SET_PROCESS_ID = MSII.SET_PROCESS_ID
1887: AND MSII2.PROCESS_FLAG = MSII.PROCESS_FLAG
1888: AND (MSII2.ITEM_NUMBER = MSII.ITEM_NUMBER OR MSII2.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID)
1889: AND MSII2.ORGANIZATION_ID = MSI.ORGANIZATION_ID
1956: 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);
1957: IF (l_copy_sup = 'Y' OR l_copy_sup_site = 'Y' OR l_copy_sup_site_org = 'Y') THEN
1958: BEGIN
1959: SELECT COPY_ITEM_ID INTO l_src_item_id
1960: FROM MTL_SYSTEM_ITEMS_INTERFACE
1961: WHERE SET_PROCESS_ID = p_batch_id
1962: AND PROCESS_FLAG = 1
1963: AND COPY_ITEM_ID IS NOT NULL
1964: AND ROWNUM = 1;
2139: msii.SOURCE_SYSTEM_REFERENCE,
2140: msii.BUNDLE_ID,
2141: msii.TRANSACTION_ID,
2142: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2143: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2144: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
2145: MTL_PARAMETERS mp
2146: WHERE msii.STYLE_ITEM_FLAG = 'N'
2147: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2160: ) #';
2161: l_ag_sql := q'#
2162: SELECT
2163: ext_prod.ATTR_GROUP_ID
2164: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2165: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
2166: MTL_PARAMETERS mp
2167: WHERE msii.STYLE_ITEM_FLAG = 'N'
2168: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2242: msii.SOURCE_SYSTEM_REFERENCE,
2243: msii.BUNDLE_ID,
2244: msii.TRANSACTION_ID,
2245: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2246: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2247: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
2248: MTL_PARAMETERS mp
2249: WHERE msii.STYLE_ITEM_FLAG = 'N'
2250: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2270:
2271: l_ag_sql := q'#
2272: SELECT
2273: ext_prod.ATTR_GROUP_ID
2274: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
2275: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
2276: MTL_PARAMETERS mp
2277: WHERE msii.STYLE_ITEM_FLAG = 'N'
2278: AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2366: WHERE mirb.EFFECTIVITY_DATE <= SYSDATE
2367: AND mirb.INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
2368: AND mirb.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
2369: )
2370: AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
2371: WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
2372: AND msii.PROCESS_FLAG = 1
2373: AND msii.TRANSACTION_TYPE = 'CREATE'
2374: AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
2391: WHERE mirb.EFFECTIVITY_DATE <= SYSDATE
2392: AND mirb.INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
2393: AND mirb.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
2394: )
2395: AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
2396: WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
2397: AND msii.PROCESS_FLAG = 1
2398: AND msii.TRANSACTION_TYPE = 'CREATE'
2399: AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
2459: msii.BUNDLE_ID,
2460: msii.TRANSACTION_ID,
2461: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2462: FROM
2463: MTL_SYSTEM_ITEMS_INTERFACE msii,
2464: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
2465: MTL_PARAMETERS mp
2466: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2467: AND msii.PROCESS_FLAG = 1
2486: l_ag_sql := q'#
2487: SELECT
2488: ext_prod.ATTR_GROUP_ID
2489: FROM
2490: MTL_SYSTEM_ITEMS_INTERFACE msii,
2491: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
2492: MTL_PARAMETERS mp
2493: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
2494: AND msii.PROCESS_FLAG = 1
2565: );
2566:
2567: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
2568: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT msii.STYLE_ITEM_FLAG , msii.STYLE_ITEM_ID
2569: FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
2570: WHERE msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
2571: AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
2572: AND msii.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
2573: AND msii.SET_PROCESS_ID = eiai.BATCH_ID
2576: WHERE eiai.BATCH_ID = p_batch_id
2577: AND eiai.PROCESS_FLAG = 1
2578: AND eiai.TRANSACTION_TYPE = 'CREATE'
2579: AND EXISTS (SELECT 1
2580: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2, MTL_PARAMETERS mp2
2581: WHERE MSII2.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
2582: AND MSII2.ORGANIZATION_ID = mp2.MASTER_ORGANIZATION_ID
2583: AND eiai.ORGANIZATION_ID = mp2.ORGANIZATION_ID
2584: AND MSII2.SET_PROCESS_ID = eiai.BATCH_ID
3037: ext_intf.SOURCE_SYSTEM_REFERENCE,
3038: ext_intf.BUNDLE_ID,
3039: ext_intf.DATA_LEVEL_ID
3040: FROM
3041: MTL_SYSTEM_ITEMS_INTERFACE msii,
3042: EGO_ITM_USR_ATTR_INTRFC ext_intf,
3043: MTL_PARAMETERS mp,
3044: EGO_FND_DSC_FLX_CTX_EXT ag_ext
3045: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
3431: 1 PROCESS_FLAG,
3432: MSII.SET_PROCESS_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
3674: Debug_Conc_Log('Validate_Timestamp_With_Prod: l_item_master_dl_id='||l_item_master_dl_id);
3675:
3676: l_item_detail_tbl := NULL;
3677:
3678: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
3679: SET PROCESS_FLAG = 6,
3680: REQUEST_ID = l_request_id,
3681: PROGRAM_ID = l_prog_id,
3682: PROGRAM_APPLICATION_ID = l_prog_appid,
3706: BULK COLLECT INTO l_item_detail_tbl;
3707:
3708: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated MSII rowcount='||SQL%ROWCOUNT);
3709: IF l_item_detail_tbl IS NOT NULL THEN
3710: Debug_Conc_Log('Validate_Timestamp_With_Prod: Logging errors for MTL_SYSTEM_ITEMS_INTERFACE');
3711: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3712: ERRBUF => l_err_buf,
3713: p_item_detail_tbl => l_item_detail_tbl,
3714: p_show_prod_timestemp => TRUE,
3711: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3712: ERRBUF => l_err_buf,
3713: p_item_detail_tbl => l_item_detail_tbl,
3714: p_show_prod_timestemp => TRUE,
3715: p_intf_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE',
3716: p_user_id => l_user_id,
3717: p_login_id => l_login_id,
3718: p_prog_appid => l_prog_appid,
3719: p_prog_id => l_prog_id,
3733: LAST_UPDATED_BY = l_user_id
3734: WHERE DATA_SET_ID = p_batch_id
3735: AND PROCESS_STATUS = 1
3736: AND ROW_IDENTIFIER IN (SELECT eiuai2.ROW_IDENTIFIER
3737: FROM EGO_INBOUND_MSG_EXT eime, EGO_ITM_USR_ATTR_INTRFC eiuai2, MTL_SYSTEM_ITEMS_INTERFACE msii
3738: WHERE eiuai2.DATA_SET_ID = eiuai.DATA_SET_ID
3739: AND eiuai2.PROCESS_STATUS = 1
3740: AND eime.SOURCE_SYSTEM_ID = eiuai2.SOURCE_SYSTEM_ID
3741: AND eime.DATA_LEVEL_ID = eiuai2.DATA_LEVEL_ID
3791: LAST_UPDATED_BY = l_user_id
3792: WHERE BATCH_ID = p_batch_id
3793: AND PROCESS_FLAG = 1
3794: AND EXISTS (SELECT NULL
3795: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
3796: WHERE eiai.BATCH_ID = msii.SET_PROCESS_ID
3797: AND msii.PROCESS_FLAG = 6
3798: AND eiai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
3799: AND eiai.ORGANIZATION_ID = msii.ORGANIZATION_ID
3840: LAST_UPDATED_BY = l_user_id
3841: WHERE SET_PROCESS_ID = p_batch_id
3842: AND PROCESS_FLAG = 1
3843: AND EXISTS (SELECT NULL
3844: FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
3845: WHERE mici.SET_PROCESS_ID = msii.SET_PROCESS_ID
3846: AND msii.PROCESS_FLAG = 6
3847: AND mici.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
3848: AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
3864: LAST_UPDATED_BY = l_user_id
3865: WHERE BATCH_ID = p_batch_id
3866: AND PROCESS_FLAG = 1
3867: AND EXISTS (SELECT NULL
3868: FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
3869: WHERE bbmi.BATCH_ID = msii.SET_PROCESS_ID
3870: AND msii.PROCESS_FLAG = 6
3871: AND bbmi.ASSEMBLY_ITEM_ID = msii.INVENTORY_ITEM_ID
3872: AND bbmi.ORGANIZATION_ID = msii.ORGANIZATION_ID
3887: LAST_UPDATED_BY = l_user_id
3888: WHERE BATCH_ID = p_batch_id
3889: AND PROCESS_FLAG = 1
3890: AND EXISTS (SELECT NULL
3891: FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
3892: WHERE bici.BATCH_ID = msii.SET_PROCESS_ID
3893: AND msii.PROCESS_FLAG = 6
3894: AND bici.COMPONENT_ITEM_ID = msii.INVENTORY_ITEM_ID
3895: AND bici.ORGANIZATION_ID = msii.ORGANIZATION_ID
4211: STYLE_ITEM_ID,
4212: SOURCE_SYSTEM_ID,
4213: ITEM_CATALOG_GROUP_ID,
4214: ITEM_CATALOG_GROUP_NAME
4215: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4216: WHERE SET_PROCESS_ID = p_batch_id
4217: AND PROCESS_FLAG = 1
4218: AND STYLE_ITEM_FLAG IN ('N', 'Y')
4219: AND UPPER(TRANSACTION_TYPE) IN ('CREATE', 'SYNC')
4230: MSI.INVENTORY_ITEM_ID AS STYLE_ITEM_ID,
4231: c_source_system_id AS SOURCE_SYSTEM_ID,
4232: MSI.ITEM_CATALOG_GROUP_ID,
4233: NULL
4234: FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
4235: WHERE MSII.SET_PROCESS_ID = p_batch_id
4236: AND MSII.PROCESS_FLAG = 1
4237: AND MSII.STYLE_ITEM_FLAG = 'N'
4238: AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
4268: FOR i IN c_intf_records(l_source_system_id) LOOP
4269: IF i.STYLE_ITEM_FLAG = 'Y' THEN
4270: 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);
4271: -- creating org assignment for all existing SKUs
4272: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
4273: (
4274: SET_PROCESS_ID,
4275: PROCESS_FLAG,
4276: ORGANIZATION_ID,
4302: AND MSI.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
4303: AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
4304: AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
4305: AND NOT EXISTS (SELECT 1
4306: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4307: WHERE MSII.SET_PROCESS_ID = p_batch_id
4308: AND MSII.PROCESS_FLAG = 1
4309: AND MSII.ORGANIZATION_ID = i.ORGANIZATION_ID
4310: AND (MSII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID OR MSII.ITEM_NUMBER = MSI.CONCATENATED_SEGMENTS)
4317: Debug_Conc_Log('Default_Org_Assignments: Done rows processed='||SQL%ROWCOUNT);
4318:
4319: -- creating org assignment for all SKUs to be created in MSII
4320: 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);
4321: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
4322: (
4323: SET_PROCESS_ID,
4324: PROCESS_FLAG,
4325: ORGANIZATION_ID,
4345: 'CREATE',
4346: MSII.ITEM_CATALOG_GROUP_ID,
4347: MSII.ITEM_CATALOG_GROUP_NAME,
4348: -99
4349: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
4350: WHERE MSII.SET_PROCESS_ID = p_batch_id
4351: AND MSII.PROCESS_FLAG = 1
4352: AND MSII.STYLE_ITEM_FLAG = 'N'
4353: AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
4354: AND MSII.ORGANIZATION_ID = MP.ORGANIZATION_ID
4355: AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
4356: AND MSII.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
4357: AND NOT EXISTS (SELECT 1
4358: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
4359: WHERE MSII2.SET_PROCESS_ID = p_batch_id
4360: AND MSII2.PROCESS_FLAG = 1
4361: AND MSII2.ORGANIZATION_ID = i.ORGANIZATION_ID
4362: AND (MSII2.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID OR MSII2.ITEM_NUMBER = MSII.ITEM_NUMBER)
4364:
4365: Debug_Conc_Log('Default_Org_Assignments: Done rows processed='||SQL%ROWCOUNT);
4366: ELSE -- IF i.STYLE_ITEM_FLAG = 'Y' THEN
4367: 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);
4368: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
4369: (
4370: SET_PROCESS_ID,
4371: PROCESS_FLAG,
4372: ORGANIZATION_ID,
4394: i.ITEM_CATALOG_GROUP_NAME,
4395: -99
4396: FROM DUAL
4397: WHERE NOT EXISTS (SELECT 1
4398: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
4399: WHERE MSII2.SET_PROCESS_ID = p_batch_id
4400: AND MSII2.PROCESS_FLAG = 1
4401: AND MSII2.ORGANIZATION_ID = i.ORGANIZATION_ID
4402: AND MSII2.INVENTORY_ITEM_ID = i.STYLE_ITEM_ID
4434: Debug_Conc_Log('Enable_Disable_SKU_Processing: Starting, p_enable_sku_processing='||p_enable_sku_processing);
4435:
4436: IF p_enable_sku_processing = 'F' THEN
4437: Debug_Conc_Log('Enable_Disable_SKU_Processing: Disabling SKUs');
4438: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
4439: SET PROCESS_FLAG = 10
4440: WHERE SET_PROCESS_ID = p_batch_id
4441: AND PROCESS_FLAG = 1
4442: AND STYLE_ITEM_FLAG = 'N';
4445: IF SQL%ROWCOUNT > 0 THEN
4446: EGO_WF_WRAPPER_PVT.Set_Item_Bulkload_Bus_Event(p_true_false => p_enable_sku_processing);
4447: END IF;
4448:
4449: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_SYSTEM_ITEMS_INTERFACE Processed rows='||SQL%ROWCOUNT);
4450:
4451: UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
4452: SET PROCESS_FLAG = 10
4453: WHERE SET_PROCESS_ID = p_batch_id
4451: UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
4452: SET PROCESS_FLAG = 10
4453: WHERE SET_PROCESS_ID = p_batch_id
4454: AND PROCESS_FLAG = 1
4455: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
4456: WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
4457: AND msii.PROCESS_FLAG = 10
4458: AND ( (msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = miri.ITEM_NUMBER) OR
4459: (msii.SOURCE_SYSTEM_ID = miri.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = miri.SOURCE_SYSTEM_REFERENCE)
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
4480: WHERE msii.SET_PROCESS_ID = mici.SET_PROCESS_ID
4481: AND msii.PROCESS_FLAG = 10
4482: AND ( (msii.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = mici.ITEM_NUMBER) OR
4483: (msii.SOURCE_SYSTEM_ID = mici.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = mici.SOURCE_SYSTEM_REFERENCE)
4502: UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
4503: SET PROCESS_STATUS = 1.5
4504: WHERE DATA_SET_ID = p_batch_id
4505: AND PROCESS_STATUS = 1
4506: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
4507: WHERE msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
4508: AND msii.PROCESS_FLAG = 10
4509: AND ( (msii.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eiuai.ITEM_NUMBER) OR
4510: (msii.SOURCE_SYSTEM_ID = eiuai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiuai.SOURCE_SYSTEM_REFERENCE)
4526: UPDATE EGO_AML_INTF eai
4527: SET PROCESS_FLAG = 10
4528: WHERE DATA_SET_ID = p_batch_id
4529: AND PROCESS_FLAG = 1
4530: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
4531: WHERE msii.SET_PROCESS_ID = eai.DATA_SET_ID
4532: AND msii.PROCESS_FLAG = 10
4533: AND ( (msii.INVENTORY_ITEM_ID = eai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eai.ITEM_NUMBER) OR
4534: (msii.SOURCE_SYSTEM_ID = eai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eai.SOURCE_SYSTEM_REFERENCE)
4550: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
4551: SET PROCESS_FLAG = 10
4552: WHERE BATCH_ID = p_batch_id
4553: AND PROCESS_FLAG = 1
4554: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
4555: WHERE msii.SET_PROCESS_ID = eiai.BATCH_ID
4556: AND msii.PROCESS_FLAG = 10
4557: AND ( (msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eiai.ITEM_NUMBER) OR
4558: (msii.SOURCE_SYSTEM_ID = eiai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiai.SOURCE_SYSTEM_REFERENCE)
4570: UPDATE EGO_ITEM_PEOPLE_INTF eipi
4571: SET PROCESS_STATUS = 10
4572: WHERE DATA_SET_ID = p_batch_id
4573: AND PROCESS_STATUS = 1
4574: AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
4575: WHERE msii.SET_PROCESS_ID = eipi.DATA_SET_ID
4576: AND msii.PROCESS_FLAG = 10
4577: AND ( (msii.INVENTORY_ITEM_ID = eipi.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eipi.ITEM_NUMBER) OR
4578: (msii.SOURCE_SYSTEM_ID = eipi.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eipi.SOURCE_SYSTEM_REFERENCE)
4596:
4597: --Maybe set to T if batch contains ONLY Styles, to prevent raising again in such cases
4598: EGO_WF_WRAPPER_PVT.Set_Item_Bulkload_Bus_Event(p_true_false => 'F');
4599:
4600: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
4601: SET PROCESS_FLAG = 1
4602: WHERE SET_PROCESS_ID = p_batch_id
4603: AND PROCESS_FLAG = 10
4604: AND STYLE_ITEM_FLAG = 'N';
4606: --R12C Business Events to be fired only ONCE although Batch Import is called twice for Styles and then SKUs
4607: IF SQL%ROWCOUNT > 0 THEN
4608: EGO_WF_WRAPPER_PVT.Set_Item_Bulkload_Bus_Event(p_true_false => p_enable_sku_processing);
4609: END IF;
4610: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_SYSTEM_ITEMS_INTERFACE Processed rows='||SQL%ROWCOUNT);
4611:
4612: IF SQL%ROWCOUNT > 0 THEN
4613: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
4614: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
4609: END IF;
4610: Debug_Conc_Log('Enable_Disable_SKU_Processing: MTL_SYSTEM_ITEMS_INTERFACE Processed rows='||SQL%ROWCOUNT);
4611:
4612: IF SQL%ROWCOUNT > 0 THEN
4613: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
4614: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
4615: FROM MTL_SYSTEM_ITEMS_KFV MSIK
4616: WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
4617: AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
4622: AND STYLE_ITEM_FLAG = 'N'
4623: AND PROCESS_FLAG = 1;
4624:
4625: Debug_Conc_Log('Enable_Disable_SKU_Processing - Done Resolving Style Item Id cnt- '||SQL%ROWCOUNT );
4626: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
4627: SET COPY_ITEM_ID = STYLE_ITEM_ID,
4628: COPY_ORGANIZATION_ID = ORGANIZATION_ID,
4629: TEMPLATE_ID = NULL,
4630: TEMPLATE_NAME = NULL
4795: grants.GRANTEE_TYPE,
4796: grants.GRANTEE_KEY,
4797: grants.END_DATE
4798: FROM
4799: MTL_SYSTEM_ITEMS_INTERFACE msii,
4800: FND_GRANTS grants,
4801: FND_MENUS menus
4802: WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
4803: AND msii.PROCESS_FLAG = 7
5007: AND mirb.ORGANIZATION_ID = msii.ORGANIZATION_ID
5008: AND mirb.EFFECTIVITY_DATE <= SYSDATE
5009: ) AS DEST_REVISION_ID
5010: FROM
5011: MTL_SYSTEM_ITEMS_INTERFACE msii,
5012: MTL_PARAMETERS mp
5013: WHERE msii.SET_PROCESS_ID = p_batch_id
5014: AND msii.PROCESS_FLAG = 7
5015: AND msii.TRANSACTION_TYPE = 'CREATE'
5131: l_err_txt VARCHAR2(4000);
5132: l_ret_code VARCHAR2(100);
5133: BEGIN
5134: -- nulling out all the segments
5135: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5136: SET ITEM_NUMBER = p_item_number,
5137: SEGMENT1 = NULL,
5138: SEGMENT2 = NULL,
5139: SEGMENT3 = NULL,
5291: FROM mtl_item_templates
5292: WHERE template_name = cp_template_name;
5293:
5294: CURSOR c_batch_exists IS
5295: SELECT 1 FROM mtl_system_items_interface
5296: WHERE set_process_id = p_batch_id
5297: AND process_flag = 1;
5298:
5299: l_template_id NUMBER;
5427: AND application_id = 431);
5428:
5429: CURSOR c_item_details
5430: IS
5431: SELECT style_item_flag,mtl_system_items_interface_s.NEXTVAL,
5432: item_catalog_group_id
5433: FROM mtl_system_items_interface
5434: WHERE item_number = p_item_number
5435: AND organization_id = p_organization_id
5429: CURSOR c_item_details
5430: IS
5431: SELECT style_item_flag,mtl_system_items_interface_s.NEXTVAL,
5432: item_catalog_group_id
5433: FROM mtl_system_items_interface
5434: WHERE item_number = p_item_number
5435: AND organization_id = p_organization_id
5436: AND set_process_id = p_batch_id
5437: AND process_flag = 1;
5555: )
5556: VALUES
5557: (
5558: 2
5559: ,MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
5560: ,p_batch_id
5561: ,'SYNC'
5562: ,p_item_number
5563: ,p_organization_id
5599: x_rev_err_table OUT NOCOPY EGO_VARCHAR_TBL_TYPE,
5600: x_uda_err_table OUT NOCOPY EGO_VARCHAR_TBL_TYPE)
5601: IS
5602: TYPE transaction_table_type IS TABLE OF
5603: mtl_system_items_interface.transaction_type%TYPE
5604: INDEX BY BINARY_INTEGER;
5605:
5606: transaction_table transaction_table_type;
5607: l_item_err_table EGO_VARCHAR_TBL_TYPE;
5612: BEGIN
5613: SELECT error_message BULK COLLECT INTO l_item_err_table
5614: FROM mtl_interface_errors
5615: WHERE transaction_id IN ( SELECT transaction_id
5616: FROM mtl_system_items_interface
5617: WHERE set_process_id = p_batch_id
5618: AND process_flag = 3);
5619: EXCEPTION
5620: WHEN no_data_found THEN
5705: RETURN;
5706: END;
5707:
5708: Debug_Conc_Log('Validate_Timestamp_In_Batch: l_item_master_dl_id='||l_item_master_dl_id);
5709: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
5710: SET PROCESS_FLAG = 14
5711: WHERE SET_PROCESS_ID = p_batch_id
5712: AND PROCESS_FLAG = 4
5713: AND BUNDLE_ID = (SELECT MAX(BUNDLE_ID) KEEP (DENSE_RANK FIRST ORDER BY MESSAGE_TIMESTAMP DESC )
5710: SET PROCESS_FLAG = 14
5711: WHERE SET_PROCESS_ID = p_batch_id
5712: AND PROCESS_FLAG = 4
5713: AND BUNDLE_ID = (SELECT MAX(BUNDLE_ID) KEEP (DENSE_RANK FIRST ORDER BY MESSAGE_TIMESTAMP DESC )
5714: FROM MTL_SYSTEM_ITEMS_INTERFACE msii2
5715: WHERE msii2.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
5716: AND msii2.ORGANIZATION_ID = msii.ORGANIZATION_ID
5717: AND msii2.SET_PROCESS_ID = msii.SET_PROCESS_ID
5718: AND msii2.PROCESS_FLAG = msii.PROCESS_FLAG
5718: AND msii2.PROCESS_FLAG = msii.PROCESS_FLAG
5719: );
5720:
5721: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 14, rowcount='||SQL%ROWCOUNT);
5722: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
5723: SET PROCESS_FLAG = DECODE(PROCESS_FLAG, 4, 16, 4)
5724: WHERE SET_PROCESS_ID = p_batch_id
5725: AND PROCESS_FLAG IN ( 4, 14 );
5726:
5733: LAST_UPDATED_BY = l_user_id
5734: WHERE SET_PROCESS_ID = p_batch_id
5735: AND PROCESS_FLAG = 1
5736: AND ROWID <> (SELECT MIN(miri2.ROWID)
5737: FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_ITEM_REVISIONS_INTERFACE miri2
5738: WHERE msii.INVENTORY_ITEM_ID = miri2.INVENTORY_ITEM_ID
5739: AND msii.ORGANIZATION_ID = miri2.ORGANIZATION_ID
5740: AND msii.SET_PROCESS_ID = miri2.SET_PROCESS_ID
5741: AND msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
5760: LAST_UPDATED_BY = l_user_id
5761: WHERE DATA_SET_ID = p_batch_id
5762: AND PROCESS_STATUS = 2
5763: AND EXISTS (SELECT NULL
5764: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5765: WHERE msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
5766: AND msii.PROCESS_FLAG = 16
5767: AND eiuai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
5768: AND eiuai.ORGANIZATION_ID = msii.ORGANIZATION_ID
5809: LAST_UPDATED_BY = l_user_id
5810: WHERE BATCH_ID = p_batch_id
5811: AND PROCESS_FLAG = 1
5812: AND EXISTS (SELECT NULL
5813: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5814: WHERE msii.SET_PROCESS_ID = eiai.BATCH_ID
5815: AND msii.PROCESS_FLAG = 16
5816: AND eiai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
5817: AND eiai.ORGANIZATION_ID = msii.ORGANIZATION_ID
5858: LAST_UPDATED_BY = l_user_id
5859: WHERE SET_PROCESS_ID = p_batch_id
5860: AND PROCESS_FLAG = 1
5861: AND EXISTS (SELECT NULL
5862: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5863: WHERE msii.SET_PROCESS_ID = mici.SET_PROCESS_ID
5864: AND msii.PROCESS_FLAG = 16
5865: AND mici.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
5866: AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
5877: LAST_UPDATED_BY = l_user_id
5878: WHERE BATCH_ID = p_batch_id
5879: AND PROCESS_FLAG = 1
5880: AND EXISTS (SELECT NULL
5881: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5882: WHERE msii.SET_PROCESS_ID = bbmi.BATCH_ID
5883: AND msii.PROCESS_FLAG = 16
5884: AND bbmi.ASSEMBLY_ITEM_ID = msii.INVENTORY_ITEM_ID
5885: AND bbmi.ORGANIZATION_ID = msii.ORGANIZATION_ID
5896: LAST_UPDATED_BY = l_user_id
5897: WHERE BATCH_ID = p_batch_id
5898: AND PROCESS_FLAG = 1
5899: AND EXISTS (SELECT NULL
5900: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
5901: WHERE msii.SET_PROCESS_ID = bici.BATCH_ID
5902: AND msii.PROCESS_FLAG = 16
5903: AND bici.COMPONENT_ITEM_ID = msii.INVENTORY_ITEM_ID
5904: AND bici.ORGANIZATION_ID = msii.ORGANIZATION_ID
5906: );
5907:
5908: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated BOM_INVENTORY_COMPS_INTERFACE rowcount='||SQL%ROWCOUNT);
5909:
5910: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
5911: SET PROCESS_FLAG = 6,
5912: REQUEST_ID = l_request_id,
5913: PROGRAM_ID = l_prog_id,
5914: PROGRAM_APPLICATION_ID = l_prog_appid,
5930: BULK COLLECT INTO l_item_detail_tbl;
5931:
5932: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 6, rowcount='||SQL%ROWCOUNT);
5933: IF l_item_detail_tbl IS NOT NULL THEN
5934: Debug_Conc_Log('Validate_Timestamp_In_Batch: Logging errors for MTL_SYSTEM_ITEMS_INTERFACE');
5935: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
5936: ERRBUF => l_err_buf,
5937: p_item_detail_tbl => l_item_detail_tbl,
5938: p_show_prod_timestemp => FALSE,
5935: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
5936: ERRBUF => l_err_buf,
5937: p_item_detail_tbl => l_item_detail_tbl,
5938: p_show_prod_timestemp => FALSE,
5939: p_intf_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE',
5940: p_user_id => l_user_id,
5941: p_login_id => l_login_id,
5942: p_prog_appid => l_prog_appid,
5943: p_prog_id => l_prog_id,
6009: ORGANIZATION_ID,
6010: NULL AS SUPPLIER_ID,
6011: NULL AS SUPPLIER_SITE_ID,
6012: MAX(MESSAGE_TIMESTAMP) AS MESSAGE_TIMESTAMP
6013: FROM MTL_SYSTEM_ITEMS_INTERFACE
6014: WHERE SET_PROCESS_ID = p_batch_id
6015: AND PROCESS_FLAG = 7
6016: AND REQUEST_ID = l_request_id
6017: GROUP BY SOURCE_SYSTEM_ID, l_item_master_dl_id, INVENTORY_ITEM_ID, ORGANIZATION_ID
6023: eiuai.ORGANIZATION_ID,
6024: eiuai.PK1_VALUE AS SUPPLIER_ID,
6025: eiuai.PK2_VALUE AS SUPPLIER_SITE_ID,
6026: MAX(msii.MESSAGE_TIMESTAMP) AS MESSAGE_TIMESTAMP
6027: FROM EGO_ITM_USR_ATTR_INTRFC eiuai, MTL_SYSTEM_ITEMS_INTERFACE msii
6028: WHERE eiuai.DATA_SET_ID = p_batch_id
6029: AND eiuai.PROCESS_STATUS = 4
6030: AND eiuai.REQUEST_ID = l_request_id
6031: AND msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
6103: SELECT msii.ORGANIZATION_ID AS DEST_ORG_ID,
6104: msii.INVENTORY_ITEM_ID AS DEST_ITEM_ID,
6105: msii.COPY_ORGANIZATION_ID AS SOURCE_ORG_ID,
6106: msii.COPY_ITEM_ID AS SOURCE_ITEM_ID
6107: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
6108: MTL_PARAMETERS mp
6109: WHERE msii.SET_PROCESS_ID = p_batch_id
6110: AND msii.PROCESS_FLAG = 7
6111: AND msii.TRANSACTION_TYPE = 'CREATE'
6182: WHERE EXISTS (SELECT NULL
6183: FROM
6184: EGO_FND_DSC_FLX_CTX_EXT ag_ext,
6185: EGO_OBJ_AG_ASSOCS_B assoc,
6186: MTL_SYSTEM_ITEMS_INTERFACE msii
6187: WHERE msii.INVENTORY_ITEM_ID = esvas.SKU_ITEM_ID
6188: AND msii.ORGANIZATION_ID = esvas.ORGANIZATION_ID
6189: AND msii.SET_PROCESS_ID = p_batch_id
6190: AND msii.REQUEST_ID = l_request_id