Results for “pay_user_column_instances_f”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
PAY_USER_COLUMN_INSTANCES_F is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the actual values entered into user-defined tables — the row-and-column intersections that make up the configurable data sets Oracle Payroll administrators create through the User Tables and Columns setup. Each record represents a single cell value for a specific user-defined column at a specific user-defined row, for a given effective date range and business group.
The table is date-tracked (the _F suffix and the EFFECTIVE_START_DATE / EFFECTIVE_END_DATE columns indicate a datetrack-enabled or "F" (frozen/current) style table), so historical values are preserved and can be queried as of any effective date. In Data Vault terms, the mined foreign-key structure suggests a satellite-leaning classification: it depends on a parent key (USER_COLUMN_ID) and carries descriptive attributes (the VALUE payload) rather than defining a new hub or resolving a many-to-many relationship. This is a modeling suggestion only — the table is a standard EBS descriptive child, not an implemented Data Vault object.
Key Information Stored
The documented schema contains 16 columns. The most significant are:
- USER_COLUMN_INSTANCE_ID — surrogate identifier for the instance row; part of the composite primary key PAY_USER_COLUMN_INSTANCES_PK.
- EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack window defining when the value is valid. Both participate in the primary key and the unique business-key index.
- USER_ROW_ID — identifies the user-defined row this value belongs to.
- USER_COLUMN_ID — identifies the user-defined column; this is the foreign key to PAY_USER_COLUMNS.
- VALUE — the actual data stored at the row/column intersection; the core payload of the table.
- BUSINESS_GROUP_ID — the HR business group that owns the value, enforcing multi-tenant separation.
- LEGISLATION_CODE / LEGISLATION_SUBGROUP — the legal jurisdiction context for the user-defined table.
- OBJECT_VERSION_NUMBER — optimistic locking counter used by the ADF/BC4J framework.
- ZD_EDITION_NAME — editioning column used by the EBS 12.2 online patching (Online Patching / Edition-Based Redefinition) infrastructure; it is part of the documented unique index.
- Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.
The unique index PAY_USER_COLUMN_INSTANCES_PK spans USER_COLUMN_INSTANCE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE and ZD_EDITION_NAME, making it both the physical primary key and the business-key candidate. Note the edition column in the unique constraint is an EBS 12.2-specific detail; in 12.1.1 it is absent.
Common Use Cases and Queries
Typical scenarios include retrieving all current values for a user-defined table, reconstructing historical values as of a past date, and joining values back to their column definitions for descriptive reporting.
- Current values for a column:
SELECT USER_ROW_ID, VALUE FROM PAY_USER_COLUMN_INSTANCES_F WHERE USER_COLUMN_ID = :col_id AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE AND BUSINESS_GROUP_ID = :bg_id; - As-of historical query: use a datetrack BETWEEN on the effective dates, or the
AS OFclause where flashback is enabled, to return the value valid at the requested point in time. - Pivoted reporting: join multiple instances on USER_ROW_ID and USER_COLUMN_ID to reconstruct full user-defined table rows for extraction or LOV construction.
- Migration and audit: compare VALUE against pre-patch data to validate user table contents, or feed values into payroll element inputs and fast formulas.
Always filter on EFFECTIVE_START_DATE/EFFECTIVE_END_DATE and BUSINESS_GROUP_ID to avoid mixing historical or cross-business-group rows.
Related Objects
- PAY_USER_COLUMNS — parent; joined on USER_COLUMN_ID = PAY_USER_COLUMNS.USER_COLUMN_ID. This is the only documented foreign key.
- PAY_USER_ROWS_F — the companion date-tracked table supplying row definitions (USER_ROW_ID).
- PAY_USER_TABLES — defines the user-defined table to which columns and rows belong.
- PAY_USER_TABLE_FORMULAS / PAY_USER_TABLE_FUNCTIONS — logic that consumes user-defined table values.
- PAY_USER_ROW_INSTANCES_F — parallel instances table for rows, useful for full row/column reconstruction.
- FND_LOOKUPS / HR lookups — frequently referenced for column value validation.
- Payroll Fast Formula runtime — reads these values via user-defined table database items.
-
Actual values for each row and column combination in a user defined table.
-
Actual values for each row and column combination in a user defined table.