CST Item Average Cost Upload
Description
CST Item Average Cost Upload updates item average costs for inventory organizations that use average costing, from Excel – at the whole-item (summary) level or for individual cost elements. It records standard average cost update transactions and revalues your on-hand inventory accordingly. It is the spreadsheet alternative to the Average Cost Update form.
When to use it
- Mass-revalue inventory by setting new average costs for many items at once.
- Apply a percentage cost change across a set of items.
- Make a value (revaluation) adjustment to on-hand inventory by a target amount.
- Update a specific elemental cost (for example only Material or only Overhead) using the Detail template.
- Download current costs, review the adjusted figures in Excel, and upload the changes.
Before you start
- Blitz Report is installed and you are signed in to a responsibility with access to the inventory organization.
- The organization uses the average costing method, and the items are costed inventory-asset items.
- A valid adjustment account is available, and the transaction date is in an open period.
Choose a template
| Template | Use it for |
|---|---|
| Detail – Elemental Costs Template (default) | Updating an item’s individual cost elements; the item’s total cost is recalculated from the elements. |
| Summary – Item Costs Template | Updating an item’s total cost; the change is apportioned across the existing cost elements. |
Step 1 – Choose a template, set the parameters and download
In Blitz Report, open CST Item Average Cost Upload, choose your template, and set the parameters:
| Parameter | Purpose |
|---|---|
| Upload Mode | Create (empty template) or Create, Update (download current costs for editing). |
| Organization Code | The average-costing inventory organization (limited to those you can access). |
| Transaction Date | The effective date of the cost update. |
| Default Adjustment Account | The GL account used for the inventory revaluation (can be overridden per cost element). |
| Item / Category filters | Restrict which items are downloaded. |
Run the upload to download and open the Excel file.
Step 2 – Enter the cost change
On each row, enter exactly one of New Average Cost, Percentage Change or Value Change. A value change requires on-hand quantity and cannot drive the inventory value negative. In the Detail template the Cost Element Level and Cost Element identify which element you are changing; in the Summary template they are left blank. You can override the adjustment account per cost element and add a Reason and Reference.
Step 3 – Validate and Save
Click Validate and Save. This checks for missing required values and runs the upload’s validation (for example, that exactly one of the three change columns is entered), 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 records the average cost update transactions and revalues inventory. When it finishes, a result report opens showing each row as success or error.
What’s produced
- Average cost update transactions that revalue the items and their on-hand inventory.
- A result report listing every row with a status (success or error) and a message.
Common questions
Summary or elemental – which template?
Use Summary to change the whole item cost (the change is apportioned across the existing elements); use Detail to change specific cost elements, with the total cost recalculated from the elements.
Can I enter more than one change column on a row?
No – enter exactly one of New Average Cost, Percentage Change or Value Change.
Why is my value change rejected?
A value change requires on-hand quantity and cannot drive the inventory value negative.
Do I have to fill the adjustment accounts in the sheet?
No – leave them blank to use the Default Adjustment Account from the parameters; fill them only to override per cost element.
Does this work for standard-cost organizations?
No. Only average-costing organizations are supported.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| Specify either a New Average Cost, Percentage Change or Value Change | None, or more than one, of the three change columns was entered. | Enter exactly one of the three. |
| Cost Element columns must match the template | Cost Element Level / Cost Element entered on a Summary row, or left blank on a Detail row. | Leave them blank for Summary; populate both for Detail. |
| A Default or per-cost-element Adjustment Account must be specified | No adjustment account available for a cost element being updated. | Set the Default Adjustment Account parameter or the per-cost-element account. |
| Value change needs on-hand quantity / cannot go negative | A value change with no on-hand, or one that would make inventory value negative. | Use New Average Cost or Percentage Change, or adjust the value-change amount. |
| This Organization / Item / Cost Group combination has already been entered | The same combination appears on more than one row. | Consolidate to a single row per combination. |
select null action_, null status_, null message_, null modified_columns_, :p_upload_type upload_type, to_number(null) source_line_id, to_number(null) source_header_id, :p_trx_type transaction_type, :p_trx_source source, x.* from ( select mp.organization_code organization, msiv.concatenated_segments item, ccg.cost_group cost_group, :p_trx_date transaction_date, msiv.description description, cql.item_cost item_average_cost, -- clcdv.level_type_dsp cost_element_level, clcdv.cost_element, -- to_number(null) new_average_cost, to_number(null) percentage_change, to_number(null) value_change, -- cql.layer_quantity valued_quantity, decode(:p_upload_type,'D',clcdv.item_cost,cql.item_cost) old_average_cost, round(cql.layer_quantity * decode(:p_upload_type,'D',clcdv.item_cost,cql.item_cost),2) old_valuation, -- to_number(null) adjusted_average_cost, to_number(null) adjusted_valuation, to_number(null) valuation_change, -- null material_adj_account, null material_overhead_adj_account, null resource_adj_account, null outside_processing_adj_account, null overhead_adj_account, :p_default_adj_account default_adj_account, -- null reason, null reference, -- clcdv.cost_element_id, clcdv.level_type, 0 upload_row from mtl_parameters mp, mtl_system_items_vl msiv, cst_quantity_layers cql, cst_layer_cost_details_v clcdv, cst_cost_groups ccg where mp.organization_id = msiv.organization_id 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 mp.primary_cost_method = 2 and msiv.organization_id = cql.organization_id and msiv.inventory_item_id = cql.inventory_item_id and msiv.inventory_item_flag = 'Y' and msiv.inventory_asset_flag = 'Y' and msiv.costing_enabled_flag = 'Y' and decode(:p_upload_type,'D',cql.layer_id,null) = clcdv.layer_id(+) and (:p_upload_type = 'S' or clcdv.cost_element_id is not null) and cql.cost_group_id = ccg.cost_group_id and ( (ccg.cost_group_id = mp.default_cost_group_id) or ( nvl(ccg.cost_group_type,1) in (1,3) and exists (select 'EXISTS' from cst_cost_group_accounts ccga where ccga.cost_group_id = ccg.cost_group_id and ccga.organization_id = mp.organization_id ) ) ) and 1=1 union -- for new items with no costing layers take from the cst_item_costs/cst_item_cost_details select mp.organization_code organization, msiv.concatenated_segments item, ccg.cost_group cost_group, :p_trx_date transaction_date, msiv.description description, cic.item_cost item_average_cost, -- cicdv.level_type_dsp cost_element_level, cicdv.cost_element, -- to_number(null) new_average_cost, to_number(null) percentage_change, to_number(null) value_change, -- 0 valued_quantity, decode(:p_upload_type,'D',cicdv.item_cost,cic.item_cost) old_average_cost, 0 old_valuation, -- to_number(null) adjusted_average_cost, to_number(null) adjusted_valuation, to_number(null) valuation_change, -- null material_adj_account, null material_overhead_adj_account, null resource_adj_account, null outside_processing_adj_account, null overhead_adj_account, null default_adj_account, -- null reason, null reference, -- cicdv.cost_element_id, cicdv.level_type, 0 upload_row from mtl_parameters mp, mtl_system_items_vl msiv, cst_item_costs cic, cst_item_cost_details_v cicdv, cst_cost_groups ccg where mp.organization_id = msiv.organization_id 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 mp.primary_cost_method = 2 and msiv.organization_id = cic.organization_id and msiv.inventory_item_id = cic.inventory_item_id and msiv.inventory_item_flag = 'Y' and msiv.inventory_asset_flag = 'Y' and msiv.costing_enabled_flag = 'Y' and not exists (select null from cst_quantity_layers cql where cql.organization_id = msiv.organization_id and cql.inventory_item_id = msiv.inventory_item_id) and mp.primary_cost_method = cic.cost_type_id and decode(:p_upload_type,'D',cic.organization_id,null) = cicdv.organization_id(+) and decode(:p_upload_type,'D',cic.inventory_item_id,null) = cicdv.inventory_item_id(+) and decode(:p_upload_type,'D',cic.cost_type_id,null) = cicdv.cost_type_id(+) and (:p_upload_type = 'S' or cicdv.cost_element_id is not null) and mp.default_cost_group_id = ccg.cost_group_id and 1=1 ) x |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Upload Mode |
| LOV | |
| Upload Type | LOV | ||
| Transaction Type | LOV | ||
| Transaction Date | DateTime | ||
| Transaction Source Name | Char | ||
| Default Adjustment Account Alias |
| LOV | |
| Default Adjustment Account | LOV | ||
| Organization Code |
| LOV | |
| Cost Group |
| LOV | |
| Category Set |
| LOV | |
| Category |
| LOV | |
| Item |
| LOV | |
| Item Like |
| Char | |
| Item Description |
| LOV | |
| Item Type |
| LOV | |
| Excluded Item Statuses |
| LOV | |
| Make or Buy |
| LOV | |
| Buyer |
| LOV | |
| Planner |
| LOV |