CST Elemental Inventory Value Report - by Cost Group

SELECT null IC_ITEM_NUMBER
,      null IC_CATEGORY
,      MSI.description IC_DESCRIPTION
,      MSI.primary_uom_code IC_UOM
,      CCG.cost_group IC_COSTGROUP
,      decode(:P_SORT_OPTION,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) IC_ORDER_NEW
,      ROUND(CIQT.rollback_qty,:P_qty_precision) IC_QTY
,      DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*') IC_DEFAULTED
,      ROUND(CIQT.rollback_qty *
              DECODE( nvl(SEC.asset_inventory,1) ,1,1,0) *
              NVL(CICT.material_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT)*:ROUND_UNIT IC_MATL_COST
,      ROUND(CIQT.rollback_qty * DECODE( nvl(SEC.asset_inventory,1) ,1,1,0) *
              NVL(CICT.material_overhead_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT)*:ROUND_UNIT IC_MOVH_COST
,      ROUND(CIQT.rollback_qty *
              DECODE( nvl(SEC.asset_inventory,1) ,1,1,0) *
              NVL(CICT.resource_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT)*:ROUND_UNIT IC_RES_COST
,      ROUND(CIQT.rollback_qty *  DECODE( nvl(SEC.asset_inventory,1) ,1,1,0) *
              NVL(CICT.outside_processing_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT)*:ROUND_UNIT IC_OSP_COST
,      ROUND(CIQT.rollback_qty * DECODE( nvl(SEC.asset_inventory,1) ,1,1,0) * NVL(CICT.overhead_cost,0) *
              :P_EXCHANGE_RATE / :ROUND_UNIT)*:ROUND_UNIT IC_OVHD_COST
,      ROUND(CIQT.rollback_qty * DECODE( nvl(SEC.asset_inventory,1) ,1,1,0) *
              NVL(CICT.item_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT)*:ROUND_UNIT IC_TOTAL_COST,
	BOM_CSTREIVW_XMLP_PKG.ic_orderformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, MC.attribute1 || MC.attribute2 || MC.attribute3 || MC.attribute4 || MC.attribute5 || MC.description || MC.segment1, :IC_ITEM_SEGMENT, :IC_CATEGORY_SEGMENT, :IC_ITEM_PSEG, :IC_CAT_PSEG) IC_ORDER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') IC_ITEM_PSEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') IC_ITEM_SEGMENT,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_cat_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') IC_CAT_PSEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') IC_CATEGORY_SEGMENT,
	BOM_CSTREIVW_XMLP_PKG.ic_rowcountformula(:IC_QUANTITY) IC_ROWCOUNT,
	BOM_CSTREIVW_XMLP_PKG.ic_quantity1formula(:IC_QUANTITY) IC_QUANTITY1,
	BOM_CSTREIVW_XMLP_PKG.ic_matl1formula(:IC_QUANTITY, :IC_MATL) IC_MATL1,
	BOM_CSTREIVW_XMLP_PKG.ic_movh1formula(:IC_QUANTITY, :IC_MOVH) IC_MOVH1,
	BOM_CSTREIVW_XMLP_PKG.ic_res1formula(:IC_QUANTITY, :IC_RES) IC_RES1,
	BOM_CSTREIVW_XMLP_PKG.ic_osp1formula(:IC_QUANTITY, :IC_OSP) IC_OSP1,
	BOM_CSTREIVW_XMLP_PKG.ic_ovhd1formula(:IC_QUANTITY, :IC_OVHD) IC_OVHD1,
	BOM_CSTREIVW_XMLP_PKG.ic_total1formula(:IC_QUANTITY, :IC_TOTAL) IC_TOTAL1
FROM   mtl_categories_b MC,
       mtl_system_items_vl MSI,
       cst_cost_groups CCG,
       mtl_secondary_inventories SEC,
       cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT,
       mtl_parameters MP
WHERE  SEC.organization_id (+) = CIQT.organization_id
AND    SEC.secondary_inventory_name (+) = CIQT.subinventory_code
AND    CCG.cost_group_id = CIQT.cost_group_id
AND    MSI.organization_id = CIQT.organization_id
AND    MSI.inventory_item_id = CIQT.inventory_item_id
AND    MP.organization_id = CIQT.organization_id
AND    CICT.organization_id = CIQT.organization_id
AND    CICT.inventory_item_id = CIQT.inventory_item_id
AND    (MP.primary_cost_method = 1
      OR CICT.cost_group_id = CIQT.cost_group_id)
AND    MC.category_id = CIQT.category_id
ORDER BY 6, fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ,fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') ,3 ,4 ,8 ,5