create or replace function ht_gettopdeptNAME(deptid in varchar) return varchar2 is r varchar2(1024); c int; m varchar2(20); r_f varchar2(1024); begin select a.tree_level_num, a.parent_node_name,a.tree_node into c, r,m from pstreenode a where a.tree_name = 'DEPT_SECURITY' and a.tree_node = deptid and A.EFFDT =(SELECT MAX(A_ED.EFFDT) FROM pstreenode A_ED WHERE A.tree_node = A_ED.tree_node); if c<3 then return null; end if; if c= 3 then select l.descr into r_f from ps_dept_tbl L WHERE L.DEPTID=m; return r_f; return r_f; else while(c<>3) loop select a.tree_level_num, a.parent_node_name into c, r from pstreenode a where a.tree_name = 'DEPT_SECURITY' and a.tree_node = m and A.EFFDT =(SELECT MAX(A_ED.EFFDT) FROM pstreenode A_ED WHERE A.tree_node = A_ED.tree_node); select a.tree_level_num, a.parent_node_name,a.parent_node_name into c, r,m from pstreenode a where a.tree_name = 'DEPT_SECURITY' and a.tree_node = m and A.EFFDT =(SELECT MAX(A_ED.EFFDT) FROM pstreenode A_ED WHERE A.tree_node = A_ED.tree_node); if c = 4 then select l.descr into r_f from ps_dept_tbl L WHERE l.deptid=r; return r_f; end if; end loop; end if; end ht_gettopdeptNAME;