Technical Blog Post
Abstract
manageItem API's CheckInventoryBeforeDelete=N creates orphaned records in YFS_INVENTORY_SUPPLY and YFS_INVENTORY_ITEM tables
Body
As a part of OMS 9.2.1 FP 19 we provided a feature that allowed us to delete an item from YFS_ITEM even if this item had corresponding entries in YFS_INVENTORY_SUPPLY and YFS_INVENTORY_ITEM tables
This was given in the form of a flag CheckInventoryBeforeDelete in the manageItem API
Reference : http://www-01.ibm.com/support/knowledgecenter/SS6PEW_9.2.1/com.ibm.help.om.foundation.fixpack.doc/r_FP19.html
Apart from what this is meant to do, it also creates orphaned records in the YFS_INVENTORY_SUPPLY and YFS_INVENTORY_ITEM tables
In the first appearance this seems like a bug and one would expect manageItem API to deal with these records. However, manageItem API is purely a configuration related API and is not meant to make any updates on YFS_INVENTORY_SUPPLY and YFS_INVENTORY_ITEM tables
For getting rid of the orphaned entries in the YFS_INVENTORY_SUPPLY you can follow a few simple steps :
1) Activate AFTER_DELETE_ITEM event of manageItem transaction
2) Create an Action to call a service
3) This service can call API getATP to get info on inventory supply for the item
4) With the information obtained from getATP API, you can call adjustInventory and negate the supply
Sample input xmls :
1) manageItem
<ItemList ComputeUnitCost="" ValidateAdditionalAttributeValues="">
<Item Action="Delete" CanUseAsServiceTool="" CheckInventoryBeforeDelete="N" GlobalItemID="" ItemGroupCode="" ItemID="item"
ItemKey="201504221343535012" OrganizationCode="DEFAULT" SubCatalogOrganizationCode="" SyncTS="" UnitOfMeasure="EACH">
</Item>
</ItemList>
2) getATP
<GetATP ConsiderAllNodes="" ConsiderAllSegments="" DistributionRuleId=""
EndDate="" IgnoreOrdering="Y" ItemID="item"
OrganizationCode="DEFAULT" PeriodicalLength="" ProductClass=""
Segment="" SegmentType="" ShipNode="" UnitOfMeasure="EACH"/>
3) adjustInventory
<Items>
<Item AdjustmentType="ADJUSTMENT" Availability="TRACK"
ETA="" ItemID="item" OrganizationCode="DEFAULT"
ProductClass="" Quantity="-10.0" ReasonCode=""
ReasonText="" Reference_1="" Reference_2=""
Reference_3="" Reference_4="" Reference_5=""
Segment="" SegmentType="" ShipNode="" SupplyType="" UnitOfMeasure="EACH"/>
</Items>
As for deleting entries from the YFS_INVENTORY_ITEM, we of course don't recommend direct database update and neither do we have a feature available to do this as of now
However we do have an Enhancement Request logged for this in our RFE tool. You can refer the following link
https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=70415
If you are looking for a similar enhancement, don't forget to hit the 'Vote' button
UID
ibm11124871