Search Results wsh_regions_tl_interface




Overview
Explore in AI Chat

Overview

WSH_REGIONS_TL_INTERFACE is a translated (TL) interface table owned by the WSH schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the Shipping Execution product and serves as a staging area for region data prior to validation and upload into the permanent region tables. The table holds multilingual region definitions, where each region record is stored once per installed language, enabling Oracle EBS to present region, country, state, city, and zone information in the user's session language. Because it is an interface table, its contents are transient: rows are populated by external feeds, concurrent programs, or custom loaders and are subsequently consumed by the region import process into WSH_REGIONS_TL and its base counterpart.

From a Data Vault modeling perspective, the heuristic classification for this object is standalone. It does not participate in any documented foreign key relationships, so no hub, link, or satellite role can be inferred from the FK structure. The table is best treated as a staging entity that feeds the core region reference model rather than as a component of a normalized Data Vault design. The documented physical schema in ETRM 12.2.2 comprises 18 columns.

Key Information Stored

The primary key of the table is WSH_REGIONS_INTERFACE_TL_PK1, defined on the composite of REGION_ID and LANGUAGE. These two columns also constitute the unique index WSH_REGIONS_TL_INTERFACE_U1, making them the business-key candidates for identifying a region's translated description within a given language.

  • REGION_ID — Surrogate identifier for the region; part of the composite primary key and the primary join key to the base region interface table.
  • LANGUAGE — The language code for the translated row; the second component of the composite primary key.
  • SOURCE_LANG — The source language from which the translated text was derived, used by the translation (TL) mechanism.
  • ALTERNATE_NAME — The translated or alternate name of the region, typically the primary descriptive attribute presented to users.
  • CONTINENT — Continent associated with the region, used for geographic rollups.
  • COUNTRY — Country to which the region belongs.
  • COUNTRY_REGION — Higher-level grouping of country and region used in shipping and territory definitions.
  • STATE — State or province within the region.
  • CITY — City associated with the region.
  • ZONE — Delivery or shipping zone classification.
  • POSTAL_CODE_FROM and POSTAL_CODE_TO — The postal code range boundaries used to map an address to a region or zone.
  • COUNTY — County information for finer geographic segmentation.

Standard audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN track who inserted and last modified each staging row.

Common Use Cases and Queries

This table is most commonly used during region data migration and localization projects. A typical scenario is loading region translations from an external file into WSH_REGIONS_TL_INTERFACE and then running the region import concurrent program to move validated rows into WSH_REGIONS_TL. A frequent validation query identifies rows present in the interface but not yet promoted to the base tables:

SELECT i.region_id, i.language, i.alternate_name, i.country, i.state
FROM   wsh.wsh_regions_tl_interface i
WHERE  NOT EXISTS (SELECT 1 FROM wsh.wsh_regions_tl r
                   WHERE r.region_id = i.region_id
                   AND   r.language  = i.language);

Reporting scenarios include reconciling postal code ranges to shipping zones, detecting duplicate REGION_ID and LANGUAGE combinations, and auditing translation completeness across languages:

SELECT language, COUNT(*) FROM wsh.wsh_regions_tl_interface GROUP BY language;

Purge scripts use CREATION_DATE and LAST_UPDATE_DATE to clear stale staging rows after a successful import cycle.

Related Objects

  • WSH_REGIONS_TL — The permanent translated region table; joined on REGION_ID and LANGUAGE, and the target of the interface load.
  • WSH_REGIONS_INTERFACE — The base (non-translated) region interface table, joined on REGION_ID.
  • WSH_REGIONS — The base region entity table, joined on REGION_ID.
  • WSH_REGIONS_TL_INTERFACE — Related TL interface lineage through shared REGION_ID and LANGUAGE keys.
  • Region import concurrent program and its validation APIs, which read this table and write to WSH_REGIONS_TL.

Because the object is classified as standalone with no documented foreign keys, relationships to the above tables are maintained by matching REGION_ID and LANGUAGE rather than by enforced constraints.