Search Results msc_regions_temp_u1

Overview

MSC.MSC_REGIONS_TEMP is a transient staging table in the Oracle EBS Advanced Supply Chain Planning (ASCP) schema, MSC. As documented in the ETRM metadata, the table temporarily holds geographical region data required to satisfy specific Available-to-Promise (ATP) requests. Rows are scoped to a session identifier and a partner site, making the table inherently session-bound and short-lived; content is populated and consumed within the lifecycle of an individual ATP or planning calculation.

The object is stored in the APPS_TS_NOLOGGING tablespace, a choice consistent with its temporary, high-turnover nature. From a Data Vault modeling perspective, the mined relationship data classifies this object as standalone: it holds no foreign keys to other MSC objects and is best treated as a transient satellite-style staging structure rather than a durable hub or link. Modelers should treat the classification as a heuristic suggestion, since the table's role is operational staging rather than persistent dimensional storage.

Key Information Stored

The documented physical schema defines six columns. The most significant are:

• SESSION_ID (NUMBER) — Identifies the session that owns the staged region rows; the primary scoping key.
• PARTNER_SITE_ID (NUMBER) — The partner (supplier or customer) site the region data applies to.
• REGION_ID (NUMBER) — The geographical region identifier being staged.
• REGION_TYPE (NUMBER) — Encodes the region granularity: 0 = Country, 1 = State, 2 = City, 3 = Zipcode.
• ZONE_FLAG (VARCHAR2) — 'Y' indicates a region zone; 'N' indicates a region.
• PARTNER_TYPE (NUMBER) — Distinguishes the partner category: 1 = Supplier, 2 = Customer.

The primary key MSC_REGIONS_TEMP_PK is a composite surrogate key on (SESSION_ID, PARTNER_SITE_ID, REGION_ID). A second, broader uniqueness constraint is exposed through the unique index MSC_REGIONS_TEMP_U1 on (SESSION_ID, PARTNER_SITE_ID, REGION_ID, PARTNER_TYPE). Because REGION_TYPE, ZONE_FLAG, and PARTNER_TYPE are conditional qualifiers, the unique index MSC_REGIONS_TEMP_U1 functions as the practical business-key candidate, ensuring that a given partner site is not mapped to the same region under the same partner category more than once per session.

Common Use Cases and Queries

Typical usage centers on ATP region resolution and planning diagnostics. A support or development query to inspect staged regions for a session follows the documented query text:

• SELECT SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE FROM MSC.MSC_REGIONS_TEMP;
• Filter by SESSION_ID to isolate a single ATP run's geography set.
• Group by REGION_TYPE to validate that country/state/city/zipcode hierarchies were staged correctly.
• Filter on ZONE_FLAG = 'Y' to identify zone-level definitions versus individual regions.
• Filter on PARTNER_TYPE to separate supplier-side from customer-side staging.

Because the table is nologging and transient, it is not a reliable source for historical reporting; it is most valuable for troubleshooting faulty ATP results and for confirming that the correct geographic scope was assembled before promise-date calculation.

Related Objects

The ETRM metadata records that MSC.MSC_REGIONS_TEMP does not reference any database object, and is referenced only by the APPS synonym MSC_REGIONS_TEMP. The principal join columns available for correlation with surrounding ATP and planning objects are SESSION_ID, PARTNER_SITE_ID, and REGION_ID, which align with the ATP session, partner site, and geography dimensions used throughout the MSC schema. External tables commonly consulted alongside this staging data include partner site and region definition sources in the MSC and MRP schemas; because no foreign keys are documented, joins must be constructed manually on these shared columns rather than through declared constraints.