Search Results base_user_table_name




Overview
Explore in AI Chat

Overview

PAY_USER_TABLES_VL is a validated APPS-owned translation (VL) view within the Oracle E-Business Suite Payroll (PAY) product module. It presents the user-defined payroll tables maintained in Oracle Payroll in a language-sensitive form, joining the base definition stored in PAY_USER_TABLES with the corresponding translated rows held in PAY_USER_TABLES_TL. The "_VL" suffix indicates that the view restricts the translated side to the session's current language, resolved at runtime through USERENV('LANG'), so that users querying the view automatically see the description fields in their own language while the underlying identifiers remain unchanged. In Oracle EBS 12.1.1 and 12.2.2, this view forms the supported, read-oriented interface for reporting, integrations, and extensions that need to retrieve user table metadata without direct reference to the multi-language tables. Because it is a view rather than a table, it carries no storage of its own and inherits the row-level security and business group partitioning defined on the base entities.

Underlying Base Objects

Per the documented ETRM metadata for 12.2.2, the view is owned by APPS and references two base objects through synonyms: PAY_USER_TABLES and PAY_USER_TABLES_TL. PAY_USER_TABLES holds the language-independent definition of each user table, including its business group, legislation, matching rules, and key units. PAY_USER_TABLES_TL holds the translatable columns, principally the user table name and row title, one row per installed language. The view text joins these two objects on USER_TABLE_ID and applies the predicate TL.LANGUAGE = USERENV('LANG'), returning exactly one translated row per user table for the current session language. The view also exposes the untranslated name and title from the base table, aliased as BASE_USER_TABLE_NAME and BASE_USER_ROW_TITLE, which allows reports to compare the base and translated values when required.

Key Columns

  • ROW_ID — the ROWID of the PAY_USER_TABLES row, providing a stable physical identifier for the record.
  • USER_TABLE_ID — the primary surrogate key linking the base definition and its translations; the primary join column for the view.
  • BUSINESS_GROUP_ID — the business group that owns the user table, governing visibility in multi-organization environments.
  • LEGISLATION_CODE and LEGISLATION_SUBGROUP — the legislation and subgroup context under which the user table applies.
  • RANGE_OR_MATCH — indicates whether the user table is defined as a range-based or match-based structure.
  • USER_KEY_UNITS — the key units associated with the user table definition.
  • BASE_USER_TABLE_NAME and BASE_USER_ROW_TITLE — the untranslated values from PAY_USER_TABLES.
  • USER_TABLE_NAME and USER_ROW_TITLE — the translated values from PAY_USER_TABLES_TL for the session language.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns inherited from PAY_USER_TABLES.

Common Use Cases and Queries

The view is typically consumed by payroll configuration reports, data extraction routines, and integration components that must resolve user table names in the operator's language. A representative query retrieves all user tables for a given business group and legislation:

  • SELECT user_table_id, user_table_name, user_row_title, legislation_code, range_or_match FROM apps.pay_user_tables_vl WHERE business_group_id = :p_bg_id AND legislation_code = :p_legislation ORDER BY user_table_name;
  • SELECT user_table_id, base_user_table_name, user_table_name FROM apps.pay_user_tables_vl WHERE user_table_name <> base_user_table_name; — to identify rows where a translation differs from the base value.
  • SELECT COUNT(*) FROM apps.pay_user_tables_vl WHERE range_or_match = 'R'; — to quantify range-based user tables in the current language context.

Because the view filters on USERENV('LANG'), concurrent programs and reports should set the language of the session appropriately to obtain deterministic results across multilingual deployments.