Results for “pay_us_zip_codes”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The PAY_US_ZIP_CODES table is a reference data object owned by the HR schema within the Oracle E-Business Suite Payroll (PAY) product family. Its documented purpose is to store valid ZIP code ranges associated with VERTEX city data, which Oracle Payroll uses to validate and resolve United States addresses. VERTEX is the third-party tax and geocode content provider integrated into EBS Payroll for statutory address verification, and this table supplies the postal range boundary logic that ties a given ZIP code to the correct state, county, and city combination.

The table is status VALID in the ETRM 12.2.2 physical schema, with six documented columns. Because the object is populated with VERTEX-supplied geographic reference content rather than transactional payroll activity, it functions primarily as lookup data consumed during address validation. In Data Vault modeling terms, the mined foreign key structure suggests a satellite-leaning classification: the table describes and qualifies a parent geographic entity (the city geocode) rather than introducing an independent business key or resolving a many-to-many association. It would typically be modeled as a descriptive satellite attached to a city-geocode hub or link, carrying ZIP range attributes and the edition context under which the content was loaded.

Key Information Stored

The most significant columns and their roles are:

• ZIP_START and ZIP_END — the lower and upper boundaries of the ZIP code range assigned to a given geographic combination.
• STATE_CODE — the two-character state identifier; also a foreign key component referencing PAY_US_CITY_GEOCODES.
• COUNTY_CODE — the county identifier associated with the ZIP range.
• CITY_CODE — the VERTEX city identifier linking the range to a specific city record.
• ZD_EDITION_NAME — the edition marker that identifies the VERTEX content version, allowing multiple editions of reference data to coexist.

The documented primary key is PAY_US_ZIP_CODES_PK. Operationally it spans the geographic and range attributes (ZIP_START, ZIP_END, STATE_CODE, COUNTY_CODE, CITY_CODE). The unique index enumerates the same business-key candidates plus ZD_EDITION_NAME, indicating that edition name is the discriminator distinguishing business-key versions. No separate single-column surrogate key is documented; the composite acts as the business key.

Common Use Cases and Queries

Typical usage centers on validating or completing a US address for payroll and tax purposes: determining which state, county, and city a ZIP code belongs to before payroll processing, and confirming that an entered ZIP is consistent with the assigned city and state.

A representative pattern resolves a ZIP against the ranges for a specific edition:

• Restrict by ZD_EDITION_NAME to the active VERTEX edition.
• Test :p_zip BETWEEN ZIP_START AND ZIP_END.
• Select STATE_CODE, COUNTY_CODE, CITY_CODE for the matched row.

Reporting use cases include regional distribution analysis by joining to city geocode content, and edition-to-edition comparison to detect ZIP reassignments between VERTEX content refreshes. Because the object is reference data, queries should generally join outward to descriptive geocode tables rather than being used transactionally.

Related Objects

The documented foreign key relationship links the table to VERTEX city geocode content:

• PAY_US_CITY_GEOCODES — referenced via the composite of STATE_CODE, COUNTY_CODE, and CITY_CODE; this is the parent geographic object that supplies city-level detail.
• PAY_US_ZIP_CODES_PK — the primary key/unique index enforcing uniqueness across the range and geographic attributes plus edition.

Beyond these documented relationships, address-validation logic elsewhere in Payroll and HR typically consumes this table to reconcile entered postal addresses with VERTEX geography. The table should be treated as read-mostly content refreshed through VERTEX data updates, and its ZD_EDITION_NAME column allows multiple content editions to be retained side by side.