Using the new (Oracle10g and higher) recursive subquery factoring syntax
with orgs (ftvorgn_orgn_code, ftvorgn_title, lvl, dir_dept) as (select ftvorgn_orgn_code, ftvorgn_title, 0 lvl, dir_dept from ftvorgn left outer join t_drew_dir_xwalk_ftvorgn xw on 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 and ftvorgn_orgn_code_pred is null union all select ftvorgn.ftvorgn_orgn_code, ftvorgn.ftvorgn_title, orgs.lvl + 1 lvl, nvl(xw.dir_dept, orgs.dir_dept) from ftvorgn join orgs on ftvorgn.ftvorgn_orgn_code_pred = orgs.ftvorgn_orgn_code left outer join t_drew_dir_xwalk_ftvorgn xw on ftvorgn.ftvorgn_orgn_code = xw.orgn_code where ftvorgn.ftvorgn_coas_code = 'D' and ftvorgn.ftvorgn_eff_date <= sysdate and nvl(ftvorgn.ftvorgn_term_date, sysdate + 1) > sysdate and nvl(ftvorgn.ftvorgn_nchg_date, sysdate + 1) > sysdate) search depth first by ftvorgn_title set seqno select lpad(' ', 2*lvl)||ftvorgn_orgn_code ftvorgn_orgn_code, ftvorgn_title, lvl, orgs.dir_dept, dept_desc from orgs left outer join t_drew_dir_deparments on orgs.dir_dept = t_drew_dir_deparments.dir_dept order by seqno;