-- Start of DDL Script for View SD_HOSPITAL.VW_YS_BRJBDJ_MZDZBL
-- Generated 01-9月-2021 15:54:35 from SD_HOSPITAL@ZHXYYGL

CREATE OR REPLACE VIEW vw_ys_brjbdj_mzdzbl (
   ghid00,
   brblh0,
   ghh000,
   ybghh0,
   ghlb00,
   brid00,
   xm0000,
   xb0000,
   csrq00,
   fbbh00,
   lbbh00,
   gfzh00,
   ghrq00,
   ghsj00,
   ghy000,
   ghyxm0,
   ghks00,
   jzrq00,
   jzsj00,
   jzks00,
   jzys00,
   jzysxm,
   thrq00,
   thsj00,
   thy000,
   thyxm0,
   sfyy00,
   thbz00,
   pjh000,
   ghyxts,
   thpjh0,
   hzlb00,
   sfnlyd,
   hzsjd0,
   hzsjn0,
   jzsjn0,
   ghje00,
   brnl00,
   brjtdz,
   brdh00,
   brzy00,
   brztqk,
   brlxxm,
   lbmc00,
   fbmc00,
   ghksmc,
   jzksmc,
   fbrq00,
   sfcz00,
   yqbgbz,
   jbmc00,
   sfcrb0,
   zdrq00,
   bbrq00,
   yblb00,
   brlxdz,
   brxy00,
   xyzdmc,
   zyzdmc,
   wdwid0,
   brzjbh,
   tsbzbh,
   ssy000,
   szy000,
   tsbzmc )
AS
select
  A.GHID00,  A.BRBLH0,  A.GHH000,  A.YBGHH0,  A.GHLB00,
  A.BRID00,  A.XM0000,  A.XB0000,  A.CSRQ00,
  A.FBBH00,  A.LBBH00,  A.GFZH00,  A.GHRQ00,  A.GHRQ00||' '||A.GHSJ00 GHSJ00,
  A.GHY000,  A.GHYXM0,  A.GHKS00,  A.JZRQ00,  A.JZRQ00||' '||A.JZSJ00 JZSJ00,
  A.JZKS00,  A.JZYS00,  A.JZYSXM,  A.THRQ00,  A.THSJ00,
  A.THY000,  A.THYXM0,  A.SFYY00,  A.THBZ00,  A.PJH000,
  A.GHYXTS,  A.THPJH0,  A.HZLB00,  A.SFNLYD,  A.HZSJD0,
  A.HZSJN0,  A.JZSJN0,  A.GHJE00,  substr(SF_BQ_CSRQTONL(A.CSRQ00),1,8) AS BRNL00,
  substrb(B.BRJTDZ,1,60) BRJTDZ,  nvl(B.BRLXDH,B.BRDH00) BRDH00,  B.BRZY00, B.BRZTQK,B.BRLXXM,
  (select LBMC00 from BM_GHLBB0 b where a.GHLB00=b.LBBH00 and rownum=1) as LBMC00,
  (select BRLBJC from BM_YBBRLB c,IC_YBBRLB d where D.FBBH00=B.FBBH00 and d.YBLB00=b.YBLB00 and c.YBBRLB=b.YBBRLB and c.YBZXLB=d.YBZXLB AND c.FBBH00=b.FBBH00 and rownum=1) as FBMC00,
  (select BMMC00 from BM_BMBM00 d where a.GHKS00=d.BMBH00 and rownum=1) as GHKSMC,
  (select BMMC00 from BM_BMBM00 e where a.JZKS00=e.BMBH00 and rownum=1) as JZKSMC,
  nvl(c.FBRQ00,TO_CHAR((TO_DATE(A.GHRQ00,'YYYYMMDD')-1),'YYYYMMDD')) as FBRQ00,
  (select decode(SFCZ00,'0','初诊','1','复诊') from SF_BRXXB0 f where a.GHID00=f.GHID00 and rownum=1) as SFCZ00,
  nvl(decode((select 1 from YS_CRBDJ0 f where a.GHID00=f.MZID00 and rownum=1),'1','报告','未报'),'未报') as YQBGBZ,
  nvl((select nvl(h.zdmc00,g.JBMC00) from XT_ICD900 g,YS_BRZDXX h where  h.ZZDBZ0='3' and
              a.GHID00=h.GHID00 and g.ICD900(+)=h.ICD900 and rownum=1 ),
  (select nvl(h.zdmc00,g.JBMC00) from XT_ICD900 g,YS_BRZDXX h where
  a.GHID00=h.GHID00 and g.ICD900(+)=h.ICD900 and rownum=1 )) as JBMC00,
  (select nvl(max(G.SFCRB0),'0') from XT_ICD900 g,YS_BRZDXX h where
  a.GHID00=h.GHID00 and g.ICD900(+)=h.ICD900) as SFCRB0,--是否传染病 '0'是 '1'不是
  nvl((select h.ZDRQ00 from YS_BRZDXX h where h.ZZDBZ0='3' and a.GHID00=h.GHID00 and rownum=1 ),
      (select h.ZDRQ00 from YS_BRZDXX h where a.GHID00=h.GHID00 and rownum=1 )) as ZDRQ00,a.BBRQ00,
  trim(b.YBLB00) as YBLB00, B.BRLXDZ,
  (select decode(SSY000||'/'||SZY000||' m','/ m','',SSY000||'/'||SZY000||' m') from YS_BRBQB0 where GHID00=a.GHID00 and rownum=1) as BRXY00,
  SF_YF_GETXYZYZD(a.GHH000,'1') xyzdmc,
  SF_YF_GETXYZYZD(a.GHH000,'0') zyzdmc,
  b.WDWID0,b.BRZJBH,A.TSBZBH,C.SSY000,C.SZY000,
  (select MC0000 from BM_TSBZB0 where BH0000=A.TSBZBH and rownum=1) TSBZMC--特殊病种名称
from SF_BRXXB0 a,BM_BRXXB0 b,YS_BRBQB0 c
where a.BRID00=b.BRID00 and GHLB00<>-99999  and a.GHH000  not like 'QJ%' and a.GHH000  not like 'TJ%'  and c.GHID00(+) = a.GHID00
/


-- End of DDL Script for View SD_HOSPITAL.VW_YS_BRJBDJ_MZDZBL