Active employees by department
Headcount by business unit / department / location — quarter-end staffing snapshot.
Tables used
per_all_assignments_m per_all_people_f hr_all_organization_units hr_locations_all
Bind parameters
None — the statement runs as written; edit the literals in the WHERE clause to narrow it.
How to run it
Paste it into a BI Publisher data model as a SQL data set, or into QueryWell's Query Studio and run it against your pod with a reporting account. Results come back to a grid you can profile, chart and export with a SHA-256 hash. HCM data-source — point connection at ApplicationDB_HCM if available.
SELECT
org.name AS department_name,
loc.location_code,
COUNT(*) AS active_employees
FROM per_all_assignments_m asg
JOIN per_all_people_f ppl ON ppl.person_id = asg.person_id
AND TRUNC(SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
JOIN hr_all_organization_units org ON org.organization_id = asg.organization_id
LEFT JOIN hr_locations_all loc ON loc.location_id = asg.location_id
WHERE asg.assignment_status_type IN ('ACTIVE_ASSIGN','ACTIVE')
AND asg.assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
GROUP BY org.name, loc.location_code
ORDER BY active_employees DESCA single read-only SELECT against Oracle Fusion Cloud's supported reporting surface. Column names and views can differ by release and by the roles your reporting account holds, so validate the result against a report you already trust before relying on it.
hcmheadcount
More Human Capital queries
Run it without the template cycle
QueryWell runs this and 81 other curated scripts against your own Fusion pod from one signed desktop app — grid, charts, profiling and hashed exports included. Perpetual licence, 14-day trial.