How to design Periodic Alert to send emails?


1] Go to ‘Alert Manager’ responsibility and navigate Alert > Define.

  • 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.

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.

Click on the ‘Verify’ button to check the select statement is correct.

Click on the ‘Run’ button to execute the Select statement.

Once you are satisfied with the SQL statement, save your work.

2] You can view all the input and output column details in ‘Alert Details’ Tab. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

3] After you define your alert you need to create the actions you want your alert to perform. For that click on the ‘Actions’ tab.

  • Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.
  • Select a level for your action: Detail, Summary, or No Exception.
  1. Detail action: performs once for each individual exception found
  2. Summary action: performs once for all exceptions found
  3. No exception action: performs when no exceptions are found.

4] Click on ‘Action Details’ tab to display the Action Details window.

  • Select the Action Type field as ‘Message’ if you want to send emails. Other action types are: Concurrent Program, Operating System Script and SQL Script.
  • Specify the electronic mail IDs of the recipients you want to send your message to in the To field.
  • If you list more than one recipient in any of these recipient fields, separate each recipient by a space, or a comma, or a combination of the two.
  • You can enter as many recipients as you want, up to 240 characters.
  • You can also enter alert outputs or response variables in any of the alert detail fields. Oracle Alert automatically substitutes the associated output value when checking the alert or the associated response variable value when reading the response.
  • Save your changes.

5] Click on ‘Action Sets’ tab in the main Alert Window.

  • Once you create your alert actions, you must include them in an enabled action set for Oracle Alert to perform during an alert check. An action set can include an unlimited number of actions and any combination of actions.
  • Enter a Sequence number that lets you order the execution of action sets during an alert check.
  • Give any suitable name and description.
  • Check Suppress Duplicates if you want Oracle Alert to suppress the actions in this action set if the exception found is a duplicate that occurred during the last alert check.

6] Click on ‘Action Set Details’ tab.

  • Go to ‘Members’ tab.
  • Find and attach the action that is created in Step 3.
  • Save the changes. 

7] Since it is an ‘On Demand’ periodic alert, we can run the alert at any time we want. For that go to Request > Check and enter the alert details. Then click on ‘Submit Request’.

  • This will fire one concurrent program which you can view by going through the navigation:  Request > View
  • View the Log and Output files of the concurrent program to find that the alert is fired successfully.

Done…check your mailbox and you should get emails that are sent from Oracle Alerts.

Advertisements

Alerts in Oracle Application


Alerts in Oracle Application

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

Event Alert:

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

Periodic Alert:

A periodic alert, on the other hand, checks the database for information according to a schedule you define. In a periodic alert specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that  Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. We include all the actions we want Oracle Alert to perform, in an action set.

Navigation in Oracle Apps to define an alert:

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.

How to define an periodic alert:

  1. Go to Alert Manager > Alert > Define.
  2. Select the ‘Periodic’ Tab.
  3. Enter the name of the application that owns the alert in the Application field.
  4. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
  5. Check Enabled to enable your periodic alert.
  6. Set the frequency for the periodic alert to any of the following:
  • 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

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define. Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.

Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function.

You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.

Choose Run to execute the Select statement in one of your application’s Oracle IDs, and display the number of rows returned in a Note window.

Once you are satisfied with the SQL statement, save your work.

Specifying Alert Details:

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

Creating Alert Actions:

After you define your alert you need to create the actions you want your alert to perform. There are four types of actions you can create:

• message actions

• concurrent program actions

• operating script actions

• SQL statement script actions

Choose Actions

Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.

Select a level for your action: Detail, Summary, or No Exception.

Choose Action Details to display the Action Details window.

Select the type of action you want to create in the Action Type field

Creating an Event Alert:

Specify the name of the application and the database table that you want Oracle Alert to monitor.

Note: You cannot use a view as the event table for your alert.

Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.

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.

Important Alert Tables:

  • ALR_ALERTS
  • ALR_ACTIONS
  • ALR_ACTION_SETS
  • ALR_ACTION_SET_INPUTS
  • ALR_ACTION_SET_OUTPUTS
  • ALR_ACTION_SET_MEMBERS
  • ALR_ALERT_CHECKS
  • ALR_ALERT_INPUTS
  • ALR_ALERT_OUTPUTS
  • ALR_ACTION_SET_CHECKS
  • ALR_RESPONSE_SETS
  • ALR_RESPONSE_ACTIONS
  • ALR_VALID_RESONSES

Oracle Alert uses the following internal views:

  •  ALR_ALERT_ACTIONS_VIEW
  •  ALR_ALERT_HISTORY_VIEW
  •  ALR_CHECK_ACTION_HISTORY_VIEW
  •  ALR_INSTALLATIONS_VIEW
  •  ALR_PERIODIC_ALERTS_VIEW
  •  ALR_RESPONSE_ACTIONS_VIEW
  •  ALR_SCHEDULED_PROGRAMS
  •  ALR_VARIABLES_AND_OUTPUTS

For complete details you can refer Oracle Alert User’s Guide