create or replace view vw_zypy_bqzjlyd_msql as
select x.DLXH00,
       x.YPNM00,
       x.QLDW00,
       x.DXSYBZ,
       x.YPDJ00,
       x.YPMC00,
       x.YPGG00,
       x.YPZSL0,
       x.YPZSL1,
       x.CFJE00,
       x.PYSM00,
       x.LSDJ00,
       x.YPBM00,
       x.CFID00,
       x.GZDJ00,
       x.GWYPFL,
       x.LBBH00,
       x.CFMXID,
       x.CFXH00,
       x.TOTPAG,
       x.DKSL00,
case when x.YPZSL1+x.DKSL00>0 then x.YPZSL1+x.DKSL00
     when x.YPZSL1+x.DKSL00<=0 then 0
else 0 end SFSL00,
round((x.YPZSL1-x.DKSL00)*x.LSDJ00,2) CFJE01,
decode(YPDJ00,'普通药品',0||YPDJ00,'高危药品',1||YPDJ00,'贵重药品',2||YPDJ00,'大输液药品',3||YPDJ00) YPDJ01,
x.YLBH00,
x.SFBD00
 from
 (
select
        C.DLXH00,
        A.YPNM00,
        A.QLDW00,
        B.DXSYBZ,
        decode(DXSYBZ,'1','大输液药品',decode(sign(GZDJ00),1,'贵重药品',decode(B.GWYPFL,'3','高危药品','2','高危药品','1','高危药品','C','高危药品','A','高危药品','B','高危药品','普通药品'))) YPDJ00,
        B.YPMC00,
        A.YPGG00,
        to_char(sum(A.YPZSL0))||'('||trim(A.QLDW00)||')' YPZSL0,
        sum(A.YPZSL0)YPZSL1,
        round(sum(A.YPZSL0*A.LSDJ00),2) CFJE00,
        B.PYSM00,
        A.LSDJ00,
        B.YPBM00,
        (select D.CFID00 from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=d.DLXH00 and E.CFID00=D.CFID00 and E.YPNM00=A.YPNM00 and rownum=1) CFID00,
        B.GZDJ00,
        B.GWYPFL,
        B.LBBH00,
       (select E.CFMXID from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=d.DLXH00 and E.CFID00=D.CFID00 and E.YPNM00=A.YPNM00 and rownum=1) CFMXID,
       (select D.CFXH00 from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=d.DLXH00 and E.CFID00=D.CFID00 and E.YPNM00=A.YPNM00 and rownum=1) CFXH00,
       (select max(D.CFXH00) from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=D.DLXH00 and E.CFID00=D.CFID00 ) TOTPAG,
      -- (select nvl(sum(d.CXSL00),0) from YF_BQTYJL d where d.BQBMBH=A.BQBMBH
      --        and d.YFBMBH=A.YFBMBH and d.YPNM00=A.YPNM00 and d.ZT0000='1'
       --       and d.TYR000='-9'
       --       and d.CZRQ00=A.FYRQ00) DKSL00,
        A.Dksl00,
        B.YLBH00,
        (select decode(nvl(DYCS00,'0'),'0','','（补打）') from YF_YPCKD0 where CKDH00=A.CKDH00) SFBD00
        from YF_YZYPSQ A ,BM_YD0000 B,YF_YZDLDY C where a.YPNM00=b.YPNM00
        and A.YPQLPC=C.YPQLPC 
        group by C.DLXH00,A.YPNM00,B.YPMC00,A.YPGG00,A.QLDW00,A.LSDJ00,B.PYSM00,B.DXSYBZ,B.GZDJ00,A.BQBMBH,A.YFBMBH,A.FYRQ00,B.YPBM00,B.GWYPFL,B.LBBH00,B.YLBH00,A.CKDH00,Dksl00
 ) x;