create or replace view vw_sf_jsfpxmqd as
select
  a.BRID00,                                                             --病人ID
  e.JZDH00,                                                             --结算单号
  c.zlxmjc as XMMC00,                                                   --项目名称
  d.BH0000 as FPXMBH,                                                   --发票项目编号
  d.XMMC00 as FPXMMC,                                                   --发票项目名称
  count(distinct c.YJDJH0)*e.BZ0000 as XMSL00,                          --诊疗项目数量（这种写法有个问题：如果有冲销的医技记录，那么冲销的也会被加到数量上，本需求暂不处理）
  sum(a.HJJE00*e.BZ0000) as HJJE00,                                     --合计金额
  sum(a.ZFJE00*e.BZ0000) as ZFJE00,                                     --自付金额
  sum(a.GFJE00*e.BZ0000) as GFJE00,                                     --减免金额
  sum(a.JZJE00*e.BZ0000) as JZJE00                                      --计账金额
from  SF_FYMX00 a,SF_JZMXXM e,YJ_YW0000 c,BM_FPXM00 d
where a.MXID00 = e.MXID00
  and a.YJDJH0 = c.YJDJH0
  and a.MZFPID = d.FPXMID
  and a.SFLB00 <> 3  --非药品检验费
  and c.zlxmid in (select  zlxmid from bm_zlzd00 where lbbh00='4')
group by a.BRID00,e.JZDH00,d.BH0000,d.XMMC00,c.zlxmjc,e.BZ0000
union all
select
  a.BRID00,                                                             --病人ID
  e.JZDH00,                                                             --结算单号
  a.XMMC00 as XMMC00,                                                   --项目名称
  d.BH0000 as FPXMBH,                                                   --发票项目编号
  d.XMMC00 as FPXMMC,                                                   --发票项目名称
  count(distinct c.YJDJH0)*e.BZ0000 as XMSL00,                          --诊疗项目数量（这种写法有个问题：如果有冲销的医技记录，那么冲销的也会被加到数量上，本需求暂不处理）
  sum(a.HJJE00*e.BZ0000) as HJJE00,                                     --合计金额
  sum(a.ZFJE00*e.BZ0000) as ZFJE00,                                     --自付金额
  sum(a.GFJE00*e.BZ0000) as GFJE00,                                     --减免金额
  sum(a.JZJE00*e.BZ0000) as JZJE00                                      --计账金额
from  SF_FYMX00 a,SF_JZMXXM e,YJ_YW0000 c,BM_FPXM00 d
where a.MXID00 = e.MXID00
  and a.YJDJH0 = c.YJDJH0
  and a.MZFPID = d.FPXMID
  and a.SFLB00 <> 3  --非药品非检验费
  and c.zlxmid in (select  zlxmid from bm_zlzd00 where lbbh00<>'4')
group by a.BRID00,e.JZDH00,d.BH0000,d.XMMC00,a.XMMC00,e.BZ0000

union all
select
  a.BRID00,                                                             --病人ID
  e.JZDH00,                                                             --结算单号
  a.XMMC00,                                                             --项目名称
  d.BH0000 as FPXMBH,                                                   --发票项目编号
  d.XMMC00 as FPXMMC,                                                   --发票项目名称
  sum(a.XMSL00*e.BZ0000) as XMSL00,                                     --数量
  sum(a.HJJE00*e.BZ0000) as HJJE00,                                     --合计金额
  sum(a.ZFJE00*e.BZ0000) as ZFJE00,                                     --自付金额
  sum(a.GFJE00*e.BZ0000) as GFJE00,                                     --减免金额
  sum(a.JZJE00*e.BZ0000) as JZJE00                                      --计账金额
from  SF_FYMX00 a,SF_JZMXXM e,BM_FPXM00 d
where a.MXID00 = e.MXID00
  and a.MZFPID = d.FPXMID
  and a.SFLB00 = 3
  and a.MZFPID <> 3 --西药，成药
group by a.BRID00,e.JZDH00,d.BH0000,d.XMMC00,a.XMMC00
union all
select
  a.BRID00,                                                             --病人ID
  e.JZDH00,                                                             --结算单号
  '中草药费' as XMMC00,                                                 --项目名称
  d.BH0000 as FPXMBH,                                                   --发票项目编号
  d.XMMC00 as FPXMMC,                                                   --发票项目名称
  1 as XMSL00,                                                          --数量
  sum(a.HJJE00*e.BZ0000) as HJJE00,                                     --合计金额
  sum(a.ZFJE00*e.BZ0000) as ZFJE00,                                     --自付金额
  sum(a.GFJE00*e.BZ0000) as GFJE00,                                     --减免金额
  sum(a.JZJE00*e.BZ0000) as JZJE00                                      --计账金额
from  SF_FYMX00 a,SF_JZMXXM e,BM_FPXM00 d
where a.MXID00 = e.MXID00
  and a.MZFPID = d.FPXMID
  and a.SFLB00 = 3
  and a.MZFPID = 3  --中草药费
group by a.BRID00,e.JZDH00,d.BH0000,d.XMMC00;