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 what 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 these 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 .

Alert Back end tables

 --------------------------

ALR_ACTIONS

ALR_ACTION_SETS

ALR_ACTION_HISTORY

ALR_ALERTS

ALR_DISTRIBUTION_LISTS

ALR_PROFILE_OPTIONS