PROCEDURE         bcd_ict_data_bld_AR_BAL_LIST_2 (Audit_batch_no in number, as_on_date in Date, Created_user in varchar2) IS

CC varchar2(30);
CUST_CODE varchar2(15);
CUST_NAME varchar2(150);
CUST_GROUP varchar2(15);
var_as_on_date Date;
var_Audit_batch_no number;
var_Created_user varchar2(15);
CCY Varchar2(3);
ACT_BAL number;
DOC_CNT number;
LT16 number;
age16to30 number;
age31to60 number;
age61to90  number;
age91to180 number;
gt180 number;
gt3yrs number;
gt5yrs number;


CURSOR data_build_for_Block IS
select CC,CUST_CODE,CUST_NAME,CUST_GROUP, CCY,
round(ACT_BAL,2) ACT_BAL, DOC_CNT,
round(LT16,2) LT16,round( AGE16TO30,2) AGE16TO30,round( AGE31TO60,2) AGE31TO60,round( AGE61TO90,2) AGE61TO90
,round( AGE91TO180,2) AGE91TO180,round( GT180,2) GT180,round( GT3YRS,2) GT3YRS,round( GT5YRS,2) GT5YRS
from (
                select CC,CUST_CODE,CUST_NAME,CUST_GROUP,'KES' CCY,
                sum(ACT_BAL) ACT_BAL,count(AGED) DOC_CNT,sum(LT16) LT16,sum(AGE16TO30) AGE16TO30,
                sum(AGE31TO60) AGE31TO60,sum(AGE61TO90) AGE61TO90,sum(AGE91TO180) AGE91TO180,sum(GT180) GT180, sum(GT3YRS) GT3YRS, sum(GT5YRS) GT5YRS
                from
                -- Starting KES Transaction Selection Script
                (
                  select ar_analysis_2 CC,art_code CUST_CODE,
                  art_doc_date,
                  Replace(replace(replace(replace(replace(replace(ar_name,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') CUST_NAME,
                  ar_analysis_6 CUST_GROUP,art_currency CCY,
                  nvl(art_foreign_balance,0) ACT_BAL,
                  round((to_date(as_on_date)-art_doc_date),0) AGED,
                  -- Bringing Ageing


                         (case
                              When round((to_date(as_on_date)-art_doc_date),0)<16 then nvl(art_foreign_balance,0) else 0
                         End) LT16,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 16 and 30
                              then nvl(art_foreign_balance,0) else 0
                         End) age16to30,

                          (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 31 and 60
                              then nvl(art_foreign_balance,0) else 0
                         End) age31to60,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 61 and 90
                              then nvl(art_foreign_balance,0) else 0
                         End) age61to90,

                          (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 91 and 180
                              then nvl(art_foreign_balance,0) else 0
                         End) age91to180,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 181 and 1095
                              then nvl(art_foreign_balance,0)
                         End) GT180,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 1096 and 1825
                              then nvl(art_foreign_balance,0)
                         End) GT3YRS,


                        (case
                              When round((to_date(as_on_date)-art_doc_date),0)>1825 then nvl(art_foreign_balance,0)
                         End) GT5YRS


                  -- End of Ageing

                  from id_ar_trans,id_ar_master
                  where

                  (art_flex_2 not like('%EX~DIFF%')
                  or art_flex_2 is null)

                  and art_code=ar_code
                  and art_currency='KES'
                  and ar_analysis_6 not in ('STAFF-1','STAFF-2','INTCOM','SUBS')
                  and art_foreign_balance not in (0)
                  and art_doc_date<=as_on_date
                 ) -- end of transation selection script for kes

                group by
                CC,CUST_CODE,CUST_NAME,CUST_GROUP

UNION ALL

                select CC,CUST_CODE,CUST_NAME,CUST_GROUP,'USD' CCY,
                sum(ACT_BAL) ACT_BAL,count(AGED) DOC_CNT,sum(LT16) LT16,sum(AGE16TO30) AGE16TO30,
                sum(AGE31TO60) AGE31TO60,sum(AGE61TO90) AGE61TO90,sum(AGE91TO180) AGE91TO180,sum(GT180) GT180, sum(gt3YRS) GT3YRS, sum(GT5YRS) GT5YRS
                from
                -- Starting KES Transaction Selection Script
                (
                  select ar_analysis_2 CC,art_code CUST_CODE,
                  art_doc_date,
                  Replace(replace(replace(replace(replace(replace(ar_name,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') CUST_NAME,
                  ar_analysis_6 CUST_GROUP,art_currency CCY,
                  nvl(art_foreign_balance,0) ACT_BAL,
                  round((to_date(as_on_date)-art_doc_date),0) AGED,
                  -- Bringing Ageing


                         (case
                              When round((to_date(as_on_date)-art_doc_date),0)<16 then nvl(art_foreign_balance,0) else 0
                         End) LT16,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 16 and 30
                              then nvl(art_foreign_balance,0) else 0
                         End) age16to30,

                          (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 31 and 60
                              then nvl(art_foreign_balance,0) else 0
                         End) age31to60,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 61 and 90
                              then nvl(art_foreign_balance,0) else 0
                         End) age61to90,

                          (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 91 and 180
                              then nvl(art_foreign_balance,0) else 0
                         End) age91to180,

                        (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 181 and 1095
                              then nvl(art_foreign_balance,0)
                         End) GT180,

                         (case
                              When round((to_date(as_on_date)-art_doc_date),0) between 1096 and 1825
                              then nvl(art_foreign_balance,0)
                         End) GT3YRS,

                        (case
                              When round((to_date(as_on_date)-art_doc_date),0)>1825 then nvl(art_foreign_balance,0)
                         End) GT5YRS


                  -- End of Ageing

                  from id_ar_trans,id_ar_master
                  where

                  art_code=ar_code
                  and art_currency='USD'
                  and ar_analysis_6 not in ('STAFF-1','STAFF-2','INTCOM','SUBS')
                  and art_foreign_balance not in (0)
                  and art_doc_date<=as_on_date
                 ) -- end of transation selection script for kes

                group by
                CC,CUST_CODE,CUST_NAME,CUST_GROUP
)
order by cust_code, ccy;
                



dbfb data_build_for_Block%ROWTYPE;

BEGIN

update id_ar_trans set art_detail_10=1 where art_flex_2='EX~DIFF' and art_detail_10<>1;
commit;

var_as_on_date:=as_on_date;
var_Audit_batch_no:=audit_batch_no;
var_Created_user:=Created_user;

    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_new values

    (

      dbfb.CC ,
      dbfb.CUST_CODE ,
      dbfb.CUST_NAME ,
      dbfb.CUST_GROUP ,
      var_as_on_date,
      var_Audit_batch_no,
      var_Created_user,
      dbfb.CCY,
      dbfb.ACT_BAL,
      dbfb.DOC_CNT,
      dbfb.LT16,
      dbfb.age16to30,
      dbfb.age31to60,
      dbfb.age61to90,
      dbfb.age91to180,
      dbfb.gt180,
      dbfb.gt3yrs,
      dbfb.gt5yrs
    );

    commit;
    end loop;
    close data_build_for_Block;

END;
