Search Results country_region




Overview
Explore in AI Chat

Overview

WSH_REGIONS_TL is the translation-enabled (TL) table within the Oracle Shipping Execution (WSH) schema that stores geographic region and zone information used throughout the logistics and shipping lifecycle. In Oracle EBS 12.1.1 and 12.2.2, it serves as the descriptive, language-dependent companion to the base region definition, holding continent, country, state, city, zone, and postal-code attributes that drive carrier rating, zone-based freight calculation, ship-to and ship-from validation, and geographic reporting. The table is owned by WSH and is documented as VALID in the ETRM repository.

From a dimensional modeling perspective, ETRM classifies WSH_REGIONS_TL as standalone based on its foreign-key structure. This is a heuristic classification, not a physical constraint: it suggests the table has no incoming or outgoing referential dependencies mined from FK metadata and can therefore be modeled independently as a reference/dimension rather than as a hub, link, or satellite. Practitioners designing a Data Vault layer should treat it as a standalone descriptive entity keyed by region and language.

Key Information Stored

The table is documented with 18 physical columns. The primary key WSH_REGIONS_TL_PK1 is composite, comprising REGION_ID and LANGUAGE. A unique index, WSH_REGIONS_TL_U1, mirrors the same column pair (REGION_ID, LANGUAGE) and represents the business-key candidate for this object. The pairing of a surrogate REGION_ID with a LANGUAGE code is what renders this a TL table: one logical region fans out into one row per installed language.

  • REGION_ID — the numeric surrogate identifier that joins to the base region definitions and other shipping entities.
  • LANGUAGE — the language code that, together with REGION_ID, forms the composite primary key and unique business key.
  • SOURCE_LANG — the base language from which the translated row was derived.
  • CONTINENT — the translated continent name.
  • COUNTRY — the translated country name.
  • COUNTRY_REGION — the broader regional grouping within a country.
  • STATE — the translated state or province designation.
  • CITY — the translated city name.
  • ZONE — the shipping zone label used for rating and routing logic.
  • COUNTY — the translated county or sub-state administrative area.
  • POSTAL_CODE_FROM / POSTAL_CODE_TO — the postal-code range bounds associated with the region/zone entry.
  • ALTERNATE_NAME — an alternative or legacy name for the region.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS who-column audit attributes.

Common Use Cases and Queries

Typical uses include freight rating and zone lookup, address validation for ship-to and ship-from parties, and multilingual geographic reporting. A frequently used pattern retrieves region descriptions in a specific language while falling back to the source language:

SELECT region_id, country, state, city, zone, postal_code_from, postal_code_to
FROM wsh_regions_tl
WHERE language = USERENV('LANG')
ORDER BY country, state, city;

To resolve a postal code to its zone for a given language, query the postal-code range columns:

SELECT zone, country, state
FROM wsh_regions_tl
WHERE language = 'US'
AND :postal_code BETWEEN postal_code_from AND postal_code_to;

For validation reports, joining all translations for one region is achieved by selecting on REGION_ID with an equality predicate on LANGUAGE, exploiting the WSH_REGIONS_TL_U1 unique index for efficient access.

Related Objects

The most significant relationships center on REGION_ID, the column shared with base and transactional shipping entities. While ETRM classifies the table as standalone, practical joins include:

  • WSH_REGIONS_B / base region tables — joined on REGION_ID to obtain language-independent attributes.
  • WSH_CARRIER_SERVICES — rating and service definitions that reference geographic zones.
  • WSH_RATE_GEOGRAPHIES / rate zone tables — freight rate geographies linked by region and zone.
  • WSH_TRIPS and trip stops — movement records referencing ship-from and ship-to locations.
  • WSH_DELIVERY_ASSIGNMENTS — delivery routing that depends on destructured location and zone data.
  • HR_LOCATIONS_ALL — organization locations validated against region and country values.
  • FND_LANGUAGES — supplies the LANGUAGE and SOURCE_LANG codes used by this TL table.
  • HZ_LOCATIONS / party site tables — address data that map to region rows during shipping.

These associations are predominantly logical joins on REGION_ID rather than enforced foreign keys, consistent with the standalone classification reported in the ETRM metadata.