Results for “pay_ca_unique_cities_v”

18 results




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

Overview

The view PAY_CA_UNIQUE_CITIES_V is an Oracle E-Business Suite database object owned by the APPS schema and registered under the Payroll (PAY) product family. It is a lightweight, read-only view that exposes a de-duplicated list of city names associated with a single jurisdiction — specifically Canadian addresses, as indicated by the CA product code embedded in the object name. The view presents one column, CITY_NAME, and its purpose is to provide a clean, distinct set of valid city values that downstream forms, list-of-values (LOVs), and reporting queries can consume without encountering duplicates or disabled records. In EBS 12.1.1 and 12.2.2, the view retains an identical definition and a VALID status, meaning it is a stable, supported object that Oracle has not deprecated. Because it is a simple projection rather than a business-logic view, it carries no PL/SQL or row-level security, making it suitable for direct SQL access from custom reports, concurrent programs, and integration extracts.

Underlying Base Objects

The view is defined over a single referenced base object, PAY_US_CITY_NAMES, accessed through a synonym in the APPS schema. Despite the "US" token in the table name, the table functions as the shared city-name repository for payroll geography validation, and the view filters it to Canadian records. The defining query is:

  • SELECT DISTINCT CITY_NAME FROM PAY_US_CITY_NAMES WHERE STATE_CODE = 70 AND (DISABLE = 'N' OR DISABLE IS NULL)

Three filters shape the result set. First, STATE_CODE = 70 restricts rows to the Canadian geography bucket — this is the mechanism that narrows the shared multi-country table to Canada. Second, the DISABLE predicate excludes any city marked disabled, while the OR DISABLE IS NULL clause preserves legacy rows where the flag was never populated. Third, DISTINCT removes duplicate city entries that may arise from multiple state, province, or postal mappings pointing to the same city. The view therefore inherits the column set of the base object but publishes only the single field relevant to city selection.

Key Columns

The view exposes exactly one documented column:

  • CITY_NAME — the textual name of a Canadian city, sourced directly from the analogous column in PAY_US_CITY_NAMES. Values are unique within the view due to the DISTINCT operator, and each value corresponds to at least one enabled (non-disabled) city record.

Because only CITY_NAME is projected, consumers requiring province codes, postal ranges, or effective dates must query the base table directly. The view is intentionally narrow, functioning as a validated pick-list source rather than a full geography entity.

Common Use Cases and Queries

Typical applications include populating city LOVs on Canadian payroll and HR forms, validating free-text address input during data migration, and driving parameter prompts in concurrent programs or BI Publisher reports. A simple uniqueness check or extract resembles:

  • SELECT CITY_NAME FROM APPS.PAY_CA_UNIQUE_CITIES_V ORDER BY CITY_NAME;
  • SELECT CITY_NAME FROM APPS.PAY_CA_UNIQUE_CITIES_V WHERE CITY_NAME LIKE 'TOR%';
  • SELECT COUNT(*) FROM APPS.PAY_CA_UNIQUE_CITIES_V;

For joins against employee or assignment addresses, the view is best used with an equality or UPPER() match on the city attribute. Where province-level context is needed, join back to PAY_US_CITY_NAMES on CITY_NAME and the appropriate Canadian STATE_CODE. As with all APPS views, referential access should be granted through the standard APPS synonym rather than by qualifying the base table directly.