Oracle Fusion HCM - Query to get Number of manager change for an employee

-

 ✅ Purpose

This query fetches the number of manager changes (specifically line manager) per employee between a selected date range, displaying current and historical manager assignments.

 ✅ Table details      

  • PER_ALL_PEOPLE_F – Employee personal data

  • PER_ALL_ASSIGNMENTS_M – Assignment-level data

  • PER_ASSIGNMENT_SUPERVISORS_F – Supervisor (manager) history

  • PER_PERIODS_OF_SERVICE – Employee service periods

  • PER_PERSON_NAMES_F – Full names of employees and managers

 🧾 Final SQL Query 

ð SELECT DISTINCT 
EMP.PERSON_NUMBER EMPLOYEE_NUMBER,
EMP_NAME.FULL_NAME EMP_NAME,
MGR_NAME.FULL_NAME MANAGER_NAME,
MGR.PERSON_NUMBER SUPERVISOR_EMP_NUMBER,
to_char(PASF.EFFECTIVE_START_DATE,'yyyy/mm/dd') EFFECTIVE_DATE_DATA_CHANGE,
to_char(PASF.EFFECTIVE_START_DATE,'yyyy/mm/dd') EFFECTIVE_DATE_MANAGER, 
FLV.MEANING MANAGER_TYPE,
(COUNT(DISTINCT MGR.PERSON_NUMBER ) OVER (PARTITION BY EMP.PERSON_ID)) Mgr_count  

FROM
PER_ALL_PEOPLE_F    EMP,
PER_ALL_ASSIGNMENTS_M   PAAM_EMP,
PER_ASSIGNMENT_SUPERVISORS_F PASF,
PER_ALL_PEOPLE_F    MGR,
PER_PERIODS_OF_SERVICE PPS,
PER_PERSON_NAMES_F    EMP_NAME,
PER_PERSON_NAMES_F    MGR_NAME,
FND_LOOKUP_VALUES FLV
WHERE
EMP.PERSON_ID = PAAM_EMP.PERSON_ID
AND PAAM_EMP.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAM_EMP.ASSIGNMENT_TYPE = 'E'
and PAAM_EMP.PRIMARY_FLAG='Y'
AND PAAM_EMP.EFFECTIVE_LATEST_CHANGE = 'Y'
AND ACTION_CODE  ='MANAGER_CHANGE'
AND EMP.PERSON_ID = PASF.PERSON_ID
AND PASF.ASSIGNMENT_ID = PAAM_EMP.ASSIGNMENT_ID
AND PASF.MANAGER_TYPE in('LINE_MANAGER')
AND MGR.PERSON_ID = PASF.MANAGER_ID
AND PAAM_EMP.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PAAM_EMP.PERSON_ID = PPS.PERSON_ID
AND  PPS.DATE_START= (SELECT MAX(DATE_START) FROM PER_PERIODS_OF_SERVICE PPOS_IN WHERE EMP.PERSON_ID=PPOS_IN.PERSON_ID AND PPOS_IN.   PRIMARY_FLAG='Y')
AND MGR.PERSON_ID=MGR_NAME.PERSON_ID
AND MGR_NAME.NAME_TYPE = 'GLOBAL'
AND EMP.PERSON_ID = EMP_NAME.PERSON_ID 
AND EMP_NAME.NAME_TYPE = 'GLOBAL'
AND FLV.LOOKUP_TYPE ='PER_SUPERVISOR_TYPE' 
AND FLV.LOOKUP_CODE= PASF.MANAGER_TYPE 
and FLV.LANGUAGE='US'
AND TRUNC(PAAM_EMP.EFFECTIVE_START_DATE) BETWEEN EMP_NAME.EFFECTIVE_START_DATE AND EMP_NAME.EFFECTIVE_END_DATE
AND TRUNC(PAAM_EMP.EFFECTIVE_START_DATE) BETWEEN MGR_NAME.EFFECTIVE_START_DATE AND MGR_NAME.EFFECTIVE_END_DATE
AND TRUNC(PAAM_EMP.EFFECTIVE_START_DATE) BETWEEN EMP.EFFECTIVE_START_DATE AND EMP.EFFECTIVE_END_DATE
AND TRUNC(PAAM_EMP.EFFECTIVE_START_DATE) BETWEEN MGR.EFFECTIVE_START_DATE AND MGR.EFFECTIVE_END_DATE
AND TRUNC(PAAM_EMP.EFFECTIVE_START_DATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
AND  to_date(TO_CHAR( PAAM_EMP.EFFECTIVE_START_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') between to_DATE(:Effective_Date_From,'MM/DD/YYYY') and to_DATE(:Effective_Date_To,'MM/DD/YYYY')
ORDER BY EMP.PERSON_NUMBER

Comments

Post a Comment

Popular posts from this blog

Query to get Salary Change details in Fusion HCM

-
 SELECT papf.person_number PERSON_NUMBER, (select full_name from per_person_names_f ppnf where ppnf.person_id = papf.person_id and name_type = 'GLOBAL' and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date) person_name, ple.name LEGAL_EMPLOYE, (CASE  when cs.SALARY_BASIS_CODE = 'ANNUAL' THEN 'Salary Change'  else  'Hourly Rate Change' end)  ACTION_NAME, to_char(cs_old.salary_amount) OLD_DATA,  to_char(CS.salary_amount) NEW_DATA    ,TO_CHAR(cs.date_from, 'MM/DD/YYYY') EFFECTIVE_DATE    ,  TO_CHAR(cs.creation_date, 'MM/DD/YYYY') CHANGED_DATE    , cs.LAST_UPDATED_BY CHANGED_BY           from  fusion.CMP_SALARY cs,           CMP_SALARY cs_old,   PER_ALL_ASSIGNMENTS_M  PAAM,   PER_ALL_PEOPLE_F PAPF   ,per_legal_employers ple          where 1 = 1    AND PAPF.PERSON_ID = P...
Read more

🧾 Streamlining Payroll Operations with Oracle Payroll Activity Center

-
Image
Managing payroll efficiently across global teams has never been more critical—or more complex. Oracle Fusion HCM introduces the  Payroll Activity Center , part of the Redwood user experience, to help payroll teams take control with a centralized, intelligent, and highly actionable interface. Designed with usability and impact in mind, this hub simplifies payroll monitoring, boosts accuracy, and brings AI-powered insight to the forefront. 🌟 What Is the Payroll Activity Center? The  Payroll Activity Center  is an action-focused workspace tailored to payroll professionals. It aggregates relevant applications, displays role-specific KPIs, and provides intuitive navigation through the payroll cycle—all within Oracle’s new Redwood design system. This replaces the older Payroll Dashboard and brings everything you need into a single, streamlined view. 🔑 Key Features Aggregated Applications View relevant payroll applications, flows, and data all in one place. In-Workflow Communi...
Read more

Oracle Fusion HCM - Enhancements to Summary of Changes and History Sections in Redwood Workforce Structures Pages

-
Image
 The  Summary of Changes  and  History  sections on Redwood  Workforce Structures  pages now display  additional attributes , providing a more detailed view of historical and recent changes to your workforce data. These enhancements apply to the following Redwood pages: Redwood Page New Attributes in Summary of Changes and History Sections Departments Status Divisions Status Disability Organizations Status Enterprise HCM Information Status Jobs Annual Working Duration Scheduling Group Standard Annual Working Duration Standard Working Hours Standard Working Frequency Locations Annual Working Duration Standard Annual Working Duration Standard Working Hours Standard Working Frequency Reporting Establishments Status Worker Unions Status These additional attributes are now visible in both the  Summary of Changes  and  History  sections, enabling users to quickly access and review key data points related to structural and job-related...
Read more