EBS: 采购人员审核层级（需人工整理为树状结构图）

/* -- 环境初始化 
DECLARE 
 L_USER_ID NUMBER;
 L_RESP_ID NUMBER;
 L_RESP_APPL_ID NUMBER;
BEGIN 
  L_USER_ID:= 24354; -- 账号ID 
  L_RESP_ID:=21538;  --  21538 全局 HRMS 超级管理员
  L_RESP_APPL_ID:=800;  --800：21538  PER:人力资源管理系统
  
  APPS.fnd_global.apps_initialize(L_USER_ID,L_RESP_ID,L_RESP_APPL_ID); 
  MO_GLOBAL.INIT('S');  
END; 
*/ 
-- 人员分配
-- SELECT * FROM APPS.PER_ASSIGNMENTS_v  PA 

SELECT PSE.POS_STRUCTURE_ELEMENT_ID,
       PSE.PS_NAME AS "报告对象-层次结构" , 
       PSE.PARENT_POSITION_ID,
       PSE.PST_NAME AS "报告对象-名称",
       PPOS.POSITION_ID AS 职位ID,
       PPOS.NAME AS "职位-名称",
     --  PPOS.ORGANIZATION_ID,
     --  PPOS.ORG_NAME AS "组织",
       PPOS.JOB_ID,
       PPOS.JOB_NAME AS "职务",
       PPOS.LOCATION_ID,
       PPOS.LOCATION_CODE AS "地点" ,
     /*  PPOS.STATUS, 
       PPOS.STATUS_DESC AS "状态",
       PPOS.WORKING_HOURS AS "工作时数",
       PPOS.FREQUENCY ,
       PPOS.FREQUENCY_DESC 频率,
       PPOS.TIME_NORMAL_START AS "正常时间-开始",
       PPOS.TIME_NORMAL_FINISH AS "正常时间-终止",
       PPOS.REPLACEMENT_REQUIRED_FLAG,
       PPOS.REP_REQ_DESC AS "要求输入补替人员", */
       PAA.ASSIGNMENT_ID AS "分配ID",
       PAA.EFFECTIVE_START_DATE AS 开始日期,
       EMP.EMPLOYEE_NUMBER AS "工号",
       EMP.PERSON_ID AS "EMPLOYEE_ID",  --72690
       EMP.FULL_NAME AS "姓名"
     --  PPOS.POSITION_ID AS 职位ID,
     --  PPOS.NAME AS "职位-名称",
     --  PJ.JOB_ID AS "职务ID",
     --  PJ.NAME AS "职务",
      -- LOC.LOCATION_ID AS "地点ID",
      -- LOC.LOCATION_CODE AS "地点"
      -- , PAA.*
 FROM APPS.PER_PEOPLE_F EMP,  -- 人员表 
      APPS.PER_ALL_ASSIGNMENTS_F PAA,  --分配表
      APPS.PER_POSITIONS_V PPOS,  -- 职位表
      APPS.PER_JOBS_VL  PJ, -- 职务表
      APPS.HR_LOCATIONS LOC, -- 地点表
      APPS.PER_POS_STRUCTURE_ELEMENTS_V2   PSE -- 此职位的报告对象
    --  APPS.PER_POSITIONS_V PP -- 职位 
 WHERE 1=1
  AND NVL(PAA.EFFECTIVE_END_DATE,SYSDATE) >= SYSDATE
 AND EMP.PERSON_ID = PAA.PERSON_ID
-- AND PAA.PERSON_ID = 72690
 AND PAA.POSITION_ID = PPOS.POSITION_ID 
 AND PAA.JOB_ID = PJ.JOB_ID 
 AND PAA.LOCATION_ID = LOC.location_id  
 AND PAA.LOCATION_ID = 134357 --XX公司地点
 AND PSE.SUBORDINATE_POSITION_ID = PPOS.POSITION_ID
 ORDER BY  PSE.PARENT_POSITION_ID ,"报告对象-名称",职位ID,"职位-名称"
 -- ,地点ID, 职务ID,EMPLOYEE_ID
 
 -- PP.PERSON_ID = 72690
-- 人员表
/*
SELECT PP.EMPLOYEE_NUMBER AS "工号",
       PP.PERSON_ID AS "EMPLOYEE_ID",  --72690
 PP.* 
 FROM APPS.PER_PEOPLE_F PP
WHERE 1=1
-- AND LAST_NAME LIKE '陈XX'  -- 22110031
 AND PP.EMPLOYEE_NUMBER LIKE '2302117%'
*/
-- 职务表
-- SELECT * FROM  APPS.PER_JOBS_VL  PJ

-- SELECT * FROM APPS.HR_LOCATIONS LOC WHERE LOC.LOCATION_ID = 134357