This query gets the display name of the active line manager assigned to an employee based on the current system date.

This query selects the DISPLAY_NAME of a manager from PER_PERSON_NAMES_F who is assigned as a LINE_MANAGER to an employee. It uses PER_ASSIGNMENT_SUPERVISORS_F to identify the reporting relationship between the employee and the manager. The query joins PER_ALL_ASSIGNMENTS_F twice to link the employee’s assignment and the manager’s assignment. It also joins PER_ALL_PEOPLE_F and PER_PERSON_NAMES_F to retrieve the manager’s personal and name details. The query filters records using SYSDATE to ensure that all effective-dated records are currently valid. Additionally, it ensures both the employee and the manager have active employee assignments. The result returns distinct manager DISPLAY_NAME values.

SQL Query Section 

SELECT DISTINCT 

       MGR_PPNFF4.DISPLAY_NAME 

FROM 

per_assignment_supervisors_f psff1 , 

PER_ALL_ASSIGNMENTS_F emp_paaaf, 

PER_ALL_ASSIGNMENTS_F MGR_PAAFF5, 

PER_ALL_PEOPLE_F MGR_PAPFF3 , 

PER_PERSON_NAMES_F MGR_PPNFF4  

WHERE 

1=1 

AND psff1.MANAGER_TYPE = 'LINE_MANAGER'  

AND psff1.ASSIGNMENT_ID = emp_paaaf.ASSIGNMENT_ID 

AND psff1.MANAGER_ASSIGNMENT_ID = MGR_PAAFF5.ASSIGNMENT_ID 

AND MGR_PAAFF5.PERSON_ID = MGR_PAPFF3.PERSON_ID 

AND MGR_PAPFF3.PERSON_ID = MGR_PPNFF4.PERSON_ID 

AND MGR_PPNFF4.NAME_TYPE = 'GLOBAL' 

AND sysdate BETWEEN psff1.effective_start_date AND psff1.EFFECTIVE_END_DATE 

AND sysdate BETWEEN emp_paaaf.effective_start_date AND emp_paaaf.EFFECTIVE_END_DATE 

AND sysdate BETWEEN MGR_PAAFF5.effective_start_date AND MGR_PAAFF5.EFFECTIVE_END_DATE 

AND sysdate BETWEEN MGR_PAPFF3.effective_start_date AND MGR_PAPFF3.EFFECTIVE_END_DATE 

AND sysdate BETWEEN MGR_PPNFF4.effective_start_date AND MGR_PPNFF4.EFFECTIVE_END_DATE 

AND emp_paaaf.assignment_status_type = 'ACTIVE' 

AND emp_paaaf.assignment_type = 'E' 

AND MGR_PAAFF5.assignment_status_type = 'ACTIVE' 

AND MGR_PAAFF5.assignment_type = 'E' 

--and PAPF_EMPP2.person_id =:{PARAMETER.PERSON_ID}

Output Results

This query is used to get the person number of the active line manager assigned to an employee as of a specific effective date.

This query is used to get the date that is 12 months after the effective start date of an element entry value associated with a payroll relationship group.

This query is used to get the person number of the active line manager assigned to an employee as of a specific effective date.

This query is used to get the date that is 12 months after the effective start date of an element entry value associated with a payroll relationship group.

Leave a Reply Cancel reply

Useful Links

Our Services

Contact Information

This query gets the display name of the active line manager assigned to an employee based on the current system date.