Presented By :

Presented To : HMC

Oracle AlertsM V RajeshSr. Technical Consultant

‘Alert Manager’ Responsibility

To Define a new alert.

To ‘Run’ an alert.

To choose the e-mail server.

What is an Alert?

• Oracle Alert is a complete Exception Control and Reporting System.

• Oracle Alert facilitates the flow of information within your organization by letting you create entities called Alerts to monitor your business information and to notify you of the information you want.

M V Rajesh

Alerts are of TWO types:

• Event.

• Periodic

M V Rajesh

What’s the difference?

• Event Alert: – Notifies immediately as

the EVENT occurs.– What we need to

specify? • The event to be

monitored. • The selection

criteria; i.e., the SQL statement.

• The action to be taken.

• Periodic Alert: – Checks the database as

per the schedule we define.

– What we need to specify?

• The Frequency. • The selection

criteria; i.e., the SQL statement.

• The action to be taken.

What’s an Event?

• An event can be Insert/Update on any particular record(s) of the database.

• An Event Alert is similar to any database trigger. The only difference is in the choice of ‘Actions’ provided. In a trigger, this action can only be to Insert/Update/Delete any records from the database; that is, running an SQL statement script.

What can the “Action” be?

Oracle Alert can be configured to perform any or all of the following functions:

• Sending the retrieved information to someone in a (reasonably formatted) e-mail message .

• Invocation of a – Concurrent Program, – Operating Script,– SQL Statement Script.

M V Rajesh

Other important features:

• Keep us informed of exception conditions through a single point; our electronic mail.

• Take predefined actions when it finds exceptions in our database, without user intervention.

• Take the actions we specify, depending upon our response to an alert message.

M V Rajesh

Other important features: contd

• Perform routine database tasks automatically, according to the schedule we define.

• Be fully integrated with our e-mail system: it is configurable with Oracle Office, Unix mail, Windows NT mail, and VMS mail.

• Allows definition of distribution list, so that one can avoid typing the same set of recipients for many messages.

Other important features: contd

• Alert can send messages or perform predefined actions in an action set when important events occur.

• Alerts can be defined to any of the Oracle Applications; viz., Oracle Financials, Oracle Manufacturing, Oracle Human Resources, or Oracle Public Sector Financials application as well as any custom Oracle application.

M V Rajesh

Other Important Features: contd

• Easy to define.

• Easy customization (of Frequency, Actions, etc.)

• Actions for singular exception, a combination of exceptions, or no-exception condition.

Other important features: contd

• Alert History is maintained. • Action Escalation: e.g., if an alert has to be

defined to send mails to for credit card defaulters. The severity of the mails can be made to vary depending on the period for which the defaulter has defaulted. This means that different Actions can be taken based on practically any parameter. In this example, the parameter was(sysdate-last_date_for_payment).

M V Rajesh

Two types of Actions:

• Detail Action: Individual action is

taken for each exception.

E.g., Mails are sent to each defaulter individually.

• Summary Action: Action is taken

collectively per type of exception.

E.g., A common mail is sent to a set of defaulters.

Oracle Alert automatically determines whether to perform detail actions or summary action depending on the number of exceptions.

Summary Threshold:

• Oracle can automatically determine whether to perform a detail or summary action, depending upon the number of exceptions found by the alert Select statement.

• If we define a summary threshold, Oracle Alert performs a detail action for each exception found by the Select statement, but if the number of exceptions found exceeds the summary threshold, Oracle Alert performs a summary action. You need to first define a detail and a summary action, include them in a threshold group, and then specify a summary threshold.

Other important features: contd

• Self-Referencing Alerts: (DATE_LAST_CHECKED):

An alert that checks the database for only those exceptions that are new since the last time the alert was checked –without saving history.

WHERE ...AND creation_date > TO_DATE(:DATE_LAST_CHECKED,’DD–MON–YYYY HH24:MI:SS’)

Response Processing

• Oracle Alert can process responses to the alert messages. When Oracle Alert receives a response to a specific alert message, it automatically performs the actions you define. • Optionally, respondents can supply values that Oracle Alert uses to perform these actions.• Response processing lets you automate routine user–entry transactions, streamlining your organization’s operations.• We must have a Windows NT MAPI–compliant mailserver and define at least one mailbox in this mail application if we want to use response processing.

M V Rajesh

The Periodic Alert Scheduler

• The Periodic Alert Scheduler (ALEPPE) is a concurrent program that automatically checks the scheduled periodic alerts.• The Periodic Alert Scheduler runs at each day at 12 AM. When it runs, the Periodic Alert Scheduler looks for all periodic alerts scheduled to be checked that day, and then submits one concurrent request for eachscheduled check.

A sample Mail from Alert:

=== G1: New Item List ===

Org FD GropCode Global Item ProdCode Type Date Status

---- ---- -------- ------------------------- -------- ----- ------ ----------

04N X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE

43A X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE

43S X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE

46X X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE

49C X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE

580 X81 LZ3CB CDT-TEST-ITEM 123 FG 020529 ACTIVE

A01 X81 LZ3CB CDT-TEST-ITEM 123 FG 020529 ACTIVE

GLO X35 AA TEST-ORD 1234 FG 020529 INACTIVE

GLO X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE

GLO X85 C-ITEM FG 020528 ENGINEER

GLO D-ITEM FG 020528 ENGINEER

TSB X35 AA TEST-ORD 1234 FG 020529 INACTIVE

TSB X81 LZ3CB CDT-TEST-ITEM 123 FG 020528 ACTIVE