Hierarchy query example

with
  -- all current data-enterable finance orgs
  orgs_cur (ftvorgn_orgn_code, ftvorgn_title, ftvorgn_orgn_code_pred)
    as (select ftvorgn_orgn_code, ftvorgn_title, ftvorgn_orgn_code_pred
        from ftvorgn
        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),

  -- generate hierarchy with links to directory departments at rollup levels
  orgs_hier (orgn_code, orgn_title, lvl, dir_dept)
    as (select c.ftvorgn_orgn_code                 orgn_code,
               c.ftvorgn_title                     orgn_title,
               0                                   lvl,
               dxw.dir_dept                        dir_dept
        from orgs_cur c
        left outer join t_drew_dir_xwalk_ftvorgn dxw
          on c.ftvorgn_orgn_code = dxw.orgn_code
        where c.ftvorgn_orgn_code_pred is null

        union all

        select c.ftvorgn_orgn_code                 orgn_code,
               c.ftvorgn_title                     orgn_title,
               p.lvl + 1                           lvl,
               -- use predecessor org to dir_dept relationship
               -- if none set at this level
               nvl(dxw.dir_dept, p.dir_dept)       dir_dept
        from orgs_cur c
        join orgs_hier p
          on c.ftvorgn_orgn_code_pred = p.orgn_code
        left outer join t_drew_dir_xwalk_ftvorgn dxw
          on c.ftvorgn_orgn_code = dxw.orgn_code)
    search depth first by orgn_title set seqno

select oh.orgn_code,
       lpad(' ', 2*lvl)||oh.orgn_code orgn_code_padded,
       oh.orgn_title,
       lpad(' ', 2*lvl)||oh.orgn_title orgn_title_padded,
       oh.lvl,
       oh.dir_dept,
       d.dept_desc
from orgs_hier oh
left outer join t_drew_dir_deparments d
  on oh.dir_dept = d.dir_dept
order by oh.seqno;