--create or replace view ke014t3.bcd_ict_ar_balance_as_on_staff as
select
decode((select ar_analysis_2 from id_ar_master where ar_code=cust_code and rownum=1),'IRO','IMELDA','KEN','KENNDY','CK',
'CHRISTOPHER','CMM','JUMA','NOT ASSIGNED')
Credit_Controller,
CUST_CODE, CUST_NAME, CUST_GROUP,USD_BAL,KES_BAL
from
(
select CUST_CODE, CUST_NAME, CUST_GROUP,sum(USD_BAL) USD_BAL,sum(KES_BAL) KES_BAL
from
(
select CUST_CODE, CUST_NAME, CUST_GROUP,
(case
when CCY='USD' then OS_AMT
end) USD_BAL,
(case
when CCY='KES' then OS_AMT
end) KES_BAL
from
(
(select art_code CUST_CODE,ar_name CUST_NAME, ar_analysis_6 CUST_GROUP,art_currency CCY,
sum(art_foreign_balance) OS_AMT
from id_ar_trans,id_ar_master
where 
art_code=ar_code
--and art_doc_date<'01-FEB-2020'
and art_currency='KES'
and ar_analysis_6 in ('STAFF-1','STAFF-2')
group by art_code,ar_name, ar_analysis_6,art_currency,ar_analysis_2
union all
select art_code CUST_CODE,ar_name CUST_NAME, ar_analysis_6 CUST_GROUP,art_currency CCY,
sum(art_foreign_balance)
from id_ar_trans,id_ar_master
where 
art_code=ar_code
--and art_doc_date<'01-FEB-2020'
and art_currency='USD'
and ar_analysis_6  in ('STAFF-1','STAFF-2')
group by art_code,ar_name, ar_analysis_6,art_currency)
)
)
group by CUST_CODE, CUST_NAME, CUST_GROUP
having (sum(USD_BAL)+sum(KES_BAL))<>0)
order by CUST_GROUP,CUST_CODE, CUST_NAME
