Use the item import process to import items from your legacy system . You can import items from any source into oracle inventory and oracle engineering.
When you import items through the item interface, you create new items in your item master organization or assign existing items to additional organizations. You can specify values for all the item attributes, or you can specify just a few attributes and let the remainder default or remain null.
You can also specify an item template for each item and inherit attribute values from the template.
The item interface also lets you import revision details, including past and future revisions and effectively dates. Validation of imported items is done using the same rules as the item definition forms, so you are insured of valid items.
The item interface reads data from two tables for importing items and item details. You use the mtl_systems_item_interface table for new item numbers and all item attributes. This is the main item interface table, and can be the only table you choose to use. If you are importing revision details for new items, you can use the mtl_item_revisions_interface table. A third table, mtl_interface_errors, is used for error tracking of all items that the item interface fails. You can import item categories using interface table called as mtl_item_categories_interface.
Before you use the item interface, you must write and run a custom program that extracts item information from your source system and inserts it into the mtl_system_item_interface table, and (if revision detail is included) the mtl_items_revisions_interface table.
After you load the items into these interface tables, you run the item interface to import the data.
The item interface assigns defaults, validates data you include, and then imports the new items.
You may also specify an item template for each item being imported.
You must import items into the item master organization before you import items into additional organizations. You can accomplish this by specifying only your item master organization on first runs of the item interface.
Once this has completed, you can run the item interface again, this time specifying an additional or all organizations. You can also use the item interface to import a single item material cost and material overhead, and revision details.
Testing the basic item import with minimum columns populated.
First always try to populate the master organization with a sample record and test.
1. How do i know my organization code and organization id?
Select organization_code, organization_id from org_organization_definitions
Order by organization_code;
Sample out put is below.
Org organization_id
--- -------------------------------
V1 204
M1 207
M2 209
M3 606
M4 1641
M5 1642
M6 1643
M7 1644
....
2. How do i know which organization is master to a child organization?
SELECT a.organization_id,
a.organization_code,
a.master_organization_id,
B.organization_code
FROM mtl_parameters a, org_organization_definitions b
WHERE a.master_organization_id = b.organization_id
ORDER BY a.organization_code;
Organization_id org master_organization_id org
204 v1 204 v1
207 m1 204 v1
209 m2 204 v1
606 m3 204 v1
1641 m4 204 v1
1642 m5 204 v1
1643 m6 204 v1
1644 m7 204 v1
in the above example for organizations m1 to m7, v1 organization is the master organization.
Populating v1 organization with one new item using just the basic columns.
Please truncate all ioi interface tables before loading mtl_system_items_interface:
ex. Truncate table <tablename>
Truncate table inv.mtl_system_items_interface;
Truncate table inv.mtl_interface_errors;
Truncate table inv.mtl_item_revisions_interface;
Truncate table inv.mtl_item_categories_interface;
Note: truncate frees space, delete does not free space.
INSERT INTO mtl_system_items_interface (process_flag,
set_process_id,
transaction_type,
organization_id,
Segment1,
description)
VALUES (1,
1,
'create',
204,
'testitemimport',
'testing item import');
COMMIT;
In the above insert process_flag = 1. Only when process_flag = 1 will the item
Import program processes the record.
Set_process_id = 1. You can set which ever number you want for this column.
This is basically used like a batch number. You can tell item import which set of records to process by entering a set_process_id.
Transaction_type is 'create' when you are inserting new records and 'update' when you are updating existing records.
We are using organization_id 204, which is for master organization v1.
Note: if you have more than one segment defined for items please populate all those segments.
To check which segments to be populated please got to
Setup > flexfields > key > segments. Now query up 'system items' by putting
It in flexfield title.
Select segments button, and now you should be able to see the different
Segments being defined.
Now run the item import program ie. Item > import > import items.
In process set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit ok button.
You can check view requests to check whether your request has completed
Successfully.now go to master items screen and query on 'testitemimport' the item that we had populated and you should be able to see the item.
Similarly you can import the same item for the child organization. Eg. We want item 'testitemimport' in organization m1 say seattle manufacturing then all i have to do is replace the organization_id by m1 organization_id. Now the insert statement will look like this.
INSERT INTO mtl_system_items_interface (process_flag,
set_process_id,
transaction_type,
organization_id,
segment1,
description)
VALUES (1,
1,
'create',
207,
'testitemimport',
'testing item import');
COMMIT;
Run the item import with the same parameters and then check if the concurrent
Request has completed successfully.
Now do change organization and go to organization item screen and query up testitemimport and you should be able to see the item in m1 organization.
Part 2
Item(Item Templates) Import Part 2
