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

 )
 AS 
select X.CFLSH0,X.CFID00,0,decode(X.CFID00,1,X.YPMX00,rpad(X.YPMX00,23,' '))||' '||
       rpad((SELECT Z.YPMX00 FROM VW_YF_MZZYCF_MX0000 Z WHERE X.ROW000=Z.ROW000 AND Z.COL000=0),23,' '),' ',
       NVL(JE0000,0)+NVL((select JE0000 FROM VW_YF_MZZYCF_MX0000 Z WHERE X.ROW000=Z.ROW000 AND Z.COL000=0),0)
from   VW_YF_MZZYCF_MX0000 X
where  X.COL000=1
union all
SELECT CFLSH0,0,3,(SELECT trim(YYJC00) FROM XT_YYXX00 WHERE ROWNUM=1),'',0 FROM YF_MZCF00
union all
select a.CFLSH0,-1,3,rpad('药　房:'||Trim(b.BMMC00),19,' ')||rpad('病历号:'||(select Trim(c.BRBLH0) from SF_BRXXB0 c
where a.GHh000=c.GHH000),20,' '),' ',0
from YF_MZCF00 a,BM_BMBM00 b where a.YFBMBH=b.BMBH00
union all
select a.CFLSH0,-2,3,rpad('挂号号:'||Trim(a.GHH000),17.8,' ')||rpad('处方号:'||Trim(a.CFLSH0),20,' '),' ',0
from YF_MZCF00 a
union all
select a.CFLSH0,-3,3,rpad('姓名:'||Trim(a.BRXM00),13,' ')||rpad('性别:'||Trim(a.XB0000),10,' ')||
       rpad('年龄:'||Trim(SF_YF_CSRQTONL(a.CSRQ00,a.SRRQ00)),9,' '),
       '',0
from YF_MZCF00 a
union all
select a.CFLSH0,-4,3,rpad('科室:'||(select Trim(bmmc00) from bm_bmbm00 where bmbh00=a.ghks00),22,' ')||
  rpad('费别:'||(select Trim(d.FBMC00) from BM_BRXXB0 c,BM_BRFBB0 d where c.FBBH00=d.FBBH00 and   a.BRID00=c.BRID00 ),10,'
'),'',0
from YF_MZCF00 a
union all
select a.CFLSH0,-5,3,sf_py_brzxx1(ghid00),'',0
from YF_MZCF00 a
union all-- CFID00<=-10的为页脚--||Trim(to_char(ROUND(a.CFZJE0,2),'99990.00'))
SELECT a.CFLSH0,-10,3,'合计金额:',
      '审核、调配:'||(select Trim(d.ZWXM00) from BM_YGBM00 d where a.FYR000=d.YGBH00),0
FROM YF_MZCF00 a
union all-- CFID00<=-10的为页脚
SELECT a.CFLSH0,-11,3,'医    生:'||Trim(a.YSXM00),'核对、发药:'||(select Trim(c.ZWXM00) from BM_YGBM00 c where a.FHR000=c.YGBH00),0
FROM YF_MZCF00 a
union all-- CFID00<=-10的为页脚
SELECT a.CFLSH0,-12,3,'发药日期:'||to_char(to_date(a.FYRQ00,'YYYYMMDD'),'YYYY-MM-DD')||'   '||a.FYSJ00,'',0
FROM YF_MZCF00 a
union all-- CFID00<=-10的为页脚
SELECT a.CFLSH0,-13,3,'打印日期:'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||'   '||TO_CHAR(SYSDATE,'HH24:MI:SS'),'',0
FROM YF_MZCF00 a
/