CST Inventory Value Report - by Cost Group (1)

SELECT CCG.cost_group CG_QOH_CG
,      CCG.DESCRIPTION CG_DESCRIPTION
,      MSI.description CG_ITEM_DESC
,      MSI.primary_uom_code CG_UOM
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') CG_ITEM_PSEG
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CG_ITEM_SEGMENT
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_cat_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CG_CAT_SEGMENT
,      DECODE(:P_ITEM_REVISION, 1, CIQT.revision, NULL) CG_REVISION
,      MSI.inventory_item_status_code CG_ITEM_STATUS
,      LU3.meaning CG_INV_ASSET
,      LU1.meaning CG_MAKE_BUY
,      SEC.asset_inventory
,      DECODE(MSI.inventory_planning_code,
           6, LU4.meaning, LU2.meaning) CG_PLANNING_METHOD
,      ROUND(sum(CIQT.rollback_qty),:P_QTY_PRECISION) CG_QTY
,      ROUND((NVL(CICT.item_cost,0) * :P_EXCHANGE_RATE), 5) CG_UNIT_COST
,      DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*') CG_DEFAULTED
,      Round(Sum(CIQT.rollback_qty) *
             NVL(CICT.material_cost,0) *
             DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) *
             :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_MATL_COST
,      Round(Sum(CIQT.rollback_qty) *
              NVL(CICT.material_overhead_cost,0) *
              DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) *
              :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_MOVH_COST
,      Round(Sum(CIQT.rollback_qty) *
              NVL(CICT.resource_cost,0) *
              DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) *
             :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_RES_COST
,      Round(Sum(CIQT.rollback_qty) *
              NVL(CICT.outside_processing_cost,0) *
              DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) *
              :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_OSP_COST
,      Round(Sum(CIQT.rollback_qty) *
              NVL(CICT.overhead_cost,0) *
              DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) *
             :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_OVHD_COST
,      Round(sum(CIQT.rollback_qty) *
              NVL(CICT.item_cost,0) *
              DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) *
              :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT  CG_TOTAL_COST
FROM   mtl_secondary_inventories SEC
,      mtl_categories_b MC
,      mtl_system_items_vl MSI
,      cst_cost_groups CCG
,      mfg_lookups LU4
,      mfg_lookups LU3
,      mfg_lookups LU2
,      mfg_lookups LU1
,      cst_inv_qty_temp CIQT
,      cst_inv_cost_temp CICT
,      mtl_parameters MP
WHERE  CCG.cost_group_id = CIQT.cost_group_id
AND    SEC.organization_id (+)= CIQT.organization_id
AND    SEC.secondary_inventory_name (+)= CIQT.subinventory_code
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    (CICT.cost_group_id = CIQT.cost_group_id
     OR MP.primary_cost_method = 1)
AND    MC.category_id = CIQT.category_id
AND    LU1.lookup_type(+) = 'MTL_PLANNING_MAKE_BUY'
AND    LU1.lookup_code(+) = MSI.planning_make_buy_code
AND    LU2.lookup_type = 'MTL_MATERIAL_PLANNING'
AND    LU2.lookup_code = NVL(MSI.inventory_planning_code,6)
AND    LU3.lookup_type = 'SYS_YES_NO'
AND    LU3.lookup_code = CICT.inventory_asset_flag
AND    LU4.lookup_type = 'MRP_PLANNING_CODE'
AND    LU4.lookup_code = NVL(MSI.mrp_planning_code,6)
AND     :P_SORT_OPTION  = 8
GROUP BY  CCG.cost_group
,      MSI.description 
,      MSI.primary_uom_code 
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') 
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') 
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_cat_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') 
,      DECODE(:P_ITEM_REVISION, 1, CIQT.revision, NULL)
,      MSI.inventory_item_status_code
,      LU3.meaning 
,      LU1.meaning 
,      DECODE(MSI.inventory_planning_code,
           6, LU4.meaning, LU2.meaning)
,      DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*')
,      SEC.asset_inventory
,      CCG.DESCRIPTION
,      NVL(CICT.item_cost,0)
,      NVL(CICT.overhead_cost,0)
,      NVL(CICT.outside_processing_cost,0)
,     NVL(CICT.resource_cost,0)
,     NVL(CICT.material_overhead_cost,0)
,     NVL(CICT.material_cost,0)
HAVING Decode(:P_NEG_QTY,1,1,2) =  Decode(:P_NEG_QTY,1,Decode(sign(Sum(CIQT.rollback_qty)),'-1',1,                                                                                     2),2)
       AND  ROUND(sum(NVL(CIQT.rollback_qty,0)),:P_qty_precision) <> 0
order by CCG.cost_group,fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE')