Search Results per_people_v3




Overview
Explore in AI Chat

Overview

PER_PEOPLE_V3 is a validity-tracked database view owned by the APPS schema within the Oracle E-Business Suite Human Resources (PER) product family. In ETRM metadata for release 12.1.1 and 12.2.2 the object is recorded with a status of VALID and is described as being "used to support user interface." This description is significant: PER_PEOPLE_V3 is not a public, supported API surface but an internal rendering view that Oracle Forms-based HR windows and related self-service screens reference when displaying person, assignment, and status information to the end user.

Functionally, the view presents a flattened, denormalized projection of the person record held in PER_ALL_PEOPLE_F, enriched with decoded lookups, current assignment status, and derived telephone and person-type information. Because it exposes ROWID (as ROW_ID), it can be used as the driving query for an updatable block, although direct DML against a view of this complexity is not recommended. For reporting and integration purposes it serves as a convenient single-source read model that spares the developer from manually joining PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, lookup tables, and the HR_GENERAL and HR_PERSON_TYPE_USAGE_INFO packages.

Its effective-dated nature is inherited from the underlying PER_ALL_PEOPLE_F synonym: each row carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, and consumers must apply a date predicate to obtain the correct version of a person record at a point in time.

Underlying Base Objects

The documented referenced objects show that PER_PEOPLE_V3 is a multi-table composite. Its core is the PER_ALL_PEOPLE_F synonym (exposed in the view text as the PEO alias), which supplies all person-level attributes including name, dates of birth, nationality, email, employee number, and the ATTRIBUTE1 through ATTRIBUTE30 descriptive flexfield columns.

Assignment data is drawn from PER_ALL_ASSIGNMENTS (a view) and PER_ALL_ASSIGNMENTS_F, while assignment status is resolved through PER_ASSIGNMENT_STATUS_TYPES and its translation table PER_ASSIGNMENT_STATUS_TYPES_TL, supplemented by PER_ASS_STATUS_TYPE_AMENDS and PER_ASS_STATUS_TYPE_AMENDS_TL. Person type classification comes from PER_PERSON_TYPES, and the SYSTEM_PERSON_TYPE value is surfaced through it.

Lookups are provided by HR_LOOKUPS (a view) for the D_TITLE and D_SEX decoded meanings. Location information is joined through HR_LOCATIONS_ALL_TL and HR_LOCATIONS_NO_JOIN. Organizational context is available via HR_ALL_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS_TL.

Several columns are computed by PL/SQL packages rather than simple joins: HR_GENERAL.GET_WORK_PHONE derives WORK_TELEPHONE, and HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE resolves the user-visible person type. Additional package references include HR_API, HR_PERSON_NAME, HR_SECURITY, and PER_APPLICANT_PKG, the last of which supports applicant-related columns such as APPLICANT_NUMBER and CURRENT_APPLICANT_FLAG.

Key Columns

  • PERSON_ID, BUSINESS_GROUP_ID — primary identifiers for the person and the operating business group.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-track keys inherited from PER_ALL_PEOPLE_F.
  • LAST_NAME, FIRST_NAME, MIDDLE_NAMES, FULL_NAME, KNOWN_AS, ORDER_NAME — name components and display variants.
  • EMPLOYEE_NUMBER / NPW_NUMBER — the NVL expression returns the employee number where present, otherwise the non-payroll worker number.
  • SYSTEM_PERSON_TYPE and the GET_USER_PERSON_TYPE expression — system-level and user-facing person type classification.
  • STATUS and SYSTEM_STATUS — the NVL pairings over assignment status type amendments and assignment status types yield the user status and the underlying system status.
  • WORK_TELEPHONE, EMAIL_ADDRESS — contact information, the telephone derived via HR_GENERAL.
  • SEX, D_SEX, TITLE, D_TITLE — lookup codes alongside their decoded meanings.
  • CURRENT_EMPLOYEE_FLAG, CURRENT_APPLICANT_FLAG, CURRENT_EMP_OR_APL_FLAG — convenience flags for filtering active populations.
  • ATTRIBUTE1–ATTRIBUTE30, ATTRIBUTE_CATEGORY — the person descriptive flexfield.
  • ROW_ID — the ROWID of the underlying person row.

Common Use Cases and Queries

The most frequent use is as a read-only reporting source for current employee and applicant listings. A typical query filters on the effective date and current-employee flag:

  • SELECT person_id, full_name, employee_number, status FROM apps.per_people_v3 WHERE current_employee_flag = 'Y' AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
  • SELECT person_id, full_name, work_telephone, email_address FROM apps.per_people_v3 WHERE business_group_id = :p_bg AND current_emp_or_apl_flag = 'Y';
  • SELECT person_id, full_name, d_sex, d_title FROM apps.per_people_v3 WHERE last_name LIKE :p_prefix ORDER BY last_name;

Because the view calls HR_GENERAL and HR_PERSON_TYPE_USAGE_INFO per row, large extracts can be slow; restricting by business group and effective date is advisable. Implementations should treat PER_PEOPLE_V3 as an internal UI-support object and prefer documented public views or APIs where an upgrade-safe interface is required.