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;