create or replace procedure SP_ZY_ZYFPSR_TJ0000 ( as_KSRQ00 in char, --开始日期 as_KSSJ00 in char, --开始时间 as_JSRQ00 in char, --结束日期 as_JSSJ00 in char, --结束时间 as_HSXMMC in char, --核算项目名称 as_XXLB00 in char, --显示类别,'0':按发票项目显示,'1':按核算项目显示 as_TJLB00 in char, --统计类别,'0':所有病人,'1':现金病人,'2':持卡病人,'3':记账 as_FYFSLB in char, --费用发生类别：'0':全部 '1':自付 '2': 记帐 '3': 减免 as_BRFBMC in char, --病人费别名称 as_YYJC00 in char, --分院简称 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_SFDZX0 in char default 'N' --是否按大中心统计 ) as -- MODIFICATION HISTORY -- Person Date Comments -- sechen 2008.11.27 新增 -- zhangwz 2010.11.18 处理全部统计查询时右边出现不了 预缴金病人结算以及减免数据 . -- zhangyc 2011.06.01 增加是否按大中心统计as_SFDZX0 by ZYSF-20110527-004 -- zhangwz 2011.07.02 统计时增加BM_MZSRTJ.ORDER0数据的写入供前台查询时排序用. by ZYSF-20110628-001 --zhangyc 2013.06.25 医疗救助基金Mzbzje=BM_BRZFFS.ZFFSBH=22 商业保险支付sybxzf=BM_BRZFFS.ZFFSBH=13 BY ZYSF-20130625-002 --zhangyc 2015.09.22 修正bug by ZYSF-20150917-001 --zhangyc 2016.07.27 注释掉条件having sum(nvl(b.hjje00,0))>0 by ZYSF-20160712-002 --qiulf 2020.02.20 增加微信、支付宝、银行卡支付 by ZYSF-20190808-001 ls_BH0000 BM_FPXM00.BH0000%TYPE; ls_BM0000 BM_FPXM00.BH0000%TYPE; ls_YBMC00 IC_YBBRLB.YBMC00%TYPE; ls_TMPBH0 BM_FPXM00.XMMC00%TYPE;--BM_FPXM00.BH0000%TYPE; ls_XMMC00 BM_FPXM00.XMMC00%TYPE; ls_HJJE00 ZY_FYMX00.HJJE00%TYPE; ls_ZFJE00 ZY_FYMX00.ZFJE00%TYPE; ls_GFJE00 ZY_FYMX00.GFJE00%TYPE; ls_JZJE00 ZY_FYMX00.JZJE00%TYPE; ls_XMBH00 BM_MZSRTJ.XMBH01%TYPE; ls_JE0000 ZY_FYMX00.HJJE00%TYPE; ls_JE0001 number(12,2);--ZY_FYMX00.HJJE00%TYPE; ls_JE0002 number(12,2);--ZY_FYMX00.HJJE00%TYPE; ls_JE0003 number(12,2);--ZY_FYMX00.HJJE00%TYPE; V_YYID00 XT_YYXX00.YYID00%TYPE; ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; ls_yjshj0 number(12,2); ls_wjshj0 number(12,2); Vcounter number(5); ls_order0 BM_MZSRTJ.ORDER0%TYPE; --排序序号 LS_SFYX00 BM_BRZFFS.SFYX00%type; LS_YYTJE0 ZY_BRJFB0.JFJE00%type; --按核算项目显示 CURSOR CUR_ZY_ZYHSXM_TJ0000 IS select C.XLH000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from ZY_FYMX00 A,BM_YYSFXM B,BM_PARAMS C,ZY_BRFY00 H,BM_BRXXB0 I,IC_YBBRLB J where A.XMBH00 = B.SFXMID and B.HSXMID = C.ID0000(+) and (C.BH0000 like ls_TMPBH0||'%' or as_HSXMMC = '所有项目') and A.DJH000 = H.DJH000 and H.BRID00 = I.BRID00 and I.FBBH00 = J.FBBH00 and I.YBLB00 = J.YBLB00 and H.CZRQ00 >= as_KSRQ00 and H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and ((J.YBMC00 = as_BRFBMC and as_SFDZX0='N') or (as_BRFBMC = '所有病人') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB)) and ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)) and (as_YYJC00='0' or A.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by C.XLH000,C.XMMC00; begin --如果结账表的CZYKS0为空，要填入值 if (as_SFDZX0='Y') and (as_BRFBMC <> '所有病人') then select YBZXLB into ls_YBZXLB from IC_YBBRLB where YBMC00=as_BRFBMC and rownum=1; else ls_ybzxlb:='Z'; --随便一个值 end if; ls_TMPBH0:=''; select YYID00 into V_YYID00 from xt_yyxx00 where (yyjc00=as_YYJC00 or as_YYJC00='0') and rownum=1; select SQ_BM_MZSRTJ_ID0000.nextval into ad_ID0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01,XMBH02,XMMC02,XMJE02,XMJE03,XMJE04,ORDER0) values(ad_ID0000,'00',' 合计',0,'00',' 合计',0,0,0,-1); IF as_XXLB00 = '0' THEN --按票据项目 insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01,ORDER0) select ad_ID0000,BH0000,XMMC00,0,ORDER0 from BM_FPXM00 Group by BH0000,XMMC00,ORDER0; --按发票项目显示 if as_BRFBMC = '所有病人' then declare CURSOR CUR_ZY_ZYFPSR_TJ0000 IS select C.BH0000,C.XMMC00,c.ORDER0,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from ZY_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,ZY_BRFY00 H where A.XMBH00=B.SFXMID and B.ZYFPID=C.FPXMID and A.DJH000=H.DJH000 and H.DJH000 <>0 and H.CZRQ00 >= as_KSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and --((as_TJLB00 in('0','3'))or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0)) ((as_TJLB00='0')or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0)) and (as_YYJC00='0' or A.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by C.BH0000,C.XMMC00,c.ORDER0 order by c.ORDER0; begin OPEN CUR_ZY_ZYFPSR_TJ0000; LOOP FETCH CUR_ZY_ZYFPSR_TJ0000 INTO ls_BH0000,ls_XMMC00,ls_ORDER0,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_ZY_ZYFPSR_TJ0000%NOTFOUND; Update BM_MZSRTJ set XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; END LOOP; CLOSE CUR_ZY_ZYFPSR_TJ0000; end; else--指定费别 --按理这里医保病人的日收入指的是结算的收入，公费病人的收入指的是记账收入，自费病人的收入指的是非公费收入－医保结算收入 declare CURSOR CUR_ZY_ZYFPSR_TJ0000 IS select C.BH0000,C.XMMC00,c.ORDER0,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from ZY_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,ZY_BRFY00 H,BM_BRXXB0 I where A.XMBH00=B.SFXMID and B.ZYFPID=C.FPXMID and A.DJH000=H.DJH000 and H.BRID00=I.BRID00 and H.DJH000 <>0 and H.CZRQ00 >= as_KSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and ((as_TJLB00='0')or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0)) and (I.FBBH00,I.YBLB00) in (select fbbh00,yblb00 from IC_YBBRLB where (YBMC00=as_BRFBMC and as_SFDZX0='N')or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB)) and (as_YYJC00='0' or A.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by C.BH0000,C.XMMC00,c.ORDER0 order by c.ORDER0; begin OPEN CUR_ZY_ZYFPSR_TJ0000; LOOP FETCH CUR_ZY_ZYFPSR_TJ0000 INTO ls_BH0000,ls_XMMC00,ls_ORDER0,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_ZY_ZYFPSR_TJ0000%NOTFOUND; Update BM_MZSRTJ set XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; END LOOP; CLOSE CUR_ZY_ZYFPSR_TJ0000; end; end if; ELSE --按核算项目 if as_HSXMMC <> '所有项目' then select min(BH0000) into ls_TMPBH0 from BM_YYHSXM where HSXMMC||'('||BH0000||')' =as_HSXMMC; end if; insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01,ORDER0) select ad_ID0000,XLH000,XMMC00,0,TO_NUMBER(XLH000) from BM_PARAMS where ( BH0000 like ls_TMPBH0||'%' or as_HSXMMC = '所有项目') Group by XLH000,XMMC00,TO_NUMBER(XLH000); OPEN CUR_ZY_ZYHSXM_TJ0000; LOOP FETCH CUR_ZY_ZYHSXM_TJ0000 INTO ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_ZY_ZYHSXM_TJ0000%NOTFOUND; Update BM_MZSRTJ set XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; END LOOP; CLOSE CUR_ZY_ZYHSXM_TJ0000; END IF; if as_BRFBMC = '所有病人' then --期间所有发生的费用(包括期间现金结算) declare CURSOR cur_ZY_brfy00 IS select jflbid,sum(nvl(b.hjje00,0))hjje00 from ZY_BRFY00 A,ZY_FYMX00 B where A.DJH000=B.DJH000 AND A.CZRQ00 >= as_KSRQ00 and A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00 and A.CZRQ00 <= as_JSRQ00 and A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00 and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID =1)) and (as_YYJC00='0' or B.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by jflbid having sum(nvl(b.hjje00,0))>0; begin for fy in cur_ZY_brfy00 loop -- if fy.jflbid=1 then =1 为门诊预交金 in BM_BRJFLB by zhangwz if fy.jflbid=2 then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '93',XMJE02 = NVL(fy.HJJE00,0),XMMC02 = '预交金病人结算',XMJE03 = NVL(fy.HJJE00,0),XMJE04 = 0 where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; elsif fy.jflbid=0 then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '90',XMJE02 = NVL(fy.HJJE00,0),XMMC02 = '现金病人结算',XMJE03 = 0,XMJE04 = NVL(fy.HJJE00,0) where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; end if; end loop; end; else--指定费别 --期间所有发生的费用(包括期间现金结算) declare CURSOR cur_ZY_brfy00 IS select jflbid,sum(nvl(c.hjje00,0))hjje00 from ZY_BRFY00 A,BM_BRXXB0 B,ZY_FYMX00 C where A.DJH000=C.DJH000 AND A.BRID00 = B.BRID00 and A.CZRQ00>=as_KSRQ00 and A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00 and A.CZRQ00<=as_JSRQ00 and A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00 and (B.FBBH00,B.YBLB00) in (select fbbh00,yblb00 from IC_YBBRLB where (YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) ) and (as_YYJC00='0' or C.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by jflbid having sum(nvl(c.hjje00,0))>0; begin for fy in cur_ZY_brfy00 loop if fy.jflbid=2 and as_TJLB00 in ('0','2') then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '93',XMJE02 = NVL(fy.HJJE00,0),XMMC02 = '预交金病人结算',XMJE03 = NVL(fy.HJJE00,0),XMJE04 = 0 where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; elsif fy.jflbid=0 and as_TJLB00 in ('0','1') then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '90',XMJE02 = NVL(fy.HJJE00,0),XMMC02 = '现金病人结算',XMJE03 = 0,XMJE04 = NVL(fy.HJJE00,0) where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; end if; end loop; end; end if; --医保病人医保中心支付 if as_SFDZX0='Y' then declare CURSOR cur_zy_ybjfb0 IS select a.jflbid,b.fbbh00,c.ybzxlb,sum(nvl(a.jfje00,0)) jfje00 from zy_brjfb0 a,zy_jzb000 b,ic_ybbrlb c where a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and b.fbbh00=c.fbbh00 and b.yblb00=c.yblb00 and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00 and a.zffs00 in(3,4,13,22) and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where (YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人'))and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) AND (as_YYJC00='0' or B.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by a.jflbid,b.fbbh00,c.ybzxlb; --having sum(nvl(a.jfje00,0))>0; --by ZYSF-20160712-002 begin for yb in cur_zy_ybjfb0 loop select YBMC00 into ls_YBMC00 from ic_ybbrlb where fbbh00=yb.fbbh00 and ybzxlb=yb.ybzxlb and yblb00=yb.ybzxlb; Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+yb.JFJE00,XMJE03 =nvl(XMJE03,0)+Decode(yb.jflbid,0,0,yb.JFJE00),XMJE04 =nvl(XMJE04,0)+Decode(yb.jflbid,0,yb.JFJE00,0) where ID0000 = ad_ID0000 and XMMC02 = ls_YBMC00; if sql%notfound then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMJE02 = yb.JFJE00 ,XMMC02 = ls_YBMC00,XMJE03 =Decode(yb.jflbid,0,0,yb.JFJE00),XMJE04 =Decode(yb.jflbid,0,yb.JFJE00,0) where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; if SQL%NOTFOUND then insert into BM_MZSRTJ(ID0000,XMJE02,XMMC02,XMJE03,XMJE04) values(ad_ID0000,yb.JFJE00,ls_YBMC00,Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0)); end if; if yb.jflbid=0 then--现金结算病人 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-yb.jFJE00,XMJE04 =nvl(XMJE04,0)-yb.jFJE00 where ID0000 = ad_ID0000 and XMBH02 = '90'; else--预交金结算病人 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-yb.jFJE00,XMJE03 =nvl(XMJE03,0)-yb.jFJE00 where ID0000 = ad_ID0000 and XMBH02 = '93'; end if; end if; end loop; end; else declare CURSOR cur_zy_ybjfb0 IS select a.jflbid,b.yblb00,b.fbbh00,c.ybzxlb,sum(nvl(a.jfje00,0)) jfje00 from zy_brjfb0 a,zy_jzb000 b,ic_ybbrlb c where a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and b.fbbh00=c.fbbh00 and b.yblb00=c.yblb00 and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00 and a.zffs00 in(3,4,13,22) and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where (YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人'))and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) AND (as_YYJC00='0' or B.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by a.jflbid,b.yblb00,c.ybzxlb,b.fbbh00; --having sum(nvl(a.jfje00,0))>0; --by ZYSF-20160712-002 begin for yb in cur_zy_ybjfb0 loop select YBMC00 into ls_YBMC00 from ic_ybbrlb where fbbh00=yb.fbbh00 and ybzxlb=yb.ybzxlb and yblb00=yb.yblb00; Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+yb.JFJE00,XMJE03 =nvl(XMJE03,0)+Decode(yb.jflbid,0,0,yb.JFJE00),XMJE04 =nvl(XMJE04,0)+Decode(yb.jflbid,0,yb.JFJE00,0) where ID0000 = ad_ID0000 and XMMC02 = ls_YBMC00; if sql%notfound then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMJE02 = yb.JFJE00 ,XMMC02 = ls_YBMC00,XMJE03 =Decode(yb.jflbid,0,0,yb.JFJE00),XMJE04 =Decode(yb.jflbid,0,yb.JFJE00,0) where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; if SQL%NOTFOUND then insert into BM_MZSRTJ(ID0000,XMJE02,XMMC02,XMJE03,XMJE04) values(ad_ID0000,yb.JFJE00,ls_YBMC00,Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0)); end if; if yb.jflbid=0 then--现金结算病人 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-yb.jFJE00,XMJE04 =nvl(XMJE04,0)-yb.jFJE00 where ID0000 = ad_ID0000 and XMBH02 = '90'; else--预交金结算病人 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-yb.jFJE00,XMJE03 =nvl(XMJE03,0)-yb.jFJE00 where ID0000 = ad_ID0000 and XMBH02 = '93'; end if; end if; end loop; end; end if; --公费病人发生的记帐费用 declare CURSOR cur_zy_gfjfb0 IS select jflbid,sum(nvl(c.jzje00,0)) jfje00,b.yblb00,b.fbbh00 from zy_brfy00 a,bm_brxxb0 b,zy_FYMX00 C where A.DJH000=C.DJH000 AND a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb where ((YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人')) and b.fbbh00 not in ('1','3') ) and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) and (as_YYJC00='0' or C.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) group by a.jflbid,b.yblb00,b.fbbh00 having sum(nvl(c.jzje00,0))>0; begin for yb in cur_zy_gfjfb0 loop select YBMC00 into ls_YBMC00 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00; Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+yb.JFJE00,XMJE03 =nvl(XMJE03,0)+Decode(yb.jflbid,0,0,yb.JFJE00),XMJE04 =nvl(XMJE04,0)+Decode(yb.jflbid,0,yb.JFJE00,0) where ID0000 = ad_ID0000 and XMMC02 = ls_YBMC00||'(记帐)'; if sql%notfound then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMJE02 = yb.JFJE00 ,XMJE03 =Decode(yb.jflbid,0,0,yb.JFJE00),XMJE04 =Decode(yb.jflbid,0,yb.JFJE00,0), XMMC02 = ls_YBMC00||'(记帐)' where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; if SQL%NOTFOUND then insert into BM_MZSRTJ(ID0000,XMJE02,XMMC02,XMJE03,XMJE04) values(ad_ID0000,yb.JFJE00,ls_YBMC00||'(记帐)',Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0)); end if; if yb.jflbid=0 then--现金结算病人 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-yb.jFJE00,XMJE04 =nvl(XMJE04,0)-yb.jFJE00 where ID0000 = ad_ID0000 and XMBH02 = '90'; else--预交金结算病人 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-yb.jFJE00,XMJE03 =nvl(XMJE03,0)-yb.jFJE00 where ID0000 = ad_ID0000 and XMBH02 = '93'; end if; end if; end loop; end; --计算减免费用 select sum(nvl(c.gfje00,0)) into ls_GFJE00 from zy_brfy00 a,bm_brxxb0 b,zy_FYMX00 C where A.DJH000=C.DJH000 AND a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb where ((YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人'))) and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) and (as_YYJC00='0' or C.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) and a.gfje00<>'0' ; -- and a.hjje00=0; 去除a.hjje00=0 要不然查询不到数据 by zhangwz if ls_GFJE00<>0 then select nvl(min(XMBH01),9999) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='费用减免',XMJE03 =ls_GFJE00,XMJE04 = 0 where ID0000 = ad_ID0000 and XMMC02 is null and XMBH01 = ls_XMBH00; Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-ls_GFJE00,XMJE03 =nvl(XMJE03,0)-ls_GFJE00 where ID0000 = ad_ID0000 and XMBH02 = '93'; end if; --计算左边的合计数 update BM_MZSRTJ set XMJE01 = (select sum(XMJE01) from BM_MZSRTJ where ID0000 = ad_ID0000 and XMBH01 not in ('97','98')) where ID0000 = ad_ID0000 and XMBH01 = '00'; --计算右边的合计数 if V_YYID00='220034' then --各个单位转帐结算的金额不再统计（煤矿医院） update BM_MZSRTJ set (XMJE02,XMJE03,XMJE04) = (select sum(XMJE02),sum(XMJE03),sum(XMJE04) from BM_MZSRTJ where ID0000 = ad_ID0000 AND XMMC02 not LIKE '%其中%' ) where ID0000 = ad_ID0000 and XMBH02 = '00'; else update BM_MZSRTJ set (XMJE02,XMJE03,XMJE04) = (select sum(XMJE02),sum(XMJE03),sum(XMJE04) from BM_MZSRTJ where ID0000 = ad_ID0000) where ID0000 = ad_ID0000 and XMBH02 = '00'; end if; select xmje01,xmje02,xmje03+xmje04 into ls_je0001,ls_je0002,ls_JE0003 from bm_mzsrtj where id0000=ad_id0000 and xmbh02='00'; ls_je0000:=nvl(ls_je0001,0)-nvl(ls_je0002,0); if nvl(ls_je0000,0)<>0 then--更新左右两边不平的数据 update BM_MZSRTJ set XMJE02 =nvl(xmje02,0)+nvl(ls_je0000,0) where ID0000 = ad_ID0000 and XMBH02 = '00'; begin update BM_MZSRTJ set XMJE02 =nvl(xmje02,0)+nvl(ls_je0000,0) where ID0000 = ad_ID0000 and XMBH02 = '93'; exception WHEN OTHERS THEN select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '93',XMJE02 = NVL(ls_je0000,0), XMMC02 = '预交金病人结算' where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; end; end IF; ls_je0000:=nvl(ls_je0001,0)-nvl(ls_je0003,0); if nvl(ls_je0000,0)<>0 then--更新左边跟(预交金+现金)合计不平的数据 update BM_MZSRTJ set XMJE03 =nvl(xmje03,0)+nvl(ls_je0000,0) where ID0000 = ad_ID0000 and XMBH02 = '00'; begin update BM_MZSRTJ set XMJE03 =nvl(xmje03,0)+nvl(ls_je0000,0) where ID0000 = ad_ID0000 and XMBH02 = '93'; exception WHEN OTHERS THEN select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '93',XMJE03 = NVL(ls_je0000,0),XMJE04 = 0, XMMC02 = '预交金病人结算' where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; end; end IF; if as_XXLB00='1' then if trim(as_HSXMMC)='所有项目' then delete from BM_MZSRTJ where id0000=ad_ID0000 and trim(xmmc01) is null and trim(xmmc02) is null; else delete from BM_MZSRTJ where id0000=ad_ID0000 and trim(xmmc01) is null;-- and trim(xmmc02) is null; end if; end if; select max(XMBH01) into ls_BM0000 from BM_MZSRTJ where ID0000 = ad_ID0000; --统计微信支付金额--begin-- begin select SFYX00 into LS_SFYX00 from BM_BRZFFS where ZFFSBH=24;--微信支付 exception when others then LS_SFYX00:='N'; end; if LS_SFYX00='Y' then select sum(JFJE00) into LS_YYTJE0 from ZY_BRJFB0 A,BM_BRXXB0 b where 1=1 and A.BRID00=B.BRID00 and A.ZFFS00 in (24) and A.JFRQ00>=AS_KSRQ00 and A.JFRQ00||A.JFSJ00>=AS_KSRQ00||AS_KSSJ00 and A.JFRQ00<=AS_JSRQ00 and A.JFRQ00||A.JFSJ00<=AS_JSRQ00||AS_JSSJ00 and (B.YBLB00,B.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where ((YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人'))) and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) --and (as_YYJC00='0' or C.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) ; --if nvl(LS_YYTJE0,0)<>0 then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update bm_mzsrtj set XMJE02 = nvl(LS_YYTJE0,0),XMMC02 = '微信支付' where ID0000= ad_ID0000 and XMBH01 = ls_XMBH00; if sql%notfound then select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMHJ00) values(ad_ID0000,ls_BM0000,nvl(LS_YYTJE0,0),'微信支付',100); end if; --end if; end if; --统计微信支付金额--end-- --统计支付宝支付金额--begin-- begin select SFYX00 into LS_SFYX00 from BM_BRZFFS where ZFFSBH=25;--支付宝支付 exception when others then LS_SFYX00:='N'; end; if LS_SFYX00='Y' then select sum(JFJE00) into LS_YYTJE0 from ZY_BRJFB0 A,BM_BRXXB0 b where 1=1 and A.BRID00=B.BRID00 and A.ZFFS00 in (25) and A.JFRQ00>=AS_KSRQ00 and A.JFRQ00||A.JFSJ00>=AS_KSRQ00||AS_KSSJ00 and A.JFRQ00<=AS_JSRQ00 and A.JFRQ00||A.JFSJ00<=AS_JSRQ00||AS_JSSJ00 and (B.YBLB00,B.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where ((YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人'))) and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) --and (as_YYJC00='0' or C.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) ; --if nvl(LS_YYTJE0,0)<>0 then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update bm_mzsrtj set XMJE02 = nvl(LS_YYTJE0,0),XMMC02 = '支付宝支付' where ID0000= ad_ID0000 and XMBH01 = ls_XMBH00; if sql%notfound then select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMHJ00) values(ad_ID0000,ls_BM0000,nvl(LS_YYTJE0,0),'支付宝支付',100); end if; --end if; end if; --统计支付宝支付金额--end-- --统计银行卡支付金额--begin-- begin select SFYX00 into LS_SFYX00 from BM_BRZFFS where ZFFSBH=8;--银行卡支付 exception when others then LS_SFYX00:='N'; end; if LS_SFYX00='Y' then select sum(JFJE00) into LS_YYTJE0 from ZY_BRJFB0 A,BM_BRXXB0 b where 1=1 and A.BRID00=B.BRID00 and A.ZFFS00 in (8) and A.JFRQ00>=AS_KSRQ00 and A.JFRQ00||A.JFSJ00>=AS_KSRQ00||AS_KSSJ00 and A.JFRQ00<=AS_JSRQ00 and A.JFRQ00||A.JFSJ00<=AS_JSRQ00||AS_JSSJ00 and (B.YBLB00,B.FBBH00) in (select YBLB00,FBBH00 from IC_YBBRLB where ((YBMC00=as_BRFBMC and as_SFDZX0='N') or (as_SFDZX0='Y' and YBZXLB=ls_YBZXLB) or (as_BRFBMC='所有病人'))) and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) --and (as_YYJC00='0' or C.KDKS00 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) ; --if nvl(LS_YYTJE0,0)<>0 then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update bm_mzsrtj set XMJE02 = nvl(LS_YYTJE0,0),XMMC02 = '银行卡支付' where ID0000= ad_ID0000 and XMBH01 = ls_XMBH00; if sql%notfound then select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMHJ00) values(ad_ID0000,ls_BM0000,nvl(LS_YYTJE0,0),'银行卡支付',100); end if; --end if; end if; --统计银行卡支付金额--end-- --commit; exception when others then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_ZYFPSR_TJ0000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end;