create table SD_ZS.ZS_LJGLTJ ( ID0000 number(10) primary key, --ID YYID00 varchar2(6) not null, --医院ID YYQC00 varchar2(60) not null, --医院全称 CYRQ00 char(8) not null, --出院日期 LJID00 number(10) not null, --路径ID LJMC00 varchar2(50) not null, --路径名称 ZLS000 number(8) not null, --总例数 RJS000 number(8) not null, --入径数 RJL000 varchar2(20) not null, --入径率 WCS000 number(8) not null, --完成数 WCL000 varchar2(20) not null, --完成率 BYS000 number(8) not null, --变异数 BYL000 varchar2(20) not null, --变异率 CDRGZS number(8) not null, --纳入CDRG管理总病例数 CDRGRJ number(8) not null, --纳入CDRG管理入径数 CDRGWC number(8) not null, --纳入CDRG管理完成数 RZL000 varchar2(20) not null, --入组率 ZRYS15 number(8) not null, --15日再入院人数 ZRYL15 varchar2(20) not null, --出院15天内再住院率 LJCDRG varchar2(20) not null, --临床路径管理数与CDRG入组率 ZTS000 number(8) not null, --总天数 PJZYTS number(8) not null, --平均住院天数 ZYS000 number(8) not null, --治愈数 ZYL000 varchar2(20) not null, --治愈率 HZS000 number(8) not null, --好转数 HZL000 varchar2(20) not null, --好转率 WZYS00 number(8) not null, --未治愈数 SWS000 number(8) not null, --死亡数 QTRS00 number(8) not null, --其他人数 ZRYS30 number(8) not null, --30日内再入院 ZSSS00 number(8) not null, --再手术数 BFZHBZ number(8) not null, --并发症与合并症数 ZFY000 number(8,2) not null, --总费用 PJFY00 number(8,2) not null, --平均费用 ZYF000 number(8,2) not null, --总药费 YBBXFY number(8,2) not null, --医保报销费用 KSBH00 number(5) not null, --科室编号 KSMC00 varchar2(30) not null --科室名称 ); comment on table SD_ZS.ZS_LJGLTJ is '路径管理统计【住院电子病历系统-临床路径】'; comment on column SD_ZS.ZS_LJGLTJ.YYID00 is 'ID'; comment on column SD_ZS.ZS_LJGLTJ.ID0000 is '医院ID'; comment on column SD_ZS.ZS_LJGLTJ.YYQC00 is '医院全称'; comment on column SD_ZS.ZS_LJGLTJ.CYRQ00 is '出院日期'; comment on column SD_ZS.ZS_LJGLTJ.LJID00 is '路径ID'; comment on column SD_ZS.ZS_LJGLTJ.LJMC00 is '路径名称'; comment on column SD_ZS.ZS_LJGLTJ.ZLS000 is '总例数'; comment on column SD_ZS.ZS_LJGLTJ.RJS000 is '入径数'; comment on column SD_ZS.ZS_LJGLTJ.RJL000 is '入径率'; comment on column SD_ZS.ZS_LJGLTJ.WCS000 is '完成数'; comment on column SD_ZS.ZS_LJGLTJ.WCL000 is '完成率'; comment on column SD_ZS.ZS_LJGLTJ.BYS000 is '变异数'; comment on column SD_ZS.ZS_LJGLTJ.BYL000 is '变异率'; comment on column SD_ZS.ZS_LJGLTJ.CDRGZS is '纳入CDRG管理总病例数'; comment on column SD_ZS.ZS_LJGLTJ.CDRGRJ is '纳入CDRG管理入径数'; comment on column SD_ZS.ZS_LJGLTJ.CDRGWC is '纳入CDRG管理完成数'; comment on column SD_ZS.ZS_LJGLTJ.RZL000 is '入组率'; comment on column SD_ZS.ZS_LJGLTJ.ZRYS15 is '15日再入院人数'; comment on column SD_ZS.ZS_LJGLTJ.ZRYL15 is '出院15天内再住院率'; comment on column SD_ZS.ZS_LJGLTJ.LJCDRG is '临床路径管理数与CDRG入组率'; comment on column SD_ZS.ZS_LJGLTJ.ZTS000 is '总天数'; comment on column SD_ZS.ZS_LJGLTJ.PJZYTS is '平均住院天数'; comment on column SD_ZS.ZS_LJGLTJ.ZYS000 is '治愈数'; comment on column SD_ZS.ZS_LJGLTJ.ZYL000 is '治愈率'; comment on column SD_ZS.ZS_LJGLTJ.HZS000 is '好转数'; comment on column SD_ZS.ZS_LJGLTJ.HZL000 is '好转率'; comment on column SD_ZS.ZS_LJGLTJ.WZYS00 is '未治愈数'; comment on column SD_ZS.ZS_LJGLTJ.SWS000 is '死亡数'; comment on column SD_ZS.ZS_LJGLTJ.QTRS00 is '其他人数'; comment on column SD_ZS.ZS_LJGLTJ.ZRYS30 is '30日内再入院'; comment on column SD_ZS.ZS_LJGLTJ.ZSSS00 is '再手术数'; comment on column SD_ZS.ZS_LJGLTJ.BFZHBZ is '并发症与合并症数'; comment on column SD_ZS.ZS_LJGLTJ.ZFY000 is '总费用'; comment on column SD_ZS.ZS_LJGLTJ.PJFY00 is '平均费用'; comment on column SD_ZS.ZS_LJGLTJ.ZYF000 is '总药费'; comment on column SD_ZS.ZS_LJGLTJ.YBBXFY is '医保报销费用'; comment on column SD_ZS.ZS_LJGLTJ.KSBH00 is '科室编号'; comment on column SD_ZS.ZS_LJGLTJ.KSMC00 is '科室名称'; create synonym ZS_LJGLTJ for SD_ZS.ZS_LJGLTJ; create sequence SQ_ZS_LJGLTJ_ID0000 increment by 1 start with 1 maxvalue 99999999999 minvalue 1 cycle cache 10; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% create or replace procedure SP_ZS_LJGLTS_ZYDZBL ( as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_KSBH00 in number default 0 --科室编号 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zouqc 2021.09.27 create for 路径管理统计数据推送给医共体 ad_ID0000 number(10); begin select SQ_LJ_TJ0000_TJID00.nextval into ad_ID0000 from dual; insert into LJ_TJTEMP(ID0000,ZYID00,LJID01,LJID00,RZKS00,ZRZCID,RYRQ00) select ad_ID0000,ZYID00,LJID01,LJID00,RZKS00,ZRZCID,RYRQ00 from VW_ZS_RJBRMX_ALL_ZYDZBL a where SJCYRQ>=as_KSRQ00 and SJCYRQ<=as_JSRQ00 ; update LJ_TJTEMP a set LJID01=LJID00 where LJID00>0 and ID0000=ad_ID0000; update LJ_TJTEMP a set (JSFS00,MINTS0,MAXTS0,GJTS00,SJTS00) =( select JSFS00,MINTS0,MAXTS0,GJTS00,SJTS00 from LJ_ZRZC00 b where ZYID00=a.ZYID00 and LJID00=a.LJID00 and JSFS00 in ('完成','强制退出') and rownum<=1 ) where ID0000=ad_ID0000; update LJ_TJTEMP a set SSRQ00=(select SSRQ00 from LJ_TJZBZ0 where ZYID00=a.ZYID00 and ZRZCID=a.ZRZCID and rownum=1 ) where ID0000=ad_ID0000; insert into LJ_TJ0000(TJID00,KSBH00,LJID00,TJ0001,TJ0011,TJ0019) select distinct ad_ID0000,RZKS00,a.LJID00,'0',0,0 from LJ_TJTEMP a where ID0000=ad_ID0000 and LJID00>0 and not exists(select 1 from LJ_TJ0000 where TJID00=ad_ID0000 and KSBH00=a.RZKS00 and LJID00=a.LJID00) ; --入院总人数 update LJ_TJ0000 a set RYZS00=(select to_char(count(*)) from LJ_TJTEMP c where ID0000=ad_ID0000 and (LJID00=a.LJID00 or LJID01=a.LJID00) and (RZKS00=as_KSBH00 or as_KSBH00=0) ) where TJID00=ad_ID0000; ---TJ0001 入径总人次 update LJ_TJ0000 a set TJ0001=(select to_char(count(*)) from LJ_TJTEMP c where ID0000=ad_ID0000 and LJID00=a.LJID00 and (RZKS00=as_KSBH00 or as_KSBH00=0) ) where TJID00=ad_ID0000; --TJ0002:完成路径人数 update LJ_TJ0000 a set TJ0002=nvl((select to_char(count(*)) from LJ_TJTEMP c where c.ID0000=ad_ID0000 and JSFS00 in('完成','强制退出') and LJID00=a.LJID00 and (RZKS00=as_KSBH00 or as_KSBH00=0) and nvl(SJTS00,0)-nvl(GJTS00,0)<=0 ) ,'0') where TJID00=ad_ID0000; --TJ0003:变异人次 update LJ_TJ0000 a set TJ0003=nvl((select to_char(count(*)) from LJ_TJTEMP c where c.ID0000=ad_ID0000 and JSFS00 in('完成','强制退出') and LJID00=a.LJID00 and (RZKS00=as_KSBH00 or as_KSBH00=0) and nvl(SJTS00,0)-nvl(GJTS00,0)>0 ) ,'0') where TJID00=ad_ID0000; --TJ0004 总住院天数 update LJ_TJ0000 a set TJ0004=to_char(decode(TJ0002,'0',0,nvl((select sum(SJTS00) from LJ_TJTEMP where LJID00=a.LJID00 and ID0000=ad_ID0000 and (RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') ),0) ),'99990.99') where TJID00=ad_ID0000; --TJ0005 平均住院天数 update LJ_TJ0000 a set TJ0005=to_char(decode(TJ0002,'0',0,nvl((select sum(SJTS00) from LJ_TJTEMP where LJID00=a.LJID00 and ID0000=ad_ID0000 and (RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') ),0) /to_number(TJ0002)),'99990.99') where TJID00=ad_ID0000; --TJ0015 住院费用 update LJ_TJ0000 a set TJ0015=decode(TJ0002,'0','0',to_char( nvl((select sum(HJJE00) from zy_fymx00 b,LJ_TJTEMP c where c.ZYID00=b.ZYID00 and c.LJID00=a.LJID00 --and c.RZKS00=a.KSBH00 and (RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') and c.ID0000=ad_ID0000),0),'9999999990.99')) where TJID00=ad_ID0000; --TJ0016 住院药费 update LJ_TJ0000 a set TJ0016=decode(TJ0002,'0','0',to_char(nvl((select sum(c.HJJE00) from zy_fymx00 c,LJ_TJTEMP b where c.ZYID00=b.ZYID00 and JSFS00 in('完成','强制退出') and b.LJID00=a.LJID00 and c.sflb00='3' and (RZKS00=as_KSBH00 or as_KSBH00=0) and b.ID0000=ad_ID0000),0),'9999999990.99') ) where TJID00=ad_ID0000; --TJ0017 纳入CDRG管理总病历数 update LJ_TJ0000 a set TJ0017=(select count(*) from ZY_BRXXB0 b,LJ_TJTEMP c where b.ZYID00=c.ZYID00 and b.CDRGFS='1' and ID0000=ad_ID0000 and (C.RZKS00=as_KSBH00 or as_KSBH00=0) and (LJID00=a.LJID00 or LJID01=a.Ljid00)) where TJID00=ad_ID0000; --TJ0018 纳入CDRG管理入径数 update LJ_TJ0000 a set TJ0018=(select count(*) from ZY_BRXXB0 b,LJ_TJTEMP c where b.ZYID00=c.ZYID00 and b.CDRGFS='1' and ID0000=ad_ID0000 and (C.RZKS00=as_KSBH00 or as_KSBH00=0) and LJID00=a.LJID00) where TJID00=ad_ID0000; --TJ0019 纳入CDRG管理完成数 update LJ_TJ0000 a set TJ0019=nvl((select to_char(count(*)) from ZY_BRXXB0 b,LJ_TJTEMP c where b.ZYID00=c.ZYID00 and b.CDRGFS='1' and c.ID0000=ad_ID0000 and (C.RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') and LJID00=a.LJID00 and nvl(SJTS00,0)-nvl(GJTS00,0)<=0 ) ,'0') where TJID00=ad_ID0000; --TJ0020 医保报销费用 update LJ_TJ0000 a set TJ0020=decode(TJ0002,'0','0',to_char(nvl((select sum(c.TCJJZF) from ZY_JZB000 c,LJ_TJTEMP b where c.ZYID00=b.ZYID00 and JSFS00 in('完成','强制退出') and b.LJID00=a.LJID00 and (B.RZKS00=as_KSBH00 or as_KSBH00=0) and b.ID0000=ad_ID0000),0),'9999999990.99') ) where TJID00=ad_ID0000; update LJ_TJ0000 d set (TJ0006,TJ0007,TJ0008,TJ0009,TJ0010,TJ0011,TJ0012,TJ0013,TJ0014) =(select sum(decode(ZLJG00,'治愈',1,0)) ZYRS00,--治愈人数 sum(decode(ZLJG00,'好转',1,0)) ZYRS00,--好转人数 sum(decode(ZLJG00,'未愈',1,0)) ZYRS00,--未治愈人数 sum(decode(ZLJG00,'死亡',1,0)) as SWRS00, --死亡人数 sum(decode(ZLJG00,'治愈',0,'好转',0,'死亡',0,1)), --其他人数 sum(case when to_date(a.RYRQ00,'YYYYMMDD')-to_date(b.SCCYRQ,'YYYYMMDD')<=30 and to_date(a.RYRQ00,'YYYYMMDD')-to_date(b.SCCYRQ,'YYYYMMDD')>=2 then 1 else 0 end) as CYRY31 , --30日内再入院人数 sum(decode(FJHZSS,'1',1,0)) as FJHZSS , --非计划内再手术人数 sum(decode(SFYBFZ,'1',1,0)+decode(HBZ000,'1',1,0)) as BFZRS0, --有并发症人数 sum(case when to_date(a.RYRQ00,'YYYYMMDD')-to_date(b.SCCYRQ,'YYYYMMDD')<=15 then 1 else 0 end) as CYRY15 --15日再入院 from LJ_TJTEMP a,LJ_TJZBZ0 b where a.ID0000=ad_ID0000 and a.ZYID00=b.ZYID00 and a.ZRZCID=b.ZRZCID and a.ZRZCID>0 and LJID00=d.LJID00 and JSFS00 in ('完成','强制退出') ) where TJID00=ad_ID0000; update LJ_TJ0000 a set (a.LJBM00,a.LJMC00)=(select LJBM00,LJMC00 from LJ_MB0000 where LJID00=a.LJID00) where a.TJID00=ad_ID0000; insert into ZS_LJGLTJ(ID0000,YYID00,YYQC00,CYRQ00,LJID00,LJMC00,ZLS000,RJS000,RJL000,WCS000,WCL000,BYS000,BYL000,CDRGZS,CDRGRJ,CDRGWC,RZL000,ZRYS15,ZRYL15,LJCDRG,ZTS000,PJZYTS,ZYS000,ZYL000,HZS000, HZL000,WZYS00,SWS000,QTRS00,ZRYS30,ZSSS00,BFZHBZ,ZFY000,PJFY00,ZYF000,YBBXFY,KSBH00,KSMC00) select SQ_ZS_LJGLTJ_ID0000.NEXTVAL,224465,'沙县总医院',as_KSRQ00,A.LJID00,A.LJMC00,A.RYZS00,nvl(TJ0001,0),decode(nvl(RYZS00,'0'),'0','0',round((TJ0001/RYZS00)*100,2))||'%',TJ0002,decode(nvl(TJ0001,'0'),'0','0',round((TJ0002/TJ0001)*100,2))||'%', TJ0003,decode(nvl(TJ0001,'0'),'0','0',round((TJ0003/TJ0001)*100,2))||'%',TJ0017,TJ0018,TJ0019,decode(nvl(TJ0017,'0'),'0','0',round((TJ0018/TJ0017)*100,2))||'%',nvl(TJ0014,0),decode(nvl(RYZS00,'0'),'0','0',round((nvl(TJ0014,0)/RYZS00)*100,2))||'%', decode(nvl(TJ0017,'0'),'0','0',round((TJ0019/TJ0017)*100,2))||'%',TJ0004,TJ0005,nvl(TJ0006,0),decode(nvl(TJ0001,'0'),'0','0',round((nvl(TJ0006,0)/TJ0001)*100,2))||'%',nvl(TJ0007,0),decode(nvl(TJ0001,'0'),'0','0',round((nvl(TJ0007,0)/TJ0001)*100,2))||'%', nvl(TJ0008,0),nvl(TJ0009,0),nvl(TJ0010,0),nvl(TJ0011,0),nvl(TJ0012,0),nvl(TJ0013,0),TJ0015,decode(nvl(TJ0001,'0'),'0','0',round(TJ0015/TJ0001,2)),TJ0016,TJ0020,A.KSBH00,(select BMMC00 from BM_BMBM00 where BMBH00=A.KSBH00) from LJ_TJ0000 a where TJID00=ad_ID0000 ; delete LJ_TJTEMP where ID0000=ad_ID0000; commit; exception when others then raise_application_error(-20020,substr(SQLERRM||'执行存储过程错误:SP_ZS_LJZLGL_ZYDZBL('||',ad_ID0000)',1,150)); rollback; end; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% create or replace function SF_ZS_SJDNZXCCGC( P_KSRQ00 IN char, P_JSRQ00 IN char ) -- MODIFICATION HISTORY -- Person Date Comments -- zouqc 2021.09.27 create for 按日期区间执行sp_zs_ljglts_zydzbl过程 return varchar2 is V_RETURN varchar2(2000); cursor cur_rq is select distinct SJCYRQ from ZY_BRXXB0 where SJCYRQ>=P_KSRQ00 and SJCYRQ<=P_JSRQ00; begin for rec in cur_rq loop sp_zs_ljglts_zydzbl(rec.SJCYRQ,rec.SJCYRQ,0); end loop; V_RETURN:='成功'; return V_RETURN; end; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% create or replace procedure SP_ZS_LJZLGL_ZYDZBL ( as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_KSBH00 in number default 0, --科室编号 ad_ID0000 out number --统计报表对应的ID ) as -- MODIFICATION HISTORY -- Person Date Comments -- zouqc 2019.08.09 create for ZYDZBL-20190710-008 -- zouqc 2019.10.25 增加纳入CDRG管理总病历数、15天内再次入院人数、入组率、出院15天内再住院率、临床路径管理数与CDRG入组率、纳入CDRG管理入径数、纳入CDRG管理完成数字段 for ZYDZBL-20190924-004 -- zouqc 2020.02.06 总例数、纳入CDRG管理总例数取值包括符合路径但是未入径的病人 -- zouqc 2020.07.10 增加医保报销费用 for ZYDZBL-20200708-004 -- zouqc 2021.06.28 增加KSBH00查询条件 for ZYDZBL-20210220-005 -- zouqc 2021.09.29 修改住院费用、住院药费的取值 begin select SQ_LJ_TJ0000_TJID00.nextval into ad_ID0000 from dual; insert into LJ_TJTEMP(ID0000,ZYID00,LJID01,LJID00,RZKS00,ZRZCID,RYRQ00) select ad_ID0000,ZYID00,LJID01,LJID00,RZKS00,ZRZCID,RYRQ00 from VW_ZS_RJBRMX_ALL_ZYDZBL a where SJCYRQ>=as_KSRQ00 and SJCYRQ<=as_JSRQ00 ; update LJ_TJTEMP a set LJID01=LJID00 where LJID00>0 and ID0000=ad_ID0000; update LJ_TJTEMP a set (JSFS00,MINTS0,MAXTS0,GJTS00,SJTS00) =( select JSFS00,MINTS0,MAXTS0,GJTS00,SJTS00 from LJ_ZRZC00 b where ZYID00=a.ZYID00 and LJID00=a.LJID00 and JSFS00 in ('完成','强制退出') and rownum<=1 ) where ID0000=ad_ID0000; update LJ_TJTEMP a set SSRQ00=(select SSRQ00 from LJ_TJZBZ0 where ZYID00=a.ZYID00 and ZRZCID=a.ZRZCID and rownum=1 ) where ID0000=ad_ID0000; insert into LJ_TJ0000(TJID00,KSBH00,LJID00,TJ0001,TJ0011,TJ0019) select distinct ad_ID0000,RZKS00,a.LJID00,'0',0,0 from LJ_TJTEMP a where ID0000=ad_ID0000 and LJID00>0 and not exists(select 1 from LJ_TJ0000 where TJID00=ad_ID0000 and KSBH00=a.RZKS00 and LJID00=a.LJID00) ; --入院总人数 update LJ_TJ0000 a set RYZS00=(select to_char(count(*)) from LJ_TJTEMP c where ID0000=ad_ID0000 and (LJID00=a.LJID00 or LJID01=a.LJID00) and (RZKS00=as_KSBH00 or as_KSBH00=0) ) where TJID00=ad_ID0000; ---TJ0001 入径总人次 update LJ_TJ0000 a set TJ0001=(select to_char(count(*)) from LJ_TJTEMP c where ID0000=ad_ID0000 and LJID00=a.LJID00 and (RZKS00=as_KSBH00 or as_KSBH00=0) ) where TJID00=ad_ID0000; --TJ0002:完成路径人数 update LJ_TJ0000 a set TJ0002=nvl((select to_char(count(*)) from LJ_TJTEMP c where c.ID0000=ad_ID0000 and JSFS00 in('完成','强制退出') and LJID00=a.LJID00 and (RZKS00=as_KSBH00 or as_KSBH00=0) and nvl(SJTS00,0)-nvl(GJTS00,0)<=0 ) ,'0') where TJID00=ad_ID0000; --TJ0003:变异人次 update LJ_TJ0000 a set TJ0003=nvl((select to_char(count(*)) from LJ_TJTEMP c where c.ID0000=ad_ID0000 and JSFS00 in('完成','强制退出') and LJID00=a.LJID00 and (RZKS00=as_KSBH00 or as_KSBH00=0) and nvl(SJTS00,0)-nvl(GJTS00,0)>0 ) ,'0') where TJID00=ad_ID0000; --TJ0004 总住院天数 update LJ_TJ0000 a set TJ0004=to_char(decode(TJ0002,'0',0,nvl((select sum(SJTS00) from LJ_TJTEMP where LJID00=a.LJID00 and ID0000=ad_ID0000 and (RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') ),0) ),'99990.99') where TJID00=ad_ID0000; --TJ0005 平均住院天数 update LJ_TJ0000 a set TJ0005=to_char(decode(TJ0002,'0',0,nvl((select sum(SJTS00) from LJ_TJTEMP where LJID00=a.LJID00 and ID0000=ad_ID0000 and (RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') ),0) /to_number(TJ0002)),'99990.99') where TJID00=ad_ID0000; --TJ0015 住院费用 update LJ_TJ0000 a set TJ0015=decode(TJ0002,'0','0',to_char( nvl((select sum(HJJE00) from zy_fymx00 b,LJ_TJTEMP c where c.ZYID00=b.ZYID00 and c.LJID00=a.LJID00 --and c.RZKS00=a.KSBH00 and (RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') and c.ID0000=ad_ID0000),0),'9999999990.99')) where TJID00=ad_ID0000; --TJ0016 住院药费 update LJ_TJ0000 a set TJ0016=decode(TJ0002,'0','0',to_char(nvl((select sum(c.HJJE00) from zy_fymx00 c,LJ_TJTEMP b where c.ZYID00=b.ZYID00 and JSFS00 in('完成','强制退出') and b.LJID00=a.LJID00 and c.sflb00='3' and (RZKS00=as_KSBH00 or as_KSBH00=0) and b.ID0000=ad_ID0000),0),'9999999990.99') ) where TJID00=ad_ID0000; --TJ0017 纳入CDRG管理总病历数 update LJ_TJ0000 a set TJ0017=(select count(*) from ZY_BRXXB0 b,LJ_TJTEMP c where b.ZYID00=c.ZYID00 and b.CDRGFS='1' and ID0000=ad_ID0000 and (C.RZKS00=as_KSBH00 or as_KSBH00=0) and (LJID00=a.LJID00 or LJID01=a.Ljid00)) where TJID00=ad_ID0000; --TJ0018 纳入CDRG管理入径数 update LJ_TJ0000 a set TJ0018=(select count(*) from ZY_BRXXB0 b,LJ_TJTEMP c where b.ZYID00=c.ZYID00 and b.CDRGFS='1' and ID0000=ad_ID0000 and (C.RZKS00=as_KSBH00 or as_KSBH00=0) and LJID00=a.LJID00) where TJID00=ad_ID0000; --TJ0019 纳入CDRG管理完成数 update LJ_TJ0000 a set TJ0019=nvl((select to_char(count(*)) from ZY_BRXXB0 b,LJ_TJTEMP c where b.ZYID00=c.ZYID00 and b.CDRGFS='1' and c.ID0000=ad_ID0000 and (C.RZKS00=as_KSBH00 or as_KSBH00=0) and JSFS00 in('完成','强制退出') and LJID00=a.LJID00 and nvl(SJTS00,0)-nvl(GJTS00,0)<=0 ) ,'0') where TJID00=ad_ID0000; --TJ0020 医保报销费用 update LJ_TJ0000 a set TJ0020=decode(TJ0002,'0','0',to_char(nvl((select sum(c.TCJJZF) from ZY_JZB000 c,LJ_TJTEMP b where c.ZYID00=b.ZYID00 and JSFS00 in('完成','强制退出') and b.LJID00=a.LJID00 and (B.RZKS00=as_KSBH00 or as_KSBH00=0) and b.ID0000=ad_ID0000),0),'9999999990.99') ) where TJID00=ad_ID0000; update LJ_TJ0000 d set (TJ0006,TJ0007,TJ0008,TJ0009,TJ0010,TJ0011,TJ0012,TJ0013,TJ0014) =(select sum(decode(ZLJG00,'治愈',1,0)) ZYRS00,--治愈人数 sum(decode(ZLJG00,'好转',1,0)) ZYRS00,--好转人数 sum(decode(ZLJG00,'未愈',1,0)) ZYRS00,--未治愈人数 sum(decode(ZLJG00,'死亡',1,0)) as SWRS00, --死亡人数 sum(decode(ZLJG00,'治愈',0,'好转',0,'死亡',0,1)), --其他人数 sum(case when to_date(a.RYRQ00,'YYYYMMDD')-to_date(b.SCCYRQ,'YYYYMMDD')<=30 and to_date(a.RYRQ00,'YYYYMMDD')-to_date(b.SCCYRQ,'YYYYMMDD')>=2 then 1 else 0 end) as CYRY31 , --30日内再入院人数 sum(decode(FJHZSS,'1',1,0)) as FJHZSS , --非计划内再手术人数 sum(decode(SFYBFZ,'1',1,0)+decode(HBZ000,'1',1,0)) as BFZRS0, --有并发症人数 sum(case when to_date(a.RYRQ00,'YYYYMMDD')-to_date(b.SCCYRQ,'YYYYMMDD')<=15 then 1 else 0 end) as CYRY15 --15日再入院 from LJ_TJTEMP a,LJ_TJZBZ0 b where a.ID0000=ad_ID0000 and a.ZYID00=b.ZYID00 and a.ZRZCID=b.ZRZCID and a.ZRZCID>0 and LJID00=d.LJID00 and JSFS00 in ('完成','强制退出') ) where TJID00=ad_ID0000; update LJ_TJ0000 a set (a.LJBM00,a.LJMC00)=(select LJBM00,LJMC00 from LJ_MB0000 where LJID00=a.LJID00) where a.TJID00=ad_ID0000; --合计 insert into LJ_TJ0000(TJID00,KSBH00,LJID00,RYZS00,LJMC00,TJ0001,TJ0002,TJ0003,TJ0004,TJ0005,TJ0006,TJ0007, TJ0008,TJ0009,TJ0010,TJ0011,TJ0012,TJ0013,TJ0014,TJ0015,TJ0016,TJ0017,TJ0018,TJ0019,TJ0020) select ad_ID0000,0,0,sum(RYZS00),'合计',sum(TJ0001),sum(TJ0002),sum(TJ0003),sum(TJ0004), decode(sum(TJ0002),'0',0,round(sum(TJ0004)/to_number(sum(TJ0002)),0)),sum(TJ0006),sum(TJ0007),sum(TJ0008), sum(TJ0009),sum(TJ0010),sum(TJ0011),sum(TJ0012),sum(TJ0013),sum(TJ0014), sum(TJ0015),sum(TJ0016),sum(TJ0018),sum(TJ0018),sum(TJ0019),sum(TJ0020) from LJ_TJ0000 a where TJID00=ad_ID0000 ; delete LJ_TJTEMP where ID0000=ad_ID0000; commit; exception when others then raise_application_error(-20020,substr(SQLERRM||'执行存储过程错误:SP_ZS_LJZLGL_ZYDZBL('||',ad_ID0000)',1,150)); rollback; end;