My Oracle Support Banner

Orphan Records Left in MTL_SYSTEM_ITEMS_INTERFACE When Creating a Item Using EGO_ITEM_PUB.PROCESS_ITEMS API in R12.2.5 (Doc ID 2220755.1)

Last updated on JUNE 06, 2018

Applies to:

Oracle Product Hub - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

Actual Behavior
Orphan records left in MTL_SYSTEM_ITEMS_INTERFACE when creating a item using EGO_ITEM_PUB.PROCESS_ITEMS in R12.2.5 instance.

When calling API(EGO_ITEM_PUB.Process_Items) to create/update item attributes then after successfully to update item attribute, we know the interface will keep the failure data on the interface (process_flag=3).
But why MTL_SYSTEM_ITEMS_INTERFACE still keep process_flag=7(successful) on the interface ?

Also orphan records left in MTL_SYSTEM_ITEMS_INTERFACE when creating a item from front end(OA Framework) instead of API.

Expected Behavior
Only keep the failure data , then successful data won't keep on the interface.

Steps to Reproduce

  1. Check interface record
    SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE;
    ==>No rows selected
  2. Create a item using API
    l_item_tbl(1).Transaction_Type := 'CREATE';
    l_item_tbl(1).Segment1 := 'XXXX_EGOITEM_011'; -- dummy
    l_item_tbl(1).Description := '20161111 from API(EGO_ITEM_PUB.PROCESS_ITEMS)';
    l_item_tbl(1).Organization_Code := 'V1';
    l_item_tbl(1).Template_Name := 'Finished Good';
    l_item_tbl(1).Item_Catalog_Group_Id := 25041;

    EGO_ITEM_PUB.PROCESS_ITEMS(
    p_api_version => 1
    ,p_init_msg_list => FND_API.G_TRUE
    ,p_commit => FND_API.G_FALSE
    ,p_item_tbl => l_item_tbl
    ,p_role_grant_tbl => l_role_grant_tbl
    ,x_item_tbl => x_item_tbl
    ,x_return_status => x_return_status
    ,x_msg_count => x_msg_count);

  3. Check MTL_SYSTEM_ITEMS_B  

    SELECT * FROM mtl_system_items_b where segment1 = 'XXXX_EGOITEM_011';
    ==>
    INVENTORY_ITEM_ID      | 303214
    ORGANIZATION_ID        | 204
    LAST_UPDATE_DATE       | 2016/11/11
    LAST_UPDATED_BY        | 1014867
    CREATION_DATE          | 2016/11/11
    CREATED_BY             | 1014867
    SEGMENT1               | XXXX_EGOITEM_011
    ITEM_CATALOG_GROUP_ID  | 25041
    REQUEST_ID             | -1
    PROGRAM_APPLICATION_ID | -1
    PROGRAM_ID             | -1
    PROGRAM_UPDATE_DATE    | 2016/11/11

      
  4. Check interface record
    SELECT * FROM MTL_SYSTEM_ITEMS_INTERFACE;
    ==>
    INVENTORY_ITEM_ID         | 303214
    ORGANIZATION_ID           | 999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    LAST_UPDATE_DATE          | 2016/11/11
    CREATION_DATE             | 2016/11/11
    SEGMENT1                  | (null)
    ITEM_CATALOG_GROUP_ID     | (null)
    REQUEST_ID                | 7553941
    PROCESS_FLAG              | 7
    TRANSACTION_TYPE          | CREATE
    SET_PROCESS_ID            | 7553941
    INTERFACE_TABLE_UNIQUE_ID | 62604
      
    ==>Orphan records left in MTL_SYSTEM_ITEMS_INTERFACE with PROCESS_FLAG:7

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.