Warehouse to Store Transfer With More Than 2 Line Items Fails to Upload to SIM; "ORA-00001: Unique Constraint (SIMUSER.UK_RK_SHIPMENT_ITEM) Violated" Error Occurs (Doc ID 818043.1)

Last updated on AUGUST 17, 2016

Applies to:

Oracle Retail Store Inventory Management - Version 13.0 to 13.0.3 [Release 13.0]
Oracle Retail Integration Bus - Version 13.0 to 13.0.3 [Release 13.0]
Oracle Retail Merchandising System - Version 13.0 to 13.0.3 [Release 13.0]
Information in this document applies to any platform.
Checked for relevance July 2013


Symptoms

ASNOut messages that are being created in Retail Merchandising System (RMS) get transformed to ASNIn messages for Store Inventory Management (SIM) for a Warehouse to Store transfer.  These messages are not getting consumed in SIM and throw the following error:

ERROR
oracle.retail.sim.closed.common.SimServerException: Unable to execute SimStatement[3]: insert into RK_SHIPMENT_ITEM (ID, SHIPMENT_ID, ITEM_ID, CARTON_ID, QUANTITY_EXPECTED, QUANTITY_RECEIVED, QUANTITY_DAMAGED, PACK_SIZE, UNIT_COST, RECEIPT_DOC_ID, RECEIPT_DOC_TYPE, COMMENT_DESC) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Batch 0: [229][74][100001105][3325][8.0][Null(NUMERIC)][0.0][1.0][Null(VARCHAR)][3000002929][T][Null(VARCHAR)]
Batch 1: [230][74][100001105][3325][8.0][Null(NUMERIC)][0.0][1.0][Null(VARCHAR)][3000002929][T][Null(VARCHAR)]
Batch 2: [231][74][100001105][3325][8.0][Null(NUMERIC)][0.0][1.0][Null(VARCHAR)][3000002929][T][Null(VARCHAR)]
Batch 3: [232][74][330][3325][9.0][Null(NUMERIC)][0.0][1.0][Null(VARCHAR)][3000002929][T][Null(VARCHAR)]
at oracle.retail.sim.closed.dataaccess.BaseOracleDao.executeSimStatements(BaseOracleDao.java:277)
at oracle.retail.sim.shared.dataaccess.sim.oracle.dao.ShipmentOracleDao.insert(ShipmentOracleDao.java:64)
at oracle.retail.sim.shared.dataaccess.rib.subscriber.WarehouseDeliveryCreateInjector.inject(WarehouseDeliveryCreateInjector.java:149)
at oracle.retail.sim.shared.dataaccess.rib.subscriber.ASNInCreateInjector.doInject(ASNInCreateInjector.java:26)
at oracle.retail.sim.shared.dataaccess.rib.subscriber.SimRibInjector.inject(SimRibInjector.java:62)
at com.retek.rib.binding.injector.impl.ApplicationMessageInjectorImpl.inject(ApplicationMessageInjectorImpl.java:98)
at com.retek.rib.binding.injector.impl.ApplicationMessageInjectorEjb.inject(ApplicationMessageInjectorEjb.java:59)
at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.evermind.server.ejb.interceptor.joinpoint.EJBJoinPointImpl.invoke(EJBJoinPointImpl.java:35)
at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
at com.evermind.server.ejb.interceptor.system.TxRequiredInterceptor.invoke(TxRequiredInterceptor.java:50)
at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
at com.evermind.server.ejb.InvocationContextPool.invoke(InvocationContextPool.java:55)
at com.evermind.server.ejb.StatelessSessionEJBObject.OC4J_invokeMethod(StatelessSessionEJBObject.java:87)
at ApplicationMessageInjector_RemoteProxy_61h1813.inject(Unknown Source)
at sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.evermind.server.rmi.RmiMethodCall.run(RmiMethodCall.java:53)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SIMUSER.UK_RK_SHIPMENT_ITEM) violated

at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:380)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9145)
at oracle.oc4j.sql.proxy.StatementBCELProxy.executeBatch(StatementBCELProxy.java:452)
at oracle.retail.sim.closed.dataaccess.BaseOracleDao.executeSimStatements(BaseOracleDao.java:269)
... 26 more
2009-04-02 17:50:33,807 DEBUG [RMICallHandler-20][dataaccess.DataSourceDbThreadLocalConnectionFactory] getThreadLocalConnection clean(): closing thread local connection
2009-04-02 17:50:33,808 DEBUG [RMICallHandler-20][service-timings] End of ASNInCreateInjector.inject() for type: ASNInCre, payload class: ASNInDesc
2009-04-02 17:50:33,808 ERROR [RMICallHandler-20][impl.ApplicationMessageInjectorImpl] Exception calling inject.

 


Steps to Reproduce:

1. Log into RMS
2. Create a transfer from WH-Store for multiple items.
3. Approve and ship the transfer.
4. Check SIM error logs - the shipment is not consumed.
5. Check $ORACLE_HOME/opmn/logs for sim_oc4j container logs. It will show the ORA-00001 unique constraint violation error.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms