CREATE OR REPLACE PROCEDURE SP_SF_SFYJK0_RBB000 ( as_KSRQ00 IN CHAR, --开始日期 as_KSSJ00 IN CHAR, --开始时间 as_JSRQ00 IN CHAR, --结束日期 as_JSSJ00 IN CHAR, --结束时间 as_SFY000 IN CHAR, --收费员姓名 as_SFASFY IN CHAR default 'N', --是否按收费员汇总 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR, --系统提示的错误信息 as_JKDH00 OUT VARCHAR --缴款单号 ) -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2007.09.11 修改票据起止号的显示 ; -- jlg 2007.09.14 增加传入参数ad_PJLYID -- jlg 2008.01.03 修改SF_SF_GETQZRQPJXH内容太长的问题 -- qks 2010.01.08 按收费员汇总,MZSF-20100107-003 -- zhr 2010.05.19 改掉MZSF-20100107-003做法，采用新的独立过程 -- qks 2010.06.23 BM_JKB000增加电子钱包金额字段DZQBJE -- jinfl 2011.08.25 增加将公务员补助统计到SBTCJJ -- qks 2011.09.16 退费的转帐部分也要体现出来 MZSF-20110916-001 -- jinfl 2012.03.05 将退费的发票或挂号单的其他的支付方式恢复成(1,2,3,4,7,10,11,18) MZSF-20120222-004 --zhangyc 2013.03.13 增加医保减免金额YYJMJE(支付方式=21) by MZSF-20130307-001 --zhangyc 2017.06.05 增加微信支付、支付宝支付、医院支付、健康通支付 by MZSF-20170508-001 as ls_SFY000 BM_JKB000.SFY000%TYPE ; --收费员编号 ls_PLBH00 BM_JKB000.PJBH00%TYPE ; --票据类型编号 ls_JFBZ00 SF_BRJFB0.JFBZ00%TYPE ; --交费标志 ls_ZFFS00 SF_BRJFB0.ZFFS00%TYPE ; --支付方式 ls_PJZFH0 BM_JKB000.PJZFH0%TYPE ; --作废单据 ls_PJZFJE BM_JKB000.PJZFJE%TYPE ; --票据作废金额=现金金额+转帐金额+银行卡金额 ls_PJZFXJ SF_PJSYQK.XJJE00%TYPE ; --票据作废的现金金额 ls_PJZFZZ SF_PJSYQK.ZZJE00%TYPE ; --票据作废的转账金额 ls_PJZFYK SF_PJSYQK.YHKJE0%TYPE ; --票据作废的银行卡金额 ls_FPZFS0 BM_JKB000.PJZFH0%TYPE ; --发票作废数 ls_XJJE00 BM_JKB000.XJJE00%TYPE ; --现金金额 ls_ZZJE00 BM_JKB000.ZZJE00%TYPE ; --转帐金额 ls_GRZHZF BM_JKB000.GRZHZF%TYPE ; --个人账户支付 ls_TJJJZF BM_JKB000.TJJJZF%TYPE ; --统筹基金支付 ls_SBGRZH BM_JKB000.SBGRZH%TYPE ; --商保个人帐户 ls_SBTCJJ BM_JKB000.SBTCJJ%TYPE ; --商保统筹支付 ls_JKDH00 BM_JKB000.JKDH00%TYPE ; --缴款单号 ls_PJH000 SF_PJSYQK.PJH000%TYPE ; --票据流水号 ls_PJLYID SF_PJSYQK.PJLYID%TYPE ; --票据领用ID ls_ZXPJXH BM_JKB000.PJQSH0%TYPE ; --票据起始号 ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_COUNT0 NUMBER(10); ls_XMJE00 BM_JKKJMX.XMJE00%TYPE ; --项目金额0 ls_XMJE01 BM_JKKJMX.XMJE00%TYPE ; --项目金额1 ls_XMJE02 BM_JKKJMX.XMJE00%TYPE ; --项目金额2 ls_ZPSR00 NUMBER(12,2); --支票收入 ls_YHKSR0 NUMBER(12,2); --银行卡收入 ls_BZZZQD NUMBER(12,2); --不足转账起点 ls_YHKJS0 NUMBER(12,2); --银行卡结算 ls_YBJS00 NUMBER(12,2); --医保结算 ls_FBRZZF NUMBER(12,2); --付病人转诊费 ls_JMBRYF NUMBER(12,2); --减免病人医药费 ls_SFYHZ0 char(1); --是否按收费员汇总 CURSOR CUR_SF_JKB000_PJZFH0 IS --计算作废的票据情况,注意要把预交金的扣除 select A.PLBH00,count(*) NUM,NVL(sum(A.XJJE00),0),NVL(sum(A.ZZJE00),0),NVL(sum(A.YHKJE0),0),nvl(PJLYID,0) PJLYID from SF_PJSYQK A where exists ( select 1 from SF_BRJFB0 B where B.PJH000 = A.PJH000 ) --where A.PJH000 = B.PJH000 and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and (A.CZY000 = ls_SFY000 or ls_SFY000 = -1) and A.PJZT00 = '1' --计算已作废的票据数 and A.SFDY00 = 'Y' --同时还应该是已打印过的票据 and substr(A.pjxh00,1,1) <> '-' --首位是'-'的说明是系统添加的 and A.CZY000 = HSCZY0 group by A.PLBH00,nvl(PJLYID,0); CURSOR CUR_SF_JKB000_PJHS00 IS --计算有效的,退费的票据情况 select PLBH00,JFBZ00,ZFFS00,sum(PJZS00) NUM, NVL(sum(JFJE00),0),PJLYID from (select B.JFBZ00,B.ZFFS00,B.JFJE00, B.PLBH00,B.PJZS00,nvl(PJLYID,0) PJLYID from VW_SF_BRJFB1 B where B.JFRQ00 >= as_KSRQ00 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||B.JFSJ00 >= as_KSRQ00||as_KSSJ00 and B.JFRQ00||B.JFSJ00 <= as_JSRQ00||as_JSSJ00 and (B.JFCZY0 = ls_SFY000 or ls_SFY000 = -1) and pjh000>0 and exists (select 1 from SF_PJSYQK a where A.FZPJH0 = B.PJH000) ) group by PLBH00,JFBZ00,ZFFS00,PJLYID; CURSOR CUR_SF_JKB000_MINQSH IS select PLBH00,min(lpad(trim(PJXH00),30,' ')),count(distinct A.PJH000),nvl(PJLYID,0) PJLYID from SF_PJSYQK A where A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and (A.CZY000 = ls_SFY000 or ls_SFY000 = -1) and A.PJXH00<>'0' and substr(a.PJXH00,1,1) <> '-' group by PLBH00,nvl(PJLYID,0); begin --肺科算法 select nvl(max(trim(VALUE0)),'N') into ls_sfyhz0 from XT_XTCS00 where name00='SF_SFYJKRBBASFYHZ'; if ls_sfyhz0='Y' then SP_SF_SFYJK0_RBB002(as_KSRQ00,as_KSSJ00,as_JSRQ00,as_JSSJ00,as_SFY000,'Y',as_YHMSG0,as_SYSMSG,as_JKDH00); return; end if; ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_PJH000 := 0; IF as_SFY000 = '所有收费员' then ls_SFY000 := -1; else begin select YGBH00 into ls_SFY000 from BM_YGBM00 where ZWXM00||'_'||XKH000 = as_SFY000; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='系统查找不到该收费员！'; as_SYSMSG:=SQLERRM||'select * from BM_YGBM00 where ZWXM00_XKH000 = '||as_SFY000; ROLLBACK; RETURN; END; end if; select SQ_BM_JKB000_JKBID0.nextval into ls_JKDH00 from dual; --根据发票上的金额计算个人账户,统筹基金金额 --select sum(GRZHZF),sum(TCJJZF),sum(ZFJE00),sum(SBGRZH),sum(SBTCJJ) into ls_GRZHZF,ls_TJJJZF,ls_XJJE00,ls_SBGRZH,ls_SBTCJJ from SF_JZB000 -- where JZY000 = ls_SFY000 and JZDH00 in ( select JZDH00 from SF_BRJFB0 where JFRQ00 <= as_JSRQ00 and JFRQ00||JFSJ00 <= as_JSRQ00||as_JSSJ00 -- and JFRQ00 >= as_KSRQ00 and JFRQ00||JFSJ00 >= as_KSRQ00||as_KSSJ00 and (JFCZY0 = ls_SFY000 or ls_SFY000 = -1)); Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,SBGRZH,SBTCJJ,PJZFJE,HSZFH0,HSZFJE,PJLYID) select ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,PLBH00,ltrim(min(lpad(trim(pjxh00),30,' '))),ltrim(max(lpad(trim(pjxh00),30,' '))),0,0,0,0,ls_SFY000,'9',0,0,0,0,0,0,0,nvl(PJLYID,0) from SF_PJSYQK A where substr(A.pjxh00,1,1) <> '-' and A.DYRQ00 >= as_KSRQ00 and A.DYRQ00||A.DYSJ00 >= as_KSRQ00||as_KSSJ00 and A.DYRQ00 <= as_JSRQ00 and A.DYRQ00||A.DYSJ00 <= as_JSRQ00||as_JSSJ00 and (A.CZY000 = ls_SFY000 or ls_SFY000 = -1) group by PLBH00,nvl(PJLYID,0); if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,PJLYID) select ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,nvl(PJLYID,0) from SF_PJSYQK where DYRQ00>= as_KSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and (CZY000 = ls_SFY000 or ls_SFY000 = -1) and PLBH00 <> 5 --是有效的票据 group by PLBH00,nvl(PJLYID,0); end if; OPEN CUR_SF_JKB000_PJZFH0; --计算作废的票据情况 LOOP FETCH CUR_SF_JKB000_PJZFH0 INTO ls_PLBH00,ls_PJZFH0,ls_PJZFXJ,ls_PJZFZZ,ls_PJZFYK,ls_PJLYID; EXIT WHEN CUR_SF_JKB000_PJZFH0%NOTFOUND; update BM_JKB000 set PJZFH0 = NVL(ls_PJZFH0,0), --作废票据数 PJZFJE = NVL(ls_PJZFXJ,0)+NVL(ls_PJZFZZ,0)+NVL(ls_PJZFYK,0) --作废票据金额 where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; END LOOP; CLOSE CUR_SF_JKB000_PJZFH0; OPEN CUR_SF_JKB000_PJHS00; --计算有效的或退费的票据情况 LOOP FETCH CUR_SF_JKB000_PJHS00 INTO ls_PLBH00,ls_JFBZ00,ls_ZFFS00,ls_PJZFH0,ls_PJZFJE,ls_PJLYID; EXIT WHEN CUR_SF_JKB000_PJHS00%NOTFOUND; IF ls_JFBZ00 = '1' THEN --退费的 if ls_PLBH00 = 1 or ls_PLBH00 = 0 then --若票据类型是发票或挂号单 if ls_ZFFS00 = 1 then --现金,2,8) update BM_JKB000 set HSZFH0 = NVL(HSZFH0,0)+NVL(ls_PJZFH0,0), --退费票据数 HSZFJE = NVL(HSZFJE,0)+NVL(ls_PJZFJE,0) --退费票据金额 where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,NVL(ls_PJZFH0,0),NVL(ls_PJZFJE,0),ls_PJLYID); end if; end if; if ls_ZFFS00 = 2 then --转账 update BM_JKB000 set HSZFH0 =NVL(HSZFH0,0)+NVL(ls_PJZFH0,0),ZZJE00 = NVL(ZZJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,ls_PJZFJE,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 7 then --记账 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,JZJE00 = NVL(JZJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,JZJE00,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_PJZFJE,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 18 then --电子钱包 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,DZQBJE = NVL(DZQBJE,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,JZJE00,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID,DZQBJE) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,0,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; if ls_ZFFS00 = 16 then --公务员补助 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,GWYBZ0 = NVL(GWYBZ0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,JZJE00,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID,GWYBZ0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,0,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; if ls_ZFFS00 = 21 then --减免金额 by MZSF-20130307-001 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YYJMJE = NVL(YYJMJE,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,YYJMJE) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --20：医院支付 if ls_ZFFS00 = 20 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YYZF00 = NVL(YYZF00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,YYZF00) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --24：微信支付 if ls_ZFFS00 = 24 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,WXZF00 = NVL(WXZF00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,WXZF00) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --25：支付宝支付 if ls_ZFFS00 = 25 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,ZFBZF0 = NVL(ZFBZF0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,ZFBZF0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --26：健康通支付 if ls_ZFFS00 = 26 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,MSTZF0 = NVL(MSTZF0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,MSTZF0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; if ls_ZFFS00 not in(1,2,3,4,7,10,11,18,16,21,20,24,25,26) then --其他 --if ls_ZFFS00 not in(1,2,7,18) then --其他 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,SBGRZH = NVL(SBGRZH,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFJE,ls_PJZFH0,ls_PJLYID); end if; end if; else --否则剩下的是预交金单 if ls_ZFFS00 = 1 then --现金 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,XJJE00 = NVL(XJJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then --+NVL(ls_PJZFJE,0) Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,ls_PJZFJE,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 2 then --转账 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,ZZJE00 = NVL(ZZJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,ls_PJZFJE,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 8 then --银行卡 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YHKJE0 = NVL(YHKJE0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,ls_PJZFJE,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 in (3,10) then --医保个人帐户 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,GRZHZF = NVL(GRZHZF,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',ls_PJZFJE,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 in (4,11) then --医保统筹基金 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,TJJJZF = NVL(TJJJZF,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,ls_PJZFJE,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 18 then --电子钱包 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,DZQBJE = NVL(DZQBJE,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,DZQBJE) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; if ls_ZFFS00 = 21 then --减免金额 by MZSF-20130307-001 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YYJMJE = NVL(YYJMJE,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,YYJMJE) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; if ls_ZFFS00 = 16 then --公务员补助 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,GWYBZ0 = NVL(GWYBZ0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,GWYBZ0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_PJZFJE,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; --20：医院支付 BM_JKB000.YYZF00 if ls_ZFFS00 = 20 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YYZF00 = NVL(YYZF00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,YYZF00) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --24：微信支付 BM_JKB000.WXZF00 if ls_ZFFS00 = 24 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,WXZF00 = NVL(WXZF00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,WXZF00) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --25：支付宝支付 BM_JKB000.ZFBZF0 if ls_ZFFS00 = 25 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,ZFBZF0 = NVL(ZFBZF0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,ZFBZF0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --26：健康通支付 BM_JKB000.MSTZF0 if ls_ZFFS00 = 26 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,MSTZF0 = NVL(MSTZF0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,MSTZF0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; -- if ls_ZFFS00 <> 1 and ls_ZFFS00 <> 2 and ls_ZFFS00 <> 8 and ls_ZFFS00 <> 3 and ls_ZFFS00 <> 4 and ls_ZFFS00 <> 10 and ls_ZFFS00 <> 11 then --商保 if ls_ZFFS00 not in (1,2,3,4,8,10,11,18,16,21,20,24,25,26) then --其它 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,SBGRZH = NVL(SBGRZH,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFJE,ls_PJZFH0,ls_PJLYID); end if; end if; end if; ELSE --交费的 if ls_ZFFS00 = 1 then --现金 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,XJJE00 = NVL(XJJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then --+NVL(ls_PJZFJE,0) Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,ls_PJZFJE,0,0,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 2 then --转账 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,ZZJE00 = NVL(ZZJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,ls_PJZFJE,0,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 7 then --记账 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,JZJE00 = NVL(JZJE00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,JZJE00,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_PJZFJE,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 8 then --银行卡 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YHKJE0 = NVL(YHKJE0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,ls_PJZFJE,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 16 then --公务员补助 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,GWYBZ0 = NVL(GWYBZ0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,GWYBZ0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_PJZFJE,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 in (3,10) then --医保个人帐户 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,GRZHZF = NVL(GRZHZF,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',ls_PJZFJE,0,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 in (4,11) then --医保统筹基金 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,TJJJZF = NVL(TJJJZF,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,ls_PJZFJE,0,0,0,ls_PJZFH0,ls_PJLYID); end if; end if; if ls_ZFFS00 = 18 then --电子钱包 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,DZQBJE = NVL(DZQBJE,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID,DZQBJE) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; if ls_ZFFS00 = 21 then --减免金额 by MZSF-20130307-001 update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YYJMJE = NVL(YYJMJE,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,YYJMJE) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --20：医院支付 BM_JKB000.YYZF00 if ls_ZFFS00 = 20 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,YYZF00 = NVL(YYZF00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,YYZF00) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --24：微信支付 BM_JKB000.WXZF00 if ls_ZFFS00 = 24 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,WXZF00 = NVL(WXZF00,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,WXZF00) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --25：支付宝支付 BM_JKB000.ZFBZF0 if ls_ZFFS00 = 25 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,ZFBZF0 = NVL(ZFBZF0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,ZFBZF0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; --26：健康通支付 BM_JKB000.MSTZF0 if ls_ZFFS00 = 26 then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,MSTZF0 = NVL(MSTZF0,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,PJZS00,PJLYID,MSTZF0) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFH0,ls_PJLYID,ls_PJZFJE); end if; end if; -- if ls_ZFFS00 <> 1 and ls_ZFFS00 <> 2 and ls_ZFFS00 <> 7 and ls_ZFFS00 <> 8 and ls_ZFFS00 <> 3 and ls_ZFFS00 <> 4 and ls_ZFFS00 <> 10 and ls_ZFFS00 <> 11 then --商保 if ls_ZFFS00 not in (1,2,3,4,7,8,10,11,18,16,21,20,24,25,26) then update BM_JKB000 set PJZS00 = NVL(PJZS00,0)+ls_PJZFH0,SBGRZH = NVL(SBGRZH,0)+NVL(ls_PJZFJE,0) where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=ls_PJLYID; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00,GRZHZF,TJJJZF,HSZFH0,HSZFJE,SBGRZH,PJZS00,PJLYID) values(ls_JKDH00,ls_PJH000,ls_SFY000,ls_CZRQ00,ls_CZSJ00,ls_PLBH00,'0','0',0,0,0,0,ls_SFY000,'9',0,0,0,0,ls_PJZFJE,ls_PJZFH0,ls_PJLYID); end if; end if; END IF; END LOOP; CLOSE CUR_SF_JKB000_PJHS00; OPEN CUR_SF_JKB000_MINQSH; LOOP FETCH CUR_SF_JKB000_MINQSH INTO ls_PLBH00,ls_ZXPJXH,ls_PJZFH0,ls_PJLYID; EXIT WHEN CUR_SF_JKB000_MINQSH%NOTFOUND; update BM_JKB000 set PJQSH0 = ls_ZXPJXH, PJZS00 = ls_PJZFH0 where PJBH00 = ls_PLBH00 and PJLYID=ls_PJLYID and JKDH00=ls_JKDH00; END LOOP; CLOSE CUR_SF_JKB000_MINQSH; IF ls_SFY000 = -1 THEN --查询全部收费员 update BM_JKB000 A set BEIZHU = trim(PJQSH0)||'-'||rtrim(PJZZH0)||'('||to_char(PJZS00)||')' where JKDH00=ls_JKDH00; ELSE update BM_JKB000 A set BEIZHU = substr(SF_SF_GETQZRQPJXH(PJBH00,as_KSRQ00,as_KSSJ00,as_JSRQ00,as_JSSJ00,ls_SFY000,PJLYID),1,200) where JKDH00=ls_JKDH00; END IF; as_JKDH00:=ls_JKDH00; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='系统查找不到该收费员！'; as_SYSMSG:=SQLERRM; ROLLBACK; WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_SFYJK0_RBB000('||as_JSRQ00||','||as_JSSJ00||','||to_char(ls_SFY000)||','||as_SFY000||','|| as_SFY000||','||',as_YHMSG0,as_SYSMSG,as_JKDH00)',1,150); ROLLBACK; END; /