OPM Formula Where-Used – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Process Manufacturing · all Process Manufacturing reports

Overview

Oracle Process Manufacturing (OPM) formula where-used: lists which formulas consume a given ingredient item, with the ingredient quantity and UOM, the using formula and its status, the formula’s primary product, and how many recipes use that formula. Supports ingredient impact analysis for substitution, supplier change and allergen / regulatory recall.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Organization Code, Ingredient Item, Formula Status

Oracle EBS Tables Used

fm_matl_dtl, mtl_system_items_vl, gmd_recipes_vl, fm_form_mst, org_organization_definitions, gmd_status_vl, org_access_view

Report Categories

Enginatics, R12 only

Related Reports

OPM Recipe Upload, CAC OPM Costed Formula, OPM Formula Details, OPM Recipe Details, OPM Formula Upload

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource	Link
Excel Example Output	OPM Formula Where-Used 05-Jun-2026 120002.xlsx
Blitz Report™ XML Import	OPM_Formula_Where_Used.xml
SQL Source	OPM Formula Where-Used.sql
Full Report on Enginatics	www.enginatics.com/reports/opm-formula-where-used/

Case Study & Technical Analysis: OPM Formula Where-Used

Executive Summary

This report answers the question “which formulas use this ingredient?” in Oracle Process Manufacturing. For a given ingredient item it lists every formula that consumes it, with the quantity and unit of measure, the formula’s version and status, its primary product, and the number of recipes built from that formula. Process manufacturing quality, regulatory and supply chain teams use it for impact analysis before a substitution, a supplier change or an allergen or regulatory recall. The report runs in Excel through Blitz Report.

Business Challenge

• Substitution impact: Replacing an ingredient needs every formula that consumes it to be identified before the change is released.
• Supplier change control: A new supplier of a raw material must be checked against all the formulas it touches.
• Recall scope: An allergen or regulatory recall must reach every formula and recipe that contains the affected ingredient.
• Formula status: Obsolete or on-hold formulas still consume ingredients in the data, and their status must be visible.

The Solution

• Ingredient consumption: The report selects each formula line where the item is an ingredient, identified by a negative line type.
• Quantity and unit: The ingredient quantity, its unit of measure and its scrap factor are shown on each row.
• Formula identity: The formula number, version and status name are returned so each usage can be judged.
• Primary product: The item of the formula’s product line is shown, so the consumer of the ingredient is identified.
• Recipe count: The number of distinct recipes built from the formula shows how far the change would spread.

Technical Architecture (High Level)

Primary Tables

• FM_MATL_DTL: the formula material lines, with line type, quantity, unit of measure and scrap factor. Ingredient lines have a negative line type and product lines have a positive one.
• FM_FORM_MST: the formula master, with formula number, version and status.
• MTL_SYSTEM_ITEMS_VL: the ingredient and product item and its description.
• GMD_RECIPES_VL: the recipes that use each formula, counted for the recipe count.
• GMD_STATUS_VL: the status name of the formula.
• ORG_ORGANIZATION_DEFINITIONS: the organization code.
• ORG_ACCESS_VIEW: the organizations the responsibility can access.

Logical Relationships

1. FM_MATL_DTL joins to FM_FORM_MST on formula id, and is restricted to ingredient lines with line type -1.
2. FM_MATL_DTL joins to MTL_SYSTEM_ITEMS_VL on inventory item id and organization id, which gives the ingredient item.
3. FM_FORM_MST joins to GMD_STATUS_VL on the formula status code, as an outer join.
4. The product item is a scalar subquery on the formula’s product lines, with line type 1, and returns the first match.
5. The recipe count is a scalar subquery on GMD_RECIPES_VL counting distinct recipe ids for the formula.

Parameters & Filtering

• Organization Code: restricts the usages to one process manufacturing organization.
• Ingredient Item: restricts the report to one ingredient.
• Formula Status: restricts the formulas to one status.

Performance & Optimization

• The ingredient line filter on line type applies before the item and formula joins, so the report reads only consumption lines.
• The organization access list limits the material detail rows to the organizations the responsibility can access.
• The product item and recipe count are scalar subqueries keyed on the formula id, so they run once for each returned formula line.

FAQ

Q: How do I find every formula that uses a particular ingredient? A: Enter the item in Ingredient Item and leave the other parameters blank, or select the organization that holds the formula.

Q: What does the recipes using column count? A: It counts the distinct recipes built from the formula, so it shows how many recipes a formula change would affect.

Q: Why does a formula appear more than once? A: The report has one row per ingredient line, so a formula that uses the item in two lines appears twice.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH