-- Start of DDL Script for View SD_HOSPITAL.VW_SF_RQDMX8_BAK_20220216 -- Generated 16-2月-2022 17:38:29 from SD_HOSPITAL@HISDB CREATE OR REPLACE VIEW vw_sf_rqdmx8_bak_20220216 ( 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, 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, --发票编码 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, --发票编码 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, --发票编码 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' / -- End of DDL Script for View SD_HOSPITAL.VW_SF_RQDMX8_BAK_20220216