Importing material cost associated to item.

Importing material cost associated to item.


First check which cost elements are defined for the organizaion.


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 check 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;


Note : Using mtl_system_items_interface 'Frozen cost' also be converted


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).


NOTE: Item import cannot be used to update item costs.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You have to populate
CST_ITEM_CST_DTLS_INTERFACE,
CST_RESOURCE_COSTS_INTERFACE,
CST_RES_OVERHEADS_INTERFACE and
CST_DEPT_OVERHEADS_INTERFACE accordingly.


The concurrent request can be launched by navigating to cost->cost massedits->Import Cost Information.


The parameters for the Cost Import concurrent program and their description is
as follows :


Import Cost Option : A LOV is provided from which the user can select one of the import options which may be either to import Only item costs , Only resource costs , Only overhead rates or all the cost information .


Option Table from which data is processed
---------- --------------------------------------------
Only item cost cst_item_cst_dtls_interface
Only resource costs cst_resource_costs_interface
Only overhead rates cst_res_overheads_interface ,
cst_dept_overheads_interface


All Cost Information From all the four interface tables


Mode to run this request : A LOV is provided with possible two values , 'Insert new cost' or 'Remove and replace cost'.


The 'Insert new cost' mode , is useful if you are importing large # of items and are not sure if that Item/Organization/Cost Type combination already exists in the production tables, if it does then the row in the interface table would be flaged as errored and not imported.This would prevent any accidental overwrite of already existing data.
With 'Remove and replace cost' mode all the previous cost information for this item, cost_type and organization combination will be deleted from the production tables and the new information will overwrite (replace) the already existing one.


Group Id Option : A LOV is provided from which the user can either select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple Cost Import process requests he can do so by submitting one request per group id. For doing so the data in the interface tables should be stamped with distinct group id value by using the NEXTVAL from the sequence generator CST_LISTS_S .The use of this sequence geneartor is a MUST for generating multiple groups or may lead to data corruption as these interface tables are used by other processes too.
If the user selects "ALL" from the list then a group ID generated by a sequence will replace the group ID in the interface tables (if any) and all the unprocessed rows from the four interface table (viz.cst_item_cst_dtls_interface , cst_resource_costs_interface ,cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed in one run.


Cost type to import to : The user is provided with a LOV from which he needs to select the cost type in which he wishes to import the cost information. Even if the user has populated a cost type or cost type ID in the interface tables, it would be overwritten with the one that is selected here.


The cost types that the user can pick from is restricted to the multi-org,
updateable cost types.


Delete succesfull rows : This parameter decides whether the successfully processed rows should be deleted from the interface tables at the end of the run. If the user selects 'Yes' then all the successful rows be deleted, basically rows that do not have their error flag set to "E".


Importing directly into Frozen/Average cost type (i.e non updateable cost types)and merging of new cost with existing costs is not supported at this time and would still have to be processed by cost update routines. Also when importing the costs from the interface table ,material overhead defaults (if any) specified for an Organization/Category would not be respected.




Minimum columns in each table that the user needs to provide
1. CST_ITEM_CST_DTLS_INTERFACE
The columns that the user has to provide are
a. Inventory_item_id
b. organization_ID or organization_code.
c. resource_ID or resource_code for cost elements other than 1(material).
If we are importing cost into material cost element and default material
subelement has been specified on 'Define Organization parameters' form
then that would be respected unless the user overides it with a value
in this column.
d. usage_rate_or_amount
e. cost_element_ID or cost_element
f. Process Flag (must be set to 1)


We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag,
lot_size from the row from CST_ITEM_COSTS for this item and the default cost
type(default cost type of the cost type specified to import the costs into). If
there is no such row already defined in CST_ITEM_COSTS, then, the values for
these 4 columns also need to be specified and they have to be the same for all
rows of this item, cost type and organization combination.


CST_RESOURCE_COSTS_INTERFACE
a. Resource_ID or resource_code
b. organization_ID or organization_code
c. resource_rate
d. Process_flag (must be set to 1)


CST_RES_OVERHEADS_INTERFACE
a. Resource_ID or resource_code
b. Overhead_ID or overhead
c. Organization_ID or organization_code
d. Process_flag(must be set to 1)


CST_DEPT_OVERHEADS_INTERFACE
a. Department_ID or department
b. Overhead_ID or overhead
c. Organization_ID
d. Rate_or_amount
e. Process_flag(must be set to 1)


The other columns will be defaulted.


CST_ITEM_COSTS_INTERFACE can be used to import all the costs
MATERIAL_COST, OUTSIDE PROCESSING COST, OVER HEAD COST and other costs

Article from Uday Somavarapu Blog