PER Jobs and Positions – 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

Positions valid at the Effective Date with their job, organization, status, valid grades, FTE, maximum persons, headcount and incumbent FTE, plus the jobs that have no position valid at the Effective Date, for enterprises that use jobs only.

Headcount counts the active and suspended employee and contingent worker assignments of the position, or for a job without positions, the assignments of the job without a position. Incumbent FTE sums their FTE assignment budget values. Vacancies is the maximum persons of the position, one if blank, minus its headcount.

Detail Level Incumbents adds one row per incumbent assignment. Detail Level Extra Information lists one row per position or job extra information record, a position row also showing the extra information of its job. Select an Information Type to show its segments as columns.

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

Effective Date, Business Group, Organization, Job, Position, Record Type, Vacant Only, Detail Level, Information Type

Oracle EBS Tables Used

per_grades_vl, per_valid_grades, hr_all_positions_f_vl, hr_all_organization_units_vl, per_jobs_vl, per_shared_types_vl, hr_locations_all, hr_all_positions_f, per_assignment_budget_values_f, per_all_assignments_f, per_assignment_status_types

Report Categories

Enginatics

PER Person Assignment Upload, PER New Hires, PER Position Hierarchy, PER Employee Assignments, PER Employee Length of Service, 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 PER Jobs and Positions 15-Oct-2026 022629.xlsx
Blitz Report™ XML Import PER_Jobs_and_Positions.xml
SQL Source PER Jobs and Positions.sql
Full Report on Enginatics www.enginatics.com/reports/per-jobs-and-positions/

Case Study & Technical Analysis: PER Jobs and Positions

Executive Summary

This report shows the jobs and positions of an Oracle Human Resources business group as they stand on an effective date. For each position it gives the job, organization, location, status, entry grade and valid grades, FTE, maximum persons, headcount, incumbent FTE and vacancies. It also lists jobs that have no position valid on that date, for enterprises that use jobs without positions. HR planning and workforce management teams use it to see where vacancies exist, how full each position is, and which grades can be filled, and HR analysts use the extra information option to see the additional position and job data that the business group records. Blitz Report™ runs the report in Excel, where the headcount figures can be sorted and summed.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. The position record is selected for the effective date and joined to the business group, the job, the status and the organization.
  2. The valid grades of a position are aggregated into one text value, and grades longer than the limit are left out.
  3. An inline view counts the active assignments and sums their FTE by position key, which is the position identifier or the job identifier.
  4. The position and the assignment counts are joined on that key, so a position with no assignments still appears with zero headcount.
  5. Jobs with no position on the date are added with a UNION ALL, so every job appears either through a position or on its own.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How are vacancies calculated? A: Vacancies is the maximum persons of the position, or one if that is blank, minus its headcount. It is zero where the headcount is above the maximum.

Q: Which assignments count as headcount? A: Employee and contingent worker assignments with an active or suspended status are counted, as well as the job-level assignments of a job without a position.

© Enginatics GmbH