CREATE OR REPLACE VIEW SD_HOSPITAL.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

 )
 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(c.GJBM00||decode(c.JSXM00,'2','/'||C.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
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
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  ,
      trim(c.GJBM00)||decode(c.JSXM00,'2','/'||C.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
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,
      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
from  ZY_BRFY00 A,YF_ZYCF00 B,YF_ZYCFMX C ,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BMBM00 M  --yf_zycfmx没有含有中药帖数
where  A.DJH000 = B.DJH000  and  B.CFLSH0 = C.CFLSH0  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID  and  E.ZYFPID = F.FPXMID and M.BMBH00=B.YFBMBH
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,
      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
from  ZY_BRFY00 A,YF_YZYPSQ B,BM_YPLB00 D,BM_YYSFXM E,BM_FPXM00 F,BM_BRXXB0 G,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
/