Search Results pay_user_tables_pk




Overview
Explore in AI Chat

Overview

PAY_USER_TABLES is a Payroll (PAY) product table owned by the HR schema that stores the definitions of user-defined tables. Within Oracle EBS 12.1.1 and 12.2.2, it acts as the metadata registry for customer-created "user tables," which allow implementers to define their own data structures and populate them with payroll-specific rows without modifying delivered schema objects. Each record in PAY_USER_TABLES describes one such user table, including its name, the business group and legislation it belongs to, and behavioral attributes such as whether it is populated by range or match logic. In the documented 12.2.2 physical schema the table contains 15 columns and carries two unique indexes: PAY_USER_TABLES_PK on USER_TABLE_ID (plus ZD_EDITION_NAME) and PAY_USER_TABLES_UK2 on USER_TABLE_NAME, BUSINESS_GROUP_ID, and LEGISLATION_CODE (plus ZD_EDITION_NAME). Based on its FK structure, the table is a hub-leaning candidate in a Data Vault model: it is referenced by multiple dependent tables through USER_TABLE_ID but does not itself point to a parent, making it a natural hub for user-defined table metadata.

Key Information Stored

The surrogate primary key is USER_TABLE_ID, which uniquely identifies each user table definition and is the column propagated to all child tables. The business-key candidate captured by PAY_USER_TABLES_UK2 combines USER_TABLE_NAME with BUSINESS_GROUP_ID and LEGISLATION_CODE, so a table name need only be unique within a business group and legislation context. USER_TABLE_NAME holds the user-assigned identifier for the table, while BUSINESS_GROUP_ID scopes the definition to a specific business group and LEGISLATION_CODE scopes it to a country or regional legislative context. LEGISLATION_SUBGROUP provides a finer legislative partition where applicable. RANGE_OR_MATCH and USER_KEY_UNITS describe how rows are keyed and grouped, and USER_ROW_TITLE supplies the display title used for rows. Standard audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — record who created and last changed each definition, and OBJECT_VERSION_NUMBER and ZD_EDITION_NAME support optimistic locking and the 12.2 online patching editioning model.

Common Use Cases and Queries

Typical uses include identifying which user tables exist for a business group, inspecting the columns and rows stored beneath them, and joining definitions to their populated content for payroll reporting. A common lookup resolves a table name to its identifier:

  • SELECT user_table_id, user_table_name, legislation_code FROM hr.pay_user_tables WHERE user_table_name = :name AND business_group_id = :bg_id;
  • Enumerating columns belonging to a user table: SELECT * FROM hr.pay_user_columns WHERE user_table_id = :id;
  • Retrieving populated rows: SELECT * FROM hr.pay_user_rows_f WHERE user_table_id = :id;

These patterns support validation of setup before payroll runs, migration audits comparing defined versus populated tables, and reporting that exposes user-table content to downstream extracts.

Related Objects

The most significant dependent objects are the child tables that reference PAY_USER_TABLES.USER_TABLE_ID. PAY_USER_COLUMNS defines the columns of each user table, and PAY_USER_ROWS_F holds the actual row-level data. GHR_MASS_SALARIES references user tables in mass-salary processing. The HR_S_* shadow tables — HR_S_USER_TABLES, HR_S_USER_COLUMNS, and HR_S_USER_ROWS_F — mirror the base objects for the 12.2 editioning model. Together these objects form the complete user-table framework centered on PAY_USER_TABLES as the hub.