Tcharge:=Tneedpay+Tbasicfee+Taddfee1+Taddfee2+Tlatefee; if Tpay!=Tcharge then Terror:='金额错误'; error:=Terror; else
update monthlyneedpay set paystatus='1'where clientno=client_no;
select clientname,address into Tname,Taddress from client where client_no=clientno; LName:=TName; address:=Taddress; balance:=Tcharge; endif; commit;
end checkcharge;
第三部分【冲正】 交易描述
冲正交易为缴费交易的逆向操作，但不能简单的把原记录删除，应该在缴费表中增加一条相当于原记录负值的记录，同时应收费用表中缴费操作产生的数据恢复为未缴费的初始状态。
判断是否能找到原银行流水，客户号，实收金额的收费记录，没找到，返回冲正错误的提示信息。如果找到修改应收费用表中的应收违约金，附加费用1，附加费用2，实收违约金，收费标志几个字段，使这几个字段的值恢复为未缴费前的状态；用户交费表中增加一条记录，其中操作类型为“冲正”，银行流水改为银行交费时产生的原流水号。
createorreplaceprocedure checkpay(banktransfer_id innumber,resultoutvarchar2,balance outnumber)is num number(4); Terror varchar2(20); Tclientno number(4); Tpayday date;
10
Tneedpay number(7,2); Taddfee1 number(7,2); Taddfee2 number(7,2); Tlatefee number(7,2); Trbalance number(7,2); Tbalance number(7,2); begin
selectcount(banktransfer_id)into num from chargelog where banktransferid=banktransfer_id ; if num=0then
Terror:='冲正错误'; result:=Terror; endif;
select chargedate,clientno into Tpayday,Tclientno from chargelog where banktransferid=banktransfer_id; dbms_output.put_line(Tpayday); dbms_output.put_line(Tclientno);
select needpay,addfee1,addfee2,latefee into Tneedpay,Taddfee1,Taddfee2,Tlatefee from monthlyneedpay where to_char(payday,'YYYY-MM-DD')=to_char(Tpayday,'YYYY-MM-DD')and clientno=Tclientno;
select balance into Tbalance from client where clientno=Tclientno; Tbalance:=Tbalance+Tneedpay+Taddfee1+Taddfee2+Tlatefee; Trbalance:=Tneedpay+Taddfee1+Taddfee2+Tlatefee;
update client set balance=Tbalance where clientno=Tclientno; insertinto paylog
(id,clientno,paymoney,payday,paytype)values(seq_pay.nextval,Tclientno,-Trbalance,sysdate,'1');
update monthlyneedpay set paystatus=0where
to_char(payday,'YYYY-MM-DD')=to_char(Tpayday,'YYYY-MM-DD')and clientno=Tclientno; result:='冲正成功'; balance:=Trbalance;
end checkpay;
第四部分【对总账】
11
交易描述
每天凌晨银行会发送所有前一天的总交易金额，总交易笔数，及所有交易金额等明细信息，供企业方核对。银行传来的信息包括：银行代码，总笔数，总金额，对帐日期。
根据银行代码，对帐日期在相关表中查找所有满足当前银行，日期的缴费记录笔数和金额，同银行传来数据做比较，如果总笔数，总金额都相同，可以认为此次对总账成功，否则调用对明细账模块。其中冲正交易和被冲正交易的笔数和金额不计入在内。不需要返回给银行对总账是否成功的信息。 createorreplaceprocedure CheckTotalProcedure(bank_id innumber,resultoutvarchar2)is
ourtotalcount number(4); ourtotalmoney number(7,2); banktotalcount number(4); banktotalmoney number(7,2); begin
selectcount(*),sum(b.transfermoney) into ourtotalcount, ourtotalmoney from banktransferrecord b where b.bankid = bank_id
and TO_CHAR(b.transferdate,'YYYY-MM')= TO_CHAR(sysdate-1,'YYYY-MM');
selectcount(*),sum(b.transfermoney) into banktotalcount, banktotalmoney from banktransferfile b where b.bankid = bank_id
and TO_CHAR(b.transferdate,'YYYY-MM')= TO_CHAR(sysdate-1,'YYYY-MM');
if ourtotalcount = banktotalcount and ourtotalmoney = banktotalmoney then
insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'1'); result:='true'; else
insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'0'); result:='false'; endif; commit;
12
end CheckTotalProcedure;
第五部分【对明细账】 交易描述
总账不平需要逐笔核对明细账。判断账务不平的原因，并记录到对账异常表中，由其他应用程序来处理。 根据银行传来的银行代码，银行交易流水号，交易日期时间，客户号，交费金额等信息，在业务系统表中查找相应的记录，对账不平的可能性有企业方无此流水号的缴费信息，银行无此流水号的缴费信息，金额不等，把所有对账不平的信息及原因记录到对账异常表。
createorreplaceprocedure checkdetailmoney(bank_id innumber,resultoutvarchar2)is
ourtotalcount number(4); ourtotalmoney number(7,2); banktotalcount number(4); banktotalmoney number(7,2); cursor recordcursor is
select*from banktransferrecord
where to_char(transferdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd') and bankid=bank_id;
Cursor filecursor is
Select*from banktransferfile
where to_char(transferdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd') and bankid=bank_id;
rc recordcursor%rowtype; fc filecursor%rowtype;
num number(4);
filemoney number(7,2); begin
selectcount(*),sum(b.transfermoney)
13
into ourtotalcount, ourtotalmoney from banktransferrecord b where b.bankid = bank_id
and TO_CHAR(b.transferdate,'YYYY-MM-DD')= TO_CHAR(sysdate-1,'YYYY-MM-DD');
selectcount(*),sum(b.transfermoney) into banktotalcount, banktotalmoney from banktransferfile b where b.bankid = bank_id
and TO_CHAR(b.transferdate,'YYYY-MM-dd')= TO_CHAR(sysdate-1,'YYYY-MM-dd');
if ourtotalcount = banktotalcount and ourtotalmoney = banktotalmoney then
insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'1'); result:='true'; else
insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'0'); result:='false'; open recordcursor;
fetch recordcursor into rc; while recordcursor%foundloop
selectcount(*)into num from banktransferfile b where rc.banktransferid=b.banktransferid; if num=0 then
dbms_output.put_line(rc.clientno); dbms_output.put_line(rc.banktransferid); dbms_output.put_line(rc.transfermoney);
insertinto checkerrordetail values(seq_errordetail.nextval,bank_id,rc.banktransferid, rc.clientNo,'',rc.transfermoney,'银行无此流水号',sysdate); else
select transfermoney into filemoney from banktransferfile b where rc.banktransferid=b.banktransferid;
if filemoney != rc.transfermoney then
insertinto checkerrordetail values(seq_errordetail.nextval,bank_id,rc.banktransferid, rc.clientNo,filemoney,rc.transfermoney,'对账金额有误',sysdate); endif; endif;
fetch recordcursor into rc;
14