Page History

with
  -- all current data-enterable finance orgs
  orgs_cur (ftvorgn_orgn_code, ftvorgn_title, lvl, dir_deptftvorgn_orgn_code_pred)
    as (select ftvorgn_orgn_code, ftvorgn_title, 0 lvl, dir_dept
        from ftvorgn 
        left outer join t_drew_dir_xwalk_ftvorgn xw_orgn_code_pred
          onfrom 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),

  -- 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     
        union all
   orgn_code, 
       
        select ftvorgnc.ftvorgn_title                     orgn_codetitle, ftvorgn.ftvorgn_title, orgs.lvl + 1 lvl, 
               0                                   lvl, 
               nvl(xwdxw.dir_dept, orgs.dir_dept)
        from ftvorgn
        join orgs
     dir_dept
      on ftvorgn.ftvorgn_orgn_code_pred = from orgs.ftvorgn_orgn_code_cur c
        left outer join t_drew_dir_xwalk_ftvorgn xwdxw
          on ftvorgnc.ftvorgn_orgn_code = xwdxw.orgn_code
        where ftvorgnc.ftvorgn_coas_code = 'D'orgn_code_pred is null
           
        union all
           
        andselect ftvorgnc.ftvorgn_eff_date <= sysdate
orgn_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)||ftvorgnoh.orgn_code orgn_code ftvorgn__padded,
       oh.orgn_codetitle,
       lpad(' ',  ftvorgn2*lvl)||oh.orgn_title orgn_title_padded,
       oh.lvl,
       orgsoh.dir_dept,
       d.dept_desc
from orgs_hier oh
left outer join t_drew_dir_deparments d
  on orgsoh.dir_dept = t_drew_dir_deparmentsd.dir_dept
order by oh.seqno;