Search Results create_substitution_header

Overview

GMD_SUBSTITUTION_PVT is a private PL/SQL package in the Oracle EBS Process Manufacturing (OPM/GMD) product family, owned by the APPS schema. It provides the internal implementation layer for managing item substitutions and their association with formulas. An item substitution defines an alternative item that may be used in place of a primary item within a formula or recipe context, and this package encapsulates the logic required to create, modify, copy, and delete substitution headers, substitution detail lines, and formula associations. The package is classified as a PVT (private) API, meaning it is intended for internal invocation by the public substitution API layer (GMD_SUBSTITUTION_PUB) rather than for direct external calls. Its core responsibilities include validation of substitution records, message accumulation for error reporting, and coordinated DML across the substitution header, detail, and formula association tables, including the corresponding date-tracked ( _S ) shadow tables.

Key Procedures and Functions

The documented procedures fall into three functional groups. The creation group includes CREATE_SUBSTITUTION_HEADER, which inserts a new substitution header record; CREATE_SUBSTITUTION_DETAIL, which inserts an individual substitution line associated with a given substitution identifier; and CREATE_FORMULA_ASSOCIATION, which links a substitution to one or more formula-substitution entries using a PL/SQL table structure. The update group includes UPDATE_substitution_HEADER, which modifies an existing header using a full row type, and UPDATE_SUBSTITUTION_DETAIL, the procedure the user searched for, which updates an existing substitution detail line passed as a gmd_item_substitution_dtl%ROWTYPE record. UPDATE_SUBSTITUTION_DETAIL is the internal worker routine invoked when a substitution line is changed, and it returns a message count, a message list, and a return status for caller-level error handling. The package also documents DELETE_FORMULA_ASSOCIATION and COPY_SUBSTITUTION, covering removal of formula links and duplication of an existing substitution respectively. Each procedure follows the standard ETRM message/return-status convention with x_message_count, x_message_list, and x_return_status output parameters.

Tables Accessed

The package operates against the substitution data model and its related entities. GMD_ITEM_SUBSTITUTION_HDR_B, GMD_ITEM_SUBSTITUTION_HDR_S, GMD_ITEM_SUBSTITUTION_DTL, and GMD_ITEM_SUBSTITUTION_DTL_S store and track substitution headers and detail lines. GMD_FORMULA_SUBSTITUTION and GMD_FORMULA_SUBSTITUTION_S hold the associations between substitutions and formulas. FM_MATL_DTL represents formula material detail, used to validate that substituted items are relevant to the referenced formula. MTL_SYSTEM_ITEMS_B supplies item validation from the inventory master. DUAL and PLITBLM support minor internal operations such as sequence lookups and PL/SQL table handling. The _S and _B variants indicate date-tracked and base-table structures, and writes are typically coordinated across both to preserve audit and version history.

Usage Notes

Because GMD_SUBSTITUTION_PVT is a private package, it is normally invoked indirectly through the public API package GMD_SUBSTITUTION_PUB, which is in turn called by OPM substitution forms and by concurrent programs that process substitutions. Customizations should prefer the public layer to remain upgrade-safe. The procedure is referenced by one other package in the ETRM metadata, consistent with its role as a shared internal worker. Callers must supply a fully populated gmd_item_substitution_dtl%ROWTYPE and must inspect x_return_status and x_message_list after every invocation, since the package reports errors through the message list rather than raising exceptions in all cases. As with all date-tracked OPM entities, updates performed through UPDATE_SUBSTITUTION_DETAIL affect both the operational record and its history.