Search Results msc_tp_site_id_lid




Overview
Explore in AI Chat

Overview

MSC_TP_SITE_ID_LID is a table owned by the MSC schema and belongs to the Advanced Supply Chain Planning (ASCP) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It is populated by the ASCP data collection program, which extracts master and transactional data from source EBS instances and loads it into the planning server schema. The table maintains the correspondence between identifiers that originate in the source instance and the surrogate identifiers generated and used within the planning data model for trading partner sites.

In an ASCP topology, source instance identifiers (such as a vendor site or customer site primary key) cannot be assumed to be unique or stable across multiple source instances. The planning engine therefore assigns its own internal TP_SITE_ID, and this table records the cross-reference between the two identifier spaces. Each row ties a specific trading partner site, as seen through a specific source instance and partner type, to its planning-side identity.

Under a heuristic Data Vault classification derived from the foreign key structure, this object is modeled as a standalone entity. That classification should be treated as a modeling suggestion only: the table behaves as a mapping or cross-reference structure rather than as a hub, link, or satellite in a strict Data Vault sense, because it exists primarily to translate source keys into planning keys.

Key Information Stored

The documented physical schema contains eight columns. The most significant are the following:

  • SR_TP_SITE_ID — the trading partner site identifier as defined in the source instance. This is the source-side business key.
  • PARTNER_TYPE — discriminates the category of trading partner (for example, supplier or customer), since the same site identifier space may be shared across partner roles.
  • SR_INSTANCE_ID — identifies the source EBS instance from which the record was collected. This is essential in multi-instance planning deployments.
  • SR_COMPANY_ID — the source-side company or organization identifier associated with the site.
  • TP_SITE_ID — the surrogate site identifier used internally by the planning application.
  • OPERATING_UNIT — the operating unit context under which the site is defined.
  • LOCATION_ID — the location identifier linked to the trading partner site.
  • SR_CUST_ACCT_ID — the source customer account identifier when the partner is a customer.

The primary key, MSC_TP_SITE_ID_LID_PK, is a composite business key spanning SR_TP_SITE_ID, PARTNER_TYPE, and SR_INSTANCE_ID. A separate unique index, MSC_TP_SITE_ID_LID_U1, covers SR_INSTANCE_ID, SR_TP_SITE_ID, PARTNER_TYPE, and SR_COMPANY_ID, and serves as an additional business-key candidate that enforces uniqueness across the company dimension.

Common Use Cases and Queries

The dominant use case is resolving a source-side trading partner site identifier to its planning-side TP_SITE_ID during collection validation, data reconciliation, and planning data debugging. A typical query joins the table to planning supply and demand tables to report activity by source site:

  • Source-to-planning translation: SELECT TP_SITE_ID FROM MSC.MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID = :inst AND SR_TP_SITE_ID = :site AND PARTNER_TYPE = :type;
  • Duplicate or orphan detection: grouping by SR_INSTANCE_ID, SR_TP_SITE_ID, and PARTNER_TYPE to identify rows that lack a matching planning-side record.
  • Collection reconciliation: counting rows per SR_INSTANCE_ID to confirm that a collection run loaded the expected number of trading partner sites.
  • Reporting: joining to planning tables to attribute plan output back to source operating units and companies using OPERATING_UNIT and SR_COMPANY_ID.

Related Objects

The table is classified as standalone, with no documented foreign keys to other objects. In practice it is referenced conceptually alongside the following planning and collection objects, joined through the columns noted:

  • MSC_TP_SITES — the trading partner site planning entity, joined on TP_SITE_ID.
  • MSC_SYSTEM_ITEMS and planning item tables — joined indirectly through source and location identifiers.
  • MSC_SR_INSTANCES — the source instance registry, joined on SR_INSTANCE_ID.
  • MSC_PARTNER_TYPES — the partner type reference, joined on PARTNER_TYPE.
  • MSC_COLLECTION_* collection program tables — used together during load and reconciliation.

Because the table is maintained exclusively by the collection program, it should not be updated directly; corrections must be driven through re-collection of the affected source instance.