Search Results hr_s_user_columns




Overview
Explore in AI Chat

Overview

The Oracle EBS table HR.HR_S_USER_COLUMNS resides in the HR schema and belongs to the PER – Human Resources product module. The ETRM record carries a concise description marking the object as Retrofitted, indicating it was introduced or re-engineered during an EBS upgrade path (notably the 12.1.1 to 12.2.2 transition) rather than being part of the original schema lineage. In the EBS data model, HR_S_USER_COLUMNS functions as a supporting definition table that links user-defined column metadata to the underlying user table and formula framework used by Oracle Payroll and related HR/payroll processing engines. It holds the "S" prefix convention typical of shadow or setup-tier objects that mirror configuration data consumed by runtime engines.

The Data Vault classification derived heuristically from the foreign key structure is standalone. As a modeling suggestion, this means the table neither behaves strictly as a hub (no uniquely identified core business entity) nor as a dependent satellite of a single parent, but rather as an independent reference structure whose relationships are expressed through two foreign keys pointing outward. Modelers should treat it as a reference/setup entity rather than a transactional fact.

Key Information Stored

The documented physical schema for ETRM 12.2.2 reports 13 columns under owner HR. The most significant are:

No unique index columns are separately documented in the ETRM extract beyond the primary-key surrogate; USER_COLUMN_NAME combined with USER_TABLE_ID and BUSINESS_GROUP_ID is the practical business-key candidate.

Common Use Cases and Queries

Typical uses include auditing user-defined column definitions, reconciling HR-side column metadata against the Payroll master tables, and diagnosing formula-driven payroll elements. A representative query pattern:

  • Filter by BUSINESS_GROUP_ID and LEGISLATION_CODE to scope results to a single legal employer and territory.
  • Join to PAY_USER_COLUMNS on USER_COLUMN_ID to retrieve authoritative Payroll attributes.
  • Join to PAY_USER_TABLES on USER_TABLE_ID to resolve the owning user table.
  • Join to formula tables on FORMULA_ID to identify calculation logic behind a column.
  • Report on audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY) to trace configuration changes.

Example skeleton: SELECT c.USER_COLUMN_NAME, t.USER_TABLE_NAME FROM HR.HR_S_USER_COLUMNS c JOIN PAY.PAY_USER_TABLES t ON t.USER_TABLE_ID = c.USER_TABLE_ID WHERE c.BUSINESS_GROUP_ID = :bg_id;

Related Objects

The principal dependencies, grounded in the documented foreign keys, are:

  • PAY_USER_COLUMNS — referenced via USER_COLUMN_ID; the Payroll master definition of the same column.
  • PAY_USER_TABLES — referenced via USER_TABLE_ID; the parent user table container.
  • PER_BUSINESS_GROUPS — implicit relationship through BUSINESS_GROUP_ID.
  • FF_FORMULAS / FF_FORMULA_TYPES (Fast Formula) — implicit relationship through FORMULA_ID.
  • FND_LEGAL_ENTITIES / legislative data group tables — implicit through LEGISLATION_CODE and LEGISLATION_SUBGROUP.

Because the object is flagged standalone, it has no child tables in the mined FK graph; dependents are inferred from business usage rather than enforced referential constraints.