Search Results per_assignment_extra_info

Overview

PER_ASSIGNMENT_EXTRA_INFO is a Human Resources (PER) table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model. It resides in the HR schema and stores descriptive flexfield (DFF) content captured against an employee assignment record. The object is documented in ETRM with a VALID status and is described as "extra information for an assignment." Functionally, it preserves the free-format and context-sensitive attributes that extend the core assignment entity beyond the fixed columns held in PER_ALL_ASSIGNMENTS_F.

The table is organized around a single primary key, PER_ASSIGNMENT_EXTRA_INFO_PK on ASSIGNMENT_EXTRA_INFO_ID, and references PER_ASSIGNMENT_INFO_TYPES through the INFORMATION_TYPE column. The ETRM heuristic Data Vault classification identifies this object as satellite-leaning. In a Data Vault model, this suggests the table behaves as a satellite attached to the assignment hub, holding descriptive, version-tracked detail rather than serving as a standalone hub or a link between two hubs.

Key Information Stored

The documented schema contains 65 columns. The most significant are:

• ASSIGNMENT_EXTRA_INFO_ID — the surrogate primary key and unique identifier for each extra-information row.
• ASSIGNMENT_ID — the foreign reference to the assignment to which the extra information belongs; this is the principal business join key.
• INFORMATION_TYPE — the foreign key to PER_ASSIGNMENT_INFO_TYPES, identifying which information type or DFF context is being stored.
• AEI_ATTRIBUTE_CATEGORY — the context or category value that determines how the attribute segments are interpreted.
• AEI_ATTRIBUTE1 through AEI_ATTRIBUTE20 — twenty generic descriptive flexfield attribute segments holding user-defined data values.
• AEI_INFORMATION_CATEGORY and AEI_INFORMATION1 through AEI_INFORMATION30 — a thirty-segment information flexfield used for additional structured detail.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by Oracle Forms and OAF to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard WHO audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context for the row.

The only documented unique index is the primary key index, so ASSIGNMENT_EXTRA_INFO_ID is the sole documented business-key candidate; ASSIGNMENT_ID plus INFORMATION_TYPE function as the practical composite key for data retrieval.

Common Use Cases and Queries

Reporters and integrators query this table to extract DFF values attached to assignments that are not exposed in the core assignment table. A typical query joins to the assignment and the information type:

• Extract all extra information for a given assignment: SELECT * FROM per_assignment_extra_info WHERE assignment_id = :p_assignment_id;
• Retrieve a specific information type: SELECT aei_information1, aei_attribute1 FROM per_assignment_extra_info WHERE assignment_id = :p_id AND information_type = :p_type;
• Reconcile DFF context values: join to PER_ASSIGNMENT_INFO_TYPES on information_type to resolve the descriptive name of each information type.
• Audit recent changes: filter on last_update_date or last_updated_by within a date range for HR data-quality reporting.
• Concurrent-program tracing: filter by request_id or program_id to identify rows produced by a batch load or interface.

Because the table is satellite-leaning, ETL designs typically treat it as the effective-dated detail layer around the assignment hub, loading new rows rather than updating in place where history is required.

Related Objects

The most significant related objects, based on documented foreign-key and dependency structure, are:

• PER_ASSIGNMENT_INFO_TYPES — referenced by INFORMATION_TYPE; defines the valid information types and drives DFF context behavior.
• PER_ALL_ASSIGNMENTS_F — the primary assignment entity linked via ASSIGNMENT_ID.
• PER_ALL_PEOPLE_F — the person record associated with the assignment, reached indirectly through the assignment.
• FND_DESCR_FLEX_COLUMN_USAGES — defines the flexfield segments mapped to the AEI_ATTRIBUTE and AEI_INFORMATION columns.
• FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — validate the values held in the flexfield segments.
• Assignment and HR APIs such as HR_ASSIGNMENT_API — used to create and maintain assignment records and their extra information programmatically.