Search Results jtf_fm_query_mes_pk

Overview

JTF_FM_QUERY_MES is a cross-reference table within the JTF (CRM Foundation) product family of Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. Its documented purpose is to store the association between a stored query and a "master document" entity, where the master document identifier is carried in the MES_DOC_ID column. In practical terms, the table acts as the linkage layer that records which saved query definitions belong to a given master entity, supporting content and template management features in the CRM Foundation module.

The ETRM metadata classifies this object heuristically as satellite-leaning within a Data Vault modeling context. This is a modeling suggestion rather than a physical property: the table primarily holds descriptive attributes attached to a parent identity (MES_DOC_ID) plus a foreign key to the query definition, and it is referenced by downstream content objects rather than acting as an independent transaction hub.

Key Information Stored

The table contains eleven documented columns. The most significant are:

• MES_DOC_ID — the surrogate primary key, enforced by JTF_FM_QUERY_MES_PK. It identifies the master document record associated with a query.
• QUERY_ID — foreign key to JTF_FM_QUERIES_ALL, identifying the stored query definition participating in the association.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling multi-tenant style data visibility per security group.
• ZD_EDITION_NAME — editioning column, used with the unique index JTF_FM_QUERY_MES_U1 (MES_DOC_ID, ZD_EDITION_NAME) to isolate data across editioned objects.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications Framework to detect concurrent updates.
• F_DELETEDFLAG — soft-delete indicator.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording creation and last modification context.

The business-key candidate is the combination of MES_DOC_ID and ZD_EDITION_NAME, while MES_DOC_ID alone serves as the primary-key surrogate.

Common Use Cases and Queries

Reporting and integration scenarios typically resolve the association between a master document and its originating query. A representative join pattern is:

• Joining JTF_FM_QUERY_MES to JTF_FM_QUERIES_ALL on QUERY_ID = QUERY_ID to retrieve query names and definitions for a given master document.
• Filtering by SECURITY_GROUP_ID to restrict output to a specific security context.
• Filtering by F_DELETEDFLAG = 'N' to exclude logically deleted associations.
• Joining to FND_SECURITY_GROUPS to render human-readable security group names in administrative reports.

Typical use cases include auditing which queries are attached to which master content, troubleshooting template and content history issues, and building migration or validation scripts that compare editioned rows via ZD_EDITION_NAME.

Related Objects

The most significant related objects, based on documented foreign key relationships, are:

• JTF_FM_QUERIES_ALL — parent of QUERY_ID; holds the stored query definitions.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for security group resolution.
• JTF_FM_CONTENT_HISTORY — references this table through MES_DOC_ID, tracking content history records.
• JTF_FM_TEMPLATE_CONTENTS — references this table through MES_DOC_ID, linking template content to master documents.

These relationships position JTF_FM_QUERY_MES as a central association point between query definitions and the content, template, and history objects that depend on the master document identity.