Search Results ca_province




Overview
Explore in AI Chat

Overview

APPS.PAY_CA_PROVINCES_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases that exposes Canadian province information in a decoded, user-facing form. Its principal role is to reconcile the physical county/province coding stored in the payroll geography tables with the multilingual lookup values maintained in the Application Object Library. Rather than returning raw codes, the view joins the county abbreviation held against a Canadian address to the CA_PROVINCE lookup so that the corresponding translated province name (MEANING) is returned.

The view is defined only over Canadian territory: the predicate co.state_code = '70' restricts the result set to the Canadian state/province grouping, where 70 represents Canada within the PAY_US_COUNTIES structure. Because it resolves lookup meanings in the session language via USERENV('LANG'), the view is useful for localized reporting, interfaces, and any downstream process that requires a display-ready province name rather than a code. It is a read-only, presentation-layer object and carries no data of its own.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view is owned by APPS and references two base objects, both accessed through synonyms:

  • PAY_US_COUNTIES — supplies the county/province code (COUNTY_CODE) and abbreviation (COUNTY_ABBREV). Despite its name, it stores geography records for the United States and Canada, with STATE_CODE = '70' denoting Canada.
  • FND_LOOKUP_VALUES — supplies the translated province name (MEANING) for lookup type CA_PROVINCE, spanning the Oracle Application Object Library lookup framework.

The join correlates the county abbreviation to the lookup code (flv.lookup_code = co.county_abbrev) and constrains on lookup_type = 'CA_PROVINCE', the session language, and enabled_flag = 'Y' so that only active, language-appropriate values are returned. The relationship is therefore many-to-one from the county perspective: each Canadian county/province record matches its single enabled lookup meaning for the active language.

Key Columns

  • MEANING — the translated, display-ready province name sourced from FND_LOOKUP_VALUES for the CA_PROVINCE lookup type; this is the primary human-readable output.
  • COUNTY_CODE — the code identifying the province within PAY_US_COUNTIES; used as a stable key in interfaces and joins.
  • COUNTY_ABBREV — the province abbreviation that links to the lookup code and typically mirrors the standard two-letter provincial designator.

No additional columns are documented; the exposed projection is limited to these three fields.

Common Use Cases and Queries

The view is typically used to list valid Canadian provinces for a value list or to translate stored abbreviations into readable names for reports and outbound interfaces. A standard query retrieving all enabled provinces in the session language is:

  • SELECT meaning, county_code, county_abbrev FROM apps.pay_ca_provinces_v ORDER BY meaning;
  • SELECT meaning, county_code FROM apps.pay_ca_provinces_v WHERE county_abbrev = 'ON';

It can also be joined to address, employee, or payroll tables on COUNTY_ABBREV or COUNTY_CODE to render localized province names, for example in a Canadian payroll or tax report. Because output depends on USERENV('LANG') and ENABLED_FLAG = 'Y', results vary with session language and lookup configuration, so consumers should not treat the view as a static reference and should account for disabled or untranslated lookup rows.