Search Results jtf_perz_query_raw_sql




Overview
Explore in AI Chat

Overview

The JTF_PERZ_QUERY_RAW_SQL table is a core data object within the Oracle E-Business Suite (EBS) CRM Foundation (JTF) module. Its primary function is to persistently store the executable SQL statement text that defines a personalized query object. Personalized queries are a key feature within the EBS CRM framework, allowing users to create, save, and reuse custom data searches and reports. This table acts as the technical repository for the query logic, separating the raw SQL definition from the query's metadata, which is stored in the related JTF_PERZ_QUERY table. Its role is critical for the runtime execution of saved queries across various CRM components in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's central purpose is to hold the SQL string 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 primary identifier is the QUERY_RAW_SQL_ID column, which uniquely identifies each SQL string record. The most critical column is the one containing the actual SQL command text, though its specific name (e.g., SQL_TEXT, QUERY_STRING) is implied by the table's description. The QUERY_ID column is the essential foreign key that links the SQL string definitively to its corresponding query definition record in the JTF_PERZ_QUERY table, ensuring a one-to-one or one-to-many relationship between a query's metadata and its executable code.

Common Use Cases and Queries

This table is primarily accessed by the EBS application logic to retrieve and execute saved user queries. Common operational scenarios include the runtime rendering of a personalized report or search screen where the system fetches the SQL from this table based on a QUERY_ID. Administrators or developers may query this table for auditing, troubleshooting, or migration purposes. For instance, to analyze all SQL definitions for queries owned by a specific user, one might join to the JTF_PERZ_QUERY table:

  • SELECT pqr.sql_text, pq.QUERY_NAME FROM jtf_perz_query_raw_sql pqr, jtf_perz_query pq WHERE pqr.query_id = pq.query_id AND pq.created_by = 1234;

Another critical use case is during patch or upgrade analysis, where custom SQL strings may need to be reviewed for compatibility with new database features or changed application table structures.

Related Objects

The JTF_PERZ_QUERY_RAW_SQL table has a direct and documented dependency relationship with the JTF_PERZ_QUERY table, which stores the descriptive metadata for the query object. The relationship is enforced by a foreign key constraint where the QUERY_ID column in JTF_PERZ_QUERY_RAW_SQL references the primary key of the JTF_PERZ_QUERY table. This design indicates that for every record in JTF_PERZ_QUERY_RAW_SQL, there must be a corresponding parent record in JTF_PERZ_QUERY. The personalized query functionality is integral to the JTF (CRM Foundation) layer and is therefore referenced by various CRM application interfaces and public APIs that manage and execute saved queries.