Results for “pay_user_rows_x”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PAY_USER_ROWS_X is a date-effective (DTE) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the PAY (Payroll) product family and is documented as a "Date-effective view" in the ETRM repository. The view presents the currently effective rows from the Payroll user-defined row/column (quickpick) definitions used by user tables. In Oracle Payroll, user tables allow implementers and administrators to define lookup-style datasets whose rows are selected by name or by value range. The base information is stored in PAY_USER_ROWS_F, a date-tracked ("_F") table, while PAY_USER_ROWS_X exposes only those records whose effective date range encloses the current system date (TRUNC(SYSDATE)). This design ensures that any form, concurrent program, or integration query that reads the view automatically sees the active definition on the current day without having to explicitly filter date-tracked columns. This behavior is central to reporting integrity: a report run on a given date reflects the valid rows for that date, rather than a historic snapshot.

Underlying Base Objects

The view is defined entirely over a single base object, PAY_USER_ROWS_F (referenced through a synonym). The metadata confirms this relationship, and the view text is a straightforward filtered projection of the columns of PAY_USER_ROWS_F. No joins to other Payroll tables are present in the view definition itself; relationships to user tables and legislation are implied by foreign-key style columns (for example USER_TABLE_ID and LEGISLATION_CODE) rather than expressed in the SQL. Because the view is read-only by nature, all DML against user-row definitions must be performed against the underlying _F table through supported Payroll forms or APIs.

Key Columns

• USER_ROW_ID — Primary identifier for the user row definition.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date-tracked range that governs visibility in this view.
• BUSINESS_GROUP_ID — Identifies the Business Group (HR security organization) that owns the row.
• LEGISLATION_CODE — Legislative context for which the row is defined.
• USER_TABLE_ID — The user table to which the row belongs; this is the foreign key most frequently used in reporting.
• ROW_LOW_RANGE_OR_NAME and ROW_HIGH_RANGE — Define the low and high values of a value-range row, or the name of a discrete row.
• DISPLAY_SEQUENCE — Ordering used when presenting rows to users.
• LEGISLATION_SUBGROUP — Further subdivision of legislative grouping.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard audit columns.

Common Use Cases and Queries

The most common use is to retrieve the rows currently valid for a specific user table. Because the view already filters on SYSDATE, callers normally need only constrain by USER_TABLE_ID:

• List active rows for a user table:

SELECT user_row_id,
       user_table_id,
       row_low_range_or_name,
       row_high_range,
       display_sequence,
       legislation_code
FROM   apps.pay_user_rows_x
WHERE  user_table_id = :p_user_table_id
ORDER  BY display_sequence;

• Filter by legislation and business group for cross-reference reporting:

SELECT ur.user_row_id, ur.user_table_id, ur.row_low_range_or_name
FROM   apps.pay_user_rows_x ur
WHERE  ur.legislation_code = :p_legislation
AND    ur.business_group_id = :p_bg_id;

• Effective-dated audit trail: Because date effectiveness hides superseded rows, users who need historic data must query PAY_USER_ROWS_F directly and supply their own date predicate. This is an important distinction for payroll audits, since a user row active last year may not appear in PAY_USER_ROWS_X today.

In integration scenarios, the view is frequently joined to PAY_USER_TABLES (or its business-group equivalent) to resolve the user table name and to Payroll element/input value definitions that reference the table via USER_TABLE_ID. The 12.1.1 and 12.2.2 implementations share an identical view definition and column list, so queries and interfaces are portable across both releases.