select  a.GJBM00,a.XMMC00,a.XMGG00,a.XMDW00,a.XMDJ00,sum(a.XMSL00) XMSL00,sum(a.HJJE00) HJJE00,a.FPXMMC,a.FPXMBH,a.ZFBL00,ZXKSMC,yexm00

 from VW_ZY_RQDMX8 A 

 Where A.ZYID00=0.896830200e+007 

 and QDRQ00>='20240107' and QDRQ00<='20240507' and nvl(yexm00,'全部')='全部'

 group  by  GJBM00,XMMC00,XMGG00,XMDW00,XMDJ00,ZFBL00,FPXMMC,FPXMBH,ZXKSMC,yexm00 HAVING sum(a.XMSL00)<>0 

 order  by  FPXMBH,XMMC00,GJBM00;
 
 
 
 CREATE OR REPLACE VIEW VW_ZY_RQDMX8
(qdrq00, zyid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, fpxmbh, zfbl00, zfje00, jzje00, fpxmmc, gjbm00, sfyp00, gfje00, sfxmid, czrq00, czsj00, jzdh00, ybfsdh, zxksmc, zyh000, order0)
AS
select
      A.QDRQ00  ,    --清单日期
      A.ZYID00  ,    --住院ID
      B.XMBH00  ,    --医疗收费编码
      substrb(decode((select count(1) from XT_XTCS00 where NAME00='ZY_RQDMX8_XMMCBHFPMC' and VALUE0='N'),0,SF_ZY_ZLXMJC_GET(B.MXID00)||B.XMMC00||'['||D.XMMC00||']',SF_ZY_ZLXMJC_GET(B.MXID00)||B.XMMC00),1,100),    --项目名称
      substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50),    --药品规格
      trim(C.DW0000)  ,    --单位
      B.XMDJ00  ,    --单价
      B.XMSL00  ,    --数量
      B.HJJE00  ,    --合计金额
      D.BH0000  ,    --发票项目编号
      to_char(SF_ZY_HQZFBL(a.ZYID00,B.XMBH00,'N',B.ZFBL00))*100||'%'  ,    --自付比率
      B.ZFJE00  ,        --自付金额
      B.JZJE00  ,
      D.XMMC00  ,
      B.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,''),
      'N',
      B.GFJE00  ,
      B.XMBH00  ,
      A.CZRQ00  ,
      A.CZSJ00  ,
      A.JZDH00  ,
      A.YBFSDH  ,
      M.BMMC00  ,
      A.ZYH000  ,
      D.ORDER0
from  ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BMBM00 M
where A.DJH000 = B.DJH000 and B.XMBH00 = C.SFXMID and C.ZYFPID = D.FPXMID AND M.BMBH00=B.ZXKS00
  and (B.XMBH00 < 9999999990  or  JMBZ00 <> 0)  and  B.XMSL00<>0 --非减免,非优惠的
union all
select
      A.QDRQ00  ,    --清单号
      A.ZYID00  ,    --住院ID
      C.YPNM00  ,    --药品内码
      substrb(C.YPMC00||'['||F.XMMC00||']',1,100)  ,    --药品名称
      C.YPGG00  ,    --药品规格
      C.CFDW00  ,    --单位
      C.LSDJ00  ,    --单价
      C.YPZSL0  ,        --数量
      round(C.LSDJ00*C.YPZSL0,2), --金额
      F.BH0000  ,    --发票项目编号
      to_char(SF_ZY_HQZFBL(a.ZYID00,C.YPNM00,'Y',C.ZFBL00))*100||'%',    --自付比率
      round(C.ZFJE00,2),       --自付金额
      round(C.LSDJ00*C.YPZSL0-C.ZFJE00-C.GFJE00,2) ,
      F.XMMC00,
      decode((SELECT count(1) FROM XT_XTCS00 where NAME00='ZY_QDDYGJBMXSFS' and VALUE0='1'),0,
             (select nvl(s.YBXMBH,y.YPBM00) from BM_YD0000 y,BM_YBSFDY s where c.YPNM00=y.YPNM00 and c.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1'),
             (select nvl(y.YPBM00,s.YBXMBH) from BM_YD0000 y,BM_YBSFDY s where c.YPNM00=y.YPNM00 and c.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1')),
      'Y',
      round(decode(C.SFZBY0,'Y',0,C.GFJE00),2), --减免金额
      C.YPNM00  ,
      A.CZRQ00  ,
      A.CZSJ00  ,
      A.JZDH00  ,
      A.YBFSDH  ,
      M.BMMC00  ,
      A.ZYH000 ,
      F.ORDER0
from  ZY_BRFY00 A,YF_ZYCF00 B,YF_ZYCFMX C ,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BMBM00 M  --yf_zycfmx没有含有中药帖数
where  A.DJH000 = B.DJH000  and  B.CFLSH0 = C.CFLSH0  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID  and  E.ZYFPID = F.FPXMID and M.BMBH00=B.YFBMBH
union all
select
      A.QDRQ00  ,    --清单号
      A.ZYID00  ,    --住院ID
      B.YPNM00  ,    --药品内码
      substrb(B.YPMC00||'['||F.XMMC00||']',1,100)  ,    --药品名称
      B.YPGG00  ,    --药品规格
      B.QLDW00  ,    --单位
      B.LSDJ00  ,    --单价
      B.YPZSL0  ,    --数量
      round(B.LSDJ00*B.YPZSL0,2), --金额
      F.BH0000  ,    --发票项目编号
      to_char(SF_ZY_HQZFBL(a.ZYID00,b.YPNM00,'Y',b.ZFBL00))*100||'%'  ,    --自付比率
      round(B.ZFJE00,2), --自付金额
      round(B.LSDJ00*B.YPZSL0-B.ZFJE00-B.GFJE00,2),
      F.XMMC00,
      decode((select count(1) from XT_XTCS00 where NAME00='ZY_QDDYGJBMXSFS' and VALUE0='1'),0,
             (select nvl(s.YBXMBH,y.YPBM00) from BM_YD0000 y,BM_YBSFDY s where b.YPNM00=y.YPNM00 and b.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1'),
             (select nvl(y.YPBM00,s.YBXMBH) from BM_YD0000 y,BM_YBSFDY s where b.YPNM00=y.YPNM00 and b.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1')),
      'Y',
      B.GFJE00  ,--减免金额
      B.YPNM00  ,
      A.CZRQ00  ,
      A.CZSJ00  ,
      A.JZDH00  ,
      A.YBFSDH  ,
      M.BMMC00  ,
      A.ZYH000 ,
      F.ORDER0
from  ZY_BRFY00 A,YF_YZYPSQ B,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BMBM00 M   --yf_yzypsq的总数量有含有中药的帖数
where  A.DJH000 = B.DJH000  and  B.YPDLBH = D.LBBH00  and  D.SFDM00 = E.SFXMID and M.BMBH00=B.YFBMBH
  and  E.ZYFPID = F.FPXMID
;