Alerts in Oracle Applications

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.