Search Results msc_regions




Overview
Explore in AI Chat

Overview

MSC_REGIONS is a table in the MSC schema (Advanced Supply Chain Planning) within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores geographical region data used by the Advanced Supply Chain Planning (ASCP) application to model supply chain networks, define sourcing and transportation lanes, and support planning calculations that depend on location hierarchies. Each row represents a region node, which can be a continent, country, country region, state, city, zone, or postal code range, as indicated by the REGION_TYPE column. The table also carries flags and coordinates that identify ports, airports, road terminals, and rail terminals, enabling the planning engine to evaluate transportation and lead-time logic across a global network.

From a data modeling perspective, the metadata suggests a standalone classification under the Data Vault heuristic, meaning MSC_REGIONS is not mined as a hub, link, or satellite through foreign key structure. It functions as a reference or dimension-like entity whose primary key, REGION_ID, is enforced by the constraint MSC_REGION_PK. Because it is largely self-referencing (through PARENT_REGION_ID), it behaves as a hierarchical region dimension rather than a transactional hub.

Key Information Stored

The unique index is documented as MSC_REGION_PK on REGION_ID; business-key candidates include the country, state, and city code combinations.

Common Use Cases and Queries

MSC_REGIONS is commonly queried to populate region lookups in ASCP, to build sourcing and shipping network definitions, and to drive reports on planned shipments by geography. Typical patterns include hierarchy traversal using PARENT_REGION_ID, filtering by REGION_TYPE, and joining to planning tables on REGION_ID or on code combinations.

Sample query — list all countries:

  • SELECT REGION_ID, COUNTRY, COUNTRY_CODE FROM MSC_REGIONS WHERE REGION_TYPE = 'COUNTRY' AND DELETED_FLAG = 'N';

Sample query — find child regions of a parent:

  • SELECT CHILD.REGION_ID, CHILD.CITY, CHILD.STATE FROM MSC_REGIONS CHILD, MSC_REGIONS PARENT WHERE CHILD.PARENT_REGION_ID = PARENT.REGION_ID AND PARENT.REGION_ID = :region_id;

Sample query — identify transportation-capable regions:

  • SELECT REGION_ID, CITY, COUNTRY FROM MSC_REGIONS WHERE (PORT_FLAG = 'Y' OR AIRPORT_FLAG = 'Y' OR RAIL_TERMINAL_FLAG = 'Y') AND DELETED_FLAG = 'N';

Related Objects

  • MSC_REGION_PK — primary key constraint on REGION_ID, enforcing uniqueness.
  • PARENT_REGION_ID — self-referencing relationship back to MSC_REGIONS.REGION_ID for hierarchy traversal.
  • MSC_SR_INSTANCES — source instance reference via SR_INSTANCE_ID / SR_INSTANCE_CODE for collected region data.
  • MSC_SOURCING_RULES — sourcing rules reference regions as source or destination locations.
  • MSC_SHIPPING_NETWORKS — shipping lanes join to region rows on REGION_ID for origin and destination.
  • MSC_TRADING_PARTNERS — trading partner locations reference region identifiers.
  • MSC_PLANS and MSC_SUPPLIES — planning results referencing destination region data.
  • MSC_ST_TRANSACTIONS — collection staging referenced through ST_TRANSACTION_ID, BATCH_ID, and MESSAGE_ID columns on MSC_REGIONS.