Search Results per_periods_of_service_v




Overview
Explore in AI Chat

Overview

PER_PERIODS_OF_SERVICE_V is a seeded Oracle E-Business Suite view owned by the APPS schema and registered as VALID in both release 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and is documented in the E-Business Suite Technical Reference Manual (ETRM) with the concise description "Used to support user interface." The view exposes the full row set of the PER_PERIODS_OF_SERVICE entity, extended with a decoded meaning for the leaving reason and a ROWID pseudo-column that supports updateable, form-driven access.

Functionally, the view presents the complete lifecycle of a person's period of service: the hire or start date, projected and actual termination information, termination acceptance data, final processing dates, leaving reason, descriptive flexfield (DFF) attributes, the PDS_INFORMATION extensible columns, and standard WHO audit columns. Because Oracle Forms-based HR windows and self-service pages reference this view rather than the base object directly, it is the canonical read interface for period-of-service data in reporting, conversion, and integration scenarios.

Underlying Base Objects

ETRM 12.2.2 documents three referenced objects behind PER_PERIODS_OF_SERVICE_V:

  • PER_PERIODS_OF_SERVICE (SYNONYM) — the primary source, aliased PPS in the view text. It supplies every functional column: PERIOD_OF_SERVICE_ID, PERSON_ID, DATE_START, the termination date set, LEAVING_REASON, all ATTRIBUTE1–20 and PDS_INFORMATION1–30 columns, and the audit and concurrent-program columns.
  • HR_LOOKUPS (VIEW) — joined with an outer join (indicated by the (+) operator) on LOOKUP_TYPE = 'LEAV_REAS' and LOOKUP_CODE = PPS.LEAVING_REASON. It contributes the L.MEANING column so that the coded leaving reason is presented as a user-readable description.
  • HR_API (PACKAGE) — the PL/SQL business logic package that governs inserts, updates, and deletes against the period-of-service entity. It is the enforcement layer through which UI and API writes flow.

Two notable expressions appear in the view text. BUSINESS_GROUP_ID is exposed as PPS.BUSINESS_GROUP_ID + 0, a numeric coercion Oracle Forms relies on to ensure consistent datatype handling. The PPS.ROWID pseudo-column is selected explicitly, which, combined with the outer-joined lookup, permits the view to be used for updateable blocks where the retained ROWID identifies the underlying base row.

Key Columns

Common Use Cases and Queries

The view is widely used for workforce reporting, termination analysis, rehire detection, and data migrations. Because it decodes the leaving reason and includes the adjusted service date, it is a convenient single source for length-of-service and attrition extracts. A typical listing query joins to PER_ALL_PEOPLE_F for the employee name:

  • SELECT pps.person_id, pps.period_of_service_id, pps.date_start, pps.actual_termination_date, pps.leaving_reason, pps.meaning FROM per_periods_of_service_v pps WHERE pps.actual_termination_date BETWEEN :p_from AND :p_to ORDER BY pps.actual_termination_date;
  • SELECT pps.person_id, papf.full_name, pps.adjusted_svc_date, pps.date_start FROM per_periods_of_service_v pps, per_all_people_f papf WHERE papf.person_id = pps.person_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
  • SELECT pps.person_id, pps.leaving_reason, pps.meaning, COUNT(*) FROM per_periods_of_service_v pps GROUP BY pps.person_id, pps.leaving_reason, pps.meaning;

Because the view retains ROWID and represents a single base entity, it supports both query-only reporting and, through HR_API, controlled maintenance. For direct data fixes, Oracle recommends the HR_API package rather than DML against the view, since the API maintains validation, date-track, and audit integrity.