CST Cost Type Comparison

select
        decode(:report_sort_opt,
                      1, msi.padded_concatenated_segments ||
                          mc.padded_concatenated_segments ||
                          rpad(msi.primary_uom_code, 3),
                      2,  mc.concatenated_segments || 
                           msi.padded_concatenated_segments ||
                           rpad(msi.primary_uom_code, 3)) sort_order,
       msi.concatenated_segments item_data,
       msi.padded_concatenated_segments padded_item_data,
       mc.concatenated_segments  cat_data,
       mc.padded_concatenated_segments padded_cat_data,
       msi.description item_desc,
       msi.primary_uom_code uom_code,
       &COLUMN1 column1_name,
       round(sum(decode(cdcv.cost_type_id, :cost_type_id1,
                        cdcv.item_cost,0)), :ext_precision_saved) type1,
       round(sum(decode(cdcv.cost_type_id, :cost_type_id2,
                        cdcv.item_cost,0)), :ext_precision_saved) type2,
       round(sum(decode(cdcv.cost_type_id, :cost_type_id2,
                        cdcv.item_cost,0)) -
             sum(decode(cdcv.cost_type_id, :cost_type_id1,
                      cdcv.item_cost,0)), :ext_precision_saved) difference,
       round(decode(sum(decode(cdcv.cost_type_id, :cost_type_id1,
                                cdcv.item_cost,0)), 0,
       (decode(sum(decode(cdcv.cost_type_id, :cost_type_id2,
                                cdcv.item_cost,0)), 0, 0, 100)),
             ((sum(decode(cdcv.cost_type_id, :cost_type_id2,
                                cdcv.item_cost,0)) /
               sum(decode(cdcv.cost_type_id, :cost_type_id1,
                            cdcv.item_cost,0))) - 1) * 100), 2) per_change, 
	BOM_CSTRCTCR_XMLP_PKG.tot_per_changeformula(:tot_item_cost1, :tot_item_cost2, :tot_difference) tot_per_change
from  &P_TABLE_ORDER 
where 
            msi.organization_id = :org_id 
   and   mc.structure_id = :P_STRUCT_NUM  
   and   mic.category_id = mc.category_id
   and   mic.organization_id = :org_id  
   and   mic.category_set_id = :P_CATEGORY_SET
   and   msi.inventory_item_id = mic.inventory_item_id 
   and   cic.inventory_item_id = msi.inventory_item_id
   and   (cic.cost_type_id = :cost_type_id1
   or       (cic.cost_type_id = :cost_type_id2 and exists
                                      (select 'x' from cst_item_costs
                                        where inventory_item_id = cic.inventory_item_id
                                           and  cost_type_id = :cost_type_id1
                                          and   organization_id  = :org_id) )
           )
   and  cic.organization_id = :org_id
   and  cdcv.inventory_item_id = cic.inventory_item_id
   and  cdcv.cost_type_id  =  cic.cost_type_id
   and  cdcv.inventory_item_id = mic.inventory_item_id
   and  msi.inventory_item_id = cdcv.inventory_item_id
   and  cdcv.organization_id = :org_id
   and  &CREATE_WHERE_ITEM1 &P_WHERE_ITEM &CREATE_WHERE_ITEM2
   and  &CREATE_WHERE_CAT1 &P_WHERE_CAT &CREATE_WHERE_CAT2
   --and mc.category_id in (select category_id from mtl_categories MCT where 1 = 1and MCT.structure_id =  101  )
group by msi.concatenated_segments,
                 msi.padded_concatenated_segments,
                 mc.concatenated_segments,
                 mc.padded_concatenated_segments,
                 msi.description,
                 msi.primary_uom_code,
                &COLUMN1
ORDER BY 1 ASC