ItemLocMod Messages Fail in MPS STAGED MESSAGE With 'ORA-00001: unique constraint' Error (Doc ID 2809474.1)

Last updated on JANUARY 31, 2023

Applies to:

Symptoms

When attempting to process ItemLocMod messages, the following error occurs.

ERROR
---------

ERROR-182538 Time: 1/16/21 5:56 AM Type: SimServerException Message: Error processing message! [Inbound: true, MessageType: ItemLocMod, BusinessId: 00001903414] Root Cause: Error executing statement.
oracle.retail.sim.common.core.SimServerException: Error executing statement.
:

:
2021-01-16 05:57:18,133 ERROR [[ACTIVE] ExecuteThread: '100' for queue: 'weblogic.kernel.Default (self-tuning)'][o.r.s.s.d.d.StoreItemConsumerOracleDao] Error executing statement [0]: insert into STORE_ITEM (ITEM_ID, STORE_ID, ITEM_TYPE, SHORT_DESCRIPTION, LONG_DESCRIPTION, STATUS, STATUS_DATE, DEFAULT_CURRENCY, PRIMARY_SUPPLIER_ID, NEXT_DELIVERY_DATE, UIN_REQUIRED, REPLENISHMENT_TYPE, STORE_ORDER_ALLOWED, STORE_CONTROL_PRICING, MULTIPLE_DELIVERY_PER_DAY, RFID, CONSIGNMENT_TYPE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SIM01.STORE_ITEM_PK) violated.

Steps to Reproduce:

For Item 21500, data exists in all the tables below.

select * from ITEM WHERE ITEM_ID = '21500';  TRANS LEVEL = 2 and ITEM LEVEL = 1
select * from STORE_ITEM WHERE ITEM_ID = '21500';  Record exists for all stores
select * from SUPPLIER_ITEM WHERE ITEM_ID = '21500';  Record exists
select * from STORE_ITEM_STOCK WHERE ITEM_ID = '21500';  Record exists for all stores

Process the following ItemLocMod message:

Changes

Cause

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