Search Results exception_sequence

Overview

The APPS.ALR_CHECK_ACTION_HISTORY_VIEW is a reporting view within the Oracle E-Business Suite Alert (ALR) module that consolidates the complete historical record of alert check executions and the actions they triggered. In Oracle Alerts, a periodic database check evaluates a defined condition against application data; when the condition is met, an action set executes, producing one or more actions and output values. This view joins those separate pieces of the alert engine — check history, action set check history, action history, action data, and output history — into a single denormalized result set, allowing administrators and reporting tools to reconstruct exactly what occurred during each alert evaluation.

Because the view carries the ALERT_CHECK_ID identifier (the key surfaced in the user's search) alongside ALERT_ID and APPLICATION_ID, it serves as the primary diagnostic surface for tracing a specific check run. It is typically consumed for operational monitoring, historical auditing of alert behavior, and integration into custom BI Publisher reports or SQL-based extracts. The view is owned by the APPS schema and holds VALID status in both 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over five documented base objects, accessed through APPS synonyms:

• ALR_ALERT_CHECKS (AC) — the driving table; supplies APPLICATION_ID, ALERT_ID, ALERT_CHECK_ID, check date, purge date, and the alert-level success flag.
• ALR_ACTION_SET_CHECKS (SC) — one row per action set check; supplies CHECK_ID, ACTION_SET_ID, ORACLE_ID, exception counts, and action-set success flag.
• ALR_ACTION_HISTORY (AH) — records the individual actions executed, including member, action ID, version, level, and executed flag.
• ALR_ACTION_DATA (AD) — captures per-exception row data, including ROW_NUMBER and DUPLICATE_FLAG.
• ALR_OUTPUT_HISTORY (OH) — provides the output name, data type, and value produced by each executed action.

ALR_ALERT_CHECKS is joined to ALR_ACTION_SET_CHECKS on the composite key (APPLICATION_ID, ALERT_ID, ALERT_CHECK_ID). The remaining tables are outer-joined (indicated by the (+) operators), so a check that produced no actions or no exceptions still appears in the result set. The final join condition, NVL(AD.ROW_NUMBER,-1) = NVL(OH.ROW_NUMBER,-1), aligns output values with their corresponding exception row without dropping NULL row numbers.

Key Columns

• APPLICATION_ID, ALERT_ID, ALERT_CHECK_ID — the composite identity of an alert check run.
• ALERT_CHECK_DATE — sourced from LAST_UPDATE_DATE; the effective timestamp of the check execution.
• HISTORY_PURGE_DATE — when the history row becomes eligible for purge.
• ALERT_CHECK_SUCCESS_FLAG — whether the alert check completed successfully.
• ACTION_SET_CHECK_ID, ACTION_SET_ID, ORACLE_ID — identity of the associated action set check.
• NUMBER_OF_EXCEPTIONS, NUMBER_OF_ACTIONS_PERFORMED — counts of exception rows and executed actions.
• ACTION_SET_CHECK_SUCCESS_FLAG, ACTION_EXECUTED_FLAG — success/execution indicators for the action set and each action.
• ACTION_SET_MEMBER_ID, ACTION_ID, ACTION_VERSION_NUMBER, ACTION_LEVEL — metadata describing each executed action.
• EXCEPTION_SEQUENCE, DUPLICATE_FLAG — ordering and duplicate detection for exception rows.
• OUTPUT_NAME, OUTPUT_DATA_TYPE, OUTPUT_VALUE — the captured output of each action.

Common Use Cases and Queries

A frequent requirement is to retrieve every historical execution of a particular alert check, which is precisely the scenario implied by the search term alert_check_id:

• Trace a single check run:

  SELECT alert_check_id, alert_check_date, alert_check_success_flag,
         number_of_exceptions, number_of_actions_performed
  FROM   apps.alr_check_action_history_view
  WHERE  alert_check_id = :p_check_id;

• Report all action executions for an alert within a date range:

  SELECT alert_id, alert_check_date, action_id, output_name, output_value
  FROM   apps.alr_check_action_history_view
  WHERE  alert_id = :p_alert_id
  AND    alert_check_date BETWEEN :p_from AND :p_to
  ORDER  BY alert_check_date, exception_sequence;

• Identify failing checks and their executed actions:

SELECT alert_check_id, action_set_check_success_flag,
       action_id, action_executed_flag
FROM   apps.alr_check_action_history_view
WHERE  alert_check_success_flag = 'N';

Because the view is read-only and denormalized, it is well suited to ad-hoc diagnostics, purge validation, and downstream ETL extracts without risking the underlying alert definition tables.