Search Results line_item_uom




Overview
Explore in AI Chat

Overview

APPS.GMD_MATERIAL_EFFECTIVITIES_VW is a reporting view in the Oracle E-Business Suite Process Manufacturing (OPM) module, specifically within the ETRM (Enterprise Territory and Resource Management) formula and material management area. The view presents the effective material lines for a formula, combining the base ingredient definitions held in FM_MATL_DTL with any approved item substitutions captured in the GMD item substitution tables. It serves as a normalized feed for reports, integrations, and downstream processes that must resolve the material actually consumed on a formula, including substitution-driven overrides.

The view is exposed with a UNION ALL structure that distinguishes original formula items from substituted items via the Original_Item_flag column. Rows with a flag of 1 represent the standard formula material detail; rows with a flag of 0 represent an active substitution applied to a formula line, carrying substitution preference, quantity, and unit-of-measure data drawn from the substitution header and detail records. This makes the view particularly relevant to the search term "line_item_uom," which is one of the columns the view surfaces for substitution rows.

Underlying Base Objects

The view is defined over four documented base objects, all accessed through APPS synonyms:

  • FM_MATL_DTL — the core formula material detail table, supplying formulaline_id, formula_id, line_type, line_no, inventory_item_id, qty, detail_uom, scrap_factor, scale attributes, by_product_type, and organization_id.
  • GMD_FORMULA_SUBSTITUTION — links a formula to an associated substitution definition; the view requires associated_flag = 'Y'.
  • GMD_ITEM_SUBSTITUTION_HDR_B — the substitution header, providing start/end dates, preference, original quantity and UOM, and the original inventory item identifier.
  • GMD_ITEM_SUBSTITUTION_DTL — the substitution detail line, supplying the substitute inventory_item_id, unit_qty, and the substitute detail UOM.

Joins are made on formula_id, substitution_id, and inventory_item_id, and substitutions are restricted to those with an active status range (substitution_status between 700 and 799).

Key Columns

  • formulaline_id / formula_id / line_no / line_type — identify the formula and the specific material line.
  • inventory_item_id — the item consumed; for substitution rows this is the substitute item.
  • qty / detail_uom — quantity and its unit of measure for the original formula line.
  • Original_Item_flag — 1 for the base formula material row, 0 for a substitution row.
  • line_item_uom — the unit of measure of the original formula line, carried onto substitution rows via d.detail_uom, aligning the substitution back to its source line UOM.
  • line_item_id / line_item_primary_uom / line_item_qty — the original item identifier, its primary UOM, and formula quantity, used to reconcile substitution against the base line.
  • substitution_item_uom / sub_replace_qty / sub_original_qty — the substitute item's UOM and the replacement/original quantities.
  • replacement_uom — derived via DECODE on replacement_uom_type, resolving to either the formula detail UOM or the substitute detail UOM.
  • start_date / end_date — effective dating for substitution rows (NULL for original rows).
  • preference — substitution preference ranking.

Common Use Cases and Queries

Typical uses include formula explosion reports, cost rollups, and integration extracts that must reflect active substitutions. A common query filters on the original/substitute flag and unit of measure:

  • List effective materials for a formula: SELECT formulaline_id, inventory_item_id, qty, detail_uom, line_item_uom, Original_Item_flag FROM APPS.GMD_MATERIAL_EFFECTIVITIES_VW WHERE formula_id = :p_formula_id;
  • Isolate substitution rows and their UOMs: SELECT line_item_id, line_item_uom, substitution_item_uom, sub_replace_qty, preference FROM APPS.GMD_MATERIAL_EFFECTIVITIES_VW WHERE Original_Item_flag = 0 AND organization_id = :p_org;
  • Filter by line unit of measure: SELECT * FROM APPS.GMD_MATERIAL_EFFECTIVITIES_VW WHERE line_item_uom = :p_uom;

Because the view coalesces substitution and base data, callers should always disambiguate using Original_Item_flag and the effective date columns before aggregating quantities.