[Home] [Help]
268: AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
269:
270: Debug_Conc_Log('Resolve_PKs_For_Child: Updated User Attrs '||SQL%ROWCOUNT);
271:
272: UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
273: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
274: FROM MTL_SYSTEM_ITEMS_KFV MSIK
275: WHERE MSIK.CONCATENATED_SEGMENTS = EIAI.ITEM_NUMBER
276: AND MSIK.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
1349: NULL AS PK4_VALUE,
1350: NULL AS PK5_VALUE,
1351: eiai.DATA_LEVEL_ID
1352: FROM
1353: EGO_ITEM_ASSOCIATIONS_INTF eiai
1354: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
1355: AND NVL(eiai.STYLE_ITEM_FLAG, 'Y') = 'Y'
1356: AND eiai.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'# #';
1357:
2732: END IF;
2733:
2734: -- For Defaulting of UDAs for Item Supplier Intersections (SKU + Style + Std. Item), updating
2735: -- the Style_item_flag and Style_item_id in the associations interface table.
2736: Debug_Conc_Log('Default_User_Attrs_From_Prod: Updating Style_item_id, Style_item_flag in EGO_ITEM_ASSOCIATIONS_INTF');
2737: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
2738: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT
2739: msi.STYLE_ITEM_FLAG,
2740: msi.STYLE_ITEM_ID
2733:
2734: -- For Defaulting of UDAs for Item Supplier Intersections (SKU + Style + Std. Item), updating
2735: -- the Style_item_flag and Style_item_id in the associations interface table.
2736: Debug_Conc_Log('Default_User_Attrs_From_Prod: Updating Style_item_id, Style_item_flag in EGO_ITEM_ASSOCIATIONS_INTF');
2737: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
2738: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT
2739: msi.STYLE_ITEM_FLAG,
2740: msi.STYLE_ITEM_ID
2741: FROM MTL_SYSTEM_ITEMS_B msi, MTL_PARAMETERS mp
2752: AND eiai.ORGANIZATION_ID = mp2.ORGANIZATION_ID
2753: AND msi2.ORGANIZATION_ID = mp2.MASTER_ORGANIZATION_ID
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
2772: AND eiai.ORGANIZATION_ID = mp2.ORGANIZATION_ID
2773: AND MSII2.SET_PROCESS_ID = eiai.BATCH_ID
2774: AND MSII2.PROCESS_FLAG = p_msii_miri_process_flag );
2775:
2776: Debug_Conc_Log('Default_User_Attrs_From_Prod: Updated Style_item_id, Style_item_flag in EGO_ITEM_ASSOCIATIONS_INTF');
2777: -- building SQLs for Defaulting of UDAs for Item Supplier Intersections (SKU + Style + Std. Item)
2778: l_src_sql := q'#
2779: SELECT
2780: ROWNUM AS ROW_IDENTIFIER,
2800: eiai.BUNDLE_ID,
2801: eiai.TRANSACTION_ID,
2802: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2803: FROM
2804: EGO_ITEM_ASSOCIATIONS_INTF eiai,
2805: EGO_MTL_SY_ITEMS_EXT_VL ext_prod
2806: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
2807: AND eiai.TRANSACTION_TYPE = 'CREATE'
2808: AND eiai.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
2829: l_ag_sql := q'#
2830: SELECT
2831: ext_prod.ATTR_GROUP_ID
2832: FROM
2833: EGO_ITEM_ASSOCIATIONS_INTF eiai,
2834: EGO_MTL_SY_ITEMS_EXT_B ext_prod
2835: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
2836: AND eiai.TRANSACTION_TYPE = 'CREATE'
2837: AND eiai.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
2913: eiai.BUNDLE_ID,
2914: eiai.TRANSACTION_ID,
2915: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
2916: FROM
2917: EGO_ITEM_ASSOCIATIONS_INTF eiai,
2918: EGO_MTL_SY_ITEMS_EXT_VL ext_prod
2919: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
2920: AND eiai.TRANSACTION_TYPE = 'CREATE'
2921: AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
2947: l_ag_sql := q'#
2948: SELECT
2949: ext_prod.ATTR_GROUP_ID
2950: FROM
2951: EGO_ITEM_ASSOCIATIONS_INTF eiai,
2952: EGO_MTL_SY_ITEMS_EXT_B ext_prod
2953: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
2954: AND eiai.TRANSACTION_TYPE = 'CREATE'
2955: AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
3036: eiai.BUNDLE_ID,
3037: eiai.TRANSACTION_ID,
3038: ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
3039: FROM
3040: EGO_ITEM_ASSOCIATIONS_INTF eiai,
3041: EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
3042: MTL_PARAMETERS mp
3043: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
3044: AND eiai.TRANSACTION_TYPE = 'CREATE'
3074: l_ag_sql := q'#
3075: SELECT
3076: ext_prod.ATTR_GROUP_ID
3077: FROM
3078: EGO_ITEM_ASSOCIATIONS_INTF eiai,
3079: EGO_MTL_SY_ITEMS_EXT_B ext_prod,
3080: MTL_PARAMETERS mp
3081: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
3082: AND eiai.TRANSACTION_TYPE = 'CREATE'
3317: ext_intf.SOURCE_SYSTEM_REFERENCE,
3318: ext_intf.BUNDLE_ID,
3319: eiai.DATA_LEVEL_ID
3320: FROM
3321: EGO_ITEM_ASSOCIATIONS_INTF eiai,
3322: EGO_ITM_USR_ATTR_INTRFC ext_intf,
3323: EGO_FND_DSC_FLX_CTX_EXT ag_ext
3324: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
3325: AND eiai.TRANSACTION_TYPE = 'CREATE'
3414: ext_intf.SOURCE_SYSTEM_REFERENCE,
3415: ext_intf.BUNDLE_ID,
3416: eiai.DATA_LEVEL_ID
3417: FROM
3418: EGO_ITEM_ASSOCIATIONS_INTF eiai,
3419: EGO_ITM_USR_ATTR_INTRFC ext_intf,
3420: EGO_FND_DSC_FLX_CTX_EXT ag_ext
3421: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
3422: AND eiai.TRANSACTION_TYPE = 'CREATE'
3514: ext_intf.SOURCE_SYSTEM_REFERENCE,
3515: ext_intf.BUNDLE_ID,
3516: eiai.DATA_LEVEL_ID
3517: FROM
3518: EGO_ITEM_ASSOCIATIONS_INTF eiai,
3519: EGO_ITM_USR_ATTR_INTRFC ext_intf,
3520: MTL_PARAMETERS mp,
3521: EGO_FND_DSC_FLX_CTX_EXT ag_ext
3522: WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
3984: END IF;
3985:
3986: l_item_detail_tbl := NULL;
3987:
3988: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
3989: SET PROCESS_FLAG = 6,
3990: REQUEST_ID = l_request_id,
3991: PROGRAM_ID = l_prog_id,
3992: PROGRAM_APPLICATION_ID = l_prog_appid,
4014: PK2_VALUE,
4015: PROCESS_FLAG
4016: BULK COLLECT INTO l_item_detail_tbl;
4017:
4018: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated EGO_ITEM_ASSOCIATIONS_INTF rowcount='||SQL%ROWCOUNT);
4019: IF l_item_detail_tbl IS NOT NULL THEN
4020: Debug_Conc_Log('Validate_Timestamp_With_Prod: Logging errors for EGO_ITEM_ASSOCIATIONS_INTF');
4021: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
4022: ERRBUF => l_err_buf,
4016: BULK COLLECT INTO l_item_detail_tbl;
4017:
4018: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated EGO_ITEM_ASSOCIATIONS_INTF rowcount='||SQL%ROWCOUNT);
4019: IF l_item_detail_tbl IS NOT NULL THEN
4020: Debug_Conc_Log('Validate_Timestamp_With_Prod: Logging errors for EGO_ITEM_ASSOCIATIONS_INTF');
4021: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
4022: ERRBUF => l_err_buf,
4023: p_item_detail_tbl => l_item_detail_tbl,
4024: p_show_prod_timestemp => TRUE,
4021: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
4022: ERRBUF => l_err_buf,
4023: p_item_detail_tbl => l_item_detail_tbl,
4024: p_show_prod_timestemp => TRUE,
4025: p_intf_table_name => 'EGO_ITEM_ASSOCIATIONS_INTF',
4026: p_user_id => l_user_id,
4027: p_login_id => l_login_id,
4028: p_prog_appid => l_prog_appid,
4029: p_prog_id => l_prog_id,
5246: END IF;
5247:
5248: Debug_Conc_Log('Enable_Disable_SKU_Processing: EGO_AML_INTF Processed rows='||SQL%ROWCOUNT);
5249:
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
5264: AND msik.ORGANIZATION_ID = eiai.ORGANIZATION_ID
5265: AND msik.STYLE_ITEM_FLAG = 'N'
5266: );
5267:
5268: Debug_Conc_Log('Enable_Disable_SKU_Processing: EGO_ITEM_ASSOCIATIONS_INTF Processed rows='||SQL%ROWCOUNT);
5269:
5270: UPDATE EGO_ITEM_PEOPLE_INTF eipi
5271: SET PROCESS_STATUS = 10
5272: WHERE DATA_SET_ID = p_batch_id
5415: EGO_WF_WRAPPER_PVT.Set_PostAml_Change_Event(p_true_false => p_enable_sku_processing);
5416: END IF;
5417: Debug_Conc_Log('Enable_Disable_SKU_Processing: EGO_AML_INTF Processed rows='||SQL%ROWCOUNT);
5418:
5419: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
5420: SET PROCESS_FLAG = 1
5421: WHERE BATCH_ID = p_batch_id
5422: AND PROCESS_FLAG = 10;
5423:
5426: x_skus_to_process := 'T';
5427: END IF;
5428: -- Bug 9678667 : change end
5429:
5430: Debug_Conc_Log('Enable_Disable_SKU_Processing: EGO_ITEM_ASSOCIATIONS_INTF Processed rows='||SQL%ROWCOUNT);
5431:
5432: UPDATE EGO_ITEM_PEOPLE_INTF eipi
5433: SET PROCESS_STATUS = 1
5434: WHERE DATA_SET_ID = p_batch_id
6583: END IF;
6584:
6585: l_item_detail_tbl := NULL;
6586:
6587: UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
6588: SET PROCESS_FLAG = 6,
6589: REQUEST_ID = l_request_id,
6590: PROGRAM_ID = l_prog_id,
6591: PROGRAM_APPLICATION_ID = l_prog_appid,
6613: PK2_VALUE,
6614: PROCESS_FLAG
6615: BULK COLLECT INTO l_item_detail_tbl;
6616:
6617: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated EGO_ITEM_ASSOCIATIONS_INTF rowcount='||SQL%ROWCOUNT);
6618: IF l_item_detail_tbl IS NOT NULL THEN
6619: Debug_Conc_Log('Validate_Timestamp_In_Batch: Logging errors for EGO_ITEM_ASSOCIATIONS_INTF');
6620: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6621: ERRBUF => l_err_buf,
6615: BULK COLLECT INTO l_item_detail_tbl;
6616:
6617: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated EGO_ITEM_ASSOCIATIONS_INTF rowcount='||SQL%ROWCOUNT);
6618: IF l_item_detail_tbl IS NOT NULL THEN
6619: Debug_Conc_Log('Validate_Timestamp_In_Batch: Logging errors for EGO_ITEM_ASSOCIATIONS_INTF');
6620: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6621: ERRBUF => l_err_buf,
6622: p_item_detail_tbl => l_item_detail_tbl,
6623: p_show_prod_timestemp => FALSE,
6620: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6621: ERRBUF => l_err_buf,
6622: p_item_detail_tbl => l_item_detail_tbl,
6623: p_show_prod_timestemp => FALSE,
6624: p_intf_table_name => 'EGO_ITEM_ASSOCIATIONS_INTF',
6625: p_user_id => l_user_id,
6626: p_login_id => l_login_id,
6627: p_prog_appid => l_prog_appid,
6628: p_prog_id => l_prog_id,
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:
7086: else
7087:
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:
7096:
7097: end if;