Search Results sql_valid

Overview

OKS_PROFILES_VISIBLE is an Oracle E-Business Suite service contract management package body owned by the APPS schema and classified under the OTHER API category. Its single documented function, PROFILE_VISIBLE_VALUE, evaluates the visibility condition associated with a given profile option value. In the context of Oracle EBS 12.1.1 and 12.2.2, fnd_profile_option_values may be constrained by a SQL validation statement (sql_validation) stored on the profile option definition. That validation statement frequently contains substitution tokens — notably :PROFILE_OPTION_VALUE and :VISIBLE_OPTION_VALUE — which must be bound at runtime before the statement can be executed. This package performs that token resolution: it parses the stored sql_validation text, isolates the SQL fragment between SELECT and COLUMN keywords, removes escape backslashes, and substitutes the actual profile option value into the bind location, producing a runnable predicate that determines whether the profile value should be visible to the user.

Key Procedures and Functions

PROFILE_VISIBLE_VALUE — the only documented function in the package. It returns a varchar2 value representing the resolved visibility expression for a profile option. The function accepts identifiers for the application, profile option, level, level value, and an optional level value application id (defaulting to NULL), and it uses these to locate the correct row in the profile option values table. Internally, a cursor (GET_SQL) joins fnd_profile_options_vl with fnd_profile_option_values and derives a SQL_VALID column by extracting and cleaning the sql_validation text. Cursor results are iterated; positional flags such as INTO, WHERE, the profile value token, and the visible option token are computed with instr to build the final predicate. Local variables including v_profoval (initialised to 'NO_VAL') hold intermediate results, and the function returns the constructed visibility string for use by the caller.

Tables Accessed

• FND_PROFILE_OPTION_VALUES — the core source of user/profile level values. The cursor filters on profile_option_id, level_id, application_id, level_value, and level_value_application_id to retrieve the effective profile_option_value for the requested context.
• DUAL — used for scalar instr computations that locate the INTO, WHERE, :PROFILE_OPTION_VALUE, and :VISIBLE_OPTION_VALUE positions within the extracted SQL fragment.

The join to fnd_profile_options_vl (visible via the package's own SQL, though not listed among the documented table references) supplies the sql_validation column from which the visibility predicate is derived.

Usage Notes

This package is normally invoked indirectly by Oracle service contract setup components rather than by end users or concurrent programs. When a form or region displays profile-dependent fields — for example, contract or service profile options governed by visibility rules — the calling framework passes the application, profile option, level, and level value identifiers to PROFILE_VISIBLE_VALUE and uses the returned string to decide whether the profile value is rendered or suppressed. The function is not referenced by other documented packages, so its consumers are typically internal forms and tightly coupled customisations. Custom code that needs to reproduce profile visibility logic should call this function rather than re-implementing the token substitution, as the header revision (115.4) is stable and the parsing logic is specific to the FND profile validation format. Because the function returns only the visibility expression, callers must still execute or interpret it appropriately within their own rendering logic.