Search Results inv_vr_exist

Overview

GMD_SPEC_VRS_GRP is a group-layer PL/SQL package in the Oracle E-Business Suite Process Manufacturing (OPM) module, owned by the APPS schema. In OPM, specifications define the quality and compliance attributes that products, ingredients, and materials must satisfy, while specification versions (VR) represent the individual, versioned instances of those specifications. The GMD_SPEC_VRS_GRP package provides the centralized validation API layer for specification version entities across multiple specification domains: monitoring, inventory, WIP, customer, and supplier. It encapsulates the business rules that govern record integrity, foreign key relationships, and guard conditions that must be satisfied before a specification version is inserted, updated, or deleted.

By centralizing these validations in a single group-layer package, OPM ensures that specification version data entered through any channel — whether a Java/Forms user interface, a public API, or a concurrent process — is subjected to a consistent set of constraints. This design reflects the standard EBS layered architecture, in which the group (GRP) layer sits above the entity/table handler layer and below the user-facing application layer.

Key Procedures and Functions

The package exposes 23 documented procedures and functions, organized by specification domain, each following a consistent validation pattern:

• Monitoring specification versions: VALIDATE_MON_VR performs the primary validation of a monitoring specification version record, returning the validated row and a return status. CHECK_FOR_NULL_AND_FKS_IN_MVR enforces mandatory-column and foreign key checks, and MON_VR_EXIST is a Boolean existence check for a monitoring version.
• Inventory specification versions: VALIDATE_INV_VR, CHECK_FOR_NULL_AND_FKS_IN_IVR, and INV_VR_EXIST mirror the same pattern.
• WIP specification versions: VALIDATE_WIP_VR, CHECK_FOR_NULL_AND_FKS_IN_WVR, and WIP_VR_EXIST.
• Customer specification versions: VALIDATE_CUST_VR, CHECK_FOR_NULL_AND_FKS_IN_CVR, and CUST_VR_EXIST.
• Supplier specification versions: VALIDATE_SUPP_VR, CHECK_FOR_NULL_AND_FKS_IN_SVR, and SUPP_VR_EXIST.
• Pre-delete guards: VALIDATE_BEFORE_DELETE_INV_VRS, VALIDATE_BEFORE_DELETE_WIP_VRS, VALIDATE_BEFORE_DELETE_CST_VRS, and VALIDATE_BEFORE_DELETE_SUP_VRS block deletion of version records that are still referenced elsewhere.
• Audit helper: CHECK_WHO supports standard WHO-column (creation/update user and timestamp) handling.

The VALIDATE_* and CHECK_FOR_NULL_AND_FKS_* routines accept rowtype parameters and a p_called_from indicator, returning a validated rowtype plus x_return_status to signal success or failure to the caller.

Tables Accessed

The package reads and writes specification version and supporting definition tables through APPS synonyms:

• GMD_MONITORING_SPEC_VRS, GMD_INVENTORY_SPEC_VRS, GMD_WIP_SPEC_VRS, GMD_CUSTOMER_SPEC_VRS, and GMD_SUPPLIER_SPEC_VRS — the five domain-specific version tables being validated.
• GMD_SPECIFICATIONS — the parent specification entity used to resolve foreign key relationships.
• FND_USER — referenced for WHO-column and audit validation.
• Quality and routing definition tables including CR_RSRC_DTL, CR_RSRC_MST, FM_FORM_MST, FM_FORM_MST_B, FM_MATL_DTL, FM_ROUT_DTL, GMD_OPERATIONS, and GMD_PARAMETERS_HDR — used to validate specification relationships against resources, formulas, routings, operations, and parameter definitions.

Usage Notes

GMD_SPEC_VRS_GRP is normally invoked indirectly rather than called directly by end users. The OPM specification setup forms (Quality and Specification windows) and the specification version public APIs route their create, update, and delete operations through these validation routines. Because it is referenced by one other package, it is also consumed as a lower-layer dependency in the OPM quality API stack.

Custom integrations that insert or modify specification version records should call the appropriate VALIDATE_* procedure before committing, checking x_return_status and handling any error messages, thereby replicating the validation applied by the standard application. Deletion logic should call the relevant VALIDATE_BEFORE_DELETE_* guard to avoid orphaning dependent records. The package is intra-APPS only; it is not a public external API and should be treated as internal infrastructure supporting specification version integrity.