Oracle Apps Knowledge Sharing

Item import (Item conversion)

The Item Interface lets you import items into Oracle Inventory.

Pre-requisites:

Creating an Organization
Code Combinations
Templates
Defining Item Status Codes
Defining Item Types

Interface tables:

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
MTL_INTERFACE_ERRORS (View errors after import)

Concurrent Program:

Item import

In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.

Base Tables:

MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES

Validations:

Check for valid item type.
Check for valid part_id/segment of the source table.
Validate part_id/segment1 for master org.
Validate and translate template id of the source table.
Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
Check for valid item status.
Validate primary uom of the source table.
Validate attribute values.
Validate other UOMs of the source table.
Check for unique item type. Discard the item, if part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.
Load master records and category records only if all
Load child record if no error found.

Some important columns that need to populated in the interface tables:

MTL_SYSTEM_ITEMS_INTERFACE:

PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)

MTL_ITEM_CATEGORIES_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER.
ORGANIZATION_ID or ORGANIZATION_CODE or both.
TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not possible through Item Import).
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
CATEGORY_ID or CATEGORY_NAME or both.
PROCESS_FLAG = 1
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)

MTL_ITEM_REVISIONS_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the ORGANIZATION_ID or ORGANIZATION_CODE or both)
 REVISION
CHANGE_NOTICE
ECN_INITIATION_DATE
IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER
EFFECTIVITY_DATE
ATTRIBUTE_CATEGORY
ATTRIBUTEn
REVISED_ITEM_SEQUENCE_ID
DESCRIPTION
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1

Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.