Alerts are basically used automate the system
maintenance, provide reports in the format chosen, for database
activity/Business Requirement as it occurs..
few scenarios:
- A database event which was supposed to be monitored
for instance insert into some table(event based alert).
- A SQL statement which provides specific information
(event based alert).
- The frequency we want the report of a SQL
statement(periodic alert)
Responsibility Name: Alert Manager
Pre-requisites:
Navigation: Alert Manager --> System -->
Options
-- Electronic Account Mail setup
-- Oracle Alert Mailbox
Navigation: Alert Manager --> System -->
Installations
-- Setup if there are any CUSTOM application where
the Alert needs to be created.
Types of Alerts: Event based and Periodic
-- Both types of alerts are defined by SQL statement
specified in the SQL section.
Periodic Alert: Navigate to alert form and select the
periodic Alert option and then below tasks to be performed.
- Application Name : Application name that owns the
alert
- Alert Name : Name of the alert(up to 50 chars) with
some meaningful description(up to 240 chars) and check the enabled field.
- Frequency : Select the frequency of the alert,below
are the available options.
On Demand
On Day of the month
On Day of the week
Every N calendar Days
Every Day
Every Other Day
Every N Business Days
Every Business Day
Every Other Business Day
Also Start time and end time, number of times in 24
hours and end date if not required after some date.
- SQL : Enter a SQL Statement that retrieves all the
alert information, that can used for actions to be planned.The statement should
have INTO clause and one
output field.
Example: Input field :INPUT_NAME
Output field
&OUTPUT_NAME
Oracle Alert does not support pl/sql statements, but
we have a provision to create the database function and use in the SQL
statement.
Example: SELECT PACKAGE.FUNCTION_NAME(:INPUT_VALUE)
INTO &OUTPUT_VALUE FROM DUAL;
IMPORT Option: Rather than creating a SQL statement,
we can also import from file and can use in the application.
Event Alert: Navigate to alert form and select the
Event Alert option and then below tasks to be performed.
- Application Name : Application name that owns the
alert
- Alert Name : Name of the alert(up to 50 chars) with
some meaningful description(up to 240 chars) and check the enabled field
- Specify the event table name (application be
different that the table resides) but privileges should be present.
- Check the insert/update, when the alert has to be
fired.
- SQL : Enter a SQL Statement that retrieves all the
alert information, that can used for actions to be planned.The statement should
have INTO clause and one
output field.
Example: Input field :INPUT_NAME
Output field
&OUTPUT_NAME
Oracle Alert does not support pl/sql statements, but
we have a provision to create the database function and use in the SQL
statement.
Also make sure there is a condition based on the
event table in the SQL, by using :ROWID condition.
Example:
SELECT user_name INTO &NEWUSER FROM fnd_user
WHERE rowid = :ROWID;
The SQL statement should be verified, run then it
should be saved in the alert.
- Alert Details: Provide the input,output and
installation/user id details.
- Action: Create the action Name and click on the
action details.
Action type can be set as Message,SQL Statement
script and operating system script.
and provide the details for the message details like
To list and message data.
- Action sets: Once the alert actions are created,
those alert actions can be included in the action sets.
