Search Results jtf_fm_email_stats_u1

Overview

The JTF.JTF_FM_EMAIL_STATS table is a transactional statistics repository within the Oracle E-Business Suite Customer Relationship Management (CRM) foundation schema (JTF). It stores aggregated delivery and engagement metrics for electronic mail dispatched through the Fulfillment Server, which is the outbound messaging infrastructure used by Oracle Marketing, iStore, and related fulfillment-driven modules. Each row corresponds to a single Fulfillment Request and captures the outcome of the email campaign or notification batch associated with that request.

From a Data Vault modeling perspective, the heuristic classification is standalone. The table carries no foreign key dependencies and exposes a single-column primary key (REQUEST_ID), meaning it functions closer to a hub-like key anchor combined with an implicit satellite of descriptive measures. Modelers should treat REQUEST_ID as the business key, with the numeric counters (TOTAL, SENT, MALFORMED, and so on) acting as satellite-style descriptive attributes. Because it is not part of a hub-link-satellite chain, it can be loaded independently and joined to fulfillment tables at query time rather than on insert.

Key Information Stored

The table contains 15 documented columns. The most operationally significant are:

• REQUEST_ID — Numeric identifier of the Fulfillment Request. This is the primary key and the sole column in the unique index JTF_FM_EMAIL_STATS_U1, making it the business-key candidate as well as the surrogate key.
• TOTAL — Aggregate count of emails queued or sent for the request.
• SENT — Number of emails successfully delivered.
• MALFORMED — Recipients whose email address failed syntax or format validation and therefore could not be sent.
• BOUNCED — Emails returned undeliverable by the receiving mail system.
• OPENED — Emails recorded as opened by recipients, supporting engagement measurement.
• UNSUBSCRIBED — Recipients who opted out after receiving the request.
• DO_NOT_CONTACT — Recipients whose contact record carried a "Do Not Contact" flag at send time.
• RESUBMITTED_MALFORMED — Jobs within the request that initially had malformed addresses but were corrected and resubmitted.
• RESUBMITTED_JOB_COUNT — Count of individual jobs resubmitted for the request.
• CREATION_DATE / LAST_UPDATE_DATE — Standard audit timestamps maintained alongside CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The primary use case is campaign effectiveness reporting: comparing SENT against TOTAL to derive delivery rate, and OPENED against SENT to derive open rate. Operations teams use MALFORMED, BOUNCED, and RESUBMITTED_MALFORMED to quantify data-quality remediation effort, while marketing compliance teams monitor UNSUBSCRIBED and DO_NOT_CONTACT to enforce opt-out obligations.

A typical delivery-health query:

• SELECT REQUEST_ID, TOTAL, SENT, BOUNCED, MALFORMED, OPENED, UNSUBSCRIBED, DO_NOT_CONTACT FROM JTF.JTF_FM_EMAIL_STATS WHERE REQUEST_ID = :p_request_id;

A campaign efficiency view aggregates ratios across many requests:

• SELECT REQUEST_ID, ROUND(SENT/NULLIF(TOTAL,0)*100,2) AS delivery_pct, ROUND(OPENED/NULLIF(SENT,0)*100,2) AS open_pct, ROUND(BOUNCED/NULLIF(TOTAL,0)*100,2) AS bounce_pct FROM JTF.JTF_FM_EMAIL_STATS WHERE CREATION_DATE >= :p_range_start;

Reconciliation queries identify requests with outstanding remediation: WHERE MALFORMED > 0 AND RESUBMITTED_MALFORMED < MALFORMED surfaces jobs not yet corrected.

Related Objects

The ETRM relationship data classifies this object as standalone, with the only documented dependent structure being its own unique index:

• JTF_FM_EMAIL_STATS_U1 — Unique index on REQUEST_ID (tablespace APPS_TS_TX_IDX) enforcing the business key.

Because no foreign keys are documented, integration with surrounding fulfillment objects is driven by the shared REQUEST_ID value rather than by referential constraints. The most significant objects that logically join on REQUEST_ID are the JTF Fulfillment Server request and job tables (such as the fulfillment request header and request job tables that generate the REQUEST_ID consumed here), plus the recipient and contact tables that supply the address-quality attributes reflected in MALFORMED, DO_NOT_CONTACT, and UNSUBSCRIBED. Reporting layers commonly join this table to CRM contact and campaign tables to attribute metrics back to a customer segment. Administrators should note the absence of enforced referential integrity and validate REQUEST_ID against the source fulfillment request table when building joins.