Search Results msd_bom_components_v




Overview
Explore in AI Chat

Overview

MSD_BOM_COMPONENTS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the MSD product, Demand Planning, and exposes Planning Bills of Material together with their structural attributes for consumption by the demand planning engine and by downstream reporting. The view consolidates bill-of-material component rows with the level hierarchy used by the planning data model, allowing planning applications to resolve each parent and component item into the correct planning level and hierarchy key.

Because the view is a query abstraction rather than a stored table, it does not hold data of its own. It joins the planning bill structures stored in the MSC collection tables to MSD_LEVEL_VALUES, which carries the level identifiers that demand planning uses to organize the multidimensional planning cube. The view is documented as VALID in the ETRM metadata, confirming that its definition compiles against the base objects listed for 12.2.2.

Underlying Base Objects

The ETRM metadata records the following referenced base objects: FND_PROFILE (PACKAGE), MSC_APPS_INSTANCES (SYNONYM), MSC_BOMS (SYNONYM), MSC_BOM_COMPONENTS (SYNONYM), MSC_SYSTEM_ITEMS (SYNONYM), MSD_BOM_COMPONENTS (SYNONYM), MSD_COMMON_UTILITIES (PACKAGE), MSD_LEVEL_VALUES (SYNONYM), and DUAL (SYNONYM).

The principal fact source is MSC_BOM_COMPONENTS, the bill component collection populated during a planning data collection run. MSC_BOMS supplies the bill header, including assembly item, bill sequence, alternate designator, and the plan, organization, and source instance keys that uniquely identify a collected bill. MSC_SYSTEM_ITEMS is referenced twice, once for the using (parent) assembly and once for the component item, so that attributes such as MRP planning code, ATO forecast control, and BOM item type can be applied as filters on both sides of the relationship.

MSD_LEVEL_VALUES appears three times in the view text, aliased MV1, MV2, and MV3. These aliases map the component level (LEVEL_ID = 1), the parent assembly level (LEVEL_ID = 1), and the organization level (LEVEL_ID = 7) to the planning hierarchy. FND_PROFILE supplies the MSD_PLANNING_PERCENTAGE profile option, which gates the rows returned by the view. MSD_COMMON_UTILITIES and MSC_APPS_INSTANCES support the second branch of the UNION ALL in the definition.

Key Columns

The view projects three pairs of level columns corresponding to the three hierarchy dimensions resolved through MSD_LEVEL_VALUES:

  • LEVEL_PK / LEVEL_VALUE (MV1) — the planning level key and value for the component item.
  • LEVEL_PK / LEVEL_VALUE (MV2) — the planning level key and value for the using assembly.
  • LEVEL_PK / LEVEL_VALUE (MV3) — the planning level key and value for the organization.
  • PLANNING_FACTOR — the planning percentage applied to the component within the planning bill.
  • USAGE_QUANTITY — the component quantity required per unit of the parent assembly.
  • EFFECTIVITY_DATE — the date from which the component relationship is effective.
  • DISABLE_DATE — the date on which the component relationship is disabled.

Two additional columns are projected as NULL literals in the first branch of the UNION ALL, serving as placeholders that preserve column alignment across the combined result set.

Common Use Cases and Queries

Typical usage is to retrieve the flattened planning bill structure for a plan and organization so that forecast explosion can be performed. The profile-driven gate on MSD_PLANNING_PERCENTAGE means the view returns no rows when the profile is set to 3, so reports should confirm this setting before diagnosing empty results.

A representative query follows:

  • SELECT level_pk, level_value, planning_factor, usage_quantity, effectivity_date, disable_date FROM msd_bom_components_v WHERE usage_quantity > 0;
  • SELECT planning_factor, SUM(usage_quantity) FROM msd_bom_components_v GROUP BY planning_factor ORDER BY planning_factor;

Because the definition enforces filters on MRP_PLANNING_CODE, ATO_FORECAST_CONTROL, and BOM_ITEM_TYPE, the view already excludes non-planning items, ATO forecast-disabled items, and certain bill item types. Report authors should therefore avoid re-applying those predicates and instead focus on plan, organization, and effectivity criteria when narrowing results.