Search Results get_formula_no

Overview

GMDFMVAL_PUB is the public validation package for Oracle Process Manufacturing (OPM) formula definitions in Oracle E-Business Suite 12.1.1 and 12.2.2. It centralizes the server-side validation logic that governs the creation, modification, and querying of formulas and their associated detail lines, substitutions, and recipe relationships. The package belongs to the APPS schema and is classified as a public API, meaning it is intended to be called from Oracle Forms, concurrent programs, workflows, and custom extensions rather than being treated as an internal implementation detail.

A substantial portion of its responsibilities involves the resolution of surrogate keys. The header comment for get_formula_id documents a routine that retrieves a formula's surrogate key from the supplied formula number, version, and type. The same pattern recurs across get_formulaline_id, get_item_id, and get_element, which return primary keys for lines, inventory items, and formula elements respectively. This design allows callers to work with user-visible identifiers while the package performs the underlying key lookups against the OPM base tables.

Key Procedures and Functions

The documented procedures and functions fall into several functional groups:

• Record validation: VALIDATE_INSERT_RECORD, VALIDATE_UPDATE_RECORD, DETAIL_LINE_VAL, CHECK_EXPR_ITEMS, and CHECK_REWORK_TYPE enforce business rules before a formula row is inserted or updated, verifying line details, expression items, and rework classification.
• Key and attribute retrieval: GET_FORMULA_ID, GET_FORMULALINE_ID, GET_ITEM_ID, GET_ELEMENT, GET_LINE_QTY, and DETERMINE_PRODUCT resolve surrogate keys, quantities, and product assignments from user-entered identifiers.
• Substitution handling: GET_SUBSTITUTE_ITEMS, GET_SUBSTITUTE_LINE_ITEM, and COPY_FORMULA_SUBSTITUTION_LIST manage the retrieval and duplication of formula substitution data.
• Property validation: FORMULA_CLASS_VAL, TYPE_VAL, COST_ALLOC_VAL, LOCKED_EFFECTIVITY_VAL, GMD_EFFECTIVITY_LOCKED_STATUS, and CONVERTUOM_VAL validate formula class, type, cost allocation, effectivity locking, and unit-of-measure conversion.

The documented call signature for GET_FORMULA_ID accepts a formula number, version, and type, returning the surrogate key through an OUT parameter and a status code indicating success, a "formula_id not found" condition, or an RDBMS error.

Tables Accessed

The package reads and writes through APPS synonyms across the OPM and Oracle Inventory schemas. FM_FORM_MST and FM_MATL_DTL hold the formula header and material detail records. GMD_RECIPES, GMD_RECIPES_B, and GMD_RECIPE_VALIDITY_RULES support recipe-to-formula relationships and effectivity. Substitution data resides in GMD_FORMULA_SUBSTITUTION, GMD_FORMULA_SUBSTITUTION_S, GMD_ITEM_SUBSTITUTION_DTL, and GMD_ITEM_SUBSTITUTION_HDR_B. Item attributes and revisions are sourced from MTL_SYSTEM_ITEMS, MTL_SYSTEM_ITEMS_KFV, and MTL_ITEM_REVISIONS, while FM_FORM_CLS supplies formula class values, CM_CMPT_DTL provides compatibility detail, and FND_USER is referenced for user context.

Usage Notes

In typical EBS deployments, GMDFMVAL_PUB is invoked from the OPM Formula and Recipe Forms when a user enters or modifies a formula, and from concurrent programs that validate or migrate formula data in bulk. Because it is documented as a public API referenced by eighteen other packages, it is a common integration point for custom PL/SQL that needs formula validation or surrogate key resolution rather than direct table access. Callers should check the returned status code after every invocation and treat a non-zero value as an authoritative validation failure.