This Employee Grade Details query retrieves active primary employee assignments, including personal details, assignment attributes, and grade information. It filters out non-employees and ensures the latest data is selected by using subqueries for versioning and update timestamps. Results are ordered by employee number for clear, structured reporting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves active employee assignments with latest grade and personal details.
HAOU.NAME,
PAPF.PERSON_NUMBER,
PPNF.FULL_NAME,
PAAF.ASSIGNMENT_NUMBER,
PAAF.ASSIGNMENT_NAME,
PAAF.ASSIGNMENT_TYPE,
PAAF.ASSIGNMENT_STATUS_TYPE,
PAAF.EFFECTIVE_START_DATE ASSIGNMENT_EFFECTIVE_START_DATE,
PAAF.EFFECTIVE_END_DATE ASSIGNMENT_EFFECTIVE_END_DATE,
PGF.GRADE_CODE,
PGF.NAME GRADE_NAME,
PGF.GRADE_TYPE,
PGF.ACTIVE_STATUS GRADE_STATUS,
PGF.EFFECTIVE_START_DATE GRADE_EFFECTIVE_START_DATE,
PGF.EFFECTIVE_END_DATE GRADE_EFFECTIVE_END_DATE
FROM
PER_ALL_ASSIGNMENTS_M PAAF,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F_V PPNF,
PER_GRADES_F_VL PGF,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_TYPE NOT IN ( 'ET', 'CT', 'PT' ) --THIS FILTER EXCLUDES EXTERNAL TRAINEES, CONTRACTORS, AND PENDING WORKERS WHO HAVE NOT YET STARTED.
AND PAAF.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' --THIS FILTER INCLUDES ONLY ACTIVE EMPLOYEE ASSIGNMENTS.
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID (+)
AND PAAF.PRIMARY_FLAG = 'Y' --THIS FILTER INCLUDES ONLY THE EMPLOYEE’S PRIMARY ASSIGNMENT.
AND PAAF.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PPNF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.OBJECT_VERSION_NUMBER = (
SELECT
MAX(OBJECT_VERSION_NUMBER)
FROM
PER_ALL_PEOPLE_F V
WHERE
V.PERSON_ID = PAPF.PERSON_ID
)
AND PAPF.LAST_UPDATE_DATE = (
SELECT
MAX(LAST_UPDATE_DATE)
FROM
PER_ALL_PEOPLE_F M
WHERE
M.PERSON_ID = PAPF.PERSON_ID
)
AND PAAF.LAST_UPDATE_DATE = (
SELECT
MAX(LAST_UPDATE_DATE)
FROM
PER_ALL_ASSIGNMENTS_M M
WHERE
M.PERSON_ID = PAAF.PERSON_ID
)
AND PPNF.OBJECT_VERSION_NUMBER = (
SELECT
MAX(OBJECT_VERSION_NUMBER)
FROM
PER_PERSON_NAMES_F_V V
WHERE
V.PERSON_ID = PPNF.PERSON_ID
)
AND PAAF.GRADE_ID = PGF.GRADE_ID
AND PGF.LAST_UPDATE_DATE = (
SELECT
MAX(LAST_UPDATE_DATE)
FROM
PER_GRADES_F_VL M
WHERE
M.GRADE_ID = PGF.GRADE_ID
)
AND PGF.GRADE_TYPE = 'GRADE'
AND PPNF.EFFECTIVE_START_DATE = (
SELECT
MAX(EFFECTIVE_START_DATE)
FROM
PER_PERSON_NAMES_F_V V
WHERE
V.PERSON_NAME_ID = PPNF.PERSON_NAME_ID
)
AND 1 = 1
ORDER BY
TO_NUMBER(PERSON_NUMBER) ASC;