create or replace view vw_py_ypbqxx as
select
  C.CFLSH0, --处方号
  substrb(C.FYRQ00,1,4)||'-'||substrb(C.FYRQ00,5,2)||'-'||substrb(C.FYRQ00,7,2) FYRQ00, --发药日期
  I.CFID00, --
  substr(C.BRXM00,1,6) brxm00, --姓名
  C.XB0000, --性别
  I.YPMC00, --药品名称
  I.YPYFMC, --药品用法名称
  '('||I.YPGG00||')' YPGG00,
  substr(SF_YF_GETYPKCZLXX(i.YPZSL0*i.ZHL000,2,d.K2J000,d.ZJJL2J,d.KCDW00,d.ZJJLDW,d.JLDW00),1,10) SLDW00,   --药品总量
--  decode(substr(trim(I.YPZSL0),1,1),'.','0','')||trim(I.YPZSL0)||trim(I.CFDW00) SLDW00,
    nvl((select trim(SM0000)||' ' from BM_PC0000 M where M.PCMC00=I.PCMC00 and rownum=1),' ')||
    decode(I.CSL000,null,null,'每次'||rtrim(replace(to_char(i.CSL000*i.F2J000/d.ZJJL2J,'FM9999990.99')||' ','. ',''))|| trim(d.ZJJLDW)||
    ' ('||rtrim(replace(to_char(I.CSL000,'FM9999990.99')||' ','. ',''))|| trim(I.JLDW00)||')') XXYF00, --详细用法
  nvl(D.BZ0000,E.MC0000) BZ0000, --用法备注
  D.YFID00,  --用法ID
  (select ckmc00 from bm_ckbm00 where ckbh00=(select fyckbh from yf_cfdl00 WHERE cflsh0=c.cflsh0)) ckmc00,--窗口名称
  (select fydlxh from yf_cfdl00 where cflsh0=c.cflsh0) fydlxh,  --序号
  i.yfsmmc ,  --用法说明名称
   (select yyqc00 from xt_yyxx00 where yyid00 in (select yyid00  from bm_bmbm00 where bmbh00=c.yfbmbh )) yymc00 -- 医院名称
from YF_MZCFMX I, BM_YD0000 D, YF_MZCF00 C,VW_YK_YPCCFS E
where I.YPNM00=D.YPNM00 and I.SFZBY0='N' and I.CFLSH0=C.CFLSH0 and D.CCFS00=E.NBBH00(+)
  and (LBBH00 in ('0','1') and (I.YPYFMC in (select YPYFMC from BM_YPYF00 where YFLBID = 0) /*or D.BZ0000 is not null*/
       or exists (select 1 from VW_YK_YPCCFS E where D.CCFS00=E.NBBH00)) ) and i.ypyfmc not like '肌肉注射%';