CREATE OR REPLACE VIEW SD_HOSPITAL.VW_SF_RQDMX8 ( JZDH00, GJBM00, GJBM01, QDH000, XM0000, MZID00, MZH000, GHID00, XMBH00, XMMC00, XMGG00, XMDW00, XMDJ00, XMSL00, HJJE00, ZFJE00, GFJE00, JZJE00, BH0000, FPXMMC, SFYP00, ZFBL00, XMZT00 ) AS select B.JZDH00, max(trim(A.GJBM00)), max(A.GJBM00) , --国家编码(排序用) B.CZRQ00 , --清单号 K.BRXM00 , --姓名 B.BRID00 , --门诊ID(实为病人ID) K.BRBLH0 , --门诊号 B.MZID00 , --挂号ID(门诊ID) max(A.XMBH00) , --医疗收费编码 A.HBXMMC, --项目名称 max(trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50))) XMGG00, --项目规格 max(trim(C.DW0000)) , --单位 sum(decode(A.JMBZ00,'4',A.HJJE00+A.JMJE00,A.HJJE00)) , --单价 1 XMSL00 , --数量 sum(decode(A.JMBZ00,'4',A.HJJE00+A.JMJE00,'5',A.JMJE00,A.HJJE00)) , --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额 sum(decode(A.JMBZ00,'4',A.ZFJE00+A.JMJE00,A.ZFJE00)) , --自付金额 sum(A.GFJE00) , --减免金额 sum(A.JZJE00) , --计账金额 max(D.BH0000) , --发票项目编号 max(D.XMMC00) , --发票项目名称 'N', --是否药品 max(to_char(SF_SF_HQZFBL(b.MZID00,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00, --自付比率 max(decode((select XMZT00 from YJ_YW0000 E,YJ_YWJJ00 F where E.YJDJH0=F.YJDJH0 AND E.SFDJH0=A.DJH000 and F.SFXMID=A.XMBH00 and rownum=1),'2','N','3','Y','Y')) XMZT00 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 K where A.DJH000 = B.DJH000 and B.BRID00 = K.BRID00 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and A.HBXMMC is not null and (A.XMBH00 < 9999999990 or JMBZ00 = '3') --增加药费的事后减免 group by B.JZDH00,B.CZRQ00,K.BRXM00,B.BRID00,K.BRBLH0,B.MZID00,A.HBXMMC union all select B.JZDH00, trim(A.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,'')), A.GJBM00 , --国家编码(排序用) B.CZRQ00 , --清单号 K.BRXM00 , --姓名 B.BRID00 , --门诊ID(实为病人ID) K.BRBLH0 , --门诊号 B.MZID00 , --挂号ID(门诊ID) A.XMBH00 , --医疗收费编码 substr(decode(SF_SF_GETYYID00(),'223488',Trim(A.XMMC00),SF_MZ_ZLXMJC_GET(A.MXID00)||Trim(A.XMMC00)||'['||D.XMMC00||']'),1,50), --项目名称 trim(substrb(decode(C.JSXM00,'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,'5',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(b.MZID00,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00, --自付比率 decode((select XMZT00 from YJ_YW0000 E,YJ_YWJJ00 F where E.YJDJH0=F.YJDJH0 AND E.SFDJH0=A.DJH000 and F.SFXMID=A.XMBH00 and rownum=1),'2','N','3','Y','Y') XMZT00 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 K where A.DJH000 = B.DJH000 and B.BRID00 = K.BRID00 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and A.HBXMMC is null and (A.XMBH00 < 9999999990 or JMBZ00 = '3') --增加药费的事后减免 union all select A.JZDH00, (select aa.YPBM00 from BM_YD0000 aa where aa.YPNM00=c.YPNM00 and rownum=1)GJBM00, (select aa.YPBM00 from BM_YD0000 aa where aa.YPNM00=c.YPNM00 and rownum=1)GJBM01, --(select trim(nvl(s.YBXMBH,y.YPBM00)) from BM_YD0000 y,BM_YBSFDY s where c.YPNM00=y.YPNM00 and c.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1') GJBM00, -- '', A.CZRQ00 , --清单号 K.BRXM00 , --姓名 A.BRID00 , --门诊ID(实为病人ID) K.BRBLH0 , --门诊号 A.MZID00 , --挂号ID(门诊ID) C.YPNM00 , --药品内码 Trim(C.YPMC00), --药品名称 C.YPGG00 , --药品规格 C.CFDW00 , --单位 decode((select count(1) from xt_xtcs00 where name00='SF_MZQDSFANSBL' and value0='Y'),'1',nvl((select 1/decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=C.YPNM00),1),1)*C.LSDJ00 , --单价 decode((select count(1) from xt_xtcs00 where name00='SF_MZQDSFANSBL' and value0='Y'),'1',nvl((select decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=C.YPNM00),1),1)*(DECODE(C.SFZBY0,'Y',0,C.YPZSL0)), --数量,若是自备药,则将其数量改为0 (round(C.LSDJ00*DECODE(C.SFZBY0,'Y',0,C.YPZSL0)*100)/100), --金额 Round((DECODE(C.SFZBY0,'Y',0,C.ZFJE00)),2), --自付金额 Round((DECODE(C.SFZBY0,'Y',0,C.GFJE00)),2), --减免金额 0, --计账金额 F.BH0000 , --发票项目编号 F.XMMC00 , --发票项目名称 'Y', --是否药品 (to_char(SF_SF_HQZFBL(a.MZID00,C.YPNM00,'Y',C.ZFBL00))*100||'%') ZFBL00, --自付比率 DECODE(B.CFZT00,'1','N','2','Y','6','Y','Y') XMZT00 from SF_BRFY00 A,yf_mzcf00 B,yf_mzcfmx C,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F ,BM_BRXXB0 K where A.DJH000 = B.DJH000 and A.BRID00 = K.BRID00 and B.cflsh0 = C.cflsh0 and B.YPDLBH = D.LBBH00 and D.SFDM00 = E.SFXMID and E.MZFPID = F.FPXMID union all select B.JZDH00 , trim(A.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,'')), A.GJBM00 , --国家编码(排序用) B.CZRQ00 , --清单号 K.BRXM00 , --姓名 B.BRID00 , --门诊ID(实为病人ID) K.BRBLH0 , --门诊号 B.MZID00 , --挂号ID(门诊ID) A.XMBH00 , --医疗收费编码 Trim(A.XMMC00)||'(减免)', --项目名称 trim(substrb(decode(C.JSXM00,'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(b.MZID00,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00, 'Y' --自付比率 from SF_FYMX00 A,SF_BRFY00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BRXXB0 K where A.DJH000 = B.DJH000 and B.BRID00 = K.BRID00 and A.XMBH00 = C.SFXMID and C.MZFPID = D.FPXMID and JMBZ00= '4' /