-- Start of DDL Script for View SD_HOSPITAL.VW_YS_CFDY
-- Generated 2020-02-28 10:45:41 from SD_HOSPITAL@30

CREATE OR REPLACE VIEW vw_ys_cfdy (
   cflsh0,
   ghid00,
   ghh000,
   brid00,
   brxm00,
   xb0000,
   brcsrq,
   nl0000,
   ghks00,
   ghksmc,
   ysgzh0,
   kdysxm,
   yfbmbh,
   yfbmmc,
   brtz00,
   cfzje0,
   psjg00,
   ph0000,
   zj0000,
   ghrq00,
   ghsj00,
   ghsjd0,
   zdmc00,
   qtzdmc,
   gmsy00,
   cfdgs0,
   brlxdh )
AS
select a.CFLSH0,a.GHID00,a.GHH000,a.BRID00,a.BRXM00,a.XB0000,c.BRCSRQ,SF_XT_CSRQTONL(c.BRCSRQ) NL0000,
b.GHKS00,(select BMMC00 from BM_BMBM00 where BMBH00=b.GHKS00)GHKSMC,
a.YSGZH0,(select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0)KDYSXM,
a.YFBMBH,(select BMMC00 from BM_BMBM00 where BMBH00=a.YFBMBH)YFBMMC,
(select to_char(tz0000) from (select ID0000,BRID00,TW0000,MB0000,HX0000,SZY000,SSY000,SG0000,TZ0000,CZY000,CZRQ00,CZSJ00,SJLY00,XYBHD0,TTPG00 from Ys_brtzxx t where TZ0000 > 0 order by ID0000 desc) t where t.BRID00=a.BRID00 and rownum=1) BRTZ00,
a.CFZJE0,
'' PSJG00,--皮试结果
'' PH0000,--批号
'' ZJ0000,--诊间名称
b.GHRQ00,b.GHSJ00,b.GHSJD0,(select ZDMC00 from YS_BRZDXX where ZZDBZ0 ='3' and GHID00=b.GHID00 and rownum=1) ZDMC00,
(select to_char(wm_concat(ZDMC00)) from YS_BRZDXX where ZZDBZ0 ='4' and GHID00=b.GHID00) QTZDMC,
(select YWGM00||FYWGM0 from YS_MZBLWS where GHID00=a.ghid00
and wszt00<>'3'
and blxmbh in(select blxz00 from ys_blzcxm a where a.flid00='1')) GMSY00, --过敏试验,
--d.YWGM00||d.FYWGM0 GMSY00, --过敏试验
a.CFDGS0, --麻/精标注
nvl(c.BRLXDH,BRDH00) BRLXDH --病人联系电话
from YS_CFXXB0 a,SF_BRXXB0 b,BM_BRXXB0 c
where a.GHID00=b.GHID00
and b.BRID00=c.BRID00
/


-- End of DDL Script for View SD_HOSPITAL.VW_YS_CFDY