Search Results gmd_recipe_step_materials_pk

Overview

GMD_RECIPE_STEP_MATERIALS is a table within the GMD schema, belonging to the Process Manufacturing Product Development module of Oracle E-Business Suite (documented against 12.1.1 and 12.2.2). It stores step material information at the recipe level, associating materials and formula line references with the routing steps that define how a recipe is executed. In the process manufacturing model, a recipe binds a formula (the bill of materials equivalent) to a routing (the sequence of operations), and this table supplies the material-level detail that is specific to each routing step, including quantity and delay characteristics.

The documented primary key is GMD_RECIPE_STEP_MATERIALS_PK, composed of RECIPE_ID and FORMULALINE_ID. A foreign key relationship is documented from ROUTINGSTEP_ID to FM_ROUT_DTL, linking each step material row to its parent routing step. The table is classified heuristically as standalone under a Data Vault modeling suggestion; because the documented structure resolves to a composite business key without additional parent/child hubs or links identified in the metadata, it is best treated as an independent reference that carries its own identifier rather than as a true hub, link, or satellite.

Key Information Stored

The table contains 43 documented columns. The most significant are:

• RECIPE_ID — identifies the recipe to which the step material belongs; part of the composite primary key.
• FORMULALINE_ID — identifies the specific formula line (material) being placed at the step; the second component of the primary key.
• ROUTINGSTEP_ID — the routing step from FM_ROUT_DTL at which the material is consumed or produced.
• TEXT_CODE — a coded text descriptor associated with the step material record.
• MINIMUM_TRANSFER_QTY — the minimum quantity that must be transferred for the material at the step.
• MINIMUM_DELAY and MAXIMUM_DELAY — the delay windows permitted around the step material activity.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created and last modified the row and when.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30 — the descriptive flexfield (DFF) columns, providing thirty free-format segments plus a context category for customer-specific extensions.

The surrogate-style composite primary key (RECIPE_ID, FORMULALINE_ID) is also the sole documented unique index, making it the business-key candidate. ROUTINGSTEP_ID is the foreign key that ties the row into the routing structure; it is not part of the primary key but is essential for meaningful joins.

Common Use Cases and Queries

Typical uses include retrieving all materials assigned to a given recipe, listing materials for a specific routing step, and reporting on transfer quantities and delay windows for shop-floor scheduling. A representative query joining the table to its routing detail parent is:

• SELECT m.RECIPE_ID, m.FORMULALINE_ID, m.ROUTINGSTEP_ID, m.MINIMUM_TRANSFER_QTY FROM GMD_RECIPE_STEP_MATERIALS m WHERE m.RECIPE_ID = :p_recipe_id;
• SELECT m.RECIPE_ID, m.FORMULALINE_ID, r.* FROM GMD_RECIPE_STEP_MATERIALS m, FM_ROUT_DTL r WHERE m.ROUTINGSTEP_ID = r.ROUTINGSTEP_ID AND m.RECIPE_ID = :p_recipe_id;
• SELECT m.RECIPE_ID, m.FORMULALINE_ID, m.MINIMUM_DELAY, m.MAXIMUM_DELAY FROM GMD_RECIPE_STEP_MATERIALS m WHERE m.ROUTINGSTEP_ID = :p_step_id;

Reporting scenarios include recipe explosion for costing and material availability, verification of step-level material assignments before releasing a recipe, and audit extraction of DFF attribute values for compliance.

Related Objects

The following objects are most significant in relation to GMD_RECIPE_STEP_MATERIALS:

• FM_ROUT_DTL — referenced by ROUTINGSTEP_ID; the routing step master that defines where each material is consumed.
• GMD_RECIPES — the recipe header referenced by RECIPE_ID.
• GMD_FORMULA_LINES (or the corresponding formula line entity) — referenced by FORMULALINE_ID, defining the material identity and quantity.
• FM_ROUT_HEADERS — the routing header to which FM_ROUT_DTL rows belong, useful for context when tracing step materials to a routing.
• GMD_FORMULA_HEADERS — the formula header providing the overall material context for the recipe.

Together these objects allow the step material detail to be fully resolved from recipe header through formula, routing, and individual routing step.