Search Results msc_apps_instances_pk

Overview

MSC_APPS_INSTANCES is a configuration and registration table within the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Each row represents a distinct connected Oracle Applications instance that participates in the planning data model — for example, a source transactional instance feeding a central planning instance, or the planning instance itself. The table therefore acts as the master registry that binds together collection definitions, source/target database links, organization assignments, and planning engine configuration.

From a Data Vault modeling perspective, the heuristic classification of this object is hub-leaning. The instance identity is the stable business concept being registered, with INSTANCE_ID serving as the durable key, while the numerous descriptive and technical columns behave as satellite-style attributes describing each instance at a point in time. This classification is a modeling suggestion derived from foreign key structure rather than a documented Oracle designation.

Key Information Stored

The table contains 64 documented columns. The most operationally significant include:

• INSTANCE_ID — the primary key (MSC_APPS_INSTANCES_PK) and the unique business-key candidate (MSC_APPS_INSTANCES_U1). This surrogate identifier is referenced by downstream planning objects.
• INSTANCE_CODE — the user-facing short name identifying the connected instance.
• APPS_VER and DBS_VER — the application and database version of the connected instance, used for compatibility checks during collection.
• INSTANCE_TYPE — classifies the instance (for example, source or destination role).
• A2M_DBLINK and M2A_DBLINK — the database links used for Applications-to-MSC and MSC-to-Applications connectivity.
• ENABLE_FLAG and ST_STATUS — control whether the instance is active for collection and its current status.
• COMPANY_ID — foreign key to PN_COMPANIES_ALL, linking the instance to its legal entity context.
• GMT_DIFFERENCE — time-zone offset applied when aligning collection timestamps.
• CURRENCY — the currency associated with the instance configuration.
• COLLECTIONS_START_TIME and LAST_TGT_CONT_COLL_TIME — scheduling and last-run timestamps for the collections process.
• STAGING_COPY_ENABLED and STAGING_COPY_COMPLETE — govern staging-copy behavior during data refresh.
• LAST_IBUC_COLL_DATE and IBUCCP_LCID — tracking for the IBUC-based collection cycle.
• PJM_ENABLED — indicates whether Project Manufacturing functionality is enabled for the instance.
• Standard audit columns (LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, REQUEST_ID, PROGRAM_ID) and the ATTRIBUTE1–15 descriptive flexfield columns.

The distinction between the surrogate key and the business key matters: INSTANCE_ID is the enforced primary key and is also the unique-indexed column, meaning it operates as both the technical row identifier and the de facto business identifier for an instance in most DBA and integration queries.

Common Use Cases and Queries

Typical scenarios include verifying which source instances are enabled for collection, auditing application and database versions across connected instances before upgrade, and troubleshooting database links or time-zone offsets when collection jobs fail.

A common query lists active instances with their connectivity and version details:

SELECT instance_id, instance_code, apps_ver, dbs_ver,
       a2m_dblink, m2a_dblink, gmt_difference
FROM   msc.msc_apps_instances
WHERE  enable_flag = 'Y';

Collection monitoring frequently joins this table to organization mappings and compares COLLECTIONS_START_TIME with LAST_TGT_CONT_COLL_TIME to detect stalled or delayed refreshes. Reporting customers often expose INSTANCE_CODE alongside plan and org data so planners can attribute supply and demand to the correct source instance.

Related Objects

The most significant dependent object is MSC_INSTANCE_ORGS, which references this table through SR_INSTANCE_ID. This child table maps organizations to each connected instance and is the primary mechanism by which instance-scoped organization data is scoped.

The table itself references PN_COMPANIES_ALL through COMPANY_ID, tying instance registration to company and legal-entity definitions. Broader ASCP processing — collections programs, staging-copy routines, and planning engine setup — depends on the A2M_DBLINK, M2A_DBLINK, and status columns defined here, while collections scheduling logic consumes COLLECTIONS_START_TIME and the IBUC tracking columns stored on the same row.