You are currently viewing SQL Query to get Unique and Active Line Managers Names
SQL Query to get Unique and Active Line Managers Names

In this article we will look into how to get the line manager/supervisor names.

Normally we get the Line Manager Info from PER_ASSIGNMENT_SUPERVISORS_F for each employee.

If we have a requirement to get the Active Line Managers list, we will have to check this table for the manager and then check if he has an active assignment or not. Also, as multiple employees can have the same manager, we could get duplicate rows.

Here is the query that we have designed to get the Active Line Manager Names.

select distinct PAPF_SUP.PERSON_NUMBER, PPNF.FULL_NAME
from PER_ASSIGNMENT_SUPERVISORS_F PASF,PER_ALL_ASSIGNMENTS_M ASSG, PER_ALL_PEOPLE_F PAPF_SUP, PER_PERSON_NAMES_F PPNF
where PASF.MANAGER_ID = PAPF_SUP.PERSON_ID
AND ASSG.PERSON_ID = PASF.MANAGER_ID
AND ASSG.SYSTEM_PERSON_TYPE = 'EMP'
AND ASSG.ASSIGNMENT_TYPE = 'E'
AND ASSG.PRIMARY_FLAG = 'Y'
AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y'
AND ASSG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
AND PAPF_SUP.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND sysdate BETWEEN ASSG.EFFECTIVE_START_DATE AND ASSG.EFFECTIVE_END_DATE
AND sysdate BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
AND sysdate BETWEEN PAPF_SUP.EFFECTIVE_START_DATE AND PAPF_SUP.EFFECTIVE_END_DATE
AND sysdate BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

If there is a requirement to get this list of values for a DFF configured on a page, then we have to create a table Valueset to accommodate this SQL Query. However as we cannot write the distinct clause in the table Valueset, we will end up having duplicate names in the list of values.

Here is a workaround for that requirement where in we can use the distinct clause in the from query itself and then join it with other tables in the where clause.

select PPNF.FULL_NAME
from 
(select distinct MANAGER_TYPE, MANAGER_ID from PER_ASSIGNMENT_SUPERVISORS_F where sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE) PASF,PER_ALL_ASSIGNMENTS_M ASSG, PER_ALL_PEOPLE_F PAPF_SUP, PER_PERSON_NAMES_F PPNF
where 
PASF.MANAGER_ID = PAPF_SUP.PERSON_ID
AND ASSG.PERSON_ID = PASF.MANAGER_ID
AND ASSG.SYSTEM_PERSON_TYPE = 'EMP'
AND ASSG.ASSIGNMENT_TYPE = 'E'
AND ASSG.PRIMARY_FLAG = 'Y'
AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y'
AND ASSG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
AND PAPF_SUP.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND sysdate BETWEEN ASSG.EFFECTIVE_START_DATE AND ASSG.EFFECTIVE_END_DATE
AND sysdate BETWEEN PAPF_SUP.EFFECTIVE_START_DATE AND PAPF_SUP.EFFECTIVE_END_DATE
AND sysdate BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.