Search Results fm_matl_dtl

Overview

The FM_MATL_DTL table is a core data object within the Process Manufacturing Product Development (GMD) module of Oracle E-Business Suite (EBS) releases 12.1.1 and 12.2.2. It serves as the detailed repository for all material components defined within a formula. This table is fundamental to the formula management process, storing the comprehensive list of ingredients, products, and by-products that constitute a manufacturing recipe. Its role is to define the material requirements, quantities, and characteristics for each line item, enabling precise production planning, costing, and execution within the Process Manufacturing suite.

Key Information Stored

The table's primary purpose is to detail each material line associated with a formula. Key columns include FORMULA_ID, which links the detail line to its parent formula in the FM_FORM_MST table. The LINE_TYPE and LINE_NO columns form a unique key with FORMULA_ID, identifying the type of material (e.g., ingredient, product) and its sequence. The FORMULALINE_ID is the primary unique identifier for each row. Critical material-specific columns are ITEM_ID, linking to the inventory item master (IC_ITEM_MST_B), and ITEM_UM, which stores the unit of measure for the item, referencing SY_UOMS_MST. The TEXT_CODE column provides a link to descriptive text blocks stored in FM_TEXT_HDR.

Common Use Cases and Queries

This table is central to formula inquiry, reporting, and integration. Common use cases include generating a Bill of Materials for a formula, calculating total ingredient costs, and validating formula completeness. A typical query retrieves all material details for a specific formula, joining to item master and unit of measure tables for descriptive information. For example:

• SELECT fmd.LINE_NO, fmd.LINE_TYPE, iim.ITEM_NO, iim.DESCRIPTION, fmd.QTY, fmd.ITEM_UM FROM GMD.FM_MATL_DTL fmd, INV.IC_ITEM_MST_B iim WHERE fmd.FORMULA_ID = :formula_id AND fmd.ITEM_ID = iim.ITEM_ID ORDER BY fmd.LINE_NO;

This data is also essential for downstream processes, as the FORMULALINE_ID is referenced by routing material requirements (FM_ROUT_MTL) and quality management objects (QC_SPEC_MST, QC_SMPL_MST, QC_RSLT_MST) to associate specifications, samples, and results with specific formula lines.

Related Objects

FM_MATL_DTL has extensive relationships within the EBS schema. Its primary parent is FM_FORM_MST (formula master). It references several foundational tables via foreign keys: IC_ITEM_MST_B for items, SY_UOMS_MST for units of measure, and FM_TEXT_HDR for descriptive text. Crucially, the FORMULALINE_ID column is referenced as a foreign key by several dependent objects, highlighting its operational importance. These include FM_ROUT_MTL for routing material details, and the quality management tables QC_SPEC_MST (specifications), QC_SMPL_MST (samples), and QC_RSLT_MST (results), creating a critical link between formula definition and quality control execution.