Search Results per_people_extra_info_fk1

Overview

The HR.PER_PEOPLE_EXTRA_INFO table is a core data structure within the Oracle E-Business Suite Human Resources (HRMS) module, specifically in releases 12.1.1 and 12.2.2. It functions as an intersection table, establishing a many-to-many relationship between persons (PER_ALL_PEOPLE_F) and the types of extra information that can be stored about them (PER_PEOPLE_INFO_TYPES). Its primary role is to store extensible, user-defined supplementary information for employees, contingent workers, and other person types beyond the standard attributes captured in the primary person tables. This design enables significant flexibility, allowing organizations to configure and capture diverse data points specific to their business needs.

Key Information Stored

The table's structure is defined by a system-generated primary key, foreign key relationships, a set of standard tracking columns, and a descriptive flexfield (DFF). The PERSON_EXTRA_INFO_ID is the unique, system-generated identifier. The PERSON_ID column is a foreign key linking to a specific person record in PER_ALL_PEOPLE_F. The INFORMATION_TYPE column is a foreign key linking to a configured information type in PER_PEOPLE_INFO_TYPES, which defines the category of data being stored (e.g., 'Emergency Contact', 'Visa Details'). The table also includes standard "Who" columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) for tracking data origin. Crucially, the table provides 12 attribute columns (PEI_ATTRIBUTE1-12) and a category column (PEI_ATTRIBUTE_CATEGORY), which together form a descriptive flexfield. The actual values for the configured extra information are stored within these attribute columns, with their meaning determined by the flexfield configuration tied to the INFORMATION_TYPE.

Common Use Cases and Queries

This table is central to reporting on and integrating custom person data. Common use cases include extracting emergency contact details, visa or passport information, professional certifications, or any other configured person-specific attributes. A typical query joins PER_PEOPLE_EXTRA_INFO to PER_ALL_PEOPLE_F for person details and to PER_PEOPLE_INFO_TYPES for context on the information type. For example, to retrieve all 'Drivers License' information for active employees, one might use a query pattern such as:

• SELECT ppf.employee_number, ppf.full_name, einfo.PEI_ATTRIBUTE1 as license_number, einfo.PEI_ATTRIBUTE2 as expiry_date FROM hr.per_people_extra_info einfo, hr.per_all_people_f ppf, hr.per_people_info_types pit WHERE einfo.person_id = ppf.person_id AND einfo.information_type = pit.information_type AND pit.legislation_code = 'US' AND pit.information_type = 'Driver''s License' AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;

Data is often created or modified via the Oracle HRMS Person API (HR_PERSON_API) or through dedicated forms configured for the specific information type.

Related Objects

The table maintains defined foreign key relationships with other core HRMS tables, as documented in the ETRM metadata. The primary dependencies are:

• PER_ALL_PEOPLE_F: The relationship is established via the PERSON_ID column in PER_PEOPLE_EXTRA_INFO, which is a foreign key to the PERSON_ID in PER_ALL_PEOPLE_F. This links each extra information record to a specific person.
• PER_PEOPLE_INFO_TYPES: The relationship is established via the INFORMATION_TYPE column in PER_PEOPLE_EXTRA_INFO, which is a foreign key to the INFORMATION_TYPE in PER_PEOPLE_INFO_TYPES. This links each record to a valid, configured category of extra information. The index PER_PEOPLE_EXTRA_INFO_FK1 supports this relationship.

Additionally, the table's primary key (PERSON_EXTRA_INFO_ID) is enforced by the PER_PEOPLE_EXTRA_INFO_PK index, and a non-unique index (PER_PEOPLE_EXTRA_INFO_N50) exists on the combination of PERSON_ID and INFORMATION_TYPE to support efficient queries.