Search Results msd_app_instance_orgs




Overview
Explore in AI Chat

Overview

MSD_APP_INSTANCE_ORGS is a table owned by the MSD schema and belongs to the Oracle Demand Planning module (MSD). It resides in the source instance of an Oracle E-Business Suite environment and is not a transactional planning table itself. Instead, it serves as a control and filtering mechanism that defines which inventory organizations are eligible to have their data collected and transferred into the planning server. This table is central to the Oracle Demand Planning (ODP) data collection architecture in EBS 12.1.1 and 12.2.2, where source-side views read from operational tables and restrict the rows they expose using the organization list maintained here.

The metadata documents 27 columns and a single unique index, MSD_APP_INSTANCE_ORGS_PK, defined on the composite key (INSTANCE_ID, ORGANIZATION_ID). From a Data Vault modeling perspective, the heuristic classification of this object is standalone, meaning it does not exhibit the foreign-key relationships that would define it as a hub, link, or satellite. As a modeling suggestion, its composite primary key and descriptive attributes could reasonably be interpreted as a link-like association between an instance and an organization, but no such relationship was mined from the documented FK structure.

Key Information Stored

The defining content of the table is its two primary key columns, which together identify each permitted row:

  • INSTANCE_ID — Identifies the application instance or planning collection context under which organizations are registered. This is the first component of the composite primary key.
  • ORGANIZATION_ID — The inventory organization identifier for which data must be brought into the planning server. This is the second component of the composite primary key and the primary business discriminator.

Supporting audit and operational columns follow the standard EBS WHO-column pattern: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN track row provenance and change history. REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE associate each row with the concurrent program execution that inserted or maintained it, which is important for tracing which collection run added a given organization. The remaining columns are fifteen generic ATTRIBUTE1 through ATTRIBUTE15 fields plus ATTRIBUTE_CATEGORY, which serve as the standard EBS descriptive-flexfield extension points and are typically unused by base Demand Planning functionality.

Common Use Cases and Queries

The principal use case is administering the set of organizations included in a Demand Planning data pull. Planning administrators insert, remove, or audit organization membership to control which source data reaches the planning server. A representative query lists the organizations currently enabled for a given instance:

  • SELECT organization_id FROM msd.msd_app_instance_orgs WHERE instance_id = :p_instance_id;
  • Reconciliation queries join this table against the inventory organization definitions to confirm that enabled organizations still exist and are valid.
  • Audit queries filter on CREATION_DATE, LAST_UPDATE_DATE, or REQUEST_ID to identify which concurrent request added or modified a given organization.
  • Reporting extracts use the composite key as the driving set when validating that ODP source views are returning the expected organization scope.

Related Objects

The metadata classifies this object as standalone, so no explicit foreign keys were mined. Related objects are therefore identified functionally rather than through documented FK constraints:

  • ODP source views — The documented consumers of this table; they restrict collected data using the organization list defined here.
  • ORG_ORGANIZATION_DEFINITIONS — Provides the organization name and classification corresponding to each ORGANIZATION_ID.
  • FND_CONCURRENT_REQUESTS — Joins on REQUEST_ID to attribute rows to specific collection program runs.
  • MSD_APP_INSTANCES — The logical companion that defines the instance referenced by INSTANCE_ID.
  • FND_APPLICATION and FND_CONCURRENT_PROGRAMS — Resolve PROGRAM_APPLICATION_ID and PROGRAM_ID to the owning application and program.