Oracle HCM SQL Query Library

Feb 6, 2026 25 min read 7 queries

Production-ready SQL queries for Oracle Fusion Cloud HCM. Every query uses proper date-effective joins, correct table aliases, and handles the common gotchas that trip up Oracle developers.

Before you start: All queries use PER_ALL_ASSIGNMENTS_M (the materialized table). If you're on an older release, swap _M for _F and add EFFECTIVE_LATEST_CHANGE = 'Y' to the date-effective filters.

1. Active Employee Roster

Core HR Most Used

The foundation query. Returns all active employees with their department, location, job, position, grade, and hire date. This is the query you'll modify for 80% of your reports.

SELECT
    papf.PERSON_NUMBER,
    ppnf.FIRST_NAME,
    ppnf.LAST_NAME,
    paam.ASSIGNMENT_NUMBER,
    paam.ASSIGNMENT_STATUS_TYPE,
    haou.NAME AS DEPARTMENT,
    hla.LOCATION_NAME,
    pjfv.NAME AS JOB_NAME,
    hapf.NAME AS POSITION_NAME,
    pgf.NAME AS GRADE_NAME,
    paam.NORMAL_HOURS,
    paam.FREQUENCY,
    ppos.DATE_START AS HIRE_DATE,
    paam.EFFECTIVE_START_DATE
FROM PER_ALL_PEOPLE_F papf
JOIN PER_PERSON_NAMES_F ppnf
    ON papf.PERSON_ID = ppnf.PERSON_ID
    AND ppnf.NAME_TYPE = 'GLOBAL'
    AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_M paam
    ON papf.PERSON_ID = paam.PERSON_ID
    AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
    AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND paam.PRIMARY_FLAG = 'Y'
    AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_PERIODS_OF_SERVICE ppos
    ON paam.PERIOD_OF_SERVICE_ID = ppos.PERIOD_OF_SERVICE_ID
LEFT JOIN HR_ALL_ORGANIZATION_UNITS haou
    ON paam.ORGANIZATION_ID = haou.ORGANIZATION_ID
LEFT JOIN HR_LOCATIONS_ALL hla
    ON paam.LOCATION_ID = hla.LOCATION_ID
LEFT JOIN PER_JOBS_F_VL pjfv
    ON paam.JOB_ID = pjfv.JOB_ID
    AND TRUNC(SYSDATE) BETWEEN pjfv.EFFECTIVE_START_DATE AND pjfv.EFFECTIVE_END_DATE
LEFT JOIN HR_ALL_POSITIONS_F_VL hapf
    ON paam.POSITION_ID = hapf.POSITION_ID
    AND TRUNC(SYSDATE) BETWEEN hapf.EFFECTIVE_START_DATE AND hapf.EFFECTIVE_END_DATE
LEFT JOIN PER_GRADES_F_VL pgf
    ON paam.GRADE_ID = pgf.GRADE_ID
    AND TRUNC(SYSDATE) BETWEEN pgf.EFFECTIVE_START_DATE AND pgf.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
ORDER BY ppnf.LAST_NAME, ppnf.FIRST_NAME

Why PRIMARY_FLAG = 'Y'? Employees can have multiple assignments (e.g., a primary job + a secondary assignment). Without this filter, you get duplicate rows.

Key tables: PER_ALL_PEOPLE_F, PER_PERSON_NAMES_F, PER_ALL_ASSIGNMENTS_M

2. Headcount by Department

Core HR Analytics

Department-level headcount with FTE totals and full-time vs. part-time breakdown. Essential for workforce planning.

SELECT
    haou.NAME AS DEPARTMENT,
    hla.LOCATION_NAME,
    COUNT(DISTINCT papf.PERSON_ID) AS HEADCOUNT,
    SUM(CASE WHEN paam.ASSIGNMENT_CATEGORY = 'FR' THEN 1 ELSE 0 END) AS FULL_TIME,
    SUM(CASE WHEN paam.ASSIGNMENT_CATEGORY = 'PT' THEN 1 ELSE 0 END) AS PART_TIME,
    SUM(pawm.FTE) AS TOTAL_FTE
FROM PER_ALL_PEOPLE_F papf
JOIN PER_ALL_ASSIGNMENTS_M paam
    ON papf.PERSON_ID = paam.PERSON_ID
    AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
    AND paam.PRIMARY_FLAG = 'Y'
    AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
LEFT JOIN PER_ASSIGN_WORK_MEASURES_F pawm
    ON paam.ASSIGNMENT_ID = pawm.ASSIGNMENT_ID
    AND TRUNC(SYSDATE) BETWEEN pawm.EFFECTIVE_START_DATE AND pawm.EFFECTIVE_END_DATE
LEFT JOIN HR_ALL_ORGANIZATION_UNITS haou
    ON paam.ORGANIZATION_ID = haou.ORGANIZATION_ID
LEFT JOIN HR_LOCATIONS_ALL hla
    ON paam.LOCATION_ID = hla.LOCATION_ID
WHERE TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
GROUP BY haou.NAME, hla.LOCATION_NAME
ORDER BY haou.NAME

Key tables: PER_ASSIGN_WORK_MEASURES_F for FTE, HR_ALL_ORGANIZATION_UNITS for department names

3. Turnover Report (Last 12 Months)

Core HR Analytics

Monthly termination counts with voluntary vs. involuntary breakdown. The query most executives ask for.

SELECT
    TO_CHAR(ppos.ACTUAL_TERMINATION_DATE, 'YYYY-MM') AS TERM_MONTH,
    haou.NAME AS DEPARTMENT,
    COUNT(*) AS TERMINATIONS,
    COUNT(CASE WHEN ppos.LEAVING_REASON = 'RESIGNATION' THEN 1 END) AS VOLUNTARY,
    COUNT(CASE WHEN ppos.LEAVING_REASON != 'RESIGNATION' THEN 1 END) AS INVOLUNTARY
FROM PER_PERIODS_OF_SERVICE ppos
JOIN PER_ALL_ASSIGNMENTS_M paam
    ON ppos.PERSON_ID = paam.PERSON_ID
    AND ppos.PERIOD_OF_SERVICE_ID = paam.PERIOD_OF_SERVICE_ID
    AND paam.PRIMARY_FLAG = 'Y'
LEFT JOIN HR_ALL_ORGANIZATION_UNITS haou
    ON paam.ORGANIZATION_ID = haou.ORGANIZATION_ID
WHERE ppos.ACTUAL_TERMINATION_DATE >= ADD_MONTHS(TRUNC(SYSDATE), -12)
    AND ppos.ACTUAL_TERMINATION_DATE IS NOT NULL
GROUP BY TO_CHAR(ppos.ACTUAL_TERMINATION_DATE, 'YYYY-MM'), haou.NAME
ORDER BY TERM_MONTH DESC, DEPARTMENT

Watch out: LEAVING_REASON values vary by implementation. Check your lookup codes under LEAVING_REASON for your org's specific values.

Key tables: PER_PERIODS_OF_SERVICE — the only table with ACTUAL_TERMINATION_DATE

4. New Hires Report

Core HR Onboarding

Employees hired in the last 30 days with their assignment details. Modify the ADD_MONTHS parameter for different time ranges.

SELECT
    papf.PERSON_NUMBER,
    ppnf.FULL_NAME,
    ppos.DATE_START AS HIRE_DATE,
    haou.NAME AS DEPARTMENT,
    pjfv.NAME AS JOB_NAME,
    hapf.NAME AS POSITION_NAME,
    paam.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F papf
JOIN PER_PERSON_NAMES_F ppnf
    ON papf.PERSON_ID = ppnf.PERSON_ID
    AND ppnf.NAME_TYPE = 'GLOBAL'
    AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
JOIN PER_PERIODS_OF_SERVICE ppos
    ON papf.PERSON_ID = ppos.PERSON_ID
JOIN PER_ALL_ASSIGNMENTS_M paam
    ON papf.PERSON_ID = paam.PERSON_ID
    AND paam.PRIMARY_FLAG = 'Y'
    AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
LEFT JOIN HR_ALL_ORGANIZATION_UNITS haou ON paam.ORGANIZATION_ID = haou.ORGANIZATION_ID
LEFT JOIN PER_JOBS_F_VL pjfv ON paam.JOB_ID = pjfv.JOB_ID
    AND TRUNC(SYSDATE) BETWEEN pjfv.EFFECTIVE_START_DATE AND pjfv.EFFECTIVE_END_DATE
LEFT JOIN HR_ALL_POSITIONS_F_VL hapf ON paam.POSITION_ID = hapf.POSITION_ID
    AND TRUNC(SYSDATE) BETWEEN hapf.EFFECTIVE_START_DATE AND hapf.EFFECTIVE_END_DATE
WHERE ppos.DATE_START >= ADD_MONTHS(TRUNC(SYSDATE), -1)
    AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
ORDER BY ppos.DATE_START DESC

5. Employee Contact Directory

Core HR Contact Info

Work email, personal email, work phone, mobile, and home address for all employees. Uses LEFT JOINs since not every employee has all contact types.

SELECT
    papf.PERSON_NUMBER,
    ppnf.FIRST_NAME,
    ppnf.LAST_NAME,
    pea_work.EMAIL_ADDRESS AS WORK_EMAIL,
    pea_home.EMAIL_ADDRESS AS PERSONAL_EMAIL,
    pp_work.PHONE_NUMBER AS WORK_PHONE,
    pp_mobile.PHONE_NUMBER AS MOBILE_PHONE,
    pa.ADDRESS_LINE_1,
    pa.TOWN_OR_CITY,
    pa.REGION_2 AS STATE,
    pa.POSTAL_CODE
FROM PER_ALL_PEOPLE_F papf
JOIN PER_PERSON_NAMES_F ppnf
    ON papf.PERSON_ID = ppnf.PERSON_ID
    AND ppnf.NAME_TYPE = 'GLOBAL'
    AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
LEFT JOIN PER_EMAIL_ADDRESSES pea_work
    ON papf.PERSON_ID = pea_work.PERSON_ID AND pea_work.EMAIL_TYPE = 'W1'
LEFT JOIN PER_EMAIL_ADDRESSES pea_home
    ON papf.PERSON_ID = pea_home.PERSON_ID AND pea_home.EMAIL_TYPE = 'H1'
LEFT JOIN PER_PHONES pp_work
    ON papf.PERSON_ID = pp_work.PERSON_ID AND pp_work.PHONE_TYPE = 'W1'
LEFT JOIN PER_PHONES pp_mobile
    ON papf.PERSON_ID = pp_mobile.PERSON_ID AND pp_mobile.PHONE_TYPE = 'M'
LEFT JOIN PER_ADDRESSES_F pa
    ON papf.PERSON_ID = pa.PERSON_ID AND pa.ADDRESS_TYPE = 'HOME'
    AND TRUNC(SYSDATE) BETWEEN pa.EFFECTIVE_START_DATE AND pa.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE

Email/Phone types: W1 = Work primary, H1 = Home primary, M = Mobile. These are standard Oracle lookup codes in PHONE_TYPE and EMAIL_TYPE.

Key tables: PER_EMAIL_ADDRESSES, PER_PHONES, PER_ADDRESSES_F

6. Compensation / Salary Report

Compensation Restricted

Current salary for each employee including annual salary, currency, and salary basis. Requires compensation data access.

SELECT
    papf.PERSON_NUMBER,
    ppnf.FULL_NAME,
    haou.NAME AS DEPARTMENT,
    pjfv.NAME AS JOB_NAME,
    pgf.NAME AS GRADE_NAME,
    cs.SALARY_AMOUNT,
    cs.ANNUAL_SALARY,
    cs.CURRENCY_CODE,
    cs.SALARY_BASIS_NAME,
    cs.DATE_FROM AS SALARY_START_DATE
FROM PER_ALL_PEOPLE_F papf
JOIN PER_PERSON_NAMES_F ppnf
    ON papf.PERSON_ID = ppnf.PERSON_ID AND ppnf.NAME_TYPE = 'GLOBAL'
    AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_M paam
    ON papf.PERSON_ID = paam.PERSON_ID AND paam.PRIMARY_FLAG = 'Y'
    AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN CMP_SALARY cs
    ON paam.ASSIGNMENT_ID = cs.ASSIGNMENT_ID
    AND TRUNC(SYSDATE) BETWEEN cs.DATE_FROM AND NVL(cs.DATE_TO, TO_DATE('4712-12-31','YYYY-MM-DD'))
LEFT JOIN HR_ALL_ORGANIZATION_UNITS haou ON paam.ORGANIZATION_ID = haou.ORGANIZATION_ID
LEFT JOIN PER_JOBS_F_VL pjfv ON paam.JOB_ID = pjfv.JOB_ID
    AND TRUNC(SYSDATE) BETWEEN pjfv.EFFECTIVE_START_DATE AND pjfv.EFFECTIVE_END_DATE
LEFT JOIN PER_GRADES_F_VL pgf ON paam.GRADE_ID = pgf.GRADE_ID
    AND TRUNC(SYSDATE) BETWEEN pgf.EFFECTIVE_START_DATE AND pgf.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE

CMP_SALARY date handling: This table uses DATE_FROM / DATE_TO instead of the usual EFFECTIVE_START_DATE / EFFECTIVE_END_DATE. The NVL(DATE_TO, '4712-12-31') handles open-ended current salaries.

Key tables: CMP_SALARY — joins on ASSIGNMENT_ID, not PERSON_ID

7. Absence Balances

Absence

Current absence balances (accrued, used, remaining) by absence type and plan. Essential for PTO reporting.

SELECT
    papf.PERSON_NUMBER,
    ppnf.FULL_NAME,
    aatb.NAME AS ABSENCE_TYPE,
    apb.ACCRUED_BALANCE,
    apb.USED_BALANCE,
    (apb.ACCRUED_BALANCE - apb.USED_BALANCE) AS REMAINING_BALANCE,
    apb.PLAN_NAME
FROM PER_ALL_PEOPLE_F papf
JOIN PER_PERSON_NAMES_F ppnf
    ON papf.PERSON_ID = ppnf.PERSON_ID AND ppnf.NAME_TYPE = 'GLOBAL'
    AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
JOIN ANC_PER_ABS_PLAN_BAL apb
    ON papf.PERSON_ID = apb.PERSON_ID
JOIN ANC_ABSENCE_TYPES_B aatb
    ON apb.ABSENCE_TYPE_ID = aatb.ABSENCE_TYPE_ID
WHERE TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE

Key tables: ANC_PER_ABS_PLAN_BAL for balances, ANC_ABSENCE_TYPES_B for type names

SQL Best Practices for Oracle HCM

Always use date-effective filters on _F tables — TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
Use PRIMARY_FLAG = 'Y' on assignments to avoid duplicates from secondary assignments
Use EFFECTIVE_LATEST_CHANGE = 'Y' on PER_ALL_ASSIGNMENTS_M to get the latest version of each assignment
Join names with NAME_TYPE = 'GLOBAL' — otherwise you get separate rows for legal/display names
Use _VL views (like PER_JOBS_F_VL) instead of joining _F + _TL manually
Use LEFT JOIN for optional lookups (grade, position, location) — not all assignments have all fields populated
Prefer PER_ALL_ASSIGNMENTS_M over _F — Oracle deprecated _F in 24B and _M is faster for reporting

Search All 35,000+ Tables

Find columns, types, and schema for any Oracle Fusion Cloud table instantly.

Search Tables