create or replace procedure proc_autoAuditCreditAmount
as
--声明游标 结果集A:需要核销的机构(有未核销单据)
cursor ivan_cur is
select customerDepaID, min(CreditAccount_ID) acBeginID, max(CreditAccount_ID) acEndID
from Tbl_Creditaccount
where alterationType<=0 and isPayback=0
group by customerDepaID
order by customerDepaID;
--声明游标(带参数) 结果集B:某机构需要核销的单据,某机构的id来源于结果集A的循环
cursor ivan_cur_b(
p_depa_id Tbl_Creditaccount.Creditaccount_Id%type
,p_beginID Tbl_Creditaccount.Creditaccount_Id%type
,p_endID Tbl_Creditaccount.Creditaccount_Id%type )
is
select *
from Tbl_Creditaccount
where alterationType<=0 and isPayback=0 and customerDepaID=p_depa_id
and CreditAccount_ID between p_beginID and p_endID
order by alterationTime asc, CreditAccount_ID asc;
--声明变量paybackmoney,它的类型是表Tbl_Creditaccount中字段Alterationamount的类型,初始化为0
v_paybackNum number := 0; --某机构还款单数量
v_paybackmoney Tbl_Creditaccount.Alterationamount%type := 0;--某机构还款单总金额
v_minPaybackID Tbl_Creditaccount.Creditaccount_Id%type; --某机构还款单开始ID
v_maxPaybackID Tbl_Creditaccount.Creditaccount_Id%type; --某机构还款单结束ID
v_checkNum number := 0; --某机构已核销记录数量
v_lastSettleBalance Tbl_Cancelrecord.Settlebalance%type := 0; --某机构最后一次结余金额
v_settleBalance Tbl_Cancelrecord.Settlebalance%type := 0;--某机构本次可用来还款的总金额
v_totalAmount Tbl_Cancelrecord.totalamount%type := 0;--某机构本次的核销了的单据的总金额
v_recordNo number := 0; --核销单编号yyyyMMddsys00001
v_index number;
v_depaName Tbl_Depa.Depaname%type := 0; --机构名称(确保最新)
v_cancelrecord_id Tbl_Cancelrecord.cancelrecord_id%type := 0; --核销主表ID
v_calculateenddatetime Tbl_Cancelrecord.calculateenddatetime%type; --核销结束时间
--v_alterationAmount Tbl_Creditaccount.Alterationamount%type := 0;--某机构每笔借贷的金额
begin
--1.循环核销每一个需要核销的机构
for ivan_rec in ivan_cur loop
begin
savepoint sp1;
v_recordNo := v_recordNo + 1;
v_index := 0; --每次循环开头都要初始化,因为这是子循环的index
select depaname into v_depaName from tbl_Depa where depa_id = ivan_rec.customerDepaID;--取机构此刻的名称
--DBMS_OUTPUT.put_line('The customerID is '||ivan_rec.customerDepaID||', record from '||ivan_rec.acBeginID||' to '||ivan_rec.acEndID);
--2.查找该机构可使用(未核销)的还款单总金额
--(2.1)首先判断该机构是否有可还款的记录,防止Not_Data_Found Exception
select count(ca.creditaccount_id) into v_paybackNum
from Tbl_Creditaccount ca
where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID;
if v_paybackNum = 0 then
v_paybackmoney := 0;
v_minPaybackID := 0;
v_maxPaybackID := 0;
else
--(2.2)查找该机构可使用(未核销)的还款单总金额,放入变量paybackmoney
select
sum(alterationamount) into v_paybackmoney
from Tbl_Creditaccount ca
where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID
group by customerDepaID;
select min(Creditaccount_Id) into v_minPaybackID
from Tbl_Creditaccount ca
where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID;
select max(Creditaccount_Id) into v_maxPaybackID
from Tbl_Creditaccount ca
where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID;
end if;
--DBMS_OUTPUT.put_line('This agent has '||v_paybackmoney||' RMB to pay!');
--3.在核销主表中,查找该机构最后一条核销记录,取出该记录的结余金额
--(3.1)首先判断该机构是否有结余金额数据,防止Not_Data_Found Exception
select count(cr.cancelrecord_id) into v_checkNum
from Tbl_Cancelrecord cr
where recordState=2 and agentDepaID=ivan_rec.customerDepaID;
if v_checkNum = 0 then
v_lastSettleBalance := 0;
else
--(3.2)查找机构上一次的结余金额
select settleBalance into v_lastSettleBalance
from (
select rank() over(partition by agentDepaID order by cancelRecordDatetime desc, cancelRecord_ID desc) rn
, cr.settleBalance
from Tbl_Cancelrecord cr
where recordState=2 and agentDepaID=ivan_rec.customerDepaID
) a
where rn=1;
end if;
--DBMS_OUTPUT.put_line('The lastest settle Balance is '||v_lastSettleBalance||' RMB!');
v_settleBalance := v_paybackmoney + v_lastSettleBalance;
DBMS_OUTPUT.put_line('Begin : Agent '||ivan_rec.customerDepaID||'_'||v_depaName||' has '||v_settleBalance||' RMB to pay this time!' );
--前面是准备工作,现在开始为机构核销单据了!
--循环结果集B,核销该机构的订单
for ivan_rec_b in ivan_cur_b(ivan_rec.customerDepaID, ivan_rec.acBeginID, ivan_rec.acEndID) loop
begin
DBMS_OUTPUT.put_line('No.'||ivan_rec_b.billNo||', borrow money :'||ivan_rec_b.alterationAmount||', current settleBalance :'||v_settleBalance);
if v_settleBalance < (-ivan_rec_b.alterationAmount) then --无法核销本单据(钱不够),跳出子循环(如果是第一笔,那么v_cancelrecord_id=0)
DBMS_OUTPUT.put_line('无法继续核销该机构,跳出子循环!' );
exit;
else
if v_index = 0 then --有钱核销第一笔单据,那么首先往核销主表插入一条数据
insert into tbl_cancelrecord(
cancelrecord_id,agentdepaid, agentdepaname
,cancelrecorddatetime,cancelrecordtype--核销种类:0人工 1自动
,calculatebegindatetime--,calculateenddatetime
,cancelrecordno
,createpersonid,createpersonaccount,createdepaid,createdepaname,createdatetime
,lastmodifydepaid,lastmodifypersonname,lastmodifypersonid,lastmodifydepaname,lastmodifydatetime
,recordstate
)
values(
seq_cancelrecord.nextval, ivan_rec_b.customerdepaid, v_depaName
,sysdate, 0 --核销种类:0自动 1人工
,ivan_rec_b.alterationtime
,to_char(sysdate,'yyyyMMdd')||'sys'||Lpad(v_recordNo,5,0)--核销单编号yyyyMMddsys00001
,1,'Admin',3,'综合业务部',sysdate --写死
,1,'Admin',3,'综合业务部',sysdate --写死
,2 --已核销
);
select seq_cancelrecord.currval into v_cancelrecord_id from dual; --赋值到变量,记录核销主表ID
end if;
end if;
--够钱核销该单据
--将本条单据置为已核销
update tbl_creditaccount
set ispayback = 1, payBackType = 0
where creditaccount_id = ivan_rec_b.creditaccount_id;
--插入一条核销记录至核销明细表
insert into tbl_cancelrecorddetails(
cancelrecorddetails_id
,cancelrecordid
,agentdepaid, agentdepaname
,orderid, orderno
,ordertype
)
values(
seq_cancelrecorddetails.nextval
,v_cancelrecord_id
,ivan_rec_b.customerdepaid, v_depaName
,ivan_rec_b.billid, ivan_rec_b.billno
,ivan_rec_b.billtype
);
v_index := v_index + 1;
v_settleBalance := v_settleBalance + ivan_rec_b.alterationAmount; --记住是+,因为ivan_rec_b.alterationAmount带正负号
v_totalAmount := v_totalAmount - ivan_rec_b.alterationAmount; --记住是-,因为ivan_rec_b.alterationAmount带正负号
v_calculateenddatetime := ivan_rec_b.alterationtime;
end;
end loop;
if v_index > 0 then --说明本机构核销了单据
update tbl_cancelrecord --更新核销主表某些字段
set calculateenddatetime = v_calculateenddatetime --本次核销最后一笔单据时间
,totalamount = v_totalAmount--本次共核销金额
,settleBalance = v_settleBalance --本次核销后,结余金额
where cancelrecord_id = v_cancelrecord_id;
update Tbl_Creditaccount --将所有未核销的还款单置为已核销(已用来还款)
set ispayback = 1, payBackType = 0
where alterationType=1 and isPayback=0 --单据:还款单,状态:未核销(未使用)
and customerDepaID=ivan_rec.customerDepaID
--一定要加修改范围,因为这途中可能增加了未核销的还款单,虽然几率很微小
and creditaccount_id between v_minPaybackID and v_maxPaybackID;
end if;
/*
--测试异常
if v_recordNo = 3 then
v_index := v_index / 0;
end if;
*/
--提交一个机构的数据
commit;
DBMS_OUTPUT.put_line('v_recordNo ' || v_recordNo || '-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*' );
exception
when others then
dbms_output.put_line('Error!!!! Error on agent: ' || v_depaName);
rollback to savepoint sp1; --回滚数据,并继续循环下一个机构
end;
end loop;
end;