Sujeet APPS DBA: Alerts in Oracle Applications

Overview: You can use alerts simply to notify one person/multiple persons about an event or some data change. There are two type of alerts

Event based Alerts
Periodic Alerts

Event based Alerts: These Alerts are fired/triggered based on some change in data in the database. Ex: If you want to notify your manager when you create an item in the inventory you can use Event based alerts. When you create an item in the inventory it will create a new record in mtl_system_items_b, here inserting a record in the table is an event so when ever a new record is inserted it will send the alert. In same alert you can also send the information related to that particular item.

Periodic Alerts: These Alerts are triggered hourly, daily, weekly, monthly or yearly based on your input. Ex: If you want to know list of all items created on that day at the end of day you can use periodic alerts repeating periodically by single day. This alert is not based on any changes to database. This alert will notify you everyday regardless of data exists or not that means even if no items are created you will get a blank notification.

What can be done with Alerts?

You can send notifications
You can send log files as attachments to notifications
You can call PL/SQL stored procedures.
You can send approval emails and get the results.
Print some content dynamically

How to create an Alert?

Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
If you are going for periodic alert decide the frequency
If you have chosen event based alert then find out on what event (insert, update, delete) you want to fire the alert.
Decide what data need to be included in the alert
Based on the data you want in the alert write a SELECT SQL Statement to pull the data.
Create a distribution list grouping all the people to whom you want to send the alert.