Search Results pay_user_column_instances_x




Overview
Explore in AI Chat

Overview

The PAY_USER_COLUMN_INSTANCES_X view is an Oracle E-Business Suite date-effective (MLS-style "_X") reporting view owned by the APPS schema within the Payroll (PAY) product module. It exposes the currently effective slice of the PAY_USER_COLUMN_INSTANCES_F date-tracked table, returning only those rows whose effective date range brackets the current system date. This design follows the standard Oracle HRMS and Payroll date-effective object pattern, in which the "_F" object stores the full history of changes and the "_X" view presents the record version valid "as of today." As a result, the view requires no effective date predicate from the caller, simplifying both ad hoc reporting and integration queries that must reflect only the live configuration of user-defined payroll column instances.

User column instances form part of the Payroll user-defined column framework, which allows payroll administrators to extend standard payroll data structures with customer-configured columns and values. The view sits at the intersection of that configuration metadata and the transactional values captured against it, and is therefore consumed by reports, extracts, and interfaces that need the current value set without traversing history.

Underlying Base Objects

The view is defined over a single documented base object, the synonym PAY_USER_COLUMN_INSTANCES_F (which resolves to the underlying APPS table of the same name). The view text is a straightforward projection:

Because the view is not a join, its relationship to the base table is one of pure filtering and projection. No denormalization, translation, or lookup resolution occurs inside the view; consumers requiring descriptive attributes (for example, the column definition or the parent user row context) must join outward to the corresponding user column definition and user row entities.

Key Columns

  • USER_COLUMN_INSTANCE_ID — Surrogate primary key uniquely identifying each date-effective instance record; this is the identifier most commonly referenced in downstream tables and queries.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date-effective bounds; together they define the validity window used by the view filter.
  • USER_ROW_ID — Foreign reference to the user-defined row to which the column instance belongs.
  • USER_COLUMN_ID — Foreign reference to the user-defined column definition.
  • BUSINESS_GROUP_ID — The business group (legislative data group) owning the instance, providing the multi-tenant boundary for the configuration.
  • LEGISLATION_CODE / LEGISLATION_SUBGROUP — The legislation and subgroup under which the instance applies, supporting country-specific payroll configurations.
  • VALUE — The actual user-entered value stored for the column instance.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide standard WHO tracking for change auditing and interface reconciliation.

Common Use Cases and Queries

Typical uses include payroll configuration reporting, migration validation, and inbound/outbound interface extraction that should reflect only the active effective-dated configuration. A representative query retrieving today's values for a given business group is:

SELECT user_column_instance_id, user_row_id, user_column_id, value, legislation_code FROM apps.pay_user_column_instances_x WHERE business_group_id = :p_business_group_id ORDER BY user_row_id, user_column_id;

A related pattern filters by the specific column instance key when an integration must reconcile a single record:

SELECT user_column_instance_id, effective_start_date, effective_end_date, value FROM apps.pay_user_column_instances_x WHERE user_column_instance_id = :p_instance_id;

Because the view supplies no effective date parameters, queries against the "_F" table should be used instead whenever as-of-date or historical analysis is required.