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, ybbm00) 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, --发票编码 --2021.09.09 新增 ybbm00 医保编码 字段 (select ybxmbh from bm_ybsfdy a1,ic_ybbrlb a2 where a1.sfxmid = a.xmbh00 and a1.ybzxlb = a2.ybzxlb and a2.yblb00 = a.yblb00 and rownum=1) ybbm00 --医保编码 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, --发票编码 --2021.09.09 新增 ybbm00 医保编码 字段 (select ybxmbh from bm_ybsfdy a1,ic_ybbrlb a2 where a1.sfxmid = a.xmbh00 and a1.ybzxlb = a2.ybzxlb and a2.yblb00 = a.yblb00 and rownum=1) ybbm00 --医保编码 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, --发票编码 --2021.09.09 新增 ybbm00 医保编码 字段 (select ybxmbh from bm_ybsfdy a1,ic_ybbrlb a2 where a1.sfxmid = a.xmbh00 and a1.ybzxlb = a2.ybzxlb and a2.yblb00 = a.yblb00 and rownum=1) ybbm00 --医保编码 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';