CREATE OR REPLACE VIEW SD_HOSPITAL.VW_XT_YZMB00_ALL_ZYDZBL
 (
 MBID00, MBLBID, MBMC00, PYSM00, WBSM00, YXBZ00, CJRQ00, CJSJ00, CZY000, 
BZ0000, MBNRBZ, MZZYBZ, PLXH00, DJFS00, MBJE00

 )
 AS 
SELECT MBID00,  MBLBID,  MBMC00,  PYSM00,  WBSM00,  decode(YXBZ00,'1','有效','无效')YXBZ00,  CJRQ00,  CJSJ00,  CZY000, BZ0000,
  decode(MBNRBZ,'000','无',decode(SUBSTR(MBNRBZ,1,1),'1','西','')||decode(SUBSTR(MBNRBZ,2,1),'1','诊','')||
         decode(SUBSTR(MBNRBZ,3,1),'1','草',''))MBNRBZ,MZZYBZ,PLXH00,DJFS00
         , round((
select nvl(sum(b.SFJE00*c.sfcs00*nvl(d.MCSL00,0)),0)
  from BM_YYSFXM b,
  XT_ZLSFGX c,
  XT_ZLMBMX d
 where b.SFXMID =c.SFXMID
 and c.ZLXMID = d.ZLXMID and substr(MBNRBZ,2,1)='1'
 and d.MBID00 = t.MBID00)
 +
 (select nvl(sum(ROUND(B.LSJ000/(B.K2J000*B.NSBL00)*B.F2J000,4)*nvl(c.YPZSL0,0)),0)
 FROM BM_YD0000 b,XT_YPMBMX c
 where b.YPNM00=c.YPNM00 and substr(MBNRBZ,1,1)='1' 
 and c.MBID00=t.MBID00  )
 + 
 (select nvl(sum(ROUND(B.LSJ000/(B.K2J000*B.NSBL00)*B.M2J000,4)*nvl(c.YPZSL0,0)),0)
 FROM BM_YD0000 b,XT_YPMBMX c
 where b.YPNM00=c.YPNM00 and substr(MBNRBZ,3,1)='1' 
 and c.MBID00=t.MBID00  ),2) MBJE00
FROM XT_YZMB00 t
/