Search Results jtf_fm_query_mes_u1

Overview

JTF.JTF_FM_QUERY_MES is a cross-reference entity within the Oracle E-Business Suite JTF (Java Transaction Framework) schema, documented as VALID in both ETRM 12.1.1 and 12.2.2. The table’s stated purpose is to store the association between a query and a master document managed by the MES (Master/Media/Content) framework. In practical terms, it functions as a bridge that records which saved query definition is bound to a given MES document identifier, enabling content, template, and history processing to resolve the query that produced or governs a document.

From a Data Vault modeling perspective, the mined relationship data classifies this object as satellite-leaning. That classification is a heuristic suggestion rather than a documented architectural declaration. The table carries a surrogate primary key and standard WHO audit columns alongside descriptive and foreign-key attributes, which is consistent with a satellite attached to a hub-and-link core. Its narrow column set and its dependent foreign keys reinforce that it is an associative/satellite construct rather than an independent business entity.

Key Information Stored

The table is physically stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and it consists of 11 documented columns in ETRM 12.2.2. The most significant are summarised below.

• MES_DOC_ID (NUMBER, mandatory): the document identifier assigned by the MES system. This is the surrogate primary key (JTF_FM_QUERY_MES_PK) and also participates in the unique business-key candidate.
• ZD_EDITION_NAME (VARCHAR2(30)): edition name used for edition-based redefinition; it completes the unique index alongside MES_DOC_ID.
• QUERY_ID (NUMBER): the query identifier, foreign key to JTF_FM_QUERIES_ALL. This is the operative business link between a document and its query.
• SECURITY_GROUP_ID (NUMBER): used in hosted/multi-tenant environments; foreign key to FND_SECURITY_GROUPS.
• F_DELETEDFLAG (VARCHAR2): flag indicating HTML components whose records have been logically deleted.
• OBJECT_VERSION_NUMBER (NUMBER): sequential locking control value used by HTML-based user interfaces.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN: standard WHO audit columns. LAST_UPDATED_BY and CREATED_BY reference FND_USER.USER_ID; LAST_UPDATE_LOGIN references FND_LOGINS.LOGIN_ID.

Two indexes are documented. JTF_FM_QUERY_MES_U1 is a UNIQUE normal index on (MES_DOC_ID, ZD_EDITION_NAME) in APPS_TS_TX_IDX, making this pair the business-key candidate. JTF_FM_QUERY_MES_N1 is a NONUNIQUE normal index on QUERY_ID, supporting lookup by query in the reverse direction.

Common Use Cases and Queries

Because the table connects documents to queries, its principal use is resolving the query associated with a given MES document, or enumerating every document that uses a given query. Typical scenarios include reporting on content-template composition, diagnosing orphaned query bindings after a query is redefined, and auditing logical deletions through F_DELETEDFLAG.

A direct lookup by document is efficient via the primary key and unique index:

• SELECT MES_DOC_ID, QUERY_ID, F_DELETEDFLAG, OBJECT_VERSION_NUMBER FROM JTF.JTF_FM_QUERY_MES WHERE MES_DOC_ID = :doc_id AND ZD_EDITION_NAME = :edition;
• SELECT MES_DOC_ID, QUERY_ID FROM JTF.JTF_FM_QUERY_MES WHERE QUERY_ID = :query_id; — uses JTF_FM_QUERY_MES_N1.

For a full extract, the code block documented above selects all eleven columns:

SELECT MES_DOC_ID, QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
       CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, F_DELETEDFLAG,
       OBJECT_VERSION_NUMBER, SECURITY_GROUP_ID, ZD_EDITION_NAME
FROM JTF.JTF_FM_QUERY_MES;

Filtering on F_DELETEDFLAG keeps logically deleted rows out of user-facing reports. Filtering on SECURITY_GROUP_ID is advisable in hosted deployments. Any update path should respect OBJECT_VERSION_NUMBER as the database-locking discriminator to avoid lost updates.

Related Objects

The relationship metadata identifies the following significant objects. Foreign keys originating from this table point to JTF_FM_QUERIES_ALL via QUERY_ID and to FND_SECURITY_GROUPS via SECURITY_GROUP_ID. Two tables reference JTF_FM_QUERY_MES through MES_DOC_ID: JTF_FM_CONTENT_HISTORY and JTF_FM_TEMPLATE_CONTENTS.

• JTF_FM_QUERIES_ALL — parent of QUERY_ID; supplies the query text and definition referenced by each row.
• JTF_FM_CONTENT_HISTORY — references MES_DOC_ID; records historical content events for the same document.
• JTF_FM_TEMPLATE_CONTENTS — references MES_DOC_ID; holds template composition for the document.
• FND_SECURITY_GROUPS — parent of SECURITY_GROUP_ID for hosted/partitioned data access.
• FND_USER and FND_LOGINS — indirectly referenced through the WHO columns LAST_UPDATED_BY, CREATED_BY, and LAST_UPDATE_LOGIN.
• JTF_FM_QUERY_MES# — the editioning view shown in the dependency section.

No database objects are reported as directly referenced by JTF.JTF_FM_QUERY_MES beyond the foreign keys listed, confirming its narrow, associative role in the JTF content-query model.