Results for “msc_st_location_associations”

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

Overview

MSC_ST_LOCATION_ASSOCIATIONS is a staging (interface) table in the MSC schema, owned by Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to hold raw, unprocessed location-association data collected from source systems by the ASCP collection program so that it can be validated, cleansed, and ultimately loaded into the base planning table MSC_LOCATION_ASSOCIATIONS. The "ST_" prefix denotes the staging layer of the ASCP data-collection architecture; records flow from source applications into this table, are validated by the collection program, and then transferred to the final planning table. Because it is a transient staging object, its contents reflect only the most recent collection cycle (or cycles identified by REFRESH_ID/BATCH_ID).

From a Data Vault modeling perspective, the mined foreign-key metadata classifies this table heuristically as standalone — that is, it does not present a clean hub, link, or satellite pattern on its own. In practice it behaves like a hybrid staging construct: it carries hub-like business keys (location code, partner, partner site), link-like relationships (location-to-partner-site associations), and satellite-like descriptive and audit attributes. It should therefore be treated as a source for downstream hub/link/satellite derivation rather than a conformed Data Vault entity itself.

Key Information Stored

The table contains 36 documented columns. The most significant for planning and integration purposes are:

• BATCH_ID / ST_TRANSACTION_ID — The staging batch and transaction identifiers that group and uniquely tag records within a collection run.
• LOCATION_ID — The identifier of the location being associated; a principal business-key candidate.
• PARTNER_ID / PARTNER_SITE_ID — The trading partner and partner site participating in the association; PARTNER_SITE_ID is a documented foreign key to MSC_TRADING_PARTNER_SITES.
• SR_TP_ID / SR_TP_SITE_ID — Source-system trading partner and site identifiers used for cross-referencing back to the originating application.
• SR_INSTANCE_ID / SR_INSTANCE_CODE — The source instance that supplied the record.
• LOCATION_CODE / ORGANIZATION_CODE / TP_SITE_CODE — Human-readable business codes used for validation and matching.
• COMPANY_ID / COMPANY_NAME — Legal entity context; COMPANY_ID is a documented foreign key to PN_COMPANIES_ALL.
• PARTNER_TYPE — Classifies the partner (supplier, customer, etc.).
• PROCESS_FLAG / DELETED_FLAG / ERROR_TEXT — Collection-program control columns indicating processing status, logical deletion, and any validation failure reasons.
• REFRESH_ID / REQUEST_ID / PROGRAM_ID — Audit and traceability to the concurrent request and program that performed the load.
• LAST_UPDATE_DATE / CREATION_DATE / LAST_UPDATED_BY / CREATED_BY — Standard EBS WHO columns.

No single-column surrogate primary key is documented in the metadata; LOCATION_ID combined with source and partner identifiers functions as the practical composite business key, while BATCH_ID and ST_TRANSACTION_ID provide the staging-level uniqueness expected of an interface table.

Common Use Cases and Queries

Typical uses include diagnosing failed collections, reconciling source-to-planning data, and monitoring staging throughput before a load. A frequent pattern is to query unprocessed or errored rows:

• Identifying records that failed validation: SELECT ST_TRANSACTION_ID, LOCATION_ID, PARTNER_SITE_ID, ERROR_TEXT FROM MSC_ST_LOCATION_ASSOCIATIONS WHERE PROCESS_FLAG IS NULL OR ERROR_TEXT IS NOT NULL;
• Summarizing a collection run: SELECT BATCH_ID, REFRESH_ID, COUNT(*) FROM MSC_ST_LOCATION_ASSOCIATIONS GROUP BY BATCH_ID, REFRESH_ID;
• Tracing a specific source instance's contributions grouped by SR_INSTANCE_ID.
• Reconciling staging rows against the loaded base table MSC_LOCATION_ASSOCIATIONS by joining on LOCATION_ID and PARTNER_SITE_ID.
• Extracting new/updated associations using LAST_UPDATE_DATE or CREATION_DATE filters for upstream error reporting or downstream analytics.

Because the table is transient, reporting should generally target the base table; staging queries are operational and diagnostic in nature.

Related Objects

The following objects are most relevant to this staging table, based on documented foreign-key metadata and ASCP collection architecture:

• MSC_LOCATION_ASSOCIATIONS — The base (destination) planning table that the collection program populates from this staging table.
• MSC_TRADING_PARTNER_SITES — Referenced by PARTNER_SITE_ID.
• PN_COMPANIES_ALL — Referenced by COMPANY_ID for legal entity context.
• MSC_SYSTEM_ITEMS / MSC_TRADING_PARTNERS — Related planning entities commonly joined on partner and location keys.
• MSC_ST_LOCATIONS and other MSC_ST_* staging tables — Siblings in the same collection batch used for consistent, cross-referential loading.
• FND_CONCURRENT_REQUESTS — Joined via REQUEST_ID for collection-run traceability.