Search Results pay_us_emp_city_tax_rules_f

Overview

The PAY_US_EMP_CITY_TAX_RULES_F table is a core data structure within the Oracle E-Business Suite Payroll module, specifically for U.S. localization. It stores city-level tax withholding rules and details for employee assignments. As a date-tracked table (indicated by the '_F' suffix and the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns in its primary key), it maintains a historical record of changes to an employee's city tax information over time. This table is critical for the accurate calculation, withholding, and reporting of municipal income taxes during payroll processing in Oracle EBS releases 12.1.1 and 12.2.2.

Key Information Stored

The table's primary key is the composite key (EMP_CITY_TAX_RULE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), which enforces data integrity for its temporal records. Essential columns include the unique identifier EMP_CITY_TAX_RULE_ID and the effective dates that manage the record's validity period. Crucially, it stores geographic tax jurisdiction codes: STATE_CODE, COUNTY_CODE, and CITY_CODE, which link to the PAY_US_CITY_GEOCODES table to define the specific taxing authority. Other significant columns typically include BUSINESS_GROUP_ID, which links to HR_ALL_ORGANIZATION_UNITS, and various fields for storing tax rules such as filing status, additional withholding amounts, exemption counts, and tax calculation methods applicable to the employee's work or residence city.

Common Use Cases and Queries

The primary use case is supporting the payroll calculation engine in determining the correct city tax to withhold from an employee's earnings. It is also central for tax reporting, such as generating quarterly or annual city tax summaries. Common queries involve retrieving the current effective city tax rule for an assignment or auditing historical changes. A typical reporting query might join this table to employee and geographic data:

• SELECT pectr.state_code, pectr.city_code, ppf.full_name, pectr.effective_start_date FROM pay_us_emp_city_tax_rules_f pectr, per_all_people_f ppf WHERE pectr.assignment_id = :assignment_id AND SYSDATE BETWEEN pectr.effective_start_date AND pectr.effective_end_date AND ppf.person_id = :person_id AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;

Data is primarily maintained through the "Tax Information" region of the employee's assignment form or via dedicated batch processes.

Related Objects

As indicated by the foreign key constraints, PAY_US_EMP_CITY_TAX_RULES_F has strong dependencies on several key reference tables. It references PAY_US_STATES for state validation, PAY_US_CITY_GEOCODES for valid city/county/state combinations, and HR_ALL_ORGANIZATION_UNITS for the business group context. It is a child table to the employee assignment (PER_ALL_ASSIGNMENTS_F), though this specific foreign key is not listed in the provided excerpt. The table is also closely related to other employee-level tax rule tables, such as PAY_US_EMP_STATE_TAX_RULES_F and PAY_US_EMP_COUNTY_TAX_RULES_F, forming a hierarchy of geographic tax jurisdictions. Key APIs for maintaining this data are typically found within the PAY_US_TAX_RULES_MAINT package or similar.