Search Results province_code




Overview
Explore in AI Chat

Overview

PAY_CA_CITIES_V is a payroll-related database view owned by the APPS schema in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It is registered under the PAY – Payroll product family and carries a VALID status in the ETRM repository. The view exposes a filtered list of Canadian city name records used by Oracle Payroll and related HRMS modules that require validated city and province references for Canadian addresses, tax jurisdictions, and employee or organization location data.

Despite its broadly descriptive name, PAY_CA_CITIES_V is not a complete catalog of all Canadian cities. Its WHERE clause restricts output to a single state/province code (STATE_CODE = 70) and to rows that are either not disabled or have a null disable flag. As a result, the view functions as a focused reporting and integration layer rather than a full reference table surrogate, and consumers should not assume it returns every Canadian province's cities.

Underlying Base Objects

PAY_CA_CITIES_V is defined as a SELECT from PAY_US_CITY_NAMES, which is documented as a synonym in the ETRM metadata. In the underlying execution context, this synonym resolves to the Oracle Payroll geography/city repository used for North American address validation. The view text is:

Because the view is defined on a synonym, the physical table behind it may reside in an application schema such as APPS or a related HRMS schema. The base table contributes the STATE_CODE and DISABLE columns used purely for filtering; those columns are not projected in the view output, so they are unavailable to consumers of PAY_CA_CITIES_V. This design intentionally narrows the exposed data set to enabled city records for the target province.

Key Columns

  • CITY_NAME – The descriptive name of the city (for example, the label presented to end users or printed on documents).
  • CITY_CODE – The internal code identifying the city within the payroll geography repository. This value is typically used for lookups, foreign key references, and downstream processing rather than display.
  • PROVINCE_CODE – The province identifier exposed by the view. Notably, the documented view text selects COUNTY_CODE from the base table, while the documented column list for PAY_CA_CITIES_V shows PROVINCE_CODE. This reflects the Canadian localization mapping, where the county/state code position is surfaced as a province code to Canadian consumers. Users searching for "province_code" will find it in this column.
  • PRIMARY_FLAG – A flag indicating whether the city record is treated as the primary reference for its associated geography. It is useful for de-duplicating or prioritizing city selections.

The view does not expose STATE_CODE or DISABLE, because both are consumed by the WHERE predicate. Consequently, all rows returned are implicitly enabled and belong to the single target province.

Common Use Cases and Queries

PAY_CA_CITIES_V is typically used in reporting, data validation, and integration scenarios for Canadian payroll. Common uses include populating city list-of-values components, validating city codes on inbound interfaces, and joining to employee or location data where a province-aware city reference is required.

A basic query returns the enabled city list for the target province:

  • SELECT CITY_NAME, CITY_CODE, PROVINCE_CODE, PRIMARY_FLAG FROM APPS.PAY_CA_CITIES_V ORDER BY CITY_NAME;

To restrict selection to primary cities only:

  • SELECT CITY_NAME, CITY_CODE FROM APPS.PAY_CA_CITIES_V WHERE PRIMARY_FLAG = 'Y';

Because the view is already filtered to an enabled subset, no DISABLE predicate is required. When joining to base geography tables for broader province coverage, implementers should query PAY_US_CITY_NAMES directly or use the appropriate Canadian localization view, since PAY_CA_CITIES_V intentionally limits its scope to the province code encoded in its definition.