Search Results jtf_fm_queries_all




Overview
Explore in AI Chat

Overview

JTF_FM_QUERIES_ALL is a CRM Foundation (JTF) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores metadata definitions for fulfillment queries used by the CRM fulfillment engine. Each row represents a named, reusable query object — typically a stored SQL or parameterized search definition — that the fulfillment manager invokes to identify and process business documents such as orders, quotes, service requests, or tasks. The table functions as a catalog of query definitions rather than a transactional fact store.

From a Data Vault modeling perspective, the documented FK structure suggests this object is hub-leaning: QUERY_ID acts as a durable business key around which descriptive and administrative attributes are attached, and dependent child tables reference it as a parent. It therefore behaves less like an insert-only transactional satellite and more like a master/reference entity. The presence of ZD_EDITION_NAME and OBJECT_VERSION_NUMBER indicates the table participates in Oracle's edition-based redefinition (EBR) and optimistic locking frameworks.

Key Information Stored

The table comprises 18 documented columns. The most significant are:

The unique index JTF_FM_QUERIES_ALL_U1 covers (QUERY_ID, ZD_EDITION_NAME), confirming that the business key is the query identifier scoped by edition.

Common Use Cases and Queries

Typical scenarios include auditing which fulfillment queries are defined in an instance, tracing which messages depend on a given query, and validating query definitions across editions.

SELECT q.query_id, q.query_name, q.query_desc,
       q.security_group_id, q.org_id, q.f_deletedflag
FROM   jtf.jtf_fm_queries_all q
WHERE  NVL(q.f_deletedflag,'N') = 'N'
ORDER BY q.query_name;

Because the table is EBR-aware, lookups against the current edition should filter on ZD_EDITION_NAME or use an edition-enabled synonym. Joining to JTF_FM_QUERY_MES reveals dependent message rows:

SELECT m.*, q.query_name
FROM   jtf.jtf_fm_query_mes m,
       jtf.jtf_fm_queries_all q
WHERE  m.query_id = q.query_id;

Reporting use cases include catalog reports of active queries per security group, impact analysis prior to deleting or renaming a query, and migrations that must copy query definitions between environments.

Related Objects

The following objects are most relevant based on the documented relationships:

  • JTF_FM_QUERY_MES — Child table whose QUERY_ID column references JTF_FM_QUERIES_ALL.QUERY_ID; stores per-query messages.
  • FND_SECURITY_GROUPS — Parent referenced by SECURITY_GROUP_ID, controlling visibility of the query.
  • FND_APPLICATION / FND_USER — Contextual references for APP_DBNAME and APP_USERID.
  • JTF_FM_QUERIES_ALL_PK and JTF_FM_QUERIES_ALL_U1 — The primary-key constraint and edition-aware unique index.
  • FND_ORG_ACCESS / FND_ORG_ASSIGNMENT — Related to ORG_ID-based multi-org filtering.

Together these relationships position JTF_FM_QUERIES_ALL as the authoritative catalog of fulfillment query definitions, with JTF_FM_QUERY_MES providing its primary downstream dependency.