Search Results gmd_formula_substitution




Overview
Explore in AI Chat

Overview

GMD_FORMULA_SUBSTITUTION is a table in the GMD schema belonging to the Oracle EBS Process Manufacturing Product Development module (also known as Oracle Process Manufacturing, or OPM). It records the set of formulas that are considered valid in the context of a specific item substitution definition. In other words, it acts as the association between substitution rules and the formulas that may legitimately be used when a substitution is applied during product development, costing, or manufacturing execution.

The table is physically stored as GMD.GMD_FORMULA_SUBSTITUTION and is documented as VALID in ETRM 12.2.2 with nine columns. Oracle proprietary and confidential notices apply. Heuristic Data Vault classification mined from the foreign-key structure indicates this is a standalone table with no outgoing relationships to other Data Vault hubs, links, or satellites; that classification should be treated as a modeling suggestion rather than a definitive architectural statement.

Key Information Stored

The table stores nine columns. The most significant are the identifier, the substitution reference, the formula reference, and an association flag, alongside standard audit columns.

  • FORMULA_SUBSTITUTION_ID — the surrogate primary key. It is the single-column business-key candidate defined by unique index GMD_FORMULA_SUBSTITUTION_U1, guaranteeing uniqueness of every substitution-to-formula assignment row.
  • SUBSTITUTION_ID — the foreign key to GMD_ITEM_SUBSTITUTION_HDR_B, identifying the parent item substitution header this row qualifies. This column also participates in the composite unique index GMD_FORMULA_SUBSTITUTION_U2.
  • FORMULA_ID — identifies the specific formula that is valid for the referenced substitution. Together with SUBSTITUTION_ID, it forms the composite business key enforced by GMD_FORMULA_SUBSTITUTION_U2, preventing duplicate substitution/formula pairings.
  • ASSOCIATED_FLAG — indicates the association status of the formula relative to the substitution, typically distinguishing active or associated rows from informational ones.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — the standard Oracle EBS audit columns capturing row creation and modification history for accountability and change tracking.

Common Use Cases and Queries

The primary use case is validating whether a given formula is permitted under a defined item substitution before it is applied in formulation or costing logic. Reporting queries commonly resolve the substitution header and formula descriptions for a specific item or formula.

  • Listing all valid formulas for a substitution: 
    SELECT fs.FORMULA_SUBSTITUTION_ID, fs.SUBSTITUTION_ID, fs.FORMULA_ID, fs.ASSOCIATED_FLAG
FROM   GMD_FORMULA_SUBSTITUTION fs
WHERE  fs.SUBSTITUTION_ID = :p_substitution_id;
  • Joining to the substitution header to enrich results with item context: 
    SELECT h.SUBSTITUTION_ID, h.ITEM_ID, fs.FORMULA_ID
FROM   GMD_ITEM_SUBSTITUTION_HDR_B h, GMD_FORMULA_SUBSTITUTION fs
WHERE  h.SUBSTITUTION_ID = fs.SUBSTITUTION_ID
AND    fs.ASSOCIATED_FLAG = 'Y';
  • Detecting formulas associated with multiple substitutions for impact analysis before a substitution is retired or changed.

Related Objects

The documented FK relationship defines the following dependencies and related reference points.

  • GMD_ITEM_SUBSTITUTION_HDR_B — joined via SUBSTITUTION_ID; the parent substitution header table that this table qualifies.
  • GMD_ITEM_SUBSTITUTION_HDR_TL — the translated header view for descriptions, joined through the header.
  • GMD_FORMULA_HDR_B — logically referenced by FORMULA_ID to resolve formula header details.
  • GMD_FORMULA_HDR_TL — translation layer supplying formula descriptions for reporting.
  • OPM Substitution and Formula APIs — the public process manufacturing interfaces that validate submissions against this table before persisting additional rows.