CREATE OR REPLACE PROCEDURE sp_zh_zhrbtj( V_FLAG00 IN NUMBER DEFAULT 0) as C_STADATE CHAR(8); C_ENDDATE CHAR(8); C_NOW000 CHAR(8); --今天 C_NOWSJ0 CHAR(8); C_NOWSJN NUMBER(2); C_TS0000 NUMBER(3); --统计天数 i NUMBER(3); d CHAR(8); Vsbdlyb char(1); --商保支付是否独立于医保支付金额 Vlgsftj char(1); --住院留观是否参与统计床位使用率 Vtemp00 ZH_TJZB00.TJJG00%TYPE; Vtemp01 ZH_TJZB00.TJJG00%TYPE; v_SSLSCYJQ char(1); --手术例数从医技取 Vzxcgbz number; as_YHMSG0 varchar2(200); as_SYSMSG varchar2(200); Vlsrq00 char(8); Vtjjg00 number; Vtjjg01 number; v_YZBXCY char(1); cursor c_zb is select TJBH00,TJRQ00,TJJG00 from ZH_TJZB00 where TJJG00<>0 and TJRQ00=d order by tjrq00,tjbh00; --date revisor comments --2007.09.25 zhr 修改病区人数、请假人数、转科人数，预出院人数算法，与其他报表算法保持一致。 --2007.12.11 zhr 修改入院数、出院数算法，与其他报表算法保持一致。 --2008.05.26 ZHR 删除预挂号，算挂号数时不能包含预挂号。 --2008.08.24 zhr 新增一些新指标 --2008.10.23 zhr 优化请假病人计算语句 --2008.12.24 sechen 如果统计日期为空，取系统启用时间 --2009.03.05 yangy 增加手术例数 --2009.03.29 yangy 增加指标出院患者药品费用 --2009.04.01 zhr 增加指标在院人数和全院总收入 --2009.04.02 zhr 出院者占用床日数指标中，住院天数的指标改为CYRQ00-RYRQ00,不再加1,当天出入院的算一天 --2009.04.24 zhr 有些医院门诊留观按住院处理，因此需要增加急诊病区在院人数(门诊留观人数)指标，计算床位使用率的时候，不参与计算。 --2009.07.14 chenqw 没有手术系统的手术例数从医技表取 --2010.09.23 CSF 删除预挂号时，加条件只删除没有费用的预挂号 --2010.10.25 CSF 报表原来不统计预挂号，如果预挂号有费用，则要统计。 --2011.04.01 zhangwz 增加TJBH00='0037'的'实际床位数'统计数据 by ZHCX-20110401-001. --2011.04.08 CSF 预挂号如果开过处方的话不删除。 --2011.11.01 zhanghr 统计时改RYRQ00为SJRYRQ --2012.02.10 jinfl 统计增加门诊住院中草药药品收入 ZHCX-20120131-001 --2013.06.13 zhanghr 增加0040-0073统计指标 --2013.11.13 qks 增加TJBH00='0087'的'挂账未结清'统计数据 --2016.09.07 qks 增加TJBH00='0088'(门诊西成制剂收入)、TJBH00='0089'(住院西成制剂收入)、TJBH00='0090'(全院药占比); --同时增加参数ZH_YZBXCY控制:药占比 是否改为 =西成药(剔除本院制剂)/总收入(剔除中草药以及本院制剂) --2018.12.08 zhanghr 死亡人数统计直接从ZY_BRXXB0.RCYFH0取值 --2018.12.17 qks TJBH00='0013'，TJBH00='0035':增加BQ_BRLDXX.QSZT00='迁入'; --2019.01.28 qks TJBH00='0013'，TJBH00='0035':增加 and not exists (select 1 from BQ_BRLDXX where jszt00='出院' AND yxbz00='1' and jsrq00<=d and zyid00=a.zyid00) begin delete ZH_TJZB00 where TJRQ00>=CZRQ00; commit; --统计当日 --手术例数从医技取 select substrb(nvl(trim(upper(max(Value0))),'N'),1,1) into v_SSLSCYJQ from XT_XTCS00 where name00='ZH_SSLSCYJQ'; --药占比 是否都改为西成药(剔除本院制剂)/总收入(剔除中草药以及本院制剂) select substrb(nvl(trim(upper(max(Value0))),'N'),1,1) into V_YZBXCY from XT_XTCS00 where name00='ZH_YZBXCY'; if V_FLAG00=1 then C_STADATE:=to_char(sysdate,'YYYYMMDD'); C_ENDDATE:=C_STADATE; --统计历史 else select nvl(max(TJRQ00),'20010101') into C_STADATE from ZH_TJZB00; if C_STADATE>=TO_CHAR(SYSDATE-1,'YYYYMMDD') then return; end if; --2008.12.24 sechen 如果统计日期为空，取系统启用时间 if C_STADATE='20010101' then select min(TJRQ00) into C_STADATE from ( select nvl(min(RYRQ00),to_char(sysdate,'YYYYMMDD'))TJRQ00 from ZY_BRXXB0 union all select nvl(min(GHRQ00),to_char(sysdate,'YYYYMMDD'))TJRQ00 from SF_BRXXB0); end if; C_STADATE:=to_char(to_date(C_STADATE,'YYYYMMDD')+1,'YYYYMMDD'); select TO_CHAR(SYSDATE-1,'YYYYMMDD') into C_ENDDATE from dual; end if; C_NOWSJN:=SF_BQ_BRLDXX; commit; select substrb(nvl(trim(upper(max(Value0))),'Y'),1,1) into Vsbdlyb from XT_XTCS00 where name00='ZY_GRZFSFBHSB'; --留观是否统计 select substrb(nvl(trim(upper(max(Value0))),'Y'),1,1) into Vlgsftj from XT_XTCS00 where name00='ZH_ZYLGSFTJ'; select TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS'),TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) into C_NOW000,C_NOWSJ0,C_NOWSJN from dual; DELETE FROM SF_BRXXB0 a WHERE GHRQ00>=TO_CHAR(SYSDATE-5,'YYYYMMDD') AND GHRQ00<=TO_CHAR(SYSDATE-1,'YYYYMMDD') AND GHLB00=-99999 and not exists(select 1 from SF_FYMX00 where MZH000=a.GHH000) and not exists(select 1 from YF_MZCF00 where GHID00=a.GHID00 and CFZT00<>'3'); DELETE FROM SF_BRXXB0 a WHERE GHRQ00=TO_CHAR(SYSDATE,'YYYYMMDD') AND GHLB00=-99999 AND TO_CHAR(SYSDATE,'HH24')>='21' and not exists(select 1 from SF_FYMX00 where GHH000=a.GHH000) and not exists (select 1 from YF_MZCF00 where GHID00=a.GHID00 and CFZT00<>'3'); update YF_MZCF00 set YPDLBH='1' where YPDLBH='0' and exists (select 1 from YF_MZCFMX where CFLSH0=YF_MZCF00.CFLSH0 and YPDLBH='1') and SRRQ00>=C_STADATE and SRRQ00<=C_ENDDATE; --初始化所有数据 d:=C_STADATE; WHILE d<=C_ENDDATE loop --for i in 1..87 loop for i in 1..90 loop --2016.09.07 insert into ZH_TJZB00(TJRQ00,TJBH00,TJJG00,TJMC00,CZRQ00,CZSJ00) select d,trim(substr(to_char(i+10000),2,4)),0,null,C_NOW000,C_NOWSJ0 from dual; end loop; insert into ZH_TJZB00(TJRQ00,TJBH00,TJJG00,TJMC00,CZRQ00,CZSJ00) select d,'1014',0,null,C_NOW000,C_NOWSJ0 from dual; insert into ZH_TJZB00(TJRQ00,TJBH00,TJJG00,TJMC00,CZRQ00,CZSJ00) select d,'1019',0,null,C_NOW000,C_NOWSJ0 from dual; --门诊部分 update ZH_TJZB00 set TJMC00='门急诊人数',TJJG00= (select count(GHID00) from SF_BRXXB0 a where GHRQ00=d and ghlb00<>1004) where TJRQ00=d and TJBH00='0001'; update ZH_TJZB00 set TJJG00= (select nvl(ZH_TJZB00.TJJG00,0)-count(GHID00) from SF_BRXXB0 a where THRQ00=d and ghlb00<>1004) where TJRQ00=d AND TJBH00='0001'; update ZH_TJZB00 set TJMC00='急诊人数',TJJG00= (select count(GHID00) from SF_BRXXB0 a where GHRQ00=d and GHLB00 in (select LBBH00 from BM_GHLBB0 where LBMC00 like '%急诊%' ) and ghlb00<>1004) where TJRQ00=d AND TJBH00='0002'; update ZH_TJZB00 set TJJG00= (select nvl(ZH_TJZB00.TJJG00,0)-count(GHID00) from SF_BRXXB0 a where THRQ00=d and GHLB00 in (select LBBH00 from BM_GHLBB0 where LBMC00 like '%急诊%' ) and ghlb00<>1004) where TJRQ00=d AND TJBH00='0002'; --门诊处方-数量、最大金额、最小金额、总金额 update ZH_TJZB00 set TJMC00='门诊处方数',TJJG00= (select nvl(sum(sign(CFZJE0)),0) from YF_MZCF00 where SRRQ00=d) where TJRQ00=d AND TJBH00='0003'; --update ZH_TJZB00 set TJJG00= -- (select nvl(ZH_TJZB00.TJJG00,0)-count(distinct BCCFH0) from YF_MZCF00 where SRRQ00=d and BCCFH0 is not null) -- where TJRQ00=d AND TJBH00='0003'; update ZH_TJZB00 set TJMC00='处方总金额',TJJG00= (select nvl(sum(CFZJE0),0) from YF_MZCF00 where SRRQ00=d) where TJRQ00=d AND TJBH00='0004'; update ZH_TJZB00 set TJMC00='最大处方金额',TJJG00= (select max(CFZJE0) from YF_MZCF00 where SRRQ00=d and CFZJE0>0) where TJRQ00=d AND TJBH00='0005'; update ZH_TJZB00 set TJMC00='最小处方金额',TJJG00= (select min(CFZJE0) from YF_MZCF00 where SRRQ00=d and CFZJE0>0) where TJRQ00=d AND TJBH00='0006'; --zhr 20080916 新增门诊指标 update ZH_TJZB00 set TJMC00='门诊中药处方数',TJJG00= (select nvl(sum(sign(CFZJE0)),0) from YF_MZCF00 where SRRQ00=d and YPDLBH='2') where TJRQ00=d AND TJBH00='0020'; update ZH_TJZB00 set TJMC00='门诊中药处方金额',TJJG00= (select nvl(sum(CFZJE0),0) from YF_MZCF00 where SRRQ00=d and YPDLBH='2') where TJRQ00=d AND TJBH00='0021'; -- update ZH_TJZB00 set TJMC00='门诊成药处方数',TJJG00= (select nvl(sum(sign(CFZJE0)),0) from YF_MZCF00 where SRRQ00=d and YPDLBH='1') where TJRQ00=d AND TJBH00='0022'; update ZH_TJZB00 set TJMC00='门诊中药处方金额',TJJG00= (select nvl(sum(CFZJE0),0) from YF_MZCF00 where SRRQ00=d and YPDLBH='1') where TJRQ00=d AND TJBH00='0023'; update ZH_TJZB00 set TJMC00='门诊医保支付金额',TJJG00= (select nvl(sum(GRZHZF+TCJJZF+decode(Vsbdlyb,'Y',SBTCJJ+SBGRZH,0)),0) from SF_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0024'; update ZH_TJZB00 set TJMC00='门诊医保挂号数',TJJG00= (select count(GHID00) from SF_BRXXB0 a where GHRQ00=d and FBBH00=3 and ghlb00<>1004) where TJRQ00=d AND TJBH00='0025'; update ZH_TJZB00 set TJJG00= (select nvl(ZH_TJZB00.TJJG00,0)-count(GHID00) from SF_BRXXB0 a where THRQ00=d and FBBH00=3 and ghlb00<>1004) where TJRQ00=d AND TJBH00='0025'; update ZH_TJZB00 a set TJMC00='门诊参保比例',TJJG00=(select round(sum(decode(TJBH00,'0025',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0001',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=d and b.TJBH00 in('0025','0001')) where TJRQ00=d AND TJBH00='0046'; --住院部分 --2007.12.11 zhr 入院人数改变算法，改为同病区算法一致 update ZH_TJZB00 set TJMC00='入院人数',TJJG00= (SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE qszt00 in ('新入') and yxbz00='1' and qsrq00=d and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where TJRQ00=d AND TJBH00='0007'; --原算法update ZH_TJZB00 set TJMC00='入院人数',TJJG00= -- (select count(ZYID00) from ZY_BRXXB0 where RYRQ00=d and substr(BRZT00,1,1) in ('1','2','3','4','5')) -- where TJRQ00=d AND TJBH00='0007'; --2007.12.11 zhr 入院人数改变算法，改为同病区算法一致 update ZH_TJZB00 set TJMC00='出院人数',TJJG00= (SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE JSZT00='出院' AND yxbz00='1' and jsrq00=d and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) where TJRQ00=d AND TJBH00='0008'; --原算法update ZH_TJZB00 set TJMC00='出院人数',TJJG00= -- (select count(ZYID00) from ZY_BRXXB0 where CYRQ00=d and substr(BRZT00,1,1) in ('3','4','5')) -- where TJRQ00=d AND TJBH00='0008'; update ZH_TJZB00 set TJMC00='住院人数',TJJG00= (select count(ZYID00) from ZY_BRXXB0 where SJRYRQ<=d and substr(BRZT00,1,1) in ('1','2','3','4','5') and nvl(SJCYRQ,to_char(SYSDATE+1,'YYYYMMDD'))>d) where TJRQ00=d AND TJBH00='0009'; --包含出院登记未结算的病人 update ZH_TJZB00 set TJMC00='转科人数',TJJG00= (select count(distinct ZYID00) from BQ_BRLDXX where JSRQ00=d and JSZT00='转出' and YXBZ00='1') where TJRQ00=d AND TJBH00='0010'; --2007.09.25 zhr 改变算法,病人出院后可能又被收回到病区，这种记录算预出院 update ZH_TJZB00 set TJMC00='预出院人数',TJJG00= (select count(ZYID00) from ZY_BRXXB0 where CYDJRQ <= d and substr(BRZT00,1,1) in ('2','3','4','5') and nvl(CYRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>d) where TJRQ00=d AND TJBH00='0011'; --2007.09.25 zhr 改变算法,当天请假又进来的不算请假 select count(distinct ZYID00) into Vtemp00 from BQ_BRLDXX a where YXBZ00='1' and JSRQ00<=d and JSZT00='请假' AND ( EXISTS (SELECT 1 FROM BQ_BRLDXX WHERE ZYID00=A.ZYID00 AND QSRQ00>d AND YXBZ00='1' AND QSZT00='续住') OR NOT EXISTS (SELECT 1 FROM BQ_BRLDXX WHERE ZYID00=A.ZYID00 AND QSRQ00>=A.JSRQ00 AND QSRQ00||QSSJ00>A.JSRQ00||A.JSSJ00)); update ZH_TJZB00 set TJMC00='请假人数',TJJG00=Vtemp00 --原算法 (select count(distinct ZYID00) from BQ_BRLDXX a where JSRQ00<=d and JSZT00='请假' and JSRQ00||JSSJ00= -- (select max(nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))||nvl(JSSJ00,to_char(SYSDATE,'HH24:MI:SS'))) from BQ_BRLDXX b where a.ZYID00=b.ZYID00)) where TJRQ00=d AND TJBH00='0012'; --2007.09.25 zhr 改为和病区的人数算法一致，但剔除请假病人。 update ZH_TJZB00 set TJMC00='病区人数',TJJG00= (select count( distinct ZYID00) from BQ_BRLDXX a where QSZT00 in ('新入','转入','迁入') and YXBZ00='1' and QSRQ00<=d and BQJSRQ > d and not exists (select 1 from BQ_BRLDXX where jszt00='出院' AND yxbz00='1' and jsrq00<=d and zyid00=a.zyid00) ) --原算法 (select count(distinct ZYID00) from BQ_BRLDXX where QSRQ00<=d and nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>d and ZYID00 in -- (select ZYID00 from ZY_BRXXB0 where RYRQ00 <= d and substr(BRZT00,1,1) in ('1','2','3','4','5') and nvl(CYRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>d)) where TJRQ00=d AND TJBH00='0013'; --剔除请假病人 update ZH_TJZB00 a set TJJG00=(select nvl(a.TJJG00,0)-nvl(TJJG00,0) from ZH_TJZB00 where TJRQ00=d and TJBH00='0012') where TJRQ00=d AND TJBH00='0013'; --2009.04.24 zhr 有些医院门诊留观按住院处理，因此需要增加急诊病区在院人数(门诊留观人数)指标，计算床位使用率的时候，不参与计算。 update ZH_TJZB00 set TJMC00='住院留观人数',TJJG00= (select count( distinct ZYID00) from BQ_BRLDXX a where QSZT00 in ('新入','转入','迁入') and YXBZ00='1' and QSRQ00<=d and BQJSRQ > d and BQH000 in (select BMBH00 from BM_BMBM00 where BMMC00 like '急%' and BMXZ00='9') and not exists (select 1 from BQ_BRLDXX where jszt00='出院' AND yxbz00='1' and jsrq00<=d and zyid00=a.zyid00)) where TJRQ00=d AND TJBH00='0035'; --2009.04.25 zhr 门诊留观的病人(按门诊处理) update ZH_TJZB00 set TJMC00='门诊留观人数'--,TJJG00=0 where TJRQ00=d AND TJBH00='0036'; --床位情况 update ZH_TJZB00 set TJMC00='额定床位数',TJJG00= (select nvl(sum(SYCW00),0) from BM_BMBM00 where BMXZ00='9' and FLAG00='1' and CZBZ00<>'2' and (BMMC00 not like '急%' or Vlgsftj='Y')) where TJRQ00=d AND TJBH00='0014'; update ZH_TJZB00 set TJMC00='包床数',TJJG00= (select count(CH0000) from BQ_BRLDXX where QSZT00='包床' and nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>d and QSRQ00<=d) where TJRQ00=d AND TJBH00='0015'; --zhr 20080916 新增住院指标 update ZH_TJZB00 set TJMC00='开放床位数',TJJG00= (select nvl(sum(KFCW00),0) from BM_BMBM00 where BMXZ00='9' and FLAG00='1' and CZBZ00<>'2' and (BMMC00 not like '急%' or Vlgsftj='Y')) where TJRQ00=d AND TJBH00='0026'; update ZH_TJZB00 set TJMC00='额定床位数',TJJG00= (select nvl(sum(SYCW00),0) from BM_BMBM00 where BMXZ00='9' and FLAG00='1' and CZBZ00<>'2' and (BMMC00 not like '急%' or Vlgsftj='Y')) where TJRQ00=d AND TJBH00='0037'; update ZH_TJZB00 set TJMC00='出院者占用床日数',TJJG00= (SELECT nvl(sum(greatest(to_date(SJCYRQ,'YYYYMMDD')-to_date(SJRYRQ,'YYYYMMDD'),1)),0) FROM ZY_BRXXB0 b WHERE ZYID00 in (select ZYID00 from BQ_BRLDXX a where JSZT00='出院' AND YXBZ00='1' and JSRQ00=d) and upper(substr(b.zyh000,1,1)) not in ('F','M')) where TJRQ00=d AND TJBH00='0027'; if d=to_char(sysdate,'yyyymmdd') then select nvl(sum(HJJE00),0),nvl(sum(decode(b.SFLB00,'3',b.HJJE00,0)),0) into Vtemp00,Vtemp01 from ZY_FYMX00 b where ZYID00 in (select ZYID00 from BQ_BRLDXX a where JSZT00='出院' AND YXBZ00='1' and JSRQ00=d) and upper(substr(b.ZYH000,1,1)) not in ('F','M') and CZRQ00<=d; else select nvl(sum(HJJE00),0),nvl(sum(XYF000+ZYF000+CYF000),0) into Vtemp00,Vtemp01 from ZY_FYMXZ0 b where JZLX00='交费消费' and RQ0000<=d and ZYID00 in (select ZYID00 from ZY_BRXXB0 c where SJCYRQ=d and upper(substr(ZYH000,1,1)) not in ('F','M') and BRZT00<>'9'); end if; --出院后再发生费用 select nvl(sum(HJJE00),0)+Vtemp00,nvl(sum(XYF000+ZYF000+CYF000),0)+Vtemp01 into Vtemp00,Vtemp01 from ZY_FYMXZ0 b where JZLX00='交费消费' and ZYID00>0 and RQ0000=d and exists(select 1 from ZY_BRXXB0 where ZYID00=b.ZYID00 and SJCYRQ'9'); --出院患者费用 update ZH_TJZB00 a set TJMC00='出院患者费用',TJJG00=Vtemp00 where TJRQ00=d AND TJBH00='0043'; --出院患者药品费用 2009.03.29 yangy -- select nvl(sum(xmje00),0) into Vtemp00 -- from ZY_JZMX00 j,ZY_JZB000 z where j.jzdh00=z.jzdh00 and z.zyid00 in (SELECT A.ZYID00 FROM BQ_BRLDXX a,zy_brxxb0 b -- WHERE JSZT00='出院' AND yxbz00='1' and jsrq00=d and a.zyid00=b.zyid00 -- and (upper(substr(b.zyh000,1,1)) not in ('F','M'))) and j.xmmc00 like '%药费'; update ZH_TJZB00 set TJMC00='出院患者药品费用', TJJG00=Vtemp01 where TJRQ00=d AND TJBH00='0032'; update ZH_TJZB00 a set TJMC00='住院患者人均费用',TJJG00=(select round(sum(decode(TJBH00,'0043',TJJG00,0))/sum(decode(TJBH00,'0008',decode(TJJG00,0,1,TJJG00),0)),1) from ZH_TJZB00 b where b.TJRQ00=d and b.TJBH00 in('0008','0043')) where TJRQ00=d AND TJBH00='0028'; select nvl(sum(JFJE00),0) into Vtemp00 from ZY_BRJFB0 b where JFBZ00<>'2' and JZDH00=0 and JFRQ00<=d and (PLBH00='8' or ((PLBH00='3' and ZFFS00 in ('1','2','8','24','25') and JSLX00='3'))); update ZH_TJZB00 a set TJMC00='住院预收款',TJJG00=(SELECT Vtemp00+nvl(sum(JFJE00),0) from ZY_BRJFB0 a,ZY_JZB000 b where a.JZDH00=b.JZDH00 and JZRQ00>d and JFRQ00<=d and JFBZ00<>'2' and (PLBH00='8' or (PLBH00='3' and ZFFS00 in ('1','2','8','24','25') and a.JSLX00='3'))) where TJRQ00=d AND TJBH00='0029'; select nvl(sum(b.HJJE00*a.BZ0000),0) into Vtemp00 from ZY_JZMXXM a,ZY_FYMX00 b,ZY_JZB000 c where a.MXID00=b.MXID00 and a.JZDH00=c.JZDH00 and c.JZRQ00>d and b.czrq00<=d; update ZH_TJZB00 a set TJMC00='未结费用',TJJG00=(select Vtemp00+nvl(sum(C.HJJE00),0) from ZY_FYMX00 C where C.CZRQ00<=d AND C.JZDH00=0) where TJRQ00=d AND TJBH00='0030'; --收入部分 update ZH_TJZB00 set TJMC00='门诊总收入',TJJG00= (select nvl(sum(HJJE00),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0016'; update ZH_TJZB00 set TJMC00='门诊药品收入',TJJG00= (select nvl(sum(HJJE00),0) from SF_FYMX00 where CZRQ00=d and SFLB00='3') where TJRQ00=d AND TJBH00='0017'; update ZH_TJZB00 set TJMC00='住院总收入',TJJG00= (select nvl(sum(HJJE00),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0018'; update ZH_TJZB00 set TJMC00='住院药品收入',TJJG00= (select nvl(sum(HJJE00),0) from ZY_FYMX00 where CZRQ00=d and SFLB00='3') where TJRQ00=d AND TJBH00='0019'; update ZH_TJZB00 set TJMC00='门诊中草药药品收入',TJJG00= (select nvl(sum(HJJE00),0) from SF_FYMX00 where CZRQ00=d and SFLB00='3' and MZFPID=3) where TJRQ00=d AND TJBH00='0038'; update ZH_TJZB00 set TJMC00='住院中草药药品收入',TJJG00= (select nvl(sum(HJJE00),0) from ZY_FYMX00 where CZRQ00=d and SFLB00='3' and ZYFPID=3) where TJRQ00=d AND TJBH00='0039'; update ZH_TJZB00 a set TJMC00='门诊西成制剂收入',TJJG00=( select nvl(sum(HJJE00),0) from SF_FYMX00 b where CZRQ00=d and SFLB00='3' and MZFPID<>3 and exists (select 1 from bm_yd0000 where ypnm00=b.xmbh00 and zjbz00='1')) where TJRQ00=d AND TJBH00='0088'; update ZH_TJZB00 a set TJMC00='住院西成制剂收入',TJJG00=( select nvl(sum(HJJE00),0) from ZY_FYMX00 b where CZRQ00=d and SFLB00='3' and ZYFPID<>3 and exists (select 1 from bm_yd0000 where ypnm00=b.xmbh00 and zjbz00='1')) where TJRQ00=d AND TJBH00='0089'; --手术部分 2009.01.19 sechen 新增 if v_SSLSCYJQ <> 'Y' then update ZH_TJZB00 set TJMC00='全院手术例数', TJJG00=(select count(1) from vw_ss_yw0000 where yyssrq=d and sszt00 in ('2','4')) where TJRQ00=d AND TJBH00='0031'; update ZH_TJZB00 set TJMC00='门诊手术例数', TJJG00=(select count(1) from vw_ss_yw0000 where yyssrq=d and sszt00 in ('2','4') and mzzybz='0') where TJRQ00=d AND TJBH00='0040'; update ZH_TJZB00 set TJMC00='住院手术例数', TJJG00=(select count(1) from vw_ss_yw0000 where yyssrq=d and sszt00 in ('2','4') and mzzybz='1') where TJRQ00=d AND TJBH00='0041'; else update ZH_TJZB00 set TJMC00='全院手术例数',TJJG00=(select count(*) from (select count(brid00) from yj_yw0000 A where kdrq00= d and sfdjh0>0 and exists (select 1 from vw_bm_zlzd00 B where lbbh00='6' and b.ZLXMID = a.zlxmid ) group by brid00 having sum(sl0000)>0 ) ) where TJRQ00= d and TJBH00='0031'; update ZH_TJZB00 set TJMC00='门诊手术例数',TJJG00=(select count(*) from (select count(brid00) from yj_yw0000 A where kdrq00= d and sfdjh0>0 and exists (select 1 from vw_bm_zlzd00 B where lbbh00='6' and b.ZLXMID = a.zlxmid and mzzybz='0') group by brid00 having sum(sl0000)>0 ) ) where TJRQ00= d and TJBH00='0040'; update ZH_TJZB00 set TJMC00='住院手术例数',TJJG00=(select count(*) from (select count(brid00) from yj_yw0000 A where kdrq00= d and sfdjh0>0 and exists (select 1 from vw_bm_zlzd00 B where lbbh00='6' and b.ZLXMID = a.zlxmid and mzzybz='1') group by brid00 having sum(sl0000)>0 ) ) where TJRQ00= d and TJBH00='0041'; end if; --在院人数 update ZH_TJZB00 set TJMC00='在院人数', TJJG00=(select sum(tjjg00) from ZH_TJZB00 where TJRQ00=d AND TJBH00 in ('0012','0013')) where TJRQ00=d AND TJBH00='0033'; --总收入 update ZH_TJZB00 set TJMC00='全院总收入', TJJG00=(select sum(tjjg00) from ZH_TJZB00 where TJRQ00=d AND TJBH00 in ('0016','0018')) where TJRQ00=d AND TJBH00='0034'; --出院平均住院日 update ZH_TJZB00 a set TJMC00='出院平均住院日',TJJG00=(select round(sum(decode(TJBH00,'0027',TJJG00,0))/sum(decode(TJBH00,'0008',decode(TJJG00,0,1,TJJG00),0)),1) from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0027','0008')) where TJRQ00=d AND TJBH00='0042'; update ZH_TJZB00 a set TJMC00='门诊中医参与数',TJJG00=(select count(distinct mzh000) from SF_FYMX00 where CZRQ00=d and ( (SFLB00 = 3 and exists (select 1 from BM_YD0000 where YPNM00=XMBH00 and LBBH00 in ('1','2') )) or(SFLB00 in ('0','1') and (GJBM00 like '34%' or GJBM00 like '4%')) ) ) where TJRQ00=d AND TJBH00='0044'; update ZH_TJZB00 a set TJMC00='门诊中医参与率',TJJG00=(select round(sum(decode(TJBH00,'0044',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0001',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0044','0001')) where TJRQ00=d AND TJBH00='0045'; update ZH_TJZB00 a set TJMC00='门诊抗菌药物使用人次',TJJG00=(select count(distinct ghid00) from VW_YK_KSSJB0_MZ where FYRQ00=d) where TJRQ00=d AND TJBH00='0047'; update ZH_TJZB00 a set TJMC00='门诊患者使用抗菌药物百分率',TJJG00=(select round(sum(decode(TJBH00,'0047',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0001',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=d and b.TJBH00 in('0047','0001')) where TJRQ00=d AND TJBH00='0048'; update ZH_TJZB00 a set TJMC00='门诊抗菌药物使用量(DDD)',TJJG00=(select sum(round(YPZSL0*ZHL000/DDD000,2)) from VW_YK_KSSJB0_MZ b where b.FYRQ00=d ) where TJRQ00=d AND TJBH00='0049'; update ZH_TJZB00 set TJMC00='门诊自费金额',TJJG00=(select nvl(sum(ZFJE00),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0050'; update ZH_TJZB00 set TJMC00='门诊记账金额',TJJG00=(select nvl(sum(JZJE00),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0051'; update ZH_TJZB00 set TJMC00='门诊减免金额',TJJG00=(select nvl(sum(decode(JMBZ00,1,GFJE00,0)),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0052'; update ZH_TJZB00 set TJMC00='门诊优惠金额',TJJG00=(select nvl(sum(decode(JMBZ00,1,0,GFJE00)),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0053'; update ZH_TJZB00 set TJMC00='门诊诊疗收入',TJJG00=(select nvl(sum(decode(SFLB00,0,HJJE00,1,HJJE00,0)),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0054'; update ZH_TJZB00 set TJMC00='门诊材料收入',TJJG00=(select nvl(sum(decode(SFLB00,2,HJJE00,0)),0) from SF_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0055'; update ZH_TJZB00 a set TJMC00='门诊次均费用',TJJG00=(select round(sum(decode(TJBH00,'0016',TJJG00,0))/sum(decode(TJBH00,'0001',decode(TJJG00,0,1,TJJG00),0)),1) from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0016','0001')) where TJRQ00=d AND TJBH00='0056'; update ZH_TJZB00 a set TJMC00='门诊预交金余额',TJJG00=(select BQJC00 from SF_YJJYE0 where BRID00=0 and CZRQ00=d) where TJRQ00=d AND TJBH00='0057'; if V_YZBXCY = 'Y' then update ZH_TJZB00 a set TJMC00='门诊药品比例',TJJG00=(select round(sum(decode(TJBH00,'0017',TJJG00,'0038',-TJJG00,'0088',-TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0016',TJJG00,'0038',-TJJG00,'0088',-TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0016','0017','0038','0088')) where TJRQ00=d AND TJBH00='0058'; else update ZH_TJZB00 a set TJMC00='门诊药品比例',TJJG00=(select round(sum(decode(TJBH00,'0017',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0016',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0016','0017')) where TJRQ00=d AND TJBH00='0058'; end if; update ZH_TJZB00 a set TJMC00='门诊诊疗比例',TJJG00=(select round(sum(decode(TJBH00,'0054',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0016',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0016','0054')) where TJRQ00=d AND TJBH00='0059'; update ZH_TJZB00 set TJMC00='住院自付金额',TJJG00=(select nvl(sum(ZFJE00),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0060'; update ZH_TJZB00 set TJMC00='住院记账金额',TJJG00=(select nvl(sum(JZJE00),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0061'; update ZH_TJZB00 set TJMC00='住院减免金额',TJJG00=(select nvl(sum(decode(JMBZ00,1,GFJE00,0)),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0062'; update ZH_TJZB00 set TJMC00='住院优惠金额',TJJG00=(select nvl(sum(decode(JMBZ00,1,0,GFJE00)),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0063'; update ZH_TJZB00 set TJMC00='住院诊疗收入',TJJG00=(select nvl(sum(decode(SFLB00,0,HJJE00,1,HJJE00,0)),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0064'; update ZH_TJZB00 set TJMC00='住院材料收入',TJJG00=(select nvl(sum(decode(SFLB00,2,HJJE00,0)),0) from ZY_FYMX00 where CZRQ00=d) where TJRQ00=d AND TJBH00='0065'; update ZH_TJZB00 set TJMC00='住院结算总收入',TJJG00=(select nvl(sum(HJJE00),0) from ZY_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0066'; update ZH_TJZB00 set TJMC00='住院结算自付收入',TJJG00=(select nvl(sum(ZFJE00),0) from ZY_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0067'; update ZH_TJZB00 set TJMC00='住院结算记账收入',TJJG00=(select nvl(sum(JZJE00),0) from ZY_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0068'; update ZH_TJZB00 set TJMC00='住院结算减免收入',TJJG00=(select nvl(sum(GFJE00),0) from ZY_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0069'; update ZH_TJZB00 set TJMC00='住院结算优惠收入',TJJG00=(select nvl(sum(JMJE00),0) from ZY_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0070'; update ZH_TJZB00 set TJMC00='住院结算药品收入',TJJG00=(select nvl(sum(decode(SFLB00,3,b.BZ0000*c.HJJE00,0)),0) from ZY_JZB000 a,ZY_JZMXXM b,ZY_FYMX00 c where a.JZDH00=b.JZDH00 and b.MXID00=c.MXID00 and JZRQ00=d) where TJRQ00=d AND TJBH00='0071'; update ZH_TJZB00 set TJMC00='住院结算诊疗收入',TJJG00=(select nvl(sum(decode(SFLB00,0,b.BZ0000*c.HJJE00,1,b.BZ0000*c.HJJE00,0)),0) from ZY_JZB000 a,ZY_JZMXXM b,ZY_FYMX00 c where a.JZDH00=b.JZDH00 and b.MXID00=c.MXID00 and JZRQ00=d) where TJRQ00=d AND TJBH00='0072'; update ZH_TJZB00 set TJMC00='住院结算材料收入',TJJG00=(select nvl(sum(decode(SFLB00,2,b.BZ0000*c.HJJE00,0)),0) from ZY_JZB000 a,ZY_JZMXXM b,ZY_FYMX00 c where a.JZDH00=b.JZDH00 and b.MXID00=c.MXID00 and JZRQ00=d) where TJRQ00=d AND TJBH00='0073'; update ZH_TJZB00 set TJMC00='住院结算医保支付',TJJG00=(select nvl(sum(GRZHZF+TCJJZF+YBQTZF),0) from ZY_JZB000 where JZRQ00=d) where TJRQ00=d AND TJBH00='0074'; update ZH_TJZB00 set TJMC00='住院病重',TJJG00=(select count(distinct ZYID00) from BQ_YJYZ00 where QYRQ00<=d and dd and FBBH00=3) where TJRQ00=d AND TJBH00='0082'; update ZH_TJZB00 a set TJMC00='住院参保比例',TJJG00=(select round(sum(decode(TJBH00,'0082',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0033',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0033','0082')) where TJRQ00=d AND TJBH00='0081'; update ZH_TJZB00 a set TJMC00='住院日均费用',TJJG00=(select round(sum(decode(TJBH00,'0018',TJJG00,0))/greatest(nvl(sum(decode(TJBH00,'0033',TJJG00,0)),0),1),1) from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0018','0033')) where TJRQ00=d AND TJBH00='1014'; update ZH_TJZB00 a set TJMC00='病床使用率',TJJG00=(select round(sum(decode(TJBH00,'0033',TJJG00,0))/sum(decode(TJBH00,'0026',decode(TJJG00,0,1,TJJG00),0)),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0033','0026')) where TJRQ00=d AND TJBH00='1019'; update ZH_TJZB00 a set TJMC00='住院抗菌药物使用人次',TJJG00=(select count(*) from ZY_BRXXB0 a where SJCYRQ=d and BRZT00 not in ('9','1') and exists (select 1 from VW_YK_KSSJB0_ZY where ZYID00=a.ZYID00 and rownum=1)) where TJRQ00=d AND TJBH00='0083'; update ZH_TJZB00 a set TJMC00='住院患者使用抗菌药物百分率',TJJG00=(select round(sum(decode(TJBH00,'0083',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0008',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=d and b.TJBH00 in('0083','0008')) where TJRQ00=d AND TJBH00='0084'; update ZH_TJZB00 a set TJMC00='住院抗菌药物使用量(DDD)',TJJG00=(select nvl(sum(round(YPZSL0*ZHL000/DDD000,2)),0) DDD000 from VW_YK_KSSJB0_ZY where FYRQ00=d) where TJRQ00=d AND TJBH00='0085'; update ZH_TJZB00 a set TJMC00='抗菌药物使用强度',TJJG00=(select round(sum(decode(TJBH00,'0084',TJJG00,0))/greatest(nvl(sum(decode(TJBH00,'0027',TJJG00,0)),0),1)*100,1) from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0084','0027')) where TJRQ00=d AND TJBH00='0086'; update ZH_TJZB00 a set TJMC00='挂账未结清',TJJG00=(select nvl(sum(XJYE00),0) from zy_jzb000 where jzrq00=d and JSLX00='2') where TJRQ00=d AND TJBH00='0087'; if V_YZBXCY = 'Y' then update ZH_TJZB00 a set TJMC00='全院药占比',TJJG00=(select round(sum(decode(TJBH00,'0019',TJJG00,'0017',TJJG00,'0038',-TJJG00,'0039',-TJJG00,'0088',-TJJG00,'0089',-TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0034',TJJG00,'0038',-TJJG00,'0039',-TJJG00,'0088',-TJJG00,'0089',-TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0017','0019','0038','0039','0088','0089','0034')) where TJRQ00=d AND TJBH00='0090'; else update ZH_TJZB00 a set TJMC00='全院药占比',TJJG00=(select round(sum(decode(TJBH00,'0017',TJJG00,'0019',TJJG00,0))/SF_XT_DIVIDE(sum(decode(TJBH00,'0034',TJJG00,0))),3)*100 from ZH_TJZB00 b where b.TJRQ00=a.TJRQ00 and b.TJBH00 in ('0017','0019','0034')) where TJRQ00=d AND TJBH00='0090'; end if; --历史新高新低 for zb in c_zb loop select nvl(min(TJJG00),0),nvl(max(TJJG00),0) into Vtjjg00,Vtjjg01 from ZH_TJZB00 where TJRQ000; if zb.TJJG00>Vtjjg01 and Vtjjg01<>0 then select TJRQ00 into Vlsrq00 from ZH_TJZB00 where TJBH00=zb.TJBH00 and TJJG00=Vtjjg01 and rownum=1; update ZH_TJZB00 set LSXGBZ='1',LSXGZ0=Vtjjg01,LSXGBL=round((zb.TJJG00-Vtjjg01)/Vtjjg01,4)*100,LSXGRQ=Vlsrq00 where TJBH00=zb.TJBH00 and TJRQ00=zb.TJRQ00; elsif zb.TJJG000 then select TJRQ00 into Vlsrq00 from ZH_TJZB00 where TJBH00=zb.TJBH00 and TJJG00=Vtjjg00 and rownum=1; update ZH_TJZB00 set LSXGBZ='2',LSXGZ0=Vtjjg00,LSXGBL=round((Vtjjg00-zb.TJJG00)/Vtjjg00,4)*100,LSXGRQ=Vlsrq00 where TJBH00=zb.TJBH00 and TJRQ00=zb.TJRQ00; end if; end loop; --日报短信 SP_YJ_DXTZ00(to_number(d),'HIS日报','',0,0,'N',Vzxcgbz,as_YHMSG0,as_SYSMSG); d:=to_char(to_date(d,'YYYYMMDD')+1,'YYYYMMDD'); commit; end loop; exception when no_data_found then rollback; raise_application_error(-20002,'未找到统计结果，请管理员检查统计任务是否正常运行! SP_ZH_ZHRBTJ*'||SQLCODE||SQLERRM); when others then rollback; raise_application_error(-20002,SQLCODE||SQLERRM); end;