CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_ZYBRZHXX
 (
 ZYID00, ZYH000, FBBH00, DBZBZ0, HJJE00, DBZFY0, BRYJZE, ZGDBJE, YJYJZE

 )
 AS 
select a.ZYID00,
       a.ZYH000,
       a.FBBH00,
       a.DBZBZ0,
       (select nvl(sum(HJJE00),0) HJJE00 from VW_ZY_FYMX00 where ZYID00 = a.ZYID00) HJJE00 ,--总费用
       nvl(b.DBZCWF,0) +nvl(b.dbzhcf,0)+ nvl(( select BZSFJE from BM_BZSFBZ where a.DBZBM0 = FFBZBM and rownum =1),0) DBZFY0,--单病种费用
       nvl(a.BRYJZE,0) BRYJZE, --病人总预交金
       (select nvl(sum(DBJE00),0) from ZY_BRDB00 where ZYID00 = a.ZYID00 and SFYX00='Y' and SSLB00= '1' and DBJSRQ>to_char(sysdate,'YYYYMMDD')) ZGDBJE,--职工担保金额
       (case when a.FBBH00='3' then b.WJYJZE - b.YJZFJE
            when a.FBBH00='1' and a.DBZBZ0 ='单' then a.BRYJZE -  (nvl(b.BZWFY0,0) + nvl(( select BZSFJE from BM_BZSFBZ where a.DBZBM0 = FFBZBM and rownum =1),0))
       else b.ZYE000 end) YJYJZE --预结后余额
from VW_ZY_BRXXB0 a,VW_ZY_BRZHXX b
  where a.ZYID00 = b.ZYID00
/