-- Start of DDL Script for View SD_HOSPITAL.VW_YS_HZBR00
-- Generated 2014-6-30 15:08:57 from SD_HOSPITAL@DXCTEST1

CREATE OR REPLACE VIEW 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,
   sjhzck,
   bjbz00 )
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,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",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,
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')  JHJZSJ,
b.GHHZCK SJHZCK,b.bjbz00
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(+)
/


-- End of DDL Script for View SD_HOSPITAL.VW_YS_HZBR00