--------------------------将内部对账表中的内部流水号(编号)更新到主订单表流水中------------------
DECLARE
---------定义一个对账流水的变量,用来接受获得的对账流水
t_chk_no t_order_main.VER_NO%TYPE;
BEGIN
FOR cr IN (
select distinct ( t_inner_chk_result.settle_date) as settle_date from t_inner_chk_result
)
LOOP
-- 首先在内部对账流水表中查询出对账流水号赋值给定义好的对账流水号变量-------------
select nvl(t2.id,'') into t_chk_no from t_inner_chk_result t2 where t2.settle_date = cr.settle_date;
-- 更新主订单表中的对账流水号,其中条件为主订单表的内部流水号是业务交易详情表中的流水号,内部详情表中的流水号为商户对账表中的id---------
update t_order_main o set o.ver_no = t_chk_no where o.inner_trans_no in (
select t1.inner_trans_no from t_busi_trans_detail t1 where t1.INNER_TRANS_NO in(
select id from t_Mchnt_Chk_Result t where t.SETTLE_DATE = cr.settle_date
));
END LOOP;
END;
---------------将业务交易明细表中的非成功状态的交易的对账流水置为空-----------------
update t_busi_trans_detail b set b.ver_no = null where b.ver_status not in ('A','C');
---------------把商户对账表中对账差错不为0的差错数置为0,差错金额数值置为0,总金额=成功金额,总笔数=成功笔数-----------------
update t_mchnt_chk_result t set t.chk_err_count=0,t.CHK_ERR_AMT=0, t.TRANS_AMT = t.CHK_SUCC_AMT,t.CHK_COUNT =CHK_SUCC_COUNT where t.CHK_ERR_COUNT>0;
----------------------删除商户对账差错表中的所有记录--------------------------------------------------------
delete from t_mchnt_chk_err;