--##2021.06.17 update by nichaolin for zysf --增加通用字典参数SFYJZFPKJSBSFYXJK_ZY对于收费员电子发票开具失败不能缴款的问题,增加BM_JKB000.WBPJLY字段的写入 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, --预交金票据的外部流水号 ad_CZYKS0 in number, --操作员科室 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_JKDH00 out varchar, --缴款单号 as_COMMIT in varchar2 default 'Y', --是否提交 ad_YYID00 in varchar default '0' --医院ID ) -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2013.01.08 加 ad_CZYKS0 -- jlg 2013.06.05 缴款结账时ZY_JZB000也记录JKBID0 -- qks 2013.11.12 缴款结账时ZY_BRJFB0.PLBH00=20不能统计到预交金收入、退出部分 -- qks 2013.11.22 住院结账单中的“冲销张数”值有错误 -- jlg 2016.04.12 对于没有领取票据的处理 ZYSF9-20160411-001 -- qks 2017.01.13 预交金没启用领用票据管理，缴款有问题; for ZYSF9-20170113-001 -- jlg 2017.10.08 处理结清挂账金额(住院呆账结算单金额)没有计入结账单的问题 ZYSF9-20170928-002 -- jlg 2018.04.09 增加WXZF00,ZFBZF0 的写入 ZYSF9-20180402-001 -- qks 2018.04.25 参数ZY_SFQYHTJSPJKPJK启用时,BM_JKB000.PJQSH0,PJZZH0,PJZS00,PJZFH0都使用HTFPXH来计算; for ZYSF9-20180425-002 -- qks 2018.09.12 重新计算表BM_JKKJMX中：3、预交金退出 ; for ZYSF9-20180806-001 -- pwt 2018.12.03 增加ad_YYID00,满足分院时的缴款; for ZYSF9-20181127-003 -- liwm 2019.06.11 ls_COUNT0长度增加 ZYSF9-20190611-002 -- linzetao 2020.01.06 自动缴款参数SF_ZJJQYTYJK='Y'时自动缴款期间限制收费员操作 ZYSF9-20191227-002 -- jlg 2020.03.20 增加处理BM_JKB000.PJQSH0,PJZZH0，如果ZY_PJSYQK.WBPJXH有值，则优先按该值处理 ZYSF9-20200319-005 -- linzetao 2020.07.16 处理启用博思电子票据后缴款票据分段问题 ZYSF9-20200521-003 -- jlg 2020.09.14 生成BM_JKMXB0时增加兼容历史未去空数据的处理 ZYSF9-20200914-004 -- ncl 2021.06.17 增加通用字典参数SFYJZFPKJSBSFYXJK_ZY对于收费员电子发票开具失败不能缴款的问题,增加BM_JKB000.WBPJLY字段的写入 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_YYID00 XT_YYXX00.YYID00%type; ls_LASTRQ char(8); ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_KSRQSJ char(16); ls_JSRQSJ char(16); ls_COUNT0 number(12); 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_NBJE00 number(12,2); --农合金额 ls_NBZYJE number(12,2); --农保住院票据金额 ls_NBZYTK number(12,2); --农保住院票据退款金额 ls_DBZCE0 number(12,2); --单病种差额 ls_DBZCE1 number(12,2); --单病种差额 ls_LSRQSJ char(16); --上次结账的日期时间 ls_SFXMYB varchar2(10); --是否厦门医保接口 ls_YBJECF varchar2(10); --医保结算金额是否把农保独立出来 ls_DZDJK0 XT_XTCS00.VALUE0%type; --住院呆账结算单是否计入收费员结账金额 ls_ZYHTFP XT_XTCS00.VALUE0%type; --是否启用住院航天金税票据开票接口，1开启0关闭，默认0 ls_SFZDJK XT_XTCS00.VALUE0%type; --是否启用自动缴款参数 ls_WBPJXH ZY_PJSYQK.WBPJXH%type; --外部票据序号 ls_WBLYID ZY_PJSYQK.WBLYID%type; --外部领用ID ls_WBPJLY ZY_PJSYQK.WBPJLY%type; --外部票据来源 Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); cursor CUR_ZY_ZFFP00 is --计算作废票据 select A.PLBH00,count(distinct a.HTFPXH) HTZFH0,count(*) PJZFH0,nvl(a.PJLYID,0) PJLYID,nvl(a.WBLYID,0) WBLYID,sum(a.HJJE00) HJJE00 from ZY_PJSYQK A where A.JKDID0 = ls_JKDH00 and PJZT00='3' and substrb(a.PJXH00,1,1)<>'-' group by A.PLBH00,nvl(a.PJLYID,0),nvl(a.WBLYID,0); cursor CUR_ZY_FPJE00 is --计算现金发票的金额 select a.PLBH00,nvl(a.PJLYID,0) PJLYID,nvl(a.WBLYID,0) WBLYID, sum(HJJE00)HJJE00, sum(ZFJE00)ZFJE00, sum(JZJE00)JZJE00, sum(JMJE00)JMJE00, sum(YHJE00)YHJE00, sum(decode(sign(HJJE00),1,0,HJJE00))HJJE01, sum(decode(sign(ZFJE00),1,0,ZFJE00))ZFJE01, sum(decode(sign(JZJE00),1,0,JZJE00))JZJE01, sum(decode(sign(JMJE00),1,0,JMJE00))JMJE01, sum(decode(sign(YHJE00),1,0,YHJE00))YHJE01, sum(decode(sign(HJJE00),-1,1,0))HSZFH0 from ZY_PJSYQK a where a.JKDID0 = ls_JKDH00 and a.PJZT00 in ('0','1','2') and ((ls_DZDJK0='0' and a.PLBH00<>20) or (ls_DZDJK0='1')) group by a.PLBH00,nvl(a.PJLYID,0),nvl(a.WBLYID,0); cursor CUR_ZY_ZFJE00 is --计算支付金额 select PLBH00,nvl(PJLYID,0) PJLYID,nvl(WBLYID,0) WBLYID, sum(decode(ZFFS00,1 ,JFJE00+JFJE01,0)) XJ0000,sum(decode(ZFFS00,1 ,JFJE01,0)) XJ0001, sum(decode(ZFFS00,2 ,JFJE00+JFJE01,0)) ZZ0000,sum(decode(ZFFS00,2 ,JFJE01,0)) ZZ0001, sum(decode(ZFFS00,8 ,JFJE00+JFJE01,0)) YHK000,sum(decode(ZFFS00,8 ,JFJE01,0)) YHK001, sum(decode(ZFFS00,18,JFJE00+JFJE01,0)) DZQB00,sum(decode(ZFFS00,18,JFJE01,0)) DZQB01, sum(decode(ZFFS00,19,JFJE00+JFJE01,0)) YJJ000,sum(decode(ZFFS00,19,JFJE01,0)) YJJ001, sum(decode(ZFFS00,3 ,JFJE00+JFJE01,0)) GRZH00, sum(decode(ZFFS00,4 ,JFJE00+JFJE01,0)) TCJJ00, sum(decode(ZFFS00,12,JFJE00+JFJE01,0)) YBQTZF, sum(decode(ZFFS00,10,JFJE00+JFJE01,0)) SBZH00, sum(decode(ZFFS00,11,JFJE00+JFJE01,0)) SBTC00, sum(decode(ZFFS00,16,JFJE00+JFJE01,0)) GWYBZ0, sum(decode(ZFFS00,24,JFJE00+JFJE01,0)) WXZF00, sum(decode(ZFFS00,25,JFJE00+JFJE01,0)) ZFBZF0 from (select PLBH00,ZFFS00, decode(sign(JFJE00),-1,0,JFJE00) JFJE00, decode(sign(JFJE00),-1,JFJE00,0) JFJE01, (select PJLYID from ZY_PJSYQK s where s.FZPJH0=a.PJH000 and rownum=1) PJLYID, (select nvl(WBLYID,0) from ZY_PJSYQK s where s.FZPJH0=a.PJH000 and rownum=1) WBLYID from ZY_BRJFB0 a where a.JKBID0=ls_JKDH00 and a.JFBZ00<>'2' and a.JSLX00<>'3' and ((ls_DZDJK0='0' and a.PLBH00<>20) or (ls_DZDJK0='1')) ) group by PLBH00,nvl(PJLYID,0),nvl(WBLYID,0); cursor C_YYID is select distinct decode(ad_YYID00, '0', YYID00, ad_YYID00) YYID00 from (select distinct (select nvl(YYID00,'0') from BM_BMBM00 where a.CZYKS0=BMBH00) YYID00 from ZY_PJSYQK a where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and CZY000 = ls_SFY000 and ls_SFY000>0 and JKDID0 = 0 union all select YYID00 from XT_YYXX00 where ls_SFY000=0) t; --电子钱包缴款 cursor C_PJSYQK_JKDID is select PJH000,WBPJLY,PLBH00,WBPJXH from ZY_PJSYQK where JKDID0=ls_JKDH00 --and (PJXH00 is null or PJXH00='0' or PJXH00='-0' or PJXH00='-') --本条件注释，因为换开纸质发票PJXH00是有值的,只需要下面条件就可以了 ncl and WBPJLY is not null and WBPJXH is not null --and (WBLYID is null or WBLYID=0) order by PLBH00,WBPJLY,WBPJXH; --增加按WBPJLY排序 begin ls_czrq00:=as_JSRQ00; ls_czsj00:=as_JSSJ00; select nvl(max(trim(Value0)),5) into ls_JKTQSJ from XT_XTCS00 where NAME00 = 'XT_JKTQSJ'; select nvl(max(trim(Value0)),'N') into ls_SFXMYB from XT_XTCS00 where NAME00 = 'ZY_SFXMYBJK'; select nvl(max(trim(Value0)),'N') into ls_YBJECF from XT_XTCS00 where NAME00 = 'ZY_JKYBSFCF'; select nvl(max(trim(VALUE0)),'0') into ls_DZDJK0 from XT_XTCS00 where NAME00 = 'ZY_DZJSDSFJRSFYJZJE'; --住院呆账结算单是否计入收费员结账金额 select nvl(max(trim(VALUE0)),'0') into ls_ZYHTFP from XT_XTCS00 where NAME00 = 'ZY_SFQYHTJSPJKPJK'; --是否启用住院航天金税票据开票接口，1开启0关闭，默认0 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; if ad_CZY000>0 then --是否启用自动缴款参数 select nvl(regexp_substr(nvl(max(value0),'N,N,N'),'[^,]+',1,2),'N') into ls_SFZDJK from XT_XTCS00 where NAME00='SF_ZJJQYTYJK'; --只有自动缴款参数SF_ZJJQYTYJK='Y'才启用自动缴款限制 if ls_SFZDJK='Y' and to_char(sysdate,'hh24:mi') between '00:00' and '00:10' then as_YHMSG0:='00:00-00:10系统准备进行自动缴款，此区间暂停本操作！'; as_SYSMSG:=SQLERRM; 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_DBZCE0:= 0; ls_DBZCE1:= 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; select nvl(max(Trim(Value0)),'Y') into ls_YXTDJK from XT_XTCS00 where name00='SF_SFYXSFYDJK'; if ls_YXTDJK='N' and ls_SFY000<>ad_CZY000 and ad_CZY000>0 then--非超级用户，不能替代缴款 as_YHMSG0:='对不起，只能由收费员本人进行缴款登记，不能由他人代劳！'; as_SYSMSG:=SQLERRM; rollback; return; end if; --select YYID00 into ls_YYID00 from XT_YYXX00 where rownum=1; ls_YYID00 := ad_YYID00; update ZY_PJSYQK set YYID00 = (select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where JKDID0 = 0 and YYID00 is null; update ZY_BRJFB0 set YYID00=(select nvl(YYID00,ls_YYID00) from BM_BMBM00 where CZYKS0=BMBH00) where JKBID0 = 0 and YYID00 is null; --未缴过款的 /* --代理未转入的，不能缴款 select count(*) into ls_COUNT0 from ZY_PJSYQK where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and CZY000 = ls_SFY000 and JKDID0 = 0 and DZQBJE=0 and DLR000=ls_SFY000 and DLZRRQ is null; if ls_COUNT0>0 then as_YHMSG0:='代理未转入，不能缴款！'; as_SYSMSG:=SQLERRM; rollback; return; end if;*/ --有可能一个收费员到多个分院操作，因此一次缴款只清算一个分院的 for yy in C_YYID loop ls_YYID00:=yy.YYID00; select SQ_BM_JKB000_JKBID0.nextval into ls_JKDH00 from dual; select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; select nvl(max(JKRQ00),'20000101') into ls_LASTRQ from BM_JKB000 where SFY000=ls_SFY000 and YYID00=ls_YYID00 and MZBZ00='1'; select nvl(max(JKRQ00||JKSJ00),'2000010100:00:00') into ls_LSRQSJ from BM_JKB000 where SFY000=ls_SFY000 and YYID00=ls_YYID00 and MZBZ00='1'; if ls_SFY000>0 then--正常缴款 --将该收费员在限定的时间段内所做的所有票据设为已缴款标志 update ZY_PJSYQK t set JKDID0 = ls_JKDH00 where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and CZY000 = ls_SFY000 and JKDID0 = 0 and DZQBJE=0 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --再将回收的票据打上标志 update ZY_PJSYQK t set HSJKID = ls_JKDH00 where HSRQ00 <= as_JSRQ00 and HSRQ00>=ls_LASTRQ and HSRQ00||HSSJ00 <= as_JSRQ00||as_JSSJ00 and HSCZY0 = ls_SFY000 and HSJKID+0 = 0 and DZQBJE=0 --and SFDY00 = 'Y' --同时还应该是已打印过的票据 and substr(PJXH00,1,1) <> '-' --首位是'-'的说明是系统添加的 -- and nvl(YYID00,'0')=ls_YYID00; and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --SFYJZFPKJSBSFYXJK_ZY：（住院）收费员存在电子发票开具失败的票据记录，是否允许结账缴款，0不允许，1允许，默认1 if SF_XT_TYZD00('第三方电子发票平台接口','SFYJZFPKJSBSFYXJK_ZY') = '0' then select count(*) into ls_COUNT0 from ZY_PJSYQK a where a.JKDID0 = ls_JKDH00 and PLBH00 = '3' and a.PJXH00 ='0' and nvl(a.WBPJXH,'0') = '0' and a.PJZT00 = '0'; if ls_COUNT0 > 0 then as_YHMSG0:='该收费员当前有存在未开具成功的电子票，系统限制不允许结账缴款！请先补充开具这些电子票后再结账缴款'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; ls_WBPJLY:= '00'; --取出缴款单号下外部票据序号不为空的所有票据 for pjsy in C_PJSYQK_JKDID loop if ls_PLBH00 <> pjsy.PLBH00 or ls_WBPJLY <> pjsy.WBPJLY or SF_XT_QHWBPJXHSFLX(ls_WBPJXH,pjsy.WBPJXH) = '0' then --票据序号不连续, 取新的外部领用id select SQ_BM_PJLYQK_PJLYID.nextval into ls_WBLYID from dual; end if; update ZY_PJSYQK set WBLYID=ls_WBLYID where PJH000=pjsy.PJH000; ls_PLBH00 := pjsy.PLBH00; ls_WBPJLY := pjsy.WBPJLY; ls_WBPJXH := pjsy.WBPJXH; end loop; update ZY_BRJFB0 t set JKBID0 = ls_JKDH00 where JFRQ00||'*' <= as_JSRQ00||'*' and JFRQ00||JFSJ00 <=as_JSRQ00||as_JSSJ00 and JFCZY0 = ls_SFY000 and ZFFS00<>18 and JKBID0 = 0 -- and nvl(YYID00,'0')=ls_YYID00; --未缴过款的 and exists (select 1 from BM_BMBM00 where t.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); update ZY_JZB000 set JKBID0 = ls_JKDH00 where JZRQ00 <= as_JSRQ00 and JZRQ00||JZSJ00 <= as_JSRQ00||as_JSSJ00 and JZRQ00||JZSJ00 >ls_LSRQSJ --大于上次缴款时间 and JZY000 = ls_SFY000 and JKBID0 = 0; elsif ls_SFY000=0 then --统一缴电子钱包款(包括自助机款) --将该收费员在限定的时间段内所做的所有票据设为已缴款标志 update ZY_PJSYQK a set JKDID0 = ls_JKDH00 where DYRQ00||'*' <= as_JSRQ00||'*' and DYRQ00||DYSJ00 <=as_JSRQ00||as_JSSJ00 and --not exists (select 1 from BM_YGBM00 where YGBH00=a.CZY000 and instrb(YGXZ00,'3')>0) and JKDID0 = 0 and DZQBJE<>0 and exists (select 1 from BM_BMBM00 where a.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); --再将回收的票据打上标志 update ZY_PJSYQK a set HSJKID = ls_JKDH00 where HSRQ00 <= as_JSRQ00 and HSRQ00>=ls_LASTRQ and HSRQ00||HSSJ00 <= as_JSRQ00||as_JSSJ00 --and not exists (select 1 from BM_YGBM00 where YGBH00=a.HSCZY0 and instrb(YGXZ00,'3')>0) and HSJKID+0 = 0 and DZQBJE<>0 --and SFDY00 = 'Y' --同时还应该是已打印过的票据 and substr(PJXH00,1,1) <> '-' --首位是'-'的说明是系统添加的 --and nvl(YYID00,'0')=ls_YYID00; and exists (select 1 from BM_BMBM00 where a.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); update ZY_BRJFB0 a set JKBID0 = ls_JKDH00 where JFRQ00||'*' <= as_JSRQ00||'*' and JFRQ00||JFSJ00 <=as_JSRQ00||as_JSSJ00 --and not exists (select 1 from BM_YGBM00 where YGBH00=a.JFCZY0 and instrb(YGXZ00,'3')>0) and ZFFS00=18 and JKBID0 = 0 --and nvl(YYID00,'0')=ls_YYID00; --未缴过款的 and exists (select 1 from BM_BMBM00 where a.CZYKS0=BMBH00 and nvl(YYID00,'0')=ls_YYID00); end if; --2010.07.30 ZY_BRJFB0增加PLBH00票类编号,字段在缴款时填写 由于可能结算时可能不产生ZY_PJSYQK 记录,所以发票要根据结账表来更新 --预交金 update ZY_BRJFB0 a set PLBH00=(select PLBH00 from ZY_PJSYQK where FZPJH0=a.PJH000 and rownum=1) where JKBID0 = ls_JKDH00 and PLBH00 is null; --发票 update ZY_BRJFB0 a set PLBH00=1 where JKBID0 = ls_JKDH00 and PLBH00 is null and exists (select 1 from ZY_JZB000 where JZDH00=a.JZDH00); --添加结帐明细表的内容(既发票项目的内容) insert into BM_JKMXB0(JKDH00,SFXMID,XMMC00,XMJE00,BZ0000) select ls_JKDH00,b.XMBH00,trim(b.XMMC00),sum(b.XMJE00),'1' from ZY_PJSYQK a,ZY_PJSYMX b where A.PJH000=b.PJH000 and JKDID0 = ls_JKDH00 group by b.XMBH00,trim(b.XMMC00); --插入票据序号 insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00, PJQSH0, PJZZH0, SFY000,MZBZ00,PJLYID,YYID00,HJJE00,ZFJE00,JZJE00,GFJE00, XJJE00,ZZJE00,YHKJE0,DZQBJE,YJJZF0,GRZHZF,TJJJZF,YBQTZF,SBGRZH, SBTCJJ,GWYBZ0,WXZF00,ZFBZF0,HSHJJE,HSZFH0,HSZFE0,HSJZJE,HSGFJE,HSZFJE, HSZZJE,HSYHK0,HSDZQB,HSYJJ0,PJZFH0,PJZFJE,PJZS00,WBPJLY) select ls_JKDH00,ls_PJH000,ad_CZY000,ls_CZRQ00,ls_CZSJ00,PLBH00, --ltrim(min(lpad(trim(pjxh00),30,' '))), --ltrim(max(lpad(trim(pjxh00),30,' '))), (case when ls_ZYHTFP='1' and PLBH00=3 then nvl(ltrim(min(lpad(trim(htfpxh),30,' '))),' ') else ltrim(min(lpad(trim(nvl(WBPJXH,PJXH00)),30,' '))) end), (case when ls_ZYHTFP='1' and PLBH00=3 then nvl(ltrim(max(lpad(trim(htfpxh),30,' '))),' ') else ltrim(max(lpad(trim(nvl(WBPJXH,PJXH00)),30,' '))) end), ls_SFY000,'1',decode(nvl(WBLYID,0),0,nvl(PJLYID,0),WBLYID),ls_YYID00,0,0,0,0, 0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,(case when ls_ZYHTFP='1' and PLBH00=3 then count(distinct HTFPXH) else count(*) end),max(nvl(a.WBPJLY,'00')) from ZY_PJSYQK A where substr(A.PJXH00,1,1) <> '-' and a.JKDID0 = ls_JKDH00 and a.PLBH00 <> 5 group by PLBH00,decode(nvl(WBLYID,0),0,nvl(PJLYID,0),WBLYID); --没有产生的票据，补插入 insert into BM_JKB000(JKDH00,PJH000,CZY000,JKRQ00,JKSJ00,PJBH00,PJQSH0,PJZZH0, SFY000,MZBZ00,PJLYID,YYID00,HJJE00,ZFJE00,JZJE00,GFJE00, XJJE00,ZZJE00,YHKJE0,DZQBJE,YJJZF0,GRZHZF,TJJJZF,YBQTZF,SBGRZH, SBTCJJ,GWYBZ0,WXZF00,ZFBZF0,HSHJJE,HSZFH0,HSZFE0,HSJZJE,HSGFJE,HSZFJE, HSZZJE,HSYHK0,HSDZQB,HSYJJ0,PJZFH0,PJZFJE,WBPJLY) select ls_JKDH00,ls_PJH000,ad_CZY000,ls_CZRQ00,ls_CZSJ00,PLBH00,0,0, ls_SFY000,'1',0,ls_YYID00,0,0,0,0, 0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,'00' from BM_PJLXB0 where SFYX00='Y' and PLBH00 in ('3','4','8') and PLBH00 not in (select PJBH00 from BM_JKB000 where JKDH00=ls_JKDH00); --把冲销的发票纳入某一个票据领用ID update ZY_PJSYQK a set PJLYID=(case when nvl(a.PJXH00,'-')='-' or a.PJXH00='-0' then a.PJLYID else (select PJLYID from BM_JKB000 where JKDH00=ls_JKDH00 and PJBH00=a.PLBH00 and rownum=1) end), WBLYID=(case when nvl(a.PJXH00,'-') <> '-' and a.PJXH00 <> '-0' then a.WBLYID else (select PJLYID from BM_JKB000 where JKDH00=ls_JKDH00 and PJBH00=a.PLBH00 and rownum=1) end) where JKDID0 = ls_JKDH00 and substr(A.PJXH00,1,1) = '-' and not exists (select 1 from BM_JKB000 where JKDH00=ls_JKDH00 and PJBH00=a.PLBH00 and PJLYID=a.PJLYID); select count(*) into ls_COUNT0 from XT_YYXX00 where yyid00='220007';--省二 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; if ls_SFY000=99999 then update BM_JKB000 set MZBZ00='8' where JKDH00=ls_JKDH00; end if; end if; --作废票据 for zf in CUR_ZY_ZFFP00 loop --计算作废的票据情况 update BM_JKB000 set PJZFH0 =nvl(PJZFH0,0)+(case when ls_ZYHTFP='1' and PJBH00=3 then zf.HTZFH0 else zf.PJZFH0 end), --作废票据数 PJZFJE =nvl(PJZFJE,0)+zf.HJJE00 where PJBH00 = zf.PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=decode(nvl(zf.WBLYID,0),0,zf.PJLYID,zf.WBLYID); end loop; --计算现金发票或者预交金票据对应的金额 --拆分的票据进行补打的话，可能会引起缴费金额的重复计算，因此暂时补打的都不算。 --金额 for zf in CUR_ZY_FPJE00 loop update BM_JKB000 set HJJE00=zf.HJJE00,ZFJE00=zf.ZFJE00,JZJE00=zf.JZJE00,GFJE00=zf.JMJE00, HSHJJE=zf.HJJE01,HSZFE0=zf.ZFJE01,HSJZJE=zf.JZJE01,HSGFJE=zf.JMJE01, --HSZFH0=HSZFH0+decode(sign(zf.HJJE01),-1,1,0) HSZFH0=HSZFH0+zf.HSZFH0 where PJBH00 = zf.PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=decode(nvl(zf.WBLYID,0),0,zf.PJLYID,zf.WBLYID); end loop; --支付金额 for zf in CUR_ZY_ZFJE00 loop update BM_JKB000 set XJJE00=nvl(XJJE00,0)+zf.XJ0000, ZZJE00=nvl(ZZJE00,0)+zf.ZZ0000, YHKJE0=nvl(YHKJE0,0)+zf.YHK000, DZQBJE=nvl(DZQBJE,0)+zf.DZQB00, YJJZF0=nvl(YJJZF0,0)+zf.YJJ000, HSZFJE=nvl(HSZFJE,0)+zf.XJ0001, HSZZJE=nvl(HSZZJE,0)+zf.ZZ0001, HSYHK0=nvl(HSYHK0,0)+zf.YHK001, HSDZQB=nvl(HSDZQB,0)+zf.DZQB01, HSYJJ0=nvl(HSYJJ0,0)+zf.YJJ001, GRZHZF=nvl(GRZHZF,0)+zf.GRZH00, TJJJZF=nvl(TJJJZF,0)+zf.TCJJ00, YBQTZF=nvl(YBQTZF,0)+zf.YBQTZF, SBGRZH=nvl(SBGRZH,0)+zf.SBZH00, SBTCJJ=nvl(SBTCJJ,0)+zf.SBTC00, GWYBZ0=nvl(GWYBZ0,0)+zf.GWYBZ0, WXZF00=nvl(WXZF00,0)+zf.WXZF00, ZFBZF0=nvl(ZFBZF0,0)+zf.ZFBZF0 where PJBH00 = zf.PLBH00 and JKDH00 = ls_JKDH00 and PJLYID=decode(nvl(zf.WBLYID,0),0,zf.PJLYID,zf.WBLYID); end loop; as_JKDH00:=ls_JKDH00; --填写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,'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,'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,'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); end if; --1(1).收预交金 select nvl(sum(B.XJJE00),0),nvl(sum(B.ZZJE00),0),nvl(sum(YHKJE0),0) into ls_XMJE00,ls_ZPSR00,ls_YHKSR0 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) into ls_XMJE00,ls_ZPSR00,ls_YHKSR0 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'; --==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; 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'; --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; --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)||' 份)',XMJE00=nvl(ls_YHKSR0,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+YBQTZF),0), nvl(abs(sum(JZJE00)),0), nvl(sum(GFJE00),0), nvl(sum(CYYSK0),0),nvl(sum(YBYL05),0) into ls_XMJE00,ls_NBPFJE,ls_YBJS00,ls_XMJE02,ls_JMBRYF,ls_CYYSK0,ls_DBZCE0 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)<>'-'); 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_DBZCE0,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','11','12','38','45'); update BM_JKKJMX set XMJE00 = nvl(ls_CZYFY0,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '03'; --冲预收款 --3(1)冲住院费用 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; */ --==3、预交金退出== 2018.09.12 select count(1),nvl(sum(HSJE00),0) into ls_COUNT0,ls_XMJE00 from VW_ZY_YJJHS0 C where 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)--+nvl(ls_XMJE35,0) where BZ0000='1' and XMBH00 = '15' and JKDH00 = ls_JKDH00; --计算应退还现金 --四舍五入金额 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 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 = '34'; --==收回/补打住院票据 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(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+YBQTZF),0)), abs(nvl(sum(JZJE00),0)),--退记账款 abs(nvl(sum(GFJE00),0)),--退减免款 abs(nvl(sum(CYYSK0),0)),--退出院病人应收款 sum(nvl(YBYL05,0)), --单病种差额 count(*) into ls_XMJE00,ls_YBJS00,ls_XMJE02,ls_JMBRYF,ls_CYYSK0,ls_DBZCE1,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 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where JKDID0=ls_JKDH00 and BDBZ00='0' and PLBH00=3 and substr(PJXH00,1,1)='-'); if ls_YBJECF='Y' then --==2、1 农保住院票据金额 select nvl(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+YBQTZF),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 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+YBQTZF),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 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_DBZCE1,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '37'; --6(1)退非医保款 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','37'); update BM_JKKJMX set XMJE00 = nvl(ls_XMJE00,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '21'; --右边的医保结算 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'; --记帐应收款 --单病种差额 update BM_JKKJMX set XMJE00 = nvl(ls_DBZCE0,0)-nvl(ls_DBZCE1,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '42'; --单病种差额 --计算应缴金额 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'; --应缴金额 --实交现金 update BM_JKKJMX set XMJE00 = nvl(ls_XMJE00,0)-nvl(ls_XMJE01,0)- nvl(ls_ZPSR00,0)-nvl(ls_YHKSR0,0)- nvl(ls_BZZZQD,0)-nvl(ls_YHKJS0,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_DBZCE0,0)+nvl(ls_DBZCE1,0) where JKDH00 = ls_JKDH00 and BZ0000='1' and XMBH00 = '35'; --实交现金 --取上期余额 begin select xmje00 into ls_xjje00 from bm_jkkjmx where xmbh00='81' and jkdh00=( select max(jkdh00) from bm_jkb000 where jkdh00'2' and ((ls_DZDJK0='0' and a.PLBH00<>20) or (ls_DZDJK0='1')); --宁德蕉城区医保个人帐户金额直接向病人收现金 select nvl(sum(jfje00),0)+ls_XMJE00 into ls_XMJE00 from ZY_BRJFB0 B,BM_BRXXB0 C,IC_YBBRLB D where b.BRID00 = C.BRID00 and C.YBLB00 = D.YBLB00 and C.FBBH00=D.FBBH00 and B.JKBID0 = ls_JKDH00 and ZFFS00='3' and D.SFJSDW='N' and JFBZ00<>'2'; select XMJE00 into ls_XMJE01 from BM_JKKJMX where JKDH00=ls_JKDH00 and BZ0000='1' and XMBH00 = '35' ; /*if ls_XMJE00<>ls_XMJE01 then as_YHMSG0:='缴款失败!'; as_SYSMSG:='实际应交现金'||to_char(ls_XMJE00)||'与缴款表的应交现金'||to_char(ls_XMJE01)||'不一致'; rollback; return; end if; */ end loop; 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; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%