Search Results cs_forum_user_msgs




Overview
Explore in AI Chat

Overview

CS_FORUM_USER_MSGS is a Service (CS) module table in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. Its documented purpose is to store message identifiers that have been read by a particular user. In practice, this table functions as the read-state tracking mechanism for the Oracle Service discussion forum feature. When a user opens or views a forum message, an entry is recorded here so the application can distinguish read from unread content, suppress "new message" indicators, and drive per-user notification or digest logic.

Under a heuristic Data Vault classification derived from its foreign key structure, CS_FORUM_USER_MSGS is best modeled as a link table. It resolves a many-to-many association between forum users and forum messages, with MSG_TYPE carried as a discriminating attribute of that association. Each row represents the intersection of one user, one message type, and one message.

Key Information Stored

The physical schema documents 25 columns, of which the following are the most significant for functional and integration purposes:

  • USER_ID — The forum user whose read state is being recorded. Foreign key to FND_USER.
  • MESSAGE_ID — The forum message that has been read. Foreign key to CS_FORUM_MESSAGES_B.
  • MSG_TYPE — The message category or type discriminator, which forms part of the primary key and prevents collisions where identifiers overlap across message classes.
  • SECURITY_GROUP_ID — The security group under which the row was created; foreign key to FND_SECURITY_GROUPS. Supports multi-organization and data security filtering.
  • CREATION_DATE, CREATED_BY — Standard audit columns recording when and by whom the read record was inserted.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit trail columns maintained by the EBS concurrent and form layers.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The DFF (Descriptive Flexfield) segment set reserved for customer-specific extensions.

The surrogate primary key is CS_FORUM_USER_MSGS_PK, defined over (USER_ID, MSG_TYPE, MESSAGE_ID). A second unique index, CS_FORUM_USER_MSGS_U1, is defined over the identical column list, making (USER_ID, MSG_TYPE, MESSAGE_ID) the de facto business key. There is no separate single-column surrogate; the composite acts as both the technical and business identifier.

Common Use Cases and Queries

The principal use case is determining unread forum messages for a user. This is expressed as a negative-match query against the read-state table:

SELECT m.message_id, m.msg_type
FROM   cs_forum_messages_b m
WHERE  NOT EXISTS (
         SELECT 1 FROM cs_forum_user_msgs u
         WHERE  u.message_id = m.message_id
         AND    u.msg_type   = m.msg_type
         AND    u.user_id    = :p_user_id);

A second common pattern is a read-activity report per user, joined to FND_USER for the user name:

SELECT fu.user_name, COUNT(*) read_count
FROM   cs_forum_user_msgs u, fnd_user fu
WHERE  u.user_id = fu.user_id
GROUP BY fu.user_name;

Reporting use cases include forum engagement metrics (messages read per user, per period, per MSG_TYPE), audit of who has viewed sensitive threads, and purging of stale read-state rows by CREATION_DATE. Because every read generates a row, volume grows quickly and the composite unique key is critical for preventing duplicate inserts during concurrent access.

Related Objects

  • FND_USER — Joined on CS_FORUM_USER_MSGS.USER_ID = FND_USER.USER_ID to resolve the reader identity.
  • CS_FORUM_MESSAGES_B — Joined on CS_FORUM_USER_MSGS.MESSAGE_ID = CS_FORUM_MESSAGES_B.MESSAGE_ID to retrieve message content and metadata.
  • FND_SECURITY_GROUPS — Joined on CS_FORUM_USER_MSGS.SECURITY_GROUP_ID to enforce data security group scoping.
  • CS_FORUM_MESSAGES_TL — The translation table for forum messages, typically joined to CS_FORUM_MESSAGES_B for display purposes.
  • CS_FORUM_TOPICS_B — Parent topic table for messages; indirectly related through CS_FORUM_MESSAGES_B.
  • CS_FORUM_USER_PREF_KEYS / CS_FORUM_USER_PREFS — User-level forum preference tables that frequently accompany read-state logic in the same feature set.

No public PL/SQL API is documented in the ETRM metadata for this table; read-state maintenance is performed by the Service forum forms and concurrent processes that insert and delete rows directly under the composite key.