[Home] [Help]
80: IS
81: BEGIN
82: -- Bug 13434831 : Start (Performance Change)
83: /*
84: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
85: SET uai.DATA_LEVEL_ID = (SELECT edlb.DATA_LEVEL_ID
86: FROM EGO_DATA_LEVEL_B edlb
87: WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
88: AND edlb.APPLICATION_ID = 431
92: AND uai.PROCESS_STATUS = 1
93: AND uai.DATA_LEVEL_NAME IS NOT NULL
94: AND uai.DATA_LEVEL_ID IS NULL;
95:
96: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
97: SET uai.DATA_LEVEL_ID = (SELECT edlv.DATA_LEVEL_ID
98: FROM EGO_DATA_LEVEL_VL edlv
99: WHERE edlv.USER_DATA_LEVEL_NAME = uai.USER_DATA_LEVEL_NAME
100: AND edlv.APPLICATION_ID = 431
107: AND uai.DATA_LEVEL_ID IS NULL;
108: */
109:
110: -- Consolidating above two queries into single query as below.
111: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
112: SET uai.DATA_LEVEL_ID = Nvl ((SELECT edlb.DATA_LEVEL_ID
113: FROM EGO_DATA_LEVEL_B edlb
114: WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
115: AND edlb.APPLICATION_ID = 431
131: -- If all data level columns are null, then check if the --
132: -- attribute group is associated at only one level, then --
133: -- put that data level id here. --
134: -----------------------------------------------------------
135: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
136: SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID
137: FROM EGO_ATTR_GROUP_DL eagd, EGO_FND_DSC_FLX_CTX_EXT ag_ext
138: WHERE eagd.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
139: AND ag_ext.APPLICATION_ID = 431
212: -- Bug 13434831 : Start (Performance Change)
213: /* Removing the below query as after this we are again executing the same query in EGOCIUAB.pls.
214: In between we are not depending on ATTR_GROUP_ID. */
215: /*
216: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
217: SET ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
218: FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
219: WHERE APPLICATION_ID = 431
220: AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE
225: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Attribute Group ID '||SQL%ROWCOUNT);
226: */
227: -- Bug 13434831 : End (Performance Change)
228:
229: UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
230: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
231: FROM MTL_SYSTEM_ITEMS_KFV MSIK
232: WHERE MSIK.CONCATENATED_SEGMENTS = EIUAI.ITEM_NUMBER
233: AND MSIK.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
289: AND INVENTORY_ITEM_ID IS NULL
290: AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
291: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Intersections '||SQL%ROWCOUNT);
292:
293: UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
294: SET REVISION_ID = NVL((SELECT REVISION_ID
295: FROM MTL_ITEM_REVISIONS_B MIRB
296: WHERE MIRB.INVENTORY_ITEM_ID = EIUAI.INVENTORY_ITEM_ID
297: AND MIRB.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
368: EGO_USER_ATTRS_BULK_PVT.Apply_Template_On_Intf_Table(
369: p_api_version => 1.0
370: ,p_application_id => 431
371: ,p_object_name => 'EGO_ITEM'
372: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC'
373: ,p_data_set_id => p_batch_id
374: ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP'
375: ,p_request_id => p_request_id
376: ,p_program_application_id => p_prog_appid
404: EGO_USER_ATTRS_BULK_PVT.Apply_Template_On_Intf_Table(
405: p_api_version => 1.0
406: ,p_application_id => 431
407: ,p_object_name => 'EGO_ITEM'
408: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC'
409: ,p_data_set_id => p_batch_id
410: ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
411: ,p_request_id => p_request_id
412: ,p_program_application_id => p_prog_appid
440: EGO_USER_ATTRS_BULK_PVT.Apply_Template_On_Intf_Table(
441: p_api_version => 1.0
442: ,p_application_id => 431
443: ,p_object_name => 'EGO_ITEM'
444: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC'
445: ,p_data_set_id => p_batch_id
446: ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS'
447: ,p_request_id => p_request_id
448: ,p_program_application_id => p_prog_appid
1175: p_api_version =>1.0
1176: ,p_application_id =>431
1177: ,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
1178: ,p_object_name =>'EGO_ITEM'
1179: ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
1180: ,p_data_set_id => p_batch_id
1181: ,p_target_entity_sql => l_entity_sql
1182: ,p_attr_groups_to_exclude => l_exclude_ag_sql
1183: ,p_additional_class_Code_query => 'SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER WHERE CHILD_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID '
1201: --However, when user is assigning items to child organizations, we should
1202: --not be inserting records into the UDA table for item level UDAs (as they
1203: --are master controlled). So deleting the child records for item level
1204: --UDAs here (identified by rev_id = null. For 12.0+ releases, we need to use data level ID).
1205: DELETE FROM EGO_ITM_USR_ATTR_INTRFC ATTRS
1206: -- WHERE ATTRS.DATA_SET_ID = p_data_set_id
1207: WHERE ATTRS.DATA_SET_ID = p_batch_id
1208: AND ATTRS.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
1209: -- Bug 13434831 : Start
1223: -- Bug 9959169 Start
1224: -- The above api call inserts records for mandatory UDAs with default values.
1225: -- However, when user is trying to create a style item, we should bot be inserting variant attributes as it would value set for style item.
1226: -- So deleting the variant records for style item.
1227: DELETE EGO_ITM_USR_ATTR_INTRFC EIUAI
1228: WHERE EIUAI.DATA_SET_ID = p_batch_id
1229: AND EIUAI.PROCESS_STATUS = l_g_ps_in_process
1230: AND EIUAI.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
1231: AND EIUAI.PROG_INT_CHAR1 = 'EXT_DEFAULT_VAL_ROW'
1299: p_api_version =>1.0
1300: ,p_application_id =>431
1301: ,p_attr_group_type =>'EGO_ITEM_GTIN_ATTRS'
1302: ,p_object_name =>'EGO_ITEM'
1303: ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
1304: ,p_data_set_id => p_batch_id
1305: ,p_target_entity_sql => l_entity_sql
1306: ,p_attr_groups_to_exclude => l_exclude_ag_sql
1307: ,p_additional_class_Code_query => 'SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER WHERE CHILD_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID '
1361: p_api_version =>1.0
1362: ,p_application_id =>431
1363: ,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
1364: ,p_object_name =>'EGO_ITEM'
1365: ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
1366: ,p_data_set_id => p_batch_id
1367: ,p_target_entity_sql => l_entity_sql
1368: ,p_attr_groups_to_exclude => l_exclude_ag_sql
1369: ,p_additional_class_Code_query => 'SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER WHERE CHILD_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID '
3196:
3197: -- building SQLs for Defaulting of UDAs for Org Assignments (Style + Std. Item)
3198: -- fix for bug#9660659
3199: l_src_sql := q'#
3200: SELECT /*+ leading(msii) use_nl_with_index(ext_intf, EGO_ITM_USR_ATTR_INTRFC_N2) */
3201: ext_intf.TRANSACTION_ID,
3202: msii.SET_PROCESS_ID AS DATA_SET_ID,
3203: msii.ORGANIZATION_ID,
3204: msii.ORGANIZATION_CODE,
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'#
3236: AND msii.PROCESS_FLAG = 1
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'
3326: AND eiai.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
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'
3423: AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
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'#
3523: AND eiai.TRANSACTION_TYPE = 'CREATE'
3682: so that for all the user entered records will set PROG_INT_NUM4 = 0.
3683: Also the fix done for the bug 9678667 is reverted.
3684: */
3685: -- to identify the records that are not inserted by defaulting APIs
3686: -- UPDATE /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
3687: /* EGO_ITM_USR_ATTR_INTRFC
3688: SET PROG_INT_NUM4 = 0
3689: WHERE DATA_SET_ID = p_batch_id
3690: AND PROCESS_STATUS in (1,2); -- Bug 9742469
3683: Also the fix done for the bug 9678667 is reverted.
3684: */
3685: -- to identify the records that are not inserted by defaulting APIs
3686: -- UPDATE /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
3687: /* EGO_ITM_USR_ATTR_INTRFC
3688: SET PROG_INT_NUM4 = 0
3689: WHERE DATA_SET_ID = p_batch_id
3690: AND PROCESS_STATUS in (1,2); -- Bug 9742469
3691: */
3926: END IF;
3927:
3928: l_item_detail_tbl := NULL;
3929:
3930: UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
3931: SET PROCESS_STATUS = 6,
3932: REQUEST_ID = l_request_id,
3933: PROGRAM_ID = l_prog_id,
3934: PROGRAM_APPLICATION_ID = l_prog_appid,
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
3965: PK2_VALUE,
3966: PROCESS_STATUS
3967: BULK COLLECT INTO l_item_detail_tbl;
3968:
3969: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated EGO_ITM_USR_ATTR_INTRFC rowcount='||SQL%ROWCOUNT);
3970: IF l_item_detail_tbl IS NOT NULL THEN
3971: Debug_Conc_Log('Validate_Timestamp_With_Prod: Logging errors for EGO_ITM_USR_ATTR_INTRFC');
3972: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3973: ERRBUF => l_err_buf,
3967: BULK COLLECT INTO l_item_detail_tbl;
3968:
3969: Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated EGO_ITM_USR_ATTR_INTRFC rowcount='||SQL%ROWCOUNT);
3970: IF l_item_detail_tbl IS NOT NULL THEN
3971: Debug_Conc_Log('Validate_Timestamp_With_Prod: Logging errors for EGO_ITM_USR_ATTR_INTRFC');
3972: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3973: ERRBUF => l_err_buf,
3974: p_item_detail_tbl => l_item_detail_tbl,
3975: p_show_prod_timestemp => TRUE,
3972: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
3973: ERRBUF => l_err_buf,
3974: p_item_detail_tbl => l_item_detail_tbl,
3975: p_show_prod_timestemp => TRUE,
3976: p_intf_table_name => 'EGO_ITM_USR_ATTR_INTRFC',
3977: p_user_id => l_user_id,
3978: p_login_id => l_login_id,
3979: p_prog_appid => l_prog_appid,
3980: p_prog_id => l_prog_id,
4286: ||l_d_def_value(i));
4287:
4288: SELECT Count(* )
4289: INTO l_ag_exits_count
4290: FROM ego_itm_usr_attr_intrfc
4291: WHERE data_set_id = p_batch_id
4292: AND attr_group_int_name = l_attr_group_name(i)
4293: AND process_status = 2 -- Bug 12553744
4294: AND data_level_id = 43101;
4295:
4296: Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: l_ag_exits_count :'||l_ag_exits_count); -- Bug 12553744
4297:
4298: IF (l_ag_exits_count > 0) THEN
4299: INSERT INTO ego_itm_usr_attr_intrfc
4300: (process_status,
4301: data_set_id,
4302: row_identifier,
4303: attr_group_int_name,
4358: fnd_global.user_id,
4359: SYSDATE,
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 */
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
4369: WHERE data_set_id = a.data_set_id
4370: AND b.attr_int_name = l_attr_name(i)
4371: AND b.attr_group_int_name = a.attr_group_int_name
4372: -- AND b.transaction_type = a.transaction_type -- Bug 13720439
4409: a.item_catalog_group_id,
4410: attr_group_id,
4411: attr_group_type;
4412:
4413: Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Inserted '||SQL%ROWCOUNT||' rows into ego_itm_usr_attr_intrfc ' ); -- Bug 12553744
4414: END IF; -- l_ag_exits_count
4415:
4416:
4417: SELECT Nvl(Max(row_identifier),1) -- Bug 9820607
4415:
4416:
4417: SELECT Nvl(Max(row_identifier),1) -- Bug 9820607
4418: INTO l_row_id
4419: FROM ego_itm_usr_attr_intrfc
4420: WHERE data_set_id = p_batch_id;
4421:
4422: Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: l_row_id : '||l_row_id); -- Bug 12553744
4423:
4420: WHERE data_set_id = p_batch_id;
4421:
4422: Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: l_row_id : '||l_row_id); -- Bug 12553744
4423:
4424: INSERT INTO ego_itm_usr_attr_intrfc
4425: (process_status,
4426: data_set_id,
4427: row_identifier,
4428: attr_group_int_name,
4478: AND Nvl(msii.style_item_flag,'N') = 'N'
4479: AND msii.organization_id = mp.organization_id
4480: AND mp.organization_id = mp.master_organization_id
4481: AND msii.item_catalog_group_id = j.item_catalog_group_id
4482: AND NOT EXISTS ((SELECT /*+ index(B, EGO_ITM_USR_ATTR_INTRFC_N1) */ -- Bug 9678667
4483: NULL
4484: FROM ego_itm_usr_attr_intrfc b
4485: WHERE data_set_id = p_batch_id
4486: AND b.attr_int_name = l_attr_name(i)
4480: AND mp.organization_id = mp.master_organization_id
4481: AND msii.item_catalog_group_id = j.item_catalog_group_id
4482: AND NOT EXISTS ((SELECT /*+ index(B, EGO_ITM_USR_ATTR_INTRFC_N1) */ -- Bug 9678667
4483: NULL
4484: FROM ego_itm_usr_attr_intrfc b
4485: WHERE data_set_id = p_batch_id
4486: AND b.attr_int_name = l_attr_name(i)
4487: AND b.attr_group_int_name = l_attr_group_name(i)
4488: /* Fix for bug#9660659 - Start */
4500: AND b.process_status = 2 /* Bug 10263673, changing the value 2 as by the time this code gets executed all the records will be in status 2 */
4501: AND b.data_level_id = 43101)
4502: UNION ALL
4503: (SELECT NULL
4504: FROM ego_itm_usr_attr_intrfc b
4505: WHERE data_set_id = p_batch_id
4506: AND b.attr_int_name = l_attr_name(i)
4507: AND b.attr_group_int_name = l_attr_group_name(i)
4508: AND (b.item_number = msii.item_number)
4511: AND b.process_status = 2 /* Bug 10263673, changing the value 2 as by the time this code gets executed all the records will be in status 2 */
4512: AND b.data_level_id = 43101)
4513: UNION ALL
4514: (SELECT NULL
4515: FROM ego_itm_usr_attr_intrfc b
4516: WHERE data_set_id = p_batch_id
4517: AND b.attr_int_name = l_attr_name(i)
4518: AND b.attr_group_int_name = l_attr_group_name(i)
4519: AND b.source_system_id = msii.source_system_id -- Bug 9678667
4523: AND b.process_status = 2 /* Bug 10263673, changing the value 2 as by the time this code gets executed all the records will be in status 2 */
4524: AND b.data_level_id = 43101));
4525: /* Fix for bug#9660659 - End */
4526:
4527: Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Inserted '||SQL%ROWCOUNT||' rows into ego_itm_usr_attr_intrfc ' ); -- Bug 12553744
4528: END LOOP;
4529: END IF; --(if l_attr_name.COUNT > 0)
4530: END IF;--(if any of l_num_action_id or l_desc_action_id is not null)
4531: END LOOP;
4574: Debug_Conc_Log('Preprocess_Import: l_request_id='||l_request_id);
4575:
4576: -- added for bug 12693246
4577: -- for concurrent program - Import Catalog Items - all records of the interface
4578: -- table EGO_ITM_USR_ATTR_INTRFC will be processed, when value for,
4579: -- Process Set - input parameter of concurrent request is passed as NULL
4580:
4581: IF (p_batch_id IS NULL OR p_batch_id = -999 ) THEN
4582: l_batch_id := -999;
4579: -- Process Set - input parameter of concurrent request is passed as NULL
4580:
4581: IF (p_batch_id IS NULL OR p_batch_id = -999 ) THEN
4582: l_batch_id := -999;
4583: UPDATE EGO_ITM_USR_ATTR_INTRFC
4584: SET DATA_SET_ID = -999
4585: WHERE PROCESS_STATUS = 1;
4586: END IF;
4587:
4709:
4710: /* Bug 10120039 : Start
4711: to identify the records that are not inserted by defaulting APIs i.e to identify the records entered by user.
4712: */
4713: UPDATE /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */
4714: EGO_ITM_USR_ATTR_INTRFC
4715: SET PROG_INT_NUM4 = 0
4716: WHERE DATA_SET_ID = p_batch_id
4717: AND PROCESS_STATUS = 1;
4710: /* Bug 10120039 : Start
4711: to identify the records that are not inserted by defaulting APIs i.e to identify the records entered by user.
4712: */
4713: UPDATE /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */
4714: EGO_ITM_USR_ATTR_INTRFC
4715: SET PROG_INT_NUM4 = 0
4716: WHERE DATA_SET_ID = p_batch_id
4717: AND PROCESS_STATUS = 1;
4718: -- Bug 10120039 : End
4853: END IF;
4854: END IF; -- IF l_enabled_for_data_pool = 'N' THEN
4855:
4856: /* Fix for bug#9678667 : Start */
4857: UPDATE ego_itm_usr_attr_intrfc
4858: SET PROG_INT_NUM1 = NULL
4859: ,PROG_INT_NUM2 = NULL
4860: ,PROG_INT_NUM3 = NULL
4861: ,PROG_INT_CHAR1 = 'N'
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
5201: -- so, the only option left is to use numbers with decimal < 8
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
5220: IF SQL%ROWCOUNT > 0 THEN
5221: EGO_WF_WRAPPER_PVT.Set_PostAttr_Change_Event(p_true_false => p_enable_sku_processing);
5222: END IF;
5223:
5224: Debug_Conc_Log('Enable_Disable_SKU_Processing: EGO_ITM_USR_ATTR_INTRFC Processed rows='||SQL%ROWCOUNT);
5225:
5226: UPDATE EGO_AML_INTF eai
5227: SET PROCESS_FLAG = 10
5228: WHERE DATA_SET_ID = p_batch_id
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
5388: AND PROCESS_STATUS = 1.5;
5389:
5396: --R12C Business Events to be fired only ONCE although Batch Import is called twice for Styles and then SKUs
5397: IF SQL%ROWCOUNT > 0 THEN
5398: EGO_WF_WRAPPER_PVT.Set_PostAttr_Change_Event(p_true_false => p_enable_sku_processing);
5399: END IF;
5400: Debug_Conc_Log('Enable_Disable_SKU_Processing: EGO_ITM_USR_ATTR_INTRFC Processed rows='||SQL%ROWCOUNT);
5401:
5402: UPDATE EGO_AML_INTF eai
5403: SET PROCESS_FLAG = 1
5404: WHERE DATA_SET_ID = p_batch_id
5962: AND SOURCE_SYSTEM_ID = p_ss_id
5963: AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
5964:
5965: -- Fix for bug#9660659 Start
5966: --UPDATE EGO_ITM_USR_ATTR_INTRFC
5967: --SET ITEM_NUMBER = p_item_number
5968: --WHERE PROCESS_STATUS = 2
5969: -- AND DATA_SET_ID = p_batch_id
5970: -- AND SOURCE_SYSTEM_ID = p_ss_id
5970: -- AND SOURCE_SYSTEM_ID = p_ss_id
5971: -- AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
5972:
5973: IF(p_ss_ref IS NOT null) THEN
5974: UPDATE /*+ index(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N5) */ EGO_ITM_USR_ATTR_INTRFC /* Bug 9678667 - Added the hint */
5975: SET ITEM_NUMBER = p_item_number
5976: WHERE PROCESS_STATUS = 2
5977: AND DATA_SET_ID = p_batch_id
5978: AND SOURCE_SYSTEM_ID = p_ss_id
5977: AND DATA_SET_ID = p_batch_id
5978: AND SOURCE_SYSTEM_ID = p_ss_id
5979: AND SOURCE_SYSTEM_REFERENCE = p_ss_ref;
5980: ELSE
5981: UPDATE EGO_ITM_USR_ATTR_INTRFC
5982: SET ITEM_NUMBER = p_item_number
5983: WHERE PROCESS_STATUS = 2
5984: AND DATA_SET_ID = p_batch_id
5985: AND ITEM_NUMBER = p_old_item_number;
6229:
6230: CURSOR c_group_record
6231: IS
6232: SELECT MAX(ROW_IDENTIFIER), ITEM_CATALOG_GROUP_ID
6233: FROM EGO_ITM_USR_ATTR_INTRFC
6234: WHERE ITEM_NUMBER = p_item_number
6235: AND ORGANIZATION_ID = p_organization_id
6236: AND DATA_SET_ID = p_batch_id
6237: AND PROCESS_STATUS = 2
6310: END IF;
6311: END IF;
6312:
6313: IF l_error = 0 THEN
6314: INSERT INTO EGO_ITM_USR_ATTR_INTRFC
6315: (
6316: PROCESS_STATUS
6317: ,TRANSACTION_ID
6318: ,DATA_SET_ID
6420: BEGIN
6421: SELECT error_message BULK COLLECT INTO l_uda_err_table
6422: FROM mtl_interface_errors
6423: WHERE transaction_id IN ( SELECT transaction_id
6424: FROM EGO_ITM_USR_ATTR_INTRFC
6425: WHERE data_set_id = p_batch_id
6426: AND process_status = 3);
6427: EXCEPTION
6428: WHEN no_data_found THEN
6534: l_item_detail_tbl := NULL;
6535:
6536: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 16,4, rowcount='||SQL%ROWCOUNT);
6537:
6538: UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
6539: SET PROCESS_STATUS = 6,
6540: REQUEST_ID = l_request_id,
6541: PROGRAM_ID = l_prog_id,
6542: PROGRAM_APPLICATION_ID = l_prog_appid,
6564: PK2_VALUE,
6565: PROCESS_STATUS
6566: BULK COLLECT INTO l_item_detail_tbl;
6567:
6568: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated EGO_ITM_USR_ATTR_INTRFC rowcount='||SQL%ROWCOUNT);
6569: IF l_item_detail_tbl IS NOT NULL THEN
6570: Debug_Conc_Log('Validate_Timestamp_In_Batch: Logging errors for EGO_ITM_USR_ATTR_INTRFC');
6571: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6572: ERRBUF => l_err_buf,
6566: BULK COLLECT INTO l_item_detail_tbl;
6567:
6568: Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated EGO_ITM_USR_ATTR_INTRFC rowcount='||SQL%ROWCOUNT);
6569: IF l_item_detail_tbl IS NOT NULL THEN
6570: Debug_Conc_Log('Validate_Timestamp_In_Batch: Logging errors for EGO_ITM_USR_ATTR_INTRFC');
6571: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6572: ERRBUF => l_err_buf,
6573: p_item_detail_tbl => l_item_detail_tbl,
6574: p_show_prod_timestemp => FALSE,
6571: Log_Error_For_Timestamp_Val(RETCODE => l_ret_code,
6572: ERRBUF => l_err_buf,
6573: p_item_detail_tbl => l_item_detail_tbl,
6574: p_show_prod_timestemp => FALSE,
6575: p_intf_table_name => 'EGO_ITM_USR_ATTR_INTRFC',
6576: p_user_id => l_user_id,
6577: p_login_id => l_login_id,
6578: p_prog_appid => l_prog_appid,
6579: p_prog_id => l_prog_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
7010:
7011: CURSOR Check_Duplicates_in_batch(p_batch_id NUMBER,source_system_ref_check varchar2) is
7012: select RANK() OVER ( ORDER BY ATTR_VALUE_DATE desc
7013: )rnk, transaction_id,bundle_id
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
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:
7086: else
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:
7096: