Search Results msc_regions_temp_pk

Overview

MSC_REGIONS_TEMP is a transient staging table in the MSC schema, owned by Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to temporarily hold geographical region data required during an Available-to-Promise (ATP) request. Because it is a temporary working store rather than a persistent master or transactional entity, rows are scoped to a planning or ATP session and are typically purged once the request completes.

The ETRM metadata assigns this object a heuristic Data Vault classification of standalone. From a modeling perspective, this suggests the table behaves as neither a durable hub, link, nor satellite, but as session-scoped reference data materialized for the duration of an ATP computation. Its composite primary key (SESSION_ID, PARTNER_SITE_ID, REGION_ID) reinforces that rows are only meaningful within a single session.

Key Information Stored

Six columns are documented for MSC_REGIONS_TEMP at 12.2.2:

• SESSION_ID — Identifies the ATP session that owns the temporary region rows. Part of the primary key and the leading column of the unique index.
• PARTNER_SITE_ID — The partner (customer or ship-to) site for which region resolution is being performed. Part of the primary key.
• REGION_ID — The geographical region identifier. Part of the primary key.
• REGION_TYPE — Classifies the region (for example, a level or category of geographic hierarchy).
• ZONE_FLAG — A flag indicating zone-related behavior, used to qualify how the region participates in ATP sourcing or lead-time logic.
• PARTNER_TYPE — Distinguishes the partner category; this column appears only in the unique index MSC_REGIONS_TEMP_U1, not in the primary key.

The surrogate/business key distinction is explicit here: MSC_REGIONS_TEMP_PK (SESSION_ID, PARTNER_SITE_ID, REGION_ID) enforces row identity, while MSC_REGIONS_TEMP_U1 (SESSION_ID, PARTNER_SITE_ID, REGION_ID, PARTNER_TYPE) is the documented business-key candidate. The unique index is the stricter of the two, adding PARTNER_TYPE.

Common Use Cases and Queries

Typical use cases center on debugging ATP region resolution and tracing which regions were evaluated for a given partner site within a session.

• Inspect rows for a specific session: SELECT session_id, partner_site_id, region_id, region_type, zone_flag, partner_type FROM msc.msc_regions_temp WHERE session_id = :session_id;
• Confirm duplicate/business-key violations against the unique index by grouping on SESSION_ID, PARTNER_SITE_ID, REGION_ID, PARTNER_TYPE.
• Reporting: summarize regions by REGION_TYPE or ZONE_FLAG to validate that the correct zones were applied during ATP processing.
• Housekeeping: identify stale sessions still holding rows, since the table is temporary and should be self-clearing after request completion.

Because the table is session-scoped, queries should always be filtered by SESSION_ID to avoid cross-session results.

Related Objects

The ETRM FK analysis classifies MSC_REGIONS_TEMP as standalone, so no foreign-key relationships were mined. The most relevant dependencies derive from its ATP and ASCP context rather than enforced constraints:

• MSC_ATP_* / ATP processing packages — the ATP engine that populates and consumes this table.
• MSC_PARTNER_SITES — source of PARTNER_SITE_ID values.
• MSC_REGIONS / region definition tables — source of REGION_ID, REGION_TYPE.
• MSC_SYSTEM_ITEMS and related ASCP planning tables
• Session/master tables supplying SESSION_ID.

These objects are linked logically via SESSION_ID, PARTNER_SITE_ID, and REGION_ID rather than by database-enforced foreign keys.