CREATE VIEW SD_HOSPITAL.VW_YS_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, BRSZS0, BRSZZ0, BRSZC0, 
BRSZQY, BRTZ00, BRSS00, SFXNH0, SFYD00, YBSBK0, ICKH00, KMH000, PYSM00, 
WBSM00, NL0000, SFBYZG, TCJE00, BRSZX0, BRSZMP, YBDZX0, SFJZFP, ZSYID0

 )
 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,A.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,substrb(SF_BQ_CSRQTONL(A.BRCSRQ),1,200) BRNL00,
   (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) 
     + + decode((select COUNT(*) from bm_tyzd00 where ZDMC00='一卡通平台参数' AND BH0000='SF_SFQYYKTPT' AND MC0000='Y' AND ROWNUM=1),1,NVL(YKTYE0,0),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)) 
     + decode((select COUNT(*) from bm_tyzd00 where ZDMC00='一卡通平台参数' AND BH0000='SF_SFQYYKTPT' AND MC0000='Y' AND ROWNUM=1),1,NVL(YKTYE0,0),0) ZYE001,
   NVL(A.SFYDY0,'N') SFYDY0,
   A.BRSZSF,--省份
   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,
   '1' YBSBK0,
   nvl((select E.ICKH00 from IC_YHXX00 E where E.BRID00=A.BRID00 and E.ZT0000 = '1' and rownum=1),0) ICKH00,
   (select E.KMH000 from IC_YHXX00 E where E.BRID00=A.BRID00 and E.ZT0000 = '1' and rownum=1) KMH000,
   A.PYSM00,
   A.WBSM00,
   SF_YS_CSRQTONL(a.BRCSRQ) NL0000,
   nvl(a.SFBYZG,'0') SFBYZG,  --是否本院职工
   D.TCJE00,   --统筹支付金额
   a.BRSZX0,
   a.BRSZMP,
   (select YBMC00 from IC_YBBRLB where FBBH00=a.FBBH00 and YBLB00=c.YBZXLB and rownum=1 ) YBDZX0
   ,a.sfjzfp
   ,a.ZSYID0
FROM BM_BRXXB0 A,IC_YBBRLB C,SF_BRZHXX D
WHERE A.YBLB00=C.YBLB00 AND A.FBBH00=C.FBBH00 AND A.BRID00=D.BRID00(+)
/