HOW to load Items to organization, subinventory and locator using API [ID 286339.1]

Applies to:

Oracle Inventory Management - Version: 11.5.2 to 11.5.9
Information in this document applies to any platform.

Goal

Is there an open interface/API(like MTL_SYSTEMS_ITEM_INTERFACE) in Oracle R11i that can be used to load subinventory data.
Customer are using Dataload(a 3rd party Oracle Applications data loader software), the software can load data without any major problem but the processes are too slow and no error controls of the loading. Therefore, customer want to know if that is an interface for subinventory

Solution

If the Org for which you want to transfer the info , WMS enabled, then the CREATE_LOC_ITEM_TIE procedure, under Locator Maintenance APIs, may be useful for this issue;
Note that the list of public API's are all that are available thru the Oracle Inventory Apps;

The API quoted is the only API for this functionality ( locators ) Items that are not locator controlled, can be assigned to subinventories thru the normal Item Open Interface;

An enhancement request was filed for this issue previously;

Note that the Technical Reference Manual shows the table MTL_ITEM_SUB_INVS_INTERFACE and the explanation of the process; Please note, the TRM also states that this table is not used;

The following process was established;

spool msi_subinv.lst
PROMPT msi_subinv.sql

PROMPT Current counts in MTL_SYSTEM_ITEMS_INTERFACE
select process_flag, count(*) from MTL_ITEM_SUB_INVS_INTERFACE group by process_
flag;

prompt
prompt Assigning an item to a subinventory.
prompt
accept YourItemID DEFAULT '11175' prompt 'Please enter your inventory item id
(Default 11175): '
prompt
prompt
accept YourOrgID DEFAULT '207' prompt 'Please enter your Org Code (Default 207):
'
prompt

prompt
accept YourSubinv DEFAULT 'FGI' prompt 'Please enter a Subinventory Code
(Default FGI): '
prompt

insert into MTL_ITEM_SUB_INVS_INTERFACE
(
TRANSACTION_TYPE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PRIMARY_SUBINVENTORY_FLAG,
PICKING_ORDER,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
INVENTORY_PLANNING_CODE,
FIXED_LOT_MULTIPLE,
MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
PROCESS_FLAG,
ORGANIZATION_CODE,
ITEM_NUMBER
)
VALUES
(
'CREATE', --TRANSACTION_TYPE
&YourItemID, -- INVENTORY_ITEM_ID,
&YourOrgID, -- ORGANIZATION_ID,
'&YourSubinv', -- SECONDARY_INVENTORY,
sysdate,-- LAST_UPDATE_DATE,
-1, -- LAST_UPDATED_BY,
sysdate, -- CREATION_DATE,
-1, -- CREATED_BY,
-1, -- LAST_UPDATE_LOGIN,
NULL, -- PRIMARY_SUBINVENTORY_FLAG,
NULL, -- PICKING_ORDER,
NULL, -- MIN_MINMAX_QUANTITY,
NULL, -- MAX_MINMAX_QUANTITY,
NULL, -- INVENTORY_PLANNING_CODE,
NULL, -- FIXED_LOT_MULTIPLE,
NULL, -- MINIMUM_ORDER_QUANTITY,
NULL, -- MAXIMUM_ORDER_QUANTITY,
NULL, -- SOURCE_TYPE,
NULL, -- SOURCE_ORGANIZATION_ID,
NULL, -- SOURCE_SUBINVENTORY,
NULL, -- PROCESS_FLAG = PENDING
'&YourMasterOrgCode', -- ORGANIZATION_CODE,
NULL -- ITEM_NUMBER
);

spool off