Search Results validate_after_insert_all

Overview

GMD_QC_TEST_VALUES_GRP is an Oracle EBS PL/SQL group API package owned by the APPS schema (AUTHID CURRENT_USER) that manages the test value definitions belonging to Quality Control tests in the Oracle Process Manufacturing (OPM) Quality module. Test values represent the individual specifications, subranges, labels, and descriptive characteristics associated with a QC test — for example the numeric subranges of a "L" (label range) test or the acceptable text values of a character test. The package supplies the validation and maintenance logic that keeps the test value detail table (GMD_QC_TEST_VALUES / GMD_QC_TEST_VALUES_B and its translation table GMD_QC_TEST_VALUES_TL) consistent with the parent test header table (GMD_QC_TESTS / GMD_QC_TESTS_B).

A primary responsibility of the package is overlap detection. For numeric range-with-label tests, submitted subranges must not intersect one another within the same test; the package enforces this constraint and, in the process, derives the overall minimum and maximum values of the complete range so that the test header can be maintained. The package also centralizes pre-insert validation, post-insert aggregation, and deletion validation, allowing both the OPM Quality forms and programmatic callers to apply the same business rules.

Key Procedures and Functions

The procedures below are documented in the package specification and ETRM metadata. No invented parameter lists are shown; only documented purposes are described.

• CHECK_RANGE_OVERLAP — Checks whether a subrange for a test type 'L' (numeric range with label) overlaps any other subrange within the same test. It is documented to be called after insert/update of GMD_QC_TEST_VALUES_TL for test type 'L' but before COMMIT. It returns the minimum and maximum values of the whole range along with a return status and message data.
• VALIDATE_BEFORE_INSERT — Validates test value data before insert, accepting a record shaped like GMD_QC_TEST_VALUES%ROWTYPE and returning a validated record plus status and message outputs.
• VALIDATE_AFTER_INSERT_ALL — Called after all test values are inserted; updates min_value_num and max_value_num on the test header table and validates that the range does not overlap.
• VALIDATE_BEFORE_DELETE — Validation logic executed prior to deleting a test value, ensuring referential and business-rule integrity.
• VALIDATE_AFTER_DELETE_ALL — Post-deletion validation, typically refreshing header min/max values after all test values of a set are removed.
• GET_TEST_VALUE_DESC — Retrieves the description of a test value, supporting display and lookup requirements.
• TEXT_RANGE_CHAR_TO_SEQ — Converts a character-based text range into a sequence representation, supporting range comparison and ordering of character test values.

Tables Accessed

The package reads and writes the following objects through APPS synonyms:

• GMD_QC_TESTS and GMD_QC_TESTS_B — the QC test header tables, where min_value_num and max_value_num are updated based on the aggregated test values.
• GMD_QC_TEST_VALUES and GMD_QC_TEST_VALUES_B — the test value detail tables, which hold the individual subranges, labels, and values validated and maintained by this package.

The package header comment additionally references GMD_QC_TEST_VALUES_TL, the translation table for test value descriptive text, as the table involved in the insert/update sequence that must precede overlap checking.

Usage Notes

GMD_QC_TEST_VALUES_GRP is classified as a GRP (group) API and is referenced by five other packages, indicating that it acts as a shared service layer for QC test value maintenance rather than an object invoked directly by end users. Typical invocations include:

• OPM Quality forms — the Quality Control test definition form calls the validation and overlap-check procedures when users enter or amend test values, ensuring that duplicate or overlapping subranges are rejected before the transaction commits.
• Other PL/SQL packages — the five referencing packages call these procedures to reuse validation and header aggregation logic during test creation, copying, or maintenance.
• Custom and conversion code — when loading QC test values programmatically, callers must follow the documented sequence: validate before insert, insert the values, call VALIDATE_AFTER_INSERT_ALL to synchronize header min/max values, and, for type 'L' tests, invoke CHECK_RANGE_OVERLAP before COMMIT.

Because overlap checking is performed before COMMIT, callers must not issue an intermediate commit between inserting test values and invoking CHECK_RANGE_OVERLAP, or the overlap validation will not see the pending rows. Errors are surfaced through the standard x_return_status and x_message_data out parameters.