Search Results replacement_uom

Overview

GMD_MATERIAL_EFFECTIVITIES_VW is a reporting view owned by the APPS schema within the Oracle E-Business Suite Process Manufacturing (GMD) module, specifically the Product Development product. The view presents material effectiveness data for formula and recipe lines, exposing the ingredient-level detail that drives yield, step quantity, and scaling calculations in process manufacturing. In the ETRM 12.2.2 documentation the object carries a status of VALID and is catalogued under the GMD - Process Manufacturing Product Development product.

The view is designed primarily for reporting and integration purposes. Rather than requiring callers to reconcile the formula material detail table with the substitution header and detail tables separately, GMD_MATERIAL_EFFECTIVITIES_VW produces a consolidated, unioned result set that exposes both original formula lines and their associated substitution alternatives through a uniform column interface. This makes it a convenient source for custom reports, Oracle Discoverer worksheets, and interface programs that must read effective material composition without embedding the substitution join logic themselves.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through APPS synonyms: FM_MATL_DTL, GMD_FORMULA_SUBSTITUTION, GMD_ITEM_SUBSTITUTION_DTL, and GMD_ITEM_SUBSTITUTION_HDR_B. The primary driver is FM_MATL_DTL, which holds the formula material detail lines (formula lines, quantities, units of measure, and effectiveness indicators).

The view text is a UNION ALL of two branches. The first branch selects directly from FM_MATL_DTL with a trivial WHERE 1 = 1 predicate, returning original formula lines with ORIGINAL_ITEM_FLAG set to 1 and a number of substitution-related columns returned as NULL. The second branch joins FM_MATL_DTL (aliased D) to GMD_FORMULA_SUBSTITUTION (F), GMD_ITEM_SUBSTITUTION_HDR_B (H), and GMD_ITEM_SUBSTITUTION_DTL (S). This branch restricts to formula lines of LINE_TYPE = -1 whose INGREDIENT_END_DATE is not null, requires F.ASSOCIATED_FLAG = 'Y', and filters substitution header rows to a substitution status between 700 and 799. It returns ORIGINAL_ITEM_FLAG as 0 and populates the substitution-specific columns.

Key Columns

• FORMULALINE_ID, FORMULA_ID — Identifiers linking the row to its parent formula and formula line.
• LINE_TYPE, LINE_NO, INVENTORY_ITEM_ID — Line classification and the ingredient item. Note the second branch aliases S.INVENTORY_ITEM_ID to ITEM_ID in the inner select and maps it to INVENTORY_ITEM_ID in the outer projection.
• QTY, DETAIL_UOM — Line quantity and its unit of measure; QTY is NULL on the substitution branch.
• CONTRIBUTE_STEP_QTY_IND, CONTRIBUTE_YIELD_IND — The effectiveness indicators that govern whether the line contributes to step quantity and to yield respectively. These flags are central to material effectiveness reporting and are carried through both branches.
• SCRAP_FACTOR, SCALE_TYPE, SCALE_MULTIPLE, SCALE_ROUNDING_VARIANCE, ROUNDING_DIRECTION — Scaling and rounding attributes applied during formula scaling.
• COST_ALLOC, PHANTOM_TYPE, REWORK_TYPE, BY_PRODUCT_TYPE, BUFFER_IND — Costing and line-behavior attributes.
• ORIGINAL_ITEM_FLAG — Distinguishes original formula lines (1) from substitution rows (0).
• START_DATE, END_DATE — On the original branch these are NULL/INGREDIENT_END_DATE; on the substitution branch they come from the substitution header.
• PREFERENCE, SUB_ORIGINAL_QTY, SUB_REPLACE_QTY, REPLACEMENT_UOM, LINE_ITEM_ID, LINE_ITEM_UOM, LINE_ITEM_PRIMARY_UOM, LINE_ITEM_QTY, SUBSTITUTION_ITEM_UOM — Substitution context columns, NULL on original rows.
• ORGANIZATION_ID — The owning inventory organization.

Common Use Cases and Queries

Typical usage includes identifying which formula ingredients contribute to yield, auditing substitution coverage, and feeding custom yield or cost reports. A representative query is:

SELECT formula_id, formulaline_id, inventory_item_id, contribute_yield_ind, contribute_step_qty_ind, original_item_flag FROM apps.gmd_material_effectivities_vw WHERE formula_id = :p_formula_id ORDER BY line_no;

To isolate substitution rows only, filter on ORIGINAL_ITEM_FLAG = 0; to retrieve pure original formula composition, filter on ORIGINAL_ITEM_FLAG = 1 and a non-null QTY. Because the substitution branch already enforces the status range of 700–799 and ASSOCIATED_FLAG = 'Y', consumers need not repeat those predicates.