To define an event alert:To create an event alert, you perform the following tasks in the order listed:
Define the database events that will trigger your alert
Create action sets containing the actions you want your alert to perform
This section focuses on the first task of defining the database events that trigger your event alert and divides the task into smaller sub-tasks.
Before you define an event alert, make sure you do the following:
Configure the Workflow Notification Mailer to send and receive e-mail messages according to your alert requirements. See: Setup Steps.
Specify Oracle Alert options to configure how Oracle Alert checks alerts and handles alert messages. See: Specifying Oracle Alert Options.
To define an event alert:Navigate to the Alerts form.
In the Application field, use the list of values to choose the name of the application that owns the alert. This application must reside in the same Oracle database as Oracle Alert.
Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters). See: Oracle Alert Naming Conventions.
Enter a name for the alert that is unique within the application. Use an initial character other than a pound sign (#), a colon (:), or a percentage sign (%).
Choose the Event tab.
Check Enabled to enable your event alert.
To specify an event table:Specify the name of the application and the database table that you want Oracle Alert to monitor.
Although the application you enter here need not be the same application that owns the alert, both applications must reside in the same Oracle database and the application that owns the alert has to have Select privileges on the tables listed in the alert Select statement.
Attention: You cannot use a view as the event table for your alert.
Attention: Do not define an event alert on the table FND_CONCURRENT_REQUESTS. Oracle Alert submits a concurrent request to the concurrent manager when an event alert is triggered by an insert or update to an event table. For concurrent processing to occur, every submitted concurrent request automatically gets inserted as a row in the FND_CONCURRENT_REQUESTS table. If you define an event alert on this table, you create a situation where the event alert will cause an exception to occur recursively. See: How Oracle Alert Checks Alerts.
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. See: Overview of Oracle Alert History.
Specify a value in the End Date field if you want to disable your alert by a certain date. Note that you can enter a date in this field only if the alert is enabled.
In the Last Checked field, Oracle Alert displays the last date that this exception condition was checked, if it has been checked.
To enter a SQL Select statement for your event alert:Enter a SQL Select statement in the Select Statement field that retrieves all the data your alert needs to perform the actions you plan to define.
Attention: The SQL Select statement must be less than 32K.
Your event 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.
Suggestion: If you want to use an input value in an action for this alert, select the input into an output. Then you can use the output when you define actions for this alert.
When selecting number columns, Oracle Alert uses the number formats defined in your database. Optionally, you can format your number outputs as real numbers by specifying a SQL*Plus format mask in your Select statement. For each number output, simply add a pound sign (#) and format mask to your output name. For example, if you select purchase price into the output &PRICE, add "#9999.99" after &PRICE for Oracle Alert to display the value to two decimal places. Your number output looks like: &PRICE#9999.99.
Your event alert Select statement must also include a join from the table where the event transaction occurs to an implicit input we call :ROWID. Oracle Alert supplies the values for implicit inputs. Oracle Alert uses :ROWID to pinpoint the inserted or updated record in the table that caused the event.
Attention: You must include :ROWID in your event alert's Select statement Where clause, or Oracle Alert will not be able to locate the row where the event transaction occurred.
Oracle Alert also automatically supplies the values for the implicit inputs :MAILID and :ORG_ID when triggering an event alert.
The value for :MAILID is the electronic mail address of the application user who triggered the event. This value comes from the EMAIL_ADDRESS profile option. If you want to send an alert message to that user, or use that user's email username in an action, simply select :MAILID into an output.
Note: To use the EMAIL_ADDRESS profile option value as the :MAILID value, you must create and set the EMAIL_ADDRESS profile option and perform some additional setup steps. If the EMAIL_ADDRESS profile option has not been created, or if its value is null, Oracle Alert uses the operating system user ID or the Application username of the user who triggered the event, depending on the value set for the Default User Mail Account option in the Oracle Alert Options form. See: Setting Up the :MAILID Implicit Input.
The value for :ORG_ID is the ID of the operating unit that the user is running against upon triggering the event alert. If your SQL statement selects from a multiorg partitioned view, Oracle Alert automatically sets the correct operating unit context to retrieve the data and you need not use :ORG_ID in the SQL statement. You can, however, use :ORG_ID in your SQL statement to select from a table that is partitioned by operating units, that is, a table that has an ORG_ID column.
Oracle Alert does not require that you use :MAILID or :ORG_ID in your Select statement.
Attention: The inputs :MAILID, :ORG_ID and :ROWID are reserved terms that you should not use for anything other than their intended purposes.
Here is an example of an event alert Select statement that reports the creation of new users:
SELECT user_name, :MAILID INTO &NEWUSER, &USER FROM fnd_user WHERE rowid = :ROWID
Note: 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. For example, you can enter a SQL Select statement that looks like:
SELECT package1.function1(:INPUT1, column1) INTO &OUTPUT1 FROM table1
In this example, package1 is the name of the PL/SQL package andfunction1 is the name of user-defined PL/SQL function stored in the package.
To ensure that your PL/SQL function is callable from a SQL statement, verify that the packaged function meets the requirements listed in your PL/SQL Release 2.1 and Oracle Precompilers Release 1.6 Addendum.
Rather than typing in your Select statement, you may also import a SQL Select statement from a file in your operating system. Choose Import... and specify the location and name of the file that contains the SQL Select statement you want to import into your alert definition.
Attention: The file you import must be less than 32K.
To verify the SQL statement: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.
Navigate to the Alert Details window to complete the definition of your event alert. See: Specifying Alert Details.
