Search Results jtf_fm_queries_all_u1

Overview

JTF.JTF_FM_QUERIES_ALL is a transactional table in the Oracle E-Business Suite JTF (Applications Technology / CRM Foundation) schema that stores the definitions of fulfillment queries. Each row represents a named query used by the fulfillment management components to retrieve, for a document master, the data that drives fulfillment processing. The table holds the connection context for the target application database (database name, user ID, and password), the query text itself, descriptive attributes, and standard audit columns. In Oracle EBS 12.1.1 and 12.2.2, the object is flagged VALID and is owned by JTF, with the FND design data reference JTF.JTF_FM_QUERIES_ALL. Physically, the table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, and its unique indexes are stored in APPS_TS_TX_IDX.

The heuristic Data Vault classification mined from the foreign-key structure is hub-leaning. This should be treated as a modeling suggestion: the table behaves as a central entity keyed by a stable surrogate identifier, with descriptive and audit attributes attached, rather than as a pure transactional link or versioned satellite.

Key Information Stored

The surrogate primary key is QUERY_ID (NUMBER), enforced through the JTF_FM_QUERIES_ALL_PK constraint. A separate unique index, JTF_FM_QUERIES_ALL_U1, covers QUERY_ID together with ZD_EDITION_NAME; this composite index acts as the documented business-key candidate and supports edition-based or multi-version access patterns.

The most significant columns include:

• QUERY_ID — the numeric query identifier and primary key.
• QUERY_NAME — the descriptive name of the fulfillment query (VARCHAR2(50)).
• QUERY_DESC — free-text description of the query (VARCHAR2(255)).
• QUERY_STRING — the SQL text that retrieves data for the document master (VARCHAR2(4000)).
• APP_DBNAME — the application database name (VARCHAR2(240)).
• APP_USERID and APP_PASSWORD — the credentials used to connect to the target application database (VARCHAR2(240) each; schema credentials and should be treated as sensitive).
• FILE_ID — the file identifier where the query string is now stored in FND_LOBS, reflecting the migration of query text out of this table.
• ORG_ID — the organization identifier for multi-org partitioning.
• F_DELETEDFLAG — flag indicating records deleted by HTML components.
• OBJECT_VERSION_NUMBER — used for database locking control in HTML-based user interfaces.
• SECURITY_GROUP_ID — used in hosted environments; references FND_SECURITY_GROUPS.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.

Common Use Cases and Queries

Typical uses include administering and auditing fulfillment query definitions, resolving the query text (now primarily via FND_LOBS through FILE_ID), and validating connection context for cross-database fulfillment operations. Common SQL patterns are straightforward lookups and joins:

• Retrieve a query by name: SELECT QUERY_ID, QUERY_NAME, QUERY_DESC FROM JTF_FM_QUERIES_ALL WHERE QUERY_NAME = :name AND F_DELETEDFLAG IS NULL.
• List active queries for an operating unit: SELECT QUERY_ID, QUERY_NAME FROM JTF_FM_QUERIES_ALL WHERE ORG_ID = :org_id.
• Resolve the query text: join FILE_ID to FND_LOBS to reconstruct the stored SQL, since QUERY_STRING is now migrated to the LOB store.
• Audit changes: SELECT QUERY_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM JTF_FM_QUERIES_ALL ORDER BY LAST_UPDATE_DATE DESC.

Reporting use cases include inventorying fulfillment queries by description, tracking deletions via F_DELETEDFLAG, and reconciling security-group assignments in hosted deployments.

Related Objects

The following objects are the most significant dependencies based on the documented relationship data:

• JTF.JTF_FM_QUERY_MES — references this table via QUERY_ID; the message/parameter detail records that depend on a parent query.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for hosted-environment isolation.
• FND_LOBS — holds the migrated query string addressed by FILE_ID.
• FND_USER — implicit parent of LAST_UPDATED_BY and CREATED_BY WHO columns.
• FND_LOGINS — implicit parent of LAST_UPDATE_LOGIN.

Together these relationships position JTF_FM_QUERIES_ALL as the hub of the fulfillment query model, with detail and message records hanging off QUERY_ID and reference data providing security and audit context.