Page History

with
  -- all current data-enterable finance orgs
  orgs_cur (ftvorgn_orgn_code, ftvorgn_title, lvlftvorgn_orgn_code_pred)
    as (select ftvorgn_orgn_code, ftvorgn_title, 0 lvlftvorgn_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
 and ftvorgnorgs_hier (orgn_code, orgn_predtitle, is nulllvl, dir_dept)
    as (select c.ftvorgn_orgn_code                 orgn_code,
              union all
 c.ftvorgn_title            
        select ftvorgn.ftvorgn_orgn_code, ftvorgn.ftvorgn_title, orgs.lvl + 1 lvlorgn_title,
               0                                   lvl,
               dxw.dir_dept                        dir_dept
        from orgs_cur ftvorgnc
        left outer join orgst_drew_dir_xwalk_ftvorgn dxw
          on ftvorgnc.ftvorgn_orgn_code_pred = orgsdxw.ftvorgn_orgn_code
        where ftvorgnc.ftvorgn_coasorgn_code = 'D'_pred is null

        union all

        andselect ftvorgnc.ftvorgn_eff_date <= sysdateorgn_code                 orgn_code,
        and nvl(ftvorgn.ftvorgn_term_date, sysdate + 1) > sysdate
       c.ftvorgn_title                     orgn_title,
               p.lvl + 1                           lvl,
               -- use predecessor org to dir_dept relationship
               -- if none set at this level
             and  nvl(dxw.dir_dept, p.dir_dept)       dir_dept
        from orgs_cur c
        join orgs_hier p
          on c.ftvorgn.ftvorgn_nchg_date, sysdate + 1) > sysdate_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 ftvorgnorgn_title set seqno

select oh.orgn_code,
       lpad(' ', 2*lvl)||oh.orgn_code orgn_code_padded,
       
selectoh.orgn_title,
       lpad(' ', 2*lvl)||ftvorgn_oh.orgn_codetitle ftvorgnorgn_orgn_codetitle_padded,
       oh.lvl,
       ftvorgnoh.dir_titledept,
       lvld.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;