Search Results pay_us_city_names

Overview

The PAY_US_CITY_NAMES table is a core reference data object within the Oracle E-Business Suite Payroll module (PAY). It serves as a master repository for United States city information, specifically designed to support address validation and tax calculation processes. Its primary role is to store canonical city names alongside their associated VERTEX geocodes, which are critical for interfacing with the VERTEX tax calculation engine. This table ensures that city-level data used in employee addresses and tax jurisdictions is standardized and accurate, a fundamental requirement for correct payroll processing and tax withholding in the U.S.

Key Information Stored

The table's structure is defined by a composite primary key that uniquely identifies each city record. The key columns are CITY_NAME, STATE_CODE, COUNTY_CODE, and CITY_CODE. These fields, in conjunction, provide a precise geographic identifier. The CITY_NAME column holds the official name of the city, while the STATE_CODE, COUNTY_CODE, and CITY_CODE columns store the corresponding VERTEX geocode components. These numeric codes are essential for mapping a city to its specific tax rules and jurisdictions within the VERTEX system. The table's design implies a hierarchical relationship: a city (CITY_CODE) belongs to a county (COUNTY_CODE) within a state (STATE_CODE).

Common Use Cases and Queries

The primary use case is the validation and enrichment of U.S. addresses entered within the HRMS system, particularly for employees and tax reporting units. During address entry, the system can reference this table to ensure city, county, and state combinations are valid. A common reporting or validation query involves joining this table to employee address data. For example:

• SELECT per.city, pucn.state_code, pucn.county_code FROM per_addresses per, pay_us_city_names pucn WHERE per.city = pucn.city_name AND per.state = pucn.state_code;

This table is also crucial for batch processes that prepare data for the VERTEX tax engine, where the geocodes are used as lookup keys to determine the correct tax jurisdiction for payroll calculations.

Related Objects

As indicated by the foreign key relationship in the metadata, PAY_US_CITY_NAMES is directly linked to the PAY_US_CITY_GEOCODES table. The foreign key is defined on the composite of STATE_CODE, COUNTY_CODE, and CITY_CODE columns. This relationship suggests that PAY_US_CITY_GEOCODES may hold more detailed or supplemental geocode information. The table is inherently related to any HRMS objects storing U.S. addresses, such as PER_ADDRESSES. Furthermore, it is a foundational component for the Payroll tax configuration and calculation engine, interacting with modules that handle tax rules, reporting, and third-party integrations like VERTEX.