This Employee Job Family sql query provides details of employees’ active job assignments including employee name, assignment number, status, organization name, job name, and job family code. The query filters for primary, active assignments excluding certain assignment types and ensures all data is current by checking effective date ranges.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Retrieves active employee assignments with job, organization, and job family details
HAOU.NAME ORGANIZATION_NAME
,PAPF.PERSON_NUMBER EMPLOYEE_NUMBER
,PPNNFV.FULL_NAME EMPLOYEE_NAME
,PAAM.ASSIGNMENT_NUMBER
,PAAM.ASSIGNMENT_STATUS_TYPE
,PJ.FULL_PART_TIME EMPLOYMENT_STATUS --Indicates if a job is full-time or part-time.
,PJ.JOB_CODE
,PJ.NAME JOB_NAME
,PJFFT.JOB_FAMILY_NAME JOB_FAMILY_NAME
FROM
PER_PERSON_NAMES_F_V PPNNFV
,PER_ALL_ASSIGNMENTS_M PAAM
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_ALL_PEOPLE_F PAPF
,PER_JOBS PJ
,PER_JOB_FAMILY_F_TL PJFFT
WHERE
PPNNFV.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE NOT IN ('ET','CT','PT') --Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms.
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' --Denormalized status of the assignment. This is derived using the Assignment Status Type ID.
AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAM.PRIMARY_FLAG = 'Y' --If set to 'Yes', record represents the primary assignment associated to the primary Work Relationship and primary set of Employment/Placement Terms.
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y' --Date Effective Entity: 'Y' indicates that this row represents the latest change in the day.
AND PAAM.JOB_ID = PJ.JOB_ID
AND PJ.JOB_FAMILY_ID = PJFFT.JOB_FAMILY_ID
AND PJFFT.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN PPNNFV.EFFECTIVE_START_DATE AND PPNNFV.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE