CREATE OR REPLACE VIEW VW_ZS_HZBR00_DZBL AS
select  A.RYZDMC,A.SZYS00,A.ZZYS00,A.ZRYS00,A.SXYS00,A.ZYID00,A.BRID00,A.MRN000,A.ZYH000,A.XM0000,A.XB0000,A.CSRQ00,
        A.BRZT00,A.RYCWH0,A.RYRQ00,A.RYSJ00,A.RYZD00,C.BLZT00,A.DQKS00 DQKSBH,C.YSZBH0,C.YKSBH0,C.YYSZBH,
        C.YSZMC0,C.THYY00,C.GDRQ00,C.GDSJ00 , B.YBMC00,A.RZKS00,D.BMMC00,A.CYDJRQ,  
  SF_BQ_CSRQTONL(a.CSRQ00) BRNL00,    --病人年龄
  --to_char(greatest(decode(nvl(sjcyrq,'20991231'),'20991231',to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd'),to_date(a.sjcyrq,'yyyymmdd'))-to_date(a.RYRQ00,'yyyymmdd'),1))||'天' RYTSMS, --住院天数描述
  decode(substr(a.BRZT00,1,1),'2',
    to_char(greatest(to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd')-to_date(a.RYRQ00,'yyyyMMdd'),1))||'天',
    substr(a.sjcyrq,1,4)||'-'||substr(a.SJCYRQ,5,2)||'-'||substr(a.SJCYRQ,7,2)
    ) as RYTSMS, --住院天数描述
  (select ' '||x.SSMC00 from VW_SS_YW0000 x where x.ZYGHID(+)=a.ZYID00 and x.MZZYBZ='1' and x.SSZT00 not in ('1','3') 
   and x.SSQSRQ=(select trim(max(y.SSQSRQ)) from SS_YW0000 y where y.ZYGHID(+)=x.ZYGHID and y.MZZYBZ='1' 
   and y.SSZT00 not in ('1','3')) and rownum=1) ssmc00, --手术名称
  (select decode(trim(max(x.SSQSRQ)),null,null,((to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')-to_date(trim(max(x.SSQSRQ)),'yyyymmdd')))) 
   from SS_YW0000 x where x.ZYGHID(+)=a.ZYID00 and x.MZZYBZ='1' and x.SSZT00 not in ('1','3')) SSTS00,  --手术天数
  (select y.ZLXMJC from BM_ZLZD00 y where y.ZLXMID(+)=a.HLJBID) HLJB00,   --护理级别
  (select decode(substr(trim(y.ZLXMJC),1,1),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',substr(trim(y.ZLXMJC),1,1))  from BM_ZLZD00 y where y.ZLXMID(+)=a.HLJBID) HLJBJC,   --护理级别简称
  (SELECT ZDMC00 FROM VW_DZBL_BRZDXX_ONE WHERE ZYID00=a.ZYID00 And Rownum=1) ZDMC00,  --诊断名称 
  a.DQBQ00,   --当前病区
  e.SQKS00 HZSQKS,    --申请科室
  e.SQYS00 HZSQYS,    --申请医生
  (SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00(+)=E.SQYS00) SQYSXM,    --申请医生姓名
  e.SQRQ00 HZSQRQ,    --申请日期
  e.SQSJ00 HZSQSJ,    --申请时间
  DECODE(e.HZKS00,NULL,NULL,';'||e.HZKS00||';') HZKS00,    --会诊科室(多科室用";"隔开)
  e.HZKSMC,    --会诊科室名称  
  DECODE(e.HZYS00,NULL,NULL,';'||e.HZYS00||';') HZYS00,  --会诊医生(多医生用";"隔开)
    e.HZYSXM,    --会诊医生姓名
    e.HZJSBZ,    --会诊结束标志'1'结束 '0'未结束  
    f.YSZID0,       --医生组ID
    f.SSKSBH,       --所属科室编号
    f.SSKSMC,       --所属科室名称
    f.YSZZT0,        --医生组状态
    decode((select count(1) from LJ_ZRZC00 where nvl(JSFS00,' ')=' ' and zyid00=a.zyid00),0,'0','1') SFLJBR--是否路径病人1是0否
from ZY_BRXXB0 A,ZS_BLXX01 C, IC_YBBRLB B  ,BM_BMBM00 D, ZS_BRHZ00 E, ZS_YSZ000 F
 where A.ZYID00=C.ZYID00 and A.ZYID00=E.ZYID00 and A.YBLB00=B.YBLB00 and C.YSZBH0=F.YSZID0 and A.FBBH00=B.FBBH00 
   and A.RZKS00=D.BMBH00 and C.BLZT00 IN ('1','2','5')
   union all
select  A.RYZDMC,A.SZYS00,A.ZZYS00,A.ZRYS00,A.SXYS00,A.ZYID00,A.BRID00,A.MRN000,A.ZYH000,A.XM0000,A.XB0000,A.CSRQ00,
        A.BRZT00,A.RYCWH0,A.RYRQ00,A.RYSJ00,A.RYZD00,C.BLZT00,A.DQKS00 DQKSBH,C.YSZBH0,C.YKSBH0,C.YYSZBH,
        C.YSZMC0,C.THYY00,C.GDRQ00,C.GDSJ00 , B.YBMC00,A.RZKS00,D.BMMC00,A.CYDJRQ,  
  SF_BQ_CSRQTONL(a.CSRQ00) BRNL00,    --病人年龄
  --to_char(greatest(decode(nvl(sjcyrq,'20991231'),'20991231',to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd'),to_date(a.sjcyrq,'yyyymmdd'))-to_date(a.RYRQ00,'yyyymmdd'),1))||'天' RYTSMS, --住院天数描述
  decode(substr(a.BRZT00,1,1),'2',
    to_char(greatest(to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd')-to_date(a.RYRQ00,'yyyyMMdd'),1))||'天',
    substr(a.sjcyrq,1,4)||'-'||substr(a.SJCYRQ,5,2)||'-'||substr(a.SJCYRQ,7,2)
    ) as RYTSMS, --住院天数描述
  (select ' '||x.SSMC00 from VW_SS_YW0000 x where x.ZYGHID(+)=a.ZYID00 and x.MZZYBZ='1' and x.SSZT00 not in ('1','3') 
   and x.SSQSRQ=(select trim(max(y.SSQSRQ)) from SS_YW0000 y where y.ZYGHID(+)=x.ZYGHID and y.MZZYBZ='1' 
   and y.SSZT00 not in ('1','3')) and rownum=1) ssmc00, --手术名称
  (select decode(trim(max(x.SSQSRQ)),null,null,((to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')-to_date(trim(max(x.SSQSRQ)),'yyyymmdd')))) 
   from SS_YW0000 x where x.ZYGHID(+)=a.ZYID00 and x.MZZYBZ='1' and x.SSZT00 not in ('1','3')) SSTS00,  --手术天数
  (select y.ZLXMJC from BM_ZLZD00 y where y.ZLXMID(+)=a.HLJBID) HLJB00,   --护理级别
  (select decode(substr(trim(y.ZLXMJC),1,1),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',substr(trim(y.ZLXMJC),1,1))  from BM_ZLZD00 y where y.ZLXMID(+)=a.HLJBID) HLJBJC,   --护理级别简称
  (SELECT ZDMC00 FROM VW_DZBL_BRZDXX_ONE WHERE ZYID00=a.ZYID00 And Rownum=1) ZDMC00,  --诊断名称 
  a.DQBQ00,   --当前病区
  G.SQKS00 HZSQKS,    --申请科室
  G.SQYS00 HZSQYS,    --申请医生
  (SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00(+)=G.SQYS00) SQYSXM,    --申请医生姓名
  G.TJRQ00 HZSQRQ,    --申请日期
  G.TJSJ00 HZSQSJ,    --申请时间
  DECODE(e.SYKSBH,NULL,NULL,';'||e.SYKSBH||';') HZKS00,    --会诊科室(多科室用";"隔开)
  (SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=E.SYKSBH) HZKSMC,    --会诊科室名称  
  DECODE(e.SYYGBH,NULL,NULL,'-1',null,';'||e.SYYGBH||';') HZYS00,  --会诊医生(多医生用";"隔开)
  (SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=e.SYYGBH) HZYSXM,    --会诊医生姓名
    e.ZT0000 HZJSBZ,    --会诊结束标志'1'结束 '0'未结束  
    f.YSZID0,       --医生组ID
    f.SSKSBH,       --所属科室编号
    f.SSKSMC,       --所属科室名称
    f.YSZZT0,        --医生组状态
    decode((select count(1) from LJ_ZRZC00 where nvl(JSFS00,' ')=' ' and zyid00=a.zyid00),0,'0','1') SFLJBR--是否路径病人1是0否
from ZY_BRXXB0 A,ZS_BLXX01 C, IC_YBBRLB B  ,BM_BMBM00 D, zs_hzsymx E, ZS_YSZ000 F,zs_hzsq00 G
 where A.ZYID00=C.ZYID00 and A.ZYID00=G.ZYID00 AND E.HZID00=G.ID0000 and A.YBLB00=B.YBLB00 and C.YSZBH0=F.YSZID0 and A.FBBH00=B.FBBH00 
   and A.RZKS00=D.BMBH00 and C.BLZT00 IN ('1','2','5')