CREATE VIEW SD_HOSPITAL.VW_YF_MZXYDY
 (
 CFLSH0, CFID00, CFXH00, CFMX00, BTXX00, JE0000

 )
 AS 
select X.CFLSH0,X.CFID00,X.CFXH00,substrb(X.CFMX00,1,200),' ',je0000
       from VW_YF_MZXYCF X
union all
select a.CFLSH0,c.NO0000,3,
decode(c.NO0000,
0,
'福建医科大学附属第二医院',
-1,
rpad('药　房:'||Trim(b.BMMC00),19,' ')||rpad('病历号:'||(select Trim(c.BRBLH0) from SF_BRXXB0 c where a.GHh000=c.GHH000),20,' '),
-2,
rpad('挂号号:'||Trim(a.GHH000),17.8,' ')||rpad('处方号:'||Trim(a.CFLSH0),20,' '),
-3,
rpad('姓名:'||Trim(a.BRXM00),13,' ')||rpad('性别:'||Trim(a.XB0000),8,' ')||
       rpad('年龄:'||Trim(SF_YF_CSRQTONL(a.CSRQ00,a.SRRQ00)),11,' ')||rpad((select Trim(d.FBMC00) from BM_BRXXB0 c,BM_BRFBB0 d where c.FBBH00=d.FBBH00 and   a.BRID00=c.BRID00 ),5,'
'),
-4,
rpad('科室:'||(select Trim(bmmc00) from bm_bmbm00 where bmbh00=a.ghks00),15,' ')||'超期理由:'||rpad(SF_YF_GETCQLY00(a.cflsh0),22,' ')
  ,
-5,
sf_py_brzxx(a.ghid00)||';'||'预约时间:'||z.yykbrq,--'预约'||sf_py_brzxx(a.ghid00)||chr(13)||chr(10),
-10,
'合计金额:',
-11,
'医    生:'||Trim(a.YSXM00),
-12,
'发药日期:'||to_char(to_date(a.FYRQ00,'YYYYMMDD'),'YYYY-MM-DD')||'   '||a.FYSJ00,
-13,
'打印日期:'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||'   '||TO_CHAR(SYSDATE,'HH24:MI:SS')),
decode(c.NO0000,-10,'发药人:'||(select Trim(d.ZWXM00) from BM_YGBM00 d where a.FYR000=d.YGBH00),
                -11,'复核人:'||(select Trim(c.ZWXM00) from BM_YGBM00 c where a.FHR000=c.YGBH00),' '),0
from sf_brxxb0 z,YF_MZCF00 a,BM_BMBM00 b,bm_number c where z.brid00=a.brid00 and a.YFBMBH=b.BMBH00 and c.no0000 in (0,-1,-2,-3,-4,-5,-10,-11,-12,-13)
/