Search Results pay_us_state_tax_types_v




Overview
Explore in AI Chat

Overview

The PAY_US_STATE_TAX_TYPES_V view is a seeded, read-only database object owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PAY (Payroll) product family. Its purpose is to expose a consolidated, query-friendly list of the United States state-level statutory tax element types that Oracle Payroll recognizes for withholding, disability, unemployment, and workers' compensation processing. Rather than requiring report developers and integrators to join the payroll element type tables to the US tax configuration tables manually, the view pre-joins those sources and filters them down to a well-defined subset of state tax elements.

Because the view is defined with an internal filter set that restricts rows to the STATE tax domain, employee-side balances, and a fixed enumeration of element names, it functions as a reference list rather than a general-purpose tax view. Report authors, fast formulas, and interface programs can use it to drive lookups, validate element names, or enumerate the tax types applicable to a given legislative context without reproducing the join and predicate logic themselves. The object is documented as VALID in ETRM for both the 12.1.1 and 12.2.2 releases, and it is exposed in the APPS schema using synonyms over the underlying payroll and tax tables.

Underlying Base Objects

The view is defined over four base objects, all referenced through synonyms owned by APPS:

  • PAY_ELEMENT_TYPES_F — the core payroll element type definition table, supplying the element type identifier, effective dates, business group, processing priority, and legislation code.
  • PAY_ELEMENT_TYPES_F_TL — the translation table for element types, supplying the user-visible element name (ELEMENT_NAME) filtered by the session language via USERENV('LANG').
  • PAY_US_TAX_TYPES — the US tax type definition table, supplying the tax type identifier, limit tax flag, tax domain code, and tax type code.
  • PAY_US_TAX_BALANCES — the US tax balance mapping table, supplying the balance category code, the employee/employer indicator, and the user reporting name.

The join path links element types to tax types through the shared TAX_TYPE_ID on PAY_US_TAX_TYPES and PAY_US_TAX_BALANCES, while the element type tables provide the descriptive name and effective-dating context. Predicates restrict the result set to TAX_DOMAIN_CODE = 'STATE', BALANCE_CATEGORY_CODE in ('WITHHELD', 'ADVANCED'), and EE_OR_ER_CODE = 'EE', so only employee-side state withholdings and advance balances are returned.

Key Columns

  • TAX_TYPE_ID — surrogate identifier for the tax type; the principal key used to join to other tax configuration objects.
  • TAX_TYPE_CODE — derived code for the tax type. The view applies a DECODE so that WORKERS COMPENSATION maps to 'WC', WORKERS COMPENSATION2 EE maps to 'WC2', STEIC_WK maps to 'STEIC', and all other elements use the first three characters of the element name (for example SIT, SDI, SUI).
  • TAX_DOMAIN_CODE — always 'STATE' in this view, confirming the state-level scope.
  • LIMIT_TAX_FLAG — indicates whether the tax is subject to a wage limit.
  • BALANCE_CATEGORY_CODE — WITHHELD or ADVANCED, indicating the balance treatment of the tax.
  • EE_OR_ER_CODE — always 'EE', identifying the employee-side balances exposed by the view.
  • USER_REPORTING_NAME — the reporting label associated with the tax balance.
  • ELEMENT_NAME — the translated element name; restricted to SIT_WK, SDI_EE, SUI_EE, STEIC_WK, WORKERS COMPENSATION, and WORKERS COMPENSATION2 EE.
  • ELEMENT_TYPE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID, PROCESSING_PRIORITY, LEGISLATION_CODE — element type attributes used for effective-dated and business-group-scoped queries.

Common Use Cases and Queries

The view is typically used to resolve element type identifiers from tax type codes, to populate selection lists for state tax elements, or to validate configuration during payroll setup and data conversion. A representative query returns the tax type and element identifiers for all state employee withholdings:

  • SELECT tax_type_id, tax_type_code, element_name, element_type_id, processing_priority FROM pay_us_state_tax_types_v WHERE legislation_code = 'US' ORDER BY tax_type_code;
  • SELECT element_name, user_reporting_name FROM pay_us_state_tax_types_v WHERE balance_category_code = 'WITHHELD';
  • SELECT v.tax_type_code, v.element_type_id FROM pay_us_state_tax_types_v v WHERE v.business_group_id = :p_business_group_id AND SYSDATE BETWEEN v.effective_start_date AND v.effective_end_date;

Because the view is a static, seeded definition with a narrow element-name filter, custom state tax elements added by a legislated patch or by configuration will not appear unless they fall within the documented element name list. Queries intended to enumerate all state taxes should therefore be validated against PAY_US_TAX_TYPES directly when completeness is critical.