• peoplesoft function PSTREENODE 通过 deptid 获得部门树 一级部门 名称


    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;
  • 相关阅读:
    [atARC100F]Colorful Sequences
    [atARC103D]Robot Arms
    [atARC107F]Sum of Abs
    [atAGC047F]Rooks
    [loj3278]收获
    [cf809E]Surprise me
    [cf997E]Good Subsegments
    [cf603E]Pastoral Oddities
    Codeforces Round #453
    Educational Codeforces Round 32
  • 原文地址:https://www.cnblogs.com/eason-liu/p/3387898.html
Copyright © 2020-2023  润新知