Hierarchy query example

with
  orgs (ftvorgn_orgn_code, ftvorgn_title, lvl, dir_dept)
    as (select ftvorgn_orgn_code, ftvorgn_title, 0 lvl, dir_dept
        from ftvorgn 
        left outer join t_drew_dir_xwalk_ftvorgn xw
          on ftvorgn_orgn_code = xw.orgn_code
        where ftvorgn_coas_code = 'D'
        and ftvorgn_eff_date <= sysdate
        and nvl(ftvorgn_term_date, sysdate + 1) > sysdate
        and nvl(ftvorgn_nchg_date, sysdate + 1) > sysdate
        and ftvorgn_orgn_code_pred is null
           
        union all
           
        select ftvorgn.ftvorgn_orgn_code, ftvorgn.ftvorgn_title, orgs.lvl + 1 lvl,
               nvl(xw.dir_dept, orgs.dir_dept)
        from ftvorgn
        join orgs
          on ftvorgn.ftvorgn_orgn_code_pred = orgs.ftvorgn_orgn_code
        left outer join t_drew_dir_xwalk_ftvorgn xw
          on ftvorgn.ftvorgn_orgn_code = xw.orgn_code
        where ftvorgn.ftvorgn_coas_code = 'D'
        and ftvorgn.ftvorgn_eff_date <= sysdate
        and nvl(ftvorgn.ftvorgn_term_date, sysdate + 1) > sysdate
        and nvl(ftvorgn.ftvorgn_nchg_date, sysdate + 1) > sysdate)
    search depth first by ftvorgn_title set seqno
           
select lpad(' ', 2*lvl)||ftvorgn_orgn_code ftvorgn_orgn_code,
       ftvorgn_title,
       lvl,
       orgs.dir_dept,
       dept_desc
from orgs
left outer join t_drew_dir_deparments
  on orgs.dir_dept = t_drew_dir_deparments.dir_dept
order by seqno;