create or replace view vw_sf_fpqdhz_xm
(jzdh00, brid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, sfyb00, zfbl00, mzfpxm)
as
select
    a.JZDH00,
    a.BRID00,
    max(a.XMBH00) as XMBH00,
    substr(trim(c.HBXMMC),1,50) as XMMC00,                    --项目名称
    max(substrb(trim(c.GG0000),1,50)) as XMGG00, --项目规格
    max(trim(c.DW0000)) as DW0000,              --单位
    sum(a.HJJE00) as XMDJ00 ,                   --单价
    1 as XMSL00 ,                               --数量
    sum(a.HJJE00) as HJJE00 ,
    '',
    to_char(max(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100)||'%' as ZFBL00,
     (select XMMC00 from BM_FPXM00 where a.MZFPID=FPXMID) MZFPXM
  from  SF_FYMX00 a,BM_YYSFXM c
  where 1=1
    and a.XMBH00 = c.SFXMID
    and a.SFLB00 not in ('3')
    and c.HBXMMC is not null
  group by a.JZDH00,a.BRID00,c.HBXMMC,a.MZFPID
  having(sum(a.XMSL00) <> 0)
union all
select
    a.JZDH00,
    a.BRID00,
    a.XMBH00,
    substr(trim(a.XMMC00),1,50) as XMMC00,     --项目名称
    substr(trim(c.GG0000),1,50) as XMGG00,   --项目规格
    trim(c.DW0000) DW0000,                  --单位
    a.XMDJ00,                              --单价
    sum(a.XMSL00) as XMSL00,               --数量
    sum(a.HJJE00) as HJJE00,
    '',
    to_char(max(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100)||'%' as ZFBL00,
    (select XMMC00 from BM_FPXM00 where a.MZFPID=FPXMID) MZFPXM
from  SF_FYMX00 a,BM_YYSFXM c
where 1=1
  and a.XMBH00 = c.SFXMID
  and a.SFLB00 not in ('3')
  and c.HBXMMC is null
group by a.JZDH00,a.BRID00,a.XMBH00,a.XMMC00,c.GG0000,c.DW0000,a.XMDJ00,a.MZFPID
having(sum(a.XMSL00) <> 0)
union all
select
    a.JZDH00,
    a.BRID00,
    a.XMBH00,
    substr(trim(a.XMMC00),1,50) as XMMC00,     --药品名称
    substr(trim(a.XMGG00),1,50) as XMGG00 ,    --药品规格
    trim(a.XMDW00) as DW0000,                  --单位
    round(sum(a.HJJE00)/sum(a.XMSL00),4) as XMDJ00,   --单价
    sum(a.XMSL00) as XMSL00,                   --数量
    sum(a.HJJE00) as HJJE00,
    '',
    to_char(max(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'Y',a.ZFBL00))*100)||'%' as ZFBL00,
    (select XMMC00 from BM_FPXM00 where a.MZFPID=FPXMID) MZFPXM
from  SF_FYMX00 a
where a.SFLB00 = '3'
 group by a.JZDH00,a.BRID00,a.XMBH00,a.XMMC00,a.XMGG00,a.XMDW00,a.MZFPID
  having(sum(a.XMSL00) <> 0);