Results for “jtf_fm_queries_all_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• QUERY_ID — Surrogate primary key defined by JTF_FM_QUERIES_ALL_PK, uniquely identifying each fulfillment query definition.
• QUERY_NAME and QUERY_DESC — The user-facing name and descriptive text of the query.
• QUERY_STRING — The query text or filter definition executed by the fulfillment engine.
• APP_DBNAME, APP_USERID, APP_PASSWORD — Application database connection credentials associated with execution of the query.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-tenant / multi-org data security.
• ORG_ID — Operating unit identifier for multi-org access control.
• FILE_ID — Reference to an associated file artifact.
• F_DELETEDFLAG — Soft-delete indicator.
• OBJECT_VERSION_NUMBER — Concurrency control token for optimistic locking.
• ZD_EDITION_NAME — Edition label used in EBR-enabled environments.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.

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.