CREATE OR REPLACE PROCEDURE SP_SF_MZFPSR_TJ0000 ( as_KSRQ00 in varchar, --开始日期 as_KSSJ00 in varchar, --开始时间 as_JSRQ00 in varchar, --结束日期 as_JSSJ00 in varchar, --结束时间 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, --分院简称 as_FBBH00 in char, --增加病人费别编号 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_SFDZX0 in char default 'N' --是否按大中心统计 ) as ls_BH0000 BM_FPXM00.BH0000%TYPE; ls_BM0000 BM_FPXM00.BH0000%TYPE; ls_YBMC00 IC_YBBRLB.YBMC00%TYPE; LS_PXH000 IC_YBBRLB.PXH000%TYPE; ls_TMPBH0 BM_FPXM00.XMMC00%TYPE;--BM_FPXM00.BH0000%TYPE; ls_XMMC00 BM_FPXM00.XMMC00%TYPE; ls_HJJE00 SF_FYMX00.HJJE00%TYPE; ls_ZFJE00 SF_FYMX00.ZFJE00%TYPE; ls_GFJE00 SF_FYMX00.GFJE00%TYPE; ls_JZJE00 SF_FYMX00.JZJE00%TYPE; ls_XMBH00 BM_MZSRTJ.XMBH01%TYPE; ls_JE0000 SF_FYMX00.HJJE00%TYPE; ls_JE0001 SF_FYMX00.HJJE00%TYPE; ls_JE0002 SF_FYMX00.HJJE00%TYPE; ls_JE0003 SF_FYMX00.HJJE00%TYPE; V_YYID00 XT_YYXX00.YYID00%TYPE; ls_yjshj0 number(12,2); ls_wjshj0 number(12,2); Vcounter number(5); ls_bqjc number(12,2); --本期结存 ls_bqjs number(12,2); --本期减少 ls_bqzj number(12,2); --本期增加 ls_sqjc number(12,2); --上期结存 ls_sqjc1 number(12,2); ls_sqjc2 number(12,2); ls_xmje number(12,2); --收费员应缴金额 ls_yhkjs0 number(12,2); ls_xjjs00 number(12,2); ls_value char; ls_YDYBJE SF_FYMX00.HJJE00%TYPE; ls_YDYJJE SF_FYMX00.HJJE00%TYPE; ls_YDXJJE SF_FYMX00.HJJE00%TYPE; ls_YBJE00 SF_FYMX00.HJJE00%TYPE; ls_SFDM IC_YHXX00.SFDM00%TYPE; --收费代码 ls_BQZJ00 SF_YJJYE0.BRZJ00%TYPE; --本期增加 ls_BQJS00 SF_YJJYE0.BRJS00%TYPE; --本期减少 ls_bqce number(12,2); --本期差额 ls_ickyj number(12,2); ls_TJ_ZEJMXMID BM_YYSFXM.SFXMID%TYPE; ls_KFJE00 sf_fymx00.hjje00%type; ls_DWDZJE sf_fymx00.hjje00%type; ls_QBDZJE sf_fymx00.hjje00%type; ls_DWTJ01 SF_FYMX00.hjje00%TYPE; --单位体检金额 ls_DWTJ02 SF_FYMX00.hjje00%TYPE; --单位体检金额 ls_DWTJ00 SF_FYMX00.hjje00%TYPE; --单位体检金额 ls_JMJE00 SF_FYMX00.JMJE00%TYPE; ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --医保中心类别 ls_order0 BM_MZSRTJ.ORDER0%TYPE; ls_YDYBHJ sf_brjfb0.jfje00%TYPE; --异地医保小计 ls_SYBHJ0 SF_FYMX00.hjje00%TYPE; --市医保小计 ls_SBZFJE number(12,2); --商保支付金额 ls_value0 number(6); ls_DWDZJE_NEW sf_fymx00.hjje00%type; ls_XMBH02 BM_MZSRTJ.XMBH02%type; ls_XMMC02 BM_MZSRTJ.XMMC02%type; ls_XMJE02 BM_MZSRTJ.XMJE02%type; ls_xh0000 number(6); LS_TJHJJE number(12,4); LS_YYTJE0 number(12,2); LS_YJJJS0 number(12,2); LS_SFYX00 varchar2(10); --按核算项目显示 CURSOR CUR_SF_MZHSXM_TJ0000 IS select C.XLH000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_YYSFXM B,BM_PARAMS C,SF_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 H.JZDH00<>0 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 (I.FBBH00=as_fbbh00 OR as_fbbh00='-1') --and ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0) or (as_TJLB00='3')) 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 order by C.XLH000; CURSOR CUR_BM_MZSRTJ IS select XMBH02,XMMC02,XMJE02,rownum from ( select XMBH02,XMMC02,XMJE02 from BM_MZSRTJ where ID0000=ad_ID0000 and XMMC02 is not null order by to_number(nvl(XMBH02,'900')) ); -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.06.21 不体现出费用为零的项目; -- zhr 2007.12.25 煤矿医院预交金余额表的转账余额，要从SF_BRFY00.ZZZHZF中进行计算 -- xzw 2008.01.28 医保病人医保中心支付统计中，分院统计根据SF_JZB000.CZYKS0来进行区分统计的 -- zhr 2008.02.19 过程做了修整，统计方式没有变化 -- xzw 2008.02.29 煤矿医院转帐结算的金额能按单位细分 显示各个单位当月的汇总金额 -- qks 2008.05.21 省二要求统计时右边增加两个字段：预交金XMBH03、现金XMBH04 -- xzw 2009.01.08 省二要求显示异地医保汇总金额 -- zhaoqc 2009.02.26 地质医院要求‘门诊收入报表’->‘门诊收入日报表’中增加显示‘预交金余额本期余额’与‘收费员本期应缴总金额’。 -- xuzw 2009.03.11 老年医院要求显示未结算合计金额和已结算的合计金额，之前只有平摊医院显示。。。 -- xuzw 2009.04.13 添加医保小计(省二) -- xuzw 2009.05.06 增加异地医保统计后相应的医保分中心统计中统计现金和预交金语句的位置应往下调整 -- xuzw 2009.05.08 增加IC卡押金、预交金本期差额(既预交金余额核对报表中的本期差额)字段. -- xuzw 2009.09.09 增加长乐医院特殊项目左边：把ICK费中其它费中分离出来，增加单位体检打折和个人体检打折 右边增加单位现金结算和单位体检结算 -- xuzw 2009.09.09 省二要求把医保小计中不含异地医保改成含异地医保 -- xuzw 2009.09.23 把having sum(nvl(b.hjje00,0))>0改成having sum(nvl(b.hjje00,0))<>0; --zhangyc 2010.06.30 省二医院要求显示 银行卡结算 --zhangyc 2010.07.13 省二医院 现金结算金额 应扣除 银行卡结算金额 不能更新左边跟(预交金+现金)合计不平的数据，否则数据会错误 --zhangyc 2010.10.15 地质医院要求增加优惠金额字段 MZSF-20100720-007 --zhangwz 2011.01.18 增加入参 as_SFDZX0 来实现按这个大中心进行查询. by MZSF-20110111-001. --csf 2011.01.24 按大中心统计时候，不要出现分中心的数据。 --csf 2011.01.7 指定某一中心的话，应该要先显示该中心的数据，现纠正 --zhangyc 2011.03.16 省二加分院医院ID，异地医保小计(IC_YBBRLB.SFXNH0='3') --CSF 2011.04.01 GROUP BY 后面 再加 order by 8i数据库不支持，纠正写法。 --zhangyc 2011.05.17 公费病人费用统计过滤掉fbbh00='99'数据 by MZSF-20110503-003 --zhangyc 2011.06.14 省二医院增加市医保小计ic_ybbrlb.ybzxlb=2 by MZSF-20110520-003 --zhanghr 2011.07.04 脚本优化，修正bug --zhangyc 2011.07.22 厦门同安中医院(yyid00=223280)收入报表现金病人结算要扣除交款日报表中的其它金额(商业保险支付) by MZSF-20110613-002 --zhangyc 2011.07.28 modify by MZSF-20110707-004 莆田华侨医院(223488)显示转账结算金额bm_brzffs.ZFFSBH=2 --linzy 2011.08.04 长乐医院公费病人费用统计过滤掉fbbh00='99' 增加参数SF_MZSRBBSFGLFB --zhangyc 2011.08.19 modify by MZSF-20110818-001 选择大中心报错，2011.07.04修改有误 注释掉decode(c.yblb00,c.ybzxlb,' ',c.yblb00) --zhanghr 2011.10.06 增加省二爱健康卡 --JXQ 2011.11.28 修正省二医院单列爱健康卡收入统计2011-12-28 15:56:09条件 --zhanghr 2011.12.01 修正上一条错误做法 --jinfl 2011.12.28 增加查询病人费别编号 MZSF-20111223-002 --jinfl 2012.05.09 修改长乐医院单位体检折扣额的算法 MZSF-20120425-003 --zhangyc 2012.07.03 修改公费记账金额算法，公费转自费SF_BRFY00有记录SF_FYMX00中没记录导致关联明细表记账金额不能合计 by MZSF-20120702-001 --zhangyc 2012.07.18 修改按医保中心排列ic_ybbrlb.pxh000 by MZSF-20120509-002 --jinfl 2012.08.03 长乐医院统计其他费中加上体检打折费 MZSF-20120727-002 --jinfl 2012.09.04 长乐医院按核算项目统计时会把救护车费统计到个人体检中去 MZSF-20120904-001 --zhangyc 2013.06.24 显示医保中心增加SYBXZF(商业保险支付ZFFS00=13)去掉代码(V_YYID00='223280'控制) BY MZSF-20130621-003 --zhangyc 2013.06.25 医疗救助基金Mzbzje=BM_BRZFFS.ZFFSBH=22 BY MZSF-20130625-002 --zhangyc 2013.10.21 长乐医院YYID00='222137'统计 预交金病人结算(体检) by MZSF-20130927-001 --jinfl 2013.11.08 省二公费病人收入统计日期改为取JZRQ00 by MZSF-20131104-001 --zhangyc 2014.08.04 统计公费跟自费互转时按SF_BRXXB0中FBBH00,YBLB00 统计 by MZSF-20140513-001 --zhangyc 2015.05.27 注释掉参数SF_MZSRBBSFGLFB插入XT_XTCS00表语句 by MZSF-20150409-001 --zhangyc 2015.10.19 增加显示'血透费用减免' by MZSF-20150930-001 --dsm 2016.03.03 把'血透费用减免' 移到最后 for MZSF-20160224-001 --zhangyc 2016.07.15 增加显示“银医通” by MZSF-20160715-001 --zhangyc 2016.11.23 增加显示"健康通支付" by MZSF-20161110-001 --zhangyc 2016.12.12 预交金结算-健康通支付(YYID00='220007'省二) by MZSF-20161213-001 --huangjy 2017.06.02 福建中医药大学附属康复医院参考省二预交金结算-健康通支付 by MZSF-20170406-004 --zhangyc 2017.10.10 增加显示微信支付和支付宝支付 by MZSF-20170928-002 --zhangyc 2017.10.25 预交金收入扣除掉微信和支付宝支付 by MZSF-20171025-002 --dsm 2019.06.27 如果“门诊预交金病人结算”该科目的金额为负数时,不要改0 for MZSF-20190417-003 ---------- ------------- -------------------------------------------------------------------------------------------------------------------- begin --是否过滤费别 begin select to_number(nvl(trim(VALUE0),'0')) into ls_value0 from XT_XTCS00 where NAME00='SF_MZSRBBSFGLFB'; exception when others then ls_value0:=0; /* insert into xt_xtcs00(NAME00,VALUE0,FLAG00,BZ0000,FL0000) values('SF_MZSRBBSFGLFB','99','1','门诊收入日报表是否过费别。N:不过滤,否则为费别编号。默认为N','报表-门诊收入日报表'); commit; */ end; --如果结账表的CZYKS0为空，要填入值 ls_TMPBH0:=''; --异地医保取值变量 ls_YDYBJE:=0; ls_YDYJJE:=0; ls_YDXJJE:=0; ls_YBJE00:=0; ls_yhkjs0:=0; LS_TJHJJE:=0; if (as_SFDZX0='Y') and (as_BRFBMC <> '所有病人') then select YBZXLB into ls_YBZXLB from IC_YBBRLB where YBMC00=as_BRFBMC; else ls_ybzxlb:='Z'; --随便一个值 end if; select YYID00 into V_YYID00 from xt_yyxx00 where (yyjc00=as_YYJC00 or as_YYJC00='0') and rownum=1; --V_YYID00:='220007'; --省二 select SQ_BM_MZSRTJ_ID0000.nextval into ad_ID0000 from dual; -- insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMBH02,XMMC02) values(ad_ID0000,'-1',as_BRFBMC,'-1',as_BRFBMC); 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 ORDER0,BH0000,XMMC00 ; --按发票项目显示 if as_BRFBMC = '所有病人' then declare CURSOR CUR_SF_MZFPSR_TJ0000 IS select C.BH0000,C.XMMC00,c.order0,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00=B.SFXMID and B.MZFPID=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)) and (I.FBBH00=as_fbbh00 OR as_fbbh00='-1') and I.BRID00=H.BRID00 group by C.BH0000,C.XMMC00,c.order0 order by c.order0; begin OPEN CUR_SF_MZFPSR_TJ0000; LOOP FETCH CUR_SF_MZFPSR_TJ0000 INTO ls_BH0000,ls_XMMC00,ls_order0,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_SF_MZFPSR_TJ0000%NOTFOUND; --Update BM_MZSRTJ set XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; 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; --Update BM_MZSRTJ set XMJE01 =NVL(ls_HJJE00,0) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; END LOOP; CLOSE CUR_SF_MZFPSR_TJ0000; end; else--指定费别 --按理这里医保病人的日收入指的是结算的收入，公费病人的收入指的是记账收入，自费病人的收入指的是非公费收入－医保结算收入 --zhr 08.02.19目前暂未处理,SF_BRFY00表应该增加 yblb00，ybbrlb字段。 declare CURSOR CUR_SF_MZFPSR_TJ0000 IS select C.BH0000,C.XMMC00,c.order0,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00) from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00=B.SFXMID and B.MZFPID=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)) and (I.FBBH00=as_fbbh00 OR as_fbbh00='-1') group by C.BH0000,C.XMMC00,c.order0 order by c.order0; begin OPEN CUR_SF_MZFPSR_TJ0000; LOOP FETCH CUR_SF_MZFPSR_TJ0000 INTO ls_BH0000,ls_XMMC00,ls_order0,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_SF_MZFPSR_TJ0000%NOTFOUND; --Update BM_MZSRTJ set XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; 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; --Update BM_MZSRTJ set XMJE01 =NVL(ls_HJJE00,0) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; END LOOP; CLOSE CUR_SF_MZFPSR_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_SF_MZHSXM_TJ0000; LOOP FETCH CUR_SF_MZHSXM_TJ0000 INTO ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00; EXIT WHEN CUR_SF_MZHSXM_TJ0000%NOTFOUND; --Update BM_MZSRTJ set XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; 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; --Update BM_MZSRTJ set XMJE01 = NVL(ls_HJJE00,0) where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000; END LOOP; CLOSE CUR_SF_MZHSXM_TJ0000; END IF; select max(XMBH01) into ls_BM0000 from BM_MZSRTJ where ID0000 = ad_ID0000; if as_BRFBMC = '所有病人' then --期间所有发生的费用(包括期间现金结算) declare CURSOR cur_sf_brfy00 IS select jflbid,sum(nvl(b.hjje00,0))hjje00 from SF_BRFY00 A,SF_FYMX00 B,BM_BRXXB0 I 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)) and (I.FBBH00=as_fbbh00 OR as_fbbh00='-1') and I.BRID00=A.BRID00 group by jflbid having sum(nvl(b.hjje00,0))<>0; begin for fy in cur_sf_brfy00 loop if fy.jflbid=1 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; if V_YYID00='222137' then --长乐医院 --体检病人预交金 LS_TJHJJE begin select sum(nvl(b.hjje00,0))hjje00 into LS_TJHJJE from SF_BRFY00 A,SF_FYMX00 B,BM_BRXXB0 I,SF_BRXXB0 AA where A.DJH000=B.DJH000 and I.BRID00=A.BRID00 and I.BRID00=AA.BRID00 and A.MZID00=AA.GHID00 and A.jflbid=1 and AA.GHLB00 in (select LBBH00 from BM_GHLBB0 where LBMC00='体检号') 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)) and (I.FBBH00=as_fbbh00 OR as_fbbh00='-1') having sum(nvl(b.hjje00,0))<>0; exception when others then LS_TJHJJE:=0; end; --插入体检预交金病人结算 select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '93.1',XMJE02 =nvl(XMJE02,0)+ NVL(LS_TJHJJE,0),XMMC02 = '预交金病人结算(体检)', XMJE03 = NVL(LS_TJHJJE,0),XMJE04 = 0 where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; --修改 预交金病人结算 金额 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)- NVL(LS_TJHJJE,0),XMJE03 =nvl(XMJE02,0)-NVL(LS_TJHJJE,0) where ID0000=ad_ID0000 and XMBH02 = '93'; end if; else--指定费别 --期间所有发生的费用(包括期间现金结算) declare CURSOR cur_sf_brfy00 IS select jflbid,sum(nvl(c.hjje00,0))hjje00 from SF_BRFY00 A,BM_BRXXB0 B,SF_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)) and (B.FBBH00=as_fbbh00 OR as_fbbh00='-1') group by jflbid having sum(nvl(c.hjje00,0))<>0; begin for fy in cur_sf_brfy00 loop if fy.jflbid=1 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; if V_YYID00='222137' then --长乐医院 --体检病人预交金 LS_TJHJJE begin select sum(nvl(c.hjje00,0)) into LS_TJHJJE from SF_BRFY00 A,BM_BRXXB0 B,SF_FYMX00 C,SF_BRXXB0 AA where A.DJH000=C.DJH000 AND A.BRID00 = B.BRID00 AND B.BRID00 = AA.BRID00 and A.MZID00=AA.GHID00 and A.jflbid=1 and AA.GHLB00 in (select LBBH00 from BM_GHLBB0 where LBMC00='体检号') 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)) and (B.FBBH00=as_fbbh00 OR as_fbbh00='-1') having sum(nvl(c.hjje00,0))<>0; exception when others then LS_TJHJJE:=0; end; --插入体检预交金病人结算 select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02 = '93.1',XMJE02 =nvl(XMJE02,0)+ NVL(LS_TJHJJE,0),XMMC02 = '预交金病人结算(体检)', XMJE03 = NVL(LS_TJHJJE,0),XMJE04 = 0 where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; --修改 预交金病人结算 金额 Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)- NVL(LS_TJHJJE,0),XMJE03 =nvl(XMJE02,0)-NVL(LS_TJHJJE,0) where ID0000=ad_ID0000 and XMBH02 = '93'; end if; end if; --2007.12.25 煤矿医院要求转账账户支付金额独立一项 if V_YYID00='220034' then declare CURSOR cur_sf_tydw00 IS --select c.DWID00,c.DWMC00,sum(zzzhzf) into ls_dwid00,ls_dwmc00,ls_je0000 from sf_brfy00 C,bm_brxxb0 d,bm_tydwb0 c select e.DWID00,e.DWMC00,sum(zzzhzf) zzzhzf from sf_brfy00 C,bm_brxxb0 d,bm_tydwb0 e where c.brid00=d.brid00 and d.tydwid=e.dwid00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00>=as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00<=as_JSRQ00||as_JSSJ00 and ((as_TJLB00='0')or(as_TJLB00='1' and c.JFLBID=0)or(as_TJLB00='2' and c.JFLBID =1)) group by e.DWID00,e.DWMC00 having sum(zzzhzf)<>0; begin for zz in cur_sf_tydw00 loop Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+zz.zzzhzf where ID0000 = ad_ID0000 and XMMC02='转帐结算' ; if sql%notfound then Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+zz.zzzhzf,XMMC02='转帐结算' where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1; 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) values(ad_ID0000,ls_BM0000,zz.zzzhzf,'转帐结算'); end if; end if; Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+zz.zzzhzf,XMMC02=' 其中'||zz.dwmc00||'转帐结算' where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1; 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) values(ad_ID0000,ls_BM0000,zz.zzzhzf,' 其中'||zz.dwmc00||'转帐结算'); end if; end loop; end; end if; --省二要求增加银行卡结算 MZSF-20100628-003 select count(*) into Vcounter from XT_YYXX00 where YYID00='220007'; if Vcounter>0 then --------------增加银行卡结算------begin--------- begin select sum(nvl(a.jfje00,0)) into ls_yhkjs0 from sf_brjfb0 a,sf_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.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 ((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)); exception when others then ls_yhkjs0:=0; end; select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02='94',XMJE02 = nvl(ls_yhkjs0,0),XMMC02 = '银行卡结算' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; --------------增加银行卡结算------begin--------- ---------省二医院单列爱健康卡收入 select sum(nvl(c.hjje00,0)) into ls_HJJE00 from sf_brfy00 a,bm_brxxb0 b,SF_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.brid00 in (select brid00 from bm_jtkbr0 where klb000='0') 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<>ls_value0 or ls_value0=0) ) 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 (b.FBBH00=as_fbbh00 OR as_fbbh00='-1'); if ls_HJJE00<>0 then select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02='95',XMJE02 = ls_HJJE00 ,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,XMBH02,XMJE02,XMMC02) values(ad_ID0000,ls_BM0000,'95',ls_HJJE00,'爱健康卡结算'); end if; Update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-ls_HJJE00,XMJE03 =nvl(XMJE03,0)-ls_HJJE00 where ID0000 = ad_ID0000 and XMBH02 = '93'; end if; end if; --莆田华侨医院要求显示转账结算金额： MZSF-20110707-004---- if (V_YYID00='223488') then --------------转账结算------begin--------- begin select sum(nvl(a.jfje00,0)) into ls_yhkjs0 from sf_brjfb0 a,sf_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.zffs00 in(2) 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 ((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)); EXCEPTION WHEN OTHERS THEN ls_yhkjs0:=0; END; select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02='95',XMJE02 = nvl(ls_yhkjs0,0),XMMC02 = '转账结算金额' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; --------------转账结算------end--------- end if; ---异地医保小计----begin------ select sum(nvl(a.jfje00,0)) into ls_YDYBHJ from sf_brjfb0 a,sf_jzb000 b,ic_ybbrlb c --,sf_brxxb0 c where a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and b.fbbh00=c.fbbh00 and b.yblb00=c.yblb00 and a.zffs00 in(3,4,13,22) and c.sfxnh0='3' 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 B.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1'); ---异地医保小计----end------ --医保病人医保中心支付 if as_SFDZX0='Y' then declare CURSOR cur_sf_ybjfb0 IS select a.jflbid,b.fbbh00,c.ybzxlb,sum(nvl(a.jfje00,0)) jfje00 from sf_brjfb0 a,sf_jzb000 b,ic_ybbrlb c --,sf_brxxb0 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 exists (select 1 from bm_ygbm00 c,bm_bmbm00 d where b.jzy000=c.ygbh00 and c.bmbh00=d.bmbh00 and d.yyid00=V_YYID00_1) --and B.MZID00=C.GHID00 AND C.JZKS00 in (select d.bmbh00 from bm_bmbm00 d where d.yyid00=V_YYID00_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)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') group by a.jflbid,b.fbbh00,c.ybzxlb having sum(nvl(a.jfje00,0))<>0 --order by c.ybzxlb,decode(c.yblb00,c.ybzxlb,' ',c.yblb00);--zhanghr 2011.07.04 脚本优化，修正bug order by c.ybzxlb;--by MZSF-20110818-001 begin for yb in cur_sf_ybjfb0 loop select YBMC00,PXH000 into ls_YBMC00,LS_PXH000 from ic_ybbrlb where fbbh00=yb.fbbh00 and ybzxlb=yb.ybzxlb and yblb00=ybzxlb; LS_XMBH02:=to_char(to_number(nvl(LS_PXH000,100))+100); Update BM_MZSRTJ set XMBH02=LS_XMBH02,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 XMBH02=LS_XMBH02,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 select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMJE03,XMJE04,XMBH02) values(ad_ID0000,ls_BM0000,yb.JFJE00,ls_YBMC00,Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0),LS_XMBH02); end if; 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 loop; end; else declare CURSOR cur_sf_ybjfb0 IS select a.jflbid,b.fbbh00,c.yblb00,c.ybzxlb,sum(nvl(a.jfje00,0)) jfje00 from sf_brjfb0 a,sf_jzb000 b,ic_ybbrlb c --,sf_brxxb0 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 exists (select 1 from bm_ygbm00 c,bm_bmbm00 d where b.jzy000=c.ygbh00 and c.bmbh00=d.bmbh00 and d.yyid00=V_YYID00_1) --and B.MZID00=C.GHID00 AND C.JZKS00 in (select d.bmbh00 from bm_bmbm00 d where d.yyid00=V_YYID00_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)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') group by a.jflbid,b.fbbh00,c.yblb00,c.ybzxlb having sum(nvl(a.jfje00,0))<>0 --order by c.ybzxlb,decode(c.yblb00,c.ybzxlb,' ',c.yblb00);--zhanghr 2011.07.04 脚本优化，修正bug order by c.ybzxlb;--by MZSF-20110818-001 begin for yb in cur_sf_ybjfb0 loop select YBMC00,PXH000 into ls_YBMC00,LS_PXH000 from ic_ybbrlb where fbbh00=yb.fbbh00 and ybzxlb=yb.ybzxlb and yblb00=yb.yblb00; LS_XMBH02:=to_char(to_number(nvl(LS_PXH000,100))+100); Update BM_MZSRTJ set XMBH02=LS_XMBH02,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 XMBH02=LS_XMBH02,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 select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMJE03,XMJE04,XMBH02) values(ad_ID0000,ls_BM0000,yb.JFJE00,ls_YBMC00,Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0),LS_XMBH02); end if; 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; --统计异地医保的值 if (yb.fbbh00=3) and (yb.YBZXLB)='1' and (yb.yblb00)<>'1' then ls_YDYBJE := ls_YDYBJE+yb.JFJE00; if yb.jflbid=0 then ls_YDXJJE :=ls_YDXJJE+yb.JFJE00; elsif yb.jflbid=1 then ls_YDYJJE :=ls_YDYJJE+yb.JFJE00; end if; end if; ls_YBJE00 :=ls_YBJE00+yb.JFJE00; end loop; end; end if; --公费病人发生的记帐费用 /* declare cursor cur_sf_gfjfb0 is select jflbid,sum(nvl(c.jzje00,0)) jfje00,b.yblb00,b.fbbh00 from sf_brfy00 a,bm_brxxb0 b,SF_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 (b.fbbh00<>ls_value0 or ls_value0=0) ) 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 (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') group by a.jflbid,b.yblb00,b.fbbh00 having sum(nvl(c.jzje00,0))<>0; */ --公费转自费SF_BRFY00有记录SF_FYMX00中没记录导致关联明细表记账金额不能合计 by MZSF-20120702-001--- if V_YYID00='220007' then declare cursor cur_sf_gfjfb0 is select a.jflbid,sum(nvl(a.jzje00,0)) jfje00,b.yblb00,b.fbbh00 from sf_brfy00 a,bm_brxxb0 b,sf_jzb000 c where a.brid00=b.brid00 and a.jzdh00=c.jzdh00 and c.jzrq00>=as_ksrq00 and c.jzrq00||c.jzsj00>=as_KSRQ00||as_KSSJ00 and c.jzrq00<=as_jsrq00 and c.jzrq00||c.jzsj00<=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 (b.fbbh00<>ls_value0 or ls_value0=0) ) 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 a.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') group by a.jflbid,b.yblb00,b.fbbh00 having sum(nvl(a.jzje00,0))<>0; begin for yb in cur_sf_gfjfb0 loop select YBMC00,PXH000 into ls_YBMC00,LS_PXH000 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00; LS_XMBH02:=to_char(to_number(nvl(LS_PXH000,100))+100); Update BM_MZSRTJ set XMBH02=LS_XMBH02,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 XMBH02=LS_XMBH02,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 select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMJE03,XMJE04,XMBH02) values(ad_ID0000,ls_BM0000,yb.JFJE00,ls_YBMC00||'(记帐)',Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0),LS_XMBH02); 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_sf_gfjfb0 is --改为从SF_BRXXB0 中取FBBH00 和YBLB00 BY MZSF-20140513-001 select jflbid,sum(nvl(a.jzje00,0)) jfje00,decode(Trim(c.yblb00),'',b.yblb00,C.yblb00)yblb00, decode(Trim(c.yblb00),'',b.FBBH00,c.FBBH00)FBBH00 --b.yblb00,b.fbbh00 from sf_brfy00 a,bm_brxxb0 b,SF_BRXXB0 C where a.brid00=b.brid00 and a.brid00=c.brid00 and A.MZID00 = c.GHID00(+) 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 (decode(Trim(c.yblb00),'',b.yblb00,C.yblb00),decode(Trim(c.yblb00),'',b.FBBH00,c.FBBH00)) --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 decode(Trim(c.yblb00),'',b.FBBH00,c.FBBH00) not in ('1','3') and (decode(Trim(c.yblb00),'',b.FBBH00,c.FBBH00)<>ls_value0 or ls_value0=0)) --and b.fbbh00 not in ('1','3') --and (b.fbbh00<>ls_value0 or ls_value0=0)) 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 a.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) --and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') --group by a.jflbid,b.yblb00,b.fbbh00 and (decode(Trim(c.yblb00),'',b.FBBH00,c.FBBH00)=as_fbbh00 OR as_fbbh00='-1') group by a.jflbid,decode(Trim(c.yblb00),'',b.yblb00,C.yblb00),decode(Trim(c.yblb00),'',b.FBBH00,c.FBBH00) having sum(nvl(a.jzje00,0))<>0; begin for yb in cur_sf_gfjfb0 loop select YBMC00,PXH000 into ls_YBMC00,LS_PXH000 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00; LS_XMBH02:=to_char(to_number(nvl(LS_PXH000,100))+100); Update BM_MZSRTJ set XMBH02=LS_XMBH02,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 XMBH02=LS_XMBH02,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 select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02,XMJE03,XMJE04,XMBH02) values(ad_ID0000,ls_BM0000,yb.JFJE00,ls_YBMC00||'(记帐)',Decode(yb.jflbid,0,0,yb.JFJE00),Decode(yb.jflbid,0,yb.JFJE00,0),LS_XMBH02); 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; --计算公费费用 select sum(nvl(c.gfje00,0)) into ls_GFJE00 from sf_brfy00 a,bm_brxxb0 b,SF_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 (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') and a.gfje00<>'0' and a.hjje00=0 and c.JMBZ00 not in ('5'); if ls_GFJE00<>0 then update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='费用减免',XMJE03 =ls_GFJE00,XMJE04 = 0 where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1; 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; --统计血透析减免金额 by MZSF-20150930-001 --移到最后 MZSF-20160224-001 -- select abs(sum(nvl(c.gfje00,0))) into ls_GFJE00 -- from sf_brfy00 a,bm_brxxb0 b,SF_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 (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') -- and a.gfje00<>'0' and a.hjje00=0 and c.JMBZ00 in ('5'); -- if ls_GFJE00<>0 then -- update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='血透费用减免',XMJE03 =ls_GFJE00,XMJE04 = 0 where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1; -- 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; --老年医院计算本院福利卡及医疗救助(FBBH00=21,22) IF V_YYID00='220035' then --省老年医院专用 select nvl(sum(a.zfje00),0) into ls_GFJE00 from sf_brfy00 a,bm_brxxb0 b where 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='21' and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1'); if ls_GFJE00<>0 then update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='本院福利卡',XMJE03 =ls_GFJE00,XMJE04 = 0 where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1; 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,XMJE03,XMJE04) values(ad_ID0000,ls_BM0000,ls_GFJE00,'福利卡',ls_GFJE00,0); end if; 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; select nvl(sum(a.zfje00),0) into ls_GFJE00 from sf_brfy00 a,bm_brxxb0 b where 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='22' and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1'); if ls_GFJE00<>0 then update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='医疗救助',XMJE03 =ls_GFJE00,XMJE04 = 0 where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1; 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,XMJE03,XMJE04) values(ad_ID0000,ls_BM0000,ls_GFJE00,'医疗救助',ls_GFJE00,0); end if; 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; end if; --begin 长乐医院体检相关费用统计 if V_YYID00='222137' then begin select trim(VALUE0) into ls_TJ_ZEJMXMID from xt_xtcs00 where name00='TJ_ZEJMXMID'; exception when others then ls_TJ_ZEJMXMID:=''; end; select sum(A.HJJE00) HJJE00 into ls_KFJE00 --卡费 from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00 = B.SFXMID and B.MZFPID = C.FPXMID and A.DJH000 = H.DJH000 and H.BRID00 = I.BRID00 and b.sfxmid+0 = 8000000218 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 ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)); -- and ((as_BRLB00='0') or (as_BRLB00='1' and I.YBID00 <> '0') or (as_BRLB00='2' and I.YBID00 = '0')); Update BM_MZSRTJ set XMJE01 = XMJE01-NVL(ls_KFJE00,0) where XMMC01 ='其他费' and ID0000 = ad_ID0000; insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01) values(ad_ID0000,23,'IC卡',NVL(ls_KFJE00,0)); select sum(A.HJJE00) HJJE00 into ls_DWDZJE_NEW --单位打折金额（新） from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00 = B.SFXMID and B.MZFPID = C.FPXMID and A.DJH000 = H.DJH000 and H.BRID00 = I.BRID00 and A.BEIZHU like '%DWZK%' and i.fbbh00=99 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 ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)); select sum(A.HJJE00) HJJE00 into ls_DWDZJE --卡费 from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00 = B.SFXMID and B.MZFPID = C.FPXMID and A.DJH000 = H.DJH000 and H.BRID00 = I.BRID00 and b.sfxmid+0 = 2917 and i.fbbh00=99 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 ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)); -- and ((as_BRLB00='0') or (as_BRLB00='1' and I.YBID00 <> '0') or (as_BRLB00='2' and I.YBID00 = '0')); select sum(A.HJJE00) HJJE00 into ls_QBDZJE --单位和个人打折金额 from SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H where A.XMBH00 = B.SFXMID and B.MZFPID = C.FPXMID and A.DJH000 = H.DJH000 and b.sfxmid+0 = 2917 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 ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)); insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01) values(ad_ID0000,24,'单位体检打折',NVL(ls_DWDZJE,0)+NVL(ls_DWDZJE_NEW,0)); Update BM_MZSRTJ set XMJE01 = XMJE01-(NVL(ls_DWDZJE,0)+NVL(ls_DWDZJE_NEW,0)) where XMMC01 ='其他费' and ID0000 = ad_ID0000; if as_XXLB00 = '0' then --按票据项目 select NVL(XMJE01,0) into ls_QBDZJE FROM BM_MZSRTJ WHERE XMBH01='22' and ID0000 = ad_ID0000; insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01) values(ad_ID0000,25,'个人体检打折',NVL(ls_QBDZJE,0)-NVL(ls_DWDZJE,0)); -- delete from BM_MZSRTJ where XMBH01='22' and ID0000 = ad_ID0000; --去掉打折发票项目 update BM_MZSRTJ set xmmc01='',xmje01='' where XMBH01='22' and ID0000 = ad_ID0000; --去掉打折发票项目 else insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01) values(ad_ID0000,25,'个人体检打折',NVL(ls_QBDZJE,0)-NVL(ls_DWDZJE,0)); Update BM_MZSRTJ set XMJE01 = XMJE01-(NVL(ls_QBDZJE,0)-NVL(ls_DWDZJE,0)) where XMMC01 ='其他费' and ID0000 = ad_ID0000; end if; --select sum(decode(I.FBBH00,99,A.HJJE00,0)) DWTJJE,sum(decode(B.XMMC00,'自费体检舍去金额',A.HJJE00,0)) SQJE00, sum(decode(I.FBBH00,99,DECODE(A.JMBZ00,'2',A.HJJE00,0),0)) DWDZJE --未结帐的单位体检金额 /* select sum(decode(H.JZDH00,0,decode(I.FBBH00,99,A.HJJE00,0),0)) DWTJJE ,sum(decode(H.JZDH00,0,decode(A.xmbh00,ls_TJ_ZEJMXMID,decode(I.FBBH00,99,A.HJJE00,0),0),0)), sum(decode(I.FBBH00,99,0,decode(A.xmbh00,ls_TJ_ZEJMXMID,decode(H.JZDH00,0,0,A.HJJE00),0)))--,sum(decode(H.JFLBID,0,decode(A.xmbh00,ls_TJ_ZEJMXMID,decode(sign(H.JZDH00),1,A.HJJE00,0),0),0)) into ls_DWTJ01,ls_DZJE01,ls_DZJE02 --ls_DZJE01未结算的单位体检折扣金额，ls_DZJE02个人体检折扣金额 FROM SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00 = B.SFXMID and B.MZFPID = C.FPXMID and A.DJH000 = H.DJH000 and H.BRID00 = I.BRID00 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 ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)); --and H.JZDH00+0=0; --结帐的单位体检金额 select sum(-1*A.HJJE00),sum(-1*A.GFJE00) into ls_DWTJ02,ls_DZJE00 --ls_DZJE00结算后的单位折扣金额 from SF_JZB000 A,BM_BRXXB0 B where A.BRID00=B.BRID00 and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZRQ00||A.JZSJ00>=as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00<=as_JSRQ00||as_JSSJ00 and B.FBBH00=99; --结帐后记帐金额 --select sum(a.hjje00) into ls_DWTJ03 select sum(decode(a.xmbh00,ls_TJ_ZEJMXMID,0,a.hjje00)) into ls_DWTJ03 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and b.CZRQ00 >= as_KSRQ00 and b.CZRQ00 <= as_JSRQ00 and b.CZRQ00||b.CZSJ00>=as_KSRQ00||as_KSSJ00 and b.CZRQ00||b.CZSJ00<=as_JSRQ00||as_JSSJ00 and --a.xmbh00<>ls_TJ_ZEJMXMID and b.jzdh00 in (select a.jzdh00 from SF_JZB000 A,BM_BRXXB0 B where A.BRID00=B.BRID00 -- and A.JZRQ00 >= as_KSRQ00 -- and A.JZRQ00 <= as_JSRQ00 -- and A.JZRQ00||A.JZSJ00>=as_KSRQ00||as_KSSJ00 -- and A.JZRQ00||A.JZSJ00<=as_JSRQ00||as_JSSJ00 and B.FBBH00=99); --体检那边结帐门诊收费处未结算 select sum(a.hjje00),sum(decode(A.XMBH00,ls_TJ_ZEJMXMID,a.hjje00,0)) into ls_DWTJ04,ls_DZJE03 --select sum(decode(A.XMBH00,ls_TJ_ZEJMXMID,a.hjje00,0)) into ls_DZJE03 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and b.CZRQ00 >= as_KSRQ00 and b.CZRQ00 <= as_JSRQ00 --and a.xmbh00<>ls_TJ_ZEJMXMID and b.CZRQ00||b.CZSJ00>=as_KSRQ00||as_KSSJ00 and b.CZRQ00||b.CZSJ00<=as_JSRQ00||as_JSSJ00 and B.JZDH00<>0 AND b.brfb00=99 and not exists (select 1 from SF_JZB000 A where b.jzdh00=a.jzdh00 and A.FBBH00=99);*/ --select sum(decode(H.JZDH00,0,decode(I.FBBH00,99,A.HJJE00,0),0)) DWTJJE ,sum(decode(H.JZDH00,0,decode(A.xmbh00,ls_TJ_ZEJMXMID,decode(I.FBBH00,99,A.HJJE00,0),0),0)), --sum(decode(I.FBBH00,99,0,decode(A.xmbh00,ls_TJ_ZEJMXMID,decode(H.JZDH00,0,0,A.HJJE00),0)))--,sum(decode(H.JFLBID,0,decode(A.xmbh00,ls_TJ_ZEJMXMID,decode(sign(H.JZDH00),1,A.HJJE00,0),0),0))] -- ls_DWTJ00:= ls_DWTJ01+ls_DWTJ02; -- ls_DZZJE0:= ls_DZJE01+ls_DZJE02; select --sum(decode(A.XMBH00,ls_TJ_ZEJMXMID,0,a.hjje00)) sum(a.hjje00) into ls_DWTJ01 --ls_DZJE01未结算的单位体检折扣金额，ls_DZJE02个人体检折扣金额 FROM SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I where A.XMBH00 = B.SFXMID and B.MZFPID = C.FPXMID and ((H.JFLBID=1) OR (H.JFLBID=0 AND H.JZDH00>0)) --个人体检打折金额如果还未结算将不参与统计 and A.DJH000 = H.DJH000 and H.BRID00 = I.BRID00 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 ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0)) and I.FBBH00=99; ---SELECT NVL(SUM(HJJE00),0) into lv_DAXJJE --- FROM SF_BRFY00 --- WHERE CZRQ00>=as_KSRQ00 --- AND CZRQ00<=as_JSRQ00 --- and CZRQ00||CZSJ00>=as_KSRQ00||as_KSSJ00 --- and CZRQ00||CZSJ00<=as_JSRQ00||as_JSSJ00 --- and jflbid=0 and jzdh00+0=0; select sum(-1*A.HJJE00) into ls_DWTJ02 --单位打发票金额 from SF_JZB000 A,BM_BRXXB0 B where A.BRID00=B.BRID00 and A.JZRQ00 >= as_KSRQ00 and A.JZRQ00 <= as_JSRQ00 and A.JZRQ00||A.JZSJ00>=as_KSRQ00||as_KSSJ00 and A.JZRQ00||A.JZSJ00<=as_JSRQ00||as_JSSJ00 and B.FBBH00=99; --ls_DWTJ00:= nvl(ls_DWTJ01,0)+nvl(ls_DWTJ02,0)+nvl(ls_DZJE00,0)+nvl(ls_DWTJ03,0)+nvl(ls_DWTJ04,0); --单位体检金额 --ls_DZZJE0:= abs(NVL(ls_DZJE00,0))+abs(nvl(ls_DZJE01,0))+abs(nvl(ls_DZJE02,0))+abs(nvl(ls_DZJE03,0)); --打折金额 ls_DWTJ00:= nvl(ls_DWTJ01,0); --单位体检金额 Update BM_MZSRTJ set XMJE02 = ls_DWTJ00 ,XMMC02='单位体检金额' where ID0000 = ad_ID0000 and XMMC02 IS NULL and rownum=1; if sql%notfound then insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02) values(ad_ID0000,24,ls_DWTJ00,'单位体检金额'); end if; Update BM_MZSRTJ set XMJE02 = abs(nvl(ls_DWTJ02,0)) ,XMMC02='单位现金结算' where ID0000 = ad_ID0000 and XMMC02 IS NULL and rownum=1; if sql%notfound then insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02) values(ad_ID0000,24,abs(nvl(ls_DWTJ02,0)),'单位现金结算'); end if; -- Update BM_MZSRTJ set XMJE02 =ls_DZZJE0 ,XMMC02=' 其中打折金额' where ID0000 = ad_ID0000 and XMMC02 IS NULL and rownum=1; -- if sql%notfound then -- insert into BM_MZSRTJ(ID0000,XMBH01,XMJE02,XMMC02) values(ad_ID0000,24,ls_DZZJE0,' 其中打折金额'); -- end if; -- Update BM_MZSRTJ set XMJE02 = XMJE02-nvl(ls_DWTJ02,0) where ID0000 = ad_ID0000 and XMBH02 = '90'; Update BM_MZSRTJ set XMJE02 = XMJE02-ls_DWTJ00 where ID0000 = ad_ID0000 and XMBH02 = '91'; --折前金额 --Update BM_MZSRTJ set XMJE02 = XMJE02+nvl(ls_DWTJ02,0) where ID0000 = ad_ID0000 and XMBH02 = '90'; --折前金额 Update BM_MZSRTJ set XMJE02 = XMJE02+nvl(ls_DWTJ02,0) where ID0000 = ad_ID0000 and XMMC02 = '单位体检金额'; --单位体检金额 end if; --end 长乐医院体检相关费用统计 --计算左边的合计数 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 (V_YYID00 <>'220007') or (V_YYID00 <>'A') or (V_YYID00 <>'B') then begin 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)+nvl(ls_yhkjs0,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)+nvl(ls_yhkjs0,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)+nvl(ls_yhkjs0,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)+nvl(ls_yhkjs0,0),XMJE04 = 0, XMMC02 = '预交金病人结算' where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; end; end IF; end ; /* 去掉本段代码 BY MZSF-20130621-003 -- 厦门同安中医院收入报表现金病人结算要扣除交款日报表中的其它金额(商业保险支付) by MZSF-20110613-002-- --收费员交款日报表过程：SP_SF_SFYJK0_RBB000 现金扣除其它金额，医保增加其它金额 if V_YYID00='223280' then declare CURSOR CUR_SF_SBZFJE IS ---商保支付 select C.YBMC00,sum(nvl(a.jfje00,0)) jfje00 from sf_brjfb0 a,sf_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.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and a.pjh000>0 and A.ZFFS00 not in (1,2,3,4,8,10,11,18) and exists (select 1 from SF_PJSYQK AA where AA.FZPJH0 = A.PJH000) GROUP BY C.YBMC00; begin for C_SB in CUR_SF_SBZFJE loop update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)-nvl(C_SB.JFJE00,0) where ID0000 = ad_ID0000 and XMBH02 = '90'; ---现金结算扣除商保金额 update BM_MZSRTJ set XMJE02 =nvl(XMJE02,0)+nvl(C_SB.JFJE00,0) where ID0000 = ad_ID0000 and XMMC02=C_SB.YBMC00; end loop; end; end if; --end if; */ --平摊医院要求增加未结算的合计金额 if (V_YYID00 ='222211') or (V_YYID00='220035') then insert into BM_MZSRTJ(ID0000,XMBH02,XMMC02,XMJE02) select ad_ID0000,'97','未结算金额',sum(A.HJJE00) from SF_FYMX00 A,SF_BRFY00 C where A.DJH000=C.DJH000 AND C.JZDH00=0 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00>=as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00<=as_JSRQ00||as_JSSJ00 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.BRID00,C.CZRQ00; --至今未结帐 group by ad_ID0000; --已结算的合计金额 insert into BM_MZSRTJ(ID0000,XMBH02,XMMC02,XMJE02) select ad_ID0000,'98','已结算金额',sum(A.HJJE00) HJJE00 from SF_FYMX00 A,SF_BRFY00 C where A.DJH000=C.DJH000 AND C.JZDH00<>0 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00>=as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00<=as_JSRQ00||as_JSSJ00 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.BRID00,C.CZRQ00 --至今未结帐 group by ad_ID0000; end if; if (V_YYID00 ='220007') or (V_YYID00 ='A') or (V_YYID00 ='B') or (V_YYID00 ='C') or (V_YYID00 ='D') then --省二要求加上异地医保的合计 add by xuzw begin SELECT XMJE02 INTO ls_xjjs00 FROM BM_MZSRTJ where ID0000 = ad_ID0000 and XMBH02 = '90'; EXCEPTION when OTHERS then ls_xjjs00:=0; end; ---异地医保小计-- 2011.07.04 zhanghr 修正bug 为什么要update XMBH02 = '90' ? -- update BM_MZSRTJ set XMJE02 =nvl(ls_YDYBHJ,0) where ID0000 = ad_ID0000 and XMBH02 = '90'; select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMJE02 = nvl(ls_YDYBHJ,0) ,XMMC02 = '异地医保小计',XMJE03 =nvl(ls_YDYBHJ,0),XMJE04 =nvl(ls_YDYBHJ,0) 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,XMJE03,XMJE04) values(ad_ID0000,ls_BM0000,nvl(ls_YDYBHJ,0),'异地医保小计',nvl(ls_YDYBHJ,0),nvl(ls_YDYBHJ,0)); end if; -- update BM_MZSRTJ set XMJE02 =nvl(ls_xjjs00,0)-nvl(ls_yhkjs0,0) where ID0000 = ad_ID0000 and XMBH02 = '90'; -- select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; -- Update BM_MZSRTJ set XMJE02 = ls_YDYBJE ,XMMC02 = '异地医保小计',XMJE03 =ls_YDYJJE,XMJE04 =ls_YDXJJE where ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00; -- if SQL%NOTFOUND then -- insert into BM_MZSRTJ(ID0000,XMJE02,XMMC02,XMJE03,XMJE04) values(ad_ID0000,ls_YDYBJE,'异地医保小计',ls_YDYJJE,ls_YDXJJE); -- end if; ---modify by MZSF-20110520-003 省二医院增加市医保小计(市医保为BM_YBBRLB.YBZXLB=2)--- select sum(nvl(a.jfje00,0)) into ls_SYBHJ0 from sf_brjfb0 a,sf_jzb000 b,ic_ybbrlb c --,sf_brxxb0 c where a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and b.fbbh00=c.fbbh00 and b.yblb00=c.yblb00 and a.zffs00 in(3,4,13,22) and c.YBZXLB='2' 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 B.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1'); select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update bm_mzsrtj set XMJE02 = ls_SYBHJ0,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) values(ad_ID0000,ls_BM0000,ls_SYBHJ0,'市医保小计'); end if; ----医保小计(含异地医保) select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update bm_mzsrtj set XMJE02 = ls_YBJE00,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) values(ad_ID0000,ls_BM0000,ls_YBJE00,'医保小计(含异地医保)'); end if; 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; --地质医院要求日收入报表加上预交金余额 与 收费员应缴金额。 if V_YYID00='821166' then begin select trim(VALUE0) into ls_value from xt_xtcs00 where name00='SF_SFYJXGSJQY'; if ls_value='Y' then begin select nvl(sum(bqjc00),0) into ls_sqjc from sf_yjjye0 where (brid00,czrq00) in (select brid00,max(czrq00) czrq00 from sf_yjjye0 where brid00 = 0 and CZRQ00 <= as_KSRQ00 and CZRQ00||CZSJ00 =as_KSRQ00 and C.JFRQ00 <=as_JSRQ00 and C.JFRQ00||C.JFSJ00 >= as_KSRQ00||as_KSSJ00 and C.JFRQ00||C.JFSJ00 <=as_JSRQ00||as_JSSJ00 and jflbid+0 = 1 ; select NVL(sum(A.ZFJE00),0) into ls_bqjs from sf_brfy00 A where A.CZRQ00 >=as_KSRQ00 and A.CZRQ00 <=as_JSRQ00 and A.CZRQ00||A.CZSJ00 >=as_KSRQ00||as_KSSJ00 and A.CZRQ00||A.CZSJ00 <=as_JSRQ00||as_JSSJ00 and A.JFLBID+0 = 1; end; else if ls_value='N' then begin select NVL(sum(DECODE(Trim(D.YBLB00),'Z',DECODE(C.ZFFS00,10,0,11,0,C.JFJE00),C.JFJE00)),0) into ls_sqjc1 from sf_brjfb0 C,bm_brxxb0 d where jflbid = 1 and c.brid00=d.brid00 and C.JFRQ00 <= as_KSRQ00 and C.JFRQ00||C.JFSJ00 =as_KSRQ00 and C.JFRQ00 <=as_JSRQ00 and C.JFRQ00||C.JFSJ00 >= as_KSRQ00||as_KSSJ00 and C.JFRQ00||C.JFSJ00 <=as_JSRQ00||as_JSSJ00 and jflbid+0 = 1 ; select NVL(sum(A.ZFJE00),0) into ls_bqjs from sf_brfy00 A where A.CZRQ00 >=as_KSRQ00 and A.CZRQ00 <=as_JSRQ00 and A.CZRQ00||A.CZSJ00 >= as_KSRQ00||as_KSSJ00 and A.CZRQ00||A.CZSJ00 <=as_JSRQ00||as_JSSJ00 and A.JFLBID+0 = 1; end; end if; end if; select round((ls_sqjc+ls_bqzj-ls_bqjs),2) into ls_bqjc from dual; select round((ls_bqzj-ls_bqjs),2) into ls_bqce from dual; select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(id0000,xmbh01,xmmc01,xmje01)values(ad_ID0000,ls_BM0000,'预交金本期余额',ls_bqjc); --SELECT NVL(SUM(XMJE00),0) into ls_xmje FROM BM_JKKJMX WHERE JKDH00 --地质医院明细表没有数据。 --IN (SELECT DISTINCT JKDH00 FROM BM_JKB000 WHERE JKRQ00>=as_KSRQ00 and JKRQ00<=as_JSRQ00 AND MZBZ00='0') AND XMBH00='06'; select sum(jfje00) into ls_xmje from sf_brjfb0 where jfrq00>=as_KSRQ00 and jfrq00<=as_JSRQ00 and zffs00=1; select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(id0000,xmbh01,xmmc01,xmje01)values(ad_ID0000,ls_BM0000,'本期收费员应缴款金额',ls_xmje); begin select trim(value0) into ls_SFDM from xt_xtcs00 where name00='IC_SFDM'; EXCEPTION when OTHERS then ls_SFDM:=0; end; select sum(HJJE00) into ls_BQZJ00 from SF_FYMX00 where XMBH00=ls_SFDM and hjje00>0 and CZRQ00>=as_KSRQ00 AND CZRQ00<=as_JSRQ00 AND CZRQ00||CZSJ00>=as_KSRQ00||as_KSSJ00 AND CZRQ00||CZSJ00<=as_JSRQ00||as_JSSJ00 ; select sum(HJJE00) into ls_BQJS00 from SF_FYMX00 where XMBH00=ls_SFDM and hjje00<0 and CZRQ00>=as_KSRQ00 AND CZRQ00<=as_JSRQ00 AND CZRQ00||CZSJ00>=as_KSRQ00||as_KSSJ00 AND CZRQ00||CZSJ00<=as_JSRQ00||as_JSSJ00 ; select ls_BQZJ00+ls_BQJS00 into ls_ickyj from dual; select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(id0000,xmbh01,xmmc01,xmje01)values(ad_ID0000,ls_BM0000,'预交金本期差额',ls_bqce); select lpad(to_char(to_number(ls_BM0000)+1),2,0) into ls_BM0000 from dual; insert into BM_MZSRTJ(id0000,xmbh01,xmmc01,xmje01)values(ad_ID0000,ls_BM0000,'IC卡押金',ls_ickyj); ---优惠金额---- select sum(nvl(c.JMJE00,0)) into ls_JMJE00 from sf_brfy00 a,bm_brxxb0 b,SF_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 (b.FBBH00=as_fbbh00 OR as_fbbh00='-1'); --insert into BM_MZSRTJ(id0000,xmbh01,XMMC02,XMJE02)values(ad_ID0000,ls_BM0000,'优惠金额',ls_JMJE00); select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; Update BM_MZSRTJ set XMBH02=ls_XMBH00,XMJE02 = nvl(ls_JMJE00,0),XMMC02 = '优惠金额' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00; end; end if; ---by MZSF-20120509-002--按医保中心排序--- insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01,ORDER0,XMHJ00) select ad_ID0000,XMBH01,XMMC01,XMJE01,'',rownum from ( select XMBH01,XMMC01,XMJE01,ORDER0 from BM_MZSRTJ where ID0000=ad_ID0000 order by XMBH01 ) ; OPEN CUR_BM_MZSRTJ; loop fetch CUR_BM_MZSRTJ into ls_XMBH02,ls_XMMC02,ls_XMJE02,ls_XH0000; exit WHEN CUR_BM_MZSRTJ%NOTFOUND; if trim(ls_XMBH02)not in ('00','90','91','92','93','94','95','96','97','98','99') then ls_XMBH02:=''; end if; Update BM_MZSRTJ set XMBH02 =ls_XMBH02,XMMC02=ls_XMMC02,XMJE02=ls_XMJE02 where ID0000 = ad_ID0000 and XMHJ00=ls_XH0000; if sql%notfound then insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMMC02,XMJE02,XMHJ00) values(ad_ID0000,'99',ls_XMBH02,ls_XMMC02,ls_XMJE02,ls_XH0000); end if; end loop; CLOSE CUR_BM_MZSRTJ; --统计血透析减免金额 by MZSF-20150930-001 --移到这里MZSF-20160224-001,否则会出现多条"血透费用减免" select abs(sum(nvl(c.gfje00,0))) into ls_GFJE00 from sf_brfy00 a,bm_brxxb0 b,SF_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 (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') and a.gfje00<>'0' and a.hjje00=0 and c.JMBZ00 in ('5'); if ls_GFJE00<>0 then --select min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null; -- Update BM_MZSRTJ set XMJE02 = ls_GFJE00,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,ls_GFJE00,'血透费用减免',100); -- end if; end if; delete BM_MZSRTJ where ID0000 = ad_ID0000 and nvl(XMHJ00,0)=0; --统计健康通金额--begin-- if SF_SF_TYZD00('民生通系统参数','PmstOpen')='Y' then select sum(JFJE00) into LS_YYTJE0 from SF_BRJFB0 A,BM_BRXXB0 b where 1=1 and A.BRID00=B.BRID00 and A.ZFFS00 in (26) 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 a.CZYKS0 in (select bmbh00 from bm_bmbm00 a,xt_yyxx00 b where a.yyid00=b.yyid00 and yyjc00=as_YYJC00)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') ; --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; --省二预交金病人结算- 健康通支付 by MZSF-20161213-00 --增加福建中医药大学附属康复医院820007 by MZSF-20170406-004 if (V_YYID00 ='220007') or (V_YYID00 ='820007') or (V_YYID00 ='A') or (V_YYID00 ='B') or (V_YYID00 ='C') or (V_YYID00 ='D') then Select sum(XMJE02) into LS_YJJJS0 from BM_MZSRTJ where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; if nvl(LS_YYTJE0,0)>=nvl(LS_YJJJS0,0) and V_YYID00 <>'220007' then update BM_MZSRTJ set XMJE02=0 where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93' ; else update BM_MZSRTJ set XMJE02=nvl(LS_YJJJS0,0)-nvl(LS_YYTJE0,0) where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; end if; delete BM_MZSRTJ where ID0000 = ad_ID0000 and nvl(XMHJ00,0)=0; end if; end if; --统计健康通金额--end-- --统计银医通金额--begin-- if SF_SF_TYZD00('银医通共享平台','payopen')='Y' then select sum(JFJE00) into LS_YYTJE0 from SF_BRJFB0 A,BM_BRXXB0 b where 1=1 and A.BRID00=B.BRID00 and A.ZFFS00 in (18) 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)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') ; 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 = LS_YYTJE0,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,LS_YYTJE0,'银医通支付',100); end if; end if; --/* if LS_YYTJE0>0 then Select sum(XMJE02) into LS_YJJJS0 from BM_MZSRTJ where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; if LS_YYTJE0>=LS_YJJJS0 then update BM_MZSRTJ set XMJE02=0 where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93' ; else update BM_MZSRTJ set XMJE02=LS_YJJJS0-LS_YYTJE0 where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; end if; end if; -- */ delete BM_MZSRTJ where ID0000 = ad_ID0000 and nvl(XMHJ00,0)=0; end if; --统计银医通金额--end-- IF 1=0 THEN --统计微信支付金额--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 SF_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)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') ; --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; /* if nvl(LS_YYTJE0,0)>0 then Select sum(XMJE02) into LS_YJJJS0 from BM_MZSRTJ where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; if nvl(LS_YYTJE0,0)>=nvl(LS_YJJJS0,0) then update BM_MZSRTJ set XMJE02=0 where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93' ; else update BM_MZSRTJ set XMJE02=nvl(LS_YJJJS0,0)-nvl(LS_YYTJE0,0) where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; end if; end if; */ delete BM_MZSRTJ where ID0000 = ad_ID0000 and nvl(XMHJ00,0)=0; 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 SF_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)) and (b.FBBH00=as_fbbh00 OR as_fbbh00='-1') ; --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; /* if nvl(LS_YYTJE0,0)>0 then Select sum(XMJE02) into LS_YJJJS0 from BM_MZSRTJ where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; if nvl(LS_YYTJE0,0)>=nvl(LS_YJJJS0,0) then update BM_MZSRTJ set XMJE02=0 where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93' ; else update BM_MZSRTJ set XMJE02=nvl(LS_YJJJS0,0)-nvl(LS_YYTJE0,0) where ID0000=ad_ID0000 and XMMC02 = '预交金病人结算' and XMBH02 = '93'; end if; end if; */ delete BM_MZSRTJ where ID0000 = ad_ID0000 and nvl(XMHJ00,0)=0; end if; END IF; --统计支付宝支付金额--end-- -- commit; exception when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZFPSR_TJ0000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; end; /