prompt 发票收费视图 vw_sf_chargedetail create or replace view vw_sf_chargedetail as as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2020.11.09 created [门诊][易惠博思电子发票][收费项目明细视图] by MZSF9-20201111-004 -- zhangyc 2020.11.30 增加参数控制chargeCode收费发送发票项目编码和发票项目ID by MZSF9-20201202-002 -- zhangyc 2020.12.09 QZFJE0改为XMJE00 by MZSF9-20201210-001 -- jlg 2021.08.31 重新排版下 MZSF9-20210827-004 -- chenrh 2023.10.25 门诊收费：博思电子发票接口，发票项目对照 MZSF13X-20231025-001 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 ; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 发票收费视图 view vw_sf_rqdmx8 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' ; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 发票列表视图 vw_sf_listdetail 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 ; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%