Results for “msc_trading_partner_sites”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_TRADING_PARTNER_SITES is a table within the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) module of Oracle E-Business Suite. As its description states, it stores information about the different sites of an organization — specifically, the physical or logical locations associated with trading partners (customers, suppliers, and internal organizations) that participate in the planning model. Because ASCP plans across the extended supply chain, it must maintain its own representation of partner sites independent of transactional tables in other modules, and this table serves as that central reference.

In Data Vault modeling terms, the heuristic classification for this object is hub. It is a business-key-anchored entity holding the natural identity of a trading partner site, with descriptive attributes attached. It is not a link or satellite. Downstream tables reference it to establish relationships (allocations, sourcing, demands), which is consistent with hub behavior. Analysts building a Data Vault representation may treat MSC_TRADING_PARTNER_SITES as a hub keyed on PARTNER_SITE_ID, with satellites holding the descriptive and geographic attributes.

Key Information Stored

The table contains 43 columns. The most significant include:

• PARTNER_SITE_ID — the surrogate primary key, enforced by MSC_TRADING_PARTNER_SITES_PK and the unique index MSC_TRADING_PARTNER_SITES_U1.
• PARTNER_ID — foreign key to MSC_TRADING_PARTNERS, identifying the parent trading partner. It also participates in the composite unique index MSC_TRADING_PARTNER_SITES_U3 (PARTNER_ID, TP_SITE_CODE, LOCATION, OPERATING_UNIT_NAME), making these four columns the business-key candidates.
• TP_SITE_CODE — the site code used to identify the partner location.
• LOCATION — the location descriptor for the site, part of the business key.
• OPERATING_UNIT_NAME — the operating unit associated with the site, also part of the business key.
• PARTNER_TYPE — classifies the site, for example as customer or supplier oriented.
• DELETED_FLAG — soft-delete indicator for logically removed records.
• PARTNER_ADDRESS, COUNTRY, STATE, CITY, POSTAL_CODE — address and geographic attributes of the site.
• LATITUDE, LONGITUDE — geospatial coordinates, used in network and transportation planning.
• SHIPPING_CONTROL — controls shipping behavior for the site.
• SR_INSTANCE_ID and SR_TP_SITE_ID — source-instance and source-system identifiers, supporting multi-instance data integration.
• Standard audit columns (LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID) and the ATTRIBUTE1–15 descriptive flexfield columns.

Common Use Cases and Queries

Typical use cases include identifying all active supplier sites for sourcing analysis, resolving the shipping destination for demands, and reporting on partner site geography. A common query pattern joins this table to its parent partner table:

SELECT s.partner_site_id, s.tp_site_code, s.location,
       s.city, s.country, p.partner_number
FROM   msc_trading_partner_sites s,
       msc_trading_partners p
WHERE  s.partner_id = p.partner_id
AND    s.deleted_flag = 'N';

To find supplier sites referenced by sourcing rules, join to MSC_ITEM_SOURCING on SUPPLIER_SITE_ID. To examine demand destinations, join to MSC_DEMANDS on SHIP_TO_SITE_ID or CUSTOMER_SITE_ID. Geo-spatial reporting uses LATITUDE and LONGITUDE to place sites on a network map.

Related Objects

• MSC_TRADING_PARTNERS — parent entity; joined via PARTNER_ID. Also references sites through MODELED_CUSTOMER_SITE_ID and MODELED_SUPPLIER_SITE_ID.
• MSC_ITEM_SOURCING and MSC_ITEM_SUPPLIERS — reference sites via SUPPLIER_SITE_ID for sourcing and approved-supplier definitions.
• MSC_DEMANDS — references sites via SHIP_TO_SITE_ID and CUSTOMER_SITE_ID.
• MSC_ALLOCATIONS — references PARTNER_SITE_ID for allocation destinations.
• MSC_LOCATION_ASSOCIATIONS and MSC_PARTNER_CONTACTS — reference PARTNER_SITE_ID for location mapping and contact association.
• MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG, MSC_SR_SOURCE_ORG — shipping/receiving network tables referencing site IDs.
• Staging tables MSC_ST_TRADING_PARTNER_SITES, MSC_ST_PARTNER_CONTACTS, and MSC_ST_LOCATION_ASSOCIATIONS feed or mirror this data during collection.