Results for “msc_st_system_items”

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

Overview

MSC_ST_SYSTEM_ITEMS is a staging table owned by the MSC schema within the Oracle Advanced Supply Chain Planning (ASCP) product family. Its documented purpose is to receive, validate, and process item master data before that data is promoted into the MSC_SYSTEM_ITEMS planning table. The staging table therefore sits at the boundary between the transactional source systems (typically the MTL_SYSTEM_ITEMS_B and related inventory item tables) and the planning engine, which consumes the cleansed copy during a planning run.

Because ASCP collections operate on a high-volume, repeatable basis, MSC_ST_SYSTEM_ITEMS is populated by the collections program, keyed through a batch and request context, and then validated row by row. The REQUEST_ID, REFRESH_ID, SR_INSTANCE_ID, and BATCH_ID columns provide the execution context that links staged rows to a particular collection cycle and source instance.

From a modelling perspective, the Data Vault classification supplied for this object is standalone, derived heuristically from its foreign key structure. This indicates that MSC_ST_SYSTEM_ITEMS is not modelled as a hub, link, or satellite against other MSC objects but instead functions as a self-contained staging entity with only two outgoing references, to MTL_ABC_CLASSES and PN_COMPANIES_ALL.

Key Information Stored

The table carries 171 documented columns in the 12.2.2 physical schema. The most significant grouping is the item identity and organisation context:

• ORGANIZATION_ID / SR_ORGANIZATION_ID — source and staged organisation identifiers for the item.
• INVENTORY_ITEM_ID / SR_INVENTORY_ITEM_ID — the transactional item key and its source twin.
• ITEM_NAME, DESCRIPTION, REVISION — descriptive and revision attributes carried into planning.
• ABC_CLASS_ID, ABC_CLASS_NAME — classification used for planning priority; ABC_CLASS_ID references MTL_ABC_CLASSES.
• MRP_PLANNING_CODE, PLANNING_MAKE_BUY_CODE, BOM_ITEM_TYPE — planning behaviour flags.
• FIXED_LEAD_TIME, VARIABLE_LEAD_TIME, FULL_LEAD_TIME, CUMULATIVE_TOTAL_LEAD_TIME — lead-time metrics used by the planner.
• FIXED_ORDER_QUANTITY, MINIMUM_ORDER_QUANTITY, MAXIMUM_ORDER_QUANTITY, FIXED_LOT_MULTIPLIER — lot-sizing inputs.
• SAFETY_STOCK_BUCKET_DAYS, MRP_SAFETY_STOCK_CODE, MRP_SAFETY_STOCK_PERCENT — safety stock policy.
• ST_TRANSACTION_ID, MESSAGE_ID, PROCESS_FLAG, ERROR_TEXT — staging control and error reporting columns.
• REQUEST_ID, REFRESH_ID, SR_INSTANCE_ID, BATCH_ID — collection execution context.
• LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, CREATION_DATE — standard audit columns.
• COMPANY_ID, COMPANY_NAME — the second documented foreign key, referencing PN_COMPANIES_ALL.

The staging table does not surface a single document-supplied surrogate primary key in the metadata; instead the batch context columns (BATCH_ID, ST_TRANSACTION_ID) plus INVENTORY_ITEM_ID and ORGANIZATION_ID serve as the natural business-key candidates for row identification during validation.

Common Use Cases and Queries

Because error rows persist in staging after a collection completes, the most frequent usage is diagnosing failed item loads. A typical query filters rows whose processing flag indicates failure:

• Select INVENTORY_ITEM_ID, ORGANIZATION_ID, ERROR_TEXT from MSC_ST_SYSTEM_ITEMS where PROCESS_FLAG is not null and ERROR_TEXT is not null, scoped to a specific REFRESH_ID.
• Reconciliation between staged and promoted rows by joining on INVENTORY_ITEM_ID and ORGANIZATION_ID against MSC_SYSTEM_ITEMS.
• Lead-time and lot-sizing exception reports comparing FULL_LEAD_TIME or FIXED_ORDER_QUANTITY across SR_INSTANCE_CODE values.
• Source-instance audits counting rows by SR_INSTANCE_ID and BATCH_ID to confirm completeness of a collection.

Reporting is generally read-only against this table; no supported DML is performed by end users.

Related Objects

The table participates in the collections pipeline alongside the following significant objects:

• MSC_SYSTEM_ITEMS — the validated planning target populated from this staging table.
• MTL_ABC_CLASSES — referenced via MSC_ST_SYSTEM_ITEMS.ABC_CLASS_ID.
• PN_COMPANIES_ALL — referenced via MSC_ST_SYSTEM_ITEMS.COMPANY_ID.
• MTL_SYSTEM_ITEMS_B — the usual transactional source of the item master rows staged here.
• MSC_ST_* sibling staging tables — companion staging objects loaded within the same collection BATCH_ID.

These relationships confirm that MSC_ST_SYSTEM_ITEMS is an inbound staging artefact feeding the MSC planning schema rather than a shared reference entity.