Results for “gmd_mbr_util_pkg”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

GMD_MBR_MATERIAL_V1 is a read-only view owned by the APPS schema in Oracle EBS Process Manufacturing Product Development (GMD). It exposes the material lines associated with process manufacturing formulas and recipes, resolving each formula line to its currently effective material specification. The view combines recipe header data, effective-dated material lines, formula master records, inventory item attributes, and lookup descriptions into a single denormalized row per material requirement.

A central design element of the view is the ORIGINAL_ITEM_FLAG column, which the user specifically searched for. This flag distinguishes the original (primary) item on a formula line from substitute or replacement items. The view uses this flag in two important ways. First, in the effective-date filter, the predicate (SYSDATE BETWEEN NVL(START_DATE, SYSDATE) AND NVL(END_DATE, SYSDATE)) OR ORIGINAL_ITEM_FLAG = 1 ensures that the original item is always returned regardless of its effective dating. Second, the analytic RANK() OVER (PARTITION BY FORMULA_ID, LINE_TYPE, LINE_NO ORDER BY PREFERENCE, ORIGINAL_ITEM_FLAG) selects a single material line per formula, line type, and line number, preferring lower preference values and, as a tiebreaker, the original item where ORIGINAL_ITEM_FLAG = 1.

Because the view is defined in the APPS schema and references other APPS objects, it is a stable integration and reporting surface used by formula and recipe maintenance, cost rollup, and material availability inquiries.

Underlying Base Objects

The documented base objects referenced by GMD_MBR_MATERIAL_V1 are:

• GMD_RECIPES_B (synonym) — recipe header, supplying RECIPE_ID, RECIPE_NO, RECIPE_VERSION, and ROUTING_ID.
• GMD_MATERIAL_EFFECTIVITIES_VW (view) — the effective-dated material line source aliased as MATL, contributing FORMULALINE_ID, QTY, LINE_TYPE, LINE_NO, PREFERENCE, ORIGINAL_ITEM_FLAG, and related attributes.
• FM_FORM_MST (synonym) — formula master, providing FORMULA_NO and FORMULA_VERS.
• MTL_SYSTEM_ITEMS_VL (view) — inventory master, joined on ITEM_ID = INVENTORY_ITEM_ID to return CONCATENATED_SEGMENTS and DESCRIPTION.
• GEM_LOOKUPS (view) — lookup meanings for LINE_TYPE via LKUP.MEANING.
• GMDFMVAL_PUB (package) — GET_LINE_QTY resolves the quantity when ORIGINAL_ITEM_FLAG is not 1.
• GMD_MBR_UTIL_PKG (package) — GET_SAMPLING_PLAN returns SAMPLE_PLAN_ID.
• GMO_MBR_UTIL (package) — GET_DISPENSE_CONFIG returns GMO_DISP_CONFIG_ID.

Key Columns

• FORMULALINE_ID, FORMULA_ID, LINE_NO, LINE_TYPE, MEANING — line identity and its decoded type.
• QTY — decoded quantity: when ORIGINAL_ITEM_FLAG = 1 the raw MATL.QTY is returned; otherwise GMDFMVAL_PUB.GET_LINE_QTY derives the effective quantity.
• ORIGINAL_ITEM_FLAG — present in the underlying material effectivities view; identifies the primary versus substitute/replacement item.
• ITEM_ID/INVENTORY_ITEM_ID, CONCATENATED_SEGMENTS, DESCRIPTION, ORGANIZATION_ID, ITEM_UM — material item detail.
• SCRAP_FACTOR, SCALE_TYPE, SCALE_MULTIPLE, COST_ALLOC, PHANTOM_TYPE, BY_PRODUCT_TYPE, BUFFER_IND — planning and cost attributes.
• RECIPE_ID, SAMPLE_PLAN_ID, GMO_DISP_CONFIG_ID — recipe linkage and utility-derived configuration identifiers.

Common Use Cases and Queries

Typical uses include extracting formula material lines for a recipe, verifying which item is treated as original, and driving sampling or dispensing configuration lookups.

SELECT formula_id, formulaline_id, line_no, meaning,
       inventory_item_id, concatenated_segments, qty
FROM   apps.gmd_mbr_material_v1
WHERE  formula_id = :p_formula_id
ORDER  BY line_no;

Identifying original versus substitute items depends on the underlying flag, which is not projected as a standalone column; applications filter or rank using the source view behavior encoded within GMD_MBR_MATERIAL_V1.

SELECT recipe_id, recipe_no, formula_no, item_id, qty
FROM   apps.gmd_mbr_material_v1
WHERE  organization_id = :p_org_id
AND    recipe_id = :p_recipe_id;

For sampling and dispensing configuration, query SAMPLE_PLAN_ID and GMO_DISP_CONFIG_ID directly from the view, since these are computed by the packaged functions at query time.