Oracle Alert

What are Oracle Alerts?

Oracle Alerts monitor your Database information and notify you when the condition that you have specified is found. You can define Alerts in any Oracle application or custom Oracle application. Some applications (Purchasing, for example) supply Alerts that Can simply be activated and used. There are two type of Alerts, Event and Periodic.

Alerts are 2 types of alerts in oracle apps.
1. Periodic Alerts
2. Event based Alerts.

Event Based Alerts:

These Alerts are fired/triggered based on some change in data in the database.

Periodic Alert:
These Alerts are triggered hourly,daily, weekly, monthly or yearly based on your input.

EX: Employee Birthday can be any calendar day of the year. So we will tell oracle apps
to check daily once every calender day and see if today is employee's birthday and send email if true.

You need Alert Manager Responsibility to define a new Alert.
Navigate:
Alert Manager -> Alert -> Define

We need to write SQL which satisfies our condition and also to fetch required details.

SELECT global_name,
date_of_birth,
email_address into &emp_name,
&dob,
&emp_email
FROM per_all_people_f
WHERE trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');

Note:

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.
You can use PL/SQL functions in your Select statement to fetch complex business logic.

§ Enter the name of the application that will own the alert

§ Enter a suitable Name of the alert (up to 50 characters), and

give it a meaningful description (up to 240 characters).

§ Select a frequency for your periodic alert. You can choose from nine frequency options:

1.    On Demand

2.    On Day of the Month

3.    On Day of the Week

4.    Every N Calendar Days

5.    Every Day

6.    Every Other Day

7.    Every N Business Days

8.    Every Business Day

9.    Every Other Business Day

§ Choose ‘On Demand’ frequency when you are developing a periodic alert so that you can test your alert at any time you want. When you will sure that the alert is working fine, then you can change the frequency as per business need.

§ Depending on the frequency you choose in the previous step, the Start Time and End Time fields become enabled. You may also specify the number of times within a 24-hour period that Oracle Alert checks your alert.

§ Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.

§ Specify a value in the End Date field if you want to disable your alert by a certain date.

§ Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.

Check the SQL for syntax using "verify" button.

If any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occurred.

Next step is to define action if condition matches.
Don't forget to select action level of type "Detail".
This is because action should be performed once for every Alert Exception.

Then click on "Action Details" button and define the email message as shown below.

We also need "Action Sets" and attach the action which we just created.

Now we have to test this Alert:

Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]