create or replace PROCEDURE                 ke014t3.bcd_ict_data_build_AR_BAL_LIST (as_on_year in Number,as_on_month in Number) IS

CC varchar2(60);
CUST_CODE varchar2(15);
CUST_NAME varchar2(150);
CUST_GROUP varchar2(15);
USD_BAL number;
KES_BAL number;
as_on_date Date;

CURSOR data_build_for_Block IS
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')
CC,
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<as_on_date
and art_currency='KES'
and ar_analysis_6 not in ('STAFF-1','STAFF-2','INTCOM','SUBS')
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<as_on_date
and art_currency='USD'
and ar_analysis_6 not in ('STAFF-1','STAFF-2','INTCOM','SUBS')
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))<>10)
order by CUST_GROUP,CUST_CODE, CUST_NAME;


dbfb data_build_for_Block%ROWTYPE;

BEGIN


    as_on_date:=last_day(to_date(to_char(as_on_year,'9999')||to_char(as_on_month,'09')||'01','yyyy-mm-dd'));
    delete from KE014T3_SUPPORT.bcd_ict_ar_as_on_date_List;

    open data_build_for_Block;

    loop
    fetch data_build_for_Block into dbfb;
    exit when data_build_for_Block%NOTFOUND;

    insert into ke014t3_support.bcd_ict_ar_as_on_date_List values

    (

      dbfb.CC ,
      dbfb.CUST_CODE ,
      dbfb.CUST_NAME ,
      dbfb.CUST_GROUP ,
      dbfb.USD_BAL ,
      dbfb.KES_BAL 

    );

    commit;
    end loop;
    close data_build_for_Block;

END;
