Search Results check_uniqueness

Overview

JTF_Utility_PVT is a private PL/SQL package owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. As indicated by its declared purpose in the package header, it provides a private API for a set of common, low-level validation and utility tasks that recur throughout the JTF (CRM Foundation / Technology Foundation) product family. The package is declared with AUTHID CURRENT_USER, meaning that its executable statements execute with the privileges of the invoking user rather than the package owner. This choice matters because several of the package's routines inspect application data generically, and the results depend on the calling session's security context.

Because the package is classified as PVT (private) rather than PUBLIC, it is not intended as a formally supported integration API. It is nonetheless a shared internal building block, and the ETRM metadata records that it is referenced by five other packages. That reference count reflects its role as a central utility layer: rather than each dependent package re-implementing foreign key validation or debug output, they delegate to JTF_Utility_PVT. The header carries a revision identifier of 120.1 dated 2005/07/02, which indicates the code base has remained stable across the 12.1.1 and 12.2.2 codelines.

Key Procedures and Functions

The ETRM documentation lists four documented program units. Their names and documented purposes are as follows:

• CHECK_FK_EXISTS — Documented as a function that checks whether a foreign key is valid. It returns FND_API.g_true or FND_API.g_false. The header notes that any exception encountered is raised to the caller rather than being swallowed internally, so calling code must be prepared to handle exceptions. It also accepts a data type indicator for the primary key value, and the header comments caution against beginning any additional where clause with the keyword "AND."
• CHECK_UNIQUENESS — Documented as a function that checks the uniqueness of keys. The header explains that to keep the routine flexible, the caller passes in the where clause used for the unique key check. This makes it a generic duplicate-detection utility rather than a table-specific constraint check.
• DEBUG_MESSAGE — A diagnostic routine used to emit debug output during package execution. This is consistent with the package's stated purpose of supporting common tasks with minimal overhead.
• DISPLAY_MESSAGES — A routine for presenting accumulated messages, presumably surfacing the FND message stack to the caller or end user.

Two package-level constants, g_number and g_varchar2, are declared to represent numeric and varchar2 data types respectively, and are intended for use with the data type parameter of CHECK_FK_EXISTS. A resource_locked exception is also declared with PRAGMA EXCEPTION_INIT against Oracle error -54, allowing callers to trap resource-busy conditions explicitly.

Tables Accessed

The ETRM extract for this object records no tables referenced through APPS synonyms. This is expected for a utility package: its routines are designed to operate against tables and columns whose names are supplied at runtime by the caller. CHECK_FK_EXISTS and CHECK_UNIQUENESS construct and execute dynamic SQL against the table, primary key column, and where clause provided as arguments. Consequently, the effective set of accessed tables is determined dynamically by each calling package rather than being statically resolvable from the package body. Any analysis of data access for this package should therefore be performed on the caller side.

Usage Notes

JTF_Utility_PVT is invoked from PL/SQL rather than directly from Oracle Forms or concurrent program definitions. Its five documented dependent packages call it as part of validation logic executed during record creation, update, or interface processing. Typical custom-code usage follows the pattern of calling CHECK_FK_EXISTS to confirm that a value supplied for a foreign key column actually exists in the parent table before attempting an insert or update, and calling CHECK_UNIQUENESS before committing a new key combination.

Several practical constraints apply. Callers must supply fully qualified table and column names suitable for dynamic SQL, pass the correct data type constant so that the primary key value is compared correctly, and avoid leading the additional where clause with "AND." Because exceptions propagate to the caller, invoking code must include exception handlers for unexpected errors, and given the AUTHID CURRENT_USER declaration, validation results reflect the privileges of the invoking session. As a private package, it should not be treated as a supported public interface, and direct dependencies on it in customizations carry upgrade risk across 12.1.1 and 12.2.2.