--create or replace view bcd_ict_sales_refund_CMTH as
select art_code, art_year, art_period, sum(usd_sales) usd_sales, sum(usd_refunds) usd_refunds, sum(kes_sales) kes_sales, sum(kes_refunds) kes_refunds from
(
select
art_code,art_year, art_period,
(case
when ART_CURRENCY='USD' and art_type not in ('TCN','RTCN') then sum(art_foreign_amount)
end) USD_SALES,
(case
when ART_CURRENCY='USD' and art_type in ('TCN','RTCN') then sum(art_foreign_amount)
end) USD_REFUNDS,
(case
when ART_CURRENCY='KES' and art_type not in ('TCN','RTCN') then sum(art_foreign_amount)
end) KES_SALES,
(case
when ART_CURRENCY='KES' and art_type in ('TCN','RTCN') then sum(art_foreign_amount)
end) KES_REFUNDS
from
ke019t3.id_ar_trans
where art_type in ('RIN','RRIN','TIN','RTIN','TCN','RTCN')
and art_period-1=
(case
when extract(month from art_doc_date)=1 then 12 else extract(month from sysdate)
end)
and art_year=
(case
when extract(month from art_doc_date)=1 then extract(year from sysdate)-1 else extract(year from sysdate)
end)

group by 
art_code,art_year, art_period,art_currency, art_type
)
group by art_code,art_year,art_period

