Search Results msd_local_id_setup




Overview
Explore in AI Chat

Overview

MSD_LOCAL_ID_SETUP is a configuration table owned by the MSD schema within the Oracle E-Business Suite Demand Planning module (MSD – Demand Planning). Its documented purpose is to support the Legacy Collection process in generating Local IDs. As part of the broader MSD data model, this table acts as a setup repository that drives the numbering or identifier assignment mechanism used during data collection and staging. It stores 33 columns and is classified as VALID in the ETRM 12.2.2 documentation, indicating an active, supported object in the EBS environment.

From a Data Vault modeling perspective, the metadata heuristically classifies this object as standalone. This suggests the table does not function as a classic hub, link, or satellite in a normalized Data Vault sense, but rather as an independent setup or reference structure. It anchors its only documented foreign key relationship through LEVEL_ID to the MSD_LEVELS table, implying that local ID generation is scoped by a planning level hierarchy.

Key Information Stored

The table’s most significant columns fall into identity, scoping, and generic attribute categories:

  • LOCAL_ID – The primary identifier generated or managed by this setup, central to the Legacy Collection process.
  • INSTANCE_ID – Identifies the MSD application or planning instance context for the setup record.
  • LEVEL_ID – Foreign key to MSD_LEVELS; defines the hierarchy level within which the local ID is generated.
  • DATA_SOURCE_TYPE – Classifies the source or mechanism driving ID generation.
  • NUMBER1 – NUMBER5 – Generic numeric attribute columns used for flexible setup parameters.
  • CHAR1 – CHAR10 – Generic character attribute columns providing up to ten configurable text fields.
  • DATE1 – DATE5 – Generic date attributes for time-bound configuration values.
  • LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE – Standard audit columns tracking record lifecycle.
  • REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID – Concurrency and program context columns linking the setup record to the batch process that created or modified it.

The surrogate primary key appears to be LOCAL_ID combined with INSTANCE_ID or LEVEL_ID, though the documented metadata does not explicitly confirm a unique index. Business-key candidates center on the combination of instance, level, and data source type.

Common Use Cases and Queries

Typical usage involves diagnosing Local ID generation failures within Legacy Collection and validating setup data across planning levels.

  • Joining to MSD_LEVELS to determine which hierarchy level a given local ID setup applies to: SELECT l.LOCAL_ID, l.LEVEL_ID, lv.LEVEL_NAME FROM MSD.MSD_LOCAL_ID_SETUP l JOIN MSD.MSD_LEVELS lv ON l.LEVEL_ID = lv.LEVEL_ID;
  • Auditing recent configuration changes using LAST_UPDATE_DATE and LAST_UPDATED_BY filters.
  • Tracing batch execution by reviewing REQUEST_ID and PROGRAM_ID for specific collection runs.
  • Extracting generic CHAR1–CHAR10 or NUMBER1–NUMBER5 attributes to understand custom setup parameters defined for a local ID rule.

Related Objects

The most significant related objects include:

  • MSD_LEVELS – Referenced via MSD_LOCAL_ID_SETUP.LEVEL_ID → MSD_LEVELS.LEVEL_ID. This is the only documented foreign key and the primary join path.
  • MSD_LOCAL_ID or equivalent staging table – Consumes the setup to generate actual local IDs during Legacy Collection.
  • FND_CONCURRENT_REQUESTS – Joins through REQUEST_ID to identify the concurrent program run that populated or modified setup records.
  • FND_PROGRAM / FND_APPLICATION – Joins through PROGRAM_ID and PROGRAM_APPLICATION_ID for program metadata.
  • MSD_INSTANCES – Likely joins through INSTANCE_ID to resolve instance context.
  • FND_USER – Joins through CREATED_BY or LAST_UPDATED_BY for audit attribution.