Search Results ota_chat_messages




Overview
Explore in AI Chat

Overview

OTA_CHAT_MESSAGES is a transactional table in the OTA (Learning Management) schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual messages posted to a chat session by either internal learners (identified through PERSON_ID) or external learners (identified through CONTACT_ID). Each row represents a single chat message, capturing the sender, the recipient target, and the message body text. Because the chat header resides in a separate parent table, OTA_CHAT_MESSAGES functions as a child store of conversational detail.

From a dimensional modeling perspective, the metadata classifies this object as satellite-leaning. This is a heuristic suggestion rather than a documented Data Vault construct: the table carries a surrogate primary key, a foreign key to the chat header, descriptive attributes such as MESSAGE_TEXT, and standard EBS audit columns, which is the classic profile of a satellite attached to a hub or link. In EBS 12.2.2 the object is documented with 14 columns under the OTA schema and is reported as VALID.

Key Information Stored

The table's surrogate primary key is CHAT_MESSAGE_ID, enforced by the unique index OTA_CHAT_MESSAGES_PK. No other unique index is documented, so CHAT_MESSAGE_ID is the sole business-key candidate reported. The most significant columns are:

  • CHAT_MESSAGE_ID — Surrogate primary key; uniquely identifies each chat message.
  • CHAT_ID — Foreign key to OTA_CHATS_B, tying the message to its parent chat session.
  • BUSINESS_GROUP_ID — Multi-tenant / operating unit discriminator standard to HRMS-derived OTA tables.
  • PERSON_ID — The internal learner who authored the message.
  • CONTACT_ID — The external learner who authored the message when the poster is not an internal person.
  • TARGET_PERSON_ID — The internal recipient the message is addressed to.
  • TARGET_CONTACT_ID — The external recipient the message is addressed to.
  • MESSAGE_TEXT — The actual content of the posted chat message.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OTA framework for concurrent updates.
  • CREATED_BY, CREATION_DATE — Who created the message row and when, providing message timestamp context.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking the most recent change.

The pairing of PERSON_ID/CONTACT_ID and TARGET_PERSON_ID/TARGET_CONTACT_ID allows the model to represent both internal and external participants symmetrically within the same chat thread.

Common Use Cases and Queries

The primary use case is reporting and auditing chat activity associated with learning events. Typical queries range from retrieving all messages for a session to counting participation by learner.

Retrieving a full transcript for a given chat:

  • SELECT CHAT_MESSAGE_ID, PERSON_ID, CONTACT_ID, MESSAGE_TEXT, CREATION_DATE FROM OTA.OTA_CHAT_MESSAGES WHERE CHAT_ID = :p_chat_id ORDER BY CREATION_DATE;

Counting messages per internal author across a chat:

  • SELECT PERSON_ID, COUNT(*) FROM OTA.OTA_CHAT_MESSAGES WHERE CHAT_ID = :p_chat_id AND PERSON_ID IS NOT NULL GROUP BY PERSON_ID;

Reporting is often joined to OTA_CHATS_B to bring in session context such as the owning learning object. Analysts also use the four actor columns to distinguish internal-only, external-only, and mixed conversations, and CREATION_DATE to reconstruct timeline activity for compliance or dispute review.

Related Objects

The documented referential relationship is the foreign key from OTA_CHAT_MESSAGES.CHAT_ID to OTA_CHATS_B. The most significant related objects are:

  • OTA_CHATS_B — Parent chat header; joined on CHAT_ID = OTA_CHATS_B.CHAT_ID.
  • OTA_CHATS_TL — Translated chat header attributes, joined via CHAT_ID for language-specific descriptions.
  • PER_PEOPLE_F — Resolves PERSON_ID and TARGET_PERSON_ID to internal learner names.
  • HZ_PARTIES / HZ_PERSON_PROFILES — Resolve CONTACT_ID and TARGET_CONTACT_ID to external learner identities.
  • OTA_LEARNERS — Learner enrollment context for internal participants.
  • OTA_DELEGATES — Delegate records where chat participants are enrolled on behalf of others.
  • FND_USER — Correlates CREATED_BY and LAST_UPDATED_BY to application users for audit reporting.

Direct access should be read-only; message creation and maintenance are performed through the OTA Learning Management application tier rather than by direct DML.