CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS13_BRXXB0
 (
 BRID00, MRN000, BRBLH0, YBKH00, YBID00, YBLB00, BRXM00, BRXB00, BRXX00, 
BRCSRQ, BRHYZK, BRZY00, BRMZ00, BRGJ00, BRZJLX, BRZJBH, FBBH00, BRGFZH, 
BRDYFW, BRDH00, BRJTDZ, BRYB00, TYDWID, TYDWMC, BRLXGX, BRLXXM, BRLXDZ, 
BRLXDH, BRZTQK, BRJKQK, BRLX00, CZY000, CZRQ00, CZSJ00, SHR000, SHRQ00, 
RYCS00, CYQKJE, ZYZT00, SFYX00, BZ0000, YBBRLB, BRNL00, FBMC00, YBMC00, 
YBZXLB, ZYE000, KYJE00, ZYE001, SFYDY0, BRSZSF, BRSZDS, BRSZS0, BRSZZ0, 
BRSZC0, BRSZQY, BRTZ00, BRSS00, SFXNH0, SFYD00, WDWID0, FBXX00, TSSXMC, 
ZZYE00, ICKH00, KMH000

 )
 AS 
select
   A.BRID00,A.MRN000,A.BRBLH0,A.YBKH00,A.YBID00,A.YBLB00,A.BRXM00,A.BRXB00,A.BRXX00,A.BRCSRQ,
   A.BRHYZK,A.BRZY00,A.BRMZ00,A.BRGJ00,A.BRZJLX,A.BRZJBH,A.FBBH00,A.BRGFZH,A.BRDYFW,
    (case when (select count(1) from XT_XTCS00 X where X.NAME00='YS_YYGHDHYH' and X.VALUE0='Y') > 0 then
       nvl(A.BRDH00,A.BRLXDH)
       else
       A.BRDH00
   end) as BRDH00,  
   A.BRJTDZ,A.BRYB00,A.TYDWID,A.TYDWMC,A.BRLXGX,A.BRLXXM,A.BRLXDZ,A.BRLXDH,A.BRZTQK,A.BRJKQK,
   A.BRLX00,A.CZY000,A.CZRQ00,A.CZSJ00,A.SHR000,A.SHRQ00,A.RYCS00,A.CYQKJE,A.ZYZT00,A.SFYX00,
   A.BZ0000,A.YBBRLB,SF_YS13_CSRQTONL_BZ(A.BRCSRQ) BRNL00,
   (select B.FBMC00 from BM_BRFBB0 B where A.FBBH00=B.FBBH00 )||' '||decode(C.YBJGJC,e.BRLBJC,C.YBJGJC,C.YBJGJC||'('||E.BRLBJC||')') FBMC00,
   --注释原先的语句 因为只显示了费别 所以修改语句拼接了fbxx00的内容 同时显示费别和参保机构信息 caijiayuan 2025.3.18
   --(select B.FBMC00 from BM_BRFBB0 B where A.FBBH00=B.FBBH00 )FBMC00,
   C.YBMC00,C.YBZXLB,D.ZYE000,
   D.ZYE000+nvl(DBJE00,0)-D.TYJE00
     +decode(decode(A.FBBH00,3,(select count(*) from XT_YYXX00 where YYID00='223583' and rownum=1),0),1,8,nvl(D.YXQFJE,0))
     +decode((select count(*) from XT_XTCS00 where NAME00='SF_YBGRZHZWYXQFJE' and VALUE0='Y'),1,YBZHYE,0) KYJE00,
   D.ZYE000+decode(decode(A.FBBH00,3,(select count(*) from XT_YYXX00 where YYID00='223583' and rownum=1),0),1,8,
     nvl((select count(*) from XT_XTCS00 where NAME00='SF_YBGRZHZWYXQFJE' and VALUE0='Y'),0)) ZYE001,
   nvl(A.SFYDY0,'N') SFYDY0,
   A.BRSZSF,--省份
   A.BRSZDS,--市
   A.BRSZS0,--县
   A.BRSZZ0,--镇
   A.BRSZC0,--村
   nvl(A.BRSZQY,'0') BRSZQY,--病人所属区域 '0' 区域内 '1'区域外
   A.BRTZ00,
   floor(months_between(sysdate,to_date(A.BRCSRQ,'YYYYMMDD'))/12) BRSS00,
   C.SFXNH0,
   A.SFYD00,
   A.WDWID0,
   decode(C.YBJGJC,e.BRLBJC,C.YBJGJC,C.YBJGJC||'('||E.BRLBJC||')') FBXX00,
   A.TSSXMC,
   D.ZZYE00,
   B.ICKH00,
   B.KMH000
from BM_BRXXB0 A
inner join IC_YBBRLB C on A.YBLB00=C.YBLB00 and A.FBBH00=C.FBBH00
left join SF_BRZHXX D on A.BRID00=D.BRID00
inner join BM_YBBRLB E on A.YBBRLB=E.YBBRLB and A.FBBH00=E.FBBH00 and C.YBZXLB=E.YBZXLB
left join IC_YHXX00 B on B.ZT0000 = '1' and A.BRID00 = B.BRID00
where A.SFYX00 = 'Y'
/