Results for “range_or_match_code”

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

Overview

PAYBV_USER_TABLE is a read-only view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) product. Its name follows the established "BV" convention used across Oracle EBS applications to identify Business Intelligence (Discoverer) base views — simplified, denormalized projections of underlying transactional tables intended for ad hoc reporting and workbook development. As documented in the ETRM metadata for 12.2.2, the view is described as the "Discoverer base view for pay_user_tables," and its status is VALID in both 12.1.1 and 12.2.2 releases.

The view presents configuration-level metadata about user-defined tables (also known as "user tables" or "key flexfield-style lookup tables") that Oracle Payroll and its localization extensions use to drive validation, list-of-values behavior, and business-group- or legislation-specific data capture. It is not a transactional view; it exposes the definition and structural characteristics of each user table rather than the rows those tables contain. In Oracle EBS reporting and integration scenarios, PAYBV_USER_TABLE is typically joined to user table value views and to other HR/Payroll BI views to build secure, legislation-aware Discoverer workbooks.

Underlying Base Objects

Per the documented ETRM metadata, PAYBV_USER_TABLE is defined over two referenced objects:

• PAY_USER_TABLES (SYNONYM) — the primary base object. The synonym resolves to the APPS.PAY_USER_TABLES table, which is the master definition table for user-defined payroll tables. The view aliases this as PUT and inherits its columns, including USER_TABLE_NAME, RANGE_OR_MATCH, USER_KEY_UNITS, USER_ROW_TITLE, USER_TABLE_ID, BUSINESS_GROUP_ID, and the standard WHO audit columns.
• HR_BIS (PACKAGE) — a helper package that supplies decoding and security functions. Two functions are invoked: BIS_DECODE_LOOKUP translates coded lookup values (such as RANGE_MATCH and DATA_TYPE) into display names, and GET_LEGISLATION_CODE, GET_SEC_PROFILE_BG_ID are used to evaluate business-group and legislation security.

The view is defined with the WITH READ ONLY clause, so it cannot be used to insert or update PAY_USER_TABLES data. It does not itself expose the rows stored inside a user table; those are accessed through separate value/row views that reference the user table by its USER_TABLE_ID or USER_TABLE_NAME.

Key Columns

• USER_TABLE_NAME — the developer-facing name of the user table; the natural key used in joins from user table value views.
• RANGE_OR_MATCH — decoded (display-value) form of the RANGE_OR_MATCH code, produced via BIS_DECODE_LOOKUP('RANGE_MATCH', ...). Indicates the validation mode (for example, range-based versus exact-match lookup).
• RANGE_OR_MATCH_CODE — the underlying coded value of RANGE_OR_MATCH, preserved for filtering and integration.
• USER_KEY_UNITS / USER_KEY_UNITS_CODE — the decoded and coded forms of the key units definition, decoded with BIS_DECODE_LOOKUP('DATA_TYPE', ...).
• USER_ROW_TITLE — the descriptive title shown to end users when rows of the table are presented.
• LEGISLATION_CODE / LEGISLATION_SUBGROUP — localization qualifiers used to scope the table to a country or regulatory subgroup.
• USER_TABLE_ID, BUSINESS_GROUP_ID — surrogate keys used for relational joins and business-group partition.
• BG_SECURITY_FLAG_CODE — a computed 'Y'/'N' flag derived from a CASE expression that evaluates the user's security profile business group and legislation against the row's BUSINESS_GROUP_ID and LEGISLATION_CODE. This is the primary row-level security mechanism for Discoverer workbooks.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical scenarios include identifying which user tables exist for a legislation, confirming the range/match behavior of a lookup, and presenting a secured list of user tables in a Discoverer or BI Publisher report.

SELECT user_table_name,
       range_or_match,
       user_key_units,
       user_row_title
  FROM apps.paybv_user_table
 WHERE bg_security_flag_code = 'Y'
   AND legislation_code = 'US'
 ORDER BY user_table_name;

To inspect the coded versus decoded values for a specific table:

SELECT user_table_name,
       range_or_match_code,
       range_or_match,
       user_key_units_code,
       user_key_units
  FROM apps.paybv_user_table
 WHERE user_table_name = :p_table_name;

Because the view is read-only and decoded in the database, it is the recommended join target when building legislation- and business-group-aware reports over user tables, rather than querying PAY_USER_TABLES directly.