Oracle Alert is your complete exception control solution. Alerts
signal important or unexpected activity in your database. They ensure that you
are regularly and quickly informed about critical database events instead of
sorting through length reports.
The basic function of alerts includes but not limited to
· Keep you informed
of critical activity in your database
· Deliver key
information from your applications, in the format you choose
· Provide you with
regular reports on your database information
· Automate system
maintenance, and routine online tasks
Alerts keep a constant check on your database information and
prompt you when the
specified criteria are met. You can use either an Oracle
application or a custom oracle
application to define alerts. However, few applications such as
purchasing, allow you to
simply activate and use alerts supply by default.
You can define two types of alerts:
1. Event alert
2. Periodic alert.
An EVENT ALERT immediately notifies you of activity in your
database as it occurs. When you
create an event alert, you specify the following:
· A database event
that you want to monitor, that is, an insert or an update to a specific
database table.
· A SQL Select
statement that retrieves specific database information as a result of the
database event.
· Actions that you
want Oracle Alert to perform as a result of the database event. An action can
entail sending someone an electronic mail message, running a concurrent
program, running an operating script, or running a SQL statement script. You
include all the actions you want Oracle Alert to perform, in an action set.
A periodic alert, on the other hand, checks the database for
information according to a
schedule you define. When you create a periodic alert, you
specify the following:
· A SQL Select
statement that retrieves specific database information.
· The frequency that
you want the periodic alert to run the SQL statement.
· Actions that you
want Oracle Alert to perform once it runs the SQL statement. An action can
entail sending the retrieved information to someone in an electronic mail
message, running a concurrent program, running an operating script, or running
a SQL statement script. You include all the actions you want Oracle Alert to
perform, in an action set
By creating event alerts, you can
have an immediate view of the activity in your
database, so you keep on top of
important or unusual events as they happen. By
creating periodic alerts, you can
have current measurements of staff and organization
performance, so you can zero in on
potential trouble spots. You can automate routine
transactions, preserving your
valuable time for more important issues. Oracle Alert gives
you the information you need online,
so you do not have to contend with a pile of
paperwork.
Workflow vs. Alerts:
Unlike alerts, workflow is defined in
a system to detect a condition and requires user
intervention. Every time a response
is not recorded during a designated time period,
workflow may send a notification to
the user's manager depending upon the workflow
definition. It is difficult to
accomplish such notification using alerts.
Defining Alerts:
You can create alerts that are as
simple or as complex as you need them to be, and you can tailor your alerts in
a variety of ways so they perform the kind of exception reporting your
organization needs. There are two types of alerts: event and periodic. Both
types are defined by a SQL Select statement that you specify.
You can create event alerts that
monitor your applications for the exception conditions you specify. You can
create periodic alerts that check your database for predefined conditions
according to the schedule you determine.
Major Features
Verify SQL
You can verify that your alert's SQL Select statement runs
correctly, and returns the data you specify. You can do this verification
directly in Oracle Alert immediately after you enter your Select statement -
you don't have to suspend your Oracle Alert session or navigate to SQL*Plus.
Specify Installations
You can specify which Application installations you want your
alert to run against, so you can control which Application installations your
alert checks in a database with multiple Application installations.
Information Routing
With Oracle Alert, you can include a file created by another
application as part of an alert message. You can also define an alert that
distributes an electronic copy of a report, log file, or any other ASCII file.
Dynamic Message Distributions
Oracle Alert lets you define a message distribution list without
knowing ahead of time who the actual individuals on the list will be. Oracle
Alert can retrieve the appropriate electronic mail IDs from your application
tables, and send the message automatically.
Duplicate Suppression
Oracle Alert can automatically determine which action to perform
based on whether it locates the same exceptions during a sequence of alert
checks. You can have Oracle Alert perform a different action during each alert
check that finds the same database exception.
[
Customizable Inputs by Action Set
You can further customize your alerts by specifying parameters
for each set of actions you define. These parameters, or inputs, provide you
with extra flexibility in creating your alerts because you can assign specific
values to them. For example, if a vendor delivery is overdue, Oracle Alert can
notify the purchasing agent when it is two days late and the purchasing manager
when it is seven days late. The number of days late is the input; two and seven
are distinct input values you assign for each type of recipient.
Distribution Lists
Oracle Alert lets you create an electronic distribution list
that you can use on many messages. At any time, you can add or remove names
from your lists, or you can make copies of your lists and use the copies to
create new lists.
Standard Message Address Formats
Oracle Alert lets you address messages using easily recognizable
symbols: to, cc, bcc, just as you would use when writing a memo or sending
electronic mail.
[[
Printed Alert Messages
Oracle Alert lets you send messages to people who do not use
electronic mail. You can direct a message to a printer with the recipient's
name on the burst page.
[
Electronic Mail Integration
Oracle Alert leverages the Workflow Notification Mailer to send
and receive alert e-mail messages. The notification mailer uses the Simple Mail
Transfer Protocol (SMTP) for outbound messages and the Internet Message Access
Protocol (IMAP) for inbound messages.
Creating a Periodic Alert:
These Alerts are triggered hourly, daily, weekly, monthly or
yearly based on your input.
To create a periodic alert, you perform the following tasks in
the order listed:
· Define your
periodic alert and specify its frequency.
· Specify the details
for your alert.
· Define actions for
your alert.
· Create action sets
containing the actions you want your alert to perform.
Before you define a periodic alert, make sure you do the
following:
· Configure the
Workflow Notification Mailer to send and receive e-mail messages according to
your alert requirements.
· Specify Oracle
Alert options to configure how Oracle Alert checks alerts and handles alert
messages.
Enter a SQL Select statement that retrieves all the data your
alert needs to perform the actions you plan to define.
Your periodic alert Select statement must include an INTO clause
that contains one output for each column selected by your Select statement.
Identify any inputs with a colon before the name, for example, :INPUT_NAME.
Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME. Do not use set operators in your
Select statement.
Tip: If you want to use an input value in an action for this
alert, select the input into an output. Then you can use the output when you
define actions for this alert.
When selecting number columns, Oracle Alert uses the number
formats defined in your database. Optionally, you can format your number
outputs as real numbers by specifying a SQL*Plus format mask in your Select
statement. For each number output, simply add a pound sign (#) and format mask
to your output name. For example, if you select purchase price into the output
&PRICE, add "#9999.99" after &PRICE for Oracle Alert to
display the value to two decimal places. Your number output looks like:
&PRICE#9999.99. Here is an example of a periodic alert Select statement
that looks for users who have not changed their passwords within the number of
days specified by the value in :THRESHOLD_DAYS.:
SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name
Note: Although Oracle
Alert does not support PL/SQL statements as the alert SQL statement definition,
you can create a PL/SQL packaged function that contains PL/SQL logic and enter
a SQL Select statement that calls that packaged function. For example, you can enter
a SQL Select statement that looks like:
SELECT package1.function1(:INPUT1, column1)
INTO &OUTPUT1
FROM table1
In this example, package1 is the name of the PL/SQL package and
function1 is the name of user-defined PL/SQL function stored in the package.
Creating an Event Alert:
These Alerts are fired/triggered based on some change in data in
the database.
To create an event alert, you perform the following tasks in the
order listed:
· Define the database
events that will trigger your alert
· Specify the details
for your alert.
· Define actions for
your alert.
· Create action sets
containing the actions you want your alert to perform
· This section
focuses on the first task of defining the database events that trigger your
event alert and divides the task into smaller sub-tasks.
Before you define an event alert, make sure you do the
following:
· Configure the
Workflow Notification Mailer to send and receive e-mail messages according to
your alert requirements.
· Specify Oracle
Alert options to configure how Oracle Alert checks alerts and handles alert
messages.
To specify an event table:
Specify the name of the application and the database table that
you want Oracle Alert to monitor.
Although the application you enter here need not be the same
application that owns the alert, both applications must reside in the same
Oracle database and the application that owns the alert has to have Select
privileges on the tables listed in the alert Select statement.
Important: You cannot use a view as the event table for your
alert.
Important: Do not define an event alert on the table
FND_CONCURRENT_REQUESTS. Oracle Alert submits a concurrent request to the
concurrent manager when an event alert is triggered by an insert or update to
an event table. For concurrent processing to occur, every submitted concurrent
request automatically gets inserted as a row in the
FND_CONCURRENT_REQUESTS table. If you define an event alert on
this table, you create a situation where the event alert will cause an
exception to occur recursively.
Although Oracle Alert does not support PL/SQL statements as the
alert SQL statement definition, you can create a PL/SQL packaged function that
contains PL/SQL logic and enter a SQL Select statement that calls that packaged
function. For example, you can enter a SQL Select statement that looks like:
SELECT package1.function1(:INPUT1, column1)
INTO &OUTPUT1
FROM table1
Specifying Alert Details
Once you define an event or periodic alert in the Alerts window,
you need to display to the Alert Details window to complete the alert
definition. The Alert Details window includes information such as which
Application installations you want the alert to run against, what default
values you want your inputs variables to use, and what additional
characteristics you want your output variables to have.
In the Inputs tabbed region, Oracle Alert automatically displays
the inputs used in your Select statement, unless they are the implicit inputs:
:ROWID, :MAILID, :ORG_ID and :DATE_LAST_CHECKED. The values of the implicit
inputs are as follows:
• ROWID-Contains the ID number of the row where the insert or
update that triggers an event alert occurs.
• MAILID-Contains the email username of the person who enters an
insert or updates that triggers an event alert.
• ORG_ID-Contains the organization ID that is selected when the
alert runs.
• DATE_LAST_CHECKED-Contains the date and time that the alert
was most recently checked
Major Features
Event Alerts
Event alerts immediately notify you of activity in your database
as it happens. You define what a database event is - an insert or an update to
a table - and Oracle Alert informs you when it happens. You can modify our
precoded alert conditions or simply create your own, and Oracle Alert will send
messages or perform predefined actions in
Periodic Alerts
Periodic alerts periodically report key information according to
a schedule you define.
You can modify our precoded alerts or simply create your own,
and Oracle Alert will send messages or perform predefined actions from an
action set according to the schedule you set.
You can define periodic alerts on any Oracle Financials, Oracle
Manufacturing, Oracle Human Resources, or Oracle Public Sector Financials
application as well as any custom Oracle application.
Periodic alerts can be set to run as often as you need during a
24-hour period, or they can be set to run once a month - the frequency is up to
you. Used over time, periodic alerts can provide a regular and reliable measure
of performance.
For example, you can define a periodic alert for Oracle
Purchasing that sends a message to the Purchasing Manager listing the number of
approved requisition lines that each purchasing agent placed on purchase
orders. You can define this alert to run weekly, and provide performance
measurement on a consistent and timely basis.
Easy Alert Definition
Oracle Alert can load the SQL statement for your alert
definition from an operating system file, allowing you to automatically perform
the functions you currently do by hand. Oracle Alert will also transfer your
entire alert definition across databases. You can instantly leverage the work
done in one area to all your systems.
Customizable Alert Frequency
With Oracle Alert, you can choose the frequency of each periodic
alert. You may want to check some alerts every day, some only once a month,
still others only when you explicitly request them. You have the flexibility to
monitor critical exceptions as frequently as necessary, even multiple times
during a 24-hour period. You can also check less significant exceptions on a
more infrequent schedule; for example, once a month.
Customizable Alert Actions
You can define a variety of actions for Oracle Alert to perform
based on the exceptions it finds in your database. Oracle Alert can send an
electronic mail message, run a SQL script or an operating system script, or
submit a concurrent request, or any combination of the above. You can create
your own message, SQL script, or operating system script actions in Oracle
Alert, or have Oracle Alert send messages or perform scripts that reside in
external files. Each action is fully customizable to the exceptions found in
your database, so you have complete flexibility in your exception management.
Detail or Summary Actions
You can choose to have Oracle Alert perform actions based on a
single exception or a combination of exceptions found in your database. You can
define a detail action such that Oracle Alert performs that action for each
individual exception found. You can also define a summary action such that
Oracle Alert performs that action once for each unique combination of
exceptions found. You decide which exceptions you want Oracle Alert to consider
as a unique combination. You can format a detail or summary message action to
display the exception(s) in an easy-to-read message.
No Exception Actions
Oracle Alert can perform actions if it finds no exceptions in
your database. You can define Oracle Alert to send electronic mail messages,
run SQL scripts or operating system scripts, or submit concurrent requests, or
any combination of the above.
Alert History
Oracle Alert can keep a record of the actions it takes and the
exceptions it finds in your database, for as many days as you specify. When you
ask Oracle Alert to reconstruct alert history you see a complete record of
alert activity exactly as it was performed. You can even review all responses
Oracle Alert received to your messages and the actions they invoked. Oracle
Alert also lets you decide which information you want to review.
You can narrow your review criteria so you see only the history
you specifically want to examine, without sorting through all the history
information available for an alert.
Duplicate Checking
Oracle Alert can search for exceptions that remain in your
database over time, and can take certain actions based on the presence of those
"duplicate exceptions." You can track exceptions in your database for
the length of time that you save history for your alerts.
Action Escalation
You can define a sequence of actions and have Oracle Alert
perform the next action in that sequence each time it finds the same exception
or exceptions in your database. For example, you can have Oracle Alert send
messages of increasing severity if it finds the same exceptions over a period
of time. Using action escalation, you can make sure that exceptions needing
attention don't languish unattended in your database.
Summary Threshold
Oracle Alert can automatically determine whether to perform a
detail or a summary action based on the number of exceptions it finds in your
database. If your alert locates few exceptions, it can simply perform detail
actions-one for each exception. If your alert locates many exceptions, it can
perform a summary action on all of those exceptions.
Oracle Alert automatically determines when it should perform a
detail or a summary action.
Response Processing
Oracle Alert can take certain predefined actions based on a
user's response to an alert message. The response can cause Oracle Alert to
send another alert message, run a SQL script or an operating system script, or
submit a concurrent request, or any combination of the above. Because Oracle
Alert performs response actions automatically, you can delegate routine user
transactions to Oracle Alert and thereby increase your organization's
efficiency.
Self-Referencing Alerts
You can create an alert that checks for exceptions that are new
in your database since the last time the alert was checked. The alert uses its
own DATE_LAST_CHECKED value as the start time for checking for new exceptions.
Customizable Options and User Profile
You can specify exactly how you want your Oracle Alert user
interface to look and behave. From choosing a printer to specifying the header
text in your Oracle Alert messages.
Electronic Mail Integration
Oracle Alert allows you to send alert e-mail messages through
your mail system using the Simple Mail Transfer Protocol (SMTP) for outbound
messages and the Internet Message Access Protocol (IMAP) for inbound messages.
Important Alert
Tables:
·
ALR_ALERTS
·
ALR_ACTIONS
·
ALR_ACTION_SETS
·
ALR_ACTION_SET_INPUTS
·
ALR_ACTION_SET_OUTPUTS
·
ALR_ACTION_SET_MEMBERS
·
ALR_ALERT_CHECKS
·
ALR_ALERT_INPUTS
·
ALR_ALERT_OUTPUTS
·
ALR_ACTION_SET_CHECKS
·
ALR_RESPONSE_SETS
·
ALR_RESPONSE_ACTIONS
·
ALR_VALID_RESONSES
Oracle
Alert uses the following internal views:
·
ALR_ALERT_ACTIONS_VIEW
·
ALR_ALERT_HISTORY_VIEW
·
ALR_CHECK_ACTION_HISTORY_VIEW
·
ALR_INSTALLATIONS_VIEW
·
ALR_PERIODIC_ALERTS_VIEW
·
ALR_RESPONSE_ACTIONS_VIEW
·
ALR_SCHEDULED_PROGRAMS
·
ALR_VARIABLES_AND_OUTPUTS
