Alert Tables

SELECT alert_name,
       description,
       start_date_active,
       DECODE (alert_condition_type,  'P', 'Periodic',  'E', 'Event')
          ALERT_TYPE,
       frequency_type,
       insert_flag,
       update_flag
  FROM APPS.alr_alerts
 WHERE     enabled_flag = 'Y'
       AND end_date_active IS NULL
       AND alert_name LIKE 'AA%'
      
SELECT *
  FROM apps.ALR_ACTIONS_V
 WHERE subject LIKE 'JDSU%Notification%'

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

Alerts

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.

Navigation:

·         Go to “Alert Manager” Responsibility

·         Alert >> Define

Transfer Alert from one instance/database to other:

·         Go to Alert Manager Responsibility

·         Alert >> Define

·         Go to “Tools” Menu on top

·         Click on “Transfer Alert”

·         Enter source and destination fields and click Transfer.
 

Screenshots:

 Will be posted soon.