Search Results msc_supplier_flex_fences_u1

Overview

MSC.MSC_SUPPLIER_FLEX_FENCES is an Advanced Supply Chain Planning (ASCP) transactional table that stores supplier flex fence information for planned items within an MPS, MRP, or DRP plan. Each row captures the tolerance percentage a supplier permits for a given number of advance days for a specific item, supplier, and supplier site combination. In effect, the table holds the time-phased capacity tolerance bands used by Oracle Planning to determine how far a supplier's commitment may vary from the requested quantity at given points in the planning horizon.

The object resides in the MSC schema and is physically stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10. From a dimensional modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification: the table records descriptive, time-phased attributes (fence days and tolerance percentage) attached to a plan-scoped transactional grain, rather than acting as a standalone hub or pure link. This classification is a modeling suggestion only; the table is not part of a formally published Data Vault model.

Key Information Stored

The table's physical schema in ETRM 12.2.2 documents 40 columns. The most significant include:

• PLAN_ID — Identifier of the owning plan; part of the primary key.
• TRANSACTION_ID — Unique identifier for the row; the second component of the surrogate primary key MSC_SUPPLIER_FLEX_FENCES_PK (PLAN_ID, TRANSACTION_ID).
• SUPPLIER_ID — Supplier identifier; a business-key component of unique index MSC_SUPPLIER_FLEX_FENCES_U2.
• SUPPLIER_SITE_ID — Supplier site identifier; also part of U2.
• ORGANIZATION_ID — Organization identifier; part of U2.
• SR_INSTANCE_ID — Source application instance identifier; part of U2 and used to distinguish source systems.
• INVENTORY_ITEM_ID — Inventory item identifier; part of U2.
• FENCE_DAYS — Number of advance days defining the fence boundary; part of U2.
• TOLERANCE_PERCENTAGE — Capacity tolerance percentage permitted by the supplier at that fence.
• USING_ORGANIZATION_ID — Using organization identifier.
• PARENT_ID — Reference to a parent transaction identifier.
• REFRESH_NUMBER — Refresh number associated with the plan run.
• STATUS, APPLIED, COLLECTED_FLAG — Processing and lifecycle state indicators.

Standard Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and Concurrent Who columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) support audit and concurrent-program traceability. ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 provide a descriptive flexfield extension area.

The surrogate primary key (PLAN_ID, TRANSACTION_ID) is distinct from the business-key candidate represented by MSC_SUPPLIER_FLEX_FENCES_U2 (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, SUPPLIER_SITE_ID, FENCE_DAYS, SUPPLIER_ID), which enforces uniqueness of a flex fence band per item/supplier/site/plan. A second unique index, MSC_SUPPLIER_FLEX_FENCES_U1, duplicates the PK columns.

Common Use Cases and Queries

Typical usage involves retrieving the tolerance percentage applicable to a given item and supplier site at a specific number of fence days, and validating that planned supply falls within contractual flex bounds.

• Reporting tolerance bands: SELECT inventory_item_id, supplier_id, supplier_site_id, fence_days, tolerance_percentage FROM msc.msc_supplier_flex_fences WHERE plan_id = :plan_id ORDER BY fence_days;
• Plan-scoped extraction for a refresh: filter by PLAN_ID and REFRESH_NUMBER to isolate the current plan generation.
• Multi-source consolidation: join through SR_INSTANCE_ID to separate records originating from distinct source instances.
• Data quality checks: compare U2 business keys against the PK to detect duplicates or orphaned flex fence definitions.

Related Objects

The documented relationship data references MSC_SUPPLIER_FLEX_FENCES itself for the PLAN_ID foreign key, indicating the table participates in plan-scoped referential structures. In practice, meaningful joins include:

• MSC.MSC_PLANS — join on PLAN_ID to resolve plan names and attributes.
• MSC.MSC_SYSTEM_ITEMS / MSC.MSC_ITEM_ORG_ASSIGNMENTS — join on INVENTORY_ITEM_ID and ORGANIZATION_ID for item descriptions.
• PO.PO_VENDORS / PO.PO_VENDOR_SITES_ALL — join on SUPPLIER_ID and SUPPLIER_SITE_ID for supplier and site details.
• MSC.MSC_SUPPLIER_CAPACITIES — companion table holding supplier capacity data aligned by item, supplier, and site.
• MSC.MSC_FLEXFENCE_DETAILS — related flex fence detail storage for plan scenarios.
• MSC.MSC_SR_INSTANCES — resolve SR_INSTANCE_ID to the source application instance.

Because the table is plan-scoped and frequently refreshed, queries should constrain by PLAN_ID and, where relevant, REFRESH_NUMBER to avoid returning stale generations.