Search Results pay_user_column_instances




Overview
Explore in AI Chat

Overview

PAY_USER_COLUMN_INSTANCES is a date-effective (date-tracked) view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PAY (Payroll) product family. It exposes the rows stored in the underlying date-tracked table PAY_USER_COLUMN_INSTANCES_F, filtered so that only the row version effective as of the current session's effective date is returned. This is the standard Oracle EBS pattern for datetracked entities, where historical and future-dated versions of a record coexist in the "_F" table and are resolved at query time against the session context.

In Oracle EBS 12.1.1 and 12.2.2 the object is reported as VALID in the ETRM metadata. Its role is primarily reporting and integration: it gives developers, concurrent programs, and external interfaces a stable, session-aware read interface over user-defined column data held at the row level of a user-defined table definition. Because the effective-date filtering is embedded in the view definition, callers do not need to write the datetrack predicates themselves.

Underlying Base Objects

The view is defined directly over PAY_USER_COLUMN_INSTANCES_F, referenced through a synonym, and it also depends on FND_SESSIONS, also accessed via a synonym. The view text joins no other tables; instead it applies two correlated scalar subqueries against FND_SESSIONS keyed on USERENV('SESSIONID') to derive the effective date context for the current session.

  • PAY_USER_COLUMN_INSTANCES_F — the datetracked base table holding all effective-dated versions of each user column instance row.
  • FND_SESSIONS — the session table from which the effective date (SS.EFFECTIVE_DATE) of the active session is retrieved for the lower bound and upper bound comparison.

Because the view filters on EFFECTIVE_START_DATE <= session effective date AND EFFECTIVE_END_DATE >= session effective date, the set of rows returned is dynamic and depends entirely on the value stored in FND_SESSIONS for the caller's session identifier. It is therefore unsafe to reason about this view outside an initialized EBS session.

Key Columns

  • USER_COLUMN_INSTANCE_ID — primary identifier for the user column instance record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack boundaries used by the view to resolve the correct version for the session.
  • USER_ROW_ID — reference to the user-defined row to which the instance value belongs.
  • USER_COLUMN_ID — reference to the user-defined column definition that the instance populates.
  • BUSINESS_GROUP_ID — the business group owning the record, the standard multitenancy discriminator in HR/Payroll data.
  • LEGISLATION_CODE and LEGISLATION_SUBGROUP — statutory context of the value, used to scope the column instance to a country and its legislative subgroup.
  • VALUE — the stored data value for the user column instance.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns maintained by the EBS framework.

Common Use Cases and Queries

Typical uses include reporting on user-defined payroll detail captured against user-defined rows, validating that a value exists for the current effective date, and feeding downstream extracts. Queries must run within an initialized EBS session so that FND_SESSIONS contains the correct effective date.

  • Retrieve the current effective values for a given user row.
  • Restrict output by business group or legislation for country-specific reporting.
  • Join to USER_COLUMN definitions to translate identifiers into descriptive column names.

Sample SQL:

SELECT p.user_column_instance_id,
       p.user_row_id,
       p.user_column_id,
       p.business_group_id,
       p.legislation_code,
       p.value
FROM   apps.pay_user_column_instances p
WHERE  p.user_row_id = :user_row_id
AND    p.business_group_id = :business_group_id;

For historical or future-dated analysis, query PAY_USER_COLUMN_INSTANCES_F directly with explicit effective-date predicates, since the view deliberately restricts output to the single session-effective version. Always qualify the view with the APPS schema and observe EBS security conventions regarding business group access.