Search Results sub_original_qty




Overview
Explore in AI Chat

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

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.