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, 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 and ftvorgn_to directory departments at rollup levels orgs_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 + 1title, 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_coasorgn_code = 'D'_pred is null union all andselect ftvorgnc.ftvorgn_eff_date <= sysdate orgn_code orgn_code, and nvl(ftvorgnc.ftvorgn_term_date, sysdate + 1) > sysdatetitle orgn_title, p.lvl + 1 lvl, -- use predecessor org to dir_dept relationship -- if none set at this level and nvl(ftvorgn.ftvorgn_nchg_date, sysdate + 1) > sysdate 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 ftvorgnorgn_title set seqno select oh.orgn_code, select lpad(' ', 2*lvl)||ftvorgnoh.orgn_code orgn_code ftvorgn__padded, oh.orgn_codetitle, ftvorgnlpad(' ', 2*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; |