CREATE OR REPLACE VIEW SD_HOSPITAL.VW_SF_RQDMX0
 (
 QDH000, XM0000, MZID00, MZH000, GHID00, XMBH00, XMMC00, XMGG00, XMDW00, 
XMDJ00, XMSL00, HJJE00, ZFJE00, GFJE00, JZJE00, BH0000, FPXMMC, SFYP00, 
ZFBL00

 )
 AS 
select
      B.CZRQ00	,      --清单号
      K.BRXM00  ,      --姓名
      B.BRID00  ,      --门诊ID(实为病人ID)
      K.BRBLH0  ,      --门诊号
      B.MZID00  ,      --挂号ID(门诊ID)
      A.XMBH00	,      --医疗收费编码
      Trim(A.XMMC00),  --项目名称
      ' '       ,      --药品规格
      C.DW0000  ,      --单位
      DECODE(SIGN(A.XMDJ00-1),-1,'0','')||TO_CHAR(A.XMDJ00)||
      DECODE(k.FBBH00,1,'','['||to_char(A.ZFBL00,'0.99')||']') XMDJ00,
      --A.XMDJ00       --单价
      SUM(A.XMSL00),   --数量
      Round(SUM(A.HJJE00),2) ,    --合计金额
      Round(SUM(A.ZFJE00),2) ,    --自付金额
      Round(SUM(A.GFJE00),2) ,    --公费减免金额
      Round(SUM(A.JZJE00),2) ,    --计账金额
      D.BH0000  ,      --发票项目编号
      D.XMMC00  ,      --发票项目名称
      'N',             --是否药品
      A.ZFBL00
from  SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 K
where A.DJH000 = B.DJH000
  and B.BRID00 = K.BRID00
  and A.XMBH00 = C.SFXMID
  and C.MZFPID = D.FPXMID
  and (A.XMBH00 <> 9999999991  or  JMBZ00 <> 0)
  and (A.XMBH00 <> 9999999997  or  JMBZ00 <> 0)  --非减免,非优惠的
  and (A.XMBH00 <> 9999999998  or  JMBZ00 <> 0)
  and (A.XMBH00 <> 9999999999  or  JMBZ00 <> 0)
group  by  B.CZRQ00,K.BRXM00,A.XMBH00,A.XMMC00,C.DW0000,DECODE(SIGN(A.XMDJ00-1),-1,'0','')||TO_CHAR(A.XMDJ00)||
      DECODE(k.FBBH00,1,'','['||to_char(A.ZFBL00,'0.99')||']'),D.BH0000,B.BRID00,B.MZID00,K.BRBLH0,D.XMMC00,A.ZFBL00
union all
select
      A.CZRQ00	,      --清单号
      K.BRXM00  ,      --姓名
      A.BRID00  ,      --门诊ID(实为病人ID)
      K.BRBLH0  ,      --门诊号
      A.MZID00  ,      --挂号ID(门诊ID)
      C.YPNM00  ,      --药品内码
      Trim(C.YPMC00),  --药品名称
      C.YPGG00  ,      --药品规格
      C.CFDW00  ,      --单位
      DECODE(SIGN(C.LSDJ00-1),-1,'0','')||TO_CHAR(C.LSDJ00)||
      DECODE(k.FBBH00,1,'','['||to_char(c.ZFBL00,'0.99')||']'),
      --C.LSDJ00  ,    --单价
      SUM(DECODE(C.SFZBY0,'Y',0,C.YPZSL0)),  --数量,若是自备药,则将其数量改为0
      Round(SUM(round(C.LSDJ00*DECODE(C.SFZBY0,'Y',0,C.YPZSL0)*100)/100),2), --合计金额
      Round(SUM(DECODE(C.SFZBY0,'Y',0,C.ZFJE00)),2), --自付金额
      Round(SUM(DECODE(C.SFZBY0,'Y',0,C.GFJE00)),2), --公费减免金额
      0,                      --计账金额      
      F.BH0000  ,      --发票项目编号
      F.XMMC00  ,      --发票项目名称
      'Y',             --是否药品
      C.ZFBL00         --自付比例
from  SF_BRFY00 A,yf_mzcf00 B,yf_mzcfmx C,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F ,BM_BRXXB0 K
where  A.DJH000 = B.DJH000
  and  A.BRID00 = K.BRID00
  and  B.cflsh0 = C.cflsh0
  and  c.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID
  and  E.MZFPID = F.FPXMID
group by  A.CZRQ00,K.BRXM00,C.YPNM00,C.YPMC00,C.YPGG00,C.CFDW00,DECODE(SIGN(C.LSDJ00-1),-1,'0','')||TO_CHAR(C.LSDJ00)||
      DECODE(k.FBBH00,1,'','['||to_char(c.ZFBL00,'0.99')||']'),F.BH0000,A.BRID00,A.MZID00,K.BRBLH0,F.XMMC00,C.ZFBL00
/