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:
Event
alert
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.
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.
No comments:
Post a Comment