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, ybzfdj)
AS
select
--      A.QDRQ00    ,    --清单日期
      decode(A.CXDJH0,0,A.QDRQ00,decode(sign((instrb(nvl(G.CXYY00,' '),',FBZH'))),1,G.QDRQ00,A.QDRQ00)) QDRQ00, --清单日期
      A.ZYID00  ,    --住院ID
      B.XMBH00  ,    --医疗收费编码
      B.XMMC00,      --项目名称
      decode(SF_ZY_GET_XTCS00('ZY_RQDSFXSSSYBZXYBSX','N'),'Y',
      substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)||
      decode((select nvl(max(SFSX00),0) from VW_ZY_BRZFBL J,IC_YBBRLB K where K.fbbh00=E.FBBH00 and K.yblb00=E.YBLB00
              and J.fbbh00=K.fbbh00 and J.ybzxlb=K.ybzxlb  and J.sfsx00>0 and J.sfxmid=B.XMBH00),0,'',
             (select '(医保上限:'||nvl(max(SFSX00),0)||')' from VW_ZY_BRZFBL J,IC_YBBRLB K where K.fbbh00=E.FBBH00 and K.yblb00=E.YBLB00
              and J.fbbh00=K.fbbh00 and J.ybzxlb=K.ybzxlb  and J.sfsx00>0 and J.sfxmid=B.XMBH00)),
      substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)||decode(sign(b.XMDJ00-decode(b.SFSX00,0,b.XMDJ00,b.SFSX00)),1,'(医保上限'||to_char(b.SFSX00)||')',decode(instrb(b.XMMC00,'(差价)'),0,'','(医保上限'||to_char(b.SFSX00)||')')) ),    --药品规格
      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  ,
      --(SELECT B.GJBM00||decode(c.JSXM00,'2',decode(NVL(trim(MAX(N.VALUE0)),'Y'),'Y','/'||C.GJBM00,''),'') FROM XT_XTCS00 N WHERE N.NAME00(+)='ZY_QDXSCLZXGJBM') GJBM00,
      (SELECT decode(c.JSXM00,'2','',B.GJBM00)||decode(c.JSXM00,'2',decode(NVL(trim(MAX(N.VALUE0)),'Y'),'Y',C.GJBM00,''),'') FROM XT_XTCS00 N WHERE N.NAME00(+)='ZY_QDXSCLZXGJBM') GJBM00,
      'N',
      B.GFJE00  ,
      B.XMBH00  ,
      A.CZRQ00  ,
      A.CZSJ00  ,
      A.JZDH00  ,
      A.YBFSDH  ,
      M.BMMC00  ,
      0
from  ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BMBM00 M,ZY_BRXXB0 E,ZY_BRFY00 G
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  and a.ZYID00=E.ZYID00 and A.CXDJH0=G.DJH000(+) --非减免,非优惠的
  --and b.xmbh00<>'8000129147'
union all
select
--      A.QDRQ00    ,    --清单号
      decode(A.CXDJH0,0,A.QDRQ00,decode(sign((instrb(nvl(G.CXYY00,' '),',FBZH'))),1,G.QDRQ00,A.QDRQ00)) QDRQ00, --清单日期
      A.ZYID00  ,    --住院ID
      C.YPNM00  ,    --药品内码
      C.YPMC00  ,    --药品名称
      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,
      (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(s.YBXMBH,y.YPBM00) from BM_YD0000 y,ZY_BRXXB0 z,IC_YBBRLB l,BM_YBSFDY s where c.YPNM00=y.YPNM00 and z.ZYID00=a.ZYID00 and
     --    z.YBLB00=l.YBLB00 and z.FBBH00=l.FBBH00 and c.YPNM00=s.SFXMID and s.SFYP00='Y' and l.YBZXLB=s.YBZXLB),
      'Y',
      Round(DECODE(C.SFZBY0,'Y',0,C.GFJE00),2), --减免金额
      C.YPNM00  ,
      A.CZRQ00  ,
      A.CZSJ00  ,
      A.JZDH00  ,
      A.YBFSDH  ,
      M.BMMC00  ,
      --YD.YBZFDJ     --医保支付金额
       decode((select yyid00 from xt_yyxx00),'229112',(select round((ybzgdj*yd.z2j000/yd.k2j000),2) from bm_ybsfdy where sfxmid=c.ypnm00 and ybzxlb='1'),yd.ybzfdj) YBZFDJ
from  ZY_BRFY00 A,YF_ZYCF00 B,YF_ZYCFMX C ,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BMBM00 M,ZY_BRFY00 G,BM_YD0000 YD  --yf_zycfmx没有含有中药帖数
where  A.DJH000 = B.DJH000  and  B.CFLSH0 = C.CFLSH0  and  B.YPDLBH = D.LBBH00 and  YD.YPNM00 = C.YPNM00
  and  D.SFDM00 = E.SFXMID  and  E.ZYFPID = F.FPXMID and M.BMBH00=B.YFBMBH and A.CXDJH0=G.DJH000(+)
union all
select
--      A.QDRQ00    ,    --清单号
      decode(A.CXDJH0,0,A.QDRQ00,decode(sign((instrb(nvl(N.CXYY00,' '),',FBZH'))),1,N.QDRQ00,A.QDRQ00)) QDRQ00, --清单日期
      A.ZYID00  ,    --住院ID
      B.YPNM00  ,    --药品内码
      B.YPMC00  ,    --药品名称
      B.YPGG00  ,    --药品规格
      B.QLDW00  ,    --单位
      B.LSDJ00  ,    --单价
      B.YPZSL0  ,    --数量
      --2009.06.15 注释 decode(sign(a.djh000-1000000000),1,ROUND(B.LSDJ00*B.YPZSL0,2),b.ZFJE00), --金额
      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,
      (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(s.YBXMBH,y.YPBM00) from BM_YD0000 y,ZY_BRXXB0 z,IC_YBBRLB l,BM_YBSFDY s where b.YPNM00=y.YPNM00 and z.ZYID00=a.ZYID00 and
      --   z.YBLB00=l.YBLB00 and z.FBBH00=l.FBBH00 and b.YPNM00=s.SFXMID and s.SFYP00='Y' and l.YBZXLB=s.YBZXLB),
      'Y',
      B.GFJE00  ,--减免金额
      B.YPNM00  ,
      A.CZRQ00  ,
      A.CZSJ00  ,
      A.JZDH00  ,
      A.YBFSDH  ,
      M.BMMC00  ,
      --YD.YBZFDJ     --医保支付金额
      decode((select yyid00 from xt_yyxx00),'229112',(select round((ybzgdj*yd.z2j000/yd.k2j000),2) from bm_ybsfdy where sfxmid=b.ypnm00 and ybzxlb='1'),yd.ybzfdj) YBZFDJ
from  ZY_BRFY00 A,YF_YZYPSQ B,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G,BM_BMBM00 M,ZY_BRFY00 N,BM_YD0000 YD   --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  and  A.BRID00 = G.BRID00  and  YD.YPNM00 = B.YPNM00 and  A.CXDJH0=N.DJH000(+);