create or replace view VW_BQ_BRZYXX (
   BRID00,
   ZYID00,
   ZYH000,
   RYCWH0,
   CSRQ00,
   XM0000,
   XB0000,
   RYRQ00,
   DQBQ00,
   DQBQMC,
   DQKS00,
   DQKSMC,
   YBZXLB,
   YBBRLB,
   YBMC00,
   BRLBJC,
   YKZYE0,
   BJJE00,
   FBBH00,
   ZYE000,
   XJYE00,
   FBMC00,
   RYLB00,
   YKSH00,
   BRTZ00,
   ZZYS00,
   ZZYSXM,
   ZZYSLB,
   ZZXKH0,
   RYSJ00,
   RYZD00,
   RYZDMC,
   DQLJID,
   YJJZE0,   --预缴金总额
   YBZFJE,   --医保预支付现金部分
   ZRYS00,   --主任医生
   SXYS00    --书写医生
   )
as
select a.BRID00,  --病人ID
       a.ZYID00,  --住院ID
       a.ZYH000,  --住院号
       a.RYCWH0,  --床号
       a.CSRQ00,  --出生日期
       a.XM0000,  --姓名
       a.XB0000,  --性别
       a.RYRQ00,  --入院日期
       a.DQBQ00,  --病区
       (select BMMC00 from BM_BMBM00 where BMBH00=a.DQBQ00) DQBQMC,  --当前病区名称
       a.DQKS00,  --科室
       (select BMMC00 from BM_BMBM00 where BMBH00=a.DQKS00) DQKSMC,  --当前科室名称
       c.YBZXLB,  --医保中心类别
       a.YBBRLB,  --医保病人类别
       c.YBMC00,  --医保名称
       d.BRLBJC BRLBJC,  --病人类别简称
       sf_bq_kyje00(0,a.brid00)YKZYE0,  --预扣后的可用额
--     e.YKZYE0-e.TYJE00 YKZYE0,  --预扣后的可用额
       e.BJJE00,  --报警限额
       a.FBBH00,  --费别编号
       e.ZYE000,  --总余额
       e.XJYE00,  --现金余额
       f.FBMC00,  --费别名称：自费、记帐、医保等
       a.RYLB00, --入院类别
       a.yksh00,  --原科室号
       b.brtz00,  --病人体重
       a.ZZYS00,  --主治医生
       a.ZZYSXM,  --主治医生姓名
       g.YSLB00, --主治医生级别
       g.XKH000,  --主治医生胸卡号
       a.rysj00,  --入院时间
       a.ryzd00,  --入院诊断
       a.ryzdmc,   --入院诊断名称
       decode(A.DQLJID,null,0,0,0,nvl((select max(LJID00) from LJ_ZRZC00   where ZYID00=A.ZYID00 and nvl(JSRQ00,' ')=' '),0)),
--       (select  nvl(sum(JFJE00),0)  from  zy_brjfb0  where  ZYID00 =a.ZYID00 and JFLBID = 2 and jzdh00 = 0 ) YJJZE0 ,  --预缴金总额
       (select  nvl(sum(JFJE00),0)  from  zy_brjfb0  where  ZYID00 =a.ZYID00 and JFLBID+0 = 2 and jzdh00+0 = 0 ) YJJZE0 ,  --预缴金总额
       e.YBZFJE ,   --医保预支付现金部分
       a.ZRYS00,
       a.SXYS00
 from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c,BM_YBBRLB d,ZY_BRZHXX e,BM_BRFBB0 f,BM_YGBM00 g
where a.BRID00 = b.BRID00
  and a.YBLB00 = c.YBLB00
  and a.FBBH00 = c.FBBH00
  and a.FBBH00 = f.FBBH00
  and b.BRID00 = e.BRID00
  and a.YBBRLB = d.YBBRLB
  and D.YBZXLB = C.YBZXLB AND C.FBBH00=D.FBBH00
  and ((a.BRZT00 = '20') or (a.BRZT00 = '22') or (a.BRZT00 = '23'))
  and e.JFLBID = 2
  and a.ZZYS00 = g.YGBH00(+)