Using the new (Oracle10g Oracle11gR2 and higher) recursive subquery factoring syntax
No Format |
---|
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; |