Upload Inventory Item By Interface

1- The interface table for Item Import is MTL_SYSTEM_ITEMS_INTERFACE

 and it contains every column in the Inventory item master table, MTL_SYSTEM_ITEMS.

Meaning of PROCESS_FLAG Values:

Code	Code Meaning
1	Pending
2	Assign complete
3	Assign/validation failed
4	Validation succeeded; import failed
5	Import in process
7	Import succeeded

Validations:

1] Validation for organization code (standard table: ORG_ORGANIZATION_DEFINITIONS)

2] Validation for Item_number (standard table: mtl_system_items_b)

3] Validation for Description

4] Validation for Primary Unit of Measure (standard table: MTL_UNITS_OF_MEASURE)

5] Validation for Template Name (standard table: mtl_item_templates)

6] Validation for Item Type (standard table: FND_COMMON_LOOKUPS)

7] Validation for ENCUMBRANCE_ACCOUNT (standard table: gl_code_combinations)

8] Validation for EXPENSE_ACCOUNT (standard table: gl_code_combinations)

Note: You can add more validation as per your business requirement.

Record Insertion:

Through your custom program you can insert the below columns in the interface table. Again the list is a sample one; you can add additional columns as your business requirement.

Columns inserted:

• ORGANIZATION_ID                     –>Taken from ORG_ORGANIZATION_DEFINITIONS table
• ORGANIZATION_CODE               –>Taken from Staging table
• LAST_UPDATE_DATE                 –>sysdate
• LAST_UPDATED_BY                   –>fnd_global.user_id
• CREATION_DATE                        –>sysdate
• CREATED_BY                             –>fnd_global.user_id
• LAST_UPDATE_LOGIN                 –>fnd_global.login_id
• DESCRIPTION                             –>Taken from Staging table
• SEGMENT1                                 –>Taken from Staging table
• PRIMARY_UOM_CODE                 –>Taken from Staging table
• PRIMARY_UNIT_OF_MEASURE     –>Taken from MTL_UNITS_OF_MEASURE
• ITEM_TYPE                                  –>NULL
• TEMPLATE_NAME                       –>Taken from Staging table
• TEMPLATE_ID                             –>Taken from mtl_item_templates        
• MIN_MINMAX_QUANTITY             –>Taken from Staging table
• MAX_MINMAX_QUANTITY            –>Taken from Staging table
• LIST_PRICE_PER_UNIT                –>Taken from Staging table
• ITEM_CATALOG_GROUP_ID        –>Taken from Staging table
• SET_PROCESS_ID                       –>1
• PROCESS_FLAG                          –>1
• TRANSACTION_TYPE                  –>‘CREATE’

Standard Concurrent Program:

After you insert valid data into Interface table, you can go to Items > Import > Import Items and run the standard concurrent program. Here is the parameter form.

1] All Organizations:

• Yes: Run the interface for all organization codes in the item interface table.
• No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:

• Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
• No:  Do not validate items in the interface table.

3] Process Items:

• Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
• No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:

• Yes: Delete successfully processed items from the item interface tables.
• No: Leave all rows in the item interface tables.

5] Process Set:

Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.

When Error

Select

   ORGANIZATION_ID,

   UNIQUE_ID,

   REQUEST_ID ,

   TABLE_NAME ,

   COLUMN_NAME,

   ERROR_MESSAGE

   CREATION_DATE,

   MESSAGE_TYPE

from

    MTL_INTERFACE_ERRORS

order by CREATION_DATE;







Useful Metalink Note:

1. FAQ for Item Import [ID 109628.1]
2. Error in Validating MTL_SYSTEM_ITEMS_INTERFACE [ID 1057175.6]
3. A Guideline to IOI Error Messages and Solutions [ID 52746.1]
4. How to Import Item Costs Using the Item Open Interface (IOI) [ID 191376.1]
5. Item Import Performance Tips [ID 66496.1]
6. INCOIN: Basic Steps for Researching Failed Item Imports [ID 552683.1]
7. Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]
8. How To Prepare to Run Parallel Runs of the Item Import Interface INCOIN [ID 842767.1]
9. Item Import Gives Error: “The Default Primary Unit of Measure Is Invalid” [ID 789927.1]
10. Understanding Item Import and Debugging Problems with Item Import [ID 268968.1]
11. How to create a Category Set and Assign Items to Categories [ID 423551.1]
12. Item API Or Concurrent Manager, Which One Is Best For Item Creation? [ID 760498.1]