CREATE OR REPLACE VIEW vw_yf_mzzycf_mx0000 (
   cflsh0,
   cfid00,
   row000,
   col000,
   ypmx00 )
AS
select Y.CFLSH0,X.CFID00, CEIL(ROWNUM/2) ROW000, MOD(ROWNUM,2) COL000,
       rpad(Trim(X.YPMC00)||decode(Trim(X.YPGG00),null,null,' '||Trim(X.YPGG00)||' ')||
            decode(Trim(X.XMYFMC),null,null,'('||Trim(X.XMYFMC)||')'),34,' ')||
            -- decode(Trim(X.XMYFMC),null,'','('||Trim(X.XMYFMC)||')'),23,' ')),1,23)||
       rpad(decode(X.CSL000,NULL,NULL,0,NULL,decode(substrb(X.CSL000,1,1),'.','0')||
              decode(substrb(X.CSL000,1,1),'-',X.YPZSL0,X.CSL000)||trim(X.JLDW00)),4,' ')||
              lpad(Trim(decode(X.SFZBY0,'Y','0.00',to_char(ROUND(X.LSDJ00*X.YPZSL0,2),'990.00'))),9,' ')
       from YF_MZCFMX X,YF_MZCF00 Y,YF_CFLSH0 Z where X.CFLSH0=Y.CFLSH0 AND Y.CFLSH0=Z.CFLSH0
/




CREATE OR REPLACE VIEW vw_yf_mzzydy (
   cflsh0,
   cfid00,
   cfmx00 )
AS
select X.CFLSH0,X.CFID00,X.YPMX00||' '||
     --  (SELECT Y.YPMX00 FROM VW_YF_MZZYCF_MX0000 Y WHERE X.ROW000=Y.ROW000 AND Y.COL000=2)||' '||
       (SELECT Z.YPMX00 FROM VW_YF_MZZYCF_MX0000 Z WHERE X.ROW000=Z.ROW000 AND Z.COL000=0)
from   VW_YF_MZZYCF_MX0000 X
where  X.COL000=1
union all
SELECT '-1              ',0,trim(YYJC00)||'门诊' FROM XT_YYXX00 a where rownum=1
union all-- CFID00=-1~-10的为页头
select a.CFLSH0,-1,rpad('药房:'||Trim(b.BMMC00),16,' ')||rpad('挂号号:'||Trim(a.GHH000),18,' ')||
       rpad('处方号:'||Trim(a.CFLSH0),16,' ')||'日期:'||to_char(to_date(a.FYRQ00,'YYYYMMDD'),'YYYY-MM-DD')
from YF_MZCF00 a,BM_BMBM00 b where a.YFBMBH=b.BMBH00
union all
select a.CFLSH0,-2,rpad('姓名:'||Trim(a.BRXM00),16,' ')||rpad('性别:'||Trim(a.XB0000),8,' ')||
       rpad('年龄:'||Trim(SF_YF_CSRQTONL(a.CSRQ00,a.SRRQ00)),12)||
       rpad('医生:'||Trim(a.YSXM00),16,' ')||rpad('科室:'||Trim(b.BMMC00),15,' ')
from YF_MZCF00 a,BM_BMBM00 b where a.GHKS00=b.BMBH00
union all
--SELECT '-1              ',-3,'药品名(细目用法)     剂量 金 额        药品名(细目用法)     剂量 金 额'             --药品名(细目用法)       剂量  金 额
--FROM  xt_yyxx00 where rownum=1
SELECT '-1              ',-3,'药品名 规格 (细目用法)        剂量    金 额 药品名 规格 (细目用法)      剂量    金 额     '
FROM  xt_yyxx00 where rownum=1
union all-- CFID00<=-10的为页脚
SELECT a.CFLSH0,-10,rpad('剂数:'||to_char(a.ZYTS00),15,' ')||rpad('代煎剂数:'||to_char(a.ZYDJS0),15,' ')||
       rpad('整剂用法:'||(select Trim(c.ZJYFMC)||decode(Trim(c.PCMC00),null,null,','||Trim(c.PCMC00))||
       decode(Trim(c.YFSMMC),null,null,','||Trim(c.YFSMMC)) from YF_MZCFMX c where a.CFLSH0=c.CFLSH0 AND ROWNUM=1),25,' ')||
       '                              合计金额:'||Trim(to_char(ROUND(a.CFZJE0,2),'99990.00'))
FROM YF_MZCF00 a
union all-- CFID00<=-10的为页脚
SELECT a.CFLSH0,-11,rpad('发药:'||(select Trim(b.ZWXM00) from BM_YGBM00 b where a.FYR000=b.YGBH00),14,' ')||
       rpad('复核:'||(select Trim(c.ZWXM00) from BM_YGBM00 c where a.FHR000=c.YGBH00),14,' ')||
       '打印日期:'||to_char(sysdate,'MM-DD HH24:MI')||
       '  打印时余额:'||(select ZYE000 from SF_BRZHXX b where a.BRID00=b.BRID00)
FROM YF_MZCF00 a
/