Search Results hr_s_user_tables




Overview
Explore in AI Chat

Overview

HR_S_USER_TABLES is a Human Resources (PER) table in the HR schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It is documented as a retrofitted object, meaning it was introduced or realigned in support of legislative and localization extensions rather than as part of the original core HR data model. Functionally, the table holds user-defined table definitions — the configurable lookup and validation structures used by Oracle Payroll and HR legislative functionality to drive user-defined elements, including the parameters, ranges, and matching behaviour applied to those elements.

From a Data Vault modelling perspective, the FK metadata classifies this object as standalone (heuristic classification). That classification suggests it is best modelled as a hub or reference structure rather than as a dependent satellite or transactional link; it is a definitional entity that other legislative and user-defined data objects reference rather than a table that warehouses history for another business key. This distinction is useful when designing reporting layers or data warehouse extracts, because the table is a low-volume configuration object whose change history is governed by the standard WHO columns rather than by effective-dating.

Key Information Stored

The table contains 14 documented columns in the ETRM 12.2.2 physical schema. The most important are:

  • USER_TABLE_ID — the surrogate primary key and the value propagated to dependent tables. It is the FK target relationship column in HR_S_USER_TABLES.USER_TABLE_ID → PAY_USER_TABLES, indicating that this table participates in the user-table definitional chain.
  • BUSINESS_GROUP_ID — the operating unit / business group context, which scopes the user table definition to a specific enterprise grouping. This is a key filtering column for any multi-org-aware query.
  • LEGISLATION_CODE and LEGISLATION_SUBGROUP — the country and localization subgroup to which the user table applies, ensuring that statutory and local payroll rules are applied only within the correct jurisdiction.
  • USER_TABLE_NAME and USER_ROW_TITLE — the display name of the user-defined table and the title applied to each row presented to the user, providing the end-user-facing identity of the definition.
  • RANGE_OR_MATCH — controls whether the user table validates by range of values or by exact matching.
  • USER_KEY_UNITS — identifies the units in which the user key values are expressed, used during validation and formatting.
  • WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER — provide auditability and optimistic locking for concurrent maintenance.

The documented surrogate key is USER_TABLE_ID. Business-key candidates are not explicitly enumerated as unique indexes in the metadata, but the combination of BUSINESS_GROUP_ID with LEGISLATION_CODE, LEGISLATION_SUBGROUP, and USER_TABLE_NAME is the natural candidate for uniqueness in practice.

Common Use Cases and Queries

Typical use cases include auditing which user tables are defined for a given legislation, reporting the validation behaviour configured for payroll user-defined elements, and diagnosing validation errors that arise when a value falls outside a configured range.

SELECT user_table_id,
       user_table_name,
       legislation_code,
       legislation_subgroup,
       range_or_match
  FROM hr.hr_s_user_tables
 WHERE business_group_id = :p_business_group_id
   AND legislation_code  = :p_legislation_code;

A second common pattern joins the parent user table to identify which definitions originate from the seeded PAY_USER_TABLES catalogue:

SELECT h.user_table_id,
       h.user_table_name,
       p.user_table_name AS seeded_name
  FROM hr.hr_s_user_tables h,
       pay.pay_user_tables  p
 WHERE h.user_table_id = p.user_table_id;

Because OBJECT_VERSION_NUMBER is present, any maintenance interface or concurrent program performing DML must increment it to avoid lost updates.

Related Objects

  • PAY_USER_TABLES — referenced by HR_S_USER_TABLES.USER_TABLE_ID; the parent catalogue of user tables in the Payroll schema.
  • PAY_USER_ROWS / PAY_USER_COLUMNS — the row and column definitions that hang off a user table and are reached through USER_TABLE_ID.
  • PAY_USER_TABLE_FORMS — form-level bindings for user-defined tables.
  • FF_USER_TABLES and related Fast Formula user-table views — consume the same user_table_id to resolve formula validation.
  • HR_ALL_ORGANIZATION_UNITS — joined via BUSINESS_GROUP_ID to resolve the business group name in reports.
  • FND_TERRITORIES — joined via LEGISLATION_CODE to translate the legislation into a descriptive territory name.

Together these objects form the definitional spine of the user-defined table framework, with HR_S_USER_TABLES acting as the HR-side entry point into the Payroll user table catalogue.