Search Results jtf_perz_query_pk

Overview

The JTF_PERZ_QUERY table is a core data object within the Oracle E-Business Suite CRM technology stack, specifically belonging to the JTF (CRM Foundation) product family. It serves as the central repository for storing metadata that defines personalized query objects. In the context of Oracle EBS 12.1.1 and 12.2.2, these queries are fundamental to enabling user-specific, saved search criteria across various CRM modules, such as Sales, Service, and Marketing. The table's role is to persistently store the definition and configuration of a query, which is then associated with a user's personalized profile, allowing for efficient retrieval and execution of frequently used, complex searches.

Key Information Stored

The primary entity stored is the query definition itself, uniquely identified by the QUERY_ID column, which serves as the primary key (JTF_PERZ_QUERY_PK). A critical foreign key relationship exists via the PROFILE_ID column, which links each query to a specific user profile in the JTF_PERZ_PROFILE table. While the full column list is not detailed in the provided metadata, the existence of related tables for parameters, ordering, and raw SQL indicates that JTF_PERZ_QUERY typically stores header-level information. This likely includes columns for the query name, description, the application or module context, creation dates, and the user ID of the owner. The detailed components of the query—such as conditions (parameters), sort orders, and the underlying SQL structure—are stored in separate, child tables.

Common Use Cases and Queries

The primary use case is the management and execution of saved searches within the CRM interface. For instance, a sales representative may save a complex search for "My High-Priority Opportunities in the Northeast Region." The definition of this search is stored across JTF_PERZ_QUERY and its related tables. Common administrative or reporting queries involve auditing saved queries or troubleshooting. A sample SQL pattern to list all personalized queries for a specific user profile would be:

SELECT q.QUERY_ID, q.NAME, q.DESCRIPTION
FROM JTF.JTF_PERZ_QUERY q, JTF.JTF_PERZ_PROFILE p
WHERE q.PROFILE_ID = p.PROFILE_ID
AND p.USER_ID = :USER_ID;

Another common operation is identifying orphaned queries or analyzing query usage patterns by joining to the profile and user tables (FND_USER).

Related Objects

JTF_PERZ_QUERY is the parent table in several key relationships, as defined by its foreign keys. The JTF_PERZ_PROFILE table is the primary parent, holding the user profile to which the query belongs. Key child tables that store the query's constituent parts include:

• JTF_PERZ_QUERY_PARAM: Stores the individual criteria or parameters (e.g., column operator, value) that make up the query's WHERE clause conditions.
• JTF_PERZ_QUERY_ORDER_BY: Stores the sort order specifications for the query results.
• JTF_PERZ_QUERY_RAW_SQL: May store a generated or user-defined SQL statement representation of the complete query.

This structure allows the personalized query engine to dynamically reconstruct and execute searches based on a modular, stored definition.