Results for “alr_distribution_lists”

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

Overview

ALR_DISTRIBUTION_LISTS is a table owned by the ALR (Alert) schema in Oracle E-Business Suite. It stores the definition of distribution lists used by the Oracle Alert product to route alert messages and reports to named groups of recipients. A distribution list is a reusable set of addressees, defined at the application level, that can be attached to one or more alert actions so that notifications, printed output, and follow-up correspondence reach a consistent audience without re-entering recipient information for every alert.

In Oracle EBS 12.1.1 and 12.2.2 the table resides in the ALR product schema alongside the rest of the Alert Manager data model. It participates in the standard EBS multi-org and multi-language conventions through its APPLICATION_ID, SECURITY_GROUP_ID, and ZD_EDITION_NAME columns. From a data-modeling perspective, the metadata classifies the table as satellite-leaning under a heuristic Data Vault reading: it is a descriptive, change-tracked entity keyed by a business identifier whose owning application and security group are separate reference hubs. This classification is offered as a modeling suggestion rather than a documented Oracle design statement.

Key Information Stored

The physical structure documented for release 12.2.2 contains nineteen columns. The most significant for functional and reporting purposes are listed below.

• APPLICATION_ID — the FND_APPLICATION under which the distribution list is defined; this is the owning application and the leading column of both the primary key and the unique keys.
• LIST_ID — the internal identifier of the distribution list within the application.
• NAME — the user-visible name of the distribution list; it is the business-key candidate carried by the unique index ALR_DISTRIBUTION_LISTS_U2 together with APPLICATION_ID and END_DATE_ACTIVE.
• DESCRIPTION — free-text explanation of the list's purpose or membership.
• TO_RECIPIENTS, CC_RECIPIENTS, BCC_RECIPIENTS — the e-mail address strings for the primary, copy, and blind-copy recipients of electronically delivered alerts.
• PRINT_RECIPIENTS, PRINTER — the recipients and the target device for printed distribution; PRINTER references FND_PRINTER.
• REPLY_TO — the reply address presented to recipients of the alert message.
• ENABLED_FLAG — indicates whether the list is currently active for use.
• END_DATE_ACTIVE — the effective end date of the list record; it is part of both unique keys and the primary key, giving the table its date-effective, multi-version character.
• SECURITY_GROUP_ID — the security group that governs access to the row, referencing FND_SECURITY_GROUPS.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit columns recorded on every insert and update.
• ZD_EDITION_NAME — the editioning column used by EBS 12.2 online patching to isolate rows across edition cycles.

The surrogate primary key is ALR_DISTRIBUTION_LISTS_PK, defined over APPLICATION_ID, LIST_ID, and END_DATE_ACTIVE. The four-column unique indexes ALR_DISTRIBUTION_LISTS_U1 (APPLICATION_ID, LIST_ID, END_DATE_ACTIVE, ZD_EDITION_NAME) and ALR_DISTRIBUTION_LISTS_U2 (APPLICATION_ID, NAME, END_DATE_ACTIVE, ZD_EDITION_NAME) are the documented business-key candidates, with U2 enforcing uniqueness of list names within an application and effective date.

Common Use Cases and Queries

Typical uses of the table are: identifying which alerts deliver to a given list, validating recipient address assignments before enabling an alert, auditing list membership or print routing, and reporting on the set of active lists by application.

SELECT l.application_id,
       l.list_id,
       l.name,
       l.to_recipients,
       l.cc_recipients,
       l.print_recipients,
       l.printer,
       l.enabled_flag
FROM   alr.alr_distribution_lists l
WHERE  l.enabled_flag = 'Y'
AND    (l.end_date_active IS NULL OR l.end_date_active > SYSDATE)
ORDER  BY l.application_id, l.name;

Linking a list to the actions that consume it, using the documented join columns, shows the downstream alerts that depend on the list.

SELECT a.action_id,
       a.alert_id,
       l.name AS list_name,
       l.to_recipients
FROM   alr.alr_actions a,
       alr.alr_distribution_lists l
WHERE  a.list_application_id = l.application_id
AND    a.list_id             = l.list_id
AND    a.end_date_active     = l.end_date_active;

Because the keys include END_DATE_ACTIVE, any point-in-time query against historical recipients should constrain that column explicitly rather than relying on the current row only.

Related Objects

• ALR_ACTIONS — references this table via LIST_APPLICATION_ID, LIST_ID, and END_DATE_ACTIVE; each alert action may point at one distribution list, making ALR_ACTIONS the principal dependent object.
• FND_APPLICATION — reference table for APPLICATION_ID; identifies the product that owns the list.
• FND_PRINTER — reference table for PRINTER; supplies the print destination of the list.
• FND_SECURITY_GROUPS — reference table for SECURITY_GROUP_ID; governs row-level access.
• ALR_ALERTS — the alert definition reached through ALR_ACTIONS; useful when tracing which alerts ultimately consume a list.
• FND_USER — join target for CREATED_BY and LAST_UPDATED_BY when auditing who maintained list definitions.

Changes to distribution list rows should be performed through the Alert Manager forms and concurrent programs rather than direct DML, so that the audit columns, security group, and editioning values remain consistent with the EBS data model.