peoplesoft function PSTREENODE 通过 deptid 获得部门树 一级部门 code

create or replace function ht_gettopdeptid(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 return m;

       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

        return r;

      end if;

    end loop;

  end if;

end ht_gettopdeptid;