Search Results mtl_effectivity_control

Overview

APPS.MRP_SYSTEM_ITEMS_SC_V is a reporting and integration view in Oracle E-Business Suite that exposes the set of items that participate in Master Scheduling/MRP planning. Its documented description defines it as the "MRP or MPS planned items view," indicating that it presents planned-item attributes at the organization level together with associated planning and compilation context. The view joins the core planning item table (MRP_SYSTEM_ITEMS) to descriptive flexfield data, plan definitions, plan organizations, and lookup meanings, producing a single denormalized result set suitable for reporting, custom concurrent programs, and downstream integrations.

The suffix _SC_V and the underlying join to MTL_ITEM_FLEXFIELDS indicate the view is designed to surface the segments of the item key flexfield. Because the view resolves lookup codes into meanings through multiple MFG_LOOKUPS aliases (planning code, BOM item type, ATO forecast control, WIP supply type, and effectivity control, among others), it is commonly used as a read-only convenience layer rather than a base table for transactional logic. It is owned by APPS and shipped with a VALID status in EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The documented metadata lists the following referenced objects:

• MRP_SYSTEM_ITEMS (synonym) — the driving table, holding the planning attributes of each item in the plan organization.
• MRP_PLANS (synonym) — supplies plan-level context such as organization, compile designator, plan type, current schedule type, and planner settings.
• MRP_PLAN_ORGANIZATIONS (synonym) — provides the planned organization identifier used in the NVL expression for the effective organization.
• MTL_ITEM_FLEXFIELDS (view) — supplies the concatenated ITEM_NUMBER key flexfield value.
• MTL_ABC_CLASSES (synonym) — provides the ABC_CLASS_NAME for ABC class assignments.
• MFG_LOOKUPS (view) — referenced six times under aliases FC, BIT, MPC, MBC, EUN, and WST to resolve lookup codes to their meanings.

The joins are predominantly outer joins ((+)) for the lookup tables and ABC classes, ensuring items without an ABC class or optional lookup values are still returned, while inner joins to the flexfield and plan tables constrain the result to planned items with valid key flexfield data.

Key Columns

• INVENTORY_ITEM_ID, ORGANIZATION_ID — primary item and organization identifiers.
• ITEM_NUMBER — concatenated item key flexfield value.
• ORGANIZATION_CODE — inventory organization code.
• COMPILE_DESIGNATOR, PLANNED_ORGANIZATION — plan and effective planning organization.
• FULL_LEAD_TIME, FIXED_LEAD_TIME, VARIABLE_LEAD_TIME, PREPROCESSING_LEAD_TIME, POSTPROCESSING_LEAD_TIME — lead-time attributes.
• REPETITIVE_TYPE, ROUNDING_CONTROL_TYPE — planning behavior flags.
• STANDARD_COST, NETTABLE_INVENTORY_QUANTITY, NONNETTABLE_INVENTORY_QUANTITY — costing and quantity drivers.
• PLANNER_CODE, BUYER_NAME, ABC_CLASS_NAME — responsibility and classification attributes.
• FIXED_DAYS_SUPPLY, FIXED_ORDER_QUANTITY, FIXED_LOT_MULTIPLIER, MINIMUM_ORDER_QUANTITY, MAXIMUM_ORDER_QUANTITY, SHRINKAGE_RATE — lot-sizing parameters.
• MRP_PLANNING_CODE and other lookup meanings (MBC.MEANING, WST.MEANING, BIT.MEANING, FC.MEANING, EUN.MEANING) — decoded planning code, effectivity control, supply type, BOM item type, and ATO forecast control.
• PLANNING_TIME_FENCE_DATE, UOM_CODE, PLANNING_EXCEPTION_SET, PRIMARY_VENDOR_ID — additional planning attributes.

Common Use Cases and Queries

Typical uses include extracting planned items for a plan/organization, validating planner assignments, and reviewing lot-sizing or lead-time setup across organizations.

SELECT inventory_item_id,
       organization_id,
       item_number,
       organization_code,
       planner_code,
       abc_class_name,
       mrp_planning_code,
       full_lead_time,
       fixed_order_quantity
FROM   apps.mrp_system_items_sc_v
WHERE  compile_designator = :p_plan
AND    organization_id    = :p_org;

Because the view resolves lookup meanings and the flexfield item number, it is frequently used in BI Publisher reports and custom concurrent programs where a single row per planned item is preferred over repeated joins to lookup and flexfield views. Note that the view is read-only and reflects the planning_item master for the plan organization; it should not be used to update setup data directly.