Search This Blog

Thursday, October 2, 2008

Item Import (Item Conversion)

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

PROCESS_FLAG = 1 (The column is used to identify status of record)
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import .

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import(IOI) program can be run in parallel if seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item's separate
Organization records into two different SET_PROCESS_IDs that are being run in

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert '!'


Mrinish said...

Really Informative Blog.I have a question, I am trying to import item revision using Item Revision interface. I am able to import revisions for Master Org but it is not working for child orgs. What can be the issue? can somebody help?

Suresh Vaishya said...

Are you inserting record into mtl_revisions_interface table and if yes then what is the error message that you are getting.

Make sure that the starting revision number for organization is in sync with what you are using. The starting revision number is setup for each organization seperately.


Mrinish said...

Yes,I am inserting record into mtl_revisions_interface. Item import request is getting completed in error but the log message says
Item Catalog Group Descriptive Elements Open Interface import completed successfully for all records in this record set.

Starting revision format is correct.

Mrinish said...

Also one more ...currently I am inserting record only into MTL_Revision_interface ; is it necesarry to insert it in MTL_System_item_interface also to pick it through IOI

Suresh Vaishya said...

This doesnt looks like an error message.

Make sure you leave revision_number column in mtl_system_items_interface table as null.
delete all records from mtl_item_revisions_interface table and try inserting again.

first try with lesser number of items. Any message in MTL_interface_errors table.

Mrinish said...

I am getting message in interface error table as
This combination of Item and Revision already exists in MTL_ITEM_REVISIONS.
for Child orgs

Also in mtl_system_item_interface
transaction type should be create or update?

Suresh Vaishya said...

If its a new item then should be create.
If updating existing then should be update.

Mrinish said...

Thanks a ton for all your help Suresh.

Bharadwaj1177 said...

Suresh ,

Am Surya , little new to conversions but I needd to handle now.please can you leave me your email ..need your help and truly appreciate it.


Padala Sandeep said...

i run the imports items program the program completed normally.
but the data not updated into the base table can i know what's the problem

Anonymous said...

Hey Surya,
I am facing an issue with revisions interface. I have items already created in my item master with a default revision of 000. I am trying to create another revision for these items. I inserted records in the mtl_system_items_Interface (populating item_number, organization_id, Transaction_type, set_process_id and Process_flag) with Transaction Type of 'UPDATE' and I inserted records into MTL_ITEM_REVISIONS_interface (item_number, organization_id, revision, change_notice, implementation_date, effectivity_date, set_process_id and process_flag) with transaction_type as 'CREATE'.

I ran IOI, it deletes records from mtl_system_items_interface but it sets the process_flag to -888 too the records in MTL_ITEM_REVISIONS_interface and does not update Item revisions.

Am I missing something? Please help.


Reswin Rodriquez said...


We are running 40Lakhs records using item import program. We splited 5000 each bach and we did gather stats and rebuild index. But each batch is taking 7 to 10 mins. so it is running for 3 to 4 days. Is there is any way to improve the performance?

Anil Gupta said...
This comment has been removed by the author.
Anil Gupta said...

i am getting below error while calling EGO_ITEM_PUB API for item creation

INV_ITEM_GRP.Insert_Revision_Record: Unexpexted error: ORA-01403: no data found.

Until now i have not called item revision API, but then also getting this issue.

Can you please explain how to resolve this error?

Anil Gupta

Copyright (c) All rights reserved. Presented by Suresh Vaishya