Search Results per_periods_of_service

Overview

The PER_PERIODS_OF_SERVICE table is a core data object within the Oracle E-Business Suite Human Resources (PER) module. It serves as the system of record for an employee's continuous periods of employment within the enterprise. Each row represents a distinct, unbroken span of service for a person within a specific business group. This table is fundamental for tracking employment history, calculating length of service for benefits and entitlements, and managing employee life-cycle events such as hires, re-hires, and terminations. Its integrity is critical as it provides the foundational service timeline against which all assignments, payroll runs, and HR reporting are validated.

Key Information Stored

The table's primary key is PERIOD_OF_SERVICE_ID, a unique system-generated identifier for each service period. Essential columns include PERSON_ID, linking to the individual in PER_ALL_PEOPLE_F, and BUSINESS_GROUP_ID, a foreign key to HR_ALL_ORGANIZATION_UNITS defining the organizational context. Key date columns capture the lifecycle: DATE_START (the hire or re-hire date), ACTUAL_TERMINATION_DATE (the effective termination date), and FINAL_PROCESS_DATE (marking the completion of all termination settlements). Status is managed via columns like PERIOD_TYPE (e.g., 'EMPLOYMENT') and LEAVING_REASON. The table also holds attributes for statutory information, such as whether the employee is on a statutory unpaid absence.

Common Use Cases and Queries

A primary use case is generating service history reports for audits or employee records. Calculating an employee's total length of service, considering multiple periods for re-hires, is a frequent requirement. The table is also central to termination processes and validating assignment data. Common SQL patterns include identifying current employees by selecting records where ACTUAL_TERMINATION_DATE IS NULL, and calculating tenure. For example:

• SELECT person_id, date_start, actual_termination_date, TRUNC(MONTHS_BETWEEN(NVL(actual_termination_date, SYSDATE), date_start)/12) AS years_of_service FROM per_periods_of_service WHERE business_group_id = :p_bg_id;
• Joining with PER_ALL_ASSIGNMENTS_F to analyze assignments within a specific service period.
• Reporting on termination trends by querying LEAVING_REASON and termination dates within a date range.

Related Objects

PER_PERIODS_OF_SERVICE has integral relationships with other HRMS tables. As per the provided metadata, the PER_ALL_ASSIGNMENTS_F table holds a foreign key (PERIOD_OF_SERVICE_ID) to this table, meaning every assignment is linked to a specific period of service. The table itself references HR_ALL_ORGANIZATION_UNITS via BUSINESS_GROUP_ID. While not listed in the excerpt, it typically has a foreign key to PER_ALL_PEOPLE_F via PERSON_ID. Key APIs, such as the PER_SERVICE package, likely interact with this table to create and manage service periods. It is also a critical source for HR information types and underlying views like PER_PEOPLE_F.