Search Results hr_s_us_zip_codes_pk




Overview
Explore in AI Chat

Overview

The HR.HR_S_US_ZIP_CODES table is a core reference table within Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically designed for US address validation and geocoding in the Human Resources (HR) module. It functions as a lookup table that maps ranges of US ZIP codes to their corresponding state, county, and city geographic codes. Its primary role is to enable the system to automatically and accurately populate geographic information during address entry, ensuring data consistency and supporting location-based reporting and compliance. As indicated by its storage in the APPS_TS_INTERFACE tablespace and the associated warning, this table is considered an internal applications object. Direct data manipulation or access outside of standard Oracle Applications programs is not supported by Oracle.

Key Information Stored

The table stores a concise set of columns that define ZIP code ranges and their geographic hierarchy. The primary key, HR_S_US_ZIP_CODES_PK, is a composite key ensuring uniqueness across the five core columns. The key data elements are:

  • ZIP_START (VARCHAR2(10)): The starting value for a contiguous range of ZIP codes.
  • ZIP_END (VARCHAR2(10)): The ending value for the corresponding ZIP code range.
  • STATE_CODE (VARCHAR2): The geographic code for the US state. This column is a foreign key to the PAY_US_COUNTIES table.
  • COUNTY_CODE (VARCHAR2): The geographic code for the county within the state. Also a foreign key to PAY_US_COUNTIES.
  • CITY_CODE (VARCHAR2): The geographic code for the city. This column is a foreign key to the PAY_US_CITY_GEOCODES table.

This structure allows the system to efficiently validate that a given ZIP code falls within a known range and to retrieve its associated state, county, and city codes without storing every individual ZIP code.

Common Use Cases and Queries

The most frequent use case is the real-time validation and derivation of address details during data entry in forms for employees, applicants, or locations. When a user enters a US postal code, the application logic queries this table to find the range (ZIP_START to ZIP_END) that contains the entered code and returns the linked geographic identifiers. A typical lookup query pattern is:

SELECT STATE_CODE, COUNTY_CODE, CITY_CODE
FROM HR.HR_S_US_ZIP_CODES
WHERE :entered_zip_code BETWEEN ZIP_START AND ZIP_END;

For reporting and data validation, administrators may run queries to audit coverage or understand geographic distributions. A sample query to list all ranges for a specific state would be:

SELECT ZIP_START, ZIP_END, COUNTY_CODE, CITY_CODE
FROM HR.HR_S_US_ZIP_CODES
WHERE STATE_CODE = 'CA'
ORDER BY ZIP_START;

Related Objects

HR_S_US_ZIP_CODES is centrally linked to other geographic reference tables in the EBS data model via foreign key relationships, as documented in the metadata. The related objects and their join columns are:

  • PAY_US_COUNTIES: The STATE_CODE and COUNTY_CODE columns in HR_S_US_ZIP_CODES reference the corresponding columns in this table, which holds detailed county information.
  • PAY_US_CITY_GEOCODES: The CITY_CODE column in HR_S_US_ZIP_CODES is a foreign key to this table, which provides standardized city names and codes.

These relationships ensure referential integrity, meaning a ZIP code range in HR_S_US_ZIP_CODES must point to valid, pre-existing records in the referenced state/county and city tables.