Results for “hr_us_cities_lov_v”

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

Overview

HR_US_CITIES_LOV_V is a VALID Oracle E-Business Suite view owned by the APPS schema and registered under the PER (Human Resources) product family. It exists to supply the data set behind web-based List of Values (LOV) components that present United States city selections to end users. Rather than forcing an application page to join four separate payroll geography tables at runtime, the view encapsulates the required joins and exposes a single, denormalized cursor source for US city lookup.

The object is read-only by design. No DML is performed against the view itself; all maintenance occurs on the underlying payroll tables, and the view reflects changes immediately. In Oracle EBS 12.1.1 and 12.2.2 the view remains consistent in structure, which makes it safe for personalizations, custom concurrent programs, OAF-based pages, and downstream extracts. It is also a useful integration surface when external systems need to resolve a US city to its state, county, and ZIP range without reimplementing the EBS geography model.

The name is significant: the HR_ prefix indicates the Human Resources product group, the US token scopes the content to United States geography, CITIES_LOV identifies the intended list-of-values usage, and the _V suffix confirms it is a database view. The view deliberately excludes state code 70, which corresponds to a non-contiguous or special territory entry, ensuring the LOV presents only continental-style US selections.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over four synonyms that resolve to the PAY schema geography tables:

• PAY_US_CITY_NAMES (SYNONYM) — city name and city code, keyed by state and county.
• PAY_US_COUNTIES (SYNONYM) — county name and county code referenced by state.
• PAY_US_STATES (SYNONYM) — state abbreviation and state code, the top of the hierarchy.
• PAY_US_ZIP_CODES (SYNONYM) — ZIP start and end ranges assigned to a city.

The join path is fully qualified: CITY_NAMES.STATE_CODE = STATES.STATE_CODE, CITY_NAMES.COUNTY_CODE = COUNTIES.COUNTY_CODE, COUNTIES.STATE_CODE = STATES.STATE_CODE, and the ZIP table is matched on CITY_CODE, STATE_CODE, and COUNTY_CODE. This enforces referential consistency across the four base objects so that each returned row represents a city with a valid state, county, and ZIP range. The filter A.STATE_CODE != 70 is applied in the view text itself, so callers never see that exclusion as a surprise.

Key Columns

The view exposes eight columns:

• CITY_NAME — the display value for the LOV.
• CITY_CODE — the internal code used to store the selection on the transaction.
• STATE_ABBREV — two-letter state abbreviation for display and cross-reference.
• STATE_CODE — numeric state identifier used in foreign-key relationships.
• ZIP_START and ZIP_END — the ZIP range bounds associated with the city.
• COUNTY_NAME and COUNTY_CODE — county context for the city.

Common Use Cases and Queries

The primary use is an LOV query filtered by state. A typical lookup returns cities with their ZIP ranges for a single state:

SELECT city_name, city_code FROM apps.hr_us_cities_lov_v WHERE state_abbrev = 'CA' ORDER BY city_name;

For validation and integration, callers may confirm a city's county and ZIP bounds:

SELECT city_name, county_name, zip_start, zip_end FROM apps.hr_us_cities_lov_v WHERE city_code = :p_city AND state_code = :p_state;

Analysts frequently resolve ZIP codes to cities by comparing against the ZIP range columns, and developers building OAF or Forms personalizations use the view directly as the LOV data source. Because the joins are pre-built and the state 70 filter is pre-applied, these queries avoid duplicate geography logic and stay consistent with standard EBS behavior.