CREATE OR REPLACE PROCEDURE SP_ZY_JKB000_REG000 ( as_JSRQ00 IN CHAR, --缴款结束日期 as_JSSJ00 IN CHAR, --缴款结束时间 ad_CZY000 IN NUMBER, --操作员编码 as_CZYXM0 IN CHAR, --操作员姓名 as_SFY000 IN CHAR, --收费员姓名 as_YJJXH0 IN CHAR, --预交金票据的外部流水号 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR, --系统提示的错误信息 as_JKDH00 OUT VARCHAR, --缴款单号 as_COMMIT in VARCHAR2 default 'Y' --是否提交 ) -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2007.08.09 处理取作废票据序号时出错问题ZYSF-20070807-002; -- xzw 2007.11.06 对商保病人进行特殊处理; -- xzw 2008.01.17 医保费用包含商保费用问题进行重新处理,根据IC_YBBRLB中的字段YBBHSB的值进行处理; -- xzw 2008.04.29 限制省二在0点到1点之间不允许手工缴款; -- xzw 2008.06.05 把'实际应交现金***与缴款表的应交现金***不一致'提示去掉; -- xzw 2010.03.08 医保金额增加公务员补助 -- qks 2010.03.24 修改公务员补助GWYBZ0、商保(ad_SBZHJE、ad_SBTJJE) 是否包含在统筹金额中的问题; -- qks 2011.03.17 写入BM_JKB000.YYID00 -- zhangyc 2011.05.20 写入农保赔付金额ZY_SFXMYBJK='Y'(厦门地区) -- zhangyc 2011.10.18 医保结算是否把农保金额独立显示(ZY_JKYBSFCF) modify by ZYSF-20110914-006 -- zhanghr 2011.01.15 ZYSF-20120116-003 新增单病种差额 -- zhangyc 2013.07.09 修正需求ZYSF-20130625-002民政补助YBYL18(ld_MZBZJE) BY ZYSF-20130708-002 -- zhangyc 2013.11.20 修正民政救助 by ZYSF-20130723-001 -- zhangyc 2014.01.06 修正ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D 三表关联取YBBHSB用 A跟D关联 BY ZYSF-20140102-004 -- zhangyc 2014.05.09 厦门地区 SYBXZF 不包含在统筹里计算时把它归到统筹中 by ZYSF-20140425-001 -- zhangyc 2014.05.12 收费员实缴现金扣除掉ZY_JZB000.SYBXZF(不包含在统筹基金里) by ZYSF-20140425-004 --zhangyc 2014.07.14 增加统计应退还POS金额 by ZYSF-20140313-001 --zhangyc 2015.03.04 修改按床日数单病种支付实交现金算法 by ZYSF-20150204-002 /* 1:冲预收款=冲预收款-收医院支付票据金额(LS_YYZFSK) 2:退非医保款=退非医保款+退医院支付票据金额(LS_YYZFTK) 3:实交现金=实交现金-(收医院支付票据金额(LS_YYZFSK)+退医院支付票据金额(LS_YYZFTK)) 4:冲住院费用=冲住院费用-收医院支付票据金额(LS_YYZFSK) */ --zhangyc 2015.04.10 增加参数ZY_YHTTKPOS控制应退POS by ZYSF-20141222-003 --zhangyc 2016.04.12 (2)现金退出扣除掉(5)应退还pos by ZYSF-20151210-001 -- qks 2016.05.27 择日住院处理的费用转成住院预交金收款的记录，单独增加一行会计科目 “17、择日住院病例” by ZYSF-20160520-001 --zhangyc 2016.10.26 实交现金=实交现金-民政补助金额 by ZYSF-20161027-003 --zhangyc 2016.11.03 实交现金不扣除YBYL01 by ZYSF-20161110-001 --dsm 2017.03.30 获取实交现金要减民政补助部分的,要从ZY_BRJFB0取 归档 ZYSF-20170330-006 --zhangyc 2017.06.12 增加微信支付和支付宝支付 by ZYSF-20170508-001 --zhangyc 2017.12.13 增加现金退款重新查退款金额 by ZYSF-20171214-001 --zhangyc 2018.04.09 修改微信或支付重新结算银行卡错误 by ZYSF-20180409-003 --zhangyc 2018.04.25 银行卡、微信、支付宝数据错误修正 by ZYSF-20180425-001 --dsm 2018.04.26 zy_jzb0000.FBBH00=3时JZJE00都按0来计算 for MZSF-20180426-005 --dsm 2018.08.16 支付宝微信的计算不对 for ZYSF-20180807-004 --dsm 2019.01.28 计算微信支付宝时，重新结算冲正的不能计算入 归档for ZYSF-20190128-002 --zhuyr 2019.03.04 修改上条需求重新结算冲正的不能计算入的取值方法 by ZYSF-20190304-001 -- dsm 2019.03.04 增加'94'的写入 为了计算（综合管理--收费员缴款确认--预交金现金收入） for ZYSF-20190304-002 --zhangyc 2019.04.18 增加参数LS_DDJSKQ控制SFDY00 by ZYSF-20190418-008 --dsm 2019.06.15 BZ0000='结算后预交款' 归档 for MZYS-20190615-001 --dsm 2019.08.13 注释掉LS_RYTCX0变量 for ZYSF-20190814-001 as ls_SFY000 BM_JKB000.SFY000%TYPE ; --收费员编号 ls_PLBH00 BM_JKB000.PJBH00%TYPE ; --票据类型编号 ls_PJZFH0 BM_JKB000.PJZFH0%TYPE ; --作废单据 ls_PJZFJE BM_JKB000.PJZFJE%TYPE ; --票据作废金额 ls_XJJE00 BM_JKB000.XJJE00%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 ZY_PJSYQK.PJH000%TYPE ; --票据流水号 ls_ZXPJXH BM_JKB000.PJQSH0%TYPE ; --票据起始号 ls_XMJE00 BM_JKKJMX.XMJE00%TYPE ; --项目金额0 ls_XMJE01 BM_JKKJMX.XMJE00%TYPE ; --项目金额1 ls_XMJE02 BM_JKKJMX.XMJE00%TYPE ; --项目金额2 ls_BEIZHU BM_JKKJMX.BEIZHU%TYPE ; --备注 ls_BEIZHU1 BM_JKKJMX.BEIZHU%TYPE ; --备注 ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_KSRQSJ CHAR(16); ls_JSRQSJ CHAR(16); ls_COUNT0 NUMBER(5); 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_CZYFY0 NUMBER(12,2); --冲住院费用 ls_XMJE29 NUMBER(12,2); --保存在医院系统中计算的医保个人帐户支付的金额 ls_JZYSK0 NUMBER(12,2); --记账应收款 ls_XMJE30 NUMBER(12,2); --暂时未使用 ls_XMJE31 NUMBER(12,2); --暂时未使用 ls_XMJE32 NUMBER(12,2); --暂时未使用 ls_XMJE35 number(12,2); --四舍五入金额 ls_CYYSK0 number(12,2); ls_CYYTK0 number(12,2); ls_NBPFJE number(12,2); --农保赔付金额 ls_xtsj00 char(16); ls_jktqsj number(5); --缴款提前分钟数 ls_YXTDJK char(1); --是否允许替代缴款 ls_SFXMYB varchar2(10); --是否厦门医保接口 ls_NBJE00 number(12,2); --农合金额 ls_NBZYJE number(12,2); --农保住院票据金额 ls_NBZYTK number(12,2); --农保住院票据退款金额 ls_YBJECF varchar2(10); --医保结算金额是否把农保独立出来 LS_SYBXZF number(12,4); --商业报销支付 LS_SYBXZFTK number(12,4); --商业报销支付退 LS_YHTKJE number(12,2); --应退还银行卡 LS_WXKJE number(12,2); --应退还微信 LS_ZFBKJE number(12,2); --应退还支付宝 LS_YYZFSK number(12,2); --收医院支付票据金额 LS_YYZFTK number(12,2); --退医院支付票据金额 LS_YYZFJE number(12,2); --医院支付金额 LS_YHTKJE_TEMP number(12,2); --应退还银行卡 ZY_YHTTKPOS Varchar2(100); ZY_CYJSTKAYJJ varchar2(100); ls_ZRZYYJ NUMBER(12,2); --择日住院预交金转入 LS_MZBZJE NUMBER(12,2); LS_XMJE49 NUMBER(12,2); --民政救助 LS_MZBZJESK number(12,2); --民政补助金额 LS_MZBZJETK number(12,2); --民政补助金额退 ls_MZBZJE1 number(12,2); --民政补助金额 LS_WXZF00_SR number(12,2);--微信支付收入 LS_WXZF00_TK number(12,2);--微信支付退款 LS_WXZF00 number(12,2);--微信支付 LS_ZFBZF0_SR number(12,2);--支付宝支付收入 LS_ZFBZF0_TK number(12,2);--支付宝支付退款 LS_ZFBZF0 number(12,2);--支付宝支付 LS_YHSJJE number(12,2);--银行实际金额 LS_RYTCX0 number(12,2); --榕医通冲销 LS_DDJSKQ varchar2(100);--住院是否启用多点结算接口 Y:开启 N:关闭 默认值:N ls_bz0000 varchar2(1000); CURSOR CUR_ZY_JKB000_PJZFH0 IS --计算作废的票据情况 select PLBH00,count(*) NUM,sum(XJJE00+ZZJE00+YHKJE0) JE0000 from ZY_PJSYQK where PLBH00 <> 6 --非住院缴款单 and CZY000 = ls_SFY000 and PJZT00 = '1' --and SFDY00 = 'Y' --同时还应该是已打印过的票据 and ((LS_DDJSKQ in ('Y') and 1=1) or (LS_DDJSKQ not in ('Y') and SFDY00 = 'Y')) --同时还应该是已打印过的票据 and JKDID0 = 0 --未缴过款的 and CZY000 = HSCZY0 and substr(pjxh00,1,1) <> '-' and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 group by PLBH00; CURSOR CUR_ZY_JKB000_PJHS00 IS --计算回收的票据情况,注意这部分的票据对应的JKBID0不能赋值 select PLBH00,count(*) NUM,sum(XJJE00+ZZJE00+YHKJE0) JE0000 from ZY_PJSYQK where HSJKID = ls_JKDH00 group by PLBH00; CURSOR CUR_ZY_JKB000_MINQSH IS select PLBH00,min(lpad(trim(PJXH00),30,' ')) from ZY_PJSYQK where jkdid0=ls_JKDH00 and pjxh00<>'0' and substr(pjxh00,1,1) <> '-' group by PLBH00; BEGIN ls_czrq00:=as_JSRQ00; ls_czsj00:=as_JSSJ00; LS_YHTKJE:=0; LS_XMJE49:=0; LS_MZBZJE:=0; LS_MZBZJESK:=0; LS_MZBZJETK:=0; LS_YHSJJE:=0;--银行实际金额 LS_RYTCX0:=0; --榕医通冲销 begin select to_number(Trim(Value0)) into ls_jktqsj from XT_XTCS00 where NAME00='XT_JKTQSJ'; exception when others then LS_JKTQSJ:=5; end; begin --厦门医保接口 select trim(value0) into ls_SFXMYB from xt_xtcs00 where name00 ='ZY_SFXMYBJK'; exception when others then ls_SFXMYB:='N'; end; begin --医保结算金额是否把农保独立出来 select trim(value0) into ls_YBJECF from xt_xtcs00 where name00 ='ZY_JKYBSFCF'; exception when others then ls_YBJECF:='N'; end; begin --住院系统现金收入审核表和缴款表银行卡收入是否扣除应退还POS金额 Y:扣除 N:不扣除 默认值Y select trim(value0) into ZY_YHTTKPOS from xt_xtcs00 where name00 ='ZY_YHTTKPOS'; exception when others then ZY_YHTTKPOS:='Y'; end; begin select upper(trim(value0)) into ZY_CYJSTKAYJJ from xt_xtcs00 where name00='ZY_CYJSTKAYJJ'; exception when others then ZY_CYJSTKAYJJ:='N'; end; --LS_DDJSKQ 住院是否启用多点结算接口 Y:开启 N:关闭 默认值:N begin select substrb(SF_SF_TYZD00('多点结算接口参数','Open_ZY'),1,100) into LS_DDJSKQ from dual; exception when others then LS_DDJSKQ:='N'; end; if LS_DDJSKQ is null then LS_DDJSKQ:='N'; end if; select to_char(sysdate,'YYYYMMDD')||to_char(sysdate-ls_jktqsj/1440,'hh24:mi:ss') into ls_xtsj00 from dual;--肺科医院增加时间限制 if (ls_CZRQ00||ls_CZSJ00)>ls_xtsj00 then as_YHMSG0:='输入的缴款时间要小于当前时间'||to_char(ls_jktqsj)||'分钟!'; as_SYSMSG:=SQLERRM||'输入的缴款时间应'||ls_xtsj00; ROLLBACK; RETURN; end if; select count(*) into ls_COUNT0 from xt_yyxx00 where yyid00='220007';--省二在0点到1点之间不允许手工缴款 if ls_COUNT0>0 then if to_char(sysdate,'hh24') in ('00') and (ad_CZY000>0) then as_YHMSG0:='0点到1点，不能进行手工缴款，系统将自动缴款！'; as_SYSMSG:='缴款提示'; ROLLBACK; RETURN; end if; end if; ls_XMJE29:= 0 ; --保存在医院系统中计算的医保个人帐户支付的金额 ls_XMJE30:= 0 ; --暂时未使用 ls_XMJE31:= 0 ; --暂时未使用 ls_XMJE32:= 0 ; --暂时未使用 ls_ZPSR00:= 0 ; --支票收入 ls_YHKSR0:= 0 ; --银行卡收入 ls_BZZZQD:= 0 ; --不足转账起点 ls_YHKJS0:= 0 ; --银行卡结算 ls_YBJS00:= 0 ; --医保结算 ls_FBRZZF:= 0 ; --付病人转诊费 ls_JMBRYF:= 0 ; --减免病人医药费 ls_XMJE35:=0; --四舍五入金额 ls_NBJE00:=0; ls_NBZYJE:=0; ls_NBZYTK:=0; LS_YYZFSK:=0; LS_YYZFTK:=0; LS_SYBXZF:=0; LS_SYBXZFTK:=0; ls_ZRZYYJ:=0; ls_MZBZJE1:=0; LS_WXZF00_SR:=0;--微信支付收入 LS_WXZF00_TK:=0;--微信支付退款 LS_WXZF00:=0;--微信支付 LS_ZFBZF0_SR:=0;--支付宝支付收入 LS_ZFBZF0_TK:=0;--支付宝支付退款 LS_ZFBZF0:=0;--支付宝支付 LS_WXKJE:=0; --应退还微信 LS_ZFBKJE:=0; --应退还支付宝 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 A,ZY_BRJFB0 B where A.YGBH00 = B.JFCZY0 and A.ZWXM00_A.XKH000 = '||as_SFY000||' and JKBID0 = 0 '; ROLLBACK; RETURN; END; begin select Upper(substrb(value0,1,1)) into ls_YXTDJK from XT_XTCS00 where name00='SF_SFYXSFYDJK'; exception when others then ls_YXTDJK:='Y'; end ; if ls_YXTDJK='N' and ls_SFY000<>ad_CZY000 and ad_CZY000>0 then--非超级用户，不能替代缴款 as_YHMSG0:='对不起，只能由收费员本人进行缴款登记，不能由他人代劳！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; select SQ_BM_JKB000_JKBID0.nextval into ls_JKDH00 from dual; select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --根据发票上的金额计算个人账户,统筹基金金额 select nvl(sum(GRZHZF),0),nvl(sum(TCJJZF+SYBXZF),0),nvl(sum(ZFJE00),0),nvl(sum(SBGRZH),0),nvl(sum(SBTCJJ),0), --nvl(sum(nvl(YBYL01,0)+nvl(YBYL18,0)),0) sum(nvl(YBYL18,0)) into LS_GRZHZF,LS_TJJJZF,LS_XJJE00,LS_SBGRZH,LS_SBTCJJ,LS_MZBZJE from ZY_JZB000 where JZY000 = ls_SFY000 and JZDH00 in ( select JZDH00 from ZY_BRJFB0 where JFCZY0 = ls_SFY000 and JKBID0 = 0 and JZDH00 > 1 and JFRQ00 <= as_JSRQ00 and JFRQ00||JFSJ00 <= as_JSRQ00||as_JSSJ00); --添加结帐明细表的内容(既发票项目的内容) Insert into BM_JKMXB0(JKDH00,SFXMID,XMMC00,XMJE00,BZ0000) select ls_JKDH00,A.XMBH00,C.XMMC00,sum(A.XMJE00),'1' from ZY_JZMX00 A,ZY_JZB000 B,BM_FPXM00 C where A.JZDH00 = B.JZDH00 and A.XMBH00 = C.FPXMID and B.JZY000 = ls_SFY000 and A.JZDH00 in ( select JZDH00 from ZY_BRJFB0 where JFCZY0 = ls_SFY000 and JKBID0 = 0 and JZDH00 > 1 and JFRQ00 <= as_JSRQ00 and JFRQ00||JFSJ00 <= as_JSRQ00||as_JSSJ00) --注意结算单号=1的是作废的 group by A.XMBH00,C.XMMC00; --添加交款的内容 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,MZBZJE, WXZF00,ZFBZF0) select LS_JKDH00,LS_PJH000,AD_CZY000,AS_JSRQ00,AS_JSSJ00,PLBH00,ltrim(min(lpad(trim(pjxh00),30,' '))),ltrim(max(lpad(trim(pjxh00),30,' '))), 0,sum(XJJE00),sum(ZZJE00),sum(YHKJE0),ls_SFY000,'1', decode(PLBH00,3,LS_GRZHZF,0),DECODE(PLBH00,3,LS_TJJJZF,0),DECODE(PLBH00,3,LS_SBGRZH,0),decode(PLBH00,3,LS_SBTCJJ,0),0,0,0, decode(PLBH00,3,nvl(LS_MZBZJE,0),0), sum(SF_ZY_ZFJE00(0,0,AA.PJH000,24)),--微信支付 sum(SF_ZY_ZFJE00(0,0,AA.PJH000,25)) --支付宝支付 from ZY_PJSYQK AA where 1=1 and substr(pjxh00,1,1) <> '-' and JKDID0 = 0 and PJH000 in (select PJH000 from ZY_PJSYQK where DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and PLBH00 <> 6 and CZY000 = ls_SFY000 and PJZT00 = '1' and JKDID0 = 0 union all select A.PJH000 from ZY_BRJFB0 A where A.JFCZY0 = ls_SFY000 and A.JKBID0 = 0 --预交金票据 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 union all select A.PJH000 from ZY_JZB000 A,ZY_BRJFB0 B where A.JZDH00 = B.JZDH00 and A.JZY000=B.JFCZY0 and B.JFCZY0 = ls_SFY000 and B.JKBID0 = 0 and B.JZDH00 > 1 and B.JFRQ00 <= as_JSRQ00 and B.JFRQ00||B.JFSJ00 <= as_JSRQ00||as_JSSJ00 ) --发票 group by PLBH00; if SQL%NOTFOUND then Insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0,PJZFH0,XJJE00,ZZJE00,YHKJE0,SFY000,MZBZ00, GRZHZF,TJJJZF,MZBZJE,WXZF00,ZFBZF0) select ls_JKDH00,ls_PJH000,ad_CZY000,as_JSRQ00,as_JSSJ00,PLBH00,'0','0',0,sum(XJJE00),sum(ZZJE00),0,ls_SFY000,'1', DECODE(PLBH00,3,LS_GRZHZF,0),DECODE(PLBH00,3,LS_TJJJZF,0),DECODE(PLBH00,3,nvl(LS_MZBZJE,0),0), sum(SF_ZY_ZFJE00(0,0,AA.PJH000,24)),--微信支付 sum(SF_ZY_ZFJE00(0,0,AA.PJH000,25)) --支付宝支付 from ZY_PJSYQK AA where CZY000 = ls_SFY000 and JKDID0 = 0 and PLBH00 <> 6 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 group by PLBH00; end if; update BM_JKB000 j set YYID00=(select c.YYID00 from ZY_BRJFB0 b,BM_BMBM00 c where JKBID0=j.JKDH00 and b.CZYKS0=c.BMBH00 and rownum=1) where JKDH00=ls_JKDH00; OPEN CUR_ZY_JKB000_PJZFH0; LOOP FETCH CUR_ZY_JKB000_PJZFH0 INTO ls_PLBH00,ls_PJZFH0,ls_PJZFJE; EXIT WHEN CUR_ZY_JKB000_PJZFH0%NOTFOUND; --得到作废单据号 update BM_JKB000 set PJZFH0 = NVL(ls_PJZFH0,0), --作废票据数 PJZFJE = NVL(ls_PJZFJE,0) --作废票据金额 where PJBH00 = NVL(ls_PLBH00,0) and JKDH00 = ls_JKDH00; END LOOP; CLOSE CUR_ZY_JKB000_PJZFH0; Update ZY_PJSYQK set HSJKID = ls_JKDH00 --将回收的票据进行处理 where HSCZY0 = ls_SFY000 and HSJKID = 0 --and SFDY00 = 'Y' --同时还应该是已打印过的票据 and ((LS_DDJSKQ in ('Y') and 1=1) or (LS_DDJSKQ not in ('Y') and SFDY00 = 'Y')) --同时还应该是已打印过的票据 and substr(pjxh00,1,1) <> '-' --首位是'-'的说明是系统添加的 --and CZY000 <> HSCZY0 and HSRQ00 <= as_JSRQ00 and HSRQ00||HSSJ00 <= as_JSRQ00||as_JSSJ00; OPEN CUR_ZY_JKB000_PJHS00; --计算回收的票据情况 LOOP FETCH CUR_ZY_JKB000_PJHS00 into ls_PLBH00,ls_PJZFH0,ls_PJZFJE; EXIT WHEN CUR_ZY_JKB000_PJHS00%NOTFOUND; update BM_JKB000 set HSZFH0 = NVL(ls_PJZFH0,0), --回收票据数 HSZFJE = NVL(ls_PJZFJE,0) --回收票据金额 where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00; END LOOP; CLOSE CUR_ZY_JKB000_PJHS00; Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,JKDID0) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,0,0,ad_CZY000,as_CZYXM0, as_JSRQ00,as_JSSJ00,'Y','0',as_CZYXM0,' ',ls_JKDH00 from BM_PJLXB0 where PLBH00 = 6 ; --'6':是住院缴款单号 if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 6)'; ROLLBACK; RETURN; end if; Update ZY_PJSYQK set JKDID0 = ls_JKDH00 where JKDID0 = 0 and CZY000 = ls_SFY000 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00; Update ZY_BRJFB0 set JKBID0 = ls_JKDH00 where JFCZY0 = ls_SFY000 and JKBID0 = 0 and JFRQ00 <= as_JSRQ00 and JFRQ00||JFSJ00 <= as_JSRQ00||as_JSSJ00; --未缴过款的 OPEN CUR_ZY_JKB000_MINQSH; LOOP FETCH CUR_ZY_JKB000_MINQSH INTO ls_PLBH00,ls_ZXPJXH; EXIT WHEN CUR_ZY_JKB000_MINQSH%NOTFOUND; update BM_JKB000 set PJQSH0 = ls_ZXPJXH where PJBH00 = ls_PLBH00 and JKDH00 = ls_JKDH00; END LOOP; CLOSE CUR_ZY_JKB000_MINQSH; --填写BM_JKKJMX表的内容: insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'01','1、预交金收入' ,0,'','1',1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'40',' (1)收预交金' ,0,'','1',2); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'41',' (2)退预交金' ,0,'','1',3); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'02','2、住院票据金额' ,0,'','1',4); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'03',' 其中:(1)冲预收款' ,0,'','1',5); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'04',' (2)冲原收据' ,0,'','1',6); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'05',' (3)现金补交' ,0,'','1',7); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'06',' (4)补交支票' ,0,'','1',8); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'07',' (5)银行卡结算',0,'','1',9); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'08',' (5)医保结算' ,0,'','1',10); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'09',' (7)手工票据' ,0,'','1',11); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'12',' (6)记帐金额' ,0,'','1',13); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'38',' (7)减免病人医药费',0,'','1',14); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'45',' (8)出院病人应收款',0,'','1',15); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'11',' (9)医院支付' ,0,'','1',15.1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'13','3、预交金退出' ,0,'','1',16); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'14',' 其中:(1)冲住院费用',0,'','1',17); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'15',' (2)现金退出' ,0,'','1',18); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'16',' (3)应退还转账',0,'','1',19); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'17',' (4)现金暂存' ,0,'','1',20); --应退还银行卡 insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'77',' (5)应退还Pos' ,0,'','1',20.1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'78',' (6)应退微信' ,0,'','1',20.2); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'79',' (7)应退支付宝' ,0,'','1',20.3); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'18',' (5)四舍五入' ,0,'','1'); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'19','4、收回旧收据' ,0,'','1',21); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'10','5、补开新收据', 0,'','1',22); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'20','6、退款收据(退款收据 0 张)',0,'','1',23); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'21',' 其中:(1)退非医保款' ,0,'','1',24); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'22',' (2)退医保款 ' ,0,'','1',25); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'23',' (3)退记帐款 ' ,0,'','1',26); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'44',' (4)退出院病人应收款',0,'','1',27); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'39',' (5)退减免病人医药费',0,'','1',28); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'37',' (6)退医院支付',0,'','1',28.1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'24','7、应缴金额 ' ,0,'','1',29); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'25','8、支票收入 ' ,0,'','1',30); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'26','9、银行卡收入 ' ,0,'','1',31); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'27','10、不足转账起点' ,0,'','1',32); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'28','11、银行卡结算 ' ,0,'','1',33); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'29','10、医保结算 ' ,0,'','1',34); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'49','10、1、其中民政补助金额 ' ,0,'','1',34.1); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'30','13、付病人转诊费用' ,0,'','1',35); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'31','11、减免病人医药费' ,0,'','1',36); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'32','12、记帐应收款' ,0,'','1',37); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'33','13、出院病人应收款' ,0,'','1',38); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'34','14、四舍五入' ,0,'','1',39); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'42','16、医院支付' ,0,'','1',39.1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'54','A、微信支付' ,0,'','1',39.2); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'55','B、支付宝支付' ,0,'','1',39.3); --2016.05.27 增加 择日住院预交金转入 insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'43','17、择日住院病例' ,0,'','1',39.4); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'35','15、实交现金' ,0,'','1',40); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'36','20、8-15合计' ,0,'','1',41); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'80','21、今日上交' ,0,'','1',42); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'81','22、今日余额' ,0,'','1',43); if ls_SFXMYB='Y' then --XMBH00:=91 2、住院票据金额.农保赔付金额 XMBH00:=92农保赔付金额 --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'91',' (11)农保赔付金额',0,'','1',15.1); --insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'92','23、农保赔付金额',0,'','1',39.1); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'92','23、商业保险支付',0,'','1',39.1); end if; insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'94',' 其中非现金预交金',0,'','1',3.1); --1(1).收预交金 select NVL(sum(B.XJJE00),0),NVL(sum(B.ZZJE00),0),NVL(sum(YHKJE0),0), NVL(sum(DECODE(A.ZFFS00,8,B.YHKJE0,0)),0), NVL(sum(decode(sign(a.ZRZYLS),1,B.XJJE00,0)),0) into ls_XMJE00,ls_ZPSR00,ls_YHKSR0,LS_YHSJJE,ls_ZRZYYJ from ZY_BRJFB0 A,ZY_PJSYQK B where B.PLBH00 in (4,8) and substr(B.PJXH00,1,1) <> '-' and a.pjh000=b.pjh000 and a.jfbz00='0' and B.PJZT00='0' --作废的预交金票据不计算在内! and B.JKDID0=ls_JKDH00; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0)+NVL(ls_ZPSR00,0)+NVL(ls_YHKSR0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '40'; --1(2).退预交金 select NVL(sum(B.XJJE00),0),NVL(sum(B.ZZJE00),0),NVL(sum(YHKJE0),0),NVL(sum(DECODE(A.ZFFS00,8,B.YHKJE0,0)),0) into ls_XMJE00,ls_ZPSR00,ls_YHKSR0,LS_YHSJJE from ZY_BRJFB0 A,ZY_PJSYQK B where B.PLBH00 in (4,8) and substr(B.PJXH00,1,1) <> '-' and a.pjh000=b.pjh000 and a.jfbz00='1' and B.PJZT00='0' --作废的预交金票据不计算在内! and B.JKDID0 = ls_JKDH00; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0)+NVL(ls_ZPSR00,0)+NVL(ls_YHKSR0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '41'; --收：微信和支付宝 select nvl(sum(decode(A.ZFFS00,24,A.JFJE00,0)),0)WXZF00,nvl(sum(decode(A.ZFFS00,25,A.JFJE00,0)),0)ZFBZF0 into LS_WXZF00_SR,LS_ZFBZF0_SR from ZY_BRJFB0 A--,ZY_PJSYQK B where A.ZFFS00 in (24,25) --and substr(B.PJXH00,1,1) <> '-' --and a.pjh000=b.pjh000 and a.jfbz00='0' --and B.PJZT00='0' --作废的预交金票据不计算在内! and nvl(A.JGMC00,' ') not in ('重新结算冲正') and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and a.JKBID0=ls_JKDH00; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0)+NVL(ls_ZPSR00,0)+NVL(ls_YHKSR0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '40'; --1(2).退预交金 select nvl(sum(decode(A.ZFFS00,24,A.JFJE00,0)),0)WXZF00,nvl(sum(decode(A.ZFFS00,25,A.JFJE00,0)),0)ZFBZF0 into LS_WXZF00_TK,LS_ZFBZF0_TK from ZY_BRJFB0 A--,ZY_PJSYQK B where A.ZFFS00 in (24,25) --and substr(B.PJXH00,1,1) <> '-' --and a.pjh000=b.pjh000 --and A.PJH000 = B.FZPJH0 --2018.04.25 扣除应退款金额 and a.jfbz00='1' --and B.PJZT00='0' --作废的预交金票据不计算在内! and nvl(A.JGMC00,' ') not in ('重新结算冲正') and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and a.JKBID0 = ls_JKDH00; LS_WXZF00:=nvl(LS_WXZF00_SR,0)+nvl(LS_WXZF00_TK,0);--微信支付 LS_ZFBZF0:=nvl(LS_ZFBZF0_SR,0)+nvl(LS_ZFBZF0_TK,0);--支付宝 --榕医通重新结算金额 select sum(e.JFJE00) into LS_RYTCX0 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D,ZY_BRJFB0 e where A.BRID00=C.BRID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and e.jzdh00=a.jzdh00 and e.ZFFS00=28 and nvl(e.ZFMXBH,'A') in ('A') and nvl(e.BZ0000,' ')<>'结算后预交款' and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)='-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0)); --==1.预交金收入== select NVL(sum(B.XJJE00),0),NVL(sum(B.ZZJE00),0),NVL(sum(YHKJE0),0) into ls_XMJE00,ls_ZPSR00,ls_YHKSR0 --,ls_BEIZHU from ZY_PJSYQK B where B.PLBH00 in (4,8) and substr(B.PJXH00,1,1) <> '-'--作废的预交金票据不计算在内! and B.PJZT00='0' and B.JKDID0 = ls_JKDH00; --2018.03.28 重新算银行卡收入--begin- by ZYSF-20180409-003 select NVL(sum(DECODE(A.ZFFS00,8,B.YHKJE0,0)),0)YHSJJE into LS_YHSJJE from ZY_BRJFB0 A,ZY_PJSYQK B where B.PLBH00 in (4,8) and substr(B.PJXH00,1,1) <> '-'--作废的预交金票据不计算在内! and a.pjh000=b.pjh000 and B.PJZT00='0' and B.JKDID0 = ls_JKDH00; --2018.03.28 重新算银行卡收入--end-- BEGIN select SF_ZY_GETJKDYPJXH(ls_JKDH00,1) into ls_BEIZHU from dual; exception when others then ls_BEIZHU:=' '; END; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0)+NVL(ls_ZPSR00,0) +NVL(ls_YHKSR0,0),BEIZHU = ls_BEIZHU where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '01'; --主要是缴款统计（现金预交金）必须用此数来计算 Update BM_JKKJMX set XMJE00 = NVL(ls_ZPSR00,0) +NVL(ls_YHKSR0,0),BEIZHU = ls_BEIZHU where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '94'; --8、支票收入,张 select NVL(count(*),0) into ls_COUNT0 from ZY_PJSYQK B where B.PLBH00 in (4,8) and B.JKDID0 = ls_JKDH00 and substr(B.PJXH00,1,1) <> '-' and b.zzje00<>0 and b.pjzt00='0'; --转帐 Update BM_JKKJMX set XMMC00 = '8、支票收入'||'(共 '||to_char(ls_COUNT0)||' 份)',XMJE00=NVL(ls_ZPSR00,0) where BZ0000='1' and XMBH00 = '25' and JKDH00 = ls_JKDH00; --应退还POS select NVL(abs(sum(JFJE00)),0) into LS_YHTKJE from ZY_BRJFB0 A,ZY_PJSYQK B where A.pjh000 = B.pjh000 and B.PLBH00 =3 and A.ZFFS00 = 8 and jflbid=2 and A.JKBID0= LS_JKDH00; Update BM_JKKJMX set XMJE00 = nvl(LS_YHTKJE,0) where JKDH00 = LS_JKDH00 and BZ0000='1' and XMBH00 = '77'; --应退还POS XMBH00=77 --应退还微信 select nvl(abs(sum(JFJE00)),0) into LS_WXKJE from ZY_BRJFB0 A,ZY_PJSYQK B where A.pjh000 = B.pjh000 and B.PLBH00 =3 and A.ZFFS00 = 24 and jflbid=2 and A.JKBID0= LS_JKDH00; Update BM_JKKJMX set XMJE00 = nvl(LS_WXKJE,0) where JKDH00 = LS_JKDH00 and BZ0000='1' and XMBH00 = '78'; --应退还微信 XMBH00=78 --应退还支付宝 select nvl(abs(sum(JFJE00)),0) into LS_ZFBKJE from ZY_BRJFB0 A,ZY_PJSYQK B where A.pjh000 = B.pjh000 and B.PLBH00 =3 and A.ZFFS00 = 25 and jflbid=2 and A.JKBID0= LS_JKDH00; Update BM_JKKJMX set XMJE00 = nvl(LS_ZFBKJE,0) where JKDH00 = LS_JKDH00 and BZ0000='1' and XMBH00 = '79'; --应退还支付宝 XMBH00=79 --银行卡收入要扣除应退pos ls_YHKSR0 if ZY_YHTTKPOS='N' then --应退还现金不扣除应退pos LS_YHTKJE_TEMP:=0; else --应退还现金扣除应退还Pos LS_YHTKJE_TEMP:=LS_YHTKJE; end if; ls_YHKSR0:=nvl(ls_YHKSR0,0)-nvl(LS_YHTKJE_TEMP,0); LS_YHSJJE:=nvl(LS_YHSJJE,0)-nvl(LS_YHTKJE_TEMP,0); --2018.04.25 扣除应退款金额 --2018.03.28 重新算银行卡收入--begin- by ZYSF-20180409-003 if NVL(LS_YHSJJE,0)=0 and NVL(ls_YHKSR0,0)<>0 then LS_YHSJJE:=nvl(ls_YHKSR0,0)-nvl(LS_WXZF00,0)-nvl(LS_ZFBZF0,0)-nvl(LS_WXKJE,0)-nvl(LS_ZFBKJE,0); end if; --2018.03.28 重新算银行卡收入--end-- --9、银行卡收入,张 select NVL(count(*),0) into ls_COUNT0 from ZY_PJSYQK B where B.PLBH00 in (4,8) and substr(B.PJXH00,1,1) <> '-' and b.yhkje0<>0 and b.pjzt00='0' and b.JKDID0 = ls_JKDH00; --银行卡 Update BM_JKKJMX set XMMC00 = '9、银行卡收入'||'(共 '||to_char(ls_COUNT0)||' 份)', --2018.03.28 重新算银行卡收入 XMJE00=nvl(LS_YHSJJE,0) --XMJE00=nvl(ls_YHKSR0,0)-nvl(LS_WXZF00,0)-nvl(LS_ZFBZF0,0)-nvl(LS_WXKJE,0)-nvl(LS_ZFBKJE,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '26'; --==2、住院票据金额 select nvl(sum(HJJE00),0),sum(SF_ZY_GETNBPFJE(a.fbbh00,a.brid00,a.jzdh00,1)), NVL(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)),0), NVL(abs(sum(decode(a.FBBH00,3,0,JZJE00))),0), NVL(sum(GFJE00),0), NVL(sum(CYYSK0),0), nvl(sum(YBYL05),0), --nvl(sum(nvl(YBYL01,0)+nvl(YBYL18,0)),0), sum(nvl(YBYL18,0)), --sum(decode(sign(Instrb(','||trim(d.YBBHSB)||',',',18,')),1,0,nvl(A.YBYL18,0))),--民政救助 nvl(sum(A.SYBXZF),0) into ls_XMJE00, ls_NBPFJE,ls_YBJS00,ls_XMJE02,ls_JMBRYF,ls_CYYSK0,LS_YYZFSK, LS_MZBZJESK, LS_SYBXZF from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 --and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 --2014.01.03 修改为下面写法，如果医保转自费后判断错误 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 and substr(PJXH00,1,1)<>'-'); LS_YBJS00:=ls_YBJS00; --获取实交现金要减民政补助部分的,要从ZY_BRJFB0取 2017.03.30 select nvl(sum(a.JFJE00),0) into ls_MZBZJE1 from ZY_BRJFB0 A where A.JKBID0=ls_JKDH00 and a.JFCZY0=ls_SFY000 and a.ZFFS00=22; BEGIN select SF_ZY_GETJKDYPJXH(ls_JKDH00,2) into ls_BEIZHU from dual; exception when others then ls_BEIZHU:=' '; END; --2、住院票据金额 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0),BEIZHU = ls_BEIZHU where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '02'; --(5)医保结算 Update BM_JKKJMX set XMJE00 = NVL(ls_YBJS00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '08'; --(6)记帐金额 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE02,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '12'; --(7)减免病人医药费 Update BM_JKKJMX set XMJE00 = NVL(ls_JMBRYF,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 ='38'; --(8)出院病人应收款 Update BM_JKKJMX set XMJE00 = NVL(ls_CYYSK0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 ='45'; --(11)农保赔付金额 /* if ls_SFXMYB='Y' then Update BM_JKKJMX set XMJE00 = NVL(ls_NBPFJE,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 ='91'; Update BM_JKKJMX set XMJE00 = NVL(ls_NBPFJE,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 ='92'; end if; */ --(9)收医院支付 Update BM_JKKJMX set XMJE00 = NVL(LS_YYZFSK,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 ='11'; --(1)冲预收款,用倒算法 2.1=2-2.2-2.3-2.4-2.5-2.6-2.7-2.8-2.9 --(2)冲原收据 select NVL(abs(sum(a.ZFJE00+decode(d.sfjsdw,'N',a.GRZHZF,0)-a.CYYSK0)),0) into ls_XMJE00 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 and substr(PJXH00,1,1)='-'); Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '04'; --冲原收据 --(3)补交现金 ,正负要对抵(退款票据没有此项目) select abs(nvl(sum(C.XJYE00+C.ZZYE00),0)) into ls_XMJE00 from ZY_JZB000 C WHERE C.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00 =3) and ((C.XJYE00+C.ZZYE00<0 and C.HJJE00>0) or (C.XJYE00+C.ZZYE00>0 and C.HJJE00<0)) and c.CYYSK0=0; Update BM_JKKJMX set XMJE00 =nvl(XMJE00,0)+NVL(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '05'; --补交现金 --(4)补交支票 select NVL(sum(A.JFJE00),0) into ls_XMJE00 from ZY_BRJFB0 A,ZY_PJSYQK B,ZY_JZB000 C WHERE A.PJH000 = B.FZPJH0 and C.PJH000 = B.FZPJH0 and B.PLBH00 = 3 and A.ZFFS00 = 2 and A.JFLBID = 0 and A.JFBZ00 = 0 and A.JFJE00 > 0 and C.CXJZDH+0 = 0 and A.JKBID0 = ls_JKDH00; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '06'; --补交支票 Update BM_JKKJMX set XMJE00 =nvl(XMJE00,0)-NVL(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '05'; --补交现金 --2.(1) 冲预收款 select nvl(sum(decode(XMBH00,'02',1,-1)*XMJE00),0) into ls_CZYFY0 from BM_JKKJMX where JKDH00 = ls_JKDH00 and XMBH00 in ('02','03','04','05','06','07','08','09','12','38','45'); --冲预收款=冲预收款-收医院支付票据金额(LS_YYZFSK) Update BM_JKKJMX set XMJE00 = NVL(ls_CZYFY0,0)-nvl(LS_YYZFSK,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '03'; --冲预收款 --3(1)冲住院费用=冲住院费用-收医院支付票据金额(LS_YYZFSK) ls_CZYFY0:=ls_CZYFY0-LS_YYZFSK; Update BM_JKKJMX set XMJE00 = NVL(ls_CZYFY0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '14'; --冲住院费用 --不足转账起点 ls_BZZZQD := 0 ; Update BM_JKKJMX set XMJE00 = NVL(ls_BZZZQD,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '27'; --付病人转诊费 ls_FBRZZF := 0 ; Update BM_JKKJMX set XMJE00 = NVL(ls_FBRZZF,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '30'; begin select max(JKSJ00) into ls_KSRQSJ from BM_JKSJ00 where JKRY00=ls_SFY000 and jkbz00='1'; exception when others then ls_ksrqsj:=''; end; if (LS_KSRQSJ='')or(LS_KSRQSJ is null) then select to_char((to_date(min(JFRQ00||JFSJ00),'YYYYMMDDHH24:MI:SS')-1/(24*60*60)),'YYYYMMDDHH24:MI:SS') into ls_KSRQSJ from ZY_BRJFB0 where jkbid0 =ls_JKDH00; end if; ls_JSRQSJ:=as_JSRQ00||as_JSSJ00; --==3、预交金退出== select count(*),NVL(sum(C.XJJE00+C.YHKJE0+C.ZZJE00),0) into ls_COUNT0,ls_XMJE00 from ZY_PJSYQK C where C.PLBH00 in (4,8) and C.SFDY00 = 'Y' and C.HSCZY0 = ls_SFY000 and C.PJZT00='0' and C.HSRQ00 >=substr(ls_KSRQSJ,1,8) and C.HSRQ00 <=substr(ls_JSRQSJ,1,8) and C.HSRQ00||C.HSSJ00 >ls_KSRQSJ and C.HSRQ00||C.HSSJ00 <=ls_JSRQSJ; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0),XMMC00='3、预交金退出(退出'||to_char(ls_COUNT0)||' 份)' where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '13'; --预交金退出 --3(3)计算应退还转账 select NVL(abs(sum(JFJE00)),0) into ls_XMJE01 from zy_brjfb0 A, zy_pjsyqk B where A.pjh000 = B.pjh000 and B.PLBH00 =3 and A.ZFFS00 = 2 and jflbid=2 and A.JKBID0= ls_JKDH00; Update BM_JKKJMX set XMJE00 = ls_XMJE01 where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '16'; --计算应退还转账 --3(4)现金暂存的 select NVL(sum(CYYTK0),0) into ls_CYYTK0 from ZY_JZB000 A,zy_pjsyqk B where A.PJH000 = B.PJH000 and B.PLBH00 =3 --and substr(B.PJXH00,1,1) <> '-' and B.PJZT00= '0' and B.BDBZ00='0' and B.JKDID0 = ls_JKDH00; Update BM_JKKJMX set XMJE00 = ls_CYYTK0 where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '17'; --现金暂存的 --3(2)应退还现金,用倒算方法 = 退出金额 - 冲住院费用 - 转帐 - 现金暂存 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) -ls_CZYFY0 - NVL(ls_XMJE01,0)-NVL(ls_CYYTK0,0) where BZ0000='1' and XMBH00 = '15' and JKDH00 = ls_JKDH00; --计算应退还现金 --20160412 by ZYSF-20151210-001 应退还现金-应退款pos -- ZY_CYJSTKAYJJ=Y 出院结算时ZY_PJSYQK会写入一笔总额为退款的发票 if ZY_CYJSTKAYJJ not in ('Y') then Update BM_JKKJMX set XMJE00 =nvl(XMJE00,0)-nvl(LS_YHTKJE,0) where BZ0000='1' and XMBH00 = '15' and JKDH00 = ls_JKDH00; end if; --select sum(a.XJYE00) from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D -- where A.BRID00=C.BRID00 and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 and -- A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 ); --四舍五入金额 select nvl(sum(JFJE00),0) into ls_XMJE35 from zy_brjfb0 A,zy_pjsyqk B where B.PLBH00 =3 and A.pjh000 = B.pjh000 --and c.cxjzdh = 0 --and substr(B.PJXH00,1,1) <> '-' --and a.jflbid= 2 --and B.PJZT00= '0' and A.ZFFS00 = 5 and a.jkbid0 =ls_JKDH00; --Update BM_JKKJMX set XMJE00 = NVL(-ls_XMJE35,0) where -- JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '18'; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE35,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '34'; --四舍五入金额 --select nvl(sum(JFJE00),0) into ls_XMJE35 from zy_brjfb0 A,zy_pjsyqk B -- where B.PLBH00 =3 -- and A.pjh000 = B.pjh000 --and substr(B.PJXH00,1,1) <> '-' -- and a.jflbid=2 -- and B.PJZT00= '0' -- and A.ZFFS00 = 5 -- and a.jkbid0 =ls_JKDH00; --==收回/补打住院票据 select NVL(sum(xjje00+zzje00+yhkje0),0) into ls_XMJE00 from ZY_PJSYQK B where B.PLBH00 in(3,10) --and PJZT00='0' and JKDID0 = ls_JKDH00 and B.BDBZ00='1';---补打发票 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '19'; --收回旧收据 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '10'; --补开新收据 --==6、退款收据 select abs(nvl(sum(HJJE00),0)),--退款收据 --abs(NVL(sum(SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF),0)),--退款的医保款 --abs(NVL(sum(decode(D.YBBHSB,'Y',decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0,SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0)),0)),--退款的医保款 abs(NVL(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)),0)), abs(NVL(sum(decode(a.FBBH00,3,0,JZJE00)),0)),--退记账款 abs(NVL(sum(GFJE00),0)),--退减免款 abs(NVL(sum(CYYSK0),0)),--退出院病人应收款 sum(nvl(YBYL05,0)), --单病种差额 --nvl(sum(nvl(YBYL01,0)+nvl(ybyl18,0)),0), --民政补助 sum(nvl(ybyl18,0)), --民政补助 --sum(decode(sign(Instrb(','||trim(d.YBBHSB)||',',',18,')),1,0,nvl(A.YBYL18,0))),--民政救助 abs(nvl(sum(A.SYBXZF),0)), count(*) into ls_XMJE00,ls_YBJS00,ls_XMJE02,ls_JMBRYF,ls_CYYSK0,LS_YYZFTK, LS_MZBZJETK, LS_SYBXZFTK, ls_COUNT0 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 --and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 --2014.01.03 修改为下面写法，如果医保转自费后判断错误 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 and substr(PJXH00,1,1)='-'); ls_YBJS00:=ls_YBJS00; if ls_YBJECF='Y' then --MODIFY BY ZYSF-20110914-006 --==2、1 农保住院票据金额 select NVL(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)),0) into ls_NBZYJE from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 --and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 --2014.01.03 修改为下面写法，如果医保转自费后判断错误 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 and substr(PJXH00,1,1)<>'-') and D.SFXNH0='1'; --==6.1 农保退款收据 select abs(NVL(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)),0)) into ls_NBZYTK from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 --and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 --2014.01.03 修改为下面写法，如果医保转自费后判断错误 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 and substr(PJXH00,1,1)='-') and D.SFXNH0='1'; --XMBH00:=93 农保结算金额 ls_NBJE00:=NVL(ls_NBZYJE,0)-NVL(ls_NBZYTK,0); insert into BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00) values(ls_JKDH00,'93','10.1、农保结算',ls_NBJE00,'','1',35); else ls_NBJE00:=0; end if; --6、退款收据 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0), XMMC00 = '6、退款收据'||'(共 '||to_char(ls_COUNT0)||' 份)' where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '20'; --6(2)退款的医保款 Update BM_JKKJMX set XMJE00 = NVL(ls_YBJS00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '22'; --6(3)退记账款 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE02,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '23'; --6(4)退减免款 Update BM_JKKJMX set XMJE00 = NVL(ls_JMBRYF,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '39'; --退减免款 --6(5)退出院病人应收款 Update BM_JKKJMX set XMJE00 = NVL(ls_CYYSK0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '44'; --6(6)退医院支付 Update BM_JKKJMX set XMJE00 = NVL(LS_YYZFTK,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '37'; --6(1)退非医保款=退非医保款+退医院支付票据金额(LS_YYZFTK) select sum(nvl(decode(XMBH00,'20',1,-1)*XMJE00,0)) into ls_XMJE00 from bm_jkkjmx where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 in ('20','22','23','39','44'); Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0)+nvl(LS_YYZFTK,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '21'; --重新计算住院票据的合计金额 --select NVL(sum(XMJE00),0) into ls_XMJE00 from BM_JKKJMX where -- JKDH00 = ls_JKDH00 and XMBH00 -- in ('03','04','05','06','07','08','09','11','12','38','45'); --Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) where -- JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '02'; --住院票据合计金额 --右边的医保结算 select sum(nvl(xmje00,0)) into ls_xmje00 from bm_jkkjmx where jkdh00=ls_JKDH00 and bz0000='1' and xmbh00='08'; select sum(nvl(xmje00,0)) into ls_xmje01 from bm_jkkjmx where jkdh00=ls_JKDH00 and bz0000='1' and xmbh00='22'; ls_YBJS00:=NVL(ls_xmje00,0)-nvl(ls_xmje01,0); Update BM_JKKJMX set XMJE00 =ls_YBJS00-ls_NBJE00 where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '29'; ---医保结算 --右边的减免病人医药费 select sum(nvl(xmje00,0)) into ls_xmje00 from bm_jkkjmx where jkdh00=ls_JKDH00 and bz0000='1' and xmbh00='38'; select sum(nvl(xmje00,0)) into ls_xmje01 from bm_jkkjmx where jkdh00=ls_JKDH00 and bz0000='1' and xmbh00='39'; ls_JMBRYF:=NVL(ls_xmje00,0)-nvl(ls_xmje01,0); Update BM_JKKJMX set XMJE00 =ls_JMBRYF where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '31'; --右边的出院病人应收款 select sum(nvl(xmje00,0)) into ls_xmje00 from bm_jkkjmx where jkdh00=ls_JKDH00 and bz0000='1' and xmbh00='45'; select sum(nvl(xmje00,0)) into ls_xmje01 from bm_jkkjmx where jkdh00=ls_JKDH00 and bz0000='1' and xmbh00='44'; ls_CYYSK0:=NVL(ls_xmje00,0)-nvl(ls_xmje01,0); Update BM_JKKJMX set XMJE00 = nvl(ls_CYYSK0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '33'; --右边的记帐应收款 ls_JZYSK0:=0; select XMJE00 into ls_XMJE00 FROM BM_JKKJMX where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '12'; select XMJE00 into ls_XMJE01 FROM BM_JKKJMX where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '23'; ls_JZYSK0:=NVL(LS_XMJE00,0)-NVL(LS_XMJE01,0); Update BM_JKKJMX set XMJE00 = NVL(LS_JZYSK0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '32'; --记帐应收款 --医院支付金额(LS_YYZFJE) --医院支付金额=收医院支付票据金额+退医院支付票据金额 如果收为正，冲时会为负数，所以用+ LS_YYZFJE:=NVL(LS_YYZFSK,0)+NVL(LS_YYZFTK,0); Update BM_JKKJMX set XMJE00 = NVL(LS_YYZFJE,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '42'; --微信支付 update BM_JKKJMX set XMJE00 = nvl(LS_WXZF00,0) where JKDH00 = LS_JKDH00 and BZ0000='1' and XMBH00 = '54'; --支付宝 update BM_JKKJMX set XMJE00 = nvl(LS_ZFBZF0,0) where JKDH00 = LS_JKDH00 and BZ0000='1' and XMBH00 = '55'; --交款表YHKJE0（银行卡金额扣除掉微信和支付宝） update BM_JKB000 set YHKJE0=nvl(YHKJE0,0)-nvl(WXZF00,0)-nvl(ZFBZF0,0) where JKDH00=LS_JKDH00; --择日住院预交金转入 Update BM_JKKJMX set XMJE00 = NVL(ls_ZRZYYJ,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '43'; --其中民政补助金额 LS_XMJE49:=nvl(LS_MZBZJESK,0)+nvl(LS_MZBZJETK,0); Update BM_JKKJMX set XMJE00 = NVL(LS_XMJE49,0) where JKDH00 = LS_JKDH00 and BZ0000='1' and XMBH00 = '49'; --计算应缴金额 select NVL(sum(XMJE00),0) into ls_XMJE00 from BM_JKKJMX where JKDH00 = ls_JKDH00 and XMBH00 in ('01','02','16','17'); select NVL(sum(XMJE00),0) into ls_XMJE01 from BM_JKKJMX where JKDH00 = ls_JKDH00 and XMBH00 in ('13','20'); --应缴金额 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) - NVL(ls_XMJE01,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '24'; Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) - NVL(ls_XMJE01,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '36'; --应缴金额 if ls_SFXMYB='Y' then Update BM_JKKJMX set XMJE00 = nvl(LS_SYBXZF,0)-nvl(LS_SYBXZFTK,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 ='92'; end if; if ZY_YHTTKPOS='N' then --银行卡扣除掉应退POs 所以实交现金应加上应退POS LS_YHTKJE_TEMP:=LS_YHTKJE; else LS_YHTKJE_TEMP:=0; end if; ls_bz0000:='ls_XMJE00='||NVL(ls_XMJE00,0)||',ls_XMJE01='||ls_XMJE01 ||',ls_ZPSR00='|| NVL(ls_ZPSR00,0) ||',ls_YHKSR0='|| NVL(ls_YHKSR0,0) ||',LS_WXKJE='|| NVL(LS_WXKJE,0) ||',LS_ZFBKJE='|| NVL(LS_ZFBKJE,0) ||',ls_BZZZQD='|| NVL(ls_BZZZQD,0) ||',ls_YHKJS0='|| NVL(ls_YHKJS0,0) ||',LS_YHTKJE_TEMP='|| NVL(LS_YHTKJE_TEMP,0) ||',ls_YBJS00='|| NVL(ls_YBJS00,0) ||',ls_FBRZZF='|| NVL(ls_FBRZZF,0) ||',ls_JMBRYF='|| NVL(ls_JMBRYF,0) ||',ls_JZYSK0='|| NVL(ls_JZYSK0,0) ||',ls_XMJE35='|| NVL(ls_XMJE35,0) ||',ls_CYYSK0='|| NVL(ls_CYYSK0,0) ||',LS_YYZFJE='|| NVL(LS_YYZFJE,0) ||',LS_SYBXZF='|| NVL(LS_SYBXZF,0) ||',LS_SYBXZFTK='|| NVL(LS_SYBXZFTK,0) ||',ls_ZRZYYJ='|| NVL(ls_ZRZYYJ,0) ||',ls_MZBZJE1='|| NVL(ls_MZBZJE1,0); --实交现金 Update BM_JKKJMX set XMJE00 = NVL(ls_XMJE00,0) -NVL(ls_XMJE01,0) -NVL(ls_ZPSR00,0) -- -NVL(ls_YHKSR0,0) -(nvl(ls_YHKSR0,0)-nvl(LS_WXKJE,0)-nvl(LS_ZFBKJE,0)) ---nvl(LS_WXKJE,0) --微信退款 ---nvl(LS_ZFBKJE,0) --支付宝退款 ---nvl(LS_RYTCX0,0) --榕医通冲销 -NVL(ls_BZZZQD,0) -NVL(ls_YHKJS0,0) +NVL(LS_YHTKJE_TEMP,0) -NVL(ls_YBJS00,0) -NVL(ls_FBRZZF,0) -NVL(ls_JMBRYF,0) -NVL(ls_JZYSK0,0) -NVL(ls_XMJE35,0) -NVL(ls_CYYSK0,0) -NVL(LS_YYZFJE,0) -- -(nvl(LS_SYBXZF,0)-nvl(LS_SYBXZFTK,0)) -nvl(ls_ZRZYYJ,0) -NVL(ls_MZBZJE1,0)--11.1、其中民政补助金额LS_XMJE49-->ls_MZBZJE1 where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '35'; --实交现金 --取上期余额 insert into tmp_jkrz00(jkdh00,bz0000) values(ls_JKDH00,ls_bz0000); begin select xmje00 into ls_xjje00 from bm_jkkjmx where xmbh00='81' and jkdh00=( select max(jkdh00) from bm_jkb000 where jkdh00ls_XMJE01 then as_YHMSG0:='缴款失败!'; as_SYSMSG:='实际应交现金'||to_char(ls_XMJE00)||'与缴款表的应交现金'||to_char(ls_XMJE01)||'不一致'; ROLLBACK; RETURN; end if; */ if as_COMMIT='Y' then COMMIT; end if; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='系统查找不到数据！'; as_SYSMSG:=SQLERRM; ROLLBACK; WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_JKB000_REG000('||as_JSRQ00||','||as_JSSJ00||','||to_char(ad_CZY000)||','||as_CZYXM0||','||as_SFY000||','||as_YJJXH0||',as_YHMSG0,as_SYSMSG,as_JKDH00)',1,150); ROLLBACK; END;