create or replace view vw_ys_hzbr00 as
select
a.GHID00,a.BRBLH0,a.YBGHH0,a.GHLB00,DECODE(a.SFCZ00,'0','初','复')SFCZ00,a.XM0000||DECODE(A.SFZGH0,'1','/转诊','') XM0000, --如果是转诊病人后面加个‘/转诊’
a.XB0000,a.CSRQ00,a.FBBH00,a.LBBH00,a.GFZH00,a.GHRQ00,a.GHSJ00,a.GHY000,
case when a.GHY000=0 and d.PBLX00='1' then '科室医生' else a.GHYXM0 end GHYXM0,
a.GHKS00,a.JZRQ00,a.JZSJ00,a.JZKS00,a.JZYS00,
case when a.JZYS00=0 and d.PBLX00='1' then '科室医生' else a.JZYSXM end JZYSXM,a.THRQ00,
a.THSJ00,a.THY000,a.THYXM0,a.SFYY00,a.THBZ00,a.PJH000,a.GHYXTS,a.THPJH0,
a.HZLB00,a.SFNLYD,a.HZSJN0,a.JZSJN0,a.HZSJD0,a.GHJE00,
b.BRID00,b.GHH000,b.JZKSMC,b.GHYS00,
case when b.GHYS00=0 and d.PBLX00='1' then '科室医生' else b.GHYSXM end GHYSXM,
b.HZLBMC,b.HZXH00,b.HZXHMC,b.DJRQ00,b.DJSJ00,b.HZQSRQ,b.HZQSSJ,
b.HZJZRQ,b.HZJZSJ,b.YZRQ00,b.YZSJ00,b.ZJRQ00,b.ZJSJ00,b.SFXZ00,
b.HZZT00,b.CZY000,b.ID0000,b.DLXH00,SF_BQ_CSRQTONL(a.CSRQ00) NL0000,
--substr(b.HZQSSJ,1,5)||'-'||substr(b.HZJZSJ,1,5) "HZSJ00",
-- f.yyjssj HZSJ00,
--b.DJSJ00 HZSJ00,
e.hzqssj HZSJ00,
SFZZ00,
c.PYSM00,c.WBSM00,b.YSPBID,b.HZSJD0 DGJZSJ,nvl(b.JHCS00,0) JHCS00,
nvl(d.PBLX00,0) PBLX00,
b.GHHZCK,nvl(b.KSPBFZ,'0') KSPBFZ,decode(b.KSPBFZ,'1','快捷组','普通组') PBFZMC,
(select CKMC00 from BM_CKBM00 where CKBH00=b.GHHZCK) HZCKMC,

least(to_char(to_date(B.HZQSRQ||B.HZQSSJ,'YYYYMMDDHH24:MI:SS')+(NVL(B.HZXH00,0)-NVL(d.HZQSXH,0))*NVL(d.PJZZSJ,0)/24/60,'HH24:MI:SS'),
     to_char(to_date(d.GHMZSJ||d.GHJSSJ,'YYYYMMDDHH24:MI:SS')-1/96,'HH24:MI:SS') )  JHJZSJ,
replace(replace(replace(d.GHLBMC,'号',''),'医师',''),'急诊','') GHLBMC,
d.yyid00,
d.ghxhs0,--挂号限号数
b.bz0000,--备注
--E.HZSJ00 HZSD00,--时段 
E.HZQSSJ HZSD00, 
decode(nvl(F.yylb00,' '),'0','复诊预约','1','分诊台预约','2','网上/电话','3','自助机预约','4','微信预约',decode(nvl(E.brid00,0),0,'','现场取号')) yylbmc,
DECODE (SIGN (INSTRB (',' || D.wlyymx || ',', ',' || E.hzxh00 || ',')),1, '网络号',DECODE (SIGN (INSTRB (',' || D.ynblmx || ',', ',' || E.hzxh00 || ',')),1,'复诊预约','现场号' )) hyfhfs,
c.BRSFHY,
(select ZWXM00 from BM_YGBM00 where YGBH00=b.CZY000) CZYXM0 
from sf_brxxb0 a,sf_hzbr00 b,BM_BRXXB0 c,SF_YSGHHB d,YS_HBHZXH E,SF_YYBRB0 F
where a.ghh000=b.ghh000 and a.THBZ00<>'0' and b.HZZT00 IN ('0','1','2','4','5')and
      a.ghrq00>to_char(sysdate-10,'yyyymmdd') and --b.hzqsrq>to_char(sysdate-2,'yyyymmdd') and
      trunc(sysdate-1/4)-a.GHYXTS <to_date(a.ghrq00,'yyyymmdd') and b.hzqsrq=to_char(sysdate,'yyyymmdd')
      and a.BRID00=c.BRID00 and b.YSPBID=d.GHHBID(+) AND E.ghhbid=D.ghhbid and e.hzxh00=b.hzxh00 AND E.yyid00=F.yyid00(+)
      and not exists (select 1 from sf_yybrb0 where brid00=b.brid00 and yyrq00=to_char(sysdate,'yyyymmdd') and qxrq00 is not null  and yyh000=b.hzxh00 and yyysbh=b.ghys00 and yyzt00='2')
;