Hierarchical query on employees and their supervisor in Oracle HR
. Integrated into Oracle Cloud HCM so that HR professionals and managers can perform all their workforce planning activities in one place, without the need to use multiple applications. Oracle Strategic Workforce Planning Plan your workforce needs by analyzing trends and forecasts 12 Oracle HCM Cloud. Work Made Human. Yowan's post will definitely resolve 99% of the issues related to multiple (not only duplicates) records being fetched for a given employee-supervisor combination. It will bring the latest supervisor name attached to a given employee. I've highlighted 'latest' not active. So, we will use this supervisor hierarchy table to get the Direct and Indirect reportees now. MANAGERTYPE is a lookup code and we need to use the right MANAGERTYPE to get the right hierarchy. Normally everyone uses the LINEMANAGER hierarchy, However some clients create new manager types as per their requirement.
I am responsible for the sale and account management of Oracle's portfolio of Human Capital Management Software for Eastern Canada. As a consultant/advisor to existing and new Oracle clients, I assist organizations in achieving outstanding business performance through the optimization of their HR systems, processes and technologies. Unable To Update the HCM Manager / Supervisor For A User Via Manage User Screen (Doc ID 1606158.1) Last updated on OCTOBER 14, 2019. Applies to: Oracle Fusion Incentive Compensation - Version 11.13.18.10.0 and later Oracle Fusion Sales Cloud Service - Version 11.13.19.07.0 and later.
We all know the famous emp/dept hierarchical queries to show employees belonging to a department and employees with their manager. I recently came across a question on how to query all employees and their managers (called supervisors in HR) in a hierarchical manor so I created this query as combination of what I've read in https://technology.amis.nl/2005/08/16/hierarchical-query-with-nodes-from-different-tables-dept-and-emp-nodes-in-one-tree/) and Oracle database schema.
Note that the employee - supervisor link can be circular. In that case you will not find those employees in the tree, since there is no top node where the manager (supervisor_id) is empty.
Oracle Hcm Supervisor Querybaldcirclekidz Salary
with emps as(
-- The managers
select s.person_id empno
, s.first_name || ' ' || s.last_name ename
, sa.supervisor_id mgr
from per_people_x s
, per_assignments_x sa
where s.person_id = sa.person_id
and exists (select 1 from per_assignments_x a where a.supervisor_id = s.person_id)
union
-- The employees
select p.person_id empno -- all EMP-nodes
, p.first_name || ' ' || p.last_name ename
, a.supervisor_id mgr
from per_people_x p, per_assignments_x a
where p.person_id = a.person_id
)
select lpad(' ', level*3)||ename ename
from emps
connect
by prior empno = mgr
start
with
nvl (mgr,-1) = -1
/