Search Results msc_bis_periods




Overview
Explore in AI Chat

Overview

MSC_BIS_PERIODS is a table in the MSC schema of Oracle E-Business Suite, owned by the Advanced Supply Chain Planning (ASCP) product family. The table stores calendar period definitions, capturing the start and end dates that bound each planning period along with the fiscal and calendar attributes that describe how those periods are organized. In the ASCP context, period definitions form the temporal backbone for planning buckets, demand and supply aggregation, and business intelligence reporting executed through the BIS (Business Intelligence System) layer. Because planning engines operate on bucketed time rather than raw dates, MSC_BIS_PERIODS provides the authoritative mapping between a named period and the date range it represents.

From a Data Vault modeling perspective, the heuristic classification for this object is standalone, indicating it is not directly joined into a parent-child FK hierarchy within the documented schema. This suggests the object behaves as a reference or dimension-style table rather than a transactional hub or link, although the classification should be treated as a modeling suggestion rather than a definitive architectural designation.

Key Information Stored

The table comprises 29 documented columns. The primary key, MSC_BIS_PERIODS_PK, is a composite surrogate key spanning ORGANIZATION_ID, SR_INSTANCE_ID, PERIOD_SET_NAME, and PERIOD_NAME. This composite structure means a single period is uniquely identified only in the context of a specific organization, source instance, and period set.

Two unique indexes define additional business-key candidates. MSC_BIS_PERIODS_U1 covers SR_INSTANCE_ID, ORGANIZATION_ID, PERIOD_NAME, and PERIOD_SET_NAME, while MSC_BIS_PERIODS_U2 covers ORGANIZATION_ID, SR_INSTANCE_ID, PERIOD_SET_NAME, PERIOD_TYPE, PERIOD_YEAR, and PERIOD_NUM. The second index is significant because it establishes uniqueness across the calendar hierarchy rather than just the period name.

Among the most important columns are:

  • ORGANIZATION_ID — the inventory or planning organization that owns the period definition.
  • SR_INSTANCE_ID — the source instance identifier, supporting multi-instance or multi-source planning environments.
  • PERIOD_SET_NAME — the calendar or period set to which the period belongs.
  • PERIOD_NAME — the user-facing period label.
  • START_DATE and END_DATE — the bounded date range of the period.
  • YEAR_START_DATE and QUARTER_START_DATE — the fiscal year and quarter anchors for the period.
  • PERIOD_TYPE — the classification of the period (for example, standard or adjustment).
  • PERIOD_YEAR and PERIOD_NUM — the fiscal year and sequential period number.
  • QUARTER_NUM — the fiscal quarter number.
  • ADJUSTMENT_PERIOD_FLAG — indicates whether the period is an adjustment period.
  • DESCRIPTION — free-text description of the period.

Standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN), eight ATTRIBUTE columns, and a CONTEXT column are also present, following the standard EBS extensibility convention.

Common Use Cases and Queries

The most frequent use is resolving a date into its planning period, or vice versa. A typical query retrieves the period that contains a given transaction date:

  • Filtering by ORGANIZATION_ID, PERIOD_SET_NAME, and a target date falling between START_DATE and END_DATE.
  • Aggregating planning results by PERIOD_YEAR, QUARTER_NUM, and PERIOD_NUM for fiscal reporting.
  • Joining to planning or demand tables to roll up quantities into period buckets.
  • Identifying adjustment periods via ADJUSTMENT_PERIOD_FLAG for period-close or reconciliation logic.

A representative predicate pattern is: WHERE ORGANIZATION_ID = :org AND PERIOD_SET_NAME = :set AND :txn_date BETWEEN START_DATE AND END_DATE. Because PERIOD_TYPE participates in the U2 unique index, filtering by period type is also commonly required to avoid ambiguity between standard and adjustment periods within the same set.

Related Objects

The documented FK structure classifies MSC_BIS_PERIODS as standalone, meaning no formal foreign-key relationships were mined within the metadata. In practice, the table is referenced by planning and reporting objects that share its composite key columns. Significant relationships to consider include:

  • Planning and demand fact tables in the MSC schema that join on ORGANIZATION_ID and PERIOD_NAME.
  • Period set and calendar definition tables that share PERIOD_SET_NAME.
  • BI/BIS reporting views that aggregate on PERIOD_YEAR, QUARTER_NUM, and PERIOD_NUM.
  • Organization and source instance reference tables joined via ORGANIZATION_ID and SR_INSTANCE_ID.
  • Adjustment and period-close processing objects filtered on ADJUSTMENT_PERIOD_FLAG.

Because the object is standalone in the mined relationship data, join paths should be validated against the actual deployed schema rather than assumed from declarative constraints.