sum charge and cost

select to_char(sum(decode(currency,
                  'USD', amount * 1.0000,
                  'THB', amount * 0.029403,
                  'HKD', amount * 0.129017,
                  'PHP', amount * 0.020661,
                  'IDR', amount * 0.0001,
                  'VND', amount * 0.000056,
                  'KRW', amount * 0.000801,
                  'INR', amount * 0.02045,
                  'EUR', amount * 1.42400,
                  'AUD', amount * 0.8278,
                  'AED', amount * 0.272257,
                  'MYR', amount * 0.283768,
                  'CNY', amount * 0.146394,
                  'TWD', amount * 0.030321,
                  'JPY', amount * 0.01062,
                  'SGD', amount * 0.691994,
                  'NZD', amount * 0.681,
                  'CLP', amount * 0.001826,
                  'GBP', amount * 1.6237 )), '999,999,999.99') "Amount(USD)",to_char(nvl(dsi.rev_recog_date,nvl(dsi.completion_date, dsi.last_update_date)), 'mm')
  from dp_ship_charge dsc, dp_ship_instruction dsi
 where dsc.status <> 'N'
   and dsi.oid = dsc.svo_oid
   and to_char(nvl(dsi.rev_recog_date,nvl(dsi.completion_date, dsi.last_update_date)), 'yyyy')='2009'
   group by to_char(nvl(dsi.rev_recog_date,nvl(dsi.completion_date, dsi.last_update_date)), 'mm') order by  to_char(nvl(dsi.rev_recog_date,nvl(dsi.completion_date, dsi.last_update_date)), 'mm')

 

 

 

 

 select to_char(sum(decode(currency_code,
                  'USD', unit_cost * 1.0000,
                  'THB', unit_cost * 0.029403,
                  'HKD', unit_cost * 0.129017,
                  'PHP', unit_cost * 0.020661,
                  'IDR', unit_cost * 0.0001,
                  'VND', unit_cost * 0.000056,
                  'KRW', unit_cost * 0.000801,
                  'INR', unit_cost * 0.02045,
                  'EUR', unit_cost * 1.42400,
                  'AUD', unit_cost * 0.8278,
                  'AED', unit_cost * 0.272257,
                  'MYR', unit_cost * 0.283768,
                  'CNY', unit_cost * 0.146394,
                  'TWD', unit_cost * 0.030321,
                  'JPY', unit_cost * 0.01062,
                  'SGD', unit_cost * 0.691994,
                  'NZD', unit_cost * 0.681,
                  'CLP', unit_cost * 0.001826,
                  'GBP', unit_cost * 1.6237 )), '999,999,999.99') "Cost Amount(USD)",to_char(nvl(dsi.COST_RECO_DATE,nvl(dsi.completion_date, dsi.last_update_time)), 'mm')
  from dp_jo_cost dsc, dp_jo_header dsi
 where dsc.status <> 'N'
   and dsi.oid = dsc.jo_oid
   and to_char(nvl(dsi.COST_RECO_DATE,nvl(dsi.completion_date, dsi.last_update_time)), 'yyyy')='2009'
   group by to_char(nvl(dsi.COST_RECO_DATE,nvl(dsi.completion_date, dsi.last_update_time)), 'mm') order by  to_char(nvl(dsi.COST_RECO_DATE,nvl(dsi.completion_date, dsi.last_update_time)), 'mm')