update bm_fpxm00 b set b.mzfpbm='9000107',b.zyfpbm='9000209' where b.fpxmid=1 and b.xmmc00='西药费'; update bm_fpxm00 b set b.mzfpbm='9000109',b.zyfpbm='9000211' where b.fpxmid=2 and b.xmmc00='成药费'; update bm_fpxm00 b set b.mzfpbm='9000109',b.zyfpbm='9000211' where b.fpxmid=3 and b.xmmc00='草药费'; update bm_fpxm00 b set b.mzfpbm='9000111' where b.fpxmid=4 and b.xmmc00='挂号费'; update bm_fpxm00 b set b.zyfpbm='9000201' where b.fpxmid=5 and b.xmmc00='床位费'; update bm_fpxm00 b set b.mzfpbm='9000101',b.zyfpbm='9000202' where b.fpxmid=6 and b.xmmc00='诊察费'; update bm_fpxm00 b set b.zyfpbm='9000207' where b.fpxmid=7 and b.xmmc00='护理费'; update bm_fpxm00 b set b.mzfpbm='9000102',b.zyfpbm='9000203' where b.fpxmid=8 and b.xmmc00='检查费'; update bm_fpxm00 b set b.mzfpbm='9000104',b.zyfpbm='9000204' where b.fpxmid=9 and b.xmmc00='化验费'; update bm_fpxm00 b set b.mzfpbm='9000103',b.zyfpbm='9000205' where b.fpxmid=10 and b.xmmc00='治疗费'; update bm_fpxm00 b set b.mzfpbm='9000105',b.zyfpbm='9000206' where b.fpxmid=11 and b.xmmc00='手术费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=12 and b.xmmc00='救护车费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=13 and b.xmmc00='其他费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=14 and b.xmmc00='放射费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=15 and b.xmmc00='CT费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=16 and b.xmmc00='超声费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=17 and b.xmmc00='输氧费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=18 and b.xmmc00='输血费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=19 and b.xmmc00='麻醉费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=20 and b.xmmc00='麻醉相关'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=21 and b.xmmc00='其它医疗'; update bm_fpxm00 b set b.mzfpbm='9000106',b.zyfpbm='9000208' where b.fpxmid=22 and b.xmmc00='材料费'; update bm_fpxm00 b set b.mzfpbm='9000110',b.zyfpbm='9000212' where b.fpxmid=23 and b.xmmc00='诊疗费'; update bm_fpxm00 b set b.mzfpbm='9000106',b.zyfpbm='9000208' where b.fpxmid=24 and b.xmmc00='特殊材料费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=25 and b.xmmc00='体检费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=26 and b.xmmc00='心脑电图'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=27 and b.xmmc00='肿瘤放射治疗费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=28 and b.xmmc00='婴儿费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=29 and b.xmmc00='伙食费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=30 and b.xmmc00='ICU'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=31 and b.xmmc00='陪伴费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=32 and b.xmmc00='血透费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=33 and b.xmmc00='抢救费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=34 and b.xmmc00='接生费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=35 and b.xmmc00='加床费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=36 and b.xmmc00='注射费'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=37 and b.xmmc00='自费药品材料'; update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=38 and b.xmmc00='药事服务'; commit; create or replace view vw_sf_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.MZFPBM,bb.MZFPBM)) 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 SF_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_sf_rqdmx8 (jzdh00, gjbm00, gjbm01, qdh000, fyrqsj, xm0000, mzid00, mzh000, ghid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, zfje00, gfje00, jzje00, bh0000, fpxmmc, sfyp00, zfbl00, xmzt00, kdks00, kdksmc, kfczy0, kfyxm0, kfks00, kfksmc, zxks00, zxksmc, djh000, ybxj00, fpxmid, fpxmbh, mzfpbm, ybxmbh, gjbzbm, gjbzmc) as select a.JZDH00, (select GJBM00 from BM_YYSFXM where SFXMID=a.XMBH00) GJBM00, a.GJBM00 , --国家编码(排序用) a.CZRQ00 , --清单号 to_char(to_date(a.CZRQ00||a.CZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI') FYRQSJ, b.BRXM00 , --姓名 a.BRID00 , --门诊ID(实为病人ID) b.BRBLH0 , --门诊号 a.MZH000 , --挂号ID(门诊ID) a.XMBH00 , --医疗收费编码 trim(a.XMMC00), --项目名称 trim(substrb(decode(a.SFLB00,'2',c.GG0000,c.BZ0000),1,50)) XMGG00, --项目规格 trim(c.DW0000) , --单位 round((decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00))/(decode((a.XMSL00),0,1,(a.XMSL00))),2) , --单价 (a.XMSL00) , --数量 (decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00)) , --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额 (decode(a.JMBZ00,'4',a.ZFJE00+a.JMJE00,a.ZFJE00)) , --自付金额 (a.GFJE00) , --减免金额 (a.JZJE00) , --计账金额 d.BH0000 , --发票项目编号 d.XMMC00 , --发票项目名称 'N', --是否药品 (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00, --自付比率 decode((select e.XMZT00 from YJ_YW0000 e where e.YJDJH0=a.YJDJH0 AND e.SFDJH0=a.DJH000 and e.MZZYBZ='1' and rownum=1),'2','N','3','Y','Y') XMZT00, a.KDKS00 KDKS00, --开单科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKS00 and rownum=1 ) KDKSMC, --开单科室名称 a.CZY000 KFCZY0, --扣费操作员 a.CZYXM0 KFYXM0, --扣费操作员姓名 a.CZYKS0 KFKS00, --扣费科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.CZYKS0 and rownum=1 ) KFKSMC, --扣费科室名称 a.ZXKS00 ZXKS00, --执行科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.ZXKS00 and rownum=1 ) ZXKSMC, --执行科室名称 a.DJH000, -- substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'N',a.BRID00),1,30) YBXJ00, --医保限价 d.FPXMID as FPXMID, --发票ID d.BH0000 as FPXMBH, --发票编码 d.MZFPBM as MZFPBM, --电子发票接口-门诊发票编码 substr(SF_XT_GETYBXMBH(a.BRFB00,a.YBLB00,a.XMBH00,a.SFLB00),1,50) as YBXMBH, --医保项目编号 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,'N','1')) GJBZBM, --国家医保编码 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,'N','2')) GJBZMC --国家医保名称 from SF_FYMX00 a,BM_BRXXB0 b,BM_YYSFXM c,BM_FPXM00 d where a.BRID00 = b.BRID00 and a.XMBH00 = c.SFXMID and a.MZFPID = d.FPXMID and a.JMBZ00 <> '4' and a.SFLB00 not in ('3') union all select a.JZDH00 , a.GJBM00 , a.GJBM00 , --国家编码(排序用) a.CZRQ00 , --清单号 to_char(to_date(a.CZRQ00||a.CZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI') FYRQSJ, b.BRXM00 , --姓名 a.BRID00 , --门诊ID(实为病人ID) b.BRBLH0 , --门诊号 a.MZH000 , --挂号ID(门诊ID) a.XMBH00 , --医疗收费编码 trim(a.XMMC00)||'(减免)', --项目名称 trim(substrb(decode(a.SFLB00,'2',c.GG0000,c.BZ0000),1,50)) XMGG00, -- trim(c.DW0000) , --单位 (a.JMJE00), --A.XMDJ00 , --单价 -1, --SUM(A.XMSL00) , --数量 (-a.JMJE00), --SUM(A.HJJE00) , --合计金额 (-a.JMJE00), --SUM(A.ZFJE00) , --自付金额 0, --SUM(A.GFJE00) , --减免金额 0, --SUM(A.JZJE00) , --计账金额 d.BH0000 , --发票项目编号 d.XMMC00 , --发票项目名称 'N', --是否药品 (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00 , --自付比率 'Y' , -- a.KDKS00 KDKS00, --开单科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKS00 and rownum=1 ) KDKSMC, --开单科室名称 a.CZY000 KFCZY0, --扣费操作员 a.CZYXM0 KFYXM0, --扣费操作员姓名 a.CZYKS0 KFKS00, --扣费科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.CZYKS0 and rownum=1 ) KFKSMC, --扣费科室名称 a.ZXKS00 ZXKS00, --执行科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.ZXKS00 and rownum=1 ) ZXKSMC, --执行科室名称 a.DJH000, -- substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'N',a.BRID00),1,30) YBXJ00, --医保限价 d.FPXMID as FPXMID, --发票ID d.BH0000 as FPXMBH, --发票编码 d.MZFPBM as MZFPBM, --电子发票接口-门诊发票编码 substr(SF_XT_GETYBXMBH(a.BRFB00,a.YBLB00,a.XMBH00,a.SFLB00),1,50) as YBXMBH, --医保项目编号 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,'N','1')) GJBZBM, --国家医保编码 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,'N','2')) GJBZMC --国家医保名称 from SF_FYMX00 a,BM_BRXXB0 b,BM_YYSFXM c,BM_FPXM00 d where a.BRID00 = b.BRID00 and a.XMBH00 = c.SFXMID and a.MZFPID = d.FPXMID and a.JMBZ00 = '4' and a.SFLB00 not in ('3') union all select a.JZDH00, nvl(a.GJBM00,c.YPBM00) , c.YPBM00 , --国家编码(排序用) a.CZRQ00 , --清单号 to_char(to_date(a.CZRQ00||a.CZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI') FYRQSJ, b.BRXM00 , --姓名 a.BRID00 , --门诊ID(实为病人ID) b.BRBLH0 , --门诊号 a.MZH000 , --挂号ID(门诊ID) a.XMBH00 , --医疗收费编码 a.XMMC00 , --项目名称 a.XMGG00 , --项目规格 a.XMDW00 , --单位 round( (a.HJJE00)/(decode((a.XMSL00),0,1,(a.XMSL00))),2) , --单价 a.XMSL00 , --数量 a.HJJE00 , --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额 a.ZFJE00 , --自付金额 a.GFJE00 , --减免金额 a.JZJE00 , --计账金额 d.BH0000 , --发票项目编号 d.XMMC00 , --发票项目名称 'Y', --是否药品 (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'Y',a.ZFBL00))*100||'%') ZFBL00, --自付比率 decode((select CFZT00 from YF_MZCF00 where CFLSH0=a.YJDJH0 and DJH000=a.DJH000 and rownum=1),'2','N','3','Y','Y') XMZT00, A.KDKS00 KDKS00, --开单科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKS00 and rownum=1 ) KDKSMC, --开单科室名称 a.CZY000 KFCZY0, --扣费操作员 a.CZYXM0 KFYXM0, --扣费操作员姓名 a.CZYKS0 KFKS00, --扣费科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.CZYKS0 and rownum=1 ) KFKSMC, --扣费科室名称 a.ZXKS00 ZXKS00, --执行科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.ZXKS00 and rownum=1 ) ZXKSMC, --执行科室名称 a.DJH000, -- substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'Y',a.BRID00),1,30) YBXJ00, --医保限价 d.FPXMID as FPXMID, --发票ID d.BH0000 as FPXMBH, --发票编码 d.MZFPBM as MZFPBM, --电子发票接口-门诊发票编码 substr(SF_XT_GETYBXMBH(a.BRFB00,a.YBLB00,a.XMBH00,a.SFLB00),1,50) as YBXMBH, --医保项目编号 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,'Y','1')) GJBZBM, --国家医保编码 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,'Y','2')) GJBZMC --国家医保名称 from SF_FYMX00 a,BM_BRXXB0 b,BM_YD0000 c,BM_FPXM00 d where a.BRID00 = b.BRID00 and a.XMBH00 = c.YPNM00 and a.MZFPID = d.FPXMID and a.SFLB00 = '3' ; create or replace view vw_sf_listdetail as select a.MZID00 as BRID00, --brid00 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.QDH000) as listDetailNo, --listDetailNo 明细流水号 String 60 否 明细流水号 max(decode(SF_SF_TYZD00('第三方电子发票平台接口','chargeCode'),'1',a.MZFPBM,a.MZFPBM)) as chargeCode, --chargeCode 收费项目代码 String 50 否 填写业务系统内部编码值，由医疗平台配置对照,如：床位费、检查费 max(a.FPXMMC) as chargeName, --chargeName 收费项目名称 String 100 否 max(a.DJH000) as prescribeCode, --prescribeCode 处方编码 String 60 否 '' as listTypeCode, --listTypeCode 药品类别编码 String 50 否 如药品分类编码01，有则填写 '' as listTypeName, --listTypeName 药品类别名称 String 50 否 如药品分类名称，抗生素类抗感染药物 substrb(max(nvl(a.YBXMBH,nvl(trim(a.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_SF_RQDMX8 a group by a.MZID00,a.JZDH00,a.XMMC00,a.XMDJ00,a.XMBH00 ;