Results for “alr_response_variables”

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

Overview

The ALR.ALR_RESPONSE_VARIABLES table is a core configuration object within the Oracle Alert (ALR) module of Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. It stores the definition of individual response variables that belong to a response set associated with an Oracle Alert. When an alert fires, the response set determines the action taken — for example, sending an electronic mail message, invoking a concurrent program, or running a SQL script — and each of those actions may reference variables whose values are supplied at runtime. ALR_RESPONSE_VARIABLES is the metadata repository that defines those variables: their names, types, data types, default values, and the maximum length of detail returned.

Because the table is entirely a child of alert response set definitions, it holds no transaction or event data. Its rows are configuration rows maintained through the Alert Manager responsibility or the Define Alert forms. Applying the heuristic Data Vault classification supplied in the metadata, this object is best modeled as a link: it sits between the alert header and the response set, resolving the many-to-many context (application, alert, response set) into a set of named variable definitions. It is not a hub (it has no independent business identity outside its parent) and not a satellite in the strict sense, since it carries its own descriptive attributes rather than simply tracking history of another key.

Key Information Stored

The table is defined with seventeen documented columns in 12.2.2. The most significant attributes are:

• APPLICATION_ID — identifies the application that owns the alert definition; participates in both the primary key and the foreign keys.
• ALERT_ID — the alert to which the response set belongs.
• RESPONSE_SET_ID — the specific response set (a single alert may own several).
• VARIABLE_NUMBER — the ordinal position of the variable within the response set.
• NAME — the token name used inside the response action text, typically referenced as &NAME.
• TYPE — classifies the variable (for example input, output, or literal substitution).
• DATA_TYPE — the declared SQL-style data type of the value.
• DEFAULT_VALUE — the value substituted when the alert action does not explicitly supply one.
• DETAIL_MAX_LEN — maximum length permitted for the returned detail, used to size substitution output.
• DESCRIPTION — free-text explanation of the variable's purpose.
• SECURITY_GROUP_ID — the operating unit / security group context, with a foreign key to FND_SECURITY_GROUPS.

The surrogate primary key is ALR_RESPONSE_VARIABLES_PK, defined over (APPLICATION_ID, ALERT_ID, RESPONSE_SET_ID, VARIABLE_NUMBER). Two business-key candidates are documented as unique indexes: ALR_RESPONSE_VARIABLES_U1 on (APPLICATION_ID, RESPONSE_SET_ID, VARIABLE_NUMBER, ZD_EDITION_NAME) and ALR_RESPONSE_VARIABLES_U2 on (APPLICATION_ID, RESPONSE_SET_ID, NAME, ZD_EDITION_NAME). The presence of ZD_EDITION_NAME confirms the table participates in the Edition-Based Redefinition (EBR) model introduced with 12.2, so online patching can create editioned copies of variable definitions.

Common Use Cases and Queries

Typical usage is diagnostic and reporting-oriented. Administrators troubleshooting why an alert produced unexpected output will inspect the variable definitions to confirm the NAME and DEFAULT_VALUE match the tokens embedded in the response action.

• Inventory variables for a given alert: SELECT variable_number, name, data_type, default_value FROM alr.alr_response_variables WHERE application_id = :app AND alert_id = :alert_id AND response_set_id = :set_id ORDER BY variable_number;
• Find every alert that defines a particular token name: SELECT application_id, alert_id, response_set_id FROM alr.alr_response_variables WHERE name = :name;
• Audit recent configuration changes: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to identify who altered a variable and when.
• Data-lineage reports: join to ALR_RESPONSE_SETS and ALR_ALERTS to produce a catalogue of alert actions and the variables each action consumes, useful for migration or upgrade impact analysis.

In 12.2 environments, queries should be cautious of editioned rows; the standard EBR views resolve the correct edition, but direct table queries may return multiple ZD_EDITION_NAME values.

Related Objects

The following objects are the most significant dependencies:

• ALR_ALERTS — joined on APPLICATION_ID and ALERT_ID; supplies the alert header that owns the variables.
• ALR_RESPONSE_SETS — joined on APPLICATION_ID, ALERT_ID and RESPONSE_SET_ID; the immediate parent defining the response action.
• ALR_ACTION_HISTORY / ALR_HISTORY — records of alert executions where variable values are substituted, providing the runtime counterpart to these definitions.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID to enforce operating unit security.
• FND_APPLICATION — resolves APPLICATION_ID to an application short name and description for reporting.
• ALR_RESPONSE_VARIABLES_PK / _U1 / _U2 — the indexes that enforce uniqueness and drive efficient lookup by alert, response set, or variable name.

Together these objects form the configuration backbone of Oracle Alert responses, with ALR_RESPONSE_VARIABLES acting as the link that binds alert context to the substitutable tokens used at execution time.