CREATE OR REPLACE VIEW vw_bq_ypyzje (
   yzid00,
   clbz00,
   lbbh00,
   lbmc00,
   yzmxid,
   ypnm00,
   ypmc00,
   ypdj00,
   mcsl00,
   pcmc00,
   zqcs00,
   lyl000,
   je0000,
   lrsj00,
   tzsj00,
   zyid00 )
AS
select a.YZID00,--医嘱号
       c.CLBZ00,--处理标志
       c.YPLBBH LBBH00,--药品类别
       d.LBMC00,--类别名称
       a.YZMXID,--医嘱明细ID
       a.YPNM00,--药品内码
       b.YPMC00||decode(b.YPGG00,'','','['||b.YPGG00||']') YPMC00,--药品名称
       --Round(f.LSDJ00/b.K2J000*a.Z2J000,2) YPDJ00,--药品单价
       (select Round(f.LSDJ00/b.K2J000*a.Z2J000,2)  from YF_YPKCXX f where YPNM00=a.YPNM00 and rownum=1) YPDJ00,
       to_char(a.MCSL00)||trim(a.JLDW00) MCSL00,--每次数量(剂量单位)
       c.PCMC00,--频次       
       (select decode(c.CLBZ00,'0',e.ZQCS00,e.ZQCS00*c.ZQS000)  from BM_PC0000 e where e.PCID00=c.PCID00) ZQCS00,--周期次数
       to_char(a.LYL000)||trim(a.ZYFYDW) LYL000,--领药量(住院发药单位)
       --Round(a.LYL000*(f.LSDJ00/b.K2J000*a.Z2J000),2) JE0000,--金额
       --(select Round(a.LYL000*(f.LSDJ00/b.K2J000*a.Z2J000),2)  from YF_YPKCXX f where YPNM00=a.YPNM00 and rownum=1) JE0000,
       (select Round(round(a.LYL000/b.nsbl00,2)*(f.LSDJ00/b.K2J000*a.Z2J000),2)  from YF_YPKCXX f where YPNM00=a.YPNM00 and rownum=1) JE0000,
       c.LRRQ00||' '||c.LRSJ00 LRSJ00,--录入时间
       c.TZRQ00||' '||c.TZSJ00 TZSJ00, --停止时间
       C.ZYID00
from BQ_YPYZMX a,BM_YD0000 b,BQ_YPYZ00 c,BM_YPLB00 d  --,YF_YPKCXX f
where b.YPNM00 = a.YPNM00 and c.YZID00 = a.YZID00 and
      d.LBBH00 = c.YPLBBH   
      --and f.YPNM00=b.YPNM00 and f.YFBMBH=c.YFBMBH 
      and (b.LBBH00<>'2' or  jkbh00 is  null)
union all
select a.YZID00,--医嘱号
       c.CLBZ00,--处理标志
       c.YPLBBH LBBH00,--药品类别
       d.LBMC00,--类别名称
       a.YZMXID,--医嘱明细ID
       a.YPNM00,--药品内码
       b.YPMC00||decode(b.YPGG00,'','','['||b.YPGG00||']') YPMC00,--药品名称
       --Round(f.LSDJ00/b.K2J000*a.Z2J000,2) YPDJ00,--药品单价
       (select Round(f.LSDJ00/b.K2J000*a.Z2J000,2)  from YF_YPKCXX f where YPNM00=a.YPNM00 and rownum=1) YPDJ00,
       to_char(round(a.MCSL00/b.NSBL00,2))||trim(a.JLDW00) MCSL00,--每次数量(剂量单位)
       c.PCMC00,--频次
       (select decode(c.CLBZ00,'0',e.ZQCS00,e.ZQCS00*c.ZQS000) from BM_PC0000 e where e.PCID00=c.PCID00) ZQCS00,--周期次数
       to_char(round(a.LYL000/b.NSBL00,2))||trim(a.ZYFYDW) LYL000,--领药量(住院发药单位)
       --Round((a.LYL000/b.nsbl00)*(f.LSDJ00/b.K2J000*a.Z2J000),2) JE0000,--金额
       (select Round(round(a.LYL000/b.nsbl00,2)*(f.LSDJ00/b.K2J000*a.Z2J000),2)  from YF_YPKCXX f where YPNM00=a.YPNM00 and rownum=1) JE0000,
       c.LRRQ00||' '||c.LRSJ00 LRSJ00,--录入时间
       c.TZRQ00||' '||c.TZSJ00 TZSJ00, --停止时间
       C.ZYID00
from BQ_YPYZMX a,BM_YD0000 b,BQ_YPYZ00 c,BM_YPLB00 d --,YF_YPKCXX f
where b.YPNM00 = a.YPNM00 and c.YZID00 = a.YZID00 and
      d.LBBH00 = c.YPLBBH   
      --and f.YPNM00=b.YPNM00 and f.YFBMBH=c.YFBMBH
      and  b.LBBH00='2' and  b.jkbh00 is not null
/