Item import (Item conversion) In Oracle Apps

Item import (Item conversion)

Overview: Item import program can be used to create new Inventory items or to update existing inventory items.  We can import items with all attributes information or use item templates to set attributes and import items linking with item template.

Uses:

1. Import New Inventory Items in Master Organization
2. Assign Items to child Organization (Step1 has to be finished before this task can be executed)
3. Update existing Inventory item attributes information
4. Assign Item Category Assignments
5. Lot numbers can be imported along with items
6. Serial numbers can be imported along with items

Execution Methods:

1.  Run  “Import Items” concurrent program from inventory responsibility manually.
2. Use PLSQL wrapper code to kick of the “Import Items” Concurrent program from backend.

Parameters:

All Organizations: Yes / No (Select Yes if you want to import items belonging to various organizations reglardless of  current selection in form)

Validate Items: Yes / No (Select Yes if you would like to validate Items. With out validation we can not proceed to Processing)

Process Items: Yes / No (Select Yes to process / import items)

Delete Processed Items: Yes / No (Select Yes to purge data in interface tables for the processed records)

Process Set: You can use this parameter to process records in batches. It will use SET_PROCESS_ID column in interface table.

Create or Update Items: 1 / 2/ 3. (1- Create New Items, 2 – Update Existing Items, 3 – Sync Items. Option 3 should be used if we have some items to be created and some to be updated)

Gather Statistics: Yes / No. (Helps to increase performance of SQL queries based on items table)

Pre-requisites:

1. Master Organization should be defined.
2. Child Organization should be defined.
3. Code Combinations should be defined.
4. Item Templates (If using item templates in interface table)
5. Defining Item Status Codes
6. Item Types should be defined.

Common Issues:

1. Don’t try to load both master item record and child item record at a time. If you want to do that make sure you use process set column.
2. Revision numbers have to be in chronological order, meaning it can be like AA, BB, CC … or 1,2,3,….but i can’t be AA, 1, BB, 2 etc.
3. Category assingments, Lot numbers, serial numbers has to be linked properly to the respective tables.

Interface tables:

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)

Error Tracking Tables:

MTL_INTERFACE_ERRORS (View errors after import)

Base tables:
MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES

MTL_SYSTEM_ITEMS_INTERFACE: (Important columns to be populated)

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: (Mandatory columns to be populated)

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: (Mandatory columns to be populated)

INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the item_number in mtl_system_items_interface table)
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.

Notes:

Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.

Sample Script

Staging Table:

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE erps_item_import (segment1 varchar2(100), organization_id number, process_flag varchar2(100), set_process_id number, transaction_type varchar2(100), description varchar2(100), primary_UOM_code varchar2(100), validation_flag varchar2(10), validation_errors varchar2(100) );

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

-- SCRIPT FOR CUSTOM VALIDATIONS AND INSERT DATA FROM STAGING TABLE INTO INTERFACE TABLE declare v_organization_check NUMBER := 0; Begin --CUSTOM VALIDATIONS begin for i in (select * from APPS.erps_item_import) -- Staging table Loop SELECT 1 INTO v_organization_check FROM mtl_parameters mp WHERE mp.organization_id = i.organization_id ; update erps_item_import set validation_flag = 'Y' where organization_id = i.organization_id; end loop; exception when others then null; end; -- INSERT DATA FROM STAGING INTO INTERFACE TABLE INSERT INTO mtl_system_items_interface (segment1, organization_id, process_flag, set_process_id, transaction_type, description, primary_UOM_code ) SELECT segment1, organization_id, process_flag, set_process_id, transaction_type, description, primary_UOM_code FROM APPS.erps_item_import WHERE VALIDATION_FLAG ='Y' ; COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error while inserting record in interface table ' ||SUBSTR(SQLERRM,1,200)); END;

 

If you have any questions let us know in comments section.