Search Results pay_user_columns




Overview
Explore in AI Chat

Overview

PAY_USER_COLUMNS is a core Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the column definitions for user-defined tables, allowing implementers and administrators to extend Payroll with custom data structures without modifying seeded Oracle schema objects. Each row describes a single column belonging to a user-defined table, capturing its name, associated formula, and legislative context. In this way the table acts as the metadata backbone for the flexible user-defined table framework that supports customer-specific payroll and HR extensions.

Under the Data Vault classification heuristic mined from the foreign-key structure, PAY_USER_COLUMNS is best modeled as a satellite-leaning entity. It carries descriptive attributes keyed by a surrogate identifier and references a parent table, which is consistent with satellite behaviour rather than that of a hub or link.

Key Information Stored

The table contains 14 documented columns. The most significant are as follows:

The surrogate key distinguishes physical identity, while PAY_USER_COLUMNS_UK2 provides the documented business-key candidate for uniqueness across the name, table, business group and legislation combination.

Common Use Cases and Queries

Typical use cases include retrieving the column layout for a given user-defined table, validating that a column name exists within a business group, and reporting custom structures that carry legislative dependencies. A representative query joins the column definitions to their parent tables:

SELECT uc.user_column_id, uc.user_column_name,
       ut.user_table_name, uc.legislation_code
FROM   pay_user_columns uc,
       pay_user_tables  ut
WHERE  uc.user_table_id = ut.user_table_id
AND    uc.business_group_id = :p_bg_id
ORDER BY ut.user_table_name, uc.user_column_name;

Developers frequently embed such queries in concurrent programs and PL/SQL to drive dynamic DML against user-defined table instances stored in PAY_USER_COLUMN_INSTANCES_F.

Related Objects

  • PAY_USER_TABLES — parent table; join on PAY_USER_COLUMNS.USER_TABLE_ID = PAY_USER_TABLES.USER_TABLE_ID.
  • PAY_USER_COLUMN_INSTANCES_F — child table storing per-record column instance values; join on USER_COLUMN_ID.
  • HR_S_USER_COLUMNS — secure (HRMS) view over this table filtered by security profile.
  • HR_S_USER_COLUMN_INSTANCES_F — secure view over the corresponding instance data.
  • PAY_USER_COLUMNS_PK / PAY_USER_COLUMNS_UK2 — primary and unique indexes enforcing identity and business keys.