peoplesoft function PSTREENODE 通过 deptid 获得部门树 全路径 code

create or replace function getUnitFullcode(deptid in varchar)
  return varchar2 is
  r      varchar2(1024);
  c      int;
  n      varchar2(1024);
  m      varchar2(1024);
  r_name varchar2(1024);
  m_deptid varchar2(64);
begin
  r := '';
  select a.tree_level_num
    into c
    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);  
                    r:=deptid;            
                   
  if c = 1 then
    return r;
  end if;
                    

  select a.tree_level_num, a.parent_node_name, a.tree_node
    into c, n, 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);
  while c <> 1 loop
  
    r := n || '/' || r;

      select a.tree_level_num, a.parent_node_name, a.tree_node
      into c, n, m
      from pstreenode a
     where a.tree_name = 'DEPT_SECURITY'
       and a.tree_node = n
       and A.EFFDT = (SELECT MAX(A_ED.EFFDT)
                        FROM pstreenode A_ED
                       WHERE A.tree_node = A_ED.tree_node);
    end loop;
      return r;
  end getUnitFullcode;