Search Results msc_supplier_flex_fences




Overview
Explore in AI Chat

Overview

MSC_SUPPLIER_FLEX_FENCES is a table within the MSC schema (Advanced Supply Chain Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores supplier flex fence information for planned items within an MPS, MRP, or DRP plan. Each row records the tolerance percentage that a supplier allows for a certain number of days in advance for a given item, enabling planners to model how far out a supplier can be expected to absorb demand or schedule changes without penalty. The object is classified as VALID and is owned by the MSC schema.

From a Data Vault modeling perspective, the heuristic classification is satellite-leaning. Metadata mining of the foreign key structure indicates that this table primarily carries descriptive, context-dependent attributes (tolerance percentages, fence days, plan scoping) that qualify a broader business entity rather than acting as a standalone hub or as a pure transactional link. This classification should be treated as a modeling suggestion rather than a definitive architectural designation.

Key Information Stored

The physical schema documents 40 columns. The following are the most operationally significant:

  • PLAN_ID — Identifies the plan (MPS, MRP, or DRP) to which the flex fence record belongs. Combined with TRANSACTION_ID, it forms the composite primary key via MSC_SUPPLIER_FLEX_FENCES_PK.
  • TRANSACTION_ID — The second component of the composite primary key.
  • SUPPLIER_ID and SUPPLIER_SITE_ID — Identify the supplier and the specific supplier site to which the fence and tolerance apply.
  • ORGANIZATION_ID and USING_ORGANIZATION_ID — Specify the inventory organization and consuming organization context.
  • INVENTORY_ITEM_ID — The planned item for which the flex fence is defined.
  • FENCE_DAYS — The number of days in advance that the fence covers.
  • TOLERANCE_PERCENTAGE — The supplier's allowed tolerance percentage for that fence period.
  • SR_INSTANCE_ID — Identifies the source instance (relevant in multi-instance or source-system-integrated planning configurations).
  • PARENT_ID — Used in the self-referencing hierarchical relationship documented in the FK data.
  • REFRESH_NUMBER — Tracks the plan refresh cycle in which the record was generated.
  • STATUS, APPLIED, and COLLECTED_FLAG — Control flags indicating record state and whether the data has been collected or applied to the plan.
  • Standard audit columns (LAST_UPDATE_DATE, CREATED_BY, PROGRAM_ID, REQUEST_ID, and the ATTRIBUTE1–ATTRIBUTE15 flexfield set) support concurrency, traceability, and extensibility.

Two unique indexes exist as business-key candidates: MSC_SUPPLIER_FLEX_FENCES_U1 on (PLAN_ID, TRANSACTION_ID) and MSC_SUPPLIER_FLEX_FENCES_U2 on (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, SUPPLIER_SITE_ID, FENCE_DAYS, SUPPLIER_ID). The second confirms that a supplier site's tolerance is uniquely defined per plan, organization, item, supplier, and fence horizon — distinct from the surrogate composite primary key.

Common Use Cases and Queries

Planners and developers query this table to report supplier tolerance profiles, validate fence configurations prior to plan runs, and troubleshoot why the planning engine applied or rejected schedule changes. A typical query retrieving tolerance windows for an item and supplier is:

  • SELECT plan_id, inventory_item_id, supplier_id, supplier_site_id, fence_days, tolerance_percentage FROM msc_supplier_flex_fences WHERE plan_id = :plan_id AND organization_id = :org_id ORDER BY fence_days;
  • SELECT * FROM msc_supplier_flex_fences WHERE plan_id = :plan_id AND refresh_number = :refresh_number AND applied = 'Y';
  • SELECT sr_instance_id, COUNT(*) FROM msc_supplier_flex_fences GROUP BY sr_instance_id; — useful for validating source instance distribution.

Common reporting scenarios include supplier compliance dashboards, fence-gap analysis (comparing defined fence days against lead times), and reconciliation of collected versus applied fence records using COLLECTED_FLAG and APPLIED.

Related Objects

The table is self-referencing: MSC_SUPPLIER_FLEX_FENCES.PLAN_ID and MSC_SUPPLIER_FLEX_FENCES.PARENT_ID point back to columns in the same table, supporting hierarchical or parent-child record structures. Beyond this documented self-relationship, significant related objects within the MSC planning schema include the plan definition and plan item tables that supply PLAN_ID and INVENTORY_ITEM_ID, the supplier and supplier site master tables that supply SUPPLIER_ID and SUPPLIER_SITE_ID, and the organization and source instance references tied to ORGANIZATION_ID and SR_INSTANCE_ID. Joins to these are typically performed on plan, item, supplier, and organization identifiers to enrich flex fence reporting with descriptive names. Because the ETRM metadata documents only the self-referencing foreign keys, dependent objects should be confirmed against the live data dictionary for a given instance before use in production queries.