PROMPT 101. 住院床位使用情况日报表 create or replace procedure SP_ZH_CWRBB0 ( Ptjrq00 in varchar2, --输入：统计日期(截止) Pid0000 out number, --传出参数 Psfbhjz in varchar2 default '1', --是否包含急诊 Ptjqsrq in varchar2 default '', --输入：统计起始日期 PYYID00 in varchar2 default '0', --输入：分院ID PSFKSTJ in varchar2 default '0', --输入：是否按科室统计 0按病区统计，1按科室统计 Psfglty in number default 1 --是否过滤停用科室或病区 0否，1是，默认1 ) -- MODIFICATION HISTORY -- Person Date Comments -- zhr 2008.09.23 修改床位算法，同统计指标 -- yangy 2009.04.26 允许不统计急诊病区 -- zhr 2009.05.07 占用床位数算法1按在院人数算0按床位算(在院人数-请假+包床) -- qks 2009.09.27 增加传入统计起始日期 ZHCX-20090324-002 -- daihq 2012.08.31 增加输入参数PFYID00 ZHCX-20120808-001 -- liuj 2012.10.24 循环次数处理问题，导致按时间段统计时不能完成所有数据的累加 for ZHCX-20121022-002 -- qks 2017.08.03 增加字段：编制床位GHKS06;注意：目前BEIZHU字段没用到了,相关比例统计,都放在前台计算。for YZCX9-20170623-002 -- qks 2017.08.10 增加字段：有效天数GHKS07（床位创建日期小于或等于统计日期段，才计算有效天数）。for YZCX9-20170807-001 -- liwm 2018.03.29 重新取病区KFCW00和SYCW00 for YZCX9-20180320-001 -- liwm 2018.05.07 省三修改床位的统计 for YZCX9-20180507-001 -- linshu 2019.06.19 增加入参PSFKSTJ、GHKS10(指标床位)的处理 by YZCX9-20190618-001 -- linshu 2019.07.22 增加GHKS10：指标床位，GHKS11：指标床日，GHKS112：核定床日数，GHKS13：开放床数(老年)，GHKS14：开放床日数(老年) 统计 by YZCX9-20190708-003 -- qks 2019.07.23 修改GHKS11：指标床日, GHKS14：开放床日数(老年) 取值; for YZCX9-20190724-001 -- qks 2019.08.19 新增GHKS15：保存BM_BMBM00.CZBZ00值; for YZCX9-20190820-001 -- qiulf 2019.09.03 修改ZH_ZYRBB0查询出现两条同病区数据的问题 for YZCX9-20190903-001 -- qks 2019.09.17 SP_ZY_CWRBB0改名为SP_ZH_CWRBB0; for YZCX9-20190916-001 -- jlg 2021.07.16 增加入参Psfglty（是否过滤停用科室或病区） YZCX9-20210714-001 as Vcounter number; --计数器 Vparams varchar2(255); --参数变量 Verrmsg varchar2(255); --错误提示变量 Ecustom exception; --错误变量 Vzycwsf char(1); --占用床位数算法1按在院人数算0按床位算(在院人数-请假+包床) ld_LOOP00 number(5); --循环次数 ls_TJRQ00 char(8); --统计日期 vKFCW00 BM_GHKSTJ.GHKS01%type; vSYCW00 BM_GHKSTJ.GHKS02%type; vZBCW00 BM_GHKSTJ.GHKS01%type; ls_TJTS00 number(5); --统计天数 Vlgsftj char(1); --住院留观是否参与统计床位使用率 Vtemp00 number; Vtemp01 number; --取所有病区 cursor C_TJBQ00 is select BMBH00,BMMC00,KFCW00,SYCW00,substrb(CJRQSJ,1,8) as CJRQ00,CZBZ00,ZBCW00 from BM_BMBM00 where ((Psfglty=1 and CZBZ00<>'2') or Psfglty=0) and ((PSFKSTJ='0' and BMXZ00='9') or (Psfkstj='1' and BMXZ00='0')) and FLAG00='1' and ((Psfbhjz='1') or ((Psfbhjz='0') and (bmmc00 not like '%急%'))) and ((YYID00=PYYID00) or (PYYID00='0')) order by BH0000; begin --占用床位数算法1按在院人数算0按床位算(在院人数-请假+包床) select substrb(nvl(trim(upper(max(Value0))),'0'),1,1) into Vzycwsf from XT_XTCS00 where name00 = 'ZH_ZYCWSF'; --留观是否统计 select substrb(nvl(trim(upper(max(Value0))),'Y'),1,1) into Vlgsftj from XT_XTCS00 where name00 = 'ZH_ZYLGSFTJ'; select SQ_BM_GHKSTJ_ID0000.nextval into Pid0000 from dual; ld_LOOP00 := 1; ls_TJRQ00 := nvl(Ptjqsrq,Ptjrq00); ls_TJTS00 := Round(to_date(Ptjrq00,'YYYY.MM.DD')-to_date(ls_TJRQ00,'YYYY.MM.DD'))+1; while 1 <= Round(to_date(Ptjrq00,'YYYY.MM.DD')-to_date(ls_TJRQ00,'YYYY.MM.DD'))+1 loop for BQ in C_TJBQ00 Loop --GHRQ00--病区名称 --GHKS01--实际开放总床日数：取统计区间内每日开放床位数累加值 --GHKS02--编制总床日数：取统计区间内每日夜晚12点编制床位累加值 --GHKS03--实际占用总床日数：取统计区间内各科每日夜晚12点实际占用病床数(即每日夜晚12点住院人数)总和 --GHKS04--空床床位：实际占用床日数-实际开放总床日数 --GHKS05--包床数：统计区间内所有包床床日累计 --BZ0000--使用率 --GHKS06--编制总床日数：取统计区间内每日夜晚12点编制床位累加值 --GHKS07--有效天数 --GHKS08 --编制床位：取结束时间当日后台维护的病区编制床位数 --GHKS09 --开放床位：取结束时间当日后台维护的病区开放床位数 --GHKS10 --指标床位 --GHKS11 --指标床日数：统计区间内指标床位累计 --GHKS12 --核定床日数：（计算规则：统计区间内每日核定床位累计） --GHKS13 --开放床数(老年) --GHKS14 --开放床日数(老年) --GHKS if ld_LOOP00 = 1 then if nvl(BQ.CJRQ00,'19000101')>Ptjrq00 then if PSFKSTJ = '0' then insert into BM_GHKSTJ (ID0000,GHRQ00,GHKS01,GHKS02,GHKS06,GHKS11,GHKS12,GHKS15) values (Pid0000,BQ.BMMC00,0,0,0,0,0,BQ.CZBZ00); else insert into BM_GHKSTJ (ID0000,GHRQ00,GHKS01,GHKS02,GHKS06,GHKS11,GHKS12,GHKS15) select Pid0000,BQ.BMMC00,BQ.KFCW00*ls_TJTS00,BQ.SYCW00*ls_TJTS00,BQ.BMBH00,BQ.ZBCW00*ls_TJTS00,0,BQ.CZBZ00 FROM dual; end if; end if; end if; --解决历史数据，如果ZH_ZYRBB0没有统计，那补一条记录 select count(*) into Vcounter from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00; if Vcounter = 0 then insert into ZH_ZYRBB0(ZYRQ00,BQH000,KSH000,SYCW00,KFCW00,YSZID0,ZBCW00) values(ls_TJRQ00, BQ.BMBH00, 0, BQ.SYCW00, BQ.KFCW00, 0, BQ.ZBCW00); end if; --BM_GHKSTJ如果没数据补一条空的 select count(*) into Vcounter from BM_GHKSTJ where ID0000 = Pid0000 and GHRQ00=BQ.BMMC00; if Vcounter = 0 then insert into BM_GHKSTJ (ID0000,GHRQ00,GHKS01,GHKS02,GHKS06,GHKS11) values (Pid0000,BQ.BMMC00,0,0,0,0); end if; --取日报当时状态的床位数据 select nvl(max(KFCW00), 0), nvl(max(SYCW00), 0),nvl(max(zbcw00), 0) into vKFCW00, vSYCW00,vZBCW00 from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00; --更改编制床位和开放床位,不累加 核定床位(老年) --指标床位 GHKS10 qks 2019.07.24 update BM_GHKSTJ set GHKS09 = vKFCW00, GHKS08 = vSYCW00,GHKS10 = vZBCW00 where ID0000 = Pid0000 and GHRQ00=BQ.BMMC00; --核定床日数(老年) update BM_GHKSTJ set GHKS12 = nvl(GHKS12, 0 ) + (select nvl(KFCW00, 0) from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00 and rownum=1) where ID0000 = Pid0000 and GHRQ00=BQ.BMMC00; --累加SYCW00和KFCW00 update BM_GHKSTJ set GHKS01 = GHKS01 + vKFCW00, GHKS02 = GHKS02 + vSYCW00, GHKS06 = GHKS06 + vSYCW00 where ID0000 = Pid0000 and GHRQ00=BQ.BMMC00; --指标床位 GHKS10 --update BM_GHKSTJ set GHKS10 = (select nvl(sum(zbcw00), 0) -- from BM_BMBM00 -- where BMXZ00 = '9' -- and FLAG00 = '1' -- and CZBZ00 <> '2' -- and (BMMC00 not like '急%' or Vlgsftj = 'Y')) -- where ID0000 = Pid0000 and GHRQ00=BQ.BMMC00; --开放床数(老年) GHKS13 update BM_GHKSTJ set GHKS13=(select nvl(SYCW00, 0) from BM_BMBM00 where BMXZ00 = '9' and FLAG00 = '1' and CZBZ00 <> '2' and (BMMC00 not like '急%' or Vlgsftj = 'Y') and BMBH00=BQ.BMBH00) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; -- update BM_GHKSTJ set GHKS13 = (select nvl(SYCW00,0) from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00) -- where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --开放床日数(老年) GHKS14 --update BM_GHKSTJ SET GHKS14=nvl(GHKS14,0) + (select nvl(SYCW00, 0) -- from BM_BMBM00 -- where BMXZ00 = '9' -- and FLAG00 = '1' -- and CZBZ00 <> '2' -- and (BMMC00 not like '急%' or Vlgsftj = 'Y') and BMBH00=BQ.BMBH00 and substrb(CJRQSJ,1,8)<=ls_TJRQ00 ) update BM_GHKSTJ set GHKS14 = nvl(GHKS14,0) + (select nvl(SYCW00,0) from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00 and rownum=1) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; if Psfkstj='0' then --正常使用的床位 --update BM_GHKSTJ set GHKS03=(select nvl(GHKS03,0)+count(distinct ZYID00) from BQ_BRLDXX where BQH000=BQ.BMBH00 and -- QSZT00 in ('新入','转入') and YXBZ00='1' and QSRQ00<=ls_TJRQ00 and BQJSRQ>ls_TJRQ00) -- where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; update BM_GHKSTJ set GHKS03=nvl(GHKS03,0)+(SELECT nvl(sum((least(decode(a.JSRQ00, '20991231', to_date(ls_TJRQ00, 'YYYYMMDD') +1, '99991231', to_date(ls_TJRQ00, 'YYYYMMDD') +1, null, to_date(ls_TJRQ00, 'YYYYMMDD') +1, to_date(a.JSRQ00, 'YYYYMMDD')), to_date(ls_TJRQ00, 'YYYYMMDD')+1) - greatest(to_date(a.QSRQ00, 'YYYYMMDD'), to_date(ls_TJRQ00, 'YYYYMMDD')))),0) FROM BQ_BRLDXX a,zy_brxxb0 b WHERE a.qszt00 in ('新入','转入','换入','入院') and a.BQH000=BQ.BMBH00 and yxbz00='1' and a.QSRQ00<=ls_TJRQ00 and (a.jsrq00>=ls_TJRQ00 or a.jsrq00 is null) and b.ryrq00<=ls_TJRQ00 and a.zyid00=b.zyid00 and (upper(substr(b.zyh000,1,1)) not in ('F','M')) and (a.qsrq00<>nvl(a.JSRQ00,'20991231') or a.jszt00='出院')) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; /*--暂时注释掉 if Vzycwsf='0' then --扣掉请假 update BM_GHKSTJ set GHKS03=(select nvl(GHKS03,0)-count(distinct ZYID00) from BQ_BRLDXX a where BQH000=BQ.BMBH00 and YXBZ00='1' and JSRQ00<=ls_TJRQ00 and JSZT00='请假' AND ( EXISTS (SELECT 1 FROM BQ_BRLDXX WHERE ZYID00=A.ZYID00 AND QSRQ00>ls_TJRQ00 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))) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --包床 update BM_GHKSTJ set GHKS03=(select nVL(GHKS03,0)+count(CH0000) from BQ_BRLDXX where BQH000=BQ.BMBH00 and QSZT00='包床' and nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>ls_TJRQ00 and QSRQ00<=ls_TJRQ00) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; end if; --包床 update BM_GHKSTJ set GHKS05=(select nvl(GHKS05,0)+count(CH0000) from BQ_BRLDXX where BQH000=BQ.BMBH00 and QSZT00='包床' and nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>ls_TJRQ00 and QSRQ00<=ls_TJRQ00) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; if nvl(BQ.CJRQ00,'19000101')<=ls_TJRQ00 then update BM_GHKSTJ set GHKS07=nvl(GHKS07,0)+1 where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; end if; */ --指标床日数 --update BM_GHKSTJ set GHKS11=nvl(GHKS14,0) +(select nvl(zbcw00, 0) -- from BM_BMBM00 -- where BMXZ00 = '9' -- and FLAG00 = '1' -- and CZBZ00 <> '2' -- and (BMMC00 not like '急%' or Vlgsftj = 'Y') and substrb(CJRQSJ,1,8)<=ls_TJRQ00 and substrb(CJRQSJ,1,8)>ls_TJRQ00) -- where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --指标床日数 qks 2019.07.24 update BM_GHKSTJ set GHKS11=nvl(GHKS11,0) +(select nvl(max(zbcw00), 0) from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00 and rownum=1) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --update BM_GHKSTJ set GHKS13 = (select nvl(SYCW00,0) from ZH_ZYBQRB where BQH000 = BQ.BMBH00 and ZYRQ00 = ls_TJRQ00) -- where ID0000 = Pid0000 and GHRQ00=BQ.BMMC00; else update BM_GHKSTJ set GHKS03=(select nvl(GHKS03,0)+count(distinct ZYID00) from BQ_BRLDXX where KSH000=BQ.BMBH00 and QSZT00 in ('新入','转入','迁入') and YXBZ00='1' and QSRQ00<=ls_TJRQ00 and BQJSRQ>ls_TJRQ00) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; if Vzycwsf='0' then --扣掉请假 update BM_GHKSTJ set GHKS03=(select nvl(GHKS03,0)-count(distinct ZYID00) from BQ_BRLDXX a where KSH000=BQ.BMBH00 and YXBZ00='1' and JSRQ00<=ls_TJRQ00 and JSZT00 in ('请假','出院') and ( exists (select 1 from BQ_BRLDXX where ZYID00=A.ZYID00 and QSRQ00>ls_TJRQ00 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)) ) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --包床 update BM_GHKSTJ set GHKS03=(select nVL(GHKS03,0)+count(CH0000) from BQ_BRLDXX where KSH000=BQ.BMBH00 and QSZT00='包床' and nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>ls_TJRQ00 and QSRQ00<=ls_TJRQ00) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; end if; --包床 update BM_GHKSTJ set GHKS05=(select nvl(GHKS05,0)+count(CH0000) from BQ_BRLDXX where KSH000=BQ.BMBH00 and QSZT00='包床' and nvl(JSRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>ls_TJRQ00 and QSRQ00<=ls_TJRQ00) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --指标床日数 --update BM_GHKSTJ set GHKS11=nvl(ghks11,0) + (select nvl(zbcw00, 0) from BM_BMBM00 where BMXZ00 = '9' and FLAG00 = '1' and CZBZ00 <> '2' -- and (BMMC00 not like '急%' or Vlgsftj = 'Y') and substrb(CJRQSJ,1,8)<=ls_TJRQ00 and substrb(CJRQSJ,1,8)>ls_TJRQ00 ) -- where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; --指标床日数 qks 2019.07.24 update BM_GHKSTJ set GHKS11=nvl(ghks11,0) + (select nvl(max(zbcw00), 0) from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00 and rownum=1) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; update BM_GHKSTJ set GHKS13 = nvl(GHKS13,0) + (select nvl(SYCW00,0) from ZH_ZYRBB0 where BQH000 = BQ.BMBH00 and KSH000 = 0 and YSZID0 = 0 and ZYRQ00 = ls_TJRQ00 and rownum=1) where ID0000=Pid0000 and GHRQ00=BQ.BMMC00; end if; end Loop; ls_TJRQ00 := to_char(to_Date(ls_TJRQ00,'YYYY.MM.DD')+1,'YYYYMMDD'); ld_LOOP00 := ld_LOOP00+1; --循环次数累加 end loop; update BM_GHKSTJ set BEIZHU=to_char(GHKS03/decode(GHKS01,0,decode(GHKS03,0,1,GHKS03),GHKS01)*100,'999.9')||'%', GHKS04=GHKS02-GHKS03 where ID0000=Pid0000; --commit; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm||','||Vparams,1,220)); when Ecustom then raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams,1,220)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220)); rollback; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%