Cannot Create/Assign Item in Master Items Error

This document is to help those with the following errors when adding an item / assigning an existing item to a new organization via the Master Items form INVIDITM.fmb or the Item Open Interface INCOIN.

ERROR received in the application (Master Items Form):
APP-INV-05479: Please commit or clear your changes first.

Do you want to save the changes that you have made? (but won't allow save)

ERROR received in Import Items log file (with debug on):
INCOIN fails (ERROR) on CREATE with process_flag = 4 and only error message in log file is:  INVPOPIF.inopinp_OI_process_create: done INVPPROC.inproit_process_item: ret_code=1

The trace file shows that we fail to insert a row into mtl_system_items_b
There are NO errors in the tracefile: No ORA- , APP-, FND- or ROLLBACK.

ERROR received in trace file shows ORA-0001 on the following statement:

INSERT INTO MTL_SYSTEM_ITEMS_TL ( INVENTORY_ITEM_ID, ORGANIZATION_ID, LANGUAGE, SOURCE_LANG,
DESCRIPTION, LONG_DESCRIPTION, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN) SELECT :B2 , :B1 , MSI.LANGUAGE, MSI.SOURCE_LANG,
LTRIM(RTRIM(MSI.DESCRIPTION)), LTRIM(RTRIM(MSI.LONG_DESCRIPTION)), :B7 , :B6 , :B5 , :B4 , :B3
FROM MTL_SYSTEM_ITEMS_TL MSI, MTL_PARAMETERS MP WHERE MSI.INVENTORY_ITEM_ID = :B2 AND
MSI.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID AND MP.ORGANIZATION_ID = :B1

Cause

There are a variety of conditions that can cause this issue.

CONDITIONS SPECIFIC TO IMPORT ITEMS (INCOIN):

1) Space issue on the Item Open Interface tables.

2) Required attribute missing in the MTL_SYSTEM_ITEMS_INTERFACE on CREATE transactions.

CONITIONS COMMON TO THE IMPORT ITEMS process and MASTER ITEMS form:

3) Space issue on the receiving data tables.

4) Existing orphaned rows for deleted items that are being re-added. The root cause for this issue is not known at this time.

5) Unique Custom Indexes with a unique constraint violation.

6) Custom Triggers causing insert into MTL_SYSTEM_ITEMS_B to fail.

Steps to resolve each numbered condition above.

CONDITIONS SPECIFIC TO IMPORT ITEMS (INCOIN):

1) To resolve space issues on the Item Open Interfaces tables, truncate the Interface tables (This should be done every 100k rows processed via Import Items):

TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;
TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;
TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;
TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;

Note: If there is data that needs to retained in the interface tables, back_up the required data, truncate the tables, and reload the data. It is recommended that rows with process_flag = 7 (processed rows) should always be purged.

2) If CREATE transactions are being loaded into the MTL_SYSTEM_ITEMS_INTERFACE, make sure that the 6 required attributes are populated:

PROCESS_FLAG, SET_PROCESS_ID, TRANSACTION_TYPE, ORGANIZATION_ID (or CODE), SEGMENT1 (or INVENTORY_ITEM_ID) and DESCRIPTION.

CONITIONS COMMON TO IMPORT ITEMS process and MASTER ITEMS form:

3) Check and resolve space issues on the following tables (use following script and confir with DBA on results):

select owner,segment_name,segment_type,tablespace_name,blocks,extents
from dba_segments
where segment_name IN ('MTL_SYSTEM_ITEMS_B','MTL_SYSTEM_ITEMS_TL',
'MTL_ITEM_REVISIONS_B','MTL_ITEM_CATEGORIES_B',
'MTL_ITEM_REVISIONS_TL','MTL_ITEM_CATEGORIES','MTL_SYSTEM_ITEMS_INTERFACE', 'MTL_ITEM_REVISIONS_INTERFACE','MTL_ITEM_CATEGORIES_INTERFACE')
order by segment_name;

4) Execute the following scripts to detect orphaned rows for which there is no corresponding item/organizaiton entry in the MTL_SYSTEM_ITEMS_B table.

NOTE: Request a data fix if any of these scripts return a count greater than zero. Supply results of the scripts when creating the service request and mention this note.

SQL-1:

SELECT COUNT(*)
FROM mtl_system_items_tl msitl
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = msitl.inventory_item_id
AND item.organization_id = msitl.organization_id );

SQL-2:

SELECT COUNT(*)
FROM mtl_item_revisions_b mirb
WHERE NOT EXISTS ( SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mirb.inventory_item_id
AND item.organization_id = mirb.organization_id);

SQL-3:

SELECT COUNT(*)
FROM mtl_item_revisions_tl mirtl
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mirtl.inventory_item_id
AND item.organization_id = mirtl.organization_id);

SQL-4:

SELECT COUNT(*)
FROM mtl_item_categories mic
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mic.inventory_item_id
AND item.organization_id = mic.organization_id);

SQL-5:

SELECT COUNT(*)
FROM mtl_pending_item_status mpis
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mpis.inventory_item_id
AND item.organization_id = mpis.organization_id);

SQL-6:

SELECT COUNT(*)
FROM cst_item_costs cic
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = cic.inventory_item_id
AND item.organization_id = cic.organization_id);

SQL-7:

SELECT COUNT(*)
FROM eni_oltp_item_star eois
WHERE NOT EXISTS (SELECT NULL FROM mtl_system_items_b item
WHERE item.inventory_item_id = eois.inventory_item_id
AND item.organization_id = eois.organization_id);


5) Run the following script to look for custom indexes.

NOTE: Any unique index could cause this issue.  Drop custom indexes and try to recreate the issue.

SELECT SUBSTR(a.index_name, 1, 30) indname,
SUBSTR(a.column_name, 1, 20) colname,
SUBSTR(a.TABLE_NAME, 1, 20) tblname,
SUBSTR(to_char(a.column_position), 1, 2) colpos,
SUBSTR(a.index_owner, 1, 8) indown,
SUBSTR(b.tablespace_name, 1, 10) tbspace,
b.distinct_keys distkeys,
b.uniqueness uniq
FROM all_ind_columns a,
all_indexes b
WHERE a.TABLE_NAME LIKE UPPER('mtl_%item%')
and a.index_name NOT LIKE 'MTL_%'
AND a.index_name = b.index_name
AND a.TABLE_NAME = b.TABLE_NAME
ORDER BY a.table_name, a.index_name, colpos;

6) Run the following script to look for custom triggers on MTL tables with item information.

NOTE:  Disable any custom triggers and try to recreate the issue.

select TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,STATUS
from all_triggers where table_name like '%MTL_%ITEM%'
AND SUBSTR(TRIGGER_NAME,1,3) != 'MTL'
AND SUBSTR(TRIGGER_NAME,1,3) != 'EGO'
AND SUBSTR(TRIGGER_NAME,1,3) != 'AX_'
AND SUBSTR(TRIGGER_NAME,1,3) != 'GMF'
AND SUBSTR(TRIGGER_NAME,1,3) != 'JA_'
AND SUBSTR(TRIGGER_NAME,1,3) != 'MRP';