Search Results msc_group_companies




Overview
Explore in AI Chat

Overview

MSC_GROUP_COMPANIES is a table in the MSC schema, the database schema that underpins Oracle Advanced Supply Chain Planning (ASCP), a core component of the Oracle E-Business Suite Advanced Planning and Scheduling product family. The table stores membership information that associates companies with the various groups defined within the planning application. Group-to-company mapping is a foundational construct in ASCP, since many plan definitions, sourcing rules, assignment sets, and planner security profiles are resolved at the level of a group rather than an individual organization or company.

The object is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Its documented physical schema in the 12.2.2 ETRM reference contains 26 columns. From the foreign key relationships mined within the ETRM data vault, MSC_GROUP_COMPANIES is classified heuristically as a standalone object. In Data Vault modeling terms, this implies that the table most closely resembles a link or association entity rather than a pure hub or satellite, because its business purpose is to resolve the many-to-many style relationship between a group and the companies that belong to it. This classification should be treated as a modeling suggestion only, not as a definitive architectural property of the physical table.

Key Information Stored

The table's primary business purpose is captured by the pairing of GROUP_ID and COMPANY_ID. The columns documented in the ETRM metadata and most relevant to interpretation and querying are:

  • GROUP_ID — Identifies the planning group to which a company membership applies. This is the primary grouping key for resolving membership sets.
  • COMPANY_ID — Identifies the company that is a member of the group. This is the foreign key column, documented as referencing PN_COMPANIES_ALL.COMPANY_ID.
  • EFFECTIVE_DATE — The date from which the group membership becomes active, supporting time-phased membership resolution.
  • DISABLE_DATE — The date on which the membership is disabled or expires, enabling historical and date-effective enquiries.
  • CONTACT_USER — The user associated with the membership record, typically used for ownership or contact tracking.
  • CREATION_DATE, CREATED_BY — Standard Oracle who-column audit attributes recording when and by whom the row was created.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard who-column audit attributes recording the most recent modification and the login session responsible for it.
  • CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle descriptive flexfield (DFF) infrastructure, providing extensible storage for customer-specific attributes without schema modification.

The metadata does not document a surrogate primary key column distinct from the business keys; the GROUP_ID and COMPANY_ID pairing, together with the date-effective range, represents the practical business-key candidate for a membership record. The documented foreign key target for COMPANY_ID is PN_COMPANIES_ALL.

Common Use Cases and Queries

Typical uses of MSC_GROUP_COMPANIES fall into three areas: validating group membership during plan setup, reporting on which companies belong to which group as of a given date, and joining membership data to planning results or sourcing rules. A representative query resolves the current membership for a group by filtering on the effective and disable dates:

  • SELECT gc.GROUP_ID, gc.COMPANY_ID, gc.EFFECTIVE_DATE, gc.DISABLE_DATE FROM MSC.MSC_GROUP_COMPANIES gc WHERE gc.GROUP_ID = :group_id AND TRUNC(SYSDATE) BETWEEN gc.EFFECTIVE_DATE AND NVL(gc.DISABLE_DATE, TRUNC(SYSDATE) + 1);
  • Joining to PN_COMPANIES_ALL to return descriptive company information: SELECT pca.COMPANY_NAME, gc.GROUP_ID FROM MSC.MSC_GROUP_COMPANIES gc, PN_COMPANIES_ALL pca WHERE gc.COMPANY_ID = pca.COMPANY_ID;
  • Auditing membership changes over time using the EFFECTIVE_DATE and DISABLE_DATE columns together with the standard audit columns.

Because the who-columns and DFF attributes follow standard Oracle conventions, the table also supports generic audit and flexfield reporting routines used elsewhere in the MSC schema.

Related Objects

The following objects are most significant in relation to MSC_GROUP_COMPANIES, based on the documented foreign key relationship and the surrounding ASCP data model:

  • PN_COMPANIES_ALL — The parent entity referenced by MSC_GROUP_COMPANIES.COMPANY_ID; join on COMPANY_ID to obtain company attributes.
  • MSC_GROUPS — The group definition entity corresponding to GROUP_ID; join on GROUP_ID to resolve group names and attributes.
  • MSC_GROUP_MEMBERS — A companion membership table in the MSC schema that stores group membership for other entity types.
  • MSC_SYSTEM_ITEMS and MSC_SOURCING_RULES — Planning entities frequently scoped by group and resolved indirectly through the group definition.
  • MRP_ASSIGNMENT_SETS and associated assignment entities — Assignment constructs that consume group membership when determining planning scope.

These relationships should be confirmed against the current ETRM reference and the site-specific data model before being relied upon in production queries.