-- Start of DDL Script for View SD_HOSPITAL.VW_BQ_BRZYXX
-- Generated 26-4月-2022 18:21:56 from SD_HOSPITAL@fywycs1

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,  --医保中心类别
       b.YBBRLB,  --医保病人类别
       c.YBMC00,  --医保名称
       d.BRLBJC BRLBJC,  --病人类别简称
       sf_bq_kyje00(a.zyid00,0)YKZYE0,  --预扣后的可用额
       ---备份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 b.YBLB00 = c.YBLB00
  and b.FBBH00 = c.FBBH00
  and b.FBBH00 = f.FBBH00
  and b.BRID00 = e.BRID00
  and b.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(+)
/


-- End of DDL Script for View SD_HOSPITAL.VW_BQ_BRZYXX