create or replace view vw_zy_rqdmx1_ts
(zyid00, zyh000, qdrq00, gjbm00, gjbm01, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, zfje00, zfbl00, fbbh00, ybzxlb, ybbrlb)
as
select
          a.ZYID00,
          a.ZYH000,
          a.QDRQ00,
          max(g.GJBM00) , --显示用
          max(a.GJBM00) , --排序用
          max(a.XMBH00) ,
          A.HBXMMC ,
          ' ',
          max(A.XMDW00) ,
          to_char(round(sum(a.HJJE00),2)) XMDJ00,  --单价
          1,
          sum(a.XMSL00*a.XMDJ00) HJJE00,
          sum(a.XMSL00*a.XMDJ00*f.ZFBL00) ZFJE00,
          max(f.ZFBL00) ,
          max(f.FBBH00) ,
          max(f.YBZXLB) ,
          max(f.YBBRLB)
   from  ZY_FYMX00 a,VW_BM_YBSFDY f,BM_YYSFXM g
       where f.SFYP00 = 'N'
         and a.XMBH00 = f.SFXMID
         and a.XMBH00 = g.SFXMID
         and a.SFLB00 <> '3'
         and a.HBXMMC is not null
   group by a.ZYID00,a.ZYH000,a.QDRQ00,a.HBXMMC
union all
   select
          a.ZYID00 ,
          a.ZYH000 ,
          a.QDRQ00 ,
          g.GJBM00 , --显示用
          a.GJBM00 , --排序用
          a.XMBH00 ,
          a.XMMC00 ,
          substr(decode(g.JSXM00,'2',g.GG0000,g.BZ0000),1,100),     --规格,收费项目没有规格,用单位代替
          a.XMDW00 ,
          decode(sign(a.XMDJ00-1),-1,'0','')||to_char(A.XMDJ00)||'/'||to_char(f.ZFBL00) XMDJ00,
          sum(a.XMSL00) ,
          sum(a.XMSL00*a.XMDJ00) HJJE00,
          sum(a.XMSL00*a.XMDJ00*f.ZFBL00) ZFJE00,
          f.ZFBL00 ,
          f.FBBH00 ,
          f.YBZXLB ,
          f.YBBRLB
   from  ZY_FYMX00 a,VW_BM_YBSFDY f,BM_YYSFXM g
       where f.SFYP00 = 'N'
         and a.XMBH00 = f.SFXMID
         and a.XMBH00 = g.SFXMID
         and a.SFLB00 <> '3'
         and a.HBXMMC is null
   group by a.ZYID00,a.ZYH000,a.QDRQ00,g.GJBM00,a.GJBM00,a.XMBH00,a.XMMC00,substr(decode(g.JSXM00,'2',g.GG0000,g.BZ0000),1,100),a.XMDW00,decode(sign(a.XMDJ00-1),-1,'0','')||to_char(a.XMDJ00),f.ZFBL00,f.FBBH00,f.YBZXLB,f.YBBRLB
union all
   select
          a.ZYID00,
          a.ZYH000,
          a.QDRQ00,
          '',
          '',
          c.YPNM00,
          c.YPMC00,
          c.YPGG00,
          c.CFDW00,
          decode(sign(c.LSDJ00-1),-1,'0','')||to_char(c.LSDJ00)||'/'||to_char(j.ZFBL00) XMDJ00,
          sum(c.YPZSL0),
          sum(round(c.LSDJ00*c.YPZSL0,2)) HJJE00,
          sum(round(c.LSDJ00*c.YPZSL0,2)*j.ZFBL00) ZFJE00,
          j.ZFBL00,
          j.FBBH00,
          j.YBZXLB,
          j.YBBRLB
   from  ZY_FYMX00 a,yf_zycf00 B,yf_zycfmx C,VW_BM_YBSFDY J
       where a.DJH000 = b.DJH000
         and b.CFLSH0 = c.CFLSH0
         and j.SFYP00 = 'Y'
         and j.SFXMID = a.XMBH00
         and a.SFLB00 = '3'
    group by a.ZYID00,a.ZYH000,a.QDRQ00,c.YPNM00,c.YPMC00,c.YPGG00,c.CFDW00,decode(sign(c.LSDJ00-1),-1,'0','')||to_char(c.LSDJ00),j.ZFBL00,j.FBBH00,j.YBZXLB,j.YBBRLB
union all
   select
          a.ZYID00,
          a.ZYH000,
          a.QDRQ00,
          '',
          '',
          b.YPNM00,
          b.YPMC00,
          b.YPGG00,
          b.QLDW00,
          decode(sign(b.LSDJ00-1),-1,'0','')||to_char(b.LSDJ00)||'/'||to_char(f.ZFBL00) XMDJ00,
          sum(b.YPZSL0),
          sum(round(b.LSDJ00*b.YPZSL0,2)) HJJE00,
          sum(round(b.LSDJ00*b.YPZSL0,2)*f.ZFBL00) ZFJE00,
          f.ZFBL00,
          f.FBBH00,
          f.YBZXLB,
          f.YBBRLB
 from  ZY_FYMX00 a,YF_YZYPSQ b,VW_BM_YBSFDY f
      where a.DJH000 = b.DJH000 
        and a.xmbh00 = b.ypnm00 ---20210113,xzh,同个djh000存在多个药品记录的情况，增加ypnm00对应处理
        and f.SFYP00 = 'Y'
        and f.SFXMID = a.XMBH00
        and a.SFLB00 = '3'
  group by a.ZYID00,a.ZYH000,a.QDRQ00,b.YPNM00,b.YPMC00,b.YPGG00,b.QLDW00,decode(sign(b.LSDJ00-1),-1,'0','')||to_char(b.LSDJ00),f.ZFBL00,f.FBBH00,f.YBZXLB,f.YBBRLB
;