create or replace view vw_zy_rqdhz0_ybxm_new as select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.GJBM00, --国家编码 a.YBXMBH, --医保项目编号 a.YPFB00, --医保药品费别 a.XMMC00, --项目名称 a.ZLXMJC, --诊疗项目简称 a.XMGG00, --药品才有规格 a.XMDW00, --单位 a.CZRQ00, --操作日期 a.QDRQ00, --清单日期 a.ZYFPID, --住院发票ID a.FPXMBH, --发票项目编号 a.FPXMMC, --发票项目名称 a.ZFBL00, --医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1 a.XMSL00, --数量 a.HJJE00, --合计金额 a.XMDJ00, --单价 a.ZFJE00, --自费金额 a.GFJE00, --公费金额 a.JZJE00, --记帐金额 a.JMJE00, --减免金额 a.JZDH00, --结账单号 a.YEXM00, --婴儿姓名 a.SFLB00, --收费类别 a.ZXKS00, --执行科室 a.ZXKSMC, --执行科室名称 a.SFXMBH, --收费项目编号(收费项目或药品编号) (select YBJSDJ from BM_YBSFXM where XMBH00=a.YBXMBH and (FBBH00,YBZXLB)=(select FBBH00,YBZXLB from ZY_BRXXB0 where ZYID00=a.ZYID00)) YBJSDJ --医保结算单价 from ( select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.GJBZBM as GJBM00, --国家编码 (select max(c.YBXMBH) from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YBXMBH, --医保项目编号 (select decode(c.YBLB00,'4','甲类药品','5','乙类药品','7','非医保','8','医保') from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YPFB00, --医保药品费别 a.XMMC00, --项目名称 decode(a.SFLB00,'3','',g.ZLXMJC) as ZLXMJC, --诊疗项目简称 a.XMGG00, --药品才有规格 a.XMDW00, --单位 a.CZRQ00, --操作日期 nvl(a.QDRQ00,a.CZRQ00) as QDRQ00, --清单日期 a.ZYFPID, --住院发票ID f.BH0000 as FPXMBH, --发票项目编号 f.XMMC00 as FPXMMC, --发票项目名称 decode(nvl(a.ZFFSBZ,'N'),'Y',1,a.ZFBL00)*100 as ZFBL00, --医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1 sum(a.XMSL00) as XMSL00, --数量 sum(a.HJJE00) as HJJE00, --合计金额 a.XMDJ00, --单价 sum(a.ZFJE00) as ZFJE00, --自费金额 sum(a.GFJE00) as GFJE00, --公费金额 sum(a.JZJE00) as JZJE00, --记帐金额 sum(a.JMJE00) as JMJE00, --减免金额 a.JZDH00, --结账单号 a.YEXM00, --婴儿姓名 a.SFLB00, --收费类别 a.ZXKS00, --执行科室 (select BMMC00 from BM_BMBM00 where BMBH00 = a.ZXKS00) as ZXKSMC, --执行科室名称 decode(a.SFLB00,3,(select YPBM00 from BM_YD0000 where YPNM00 = a.XMBH00),(select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00)) as SFXMBH --收费项目编号(收费项目或药品编号) from ZY_FYMX00 a,BM_FPXM00 f,YJ_YW0000 g where a.ZYFPID = f.FPXMID and a.ZYID00 = g.ZYGHID(+) and a.DJID00 = g.YJDJH0(+) and a.XMSL00 > 0 group by a.BRID00,a.ZYID00,a.ZYH000,a.XM0000,a.JZDH00,a.XMBH00,a.GJBZBM,a.XMMC00,ZXKS00, a.XMGG00,a.XMDW00,a.CZRQ00,nvl(a.QDRQ00,a.CZRQ00),a.ZYFPID,f.BH0000,f.XMMC00, a.ZFBL00,a.YEXM00,a.SFLB00,a.XMDJ00,a.ZFFSBZ,decode(a.SFLB00,'3','',g.ZLXMJC) union all select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.GJBZBM as GJBM00, --国家编码 (select max(c.YBXMBH) from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YBXMBH, --医保项目编号 (select decode(c.YBLB00,'4','甲类药品','5','乙类药品','7','非医保','8','医保') from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YPFB00, --医保药品费别 a.XMMC00, --项目名称 decode(a.SFLB00,'3','',g.ZLXMJC) as ZLXMJC, --诊疗项目简称 a.XMGG00, --药品才有规格 a.XMDW00, --单位 a.CZRQ00, --操作日期 nvl(a.QDRQ00,a.CZRQ00) as QDRQ00, --清单日期 a.ZYFPID, --住院发票ID f.BH0000 as FPXMBH, --发票项目编号 f.XMMC00 as FPXMMC, --发票项目名称 decode(nvl(a.ZFFSBZ,'N'),'Y',1,a.ZFBL00)*100 as ZFBL00, --医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1 sum(a.XMSL00) as XMSL00, --数量 sum(a.HJJE00) as HJJE00, --合计金额 a.XMDJ00, --单价 sum(a.ZFJE00) as ZFJE00, --自费金额 sum(a.GFJE00) as GFJE00, --公费金额 sum(a.JZJE00) as JZJE00, --记帐金额 sum(a.JMJE00) as JMJE00, --减免金额 a.JZDH00, --结账单号 a.YEXM00, --婴儿姓名 a.SFLB00, --收费类别 a.ZXKS00, --执行科室 (select BMMC00 from BM_BMBM00 where BMBH00 = a.ZXKS00) as ZXKSMC, --执行科室名称 decode(a.SFLB00,3,(select YPBM00 from BM_YD0000 where YPNM00 = a.XMBH00),(select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00)) as SFXMBH --收费项目编号(收费项目或药品编号) from ZY_FYMX00 a,BM_FPXM00 f,YJ_YW0000 g where a.ZYFPID = f.FPXMID and a.ZYID00 = g.ZYGHID(+) and a.DJID00 = g.YJDJH0(+) and a.XMSL00 < 0 group by a.BRID00,a.ZYID00,a.ZYH000,a.XM0000,a.JZDH00,a.XMBH00,a.GJBZBM,a.XMMC00,ZXKS00, a.XMGG00,a.XMDW00,a.CZRQ00,nvl(a.QDRQ00,a.CZRQ00),a.ZYFPID,f.BH0000,f.XMMC00, a.ZFBL00,a.YEXM00,a.SFLB00,a.XMDJ00,a.ZFFSBZ,decode(a.SFLB00,'3','',g.ZLXMJC) ) a ;