转载：http://blog.csdn.net/u013057786/article/details/17165623
1、定义
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过
编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它，从而完成一个或一系列的数据库操作。
2、存储过程的创建
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。
（1）无参存储过程语法
create or replace procedure NoParPro as //声明 ; begin // 执行 ; exception//存储过程异常 ; end;
（2）带参存储过程实例
create or replace procedure queryempname(sfindno emp.empno%type) as sName emp.ename%type; sjob emp.job%type; begin .... exception .... end;
（3）带参数存储过程含赋值方式
create or replace procedure runbyparmeters
(isal in emp.sal%type,
sname out varchar,
sjob in out varchar)
as
icount number;
begin
select count(*) into icount from emp where sal>isal and job=sjob;
if icount=1 then
....
else
....
end if;
exception
when too_many_rows then
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
when others then
DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
end;其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。
（4）存储过程中游标定义使用
as //定义(游标一个可以遍历的结果集) CURSOR cur_1 IS SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn, SUM(usd_amt)/10000 usd_amt_sn FROM BGD_AREA_CM_M_BASE_T WHERE ym >= vs_ym_sn_beg AND ym <= vs_ym_sn_end GROUP BY area_code,CMCODE; begin //执行（常用For语句遍历游标） FOR rec IN cur_1 LOOP UPDATE xxxxxxxxxxx_T SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn WHERE area_code = rec.area_code AND CMCODE = rec.CMCODE AND ym = is_ym; END LOOP;
（5）游标的定义
--显示cursor的处理declare ---声明cursor,创建和命名一个sql工作区cursor cursor_name is
select real_name from account_hcz;
v_realname varchar2(20);begin
open cursor_name;---打开cursor,执行sql语句产生的结果集
fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录 dbms_output.put_line(v_realname); close cursor_name;--关闭cursorend;
3、在Oracle中对存储过程的调用
（1）过程调用方式一
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
runbyparmeters(realsal,realname,realjob);－－必须按顺序
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束
（2）过程调用方式二
declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin //过程调用开始 realsal:=1100; realname:=''; realjob:='CLERK'; －－指定值对应变量顺序可变 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); END; //过程调用结束
（3）过程调用方式三（SQL命令行方式下）
1、SQL>exec proc_emp('参数1','参数2');//无返回值过程调用
2、SQL>var vsal number
SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用
或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用4.生产环境储存过程实例
A.存储过程中as之后是需要赋值临时变存储量，as之前是自动传值进来的全局变量。
B.简化sql语句。
create or replace procedure p_im_zjgl_yhzhzjgl_cx( v_ztbh decimal, --账套编号 v_sszh varchar,--所属总行 v_zhlx varchar,--账户类型 v_ywqd varchar,--业务渠道 v_sfhz varchar,--是否汇总 01 汇总 02 不汇总 v_userid int,--操作员id v_msg in out varchar,--返回信息 v_ret in out smallint--返回值，0查询成功 99查询失败 ) as v_errorcode integer; v_errormsg varchar(200); v_zhlxbm varchar(6); v_yhzh varchar(30); v_yhzhs varchar2(32767):=''; v_jgbm varchar(50); v_zxjgbm varchar(50); v_zhlx1 varchar(200); v_sszh1 varchar(200); --v_nrets smallint; -----声明游标tmp_cur cursor tmp_cur is select bm from bm_im_zhlx where instr(v_zhlx1,bm)<>0 and substr(bm,3,2)='01' ; -----声明游标tmp_cur1 cursor tmp_cur1 is select yhzhhm,jgbm from im_zhsz where instr(v_sszh1,sszh)<>0 and instr(v_zhlx1,zhlx)<>0 and zhzt = '01' and jgbm like v_zxjgbm||'%' and (exists(select p015 from hr_yg_sjqx_yh where p015=ssyh and userid=v_userid and qxlbbm='03') or ssyh like (select nvl(min(p015),'%') from hr_yg_sjqx_yh where userid=v_userid and qxlbbm='03')) and ztbh = v_ztbh and exists (select column_value from table(f_hfsc_get_user_cwhsjgbm(v_ztbh,v_userid)) where jgbm like column_value||'%') ; ------声明游标tmp_cur2 cursor tmp_cur2 is select bm from bm_im_zhlx where bm like v_zhlx and substr(bm,3,2)='01'; ------声明游标tmp_cur3 cursor tmp_cur3 is select yhzhhm,jgbm from im_zhsz where sszh like v_sszh and instr(v_zhlx1,zhlx)<>0 and zhzt = '01' and jgbm like v_zxjgbm||'%' and (exists(select p015 from hr_yg_sjqx_yh where p015=ssyh and userid=v_userid and qxlbbm='03') or ssyh like (select nvl(min(p015),'%') from hr_yg_sjqx_yh where userid=v_userid and qxlbbm='03')) and ztbh = v_ztbh and exists (select column_value from table(f_hfsc_get_user_cwhsjgbm(v_ztbh,v_userid)) where jgbm like column_value||'%') ; begin delete TMP_IM_ZJZHGL; if trim(v_zhlx)='%' then select listagg(bm,',') within group(order by bm) bm into v_zhlx1 from bm_im_zhlx where length(bm)=6 and substr(bm,3,2)='01' ; else v_zhlx1:=v_zhlx; end if; if v_sfhz = '01' then if trim(v_sszh)='%' then select listagg(bm,',') within group(order by bm) bm into v_sszh1 from bm_sszh ; else v_sszh1:=v_sszh; end if; end if; v_zxjgbm:=f_hfsc_get_user_zxjgbm(v_userid); if v_sfhz = '01' then open tmp_cur1;------打开游标tmp_cur1,执行sql语句产生的结果集 loop -----开始循环 fetch tmp_cur1 into v_yhzh,v_jgbm;----提取tmp_cur1,提取游标中的结果集 exit when tmp_cur1%notfound; -----循环的终止条件(游标一条一条地遍历记录，当找不到记录时退出) v_yhzhs:=v_yhzhs||v_yhzh||','; insert into TMP_IM_ZJZHGL(id,sszh,sszhbm,ssyh,ssyhbm,yhzh,zhlx,zhlxbm,khyhmc,zhhm,zhhqje,zhdqje,wwcdbbs,gxsj) select f_newid,nvl(b.mc,' ') sszhmc,a.sszh,c.mc ssyhmc,a.ssyh,a.yhzhhm,d.mc zhlxmc,a.zhlx,a.yhmc,a.yhzhmc,f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,0),nvl(f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,1),0), (select count(*) from im_syxxb m where a.yhzhhm=m.yhzh and m.zt = 0 ), (select nvl(max(jyczsj),null) from im_tx_zhye where zh = v_yhzh) from im_zhsz a left join bm_sszh b on a.sszh=b.bm left join bm_yhbm c on c.bm = a.ssyh and c.jgbm like v_zxjgbm||'%' left join bm_im_zhlx d on a.zhlx = d.bm where a.yhzhhm = v_yhzh and a.jgbm = v_jgbm and a.ztbh = v_ztbh; end loop;-----结束循环 close tmp_cur1;----关闭游标tmp_cur1 else open tmp_cur3; loop fetch tmp_cur3 into v_yhzh,v_jgbm; exit when tmp_cur3%notfound; v_yhzhs:=v_yhzhs||v_yhzh||','; insert into TMP_IM_ZJZHGL(id,sszh,sszhbm,ssyh,ssyhbm,yhzh,zhlx,zhlxbm,khyhmc,zhhm,zhhqje,zhdqje,wwcdbbs,gxsj) select f_newid,nvl(b.mc,' ') sszhmc,a.sszh,c.mc ssyhmc,a.ssyh,a.yhzhhm,d.mc zhlxmc,a.zhlx,a.yhmc,a.yhzhmc,f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,0),nvl(f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,1),0), (select count(*) from im_syxxb m where a.yhzhhm=m.yhzh and m.zt = 0 ), (select nvl(max(jyczsj),null) from im_tx_zhye where zh = v_yhzh) from im_zhsz a left join bm_sszh b on a.sszh=b.bm left join bm_yhbm c on c.bm = a.ssyh and c.jgbm like v_zxjgbm||'%' left join bm_im_zhlx d on a.zhlx = d.bm where a.yhzhhm = v_yhzh and a.jgbm = v_jgbm and a.ztbh = v_ztbh; end loop; close tmp_cur3; end if; if v_sfhz = '01' then insert into TMP_IM_ZJZHGL(id,sszh,sszhbm,ssyh,ssyhbm,yhzh,zhlx,zhlxbm,khyhmc,zhhm,zhhqje,zhdqje,wwcdbbs) select f_newid,max(sszh)||'合计',max(sszhbm),' ',' ',' ',' ',' ',' ',' ',sum(zhhqje),sum(zhdqje),sum(wwcdbbs) from TMP_IM_ZJZHGL group by sszhbm order by sszhbm; end if; p_sys_optlog_add(v_zxjgbm,' ',' ',' ','06','99','061026',' ','银行账户查询',0,0,v_userid,v_ywqd,v_msg); commit; v_msg:=v_yhzhs; v_ret:=0; exception when others then v_errorcode:=sqlcode; v_errormsg :=sqlerrm; rollback; insert into t_wa_sys_log_err(err_date,name_proc,err_code,err_msg) values(sysdate,'p_im_zjgl_yhzhzjgl_cx',v_errorcode,v_errormsg); v_msg:='查询失败'; v_ret:=99; commit; end;
5.游标入账
create table grdk_tx_hk_yh_gh_0920 as
select * from grdk_tx_hk_yh yh where exists
(select ywlsh from im_tx_zjcljg_zjb where ywlsh=yh.id and pcid in
(select pcid from im_tx_zjjsjy_zjb a where a.batchno in （'20180918100077703223','20180918100077703186','20180918100077703104','20180918100077703221')) and fse<>0)
and jkhtbh not in (select jkhtbh from grdk_dk_hkmx
where jzrq=to_date('20180918','yyyymmdd') and hkzt in ('02','03') and hktj='02' and gjjzqe=0);
create table grdk_tx_hk_yh_jh_0920 as
select * from grdk_tx_hk_yh yh where exists
(select ywlsh from im_tx_zjcljg_zjb where ywlsh=yh.id and pcid in
(select pcid from im_tx_zjjsjy_zjb a where a.batchno in （'VS201809180059015334','VS201809180059015443')) and fse<>0)
and jkhtbh not in (select jkhtbh from grdk_dk_hkmx
where jzrq=to_date('20180918','yyyymmdd') and hkzt in ('02','03') and hktj='02' and gjjzqe=0);
DECLARE
v_jkhtbh1 varchar(20):='';
v_yhbm1 varchar(20):='';
v_msg varchar2(200):='';
v_ret smallint:=0;
CURSOR CUR_htbh IS select max(jkhtbh),swtyhbm from grdk_tx_hk_yh_jh_0920 group by swtyhbm;
BEGIN
OPEN CUR_htbh;
LOOP
FETCH CUR_htbh INTO v_jkhtbh1,v_yhbm1;
EXIT WHEN CUR_htbh%NOTFOUND;
dbms_output.put_line(v_yhbm1);
p_hfb_jkr_gthk_rz(v_yhbm1,' ',' ','1','gt','02','1',v_msg,v_ret);
if v_ret > 0 then
dbms_output.put_line(v_msg);
dbms_output.put_line(v_ret);
rollback;
return;
end if;
--COMMIT;
END LOOP;
CLOSE CUR_htbh;
END;
DECLARE
v_jkhtbh1 varchar(20):='';
v_yhbm1 varchar(20):='';
v_msg varchar2(200):='';
v_ret smallint:=0;
CURSOR CUR_htbh IS select max(jkhtbh),swtyhbm from grdk_tx_hk_yh_gh_0920 group by swtyhbm;
BEGIN
OPEN CUR_htbh;
LOOP
FETCH CUR_htbh INTO v_jkhtbh1,v_yhbm1;
EXIT WHEN CUR_htbh%NOTFOUND;
dbms_output.put_line(v_yhbm1);
p_hfb_jkr_gthk_rz(v_yhbm1,' ',' ','1','gt','02','1',v_msg,v_ret);
if v_ret > 0 then
dbms_output.put_line(v_msg);
dbms_output.put_line(v_ret);
rollback;
return;
end if;
--COMMIT;
END LOOP;
CLOSE CUR_htbh;
END;
create or replace procedure p_hfb_jkr_gthk_rz (
--柜台还款入账
v_yhbm1 varchar, --银行编码
v_jkhtbh1 varchar, --借款合同编号
v_ywlsh varchar, --业务流水号
v_sftj int , --是否提交 0 不提交 1提交
v_blqd varchar, --办理渠道
v_hktj varchar, --还款途径 01 柜台还款 02批扣还款 03提前还款 04保证金代偿
v_userid int, --入账操作员代码
v_msg in out varchar , --返回信息
v_ret in out int -- 返回值 0成功99失败
)
--加密
as
v_zbyh varchar(100);
v_yhbm varchar(10):=v_yhbm1;--银行编码
v_jkhtbh varchar(30):=v_jkhtbh1;--借款合同编号
v_username varchar(100);
v_swtyhbm varchar(6); --受委托银行编码
v_jgbm varchar(50); --机构编码
v_grjgbm varchar(50); --机构编码
v_zxjgbm varchar(50); --中心机构编码
v_sum_hkbj decimal(18,2); --还款本金
v_sum_hklx decimal(18,2); --还款利息
v_sum_hkfx decimal(18,2); --还款罚息
v_sum_rs int:=0 ; --还款人数
v_ywlx varchar(2) ; --业务类型
v_pzbh varchar(20):=' ';
v_sum_hkje decimal(18,2);
v_sum_zqje decimal(18,2);
v_sum_zcbj decimal(18,2):=0;
v_sum_yqbj decimal(18,2):=0;
v_sum_zclx decimal(18,2):=0;
v_sum_yqlx decimal(18,2):=0;
v_sum_fx decimal(18,2):=0;
v_sum_dcje decimal(18,2):=0;
v_hkbj decimal(18,2);
--贷款参数
v_jkrxm varchar(100); --借款人姓名
v_dkll decimal(8,4); --当前贷款利率
v_dkhkfs varchar(10); --还款方式
v_dkye decimal(18, 2); --贷款余额
v_dqjhghje decimal(18, 2); --月还金额
v_dqjhghbj decimal(18, 2); --月还本金
v_dkqs smallint; --总期数
v_shqs smallint; --已还期次
v_dqyqcs smallint; --当前逾期期次
v_dqyqbj decimal(18,2); --当前逾期本金
v_bgnr varchar(8); --变更内容
v_yqkssj date; --逾期时间
v_f001 varchar(30); --合同编号
v_f002 varchar(30); --合同编号
v_id decimal(20); --唯一id号
v_f006 decimal(18, 2); --正常本金
v_f009 decimal(18, 2); --逾期本金
v_f007 decimal(18, 2); --正常利息
v_f018 decimal(18, 2); --逾期利息
v_f011 decimal(18, 2); --罚息
v_f039 decimal(18, 2); --违约金
v_f040 decimal(18, 2); --支取公积金
v_f040_new decimal(18, 2); --支取公积金
v_f026 varchar(10); --还款类别
v_f029 smallint; --当期还款是否全部还清
v_skzh varchar(40); --收款账号
v_zhye decimal(18,2); --账户余额
--提前还款变量定义
v_e065_1 decimal(18,2); --还本后月还金额
v_t1 decimal(18,2); --还本剩余本金
v_t2 smallint; --还本后剩余次数
v_e035 decimal(18,2); --剩余利息
v_bgywbh varchar(20); --变更业务流水号
v_tqhbfs smallint; --提前还本方式 0-期限不变，重新计算月还金额,1-月还金额不变,自动缩短期限
v_cnt smallint;
v_errorcode number;
v_errormsg varchar2(200);
v_pzid decimal(20):=0;
v_sfpz smallint:=0;--是否需要自动提凭证，全对冲的需要自动提凭证，有现金的不需要
v_value1 varchar(60);
--系统参数
v_ywsj date:=sysdate;
v_rzrq date:=trunc(sysdate); --入账日期
v_lxjsffxz smallint;
v_enddate date;
v_cwjgbm varchar2(200);
v_jkrzqe decimal(18,2):=0;
v_pozqe decimal(18,2):=0;
v_flag varchar2(1):='0';
v_a000_1 varchar(30); --个人账号
v_a000_2 varchar(30); --配偶账号
v_a044_1 decimal(12,2);--借款人公积金合计缴存余额
v_a044_2 decimal(12,2);--借款人配偶公积金合计缴存余额
v_ywzy varchar(100);
cursor tmp_yhcur is select a.swtyhbm from grdk_dk_hkqc a
where trim(a.ywlsh) is null and a.jkhtbh like v_jkhtbh and a.hkzt not in('04','05') and a.jgbm like v_zxjgbm||'%'
and ((exists(select p015 from hr_yg_sjqx_yh where p015=a.hkyhbm and userid= v_userid and qxlbbm='03')
or a.hkyhbm like (select nvl(min(p015),'%') from hr_yg_sjqx_yh where userid= v_userid and qxlbbm='03'))
and a.hkyhbm like v_yhbm )group by swtyhbm;
cursor tmp_cur is select a.jkhtbh,a.id from grdk_dk_hkqc a,grdk_dk_zz b
where a.jkhtbh=b.jkhtbh and b.jkhtbh like v_jkhtbh and b.swtyhbm = v_swtyhbm and trim(a.ywlsh) is null
and a.hkzt not in ('04','05') and hktj = v_hktj order by a.jkhtbh,a.hkqs,dqhksfqbhq;
begin
--获取操作员姓名
v_username:=f_hfsc_get_user_name(v_userid);
if trim(v_yhbm) is null then
v_yhbm:='%';
else
v_yhbm:=trim(v_yhbm)||'%';
end if;
if trim(v_jkhtbh) is null then
v_jkhtbh:='%';
else
v_jkhtbh:=trim(v_jkhtbh);
end if;
v_jgbm:=f_hfsc_get_user_jgbm(v_userid);
v_zxjgbm:=f_hfsc_get_user_zxjgbm(v_userid);
v_lxjsffxz:=f_hfsc_get_zxjg_lxjsff(v_zxjgbm);
v_enddate:=f_hfsc_get_enddate(v_userid)+1;
select nvl(sum(bjje+zczyqbjje+lxje+yqlxje+fxje+wyj),0),nvl(sum(gjjzqe),0),count(a.jkhtbh) into v_sum_hkje,v_sum_zqje,v_cnt
from grdk_dk_hkqc a,grdk_dk_zz b where a.jkhtbh=b.jkhtbh and b.jkhtbh like v_jkhtbh
and trim(a.ywlsh) is null and a.hkzt not in ('04','05')
and a.jgbm like v_zxjgbm||'%'
and ((exists(select p015 from hr_yg_sjqx_yh where p015=b.hkzhyhbm and userid= v_userid and qxlbbm='03') or
b.hkzhyhbm like (select nvl(min(p015),'%') from hr_yg_sjqx_yh where userid= v_userid and qxlbbm='03'))
and b.hkzhyhbm like '%'||substr(v_yhbm,3,2) );
if v_cnt =0 then
v_msg:='不存在未入账清册！';
v_ret:=99;
return;
end if ;
if v_sum_zqje>v_sum_hkje then
v_msg:= '支取金额大于还款金额！';
v_ret:= 99;
return;
end if;
select count(*) into v_cnt from grdk_dk_hkmx where jkhtbh=v_jkhtbh and ywfsrq=v_rzrq and dkywmxlx in ('07','08');
if v_cnt >0 and v_sftj=1 then
v_msg:='期限变更当天不允许做还款入账！';
v_ret:=99;
return;
end if ;
if v_sum_zqje=v_sum_hkje then
v_sfpz:=1;
end if;
delete from tmp_gd_zqhkrz;
delete from tmp_gd_zqhkrz1;
delete from tmp_gd_zqhkrz2;
--按委托银行循环
open tmp_yhcur;
loop
fetch tmp_yhcur into v_swtyhbm;
exit when tmp_yhcur%notfound;
--生成业务流水号
--if v_pzbh = ' ' then
if trim(v_ywlsh) is not null then
v_pzbh := v_ywlsh;
else
v_pzbh := f_hfsc_max_pzbh(v_jgbm,'0302');
end if;
select b.ssyh into v_zbyh from bm_yhbm a inner join im_zhsz b on a.yhbm=b.sszh where a.bm = v_swtyhbm
and zhlx='040101' and zhzt='01' and a.jgbm like v_zxjgbm||'%';
v_skzh :=f_hfsc_get_yhzh('040101',v_zbyh,1,v_userid);
--v_skzh :=f_hfsc_get_yhzh('040101',v_swtyhbm,1,v_userid);
--资金账户处理收入----
--正常还款(现金)
select nvl(sum(bjje+zczyqbjje),0),nvl(sum(lxje+yqlxje),0),nvl(sum(bjje+lxje+zczyqbjje+yqlxje+fxje),0)
into v_sum_hkbj ,v_sum_hklx,v_sum_hkje from grdk_dk_hkqc
where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='02' and gjjzqe=0 and hktj=v_hktj;
if v_sum_hkje<>0 then
v_ywlx:='02';
v_zhye:=0;
p_hfsc_ywdz(v_jgbm,'05',v_skzh,' ',v_ywlx,v_pzbh,2,v_sum_hkje, v_sum_hkbj,v_sum_hklx,0,0,0,'正常还款','01',v_rzrq,v_userid,v_zhye,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99 ;
return ;
end if;
end if;
--逾期还款(现金)
select nvl(sum(bjje+zczyqbjje),0),nvl(sum(lxje+yqlxje),0),nvl(sum(fxje),0),nvl(sum(bjje+lxje+zczyqbjje+yqlxje+fxje),0)
into v_sum_hkbj ,v_sum_hklx,v_sum_fx,v_sum_hkje from grdk_dk_hkqc
where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='03' and gjjzqe=0 and hktj=v_hktj;
if v_sum_hkje<>0 then
v_ywlx:='04';
v_zhye:=0;
p_hfsc_ywdz(v_jgbm,'05',v_skzh,' ',v_ywlx,v_pzbh,2,v_sum_hkje, v_sum_hkbj,v_sum_hklx,v_sum_fx,0,0,'逾期还款','01',v_rzrq,v_userid,v_zhye,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99 ;
return ;
end if;
end if;
--提前还款(现金)
select nvl(sum(bjje+zczyqbjje),0),nvl(sum(lxje+yqlxje),0),nvl(sum(fxje),0),nvl(sum(bjje+lxje+zczyqbjje+yqlxje+fxje),0)
into v_sum_hkbj ,v_sum_hklx,v_sum_hkfx,v_sum_hkje from grdk_dk_hkqc
where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='01' and gjjzqe=0 and hktj=v_hktj;
if v_sum_hkje<>0 then
v_ywlx:='03';
v_zhye:=0;
p_hfsc_ywdz(v_jgbm,'05',v_skzh,' ',v_ywlx,v_pzbh,2,v_sum_hkje, v_sum_hkbj,v_sum_hklx,v_sum_hkfx,0,0,'提前还款','01',v_rzrq,v_userid,v_zhye,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99 ;
return ;
end if;
end if;
select count(*) into v_cnt from grdk_dk_hkqc where jgbm like v_zxjgbm||'%' and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='01' and hktj=v_hktj ;
if v_cnt >0 then
select count(1)into v_cnt from (select 1 from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='01'and hktj=v_hktj group by jkhtbh);
select nvl(max(a.jkrxm),' ') into v_jkrxm
from grdk_sq_htxx a ,grdk_dk_hkqc b where a.jkhtbh=b.jkhtbh and b.jgbm like v_zxjgbm||'%'
and b.jkhtbh like v_jkhtbh and b.hkyhbm=v_swtyhbm and b.hkzt='01' and b.hktj=v_hktj ;
insert into grdk_dk_hkhz(id,dkywmxlx,bjje,lxje,fxje,fsrs,ywlsh,czbz,jgbm,ywzy,swtyhbm,fhrq,jzrq,jzsj,fhsj,fhczyid,jzczyid,fhczy,jzczy,fhqd,jzqd,hkzt)
select f_newid,'03',nvl(sum(bjje+zczyqbjje),0),nvl(sum(lxje+yqlxje),0),nvl(sum(fxje),0),v_cnt,v_pzbh,'01',v_jgbm,v_jkrxm||max(ywzy),v_swtyhbm,
v_rzrq,v_rzrq,v_ywsj,v_ywsj,v_userid,v_userid,v_username,v_username,v_blqd,v_blqd,'01'
from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='01' and hktj=v_hktj ;
end if;
select count(*) into v_cnt from grdk_dk_hkqc where jgbm like v_zxjgbm||'%' and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='02' and hktj=v_hktj ;
if v_cnt>0 then
select count(1)into v_cnt from (select 1 from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='02' and hktj=v_hktj group by jkhtbh);
select nvl(max(jkrxm),' ') into v_jkrxm
from grdk_sq_htxx where jkhtbh=(select max(jkhtbh) from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='02' and hktj=v_hktj) ;
if v_cnt=1 then
v_ywzy:=v_jkrxm||'正常还款';
else
v_ywzy:=v_jkrxm||'等'||v_cnt||'人正常还款';
end if;
insert into grdk_dk_hkhz(id,dkywmxlx,bjje,lxje,fxje,fsrs,ywlsh,czbz,jgbm,ywzy,swtyhbm,fhrq,jzrq,jzsj,fhsj,fhczyid,jzczyid,fhczy,jzczy,fhqd,jzqd,hkzt)
select f_newid,'02',nvl(sum(bjje+zczyqbjje),0),nvl(sum(lxje+yqlxje),0),nvl(sum(fxje),0),v_cnt,v_pzbh,'01',v_jgbm,v_ywzy,v_swtyhbm,
v_rzrq,v_rzrq,v_ywsj,v_ywsj,v_userid,v_userid,v_username,v_username,v_blqd,v_blqd,'02'
from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='02' and hktj=v_hktj ;
end if;
select count(*) into v_cnt from grdk_dk_hkqc where jgbm like v_zxjgbm||'%' and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='03' and hktj=v_hktj ;
if v_cnt >0 then
select count(1)into v_cnt from (select 1 from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='03' and hktj=v_hktj group by jkhtbh);
select nvl(max(jkrxm),' ') into v_jkrxm
from grdk_sq_htxx where jkhtbh=(select max(jkhtbh) from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='03' and hktj=v_hktj) ;
if v_cnt=1 then
v_ywzy:=v_jkrxm||'逾期还款';
else
v_ywzy:=v_jkrxm||'等'||v_cnt||'人逾期还款';
end if;
insert into grdk_dk_hkhz(id,dkywmxlx,bjje,lxje,fxje,fsrs,ywlsh,czbz,jgbm,ywzy,swtyhbm,fhrq,jzrq,jzsj,fhsj,fhczyid,jzczyid,fhczy,jzczy,fhqd,jzqd,hkzt)
select f_newid,'04',nvl(sum(bjje+zczyqbjje),0),nvl(sum(lxje+yqlxje),0),nvl(sum(fxje),0),v_cnt,v_pzbh,'01',v_jgbm,v_ywzy,v_swtyhbm,
v_rzrq,v_rzrq,v_ywsj,v_ywsj,v_userid,v_userid,v_username,v_username,v_blqd,v_blqd,'03'
from grdk_dk_hkqc where jgbm like v_zxjgbm||'%'
and jkhtbh like v_jkhtbh and hkyhbm=v_swtyhbm and hkzt='03' and hktj=v_hktj ;
end if;
v_sum_zcbj:=0;
v_sum_yqbj:=0;
v_sum_zclx:=0;
v_sum_yqlx:=0;
v_sum_fx:=0;
v_sum_dcje:=0;
delete from tmp_gd_zqhkrz;
open tmp_cur;
loop
fetch tmp_cur into v_f001,v_id;
exit when tmp_cur%notfound;
select b.jkrxm,a.dkll,a.dkhkfs,a.dkye,a.dkqs,a.shqs,a.dqyqbj,a.bgnr,a.dqjhhkje,a.dqjhghbj,a.tqhbfs,a.bgywbh,a.dqyqcs,a.jgbm
into v_jkrxm,v_dkll,v_dkhkfs,v_dkye,v_dkqs,v_shqs,v_dqyqbj,v_bgnr,v_dqjhghje,v_dqjhghbj,v_tqhbfs,v_bgywbh,v_dqyqcs,v_cwjgbm
from grdk_dk_zz a
inner join grdk_sq_htxx b on b.jkhtbh = a.jkhtbh where a.jkhtbh = v_f001;
select hkqs,bjje,zczyqbjje,lxje,yqlxje,hkzt,dqhksfqbhq,fxje,wyj,gjjzqe
into v_f002,v_f006 , v_f009,v_f007,v_f018,v_f026,v_f029,v_f011,v_f039,v_f040
from grdk_dk_hkqc where jkhtbh = v_f001 and id = v_id;
--v_f040_new := v_f040 ;
select count(1) into v_cnt from grdk_dk_hkqc where jkhtbh = v_f001 and hkzt='01';
if v_cnt=0 then
if v_f040>0 and v_f006+v_f009+v_f007+v_f018+v_f011+v_f039<>v_f040 then
v_errorcode:= 0;
v_errormsg :='合同['||v_f001||']支取金额不等于本息合计！';
rollback ;
v_ret:=99;
v_msg:='合同['||v_f001||']支取金额不等于本息合计！';
insert into t_wa_sys_log_err (err_date,name_proc,err_code,err_msg)
values(sysdate,'p_hfb_jkr_gthk_rz',v_errorcode,v_errormsg);
commit;
return;
end if;
end if;
p_hfsc_ywdz(v_jgbm,'04',v_f001,' ',case when v_f026='01' then '03' when v_f026='03' then '04' else v_f026 end,v_pzbh,2,v_f007+v_f018+v_f011,0,v_f007+v_f018,v_f011,0,0,'计算利息和罚息','01',v_rzrq,v_userid,v_zhye,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99 ;
return ;
end if;
--提前还款处理
if v_f026='01' then
v_zhye:=0;
if v_f006+v_f009+v_f007+v_f018+v_f011 >0 then
p_hfsc_ywdz(v_jgbm,'04',v_f001,' ','03',v_pzbh,1,v_f006+v_f009+v_f007+v_f018+v_f011,v_f006+v_f009,v_f007+v_f018,v_f011,0,0,'提前还款','01',v_rzrq,v_userid,v_zhye,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99 ;
return ;
end if;
end if;
--通用版
update grdk_dk_zz set hsbjze= hsbjze + v_f006, hslxze = hslxze + v_f007,
dkye = dkye - v_f006,bgnr=' ',dqyhje=greatest(0,dqyhje-v_f006-v_f009-v_f007-v_f018-v_f011),
dqyhbj=greatest(0,dqyhbj-v_f006-v_f009),dqyhlx=greatest(0,dqyhlx-v_f007-v_f018),dqyhfx=greatest(0,dqyhfx-v_f011) where jkhtbh = v_f001;
update grdk_sq_htxx set dkye=dkye-v_f006 where jkhtbh=v_f001;
update grdk_dk_bg set sxbz=1 where ywlsh=v_bgywbh;
v_hkbj:=0;
if v_f029=1 then
select nvl(sum(bjje),0) into v_hkbj from grdk_dk_hkqc where jkhtbh=v_f001 and zqywlsh=v_bgywbh and hkzt='01' and dqhksfqbhq='1';
if v_hkbj<>v_dkye then
select dkye into v_dkye from grdk_dk_zz where jkhtbh=v_f001;
update grdk_dk_zz set tqhbqzdyhje=decode(dkhkfs,'01',dqjhhkje,'02',dqjhghbj) where jkhtbh=v_f001;--等额本息用e065,等额本金用e066
if v_tqhbfs=0 then
v_t1 := v_dkye;
v_t2 := v_dkqs - v_shqs-v_dqyqcs;
select f_hfsc_get_yhjecalc(v_dkhkfs,v_t1,v_t2,v_dkll) into v_e065_1 from dual;
select f_hfsc_get_hklxcalc(v_dkhkfs,v_t1,v_t2,v_dkll,v_e065_1) into v_e035 from dual;
update grdk_dk_zz set dqzlx = dqzlx+v_e035,lxye=v_e035,dqjhhkje = v_e065_1 where jkhtbh = v_f001;
update grdk_sq_htxx a set (a.gjjyhzlx,a.gjjyhbx)=(select dqzlx,dqjhhkje from grdk_dk_zz where jkhtbh=v_f001) where exists (select a.jkhtbh from grdk_dk_zz where jkhtbh=v_f001 and jkhtbh=a.jkhtbh ) ;
if v_dkhkfs in('02','06') then
update grdk_dk_zz set dqjhghbj = v_e065_1 where jkhtbh = v_f001;
end if;
else
update grdk_dk_zz set dkqs=shqs+f_hfsc_jkr_sycscalc(dkhkfs,dkye,dkll,dqjhhkje,dqjhghbj) where jkhtbh = v_f001;
update grdk_dk_zz set yjqrq=f_hfsc_get_dateadd(dkffrq,ydhkr,dkqs) where jkhtbh=v_f001;
update grdk_dk_zz set lxye=f_hfsc_get_hklxcalc(dkhkfs,dkye,dkqs-shqs,dkll,decode(dkhkfs,'01',dqjhhkje,dqjhghbj)) where jkhtbh = v_f001;
update grdk_dk_zz set dqzlx=hslxze+lxye where jkhtbh = v_f001;
update grdk_sq_htxx a set a.gjjyhzlx=(select dqzlx from grdk_dk_zz where jkhtbh=v_f001)
where exists (select a.jkhtbh from grdk_dk_zz where jkhtbh=v_f001 and jkhtbh=a.jkhtbh ) ;
end if;
else
update grdk_dk_zz set dkye=0,lxye=0,dkzt='00',dkjqrq=trunc(sysdate) where jkhtbh=v_f001;
update grdk_sq_htxx set dkye=0 where jkhtbh=v_f001;
end if;
else
select dkye into v_dkye from grdk_dk_zz where jkhtbh=v_f001;
if v_dkye=0 then
update grdk_dk_zz set dkye=0,lxye=0,dkzt='00',dkjqrq=trunc(sysdate) where jkhtbh=v_f001;
update grdk_sq_htxx set dkye=0 where jkhtbh=v_f001;
else
if v_tqhbfs=0 then
v_t1 := v_dkye;
v_t2 := v_dkqs - v_shqs-v_dqyqcs;
select f_hfsc_get_yhjecalc(v_dkhkfs,v_t1,v_t2,v_dkll) into v_e065_1 from dual;
select f_hfsc_get_hklxcalc(v_dkhkfs,v_t1,v_t2,v_dkll,v_e065_1) into v_e035 from dual;
update grdk_dk_zz set dqzlx = dqzlx+v_e035,lxye=v_e035,dqjhhkje = v_e065_1 where jkhtbh = v_f001;
update grdk_sq_htxx a set (a.gjjyhzlx,a.gjjyhbx)=(select dqzlx,dqjhhkje from grdk_dk_zz where jkhtbh=v_f001) where exists (select a.jkhtbh from grdk_dk_zz where jkhtbh=v_f001 and jkhtbh=a.jkhtbh ) ;
if v_dkhkfs in('02','06') then
update grdk_dk_zz set dqjhghbj = v_e065_1 where jkhtbh = v_f001;
end if;
else
update grdk_dk_zz set dkqs=shqs+f_hfsc_jkr_sycscalc(dkhkfs,dkye,dkll,dqjhhkje,dqjhghbj) where jkhtbh = v_f001;
update grdk_dk_zz set yjqrq=f_hfsc_get_dateadd(dkffrq,ydhkr,dkqs) where jkhtbh=v_f001;
update grdk_dk_zz set lxye=f_hfsc_get_hklxcalc(dkhkfs,dkye,dkqs-shqs,dkll,decode(dkhkfs,'01',dqjhhkje,dqjhghbj)) where jkhtbh = v_f001;
update grdk_dk_zz set dqzlx=hslxze+lxye where jkhtbh = v_f001;
update grdk_sq_htxx a set a.gjjyhzlx=(select dqzlx from grdk_dk_zz where jkhtbh=v_f001)
where exists (select a.jkhtbh from grdk_dk_zz where jkhtbh=v_f001 and jkhtbh=a.jkhtbh ) ;
end if;
end if;
end if;
else
v_zhye:=0;
if v_f026 <>'02' then
p_hfsc_ywdz(v_jgbm,'04',v_f001,' ','04',v_pzbh,1,v_f006+v_f009+v_f007+v_f018+v_f011,v_f006+v_f009,v_f007+v_f018,v_f011,0,0,'逾期还款','01',v_rzrq,v_userid,v_zhye,v_msg);
else
p_hfsc_ywdz(v_jgbm,'04',v_f001,' ','02',v_pzbh,1,v_f006+v_f009+v_f007+v_f018+v_f011,v_f006+v_f009,v_f007+v_f018,v_f011,0,0,'正常还款','01',v_rzrq,v_userid,v_zhye,v_msg);
end if;
if trim(v_msg) is not null then
rollback ;
v_msg:=v_msg;
v_ret:=99 ;
return ;
end if;
update grdk_dk_zz set hsbjze=hsbjze+v_f006+v_f009,hslxze=hslxze+v_f007+v_f018, dkye = dkye - v_f006 - v_f009,yqfx=yqfx+v_f011,
lxye = lxye - v_f007 - v_f018, shqs = shqs + v_f029,
dqyqcs = dqyqcs -(case when v_f026='03' then v_f029 else 0 end), dqyqbj = dqyqbj - v_f009,
dqyqlx = dqyqlx - v_f018,
yhkrq = (case when v_f026<>'03' then f_hfsc_get_dateadd(yhkrq,ydhkr,v_f029) else yhkrq end),
dqyhje=greatest(0,dqyhje-v_f006-v_f009-v_f007-v_f018-v_f011),
dqyhbj=greatest(0,dqyhbj-v_f006-v_f009),dqyhlx=greatest(0,dqyhlx-v_f007-v_f018),dqyhfx=greatest(0,dqyhfx-v_f011) where jkhtbh = v_f001;
update grdk_sq_htxx set dkye=dkye-v_f006-v_f009 where jkhtbh=v_f001;
if v_f026='03' then
v_cnt:=0;
select count(jkhtbh),min(yhkrq) into v_cnt,v_yqkssj from grdk_dk_hkqc where jkhtbh=v_f001 and hkzt in ('03','04') and id<>v_id /*and dkywmxlx<>'70'*/;
if v_cnt=0 then
update grdk_dk_zz set dkzt = '02',dqyqbj=0,dqyqlx=0,dqyqcs=0 where jkhtbh =v_f001;
else
update grdk_dk_zz set yqkssj = v_yqkssj where jkhtbh =v_f001;
end if;
end if;
if v_f026='01' then
v_cnt:=0;
select count(jkhtbh),min(yhkrq) into v_cnt,v_yqkssj from grdk_dk_hkqc where jkhtbh=v_f001 and hkzt in ('03','04') and id<>v_id /*and dkywmxlx<>'70'*/;
if v_cnt=0 then
update grdk_dk_zz set dkzt = '02',dqyqbj=0,dqyqlx=0,dqyqcs=0 where jkhtbh =v_f001;
else
update grdk_dk_zz set yqkssj = v_yqkssj where jkhtbh =v_f001;
end if;
update grdk_dk_zz set wzqhkbj=0,wzqhklx=0 where jkhtbh =v_f001;
end if;
if v_f026 = '02' then
if v_f029 = 0 then
update grdk_dk_zz set wzqhkbj = wzqhkbj + v_f006, wzqhklx = wzqhklx + v_f007 where jkhtbh = v_f001;
else
update grdk_dk_zz set wzqhkbj = 0, wzqhklx = 0 where jkhtbh = v_f001 ;
end if;
end if;
end if;
--更新贷款状态
--贷款结清处理
select dkye into v_dkye from grdk_dk_zz where jkhtbh=v_f001;
if v_dkye<=0 then
update grdk_dk_zz set dkzt = '00', dkjqrq=trunc(v_ywsj) , dkye=0 , lxye=0 where jkhtbh = v_f001;
update grdk_sq_htxx set dkye=0 where jkhtbh=v_f001;
insert into grdk_sq_dbxx_del (id, dbhtbh, jkhtbh, dkdblx, dbjgmc, dywqzh, dywtxqzh, dywfwzl, dyqjlrq, dyqjcrq, dywpgjz, bzhtbh, bzjgmc, dkbzj, fhbzjrq, zyhtbh, zywbh, zywmc, zywjz, zyhtksrq, zyhtjsrq, grbh, jgbm, dbjgbm, dywlb, dywmj, dyfwxz, dywsyr, dywsyrzjhm, dywsyryddh, dywsyrdwmc, dywsyrtxdz, dywsyrpo, dywsyrpozjhm, dywsyrpoyddh, dywsyrpodwmc, ygdjzh, dbyj, dbrgrbh, dbrgjjzh, dbrzjhm, dbrdwmc, dbrgzsr, dbrycje, dbrdwdh, dbryddh, dbrtxdz, yjkrgx, dbrpw, dbbl, dbje, dbnx, kssj, jssj, lrqd, lrczyid, lrczy, lrsj, wf_pzid, wf_processunid, dzdah, zyr, zyrzjhm, zyrycje, zyrdwmc, dbrxm, zyrgjjzh, zyrgrbh, bdcqz, bdcqzm , sjtj)
select f_newid, dbhtbh, jkhtbh, dkdblx, dbjgmc, dywqzh, dywtxqzh, dywfwzl, dyqjlrq, dyqjcrq, dywpgjz, bzhtbh, bzjgmc, dkbzj, fhbzjrq, zyhtbh, zywbh, zywmc, zywjz, zyhtksrq, zyhtjsrq, grbh, jgbm, dbjgbm, dywlb, dywmj, dyfwxz, dywsyr, dywsyrzjhm, dywsyryddh, dywsyrdwmc, dywsyrtxdz, dywsyrpo, dywsyrpozjhm, dywsyrpoyddh, dywsyrpodwmc, ygdjzh, dbyj, dbrgrbh, dbrgjjzh, dbrzjhm, dbrdwmc, dbrgzsr, dbrycje, dbrdwdh, dbryddh, dbrtxdz, yjkrgx, dbrpw, dbbl, dbje, dbnx, kssj, jssj, lrqd, lrczyid, lrczy, lrsj, wf_pzid, wf_processunid, dzdah, zyr, zyrzjhm, zyrycje, zyrdwmc, dbrxm, zyrgjjzh, zyrgrbh, bdcqz, bdcqzm , sjtj+2 from grdk_sq_dbxx where jkhtbh=v_f001;
--删除担保材料
delete from grdk_sq_dbxx where jkhtbh=v_f001;
end if;
select gjjzqe into v_f040_new from grdk_dk_hkqc where jkhtbh = v_f001 and id = v_id;
if v_f040_new>0 then
select count(1) into v_cnt from grdk_dk_hkqc where jkhtbh = v_f001 and hkzt='01';
if v_cnt=0 then
if v_flag='0' then
--正常还款逾期还款
select nvl(max(jkrgjjzh),'') into v_a000_1 from grdk_sq_htxx where jkhtbh = v_f001 and dchd=1;
select nvl(max(gtjkrgjjzh),'') into v_a000_2 from grdk_sq_gtjkr where jkhtbh = v_f001 and jkrgx = '2' and dchd=1;
if trim(v_a000_1) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_1,v_f001,v_a044_1);
else
v_a044_1 := 0;
end if;
if trim(v_a000_2) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_2,v_f001,v_a044_2);
else
v_a044_2 := 0;
end if;
v_flag:='1';
end if;
if v_a044_1>v_f040_new then
v_jkrzqe:=v_f040_new;
v_a044_1:=v_a044_1-v_f040_new;
v_f040_new:=0;
else
v_jkrzqe:=v_a044_1;
v_a044_1:=0;
v_f040_new:=v_f040_new-v_a044_1;
end if;
if v_f040_new>0 and v_a044_2>0then
if v_a044_2>v_f040_new then
v_pozqe:=v_f040_new;
v_a044_2:=v_a044_2-v_f040_new;
else
v_pozqe:=v_a044_2;
v_a044_2:=0;
v_f040_new:=v_f040_new-v_a044_2;
end if;
end if;
else
--提前还款支取公积金
select nvl(jkrzqje,0),nvl(pozqje,0)
into v_jkrzqe,v_pozqe from grdk_dk_bg where jkhtbh=v_f001 and ywlsh=v_bgywbh;
end if;
end if;
insert into grdk_dk_hkmx(id,dkzh,ywlsh,dkywmxlx,ywfsrq,dkyhdm,fse,bjje,lxje,fxje,dqqc,zczyqbjje,yqzzcbjje,jzrq,grbh,jkhtbh,hkqs,bxhj,yqlxje,whfx,yqts,gjjzqe,wyj,hkqdkye,byqk,zyrq ,yqksrq,yhkrq,fxll,yslx,dqhksfqbhq,lrqcsj,zyywlsh,zyqdkzt,pksjrq,pkpcid,zqywlsh,hkzh,kkzhkhmc,kkzhkhzjhm,jgbm,swtyhbm,hkyhbm,lrczy,zyqczy,hkrzczy,lrczyid,zyqczyid,hkrzczyid,yhhkzh,lrblqd,rzblqd,zyblqd,hkzt,bgid,hktj,shrq,jkrzqe,pozqe)
select f_newid,dkzh,v_pzbh,dkywmxlx,trunc(v_ywsj),dkyhdm,fse,bjje,lxje,fxje,dqqc,zczyqbjje,yqzzcbjje,v_rzrq,grbh,jkhtbh,hkqs,bxhj,yqlxje,whfx,yqts,gjjzqe,wyj,v_dkye+bjje+zczyqbjje,byqk,zyrq ,yqksrq,yhkrq,fxll,yslx,dqhksfqbhq,lrqcsj,zyywlsh,zyqdkzt,pksjrq,pkpcid,zqywlsh,hkzh,kkzhkhmc,kkzhkhzjhm,jgbm,swtyhbm,hkyhbm,lrczy,zyqczy,v_username,lrczyid,zyqczyid,v_userid,yhhkzh,lrblqd,v_blqd,zyblqd,hkzt,bgid,hktj,shrq,v_jkrzqe,v_pozqe from grdk_dk_hkqc where jkhtbh = v_f001 and id = v_id;
delete from grdk_dk_hkqc where jkhtbh = v_f001 and id = v_id;
if v_f040<>0 then
insert into tmp_gd_zqhkrz(f001,f040,f017,e093,a000)
values(v_f001,v_f040,v_pzbh,(case when v_bgnr='01' then v_bgywbh else ' ' end),v_f002);
end if;
v_sum_zcbj:=v_sum_zcbj+v_f006;
v_sum_yqbj:=v_sum_yqbj+v_f009;
v_sum_zclx:=v_sum_zclx+v_f007;
v_sum_yqlx:=v_sum_yqlx+v_f018;
v_sum_fx:=v_sum_fx+v_f011;
v_sum_dcje:=v_sum_dcje+v_f040;
--更新对冲表数据
update grdk_zqjl_zd set cgbz = 1 where id = v_id;
end loop;
close tmp_cur;
v_cnt:=0;
select count(f001) into v_cnt from tmp_gd_zqhkrz ;
if v_cnt>0 then
declare
v_e001 varchar(30); --合同编号
v_a000_1 varchar(30); --个人账号
v_a000_2 varchar(30); --配偶账号
v_a000_3 varchar(30); --共同还款人1账号
v_a000_4 varchar(30); --共同还款人2账号
v_a000_5 varchar(30); --共同还款人3账号
v_a044_1 decimal(12,2);--借款人公积金合计缴存余额
v_a044_2 decimal(12,2);--借款人配偶公积金合计缴存余额
v_a044_3 decimal(12,2);--共同还款人1公积金合计缴存余额
v_a044_4 decimal(12,2);--共同还款人2公积金合计缴存余额
v_a044_5 decimal(12,2); --共同还款人3公积金合计缴存余额
v_ydje_1 decimal(12,2);--借款人公积金约定支取
v_ydje_2 decimal(12,2); --借款人配偶公积金约定支取
v_ydje_3 decimal(12,2); --共同还款人1公积金约定支取
v_ydje_4 decimal(12,2); --共同还款人2公积金约定支取
v_ydje_5 decimal(12,2); --共同还款人3公积金约定支取
v_sfzqsn varchar(60); --支取参数
v_f017 varchar(20); --还款流水号
v_bm varchar(20); --支取单位编码
v_hkje decimal(12,2); --还款金额
v_zqje decimal(12,2); --支取金额
v_a000 varchar(30); --个人账号
v_d011 decimal(12,2):=0; --支取上年金额
v_d012 decimal(12,2):=0; --支取本年金额
v_zqbh varchar(20); --支取编号
v_hkzt varchar(2); --还款状态
v_dkzt varchar(2); --贷款状态
v_dwzh varchar(20);
v_xzbm varchar(6);
v_dwmc varchar2(200);
v_dwzhye decimal(18,2) ;
v_grzhye decimal(12,2);
v_zckye decimal(18,2);
v_zqyhbm varchar(6);
cursor hkzq_cur is select f001,sum(f040) hkje,max(f017) f017,max(e093) e093 from tmp_gd_zqhkrz group by f001;
cursor zqdw_cur is select bm,f017 from tmp_gd_zqhkrz2;
cursor zq_cur is select distinct max(f001),sum(d008),max(a000) from tmp_gd_zqhkrz1 where bm=v_bm and f017=v_f017 group by bm,f017,a000;
cursor zqqc_cur is select f040 from tmp_gd_zqhkrz where f001=v_e001 order by a000;
begin
open hkzq_cur;
loop
fetch hkzq_cur into v_e001,v_hkje,v_f017,v_bgywbh;
exit when hkzq_cur%notfound;
delete from tmp_gd_zqhkrz1;
if trim(v_bgywbh) is null then
--按月提取公积金偿还贷款
select nvl(max(jkrgjjzh),'') into v_a000_1 from grdk_sq_htxx where jkhtbh = v_e001 and dchd=1;
select nvl(max(gtjkrgjjzh),'') into v_a000_2 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '2' and dchd=1;
select nvl(max(gtjkrgjjzh),'') into v_a000_3 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '3' and dchd=1;
select nvl(max(gtjkrgjjzh),'') into v_a000_4 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '4' and dchd=1;
select nvl(max(gtjkrgjjzh),'') into v_a000_5 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '5' and dchd=1;
if trim(v_a000_1) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_1,v_e001,v_a044_1);
else
v_a044_1 := 0;
end if;
if trim(v_a000_2) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_2,v_e001,v_a044_2);
else
v_a044_2 := 0;
end if;
if trim(v_a000_3) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_3,v_e001,v_a044_3);
else
v_a044_3 := 0;
end if;
if trim(v_a000_4) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_4,v_e001,v_a044_4);
else
v_a044_4 := 0;
end if;
if trim(v_a000_5) is not null then
p_hfb_jkr_hkzq_zqecalc(v_a000_5,v_e001,v_a044_5);
else
v_a044_5 := 0;
end if;
select nvl(max(trim(value1)),'0') into v_value1 from bm_xtcs where bm3='03020003' and bm='11' and bm1=v_zxjgbm and sfqy='1';
if v_a044_1+v_a044_2+v_a044_3+v_a044_4+v_a044_5<v_hkje then
rollback;
v_msg:= '合同['||v_e001||']提取时余额不足！';
v_ret:= 99;
return ;
/*else
if v_value1 = '1' and v_a044_1<v_hkje and v_a044_2<v_hkje and v_a044_3<v_hkje and v_a044_4<v_hkje and v_a044_5<v_hkje then
rollback;
v_msg:= '合同['||v_e001||']提取时余额不足，已启用参数月对冲需要单人足额扣款！';
v_ret:= 99;
return ;
end if;*/
end if;
open zqqc_cur;
loop
fetch zqqc_cur into v_f040;
exit when zqqc_cur%notfound;
--按照借款人、配偶、共同还款还款的顺序支取
if v_f040>0 then
if v_a044_1>=v_f040 then
v_zqje:=v_f040;
else
if v_value1 = '1' then
v_zqje:=0;
else
v_zqje:=v_a044_1;
end if;
end if;
if v_zqje>0 then
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_1;
v_f040:=v_f040-v_zqje;
v_a044_1:=v_a044_1-v_zqje;
end if;
end if;
if v_f040>0 then
if v_a044_2>=v_f040 then
v_zqje:=v_f040;
else
if v_value1 = '1' then
v_zqje:=0;
else
v_zqje:=v_a044_2;
end if;
end if;
if v_zqje>0 then
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_2;
v_f040:=v_f040-v_zqje;
v_a044_2:=v_a044_2-v_zqje;
end if;
end if;
if v_f040>0 then
if v_a044_3>=v_f040 then
v_zqje:=v_f040;
else
if v_value1 = '1' then
v_zqje:=0;
else
v_zqje:=v_a044_3;
end if;
end if;
if v_zqje>0 then
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_3;
v_f040:=v_f040-v_zqje;
v_a044_3:=v_a044_3-v_zqje;
end if;
end if;
if v_f040>0 then
if v_a044_4>=v_f040 then
v_zqje:=v_f040;
else
if v_value1 = '1' then
v_zqje:=0;
else
v_zqje:=v_a044_4;
end if;
end if;
if v_zqje>0 then
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_4;
v_f040:=v_f040-v_zqje;
v_a044_4:=v_a044_4-v_zqje;
end if;
end if;
if v_f040>0 then
if v_a044_5>=v_f040 then
v_zqje:=v_f040;
else
if v_value1 = '1' then
v_zqje:=0;
else
v_zqje:=v_a044_5;
end if;
end if;
if v_zqje>0 then
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_5;
v_f040:=v_f040-v_zqje;
v_a044_5:=v_a044_5-v_zqje;
end if;
end if;
--如何还存在还款金额，提示存储存盘，返回
if v_f040>0 then
rollback;
v_msg:= '提取金额大于还款金额' ;
v_ret:= 99;
return ;
end if;
end loop;
close zqqc_cur;
else
select nvl(max(jkrgjjzh),'') into v_a000_1 from grdk_sq_htxx where jkhtbh = v_e001 ;
select nvl(max(gtjkrgjjzh),'') into v_a000_2 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '2' ;
select nvl(max(gtjkrgjjzh),'') into v_a000_3 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '3';
select nvl(max(gtjkrgjjzh),'') into v_a000_4 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '4';
select nvl(max(gtjkrgjjzh),'') into v_a000_5 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '5';
if trim(v_a000_1) is not null then
p_hfb_jkr_hkzq_zqecalc_tqhk(v_a000_1,v_e001,v_a044_1);
else
v_a044_1 := 0;
end if;
if trim(v_a000_2) is not null then
p_hfb_jkr_hkzq_zqecalc_tqhk(v_a000_2,v_e001,v_a044_2);
else
v_a044_2 := 0;
end if;
if trim(v_a000_3) is not null then
p_hfb_jkr_hkzq_zqecalc_tqhk(v_a000_3,v_e001,v_a044_3);
else
v_a044_3 := 0;
end if;
if trim(v_a000_4) is not null then
p_hfb_jkr_hkzq_zqecalc_tqhk(v_a000_4,v_e001,v_a044_4);
else
v_a044_4 := 0;
end if;
if trim(v_a000_5) is not null then
p_hfb_jkr_hkzq_zqecalc_tqhk(v_a000_5,v_e001,v_a044_5);
else
v_a044_5 := 0;
end if;
--提前还款支取公积金
select nvl(jkrzqje,0),nvl(pozqje,0),nvl(gthkr1zqje,0),nvl(gthkr2zqje,0),nvl(gthkr3zqje,0)
into v_ydje_1,v_ydje_2,v_ydje_3,v_ydje_4,v_ydje_5 from grdk_dk_bg where jkhtbh=v_e001 and ywlsh=v_bgywbh;
if v_ydje_1>0 then
--select nvl(max(jkrgjjzh),'') into v_a000_1 from grdk_sq_htxx where jkhtbh = v_e001 ;
--select nvl(max(grzhye),0) into v_a044_1 from gjzf_gr_zz where grzh=v_a000_1 and zhsfdj =0 and grzhzt in('01','02');
if v_ydje_1>v_a044_1 then
rollback;
v_msg:= '借款人提取金额大于可提取金额' ;
v_ret:= 99;
return ;
end if;
end if;
if v_ydje_2>0 then
--select nvl(max(gtjkrgjjzh),'') into v_a000_2 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '2';
--select nvl(max(grzhye),0) into v_a044_2 from gjzf_gr_zz where grzh=v_a000_2 and zhsfdj=0 and grzhzt in('01','02');
if v_ydje_2>v_a044_2 then
rollback;
v_msg:= '配偶提取金额大于可提取金额' ;
v_ret:= 99;
return ;
end if;
end if;
if v_ydje_3>0 then
--select nvl(max(gtjkrgjjzh),'') into v_a000_3 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '3';
--select nvl(max(grzhye),0) into v_a044_3 from gjzf_gr_zz where grzh=v_a000_3 and zhsfdj=0 and grzhzt in('01','02');
if v_ydje_3>v_a044_3 then
rollback;
v_msg:= '共同借款人1提取金额大于可提取金额' ;
v_ret:= 99;
return ;
end if;
end if;
if v_ydje_4>0 then
--select nvl(max(gtjkrgjjzh),'') into v_a000_4 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '4';
--select nvl(max(grzhye),0) into v_a044_4 from gjzf_gr_zz where grzh=v_a000_4 and zhsfdj=0 and grzhzt in('01','02');
if v_ydje_4>v_a044_4 then
rollback;
v_msg:= '共同借款人2提取金额大于可提取金额' ;
v_ret:= 99;
return ;
end if;
end if;
if v_ydje_5>0 then
--select nvl(max(gtjkrgjjzh),'') into v_a000_5 from grdk_sq_gtjkr where jkhtbh = v_e001 and jkrgx = '5';
--select nvl(max(grzhye),0) into v_a044_5 from gjzf_gr_zz where grzh=v_a000_5 and zhsfdj=0 and grzhzt in('01','02');
if v_ydje_5>v_a044_5 then
rollback;
v_msg:= '共同借款人3提取金额大于可提取金额' ;
v_ret:= 99;
return ;
end if;
end if;
if v_ydje_1>0 then
v_zqje:=v_ydje_1;
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_1;
end if;
if v_ydje_2>0 then
v_zqje:=v_ydje_2;
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_2;
end if;
if v_ydje_3>0 then
v_zqje:=v_ydje_3;
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_3;
end if;
if v_ydje_4>0 then
v_zqje:=v_ydje_4;
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_4;
end if;
if v_ydje_5>0 then
v_zqje:=v_ydje_5;
insert into tmp_gd_zqhkrz1(bm,f001,d008,a000,f017) select dwzh,v_e001,v_zqje,grzh,v_f017 from gjzf_gr_zz where grzh=v_a000_5;
end if;
end if;
delete from tmp_gd_zqhkrz2;
insert into tmp_gd_zqhkrz2 select bm,sum(d008) zqje,count(*) rs,f017,' ' from tmp_gd_zqhkrz1 group by bm,f017;
v_zqbh := f_hfsc_max_pzbh(v_jgbm,'0301');
open zqdw_cur;
loop
fetch zqdw_cur into v_bm,v_f017;
exit when zqdw_cur%notfound;
v_sum_rs:=0;
v_sum_zqje:=0;
select nvl(yhbm,' ') into v_zqyhbm from gjzf_dw_zz where dwzh=v_bm;
open zq_cur;
loop
fetch zq_cur into v_e001,v_zqje,v_a000;
exit when zq_cur%notfound;
select nvl(max(value1),'0') into v_sfzqsn from bm_xtcs where bm3='03010502' and bm1=v_zxjgbm and bm='04' and sfqy=1;
if v_sfzqsn = '1' then --部分提取是否先提完本年缴存部分后再提上年结转部分
select grzhdngjye into v_d012 from gjzf_gr_zz where grzh = v_a000 ;
if v_d012 >= v_zqje then
v_d012 := v_zqje ;
end if ;
v_d011 := case when v_zqje-v_d012>=0 then v_zqje-v_d012 else 0 end ;
else
select grzhsnjzye into v_d011 from gjzf_gr_zz where grzh = v_a000 ;
if v_d011 >= v_zqje then
v_d011 := v_zqje ;
end if ;
v_d012 := case when v_zqje-v_d011>=0 then v_zqje-v_d011 else 0 end ;
end if ;
select nvl(min(a.hkzt),' '),nvl(max(b.dkzt),' ') into v_hkzt,v_dkzt from grdk_dk_hkmx a left join grdk_dk_zz b on a.jkhtbh=b.jkhtbh where a.jkhtbh=v_e001 and a.ywlsh=v_f017;
--更新个人总账表
update gjzf_gr_zz set bnzq = bnzq + v_zqje, zqlj = zqlj + v_zqje,
grzhye = grzhye - v_zqje, bndqzq = bndqzq + v_d011, bnhqzq = bnhqzq + v_d012,
grzhsnjzye=grzhsnjzye - v_d011,grzhdngjye=grzhdngjye - v_d012,
ndjxdqjs=ndjxdqjs-f_gz_mday(v_enddate, v_rzrq, v_lxjsffxz)*v_d011,
ndjxhqjs=ndjxhqjs-f_gz_mday(v_enddate, v_rzrq, v_lxjsffxz)*v_d012
where grzh = v_a000 ;
select dwzh,grzhye,jgbm into v_dwzh,v_grzhye,v_grjgbm from gjzf_gr_zz where grzh = v_a000;
p_hfsc_ywdz(v_jgbm,'02',v_a000,'贷款对冲','74',v_zqbh,1,v_zqje, v_zqje,0,0,v_d011,v_d012,'部分提取','01',v_rzrq,v_userid,v_grzhye,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99;
return ;
end if;
select b.dwmc,b.dwjjlx into v_dwmc,v_xzbm
from gjzf_dw_zz a left join cr_dw b on a.dwbh = b.dwbh where dwzh = v_dwzh ;
insert into gjzf_gr_tq (id,grzh,dwzh, xzbm, jgbm,tqyhzh,zrzxqc,zrzxkhyh,tqyybm,
tqjehj,tqbj,tqsnjz,tqbnjc,tqfs,tqywlsh,spzt,tqclbh,lrczyid,lrczy,
lrsj,lrqd,yhbm,bpmid,ztqrclbh,beizhu,chdkhkywlsh,TQRQ,ywfhczy,ywfhczyid)
select f_newid, a.grzh, a.dwzh, v_xzbm, v_grjgbm,' ',' ',' ','0102',
v_zqje, v_zqje,v_d011, v_d012,'99',v_zqbh,0,' ',v_userid,v_username,
sysdate,v_blqd,nvl(c.yhbm,' '),0,' ',' ',v_f017,v_rzrq,v_username,v_userid
from gjzf_gr_zz a left join bm_yhbm b on a.grckzhhmssyhbm=b.bm and a.jgbm=b.jgbm
inner join gjzf_dw_zz c on a.dwzh=c.dwzh where a.grzh = v_a000;
insert into gjzf_gr_mx( id,grzh,gjhtqywlx,fse,dngjfse,snjzfse,fslxe,tqyy,tqfs,ywlsh,dwzh,jgbm,xzbm,ywrq,ywzy,
jzrq,jzsj,dzrq,dzsj,ywfhczyid,ywfhczy,ywfhqd,yue,ywsj,tqyymx)
select f_newid(),grzh,'74',v_zqje,v_d012,v_d011,0,'06','99',v_zqbh,dwzh,v_grjgbm,v_xzbm,v_rzrq,'部分提取',
v_rzrq,v_ywsj,v_rzrq,v_ywsj,v_userid,v_username,v_blqd,v_grzhye,v_ywsj,'0102'
from gjzf_gr_zz where grzh = v_a000;
v_sum_zqje:=v_sum_zqje+v_zqje;
v_sum_rs:=v_sum_rs+1;
end loop;
close zq_cur;
if v_sum_zqje<>0 then
update gjzf_dw_zz set dwzhye = dwzhye - v_sum_zqje where dwzh = v_bm ;
select dwzhye,zckye into v_dwzhye,v_zckye from gjzf_dw_zz where dwzh = v_bm ;
insert into gjzf_dw_mx(id,dwzh,jgbm,xzbm,fse,fslxe,fsrs,ywmxlx,ywlsh,ywrq,ywzy,yhbm,
jzrq,jzsj,jzczyid,jzczy,jzqd,dzrq,dzsj,dzczyid,dzczy,dzqd,ywfhsj,ywfhczyid,ywfhczy,ywfhqd,yue,zckye,HDTQYWLSH)
values(f_newid(),v_bm,v_jgbm,v_xzbm,v_sum_zqje,0,v_sum_rs,'74',v_zqbh,v_rzrq,v_bm||'共'||v_sum_rs||'人提取公积金还贷',v_zqyhbm,
v_rzrq,v_ywsj,v_userid,v_username,v_blqd,v_rzrq,v_ywsj,v_userid,v_username,v_blqd,v_ywsj,v_userid,v_username,v_blqd,v_dwzhye,v_zckye,v_f017);
end if;
end loop;
close zqdw_cur;
end loop;
close hkzq_cur;
end;
end if;
--生成财务凭证
if v_sftj = 0 then
p_cw_pz_sc(v_rzrq,v_pzbh,' ','04','02',v_jgbm,v_swtyhbm,v_swtyhbm,' ',' ',' ',v_sum_zcbj+v_sum_yqbj+v_sum_zclx+v_sum_yqlx+v_sum_fx-v_sum_dcje,
v_sum_dcje,v_sum_zcbj,v_sum_zclx,v_sum_fx,v_sum_yqlx,v_sum_yqbj,'贷款回收','01',v_userid,v_cwjgbm,v_pzid,v_msg,v_ret);
if v_ret<>0 then
rollback;
return;
else
update grdk_dk_hkhz set jzpzid=v_pzid where ywlsh=v_pzbh;
update gjzf_dw_mx set jzpzid=v_pzid where HDTQYWLSH=v_pzbh;
end if;
end if;
--全部对冲的需要生成财务凭证
if v_sftj<>0 and v_sfpz = 1 then
p_cw_pz_sc(v_rzrq,v_pzbh,' ','04','02',v_jgbm,v_swtyhbm,v_swtyhbm,' ',' ',' ',v_sum_zcbj+v_sum_yqbj+v_sum_zclx+v_sum_yqlx+v_sum_fx-v_sum_dcje,
v_sum_dcje,v_sum_zcbj,v_sum_zclx,v_sum_fx,v_sum_yqlx,v_sum_yqbj,'贷款回收','01',v_userid,v_cwjgbm,v_pzid,v_msg,v_ret);
if v_ret<>0 then
rollback;
return;
else
update grdk_dk_hkhz set jzpzid=v_pzid where ywlsh=v_pzbh;
update gjzf_dw_mx set jzpzid=v_pzid where HDTQYWLSH=v_pzbh;
end if;
end if;
--支付宝提前还款自动生成凭证
if v_blqd <> 'zxb' and v_blqd <> 'gt' then
p_cw_pz_sc(v_rzrq,v_pzbh,' ','04','02',v_jgbm,v_swtyhbm,v_swtyhbm,' ',' ',' ',v_sum_zcbj+v_sum_yqbj+v_sum_zclx+v_sum_yqlx+v_sum_fx-v_sum_dcje,
v_sum_dcje,v_sum_zcbj,v_sum_zclx,v_sum_fx,v_sum_yqlx,v_sum_yqbj,'贷款回收','01',v_userid,v_cwjgbm,v_pzid,v_msg,v_ret);
if v_ret<>0 then
rollback;
return;
else
update grdk_dk_hkhz set jzpzid=v_pzid where ywlsh=v_pzbh;
update gjzf_dw_mx set jzpzid=v_pzid where HDTQYWLSH=v_pzbh;
end if;
end if;
--防止重复入账操作
p_hfsc_ywlsh_cfcp_chk(v_pzbh,v_msg);
if trim(v_msg) is not null then
rollback;
v_ret:=99;
return;
end if;
end loop;
close tmp_yhcur;
--消息推送
p_sys_optlog_add(v_zxjgbm,v_jgbm,' ',' ','04','02','040203',' ','借款人柜台还款清册入账',v_sum_rs,v_sum_zcbj+v_sum_yqbj+v_sum_zclx+v_sum_yqlx+v_sum_fx,v_userid,v_blqd,v_msg);
if trim(v_msg) is not null then
rollback ;
v_ret:=99;
return;
end if;
/*p_act_noprocess('04','70', v_userid,v_blqd,v_ret);
if v_ret <>0 then
rollback;
return ;
end if;*/
--if v_f026 = '01' then
--推送消息
p_hfsc_mess_ts(v_jgbm,'040203',v_blqd,v_userid,v_userid,' ',v_pzbh,v_pzbh,v_msg,v_ret);
if v_ret<>0 then
v_ret:=99;
rollback;
return;
end if;
if v_f026 <> '01' then
v_msg:= '柜台还款办理完成！';
v_ret:= 0;
else
v_msg:= '提前还款入账成功！';
v_ret:= 0;
end if;
--支取公积金
/*if v_sftj=1 then
commit;
end if;*/
exception
when others then
v_errorcode:= sqlcode;
v_errormsg := sqlerrm;
v_msg:=v_errormsg;
v_ret:= 99;
rollback;
insert into t_wa_sys_log_err (err_date,name_proc,err_code,err_msg)
values(sysdate,'p_hfb_jkr_gthk_rz',v_errorcode,v_errormsg);
commit;
end;6.调账脚本
select * from grdk_dk_hkmx where jkhtbh='20103568'for update order by id desc --利息-42.15 本金减 942.63 218092001_0012256634
create table grdk_dk_hkmx_20180921 as select * from grdk_dk_hkmx where jkhtbh='20103568';
update grdk_dk_hkmx set bjje=bjje-942.63,lxje=lxje-42.15,fse=fse-942.63-42.15,gjjzqe=gjjzqe-942.63-42.15
where jkhtbh='20103568' and id='1002179598640';
select * from grdk_dk_zz where jkhtbh='20103568'
update grdk_dk_zz set hsbjze=hsbjze-942.63,hslxze=hslxze-42.15 where jkhtbh='20103568';
select * from grdk_dk_hkhz where ywlsh='218092001_0012256634'
create table grdk_dk_hkhz_20180921 as select * from grdk_dk_hkhz where ywlsh='218092001_0012256634'
update grdk_dk_hkhz set bjje=bjje-942.63,lxje=lxje-42.15 where ywlsh='218092001_0012256634'; --jzpzid='1002179598647'
select * from im_zj_ls where zh='20103568' for update order by id desc
create table im_zj_ls_20180921 as select * from im_zj_ls where zh='20103568'
delete from im_zj_ls where zh='20103568' and id in ('1002179859522','1002179598637');
update im_zj_ls set zcje=zcje-942.63 where zh='20103568' and id in ('1002179598638'); --23384.37 1005.85
update im_zj_ls set ye=ye+942.63 where zh='20103568' and id >= '1002179598638';
select * from im_zj_hz where zh='20103568' order by id desc
create table im_zj_hz_20180921 as select * from im_zj_hz where zh='20103568';
update im_zj_hz set ljsrje=ljsrje-42.15,ljzcje=ljzcje-942.63,ye=0,ljzcbj=ljzcbj-942.63,ljsrlx=ljsrlx-42.15,ljzclx=ljzclx-42.15
where zh='20103568';
--------------------------------------------------------------------------------------------------------------------------
select * from cw_pz_fl where pzid='1002179598647'
create table cw_pz_fl_20180921 as select * from cw_pz_fl where pzid='1002179598647';
delete from cw_pz_fl where pzid='1002179598647' and id='1002179598651';
update cw_pz_fl set dffse=dffse-942.63 where pzid='1002179598647' and id='1002179598649';
update cw_pz_fl set jffse=jffse-942.63-42.15 where pzid='1002179598647' and id='1002179598648';
select * from cw_pz_ml where pzid='1002179598647'
update cw_pz_ml set pzjfhj=pzjfhj-942.63-42.15,pzdfhj=pzdfhj-942.63-42.15 where pzid='1002179598647'
select * from cw_pz_yw where pzid='1002179598647'
update cw_pz_yw set xmdf=xmdf-942.63 where pzid='1002179598647'
---------------------------------------------------------------------------------------------------------------------------
select * from gjzf_gr_mx where grzh='000000151018' order by id desc --9328.50
update gjzf_gr_mx set fse=fse-942.63-42.15,snjzfse=snjzfse-942.63-42.15,yue=yue+942.63+42.15 --118092001_0012256636
where grzh='000000151018'and id='1002179598643';
select * from gjzf_dw_mx where dwzh='030237'order by id desc --1002179598647 2547511.09
update gjzf_dw_mx set fse=fse-942.63-42.15,yue=yue+942.63+42.15 where dwzh='030237' and id='1002179598644'
select * from gjzf_gr_zz where grzh='000000151018'
update gjzf_gr_zz set grzhsnjzye=grzhsnjzye+942.63+42.15,grzhye=grzhye+942.63+42.15,bndqzq=bndqzq-942.63-42.15,
bnzq=bnzq-942.63-42.15,zqlj=zqlj-942.63-42.15 where grzh='000000151018';
select * from gjzf_dw_zz where dwzh='030237'
update gjzf_dw_zz set dwzhye=dwzhye+942.63+42.15 where dwzh='030237';
select * from im_zj_ls where ywlsh='118092001_0012256636'
update im_zj_ls set zcje=zcje-942.63-42.15,ye=ye+942.63+42.15,zcdqje=zcdqje-942.63-42.15,zcbj=zcbj-942.63-42.15
where ywlsh='118092001_0012256636'
select * from im_zj_hz where zh='000000151018'
update im_zj_hz set ljzcje=ljzcje-942.63-42.15,ljzcbj=ljzcbj-942.63-42.15,ye=ye+942.63+42.15 where zh='000000151018';
DECLARE
V_GRZH VARCHAR2(20):=' ';
V_JXRQ DATE:=TO_DATE('20190701','YYYYMMDD');
V_DQJS NUMBER(20,2):=0;
V_HQJS NUMBER(20,2):=0;
V_DQLX NUMBER(20,2):=0;
V_HQLX NUMBER(20,2):=0;
V_LXHJ NUMBER(20,2):=0;
CURSOR CUR_GRZH IS SELECT GRZH FROM GJZF_GR_ZZ WHERE grzh='000000151018';
BEGIN
OPEN CUR_GRZH;
LOOP
FETCH CUR_GRZH INTO V_GRZH;
EXIT WHEN CUR_GRZH%NOTFOUND;
V_DQJS:=0;
V_HQJS:=0;
P_HFB_JCR_NDJX_JSCALC(V_GRZH,V_JXRQ,V_DQJS,V_HQJS,V_DQLX,V_HQLX,V_LXHJ);
UPDATE GJZF_GR_ZZ SET NDJXDQJS=V_DQJS,NDJXHQJS=V_HQJS WHERE GRZH=V_GRZH;
COMMIT;
END LOOP;
CLOSE CUR_GRZH;
END;
