Search Results msc_process_effectivity

Overview

The MSC_PROCESS_EFFECTIVITY table is a core data object within the Advanced Supply Chain Planning (ASCP) module of Oracle E-Business Suite (EBS). It serves as a critical junction table within the planning engine's data model, establishing and storing the necessary links between a bill of material (BOM) and its associated routing. Its primary role is to manage the effectivity—the valid date range and usage context—for a specific manufacturing process step. This table is populated during the planning data snapshot process, where transactional manufacturing data is transformed into the planning server's internal schema (MSC). It enables the planning engine to accurately model production processes, including component consumption and resource requirements, across different effectivity dates when generating constrained or unconstrained plans.

Key Information Stored

The table's structure centers on identifiers that link to other primary planning entities and columns that define the process's validity. The primary key is a composite of PLAN_ID and PROCESS_SEQUENCE_ID, ensuring uniqueness within a specific plan. Key columns include PLAN_ID, which ties the record to a specific planning run; SR_INSTANCE_ID, identifying the source instance; and BILL_SEQUENCE_ID and ROUTING_SEQUENCE_ID, which are foreign keys to the snapshot BOM and routing definitions, respectively. The table also stores ITEM_ID and ORGANIZATION_ID to identify the manufactured item. Crucially, it holds effectivity-related columns such as EFFECTIVITY_DATE and DISABLE_DATE, which define the active period for the process linkage, and an EFFECTIVITY_TYPE to denote whether the effectivity is date-based or unit-based (serial number).

Common Use Cases and Queries

The primary use case is supporting queries within the planning engine that need to resolve the correct BOM and routing combination for an item at a given point in time during plan generation. For reporting and diagnostic purposes, a planner or analyst might query this table to verify that process linkages have been correctly snapped for a plan. A common SQL pattern involves joining to MSC_SYSTEM_ITEMS, MSC_BOMS, and MSC_ROUTINGS to get descriptive information.

• Sample Query: To list all effective process links for a specific item in a plan:
  SELECT pe.*, msif.item_name, msif.description
FROM msc_process_effectivity pe,
msc_system_items msif
WHERE pe.plan_id = :p_plan_id
AND pe.item_id = :p_item_id
AND SYSDATE BETWEEN pe.effectivity_date AND NVL(pe.disable_date, SYSDATE+1)
AND pe.item_id = msif.item_id
AND pe.plan_id = msif.plan_id
AND pe.sr_instance_id = msif.sr_instance_id;

Related Objects

MSC_PROCESS_EFFECTIVITY is intrinsically linked to the core manufacturing definition tables within the planning schema, as evidenced by its documented foreign key relationships. These relationships are fundamental to maintaining referential integrity for the snapped planning data.

• MSC_BOMS: Linked via SR_INSTANCE_ID, BILL_SEQUENCE_ID, and PLAN_ID. This defines the bill of material used in the process.
• MSC_ROUTINGS: Linked via PLAN_ID, ROUTING_SEQUENCE_ID, and SR_INSTANCE_ID. This defines the routing (sequence of operations) for the process.
• MSC_SYSTEM_ITEMS: Linked via ITEM_ID, PLAN_ID, SR_INSTANCE_ID, and ORGANIZATION_ID. This provides the item master details for the manufactured assembly.

These relationships collectively ensure that every process effectivity record correctly references a valid planning item, its BOM structure, and its production routing within a specific plan instance.