create or replace view vw_bq_brzysq as
select
    a.ZYSQID ,
    a.BRID00 ,
    b.BRXM00 ,
    b.BRXB00 ,
    b.BRLXDH||decode(b.BRLXDH,null,null,' ')||replace(b.BRDH00,b.BRLXDH,'') as BRLXDH,   --病人联系电话
    b.BRZJBH,  --身份证号
    b.TYDWMC,  --单位名称
    b.BRLXDZ,  --病人联系地址
    substr(SF_XT_CSRQTONL(b.BRCSRQ,''),1,20) as NL0000,  --病人年龄
    b.FBBH00 ,
    (select FBMC00 from BM_BRFBB0 where FBBH00 = b.FBBH00) as FBMC00 ,
    substr(SF_ZY_GETFBXX00(b.FBBH00,c.YBZXLB,b.YBLB00,b.YBBRLB),1,50) as FBXX00 ,     --费别信息
    a.SQKS00 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.SQKS00) as SQKSMC ,
    a.YSGZH0 ,
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.YSGZH0) as SZYSXM ,
    a.ZGYS00 ,  --主管医生
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGYS00) as ZGYSXM , --主管医生姓名
    a.ZDMC00 ,
    a.ZDM000 ,
    a.SQRQ00 ,
    a.SQSJ00 ,
    a.BQH000 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.BQH000) as BQMC00 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.KSH000) as KSMC00 ,
    a.CH0000 ,
    a.ZYID00 ,
    decode(a.ZT0000,'0','门诊申请入院','1','门诊申请通过(无需预约)','2','预约未通知','3','预约已通知',
           '7','作废',decode((select substrb(BRZT00,1,1) from ZY_BRXXB0 where ZYID00=a.ZYID00),'0','预约','1','收费处已登记入院','2','在院','3','出院登记','4','已出院','5','出院欠费','9','取消入院','未定义')) as ZTMC00 ,  --申请状态
    a.YYZYRQ ,
    a.YYZYSJ ,
    a.YYZYRQ||' '||a.YYZYSJ as YYRQSJ ,   --预约住院日期时间
    a.TZYXRQ ,
    a.TZRQ00 ,
    a.TZSJ00 ,
    a.TZRQ00||' '||a.TZSJ00 as TZRQSJ ,  --通知日期时间
    a.YYSSRQ ,  --预约手术日期
    a.BQSZXZ ,  --病区收治选择
    decode(a.BQSZXZ,'0','本病区','1','仅限本病区所在大楼','2','无收治限制') as BQSZMC, --病区收治选择名称
    a.ZXDJBZ ,  --入院中心登记标志
    decode(a.ZXDJBZ,'1','检查','2','回家','3','住院通知') as ZXDJMC,  --入院中心登记名称
    nvl(a.ZRZYBZ,'0') as ZRZYBZ,   --择日住院标志
    decode(nvl(a.ZRZYBZ,'0'),'0','否','1','是') as SFZRZY,  --是否择日住院: ''0''否,''1''是
    a.YYDJBZ ,  --预约登记标志
    a.QXYY00 ,  --预约取消原因
    decode(a.ZXSQCG,'0','否','1','是') as SFCGJC,  --入院中心申请常规检查: ''0''否,''1''是
    decode(a.ZXTSJC,'0','否','1','是') as SFTSJC,  --入院中心特殊检查: ''0''否,''1''是
    nvl(a.RJSSBZ,'0') as RJSSBZ,  --日间手术标志 1:是，0否
    e.TZID00,                       --体征信息ID
    e.CLRQ00,                       --测量日期
    e.CLSJ00,                       --测量时间
    e.CLRQ00||' '||e.CLSJ00 as CLRQSJ,--测量日期时间
    e.TW0000,                       --体温
    e.TW0000||'°C' as TWXX00,       --体温信息
    e.MB0000,                       --脉搏
    e.MB0000||'次/min' as MBXX00,   --脉搏信息
    e.HX0000,                       --呼吸
    e.HX0000||'次/min' as HXXX00,   --呼吸信息
    e.XY0000,                       --血压
    e.XY0000||'mmHg' as XYXX00,     --血压信息
    e.SG0000,                       --身高
    e.SG0000||'cm' as SGXX00,       --身高信息
    e.TZ0000,                       --体重
    e.TZ0000||'kg' as TZXX00,       --体重信息
    e.DLBL00,                       --代理办理
    decode(e.DLBL00,'1','是','否') as DLBLMC,   --代理办理名称
    SF_XT_GETYBSXZD('ZYBRTZXX_RYFS',e.TZRYFS,'3','2',-1) as RYFSMC    --入院方式名称
from YS_BRZYSQ a,BM_BRXXB0 b,IC_YBBRLB c,ZY_BRTZXX e
  where a.BRID00 = b.BRID00
    and b.FBBH00 = c.FBBH00
    and b.YBLB00 = c.YBLB00
    and a.ZYSQID=e.ZYSQID(+)
;