This Employee Job Details query displays job, assignment, and organization details for active employees, supporting workforce analysis and assignment validation.
#sqlquery
SELECT --SQL4Fusion (An Orbit Analytics Project)
--This query returns active employees job and assignment details for the Employee Job Details report
HAOU.NAME ORGANIZATION_NAME,
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_NAME,
PAAM.ASSIGNMENT_NUMBER,
PAAM.ASSIGNMENT_NAME,
PAAM.ASSIGNMENT_STATUS_TYPE ASSIGNMENT_STATUS,
PJFVL.NAME JOB_NAME,
PJFVL.EFFECTIVE_START_DATE JOB_START_DATE,
PJFVL.EFFECTIVE_END_DATE JOB_END_DATE
FROM PER_PERSON_NAMES_F_V PPNFV,
PER_ALL_ASSIGNMENTS_M PAAM,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_ALL_PEOPLE_F PAPF,
PER_JOBS_F_VL PJFVL
WHERE PPNFV.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 'Y', 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 = PJFVL.JOB_ID
AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PJFVL.EFFECTIVE_START_DATE AND PJFVL.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;