CREATE VIEW SD_HOSPITAL.VW_ZY_RQDMX0
 (
 QDH000, CZSJ00, ZYID00, ZYH000, JZDH00, XMBH00, XMMC00, XMGG00, XMDW00, 
XMDJ00, XMSL00, HJJE00, BH0000, ZFBL00, ZFJE00, JZJE00, FPXMMC, FPXMID, 
SFYP00, SFXMID, GJBM00, XMDJ01

 )
 AS 
select
      A.QDRQ00	,    --清单号
      '00:00:00',    --操作时间
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      A.JZDH00  ,    --结帐单号
      B.XMBH00	,    --医疗收费编码
      B.XMMC00	,    --项目名称
     substrb(decode(C.GG0000,'',c.BZ0000,C.GG0000),1,60)  ,    --药品规格
      C.DW0000  ,    --单位
      DECODE(SIGN(B.XMDJ00-1),-1,'0','')||to_char(B.XMDJ00)||
      DECODE(E.FBBH00,1,'','['||to_char(B.ZFBL00*100)||'%]') XMDJ00, --单价
      --B.XMDJ00	,    --单价
  SUM(B.XMSL00)	,    --数量
  SUM(B.HJJE00) ,    --合计金额
      D.BH0000  ,    --发票项目编号
      B.ZFBL00  ,    --自付比率
  sum(B.ZFJE00) ,        --自付金额
  sum(B.JZJE00) ,
      D.XMMC00  ,
      D.FPXMID,
      'N' ,
      C.SFXMID ,
      C.GJBM00,
      DECODE(SIGN(B.XMDJ00-1),-1,'0','')||to_char(B.XMDJ00)
from  ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 E
where A.DJH000 = B.DJH000
  and B.XMBH00 = C.SFXMID
  and C.ZYFPID = D.FPXMID
  and A.BRID00 = E.BRID00
  and (B.XMBH00 < 9999999990  or  JMBZ00 <> 0)  --非减免,非优惠的
group  by  D.XMMC00 ,A.QDRQ00,A.ZYID00,A.ZYH000,A.JZDH00,B.XMBH00,B.XMMC00,C.DW0000, DECODE(SIGN(B.XMDJ00-1),-1,'0','')||TO_CHAR(B.XMDJ00)||
      DECODE(E.FBBH00,1,'','['||to_char(B.ZFBL00*100)||'%]'),D.BH0000,B.ZFBL00,D.FPXMID,C.SFXMID,decode(C.GG0000,'',c.BZ0000,C.GG0000),C.GJBM00,
      DECODE(SIGN(B.XMDJ00-1),-1,'0','')||to_char(B.XMDJ00)
union all
select
      A.QDRQ00	,    --清单号
      '00:00:00',
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      A.JZDH00  ,    --结帐单号
      C.YPNM00  ,    --药品内码
      C.YPMC00  ,    --药品名称
      C.YPGG00  ,    --药品规格
      C.CFDW00  ,    --单位
      DECODE(SIGN(C.LSDJ00-1),-1,'0','')||to_char(round(C.LSDJ00*100)/100)||
      DECODE(G.FBBH00,1,'','['||to_char(C.ZFBL00*100)||'%]') LSDJ00, --单价
      --C.LSDJ00  ,    --单价
      SUM(C.YPZSL0)  ,        --数量
      SUM(ROUND(C.LSDJ00*C.YPZSL0,2)), --金额
      F.BH0000  ,    --发票项目编号
      C.ZFBL00  ,    --自付比率
      SUM(ROUND(C.ZFJE00,2)),       --自付金额
     sum(ROUND(C.LSDJ00*C.YPZSL0-C.ZFJE00-C.GFJE00,2)) ,
      F.XMMC00 ,
      F.FPXMID,
      'Y',
      C.YPNM00,
      E.GJBM00,
      DECODE(SIGN(C.LSDJ00-1),-1,'0','')||to_char(round(C.LSDJ00*100)/100)
from  ZY_BRFY00 A,yf_zycf00 B,yf_zycfmx C ,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G  --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  A.BRID00 = G.BRID00
group by  F.XMMC00 ,A.QDRQ00,A.ZYID00,A.ZYH000,A.JZDH00,C.YPNM00,C.YPMC00,C.YPGG00,C.CFDW00,DECODE(SIGN(C.LSDJ00-1),-1,'0','')||TO_CHAR(round(C.LSDJ00*100)/100)||
      DECODE(G.FBBH00,1,'','['||to_char(C.ZFBL00*100)||'%]'),F.BH0000,C.ZFBL00,F.FPXMID,C.YPNM00,E.GJBM00,DECODE(SIGN(C.LSDJ00-1),-1,'0','')||to_char(round(C.LSDJ00*100)/100)
union all
select
      A.QDRQ00	,    --清单号
      '00:00:00',
      A.ZYID00  ,    --住院ID
      A.ZYH000  ,    --住院号
      A.JZDH00  ,    --结帐单号
      B.YPNM00  ,    --药品内码
      B.YPMC00  ,    --药品名称
      B.YPGG00  ,    --药品规格
      B.QLDW00  ,    --单位
      DECODE(SIGN(B.LSDJ00-1),-1,'0','')||to_char(round(B.LSDJ00*100)/100)||
      DECODE(G.FBBH00,1,'','['||to_char(B.ZFBL00*100)||'%]') LSDJ00, --单价
      --B.LSDJ00  ,    --单价
      SUM(B.YPZSL0)  ,        --数量
      SUM(ROUND(B.LSDJ00*B.YPZSL0,2)), --金额
      F.BH0000  ,    --发票项目编号
      B.ZFBL00  ,    --自付比率
      SUM(ROUND(B.ZFJE00,2)),       --自付金额
      sum(ROUND(B.LSDJ00*B.YPZSL0-B.ZFJE00-B.GFJE00,2)) ,
      F.XMMC00 ,
      F.FPXMID,
      'Y',
      B.YPNM00,
      E.GJBM00,
      DECODE(SIGN(B.LSDJ00-1),-1,'0','')||to_char(round(B.LSDJ00*100)/100)
from  ZY_BRFY00 A,yf_yzypsq B,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G    --yf_yzypsq的总数量有含有中药的帖数
where  A.DJH000 = B.DJH000
  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID
  and  E.ZYFPID = F.FPXMID
  and  A.BRID00 = G.BRID00
group by  F.XMMC00 ,A.QDRQ00,A.ZYID00,A.ZYH000,A.JZDH00,B.YPNM00,B.YPMC00,B.YPGG00,B.QLDW00,DECODE(SIGN(B.LSDJ00-1),-1,'0','')||TO_CHAR(round(B.LSDJ00*100)/100)||
      DECODE(G.FBBH00,1,'','['||to_char(B.ZFBL00*100)||'%]'),F.BH0000,B.ZFBL00,F.FPXMID,B.YPNM00,E.GJBM00,DECODE(SIGN(B.LSDJ00-1),-1,'0','')||to_char(round(B.LSDJ00*100)/100)
/