CREATE OR REPLACE VIEW SD_HOSPITAL.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, FPXMID ) 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 s.YBXMBH from BM_YBSFDY s where c.sfxmid=s.SFXMID and s.SFYP00<>'Y' and s.YBZXLB='1') 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, D.FPXMID --发票项目ID 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(+) --非减免,非优惠的 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, --医保支付金额 F.FPXMID 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, --医保支付金额 F.FPXMID 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(+) /