Search Results gmd_formula_substitution_u2

Overview

GMD.GMD_FORMULA_SUBSTITUTION is a seed-data table within the Oracle E-Business Suite Process Manufacturing (OPM) schema GMD. It records the association between substitution definitions and the formulas that are valid for a given context item substitution. In practical terms, each row asserts that a specific formula may participate in a particular item substitution rule, and the ASSOCIATED_FLAG column records whether that association is currently active. The table is deployed in the APPS_TS_SEED tablespace, which is consistent with its role as a relatively static configuration table populated during implementation or by the substitution maintenance programs rather than by high-volume transactional activity.

Under the heuristic Data Vault classification derived from the documented foreign key structure, this object is a standalone table. Its single documented foreign key, SUBSTITUTION_ID referencing GMD_ITEM_SUBSTITUTION_HDR_B, would in a Data Vault model be described as a link-like association between a substitution header and a formula, with the surrounding columns behaving as satellite attributes. This is offered only as a modeling suggestion; the physical implementation in EBS remains a conventional normalized table with unique indexes.

Key Information Stored

The table contains nine documented columns. The surrogate primary key is FORMULA_SUBSTITUTION_ID, a NUMBER(15) column that is also the single column of the unique index GMD_FORMULA_SUBSTITUTION_U1. Business-key candidates are documented through the unique index GMD_FORMULA_SUBSTITUTION_U2, which spans SUBSTITUTION_ID and FORMULA_ID; this composite uniqueness guarantees that a given formula cannot be associated more than once with the same substitution, which is the central integrity rule of the table.

• FORMULA_SUBSTITUTION_ID – Surrogate primary key and the column of unique index U1.
• SUBSTITUTION_ID – Foreign key to GMD_ITEM_SUBSTITUTION_HDR_B, identifying the substitution definition to which the formula is attached; first column of unique index U2.
• FORMULA_ID – Identifier of the formula that is valid for the substitution context; second column of unique index U2.
• ASSOCIATED_FLAG – Flag indicating whether the formula-to-substitution association is active.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE – Standard EBS Who columns providing audit lineage and concurrency information.

No mandatory flags are documented for the attribute columns, so nullability should be confirmed against the live dictionary before relying on it in validation logic.

Common Use Cases and Queries

The principal use case is resolving which formulas are eligible for a substitution when a production or planning process evaluates an item substitution. A typical query joins the substitution header to this table and then to the formula master:

• List formulas for a substitution: SELECT fs.FORMULA_ID, fs.ASSOCIATED_FLAG FROM GMD.GMD_FORMULA_SUBSTITUTION fs WHERE fs.SUBSTITUTION_ID = :p_substitution_id.
• Active associations only: add AND fs.ASSOCIATED_FLAG = 'Y' to filter inactive links.
• Reverse lookup — substitutions available for a formula: SELECT fs.SUBSTITUTION_ID FROM GMD.GMD_FORMULA_SUBSTITUTION fs WHERE fs.FORMULA_ID = :p_formula_id.
• Integrity audit: group by SUBSTITUTION_ID, FORMULA_ID and count(*) to detect duplicate pairs; U2 should prevent them, so any result greater than one indicates an index anomaly.
• Orphan check: outer-join SUBSTITUTION_ID to GMD_ITEM_SUBSTITUTION_HDR_B and report rows where the header key is null.

Reporting extracts commonly feed substitution-impact analyses, formula qualification matrices, and implementation migration scripts that stage substitution-to-formula relationships.

Related Objects

• GMD.GMD_ITEM_SUBSTITUTION_HDR_B – Referenced by the SUBSTITUTION_ID foreign key; the parent substitution header.
• GMD.GMD_FORMULA_SUBSTITUTION# – The documented dependent object, typically the runtime or materialized variant of this table.
• GMD.GMD_FORMULA_SUBSTITUTION_U1 / _U2 – Unique indexes enforcing the surrogate key and the substitution/formula business key.
• GMD.GMD_FORMULAS_B – Formula master generally joined via FORMULA_ID to resolve formula names and versions.
• GMD.GMD_ITEM_SUBSTITUTION_DTL – Substitution detail lines that provide the item-level context for each substitution header.

Developers should treat GMD_FORMULA_SUBSTITUTION as a controlled configuration table and, where possible, maintain it through the supported OPM substitution setup interfaces rather than through direct DML.