Results for “hr_s_us_zip_codes”

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

Overview

HR_S_US_ZIP_CODES is a reference table owned by the HR schema within the PER (Human Resources) product family of Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store valid United States zip code ranges associated with VERTEX cities, and it is used to validate US addresses as part of Oracle's address validation framework. The table pairs with VERTEX-supplied geography data so that the application can confirm that a zip code entered on an employee, applicant, or third-party record falls within a known, valid range for a given state, county, and city.

From a dimensional modelling perspective, the metadata classifies this object heuristically as standalone under a Data Vault scheme. That classification suggests the table is neither a hub nor a link nor a satellite in a strict sense, but functions as a reference/lookup structure. Where a Data Vault design were applied, the composite of ZIP_START, ZIP_END, STATE_CODE, COUNTY_CODE, and CITY_CODE would most naturally behave as a reference satellite or a small hub-like lookup rather than a transactional entity, since the table holds descriptive geography rather than business events.

Key Information Stored

The documented physical schema contains five columns. The primary key, HR_S_US_ZIP_CODES_PK, is defined over the composite of ZIP_START, ZIP_END, STATE_CODE, COUNTY_CODE, and CITY_CODE. There is no separate surrogate key column documented; the primary key itself is a composite business key, and the unique index HR_S_US_ZIP_CODES_PK is the only documented business-key candidate.

• ZIP_START — the beginning of the zip code range. This is the column referenced by the user's search term "zip_start".
• ZIP_END — the end of the zip code range; together with ZIP_START it defines the inclusive span covered by the row.
• STATE_CODE — the two-character state identifier associated with the range.
• COUNTY_CODE — the county identifier within the state, as supplied by VERTEX.
• CITY_CODE — the city identifier within the county, used to tie the range to a VERTEX city record.

Because the primary key spans all five columns, each row is uniquely a (range, state, county, city) combination. No foreign key columns are documented, consistent with the standalone classification.

Common Use Cases and Queries

The principal use case is address validation: given a user-entered zip code, the application determines which state, county, and city it belongs to, then validates the submitted address against that geography. Reporting use cases include territory assignment, tax jurisdiction lookups, and postal coverage analysis.

A typical query to resolve a zip code uses a range predicate:

• SELECT state_code, county_code, city_code FROM hr.hr_s_us_zip_codes WHERE :p_zip BETWEEN zip_start AND zip_end;

A query to list ranges within a state, useful for coverage reporting:

• SELECT zip_start, zip_end, county_code, city_code FROM hr.hr_s_us_zip_codes WHERE state_code = :p_state ORDER BY zip_start;

Because the primary key leads with ZIP_START, queries filtering or ordering on ZIP_START can use the index efficiently, while a BETWEEN predicate on ZIP_START and ZIP_END benefits from an index range scan on the leading column.

Related Objects

The ETRM metadata documents no foreign keys for HR_S_US_ZIP_CODES, confirming its standalone classification. The most significant related objects are therefore the VERTEX-driven geography and address validation components that consume this reference data rather than referential-integrity partners.

• VERTEX city and county reference tables in the HR geography set, joined on CITY_CODE and COUNTY_CODE.
• Address validation PL/SQL APIs in the PER module that query this table using ZIP_START and ZIP_END range logic.
• HR location and address tables (for example, HR_LOCATIONS) that hold US addresses validated against these ranges.
• PER employee and applicant address entities that trigger validation on entry.
• State and county lookup views that resolve STATE_CODE and COUNTY_CODE to descriptive names.

Given the composite primary key and the absence of documented foreign keys, joins to this table should be performed explicitly on the geography columns (STATE_CODE, COUNTY_CODE, CITY_CODE) and on the ZIP_START/ZIP_END range, rather than relying on declarative constraints.