create or replace view vw_zy_rqdhz0_ybxm as select a.BRID00, --1.病人ID a.ZYID00, --2.住院ID a.ZYH000, --3.住院号 a.XM0000, --4.姓名 a.XMBH00, --5.编码 医疗收费编码或药品编码 a.GJBM00, --6.国家编码 a.YBXMBH, --7.医保项目编号 a.YPFB00, --8.医保药品费别 a.XMMC00, --9.项目名称 a.ZLXMJC, --10.诊疗项目简称 a.XMGG00, --11.药品才有规格 a.XMDW00, --12.单位 a.CZRQ00, --13.操作日期 a.QDRQ00, --14.清单日期 a.ZYFPID, --15.住院发票ID a.FPXMBH, --16.发票项目编号 a.FPXMMC, --17.发票项目名称 a.ZFBL00, --18.医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1 a.XMSL00, --19.数量 a.HJJE00, --20.合计金额 a.XMDJ00, --21.单价 a.ZFJE00, --22.自费金额 a.GFJE00, --23.公费金额 a.JZJE00, --24.记帐金额 a.JMJE00, --25.减免金额 a.JZDH00, --26.结账单号 a.YEXM00, --27.婴儿姓名 a.SFLB00, --28.收费类别 a.ZXKS00, --29.执行科室 a.ZXKSMC, --30.执行科室名称 (select YBJSDJ from BM_YBSFXM where XMBH00=a.YBXMBH and (FBBH00,YBZXLB)=(select FBBH00,YBZXLB from ZY_BRXXB0 where ZYID00=a.ZYID00)) YBJSDJ, --31.医保结算单价 decode(a.SFLB00,3,(select YPBM00 from BM_YD0000 where YPNM00 = a.XMBH00), (select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00)) as SFXMBH,--32.收费项目编号(收费项目或药品编号) a.GJBZBM, --33.国家医保编码 a.GJBZMC --34.国家医保名称 from ( select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.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,'0','医保项目','1','部分自费项目','2','非医保项目','3','补充的非医保项目', '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, --医保药品费别*/ (select decode(c.YBLB00,'0','医保甲类','1','医保乙类','2','医保丙类',--'3','补充的非医保项目', '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','',max(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, --执行科室名称 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')) GJBZBM, --国家医保编码 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2')) GJBZMC --国家医保名称 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.GJBM00,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, nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')), nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2')) union all select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.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,'0','医保甲类','1','医保乙类','2','医保丙类',--'3','补充的非医保项目', '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','',max(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, --执行科室名称 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')) GJBZBM, --国家医保编码 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2')) GJBZMC --国家医保名称 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.GJBM00,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, nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')), nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2')) ) a ;