Item import (Item conversion)

Item import (Item conversion), AskHareesh.blogspot.com

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.
Important Columns:
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.


*/