Results for “pay_balance_types_uk2”

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

Overview

HR.PAY_BALANCE_TYPES is the core definition table for payroll balances in Oracle E-Business Suite, owned by the HR schema and registered under FND Design Data as PAY.PAY_BALANCE_TYPES. It stores the fundamental attributes of a balance — its name, unit of measure, currency, and legislation context — but deliberately excludes the balance dimensions used to derive specific values at runtime. Balance dimensions, feed definitions, and calculation inputs are held in separate tables that reference this one through BALANCE_TYPE_ID.

From a Data Vault modeling perspective, this object is best classified as hub-leaning: it carries a system-generated surrogate key (BALANCE_TYPE_ID) that anchors a stable business concept — the payroll balance definition — and is referenced by multiple downstream dependent tables. The heuristic classification is a modeling suggestion rather than a documented Oracle construct; implementers designing a warehouse layer should treat BALANCE_TYPE_ID as the natural hub key and treat descriptive and legislative attributes as satellite candidates.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and is validated and current in both EBS 12.1.1 and 12.2.2. In 12.2.2 the physical schema documents 44 columns, including the ZD_EDITION_NAME column introduced by the editioning framework.

Key Information Stored

The most operationally significant columns include:

• BALANCE_TYPE_ID — the system-generated surrogate primary key, defined by PAY_BALANCE_TYPES_PK. This is the value propagated to every dependent table.
• BALANCE_NAME — the user-visible name of the balance. Together with BUSINESS_GROUP_ID and LEGISLATION_CODE it forms the unique business key PAY_BALANCE_TYPES_UK2.
• BUSINESS_GROUP_ID — foreign key to HR_ORGANIZATION_UNITS, scoping the balance to a specific business group (enterprise).
• LEGISLATION_CODE — foreign key to FND_TERRITORIES, identifying the legislative context of the balance.
• LEGISLATION_SUBGROUP — further qualifies the legislation for seeded (startup) data.
• CURRENCY_CODE — foreign key to FND_CURRENCIES, applicable when the balance unit is monetary.
• BALANCE_UOM — the unit of measure, for example Money, Hours, or Days.
• REPORTING_NAME — an alternative name used for reporting output.
• ASSIGNMENT_REMUNERATION_FLAG — indicates whether the balance serves assignment remuneration or third-party payments.
• BALANCE_CATEGORY_ID and BASE_BALANCE_TYPE_ID — classify the balance and optionally relate it to a base balance.
• INPUT_VALUE_ID — links to the input value definition; indexed non-uniquely by PAY_BALANCE_TYPES_N1.
• JURISDICTION_LEVEL and TAX_TYPE — support tax and jurisdiction-related balances.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — standard concurrency and audit columns.

The surrogate key BALANCE_TYPE_ID is distinct from the business-key candidates captured in the unique indexes; the PK and UK2 indexes reside in APPS_TS_TX_IDX.

Common Use Cases and Queries

Typical scenarios include validating that a balance exists before defining a feed, reporting all balances for a legislation, and identifying remuneration-related balances. A representative query lists balances for a business group:

• SELECT balance_type_id, balance_name, balance_uom, reporting_name FROM pay_balance_types WHERE business_group_id = :p_bg_id AND legislation_code = :p_leg ORDER BY balance_name;
• SELECT bt.balance_name, bc.classification_name FROM pay_balance_types bt, pay_balance_classifications bc WHERE bt.balance_type_id = bc.balance_type_id AND bt.balance_uom = 'Money';
• SELECT balance_type_id, balance_name FROM pay_balance_types WHERE assignment_remuneration_flag = 'Y';

Because the table is a definition store, queries are predominantly descriptive lookups and joins into the balance value and feed tables rather than high-volume transactional reads.

Related Objects

The following tables reference HR.PAY_BALANCE_TYPES through BALANCE_TYPE_ID and are the primary objects to consider when extending or reporting on balance definitions:

• PAY_BALANCE_CLASSIFICATIONS — references BALANCE_TYPE_ID; holds classification assignments per balance.
• PAY_BALANCE_FEEDS_F — references BALANCE_TYPE_ID; defines balance feeds that contribute to or consume the balance.
• PAY_DEFINED_BALANCES — references BALANCE_TYPE_ID; holds user-defined balance data.
• PAY_GROSSUP_BAL_EXCLUSIONS — references BALANCE_TYPE_ID; identifies balances excluded from gross-up processing.
• HR_ORGANIZATION_UNITS — referenced by BUSINESS_GROUP_ID.
• FND_TERRITORIES — referenced by LEGISLATION_CODE.
• FND_CURRENCIES — referenced by CURRENCY_CODE.

These relationships make PAY_BALANCE_TYPES a hub-like definition point within the payroll balance sub-model, with dependent tables supplying dimensions, feeds, and calculated values.