Search Results pay_balance_types_tl




Overview
Explore in AI Chat

Overview

PAY_BALANCE_TYPES_TL is the translation table for balance type definitions within the Oracle Payroll (PAY) module. In Oracle E-Business Suite 12.1.1 and 12.2.2, balance types define the categories of accumulated values that the payroll engine maintains for employees and assignments, such as gross earnings, taxable wages, or deductions. The base definition of each balance type is stored in PAY_BALANCE_TYPES_F, while PAY_BALANCE_TYPES_TL holds the language-specific, translatable attributes—principally the balance name and the reporting name—for each defined balance type and each installed language.

The table resides in the HR schema and is classified as VALID in the ETRM repository. Because it carries only descriptive, language-dependent attributes that describe an existing business entity identified by BALANCE_TYPE_ID, the mined Data Vault classification identifies it as a standalone object, which maps naturally to the satellite modeling pattern: the descriptive context for a balance type hub keyed by the balance type identifier and language. This classification is a heuristic recommendation derived from the foreign-key structure rather than a normative warehouse design.

Key Information Stored

The table stores translated descriptive text for balance types, along with standard Oracle EBS audit and multi-language columns. The documented columns are:

  • BALANCE_TYPE_ID — Surrogate identifier of the parent balance type. Together with LANGUAGE it forms the primary key PAY_BALANCE_TYPES_TL_PK, linking each translation row to its base definition in PAY_BALANCE_TYPES_F.
  • LANGUAGE — The installed language code for the translated row, such as US or DE. It is the second component of the primary key and of every unique index.
  • BALANCE_NAME — The user-visible name of the balance type in the given language, displayed in payroll forms, element and balance setup, and balance inquiries.
  • REPORTING_NAME — An alternative label used in reports and extracts, typically shorter or formatted for printed output.
  • SOURCE_LANG — The language from which the row was originally entered or seeded, used by the translation framework when maintaining the multilingual repository.
  • ZD_EDITION_NAME — The edition indicator used by Oracle's edition-based redefinition (EBR) infrastructure in 12.2, present as the third component of the documented unique index.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard EBS audit columns recording who created and last modified each translation row and when.

The business-key candidate is the unique index PAY_BALANCE_TYPES_TL_PK on (BALANCE_TYPE_ID, LANGUAGE, ZD_EDITION_NAME), which guarantees that only one translation of a given balance type exists per language and edition. BALANCE_TYPE_ID itself is the foreign key to the base table, not a business key in the natural sense; it is the surrogate that carries identity across the multilingual pair.

Common Use Cases and Queries

The most frequent use of this table is joining translated names to the base balance type definition for reporting, extraction, and integration. A typical query retrieves balance types and their localized names for a chosen language:

  • Reporting: SELECT bt.balance_type_id, btt.balance_name, btt.reporting_name FROM pay_balance_types_f bt, pay_balance_types_tl btt WHERE bt.balance_type_id = btt.balance_type_id AND btt.language = USERENV('LANG');
  • Balance inquiries and payroll run diagnostics: resolve a balance type identifier captured in a payroll action or balance result into its user-facing name.
  • Data warehousing and BI extracts: populate multilingual dimension tables for payroll balances, using LANGUAGE to filter to a single reporting locale.
  • Ambiguity checks: detect balance types whose translations are missing by outer-joining from PAY_BALANCE_TYPES_F to PAY_BALANCE_TYPES_TL and testing for null BALANCE_NAME.
  • Multi-language reconciliation: verify that each balance type has a complete set of translations by grouping on BALANCE_TYPE_ID and counting distinct LANGUAGE values.

Related Objects

The table is tightly coupled to its base definition and to the shared EBS language infrastructure. The most significant related objects are:

  • PAY_BALANCE_TYPES_F — The base (non-translated) balance type definition table; joined on BALANCE_TYPE_ID.
  • PAY_BALANCE_TYPES_VL — The translated view that joins PAY_BALANCE_TYPES_F and PAY_BALANCE_TYPES_TL, exposing the current-language row; most forms and reports query this view rather than the tables directly.
  • FND_LANGUAGES — The installed-languages table; LANGUAGE in PAY_BALANCE_TYPES_TL corresponds to FND_LANGUAGES.LANGUAGE_CODE.
  • PAY_BALANCE_TYPES_B — Where used, the base-only table holding non-translatable attributes of the balance type, distinguished from the _F/_TL naming convention used in 12.2 multilingual objects.
  • PAY_BALANCE_DIMENSIONS and PAY_BALANCE_TYPES_TL consumers such as balance feeds and balance result tables (PAY_BALANCE_FEEDS_F, PAY_BALANCE_RESULTS) use the balance type identifier to attribute accumulated values.
  • FND_LANGUAGES_TL or related territory/language objects supply the display value for LANGUAGE in ad hoc reporting.

Custom code that writes balance types must always insert or update matching translation rows in PAY_BALANCE_TYPES_TL through the supported APIs; direct DML bypasses the translation framework and leaves orphaned or incomplete definitions in the multilingual repository.