This Employee Details query displays active employee details including employee number, name, job title, assignment, email, supervisor information, and business unit as of today, helping HR teams manage workforce records and reporting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve employee details with job, supervisor, email, and assignment info.
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF2.FULL_NAME EMPLOYEE_NAME,
PJFT.NAME JOB_NAME,
PAAM.ASSIGNMENT_NUMBER EMP_ASSIGNMENT_NUMBER,
GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 || '-' || GCC.SEGMENT5 || '-' || GCC.SEGMENT6 EXP_ACCOUNT,
PAAM.EFFECTIVE_START_DATE ASSG_EFFECTIVE_START_DATE,
PEA.EMAIL_ADDRESS,
PPNF.FULL_NAME SUPERVISOR_NAME,
PAPF2.PERSON_NUMBER SUPERVISOR_NUMBER,
HOUFT.NAME BUSINESS_UNIT,
PAPF.START_DATE HIRE_DATE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_PEOPLE_F PAPF2, -- This table is used to fetch the supervisor info.
PER_ALL_ASSIGNMENTS_M PAAM,
PER_JOBS_F PJF,
PER_JOBS_F_TL PJFT,
GL_CODE_COMBINATIONS GCC,
PER_EMAIL_ADDRESSES PEA,
PER_ASSIGNMENT_SUPERVISORS_F PASF,
PER_PERSON_NAMES_F PPNF,
PER_PERSON_NAMES_F PPNF2, -- This table is used to fetch the employee name info.
HR_ORGANIZATION_UNITS_F_TL HOUFT
WHERE
PAPF.PERSON_ID = PAAM.PERSON_ID
AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PAAM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAAM.DEFAULT_CODE_COMB_ID = GCC.CODE_COMBINATION_ID
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND PAAM.JOB_ID = PJF.JOB_ID
AND TRUNC (SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE
AND PJF.EFFECTIVE_END_DATE
AND PEA.EMAIL_ADDRESS_ID = PAPF.PRIMARY_EMAIL_ID
AND PAPF.PERSON_ID = PASF.PERSON_ID
AND PAPF.PERSON_ID = PPNF2.PERSON_ID
AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
AND PPNF.PERSON_ID = PASF.MANAGER_ID
AND PAPF2.PERSON_ID = PASF.MANAGER_ID
AND TRUNC (SYSDATE) BETWEEN PAPF2.EFFECTIVE_START_DATE
AND PAPF2.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE
AND PASF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE
AND PPNF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPNF2.EFFECTIVE_START_DATE
AND PPNF2.EFFECTIVE_END_DATE
AND PJF.JOB_ID = PJFT.JOB_ID
AND PJFT.LANGUAGE = 'US'
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PPNF2.NAME_TYPE = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN PJFT.EFFECTIVE_START_DATE
AND PJFT.EFFECTIVE_END_DATE
AND HOUFT.ORGANIZATION_ID = PAAM.BUSINESS_UNIT_ID
AND HOUFT.LANGUAGE = 'US'
ORDER BY
PAPF.PERSON_NUMBER ASC