Search Results province_code

Overview

The view APPS.HR_CA_CITIES_LOV_V is a seeded Oracle E-Business Suite database object owned by the APPS schema and validated as VALID. It belongs to the PER (Human Resources) product family and is documented in the ETRM reference material for both Oracle EBS 12.1.1 and 12.2.2. Its stated purpose is to supply data for a web-based List of Values (LOV) used to present Canadian city selections to end users, despite the underlying data being drawn from United States payroll geography tables.

Functionally, the view acts as a constrained projection of city, county, state, and ZIP information. Rather than exposing the raw geography tables directly to the LOV framework, it pre-joins them and applies a fixed filter on the state code (STATE_CODE = 70), which corresponds to a specific jurisdiction — commonly associated with Canadian province data represented within the US geography structures. This design allows forms, self-service pages, and integrations to retrieve a clean, denormalized result set of city choices with a single query.

In EBS reporting and integration terms, this view is a read-only lookup source. It carries no business logic beyond the join conditions and the hard-coded state filter, and it does not accept bind parameters. Any consumer that requires a province-specific or code-restricted subset of cities must apply that restriction itself.

Underlying Base Objects

The documented base objects are four SYNONYM references, each resolving to the underlying payroll geography tables:

• PAY_US_CITY_NAMES — source of city names and city codes.
• PAY_US_COUNTIES — source of county name, abbreviation, and code.
• PAY_US_STATES — source of the state code.
• PAY_US_ZIP_CODES — source of ZIP range start and end values.

The view joins these objects on a consistent set of keys. City names link to states by STATE_CODE, to counties by COUNTY_CODE, and to ZIP codes by the composite of CITY_CODE, STATE_CODE, and COUNTY_CODE. Counties and states are linked by STATE_CODE. A constant predicate, A.STATE_CODE = 70, restricts the output to the single jurisdiction selected for the Canadian city LOV. Because the view is defined over only four base objects and contains no subqueries or analytic functions, it remains inexpensive to query and is straightforward to trace in a dependency analysis.

Key Columns

The view exposes the following columns:

• CITY_NAME — the display value for the city, sourced from PAY_US_CITY_NAMES.
• CITY_CODE — the internal code identifying the city.
• STATE_CODE — the state (or province surrogate) code; the view returns only rows where this equals 70.
• ZIP_START and ZIP_END — the lower and upper bounds of the ZIP range associated with the city/county combination.
• COUNTY_NAME, COUNTY_ABBREV, and COUNTY_CODE — county descriptors carried through from the county table.
• PROVINCE_NAME, PROVINCE_ABBREV, and PROVINCE_CODE — province-related attributes documented in the view metadata, intended to align the Canadian city LOV with province-oriented reporting. Note that the documented view text does not list these as selected expressions; the metadata column list documents them as available outputs, and consumers should verify the actual column set in their environment.

Common Use Cases and Queries

The primary use case is populating a web LOV on HR forms and self-service pages where a user selects a Canadian city. Typical supporting scenarios include validating address or location data, driving downstream defaulting logic, and building ad hoc extracts of the available city/province population.

A basic retrieval of all LOV entries:

• SELECT city_name, city_code, state_code, county_name FROM apps.hr_ca_cities_lov_v ORDER BY city_name;

A filtered lookup for a specific city:

• SELECT city_name, city_code, zip_start, zip_end FROM apps.hr_ca_cities_lov_v WHERE province_code = :p_province AND city_name LIKE :p_city || '%';

A join-style query resolving province context:

• SELECT l.city_name, l.province_abbrev, l.province_code FROM apps.hr_ca_cities_lov_v l WHERE l.province_code = :p_province ORDER BY l.city_name;

Because the view is already constrained to STATE_CODE 70, joins to PAY_US_STATES for a descriptive state name usually return a single value. Queries should therefore rely on the view's own province columns, and where column availability differs between 12.1.1 and 12.2.2, verify against ALL_TAB_COLUMNS before deploying dependent code.