--create or replace view bcd_ict_ap_balance_as_on_SAFC as
select 
BRANCH, APT_CODE,SUPP_STATUS,max(LAST_TRANS_DATE) last_trans_date,AP_NAME,sum(KES_BAL) KES_BAL,sum(USD_BAL) USD_BAL
from
(SELECT 'SAFCOM' Branch,APT_CODE,
decode(ap_status,'N','NORMAL','B','BLACKLISTED','OTHER') SUPP_STATUS,
max(apt_doc_date) Last_Trans_date,
Replace(replace(replace(replace(replace(replace(AP_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') AP_NAME,
(case
when apt_currency='KES' then SUM(APT_FOREIGN_AMOUNT)
end) KES_BAL,
(case
when apt_currency='USD' then SUM(APT_FOREIGN_AMOUNT)
end) USD_BAL
FROM KE074T3.ID_AP_TRANS, ke074t3.id_ap_master
WHERE
ap_code=apt_code
and APT_DOC_DATE<'01-jan-2020'
GROUP BY APT_CODE,ap_status,ap_name,apt_currency
)
group by branch,apt_code,SUPP_STATUS,ap_name
having (sum(KES_BAL)+sum(USD_BAL))=0
order by apt_code

update id_ap_master set AP_ANALYSIS_7=
(select APG_GROUP_CODE from id_ap_group_link where apg_code=ap_code and rownum=1)



select * from KE014T3_support.BCD_ICT_CUST_RCT_DETAILS

select * from KE014T3.BCD_ICT_RCT_DETAILS
