To import items and material cost associated to it.
First check which cost elements are defined for the organization.
Select cost_type_id, cost_type, description from cst_cost_types;
Output looks like this.
Cost_type_id cost_type description
------------ ------------- ------------------------------------
1 frozen frozen standard cost type
2 average average cost type
3 pending pending standard cost type
To find the sub element name defined for your organization for a particular cost type please checks bom resources table.
In our example we are checking for material sub element in organization m1 with organization_id = 207 for cost_code type 1.
SELECT resource_code,
description,
cost_element_id,
cost_code_type
FROM bom_resources
WHERE organization_id = 207 AND cost_code_type = 1;
Resource_code description cost_element_id cost_code_type
------------- ----------------- --------------- --------------
Material material sub element 1 1
Labor labor sub element 1 1
Expense expense sub element 1 1
In our scenario we want to create a new item in organization m1 along with its material cost (say $11) assuming the same item is already created in master organization v1.
INSERT INTO mtl_system_items_interface (process_flag,
set_process_id,
transaction_type,
organization_id,
segment1,
description,
template_id,
material_cost,
material_sub_elem)
VALUES (1,
1,
'create',
207,
'testcostmaterial',
'testing item material cost import',
259,
11,
'material');
COMMIT;
In the above sql you might have observed we are populating material_cost and material_sub_elem.similary we can use material_sub_elem_id, material_oh_rate, material_oh_sub_elem and material_oh_sub_elem_id colums.to check the subelements you can go to cost management responsibility for your organization and under setup > sub-elements you will see the respective subelements 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 organization items and query on 'testcostmaterial' the item that we had populated and you should be able to see the item.
Go to tools > item costs and then hit open button from item cost summary screen and you should see the details. In out example we see a material cost of $11 for the item in cost type ‘frozen’ (standard costing).
To import item and revisions associated to it.
Whenever you import a new item based on the starting revision specified under 'revison/lot/serial' tab in organization parameters screen the default revison gets assigned to the item.
For eg. The default revision in v1 organization is 'a' when we do item import this revision gets associated to the item.now lets import a new item with revision 'b' which is not the default revision.
INSERT INTO mtl_system_items_interface (process_flag,
set_process_id,
transaction_type,
organization_id,
segment1,
description,
revision)
VALUES (1,
1,
'create',
204,
'testrevb1',
'testing item revision',
'b');
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 organization items and query on 'testrevb1' the item that we had populated and you should be able to see the item.
Go to tools > revisions and then you should be able to see two records one with revision 'a' which is the default revision and another with revision 'b' that we had populated.
In case you want to import a new revision say revision 'c'. You directly populate the mtl_item_revisions_interface table and then run item import.
INSERT INTO mtl_item_revisions_interface (item_number,
revision,
revision_label,
implementation_date,
effectivity_date,
Process_flag,
organization_id,
set_process_id,
transaction_type)
VALUES ('testrevb1',
'c',
'c',
SYSDATE,
SYSDATE,
1,
204,
1,
'create');
COMMIT;
Now run the item import.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.
When you query up the item 'testrevb1' you will see the new revision 'c'.
First check which cost elements are defined for the organization.
Select cost_type_id, cost_type, description from cst_cost_types;
Output looks like this.
Cost_type_id cost_type description
------------ ------------- ------------------------------------
1 frozen frozen standard cost type
2 average average cost type
3 pending pending standard cost type
To find the sub element name defined for your organization for a particular cost type please checks bom resources table.
In our example we are checking for material sub element in organization m1 with organization_id = 207 for cost_code type 1.
SELECT resource_code,
description,
cost_element_id,
cost_code_type
FROM bom_resources
WHERE organization_id = 207 AND cost_code_type = 1;
Resource_code description cost_element_id cost_code_type
------------- ----------------- --------------- --------------
Material material sub element 1 1
Labor labor sub element 1 1
Expense expense sub element 1 1
In our scenario we want to create a new item in organization m1 along with its material cost (say $11) assuming the same item is already created in master organization v1.
INSERT INTO mtl_system_items_interface (process_flag,
set_process_id,
transaction_type,
organization_id,
segment1,
description,
template_id,
material_cost,
material_sub_elem)
VALUES (1,
1,
'create',
207,
'testcostmaterial',
'testing item material cost import',
259,
11,
'material');
COMMIT;
In the above sql you might have observed we are populating material_cost and material_sub_elem.similary we can use material_sub_elem_id, material_oh_rate, material_oh_sub_elem and material_oh_sub_elem_id colums.to check the subelements you can go to cost management responsibility for your organization and under setup > sub-elements you will see the respective subelements 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 organization items and query on 'testcostmaterial' the item that we had populated and you should be able to see the item.
Go to tools > item costs and then hit open button from item cost summary screen and you should see the details. In out example we see a material cost of $11 for the item in cost type ‘frozen’ (standard costing).
To import item and revisions associated to it.
Whenever you import a new item based on the starting revision specified under 'revison/lot/serial' tab in organization parameters screen the default revison gets assigned to the item.
For eg. The default revision in v1 organization is 'a' when we do item import this revision gets associated to the item.now lets import a new item with revision 'b' which is not the default revision.
INSERT INTO mtl_system_items_interface (process_flag,
set_process_id,
transaction_type,
organization_id,
segment1,
description,
revision)
VALUES (1,
1,
'create',
204,
'testrevb1',
'testing item revision',
'b');
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 organization items and query on 'testrevb1' the item that we had populated and you should be able to see the item.
Go to tools > revisions and then you should be able to see two records one with revision 'a' which is the default revision and another with revision 'b' that we had populated.
In case you want to import a new revision say revision 'c'. You directly populate the mtl_item_revisions_interface table and then run item import.
INSERT INTO mtl_item_revisions_interface (item_number,
revision,
revision_label,
implementation_date,
effectivity_date,
Process_flag,
organization_id,
set_process_id,
transaction_type)
VALUES ('testrevb1',
'c',
'c',
SYSDATE,
SYSDATE,
1,
204,
1,
'create');
COMMIT;
Now run the item import.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.
When you query up the item 'testrevb1' you will see the new revision 'c'.
