Search Results msc_companies




Overview
Explore in AI Chat

Overview

MSC_COMPANIES is a planning-table owned by the MSC schema within Oracle Advanced Supply Chain Planning (ASCP). It stores the list of companies that participate in collaborative planning activities, functioning as a reference dimension for cross-enterprise supply chain collaboration. In EBS 12.1.1 and 12.2.2 the table is deployed under the MSC product group alongside other planning and collaboration objects, and it remains valid in both releases. The object is physically small by planning standards, carrying 25 documented columns in the 12.2.2 schema, and it is primarily populated and maintained through ASCP data collection rather than through direct user maintenance.

From a Data Vault modeling perspective, the mined foreign key metadata classifies MSC_COMPANIES as a standalone object. This heuristic suggests modeling MSC_COMPANIES as a hub for the company business concept, with its descriptive attributes loaded into a companion satellite. No downstream foreign-key dependencies are recorded against it, so it does not behave as a link table in the mined relationship graph.

Key Information Stored

The most important columns in MSC_COMPANIES are:

  • COMPANY_ID — the surrogate primary key of the table and the target of the unique index MSC_COMPANIES_U1. It is also a foreign key to PN_COMPANIES_ALL, establishing the link between the planning-side company list and its source record.
  • COMPANY_NAME — the descriptive business identifier for the company; it is the second business-key candidate, protected by the unique index MSC_COMPANIES_U2.
  • DISABLE_DATE — records when a company entry was disabled for collaboration, supporting soft-delete and effective-dating logic.
  • REFRESH_NUMBER — a collection-cycle indicator used by ASCP to track the refresh generation in which the row was loaded, enabling consistent snapshotting across planning runs.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard Oracle EBS audit columns, recording who inserted or last modified the row and when.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the flexible descriptive flexfield (DFF) segment columns, available for customer-specific extensions without a schema change.

The surrogate/business-key distinction is explicit: COMPANY_ID is the artificial identifier used in joins and foreign keys, while COMPANY_NAME is the human-readable unique value that users and reports recognize.

Common Use Cases and Queries

MSC_COMPANIES is typically queried to resolve company identifiers into names, to filter to only active collaboration partners, and to trace planning records back to a source company. A representative lookup that excludes disabled companies is:

  • SELECT company_id, company_name FROM msc.msc_companies WHERE disable_date IS NULL ORDER BY company_name;

Joining to the source master for reconciliation:

  • SELECT m.company_id, m.company_name, p.* FROM msc.msc_companies m, pn.pn_companies_all p WHERE m.company_id = p.company_id;

Collecting a list of company DFF attributes for a given refresh generation:

  • SELECT company_id, company_name, refresh_number, attribute1, attribute2 FROM msc.msc_companies WHERE refresh_number = :p_refresh;

Typical reporting scenarios include collaboration partner listings, audit reports of company master changes using the audit columns, and validation queries that confirm every planning company has a matching source record in PN_COMPANIES_ALL.

Related Objects

The most significant related objects, based on the documented foreign-key relationship and the ASCP collaboration model, are:

  • PN_COMPANIES_ALL — joined on COMPANY_ID: MSC_COMPANIES.COMPANY_ID references PN_COMPANIES_ALL, making it the upstream source of company master data.
  • MSC_COMPANIES_U1 — unique index on COMPANY_ID (primary-key enforcement).
  • MSC_COMPANIES_U2 — unique index on COMPANY_NAME (business-key enforcement).
  • MSC_AP_SUPPLIERS — planning supplier records that reference companies in collaboration flows.
  • MSC_CUSTOMERS — planning customer records that participate in the same collaborative planning context.
  • MSC_ST_SUPPLIER_SITES / MSC_ST_CUSTOMER_SITES — staging tables that feed company-related planning data during collection.
  • MSC_SYSTEM_ITEMS_B — planning item master, commonly combined with company data in collaboration extracts.

These objects, together with the MSC collection concurrent programs, form the operational context in which MSC_COMPANIES is referenced.