Search Results msc_apps_instances_u1

Overview

The table MSC.MSC_APPS_INSTANCES is a foundational configuration and control object within the Oracle EBS Advanced Supply Chain Planning (ASCP) and MSC schemas. It stores one row per connected Application instance registered with the MSC planning server, effectively acting as the registry of source instances from which planning data is collected. Each record carries the database links, refresh state, and collection control attributes needed for the MSC engine to interface with and pull data from an operational EBS instance.

The object resides in tablespace APPS_TS_TX_DATA and is owned by the MSC schema. From a Data Vault modeling perspective, the metadata heuristics classify this table as hub-leaning. This classification is a modeling suggestion only: INSTANCE_ID functions as the natural business key of the hub, while the majority of remaining columns represent descriptive and stateful attributes that a disciplined Data Vault design would typically separate into one or more satellite tables keyed to that hub.

Key Information Stored

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

• INSTANCE_ID — The numeric Application instance identifier. This is the primary key (MSC_APPS_INSTANCES_PK) and is also the column of the unique index MSC_APPS_INSTANCES_U1, the business-key candidate the user searched for.
• INSTANCE_CODE — A unique code assigned to each application instance for human-readable identification.
• APPS_VER and DBS_VER — Application and database version numbers for the connected instance.
• INSTANCE_TYPE — Numeric discriminator (1 = discrete applications, 2 = OPM, 3 = other).
• A2M_DBLINK and M2A_DBLINK — The bidirectional database links (application-to-MSC and MSC-to-application), each up to 128 characters.
• ENABLE_FLAG — Y/N indicator of whether the instance is currently active for collection.
• LRID, LRTYPE, and APPS_LRN — Last refresh ID, refresh type (C = complete, I = incremental, P = partial), and last refresh number, tracking collection progress.
• LCID — Last executed collection program number.
• ST_STATUS and CLEANSED_FLAG — Status of instance data in the staging tables and the cleansing flag.
• GMT_DIFFERENCE — Time-zone offset used for date normalization during collection.
• COMPANY_ID — Foreign key to PN_COMPANIES_ALL, linking the instance to a company entity.
• Standard Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and concurrent Who columns (REQUEST_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE).
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — Descriptive flexfield structure and segment columns.

Common Use Cases and Queries

Typical queries validate instance connectivity, audit refresh state, and reconcile instance-level collection in planning cycles.

• Listing active instances: SELECT instance_id, instance_code, instance_type FROM msc.msc_apps_instances WHERE enable_flag = 1;
• Auditing last refresh: SELECT instance_code, lrid, lrtype, apps_lrn, last_update_date FROM msc.msc_apps_instances;
• Verifying database links: SELECT instance_code, a2m_dblink, m2a_dblink FROM msc.msc_apps_instances WHERE instance_id = :id;
• Joining to organizations for instance-to-org reporting: SELECT i.instance_code, o.* FROM msc.msc_apps_instances i JOIN msc.msc_instance_orgs o ON o.sr_instance_id = i.instance_id;

Related Objects

This table participates in several key relationships:

• MSC_INSTANCE_ORGS — References MSC_APPS_INSTANCES via SR_INSTANCE_ID, mapping instances to organizations.
• PN_COMPANIES_ALL — Referenced by MSC_APPS_INSTANCES.COMPANY_ID.
• Collection and refresh programs leverage LRID, LRTYPE, and LCID, tying this registry to the planning data collection engine.
• Staging tables (referenced by ST_STATUS) depend on instance registration for their data scope.

In summary, MSC.MSC_APPS_INSTANCES is the authoritative registry of planning instances, and MSC_APPS_INSTANCES_U1 uniquely enforces the instance identifier that anchors every dependent planning collection process.