-- Start of DDL Script for View SD_HOSPITAL.VW_BM_YBSFDY1
-- Generated 16-九月-2021 14:42:34 from SD_HOSPITAL@151

CREATE OR REPLACE VIEW 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 )
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-范围外
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)
/


-- End of DDL Script for View SD_HOSPITAL.VW_BM_YBSFDY1