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;