CST Detailed Item Cost

Description
Categories: Enginatics, Kcapps
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.
Run CST Detailed Item Cost and other Oracle EBS reports with Blitz Report™ on our demo environment
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