Search Results pay_user_tables_tl




Overview
Explore in AI Chat

Overview

PAY_USER_TABLES_TL is the translation (TL) table for the Oracle Payroll user-defined table framework, owned by the HR schema and delivered under the PAY — Payroll product. Its documented purpose is to store the translated user table name and row titles, enabling multi-language display of user-defined payroll tables across the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. In EBS, user tables are customer-defined structures of validated values — for example, earnings or deduction classifications, bargaining units, or other lookup-style data — that payroll formulas and elements reference at runtime. Because these tables must appear in the user's session language, PAY_USER_TABLES_TL carries the language-specific attributes of the base definition rather than the definition itself.

The ETRM metadata classifies this object heuristically as standalone within a Data Vault modeling suggestion. In practice, its two-part unique key (USER_TABLE_ID, LANGUAGE) and its role as the language-dependent companion of a base entity make it behave most naturally as a satellite attached to a user-table hub. No foreign-key relationships were mined, so the linkage to the base table is inferred from the shared USER_TABLE_ID column rather than declared constraints.

Key Information Stored

The table contains eleven documented columns. The most significant are:

  • USER_TABLE_ID — the identifier of the parent user table; the leading column of the primary key and the join key back to the base entity.
  • LANGUAGE — the language code for which the translated name and titles are held; combined with USER_TABLE_ID it forms the composite primary key.
  • SOURCE_LANG — the language in which the row was originally entered, used by the translation infrastructure to determine whether the row is a base row or a translation.
  • USER_TABLE_NAME — the language-specific display name of the user-defined table.
  • USER_ROW_TITLE — the translated title applied to rows of the user table, controlling how individual values are labelled in the user interface.
  • ZD_EDITION_NAME — the editioning column introduced by the 12.2 online patching architecture; it participates in the unique index PAY_USER_TABLES_TL_PK (USER_TABLE_ID, LANGUAGE, ZD_EDITION_NAME) and is absent in 12.1.1 code lines.

The remaining documented columns are the standard WHO audit attributes: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE. The surrogate primary key is USER_TABLE_ID plus LANGUAGE, while the unique index extends that key with ZD_EDITION_NAME to preserve editioning integrity in 12.2.2.

Common Use Cases and Queries

The predominant use case is retrieving the translated name and row title for a user table in the user's current session language, typically with a fallback to the base language when a translation row does not exist. Reporting and diagnostics also rely on this table to audit which translations have been maintained.

  • Resolving a display name: SELECT user_table_name, user_row_title FROM pay_user_tables_tl WHERE user_table_id = :id AND language = USERENV('LANG');
  • Detecting missing translations by comparing counts of base definitions against TL rows per language.
  • Validating that SOURCE_LANG equals LANGUAGE for base rows and differs for genuine translations.
  • Joining to the base table to produce a complete multilingual catalogue for user-table maintenance forms.

Related Objects

The most significant relationships centre on the base definition that shares the USER_TABLE_ID key.

  • PAY_USER_TABLES — the base (non-translated) user table definition; join on USER_TABLE_ID.
  • PAY_USER_ROWS — the rows of values belonging to each user table; join on USER_TABLE_ID.
  • PAY_USER_COLUMNS — column definitions of user tables, also keyed by USER_TABLE_ID.
  • PAY_USER_TABLE_F — the translation infrastructure object that may mediate access to the TL columns.
  • FND_LANGUAGES — the language reference validating the LANGUAGE and SOURCE_LANG values.

In 12.2 environments, all queries should be edition-aware, filtering on the active ZD_EDITION_NAME when editioning is enabled.