create or replace view vw_yf_zycfyplb as
select
  a.CFLSH0 CFLSH0,
  b.YPNM00 YPNM00,
  b.YPMC00 YPMC00,
  decode(c.YFLBID,'1','针剂','2','针剂','4','口服','5','针剂','6','针剂','0','口服','3','口服','7','口服','其他') YPLB00
from YF_ZYCF00 a,YF_ZYCFMX b,BM_YPYF00 c
where a.CFLSH0=b.CFLSH0 and b.YPYFMC=c.YPYFMC;



---------------------------------

create or replace view vw_yf_zycfmx_bz
(cflsh0, cfid00, cfxh00, ckpdxh, bqch00, brxm00, ypnm00, ypmc00, cfmx00, je0000, xmyfmc, jxmc00, ypgg00, ypzsl0, yfyl00, lsdj00, jlbz00, pcmc00, pcmcsm, yysj00, yysjdw, yfsmmc, ypyfmc, zb0000, dlxh00, zfbl00, ypdlbh, ypyl00, yfcfwz, yfcfwz1)
as
select
  X.CFLSH0,X.CFID00,0,  (select PDXH00 from YF_YPKCXX where YPNM00 = x.YPNM00 and YFBMBH = d.YFBMBH and rownum = 1) ckpdxh,
  --(select trim(BMMC00) from BM_BMBM00 where BMBH00=d.SSKS00)||'_'||
  trim(d.CH0000)||'床',
  ''||trim(d.BRXM00),
  X.YPNM00,
  ''||X.YPMC00||'    '||trim(x.YPGG00)||'  '||y.bz0000,
  rpad(trim(x.YPGG00),10,' ')||' '||
  lpad((decode(substr(X.YPZSL0,1,1),'.','0','')||X.YPZSL0||trim(X.CFDW00)),7,' ')||' '||
  trim(nvl(X.YFSMMC,' '))||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)||','||decode(x.YYSJ00,null,'',x.YYSJ00||trim(X.YYSJDW)),--lpad(trim(to_char(round(X.LSDJ00,2),'9999990.00')),5,' '),
  trim(round(YPZSL0*LSDJ00,2)),
  trim(X.XMYFMC),
  (select trim(JXMC00) from BM_YPJX00 A,BM_YD0000 B where B.YPNM00=X.YPNM00 and A.LBBH00=B.LBBH00 and A.JXBH00=B.JXBH00),
  trim(x.YPGG00),
  lpad((decode(substr(X.YPZSL0,1,1),'.','0','')||X.YPZSL0||trim(X.CFDW00)),7,' '),
  trim(nvl(X.YFSMMC,' '))||trim(nvl(X.YPYFMC,' '))||
  decode(abs(X.CSL000),null,'',decode(substr(abs(round(X.CSL000*Y.F2J000/
  decode(Y.JLDW00,nvl(Y.MZKFDW,Y.JLDW00),X.ZHL000,Y.F2J000),3)),1,1),'.','0','')
  ||abs(round(X.CSL000*Y.F2J000/decode(Y.JLDW00,nvl(Y.MZKFDW,Y.JLDW00),X.ZHL000,
  Y.F2J000),3))||'('|| decode(Y.JLDW00,nvl(Y.MZKFDW,Y.JLDW00),trim(X.CFDW00),trim(X.JLDW00))||')') ,
  lpad(trim(to_char(round(X.LSDJ00,2),'9999990.00')),5,' '), 'Y', trim(PCMC00),
  ''||(select substr(trim(SM0000),1,5) from BM_PC0000 where trim(PCMC00)=trim(X.PCMC00) and rownum=1), TO_CHAR(X.YYSJ00)||trim(X.YYSJDW),
  trim(X.YYSJDW), trim(X.YFSMMC), trim(X.YPYFMC)||'           '||Y.BZ0000, X.ZB0000, (select FYDLXH from YF_CFDL00 where CFLSH0=X.CFLSH0) DLXH00,
  X.ZFBL00,
  X.YPDLBH,
 -- '每次'||(to_char(x.CSL000, 'FM9999999.99'))||x.jldw00,
  -- '每次'||x.CSL000||x.jldw00,
 /*  '每次'||(select DECODE(c.lbbh00,'0',decode(c.jxbh00,06,'适量',08,'适量',17,'适量',decode(substr(x.csl000,1,1),'.','0'||x.csl000,x.csl000)),decode(substr(x.csl000,1,1),'.','0'||x.csl000,x.csl000)) from bm_yd0000 c
       where x.ypnm00=c.ypnm00 and ((c.jxbh00 not in (01,15) and c.lbbh00=0) or (c.lbbh00<>0 ))   )||(select x.JLDW00 from bm_yd0000 d where x.ypnm00=d.ypnm00
       and ((d.jxbh00 not in (01,15,06,08,17) and d.lbbh00=0)  or (d.lbbh00<>0 )))|| (select round(x.CSL000*F2J000/Z2J000,2) from BM_YD0000 e
       where e.YPNM00=x.YPNM00 and   e.jxbh00 in (01,15) and e.lbbh00=0)||(select decode(f.lbbh00,0,(decode(f.jxbh00,01,zyfydw,15,zyfydw,'  ')),1,'  ',2,'  ',3,'  ')
       from BM_YD0000 f where f.ypnm00=x.ypnm00  ),*/
 '每次'||decode(trim(y.zyfydw),'支',x.csl000||x.jldw00,'瓶',x.csl000||x.jldw00,'片',decode(substr((x.csl000/y.z2j000),1,1),'.','0'||to_char(x.csl000/y.z2j000,'fm9999.00'),x.csl000/y.z2j000)||y.zyfydw,'盒',x.csl000||x.jldw00,'粒',decode(substr((x.csl000/y.z2j000),1,1),'.','0'||to_char(x.csl000/y.z2j000,'fm999.00'),x.csl000/y.z2j000)||y.zyfydw,'贴',x.csl000/y.z2j000||y.zyfydw,'袋',decode(substr((x.csl000/y.z2j000),1,1),'.','0'||x.csl000/y.z2j000,x.csl000/y.z2j000)||y.zyfydw,'包',x.csl000/y.z2j000||y.zyfydw,'板',x.csl000/y.z2j000||y.zyfydw,x.csl000),

(select PDXH00 from YF_YPKCXX where YPNM00 = x.YPNM00 and YFBMBH = d.YFBMBH and rownum = 1)||'    '||trim(to_char(round(YPZSL0*LSDJ00,2),'fm9999999990.00')),
/* '每次'||(select DECODE(c.lbbh00,'0',decode(c.jxbh00,06,'适量',08,'适量',17,'适量',decode(substr(x.csl000,1,1),'.','0'||x.csl000,x.csl000)),decode(substr(x.csl000,1,1),'.','0'||x.csl000,x.csl000)) from bm_yd0000 c
       where x.ypnm00=c.ypnm00 and ((c.jxbh00 not in (01,05,15) and c.lbbh00=0) or (c.lbbh00<>0 ))   )||(select x.JLDW00 from bm_yd0000 d where x.ypnm00=d.ypnm00
       and ((d.jxbh00 not in (01,05,15,06,08,17) and d.lbbh00=0)  or (d.lbbh00<>0 )))|| (select round(x.CSL000*F2J000/Z2J000,2) from BM_YD0000 e
       where e.YPNM00=x.YPNM00 and   e.jxbh00 in (01,05,15) and e.lbbh00=0)||(select decode(f.lbbh00,0,(decode(f.jxbh00,01,zyfydw,05,zyfydw,15,zyfydw,'  ')),1,'  ',2,'  ',3,'  ')
       from BM_YD0000 f where f.ypnm00=x.ypnm00  )*/
        '每次'||decode(trim(y.zyfydw),'支',x.csl000||x.jldw00,'瓶',x.csl000||x.jldw00,'片',to_char(round(x.csl000/y.z2j000,2),'fm9999999990.0')||y.zyfydw,'盒',x.csl000||x.jldw00,'粒',to_char(round(x.csl000/y.z2j000,2),'fm9999999990.0')||y.zyfydw,'贴',x.csl000/y.z2j000||y.zyfydw,'袋',to_char(x.csl000/y.z2j000,'fm9999999990.0')||y.zyfydw,'包',x.csl000/y.z2j000||y.zyfydw,'板',x.csl000/y.z2j000||y.zyfydw,x.csl000)
from YF_ZYCFMX X,BM_YD0000 y,YF_ZYCF00 d where x.YPNM00=y.YPNM00 and d.CFLSH0=x.CFLSH0  and X.SFZBY0='N';