Search Results per_all_people_f

Overview

PER_ALL_PEOPLE_F is the core DateTracked table in the Oracle E-Business Suite Human Resources (PER) module, owned by the HR schema. It stores personal information for every person registered in the system, encompassing employees, applicants, contingent workers, and other person types defined by the enterprise. Because it is a DateTracked table, each row is versioned by an effective date range, allowing historical and future-dated changes to be retained without losing prior values — a fundamental requirement for HR, payroll, and benefits processing in Oracle EBS 12.1.1 and 12.2.2.

With 152 documented columns in the 12.2.2 physical schema, this table serves as the central repository from which nearly all person-related transactions and reporting originate. Under a heuristic Data Vault classification mined from its foreign key structure, the table is best modeled as hub-leaning: PERSON_ID behaves as the durable business key identifying a person across time, while the effective-dated attributes (name, dates of birth, flags, and descriptive fields) exhibit satellite characteristics that capture changing descriptive state. Practically, this means PERSON_ID remains stable and unique per individual, whereas the surrounding columns describe the state of that individual during a specific effective period.

Key Information Stored

The primary key PER_PEOPLE_F_PK is defined on the composite of PERSON_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This is a surrogate identifier tied to the DateTrack mechanism, not a purely business-meaningful key. Business-key candidates documented via unique indexes align with the same three columns, meaning no single attribute uniquely identifies a person — PERSON_ID alone identifies the person, and the effective dates identify the version.

The most significant columns include:

• PERSON_ID — the system-generated surrogate identifier for the individual; the anchor for all joins.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack validity window for the row version.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS; scopes the person to a business group.
• PERSON_TYPE_ID — foreign key to PER_PERSON_TYPES; determines whether the person is an employee, applicant, or other category.
• EMPLOYEE_NUMBER / APPLICANT_NUMBER — the human-readable identifiers assigned by the enterprise.
• LAST_NAME, FIRST_NAME, MIDDLE_NAMES, FULL_NAME, KNOWN_AS — name-related attributes used extensively in reporting and search.
• DATE_OF_BIRTH, DATE_OF_DEATH, ORIGINAL_DATE_OF_HIRE — critical date fields for eligibility and lifecycle logic.
• CURRENT_EMPLOYEE_FLAG, CURRENT_APPLICANT_FLAG, CURRENT_EMP_OR_APL_FLAG — denormalized status flags refreshed by concurrent processes for fast querying.
• EMAIL_ADDRESS, WORK_TELEPHONE — contact attributes frequently joined for notifications.
• NATIONAL_IDENTIFIER, GLOBAL_PERSON_ID, PARTY_ID — identity and integration fields, with PARTY_ID linking to the Trading Community Architecture (TCA).
• OBJECT_VERSION_NUMBER — optimistic locking column used by the framework.
• ATTRIBUTE1–30, PER_INFORMATION1–30, PER_INFORMATION_CATEGORY — extensibility (DFF) columns for client-specific data.

Common Use Cases and Queries

The table is central to HR reporting, headcount analysis, and integration extracts. The most critical constraint in practice is DateTracking: queries must always filter on the effective date to isolate the correct version, typically via a SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE predicate.

• Current person lookup:
  SELECT person_id, full_name, employee_number FROM per_all_people_f WHERE business_group_id = :bg AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
• Headcount and active employees: filter on CURRENT_EMPLOYEE_FLAG = 'Y' for a denormalized fast path, or join to assignment tables to count active assignments.
• Applicant tracking: filter CURRENT_APPLICANT_FLAG = 'Y' or join PER_PERSON_TYPES to isolate applicants.
• Integration extracts to third-party systems commonly select PERSON_ID, GLOBAL_PERSON_ID, EMAIL_ADDRESS, and national identifiers, restricting to the effective-dated current row.
• Historical analysis: querying as-of a past effective date recovers superseded name or address values.

Report developers should be aware that CURRENT_* flags are maintained by the "Refresh People" process and may lag transactional updates; joining to date-effective assignments is the authoritative alternative.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — referenced by PER_ALL_PEOPLE_F.BUSINESS_GROUP_ID; defines the business group scope.
• PER_PERSON_TYPES — referenced by PERSON_TYPE_ID; classifies the person.
• PJI_RESOURCES_DENORM — references PERSON_ID; Project resource denormalization for staffing.
• WMS_DISPATCHED_TASKS / WMS_DISPATCHED_TASKS_ARCH / WMS_DISPATCHED_TASKS_HISTORY — reference PERSON_ID with effective dates for Warehouse Management task dispatch.
• WMS_EXCEPTIONS / WMS_SKIP_TASK_EXCEPTIONS — reference PERSON_ID for warehouse exception handling.
• PER_ALL_ASSIGNMENTS_F — the assignment table shares PERSON_ID as the primary join path to employment-specific data.
• TCA / HZ_PARTIES — linked via PARTY_ID for party-model integration.
• PER_PEOPLE_API (public API) — the supported programmatic interface for creating and updating person records, enforced so that DateTrack and validation logic are applied.