Results for “pay_balance_types_vl”

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

Overview

PAY_BALANCE_TYPES_VL is a multilingual (VL) view owned by the APPS schema in Oracle E-Business Suite Payroll (PAY). It presents the definition of balance types — the fundamental units of accumulation in Oracle Payroll, such as gross earnings, tax withholding, and deduction balances — in the language of the current user session. The view exists because balance type definitions are stored in a language-independent base table and translated through a companion translation table; the VL view joins them so that translatable attributes such as BALANCE_NAME and REPORTING_NAME are returned in the session language while non-translatable attributes (currency, unit of measure, legislation, category, and so forth) come from the base table.

In Oracle EBS reporting and integration, this view is the standard, supported read interface for balance type metadata. Payroll reports, balance extraction routines, and third-party integration layers query it to resolve BALANCE_TYPE_ID values into human-readable names and to retrieve the UOM, currency, and legislation context needed to interpret accumulated balance amounts. Because it is a view rather than a table, it is query-only from a functional perspective.

Underlying Base Objects

The ETRM metadata documents the view as being defined over two referenced base objects, both exposed as synonyms in the APPS schema:

• PAY_BALANCE_TYPES — the language-independent base table holding the balance type identifier, business group, legislation code, currency, unit of measure, jurisdiction level, tax type, balance category, base balance type, input value, descriptive flexfield attributes, comments, and audit columns.
• PAY_BALANCE_TYPES_TL — the translation table holding the language-specific BALANCE_NAME and REPORTING_NAME keyed by BALANCE_TYPE_ID and LANGUAGE.

The join condition is B.BALANCE_TYPE_ID = T.BALANCE_TYPE_ID combined with T.LANGUAGE = USERENV('LANG'). The view therefore filters translation rows to the current session language, ensuring one row per balance type. The documented view text exposes B.ROWID as the ROW_ID pseudo-column, which allows reporting tools that expect a unique row identifier to treat the view as updatable context.

Key Columns

• BALANCE_TYPE_ID — the primary identifier of the balance type; the key used in all balance-related joins.
• BALANCE_NAME — the translated display name of the balance type, resolved for the session language.
• REPORTING_NAME — the translated name used on statutory and management reports; may differ from BALANCE_NAME.
• BUSINESS_GROUP_ID — the business group that owns the definition, driving the multi-tenant separation of payroll data.
• LEGISLATION_CODE and LEGISLATION_SUBGROUP — the legislative context in which the balance type is valid.
• CURRENCY_CODE and BALANCE_UOM — the currency and unit of measure (for example, money or hours) in which balances accumulate.
• ASSIGNMENT_REMUNERATION_FLAG — indicates whether the balance participates in assignment remuneration calculations.
• BALANCE_CATEGORY_ID, BASE_BALANCE_TYPE_ID, INPUT_VALUE_ID — classification and lineage references used in balance calculation and feed logic.
• JURISDICTION_LEVEL and TAX_TYPE — tax-reporting attributes.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — standard concurrency and audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — the descriptive flexfield segment values.

Common Use Cases and Queries

Typical uses include resolving balance identifiers in payroll reports, building balance definition extracts for data warehousing, and validating balance setup before running payroll processes. A simple lookup follows:

• SELECT balance_type_id, balance_name, reporting_name, balance_uom, currency_code FROM pay_balance_types_vl WHERE balance_type_id = :p_id;
• SELECT balance_type_id, balance_name, legislation_code FROM pay_balance_types_vl WHERE legislation_code = 'US' AND business_group_id = :p_bg ORDER BY balance_name;
• SELECT t.balance_name, t.balance_uom, t.jurisdiction_level FROM pay_balance_types_vl t WHERE t.tax_type IS NOT NULL ORDER BY t.balance_name;

Because the view enforces the USERENV('LANG') predicate, queries automatically return translated names for the connected user's language and require no additional language filter. Joins to balance value tables via BALANCE_TYPE_ID complete the reporting picture by pairing definitions with accumulated amounts.