Results for “msc_st_item_suppliers”

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

Overview

MSC_ST_ITEM_SUPPLIERS is a staging table owned by the MSC schema within Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to serve as the collection program's staging area, where extracted source data is validated and processed before being merged into the interface table MSC_ITEM_SUPPLIERS. This staging architecture is a standard feature of ASCP data collections: source records flow from EBS transactional tables, land in an MSC_ST_* staging table, undergo validation and transformation, and are finally applied to the corresponding MSC_* interface table for use by the planning engine.

In the context of Oracle EBS 12.1.1 and 12.2.2, this table represents approved supplier list (ASL) sourcing relationships and supplier item attributes extracted for planning purposes. The ETRM 12.2.2 documented schema reports 57 columns and identifies two foreign key relationships: ASL_ID referencing PO_APPROVED_SUPPLIER_LIST and COMPANY_ID referencing PN_COMPANIES_ALL. The heuristic Data Vault classification mined from the FK structure is standalone. From a modeling perspective, this suggests the object behaves as a denormalized staging construct rather than a conformed hub, link, or satellite; its keys (INVENTORY_ITEM_ID, ORGANIZATION_ID, SUPPLIER_ID, SUPPLIER_SITE_ID) are embedded directly within it, and the surrogate ASL_ID simply carries the source ASL reference into the planning schema.

Key Information Stored

Although the table contains 57 documented columns, the most operationally significant ones cluster into item/supplier identity, sourcing attributes, and collection control metadata.

• INVENTORY_ITEM_ID, ORGANIZATION_ID – Business-key components identifying the planned item and its owning organization.
• SUPPLIER_ID, SUPPLIER_SITE_ID – The supplier and supplier site for the sourcing relationship.
• USING_ORGANIZATION_ID, USING_ORGANIZATION_CODE – The organization that can source the item from the given supplier.
• ASL_ID – FK to PO_APPROVED_SUPPLIER_LIST; the approved supplier list entry from which attributes derive.
• PROCESSING_LEAD_TIME, MINIMUM_ORDER_QUANTITY, FIXED_LOT_MULTIPLE, FIXED_ORDER_QUANTITY, MAXIMUM_ORDER_QUANTITY – Sourcing and lot-sizing parameters consumed by the planning engine.
• ITEM_PRICE – Supplier item price used in planning cost rollups.
• VMI_FLAG, VMI_REPLENISHMENT_APPROVAL, ENABLE_VMI_AUTO_REPLENISH_FLAG – Vendor-managed inventory indicators.
• MIN_MINMAX_QUANTITY, MAX_MINMAX_QUANTITY, MIN_MINMAX_DAYS, MAX_MINMAX_DAYS – Min-max replenishment thresholds.
• REPLENISHMENT_METHOD, FORECAST_HORIZON – Replenishment planning controls.
• COMPANY_ID, COMPANY_NAME – FK to PN_COMPANIES_ALL; the supplier company context.
• SR_INSTANCE_ID, SR_INSTANCE_CODE, REFRESH_ID, BATCH_ID – Collection and refresh identifiers used to scope each data load.
• PROCESS_FLAG, ERROR_TEXT, MESSAGE_ID, ST_TRANSACTION_ID, DATA_SOURCE_TYPE – Staging control columns indicating validation status, error diagnostics, and record source.
• DELETED_FLAG – Marks records logically removed at source.

The foreign keys on ASL_ID and COMPANY_ID serve as business-key candidates that link staging records back to their authoritative source rows; the remaining identifiers are collection-managed rather than user-maintained.

Common Use Cases and Queries

The principal operational use case is troubleshooting ASCP collections. Planners and technical analysts query this table to confirm that supplier-item data was extracted, to inspect validation failures via ERROR_TEXT, and to determine whether a record has been successfully processed.

• Identify failed staging records: SELECT INVENTORY_ITEM_ID, SUPPLIER_ID, ERROR_TEXT FROM MSC_ST_ITEM_SUPPLIERS WHERE PROCESS_FLAG = 'E' OR ERROR_TEXT IS NOT NULL;
• Verify supplier attributes loaded for a specific item and organization: SELECT SUPPLIER_ID, PROCESSING_LEAD_TIME, MINIMUM_ORDER_QUANTITY, ITEM_PRICE FROM MSC_ST_ITEM_SUPPLIERS WHERE INVENTORY_ITEM_ID = :item AND ORGANIZATION_ID = :org;
• Audit VMI sourcing relationships: SELECT ITEM_NAME, VENDOR_NAME, VMI_FLAG FROM MSC_ST_ITEM_SUPPLIERS WHERE VMI_FLAG = 'Y';
• Track collection batches: SELECT BATCH_ID, SR_INSTANCE_CODE, COUNT(*) FROM MSC_ST_ITEM_SUPPLIERS GROUP BY BATCH_ID, SR_INSTANCE_CODE;

Reporting use cases include validating ASL coverage before plan runs and reconciling staging counts against MSC_ITEM_SUPPLIERS post-collection.

Related Objects

• MSC_ITEM_SUPPLIERS – The 12.x interface table that receives validated staging records.
• PO_APPROVED_SUPPLIER_LIST – Joined on ASL_ID = ASL_ID; the authoritative ASL source.
• PN_COMPANIES_ALL – Joined on COMPANY_ID = COMPANY_ID; supplier company definitions.
• MSC_ITEM_SUPPLIERS_TEMP / MSC_ST_SUPPLIERS – Companion staging/interface tables in the ASCP collection set.
• ASCP Collection Programs (MSC_*_COL) – The concurrent programs that read, validate, and merge this staging table.
• MTL_SYSTEM_ITEMS_B – Supplies INVENTORY_ITEM_ID and ORGANIZATION_CODE context.