CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YY_BRYYXXCX
 (
 BRNL00, FBBH00, BRXB00, BRJTDZ, BRCSRQ, YYID00, JZID00, GHHBID, YYH000, 
BRID00, BRXM00, BRDH00, KMH000, ICKH00, YYKSBH, YYKSMC, YYYSBH, YYYSXM, 
YYLB00, YYRQ00, YYJSSJ, YYZT00, YYYGBH, YYYGXM, DJH000, GHLB00, DJRQ00, 
DJSJ00, QXRQ00, QXSJ00, BZ0000, CZYBM0, HZDYCS, SFZH00, SFSQZZ, QXR000, 
WLYYID, TZBZ00, GHLBMC, YYLBMC, GHHZCK, QXRXM0, SFCZ00

 )
 AS 
Select  "BRNL00","FBBH00","BRXB00","BRJTDZ","BRCSRQ","YYID00","JZID00","GHHBID","YYH000","BRID00","BRXM00","BRDH00","KMH000","ICKH00","YYKSBH","YYKSMC","YYYSBH","YYYSXM","YYLB00","YYRQ00","YYJSSJ","YYZT00","YYYGBH","YYYGXM","DJH000","GHLB00","DJRQ00","DJSJ00","QXRQ00","QXSJ00","BZ0000","CZYBM0","HZDYCS","SFZH00","SFSQZZ","QXR000","WLYYID","TZBZ00","GHLBMC","YYLBMC","GHHZCK","QXRXM0","SFCZ00" from (
select substr(SF_SF_CSRQTONL(trim(b.BRCSRQ)),1,10) BRNL00,
  b.FBBH00,b.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,--预约日期
  a.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,--取消时间
  a.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) GHLBMC,
  decode(a.YYLB00,'0','电话','1','分诊台','网络' ) YYLBMC,c.GHHZCK,
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.QXR000 ) 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') SFCZ00
 from SF_YYBRB0 a,BM_BRXXB0 b,SF_YSGHHB c
 where A.BRID00=B.BRID00
 and a.GHHBID=c.GHHBID
 and A.BRID00>0
union all
select '' BRNL00,1 FBBH00,'' BRXB00,'' BRJTDZ,'' 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,--预约日期
  a.YYJSSJ,--结束时间
  a.YYZT00,--预约状态 0 登记 1 确认 2 取消 3 就诊a
  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,--取消时间
  a.BZ0000,--备注
  a.CZYBM0, --操作员部门
  a.HZDYCS, --候诊打印次数
  a.SFZH00,--病人证件号码
  a.SFSQZZ,--是否社区转诊
  a.QXR000,--取消预约人
  a.WLYYID,--网络预约id,
  c.TZBZ00,(SELECT LBMC00 FROM BM_GHLBB0 WHERE LBBH00=a.GHLB00) GHLBMC,
decode(a.YYLB00,'0','电话','1','分诊台','网络' ) YYLBMC,c.GHHZCK,
(select ZWXM00 from BM_YGBM00 where YGBH00=a.QXR000 ) QXRXM0,
'0' SFCZ00
from SF_YYBRB0 A,SF_YSGHHB C 
 where a.GHHBID=c.GHHBID
 and A.BRID00=0
union all
select substr(SF_SF_CSRQTONL(trim(b.BRCSRQ)),1,10) BRNL00,
  b.FBBH00,b.BRXB00,b.BRJTDZ,b.BRCSRQ, 
  0 YYID00, --预约编号 SQ_SF_YYBRB0_YYID00.nextval  
  a.GHID00,--就诊挂号ID  SF_BRXXB0.GHID00
  0 YSPBID,--挂号号 SF_YSGHHB.GHHBID
  (SELECT count(*) from sf_brxxb0 where GHRQ00=a.GHRQ00 and GHKS00=a.GHKS00 and JZYS00=a.JZYS00 and SFCZ00=a.SFCZ00 and GHLB00<>-99999 and GHID00<=a.GHID00) YYH000,--a.YYH000,--预约号  
  a.BRID00,--病人编号 BM_BRXXB0.BRID00
  b.BRXM00,--病人姓名 BM_BRXXB0.BRXM00
  b.BRDH00,--电话号码  BM_BRXXB0.BRDH00
  (select KMH000 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and rownum=1) KMH000,--a.KMH000, --卡面号
  '' ICKH00,--就诊卡号 IC_YHXX00.ICKH00
  a.GHKS00 YYKSBH,--a.YYKSBH,--预约科室编号  SF_YSGHHB.GHKSBH
  (select BMMC00 from BM_BMBM00 where BMBH00=a.GHKS00) YYKSMC ,--a.YYKSMC,--预约科室名称 
  a.JZYS00 YYYSBH,--a.YYYSBH,--预约医生编号 SF_YSGHHB.GHYSBH
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.JZYS00) YYYSXM,--a.YYYSXM,--预约医生姓名 
  '0' YYLB00,--预约类别编号 0电话预约 1分诊台预约 2网络预约
  a.GHRQ00 YYRQ00, --a.YYRQ00,--预约日期
  a.JZSJ00 YYJSSJ,--结束时间
  '3' YYZT00,--预约状态 0 登记 1 确认 2 取消 3 就诊
  a.GHY000 YYYGBH,--预约操作员编号 BM_YGBM00.YGBH00
  (Select ZWXM00 from BM_YGBM00 where a.GHY000=YGBH00) YYYGXM,--预约员工姓名
  a.DJH000,--单据号 SF_BRFY00.DJH000 
  a.GHLB00,--挂号类别 SF_YSGHHB.GHLBBH
  a.GHRQ00 DJRQ00,--登记日期
  to_char(to_date(a.GHRQ00||a.JZSJ00,'YYYYMMDDHH24:MI:SS')-1/24,'HH24:MI:SS') DJSJ00,--登记时间
  '' QXRQ00,--取消日期
  '' QXSJ00,--取消时间
  '' BZ0000,--备注
  GHKS00 CZYBM0, --操作员部门
  1 HZDYCS, --候诊打印次数
  b.BRZJBH SFZH00,--病人证件号码
  null SFSQZZ,--是否社区转诊
  null QXR000,--取消预约人
  0 WLYYID,--网络预约id
  '0' TZBZ00,--停诊标志    '0',正常'1'停诊
  (SELECT LBMC00 FROM BM_GHLBB0 WHERE LBBH00=a.GHLB00) GHLBMC,
  '电话' YYLBMC,
  0 GHHZCK,
  '' QXRXM0,
  '1' SFCZ00
 from SF_BRXXB0 a,BM_BRXXB0 b
 where A.BRID00=B.BRID00
 and not exists (select 1 from SF_YYBRB0 where YYRQ00=a.GHRQ00 and YYKSBH=a.GHKS00 and YYYSBH=a.JZYS00 and BRID00=a.BRID00)
 and GHLB00<>-99999 and a.SFCZ00='1'
)
order by DJRQ00,DJSJ00
/