Search Results jtf_fm_email_stats




Overview
Explore in AI Chat

Overview

JTF_FM_EMAIL_STATS is a table in the JTF schema (CRM Foundation product) of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. Its purpose, per the ETRM metadata, is to hold statistical information on emails sent out by the Fulfillment Server. The Fulfillment Server is the concurrent-processing engine that dispatches campaign and interaction email messages in Oracle Marketing and related CRM modules; this table therefore acts as an operational telemetry record that captures the outcome counts for a batch of email dispatch work. The table contains 15 documented columns, is owned by JTF, and is secured by the unique index JTF_FM_EMAIL_STATS_U1 on REQUEST_ID, which is also the declared primary key.

From a dimensional modeling perspective, the documented relationship data classifies this object heuristically as a standalone structure with no outbound or inbound foreign keys mined from the schema. A Data Vault modeling suggestion would be to treat JTF_FM_EMAIL_STATS as a satellite attached to the concurrent request business key represented by REQUEST_ID, since it stores descriptive, time-stamped measurements about a single process execution rather than independent business entities or their associations. Organizations designing a warehouse layer over EBS should note this is a suggestion only; no FK constraints enforce it in the source system.

Key Information Stored

The most consequential columns cluster into three groups.

  • Identity and grain: REQUEST_ID is both the primary key and the sole business-key candidate via the unique index JTF_FM_EMAIL_STATS_U1. It corresponds to the concurrent request that executed the email dispatch, giving the table one row per fulfillment run.
  • Outcome counters: TOTAL, SENT, MALFORMED, BOUNCED, OPENED, UNSUBSCRIBED, and DO_NOT_CONTACT record the disposition of messages within that run. RESUBMITTED_MALFORMED and RESUBMITTED_JOB_COUNT track remediation activity — how many malformed addresses were retried and how many jobs were resubmitted.
  • Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN follow the standard EBS who-column convention and support change tracking and reconciliation.

Notably, the counter set spans deliverability (SENT, BOUNCED), data quality (MALFORMED), engagement (OPENED), and compliance (UNSUBSCRIBED, DO_NOT_CONTACT). This makes the table useful for both operations and marketing compliance reporting.

Common Use Cases and Queries

Typical scenarios include monitoring fulfillment throughput, diagnosing high malformed or bounce rates, and evidencing unsubscribe and do-not-contact handling for compliance audits. A simple operational query joins the statistics to the concurrent request:

  • SELECT s.REQUEST_ID, s.TOTAL, s.SENT, s.BOUNCED, s.OPENED, s.CREATION_DATE FROM JTF.JTF_FM_EMAIL_STATS s WHERE s.CREATION_DATE >= SYSDATE - 30 ORDER BY s.CREATION_DATE DESC;
  • Filter for problem runs: WHERE s.MALFORMED > 0 OR s.BOUNCED > (s.SENT * 0.05);
  • Report remediation effectiveness: SELECT s.REQUEST_ID, s.MALFORMED, s.RESUBMITTED_MALFORMED, s.RESUBMITTED_JOB_COUNT FROM JTF.JTF_FM_EMAIL_STATS s WHERE s.RESUBMITTED_JOB_COUNT > 0;
  • Derive delivery rate: SELECT ROUND(s.SENT / NULLIF(s.TOTAL,0) * 100, 2) AS delivery_pct FROM JTF.JTF_FM_EMAIL_STATS s;

Because the table has no foreign keys, joins are performed on REQUEST_ID against FND_CONCURRENT_REQUESTS and related concurrent program tables rather than through enforced constraints.

Related Objects

The documented metadata lists no FK relationships, so associations below are based on the semantic role of REQUEST_ID and the CRM Foundation module context rather than mined constraints.

  • FND_CONCURRENT_REQUESTS — natural join on REQUEST_ID; supplies program name, phase, status, and completion time.
  • FND_CONCURRENT_PROGRAMS — identifies the email fulfillment concurrent program that produced the statistics.
  • JTF_FM_EMAIL_STATS_U1 — the unique index enforcing business-key uniqueness on REQUEST_ID.
  • JTF_FM_EMAIL_* family tables — companion Fulfillment Server objects in the JTF schema that store message-level dispatch detail.
  • FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to the submitting operator.
  • FND_LOG_MESSAGES / FND_CONCURRENT_REQUEST output — log output correlated by REQUEST_ID for troubleshooting failed or partial runs.

Together these objects allow a complete picture to be assembled from request definition through dispatch outcome to audit trail.