create or replace view vw_zy_chargedetail as select aa.JZDH00 as JZDH00, --HIS结账单号 aa.JZDH00 as ywlsh, --ywlsh 业务流水号 是 string bb.FPXMID as FPXMID, --发票项目ID bb.BH0000 as FPXMBH, --发票项目编码 --1 as PXXH00, --sortNo 序号 Integer 不限 是 默认从1开始，每个收费项目序号值递增1，本次不允许重复 max(decode(SF_SF_TYZD00('第三方电子发票平台接口','chargeCode'),'1',bb.ZYFPBM,bb.ZYFPBM)) as chargeCode, --chargeCode 收费项目代码 String 50 是 填写业务系统内部编码值，由医疗平台配置对照 aa.XMMC00 as chargeName, --chargeName 收费项目名称 String 100 是 填写业务系统内部项目名称 '无' as unit, --unit 计量单位 String 20 否 sum(aa.XMJE00) as std, --std 收费标准 Number 14,2 是 1 as num, --number 数量 Number 14,2 是 sum(aa.XMJE00) as amt, --amt 金额 Number 14,2 是 sum(aa.XMJE00) as selfAmt, --selfAmt 自费金额 Number 14,2 是 如无金额，填写0 '' as remark --remark 备注 String 200 否 from ZY_JZMX00 aa,BM_FPXM00 bb where aa.XMBH00=bb.FPXMID group by aa.JZDH00,bb.FPXMID,bb.BH0000,aa.XMMC00 ; 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.国家医保名称 a.ZYFPBM --35.电子发票接口-门诊发票编码 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','医保丙类', '4','甲类药品','5','乙类药品','7','丙类药品') 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, --发票项目名称 f.ZYFPBM as ZYFPBM, --电子发票接口-门诊发票编码 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,f.ZYFPBM, 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','医保丙类', '4','甲类药品','5','乙类药品','7','丙类药品') 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, --发票项目名称 f.ZYFPBM as ZYFPBM, --电子发票接口-门诊发票编码 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,f.ZYFPBM, 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 ; create or replace view vw_zy_listdetail as select a.BRID00 as BRID00, --病人ID a.ZYID00 as ZYID00, --住院ID a.ZYH000 as ZYH000, --住院号 a.JZDH00 as JZDH00, --HIS结账单号 a.JZDH00 as ywlsh, --ywlsh 业务流水号是 string a.XMBH00 as XMBH00, --项目编码 substrb(a.XMMC00,1,50) as name, --name 药品名称 String 50 是 如药品名称，器材名称等 a.XMDJ00 as std, --std 单价 Number 14,6 是 sum(a.XMSL00) as num, --number 数量 Number 14,6 是 sum(a.HJJE00) as amt, --amt 金额 Number 14,6 是 sum(a.HJJE00) as selfAmt, --selfAmt 自费金额 Number 14,6 是 如无金额，填写0 max(a.QDRQ00) as listDetailNo, --listDetailNo 明细流水号 String 60 否 明细流水号 max(decode(SF_SF_TYZD00('第三方电子发票平台接口','chargeCode'),'1',a.ZYFPBM,a.ZYFPBM)) as chargeCode, --chargeCode 收费项目代码 String 50 否 填写业务系统内部编码值，由医疗平台配置对照,如：床位费、检查费 max(a.FPXMMC) as chargeName, --chargeName 收费项目名称 String 100 否 max(a.QDRQ00) as prescribeCode, --prescribeCode 处方编码 String 60 否 '' as listTypeCode, --listTypeCode 药品类别编码 String 50 否 如药品分类编码01，有则填写 '' as listTypeName, --listTypeName 药品类别名称 String 50 否 如药品分类名称，抗生素类抗感染药物 substrb(max(nvl(a.YBXMBH,nvl(trim(GJBM00),a.XMBH00))),1,50) as code, --code 编码 String 50 否 如药品编码，有则填写 '' as form, --form 剂型 String 50 否 substrb(max(a.XMGG00),1,50) as specification, --specification 规格 String 50 否 substrb(max(a.XMDW00),1,20) as unit, --unit 计量单位 String 20 否 '' as receivableAmt, --receivableAmt 应收费用 Number 14,6 否 '' as medicalCareType, --medicalCareType 医保药品分类 String 1 否 1：无自负/甲2：有自负/乙3：全自负/丙 '' as medCareItemType, --medCareItemType 医保项目类型 String 100 否 '' as medReimburseRate, --medReimburseRate 医保报销比例 Number 3,2 否 '' as remark, --remark 备注 String 200 否 1 as PXXH00, --sortNo 序号 Integer 不限 否 序号 '' as chrgtype --chrgtype 费用类型 String 50 否 from VW_ZY_RQDHZ0_YBXM a where a.HJJE00<>0 group by a.BRID00,a.ZYID00,a.ZYH000,a.JZDH00,a.XMMC00,a.XMDJ00,a.XMBH00 ;