create or replace view vw_zy_rqdhz0_new as select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.GJBM00, --国家编码 null as YBXMBH, --医保项目编号(本字段要取空值) null 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 SF_ZY_HQZFBL_NEW(a.ZYID00,a.XMBH00,decode(a.SFLB00,'3','Y','N'),a.ZFBL00,a.ZFFSBZ,a.JZDH00)*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, --收费项目编号(收费项目或药品编号) null as YBJSDJ, --医保结算单价 null as YBJSDJ_EX, --医保结算单价+自付比例 nvl(a.GJBZBM,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1'),1,50)) as GJBZBM, --国家医保编码 nvl(a.GJBZMC,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2'),1,220)) as GJBZMC, --国家医保名称 decode(a.SFLB00,'2',(select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00),a.GJBM00) as XMBH01, --项目编号1 decode(a.SFLB00,'3',a.GJBM00,(select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00)) as XMBH02, --项目编号2 decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,2)) as YWKSRQ, --业务开始日期 decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,3)) as YWJSRQ --业务结束日期 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, SF_ZY_HQZFBL_NEW(a.ZYID00,a.XMBH00,decode(a.SFLB00,'3','Y','N'),a.ZFBL00,a.ZFFSBZ,a.JZDH00), a.YEXM00,a.SFLB00,a.XMDJ00,a.ZFFSBZ, nvl(a.GJBZBM,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1'),1,50)), nvl(a.GJBZMC,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2'),1,220)), decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,2)), decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,3)) --having(sum(a.XMSL00)<>0) union all select a.BRID00, --病人ID a.ZYID00, --住院ID a.ZYH000, --住院号 a.XM0000, --姓名 a.XMBH00, --编码 医疗收费编码或药品编码 a.GJBM00, --国家编码 null as YBXMBH, --医保项目编号(本字段要取空值) null 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 SF_ZY_HQZFBL_NEW(a.ZYID00,a.XMBH00,decode(a.SFLB00,'3','Y','N'),a.ZFBL00,a.ZFFSBZ,a.JZDH00)*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, --收费项目编号(收费项目或药品编号) null as YBJSDJ, --医保结算单价 null as YBJSDJ_EX, --医保结算单价+自付比例 nvl(a.GJBZBM,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1'),1,50)) GJBZBM, --国家医保编码 nvl(a.GJBZMC,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2'),1,220)) GJBZMC, --国家医保名称 decode(a.SFLB00,'2',(select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00),a.GJBM00) as XMBH01, --项目编号1 decode(a.SFLB00,'3',a.GJBM00,(select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00)) as XMBH02, --项目编号2 decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,2)) as YWKSRQ, --业务开始日期 decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,3)) as YWJSRQ --业务结束日期 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, SF_ZY_HQZFBL_NEW(a.ZYID00,a.XMBH00,decode(a.SFLB00,'3','Y','N'),a.ZFBL00,a.ZFFSBZ,a.JZDH00), a.YEXM00,a.SFLB00,a.XMDJ00,a.ZFFSBZ, nvl(a.GJBZBM,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1'),1,50)), nvl(a.GJBZMC,substr(SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2'),1,220)), decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,2)), decode(a.SFLB00,'3',null,SF_ZY_GETXMYWQZRQ(a.ZYID00,a.DJID00,3)) --having(sum(a.XMSL00)<>0) ;