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_PEOPLE_F to retrieve both employee and manager person records and joins PER_PERSON_NAMES_F to obtain the manager’s GLOBAL display name. Additionally, it joins PER_ALL_ASSIGNMENTS_F to ensure the manager has an active employee assignment. Some effective date filters and parameter conditions are present in the query but are currently commented out. The result returns the display name of the employee’s line manager.
SQL Query Section
SELECT
MGR_PPNFF4.DISPLAY_NAME
FROM
per_assignment_supervisors_f psff1 ,
PER_ALL_PEOPLE_F PAPF_EMPP2 ,
PER_ALL_PEOPLE_F MGR_PAPFF3 ,
PER_PERSON_NAMES_F MGR_PPNFF4 ,
per_all_assignments_f MGR_PAAFF5
WHERE
1=1
AND psff1.MANAGER_TYPE = 'LINE_MANAGER'
and psff1.person_id = PAPF_EMPP2.person_id
and MGR_PAPFF3.person_id = psff1.manager_id
and MGR_PAPFF3.person_id = MGR_PPNFF4.person_id
and MGR_PAPFF3.person_id = MGR_PAAFF5.person_id
and MGR_PPNFF4.NAME_TYPE = 'GLOBAL'
--AND TO_DATE(:{PARAMETER.EFF_DATE},'YYYY/MM/DD') BETWEEN psff1.effective_start_date AND psff1.EFFECTIVE_END_DATE
--AND TO_DATE(:{PARAMETER.EFF_DATE},'YYYY/MM/DD') BETWEEN PAPF_EMPP2.effective_start_date AND PAPF_EMPP2.EFFECTIVE_END_DATE
--AND TO_DATE(:{PARAMETER.EFF_DATE},'YYYY/MM/DD') BETWEEN MGR_PAPFF3.effective_start_date AND MGR_PAPFF3.EFFECTIVE_END_DATE
--AND TO_DATE(:{PARAMETER.EFF_DATE},'YYYY/MM/DD') BETWEEN MGR_PPNFF4.effective_start_date AND MGR_PPNFF4.EFFECTIVE_END_DATE
--AND TO_DATE(:{PARAMETER.EFF_DATE},'YYYY/MM/DD') BETWEEN MGR_PAAFF5.effective_start_date AND MGR_PAAFF5.EFFECTIVE_END_DATE
and MGR_PAAFF5.assignment_status_type = 'ACTIVE'
and MGR_PAAFF5.assignment_type = 'E'
--and PAPF_EMPP2.person_id =:{PARAMETER.PERSON_ID} Output Results
Related Posts
How to Generate Check and Payslip
Posted on