CST Layer Inventory Value

select
CCG.cost_group cost_group,
null category,
null item,
MSI.description description,
MSI.primary_uom_code uom,
CIL.inv_layer_id inv_layer_id,
CIL.transaction_source transaction_source_id,
MTST.transaction_source_type_name transaction_source_name,
CIL.layer_quantity layer_quantity,
CIL.layer_cost*:P_EXCHANGE_RATE_CHAR layer_cost,
round(CIL.layer_cost*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, :p_ext_prec) extended_value,
round(sum(decode(CILCD.cost_element_id, 1, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) material_cost,
round(sum(decode(CILCD.cost_element_id, 2, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) material_overhead_cost,
round(sum(decode(CILCD.cost_element_id, 3, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) resource_cost,
round(sum(decode(CILCD.cost_element_id, 4, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) outside_processing_cost,
round(sum(decode(CILCD.cost_element_id, 5, NVL(CILCD.layer_cost, 0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR, 0)),:p_qty_precision) overhead_cost,
round(sum(nvl(CILCD.layer_cost,0)*CIL.layer_quantity*:P_EXCHANGE_RATE_CHAR),:p_qty_precision) total_cost, 
	decode(:P_SORT_OPTION,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),null) CF_ORDER, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT
from
cst_cost_groups CCG,
mtl_system_items_vl MSI,
mtl_item_categories MIC,
mtl_categories MC,
cst_quantity_layers CQL,
cst_inv_layers CIL,
mtl_txn_source_types MTST,
cst_inv_layer_cost_details CILCD
where
CIL.transaction_source_type_id = MTST.transaction_source_type_id AND
CCG.cost_group_id = CQL.cost_group_id AND
CCG.cost_group_id = decode (:P_COST_GROUP_OPTION_ID, 1, CCG.cost_group_id, :P_COST_GROUP_ID) AND
MSI.inventory_item_id(+) = CIL.inventory_item_id AND
MSI.inventory_asset_flag ='Y' AND
MSI.organization_id = :P_ORG_ID AND
MIC.organization_id = MSI.organization_id AND
MIC.inventory_item_id = MSI.inventory_item_id AND
MIC.category_set_id = :P_CATEGORY_SET AND
MC.category_id = MIC.category_id AND
CQL.organization_id = :P_ORG_ID AND
CIL.layer_id = CQL.layer_id AND
CILCD.layer_id = CIL.layer_id AND
CILCD.inv_layer_id = CIL.inv_layer_id AND
&P_ZERO_QTY_WHERE AND
&P_ZERO_COST_WHERE AND
&P_ITEM_WHERE AND
&P_CAT_WHERE
group by
CCG.cost_group, fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), MSI.description, MSI.primary_uom_code, CIL.inv_layer_id, CIL.transaction_source, MTST.transaction_source_type_name, CIL.layer_quantity, CIL.layer_cost, CIL.layer_cost*CIL.layer_quantity
	ORDER BY 1 ASC,ITEM_SEGMENT ASC