API to Load/maintain the Item Subinventory Information [ID 1458355.1]

Applies to:

Oracle Inventory Management - Version 11.5.10.CU2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.

Goal

Need an API to assign item only to subinventory. If the API not exist, it is possible to expand the package inv_loc_wms_pub with API to assign items to subinventory?

Fix

Currently there is no supported interface or public API for connecting items to subinventories in the mtl_item_sub_inventories table. ORACLE does not support direct insert in mtl_item_sub_inventories table.

There is an opened Enhancement Request bug for this:
Bug 5380622: ITEM SUBINVENTORY LOCATOR RELATIONSHIP API

Within the ER the following workaround has been stated:
INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE is useful only if the org is wms controlled because then the organization is mandatorily locator controlled. You can download the MFGAPI to get more information. Though MTL_ITEM_SUB_INVS_INTERFACE exists, currently we do not support this interface table.

As per the documentation for the API-INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE, we can suggest two ways to accomplish the goal of assigning items to a subinventory.

1. Create a locator using - create_locator API, assign the items to subinventory using CREATE_LOC_ITEM_TIE and then delete the locators using delete_locator api - all part of the INV_LOC_WMS_PUB API package.
This is  a roundabout route for accomplishing a simple job of assigning items to subinventory, that affects only the mtl_item_sub_inventories table.

2. Pertaining to the body of the API package -Create_LOC_ITEM_TIE code, the below part of the code can be the only one required assigning an item to the subinventory.

**************************************************************************
/* Check if an entry exists in MTL_ITEM_SUB_INVENTORIES table for the item
passed */
BEGIN
SELECT 1
INTO l_item_sub
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_inventory_item_id
AND secondary_inventory = p_subinventory_code
AND organization_id = l_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO mtl_item_sub_inventories
(inventory_item_id
, organization_id
, secondary_inventory
, last_update_date
, last_updated_by
, creation_date
, created_by
, inventory_planning_code)
VALUES (l_inventory_item_id
, l_organization_id
, p_subinventory_code
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, 6);
END;
******************************************************************************

Note: The workarounds mentioned in the above <> are also not supported.

References

NOTE:286339.1 - HOW to load Items to organization, subinventory and locator using API
BUG:5380622 - ITEM SUBINVENTORY LOCATOR RELATIONSHIP API