CREATE OR REPLACE VIEW VW_ZY_RQDMX8 (qdrq00, zyid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, fpxmbh, zfbl00, zfje00, jzje00, fpxmmc, gjbm00, sfyp00, gfje00, sfxmid, czrq00, czsj00, jzdh00, ybfsdh, zxksmc, zyh000, order0, ybzfje, yexm00, tcdjid, sftc00, xmgg01, fpxmid) AS select A.QDRQ00 , --清单日期 A.ZYID00 , --住院ID max(B.XMBH00) , --医疗收费编码 B.HBXMMC, --项目名称 max(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)), --药品规格 max(trim(C.DW0000)) , --单位 sum(B.HJJE00) , --单价, --单价 1 , --数量 sum(B.HJJE00) , --合计金额 max(D.BH0000) , --发票项目编号 max(SF_ZY_HQZFBL(a.ZYID00,B.XMBH00,'N',B.ZFBL00,0)*100), --自付比率 sum(B.ZFJE00) , --自付金额 sum(B.JZJE00) , max(D.XMMC00) , --max(B.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,'')), max(SF_ZY_YBXMBM(a.ZYID00,B.XMBH00,'N',C.BKEH83))GJBM00, --max(C.GJBM00)GJBM00, 'N', sum(B.GFJE00) , max(B.XMBH00) , A.CZRQ00 , A.CZSJ00 , A.JZDH00 , A.YBFSDH , max(M.BMMC00) , A.ZYH000 , max(D.ORDER0) , max(round(B.HJJE00*SF_ZY_HQZFBL(A.ZYID00,B.XMBH00,'N',B.ZFBL00),2)), A.YEXM00 , B.TCDJID , decode(nvl(B.TCDJID,0),0,'否','是') SFTC00, max(substrb(decode(C.JSXM00,'2',C.GG0000,C.XMNH00),1,50)), --药品规格/项目内涵 max(D.FPXMID) as FPXMID from ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BMBM00 M where A.DJH000 = B.DJH000 and B.XMBH00 = C.SFXMID and C.ZYFPID = D.FPXMID AND M.BMBH00=B.ZXKS00 and (B.XMBH00 < 9999999990 or JMBZ00 <> 0) and B.XMSL00<>0 --非减免,非优惠的 and B.HBXMMC is not null group by A.QDRQ00,A.ZYID00,B.HBXMMC,A.CZRQ00,A.CZSJ00,A.YBFSDH,A.ZYH000,A.JZDH00,A.YEXM00,B.TCDJID union all select A.QDRQ00 , --清单日期 A.ZYID00 , --住院ID B.XMBH00 , --医疗收费编码 decode((select count(1) from XT_XTCS00 where NAME00='ZY_RQDMX8_XMMCBHFPMC' and VALUE0='N'),0,SF_ZY_ZLXMJC_GET(B.MXID00)||B.XMMC00||'['||D.XMMC00||']',SF_ZY_ZLXMJC_GET(B.MXID00)||B.XMMC00), --项目名称 substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50), --药品规格 trim(C.DW0000) , --单位 B.XMDJ00 , --单价 B.XMSL00 , --数量 B.HJJE00 , --合计金额 D.BH0000 , --发票项目编号 SF_ZY_HQZFBL(A.ZYID00,B.XMBH00,'N',B.ZFBL00,0)*100, --自付比率 B.ZFJE00 , --自付金额 B.JZJE00 , D.XMMC00 , --B.GJBM00||decode(c.JSXM00,'2','/'||C.GJBM00,''), SF_ZY_YBXMBM(A.ZYID00,B.XMBH00,'N',C.BKEH83) GJBM00, 'N', B.GFJE00 , B.XMBH00 , A.CZRQ00 , A.CZSJ00 , A.JZDH00 , A.YBFSDH , M.BMMC00 , A.ZYH000 , D.ORDER0 , round(B.HJJE00*SF_ZY_HQZFBL(a.ZYID00,B.XMBH00,'N',B.ZFBL00,1),2) , A.YEXM00 , B.TCDJID , decode(nvl(B.TCDJID,0),0,'否','是') SFTC00, substrb(decode(C.JSXM00,'2',C.GG0000,C.XMNH00),1,50), --药品规格/项目内涵 D.FPXMID as FPXMID from ZY_BRFY00 A,ZY_FYMX00 B,BM_YYSFXM C,BM_FPXM00 D,BM_BMBM00 M where A.DJH000 = B.DJH000 and B.XMBH00 = C.SFXMID and C.ZYFPID = D.FPXMID AND M.BMBH00=B.ZXKS00 and (B.XMBH00 < 9999999990 or JMBZ00 <> 0) and B.XMSL00<>0 --非减免,非优惠的 and B.HBXMMC is null union all select A.QDRQ00 , --清单号 A.ZYID00 , --住院ID C.YPNM00 , --药品内码 C.YPMC00||'['||F.XMMC00||']' , --药品名称 -- C.YPGG00 , --药品规格 decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1', (decode(C.YPGG00,'100g/袋','散','250g/袋','散',C.YPGG00)),C.YPGG00),--药品规格 C.CFDW00 , --单位 --C.LSDJ00 , --单价 decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1',nvl((select 1/decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=C.YPNM00),1),1)*C.LSDJ00 , --单价 --C.YPZSL0 , --数量 decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' 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)) ypzsl0, round(C.LSDJ00*C.YPZSL0,2), --金额 F.BH0000 , --发票项目编号 SF_ZY_HQZFBL(A.ZYID00,C.YPNM00,'Y',C.ZFBL00,0)*100, --自付比率 round(C.ZFJE00,2), --自付金额 round(C.LSDJ00*C.YPZSL0-C.ZFJE00-C.GFJE00,2) , F.XMMC00, SF_ZY_YBXMBM(A.ZYID00,C.YPNM00,'Y',H.GJBZBM) GJBM00, --SF_ZY_YBXMBM(A.ZYID00,C.YPNM00,'Y',nvl((select ybxmbh from bm_ybsfdy where sfxmid=h.ypnm00 and sfyp00='Y' and ybzxlb='1'),H.GJBZBM)) GJBM00 --DECODE((SELECT COUNT(1) FROM XT_XTCS00 WHERE NAME00='ZY_QDDYGJBMXSFS' AND VALUE0='1'),0, -- (select 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'), -- (select nvl(y.YPBM00,s.YBXMBH) 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')), -- (select nvl(s.YBXMBH,y.YPBM00) from BM_YD0000 y,ZY_BRXXB0 z,IC_YBBRLB l,BM_YBSFDY s where c.YPNM00=y.YPNM00 and z.ZYID00=a.ZYID00 and -- z.YBLB00=l.YBLB00 and z.FBBH00=l.FBBH00 and c.YPNM00=s.SFXMID and s.SFYP00='Y' and l.YBZXLB=s.YBZXLB), 'Y', round(decode(C.SFZBY0,'Y',0,C.GFJE00),2), --减免金额 C.YPNM00 , A.CZRQ00 , A.CZSJ00 , A.JZDH00 , A.YBFSDH , M.BMMC00 , A.ZYH000 , F.ORDER0 , round(round(C.LSDJ00*C.YPZSL0,2)*SF_ZY_HQZFBL(A.ZYID00,C.YPNM00,'Y',C.ZFBL00,1),2), a.yexm00 , 0, '否', decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1', (decode(C.YPGG00,'100g/袋','散','250g/袋','散',C.YPGG00)),C.YPGG00), --药品规格 F.FPXMID as FPXMID from ZY_BRFY00 A,YF_ZYCF00 B,YF_ZYCFMX C ,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,bm_yd0000 H ,BM_BMBM00 M --yf_zycfmx没有含有中药帖数 where A.DJH000 = B.DJH000 and B.CFLSH0 = C.CFLSH0 and C.YPDLBH = D.LBBH00 and D.SFDM00 = E.SFXMID and E.ZYFPID = F.FPXMID and M.BMBH00=B.YFBMBH and C.YPNM00 = H.YPNM00 union all select A.QDRQ00 , --清单号 A.ZYID00 , --住院ID B.YPNM00 , --药品内码 B.YPMC00||'['||F.XMMC00||']' , --药品名称 --B.YPGG00 , --药品规格 decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1', (decode(B.YPGG00,'100g/袋','散','250g/袋','散',B.YPGG00)),B.YPGG00),--药品规格 B.QLDW00 , --单位 --B.LSDJ00 , --单价 decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1',nvl((select 1/decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=B.YPNM00),1),1)*B.LSDJ00 , --单价 --B.YPZSL0 , --数量 decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1',nvl((select decode(NSBL00,0,1,NSBL00) from BM_YD0000 where YPNM00=b.YPNM00),1),1)*(b.YPZSL0) ypzsl0, --decode(sign(a.djh000-1000000000),1,ROUND(B.LSDJ00*B.YPZSL0,2),b.ZFJE00), --金额 round(B.LSDJ00*B.YPZSL0,2), --金额 F.BH0000 , --发票项目编号 SF_ZY_HQZFBL(a.ZYID00,b.YPNM00,'Y',b.ZFBL00,0)*100 , --自付比率 round(B.ZFJE00,2), --自付金额 round(B.LSDJ00*B.YPZSL0-B.ZFJE00-B.GFJE00,2), F.XMMC00, SF_ZY_YBXMBM(a.ZYID00,B.YPNM00,'Y',H.GJBZBM) GJBM00, --DECODE((SELECT COUNT(1) FROM XT_XTCS00 WHERE NAME00='ZY_QDDYGJBMXSFS' AND VALUE0='1'),0, -- (select nvl(s.YBXMBH,y.YPBM00) from BM_YD0000 y,BM_YBSFDY s where b.YPNM00=y.YPNM00 and b.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1'), -- (select nvl(y.YPBM00,s.YBXMBH) from BM_YD0000 y,BM_YBSFDY s where b.YPNM00=y.YPNM00 and b.YPNM00=s.SFXMID and s.SFYP00='Y' and s.YBZXLB='1')), --(select nvl(s.YBXMBH,y.YPBM00) from BM_YD0000 y,ZY_BRXXB0 z,IC_YBBRLB l,BM_YBSFDY s where b.YPNM00=y.YPNM00 and z.ZYID00=a.ZYID00 and -- z.YBLB00=l.YBLB00 and z.FBBH00=l.FBBH00 and b.YPNM00=s.SFXMID and s.SFYP00='Y' and l.YBZXLB=s.YBZXLB), 'Y', B.GFJE00 ,--减免金额 B.YPNM00 , A.CZRQ00 , A.CZSJ00 , A.JZDH00 , A.YBFSDH , M.BMMC00 , A.ZYH000 , F.ORDER0 , ROUND(ROUND(B.LSDJ00*B.YPZSL0,2)*SF_ZY_HQZFBL(A.ZYID00,B.YPNM00,'Y',B.ZFBL00,1),2), a.yexm00 , 0, '否', decode((select count(1) from XT_XTCS00 where NAME00='ZY_ZYQDSFANSBL' and VALUE0='Y'),'1', (decode(B.YPGG00,'100g/袋','散','250g/袋','散',B.YPGG00)),B.YPGG00), --药品规格 F.FPXMID as FPXMID from ZY_BRFY00 A,YF_YZYPSQ B,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G,bm_yd0000 H,BM_BMBM00 M --yf_yzypsq的总数量有含有中药的帖数 where A.DJH000 = B.DJH000 and B.YPDLBH = D.LBBH00 and D.SFDM00 = E.SFXMID and M.BMBH00=B.YFBMBH and E.ZYFPID = F.FPXMID and A.BRID00 = G.BRID00 and B.YPNM00 = H.YPNM00 ;