Results for “alr_alerts”

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

Overview

ALR_ALERTS is the master definition table for the Oracle Alert (ALR) product in Oracle E-Business Suite 12.1.1 and 12.2.2. Each row represents a single alert defined against an application table, capturing the alert's identity, the source table it monitors, its periodic or event-driven check schedule, and its active date range. Because Oracle Alert underpins much of the notification and exception-reporting infrastructure in EBS, ALR_ALERTS functions as the central registry from which all alert execution, response, action, and history activity originates.

The table resides in the ALR schema and is documented as VALID in ETRM. It contains 35 columns and is identified by the primary key ALR_ALERTS_PK on (APPLICATION_ID, ALERT_ID). From a Data Vault modeling perspective, the heuristic classification of this object is hub: it holds the durable business key (APPLICATION_ID, ALERT_ID) around which a large number of dependent ALR tables form satellite-style relationships, and it references two FND tables as its own foreign keys.

Key Information Stored

The most significant columns cluster into four groups:

• Identity and keys: ALERT_ID and APPLICATION_ID form the composite primary key and the principal business key. ALERT_NAME is the human-readable identifier and, together with APPLICATION_ID, is enforced by unique index ALR_ALERTS_U1/U2. ZD_EDITION_NAME supports the editioning framework in 12.2.2.
• Source table binding: TABLE_ID and TABLE_APPLICATION_ID point back to FND_TABLES, identifying the application table the alert monitors. TABLE_NAME provides a denormalized name for reporting.
• Condition and action logic: ALERT_CONDITION_TYPE distinguishes event-driven versus periodic alerts. SQL_STATEMENT_TEXT holds the actual SQL or PL/SQL condition executed to detect qualifying rows. INSERT_FLAG, UPDATE_FLAG, and DELETE_FLAG indicate which DML events the alert reacts to.
• Schedule and lifecycle: FREQUENCY_TYPE, WEEKLY_CHECK_DAY, MONTHLY_CHECK_DAY_NUM, DAYS_BETWEEN_CHECKS, SECONDS_BETWEEN_CHECKS, CHECK_TIME, CHECK_START_TIME, CHECK_END_TIME, CHECK_BEGIN_DATE, CHECK_ONCE_DAILY_FLAG, and ONE_TIME_ONLY_FLAG govern when the alert runs. ENABLED_FLAG, START_DATE_ACTIVE, and END_DATE_ACTIVE control whether the alert is currently active. DATE_LAST_CHECKED records the last execution timestamp. MAINTAIN_HISTORY_DAYS controls retention of ALR_ACTION_HISTORY.
• Audit and security: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and SECURITY_GROUP_ID provide standard EBS auditing and Oracle Applications-level data security.

Common Use Cases and Queries

Typical reporting and administration tasks include inventorying alerts by source table, auditing inactive or never-run alerts, and identifying alerts that reference specific DML operations. Examples:

• List all active alerts for a given application: SELECT alert_id, alert_name, table_name FROM alr.alr_alerts WHERE application_id = :app_id AND enabled_flag = 'Y';
• Find alerts monitoring a particular table: SELECT a.alert_name, t.table_name FROM alr.alr_alerts a, fnd.fnd_tables t WHERE a.table_application_id = t.application_id AND a.table_id = t.table_id AND t.table_name = 'PO_HEADERS';
• Identify alerts that have never been checked: SELECT alert_name FROM alr.alr_alerts WHERE date_last_checked IS NULL AND enabled_flag = 'Y';
• Audit schedule configuration by frequency: group alerts by FREQUENCY_TYPE and compare against CHECK_ONCE_DAILY_FLAG and ONE_TIME_ONLY_FLAG to detect overlapping periodic jobs.
• Trace alert activity by joining to ALR_ACTION_HISTORY or ALR_ACTION_HISTORY on (APPLICATION_ID, ALERT_ID) to see which alerts produced the most notifications over a period.

Related Objects

ALR_ALERTS is a hub with extensive downstream dependencies. Its own foreign keys reference FND_TABLES (via TABLE_APPLICATION_ID, TABLE_ID) and FND_SECURITY_GROUPS (via SECURITY_GROUP_ID). The most significant child tables that carry FK columns back to ALR_ALERTS(APPLICATION_ID, ALERT_ID) include:

• ALR_ACTIONS and ALR_ACTION_SETS — define and group the actions an alert triggers.
• ALR_ACTIONS.HISTORY (ALR_ACTION_HISTORY) and ALR_RESPONSE_ACTION_HISTORY — execution history and response audit trails.
• ALR_ALERT_CHECKS and ALR_ALERT_INPUTS — validation and parameter definitions for the alert condition.
• ALR_ALERT_INSTALLATIONS — tracks which installations have the alert deployed.
• ALR_VALID_RESPONSES and ALR_ACTUAL_RESPONSES — allowed and recorded user responses.
• ALR_ALERT_OUTPUTS and ALR_ACTION_OUTPUTS — output formatting and routing definitions.
• ALR_RESPONSE_ACTIONS and ALR_RESPONSE_SETS — response-to-action linkage.
• ALR_PERIODIC_SET_MEMBERS — links periodic alert sets through CHILD_APPLICATION_ID and CHILD_ALERT_ID.

Together these relationships make ALR_ALERTS the authoritative entry point for querying any aspect of Oracle Alert configuration, execution, or response handling in an EBS 12.1.1 or 12.2.2 environment.