Search Results msc_tp_id_lid_pk

Overview

MSC_TP_ID_LID is a table in the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) product within Oracle E-Business Suite. It is populated and maintained by the data collection program, which extracts master and transactional data from source EBS instances and stages it for planning. The table's specific function is to hold the cross-reference between trading partner identifiers as they exist in each source instance and the internal planning identifier assigned within the planning server. Because ASCP collects data from one or more source instances, each of which may assign its own surrogate keys to the same logical trading partner, a mapping layer is required to reconcile those source keys into a single planning-side identity.

From a Data Vault modeling perspective, the mined classification for this object is standalone. This is a heuristic suggestion rather than a documented design intent: the table carries no foreign key relationships to other MSC objects and therefore cannot be characterized as a pure link table. In practice, its composite key structure — a source identifier, a partner type, and an instance identifier — behaves much like a link between a source-system reference and a planning-level partner identifier, though the absence of enforced foreign keys means the classification should be treated as advisory.

Key Information Stored

The documented physical schema contains eight columns. The most significant are described below.

• SR_TP_ID — the trading partner identifier as defined in the source instance. This is the source-side natural reference that the collection program reads from the originating application.
• SR_INSTANCE_ID — identifies which source instance the SR_TP_ID value belongs to. Because the same partner may exist in multiple instances, this column is essential for disambiguation.
• PARTNER_TYPE — classifies the trading partner, allowing a single identifier namespace to be partitioned across partner categories such as customer, supplier, or carrier.
• TP_ID — the planning-side identifier assigned to the trading partner. This is the value used by downstream planning logic in place of the source identifier.
• SR_COMPANY_ID — the source-side company or operating unit identifier associated with the partner record, supporting multi-organization source configurations.
• AGGREGATE_DEMAND_FLAG — a control flag indicating whether demand for the partner is to be aggregated during planning.
• SR_CUST_ACCOUNT_NUMBER — the customer account number from the source system, retained for traceability back to the originating records.
• RESOURCE_TYPE — indicates the resource category associated with the trading partner mapping.

The surrogate primary key is MSC_TP_ID_LID_PK, defined over (SR_TP_ID, PARTNER_TYPE, SR_INSTANCE_ID). A separate unique index, MSC_TP_ID_LID_U1, is defined over (SR_INSTANCE_ID, SR_TP_ID, PARTNER_TYPE, SR_COMPANY_ID) and represents the documented business-key candidate — it extends the primary key with the company identifier, ensuring a partner is unique within an instance, partner type, and company combination.

Common Use Cases and Queries

The principal use case is diagnostic: resolving why a trading partner appears under one identifier in planning while originating under another in the source instance. A typical lookup joins the mapping to planning tables on TP_ID:

• Locating the planning identifier for a known source partner: SELECT TP_ID FROM MSC_TP_ID_LID WHERE SR_INSTANCE_ID = :inst AND SR_TP_ID = :tp AND PARTNER_TYPE = :type;
• Confirming uniqueness of a collected partner across companies using the business-key index, which is useful when duplicate partner records are suspected after a collection run.
• Auditing collection completeness by counting distinct TP_ID values grouped by SR_INSTANCE_ID and PARTNER_TYPE, revealing instances or partner categories that failed to collect.
• Identifying partners flagged with AGGREGATE_DEMAND_FLAG to validate demand aggregation settings after configuration changes.

Related Objects

No foreign key relationships are documented for this object, consistent with its standalone classification. Operational relationships are nonetheless implied through shared column semantics rather than enforced constraints:

• Source instance registration tables, joined on SR_INSTANCE_ID, to resolve instance names and connection details.
• Planning-side trading partner tables in the MSC schema, joined on TP_ID, to retrieve the planning representation of the partner.
• Collection run and log tables, which record the execution that populated or refreshed these mappings.
• Customer and supplier master tables in the source applications, joined on SR_TP_ID, PARTNER_TYPE, and SR_COMPANY_ID, to trace mappings back to their originating records.

Because no referential constraints exist, all such joins are conventional and must be validated against the business-key index rather than relied upon as enforced relationships.