Item Open Interface

The Following article is to cover concepts:

• SQL Loader
•  Item Attributes Update with help of Template 
• Standard Item Import Program.

Template is mask or format that is created in application front end with specific item attributes which can be applied on an existing ITEMS in INV or used while creating new Item (through interface process).

Below is a sample Item Template with attributes:

Create SQL Loader script and register it as a Concurrent Program to load data.

****************************

Create Control File:

OPTIONS (READSIZE=1000000, BINDSIZE=1000000, ROWS=200, ERRORS=200000)

LOAD DATA

CHARACTERSET XX12 – (Mention the character set used by your respective system)

INFILE '$1'

APPEND INTO

TABLE MTL_SYSTEM_ITEMS_INTERFACE

FIELDS TERMINATED BY '|'

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

ORGANIZATION_ID         "TRIM (:ORGANIZATION_ID)",

ITEM_NUMBER             "TRIM (:ITEM_NUMBER)",

TEMPLATE_NAME           "upper (:TEMPLATE_NAME)",

TRANSACTION_TYPE        "upper (:TRANSACTION_TYPE)",

SET_PROCESS_ID          "TRIM (:SET_PROCESS_ID)",

PROCESS_FLAG            CONSTANT 1,

PROGRAM_UPDATE_DATE     SYSDATE,

REQUEST_ID              "fnd_global.conc_request_id",

LAST_UPDATE_DATE        SYSDATE,

CREATION_DATE           SYSDATE,

CREATED_BY              "FND_GLOBAL.USER_ID",  

LAST_UPDATE_LOGIN       "FND_GLOBAL.LOGIN_ID"

Columns in Green are required while columns in Yellow are optional.

*******************************

Data file Format: ORGANIZATION_ID|ITEM_NUMBER|TEMPLATE_NAME|TRANSACTION_TYPE|SET_PROCESS_ID|

Sample record:

11|1207909|Finished Good|UPDATE|14042|

Control File path:             $INV_TOP/bin

Data File path:                 $INV_TOP/in

Data file name:                 Item_dat_file.dat

Below is the SQL Loader command for manual loading of data.

sqlldr apps/<password> CONTROL=Item_script.ctl  log=ITEM.log

OR

Run the SQL Loader program to load the data into MTL Items Interface table:

Program name:                XX INV ITEM LOAD TEMPLATE PROGRAM

Responsibility:                  XX INV Super User

Input Parameter:             $INV_TOP /in/Item_dat_file.dat

*******************************

Info on the interface table to be taken care before submitting the standard Open Interface:

·         Columns are same as in Item master table- MTL_SYSTEM_ITEMS.

·         Mandatory columns are:

o        ITEM_NUMBER or SEGMENT1: Item Number overrides Segment.

o        ORGANIZATION_CODE or ORGANIZATION_ID: Org id overrides Org code.

o        PROCESS_FLAG, TRANSACTION_TYPE

·         Item interface generates INVENTORY_ITEM_ID.

·         Process Flag:

o        1-Pending: To be processed- need to load records with this Flag.

o        3-Validation failed

o        7-Import failed

·         TRANSACTION TYPE: CREATE/ UPDATE

Other Item Interface tables:

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

Error Table:

MTL_INTERFACE_ERRORS

*******************************

Running Item Open Interface – IMPORT ITEMS (Parameters)

Runtime Options- Yes->for all organization codes,

   No->for current organization only

Validate Items-Yes->validate items,

No->if previously validated

Process Items- Yes->import items

No->only validate (Validate Items->Yes)

Create/Update Items-Create new/update existing items