Results for “alr_message_systems”

43 results




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

Overview

The ALR_MESSAGE_SYSTEMS table is a core configuration object within the Oracle Alert (ALR) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of electronic mail systems that Oracle Alert uses to deliver alert messages, exception notifications, and recurring action outputs to recipients. Each row describes a named mail transport, together with the operating system command, arguments, and enablement status that determines how the mail is actually dispatched.

The table is owned by the ALR schema and is documented as VALID. Its documented physical schema in ETRM 12.2.2 consists of 12 columns, with a primary key constraint, ALR_MESSAGE_SYSTEMS_PK, defined on the NAME column. A unique business-key index, ALR_MESSAGE_SYSTEMS_U1, covers NAME and ZD_EDITION_NAME. In the mined Data Vault classification provided in the metadata, this table is described as standalone. As a modeling suggestion, this implies the table behaves as a reference or lookup construct rather than a transactional hub or link, and therefore does not naturally decompose into satellite structures. A single foreign key is documented: SECURITY_GROUP_ID references FND_SECURITY_GROUPS, placing the table within the standard Oracle Applications security group model.

Key Information Stored

The most significant columns documented for ALR_MESSAGE_SYSTEMS are:

  • NAME — the primary key of the table (via ALR_MESSAGE_SYSTEMS_PK) and the human-readable identifier of the mail system, for example a name such as "MAILX" or a site-defined transport label.
  • CODE — a short internal code used to identify the message system programmatically.
  • COMMAND — the operating system command invoked to submit a mail message, such as a sendmail binary or a custom mail transport utility.
  • ARGUMENTS — the command-line token string passed to COMMAND, typically including placeholders for the recipient, subject, and body supplied by Oracle Alert at runtime.
  • ENABLED_FLAG — controls whether the message system may be selected by Oracle Alert when processing alert actions.
  • SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS that scopes the row to a security group.
  • ZD_EDITION_NAME — the edition identifier used by the Oracle EBS online patching (editioning) infrastructure; combined with NAME it forms the unique business-key index ALR_MESSAGE_SYSTEMS_U1.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard Oracle Applications WHO columns that audit row creation and modification.

The surrogate primary key is NAME, while the unique business-key candidate is the composite of NAME and ZD_EDITION_NAME. The remaining WHO and audit columns provide tracing rather than identity.

Common Use Cases and Queries

The primary scenario for this table is validating and auditing Oracle Alert mail transport configuration. Administrators and support analysts query it to confirm that an enabled message system exists before alert actions are expected to deliver mail, and to verify that COMMAND and ARGUMENTS are correctly formed after a clone or upgrade.

A representative query to list enabled message systems is:

  • SELECT name, code, command, arguments FROM alr.alr_message_systems WHERE enabled_flag = 'Y' ORDER BY name;

A second pattern joins the security group to identify which group owns each transport:

  • SELECT m.name, m.code, s.security_group_id FROM alr.alr_message_systems m, fnd.fnd_security_groups s WHERE m.security_group_id = s.security_group_id;

Reporting use cases include configuration baselines between environments, patching or edition comparisons using ZD_EDITION_NAME, and troubleshooting alerts that fail to send because no enabled message system matches the expected code. Auditing LAST_UPDATE_DATE and LAST_UPDATED_BY supports change tracking after implementation or DBA activity.

Related Objects

The documented foreign key establishes the following dependency:

  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID, linking each mail system row to a security group definition.

Beyond the documented relationship, the ALR schema contains the alert definition, action, and input objects that depend functionally on message systems when dispatching notifications, and Oracle Alert's concurrent programs and mailer routines read this table at runtime. Because the metadata classifies the object as standalone with a single documented foreign key, the table should be treated primarily as a reference source rather than a heavily referenced parent. The composite unique index ALR_MESSAGE_SYSTEMS_U1 (NAME, ZD_EDITION_NAME) and the primary key ALR_MESSAGE_SYSTEMS_PK (NAME) are the principal access paths for queries that resolve a mail system by name or by edition.