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')