PER Employee Assignments – 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

Master data report showing the employee demographic information including name, gender SSN, birthdate, age, nationality, effective start date, and additional profile information.

Detail Level Costing adds one row per assignment cost allocation valid at the Effective Date, with the proportion, the total proportion of the assignment and the cost allocation flexfield. The individual cost segment columns are shown when a Business Group is selected. Assignments without assignment-level costing show blank costing columns.

Detail Level Extra Information lists one row per assignment extra information record. 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

Person Full Name, User Name, Person Type, Assignment Organization, Business Group, Effective Date, Flexfield Context, Detail Level, Information Type, Costing Total Not 100%

Oracle EBS Tables Used

fnd_user, per_phones, per_all_people_f, per_pay_bases, hr_all_organization_units_vl, per_person_types_v, per_all_assignments_f, per_assignment_status_types_v, per_jobs_vl, per_all_positions, hr_all_positions_f_tl, per_grades_vl, hr_locations_all, gl_ledgers, per_addresses, fnd_territories_vl, pay_all_payrolls_f

Report Categories

Enginatics

Related Reports

PER Person Assignment Upload, GL Account Distribution Analysis, PAY Hire to Retire Tracker, PER New Hires, GL Account Analysis, PER Employee Length of Service, GL Account Analysis (Drilldown) (with inventory and WIP)

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 Employee Assignments 03-Aug-2020 171732.xlsx
Blitz Report™ XML Import	PER_Employee_Assignments.xml
SQL Source	PER Employee Assignments.sql
Full Report on Enginatics	www.enginatics.com/reports/per-employee-assignments/

Case Study & Technical Analysis: PER Employee Assignments Report

Executive Summary

The PER Employee Assignments report is a comprehensive master data report that provides a detailed, date-effective view of employee demographic and assignment information within Oracle Human Resources (HR). It consolidates critical employee profile data, including name, gender, birthdate, and nationality, alongside key assignment details such as organization, job, position, grade, location, and payroll. This report is an indispensable tool for HR professionals, payroll administrators, and managers for workforce analysis, compliance reporting, and maintaining accurate employee records.

Business Challenge

Managing accurate and up-to-date employee information across a dynamic organization is a significant operational challenge. Human Resources and Payroll teams often struggle with:

• Fragmented Employee Data: Core employee demographic data and their various assignment details (job, department, location, pay, etc.) are spread across multiple forms and tables in Oracle HR, making it difficult to get a single, holistic view.
• Historical Data Challenges: Employee assignments change frequently due to promotions, transfers, or reorganizations. Retrieving accurate historical assignment information for a specific date (e.g., for an audit or a past event) is cumbersome with standard tools.
• Compliance and Reporting: Generating accurate headcount reports, organizational charts, or diversity statistics for compliance and internal reporting purposes requires consolidated and easily accessible data.
• Manual Data Verification: Verifying the accuracy of employee records for audits, benefit enrollments, or system integrations often involves manually cross-referencing information, which is inefficient and prone to errors.

The Solution

This report provides a consolidated, date-effective, and easily auditable view of employee assignments, streamlining HR and payroll operations.

• Comprehensive Employee Profile: It presents a single, detailed line for each employee assignment, combining core demographic information with all relevant assignment attributes. This offers an immediate and complete picture of an employee’s status.
• Date-Effective Historical Analysis: The crucial “Effective Date” parameter allows users to view employee assignments as they existed on any given date in the past or present. This is vital for historical analysis, legal compliance, and accurate retrospective reporting.
• Streamlined Workforce Reporting: With flexible filtering options, HR can quickly generate reports for headcount analysis, organizational structure reviews, or specific employee populations, supporting strategic workforce planning.
• Improved Data Quality: By providing an easy way to review all assignment details, the report helps HR professionals identify and correct data inconsistencies proactively, ensuring high data quality across the HR system.

Technical Architecture (High Level)

The report queries core Oracle HR tables, focusing on date-effective employee and assignment data.

• Primary Tables Involved:
  • per_all_people_f (the central date-effective table for employee demographic data).
  • per_all_assignments_f (the central date-effective table for employee assignment details).
  • hr_all_organization_units_vl (for organization names and hierarchy).
  • per_jobs_vl and per_all_positions (for job and position details).
  • per_assignment_status_types_v (for assignment status descriptions).
  • fnd_user (to link to Oracle users if applicable).
• Logical Relationships: The report uses the Effective Date to select the correct records from the date-effective per_all_people_f and per_all_assignments_f tables. It then joins these to various lookup and definition tables to present the full, descriptive details of each employee’s assignment at that point in time.

Parameters & Filtering

The report offers flexible parameters for targeted workforce analysis:

• Employee Identification: Filter by Person Full Name, User Name, or specific Person Type (e.g., ‘Employee’, ‘Contingent Worker’).
• Organizational Context: Assignment Organization and Business Group allow for filtering by specific departments or legal entities.
• Effective Date: The most critical parameter, allowing users to define the point in time for which they want to retrieve assignment data.
• Flexfield Context: Enables the inclusion of Descriptive Flexfield (DFF) attributes relevant to the assignment or person.

Performance & Optimization

As a master data report that deals with date-effective tables, it is optimized for efficient retrieval of historical information.

• Date-Effective Filtering: The Effective Date parameter is crucial. Oracle’s date-effective tables (_F tables) are designed and indexed to efficiently retrieve the correct record version for a given date, ensuring fast performance even when querying historical data.
• Primary Key Joins: The queries use primary keys (e.g., person_id, assignment_id) to perform efficient joins across the various HR tables.

FAQ

1. What is a ‘date-effective’ table in Oracle HR and why is it important for this report? A date-effective table (like per_all_people_f or per_all_assignments_f) stores historical changes to records. Instead of overwriting data, a new record is created with new effective start and end dates. This report leverages this structure, using the Effective Date parameter to retrieve the exact state of an employee’s record at any given point in time.

2. Can this report show an employee’s salary information? While the report shows Payrolls, it typically does not directly display sensitive salary information due to security considerations. Salary details are usually stored in separate, more restricted tables and require specific security setups to be reported. However, the framework could be extended to include this data with appropriate access controls.

3. How can I use this report to create an organizational chart? This report provides the foundational data for an organizational chart by listing employees and their reporting organizations/jobs. While it doesn’t graphically render the chart, the hierarchical information (e.g., manager-employee relationships, department structures) can be exported to tools like Excel or specialized charting software to build an organizational chart.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH