Results for “msc_alloc_time_phases_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_ALLOC_RULE_TIME_PHASES is a table in the MSC schema (Advanced Supply Chain Planning) that stores the time-phased definitions of allocation rules used during supply chain planning. Each row associates an allocation rule with an effective date range, allowing allocation logic to vary across planning horizons. In Oracle EBS 12.1.1 and 12.2.2, this table supports the time-fencing and demand prioritization behavior used by Advanced Supply Chain Planning (ASCP) when distributing constrained supply across demands.

Based on the FK topology provided in the ETRM metadata, this object is heuristically classified as satellite-leaning. It holds descriptive, time-bounded attributes (effective and disable dates) attached to a parent allocation rule, making it a natural candidate for a Data Vault satellite hanging off the MSC_ALLOCATION_RULES hub rather than a standalone hub or a pure link table.

Key Information Stored

The table contains nine documented columns. The most significant are:

• TIME_PHASE_ID — Surrogate primary key, enforced by the index MSC_ALLOC_TIME_PHASES_PK. This is also a documented business-key candidate via the unique index MSC_ALLOC_RULE_TIME_PHASES_U1, and it is the column most frequently referenced by downstream queries and joins.
• ALLOCATION_RULE_NAME — Foreign key to MSC_ALLOCATION_RULES, identifying the parent allocation rule to which this time phase belongs.
• EFFECTIVE_DATE — Start date from which the time-phased rule segment becomes active.
• DISABLE_DATE — Date on which the segment is deactivated, bounding the validity window.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created or modified the record and when.

The pairing of EFFECTIVE_DATE and DISABLE_DATE is central: it defines the time fence during which the associated allocation rule parameters apply. Together, TIME_PHASE_ID and ALLOCATION_RULE_NAME establish the relationship between the phase record and its parent rule.

Common Use Cases and Queries

Typical scenarios include reviewing active allocation phases for a given planning run, validating overlapping date ranges, and reporting on rule effectiveness for supply chain audits.

• Listing all phases for a specific allocation rule:

  SELECT TIME_PHASE_ID, ALLOCATION_RULE_NAME,
         EFFECTIVE_DATE, DISABLE_DATE
  FROM   MSC.MSC_ALLOC_RULE_TIME_PHASES
  WHERE  ALLOCATION_RULE_NAME = :rule_name
  ORDER BY EFFECTIVE_DATE;
  

• Identifying currently active phases:

  SELECT TIME_PHASE_ID, ALLOCATION_RULE_NAME
  FROM   MSC.MSC_ALLOC_RULE_TIME_PHASES
  WHERE  SYSDATE BETWEEN EFFECTIVE_DATE AND DISABLE_DATE;
  

• Detecting overlapping date windows for the same rule — useful for data quality checks before a planning run.
• Audit reporting using LAST_UPDATE_DATE, LAST_UPDATED_BY, and CREATION_DATE to trace configuration changes.

Because TIME_PHASE_ID is referenced by MSC_ALLOCATIONS, joins between allocations and their defining time phases are common in reconciliation and reporting queries.

Related Objects

• MSC_ALLOCATION_RULES — Parent table; joined via MSC_ALLOC_RULE_TIME_PHASES.ALLOCATION_RULE_NAME = MSC_ALLOCATION_RULES.ALLOCATION_RULE_NAME.
• MSC_ALLOCATIONS — Child table; references this table through MSC_ALLOCATIONS.TIME_PHASE_ID = MSC_ALLOC_RULE_TIME_PHASES.TIME_PHASE_ID.
• MSC_ALLOC_TIME_PHASES_PK — Primary key constraint/index on TIME_PHASE_ID.
• MSC_ALLOC_RULE_TIME_PHASES_U1 — Unique index supporting the business-key nature of TIME_PHASE_ID.
• Standard EBS audit and planner interfaces within the MSC module that consume allocation rule definitions during ASCP plan generation.