Search Results format_sql_validation_string




Overview
Explore in AI Chat

Overview

QLTTRAFB is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Quality Management (QLT) application family. The package is declared with AUTHID CURRENT_USER, meaning its dynamic SQL statements execute under the privileges of the invoking user rather than the package owner. The header comment records that the package was created on 21 May 1996 by Paul Mishkin, and the current version line identifies revision 120.1, last modified 10 October 2005. Its classifications in ETRM place it in the OTHER category rather than as a public or private API.

The package supports validation and dynamic SQL execution for Quality collection plan elements and specifications — the configurable criteria against which quality results, characteristics, and specification limits are evaluated. Many of its routines pair a data column with an associated error column and error message so that validation failures can be surfaced back to the calling form or concurrent program in a structured way. The presence of a routine that returns a SQL validation string, together with an EXEC_SQL procedure and a token replacement helper, indicates that QLTTRAFB bridges the gap between user-defined validation expressions stored as metadata and their execution against Oracle data.

Key Procedures and Functions

  • FORMAT_SQL_VALIDATION_STRING — accepts a string and returns a reformatted VARCHAR2, preparing a stored validation expression for execution.
  • VALIDATE_TYPE — evaluates a supplied value against a datatype indicator and returns a BOOLEAN result.
  • EXEC_SQL — takes a string argument and executes it. This is the object matched by the user's search for "exec_sql".
  • DECODE_ACTION_VALUE_LOOKUP — translates a numeric code into its descriptive VARCHAR2 equivalent for action value lookups.
  • DECODE_OPERATOR — translates a numeric operator code into readable text.
  • VALIDATE_DISABLED — validates whether a collection plan element or column is disabled, given the group, user, login, request, program application, and program identifiers.
  • VALIDATE_MANDATORY — enforces mandatory entry for a column, additionally accepting a parent column name and an error column list.
  • VALIDATE_LOOKUPS — validates values against lookup definitions, with character and plan identifiers plus an error column list.
  • VALIDATE_PARENT_ENTERED — checks that a required parent column has been populated.
  • VALIDATE_READ_ONLY — introduced under tracking bugs 3104827 and 3148873 (comment dated 28 August 2003 by saugupta) to enforce read-only behavior for flag collection plan elements.
  • REPLACE_TOKEN — substitutes tokens within a validation expression, supporting parameterized validation templates.

Tables Accessed

The documented metadata lists only DBMS_SQL — the Oracle-supplied dynamic SQL built-in — among referenced objects, reached through an APPS synonym. For the 11 documented procedures and functions, no application tables are recorded; QLTTRAFB therefore operates principally as a utility layer over dynamically constructed SQL statements rather than as a direct reader or writer of QLT base tables.

Usage Notes

QLTTRAFB is referenced by three other packages within the APPS schema, and it is most plausibly invoked from Quality Management forms and concurrent programs that evaluate collection plan validation logic. The EXEC_SQL procedure should be treated with care: because the package runs with AUTHID CURRENT_USER, dynamically executed statements carry the invoker's privileges, so any custom caller must ensure the SQL string is fully controlled and cannot be influenced by end-user input. Customizations that rely on FORMAT_SQL_VALIDATION_STRING or REPLACE_TOKEN should follow the same defensive practice. As a non-public, OTHER-classified package with a 2005 revision level, it is a stable internal dependency rather than a supported extension point; Oracle support may change it without notice, so direct references from custom code should be avoided where an alternative public API exists.