CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BM_YBSFDY1
 (
 SFXMID, SFYP00, FBBH00, YBZXLB, YBXMBH, YBZYFP, YBMZFP, XMJE00, YYJE00, 
ZFBL00, ZFJE00, JMBL00, JMJE00, SFYBXM, YBBRLB, SFSX00, YXBZ00, YBLB00, 
BZ0000, NBLB00, NBZFBL, YBJSDJ, ZYZFBL, XMDW00, YBJSDJ1, XMDW01, MZZYBZ, 
BKEH83, BKEA96, XZLB00

 )
 AS 
select
       a.SFXMID,
       a.SFYP00,
       a.FBBH00,
       a.YBZXLB,
       a.YBXMBH,
       a.YBZYFP,
       a.YBMZFP,
       SF_GETXMJE(a.SFYP00,a.SFXMID,a.YYDJ00,0,'0','0'),
       SF_GETXMJE(a.SFYP00,a.SFXMID,'A',0,'0','0'),
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,1,a.ZFBL00)*b.SFMLXZ,
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,0,a.ZFJE00)*b.SFMLXZ,
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,0,a.JMBL00)*b.SFMLXZ,
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,0,a.JMJE00)*b.SFMLXZ,
       a.SFYBXM,
       --decode(b.SFMLXZ,0,'Y',decode(replace(a.YBBRLB,b.YBBRLB),a.YBBRLB,'N',a.SFYBXM)),
       b.YBBRLB,
       a.SFSX00,
       a.YXBZ00,
       decode(b.SFMLXZ,0,decode(a.SFYP00,'Y','8','0'),decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,decode(a.SFYP00,'Y','7','2'),a.YBLB00)),
       a.bz0000,
       a.NBLB00,
       a.NBZFBL,
       (select YBJSDJ||'/'||XMDW00 from BM_YBSFXM where XMBH00=a.YBXMBH and YBZXLB=a.YBZXLB and FBBH00=a.FBBH00 and rownum=1) YBJSDJ,
       decode(a.YBBRLB,'Z',a.ZYZFBL,SF_XT_ZFBL00(b.YBBRLB,a.YBBRLB,a.ZYZFBL)),
       (select XMDW00 from BM_YBSFXM where XMBH00=YBXMBH and YBZXLB=YBZXLB and rownum=1) XMDW00,
       (select YBJSDJ1 from BM_YBSFXM where XMBH00=YBXMBH and YBZXLB=YBZXLB and rownum=1) YBJSDJ1,
       (select XMDW01 from BM_YBSFXM where XMBH00=YBXMBH and YBZXLB=YBZXLB and rownum=1) XMDW01,
       nvl(a.MZZYBZ,'2') MZZYBZ,  --门诊住院标志  0门诊，1住院，2门诊住院都可用
       a.BKEH83,  --国家医保编码
       nvl(a.BKEA96,'Y'),   --限制范围类别: Y-范围内 N-范围外
       a.XZLB00
from BM_YBSFDY a, BM_YBBRLB b,BM_BRFBB0 c
where a.YBZXLB=b.YBZXLB and a.FBBH00=b.FBBH00 and a.FBBH00=c.FBBH00 and a.YBBRLB<>'-' and
      exists (select 1 from IC_YBBRLB where YBLB00=YBZXLB and YBLB00=a.YBZXLB)
/