Search Results pay_user_column_instances_f




Overview
Explore in AI Chat

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:

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 OF clause 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.