select ..
    from ORDERS o
    left join ORDERS_JOBS oj on o.doc_id = oj.order_id   -- orders-activities link table, N:M
         join SPELUNKING  s  on oj.doc_id = s.doc_id     
         join WATERSKIING w  on oj.doc_id = w.doc_id
         join CLIMBING    c  on oj.doc_id = c.doc_id
   where 
             :id = o.doc_id or
             :id = s.doc_id or
             :id = w.doc_id or
             :id = c.doc_id

CREATE VIEW DOCUPILE_V
  select ..
         (o.doc_id, s.doc_id, w.doc_id, c.doc_id) as SearchList
    from ORDERS o
    left join ORDERS_JOBS oj on o.doc_id = oj.order_id   -- orders-activities link table, N:M
         join SPELUNKING  s  on oj.doc_id = s.doc_id     
         join WATERSKIING w  on oj.doc_id = w.doc_id
         join CLIMBING    c  on oj.doc_id = c.doc_id;

...

  select * from DOCUPILE_V where :id in (SearchList)  -- цель - свернуть многострочье поиска.

   where 
             :id = o.doc_id or
             :id = s.doc_id or
             :id = w.doc_id or
             :id = c.doc_id

    Заказ1       Заказ2
     |         /    |    \
     |        /     |     \
     |       /      |      \
     |      /       |       \
       Пещеры     Лыжи      Акваланг

select ..
    from ORDERS o
    left join 
         (select ... , oj.order_id
            from ORDERS_JOBS oj
             join SPELUNKING  s  on oj.doc_id = s.doc_id     
             join WATERSKIING w  on oj.doc_id = w.doc_id
             join CLIMBING    c  on oj.doc_id = c.doc_id) a
                   on o.doc_id = a.order_id
 

tst> select * from t1 left join t2 on t1.id=t2.oid LEFT join t3 on t2.id=t3.id;

        ID         ID        OID         ID
---------- ---------- ---------- ----------
         1          1          1

select ..
    from ORDERS o
    left join ORDERS_JOBS oj on o.doc_id = oj.order_id   -- orders-activities link table, N:M
         join SPELUNKING  s  on oj.doc_id = s.doc_id     
         join WATERSKIING w  on oj.doc_id = w.doc_id
         join CLIMBING    c  on oj.doc_id = c.doc_id
   where 
             :id = o.doc_id or
             :id = s.doc_id or
             :id = w.doc_id or
             :id = c.doc_id

select ..
  from ORDERS_JOBS oj -- orders-activities link table, N:M
  join SPELUNKING  s  on oj.doc_id = s.doc_id     
  join WATERSKIING w  on oj.doc_id = w.doc_id
  join CLIMBING    c  on oj.doc_id = c.doc_id
  right outer join ORDERS  o on oj.order_id = o.doc_id 
 where 
        :id = o.doc_id or
        :id = s.doc_id or
        :id = w.doc_id or
        :id = c.doc_id

select * from DOCUPILE_V t
 where 
             :id = t.idOrder or
             :id = t.idSpelunk or
             :id = t.idWaterSki or
             :id = t.idClimbing

select * from DOCUPILE_V t  where :id = t.idSearch

...   ORDERS_JOBS oj on o.doc_id = oj.order_id
         join DOCS s  on oj.doc_id = s.doc_id and s.type='s'
         join DOCS w  on oj.doc_id = w.doc_id and w.type='w'
         join DOCS c  on oj.doc_id = c.doc_id and c.type='s'

 where 
             :id = idOrder or
             :id = idSpelunk or
             :id = idWaterSki or
             :id = idClimb

select * from DOCSPILE_V where idDocument = :id  -- выдать все документы связанные с :id (клиент, или заказ, или жалоба, или ...)
...
select distinct numOrder from DOCSPILE_V where idDocument = :idClient -- перечислить номера всех заказов клиента
...
select distinct numInvoice from DOCSPILE_V where idDocument = :idOrder -- перечислить номера всех инвойсов данного заказа
...
-- получить клиента по инвойсу, цепочка инвойс->исполнение->заказ->клиент известна представлению
-- у инвойса всегда ровно один клиент, поэтому тут просто.
select idClient into l_ClientId from DOCSPILE_V where idDocument = l_InvoiceId;