ORACLE EBUSINESS SUITE R12

2011

Event Based Alerts Step by Step Guide

Malik Asif Joyia

A N J O Y I A @ G M A I L . C O M

2 | P a g e

Contents

INTRODUCTION ................................................................................................................................................. 3

HOW TO CREATE EVENT ALERT ............................................................................................................................ 3

Define Alert ............................................................................................................................................... 3

Define Alert Action .................................................................................................................................... 7

Define Action Set .................................................................................................................................... 10

Define Alert Details ................................................................................................................................. 11

ALERT EMAIL .................................................................................................................................................. 13

TROUBLESHOOTING ......................................................................................................................................... 13

3 | P a g e

INTRODUCTION There are two types of alerts available in Oracle Alerts Module.

1. Periodic Alerts

2. Event Based Alerts

In this article we are going to cover event based alerts. Which helps to send email

notification in case of a specific event on a database table. Following are the two

events which can be used.

After Insert

After Update

For Example: If your payables department wants to have a notification whenever

a receipt is made in Inventory Department.

Same way you can have different scenarios based on your business requirements.

HOW TO CREATE EVENT ALERT

Attach “Alert Manager” responsibility to your login.

Change Responsibility to Alert Manager

Define Alert

Go to Alert -> Define

4 | P a g e

5 | P a g e

Select Application as Purchasing and Name the Alert for Example “On Receipt”

Go to Event Tab

Select the Application “Purchasing” and Table as “RCV_TRANSACTIONS”

This table is very important 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 a

alert.

For example: If you want to have an alert in case an Employee is Created or

Terminated. So you have to select Application as Human Resources. And when an

Employee is created, data is inserted in per_all_people_f so you have to use

per_all_people_f as event table in your alert definition.

6 | P a g e

Select “After Insert” if you want to send alert when a new record is created. In

the same way “After Update” will help you to send alert when a record is

updated. Based on your requirement you can use both together.

2nd Important step is to write a select query, which helps you to select your

required information when Alert is triggered.

Example Query:

This Query Select PO Number, PO Line & Receipt Number.

SELECT poh.segment1, pol.line_num, rcvsh.receipt_num

INTO

&PO_NUMBER,&PO_LINE,&RECEIPT_NUMBER

FROM po_headers_all poh,

po_lines_all pol,

po_distributions_all pod,

rcv_transactions rcvt,

rcv_shipment_headers rcvsh

WHERE poh.po_header_id = pod.po_header_id

AND poh.po_header_id = pol.po_header_id

AND pod.po_line_id = pol.po_line_id

AND rcvt.source_document_code = 'PO'

AND rcvt.po_header_id = poh.po_header_id

AND rcvt.po_line_id = pol.po_line_id

AND rcvt.shipment_header_id = rcvsh.shipment_header_id

and rcvt.TRANSACTION_TYPE = 'DELIVER'

AND rcvt.TRANSACTION_ID IN (SELECT TRANSACTION_ID FROM rcv_transactions WHERE

rowid=:ROWID )

7 | P a g e

Note: You must have to write select statement on same table which you have used

in Alert Definition to get :ROWID

:ROWID returns the row id of the data being inserted or updated in your table.

Define Alert Action

Click on Action Button

Write a Action Name.

8 | P a g e

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 a 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

9 | P a g e

In Text you have to write your Message Using your variables which you have used

in Query.

My Alert Text is as below.

Hi

New Material Receipt has been made in Stores.

Following are the details.

PO = &PO_NUMBER

POLine = &PO_LINE

Receipt Number = &RECEIPT_NUMBER

Regards,

My IT Department

Note: Variables are highlighted in yellow.

10 | P a g e

Define Action Set

Click on Action Set

Write an Action Set Name and Description. Please make sure it is marked as enabled.

Click on Action Set Details

Go to Members Tab

11 | P a g e

Select the action you defined.

Please make sure action is marked as enabled.

Save.

Close Action Set

Define Alert Details

Click on Alert Details Button.

12 | P a g e

Go to Installations Tab

Select your oracle ID as apps and your OU.

Save.

Close the Alert Details

Click on Verify Button

Save.

13 | P a g e

ALERT EMAIL

To test this alert, create a material Receipt against PO. You will receive a

notification.

TROUBLESHOOTING

To troubleshoot any problem, review the concurrent program log. For each alert

system fires a alert concurrent program.