Search Results pay_us_tax_types_pk

Overview

HR.PAY_US_TAX_TYPES is a seed-data reference table in the Oracle E-Business Suite HR schema that holds the enumerated list of United States tax types consumed by US Payroll Tax Balance reporting. It is registered against FND Design Data PAY.PAY_US_TAX_TYPES and resides in the APPS_TS_SEED tablespace, confirming its role as a seeded, low-volatility lookup rather than transactional data. Its status is VALID in both the 12.1.1 and 12.2.2 codelines, and the object is referenced by the APPS and PUBLIC synonyms — a standard EBS pattern that allows application code and ad-hoc SQL to address the table without schema qualification.

From a dimensional-modeling perspective, the mined relationship data classifies PAY_US_TAX_TYPES as hub-leaning. This is a heuristic suggestion rather than a delivered Data Vault construct: the table behaves as a durable business-key hub because it is the target of an inbound foreign key from a fact-like table (PAY_US_TAX_BALANCES) and carries a stable, unique business identifier. Analysts building a warehouse layer over EBS Payroll should treat TAX_TYPE_CODE as the natural business key and TAX_TYPE_ID as the surrogate.

Key Information Stored

The documented physical schema contains six columns. The most significant are:

• TAX_TYPE_ID — System-generated primary key. It is the surrogate identifier and the column joined from dependent tables.
• TAX_TYPE_CODE — The tax type code. This is the business-key candidate, enforced by the unique index PAY_US_TAX_TYPES_UK2.
• TAX_DOMAIN_CODE — Indicates whether the tax type is a federal, state, or local tax. This is the primary partitioning attribute for jurisdictional reporting.
• EE_ER_CODE — Code indicating whether the tax type is an Employee tax, an Employer tax, or both. It governs the split between employee withholding and employer liability amounts.
• LIMIT_TAX_FLAG — Indicates whether the tax type is a limit tax, i.e. a wage-base-capped tax such as Social Security or FUTA.
• ZD_EDITION_NAME — The editioning column introduced by the 12.2 online patching architecture (Edition-Based Redefinition), which forms part of the composite unique keys in the 12.2.2 schema.

Note the version divergence: in 12.1.1 the primary key is the single column TAX_TYPE_ID and the unique key PAY_US_TAX_TYPES_UK2 covers TAX_TYPE_CODE alone. In 12.2.2, ZD_EDITION_NAME is appended to both indexes (PAY_US_TAX_TYPES_PK on TAX_TYPE_ID plus ZD_EDITION_NAME; PAY_US_TAX_TYPES_UK2 on TAX_TYPE_CODE plus ZD_EDITION_NAME). Queries written for 12.2 must therefore account for edition rows.

Common Use Cases and Queries

The predominant use case is decode-and-label reporting: joining tax balances to this lookup to obtain human-readable domain, employee/employer, and limit-tax attributes. A typical join resolves the surrogate key:

SELECT b.TAX_TYPE_ID,
       t.TAX_TYPE_CODE,
       t.TAX_DOMAIN_CODE,
       t.EE_ER_CODE,
       t.LIMIT_TAX_FLAG
FROM   HR.PAY_US_TAX_BALANCES b,
       HR.PAY_US_TAX_TYPES   t
WHERE  b.TAX_TYPE_ID = t.TAX_TYPE_ID;

Other recurring patterns include validating that a TAX_TYPE_CODE exists before loading balances, driving state-versus-federal reconciliation reports by TAX_DOMAIN_CODE, and separating employee and employer liability lines by EE_ER_CODE. Because the table is seed data and effectively static, it is commonly queried without date filters, though 12.2 environments should filter or group by ZD_EDITION_NAME when duplicate edition rows are present.

Related Objects

• HR.PAY_US_TAX_BALANCES — The sole documented dependent table. Its TAX_TYPE_ID foreign key references PAY_US_TAX_TYPES, making this the principal child object and the driver of most joins.
• APPS.PAY_US_TAX_TYPES — Application synonym used by EBS concurrent programs and forms.
• PUBLIC.PAY_US_TAX_TYPES — Public synonym permitting unqualified access.
• PAY_US_TAX_TYPES_PK — Primary key index backing TAX_TYPE_ID (composite with ZD_EDITION_NAME in 12.2.2).
• PAY_US_TAX_TYPES_UK2 — Unique index enforcing the TAX_TYPE_CODE business key.