Search Results pay_us_tax_balances_pk

Overview

PAY_US_TAX_BALANCES is a United States Payroll tax configuration table owned by the HR schema in Oracle E-Business Suite (validated against 12.1.1 and 12.2.2). It stores the tax balance definitions used by US Payroll Tax balance reporting, acting as the bridge between a specific tax type and the balance categories that must be accumulated, reported, and eventually remitted. Each row defines one reportable tax balance — for example, an employee-level federal withholding balance or an employer-level Social Security balance — and identifies the balance category and the employee/employer indicator that distinguish it.

Because the table holds relatively stable reference/definition data rather than transactional payroll results, the ETRM relationship heuristic classifies it as satellite-leaning in Data Vault terms. As a modeling suggestion only, this implies the table's descriptive attributes (reporting name, balance type, employee/employer designation) are best treated as attributes hanging off a business key composed of the balance category, the employee/employer code, and the tax type.

Key Information Stored

The documented physical schema comprises seven columns in the HR schema. The most significant are:

• TAX_BALANCE_ID — the surrogate primary key (PAY_US_TAX_BALANCES_PK). This is the internal identifier referenced by downstream reporting tables.
• TAX_TYPE_ID — foreign key to PAY_US_TAX_TYPES; identifies which tax (e.g., federal income tax, FICA, state tax) the balance belongs to.
• BALANCE_CATEGORY_CODE — the balance category that the row represents; a business-key component.
• EE_OR_ER_CODE — distinguishes employee-level from employer-level balances; the second business-key component.
• USER_REPORTING_NAME — the user-facing label printed on tax balance reports.
• BALANCE_TYPE_ID — references the balance type definition governing how the balance is calculated.
• ZD_EDITION_NAME — the editioning column used by Oracle's Edition-Based Redefinition (EBR), present in both the primary and unique indexes for online patching support.

The business key is enforced by the unique index PAY_US_TAX_BALANCES_UK2 across BALANCE_CATEGORY_CODE, EE_OR_ER_CODE, TAX_TYPE_ID, and ZD_EDITION_NAME. The distinction between the surrogate key (TAX_BALANCE_ID) and this composite business key matters for both DBA troubleshooting and any integration that must match balances without relying on the generated ID.

Common Use Cases and Queries

The table is most commonly queried when configuring, auditing, or reconciling US Payroll tax reporting. Typical patterns include:

• Listing all reportable balances for a given tax type, joining to PAY_US_TAX_TYPES to resolve the tax name.
• Separating employee and employer balances using the EE_OR_ER_CODE column.
• Resolving a TAX_BALANCE_ID surfaced on a tax report back to its category and tax type.

A representative query:

SELECT b.tax_balance_id, t.tax_name, b.balance_category_code,
b.ee_or_er_code, b.user_reporting_name
FROM hr.pay_us_tax_balances b, hr.pay_us_tax_types t
WHERE b.tax_type_id = t.tax_type_id
AND b.balance_category_code = :category;

Because EBR editioning applies, queries against the effective (run) edition should not filter ZD_EDITION_NAME manually; Oracle resolves it automatically for the active edition.

Related Objects

• PAY_US_TAX_TYPES — parent reference table; join on PAY_US_TAX_BALANCES.TAX_TYPE_ID = PAY_US_TAX_TYPES.TAX_TYPE_ID.
• PAY_US_TAX_REPORT_BALANCES — child table that references this object; join on PAY_US_TAX_REPORT_BALANCES.TAX_BALANCE_ID = PAY_US_TAX_BALANCES.TAX_BALANCE_ID. This is the principal consumer, holding the reported balance amounts.
• Balance type and balance category definitions in the PAY balance model, indirectly linked through BALANCE_TYPE_ID and BALANCE_CATEGORY_CODE.
• US Payroll tax reporting concurrent programs and reports that read PAY_US_TAX_BALANCES to determine which balances to print.

Together these objects form the definition-to-result chain for US Payroll tax balance reporting: tax types and balance categories define reportable balances here, and PAY_US_TAX_REPORT_BALANCES captures the resulting reported figures.