Search Results jtf_perz_query_param

Overview

The JTF_PERZ_QUERY_PARAM table is a core data object within the Oracle E-Business Suite CRM Foundation (JTF) module. It functions as a child table in the personalization framework, specifically designed to store the runtime parameters associated with a saved, personalized query object. These personalized queries are often used to define filtered lists, search criteria, or report views within the CRM applications, allowing users to save and reuse complex search conditions. The table's role is to persist the individual parameter values that, when combined, constitute the complete definition of a user's saved query, enabling the system to reconstruct and execute the query on demand.

Key Information Stored

The table's primary purpose is to hold the parameter details for a query. While the full column list is not detailed in the provided metadata, the structure is defined by its primary and foreign keys. The QUERY_PARAM_ID column serves as the unique identifier for each parameter record. The QUERY_ID column is a critical foreign key that links each parameter row to its parent query definition stored in the JTF_PERZ_QUERY table. Beyond these key columns, typical parameter tables in this context would store columns for the parameter name (e.g., PARAM_NAME), the parameter's data type, the operator (such as '=', '>', 'LIKE'), and the parameter value (e.g., PARAM_VALUE). This structure allows the system to store a list of name-value pairs that define the query's filter criteria.

Common Use Cases and Queries

A primary use case is the administrative or diagnostic review of personalized queries to understand user behavior or troubleshoot issues. For instance, a support query might join to the parent table to list all parameters for queries created by a specific user. A common SQL pattern involves joining JTF_PERZ_QUERY_PARAM to JTF_PERZ_QUERY to get a readable summary.

SELECT q.QUERY_NAME, q.DESCRIPTION, p.PARAM_NAME, p.OPERATOR, p.PARAM_VALUE
FROM jtf.jtf_perz_query q,
     jtf.jtf_perz_query_param p
WHERE q.query_id = p.query_id
  AND q.created_by = :user_id;

Another critical use case is the programmatic execution or modification of saved queries, where APIs or backend logic would read from this table to build a dynamic WHERE clause. Reporting on commonly used filter parameters across the application also relies on aggregating data from this table.

Related Objects

The table maintains a strict hierarchical relationship with its parent table, as documented in the provided metadata.

• JTF_PERZ_QUERY: This is the primary and, per the documentation, the only documented foreign key relationship. Each record in JTF_PERZ_QUERY_PARAM must correspond to a single parent record in JTF_PERZ_QUERY. The relationship is enforced via the foreign key constraint on the column JTF_PERZ_QUERY_PARAM.QUERY_ID referencing JTF_PERZ_QUERY. The parent table stores the query's metadata, such as its name, description, and owner, while this table stores its constituent parameters.

The table's primary key constraint, JTF_PERZ_QUERY_PARAM_PK on QUERY_PARAM_ID, ensures each parameter record is uniquely identifiable within the system.