create or replace view vw_yy_bryyxx as
select
  substr(SF_XT_CSRQTONL(trim(b.BRCSRQ)),1,10) BRNL00,
  b.FBBH00,
  --nvl(b.BRXB00,'未知'),
  (select h.BRXB00 from bm_brxxb0 h,ic_yhxx00 i where a.KMH000=i.kmh000 and i.zt0000='1' and h.brid00=i.brid00 and rownum=1) BRXB00,
  b.BRJTDZ,
  b.BRCSRQ,
  a.YYID00,                                                         --预约编号 SQ_SF_YYBRB0_YYID00.nextval
  a.JZID00,                                                         --就诊挂号ID  SF_BRXXB0.GHID00
  a.GHHBID,                                                         --挂号号 SF_YSGHHB.GHHBID
  a.YYH000,                                                         --预约号
  a.BRID00,                                                         --病人编号 BM_BRXXB0.BRID00
  a.BRXM00,                                                         --病人姓名 BM_BRXXB0.BRXM00
  a.BRDH00,                                                         --电话号码  BM_BRXXB0.BRDH00
  a.KMH000,                                                         --卡面号
  a.ICKH00,                                                         --就诊卡号 IC_YHXX00.ICKH00
  a.YYKSBH,                                                         --预约科室编号  SF_YSGHHB.GHKSBH
  a.YYKSMC,                                                         --预约科室名称
  a.YYYSBH,                                                         --预约医生编号 SF_YSGHHB.GHYSBH
  a.YYYSXM,                                                         --预约医生姓名
  a.YYLB00,                                                         --预约类别编号 0电话预约 1分诊台预约 2网络预约
  a.YYRQ00,                                                         --预约日期
  substr(a.YYJSSJ,1,5) as YYJSSJ,
  a.YYZT00,                                                         --预约状态 0 登记 1 确认 2 取消 3 就诊
  a.YYYGBH,                                                         --预约操作员编号 BM_YGBM00.YGBH00
  a.YYYGXM,                                                         --预约员工姓名
  a.DJH000,                                                         --单据号 SF_BRFY00.DJH000
  a.GHLB00,                                                         --挂号类别 SF_YSGHHB.GHLBBH
  a.DJRQ00,                                                         --登记日期
  a.DJSJ00,                                                         --登记时间
  a.QXRQ00,                                                         --取消日期
  a.QXSJ00,                                                         --取消时间
  substrb(decode(a.VIP000,'Y','VIP','')||a.BZ0000,1,200) as BZ0000, --备注
  a.CZYBM0,                                                         --操作员部门
  a.HZDYCS,                                                         --候诊打印次数
  a.SFZH00,                                                         --病人证件号码
  a.SFSQZZ,                                                         --是否社区转诊
  a.QXR000,                                                         --取消预约人
  a.WLYYID,                                                         --网络预约id
  c.TZBZ00,                                                         --停诊标志    '0',正常'1'停诊
  (select LBMC00 from BM_GHLBB0 where LBBH00=a.GHLB00) as GHLBMC,
  decode(a.YYLB00,'0','电话','1','分诊台','网络' ) as YYLBMC,
  c.GHHZCK,
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.QXR000 ) as QXRXM0,
  nvl((select SFCZ00 from SF_BRXXB0 where JZKS00=a.YYKSBH and JZYS00=a.YYYSBH and JZRQ00=a.YYRQ00 and BRID00=a.BRID00 and rownum=1),'0') as SFCZ00,
  a.WLYYMC,                                                         --预约公司名称
  decode(to_char(to_date(a.YYRQ00,'yyyymmdd'),'D'),1,'星期日',2,'星期一',3,'星期二',4,'星期三',5,'星期四',6,'星期五',7,'星期六') as YYRQ01,
  c.YYID00 as YYID01,                                               --医院ID00
  (select YYBZ10 from XT_YYXX00 where YYID00=c.YYID00 and rownum=1) as YYJC00,  --医院简称
  a.GHCJ00                                                          --挂号厂家
from SF_YYBRB0 a,BM_BRXXB0 b,SF_YSGHHB c
where A.BRID00 = B.BRID00(+)
  and a.GHHBID = c.GHHBID