Search Results pay_us_tax_balances_pk

Overview

HR.PAY_US_TAX_BALANCES is a reference and configuration table within the Oracle E-Business Suite US Payroll module. It holds the definitive list of tax balances used by US Payroll Tax Balance reports, mapping each balance to a specific tax type, a balance category, and an employee-versus-employer designation. The table is owned by the HR schema and registered under FND Design Data as PAY.PAY_US_TAX_BALANCES. It resides in the APPS_TS_SEED tablespace, reflecting its role as seeded setup data rather than high-volume transactional data. The object carries a VALID status in the ETRM repository and is documented identically across Release 12.1.1 and 12.2.2, with the 12.2.2 physical schema adding the ZD_EDITION_NAME column used by the editioning infrastructure.

The table sits at the intersection of the tax type definition model and the tax reporting model. It is referenced directly by PAY_US_TAX_REPORT_BALANCES through the TAX_BALANCE_ID foreign key, meaning every reported tax balance row traces back to a definition registered here. Heuristic analysis of the foreign key structure classifies this object as satellite-leaning: it has a single, stable foreign key outward to PAY_US_TAX_TYPES, and its descriptive attributes (category, reporting name, employee/employer indicator) are supplementary context rather than independent relationships. Under a Data Vault modeling suggestion, it would most naturally be modelled as a satellite attached to the tax type hub, with the unique key acting as the business key.

Key Information Stored

The table comprises seven documented columns. The most significant are:

• TAX_BALANCE_ID — System-generated surrogate primary key, enforced by the unique index PAY_US_TAX_BALANCES_PK. This is the column referenced by child tables.
• TAX_TYPE_ID — Foreign key to PAY_US_TAX_TYPES. Identifies which US tax (for example a specific federal, state, or local tax) the balance belongs to.
• BALANCE_CATEGORY_CODE — A code indicating the balance category, such as wages, tips, or tax withheld. Part of the business key.
• EE_OR_ER_CODE — Indicates whether the tax is an Employee tax or an Employer tax. Part of the business key.
• USER_REPORTING_NAME — The display name for the tax balance as it appears on reports.
• BALANCE_TYPE_ID — Identifies the underlying balance definition.
• ZD_EDITION_NAME — Editioning column present in the 12.2.2 physical schema; included in both unique indexes.

Two unique indexes define the business keys. PAY_US_TAX_BALANCES_PK covers TAX_BALANCE_ID and ZD_EDITION_NAME. PAY_US_TAX_BALANCES_UK2 covers BALANCE_CATEGORY_CODE, EE_OR_ER_CODE, TAX_TYPE_ID, and ZD_EDITION_NAME. The user's search term, pay_us_tax_balances_uk2, corresponds directly to this second index — the composite uniqueness constraint that prevents duplicate balance definitions for the same category, employee/employer side, and tax type within an edition.

Common Use Cases and Queries

The primary use case is reporting: identifying which balances are available for each US tax report and how they should be labelled. A typical retrieval joins the table to its parent tax type:

• SELECT tb.TAX_BALANCE_ID, tb.USER_REPORTING_NAME, tb.BALANCE_CATEGORY_CODE, tb.EE_OR_ER_CODE FROM HR.PAY_US_TAX_BALANCES tb WHERE tb.EE_OR_ER_CODE = 'EE';
• Joining to HR.PAY_US_TAX_TYPES on TAX_TYPE_ID to resolve the tax name.
• Joining to HR.PAY_US_TAX_REPORT_BALANCES on TAX_BALANCE_ID to reconcile reported amounts against their definitions.
• Diagnosing duplicate or missing balance definitions by grouping on BALANCE_CATEGORY_CODE and EE_OR_ER_CODE.

Related Objects

• PAY_US_TAX_TYPES — Parent table; joined on TAX_TYPE_ID.
• PAY_US_TAX_REPORT_BALANCES — Child table referencing TAX_BALANCE_ID; the transactional counterpart of this definition table.
• PAY_US_TAX_BALANCES (APPS synonym) — The APPS-layer synonym used by application code and reports.
• PAY_US_TAX_BALANCES (PUBLIC synonym) — Public access point for the same object.
• PAY_US_TAX_BALANCES_PK / UK2 / FK1 — Supporting unique and non-unique indexes on TAX_BALANCE_ID, the business key, and TAX_TYPE_ID respectively.