Results for “iem_themes”

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

Overview

IEM_THEMES is a table within the IEM (Email Center) product schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to maintain the list of themes associated with each classification used by the Email Center. In practical terms, the table stores the catalog of theme definitions that the Email Center uses when categorizing, routing, or auto-responding to inbound email messages. Each theme is tied to a parent classification, allowing the Email Center to organize the taxonomy of incoming message categories hierarchically: a classification groups related themes, and each theme carries the keywords, scoring criteria, and response logic needed to classify and answer a message.

From a Data Vault modeling perspective, the relationship data classifies this object as satellite-leaning. That is a heuristic suggestion rather than a mandate: the table behaves like a descriptive satellite attached to the classification hub, since its rows carry attributes describing a theme and reference a parent classification through a foreign key. Modelers should treat the classification identifier as the link to the parent hub and THEME_ID as the driving key within the satellite.

Key structural facts from the ETRM metadata: the owner is IEM; the primary key is IEM_THEMES_PK on THEME_ID; a unique business key IEM_THEMES_U1 also exists on THEME_ID (and IEM_THEMES_UK covering CLASSIFICATION_ID and THEME in the earlier release). The documented physical schema in 12.2.2 contains 27 columns.

Key Information Stored

The most significant columns documented for IEM_THEMES include:

• THEME_ID — the surrogate primary key (IEM_THEMES_PK, with unique index IEM_THEMES_U1). This is the column users search for when joining other Email Center objects back to a specific theme; it is the canonical identifier to use in queries and API calls.
• CLASSIFICATION_ID — foreign key to IEM_CLASSIFICATIONS. This is the business-key candidate (with THEME, via IEM_THEMES_UK) that groups themes under the classification they belong to.
• THEME — the theme definition itself, the descriptive name/keyword content the Email Center matches against inbound messages.
• QUERY_RESPONSE — the response or query associated with the theme, used when the Email Center generates a reply or prompts for additional information.
• SCORE — the weight or score used to rank how well a message matches the theme during classification.
• DOC_COUNT — a count of documents (messages) associated with the theme, useful for volume reporting and rule tuning.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS; drives Multi-Org / data-security filtering so users see only themes within their authorized security group.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard who-audit columns maintained automatically on insert and update.
• ATTRIBUTE1 through ATTRIBUTE15 — the DFF/flexfield columns reserved for customer-specific descriptive data.

Common Use Cases and Queries

Typical usage centers on resolving classifications to themes, measuring theme activity, and auditing changes. A representative join pattern resolves a classification name to its themes:

• SELECT t.theme_id, t.theme, t.score, t.query_response FROM iem.iem_themes t WHERE t.classification_id = :classification_id ORDER BY t.theme;
• Join to the parent classification for context: SELECT t.theme_id, c.classification_name, t.theme FROM iem.iem_themes t, iem.iem_classifications c WHERE t.classification_id = c.classification_id AND t.security_group_id = :org_id;
• Volume reporting uses DOC_COUNT: SELECT theme, doc_count FROM iem.iem_themes ORDER BY doc_count DESC;
• Change auditing on the standard columns: SELECT theme_id, theme, last_updated_by, last_update_date FROM iem.iem_themes WHERE last_update_date > SYSDATE - 30;

Because SECURITY_GROUP_ID is present, all production queries should be filtered by the active security group if the caller is not operating with unrestricted privileges.

Related Objects

The FK relationships documented above identify the objects most tightly coupled to IEM_THEMES:

• IEM_CLASSIFICATIONS — joined via IEM_THEMES.CLASSIFICATION_ID; the parent object supplying classification-level metadata for each theme.
• FND_SECURITY_GROUPS — joined via IEM_THEMES.SECURITY_GROUP_ID; enforces data-security partitioning of theme rows.
• IEM_THEMES_PK / IEM_THEMES_U1 / IEM_THEMES_UK — the primary and unique indexes that enforce uniqueness on THEME_ID and on (CLASSIFICATION_ID, THEME).
• Email Center API / PL/SQL packages — the IEM Email Center rule and classification engine reads IEM_THEMES to score and respond to messages; themes are maintained through the Email Center administrator UI rather than by direct DML.
• Concurrent/rule-processing programs — background classification runs consume THEME, SCORE, and QUERY_RESPONSE and update DOC_COUNT as messages are processed.