Search Results parent_region_id

Overview

The MSC_ZONE_REGIONS table is a core data object within the Advanced Supply Chain Planning (ASCP) module of Oracle E-Business Suite. It serves as a mapping table that defines the hierarchical relationship between user-defined planning zones and the specific geographic or organizational regions they encompass. This structure is fundamental for enabling aggregate-level planning and analysis, allowing planners to define logical groupings of regions (zones) for streamlined demand, supply, and capacity planning. The table's integrity is maintained by two primary key constraints, ensuring the uniqueness of both its surrogate key and its critical business key combination.

Key Information Stored

The table stores the essential foreign key relationships that define a zone's composition. The most critical columns, as indicated by the primary key constraint MSC_ZONE_REGIONS_U1, are PARENT_REGION_ID and REGION_ID. The PARENT_REGION_ID column holds the identifier for the user-defined zone (the parent grouping). The REGION_ID column holds the identifier for a specific region that is a member of that zone. The ZONE_REGION_ID column, protected by the MSC_ZONE_REGIONS_PK constraint, is a unique system-generated surrogate key for each mapping record. This design allows a single region to potentially belong to multiple different zones, providing flexible planning hierarchies.

Common Use Cases and Queries

A primary use case is generating reports to audit and validate the structure of planning hierarchies. Planners often need to understand the complete composition of a zone or identify all zones a particular region belongs to. Common SQL queries involve joining this table to the base regions table (often MSC_GEOGRAPHY) to resolve region names. For example, to list all regions within a specific zone, one would query: SELECT r.region_name FROM msc_zone_regions z, msc_geography r WHERE z.region_id = r.region_id AND z.parent_region_id = <ZONE_ID>;. Conversely, to find all parent zones for a given region: SELECT parent_region_id FROM msc_zone_regions WHERE region_id = <REGION_ID>;. These mappings are critical during the plan launch process, where ASCP aggregates data according to these defined hierarchies.

Related Objects

The table's design indicates strong relationships with other region-master data tables through its key columns. The PARENT_REGION_ID and REGION_ID columns are foreign keys referencing a primary key in a master regions table, most commonly MSC_GEOGRAPHY or a similar entity storing geographic and organization definitions. The existence of the MSC_ZONE_REGIONS_U1 primary key on these two columns confirms that the relationship between a specific parent zone and a child region is unique and directly enforced at the database level. This table is a foundational piece for ASCP's planning engine, which references these mappings to perform aggregate calculations and hierarchical plan output.