CST Detailed Item Cost

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,
--
round(cic.material_cost,fc.extended_precision) item_material_cost,
round(cic.material_overhead_cost,fc.extended_precision) item_material_overhead_cost,
round(cic.resource_cost,fc.extended_precision) item_resource_cost,
round(cic.outside_processing_cost,fc.extended_precision) item_outside_processing,
round(cic.overhead_cost,fc.extended_precision) item_overhead,
round(cic.item_cost,fc.extended_precision) item_cost,
--
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) detail_material_cost,
round(decode(cicd.cost_element_id,2,cicd.item_cost,0),fc.extended_precision) detail_material_overhead_cost,
round(decode(cicd.cost_element_id,3,cicd.item_cost,0),fc.extended_precision) detail_resource_cost,
round(decode(cicd.cost_element_id,4,cicd.item_cost,0),fc.extended_precision) detail_outside_processing,
round(decode(cicd.cost_element_id,5,cicd.item_cost,0),fc.extended_precision) detail_overhead,
round(sum(cicd.item_cost) over (partition by cicd.organization_id, cicd.inventory_item_id, cicd.cost_type_id), fc.extended_precision) detail_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(+) and
(cicd.cost_type_id is not null or
 (nvl(:p_exclude_no_cost_iterms,'N') != 'Y' and
  not exists
  (select
   null
   from
   cst_item_cost_details cicd2
   where
   cicd2.cost_type_id = cic.cost_type_id and
   cicd2.organization_id = cic.organization_id and
   cicd2.inventory_item_id = cic.inventory_item_id
  )
 )
)
order by
ledger,
organization_code,
item,
op_level desc,
cost_element