PER Headcount by Organization – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Human Resources · all Human Resources reports

Overview

Active employee and contingent worker headcount summarized by business group, organization, location, job, grade, and employment category. Includes gender breakdown and average service years and age. Use Excel pivot tables for flexible grouping and analysis.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Business Group, Organization, Location, Person Type, Effective Date

Oracle EBS Tables Used

hr_all_organization_units_vl, per_all_people_f, per_person_types_v, per_all_assignments_f, per_assignment_status_types, per_jobs_vl, per_grades_vl, hr_locations_all

Report Categories

Enginatics

PER Person Assignment Upload, PER New Hires, PER Employee Assignments, PER Employee Length of Service, PER Position Hierarchy, GL Account Distribution Analysis

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import PER_Headcount_by_Organization.xml
SQL Source PER Headcount by Organization.sql
Full Report on Enginatics www.enginatics.com/reports/per-headcount-by-organization/

Case Study & Technical Analysis: PER Headcount by Organization

Executive Summary

The PER Headcount by Organization report summarizes the active workforce in Oracle Human Resources by business group, organization, location, job, grade and employment category. It counts employees and contingent workers in each combination, with a male and female breakdown and the average length of service and average age. HR analysts, workforce planners and finance business partners use it to see how the headcount is distributed across the organization structure at a given date, and to prepare headcount and workforce reports for management. The report runs in Excel via Blitz Report, and the summary is built so that users can apply their own pivot tables for further grouping.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. PER_ALL_PEOPLE_F is joined to the business group organization through the business group identifier.
  2. PER_ALL_PEOPLE_F is joined to PER_ALL_ASSIGNMENTS_F on person identifier, and the person type is joined through PER_PERSON_TYPES_V.
  3. PER_ASSIGNMENT_STATUS_TYPES is joined to the assignment, and the filter keeps only the ACTIVE_ASSIGN system status.
  4. The assignment’s organization, job, grade and location are outer joined to HR_ALL_ORGANIZATION_UNITS_VL, PER_JOBS_VL, PER_GRADES_VL and HR_LOCATIONS_ALL.
  5. The output is grouped on the descriptive columns, and the employment category is translated through the EMP_CAT lookup.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Does the report count contingent workers? A: Yes. Assignments of type E (employee) and C (contingent worker) are included, and the person type column shows which kind each group is.

Q: How do I see headcount as of a past date? A: Enter the date in the Effective Date parameter. The person and assignment records valid on that date are used.

Q: How are the average service and age calculated? A: Service is measured from the original date of hire and age from the date of birth, each in years to one decimal place, relative to the effective date.

© Enginatics GmbH