Search Results pay_user_columns_tl




Overview
Explore in AI Chat

Overview

PAY_USER_COLUMNS_TL is the translation (TL) table for user-defined payroll columns in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It resides in the HR schema and belongs to the PAY (Payroll) product family. Its documented description is "Translated User Column Names," which establishes its role: it stores the language-specific display names for the functional columns that customers define through the payroll user-column framework, so that a single logical column definition can be presented in multiple installed languages.

In Oracle EBS, the "_TL" suffix denotes a table that carries translated (MLS) attribute values keyed by LANGUAGE, paired with a "_B" base table that carries the language-independent definition. This object therefore supplies the multilingual text layer rather than the structural definition itself. Heuristically, based on the mined foreign-key structure, the object is classified as standalone; from a Data Vault modeling perspective, it is best suggested as a satellite — a set of descriptive, language-qualified attributes hanging off the user column key — rather than a hub or a link. Because the classification is heuristic, it should be treated as a modeling suggestion, not a documented constraint of the schema.

Key Information Stored

The table is documented with 10 columns in the 12.2.2 physical schema. The most significant are the following:

  • USER_COLUMN_ID — the surrogate/foreign key to the underlying user column definition. Together with LANGUAGE, this forms the primary key.
  • LANGUAGE — the language code identifying the translation. It is the second component of the primary key and the discriminator that makes this a translation table.
  • SOURCE_LANG — the language from which the row was originally created, used by the MLS (Multi-Lingual Support) mechanism to track translation provenance.
  • USER_COLUMN_NAME — the translated, user-facing name of the column as displayed in the relevant language. This is the primary payload of the table.
  • ZD_EDITION_NAME — the editioning column introduced by the EBR (Edition-Based Redefinition) infrastructure in 12.2.x, used to support online patching. It appears in the unique index PAY_USER_COLUMNS_TL_PK (USER_COLUMN_ID, LANGUAGE, ZD_EDITION_NAME).
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard "Who" audit columns recording when and by whom the row was most recently modified.
  • CREATED_BY, CREATION_DATE — the standard audit columns recording the original insertion event.

The documented primary key constraint is PAY_USER_COLUMNS_TL_PK on (USER_COLUMN_ID, LANGUAGE). The unique index also includes ZD_EDITION_NAME, reflecting the 12.2.x editioning model. USER_COLUMN_ID is the surrogate key; the (USER_COLUMN_ID, LANGUAGE) pair is the business-key candidate that uniquely identifies a translated row.

Common Use Cases and Queries

The most frequent requirement is retrieving the translated display name for a given user column in the session's language. A typical query joins the translation table to its base table on USER_COLUMN_ID and filters by LANGUAGE or by the session language:

  • Reporting the effective user column name: SELECT tl.user_column_name FROM pay_user_columns_tl tl WHERE tl.user_column_id = :id AND tl.language = USERENV('LANG');
  • Auditing available translations: SELECT language, user_column_name FROM pay_user_columns_tl WHERE user_column_id = :id ORDER BY language;
  • Detecting missing translations by comparing expected installed languages against rows present for a given user column.
  • Reconciliation after patching, using ZD_EDITION_NAME and the audit columns to confirm which edition's translation rows are active.
  • Metadata and BI Publisher extracts that must render user-defined column captions in the correct locale.

Because it is a translation layer, queries should generally resolve LANGUAGE explicitly rather than relying on implicit defaults, and should be joined to the base table to obtain non-translated attributes such as data type and validation rules.

Related Objects

The following objects are the most significant references and dependencies:

  • PAY_USER_COLUMNS (base table) — joined on USER_COLUMN_ID; supplies the language-independent column definition.
  • PAY_USER_COLUMNS_TL_PK — the primary key constraint and supporting unique index on (USER_COLUMN_ID, LANGUAGE, ZD_EDITION_NAME).
  • PAY_USER_ROWS / PAY_USER_ROW_VALUES and related user-row objects — consume the column identifiers when presenting user-defined payroll data.
  • PAY_USER_TABLES_TL and other _TL tables in the PAY user-defined family — share the same MLS translation pattern and ZD_EDITION_NAME editioning convention.
  • FND_LANGUAGES — joined on LANGUAGE for validation against installed languages.
  • HR.PAY_USER_COLUMNS_ shadow/editioning objects in 12.2.x — support EBR and online patching.

Together these objects form the metadata backbone of the user-defined payroll column feature, with PAY_USER_COLUMNS_TL providing the multilingual text required for correct display across Oracle EBS locales.