Oracle Alerts

Introduction:
Oracle Alerts is something that can be used to Notify/Alert to one or multiple
persons about an activity or change that occurs in the system. The alerts can also
be used to call a procedure, run some sql script etc.
There are 2 types of alert
1) Periodic Alert
2) Event Alert
Periodic Alerts:
These alerts are trigger periodically, hourly, daily, weekly, monthly etc based
upon how it is setup to be triggered. When alert runs and the condition(SQL
Query etc.) in the alerts fetches record, then the events specified in the alert are
triggered.
Ex. 1) Daily alert to send notification on the sales order on which credit check
hold is applied for a day
2) Hourly alert to send notification on all the concurrent request that completed
with error
3/If you want to know list of 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 chages to database.this alert will notify you everyday regardless of data exists
or not that means even if no items are created you wil get a blank notification.
Event Alerts:
These Alerts are fired/triggered based on some change in data in the database.
This is very similar to the triggers written on the table. Unlikely, event alerts can
only fire on After Insert or After Update.
Ex. 1) An alert that sends notification when new item is created.
Ex: If u 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
cretae 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.
What can be done with Alerts :
1.You can send notifications
2.You can send log files as attachments to notifications
3.You can call PL/SQL stores procedures
4.You can send approval emails and get the results
5.Print some content dynamically
How to create an Alert?
1.Study your Business requirement and decide what type of alert you need either
periodic alert or event based alert.

2. If you are going for periodic alert decide the frequency.
3. If you have chosen event based alert then find out on whst
event(insert,update,delete) you want to fire the alert.
4. Decide what data need to be included in the alert.
5. Based on the data you want in the alert write a SELECT SQL statement to pull
the data.
6. Create a distribution list grouping all the people to whom you want to send the
alert.
Navigation :
1. Go to "Alert Manager" Responsibility.
2. Alert >> Define
Business Requirement
---------------------------
Notify when sales order is booked or new line is entered on booked order
We can do this through triggers. Alternative way is Alerts
Query
SELECT ooh.order_number
, ool.line_number||'.'||ool.shipment_number line_number
, ordered_item, ordered_quantity, ool.flow_Status_code
INTO &order_num, &line_num,&Item_num, &Quantity, &line_Status
FROM oe_order_headers_all ooh, oe_order_lines_all ool
WHERE ooh.header_id = ool.header_id
AND
( ooh.booked_date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
OR (ool.creation_Date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
AND ool.creation_date > ooh.booked_date)
)
2/Define Actions
Click on the actions button and then actions Detail button and define message as
shown in screenshot. Note that the message type is summary.

3) Define Action Sets
Click on action sets and then action set details and in the members tab enter the
action

4) Schedule the Request
Navigate to Request --> Check and submit the alert. Based on the definition of
alert it will be scheduled to run.
Second Example
----------------------
We have to design periodic alert “Items with zero weight” for our Client.Basic Business
need of this alerts is for Email to MIS User for List of item having Zero Weight saved in
Oracle Inventory.
Alert Manager=>Alerts=>Define



Application Field: Name of Application e.g Oracle Payable
Name : User defined Name of Alerts.
Choose Period tab
Frequency :Choose Frequency Accordingly.
Days: Choose days according to Frequency. Suppose you Chosse Frequency “Every N
Business Days” Then Enter Value “1” in Day Field.
Start Time: Time You want to Fire periodic alerts suppose you want to fire at 6:30 A.M
Write “06:30:00” in Start Time Field.
Keep: How many days You Want to Mainitain History.
Select Statement: Write Query in select Statement . Here is an Exapmle of Select
Statement for Test Periodic Alert “Items with zero weight”. Select statement must
include an INTO clause that contains one output for each column selected by your Select
statement.In Example all input Column like
Orgaization_code,tem_number(segment1),Description,Creation_date have Output
Variable ORG,ITEM,DESCR,Create_date preceded by Amperstand(&).
Query is: Test Query for “Items with zero weight” Alert is




SELECT
distinct p.organization_code ,
substr(i.segment1,1,20) ,
substr(i.description,1,50),
i.creation_date,
INTO
&org
, &item
, &descr
, &create_date
FROM
mtl_system_items i,
mtl_parameters p
where i.organization_id = p.organization_id
and p.organization_code in ('INF','ENF')
and i.INVENTORY_ITEM_STATUS_CODE||'' = 'Active'
and i.unit_weight is null
and I.ITEM_TYPE = 'P'
order by 1,2
Verify: Click on Verify Button to Verify your Query. This message will populate if Query is Right.




Run: To Check Record Count of Your Query Click on Run Button. Suppose Your Query Written
Zero Rows This Message will populate.
STEP2 : Define Action:
Click on Action Button(Marked With Circle). This Form (Action Window 1.0) Will Poulate..
Explaination of Field on this Form :
Action Name:Specify Name of your Action. For Example Our Alert ““Items with zero weight”. is for
Email.we specify name “EMAIL” in Action Name.
Action Level: Action level should be Summary.
Three are three Action Level
Detail - action is performed for each exception returned.
Summary - action performed once for each exception.
No Exception - action performed when no exceptions returned.
Click on Action Details





Action Type: Four Action type.
Message - send message
Concurrent Program Request - submit concurrent program
SQL script - execute a SQL script
OS script - execute an OS script
We Choose Action Type “Message” because “Items with zero weight” Alert is for Email
Purpose.
Enter Email Addrees in To Field.
Text : In Text field design Layout of your Periodic Alerts. “Items with zero weight” Alert
Layout is this:
Important thing is that Output Variable &org,&item etc should be placed with in template
like this...




=**= Enter summary template below this line =**=
**&org &item &descr &create_date
=**= Enter summary template above this line =**=
Layout Sample of Test Periodic Alert “Items with zero weight”:
The following items currently have no weight maintained against them in the system.
Org Item Description Creation Date
=== ==================== ==============================
=============
=**= Enter summary template below this line =**=
**&org &item &descr &create_date
=**= Enter summary template above this line =**=
Column OverFlow: ‘Wrap’
Max Width: Choose According to Requirments.
80 Characters
132 Characters
180 Characters
STEP3 : Define Action Sets:
Enter the groups of action to be run. Click on Action Sets Button.
Action Set Window (Shown in Pictiure “Action set Window 1.2”) will populate.
Action set Name: Enter Action Set Name. In our Test Alert we enter Action set Name
“EMAIL”.
Note : Action set Name should be same as in Action Name. Otherwise Periodic Alert will
not Fire.




Action set Name: Enter Action Set Name. In our Test Alert we enter Action set Name
“EMAIl”.
Output Tab: Output tab Contain List of Output Variable defoen in select Statement as
shown in Window 1.4.Note that if you Output tab is Blank Requery the Alert then it will
show output Variable.
This problem we generally faced when designing periodic Alerts.
Member Tab: Enter Name of Action .