Search Results wsh_regions_interface_u1

Overview

WSH.WSH_REGIONS_INTERFACE is a standard Oracle E-Business Suite interface table owned by the WSH (Shipping) schema. It is defined in Oracle EBS 12.1.1 and 12.2.2 with the FND Design Data reference WSH.WSH_REGIONS_INTERFACE and holds a status of VALID. Its documented purpose is to serve as a staging area for region data prior to upload into the regular region tables. Records inserted into this table are processed by a concurrent program that validates, deduplicates, and transfers them into the permanent region definition tables used by shipping, transportation, and warehouse management functionality.

The table is stored in the APPS_TS_INTERFACE tablespace with PCT Free 10, which is consistent with Oracle's convention of isolating high-churn staging data from the operational tablespaces. Under a heuristic Data Vault classification, this object is a standalone modeling structure: it contains no foreign key out-riggers to other interface objects and no dependent links, so it is best treated as an independent staging entity rather than as a hub, link, or satellite.

Key Information Stored

The table carries 21 documented columns. The most significant are listed below.

• REGION_ID (NUMBER) — the surrogate primary key for the table, enforced by the unique index WSH_REGIONS_INTERFACE_U1. It is also the business-key candidate identified in the documented metadata.
• REGION_TYPE (NUMBER) — classifies the record: 0=country, 1=state, 2=city, 3=postal code, 10=zone, 11=parcel zone.
• PARENT_REGION_ID (NUMBER) — identifies the parent region, enabling the hierarchical grouping of countries, states, cities, and postal codes.
• COUNTRY_CODE (VARCHAR2 10) — the country code for the region.
• COUNTRY_REGION_CODE (VARCHAR2 10) — the region code within the country.
• STATE_CODE, CITY_CODE (VARCHAR2 10 each) — state and city identifiers used during upload.
• ZONE_LEVEL (NUMBER) — the equivalent region level assigned to a zone record.
• PROCESSED_FLAG (VARCHAR2) — indicates whether the row has already been processed and transferred to the permanent region tables.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Who columns.
• PORT_FLAG, AIRPORT_FLAG, ROAD_TERMINAL_FLAG, RAIL_TERMINAL_FLAG, LONGITUDE, LATITUDE, TIMEZONE — reserved attributes documented as "for future use."

Common Use Cases and Queries

The principal use case is bulk loading of geographical regions. Data extracted from external sources or legacy systems is inserted into this table, then validated and uploaded by the standard concurrent process. Typical operational queries monitor pending and failed rows:

• Count unprocessed records: SELECT COUNT(*) FROM WSH.WSH_REGIONS_INTERFACE WHERE PROCESSED_FLAG IS NULL;
• Inspect rows by type: SELECT REGION_ID, REGION_TYPE, COUNTRY_CODE, STATE_CODE, CITY_CODE FROM WSH.WSH_REGIONS_INTERFACE WHERE REGION_TYPE = 2;
• Resolve hierarchy: join the table to itself on PARENT_REGION_ID = REGION_ID to display child regions with their parents.
• Reconciliation reporting by country: SELECT COUNTRY_CODE, REGION_TYPE, COUNT(*) FROM WSH.WSH_REGIONS_INTERFACE GROUP BY COUNTRY_CODE, REGION_TYPE;

The non-unique indexes WSH_REGIONS_INTERFACE_N1 (PARENT_REGION_ID), N2 (REGION_TYPE), and N3 (COUNTRY_CODE) support precisely these filter and grouping predicates.

Related Objects

The interface feeds the permanent region definition tables in the WSH schema, such as WSH_REGIONS and its associated hierarchy structures, which hold the same REGION_ID and PARENT_REGION_ID semantics once validated. The upload process is driven by a standard concurrent program that reads REGION_ID and writes the permanent records. Because the table is standalone with no documented foreign keys, dependencies are procedural rather than declarative: the parent-child relationship is expressed internally through PARENT_REGION_ID, and downstream shipping, transportation, and warehouse management modules consume the resulting region data through their own region lookup views and APIs.