Search Results msd_app_instance_orgs_pk

Overview

MSD.MSD_APP_INSTANCE_ORGS is a transactional table owned by the MSD schema within Oracle E-Business Suite. Its documented purpose is to reside in the source instance and hold the list of organizations for which data must be brought into the planning server. The Oracle Demand Planning (ODP) source view restricts extracted data using this table, making it a control artifact for the scope of planning data replication rather than a transactional fact store.

The table is classified as VALID and is registered in FND Design Data as MSD.MSD_APP_INSTANCE_ORGS. It is physically stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. From a Data Vault modeling perspective, the documented structure — a composite two-column unique index over INSTANCE_ID and ORGANIZATION_ID, without substantive descriptive columns — suggests this object behaves as a link (or reference/association) table connecting instances to organizations. This classification is a modeling heuristic rather than a documented constraint; the primary key columns themselves carry no intrinsic measures or attributes.

Key Information Stored

The core information in this table is captured by its two key columns, which together comprise the unique index MSD_APP_INSTANCE_ORGS_PK. The documented metadata does not identify a separate surrogate key column; the business-key candidate and the primary key are the same composite.

• INSTANCE_ID (NUMBER) — Source instance identifier, the first component of MSD_APP_INSTANCE_ORGS_PK. It identifies which source instance the organization mapping belongs to.
• ORGANIZATION_ID (NUMBER) — Source organization identifier, the second component of the primary key. It identifies the specific inventory/operating organization eligible for planning extraction.

Together, INSTANCE_ID and ORGANIZATION_ID define the functional scope of the table: the pairing asserts that the given organization belongs to the set of organizations selected for inclusion in the planning server's data load.

The remaining documented columns are administrative rather than business-relevant. Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — provide audit tracing for each row. Concurrent Who columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — record the concurrent program execution that last populated or refreshed the row, enabling traceability back to the planning data collection process. A Descriptive Flexfield block is present, consisting of ATTRIBUTE_CATEGORY (VARCHAR2(30)) as the structure-defining column and fifteen attribute segments (ATTRIBUTE1 through ATTRIBUTE15, each VARCHAR2(150)), available for customer-specific extensions.

Common Use Cases and Queries

The primary use case is determining and auditing which organizations are enabled for planning data extraction. Because the ODP source view filters on this table, a straightforward query identifies organizations in scope for a given instance:

SELECT instance_id, organization_id FROM msd.msd_app_instance_orgs WHERE instance_id = :instance_id;

A common reporting pattern joins this table to organization-related reference data to produce a human-readable in-scope list, restricting by the composite key:

SELECT a.instance_id, a.organization_id FROM msd.msd_app_instance_orgs a WHERE a.instance_id = :p_instance AND EXISTS (SELECT 1 FROM hr_operating_units o WHERE o.organization_id = a.organization_id);

Administrators also use the table to troubleshoot planning data collection issues, verifying whether a missing organization is absent from MSD_APP_INSTANCE_ORGS and therefore excluded by the source view. Concurrent Who columns support auditing which data-collection request last touched a given scope row.

Related Objects

The documented metadata identifies this object as standalone under the Data Vault heuristic, with no foreign keys formally declared. Practical dependencies arise through the shared key semantics and the documented planner integration:

• MSD_APP_INSTANCE_ORGS_PK — the unique index over (INSTANCE_ID, ORGANIZATION_ID) that enforces one row per instance/organization pairing.
• ODP source views — documented as restricting extracted data via this table; they reference INSTANCE_ID and ORGANIZATION_ID as filter predicates.
• Instance and organization reference objects (HR_ALL_ORGANIZATION_UNITS / HR_OPERATING_UNITS) join on ORGANIZATION_ID to resolve names.
• Planning data collection concurrent programs — recorded through REQUEST_ID, PROGRAM_APPLICATION_ID, and PROGRAM_ID, linking scope rows to their generating process.
• FND Design Data entry MSD.MSD_APP_INSTANCE_ORGS — governs registration of the object and its DFF context.

Because the documented schema is limited to key, audit, and DFF columns, lineage into larger planning staging tables occurs through the composite keys rather than through declared foreign-key constraints.