create or replace view vw_zs_wgdbl0 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, 
       sf_zs_getybmc(e.BRZTQK,B.YBJGJC,B.Ybmc00) YBMC00,
       A.RZKS00,
       D.BMMC00,
       A.CYDJRQ,
       A.ZZYSXM,
       A.SXYSXM,
       A.ZRYSXM,
       A.RYSQK0,
       decode((select count(1) from xt_yyxx00 where yyid00='226523'),0,(select SF_ZS_CSRQSJTONL(BRCSRQ ,BRCSSJ,SJRYRQ) from BM_BRXXB0 where BRID00=a.BRID00),(select sf_bq_csrqsjtonl(A.CSRQ00,BRCSSJ) from BM_BRXXB0 where BRID00=a.BRID00)) BRNL00,   --病人年龄(漳浦妇幼特殊处理)
      --(select SF_ZS_CSRQSJTONL(BRCSRQ ,BRCSSJ,SJRYRQ) from BM_BRXXB0 where BRID00=a.BRID00) BRNL00,    --病人年龄
       --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(C.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),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',decode(substr(trim(Y.ZLXMJC),1,3),'I级护','Ⅰ','II级','Ⅱ','III','Ⅲ','IV级','Ⅳ',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,  --诊断名称
       --(select ZDMC00 from (select ZDMC00,ZYID00 from BQ_BRZDXX where ZDLB00='8' order by ZDRQ00,ZDSJ00,PLXH00 ) where ZYID00=A.ZYID00 and rownum=1) XYCBZD,--西医初步诊断
       --(select ZDMC00 from (select ZDMC00,ZYID00 from BQ_BRZDXX where ZDLB00='h' order by ZDRQ00,ZDSJ00,PLXH00 ) where ZYID00=A.ZYID00 and rownum=1) ZYCBZD,--中医初步诊断
       (select ZDMC00 from BQ_BRZDXX where ZYID00=A.ZYID00 and ZDLB00='8' and plxh00 = '1' and  rownum=1) XYCBZD,--西医初步诊断
       (select ZDMC00 from BQ_BRZDXX where ZYID00=A.ZYID00 and ZDLB00='h' and plxh00 = '1' and rownum=1) ZYCBZD,--中医初步诊断
       A.DQBQ00,   --当前病区
       (select BRZJBH from BM_BRXXB0 where BRID00=A.BRID00) BRZJBH, --病人身份证

       decode(
       nvl((select value0 from xt_xtcs00  where name00='ZS_SFXSYWCLJ'),'Y'),
       'N',
    nvl((select max(decode(JSFS00,'',LJID00,0)) from LJ_ZRZC00 where ZYID00=A.ZYID00 ),0),
    nvl((select max(decode(JSFS00,'',LJID00,-1)) from LJ_ZRZC00 where ZYID00=A.ZYID00 and (nvl(JSRQ00,' ')=' ' or JSFS00 in('完成','强制退出'))),0)
       )
       as DQLJID,--当前路径id rbh2011-11-04 增加,huanglw 2018-06-19修改
       
    SF_BQ_KYJE00_ZYDZBL(A.ZYID00,A.BRID00)  KYJE00,
       (select YPMC00  from YJ_BRGMJL t where t.BRID00(+)=A.BRID00  and rownum=1) BRGMJL,
       C.RYRQ00 BLRYRQ,--ZS_BLXX01.RYRQ00
       a.BRRYCS, 
      (select bmmc00 from BM_BMBM00 where    a.dqbq00=BMBH00 and rownum<=1) DQBQMC ,    --当前病区
       decode((select count(1) from LJ_ZRZC00 where nvl(JSFS00,' ')=' ' and ZYID00=A.ZYID00),0,'0','1') SFLJBR,--是否路径病人1是0否
      a.DBZBM0,   --单病种编码
      a.DBZMC0,   --单病种名称
      a.ZRZYBZ,
      decode(B.YBZXLB,'1',decode(B.YBLB00,'1','省医保中心','异地医保'),'2','福州市医保中心','3','福建省两费中心','4','福建省保健办','Z','铁路医保中心','@','福州市新农合中心','h','省市特殊人群',B.YBMC00) YBDZX0, --医保大中心
      A.SFJZFP,--是否精准扶贫 1：是，0：否
     A.SJCYRQ
from ZY_BRXXB0 A,ZS_BLXX01 C, IC_YBBRLB B  ,BM_BMBM00 D,bm_brxxb0 E
 where A.ZYID00=C.ZYID00 and A.YBLB00=B.YBLB00 and A.FBBH00=B.FBBH00 and A.RZKS00=D.BMBH00 and A.BRID00=E.BRID00
     and BLZT00 in ('1','2','5') and A.BRZT00<>'9' and A.BRZT00<>'1'