create or replace view vw_bm_bqksdz as
select -1 as BMBH00,
       ' ' as BH0000,
       '全部病区' as BMMC00,
       '9' as BMXZ00,
       '0' as YYID00,
       'qbbq' as PYSM00,
       '' as WBSM00,
       -2 as PRBMBH,
       '' as PRBMMC,
       '' as SSHTBZ,
       '' as YJHTBZ,
       '0' as SYBZ00
  from dual
union all
select b.BMBH00,
       b.BH0000,
       b.BMMC00,
       b.BMXZ00,
       b.YYID00,
       b.PYSM00,
       b.WBSM00,
       a.BQH000 as PRBMBH,
       c.BMMC00 as PRBMMC,
       d.SSHTBZ,
       d.YJHTBZ,
       nvl(b.SYBZ00,'0') as SYBZ00     --使用标志, 空或0都可使用1医疗使用2养老使用
  from BM_BQKS00 a,BM_BMBM00 b,BM_BMBM00 c,XT_YYXX00 d
 where a.YXBZ00 = '1'
   and a.KSH000 = b.BMBH00
   and a.BQH000 = c.BMBH00
   and b.YYID00 = d.YYID00(+) and b.czbz00  != '2'
union all
select a.BMBH00,
       a.BH0000,
       a.BMMC00,
       a.BMXZ00,
       a.YYID00,
       a.PYSM00,
       a.WBSM00,
       -1 as PRBMBH,
       '' as PRBMMC,
       d.SSHTBZ,
       d.YJHTBZ,
       nvl(a.SYBZ00,'0') as SYBZ00     --使用标志, 空或0都可使用1医疗使用2养老使用
  from BM_BMBM00 a,XT_YYXX00 d
 where a.BMXZ00 = '9'
   and a.SFYX00 = '1' and a.czbz00  != '2'
   and a.YYID00 = d.YYID00(+);