CST Item Standard Cost Upload

➜ Back to Library
Description

CST Item Standard Cost Upload loads or revises item standard costs in an updateable, non-frozen standard cost type, from Excel. It stages the elemental costs and runs Oracle’s standard Import Item Costs program, and can optionally run a cost rollup afterwards. It loads this-level elemental costs.

When to use it

  • Load brand-new item costs into a standard, updateable cost type.
  • Download current costs, amend them in Excel, and re-upload.
  • Copy costs from one cost type to another.
  • Mass-update resource or sub-element rates across many items.
  • Trigger a cost rollup automatically after loading costs.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the inventory organization.
  • The target cost type uses standard costing, is updateable and not frozen.
  • The items are costing-enabled, and any resources, departments and operations you reference exist.

Step 1 – Set the parameters and download

In Blitz Report, open CST Item Standard Cost Upload and set the parameters:

ParameterPurpose
Target Cost TypeThe cost type the costs are loaded into (updateable, non-frozen standard cost types only).
ModeRemove and replace cost information, or Insert new cost information only. Default is Remove and replace.
Source Cost TypeOptionally download current costs from another cost type (to amend, or to copy into the target).
Rollup Costs? / Rollup Type / Rollup OptionOptionally run a cost rollup after the import.
Organization / Item / Category filtersRestrict which items are downloaded.

Run the upload to download and open the Excel file.

Step 2 – Enter the costs

Each row is one elemental cost line of an item. The Organization Code, Item and Cost Element identify the line; enter the Usage Rate or Amount and, where they apply, the Sub Element (resource), Basis, and the routing Operation Seq Num and Department. The item-level controls Inventory Asset, Based on Rollup, Lot Size and Shrinkage Rate apply to the whole item.

Step 3 – Validate and Save

Click Validate and Save. This checks for missing required values and runs any extra validation the upload defines, then saves the file. Correct anything it flags before continuing.

Step 4 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which stages the costs and runs Oracle’s Import Item Costs program (and a cost rollup if you chose one). When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Item standard costs loaded into the chosen cost type, optionally rolled up.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Which cost types can I upload to?
Only standard-costing cost types that are updateable, not frozen and not disabled, and within your organization access.

What’s the difference between Remove and replace and Insert new cost information only?
Remove and replace overwrites the existing costs for the items being imported; Insert adds costs only where none currently exist. The default is Remove and replace.

I edited only some rows – will the unchanged ones upload?
It depends on Auto Populate Upload Columns. If Yes (the default), all downloaded rows upload; if left blank, only rows you edit upload – and for a multi-element item you must edit every element row.

Can I upload rolled-up (lower-level) costs?
No. Oracle’s interface supports this-level costs only. Use the rollup option to derive lower-level costs.

How do I roll up costs after uploading?
Set Rollup Costs? to Yes and choose the Rollup Type and Rollup Option. The rollup runs only after a successful import.

Troubleshooting

MessageCauseWhat to do
Item Cost Import not submittedThe Import Item Costs request could not be submitted.Check the message and the concurrent manager; confirm you can submit the program.
Item Cost Import completed in errorThe import program ran but ended in error for one or more rows.Open the named Oracle request log, correct the flagged rows and re-upload.
Per-row error (invalid rate, basis, sub-element, operation or department)A staged row failed Oracle’s import validation.Fix the offending value for that organization and cost element, and re-upload.
Rollup Type and Rollup Option must be specifiedRollup Costs? is Yes but the type or option was left blank.Provide both Rollup Type and Rollup Option, or set Rollup Costs? back to No.
Cost rollup failed warningThe post-import rollup failed for some items.The costs were imported; review the rollup request log and re-run the rollup or fix the BOM/routing.
select
case when nvl2(:p_autopopulate_upload_status,'Y','N')='Y' then xxen_upload.action_meaning(xxen_upload.action_update) else null end action_,
case when nvl2(:p_autopopulate_upload_status,'Y','N')='Y' then xxen_upload.status_meaning(xxen_upload.status_new) else null end status_,
case when nvl2(:p_autopopulate_upload_status,'Y','N')='Y' then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) else null end message_,
to_number(null) request_id_,
null modified_columns_,
null row_id,
-- Item
cct_t.cost_type target_cost_type,
mp.organization_code,
msiv.concatenated_segments item,
msiv.description item_description,
(select misv.inventory_item_status_code_tl from mtl_item_status_vl misv where misv.inventory_item_status_code=msiv.inventory_item_status_code) item_status,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy,
xxen_util.meaning(cic.inventory_asset_flag,'SYS_YES_NO',700) inventory_asset,
xxen_util.meaning(cic.based_on_rollup_flag,'CST_BONROLLUP_VAL',700) based_on_rollup,
coalesce(cic.lot_size,msiv.std_lot_size,1) lot_size,
coalesce(cic.shrinkage_rate,msiv.shrinkage_rate,0) shrinkage_rate,
-- Item Cost Element Details
xxen_util.meaning(cicd.rollup_source_type,'CST_SOURCE_TYPE',700) cost_source_type,
(select cce.cost_element from cst_cost_elements cce where cce.cost_element_id=nvl(cicd.cost_element_id,decode(cic.inventory_asset_flag,1,1))) cost_element,
nvl(br.resource_code,
(select
 br2.resource_code
 from
 bom_resources br2
 where
 br2.resource_id=mp.default_material_cost_id and
 br2.organization_id=mp.organization_id and
 br2.cost_element_id=1 and
 nvl(br2.allow_costs_flag,1)=1 and
 nvl(br2.disable_date,sysdate)>=sysdate and
 cic.inventory_asset_flag = 1
)) sub_element,
nvl(cicd.usage_rate_or_amount,decode(cic.inventory_asset_flag,1,0))  usage_rate_or_amount,
xxen_util.meaning(cicd.basis_type,'CST_BASIS_SHORT',700) basis,
ca.activity,
cicd.activity_units,
cicd.item_units,
-- other enterable fields
xxen_util.display_flexfield_context(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category) attribute_category,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE1',cicd.rowid,cicd.attribute1) item_cost_attribute1,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE2',cicd.rowid,cicd.attribute2) item_cost_attribute2,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE3',cicd.rowid,cicd.attribute3) item_cost_attribute3,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE4',cicd.rowid,cicd.attribute4) item_cost_attribute4,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE5',cicd.rowid,cicd.attribute5) item_cost_attribute5,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE6',cicd.rowid,cicd.attribute6) item_cost_attribute6,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE7',cicd.rowid,cicd.attribute7) item_cost_attribute7,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE8',cicd.rowid,cicd.attribute8) item_cost_attribute8,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE9',cicd.rowid,cicd.attribute9) item_cost_attribute9,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE10',cicd.rowid,cicd.attribute10) item_cost_attribute10,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE11',cicd.rowid,cicd.attribute11) item_cost_attribute11,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE12',cicd.rowid,cicd.attribute12) item_cost_attribute12,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE13',cicd.rowid,cicd.attribute13) item_cost_attribute13,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE14',cicd.rowid,cicd.attribute14) item_cost_attribute14,
xxen_util.display_flexfield_value(702,'CST_ITEM_COST_DETAILS',cicd.attribute_category,'ATTRIBUTE15',cicd.rowid,cicd.attribute15) item_cost_attribute15,
-- display only - calculated by Item Cost Import
cicd.basis_factor,
cicd.net_yield_or_shrinkage_factor,
cicd.resource_rate,
cicd.item_cost element_unit_cost,
-- Item Cost Summary Amounts
cic.material_cost item_material_cost,
cic.material_overhead_cost item_material_overhead_cost,
cic.resource_cost item_resource_cost,
cic.outside_processing_cost item_outside_processing_cost,
cic.overhead_cost item_overhead_cost,
cic.item_cost,
cic.unburdened_cost item_unburdened_cost,
cic.burden_cost item_burden_cost,
1 level_type,
1 rollup_source_type,
1 process_flag,
:p_upload_mode upload_mode,
:p_do_cost_rollup rollup_costs,
:p_rollup_range rollup_type,
:p_rollup_option rollup_option,
:p_rollup_inc_unimp_ecn_flag rollup_unimpl_ecos,
null upload_row
--
from
cst_cost_types cct_s,
cst_cost_types cct_t,
cst_item_costs cic,
cst_item_cost_details cicd,
cst_activities ca,
bom_resources br,
mtl_system_items_vl msiv,
mtl_parameters mp
where
1=1 and
:p_upload_mode=:p_upload_mode and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
--
cct_s.cost_type=:p_source_cost_type and
cct_t.cost_type=:p_target_cost_type and
--
cct_s.cost_type_id=cic.cost_type_id and
cic.cost_type_id=cicd.cost_type_id(+) and
cic.organization_id=cicd.organization_id(+) and
cic.inventory_item_id=cicd.inventory_item_id(+) and
ca.activity_id(+)=cicd.activity_id and
br.organization_id(+)=cicd.organization_id and
br.resource_id(+)=cicd.resource_id and
cic.organization_id=mp.organization_id and
cic.organization_id=msiv.organization_id and
cic.inventory_item_id=msiv.inventory_item_id and
(cicd.rollup_source_type=1 or cicd.rowid is null)
Parameter NameSQL textValidation
Target Cost Type
 
LOV
Mode
 
LOV Oracle
Auto Populate Upload Columns
 
LOV
Source Cost Type
 
LOV
Rollup Costs?
 
LOV
Rollup Type
 
LOV
Rollup Option
 
LOV
Organization Code
mp.organization_code=:p_organization_code
LOV
Category Set
(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_item_categories mic
where
mcsv.category_set_name=:p_category_set_name and
mcsv.category_set_id=mic.category_set_id
)
LOV
Category
(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_categories_kfv mck,
mtl_item_categories mic
where
mck.concatenated_segments=:p_category and
mcsv.category_set_name=:p_category_set_name and
mcsv.category_set_id=mic.category_set_id and
mcsv.structure_id=mck.structure_id and
mck.category_id=mic.category_id
)
LOV
Item
msiv.concatenated_segments=:p_item_number
LOV
Item Like
upper(msiv.concatenated_segments) like upper(:p_item_like)
Char
Item Description
upper(msiv.description) like upper(:p_item_desc_like)
LOV
Item Type
msiv.item_type=xxen_util.lookup_code(:p_item_type,'ITEM_TYPE',3)
LOV
Excluded Item Statuses
msiv.inventory_item_status_code not in
(select 
 misv2.inventory_item_status_code 
 from 
 mtl_item_status_vl misv2 
 where 
 misv2.inventory_item_status_code_tl = :p_item_status_to_exclude
)
LOV
Make or Buy
msiv.planning_make_buy_code=xxen_util.lookup_code(:p_make_or_buy,'MTL_PLANNING_MAKE_BUY',700)
LOV
Buyer
msiv.buyer_id=:p_buyer_id
LOV
Planner
msiv.planner_code=:p_planner_code
LOV
Inventory Asset?
cic.inventory_asset_flag=1
LOV Oracle
Based on Rollup?
cic.based_on_rollup_flag=1
LOV Oracle
Zero Cost Items?
nvl(cic.item_cost,0)=0
LOV Oracle
Download
Related Blitz Reports
CAC Item Cost & Routing CST Detailed Item Cost CAC Load More4Apps Buy Item Costs CAC Item Cost Break-Out by Activity CAC Material Account Detail CAC ICP PII Material Account Detail CAC Resource Costs CAC Last Standard Item Cost CAC Cost vs. Planning Item Controls
Blitz Report™