Search Results msc_companies_u2

Overview

MSC.MSC_COMPANIES is a reference and master data table in the MSC (Advanced Supply Chain Planning / VCP) schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the list of individual companies that participate in inter-enterprise collaboration and planning within the Oracle Advanced Planning environment. Each row represents one legal or planning entity known to the planning server, whether originating from the local ERP instance or from a remote trading partner system.

The table is populated by Collection programs running against Oracle ERP systems, or by XML/Excel loads of trading partner data downloaded from external systems. Company identifier 1 is reserved for the operator company defined by the profile option MSC_OPERATOR_COMPANY_NAME, which anchors the local instance within the collaborative planning network. The physical schema resides in tablespace APPS_TS_TX_DATA with a PCTFREE of 10.

The heuristic Data Vault classification mined from the foreign key structure is standalone. In Data Vault modeling terms, this suggests MSC_COMPANIES functions as a hub-like reference entity for the company business key, with no dependent link tables documented within the supplied metadata. Modelers should treat COMPANY_NAME as the natural business key candidate.

Key Information Stored

The table comprises 25 documented columns. The most significant are:

• COMPANY_ID — System-generated numeric surrogate identifier. Defined as a unique index (MSC_COMPANIES_U1), it is the primary business-key candidate from a physical perspective and is referenced as the FK target by PN_COMPANIES_ALL.
• COMPANY_NAME — VARCHAR2(1000) company name; carries a unique index (MSC_COMPANIES_U2) and is the documented logical unique identifier of each company. A function-based nonunique index MSC_COMPANIES_N3 on UPPER("COMPANY_NAME") supports case-insensitive lookups.
• DISABLE_DATE — Date on which the company becomes inactive for planning purposes.
• REFRESH_NUMBER — Tracks the collection refresh cycle that last loaded or updated the row, critical for multi-org and multi-refresh planning data sets.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who audit columns present on nearly all MSC tables.
• ATTRIBUTE_CATEGORY — Descriptive flexfield structure-defining column (VARCHAR2(30)).
• ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield segments (VARCHAR2(150)) available for customer-specific extension.

Common Use Cases and Queries

Planners and technical consultants query MSC_COMPANIES to resolve company names against planning data, to filter disabled companies out of planning output, and to identify which refresh cycle a given company belongs to. A typical lookup resolves a user-entered name to its internal identifier:

SELECT company_id, company_name, disable_date, refresh_number
FROM msc.msc_companies
WHERE UPPER(company_name) = UPPER(:p_name)
  AND (disable_date IS NULL OR disable_date > SYSDATE);

Active-company listings for reporting use the N3 function-based index:

SELECT company_id, company_name
FROM msc.msc_companies
WHERE UPPER(company_name) LIKE UPPER('MEICO%')
  AND disable_date IS NULL;

Other recurrent scenarios include validating loaded trading-partner files before collection, reconciling the operator company against the profile option value, and joining to planning tables for supply/demand reports scoped by company.

Related Objects

The documented foreign key relationship identifies the primary related object:

• PN_COMPANIES_ALL — Referenced via MSC_COMPANIES.COMPANY_ID. This is the master company definition table in the PN schema. Note that within the MSC schema, COMPANY_ID is the surrogate key and COMPANY_NAME is the business-key candidate enforced by MSC_COMPANIES_U2.

Beyond the documented FK, MSC_COMPANIES is functionally associated with the profile option MSC_OPERATOR_COMPANY_NAME, which defines company identifier 1. It also participates conceptually with collection programs and XML/Excel load processes that populate inter-enterprise planning data across the MSC/VCP planning server.