assssssssssss

create or replace package body PKG_BANK_RECON as

     /**准备对账数据
        bankCode：银行编号,为空表示获取全部的对账数据
       tradeDate：交易日期yyyymmdd，不能为空
       auto: 0 自动，1手动
       */
     procedure PROC_PREPAR_KM_DATA(bankCode varchar2, tradeDate VARCHAR2, auto VARCHAR2) as
               v_step number(1) := 0;
               v_delete_sql_1 varchar2(2000);
               v_insert_normal_sql varchar2(4000);
               --v_insert_allot_sql_1 varchar2(2000);
               --v_insert_allot_sql_2 varchar2(2000);

     begin
          /**
             准备对账数据
             第一步：清理相关日期的对账数据，便于重复对账使用
             第二步：
             1、根据交易日期及交易方式，获取paymentType表中的支付数据放入待对账临时表
             2、获取头寸记录参与对账
             第三步：
             获取对账差错但未调账的进行补充对账
          */
          /**
             第一步
          */
          v_step := 1;
         PROC_RECON_LOG(auto||'准备'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_KM_DATA','清除对账临时数据开始','INFO');

         v_delete_sql_1 := 'delete from t_kmp_bank_recon_temp';
         if bankCode is not null THEN
            v_delete_sql_1 := v_delete_sql_1||' where channel_code='''||bankCode||'''';
         end if;

         execute immediate v_delete_sql_1;

         commit;

         PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_KM_DATA','清除美对账临时数据结束','INFO');
         /**
            第二步

            TRADE("交易"), IN

            WITHDRAW("提现"), OUT

             RECHARGE("充值"),INT

             BTRANSFER("转账到银行卡"),OUT

             DFPAY("代付"),OUT

              ALLOT("调拨")IN

              AUTHEN("认证"),OUT

         */
         v_step := 2;
         PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_KM_DATA','准备美对账临时数据开始','INFO');

         v_insert_normal_sql := 'insert into t_kmp_bank_recon_temp
                 select t.paymenttype_id,
                 t.pay_channel_type,
                 t.pay_channel_name,
                   t.channel_fee,
                   t.business_type,
                   t.bank_code,
                   t.bank_name,
                   t.acc_name,
                   t.card_no,
                   t.out_trade_no,
                   t.paymenttype_id,
                   t.amount,
                   0,
                   t.create_time,
                   null,
                   null,
                   0,
                   0,
                   decode(t.business_type,'||'''WITHDRAW'''||','||'''OUT'''||','||'''REFUND'''||','||'''OUT'''||','||'''BANKTRANS'''||','||'''OUT'''||','||'''DFPAY'''||','||'''OUT'''||','||'''ALLOT'''||','||'''OUT'''||','||'''AUTHEN'''||','||'''OUT'''||','||'''IN'''||'),
                   0,
                   t.buss_interface_type,
                   t.gateway_type
         from t_payment_type t
         where t.pay_channel_type is not null and t.status = '||'''SUCCESS'''||' and create_time >= to_date('''||tradeDate||''','||'''yyyymmdd'''||') and create_time < to_date('''||tradeDate||''','||'''yyyymmdd'''||')+1 ';

         if auto = '0' then
            v_insert_normal_sql := v_insert_normal_sql || ' and ( t.is_recon = 0 or t.is_recon is null)';
         end if;

         if bankCode is not null then
            v_insert_normal_sql := v_insert_normal_sql ||' and t.pay_channel_type='''||bankCode||'''';
         end if;
         /**准备普通交易的数据*/
         --dbms_output.put_line(v_insert_normal_sql);
         execute immediate v_insert_normal_sql;

        commit;

         PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_KM_DATA','准备美对账临时数据结束','INFO');

         /**
            第三步
         */
         v_step := 3;
         PROC_RECON_LOG(auto||'回收历史对账失败数据','PROC_PREPAR_KM_DATA','掉单数据补充开始','INFO');
         v_insert_normal_sql :='insert into t_kmp_bank_recon_temp
                                           select
                                            substr(id,0,instr(id,'||'''_'''||',1,1)-1) id,
                                            channel_code,
                                            channel_name,
                                            channel_fee,
                                            business_type,
                                            bank_code,
                                            bank_name,
                                            name,
                                            card_no,
                                            out_trade_no,
                                            order_no,
                                            trade_amt,
                                            0,
                                            trade_time,
                                            null,
                                            null,
                                            0,
                                            0,
                                            in_out_type,
                                            1,
                                            buss_interface_type,
                                            trade_type
                                            from t_bank_recon_diff t
                                           where (t.tz_state is null or t.tz_state in ('||'''FORTZ'''||','||'''GRFREEZEN'''||','||'''GRUNFREEZEN'''||'))
                                           and t.recon_result = '||'''LACK'''||'
                       AND  t.TRADE_TIME <= to_date('''||tradeDate||''','||'''yyyymmdd'''||') ';

         if bankCode is not null then
            v_insert_normal_sql := v_insert_normal_sql ||' and t.channel_code='''||bankCode||'''';
         end if;

         execute immediate v_insert_normal_sql;

         COMMIT;

          /* 清除重复对账的重复数据*/
          delete from t_kmp_bank_recon_temp
          where is_repeat != 1 and id in (
          select id
           from
          (select
          id,
          row_number() over (partition by id  order by is_repeat desc )  as rn
          from t_kmp_bank_recon_temp
          where channel_code = bankCode
                    and is_repeat = 1
                    and to_char(trade_time,'yyyymmdd') = tradeDate )
          where rn = 1
          );

          commit;

         PROC_RECON_LOG(auto||'回收历史对账失败数据','PROC_PREPAR_KM_DATA','掉单数据补充结束','INFO');

     exception
     when others then
            if  v_step = 1   then
            PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_KM_DATA','清除对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            ELSIF  v_step = 2   then
            PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_KM_DATA','准备美对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            ELSIF  v_step = 3   then
            PROC_RECON_LOG(auto||'回收历史对账失败数据','PROC_PREPAR_KM_DATA','收回掉单数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            end if;

     end ;

     /**准备银行对账数据
        bankCode：银行编号,为空表示获取全部的对账数据
       tradeDate：交易日期yyyymmdd，不能为空
       */
     procedure PROC_PREPAR_BANK_DATA(bankCode varchar2, tradeDate VARCHAR2, auto VARCHAR2) AS
               v_step number(1) := 0;
               v_delete_sql_1 varchar2(1000);
               v_insert_normal_sql varchar2(4000);
     BEGIN


        /**
           第一步 清理临时数据
        */
         v_step := 1;

         PROC_RECON_LOG(auto||'准备银行'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_BANK_DATA','清除银行对账临时数据开始','INFO');

         v_delete_sql_1 := 'delete from t_bank_settle_data_temp';
         if bankCode is not null then
            v_delete_sql_1 := v_delete_sql_1||' where channel_code='''||bankCode||'''';
         end if;

         EXECUTE IMMEDIATE v_delete_sql_1;

         COMMIT;
         PROC_RECON_LOG(auto||'准备银行'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_BANK_DATA','清除银行对账临时数据结束','INFO');

         /**
             第二步 准备数据
         */
          v_step := 2;
         PROC_RECON_LOG(auto||'准备银行'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_BANK_DATA','获取银行对账临时数据开始','INFO');
         /**准备银行对账数据*/
         v_insert_normal_sql := 'insert into t_bank_settle_data_temp
                             select
                                t.id,
                                  t.bank_code ,
                                  t.settle_date ,
                                  t.order_no ,
                                  t.out_trade_no,
                                  t.old_orderno,
                                  t.trade_type,
                                  t.order_amount,
                                  t.order_time,
                                  t.trade_fee,
                                  t.period_fee,
                                  t.refund_no ,
                                  t.out_refund_no,
                                  t.refund_amount,
                                  t.refund_fee ,
                                  t.settle_time ,
                                  t.create_time,
                                  t.create_user,
                                  t.in_out_type,
                                  0,
                                  t.buss_interface_type,
                                  t.channel_code,
                                  t.channel_name,
                                  t.bank_name,
                                  t.name,
                                  t.card_no
                                  from t_bank_settle_data t
                                  where t.buss_interface_type != ''REEXCHANGE''
                                  and t.settle_date = '''||tradeDate||'''';

         if bankCode is not null then
            v_insert_normal_sql := v_insert_normal_sql ||' and t.channel_code='''||bankCode||'''';
         end if;

         execute immediate v_insert_normal_sql;

         commit;
         PROC_RECON_LOG(auto||'准备银行'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_BANK_DATA','获取银行对账临时数据结束','INFO');

         /**
             第三步 准备数据
         */
         v_step := 3;
         PROC_RECON_LOG(auto||'回收('||bankCode||')历史对账失败数据','PROC_PREPAR_BANK_DATA','丢单数据补充','INFO');
         v_insert_normal_sql :='insert into t_bank_settle_data_temp
                                            select  substr(t.id,0,instr(t.id,'||'''_'''||',1,1)-1) id,
                                                      t.bank_code,
                                                      to_char(t.settle_time,'||'''yyyymmdd'''||'),
                                                      t.order_no,
                                                      t.out_trade_no,
                                                      t.old_payment_type_id,
                                                      t.trade_type,
                                                      t.order_amount,
                                                      t.order_time,
                                                      t.trade_fee,
                                                      t.period_fee,
                                                      t.refund_no,
                                                      null,
                                                      t.refund_amount,
                                                      t.refund_fee,
                                                      t.settle_time,
                                                      null,
                                                      null,
                                                      t.in_out_type,
                                                      1,
                                                      t.buss_interface_type,
                                                      t.channel_code,
                                                      t.channel_name,
                                                      t.bank_name,
                                                      t.name,
                                                      t.card_no
                                                      from t_bank_recon_diff t
                                         where (t.tz_state is null or  t.tz_state in('||'''FORTZ'''||','||'''GRFREEZEN'''||','||'''GRUNFREEZEN'''||'))
                                         and t.recon_result = '||'''LOSE'''||'
                       AND  t.TRADE_TIME <= to_date('''||tradeDate||''','||'''yyyymmdd'''||') ';

         if bankCode is not null then
            v_insert_normal_sql := v_insert_normal_sql ||' and t.channel_code='''||bankCode||'''';
         end if;
         execute immediate v_insert_normal_sql;

         COMMIT;

         /* 清除重复对账的重复数据*/
          delete from t_bank_settle_data_temp
          where is_repeat != 1 and order_no in (
          select id
           from
          (select
          id,
          row_number() over (partition by id  order by is_repeat desc )  as rn
          from t_bank_settle_data_temp
          where channel_code = bankCode
                    and is_repeat = 1
                    and settle_date = tradeDate )
          where rn = 1
          );

          commit;

         PROC_RECON_LOG(auto||'回收('||bankCode||')历史对账失败数据','PROC_PREPAR_BANK_DATA','丢单数据补充','INFO');

          exception
          when others then
            if  v_step = 1   then
            PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_BANK_DATA','清除银行对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            ELSIF v_step = 2 THEN
            PROC_RECON_LOG(auto||'准备美'||tradeDate||'('||bankCode||')对账数据','PROC_PREPAR_BANK_DATA','获取银行对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            ELSIF  v_step = 3   then
            PROC_RECON_LOG(auto||'回收('||bankCode||')历史对账失败数据','PROC_PREPAR_BANK_DATA','收回丢单数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            end if;

     END;

     /**发起银行对账
       bankCode：银行编号
       tradeDate：交易日期yyyymmdd
       auto: 0 自动，1手动
     */
     procedure PROC_BANK_RECON(bankCode VARCHAR2, tradeDate varchar2, auto VARCHAR2) as
               v_step number(2) := 0;
               v_count number(16):= 0;
               --对账时间
               v_temp_date timestamp;

               v_delete_sql_1 varchar2(2000);
               v_delete_sql_2 varchar2(2000);

               /**定义临时存储信息变量，用于更新LOSE订单数据*/
               v_payment_id t_payment_type.paymenttype_id%type;
               v_business_type t_payment_type.business_type%type;
               v_pay_channel_type t_payment_type.pay_channel_type%type;
               v_pay_channel_name t_payment_type.pay_channel_name%type;
               v_gateway_type t_payment_type.gateway_type%type;
               v_buss_interface_type t_payment_type.buss_interface_type%type;
               v_channel_fee t_payment_type.channel_fee%type;
               v_bank_code t_payment_type.bank_code%type;
               v_bank_name t_payment_type.bank_name%type;
               v_create_time t_payment_type.create_time%type;
               v_acc_name t_payment_type.acc_name%type;
               v_card_no t_payment_type.card_no%type;
               v_mount t_payment_type.amount%type;

               v_old_payment_type_id t_bank_recon_detail.old_payment_type_id%type;
               v_order_time        t_bank_recon_detail.order_time%type;
               v_trade_fee        t_bank_recon_detail.trade_fee%type;
               v_period_fee      t_bank_recon_detail.period_fee%type;
               v_settle_time      t_bank_recon_detail.settle_time%type;
               v_trade_type      t_bank_recon_detail.trade_type%type;
               v_order_amount      t_bank_recon_detail.order_amount%type;


                /**定义缓存表*/
               TYPE id_table_record_type IS RECORD (
                id  t_bank_recon_detail.id%TYPE,
                business_type t_bank_recon_detail.business_type%TYPE,
                recon_status t_bank_recon_detail.recon_result%TYPE,
                in_out_type t_bank_recon_detail.in_out_type%TYPE,
                recon_time t_bank_recon_detail.recon_time%TYPE);
                TYPE id_table_type IS TABLE OF id_table_record_type;
                 id_table id_table_type;
                /**存时间*/
                TYPE id_time_record IS RECORD (
                id_times  t_bank_recon_rst.recon_time%TYPE);
                TYPE id_time_record_type IS TABLE OF id_time_record;
                 id_time id_time_record_type;

     begin
          /**
             第一步：
             清理对账结果，以便支持重复对账

             第二步：
             准备美数据

             第三步
             准备银行数据

             第四部
             执行对账：
             LOSE：丢单：我们没有银行有
             LACK：掉单：我们有银行没有
             SUCCESS：成功
             FAILURE：失败

             第五步：
             按渠道统计对账明细，形成对账汇总

             第六步：
             收集对账明细中的失败订单，存入差错表

             第七步：
             更新支付方式中的对应ID的对账方式

             第八步：
             删除对账明细表中重复插入的隔日对账数据
          */

          /**
             第一步
          */
          v_step := 1;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','清除银行对账临时数据开始','INFO');
          /**删除银行对账临时数据*/
          --delete from t_bank_settle_data_temp t where t.bank_code = bankCode and t.settle_date = tradeDate;
          /**删除对应明细*/
          --delete from t_bank_recon_detail t where t.bank_code = bankCode and to_char(t.trade_time,'yyyymmdd') = tradeDate;
          /**删除汇总：同一批次时间产生的结果*/
          select
          count (1) into  v_count
          from t_bank_recon_detail t
          where t.channel_code = bankCode
          and to_char(t.trade_time,'yyyymmdd') = tradeDate
          and trunc(recon_time) = trunc(sysdate);

           if v_count > 0 then
           --select recon_time
           --bulk collect into id_time
           --from (   select
             -- distinct recon_time
             -- from t_bank_recon_rst t
             -- where t.channel_code = bankCode and to_char(t.trade_time,'yyyymmdd') = tradeDate and trunc(recon_time) = trunc(sysdate));

          -- for i in 1..id_time.count loop
              delete from t_bank_recon_rst t where t.channel_code = bankCode and trunc(t.recon_time) = trunc(sysdate) and t.TRADE_TIME <=to_date(tradeDate,'yyyymmdd');
              delete from t_bank_recon_detail t where t.channel_code = bankCode and trunc(t.recon_time) = trunc(sysdate) and to_char(t.trade_time,'yyyymmdd') <= tradeDate;
          -- end loop;
           else
                 delete from t_bank_recon_rst t where t.channel_code = bankCode and trunc(t.recon_time) = trunc(sysdate) and t.TRADE_TIME <=to_date(tradeDate,'yyyymmdd');
          end if;
          /**删除差错*/
          delete from t_bank_recon_diff t where t.channel_code = bankCode and t.trade_time >= to_date(tradeDate,'yyyymmdd') and t.trade_time < to_date(tradeDate,'yyyymmdd')+1;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','清除银行对账临时数据结束','INFO');

          COMMIT;
          /**
             第二步
          */
          v_step := 2;
          PROC_PREPAR_KM_DATA(bankCode,tradeDate ,auto );

           /**
             第三步
          */
          v_step := 3;
           PROC_PREPAR_BANK_DATA(bankCode,tradeDate  ,auto);

           /**
             第四步
          */
          v_step := 4;
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','银行对账开始','INFO');
          --记录对账时间
          v_temp_date := sysdate;

          insert into t_bank_recon_detail
          select  decode(t1.order_no, null ,t2.order_no,t1.order_no),
                          t1.business_type,
                          decode(t1.channel_code,null,t2.channel_code,t1.channel_code),
                          decode(t1.channel_code,null,t2.channel_name,t1.channel_name),
                          t1.channel_fee,
                          decode(t1.order_no, null ,t2.bank_code,t1.bank_code),
                          decode(t1.order_no, null ,t2.bank_name,t1.bank_name),
               decode(t1.order_no, null ,to_date(t2.settle_date,'yyyymmdd'), decode(t2.order_no, null, t1.trade_time, decode(t1.trade_amt, t2.order_amount, decode(t1.is_repeat+t2.is_repeat,0,t1.trade_time,to_date(t2.settle_date,'yyyymmdd')) , t1.trade_time))) ,
                          decode(t1.order_no, null ,t2.name,t1.name),
                          decode(t1.order_no, null ,t2.card_no,t1.card_no),
                          decode(t1.out_trade_no,null,t2.out_trade_no,t1.out_trade_no),
                          decode(t1.order_no, null ,t2.order_no,t1.order_no),
                          t1.trade_amt,
                          null,
                          0,
                          0,
                          t2.old_orderno,
                          t2.order_time,
                          t2.trade_fee,
                          t2.period_fee,
                          to_date(t2.settle_date,'yyyymmdd'),
                          decode(t1.order_no, null ,t2.trade_type,t1.trade_type),
                          t2.order_amount,
                          decode(t1.order_no, null ,'LOSE', decode(t2.order_no, null, 'LACK', decode(t1.trade_amt, t2.order_amount, decode(t1.is_repeat+t2.is_repeat,0,'SUCCESS','SEDSUCC') , 'FAILURE'))) ,
                          v_temp_date,
                          decode(decode(t1.order_no, t2.order_no,  decode(t1.trade_amt, t2.order_amount, 'SUCCESS' , 'FAILURE'),'FAILURE') , 'SUCCESS' ,'UNDO' ,'FORTZ'),
                          null,
                          null,
                          decode(t1.order_no, null ,t2.in_out_type, t1.in_out_type),
                          decode(t1.order_no, null ,t2.buss_interface_type,t1.buss_interface_type)
          from
          (select id,
                     channel_code,
                     channel_name,
                     channel_fee,
                     business_type,
                     bank_code,
                     bank_name,
                     name,
                     card_no,
                     out_trade_no,
                     order_no,
                     trade_amt,
                     trade_fee,
                     trade_time,
                     in_out_type,
                     is_repeat,
                     buss_interface_type,
                     trade_type
                  from t_kmp_bank_recon_temp
                  where channel_code = bankCode
          ) t1
          full join
          (select id,
                     channel_code,
                     channel_name,
                     bank_code,
                     bank_name,
                     name,
                     card_no,
                     settle_date,
                     order_no,
                     out_trade_no,
                     old_orderno,
                     trade_type,
                     order_amount,
                     order_time,
                     trade_fee,
                     period_fee,
                     settle_time,
                     in_out_type,
                     is_repeat,
                     buss_interface_type
                  from t_bank_settle_data_temp
                  where channel_code = bankCode
          ) t2
          on t1.order_no = t2.order_no and t1.in_out_type = t2.in_out_type;
          commit;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','银行对账结束','INFO');

          /**
             清理临时表
          */
          v_delete_sql_1 := 'delete from t_kmp_bank_recon_temp where channel_code='''||bankCode||'''';
          v_delete_sql_2 := 'delete from t_bank_settle_data_temp where channel_code='''||bankCode||'''';

          execute immediate v_delete_sql_1;
          execute immediate v_delete_sql_2;

          commit;

           v_step := 5;
          --判断对账明细中是否含有所对账的渠道数据，如果没有创建一条为空的B2C，成功的记录
          select
          count (1) into  v_count
          from t_bank_recon_detail t
          where t.channel_code = bankCode and to_char(t.trade_time,'yyyymmdd') = tradeDate and trunc(recon_time) = trunc(sysdate);

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','当日对账数据为'||v_count||'条，为0不补充数据，补充记录开始','INFO');

          if v_count = 0 then
              insert into t_bank_recon_detail(ID,BUSINESS_TYPE,CHANNEL_CODE,BANK_CODE,TRADE_TIME,ORDER_TIME,SETTLE_TIME,RECON_TIME,RECON_RESULT,TZ_STATE,IN_OUT_TYPE)
              values('temp_'||SEQ_BANK_RECON_DETAIL.NEXTVAL,
              'TRADE',
              bankCode,
              'ABC',
              to_date(tradeDate,'yyyymmdd'),
              to_date(tradeDate,'yyyymmdd'),
              to_date(tradeDate,'yyyymmdd'),
              v_temp_date,
              'SUCCESS',
              'UNDO',
              'IN');
          end if;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','当日对账数据为'||v_count||'条，为0不补充数据，补充记录结束','INFO');

          commit;

           v_step := 6;
          /**
             过滤对账成功的丢调单数据
          */
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','过滤15日内对账成功的丢调单数据开始','INFO');
           delete from t_bank_recon_detail
           where channel_code = bankCode
                    and recon_time = v_temp_date
                    and INSTR('LOSE,LACK', recon_result, 1, 1) >= 1
                    and id in (select id from t_bank_recon_detail
                                where channel_code = bankCode
                                and INSTR('SEDSUCC', recon_result, 1, 1) >= 1
                                and  recon_time >= v_temp_date - 15);
           commit;

           PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','过滤15日内对账成功的丢调单数据结束','INFO');

           /**
            * 过滤银行往前切导致提前成功的掉单数据
            */
          select id,business_type,recon_result,in_out_type,recon_time
          bulk collect into id_table
          from t_bank_recon_detail
          where channel_code = bankCode
          and recon_time = v_temp_date
          and recon_result = 'LACK';

          for i in 1..id_table.count loop
            SELECT count (1) into  v_count
              FROM   t_payment_type  t
              where t.paymenttype_id = id_table(i).id
              and t.is_recon in (1,3,4);

              if v_count > 0 then
                --判断是否存在历史成功数据
                select count(1) into v_count
                from  t_bank_recon_detail  t
                where t.id = id_table(i).id
                and (t.recon_result = 'SUCCESS' or t.recon_result = 'SEDSUCC')
                and t.recon_time != v_temp_date;
                if v_count > 0 then
                  select
                  t.old_payment_type_id ,
                  t.order_time ,
                  t.trade_fee ,
                  t.period_fee ,
                  t.settle_time ,
                  t.trade_type ,
                  t.order_amount
                  into
                  v_old_payment_type_id ,
                  v_order_time ,
                  v_trade_fee ,
                  v_period_fee ,
                  v_settle_time ,
                  v_trade_type ,
                  v_order_amount
                from  t_bank_recon_detail  t
                where t.id = id_table(i).id
                and (t.recon_result = 'SUCCESS' or t.recon_result = 'SEDSUCC')
                and t.recon_time != v_temp_date;

                update t_bank_recon_detail t
                    set t.old_payment_type_id = v_old_payment_type_id,
                    t.order_time = v_order_time,
                    t.trade_fee = v_trade_fee,
                    t.period_fee = v_period_fee,
                    t.settle_time = v_settle_time,
                    t.trade_type = v_trade_type,
                    t.order_amount = v_order_amount,
                      t.recon_result = 'SUCCESS',
                      t.tz_state = 'UNDO'
                where t.id = id_table(i).id
                    and t.recon_time = v_temp_date
                    and t.in_out_type =id_table(i).in_out_type ;
              end if;
            end if;
          end loop;

            v_step := 7;
          /**
             统计丢单（LOSE）数据，更新，银行对账文件有说明交易成功，可能我们交易未拿到状态需更新
          */
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON',' 统计丢单（LOSE）数据，丢单为交易成功数据，更新，银行对账文件有说明交易成功开始','INFO');

          select id,business_type,recon_result,in_out_type,recon_time
          bulk collect into id_table
          from t_bank_recon_detail
          where channel_code = bankCode
          and recon_time = v_temp_date
          and recon_result = 'LOSE';
          for i in 1..id_table.count loop

              SELECT count (1) into  v_count
              FROM   t_payment_type  t
              where t.paymenttype_id = id_table(i).id;

              if v_count > 0 then

                 select
                        t.paymenttype_id,
                        t.business_type,
                        t.pay_channel_type,
                        t.pay_channel_name,
                        t.channel_fee,
                        t.bank_code,
                        t.bank_name,
                        t.card_no,
                        t.acc_name,
                        t.gateway_type,
                        t.buss_interface_type,
                        t.amount,
                        t.create_time
                        into
                        v_payment_id,
                        v_business_type,
                        v_pay_channel_type,
                        v_pay_channel_name,
                        v_channel_fee,
                        v_bank_code,
                        v_bank_name,
                        v_card_no,
                        v_acc_name,
                        v_gateway_type,
                        v_buss_interface_type,
                        v_mount,
                        v_create_time
              from  t_payment_type  t
              where t.paymenttype_id = id_table(i).id;

                 --更新支付方式对账状态为4（对账成功但需处理，订单调度处理），状态改为成功
                  update t_payment_type t
                  set t.is_recon = case when t.status='SUCCESS' then 1 else 4 end,
                  t.status='SUCCESS'
                  where t.paymenttype_id = id_table(i).id
                  and ( t.is_recon in ( 0, 2 ) or t.is_recon is null);
                  --更新对账明细为对账成功，且补充相关我方订单数据
                  update t_bank_recon_detail t
                  set t.order_no = v_payment_id,
                  t.business_type = v_business_type,
                  t.channel_code = v_pay_channel_type,
                  t.channel_name = v_pay_channel_name,
                  t.channel_fee = v_channel_fee,
                  t.bank_name = v_bank_name,
                  t.card_no = v_card_no,
                  t.trade_amt = v_mount,
                  t.name = v_acc_name,
                  t.trade_type = v_gateway_type,
                  t.buss_interface_type = v_buss_interface_type,
                  t.recon_result = 'SUCCESS',
                  t.tz_state = 'UNDO'
                  where t.id = id_table(i).id
                  and t.recon_time = v_temp_date
                  and t.in_out_type =id_table(i).in_out_type ;
      else
                  --美没有任何数据的情况
                  update t_bank_recon_detail t
                  set
                  t.trade_amt = t.order_amount,
                  t.recon_result = 'EXCEPTION',
                  t.tz_state = 'FORTZ'
                  where t.id = id_table(i).id
                  and t.recon_time = v_temp_date
                  and t.in_out_type =id_table(i).in_out_type ;

              end if;
          end loop;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON',' 统计丢单（LOSE）数据，丢单为交易成功数据，更新，银行对账文件有说明交易成功结束','INFO');

          commit;
           /**
             第五步
          */
          v_step := 8;
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计银行对账差错明细开始','INFO');

           /**更新或插入差错表*/
          merge into t_bank_recon_diff t1
          using (select  id||'_'||in_out_type id ,
                              business_type,
                              channel_code ,
                              channel_name ,
                              channel_fee  ,
                              bank_code    ,
                              bank_name    ,
                              trade_time   ,
                              name   ,
                              card_no  ,
                              out_trade_no  ,
                              order_no    ,
                              trade_amt      ,
                              refund_no       ,
                              refund_amount   ,
                              refund_fee      ,
                              old_payment_type_id,
                              order_time    ,
                              trade_fee     ,
                              period_fee    ,
                              settle_time   ,
                              trade_type    ,
                              order_amount   ,
                              recon_result  ,
                              recon_time    ,
                              tz_state,
                              in_out_type,
                              buss_interface_type
                        from t_bank_recon_detail
                        where channel_code = bankCode
                              and recon_time = v_temp_date
                              and ((recon_result not in ( 'SUCCESS', 'SEDSUCC'))
                                  or ( to_char(trade_time,'yyyymmdd') = tradeDate
                                       and recon_result in ('SEDSUCC')))) t2
          on (t1.id = t2.id )
          when matched then
              update set t1.business_type = t2.business_type ,
                    t1.channel_code = t2.channel_code,
                    t1.channel_name = t2.channel_name ,
                    t1.channel_fee = t2.channel_fee  ,
                    t1.bank_code = t2.bank_code    ,
                    t1.bank_name = t2.bank_name    ,
                    t1.trade_time = t2.trade_time   ,
                    t1.name = t2.name   ,
                    t1.card_no = t2.card_no  ,
                    t1.out_trade_no = t2.out_trade_no  ,
                    t1.order_no = t2.order_no    ,
                    t1.trade_amt = t2.trade_amt      ,
                    t1.refund_no = t2.refund_no       ,
                    t1.refund_amount = t2.refund_amount   ,
                    t1.refund_fee = t2.refund_fee      ,
                    t1.old_payment_type_id = t2.old_payment_type_id,
                    t1.order_time = t2.order_time    ,
                    t1.trade_fee = t2.trade_fee     ,
                    t1.period_fee = t2.period_fee    ,
                    t1.settle_time = t2.settle_time   ,
                    t1.trade_type = t2.trade_type    ,
                    t1.order_amount = t2.order_amount   ,
                    t1.recon_result = t2.recon_result  ,
                    t1.recon_time = t2.recon_time    ,
                    t1.tz_state = t2.tz_state,
                    t1.in_out_type = t2.in_out_type,
                    t1.buss_interface_type = t2.buss_interface_type
          when not matched then
               insert values (
                     t2.id ,
                    t2.business_type,
                    t2.channel_code ,
                    t2.channel_name ,
                    t2.channel_fee  ,
                    t2.bank_code    ,
                    t2.bank_name    ,
                    t2.trade_time   ,
                    t2.name   ,
                    t2.card_no  ,
                    t2.out_trade_no  ,
                    t2.order_no    ,
                    t2.trade_amt      ,
                    t2.refund_no       ,
                    t2.refund_amount   ,
                    t2.refund_fee      ,
                    t2.old_payment_type_id,
                    t2.order_time    ,
                    t2.trade_fee     ,
                    t2.period_fee    ,
                    t2.settle_time   ,
                    t2.trade_type    ,
                    t2.order_amount   ,
                    t2.recon_result  ,
                    t2.recon_time    ,
                    t2.tz_state,
                    null,
                    null,
                    t2.in_out_type,
                    null,
                    t2.buss_interface_type);

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计银行对账差错明细结束','INFO');

          COMMIT;
          /**
             第六步
          */
          v_step := 9;
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','更新支付方式或调拨对账状态开始','INFO');

          /**获取今日对账结果 , 丢单不需要*/
          select id,business_type,recon_result,in_out_type,recon_time
          bulk collect into id_table
          from t_bank_recon_detail
          where channel_code = bankCode
          and  recon_result != 'LOSE'
          and recon_time = v_temp_date;--(select distinct recon_time from  t_bank_recon_detail where bank_code = bankCode and trunc(trade_time) = to_date(tradeDate,'yyyymmdd') and trunc(recon_time) = trunc(sysdate));

          /**截止时间*/
          --v_temp_date := to_date(to_char(sysdate - 3, 'yyyymmdd'),'yyyymmdd');

           /**遍历更新*/
           /**
           注：调拨一次记账到位，记录paymenttype，无需在更新
           */
          for i in 1..id_table.count loop
              --if id_table(i).business_type = 'ALLOT' then
              if id_table(i).business_type != 'ALLOT' then
                --if id_table(i).in_out_type = 'IN' then
                         /**更新调入方对账状态*/
                --         update t_cash_manage t
                --         set t.is_in_recon = 1
                 --        where t.id = id_table(i).id ;
                 --   else
                         /**更新调出方对账状态*/
                 --        update t_cash_manage t
                 --        set t.is_out_recon = 1
                 --        where t.id = id_table(i).id ;
                  --  end if;
              ---else
                /**更新订单对账状态：1已对账成功，2未对账成功*/
                 update t_payment_type t
                 set t.is_recon = decode(id_table(i).recon_status,'SUCCESS',1,decode(id_table(i).recon_status,'SEDSUCC',1,2))
                 where t.paymenttype_id = id_table(i).id
                 --and t.status = 'SUCCESS'
                 and t.status = decode(id_table(i).recon_status,'SUCCESS','SUCCESS','SEDSUCC','SUCCESS',t.status)
                 and ( t.is_recon in ( 0, 2 )or t.is_recon is null);
               end if;

               if mod(i,1000)=0 then
                  commit;
               end if;
          end loop;
          commit;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','更新支付方式或调拨对账状态结束','INFO');

           /**
             第七步
          */
          v_step := 10;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计银行对账明细汇总开始','INFO');
          /**
          按渠道、银行、收支、交易日期
          */
          insert into t_bank_recon_rst(ID,CHANNEL_CODE,TRADE_TIME,IN_OUT_TYPE,SUCCESS_COUNT,SUCCESS_AMT,SUCCESSL_FEE,FAILURE_COUNT,
          FAILURE_KM_AMT,FAILURE_BANK_AMT,LACK_COUNT,LACK_AMT,LOSE_COUNT,LOSE_AMT,RECON_TIME,SUCCESST_FEE,LOSE_FEE,EXCEPTION_COUNT,EXCEPTION_BANK_AMT,EXCEPTION_BANK_FEE)
          select seq_bank_recon_rst.nextval, t.* from
          (select
          t.channel_code,
          --t.bank_code,
          trunc(t.trade_time),
          t.in_out_type,
          sum(decode(t.recon_result,'SUCCESS',1,decode(t.recon_result,'SEDSUCC',1,0))),                                                           --成功总笔数
          sum(decode(t.recon_result,'SUCCESS',t.trade_amt,decode(t.recon_result,'SEDSUCC',t.trade_amt,0))),                            --成功总交易金额
          sum(decode(t.recon_result,'SUCCESS',t.channel_fee,decode(t.recon_result,'SEDSUCC',t.channel_fee,0))),                      --成功总手续费金额
          sum(decode(t.recon_result,'FAILURE',1,0)),                                                                                                                  --失败总笔数
          sum(decode(t.recon_result,'FAILURE',t.trade_amt,0)),                                                                                                   --失败美总交易金额
          sum(decode(t.recon_result,'FAILURE',t.order_amount,0)),                                                                                             --失败银行总交易金额
          sum(decode(t.recon_result,'LACK',1,0)),                                                                                                                        --调单总笔数
          sum(decode(t.recon_result,'LACK',t.trade_amt,0)),                                                                                                         --调单总交易金额
          sum(decode(t.recon_result,'LOSE',1,0)),                                                                                                                         --丢单总笔数
          sum(decode(t.recon_result,'LOSE',t.order_amount,0)),                                                                                                    --丢单总交易金额
          v_temp_date,
          sum(decode(t.recon_result,'SUCCESS',t.trade_fee,0)),                                                                                                    --成功总银行手续费金额
          sum(decode(t.recon_result,'LOSE',t.trade_fee,0)),
          sum(decode(t.recon_result,'EXCEPTION',1,0)),                                                                                                                  --失败总笔数
          sum(decode(t.recon_result,'EXCEPTION',t.order_amount,0)),                                                                                                   --失败康美总交易金额
          sum(decode(t.recon_result,'EXCEPTION',t.trade_fee,0))                                                                                                          --成功总银行手续费金额
          from  t_bank_recon_detail t
          where t.recon_time = v_temp_date
          and t.channel_code = bankCode
          and INSTR(t.id ,'temp', 1, 1) <= 0
          group by t.channel_code,t.in_out_type,trunc(t.trade_time)
          ) t;

          commit;

           PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计银行对账明细汇总结束','INFO');
           /**
             第八步
          */
          v_step := 11;
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','删除对账明细表中重复插入的隔日对账数据开始','INFO');
          /**获取今日对账结果中，非当日交易删除重复的对账明细,删除此前的对账重复数据，保留最新的重复数据*/
          /**select id,business_type,in_out_type
          bulk collect into id_table
          from t_bank_recon_detail
          where channel_code = bankCode
          and trunc(recon_time) = trunc(sysdate) and to_char(trade_time,'yyyymmdd') != tradeDate;*/

          select id,business_type,recon_result,in_out_type,recon_time
          bulk collect into id_table
           from
          (select
          a.id,a.business_type,a.recon_result,a.in_out_type,a.recon_time,
          row_number() over (partition by a.id,a.in_out_type order by recon_time desc)  as rn
          from t_bank_recon_detail a
          where channel_code = bankCode
                    and recon_time = v_temp_date--(select distinct recon_time from  t_bank_recon_detail where bank_code = bankCode and trunc(trade_time) = to_date(tradeDate,'yyyymmdd') and trunc(recon_time) = trunc(sysdate) )
                    and to_char(trade_time,'yyyymmdd') != tradeDate )
          where rn = 1;

          for i in 1..id_table.count loop

             delete from t_bank_recon_detail t
             where t.id = id_table(i).id
             and t.in_out_type = id_table(i).in_out_type
             and t.recon_time != id_table(i).recon_time;

              if mod(i,1000)=0 then
                  commit;
              end if;
          end loop;

          commit;

          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','删除对账明细表中重复插入的隔日对账数据结束','INFO');

          /**删除银行对账临时数据*/
          delete from t_bank_settle_data_temp t where t.channel_code = bankCode ;--and t.settle_date = tradeDate;
          /**删除对应明细*/
          delete from t_kmp_bank_recon_temp t where t.channel_code = bankCode ;--and to_char(t.trade_time,'yyyymmdd') = tradeDate;
          commit;
     exception
     when others then
           if  v_step = 1   then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','清除银行对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 2 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','清除美对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 3 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','清除银行对账临时数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 4 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','银行对账异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 5 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','当日对账数据为'||v_count||'条，为0不补充数据，补充记录异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 6 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','过滤15日内对账成功的丢调单数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 7 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计丢单（LOSE）数据，丢单为交易成功数据，更新，异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 8 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计银行对账差错明细异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 9 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','更新支付方式或调拨对账状态异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 10 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','统计银行对账明细汇总异常：'||sqlcode||', '||sqlerrm ,'ERROR');
            elsif  v_step = 11 then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_BANK_RECON','删除对账明细表中重复插入的隔日对账数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');
           end if;

     end ;

     procedure PROC_DELETE_BANK_DATA(BANKCODE VARCHAR2, TRADEDATE VARCHAR2, auto VARCHAR2) as
     begin
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_DELETE_BANK_DATA','清除银行对账数据开始','INFO');
          /**删除银行对账临时数据*/
          delete from t_bank_settle_data_temp t where t.bank_code = bankCode and t.settle_date = tradeDate;
          PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_DELETE_BANK_DATA','清除银行对账数据结束','INFO');
          commit;

     exception
     when others then
            PROC_RECON_LOG(auto||'银行('||bankCode||')'||tradeDate||'对账','PROC_DELETE_BANK_DATA','清除银行对账数据异常：'||sqlcode||', '||sqlerrm ,'ERROR');

     end;

     /**
     记录日志
     */
     procedure PROC_RECON_LOG(proName varchar2, proFun varchar2,content varchar2, logLevel varchar2) as
     begin
          insert into t_bank_recon_log values(SEQ_BANK_RECON_LOG.Nextval, proName, proFun, logLevel, substr(content,0,400), sysdate);
          commit;
     end;

END PKG_BANK_RECON;



















CREATE OR REPLACE PACKAGE PKG_BANK_RECON AS

  /**准备美对账数据
   bankCode：银行编号,为空表示获取全部的对账数据
  tradeDate：交易日期yyyymmdd，不能为空
  auto: 0 自动，1手动
  */
  PROCEDURE PROC_PREPAR_KM_DATA(BANKCODE  VARCHAR2,
                                TRADEDATE VARCHAR2,
                                AUTO      VARCHAR2);

  /**准备银行对账数据
   bankCode：银行编号,为空表示获取全部的对账数据
  tradeDate：交易日期yyyymmdd，不能为空
  */
  PROCEDURE PROC_PREPAR_BANK_DATA(BANKCODE VARCHAR2, TRADEDATE VARCHAR2, auto VARCHAR2);

  /**发起银行对账
    bankCode：银行编号
    tradeDate：交易日期yyyymmdd
    auto: 0 自动，1手动
  */
  PROCEDURE PROC_BANK_RECON(BANKCODE  VARCHAR2,
                            TRADEDATE VARCHAR2,
                            AUTO      VARCHAR2);

  /**
  清除银行数据
   bankCode：银行编号,为空表示获取全部的对账数据
  tradeDate：交易日期yyyymmdd，不能为空
  */
  PROCEDURE PROC_DELETE_BANK_DATA(BANKCODE VARCHAR2, TRADEDATE VARCHAR2, auto VARCHAR2);

  /**记录日志*/
  PROCEDURE PROC_RECON_LOG(PRONAME  VARCHAR2,
                           PROFUN   VARCHAR2,
                           CONTENT  VARCHAR2,
                           LOGLEVEL VARCHAR2);

END PKG_BANK_RECON;