Search Results hr_s_user_column_instances_f




Overview
Explore in AI Chat

Overview

HR_S_USER_COLUMN_INSTANCES_F is a Human Resources (PER) module table in the Oracle E-Business Suite, owned by the HR schema. The ETRM metadata designates it as "Retrofitted," indicating the object was incorporated into the ETRM document repository after the main extraction cycle rather than captured through the standard reverse-engineering pass. This status accounts for the relatively terse description, but the object itself is a fully valid table within the 12.1.1 and 12.2.2 data models.

Functionally, the table stores per-row instances of user-defined column values. Where PAY_USER_COLUMNS defines the metadata for a configurable, user-extensible column (name, datatype, usage context), HR_S_USER_COLUMN_INSTANCES_F holds the actual value assigned to that column for a specific owning row, identified by USER_ROW_ID. The "_F" suffix follows the Oracle EBS convention for the primary, date-tracked ("F" = full) table in an effective-dated entity group. Under the heuristic Data Vault classification mined from its foreign-key structure, the table is modeled as standalone — that is, it does not participate in a shared hub/link/satellite topology with other objects in the mined graph. As a modeling suggestion, however, its effective-dated attribute payload and its dependent relationship to a user column definition make it a plausible satellite candidate around a user-column hub, should a Data Vault treatment be applied.

Key Information Stored

The table's 15 documented columns divide into identification, business context, value payload, and audit groups.

  • USER_COLUMN_INSTANCE_ID — the surrogate primary key uniquely identifying each instance row.
  • USER_COLUMN_ID — foreign key to PAY_USER_COLUMNS; identifies which user-defined column definition this instance realizes. This is the strongest business-key candidate in combination with USER_ROW_ID.
  • USER_ROW_ID — identifies the owning business row (for example, an assignment, element entry, or other user-extensible entity) to which the value belongs.
  • VALUE — the actual stored data for the instance; because user columns are flexibly typed, this column carries the payload regardless of the declared datatype on the definition.
  • EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date-tracked validity window, confirming the "_F" (date-tracked) designation and enabling point-in-time reconstruction of values.
  • BUSINESS_GROUP_ID — the multi-tenancy discriminator that scopes the row to a specific business group, essential in any query joining HR and PAY tables.
  • LEGISLATION_CODE and LEGISLATION_SUBGROUP — localization context controlling which legislative rules and column definitions apply.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard EBS audit columns.
  • OBJECT_VERSION_NUMBER — optimistic locking token used by the application tier to detect concurrent modification.

Common Use Cases and Queries

Because the table stores the resolved values of configurable columns, it is primarily accessed to reconstruct the extended attribute set of a business row, to report on user-defined data across a population, or to audit changes to those values over time. A typical pattern joins the instance table to its definition table and filters by business group and effective date:

  • SELECT i.USER_ROW_ID, c.USER_COLUMN_NAME, i.VALUE FROM HR.HR_S_USER_COLUMN_INSTANCES_F i, PAY.PAY_USER_COLUMNS c WHERE i.USER_COLUMN_ID = c.USER_COLUMN_ID AND i.BUSINESS_GROUP_ID = :p_bg_id AND TRUNC(SYSDATE) BETWEEN i.EFFECTIVE_START_DATE AND NVL(i.EFFECTIVE_END_DATE, TRUNC(SYSDATE));
  • Point-in-time reporting uses the effective-date pair to return the value that was in force on a historical date.
  • Change auditing compares successive effective-dated rows for a fixed USER_ROW_ID and USER_COLUMN_ID pair.
  • Data migration and interface loads must respect OBJECT_VERSION_NUMBER and the audit columns to avoid locking conflicts and to preserve EBS audit standards.

Related Objects

The documented foreign-key relationship defines the principal join:

  • PAY_USER_COLUMNS — the definition table; joined on USER_COLUMN_ID (HR_S_USER_COLUMN_INSTANCES_F.USER_COLUMN_ID → PAY_USER_COLUMNS).
  • PAY_USER_ROWS — the companion table that identifies the owning row referenced by USER_ROW_ID; commonly joined to resolve the business entity behind the instance.
  • PAY_USER_COLUMN_INSTANCES — the date-tracked sibling in the PAY schema, useful for cross-referencing where the HR-specific retrofitted copy diverges.
  • PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F — typical owning business entities when USER_ROW_ID resolves to person or assignment level.
  • HR_S_USER_COLUMN_INSTANCES — the corresponding non-"_F" view/table layer through which the application often reads the date-tracked data.

Queries should always qualify the HR schema, constrain BUSINESS_GROUP_ID where multi-tenant isolation is required, and honor the effective-date window to obtain correct point-in-time results.