Results for “tp_site_code”

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

Overview

MSC_ST_PARTNER_CONTACTS is a staging table in the MSC (Advanced Supply Chain Planning) schema. Its documented purpose is to hold inbound data collected from source systems so the collection program can validate and process it before loading the production table MSC_PARTNER_CONTACTS. It therefore acts as a transient landing area between external or upstream sources and the ASCP planning repository, carrying contact records associated with trading partners and their sites.

In Oracle EBS 12.1.1 and 12.2.2, staging tables of this kind support the collection and refresh cycle that keeps the planning schema synchronized with transactional and master data. Records are written with a refresh or batch identifier, validated by concurrent programs, flagged for processing, and then promoted or rejected with error text. The companion columns REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE make the load traceable to the concurrent request that produced it.

The heuristic Data Vault classification mined from the foreign key structure is standalone. As a modeling suggestion rather than a defined constraint, this implies the table is not a natural hub-and-satellite construct; it behaves as a staging link carrying references to trading partner sites and companies without an established parent-child dependency within the vault. Designers treating this as a Data Vault artifact would likely model the partner site and company references as links to their respective hubs rather than as embedded descriptive attributes.

Key Information Stored

The table is documented with 31 columns. The most significant are:

• NAME, DISPLAY_NAME — the contact's identifying and presentation values.
• PARTNER_ID, PARTNER_SITE_ID, PARTNER_TYPE — the trading partner and site context for the contact; PARTNER_SITE_ID carries a documented foreign key to MSC_TRADING_PARTNER_SITES.
• COMPANY_ID, COMPANY_NAME — company association; COMPANY_ID carries a documented foreign key to PN_COMPANIES_ALL.
• EMAIL, FAX, ENABLED_FLAG, DELETED_FLAG — contact channel details and record status flags.
• REFRESH_ID, SR_INSTANCE_ID, SR_INSTANCE_CODE — source system and refresh cycle identity, essential for distinguishing loads and source instances.
• PROCESS_FLAG, ERROR_TEXT, DATA_SOURCE_TYPE — validation outcome and processing control.
• MESSAGE_ID, ST_TRANSACTION_ID, BATCH_ID — message and batch tracking for the inbound feed.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program provenance.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns.

No surrogate primary key column (such as a sequence-generated ID) is documented in the column list; the closest business-key candidates are the combination of partner and site identifiers together with the refresh and source instance context. Because the ETRM excerpt does not list unique indexes, uniqueness should be verified against the actual database definition before relying on any column set as a natural key.

Common Use Cases and Queries

Typical scenarios include monitoring collection run success, diagnosing rejected contacts, and reconciling staging counts against MSC_PARTNER_CONTACTS after a load. Common patterns:

• Find newly staged contacts for a refresh: SELECT * FROM MSC.MSC_ST_PARTNER_CONTACTS WHERE REFRESH_ID = :refresh_id AND PROCESS_FLAG = 'N';
• Inspect failures: SELECT PARTNER_SITE_ID, NAME, ERROR_TEXT FROM MSC.MSC_ST_PARTNER_CONTACTS WHERE ERROR_TEXT IS NOT NULL;
• Trace a load to its request: SELECT REQUEST_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, COUNT(*) FROM MSC.MSC_ST_PARTNER_CONTACTS GROUP BY REQUEST_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE;
• Join to partner sites to enrich reporting with site codes and partner names using PARTNER_SITE_ID.
• Join to PN_COMPANIES_ALL on COMPANY_ID to resolve company attributes for validation reports.

Related Objects

• MSC_PARTNER_CONTACTS — the target production table loaded by the collection program from this staging table.
• MSC_TRADING_PARTNER_SITES — referenced by PARTNER_SITE_ID; supplies site detail for each contact.
• PN_COMPANIES_ALL — referenced by COMPANY_ID; provides legal company information.
• MSC_ST_PARTNER_SITES and other MSC_ST_* staging tables — processed together in the same refresh and batch cycle via REFRESH_ID and SR_INSTANCE_ID.
• MSC_TRADING_PARTNERS — the partner master referenced indirectly through PARTNER_ID and PARTNER_TYPE.
• Concurrent programs (MSC collection/refresh) — identified by PROGRAM_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID, which drive validation and posting.