CREATE OR REPLACE PROCEDURE SP_ZY_YBBRJS_BB0000 ( as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_KSSJ00 in char, --开始时间 as_JSSJ00 in char, --结束时间 as_CZYXM0 in char, --操作员姓名 as_YBZXMC in char, --医保中心名称 as_TJLB00 in char, --统计类别'0':按汇总显示,'1':按明细显示 as_BQMC00 in char, --病区名称 as_YYID00 in char, --分院ID as_ZTJS00 in char, --0统计中按实际日期统计，1按出院日期统计 as_BRYBZX in char default '-1', ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_SFDZX0 in char default 'N', --是否按大中心统计 as_WSQK00 in number default 0, --外伤情况 0 全部 1外伤 2非外伤 as_SFKCGF in char default '0', --0:不扣除 1：扣除 as_HSFDBF in char default '0', --是否包含伙食费、代办费 0:包含 1:不包含 as_IFJZFP in char default '0' --0：统计所有病人;1:只统计精准扶贫病人 ) as -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2007.08.08 增加商业保险支付金额的处理ZYSF-20070807-003 -- xzw 2008.02.22 根据ZY_JZB000字段CZYKS0来识别分院情况 -- xzw 2008.04.01 增加传入参数as_ZTJS00 -- xzw 2008.04.07 根据传入的as_ZTJS00值来统计是按实际日期统计，还是按出院日期统计 -- xzw 2008.08.26 增加保健基金支付,商保基金支付（省属三家市属九家医疗机构）结算 -- xzw 2008.09.04 有关联YGBH00字段的都加上YGBH00+0 -- XZW 2009.10.14 前台增加显示主治医生 -- qks 2009.12.15 出院日期和时间条件 改用ZY_JZB000.YBCYRQ以及YBCYSJ -- xzw 2010.03.10 增加公务员补助金额 -- qks 2010.03.24 修改公务员补助GWYBZ0、商保(ad_SBZHJE、ad_SBTJJE) 是否包含在统筹金额中的问题; -- zhangyc 2010.05.06 修改病人住院天数=(出院登记日期(zy_brxxb0.CYDJRQ)-入院日期(zy_brxxb0.ryrq00))+1 -- qks 2010.05.29 3.0升级解决速度慢问题 --zhangyc 2011.01.10 增加as_BRYBZX 病人医保类别条件 --zhangwz 2011.06.30 增加入参 as_SFDZX0 来实现按这个大中心进行查询. by ZYSF-20110621-003. --zhangwz 2011.09.09 增加统计结账时的医保非医保部分金额数据的统计 by ZYSF-20110714-003. --jinfl 2011.09.29 将除泉州地区的商保包含在统筹金额中. --jinfl 2012.02.17 公务员补助独立的医保中心从通用字典表中获取 ZYSF-20120206-001 --zhanghr 2012.03.21 修改脚本写法 ZYSF-20120321-001 -- liuj 2013.10.22 增加统计药品费 for ZYSF-20130715-001 -- zhangyc 2013.11.20 增加列民政救助 医院支付 by ZYSF-20130723-001 -- jinfl 2013.11.22 增加判断外伤情况 ZYSF-20131016-001 -- zhangyc 2013.12.09 修改ypfy00 写法 by ZYSF-20131207-001 -- zhangyc 2013.12.12 增加入参是否扣除公费病人（费别有参数ZY_SFBKGFBR设置）by ZYSF-20131206-001 -- zhangyc 2013.12.31 修正ZYSF-20131206-001修正的脚本和代码归档到1116和1216版本 by ZYSF-20131231-001 -- liuj 2014.01.03 修改自负金额不算在医保小计内,修正的脚本和代码归档到1116和1216版本 for ZYSF-20140103-001 -- zhangyc 2014.01.17 按医保中心查询增加人数 by ZYSF-20140116-001 （可归档到1116，1216） -- zhangyc 2014.02.12 增加按参数ZY_YBJSRBBSFXSCZYB，和yyid=220007，820007控制以及按异地医保统计 by ZYSF-20140207-002 -- liuj 2014.02.28 显示明细信息时，合计人数不从过程出，改为直接前台处理 for ZYSF-20140219-001 --zhangyc 2014.03.20 增加商保个人和统筹按ybbhsb=14 判断 by ZYSF-20140319-003 --zhangyc 2014.08.18 增加(省二医院YYID00='220007','820007')IC_YBBRLB.YBZXLB='2' 市医保小计 IC_YBBRLB.YBZXLB='@'新农合小计 by ZYSF-20140816-001 --zhangyc 2015.04.08 增加YBYL18(民政(医疗救助基金)补助金额)合并到YBYL01 by ZYSF-20141211-001 --zhangyc 2015.10.23 增加入参as_HSFDBF(是否包含伙食费、代办费 0:包含 1:不包含)增加SF_ZY_SFYLXM取值 by ZYSF-20151019-001 --zhengzk 2016.08.02 增加列大病补充补偿金额 for ZYSF-20160721-001 --dsm 2018.03.29 商保和公务员补助如果包含在统筹的不体现 维护归档 for ZYSF-20180329-002 --linshu 2018.08.10 增加入参as_IFJZFP是否只统计精准扶贫住院病人 by ZYSF-20180726-002 --linbin 2020.05.12 将民政救助与民政补助分开体现 by ZYSF-20200413-001 --qiulf 2020.08.18 统计方式从BRID00改为ZYID00,病人多次住院不会合并为一条数据 by ZYSF-20200812-001 --dsm 2020.09.14 SBJJZF,SBGRZF,GWYBZ改从ZY_BRJFB0获取 for ZYSF-20200914-002 --linshu 2020.10.29 归档了现场的不是单组分组函数执行存储过程错误’报错问题的修正 by ZYSF-20201029-001 ls_YBMC00 IC_YBBRLB.YBMC00%type; ls_BRID00 BM_BRXXB0.BRID00%type; ls_ZFFS00 ZY_BRJFB0.ZFFS00%type; ls_JFCZY0 ZY_BRJFB0.JFCZY0%type; ls_ZYID00 ZY_BRXXB0.ZYID00%type; ls_DQBQ00 ZY_BRXXB0.DQBQ00%type; ls_ZFJE00 ZY_BRJFB0.JFJE00%type; ls_GRZHZF ZY_BRJFB0.JFJE00%type; ls_TCJJZF ZY_BRJFB0.JFJE00%type; ls_SBGRZH ZY_BRJFB0.JFJE00%type; ls_SBTCJJ ZY_BRJFB0.JFJE00%type; ls_JJZFE0 ZY_BRJFB0.JFJE00%type; ls_SYBXZF ZY_BRJFB0.JFJE00%type; ls_JZJE00 ZY_BRJFB0.JFJE00%type; ls_JMJE00 ZY_BRJFB0.JFJE00%type; ls_ZYTS00 BM_GHKSTJ.GHKS11%type; --病人的住院天数 ls_COUNT0 NUMBER(12); ls_DQKS00 ZY_BRXXB0.DQKS00%type; ls_SBZFE0 ZY_BRJFB0.JFJE00%type; ls_BJZFE0 ZY_BRJFB0.JFJE00%type; ls_ZZYS00 ZY_BRXXB0.ZZYS00%type; ls_GWYBZ0 ZY_BRJFB0.JFJE00%type; ls_YBBHSB IC_YBBRLB.YBBHSB%type; ls_YBZXLB IC_YBBRLB.YBZXLB%type; --医保中心类别 ls_YPWQZF ZY_JZB000.YPWQZF%type; --药品完全自费 ls_XMWQZF ZY_JZB000.XMWQZF%type; --项目完全自费 ls_YPBFZF ZY_JZB000.YPBFZF%type; --药品部分自费 ls_XMBFZF ZY_JZB000.XMBFZF%type; --项目部分自费 ls_YPFY00 ZY_JZMX00.xmje00%type; --药品费 ls_MZJZJE ZY_JZB000.YBYL01%type; --民政救助 ls_MZBZJE ZY_JZB000.YBYL18%type; --民政补助 ls_YYZFJE ZY_JZB000.YBYL05%type; --医院支付 ls_BRZJE0 ZY_JZB000.BRZJE0%type; ls_DBBCJE ZY_JZB000.YBYL26%type; --大病补充补偿金额 ls_SFBKGFBR varchar2(200); LS_YBJSRBBSFXSCZYB varchar2(100); VYYID00 XT_YYXX00.YYID00%TYPE; LS_XSXH00 Number(2); cursor CUR_ZY_YBBRJS_BBTJ00 is select C.YBMC00,F.BRID00,F.ZYID00,F.DQBQ00,F.DQKS00,D.JZY000,f.ZZYS00,C.YBBHSB, --sum(D.BRZJE0)ls_BRZJE0, --sum(D.ZFJE00) LS_ZFJE00, sum(D.BRZJE0-SF_ZY_SFYLXM(nvl(as_HSFDBF,'0'),D.ZYID00,D.JZDH00,D.BRZJE0)) ls_BRZJE0, sum(D.ZFJE00-SF_ZY_SFYLXM(nvl(as_HSFDBF,'0'),D.ZYID00,D.JZDH00,D.ZFJE00)) LS_ZFJE00, sum(D.GRZHZF) LS_GRZHZF, sum(D.TCJJZF) LS_TCJJZF, --sum(decode(sign(Instrb(','||trim(YBBHSB)||',',',14,')),1,0,D.SBGRZH)) LS_SBGRZH, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00=10),0)) LS_SBGRZH, --sum(decode(sign(Instrb(','||trim(YBBHSB)||',',',14,')),1,0,D.SBTCJJ)) LS_SBTCJJ, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00=11),0)) LS_SBTCJJ, sum(JJZFE0) LS_JJZFE0, --sum(SYBXZF) LS_SYBXZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00=13),0)) LS_SYBXZF, SUM(D.JZJE00) LS_JZJE00, sum(D.GFJE00) ls_JMJE00, sum(SBZFE0) ls_SBZFE0, sum(BJZFE0) ls_BJZFE0, --sum(decode(sign(Instrb(','||trim(YBBHSB)||',',',15,')),1,0,D.GWYBZ0)) ls_GWYBZ0, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00=16),0)) ls_GWYBZ0, sum(nvl(D.YPWQZF,0)) ls_YPWQZF, sum(nvl(D.XMWQZF,0)) ls_XMWQZF, sum(nvl(D.YPBFZF,0)) ls_YPBFZF, sum(nvl(D.XMBFZF,0)) ls_XMBFZF, --sum(decode(h.xmbh00,1,h.xmje00,2,h.xmje00,3,h.xmje00,0)) ypfy00, sum((select sum(h.xmje00) from zy_jzmx00 h where h.xmbh00 in (1,2,3) and h.jzdh00=d.jzdh00)) ypfy00, --sum(nvl(D.YBYL01,0)) YBYL01, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00=22),0)) YBYL01, --sum(nvl(D.YBYL18,0)) YBYL18, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00=22),0)) YBYL18, sum(nvl(D.YBYL05,0)) YBYL05, sum(D.YBYL26) DBBCJE from ic_ybbrlb C,zy_jzb000 D,BM_YGBM00 E,zy_brxxb0 F,BM_BMBM00 K,BM_BMBM00 G--,zy_jzmx00 h where D.yblb00 = C.yblb00 and D.FBBH00 = C.FBBH00 and D.FBBH00<>1 and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 --and d.jzdh00=h.jzdh00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and decode(as_ZTJS00,'0',D.JZRQ00,D.YBCYRQ)||decode(as_ZTJS00,'0',D.JZSJ00,D.YBCYSJ) >= as_KSRQ00||as_KSSJ00 and decode(as_ZTJS00,'0',D.JZRQ00,D.YBCYRQ)||decode(as_ZTJS00,'0',D.JZSJ00,D.YBCYSJ) <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ((as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB ) or (as_YBZXMC = '0')) ) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') -- and ((as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1' and as_SFDZX0='Y') or (as_YBZXMC<>'异地医保' and 1=1)) and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))) and (( as_IFJZFP='1' and F.SFJZFP='1' ) or ( as_IFJZFP='0' and 1=1 )) group by C.YBMC00,F.BRID00,F.ZYID00,F.DQBQ00,D.JZY000,F.DQKS00,f.ZZYS00,C.YBBHSB; begin if as_YBZXMC<>'异地医保' then if (as_SFDZX0='Y') and (as_YBZXMC <> '0') then select YBZXLB into ls_YBZXLB from IC_YBBRLB where YBMC00=as_YBZXMC; else ls_ybzxlb:='Z'; --随便一个值 end if; end if; select trim(VALUE0) into ls_SFBKGFBR from xt_xtcs00 where name00 in ('ZY_SFBKGFBR'); begin select trim(VALUE0) into LS_YBJSRBBSFXSCZYB from XT_XTCS00 where name00='ZY_YBJSRBBSFXSCZYB'; exception when others then LS_YBJSRBBSFXSCZYB := 'N'; end; select SF_SF_GETYYID00() YYID00 into Vyyid00 from dual; Select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; open CUR_ZY_YBBRJS_BBTJ00; LOOP fetch CUR_ZY_YBBRJS_BBTJ00 INTO ls_YBMC00,ls_BRID00,ls_ZYID00,ls_DQBQ00,ls_DQKS00,ls_JFCZY0,ls_ZZYS00,ls_YBBHSB,ls_BRZJE0,ls_ZFJE00,LS_GRZHZF, LS_TCJJZF,LS_SBGRZH,LS_SBTCJJ,ls_JJZFE0,ls_SYBXZF,LS_JZJE00,ls_JMJE00,ls_SBZFE0,ls_BJZFE0,ls_GWYBZ0, ls_YPWQZF,ls_XMWQZF,ls_YPBFZF,ls_XMBFZF,ls_YPFY00,ls_MZJZJE,ls_MZBZJE,ls_YYZFJE,ls_DBBCJE; exit when CUR_ZY_YBBRJS_BBTJ00%NOTFOUND; select to_date(NVL(CYDJRQ,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(ryrq00,'YYYYMMDD')+1 into ls_ZYTS00 from zy_brxxb0 where zyid00 = ls_ZYID00; if NVL(ls_BRZJE0,0) <>0 then --合计金额 Update BM_GHKSTJ Set GHKS88 = NVL(GHKS88,0)+ls_BRZJE0,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS88,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BRZJE0,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_ZFJE00,0) <>0 then --自付金额 Update BM_GHKSTJ Set GHKS03 = NVL(GHKS03,0)+ls_ZFJE00 ,XMHJ00=NVL(XMHJ00,0) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS03,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZFJE00,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_GRZHZF,0) <> 0 then --医保个人帐户 Update BM_GHKSTJ Set GHKS04 = NVL(GHKS04,0)+ls_GRZHZF ,XMHJ00=NVL(XMHJ00,0)+ls_GRZHZF ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS04,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GRZHZF,ad_ID0000,ls_GRZHZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(LS_TCJJZF,0)<>0 then --医保统筹基金 Update BM_GHKSTJ Set GHKS05 = NVL(GHKS05,0)+LS_TCJJZF ,XMHJ00=NVL(XMHJ00,0)+LS_TCJJZF ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS05,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,LS_TCJJZF,ad_ID0000,LS_TCJJZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_SBGRZH,0)<>0 then --商保个人帐户 Update BM_GHKSTJ Set GHKS06 = NVL(GHKS06,0)+ls_SBGRZH --,XMHJ00=NVL(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',14,')),1,0,ls_SBGRZH) ,XMHJ00=NVL(XMHJ00,0)+ls_SBGRZH ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS06,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBGRZH,ad_ID0000,ls_SBGRZH,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_SBTCJJ,0) <>0 then --商保统筹基金 Update BM_GHKSTJ Set GHKS07 = NVL(GHKS07,0)+ls_SBTCJJ --,XMHJ00=NVL(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',14,')),1,0,ls_SBTCJJ) ,XMHJ00=NVL(XMHJ00,0)+ls_SBTCJJ ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS07,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBTCJJ,ad_ID0000,ls_SBTCJJ,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if (NVL(ls_JZJE00,0)<>0)AND(NVL(LS_GRZHZF,0)=0) AND(NVL(LS_TCJJZF,0)=0) then --公费记账金额 Update BM_GHKSTJ Set GHKS08 = NVL(GHKS08,0)+ls_JZJE00 ,XMHJ00=NVL(XMHJ00,0)+ls_JZJE00 ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS08,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZJE00,ad_ID0000,ls_JZJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if (NVL(ls_JMJE00,0)<>0)AND(NVL(LS_GRZHZF,0)=0) AND(NVL(LS_TCJJZF,0)=0) then --减免金额 Update BM_GHKSTJ Set GHKS09 = NVL(GHKS09,0)+ls_JMJE00 ,XMHJ00=NVL(XMHJ00,0)+ls_JMJE00 ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS09,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JMJE00,ad_ID0000,ls_JMJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_JJZFE0,0) <>0 then --保健基金支付 Update BM_GHKSTJ Set GHKS13 = NVL(GHKS13,0)+ls_JJZFE0 ,XMHJ00=NVL(XMHJ00,0)+ls_JJZFE0 ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS13,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JJZFE0,ad_ID0000,ls_JJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_SYBXZF,0) <>0 then --商业保险支付 Update BM_GHKSTJ Set GHKS14 = NVL(GHKS14,0)+ls_SYBXZF ,XMHJ00=NVL(XMHJ00,0)+ls_SYBXZF ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS14,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SYBXZF,ad_ID0000,ls_SYBXZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; select count(*) into ls_count0 from BM_TYZD00 where ZDMC00='公务员补助独立医保中心名称' and MC0000=ls_YBMC00; if ls_count0=0 then if ls_YBMC00 in ('鲤城医疗保险分中心','丰泽医疗保险分中心','洛江医疗保险分中心','泉港医疗保险分中心','晋江医疗保险分中心','石狮医疗保险分中心', '南安医疗保险分中心','惠安医疗保险分中心','安溪医疗保险分中心','永春医疗保险分中心','德化医疗保险分中心','泉州市医疗保险中心') then ls_count0:=1; end if; end if; if ls_count0=0 then if NVL(ls_GWYBZ0,0) <>0 then --公务员补助 Update BM_GHKSTJ Set GHKS19 = NVL(GHKS19,0)+ls_GWYBZ0 ,XMHJ00=NVL(XMHJ00,0) ,GHKS11 = ls_ZYTS00 --ls_GWYBZ0 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS19,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,ls_GWYBZ0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; else if NVL(ls_GWYBZ0,0) <>0 then --公务员补助 Update BM_GHKSTJ Set GHKS19 = NVL(GHKS19,0)+ls_GWYBZ0 --,XMHJ00=NVL(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',15,')),1,0,ls_GWYBZ0) ,XMHJ00=NVL(XMHJ00,0)+ls_GWYBZ0 ,GHKS11 = ls_ZYTS00 --ls_GWYBZ0 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS19,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,ls_GWYBZ0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; end if; if NVL(ls_SBZFE0,0) <>0 then --商保基金支付（省属三家市属九家医疗机构） Update BM_GHKSTJ Set GHKS15 = NVL(GHKS15,0)+ls_SBZFE0 ,XMHJ00=NVL(XMHJ00,0) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS15,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBZFE0,ad_ID0000,'0',ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_BJZFE0,0) <>0 then --保健基金支付（省属三家市属九家医疗机构） Update BM_GHKSTJ Set GHKS16 = NVL(GHKS16,0)+ls_BJZFE0 ,XMHJ00=NVL(XMHJ00,0) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS16,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BJZFE0,ad_ID0000,ls_BJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_YPWQZF,0) <>0 then --药品完全自费 Update BM_GHKSTJ Set GHKS21 = NVL(GHKS21,0)+ls_YPWQZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS21,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPWQZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_XMWQZF,0) <>0 then --项目完全自费 Update BM_GHKSTJ Set GHKS22 = NVL(GHKS22,0)+ls_XMWQZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS22,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_XMWQZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_YPBFZF,0) <>0 then --药品部分自费 Update BM_GHKSTJ Set GHKS23 = NVL(GHKS23,0)+ls_YPBFZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS23,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPBFZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_XMBFZF,0) <>0 then --项目部分自费 Update BM_GHKSTJ Set GHKS24 = NVL(GHKS24,0)+ls_XMBFZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS24,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_XMBFZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_YPFY00,0) <>0 then --药品费 Update BM_GHKSTJ Set GHKS25 = NVL(GHKS25,0)+ls_YPFY00,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS25,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPFY00,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_MZJZJE,0) <>0 then --民政救助 Update BM_GHKSTJ Set GHKS26 = NVL(GHKS26,0)+ls_MZJZJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS26,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_MZJZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_YYZFJE,0) <>0 then --医院支付 Update BM_GHKSTJ Set GHKS27 = NVL(GHKS27,0)+ls_YYZFJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS27,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YYZFJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_DBBCJE,0) <>0 then --大病补充补偿金额 Update BM_GHKSTJ Set GHKS31 = NVL(GHKS31,0)+ls_DBBCJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS31,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_DBBCJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; if NVL(ls_MZBZJE,0) <>0 then --民政补助 Update BM_GHKSTJ Set GHKS32 = NVL(GHKS32,0)+ls_MZBZJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS18=ls_ZYID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS32,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_MZBZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); end if; end if; END LOOP; CLOSE CUR_ZY_YBBRJS_BBTJ00; --GHRQ00：医保名称 GHKS01:brid00 GHKS02:操作员 GHKS10:病区编码 GHKS12:科室编码 GHKS18:住院id GHKS88：总金额 --GHKS03 自付金额 GHKS04 医保个人帐户 GHKS05 医保统筹基金 GHKS06 商保个人帐户 GHKS07 商保统筹基金 --GHKS08 公费记账金额 GHKS09 减免金额 GHKS11 住院天数 GHKS13 保健基金支付 GHKS14 商业保险支付 GHKS15 商保基金支付 --GHKS16 保健基金支付 GHKS19 公务员补助 GHKS21:药品完全自费 GHKS22:项目完全自费 --GHKS23:药品部分自费 GHKS24:项目部分自费 GHKS25:药品费 GHKS26:民政救助,GHKS27:医院支付 GHKS28:全自费金额=GHKS21+GHKS22 --GHKS29:部分自费金额=GHKS23+GHKS24 GHKS30：人次 GHKS31:大病补充补偿金额 GHKS32:民政补助 --汇总数据DYID00=99 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,99,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 group by ad_ID0000,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18; --删除数据 delete BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00<>99; --1:按收费员汇总DYID00=1 GHRQ00：医保名称 GHKS02:操作员 BEIZHU:收费员姓名 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,1,GHRQ00,GHKS02,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00,GHKS02; update BM_GHKSTJ a set a.BEIZHU=(select b.ZWXM00 from BM_YGBM00 b where b.YGBH00=a.GHKS02 and rownum=1) where ID0000 = ad_ID0000 and DYID00=1; select count(1) into ls_count0 from ( select distinct GHKS01 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99); Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,BEIZHU,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,1,'合计 '||'人数:'||to_char(ls_count0),'',999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=1; --2:显示明细信息 DYID00=2 GHRQ00：医保名称 GHKS01:brid00 GHKS18:ZYID00 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS01,GHKS02,GHKS18,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,2,' ',max(GHKS01),max(GHKS02),GHKS18,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHKS18; update BM_GHKSTJ a set a.BEIZHU=(select b.ZWXM00 from BM_YGBM00 b where b.YGBH00=a.GHKS02 and rownum=1) where ID0000 = ad_ID0000 and DYID00=2; update BM_GHKSTJ a set a.GHKS11=(select to_date(NVL(b.CYDJRQ,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(b.RYRQ00,'YYYYMMDD')+1 from ZY_BRXXB0 b where b.zyid00 =a.GHKS18 and rownum=1) where ID0000 = ad_ID0000 and DYID00=2; /*Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS18,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30) select ad_ID0000,2,'合计 '||'人数:'||to_char(ls_count0),999999999,999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=2;*/ --3：按病区汇总 DYID00=3 GHRQ00：医保名称 GHKS10:病区编号 BEIZHU:病区名称 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS10,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,3,GHRQ00,GHKS10,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00,GHKS10; update BM_GHKSTJ a set a.BEIZHU=(select b.BMMC00 from BM_BMBM00 b where b.BMBH00=a.GHKS10 and rownum=1) where ID0000 = ad_ID0000 and DYID00=3; update BM_GHKSTJ a set a.GHKS30=(select count(1) from ( select distinct GHKS01,GHRQ00,GHKS10 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99)aa where aa.GHRQ00=a.GHRQ00 and aa.GHKS10=a.GHKS10) where ID0000 = ad_ID0000 and DYID00=3; Insert into BM_GHKSTJ(ID0000,DYID00,BEIZHU,GHRQ00,GHKS10,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,3,'合计',' ',999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=3; --4：结算汇总表DYID00=4 --5：按医保中心汇总 DYID00=5 GHRQ00：医保名称 if Trim(VYYID00) in ('220007','820007') then --省二合计排在第一行，异地第二 LS_XSXH00:=-4; else LS_XSXH00:=1; end if; Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,5,GHRQ00,0,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00; update BM_GHKSTJ a set a.GHKS30=(select count(1) from ( select distinct GHKS01,GHRQ00 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99)aa where aa.GHRQ00=a.GHRQ00 ) where ID0000 = ad_ID0000 and DYID00=5; Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,5,'合计 ',LS_XSXH00,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=5 ; --GHRQ00：医保名称 GHKS01:brid00 GHKS02:操作员 GHKS10:病区编码 GHKS12:科室编码 GHKS18:住院id GHKS88：总金额 --GHKS03 自付金额 GHKS04 医保个人帐户 GHKS05 医保统筹基金 GHKS06 商保个人帐户 GHKS07 商保统筹基金 --GHKS08 公费记账金额 GHKS09 减免金额 GHKS11 住院天数 GHKS13 保健基金支付 GHKS14 商业保险支付 GHKS15 商保基金支付 --GHKS16 保健基金支付 GHKS19 公务员补助 GHKS21:药品完全自费 GHKS22:项目完全自费 --GHKS23:药品部分自费 GHKS24:项目部分自费 GHKS25:药品费 GHKS26:民政救助,GHKS27:医院支付 GHKS28:全自费金额=GHKS21+GHKS22 --GHKS29:部分自费金额=GHKS23+GHKS24 GHKS30：人次 GHKS31:大病补充补偿金额 GHKS32:民政补助 --ZY_YBJSRBBSFXSCZYB = 'Y' 城镇医保 if LS_YBJSRBBSFXSCZYB='Y' then Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,5,'城镇医保',LS_XSXH00+1,sum(D.HJJE00), sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00, sum(D.ZFJE00), --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10,3)),0)) GRZHZF, --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11,4)),0)) TCJJZF, --sum(SBGRZH) SBGRZH, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10)),0)) SBGRZH, --sum(SBTCJJ) SBTCJJ, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11)),0)) SBTCJJ, sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0, --sum(SYBXZF) SYBXZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(13)),0)) SYBXZF, sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0, --sum(D.GWYBZ0) GWYBZ0, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(16)),0)) GWYBZ0, 0,0,0,0,0, --sum(D.YBYL01), sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL01, sum(d.YBYL05),0,0,COUNT(distinct f.zyid00) RS0000,sum(D.YBYL26) DBBCJE --,sum(D.YBYL18) ,sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) from ic_ybbrlb C,ZY_JZB000 D,BM_YGBM00 E,zy_brxxb0 F,BM_BMBM00 K,BM_BMBM00 G where D.yblb00 = C.yblb00 and D.FBBH00 = C.FBBH00 and D.FBBH00=3 and d.ybbrlb='C' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ( (as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0')) ) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); end if; if (Trim(VYYID00) in ('220007','820007')) and (Trim(as_YBZXMC) = '0') and (as_YBZXMC<>'异地医保') then -- 省二医院显示 异地医保 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,5,'异地医保',LS_XSXH00+2,sum(D.HJJE00),sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00,sum(D.ZFJE00), --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10,3)),0)) GRZHZF, --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11,4)),0)) TCJJZF, --sum(SBGRZH) SBGRZH, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10)),0)) SBGRZH, --sum(SBTCJJ) SBTCJJ, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11)),0)) SBTCJJ, sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0, --sum(SYBXZF) SYBXZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(13)),0)) SYBXZF, sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0, --sum(D.GWYBZ0) GWYBZ0, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(16)),0)) GWYBZ0, 0,0,0,0,0, --sum(D.YBYL01), sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL01, sum(d.YBYL05),0,0,COUNT(distinct f.zyid00) RS0000,sum(D.YBYL26) DBBCJE --,sum(D.YBYL18) ,sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL18 from ic_ybbrlb C,ZY_JZB000 D,BM_YGBM00 E,zy_brxxb0 F,BM_BMBM00 K,BM_BMBM00 G where D.yblb00 = C.yblb00 and D.FBBH00 = C.FBBH00 and D.FBBH00=3 and C.YBZXLB='1' and c.yblb00<>'1' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ( (as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0'))) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); --IC_YBBRLB.YBZXLB=2 市医保小计 by ZYSF-20140816-001 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,5,'市医保小计',LS_XSXH00+3,sum(D.HJJE00),sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00,sum(D.ZFJE00), --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10,3)),0)) GRZHZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11,4)),0)) TCJJZF, --sum(SBGRZH) SBGRZH, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10)),0)) SBGRZH, --sum(SBTCJJ) SBTCJJ, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11)),0)) SBTCJJ, sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0,sum(SYBXZF) SYBXZF,sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0, --sum(D.GWYBZ0) GWYBZ0, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(16)),0)) GWYBZ0, 0,0,0,0,0, --sum(D.YBYL01), sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL01, sum(d.YBYL05),0,0,COUNT(distinct f.zyid00) RS0000,sum(D.YBYL26) DBBCJE --,sum(D.YBYL18) ,sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL18 from ic_ybbrlb C,ZY_JZB000 D,BM_YGBM00 E,zy_brxxb0 F,BM_BMBM00 K,BM_BMBM00 G where D.yblb00 = C.yblb00 and D.FBBH00 = C.FBBH00 and D.FBBH00=3 and C.YBZXLB='2' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ( (as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0'))) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); --IC_YBBRLB.YBZXLB=@ 新农合小计 by ZYSF-20140816-001 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,5,'新农合小计',LS_XSXH00+3,sum(D.HJJE00),sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00,sum(D.ZFJE00), --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, --sum(decode(sign(Instrb(','||Trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10,3)),0)) GRZHZF, sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11,4)),0)) TCJJZF, --sum(SBGRZH) SBGRZH, --sum(SBTCJJ) SBTCJJ, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(10)),0)) SBGRZH, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(11)),0)) SBTCJJ, sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0, --sum(SYBXZF) SYBXZF, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(13)),0)) SYBXZF, sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0, --sum(D.GWYBZ0) GWYBZ0, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(16)),0)) GWYBZ0, 0,0,0,0,0, --sum(D.YBYL01), sum(nvl((select sum(JFJE00) from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL01, sum(d.YBYL05),0,0,COUNT(distinct f.zyid00) RS0000,sum(D.YBYL26) DBBCJE --,sum(D.YBYL18) ,sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and ZFFS00 in(22)),0)) YBYL18 from ic_ybbrlb C,ZY_JZB000 D,BM_YGBM00 E,zy_brxxb0 F,BM_BMBM00 K,BM_BMBM00 G where D.yblb00 = C.yblb00 and D.FBBH00 = C.FBBH00 and D.FBBH00=3 and C.YBZXLB='@' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ( (as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0'))) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); end if; --6：按科室汇总 DYID00=6 GHRQ00：医保名称 GHKS12:科室编号 BEIZHU:科室名称 Insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS12,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,6,GHRQ00,GHKS12,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00,GHKS12; update BM_GHKSTJ a set a.BEIZHU=(select b.BMMC00 from BM_BMBM00 b where b.BMBH00=a.GHKS12 and rownum=1) where ID0000 = ad_ID0000 and DYID00=6; update BM_GHKSTJ a set a.GHKS30=(select count(1) from ( select distinct GHKS01,GHRQ00,GHKS12 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99)aa where aa.GHRQ00=a.GHRQ00 and aa.GHKS12=a.GHKS12) where ID0000 = ad_ID0000 and DYID00=6; Insert into BM_GHKSTJ(ID0000,DYID00,BEIZHU,GHRQ00,GHKS12,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32) select ad_ID0000,6,'合计',' ',999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=6; --commit; /* select count(*) into ls_COUNT0 from BM_GHKSTJ where ID0000 = ad_ID0000; --添加一条总计行 Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26,GHKS27) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',0,0,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19), sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25),sum(GHKS26),sum(GHKS27) from BM_GHKSTJ where ID0000 = ad_ID0000; */ EXCEPTION when OTHERS then --as_YHMSG0:='@'||dbms_utility.format_error_backtrace||'@'||'出错原因不详,请记录此信息并和系统管理员联系！'; as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBBRJS_BB0000('||as_KSRQ00||','||as_JSRQ00||','||as_CZYXM0||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end;