CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_HZBR00
 (
 GHID00, BRBLH0, YBGHH0, GHLB00, SFCZ00, XM0000, XB0000, CSRQ00, FBBH00, 
LBBH00, GFZH00, GHRQ00, GHSJ00, GHY000, GHYXM0, GHKS00, JZRQ00, JZSJ00, 
JZKS00, JZYS00, JZYSXM, THRQ00, THSJ00, THY000, THYXM0, SFYY00, THBZ00, 
PJH000, GHYXTS, THPJH0, HZLB00, SFNLYD, HZSJN0, JZSJN0, HZSJD0, GHJE00, 
BRID00, GHH000, JZKSMC, GHYS00, GHYSXM, HZLBMC, HZXH00, HZXHMC, DJRQ00, 
DJSJ00, HZQSRQ, HZQSSJ, HZJZRQ, HZJZSJ, YZRQ00, YZSJ00, ZJRQ00, ZJSJ00, 
SFXZ00, HZZT00, CZY000, ID0000, DLXH00, NL0000, HZSJ00, SFZZ00, PYSM00, 
WBSM00, YSPBID, DGJZSJ, JHCS00, PBLX00, GHHZCK, KSPBFZ, PBFZMC, HZCKMC, 
JHJZSJ, YYSJC0, BJBZ00, SJHZCK, XZBZ00, GHLBMC, YYID00, GHXHS0, BZ0000, 
XZTS00, GHQSSJ, GHJSSJ, SFJZFP, GHKSMC, GHKSDZ, HZXZ00

 )
 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,
   a.GHYXM0,a.GHKS00,a.JZRQ00,a.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.HZSJN0,a.JZSJN0,a.HZSJD0,a.GHJE00,
   b.BRID00,b.GHH000,b.JZKSMC,b.GHYS00,b.GHYSXM,
   b.HZLBMC,b.HZXH00,decode(b.HZXZ00,'1',b.HZXHMC||'(单)',b.HZXHMC) 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,
   nvl((select 1 from XT_XTCS00 where NAME00='MZHS_ZJGHBRSFXS' and VALUE0='Y' and b.HZXH00=0),b.DLXH00) DLXH00,
   SF_BQ_CSRQTONL(a.CSRQ00) NL0000,
   substr(b.HZQSSJ,1,5)||'-'||substr(b.HZJZSJ,1,5) "HZSJ00",SFZZ00,
   c.PYSM00,c.WBSM00,b.YSPBID,b.HZSJD0 DGJZSJ,nvl(b.JHCS00,0) JHCS00,
   nvl(d.PBLX00,0) PBLX00,
   d.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(HZQSRQ||HZQSSJ,'YYYYMMDDHH24:MI:SS')+(NVL(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,
     decode((select nvl(max(trim(VALUE0)),'N') from XT_XTCS00 where NAME00='YS_YYBRYX' and VALUE0='Y'),'Y',
       decode(YYBZ00,'6',ceil((to_date(b.HZQSRQ||b.YYSJ00,'YYYYMMDDHH24:MI:SS')-to_date(TO_char(sysdate, 'YYYYMMDDHH24:MI:SS'),'YYYYMMDDHH24:MI:SS'))* 24 * 60),nvl(d.PJZZSJ,10))
       ,d.PJZZSJ) YYSJC0, --时间差
       b.BJBZ00,
       decode(nvl(b.GHHZCK,d.GHHZCK),0,d.GHHZCK) SJHZCK,
       decode(b.SFXZ00,'1','续诊',' ') XZBZ00,
       replace(replace(replace(d.GHLBMC,'号',''),'医师',''),'急诊','') GHLBMC,
     d.yyid00,
     d.ghxhs0,--挂号限号数
     b.bz0000,
     decode(b.SFXZ00,'1','以排队叫号为准!',' ') XZTS00,
    d.GHQSSJ,d.GHJSSJ,
    decode(a.SFJZFP,'Y','精准扶贫','') SFJZFP,
    (select BMMC00 from BM_BMBM00 where BMBH00=b.GHKS00 and rownum=1) GHKSMC,
    (select ckxxdz from bm_ckbm00 where ckbh00=d.ghhzck) GHKSDZ,
    b.HZXZ00
  from sf_brxxb0 a,sf_hzbr00 b,BM_BRXXB0 c,SF_YSGHHB d
   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 ((select nvl(max(trim(VALUE0)),'N') from XT_XTCS00 where NAME00='YS_SFDCQRCXS')<>'Y' 
             or (nvl(b.YYID00,0)=0 or exists(select 1 from SF_YYBRB0 where GHHBID=b.YSPBID and YYID00=b.YYID00 and nvl(HZDYCS,0)>0))
          )
/