Hierarchy query example

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;