with "Здания" (BUILD_ID,NAME,OWNERCLS_ID) as (
  select 8209,'ул. Абызова, 19',5 from dual union all
  select 6251,'ул. Азотная, 9',5 from dual union all
  select 3735,'ул. Авроры, 17а',5 from dual union all
  select 3737,'ivy8',5 from dual)
,"Части здания" (PREMISES_ID,NAME,BUILD_ID,NOTEPRIV_ID) as (
  select 201061,'о/д',8209,2 from dual union all
  select 174138,'1',3735,null  from dual union all
  select 198459,'1',6251,1  from dual)
,"Предоставление жилья" (DWELLING_ID,NAME,PREMISES_ID) as (
  select 174228,'',174138 from dual union all
  select 240499,'сн',198459  from dual )
,"Ордера" (FLATORDER_ID,NAME,NUMBDOCUM,DATEDOCUM,DWELLING_ID,DOCDATE,ANNUL) as (
  select 174276,'ордер','014840',37816,174228,to_date('14.07.2003','dd.mm.yyyy'),0 from dual union all 
  select 258759,'сн','9517',40490,240499,to_date('14.07.2003','dd.mm.yyyy'),-1 from dual union all 
  select 248227,'сн','7618',40130,240499,to_date('14.07.2003','dd.mm.yyyy'),-1 from dual union all 
  select 280751,'сн','16729',41764,240499,to_date('14.07.2003','dd.mm.yyyy'),0 from dual )
select *
 from
   "Здания" b
  ,"Части здания" p
  ,"Предоставление жилья" d1
  ,"Ордера" f
where
  b.OWNERCLS_ID=5
  and  p.BUILD_ID(+)=b.BUILD_ID
  and (p.NOTEPRIV_ID(+) is null or NOTEPRIV_ID(+)=2)
  and d1.PREMISES_ID(+)=p.PREMISES_ID
  and f.DWELLING_ID(+)=d1.DWELLING_ID and ANNUL(+)=0
/
SQL> /

  BUILD_ID NAME            OWNERCLS_ID PREMISES_ID NAM   BUILD_ID NOTEPRIV_ID DWELLING_ID NA PREMISES_ID FLATORDER_ID NAME  NUMBDO  DATEDOCUM DWELLING_ID DOCDATE         ANNUL
---------- --------------- ----------- ----------- --- ---------- ----------- ----------- -- ----------- ------------ ----- ------ ---------- ----------- ---------- ----------
      3735 ул. Авроры, 17а           5      174138 1         3735                  174228         174138       174276 ордер 014840      37816      174228 14.07.2003          0
      8209 ул. Абызова, 19           5      201061 о/д       8209           2
      3737 ivy8                      5
      6251 ул. Азотная, 9            5