Search Results pay_us_city_tax_info_f




Overview
Explore in AI Chat

Overview

PAY_US_CITY_TAX_INFO_F is a payroll table owned by the HR schema within the PAY (Payroll) product of Oracle E-Business Suite. As its name and description indicate, it holds city tax information used by the Oracle Payroll engine for United States legislative and jurisdictional processing. The table functions as a date-tracked repository of city-level tax rules, capturing the tax components that apply to a given city jurisdiction so that payroll calculations can determine the correct withholding and liability amounts during a payroll run.

The suffix "_F" denotes a date-effective (datetracked) table, evidenced by the presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns. The documented primary key PAY_US_CITY_TAX_INFO_F_PK spans CITY_TAX_INFO_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and JURISDICTION_CODE (with ZD_EDITION_NAME appearing in the unique index listing). This composite structure allows multiple historical and future-dated versions of the same city tax record to coexist, which is essential for retroactive tax changes.

The ETRM metadata supplies a heuristic Data Vault classification of "standalone" for this object. Under Data Vault modeling conventions, this suggests the table most closely resembles a hub or self-contained reference entity rather than a transactional link, as no foreign-key relationships were mined from its structure. This classification should be treated as a modeling suggestion, since the table also carries descriptive attributes that would be modeled as satellite data in a strict Data Vault design.

Key Information Stored

The 69 documented columns fall into several logical groups. The most significant identifiers and keys are:

  • CITY_TAX_INFO_ID — the surrogate identifier for the city tax information record, forming the leading component of the primary key.
  • JURISDICTION_CODE — the business-key candidate that identifies the specific city taxing jurisdiction; it participates in both the primary key and the unique index.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack boundaries defining when a given version of the record is valid.

The core tax component columns hold the actual rate or amount values used by the payroll engine:

  • CITY_TAX — the city-level tax value or rate.
  • HEAD_TAX — the per-head (flat) tax amount where applicable.
  • SCHOOL_TAX — the school district tax component levied at the city level.

The table also includes standard EBS descriptive flexfield storage via ATTRIBUTE_CATEGORY and CITY_ATTRIBUTE1 through CITY_ATTRIBUTE20, plus a dedicated CITY_INFORMATION_CATEGORY with CITY_INFORMATION1 through CITY_INFORMATION30 for legislative/statutory city tax information. Audit and concurrency columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) and concurrent program context columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) complete the record. ZD_EDITION_NAME supports the datetrack editioning mechanism introduced in later releases.

Common Use Cases and Queries

The primary use case is supplying city tax rules to the Oracle Payroll calculation engine during payroll processing. Report writers and payroll administrators query the table to audit which city taxes apply to employees in specific jurisdictions as of a given pay period.

A typical current-version query retrieves the active record for a jurisdiction:

  • SELECT city_tax_info_id, jurisdiction_code, city_tax, head_tax, school_tax FROM hr.pay_us_city_tax_info_f WHERE jurisdiction_code = :code AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Historical analysis uses the datetrack boundaries to reconstruct the tax rule in effect for a prior pay period:

  • SELECT * FROM hr.pay_us_city_tax_info_f WHERE jurisdiction_code = :code AND :pay_date BETWEEN effective_start_date AND effective_end_date;

Because the table is standalone, queries typically join to jurisdiction or tax reference tables on JURISDICTION_CODE rather than through a declared foreign key. Common reporting outputs include city tax rate extracts, retroactive rate-change comparisons across effective dates, and reconciliation of head tax versus school tax components for a filing period.

Related Objects

The ETRM metadata documents no foreign-key relationships for this object, consistent with its standalone classification. In practice, logical associations exist via the JURISDICTION_CODE and the datetrack keys, and the following objects are the most significant for reference or joining:

  • PAY_US_JURISDICTION_RULES_F — relates city tax records to the governing city jurisdiction through JURISDICTION_CODE.
  • PAY_US_CITY_TAX_RULES_F — companion table holding city tax rule definitions that reference the same jurisdiction and effective dating scheme.
  • PAY_US_STATE_TAX_INFO_F — the state-level analog, useful for hierarchy reporting across state and city taxes.
  • PAY_US_TAX_BALANCES — stores calculated tax results that consume the rates defined here.
  • HR_ALL_ORGANIZATION_UNITS — used to associate employees and locations with the taxing jurisdiction.
  • FND_LOOKUP_VALUES — supplies lookup codes for jurisdiction and tax category reference values.
  • PAY_US_CITY_TAX_INFO_F_PK — the primary key constraint guaranteeing uniqueness across the ID, effective dates, and jurisdiction code.

Because no enforced foreign keys are present, referential integrity with these objects is maintained by application logic rather than by database constraints, and join conditions should be validated explicitly in any custom query.