Results for “fm_rout_mtl”

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

Overview

FM_ROUT_MTL is a table in the GMD schema (Process Manufacturing Product Development) within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the association between routing steps and formula lines, enabling Oracle Process Manufacturing (OPM) to link the sequence of manufacturing operations defined on a routing to the specific material and ingredient lines defined on a formula. This association is central to production scheduling, cost rollup, and shop-floor execution, because it determines which formula components are consumed or produced at each routing step.

From a dimensional modeling perspective, the supplied metadata classifies this object heuristically as a link table. This is a modeling suggestion rather than a physical designation: FM_ROUT_MTL resolves a many-to-many relationship between routings and formula lines, and the FK-derived heuristic (FORMULALINE_ID referencing FM_MATL_DTL, TEXT_CODE referencing FM_TEXT_HDR) supports treating it as an intersection/link entity rather than a hub or satellite.

Key Information Stored

The table contains 10 documented columns. The composite primary key FM_ROUT_MTL_PK is defined on three columns: FORMULA_ID, ROUTING_ID, and FORMULALINE_ID. These three columns are therefore the surrogate/primary key set and simultaneously the strongest business-key candidates, since they uniquely identify one association between a formula line and a routing step.

• FORMULA_ID — Identifies the formula (recipe) to which the routing association belongs; part of the primary key and the join to formula headers.
• ROUTING_ID — Identifies the routing (set of manufacturing steps/operations) associated with the formula; part of the primary key.
• FORMULALINE_ID — Identifies the specific formula material/detail line; part of the primary key and a foreign key to FM_MATL_DTL.
• ROUTINGSTEP_NO — The routing step number (operation sequence) at which the associated formula line is consumed or produced.
• TEXT_CODE — Foreign key to FM_TEXT_HDR, linking the association to a text/notes definition.
• Audit columns — LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_DATE, CREATION_DATE, and LAST_UPDATE_LOGIN capture standard EBS WHO-column auditability.

Common Use Cases and Queries

Typical reporting needs include determining which formula lines are tied to each routing step, validating that every formula line is assigned to a step, and driving cost or yield analysis per operation. A representative query joining to the formula detail table is:

SELECT m.FORMULA_ID, m.ROUTING_ID, m.ROUTINGSTEP_NO, m.FORMULALINE_ID, d.ITEM_ID, d.QUANTITY
FROM GMD.FM_ROUT_MTL m, GMD.FM_MATL_DTL d
WHERE m.FORMULALINE_ID = d.FORMULALINE_ID
AND m.FORMULA_ID = :p_formula_id
ORDER BY m.ROUTINGSTEP_NO;

To retrieve associated notes, join TEXT_CODE to FM_TEXT_HDR. Auditing changes over time can be performed by filtering on LAST_UPDATE_DATE and LAST_UPDATED_BY.

Related Objects

• FM_MATL_DTL — referenced via FM_ROUT_MTL.FORMULALINE_ID; holds the formula material lines.
• FM_TEXT_HDR — referenced via FM_ROUT_MTL.TEXT_CODE; holds text/note headers.
• FM_FORMULA / formula headers — joined through FORMULA_ID.
• FM_ROUT_HDR / routing headers — joined through ROUTING_ID.
• FM_ROUT_DTL — provides the routing step detail corresponding to ROUTINGSTEP_NO.
• OPM production and costing modules — consume this association during batch execution and cost rollup.