CREATE VIEW SD_HOSPITAL.VW_PY_MZCFMXDY_SEYY
 (
 CFLSH0, CFID00, YPMC00, JE0000, YPZSL0, YFYL01, YFMC00, CFLSH1, XYZD00, 
YPDLBH, CFMX01, CFMX02, CFMX03, YYMC01, YFBL01, XMXB01, NL0001, KSCQ01, 
ZDXX01, CQLY01, HJJE01, YS0001, FYSJ01, DYSJ01, FYR001, FHR001, FYCK00, 
FYDLH0

 )
 AS 
select
  distinct(x.CFLSH0) cflsh0,
  x.CFID00 cfid00,
  x.YPMC00 ypmc00,
  to_char(trim(round(YPZSL0*LSDJ00,2)),'999990.99')||'元' je0000,
  lpad((decode(substr(x.YPZSL0,1,1),'.','0','')||x.YPZSL0||trim(x.CFDW00)),7,' ') ypzsl0,

       TRIM(nvl(X.YPYFMC,' '))||'/'||Trim(DECODE(X.CSL000,NULL,'',0,'','每次 '||DECODE(SUBSTR(ABS(X.CSL000),1,1),'.','0','')||ABS(X.CSL000)
       ||trim(X.JLDW00)))||'/'||TRIM(X.PCMC00)||'/'||TO_CHAR(X.YYSJ00)||TRIM(X.YYSJDW) yfyl01,
       (select bmmc00 from bm_bmbm00 h where h.bmbh00=a.yfbmbh)||'药品清单' yfmc00,
       '处方号：'||a.cflsh0 cflsh1,
       '诊断: '||(select ZDMC00 from ys_brzdxx h where h.ghid00=a.ghid00 and h.ZZDBZ0='3' AND SFXYZD='1' AND ROWNUM=1) XYZD00,
       a.ypdlbh,
-------/*处方明细*/
       '药名:'||rpad(Trim(X.YPMC00),26,' ')||decode(y.kssjb0,0,'','级别:'||y.kssjb0),

       rpad('规格与总量:'||Trim(substr(x.YPGG00,1,decode(instr(x.YPGG00,'*')-1,-1,length(x.YPGG00),instr(x.YPGG00,'*')-1)))||'*'||
       '  '||DECODE(SUBSTR(X.YPZSL0,1,1),'.','0','')||X.YPZSL0||trim(X.CFDW00)||' ',27,' ')||
       lpad(decode(substr(trim(to_char(ROUND(x.YPZSL0*x.LSDJ00,2),'99999999.99')),1,1),'.','0','')||trim(to_char(ROUND(x.YPZSL0*LSDJ00,2),'99999999.99'))||'元',10,' '),

       '用法与用量:'||rpad(TRIM(X.YFSMMC)||TRIM(X.YPYFMC)||TRIM(X.XMYFMC),8,' ')||
       rpad(Trim(DECODE(X.CSL000,NULL,'',DECODE(SUBSTR(ABS(X.CSL000),1,1),'.','0','')||ABS(X.CSL000)||trim(X.JLDW00))),6,' ')||
       (select Trim(Y.SM0000) from bm_pc0000 Y where x.pcmc00=y.pcmc00 and rownum=1)||'/'||to_char(x.yysj00)||trim(x.yysjdw),
      --  '皮试方式:'||TRIM(m.ypmc00)||trim(x.psmc00),
--------

'福建医科大学附属第二医院',
--rpad('药　房:'||Trim(b.BMMC00),19,' ')||rpad('病历号:'||(select Trim(c.BRBLH0) from SF_BRXXB0 c where a.GHh000=c.GHH000),20,' '),
rpad('药　房:'||Trim(b.BMMC00),18,' ')||rpad('病历号:'||(select Trim(c.BRBLH0) from SF_BRXXB0 c where a.GHh000=c.GHH000),16,' '),
--rpad('挂号号:'||Trim(a.GHH000),17.8,' ')||rpad('处方号:'||Trim(a.CFLSH0),20,' '),
rpad('挂号号:'||Trim(a.GHH000),18,' ')||rpad('处方号:'||Trim(a.CFLSH0),16,' '),

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,''),

rpad('科室:'||(select Trim(bmmc00) from bm_bmbm00 where bmbh00=a.ghks00),25,' ')||'复诊时间:'||(select Z.YYKBRQ from sf_brxxb0 z where z.ghh000=a.ghh000),

'超期理由:'||rpad(SF_YF_GETCQLY00(a.cflsh0),20,' '),
--sf_py_brzxx(a.ghid00),
 substr(sf_py_brzxx(a.ghid00),0,40),

--'复诊时间:'||(select Z.YYKBRQ from sf_brxxb0 z where z.ghh000=a.ghh000),

'合计金额:'||a.CFZJE0,

'医    生:'||Trim(a.YSXM00),

'发药日期:'||to_char(to_date(a.FYRQ00,'YYYYMMDD'),'YYYY-MM-DD')||' '||a.FYSJ00,

'打印日期:'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'),
'审核、调配:'||(select Trim(c.ZWXM00) from BM_YGBM00 c where t.PYR000=c.YGBH00),
'复核、发药:'||(select Trim(c.ZWXM00) from BM_YGBM00 c where a.PFR000=c.YGBH00),
'发药窗口：'||(select ckmc00 from bm_ckbm00 where t.fyckbh=ckbh00),
'发药序号： '||t.fydlxh

/*from YF_MZCFMX X,BM_YD0000 y,yf_mzcf00 a,sf_brxxb0 z,BM_BMBM00 b,bm_number c where z.brid00=a.brid00
and a.YFBMBH=b.BMBH00 and x.cflsh0=a.cflsh0 and x.YPNM00=y.YPNM00 and X.SFZBY0='N'*/
from YF_MZCFMX X,BM_YD0000 y,yf_mzcf00 a,sf_brxxb0 z,BM_BMBM00 b,bm_number c,YF_CFDL00 T where z.brid00=a.brid00 AND T.CFLSH0=A.CFLSH0
and a.YFBMBH=b.BMBH00 and x.cflsh0=a.cflsh0 and x.YPNM00=y.YPNM00 and X.SFZBY0='N'
--AND T.FYCKBH  IN ('371','370')
/

CREATE VIEW SD_HOSPITAL.VW_PY_MZCFDY_SEYY
 (
 CFLSH0, CFID00, CFXH00, CFMX00, BTXX00, JE0000, XMYFMC, YPMC00

 )
 AS 
select a.CFLSH0,-1,3,(select BMMC00 from BM_BMBM00 where BMBH00=a.YFBMBH),'',0,'',''  from YF_MZCF00 a
 union all/*医院名称*/
   select CFLSH0,-2,3,sf_xt_yymc00(YFBMBH),'',0,'','' from YF_MZCF00
 union all /*科别  费别*/
   select a.CFLSH0,-3,3,rpad('科别:'||(select trim(b.BMMC00) from BM_BMBM00 b where BMBH00=a.GHKS00),24,' ')||
   rpad('费别:'||(select Trim(d.YBMC00) from IC_YBBRLB d where  F.YBLB00=d.YBLB00 and F.FBBH00=d.FBBH00),24,' ')||
   rpad('药房:'||(select Trim(e.BMMC00) from BM_BMBM00 e where  a.YFBMBH=e.BMBH00),32,' '),'',0,'',''
   from YF_MZCF00 a,BM_BRXXB0 f where a.BRID00=f.BRID00
 union all/*卡号*/
   select a.CFLSH0,-4,3,rpad('卡号:'||Trim(b.KMH000),25,' '),'',0,'',''
   from YF_MZCF00 a,IC_YHXX00 b where a.BRID00=b.BRID00  and b.ZT0000='1'
 union all/*姓名*/
   select a.CFLSH0,-5,3,rpad('姓名:'||Trim(a.BRXM00),25,' '),'',0,'',''   from YF_MZCF00 a
 union all/*性别*/
   select a.CFLSH0,-6,3,rpad('性别:'||Trim(a.XB0000),23,' '),'',0,'','' from YF_MZCF00 a
 union all/* 年龄*/
   select a.CFLSH0,-7,3,rpad('年龄:'||decode(SF_YF_GETCFDGS0(a.CFLSH0,'0'),'2',Trim(SF_YF_CSRQTONYR(a.CSRQ00,a.SRRQ00)),'3',Trim(SF_YF_CSRQTONYR   (a.CSRQ00,a.SRRQ00)),Trim(SF_YF_CSRQTONL(CSRQ00,SRRQ00))),20,' '),'',0,'','' from YF_MZCF00 a
 union all /*字段标题*/
   select a.CFLSH0,-8,3,' 药 名                数量                 金额','',0,'','' from YF_MZCF00 a
 union all/*合  计*/
   select a.CFLSH0,-9,3,rpad('药费合计:'||Trim(to_char(ROUND(a.CFZJE0,2),'999990.00'))||'元',20,' '),'',0,'','' from YF_MZCF00 a
 union all/*日  期*/
   select a.CFLSH0,-10,3,rpad('日    期:'||Trim(to_char(sysdate,'YYYY/MM/DD')),28,' '),'',0,'','' from YF_MZCF00 a
/