CST Detailed Item Cost

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Detail report that lists each item and the associated costs to be recognized as part of total unit cost of producing the item.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
gl.name ledger,
mp.organization_code,
msiv.concatenated_segments item,
&category_columns
msiv.description item_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
misv.inventory_item_status_code_tl item_status,
muomv.unit_of_measure_tl primary_uom,
cic.shrinkage_rate shrinkage,
cic.lot_size lot_size,
cicd.operation_seq_num op_seq,
xxen_util.meaning(cicd.level_type,'CST_LEVEL',700) op_level,
xxen_util.meaning(cicd.cost_element_id,'CST_COST_CODE_TYPE',700) cost_element,
br.resource_code sub_element,
br.unit_of_measure uom,
xxen_util.meaning(cicd.basis_type,'CST_BASIS_SHORT',700) basis,
xxen_util.meaning(cicd.rollup_source_type,'CST_SOURCE_TYPE',700) cost_source,
round(cicd.basis_factor,fc.extended_precision) basis_factor,
round(cicd.resource_rate,fc.extended_precision) res_unit_cost,
round(decode(cicd.cost_element_id,3,cicd.usage_rate_or_amount,4,cicd.usage_rate_or_amount,null),fc.extended_precision) resource_usage,
round(decode(cicd.cost_element_id,1,cicd.usage_rate_or_amount,3,null,4,null,cicd.usage_rate_or_amount),fc.extended_precision) matl_or_overhead,
round(cicd.item_cost, fc.extended_precision) unit_cost,
(select ca.activity from cst_activities ca where cicd.activity_id=ca.activity_id) activity,
cicd.item_units,
cicd.activity_units,
round(decode(cicd.cost_element_id,1,cicd.item_cost,0),fc.extended_precision) material_cost,
round(decode(cicd.cost_element_id,2,cicd.item_cost,0),fc.extended_precision) material_overhead_cost,
round(decode(cicd.cost_element_id,3,cicd.item_cost,0),fc.extended_precision) resource_cost,
round(decode(cicd.cost_element_id,4,cicd.item_cost,0),fc.extended_precision) outside_processing,
round(decode(cicd.cost_element_id,5,cicd.item_cost,0),fc.extended_precision) overhead, 
round(sum(cicd.item_cost) over (partition by cicd.organization_id, cicd.inventory_item_id, cicd.cost_type_id), fc.extended_precision) total_unit_cost,
xxen_util.user_name(cicd.created_by) created_by,
xxen_util.client_time(cicd.creation_date) creation_date,
xxen_util.user_name(cicd.last_updated_by) last_updated_by,
xxen_util.client_time(cicd.last_update_date) last_update_date
from
gl_ledgers gl,
fnd_currencies fc,
org_organization_definitions ood,
mtl_parameters mp,
cst_item_costs cic,
cst_cost_types cct,
cst_item_cost_details cicd,
mtl_system_items_vl msiv,
mtl_units_of_measure_vl muomv,
mtl_item_status_vl misv,
bom_resources br
where
1=1 and
cic.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
gl.currency_code=fc.currency_code and
fc.enabled_flag='Y' and
gl.ledger_id=ood.set_of_books_id and
ood.organization_id=mp.organization_id and
mp.cost_organization_id=cic.organization_id and
cic.cost_type_id=cct.cost_type_id and
cic.cost_type_id=cicd.cost_type_id and
cic.inventory_item_id=cicd.inventory_item_id and
cic.organization_id=cicd.organization_id and
cic.inventory_item_id=msiv.inventory_item_id and
ood.organization_id=msiv.organization_id and
msiv.primary_uom_code=muomv.uom_code(+) and
msiv.inventory_item_status_code=misv.inventory_item_status_code(+) and
cicd.resource_id=br.resource_id(+)
order by
ledger,
organization_code,
item,
op_level desc,
cost_element
Parameter Name SQL text Validation
Ledger 
gl.name=:ledger
 LOV
Organization Code 
mp.organization_code=:organization_code
 LOV
Cost Type 
cct.cost_type=:cost_type
 LOV
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and cic.organization_id=mic.organization_id and cic.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and cic.organization_id=mic.organization_id and cic.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substrb(mcsv.category_set_name||' Description',1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and cic.organization_id=mic.organization_id and cic.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and cic.organization_id=mic.organization_id and cic.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substrb(mcsv.category_set_name||' Description',1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
 LOV
Category From 
mck.concatenated_segments>=:category_from
 LOV
Category To 
mck.concatenated_segments<=:category_to
 LOV
Item 
msiv.concatenated_segments=:item
 LOV
Item From 
msiv.concatenated_segments>=:item_from
 LOV
Item To 
msiv.concatenated_segments<=:item_to
 LOV
Download
   
Related Blitz Reports
CAC Item Cost Break-Out by Activity CAC Item Cost & Routing CAC Last Standard Item Cost CAC Calculate ICP PII Item Costs CAC Item Cost Out-of-Balance CAC Calculate Average Item Costs CAC PO Receipt History for Item Costing CAC ICP PII Item Cost Summary CAC New Standard Item Costs
Blitz Report™

Blitz Report™ provides multiple benefits: