Oracle Fusion HCM SQL: 20 Real Queries Every HR & BI Team Should Know

Oracle Fusion HCM SQL becomes reliable only when the query matches the HCM business grain. A person can have multiple work relationships, a work relationship can have multiple assignments, an assignment can change more than once on the same effective date, and translated names such as job, grade, position, and location names often live in language-aware views rather than the base table.

The 20 queries below are designed for BI Publisher-style physical SQL and similar Fusion reporting workflows. They use the documented Oracle HCM relationship paths and distinguish current snapshots from historical events. Replace the example bind variables with parameters from your own data model.

Two rules before using these queries

First: PER_ALL_ASSIGNMENTS_M allows multiple changes on the same day. For a current or as-of final-state snapshot, use EFFECTIVE_LATEST_CHANGE='Y' in addition to the effective-date predicate.
Second: PRIMARY_FLAG='Y' is a business choice. It is correct when the report specifically asks for the primary employee assignment; it is not a universal duplicate-removal filter.

Core HCM Objects Used in This Guide

Object	Use in this guide
`PER_ALL_PEOPLE_F`	Person number and date-effective person row
`PER_PERSON_NAMES_F`	Global/display name; always date-filter the name row
`PER_ALL_ASSIGNMENTS_M`	Assignment grain; current snapshots require effective date + latest change
`PER_PERIODS_OF_SERVICE`	Work relationship start/termination; join from assignment by PERIOD_OF_SERVICE_ID
`PER_ASSIGNMENT_SUPERVISORS_F`	Manager relationship; manager ID is not taken from the assignment table
`PER_JOBS_F_VL`	Language-aware job name
`PER_GRADES_F_VL`	Language-aware grade name
`HR_LOCATIONS_ALL_F_VL`	Language-aware location name
`HR_ALL_POSITIONS_F_VL`	Position code/name and position attributes
`CMP_SALARY`	Salary rows; use DATE_FROM / DATE_TO, not effective_start/end
`ANC_PER_ABS_ENTRIES`	Person absence entries

BI Publisher security matters

Physical SQL against HCM base tables isn't automatically restricted by the user's Fusion data-security profile. Oracle provides secured list views such as PER_PERSON_SECURED_LIST_V, PER_ASSIGNMENT_SECURED_LIST_V, PER_POSITION_SECURED_LIST_V, and CMP_SALARY_SECURED_LIST_V. Use the secured variants when the report must enforce the runner's HCM security scope. Salary and date-of-birth reporting also deserve explicit privacy review.

1. Workforce Visibility Queries

Query 1 — Current Primary Employee Directory

Returns the current primary employee assignment with job, department, location, grade, and work-relationship start date.

SELECT p.person_number,
       pn.display_name,
       a.assignment_number,
       j.name                         AS job_name,
       ou.name                        AS department_name,
       l.location_name,
       g.name                         AS grade_name,
       pos.date_start                 AS work_relationship_start
FROM   per_all_people_f p
JOIN   per_person_names_f pn
       ON pn.person_id = p.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
JOIN   per_all_assignments_m a
       ON a.person_id = p.person_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN per_jobs_f_vl j
       ON j.job_id = a.job_id
      AND TRUNC(SYSDATE) BETWEEN j.effective_start_date AND j.effective_end_date
LEFT JOIN hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.organization_id
      AND TRUNC(SYSDATE) BETWEEN ou.effective_start_date AND ou.effective_end_date
LEFT JOIN hr_locations_all_f_vl l
       ON l.location_id = a.location_id
      AND TRUNC(SYSDATE) BETWEEN l.effective_start_date AND l.effective_end_date
LEFT JOIN per_grades_f_vl g
       ON g.grade_id = a.grade_id
      AND TRUNC(SYSDATE) BETWEEN g.effective_start_date AND g.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
ORDER BY ou.name,
         pn.display_name

The displayed start date is the start of this work relationship. It should not automatically be labeled the person's enterprise original hire date.

Query 2 — Current Headcount by Department

SELECT ou.name AS department_name,
       COUNT(DISTINCT a.person_id) AS headcount
FROM   per_all_assignments_m a
LEFT JOIN hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.organization_id
      AND TRUNC(SYSDATE) BETWEEN ou.effective_start_date AND ou.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
GROUP BY ou.name
ORDER BY headcount DESC

Query 3 — Current Headcount by Location

SELECT l.location_name,
       COUNT(DISTINCT a.person_id) AS headcount
FROM   per_all_assignments_m a
LEFT JOIN hr_locations_all_f_vl l
       ON l.location_id = a.location_id
      AND TRUNC(SYSDATE) BETWEEN l.effective_start_date AND l.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
GROUP BY l.location_name
ORDER BY headcount DESC

Query 4 — Current Employees by Legal Employer

SELECT ou.name AS legal_employer,
       COUNT(DISTINCT a.person_id) AS employee_count
FROM   per_all_assignments_m a
JOIN   hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.legal_entity_id
      AND TRUNC(SYSDATE) BETWEEN ou.effective_start_date AND ou.effective_end_date
JOIN   hr_org_unit_classifications_f oc
       ON oc.organization_id = ou.organization_id
      AND oc.classification_code = 'HCM_LEMP'
      AND TRUNC(SYSDATE) BETWEEN oc.effective_start_date AND oc.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
GROUP BY ou.name
ORDER BY employee_count DESC

The legal-employer name is resolved through the assignment's LEGAL_ENTITY_ID joined to the organization classified as HCM_LEMP (Legal Employer) in HR_ORG_UNIT_CLASSIFICATIONS_F — not a denormalized column on PER_ALL_ASSIGNMENTS_M, and not the cross-module Legal Entity Configurator name from XLE_ENTITY_PROFILES, which is a related but distinct concept.

2. Hiring, Termination, and Movement

Query 5 — Work Relationships Started in a Date Range

Useful for hire/rehire event reporting. DATE_START is the work-relationship start date, so this query should not be described as "first-ever enterprise hire" without additional logic.

SELECT p.person_number,
       pn.display_name,
       pos.period_of_service_id,
       pos.date_start AS work_relationship_start
FROM   per_periods_of_service pos
JOIN   per_all_people_f p
       ON p.person_id = pos.person_id
      AND pos.date_start BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = pos.person_id
      AND pn.name_type = 'GLOBAL'
      AND pos.date_start BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  pos.date_start >= TRUNC(:p_start_date)
AND    pos.date_start <  TRUNC(:p_end_date) + 1
ORDER BY pos.date_start DESC,
         p.person_number

Query 6 — Work Relationships Terminated in a Period

SELECT p.person_number,
       pn.display_name,
       pos.period_of_service_id,
       pos.actual_termination_date
FROM   per_periods_of_service pos
JOIN   per_all_people_f p
       ON p.person_id = pos.person_id
      AND pos.actual_termination_date
          BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = pos.person_id
      AND pn.name_type = 'GLOBAL'
      AND pos.actual_termination_date
          BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  pos.actual_termination_date >= TRUNC(:p_start_date)
AND    pos.actual_termination_date <  TRUNC(:p_end_date) + 1
ORDER BY pos.actual_termination_date DESC

Query 7 — Terminations by Department at Termination Date

This is an event-grain query: the assignment and department are resolved as of the actual termination date, not as of today.

SELECT ou.name AS department_name,
       COUNT(DISTINCT pos.period_of_service_id) AS terminated_work_relationships
FROM   per_periods_of_service pos
JOIN   per_all_assignments_m a
       ON a.period_of_service_id = pos.period_of_service_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND pos.actual_termination_date
          BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN hr_all_organization_units_f_vl ou
       ON ou.organization_id = a.organization_id
      AND pos.actual_termination_date
          BETWEEN ou.effective_start_date AND ou.effective_end_date
WHERE  pos.actual_termination_date >= TRUNC(:p_start_date)
AND    pos.actual_termination_date <  TRUNC(:p_end_date) + 1
GROUP BY ou.name
ORDER BY terminated_work_relationships DESC

Query 8 — Primary Employee Transfer Actions in a Date Range

SELECT p.person_number,
       pn.display_name,
       a.assignment_number,
       a.organization_id,
       a.action_code,
       a.effective_start_date
FROM   per_all_assignments_m a
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND a.effective_start_date BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND a.effective_start_date BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    a.action_code = 'TRANSFER'
AND    a.effective_start_date >= TRUNC(:p_start_date)
AND    a.effective_start_date <  TRUNC(:p_end_date) + 1
ORDER BY a.effective_start_date DESC,
         p.person_number

Use the action code that corresponds to the movement you need to analyze. Local transfer, global transfer, promotion, and other actions are different business events.

3. Compensation and Salary

Salary table correction: CMP_SALARY uses DATE_FROM and DATE_TO. The original pattern using EFFECTIVE_START_DATE/EFFECTIVE_END_DATE does not match the documented table definition.

Query 9 — Current Approved Salary Report

SELECT p.person_number,
       pn.display_name,
       a.assignment_number,
       cs.salary_amount,
       cs.annual_salary,
       cs.currency_code,
       cs.payroll_frequency_code
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
ORDER BY pn.display_name

Query 10 — Highest Current Annual Salaries

SELECT pn.display_name,
       a.assignment_number,
       cs.annual_salary,
       cs.currency_code
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
ORDER BY cs.annual_salary DESC NULLS LAST
FETCH FIRST 100 ROWS ONLY

Do not compare or average salaries across currencies unless you first normalize currency explicitly.

Query 11 — Average Current Annual Salary by Grade and Currency

SELECT g.name AS grade_name,
       cs.currency_code,
       COUNT(*) AS salary_rows,
       AVG(cs.annual_salary) AS average_annual_salary
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN per_grades_f_vl g
       ON g.grade_id = a.grade_id
      AND TRUNC(SYSDATE) BETWEEN g.effective_start_date AND g.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
GROUP BY g.name,
         cs.currency_code
ORDER BY g.name,
         cs.currency_code

Query 12 — Approved Salary Rows Starting in the Last 12 Months

SELECT cs.assignment_id,
       p.person_number,
       pn.display_name,
       cs.date_from,
       cs.salary_amount,
       cs.annual_salary,
       cs.currency_code,
       cs.salary_reason_code
FROM   cmp_salary cs
JOIN   per_all_assignments_m a
       ON a.assignment_id = cs.assignment_id
      AND a.assignment_type = 'E'
      AND a.effective_latest_change = 'Y'
      AND cs.date_from BETWEEN a.effective_start_date AND a.effective_end_date
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND cs.date_from BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND cs.date_from BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  cs.salary_approved = 'Y'
AND    cs.date_from >= ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER BY cs.date_from DESC,
         p.person_number

4. Organization and Managerial Structure

Query 13 — Current Primary Manager and Direct Reports

SELECT mgr.display_name AS manager_name,
       emp.display_name AS employee_name,
       a.assignment_number
FROM   per_all_assignments_m a
JOIN   per_assignment_supervisors_f sup
       ON sup.assignment_id = a.assignment_id
      AND sup.primary_flag = 'Y'
      AND TRUNC(SYSDATE) BETWEEN sup.effective_start_date AND sup.effective_end_date
JOIN   per_person_names_f emp
       ON emp.person_id = a.person_id
      AND emp.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN emp.effective_start_date AND emp.effective_end_date
JOIN   per_person_names_f mgr
       ON mgr.person_id = sup.manager_id
      AND mgr.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN mgr.effective_start_date AND mgr.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
ORDER BY mgr.display_name,
         emp.display_name

Manager relationships are modeled in PER_ASSIGNMENT_SUPERVISORS_F. The original pattern that referenced A.MANAGER_ID from PER_ALL_ASSIGNMENTS_M used the wrong object.

Query 14 — Current Primary Employees Without a Primary Manager

SELECT p.person_number,
       pn.display_name,
       a.assignment_number
FROM   per_all_assignments_m a
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    NOT EXISTS (
           SELECT 1
           FROM per_assignment_supervisors_f sup
           WHERE sup.assignment_id = a.assignment_id
           AND   sup.primary_flag = 'Y'
           AND   TRUNC(SYSDATE)
                 BETWEEN sup.effective_start_date AND sup.effective_end_date
       )
ORDER BY pn.display_name

Query 15 — Positions With No Current Employee Assignment

This finds positions with zero current employee assignments. It does not claim that the position is an approved recruiting vacancy; position hiring status, headcount capacity, requisitions, and position-control rules are separate concepts.

SELECT pos.position_code,
       pos.name,
       pos.hiring_status,
       pos.max_persons
FROM   hr_all_positions_f_vl pos
WHERE  TRUNC(SYSDATE) BETWEEN pos.effective_start_date AND pos.effective_end_date
AND    NOT EXISTS (
           SELECT 1
           FROM per_all_assignments_m a
           WHERE a.position_id = pos.position_id
           AND   a.assignment_type = 'E'
           AND   a.effective_latest_change = 'Y'
           AND   TRUNC(SYSDATE)
                 BETWEEN a.effective_start_date AND a.effective_end_date
       )
ORDER BY pos.position_code

Query 16 — Current Manager Span of Control

SELECT mgr.display_name AS manager_name,
       COUNT(DISTINCT a.person_id) AS direct_reports
FROM   per_all_assignments_m a
JOIN   per_assignment_supervisors_f sup
       ON sup.assignment_id = a.assignment_id
      AND sup.primary_flag = 'Y'
      AND TRUNC(SYSDATE) BETWEEN sup.effective_start_date AND sup.effective_end_date
JOIN   per_person_names_f mgr
       ON mgr.person_id = sup.manager_id
      AND mgr.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN mgr.effective_start_date AND mgr.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
GROUP BY mgr.person_id,
         mgr.display_name
ORDER BY direct_reports DESC,
         mgr.display_name

5. Absence and Workforce Planning

Query 17 — Absence Entries Overlapping a Reporting Period

SELECT p.person_number,
       pn.display_name,
       ae.per_absence_entry_id,
       ae.start_date,
       ae.end_date,
       ae.duration,
       ae.uom,
       ae.absence_status_cd,
       ae.approval_status_cd
FROM   anc_per_abs_entries ae
JOIN   per_all_people_f p
       ON p.person_id = ae.person_id
      AND NVL(ae.start_date, TRUNC(:p_start_date))
          BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = ae.person_id
      AND pn.name_type = 'GLOBAL'
      AND NVL(ae.start_date, TRUNC(:p_start_date))
          BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  ae.start_date <= TRUNC(:p_end_date)
AND    NVL(ae.end_date, ae.start_date) >= TRUNC(:p_start_date)
ORDER BY ae.start_date,
         p.person_number

The current Fusion Absence Management object is ANC_PER_ABS_ENTRIES. The period predicate is an overlap test, so an absence that starts before the report period but continues into it is still returned.

Query 18 — Current Work-Relationship Service Duration

SELECT p.person_number,
       pn.display_name,
       pos.date_start AS work_relationship_start,
       ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), pos.date_start) / 12, 1)
           AS relationship_years
FROM   per_all_assignments_m a
JOIN   per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
ORDER BY relationship_years DESC

This is duration from the current work relationship's DATE_START. Oracle HCM also supports adjusted service/original hire concepts, so do not use this expression as a universal seniority formula without defining the business rule.

Query 19 — Current Work-Relationship Anniversaries This Month

SELECT p.person_number,
       pn.display_name,
       pos.date_start AS work_relationship_start
FROM   per_all_assignments_m a
JOIN   per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
JOIN   per_all_people_f p
       ON p.person_id = a.person_id
      AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    EXTRACT(MONTH FROM pos.date_start) = EXTRACT(MONTH FROM SYSDATE)
AND    pos.date_start < TRUNC(SYSDATE, 'YYYY')
AND    (pos.actual_termination_date IS NULL
        OR pos.actual_termination_date >= TRUNC(SYSDATE))
ORDER BY EXTRACT(DAY FROM pos.date_start),
         pn.display_name

Query 20 — Employees Age 60+ (Illustrative Workforce Planning)

This is not a retirement-eligibility query. Retirement eligibility is legislation-, plan-, policy-, and employee-specific. This example simply selects current primary employees whose date of birth indicates an age of at least 60.

SELECT a.person_id,
       pn.display_name,
       pp.date_of_birth,
       TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE), pp.date_of_birth) / 12) AS age_years
FROM   per_assignment_secured_list_v a
JOIN   per_pub_pers_secured_list_v pp
       ON pp.person_id = a.person_id
JOIN   per_person_names_f pn
       ON pn.person_id = a.person_id
      AND pn.name_type = 'GLOBAL'
      AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
WHERE  a.assignment_type = 'E'
AND    a.primary_flag = 'Y'
AND    a.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND    pp.date_of_birth IS NOT NULL
AND    pp.date_of_birth <= ADD_MONTHS(TRUNC(SYSDATE), -60 * 12)
ORDER BY age_years DESC,
         pn.display_name

What Was Wrong With the Original HCM Patterns?

Original pattern	Problem	Corrected approach
`PER_ALL_ASSIGNMENTS_M.MANAGER_ID`	Manager relationship is not modeled there.	Use `PER_ASSIGNMENT_SUPERVISORS_F`.
`PER_POSITIONS_F`	Wrong position object for current Fusion HCM metadata.	Use `HR_ALL_POSITIONS_F` / `HR_ALL_POSITIONS_F_VL`.
`PER_ABSENCE_ENTRIES_F`	Wrong Absence Management object.	Use `ANC_PER_ABS_ENTRIES`.
`CMP_SALARY.EFFECTIVE_START_DATE`	Those are not the salary period columns.	Use `DATE_FROM` / `DATE_TO`.
`PER_JOBS_F.NAME`, `PER_GRADES_F.NAME`	Display names are translation-layer attributes.	Use the language-aware `_VL` views.
Work relationship joined only by PERSON_ID	A person may have multiple work relationships.	Join assignment → `PERIOD_OF_SERVICE_ID`.
Current PAAM row = effective-date predicate only	Multiple same-day changes can still exist.	Add `EFFECTIVE_LATEST_CHANGE='Y'`.
"Retirement eligibility = age ≥ 60"	Business/legal conclusion not supported by age alone.	Label as an age cohort unless real eligibility rules are implemented.
Legal employer via `XLE_ENTITY_PROFILES`	Resolves the cross-module Legal Entity name, not necessarily the HCM Legal Employer.	Join `HR_ALL_ORGANIZATION_UNITS_F` classified `HCM_LEMP` via `HR_ORG_UNIT_CLASSIFICATIONS_F`.

Practical HCM SQL Validation Checklist

Define whether one output row means person, work relationship, assignment, salary row, absence entry, or position.
For PER_ALL_ASSIGNMENTS_M current/as-of snapshots, apply the effective date and EFFECTIVE_LATEST_CHANGE='Y'.
Use PRIMARY_FLAG='Y' only when the report specifically needs the primary assignment.
Join PER_PERIODS_OF_SERVICE through PERIOD_OF_SERVICE_ID, not PERSON_ID alone.
Use language-aware views for job, grade, position, location, and similar translated names.
For historical event reports, date-filter related dimensions as of the event date—not automatically as of today.
Do not aggregate salary across currencies unless currency conversion is explicitly defined.
For BI Publisher reports that must honor HCM security profiles, use Oracle secured list views where documented.

Run and Validate HCM SQL with FusionLens SQL

Browse HCM tables and columns, inspect relationship metadata, write effective-date-aware SQL, and run BI Publisher-style physical SQL against your Oracle Fusion environment.

HCM Schema Navigator

Inspect tables, columns, descriptions, and relationship keys before writing a join.

SQL History

Keep working HCM query patterns and revisit them across sessions.

Live SQL Validation

Test row counts, date logic, and joins before moving the query into a production report.

Try FusionLens SQL Free

Final Thoughts

The biggest Oracle Fusion HCM SQL mistakes are usually semantic rather than syntactic. A query can compile and still be wrong because it uses the wrong work relationship, counts historical assignment versions, resolves a manager from the wrong object, treats a salary row like a normal _F table, or interprets an age threshold as a legal eligibility rule.

Treat person, work relationship, assignment, supervisor, salary, absence, and position as separate grains. Once that model is explicit, HCM SQL becomes much easier to validate.

For date-effective mechanics, see the Effective Date Handling guide. For relationship design, see Oracle Fusion Table Relationships Explained.