Results for “pds_information_category”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PER_PERIODS_OF_SERVICE is a core Human Resources (PER) table in Oracle E-Business Suite 12.1.1 and 12.2.2, owned by the HR schema. It stores the period-of-service details for an employee — the record that binds a person to a continuous span of employment with a given business group, capturing the start of service, the various termination-related dates, and the leaving reason that together define the lifecycle of an assignment relationship. Because an employee may rejoin after a break in service, a single person can hold multiple period-of-service rows across time, each anchoring the assignments that belong to that service span.

From a Data Vault modeling perspective, the ETRM metadata classifies this table heuristically as satellite-leaning. It carries a single-column surrogate primary key, PERIOD_OF_SERVICE_ID, and no composite business key of its own; instead it is referenced by dependent transaction and assignment tables, and it in turn points to HR_ALL_ORGANIZATION_UNITS. This pattern suggests treating PER_PERIODS_OF_SERVICE as descriptive satellite data attached to a person/business-group hub-and-link structure rather than as an independent hub.

Key Information Stored

The table contains 78 documented columns. The most significant include:

• PERIOD_OF_SERVICE_ID — the surrogate primary key (PER_PERIODS_OF_SERVICE_PK), a system-generated identifier that uniquely identifies each service period.
• PERSON_ID — the employee to whom the service period belongs.
• BUSINESS_GROUP_ID — the business group (organization) under which the service period is recorded; this is a foreign key to HR_ALL_ORGANIZATION_UNITS.
• DATE_START — the date the period of service begins.
• ACCEPTED_TERMINATION_DATE, ACTUAL_TERMINATION_DATE, NOTIFIED_TERMINATION_DATE, and PROJECTED_TERMINATION_DATE — the family of termination dates used to track when termination was offered, accepted, notified, projected, and actually completed.
• LEAVING_REASON — the reason the employee left, central to termination reporting.
• FINAL_PROCESS_DATE and LAST_STANDARD_PROCESS_DATE — processing markers for payroll/final processing.
• ADJUSTED_SVC_DATE — an adjusted service date used for eligibility and entitlement calculations.
• TERMINATION_ACCEPTED_PERSON_ID — the person who accepted the termination.
• OBJECT_VERSION_NUMBER and the LAST_UPDATE_DATE/CREATED_BY audit columns — standard concurrency-control and audit fields.

Beyond the surrogate key, the documented unique indexes are PER_PERIODS_OF_SERVICE_PK (on PERIOD_OF_SERVICE_ID) and the LOB index SYS_IL0000043959C00078$$; no separate business-key unique index is defined on the person/date combination, so uniqueness of a service period is enforced through the surrogate key rather than a natural business key.

Common Use Cases and Queries

The table is used for headcount, service-length, and termination reporting, and is a standard source for HR analytics extracts such as the HR_EDW_WRK_ACTVTY_F warehouse structures. Typical patterns:

• Service history for a person: SELECT date_start, actual_termination_date, leaving_reason FROM per_periods_of_service WHERE person_id = :person_id ORDER BY date_start;
• Active service spans (those without a completed termination): WHERE actual_termination_date IS NULL.
• Termination analysis: grouping by leaving_reason within a date_start/actual_termination_date range for a business group.
• Joining to assignments to relate an assignment to its parent service period: SELECT a.assignment_id, p.date_start FROM per_all_assignments_f a JOIN per_periods_of_service p ON a.period_of_service_id = p.period_of_service_id;

Related Objects

• PER_ALL_ASSIGNMENTS_F — references this table via PERIOD_OF_SERVICE_ID; each assignment belongs to a period of service.
• HR_ALL_ORGANIZATION_UNITS — referenced by this table via BUSINESS_GROUP_ID.
• HRI_EDW_PERIOD_OF_SERVICE — the ETL/warehouse object keyed on PERIOD_OF_SERVICE_ID.
• HR_EDW_WRK_ACTVTY_F and HR_EDW_WRK_ACTVTY_FSTG — analytics fact and staging tables that carry PERIOD_OF_SERVICE_ID.
• PAY_OBJECT_GROUPS — references PERIOD_OF_SERVICE_ID, linking payroll object grouping to service periods.

Together these dependencies confirm the table's role as a central service-lifecycle satellite supporting both transactional assignment processing and downstream Oracle HR analytics.