Using the new (Oracle10g and higher) recursive subquery factoring syntax
with orgs (ftvorgn_orgn_code, ftvorgn_title, lvl) as (select ftvorgn_orgn_code, ftvorgn_title, 0 lvl 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 and ftvorgn_orgn_code_pred is null union all select ftvorgn.ftvorgn_orgn_code, ftvorgn.ftvorgn_title, orgs.lvl + 1 lvl from ftvorgn join orgs on ftvorgn.ftvorgn_orgn_code_pred = orgs.ftvorgn_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 from orgs order by seqno;