Search Results per_all_people




Overview
Explore in AI Chat

Overview

PER_ALL_PEOPLE is a date-effective view owned by the APPS schema in Oracle E-Business Suite, belonging to the PER (Human Resources) product family. It presents person records — employees, applicants, contingent workers, and other person types — as of the effective date values carried on each row. Because Oracle HRMS stores person data in date-tracked (datetracked) form, the view exposes the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns that define the validity window of every version of a person record, allowing consumers to reason about historical, current, and future states of the same PERSON_ID.

The view is widely used for reporting, interfaces, and integrations where a stable, denormalized person abstraction is required without the caller having to manage datetrack logic manually. It is a core reference object for downstream data extraction and for code that must resolve a person's identity, name, employee number, or business group context.

Underlying Base Objects

Per the documented ETRM metadata, PER_ALL_PEOPLE is defined over the following base objects:

  • PER_ALL_PEOPLE_F (synonym) — the primary datetracked person table supplying PERSON_ID, effective dates, business group, person type, name components, and the majority of descriptive and DFF attributes.
  • HR_PERSON_NAME (package) — the name-handling package. The view text references name-related columns such as LAST_NAME, FIRST_NAME, FULL_NAME, ORDER_NAME, and KNOWN_AS, which are derived consistent with this package's formatting logic.
  • FND_SESSIONS (synonym) — a session/context object, typically involved in resolving database session or environment context used by the view definition.

The relationship is therefore a layered one: the view selects and projects columns from the datetracked person table, applies name-formatting rules via the HR_PERSON_NAME package, and may incorporate session context through FND_SESSIONS. The view is marked VALID and remains a documented object in both 12.1.1 and 12.2.2.

Key Columns

Common Use Cases and Queries

Typical uses include headcount reporting, person lookups for interfaces, and resolving a person for assignment-based analysis.

  • List current employees in a business group:
SELECT p.person_id, p.full_name, p.employee_number
FROM   per_all_people p
WHERE  p.business_group_id = :p_bg_id
AND    p.current_employee_flag = 'Y'
AND    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date;
  • Resolve a person by employee number for use as a foreign key:
SELECT p.person_id, p.full_name
FROM   per_all_people p
WHERE  p.employee_number = :p_emp_num
AND    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date;

When querying, always constrain the effective dates to avoid duplicate rows returned by datetracked versions, and filter by BUSINESS_GROUP_ID in multi-group environments. For reporting that requires assignment, pay, or job context, join PERSON_ID to PER_ALL_ASSIGNMENTS_F following the same effective-dating rules.