Search Results okc_aqmsgstacks




Overview
Explore in AI Chat

Overview

OKC_AQMSGSTACKS is a table in the OKC (Contracts Core) schema of Oracle E-Business Suite, documented as VALID in both release 12.1.1 and 12.2.2. It stores the detail of the message stack associated with errors reported by processes that subscribe to an Oracle Advanced Queuing (AQ) queue within the Contracts application. When a subscribing process consumes a message and raises an exception, the diagnostic messages produced during that failure are persisted here, forming a child record to the parent error captured in OKC_AQERRORS. The table therefore functions as the low-level error-logging substrate for Contracts AQ integrations, enabling administrators and developers to reconstruct why a queued message could not be processed successfully.

The ETRM metadata assigns a heuristic Data Vault classification of satellite-leaning, derived from the foreign-key structure. This is offered as a modeling suggestion rather than a physical implementation: the table behaves as a descriptive satellite that captures the changing diagnostic context surrounding an error event. Its parent, OKC_AQERRORS, carries the durable parent key, while OKC_AQMSGSTACKS records the ordered, multi-row message detail attached to that parent.

Key Information Stored

The table is documented with eleven columns. Its composite primary key, implemented by the unique index OKC_AQMSGSTACKS_PK and repeated as the business-key candidate OKC_AQMSGSTACKS_U1, consists of AQE_ID and MSG_SEQ_NO. AQE_ID is the foreign key to OKC_AQERRORS and is the surrogate link to the parent error record; MSG_SEQ_NO sequences the individual messages within the stack. Together they distinguish every row despite the name "U1" indicating a uniqueness constraint over the same pair. The most significant remaining columns are:

  • AQE_ID — foreign key identifying the parent error in OKC_AQERRORS; part of the composite primary key.
  • MSG_SEQ_NO — ordinal position of the message within the stack; second component of the primary key.
  • MESSAGE_NAME — name of the error message raised by the AQ subscriber process.
  • MESSAGE_NUMBER — numeric message code associated with the raised error.
  • MESSAGE_TEXT — the human-readable text of the stack message, the primary diagnostic payload.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org / security-group data partitioning.
  • CREATED_BY, CREATION_DATE — standard WHO audit columns identifying who inserted the row and when.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns tracking the most recent modification.

Common Use Cases and Queries

The principal use case is post-mortem diagnosis of failed Contracts AQ subscriptions. Support engineers join the stack back to its parent error to obtain the complete failure narrative. A representative query is:

  • SELECT e.aqe_id, e.*, s.msg_seq_no, s.message_name, s.message_number, s.message_text FROM okc.okc_aqerrors e, okc.okc_aqmsgstacks s WHERE e.aqe_id = s.aqe_id ORDER BY e.aqe_id, s.msg_seq_no;
  • Locating a specific error: SELECT * FROM okc.okc_aqmsgstacks WHERE aqe_id = :p_aqe_id ORDER BY msg_seq_no;
  • Searching by message content: SELECT aqe_id, msg_seq_no, message_text FROM okc.okc_aqmsgstacks WHERE message_name = :p_name;
  • Filtering by operating unit security: ... WHERE security_group_id = :p_security_group_id;

Reporting scenarios include error-frequency trending by MESSAGE_NAME against CREATION_DATE, and re-processing decisions that depend on inspecting the ordered message stack before resubmitting a queue message. Because the audit columns are populated by the standard WHO mechanism, historical trend analysis is reliable.

Related Objects

The following objects are the most significant in the dependency graph for this table:

  • OKC_AQERRORS — parent table; joined on OKC_AQMSGSTACKS.AQE_ID = OKC_AQERRORS.AQE_ID. Holds the header error record for the failing AQ subscriber process.
  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; governs data visibility and security-group partitioning.
  • OKC_AQMSGSTACKS_PK / OKC_AQMSGSTACKS_U1 — the primary key constraint and its unique index over (AQE_ID, MSG_SEQ_NO).
  • OKC_AQ_* queue and subscriber objects — the Contracts AQ infrastructure whose subscribing processes generate the errors resolved through this table.
  • Contract-related API packages — the PL/SQL error handlers that write to OKC_AQERRORS and cascade detail rows into OKC_AQMSGSTACKS.

Queries should always constrain AQE_ID against OKC_AQERRORS to avoid widening scans on this satellite table.