create or replace view ke019t3.bcd_ict_ar_balance_live as
select ar_code, ar_name,  ar_credit_days, AR_FINAL_LIMIT,
AR_GROUP,max(last_trans) LAST_TRANS_DATE,
nvl(sum(KES_Balance),0) KES_BALANCE,
nvl(sum(USD_Balance),0) USD_Balance,
(case
when (nvl(sum(usd_in_kes),0)+ nvl(sum(KES_Balance),0))=0 then '=0'
when (nvl(sum(usd_in_kes),0)+ nvl(sum(KES_Balance),0))>0 then '>0'
when (nvl(sum(usd_in_kes),0)+ nvl(sum(KES_Balance),0))<0 then '<0'
else '<>0'
end) value_flag,
nvl(sum(usd_in_kes),0) USD_IN_KES,
(nvl(sum(usd_in_kes),0)+ nvl(sum(KES_Balance),0)) con_in_kes
from
(
select ar_code,
Replace(replace(replace(replace(replace(replace(ar_name,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') AR_NAME
, ar_credit_days, AR_FINAL_LIMIT,ar_analysis_6 AR_GROUP,
max(art_doc_date) Last_trans, 
(case
when art_currency='KES' then NVL(sum(art_foreign_balance),0)
end ) KES_Balance,
(case
when art_currency='USD' then NVL(sum(art_foreign_balance),0)
end) USD_Balance,
(case
when art_currency='USD' AND NVL(SUM(art_foreign_balance),0)<>0 then NVL(sum(art_foreign_balance*art_ex_rate),0) ELSE 0
end) USD_in_KES
from
id_ar_master,
id_ar_trans
where art_code=ar_code
--and AR_STATUS<>'B'
group by ar_code, ar_name, ar_credit_days, AR_FINAL_LIMIT, art_currency, ar_analysis_6
)
--having(sum(USD_Balance)+ sum(KES_Balance))<>0 and sum(kes_balance) is not null
--having (nvl((round(sum(usd_in_kes),2)+sum(kes_balance)),0))=1
group by ar_code, ar_name,  ar_credit_days, AR_FINAL_LIMIT,AR_GROUP
order by ar_code

--select * from id_ar_trans
