EBS根据登录名或工号查询相关职位指定审批层次的审批流结构

最新推荐文章于 2024-07-13 18:10:19 发布

yimingyifei

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量68

点赞数

分类专栏： EBS数据库查询 800-PER-人力资源管理系统文章标签：数据库

本文链接：https://blog.csdn.net/yimingyifei/article/details/127358032

版权

EBS数据库查询同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

800-PER-人力资源管理系统

1 篇文章 0 订阅

订阅专栏

declare 
  I integer:=20;
  P_STRUCTURE_VERSION NUMBER(19):=审批层次版本ID;
  P_POSITION_ID NUMBER(19);
  P_POSITION VARCHAR2(100);
  P_POSITION_ID_PAR NUMBER(19);
  P_POSITION_PAR VARCHAR2(100);
  P_FLAG INTEGER:=0;
  P_LEVEL NUMBER(19):=1;
begin
SELECT PAAF.POSITION_ID INTO P_POSITION_ID
  FROM HR.PER_ALL_POSITIONS     PAP,
       APPLSYS.FND_USER         FU,
       HR.PER_ALL_ASSIGNMENTS_F PAAF,
       HR.PER_ALL_PEOPLE_F      PAPF
 WHERE 1 = 1
   AND PAP.POSITION_ID = PAAF.POSITION_ID
   AND FU.PERSON_PARTY_ID = PAPF.PARTY_ID
   AND PAAF.PERSON_ID = PAPF.PERSON_ID
   AND PAPF.EMPLOYEE_NUMBER = '工号'
   AND PAPF.GLOBAL_NAME = '姓名'
   AND FU.USER_nAME = '登录名';
SELECT COUNT(*) INTO P_FLAG FROM HR.PER_POS_STRUCTURE_ELEMENTS PPSE WHERE PPSE.SUBORDINATE_POSITION_ID=P_POSITION_ID AND PPSE.POS_STRUCTURE_VERSION_ID=P_STRUCTURE_VERSION;
WHILE P_FLAG=1 AND I>0 LOOP 
  SELECT PPSE.PARENT_POSITION_ID,PAP_F.NAME,PAP_S.NAME INTO P_POSITION_ID_PAR ,P_POSITION_PAR,P_POSITION
         FROM HR.PER_ALL_POSITIONS PAP_S,HR.PER_ALL_POSITIONS PAP_F, HR.PER_POS_STRUCTURE_ELEMENTS PPSE 
         WHERE PAP_S.POSITION_ID=PPSE.SUBORDINATE_POSITION_ID AND PAP_F.POSITION_ID=PPSE.PARENT_POSITION_ID AND PPSE.SUBORDINATE_POSITION_ID=P_POSITION_ID AND PPSE.POS_STRUCTURE_VERSION_ID=P_STRUCTURE_VERSION;
  
  DBMS_OUTPUT.PUT_LINE('层次:'||P_LEVEL||' , 父结点:'||P_POSITION_PAR||' , 子结点:'||P_POSITION);
  P_POSITION_ID:=P_POSITION_ID_PAR;
  SELECT COUNT(*) INTO P_FLAG FROM HR.PER_POS_STRUCTURE_ELEMENTS PPSE WHERE PPSE.SUBORDINATE_POSITION_ID=P_POSITION_ID AND PPSE.POS_STRUCTURE_VERSION_ID=P_STRUCTURE_VERSION;
  I:=I-1;
  P_LEVEL:=P_LEVEL+1;
END LOOP;
end;