PROMPT 35.病人疾病登记表(VW_YS_BRJBDJ)
create or replace view VW_YS_BRJBDJ as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGY      2007.06.19  是否初诊：取SF_BRXXB0里的SFCZ00
-- YANGY      2007.06.19  取主要诊断，如果没有主要诊断才取其他诊断
-- csf        2008.10.10  家庭地址取数据重复
-- csf        2008.10.14  取出是否传染病标志
-- CSF        2008.12.10  增加抱病日期字段
-- qks        2009.05.07  增加YBLB00字段
-- chenqw     2009.11.30  疫情报告标志通过GHH000来关联
-- csf        2010.02.20  用参数控制是否查询预挂号
-- JXQ        2010.03.31  Add Filed: B.BRLXDZ
-- zhangwz    2010.07.15  增加字段BRXY00(病人血压00) by MZYS-20100712-001
-- daihq      2014.04.22  增加病人的特约单位名称（BM_BRXXB0.TYDWMC) by MZYS-20140404-004
-- dsm        2015.12.14  增加WBBR00 for MZYS-20151119-002
-- dsm        2016.11.11  增加BRZJBH for MZYS-20161109-001
-- chb        2020.02.20  修改BRNL00 for MZYS-20191219-001
-- huangjy    2020.11.11  增加参数ICD900显示 for MZYS-20201030-001
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, A.JZRQ00),1,8) AS BRNL00,
  substrb(B.BRJTDZ,1,60) BRJTDZ,  nvl(B.BRLXDH,B.BRDH00) BRDH00,  B.BRZY00, 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((select fbrq00 from ys_brbqb0 f where a.ghid00=f.ghid00 and rownum=1),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((select decode(yqbgbz,'1','报告','未报')     from ys_brbqb0 f where a.ghh000=f.ghh000 and rownum=1),'未报') as YQBGBZ,
  substrb(NVL((select nvl(h.zdmc00,g.JBMC00)||decode((select upper(trim(VALUE0)) from xt_xtcs00 where name00='YS_BSSFXSICD'),
   'Y','('||g.ICD900||')','') 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)||decode((select upper(trim(VALUE0)) from xt_xtcs00 where name00='YS_BSSFXSICD'),'Y','('||g.ICD900||')','') 
    from XT_ICD900 g,YS_BRZDXX h where a.GHID00=h.GHID00 and g.ICD900(+)=h.ICD900 and rownum=1 )),1,100) as JBMC00,
  NVL((select nvl(g.SFCRB0,'0') 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(g.SFCRB0,'0') from XT_ICD900 g,YS_BRZDXX h where
  a.GHID00=h.GHID00 and g.ICD900(+)=h.ICD900 and rownum=1 )) 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,
  b.TYDWMC TYDWMC,nvl(a.WBBR00,'N') WBBR00,
  b.BRZJBH
from sf_brxxb0 a,bm_brxxb0 b
where a.brid00=b.brid00 and ( ( a.GHLB00<>-99999 and exists(select 1 from xt_xtcs00 where trim(name00)='YS_BSJGZLTJSFBKYGH' and trim(VALUE0)='N') )
    or ( not exists(select 1 from xt_xtcs00 where trim(name00)='YS_BSJGZLTJSFBKYGH' and trim(VALUE0)='N') ) ) ;