What are Oracle Alerts? Oracle Alert is an exception control solution that Oracle provides us with to monitor a piece of specific business information and take appropriate actions accordingly. There are only two types of Oracle alerts in E-Business Suite ERP (Event Alert & Periodic Alert).

How you can leverage Oracle Alerts?

• Keep you informed of database exception conditions, as they occur.
• Take predefined actions when it finds exceptions in your database, without user intervention.
• Take the actions you specify, depending upon your response to an alert message.
• Perform routine database tasks automatically, according to the schedule you define.
• Keep you informed of exception conditions through Email.

Now we will create new event alert for a business case that will send email automatically to the new user created in system to welcome and inform him about his details.

Let’s get started!

Attach “Alert Manager” responsibility to your user login

First step is to attach “Alert Manager” responsibility to your user login. Then change Responsibility to Alert Manager

Define Alert 

1. Go to Alert -> Define
2. Alerts Form will open to you then

1. Select Application as Application Object Library
2. Name the Alert for Example : ” XX_CREATE_USER_NOTIF”
3. Select Event tab
4. From Event Details select :
5. Application : “Application Object Library“
6. Select Table as : “FND_USER”

“This table is a crucial thing to understand. You have to select the table in which data is being Inserted or Updated, and for that data, you want to send an alert.”

In the above example, we need to perform a particular action once a new user is created in the system. FND_USER table is the main table that data is inserted into whenever we are creating a new user. So besides the application field, we have to enter the FND_USER in the Table field.

Write your query.

An important step is to write a select query, which helps you to select your required information when Alert triggered.

Note: You have to write a select statement on the same table, which you have used in Alert Definition to get: ROWID returns the row id of the data being inserted or updated in your table.

Oracle alert sql statement script sample :

SELECT user_name, email_address
INTO   &user_name, &email_address
FROM   fnd_user
WHERE  rowid = :rowid
AND    email_address IS NOT NULL;

Define Alert Action:

1. Click on Actions Button.
2. Write an Action Name and proper description.
3. Click on Action Details

Define Alert Action Details:

Then Action Details form will open like below:

1. Select Action type as “Message”

You can define four type of Alert Actions. It depends on your requirements.

1. Concurrent Program (This helps to execute a concurrent Program)
2. Operating System Script (Run OS Script)
3. Message (Send an Email Notification)
4. SQL Statement Script (Execute a SQL Script) in this example we are using alert to send email notification. Refer to above screenshot. Write Email address of the recipient User in TO field. Write Subject of Email.

1. Enter To field as “&email_address” variable returned from your alert query to send this email to new user email
2. Enter a subject for your message
3. In Text, you have to write your Message Using your variables, which you have, used in Query. My Alert Text is as below.

Hi &user_name,

Kindly be informed that new username has been created for you inside Oracle E-Business Suite.

Best Regards,
Oraask.CoM

Define Action Set: 

1. Click on Action Sets
2. Write an Action Set Name and Description. Please make sure it is marked as enabled

1. Click on Action Set Details
2. Go to Members Tab
3. Select the action you defined. Please make sure action is marked as enabled. Then Save.

In this article, we have demonstrated What is an Oracle Alert and How to define event alert in Oracle APPS R12.

We are delighted to hear from you in the comment section below. If you face any issues, we can asset you.

Hopefully, it was clear and concise.

[convertkit form=1415728]