Results for “jtf_fm_query”

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

Overview

The APPS.JTF_FM_QUERY view is a CRM Foundation (JTF) database object shipped with Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the metadata and connection attributes of fulfillment queries defined within the CRM Foundation module. Fulfillment queries are reusable, stored query definitions that the CRM fulfillment engine executes against external or internal data sources to retrieve results used by fulfillment and task-processing logic. The view presents a curated column list drawn from the underlying fulfillment query store, giving administrators, integrators, and reporting authors a stable, read-oriented interface to query definitions without directly accessing the base table.

The view is owned by the APPS schema and holds VALID status in the ETRM catalog. Because it is a view rather than a table, it carries no independent storage; all reads are resolved against its single documented referenced object. This design is typical of Oracle EBS layering, where APPS-owned views serve as the public API surface for a module while the physical persistence lives in a module-specific synonym.

Underlying Base Objects

The view is defined over a single base object, JTF_FM_QUERIES_ALL, which is exposed to the APPS schema as a synonym. The view definition selects QUERY_ID, APP_DBNAME, APP_USERID, APP_PASSWORD, QUERY_STRING, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ORG_ID, F_DELETEDFLAG, QUERY_NAME, QUERY_DESC, OBJECT_VERSION_NUMBER, and FILE_ID from that synonym.

The _ALL suffix on the base object indicates that the underlying table is partitioned along Multi-Org (organization) lines, with the ORG_ID column acting as the organizational discriminator. The view therefore inherits Multi-Org semantics: rows are filtered by the operating unit context in which the query is issued. References to JTF_FM_QUERY elsewhere in the documentation should be understood as pointers to this APPS synonym and its base table, not to a separate physical entity.

Key Columns

• QUERY_ID — Primary identifier for the fulfillment query definition; the principal join key to related CRM Foundation objects.
• QUERY_NAME / QUERY_DESC — User-facing name and description of the query, used in administrative and selection screens.
• APP_DBNAME / APP_USERID / APP_PASSWORD — Connection credentials or database identifier used when the fulfillment query is executed against a target application data source. APP_USERID is the account under which the query runs.
• QUERY_STRING — The stored SQL or query text executed by the fulfillment engine.
• ORG_ID — Operating unit discriminator supporting Multi-Org security.
• F_DELETEDFLAG — Soft-delete indicator; a set flag marks the record as logically removed.
• FILE_ID — Reference to an associated file, where the query definition originates from or is attached to one.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the framework to detect concurrent updates.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN capture standard EBS who/when audit information.

Common Use Cases and Queries

Typical scenarios include auditing stored fulfillment queries, identifying the data-source account (APP_USERID) used by each query, and driving custom reports over CRM Foundation query definitions. A minimal listing follows:

SELECT query_id, query_name, app_dbname, app_userid, org_id FROM apps.jtf_fm_query WHERE f_deletedflag = 'N';

To locate queries referencing a particular account:

SELECT query_id, query_name, app_userid, query_string FROM apps.jtf_fm_query WHERE app_userid = :p_userid;

Because APP_PASSWORD is exposed, access should be tightly controlled and the column masked in any reporting layer. Always apply ORG_ID or Multi-Org security predicates so results respect operating unit boundaries, and filter on F_DELETEDFLAG to exclude logically deleted definitions.