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