Search Results jtf_perz_query_order_by




Overview
Explore in AI Chat

Overview

The JTF_PERZ_QUERY_ORDER_BY table is a core data object within the Oracle E-Business Suite (EBS) CRM Foundation (JTF) module. It functions as a repository for the sorting criteria, or "ORDER BY" clauses, that are defined for personalized query objects. These personalized queries are a fundamental feature enabling users to save custom search and filter configurations across various CRM applications. The table's primary role is to persistently store the sequence and direction of columns used to order the results of these saved queries, ensuring that each execution of a personalized query returns data in a consistent, user-defined sort order. Its existence is critical for maintaining the complete definition and functional integrity of user-customized views within the CRM framework.

Key Information Stored

The table's structure is designed to capture the essential components of a sort definition. While the provided ETRM metadata does not list specific columns beyond the primary and foreign keys, the table's purpose and standard EBS design patterns indicate it typically stores the following key information. The primary key, QUERY_ORDER_BY_ID, uniquely identifies each sort rule. The foreign key column, QUERY_ID, links each sort rule to its parent query definition in the JTF_PERZ_QUERY table. Other columns commonly found in such a structure include those to specify the column or attribute to sort by (e.g., COLUMN_NAME, ATTRIBUTE_CODE), the sort sequence (e.g., SORT_ORDER_NUM to manage multiple sort columns), and the sort direction (e.g., SORT_ORDER_DIRECTION, such as 'ASC' or 'DESC'). This design allows a single personalized query to have multiple, prioritized sort criteria.

Common Use Cases and Queries

The primary use case is the backend support for the "Save Search" functionality in CRM forms like Opportunities, Service Requests, or Partners. When a user saves a query with a specific sort order, data is inserted into this table. A common reporting or diagnostic query involves joining to the main query table to analyze saved sort configurations. For example:

  • Identifying all personalized queries that order results by a specific column, such as "CREATION_DATE".
  • Troubleshooting query performance by reviewing complex multi-column sort definitions.
  • Administering user queries by selecting all sort rules for a given query ID: SELECT * FROM jtf.jtf_perz_query_order_by WHERE query_id = <query_id> ORDER BY sort_order_num;

Direct manipulation of this table via DML is strongly discouraged; interactions should occur through the appropriate CRM personalization APIs to maintain data integrity.

Related Objects

The JTF_PERZ_QUERY_ORDER_BY table has a direct and critical dependency on the JTF_PERZ_QUERY table, which stores the core definition of the personalized query itself. The documented foreign key relationship is:

  • JTF_PERZ_QUERY: This is the parent table. The foreign key is defined on the column JTF_PERZ_QUERY_ORDER_BY.QUERY_ID, which references the primary key of the JTF_PERZ_QUERY table. This relationship enforces that every sort rule must be associated with a valid, existing query object. The JTF_PERZ_QUERY table, in turn, is central to the CRM personalization engine and is referenced by other objects in the JTF schema that manage query criteria, display columns, and sharing rules.