Troubleshooting
Problem
syncLoadedInventory api erroring out with "SQL Exception".
Symptom
syncLoadedInventory api erroring out with "SQL Exception". The exception
they are getting is: java.sql.SQLException: ORA-00001: unique constraint
(STERLING.YFS_INVENTORY_ITEM_I1) violated.
Configuration before
simulating the issue:
1) reate a synchronous service. Start->
weblogic JMS-> end.
2) create a asynchronous service. start-> weblogic->
API-> End.
3) Configure the syncLoadedInventory API for the API section
in the second service. Configure number of threads as "4".
4) Backup the
YFS_INVENTORY_ITEM, YFS_INVENTORY_SUPPLY and YFS_INVENTORY_SUPPLY_TEMP table.
5) Now import the DB Dump got from DSW.
6) After importing the
tables, truncate YFS_INVENTORY_ITEM and YFS_INVENTORY_SUPPLY table.
7)
Use the following SQL's to update YFS_INVENTORY_SUPPLY_TEMP table as this table
have customer related data:
update yfs_inventory_supply_temp set
inventory_item_key=' ' where shipnode_key in ('29_29092', '29_29160')
update yfs_inventory_supply_temp set shipnode_key='DC2' where
shipnode_key='29_29160'
update yfs_inventory_supply_temp set
shipnode_key='DC3' where shipnode_key='29_29092'
update
yfs_inventory_supply_temp set organization_code='DEFAULT' where
organization_code='DEFAULT'
8) Now thru the API tester, using the
synchronous service configured, pass the following messages to the weblogic
queue.
<Inventory ApplyDifferences="Y" CompleteInventoryFlag="Y"
InsertInventoryItem="Y" ReasonCode="DAILY_STORE_INV" ReasonText="Daily Store
Inventory"
<Inventory ApplyDifferences="Y" CompleteInventoryFlag="Y"
InsertInventoryItem="Y" ReasonCode="DAILY_STORE_INV" ReasonText="Daily Store
Inventory"
9) Start the intergration server and run the server where the
asynchronous service is associated.
Current Scenario:
System is throwing the following error
<Error
ErrorCode="java.sql.SQLException" ErrorDescription=""
ErrorRelatedMoreInfo="ORA-00001: unique constraint
(STERLING.YFS_INVENTORY_ITEM_I1) violated
">
<Stack>java.sql.SQLException: ORA-00001: unique constraint
(STERLING.YFS_INVENTORY_ITEM_I1) violated
Resolving The Problem
There is nothing that product can do to prevent that SQLException as System is doing a mass insert into the YFS_Inventory_Item table from 2 different processes and inventory items are shared across all nodes. One solution of avoiding this is to preload your YFS_Inventory_Item with some values and avoid having to use the InsertInventoryItem option by loading that InventoryIemKey in as part of your SQL loader into the YFS_Inventory_Supply_Temp table.
Historical Number
NFX9420
Product Synonym
[<p><b>]Escalation ID[</b><p>];178277;[<p><b>]Severity[</b><p>];Normal;[<p><b>]Type[</b><p>];NormalFix
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21554669