Results for “hr_location_extra_info_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR.HR_LOCATION_EXTRA_INFO is a transactional child table in the Oracle E-Business Suite Human Resources (HR) schema that stores descriptive flexfield (DFF) and "extra information" attribute values attached to a specific location record. Functionally, it represents the intersection between HR_LOCATION_INFO_TYPES and HR_LOCATIONS_ALL: a row exists only when a location has been associated with an information type defined in the location information types setup. The table is registered in FND Design Data as PER.HR_LOCATION_EXTRA_INFO and is stored in the APPS_TS_TX_DATA tablespace with PCT Free 10, reflecting its role as an application data (rather than reference) object.

From a Data Vault modeling perspective, the mined FK structure (two incoming foreign keys to distinct parent hubs, plus a system-generated surrogate key) supports classifying this object heuristically as a link table: it records the association between a location and an information type, with the DFF attribute and information columns behaving like link-level (or satellite-style) descriptive payload. This is a modeling suggestion only; the physical implementation in EBS is a standard DFF storage table.

Key Information Stored

The table's identity and relationships are anchored by three core columns. The surrogate primary key is LOCATION_EXTRA_INFO_ID (NUMBER(15)), enforced by the unique index HR_LOCATION_EXTRA_INFO_PK; it is the only documented unique/business-key candidate. Two foreign-key columns establish the intersection:

• INFORMATION_TYPE (VARCHAR2(40)) — references HR_LOCATION_INFO_TYPES, identifying which category of extra information the row holds. Indexed non-uniquely by HR_LOCATION_EXTRA_INFO_FK2 is not correct: the FK2 index covers LOCATION_ID, while HR_LOCATION_EXTRA_INFO_FK1 covers INFORMATION_TYPE.
• LOCATION_ID (NUMBER(15)) — references HR_LOCATIONS_ALL, tying the row to its parent location. This is the column physically indexed by HR_LOCATION_EXTRA_INFO_FK2 — the exact index name the user searched for.

Two families of descriptive payload columns carry the actual content. The LEI_ATTRIBUTE_CATEGORY and LEI_ATTRIBUTE1–LEI_ATTRIBUTE20 columns (VARCHAR2(150) each) implement the descriptive flexfield, while LEI_INFORMATION_CATEGORY and LEI_INFORMATION1–LEI_INFORMATION30 store the associated "extra information" values. Standard WHO audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER — support concurrency and audit. Request-tracking columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE capture the concurrent program context. The documented schema lists 65 columns in total.

Common Use Cases and Queries

Typical usage centers on reporting and extracting location-level DFF data for integrations, EDI feeds, and HR analytics. A common pattern joins the extra info to locations to resolve readable location names:

• Retrieve all extra information for a location: SELECT * FROM hr.hr_location_extra_info WHERE location_id = :p_location_id;
• Resolve type descriptions: join INFORMATION_TYPE to HR_LOCATION_INFO_TYPES to obtain the information type's display name.
• Audit which concurrent program inserted a row, using REQUEST_ID and PROGRAM_ID against FND_CONCURRENT_REQUESTS.
• Extract DFF payloads by pivoting LEI_ATTRIBUTEn or LEI_INFORMATIONn columns for downstream warehouse loading.

Because the FK2 index on LOCATION_ID is non-unique, queries filtering by LOCATION_ID or joining to HR_LOCATIONS_ALL leverage that index efficiently; filters on INFORMATION_TYPE use FK1. No natural unique constraint exists beyond the surrogate PK, so deduplication on (LOCATION_ID, INFORMATION_TYPE) should be validated in reporting logic.

Related Objects

• HR.HR_LOCATIONS_ALL — referenced by LOCATION_ID; the parent location definition.
• HR.HR_LOCATION_INFO_TYPES — referenced by INFORMATION_TYPE; defines valid extra-information categories.
• HR.HR_LOCATION_EXTRA_INFO_PK — the primary key index on LOCATION_EXTRA_INFO_ID.
• HR.HR_LOCATION_EXTRA_INFO_FK1 / HR_LOCATION_EXTRA_INFO_FK2 — indexes on INFORMATION_TYPE and LOCATION_ID respectively.
• FND_CONCURRENT_REQUESTS — join target for request/program tracking columns.
• PER.HR_LOCATION_EXTRA_INFO — the FND Design Data registration used for DFF and form generation.