--create or replace view ke019t3.bcd_ict_AR_collection_CMTH_sum as
select nvl(round(sum(COL_USD_VALUE),0),0) COL_USD, nvl(round(sum(COL_KES_VALUE),0),0) COL_KES from
(
select extract(year from b.rh_doc_date) year, extract(month from b.rh_doc_date) Month, 
(case
when ALOC_CURRENCY='USD' then sum(a.ALOC_FOREIGN_AMOUNT)
end) COL_USD_VALUE,
(case
when ALOC_CURRENCY='KES' then sum(a.ALOC_FOREIGN_AMOUNT)
end) COL_KES_VALUE
from
ke019t3.id_receipt_allocation_details a,
ke019t3.id_receipt_header b
where
b.RH_DOC_NUMBER=a.aloc_doc_number
and a.ALOC_ALLOCATION_TYPE='AR'
and extract(year from sysdate)=extract(year from b.RH_DOC_DATE)
and  extract(month from sysdate)=extract(month from b.RH_DOC_DATE)
group by extract(year from b.rh_doc_date),extract(month from b.rh_doc_date),a.aloc_currency)
group by extract(year from sysdate),extract(month from sysdate)

