CREATE OR REPLACE VIEW VW_SF_RQDMX8
(gjbm00, gjbm01, qdh000, xm0000, mzid00, mzh000, ghid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, zfje00, gfje00, jzje00, bh0000, fpxmmc, sfyp00, zfbl00, xmzt00, jzdh00, jzrq00, jzsj00, grzhzf, tcjjzf, zfje01, brid00, ybzfdj)
AS
select
      trim(A.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,'')),
      A.GJBM00  ,      --国家编码(排序用)
      B.CZRQ00  ,      --清单号
      K.BRXM00  ,      --姓名
      B.BRID00  ,      --门诊ID(实为病人ID)
      K.BRBLH0  ,      --门诊号
      B.MZID00  ,      --挂号ID(门诊ID)
      A.XMBH00  ,      --医疗收费编码
      Trim(A.XMMC00),  --项目名称
      trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)) XMGG00,    --项目规格
      trim(C.DW0000)  ,      --单位
      Round((decode(A.JMBZ00,'4',A.HJJE00+A.JMJE00,A.HJJE00))/(decode((A.XMSL00),0,1,(A.XMSL00))),2)    ,      --单价
      (A.XMSL00) ,  --数量
      (decode(A.JMBZ00,'4',A.HJJE00+A.JMJE00,A.HJJE00)) ,  --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额
      (decode(A.JMBZ00,'4',A.ZFJE00+A.JMJE00,A.ZFJE00)) ,  --自付金额
      (A.GFJE00) ,  --减免金额
      (A.JZJE00) ,  --计账金额
      D.BH0000  ,      --发票项目编号
      D.XMMC00  ,      --发票项目名称
      'N',             --是否药品
      (to_char(SF_SF_HQZFBL(b.MZID00,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00,      --自付比率
      decode((select XMZT00 from YJ_YW0000 E,YJ_YWJJ00 F 
               where E.YJDJH0=F.YJDJH0 AND E.SFDJH0=A.DJH000 
               and F.SFXMID=A.XMBH00 and rownum=1),'2','N','3','Y','Y') XMZT00,
      JZDH00,
      (SELECT JZRQ00 FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT JZSJ00 FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT GRZHZF FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT TCJJZF FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT ZFJE00 FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
       B.BRID00,
       0
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 < 9999999990  or  JMBZ00 = '3')  --增加药费的事后减免
union all
select
        (select trim(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') GJBM00,
      '',
      A.CZRQ00  ,    --清单号
      K.BRXM00  ,    --姓名
      A.BRID00  ,    --门诊ID(实为病人ID)
      K.BRBLH0  ,    --门诊号
      A.MZID00  ,    --挂号ID(门诊ID)
      C.YPNM00  ,    --药品内码
      Trim(C.YPMC00),    --药品名称
      C.YPGG00  ,    --药品规格
      C.CFDW00  ,    --单位
      C.LSDJ00  ,    --单价
      (DECODE(C.SFZBY0,'Y',0,C.YPZSL0)),  --数量,若是自备药,则将其数量改为0
      (round(C.LSDJ00*DECODE(C.SFZBY0,'Y',0,C.YPZSL0)*100)/100), --金额
      Round((DECODE(C.SFZBY0,'Y',0,C.ZFJE00)),2), --自付金额
      Round((DECODE(C.SFZBY0,'Y',0,C.GFJE00)),2), --减免金额
      0,                                             --计账金额 
      F.BH0000  ,    --发票项目编号
      F.XMMC00  ,    --发票项目名称
      'Y',           --是否药品
      (to_char(SF_SF_HQZFBL(a.MZID00,C.YPNM00,'Y',C.ZFBL00))*100||'%') ZFBL00,    --自付比率
      DECODE(B.CFZT00,'1','N','2','Y','6','Y','Y') XMZT00,
      JZDH00,
      (SELECT JZRQ00 FROM SF_JZB000 WHERE JZDH00=A.JZDH00 AND ROWNUM=1),
      (SELECT JZSJ00 FROM SF_JZB000 WHERE JZDH00=A.JZDH00 AND ROWNUM=1),
      (SELECT GRZHZF FROM SF_JZB000 WHERE JZDH00=A.JZDH00 AND ROWNUM=1),
      (SELECT TCJJZF FROM SF_JZB000 WHERE JZDH00=A.JZDH00 AND ROWNUM=1),
      (SELECT ZFJE00 FROM SF_JZB000 WHERE JZDH00=A.JZDH00 AND ROWNUM=1),
      A.BRID00,
      --G.YBZFDJ   --医保支付金额
      (select round((ybzgdj*g.m2j000/g.k2j000),2) from bm_ybsfdy where sfxmid=c.ypnm00 and ybzxlb='1') 
from  SF_BRFY00 A,yf_mzcf00 B,yf_mzcfmx C,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F ,BM_BRXXB0 K,BM_YD0000 G
where  A.DJH000 = B.DJH000
  and  A.BRID00 = K.BRID00
  and  B.cflsh0 = C.cflsh0
  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID
  and  E.MZFPID = F.FPXMID
  and  G.YPNM00 = C.YPNM00
union all
select
      trim(A.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,'')),
      A.GJBM00  ,      --国家编码(排序用)
      B.CZRQ00  ,      --清单号
      K.BRXM00  ,      --姓名
      B.BRID00  ,      --门诊ID(实为病人ID)
      K.BRBLH0  ,      --门诊号
      B.MZID00  ,      --挂号ID(门诊ID)
      A.XMBH00  ,      --医疗收费编码
      Trim(A.XMMC00)||'(减免)',  --项目名称
      trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)) XMGG00,    --
      trim(C.DW0000)  ,      --单位
      (a.jmje00),--A.XMDJ00 ,     --单价
      -1,          --SUM(A.XMSL00) ,  --数量
      (-a.jmje00),--SUM(A.HJJE00) ,  --合计金额
      (-a.jmje00),--SUM(A.ZFJE00) ,  --自付金额
      0,--SUM(A.GFJE00) ,  --减免金额
      0,--SUM(A.JZJE00) ,  --计账金额
      D.BH0000  ,      --发票项目编号
      D.XMMC00  ,      --发票项目名称
      'N',             --是否药品
      (to_char(SF_SF_HQZFBL(b.MZID00,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00,
      'Y',      --自付比率
      JZDH00,
      (SELECT JZRQ00 FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT JZSJ00 FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT GRZHZF FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT TCJJZF FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      (SELECT ZFJE00 FROM SF_JZB000 WHERE JZDH00=B.JZDH00 AND ROWNUM=1),
      B.BRID00,
      0
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 JMBZ00= '4';