CST Item Standard Cost Upload

➜ Back to Library
Description
Categories: Enginatics, R12 only, Upload
Repository: Github
CST Item Standard Cost Upload
=============================

This upload can be used to
- Upload New Item Costs
- Download the current Item Costs, Update, and Upload the amended item costs for a specified Cost Type
- Download the current item Costs from one Source cost type, update, and upload the amended item costs to a different Target Cost Type

NOTE:
You can only u ...  CST Item Standard Cost Upload
=============================

This upload can be used to
- Upload New Item Costs
- Download the current Item Costs, Update, and Upload the amended item costs for a specified Cost Type
- Download the current item Costs from one Source cost type, update, and upload the amended item costs to a different Target Cost Type

NOTE:
You can only upload costs to cost types that are not flagged as frozen and are flagged as updateable. Typically, this upload is used for updating cost types using the standard costing method.

Optionally, the upload can perform a Cost Rollup after the Item Costs have been imported. The Cost Rollup can be performed for
- Specific Items - a cost rollup will be done for only those items for which costs have been uploaded
- All Items - in this mode the upload will submit the 'Supply Chain Cost Rollup - Print Report' concurrent request to rollup all items within each organization costs are uploaded to

NOTE:
The Item Cost Interface only support importing ‘This Level’ costs. As such, this upload does not support the direct upload of rolled up costs

Parameters
==========
Target Cost Type (Required) - The Cost Type to which the Item Costs are to be uploaded to.

Mode (Required, Default: Remove and replace cost information) -
The Item Cost Update Mode
- Remove and replace cost information
- Insert new Cost Information Only

Auto Populate Upload Columns (Default: Yes) -
Applies to downloaded Item Cost Entries only. If set to Yes, the downloaded records will be flagged ready for upload, even if though no changes have been made.
If left blank, the downloaded records will only be flagged for upload when the user amends any data against a record.
In this case, it is important to remember where an Item Cost has multiple cost elements, then all the cost element records for that item would need to be amended to flag them for update. Only records flagged for update are uploaded. If all cost elements for an item are to be uploaded, even if some do not require any amendment, then the user must trigger the record to be uploaded by making a change against the record.

Source Cost Type
Optionally specify the source Cost Type from which to download the current Item Costs. For the scenarios where you want to amend the current items costs for a Cost Type or want to copy the current item costs for a Cost Type to a different Target Cost Type.

Rollup Costs - set to Yes to do a cost rollup after the costs are uploaded
Rollup Type - Rollup specific Items or All Items
Rollup Option - Single level rollup or Full cost rollup

Organization Code - Optionally select the Organization(s) for which you want to download the Item Costs for
Additional Parameters - The additional parameters are used to optionally restrict the items to be downloaded.

Notes on the Upload
===================
The generated upload Excel contains one record per elemental item cost.

The following columns in the Excel are display only and are populated initially on download, and then refreshed in the results excel after the upload is performed as they calculated during the Item Cost Import

- Basis Factor
- Net Yield or Shrinkage Factor
- Element Unit Cost
- Item Material Cost - the summary Item Level Material Cost.
- Item Material Overhead Cost - the summary Item Level Material Overhead Cost.
- Item Resource Cost - the Summary Item Level Resource Cost.
- Item Outside Processing Cost - the summary Item Level Outside Processing Cost.
- Item Overhead Cost - the summary Item Level Overhead Cost.
- Item Cost - the summary Item Level Item Cost.
- Item Unburdened Cost - the summary Item Level Unburdened Cost.
- Item Burden Cost - the summary Item Level Burden Cost.
   more 
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 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
cdcv.cost_source_type,
cdcv.cost_element,
cdcv.resource_code sub_element,
cdcv.usage_rate_or_amount,
cdcv.basis,
cdcv.activity,
cdcv.activity_units,
cdcv.item_units,
-- other enterable fields
cdcv.operation_seq_num,
cdcv.department,
-- display only - calculated by Item Cost Import
cdcv.basis_factor,
cdcv.net_yield_or_shrinkage_factor,
cdcv.resource_rate,
cdcv.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 item_cost,
cic.unburdened_cost item_unburdened_cost,
cic.burden_cost item_burden_cost,
to_number(null) group_id,
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
--
from
cst_cost_types cct_s,
cst_cost_types cct_t,
cst_item_costs cic,
cst_detail_cost_view cdcv,
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 = cdcv.cost_type_id (+) and
cic.organization_id = cdcv.organization_id (+) and
cic.inventory_item_id = cdcv.inventory_item_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
(nvl(:p_exclude_rolled_up_items,'N')<>'Y' or cic.based_on_rollup_flag<>1) and
(cdcv.cost_source_type =  xxen_util.meaning('1','CST_SOURCE_TYPE',700) or
 (nvl(:p_exclude_no_cost_iterms,'N') != 'Y' and
  not exists
  (select 
   null 
   from 
   cst_detail_cost_view cdcv2 
   where
   cdcv2.cost_type_id = cic.cost_type_id and
   cdcv2.organization_id = cic.organization_id and
   cdcv2.inventory_item_id = cic.inventory_item_id
  )
 )
)
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
Exclude Items with no Cost Details
 
LOV
Exclude Rolled Up Items
 
LOV
Download
Related Blitz Reports
CAC Cost vs. Planning Item Controls CAC Where Used by Cost Type CAC Onhand Lot Value (Real-Time) CAC ICP PII Inventory Pending Cost Adjustment CAC Material Account Summary INV Material Movements CAC OPM Costed Formula CST Detailed Item Cost CAC User-Defined and Rolled Up Costs
Blitz Report™