Oracle Fusion HCM stores every critical workforce transaction inside Oracle Database structures - employees, assignments, departments, positions, compensation, absences, managers, organizational hierarchies, and more.
For HR analysts, BI developers, reporting teams, and Oracle Fusion consultants, understanding how to query this data with SQL is one of the most valuable Oracle Fusion skills.
In real-world Oracle Fusion environments, SQL is commonly used for:
- BI Publisher data model development
- OTBI validation and troubleshooting
- Workforce analytics
- Data reconciliation
- Ad-hoc HR reporting
- Cross-module analysis
- Migration validation
- Production issue investigation
The queries below are designed for practical Oracle Fusion reporting workflows and can be executed through BI Publisher-based SQL workflows and tools such as FusionLens SQL.
Before You Start: Understand Date-Effective Tables
Oracle Fusion HCM relies heavily on date-effective tables. Tables ending with _F or _M contain historical versions of records. This means employees, assignments, jobs, departments, locations, and grades all maintain historical timelines.
The most common Oracle Fusion SQL mistake is forgetting effective-date filters. Without this filter, queries often return duplicate rows, historical records, and inflated headcounts.
Always filter date-effective tables with:
TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
Core Oracle Fusion HCM Tables
| Table | Purpose |
|---|---|
| PER_ALL_PEOPLE_F | Worker master data |
| PER_PERSON_NAMES_F | Employee names |
| PER_ALL_ASSIGNMENTS_M | Assignments |
| PER_PERIODS_OF_SERVICE | Employment periods (hire and termination dates) |
| PER_JOBS_F | Jobs |
| HR_ALL_ORGANIZATION_UNITS_F | Departments |
| HR_LOCATIONS_ALL_F | Locations |
| PER_GRADES_F | Grades |
| PER_POSITIONS_F | Positions |
| CMP_SALARY | Salary information |
| PER_ABSENCE_ENTRIES_F | Absence records |
1. Workforce Visibility Queries
Query 1 - Full Active Employee Directory
The foundation of most HCM reports. Returns active employees with department, job, location, grade, and hire date.
SELECT
p.person_number,
pn.full_name,
j.name AS job_title,
ou.name AS department,
l.location_name AS work_location,
g.name AS grade,
pos.date_start AS hire_date
FROM
per_all_people_f p
JOIN per_person_names_f pn ON pn.person_id = p.person_id
JOIN per_all_assignments_m a ON a.person_id = p.person_id
JOIN per_jobs_f j ON j.job_id = a.job_id
JOIN hr_all_organization_units_f ou
ON ou.organization_id = a.organization_id
JOIN hr_locations_all_f l
ON l.location_id = a.location_id
LEFT JOIN per_grades_f g
ON g.grade_id = a.grade_id
JOIN per_periods_of_service pos
ON pos.person_id = p.person_id
WHERE
TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date
AND pn.effective_end_date
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
AND pos.actual_termination_date IS NULL
AND a.assignment_type = 'E'
AND a.primary_flag = 'Y'
ORDER BY
ou.name,
pn.full_name;
Note: a.primary_flag = 'Y' ensures only the primary assignment is returned per employee. Use LEFT JOIN for grade to avoid losing employees without a grade assigned.
Query 2 - Headcount by Department
Returns active headcount grouped by department, ordered largest first.
SELECT
ou.name AS department,
COUNT(*) AS headcount
FROM
per_all_people_f p
JOIN per_all_assignments_m a
ON a.person_id = p.person_id
JOIN hr_all_organization_units_f ou
ON ou.organization_id = a.organization_id
WHERE
TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
AND a.assignment_type = 'E'
AND a.primary_flag = 'Y'
GROUP BY
ou.name
ORDER BY
headcount DESC;
Query 3 - Headcount by Location
Useful for workforce distribution reporting across office locations.
SELECT
l.location_name,
COUNT(*) AS employee_count
FROM
per_all_assignments_m a
JOIN hr_locations_all_f l
ON l.location_id = a.location_id
WHERE
TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
AND a.assignment_type = 'E'
AND a.primary_flag = 'Y'
GROUP BY
l.location_name
ORDER BY
employee_count DESC;
Query 4 - Employees by Legal Employer
Counts active workers per legal employer - useful for multi-entity organizations.
SELECT
legal_employer_name,
COUNT(*) AS total_workers
FROM
per_all_assignments_m
WHERE
TRUNC(SYSDATE)
BETWEEN effective_start_date AND effective_end_date
GROUP BY
legal_employer_name
ORDER BY
total_workers DESC;
2. Hiring & Attrition Queries
Query 5 - New Hires in a Date Range
Returns employees hired between two dates. Replace :start_date and :end_date with your reporting period.
SELECT
p.person_number,
pn.full_name,
pos.date_start AS hire_date
FROM
per_periods_of_service pos
JOIN per_all_people_f p
ON p.person_id = pos.person_id
JOIN per_person_names_f pn
ON pn.person_id = p.person_id
WHERE
pos.date_start BETWEEN :start_date AND :end_date
ORDER BY
pos.date_start DESC;
Query 6 - Terminations in a Period
Returns all terminated employees with their termination date and leaving reason.
SELECT
p.person_number,
pn.full_name,
pos.actual_termination_date,
pos.leaving_reason
FROM
per_periods_of_service pos
JOIN per_all_people_f p
ON p.person_id = pos.person_id
JOIN per_person_names_f pn
ON pn.person_id = p.person_id
WHERE
pos.actual_termination_date
BETWEEN :start_date AND :end_date
ORDER BY
pos.actual_termination_date DESC;
Query 7 - Attrition by Department
Counts terminations per department within a date range - useful for attrition trend analysis.
SELECT
ou.name AS department,
COUNT(*) AS terminated_count
FROM
per_periods_of_service pos
JOIN per_all_assignments_m a
ON a.person_id = pos.person_id
JOIN hr_all_organization_units_f ou
ON ou.organization_id = a.organization_id
WHERE
pos.actual_termination_date
BETWEEN :start_date AND :end_date
GROUP BY
ou.name
ORDER BY
terminated_count DESC;
Query 8 - Internal Transfers
Returns assignment records where the action code is TRANSFER, showing internal movement history.
SELECT
p.person_number,
pn.full_name,
a.organization_id,
a.effective_start_date
FROM
per_all_assignments_m a
JOIN per_all_people_f p
ON p.person_id = a.person_id
JOIN per_person_names_f pn
ON pn.person_id = p.person_id
WHERE
a.action_code = 'TRANSFER'
ORDER BY
a.effective_start_date DESC;
Note: Add an effective-date filter if you want only current-period transfers rather than full assignment history.
3. Compensation & Salary Queries
Query 9 - Current Salary Report
Returns active salary records with salary amount, annual salary, and frequency per employee.
SELECT
p.person_number,
pn.full_name,
cs.salary_amount,
cs.annual_salary,
cs.frequency
FROM
per_all_people_f p
JOIN per_person_names_f pn
ON pn.person_id = p.person_id
JOIN per_all_assignments_m a
ON a.person_id = p.person_id
JOIN cmp_salary cs
ON cs.assignment_id = a.assignment_id
WHERE
TRUNC(SYSDATE)
BETWEEN cs.effective_start_date
AND cs.effective_end_date;
Note: CMP_SALARY is date-effective - always apply the effective-date filter to avoid returning historical salary rows.
Query 10 - Highest Paid Employees
Returns employees ordered by annual salary descending.
SELECT
pn.full_name,
cs.annual_salary
FROM
cmp_salary cs
JOIN per_all_assignments_m a
ON a.assignment_id = cs.assignment_id
JOIN per_person_names_f pn
ON pn.person_id = a.person_id
WHERE
TRUNC(SYSDATE)
BETWEEN cs.effective_start_date
AND cs.effective_end_date
ORDER BY
cs.annual_salary DESC;
Query 11 - Average Salary by Grade
Aggregates average annual salary per grade - useful for compensation benchmarking and equity analysis.
SELECT
g.name AS grade,
AVG(cs.annual_salary) AS avg_salary
FROM
cmp_salary cs
JOIN per_all_assignments_m a
ON a.assignment_id = cs.assignment_id
JOIN per_grades_f g
ON g.grade_id = a.grade_id
GROUP BY
g.name
ORDER BY
avg_salary DESC;
Query 12 - Salary Changes in Last 12 Months
Returns all salary records with an effective start date within the last 12 months - useful for compensation review and audit reporting.
SELECT
assignment_id,
salary_amount,
effective_start_date
FROM
cmp_salary
WHERE
effective_start_date >= ADD_MONTHS(SYSDATE, -12)
ORDER BY
effective_start_date DESC;
4. Organization & Managerial Structure
Query 13 - Manager and Direct Reports
Self-join on PER_PERSON_NAMES_F to resolve both manager and employee names from a single assignment row.
SELECT
mgr.full_name AS manager_name,
emp.full_name AS employee_name
FROM
per_all_assignments_m a
JOIN per_person_names_f emp
ON emp.person_id = a.person_id
JOIN per_person_names_f mgr
ON mgr.person_id = a.manager_id
WHERE
a.manager_id IS NOT NULL;
Query 14 - Employees Without Managers
Identifies assignments where no manager is linked - useful for org structure validation and data quality checks.
SELECT
pn.full_name
FROM
per_all_assignments_m a
JOIN per_person_names_f pn
ON pn.person_id = a.person_id
WHERE
a.manager_id IS NULL;
Query 15 - Vacant Open Positions
Uses NOT EXISTS to find positions in PER_POSITIONS_F with no current assignment linked.
SELECT
pos.position_code,
pos.name
FROM
per_positions_f pos
WHERE
NOT EXISTS (
SELECT 1
FROM per_all_assignments_m a
WHERE a.position_id = pos.position_id
);
Query 16 - Manager Span of Control
Counts direct reports per manager - useful for org design and management span analysis.
SELECT
mgr.full_name AS manager_name,
COUNT(*) AS direct_reports
FROM
per_all_assignments_m a
JOIN per_person_names_f mgr
ON mgr.person_id = a.manager_id
GROUP BY
mgr.full_name
ORDER BY
direct_reports DESC;
5. Absence & Workforce Planning Queries
Query 17 - Absence Records in a Period
Returns absence entries with employee name, start/end dates, and duration.
SELECT
pn.full_name,
ae.start_date,
ae.end_date,
ae.duration
FROM
per_absence_entries_f ae
JOIN per_person_names_f pn
ON pn.person_id = ae.person_id
WHERE
ae.start_date
BETWEEN :start_date AND :end_date;
Query 18 - Employee Tenure Bands
Calculates years of service per active employee, ordered from longest tenured first.
SELECT
pn.full_name,
ROUND(
MONTHS_BETWEEN(SYSDATE, pos.date_start) / 12,
1
) AS tenure_years
FROM
per_periods_of_service pos
JOIN per_person_names_f pn
ON pn.person_id = pos.person_id
WHERE
pos.actual_termination_date IS NULL
ORDER BY
tenure_years DESC;
Query 19 - Upcoming Work Anniversaries
Finds employees with a hire date anniversary in the current calendar month.
SELECT
pn.full_name,
pos.date_start
FROM
per_periods_of_service pos
JOIN per_person_names_f pn
ON pn.person_id = pos.person_id
WHERE
TO_CHAR(pos.date_start, 'MM')
= TO_CHAR(SYSDATE, 'MM');
Query 20 - Retirement Eligibility
Returns active employees aged 60 or above - useful for workforce planning and succession analysis.
SELECT
pn.full_name,
p.date_of_birth,
ROUND(
MONTHS_BETWEEN(SYSDATE, p.date_of_birth) / 12,
1
) AS age
FROM
per_all_people_f p
JOIN per_person_names_f pn
ON pn.person_id = p.person_id
WHERE
ROUND(
MONTHS_BETWEEN(SYSDATE, p.date_of_birth) / 12,
0
) >= 60
ORDER BY
age DESC;
Note: PER_ALL_PEOPLE_F is date-effective - add an effective-date filter if you need to restrict to active worker records only.
Common Oracle Fusion HCM SQL Mistakes
Missing Effective-Date Filters
This is the #1 cause of duplicate rows in Fusion HCM reporting. Always filter people, assignments, jobs, departments, grades, and locations with effective-date conditions. A single missed filter on a joined table silently multiplies result rows.
Ignoring Primary Assignments
Many workers have multiple assignments - temporary, contingent, or secondary roles. Without a.primary_flag = 'Y', headcount queries will count the same person multiple times.
Using INNER JOIN Instead of LEFT JOIN
Optional entities such as grades, managers, and locations can accidentally remove employees from results if INNER JOIN is used incorrectly. Use LEFT JOIN for attributes that may not exist for all workers.
Forgetting Business Unit Filters
Tables ending with _ALL often contain multi-org data. Without organization or business unit filters, reports may silently return data from multiple business units.
Run These Queries Faster with FusionLens SQL
FusionLens SQL connects directly to your Oracle Fusion Cloud environment. Browse the HCM schema, discover the right join paths for date-effective tables, and test BI Publisher SQL without hunting through documentation.
Browse HCM tables like PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, and CMP_SALARY with column descriptions and data types.
Save and revisit HCM queries across sessions - reuse working effective-date filter patterns without rewriting from scratch.
Queries can be used directly in BI Publisher data models for formatted HCM reports and scheduled outputs.
Final Thoughts
Oracle Fusion HCM reporting becomes dramatically more powerful once teams move beyond drag-and-drop reporting and understand the underlying SQL structures.
OTBI and BI Publisher remain essential parts of the Oracle Fusion ecosystem - but SQL enables advanced analytics, faster troubleshooting, flexible reporting, cross-functional analysis, and scalable BI workflows.
The most effective Oracle Fusion teams combine all three approaches together. And that workflow becomes significantly faster with SQL tooling that connects directly to Oracle Fusion and knows the schema.