Search Results pay_ca_provinces_v




Overview
Explore in AI Chat

Overview

PAY_CA_PROVINCES_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PAY (Payroll) product family. Despite the naming convention that suggests Canadian provincial data, the view is constructed to expose Canadian province information by filtering United States county records for a specific state and joining them to a lookup defined for the Canadian province context. This design reflects the shared North American payroll data model in which the Canadian Payroll application reuses the United States county structure stored in PAY_US_COUNTIES to represent Canadian provinces and territories.

The view presents a denormalized, user-facing list of province names, province codes, and province abbreviations. In Oracle EBS 12.1.1 and 12.2.2, such views are typically consumed by Oracle Forms-based payroll windows, concurrent programs, and reporting tools (Oracle Reports, BI Publisher, and ad hoc SQL) that require a validated, language-aware list of Canadian provinces without having to navigate the underlying county and lookup tables directly.

Underlying Base Objects

The documented metadata identifies two referenced base objects, both accessed through synonyms in the APPS schema:

  • PAY_US_COUNTIES (SYNONYM) — Supplies the raw geographic records. The view restricts this table with the predicate STATE_CODE = '70', which is the internal code designating Canada within the shared North American geography model. The columns COUNTY_CODE and COUNTY_ABBREV are carried into the view as the province code and abbreviation.
  • FND_LOOKUP_VALUES (SYNONYM) — Supplies the translated province name via the MEANING column. The join is constrained to LOOKUP_TYPE = 'CA_PROVINCE', matching LOOKUP_CODE against the county abbreviation, restricting to ENABLED_FLAG = 'Y' and to the session language using LANGUAGE = USERENV('LANG').

Because FND_LOOKUP_VALUES is a language-sensitive foundation table, the view automatically returns province names in the runtime language of the session, which is essential for multilingual deployments.

Key Columns

  • PROVINCE_NAME — The descriptive, translated name of the Canadian province or territory, sourced from the FND lookup MEANING column.
  • PROVINCE_CODE — The numeric or alphanumeric code identifying the province, derived from the county code in PAY_US_COUNTIES.
  • PROVINCE_ABBREV — The short form or standard abbreviation for the province, derived from the county abbreviation and used as the join key to the CA_PROVINCE lookup type.

These three columns provide a stable, consumable interface that shields callers from the physical structure of the county and lookup tables.

Common Use Cases and Queries

Typical scenarios include populating province list-of-values (LOV) regions in payroll forms, validating province entries during employee or tax information entry, and driving province-level payroll reports and statutory extracts for Canadian payroll processing.

A representative query returns the full ordered province list:

  • SELECT province_name, province_code, province_abbrev FROM apps.pay_ca_provinces_v ORDER BY province_name;

A lookup for a specific abbreviation commonly used during data validation:

  • SELECT province_name FROM apps.pay_ca_provinces_v WHERE province_abbrev = 'ON';

Because the view depends on USERENV('LANG'), calling applications should ensure the session language is initialized before querying. Integration and reporting code should also treat the view as read-only, since it is a reporting construct over foundation and geography data rather than a maintained entity.