Search This Blog

Sunday, April 5, 2009

Introduction to 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

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.

In next post we will discuss steps to create Periodic and Event alert.

37 Comments:

Vijayakumar said...

Dear Suresh,

God bless you.I would like to convey my spl thanks to you for providing such a wonderful documents which came out out of your real time experiences & examples.I further state that these would be very much useful to the consultants like me.Doing a divine job by helping so many free of cost.This site is gold mine of information.Both technical/functional material is of great help and rich in information.Hat's of to your work..If oracle is owe anything to any one,that's going to be you people.Thanks in Anticipation.

Thanks,

Kasi, Vijayakumar.

Suresh Vaishya said...

Thanks Vijayakumar for such an appreciating and encouraging message.

Vijayakumar said...

Dear Suresh,

Many thanks for your kind words. I was away from office for a week and returned today only. Based out of India and working as a staff consultant in entechsolv software India pvt ltd.Your are doing a awesome job. Your articles are extremely informative and very clear. I am very thankful to you for your tutorials. It was a great effort from your side to make such type of documents which are really very helpful. These are very handy documents. These documents really make the oracle mountain scalable. The visuals especially make it even simple. Indeed it is an ocean and you make it as very simple for understanding oracle apps through this website. You are very good and kind hearted. You are doing a service for apps community. May god bless you Thanks in Anticipation.

Thanks,

Kasi, Vijaya Kumar.

Suresh Vaishya said...

Thanks your kind words Vijaya Kumar.

Anonymous said...

dude, when is your next post on this subject? plz provide it with snapshots, especially the Event Alert!!

Suresh Vaishya said...

I will soon post how to create event alert with screenshots.

vvvv said...

Dear Suresh,

me too eagerly waiting for ur next post..

Thanks and Regards,
venkat

bedo said...

your next post never came .....

invinci said...

Hi Suresh,

I am currently developing a oracle periodic alert(on Demand) to send a notification when the order line is booked against a particular item.
I am calling this alert from a database trigger using FND_REQUEST.SUBMIT_REQUEST. I need to pass the line_id or rowid as parameter to fetch the exact line.

I tried the below command to call and pass parameters.
v_request_id := fnd_request.submit_request(
'ALR'
, 'ALECDC'
, 'Sample alert'
, NULL
, FALSE
, 20003 -- appln id
, 121082 -- alert id
,'A'
,'Send_action' -- action set name
,:NEW.LINE_ID
);

Though the concurrent program is submitted , it says there are no exceptions occurred to trigger the action .

Could you please let me know if it is correct way of passing the parameter or is there any log /debugging method to figure out the issue ?

Thanks in advance,

Regards,
savitha

Suresh Vaishya said...

Savita,
Why do you need to write a trigger for this. This can simply be achieved just by using alert. Depending upon the frequency of alert set to run you can use booked date column of oe_order_headers and trigger the alert. For e.g. If alert is running once a day then following condition in where clause should do the trick


and booked_date >= to_date(sysdate,'DD-MON-RRRR HH24:MI:SS')-1


hope this helps. Let me know if any more question.
Thanks,
Suresh

invinci said...

Hi Suresh ,

The requirement is sent out an e-mail whenever a sales order is booked with an item say 'A'.
We have 2 scenarios in which the alert should be fired

1) When i create a new order and an order line with item 'A' ; the line status will be ENTERED and in this case the alert should not be sent as it is not yet booked. Now when i book the order , the alert should be fired( means when the status changes from ENTERED to BOOKED or AWAITING_SHIPPING ).

2) When a line is entered on the booked order , the line status will be AWAITING_SHIPPING itself so alert has to be triggered immediately after an insertion into OE_ORDER_LINES_ALL. This can be achieved through event(AFTER INSERT) based alert on OE_ORDER_LINES_ALL.

But for the point 1, if i check AFTER UPDATE on the event alert ; the alert will get fired for any column being updated in the table. Duplicate alerts will be sent

So I wrote a database trigger for an update of FLOW_STATUS_CODE/ INSERT on OE_ORDER_LINES_ALL and call the alert from there. Also changed the alery type as "PERIODIC --) ON DEMAND"

Now, I am not sure how to pass the parameter (either LINE_ID or ROWID) to fetch the exact row being affected.

Kindly let me know if you require any clarifications.

Suresh Vaishya said...

Savita,
As I said earlier this requirement can be achieved just by using events. You dont need to write database triggers. Does user want immediate email when the order is booked or they are OK if we send email every hour or may be once a day on booked orders.

I will later provide you with a query or steps on how to achieve this.

Regards,
Suresh

invinci said...

Hi Suresh,

Thanks a lot for the post. User has accepted to wait an hour for the alert. So i followed the steps given by you and it's working fine.

Regards,
Savitha

suresh said...

Hi Suresh,

Could you help me in writing event based alert when order is BOOKED?

Thanks & Regards,
Suresh.

Anonymous said...

Hello Suresh

Our requirement: Send notification to user whenever AP invoice gets rejected.

I have below script for oracle event based alert. It triggers whenever invoice get rejected. But the issue is it also triggers when the distribution lines created, however the invoice is already REJECTED. It trigger as the LAST_UPDATED_BY column updates. We dont want to trigger an alert when the distribution line get created for a REJECTED invoice. We want alert only when invoice status change to rejected.

SELECT
AP.INVOICE_NUM,
AP.INVOICE_AMOUNT,
FND.USER_NAME,
FND.EMAIL_ADDRESS,
AP.ROWID
INTO
&INV_NUM,
&INV_AMT,
&USER_NAME
&EMAIL
&rowid1
FROM AP_INVOICES_ALL AP, FND_USER FND
WHERE upper(WFAPPROVAL_STATUS)='REJECTED'
AND AP.CREATED_BY = FND.USER_ID
AND AP.ROWID = :ROWID

Appreciate your help.

Thanks

Suresh Vaishya said...

one of the option could be to use attribute column and update it with some flag indicating alert has picked the record so it is not picked again.

e.g. attribute10 is null condition in the query.
First time when invoice is rejected, alert will pick invoice and update attribute10 = 'ALERTED'
next time any update on this invoice will not be picked as attribute10 is no longer null.
Hope this helps. Let me know if any further questions.

Suresh

Anonymous said...

Great, Thanks a lot Suresh.

I have created a trigger on AP_INVOICES_ALL table which updates the ATTRIBUTE column wherever invoice rejected.

I want to know if we are allowed to create such custom trigger to update the oracle standard table. Is it supported?

Thanks
Avalon

Suresh Vaishya said...

Glad that it worked for you.
For support, your best bet would be to raise a TAR with oracle and get an answer, so you don't have any problems later.

Abhishek said...

Hi,

Need an urgent help. I need to create a alert which can fetch multiple rows

Let me explain. Say below is the query.For one shipment_number I can have multiple invoice my problem is how can I show multiple invoice in one mail.If a new Shipment Number is created.PLease help

select invoice_number,shipment_number
into &invoice_number,&shipment_number
from tab1,
tab2
where
1 = 1
and other conditions;

AnjanaReddy said...

Hi Suresh,

We have created an alert and we r getting alert mails as per defined schedule in alert. my problem is, whenever alert query returns 2 rows data then we are getting 2 alert mails for each one.

Can you tell me , How to change 2 alerts details in one mail.

Thanks,
AnjanaReddy

Anonymous said...

You alert maybe setup as detail and not summary. You should try the summary version using the template provided for it.

The summary should send out a single email with both lines.

Ashutosh Parekh

Anonymous said...

APP-ALR-04020 ERROR

MY SELECT STATEMENT IN EVENT ALERT IS

SELECT A.PO_HEADER_ID,A.LAST_UPDATE_DATE,A.LAST_UPDATED_BY,A.CREATION_DATE,A.revision_num INTO &P,&L,&B,&C,&R FROM PO_HEADERS_ALL A WHERE A.ROWID=:ROWID;


MY ACTION IS

INSERT INTO POP VALUES(&P,'&L',&B,'&C',&R);


WHEN I SEE THE REQUEST IT SAYS COMPLETED WITH AN ERROR.

ERROR IS

APP-ALR-04020: Oracle Alert was unable to execute "&VALUE". Check that this file exists and that its read protection is set correctly.



PLEASE TELL ME WHAT MAY BE THE REASON FOR THE ERROR...

Anonymous said...

We have created an event alert on po_requisition_headers_all(after update). When ever the req is cancelled alert should fire, issue we are having it fires twice.
Any suggestion how we can restrict to fire only once.

Joohi said...

Hi Suresh,

I have the same requirement as Savitha had for the Event Alert.Could u plz let me know the solution which u have told her.

Thanks,
Joohi

Suresh Vaishya said...

Joohi,
can you please elabborate more on your requirement.

Thanks

Mahesh said...

Hi Suresh,

I have a requirement to notify the requistion approver once the goods are received. I am checking for the status from rcv_shipment_lines. I need to notify if the items are fully/partially received. I am checking for shipment_line_status_code in ('PARTIALLY RECEIVED','FULLY RECEIVED').
Can you please provide your inputs on how this can be achieved?

Regards
Mahesh

Anonymous said...

Hi Suresh,

In my case an alert should be sent once the ORDER lines (not header) are closed.It should fire once a day.How will i check with the dates??Could you please help me?

Thanks,
Joohi

Calogero said...

Hi, for first thing... my apologies for my bad English.
I hope you can help me,
I have scheduled a periodic alert with daily frequency, start time 00:00:00, end time 23:59:00 and check interval 12:00:00.
I don't understand because I receive only one mail a day instead of two mail a day, what am i doing wrong?
Thank in advance
Calogero

Anonymous said...

Hello,
I am actually new to this oracle alerts, and I defined an alert(ondemand) and the actions part has a sql script which calls an API, and updates the table. how do i run this alert to test if the tables are getting populated? do i have to run it like a request or anything else...please help me with this...

Jaya said...

Hi Suresh,

Thanks for the posting on the Alerts. I need help to create alert saying that when ever cost manager is Inactive the alert should work.
I have created query like this
SELECT PROCESS_TYPE
INTO &PROCESS_TYPE
FROM MTL_INTERFACE_PROC_CONTROLS_V
WHERE PROCESS_CODE <> 4

but its not working can you let me know where I am wrong.

Anonymous said...

hello suresh,
Is is possible to send (email) concurrent request log using alert.
Once a concurrent request is completed the alert should send the log as email.
Thanks,
Phani

Madhu Mathur said...

REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
'ALR',
'ALECDC',
'test', --'XXJMU_STRIKE_NOTIFICATION_PENDING',
to_char(sysdate), --TO_CHAR(sysdate, APP_DATE.canonical_DT_mask),
FALSE,
'800',
'103027', --'102028',
'A',
'SENDEMAIL',--'MSENDEMAIL',
'SUPERVISOR_NUM ='' ',
l_employee_number , CHR(0), '', '','',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '' )

when i pass parameter as supervisor employee number output is not getting gerenated. but if i pass parameter directly throuth alert it works fine.

saheb shaik said...

Hi suresh,
i have requirement to create Periiodic alert based on the condition.

Here My requirement is
Send mail to suppliers when ever PO is created to them.
If we choose Action level detail for the action, n mails are going for n rows.
Same supplier may have multiple PO's at this multiple mails are sending instead of this i want to send one mail to supplier with the no.of PO's generated to the suppliers.

Please suggest me on this.
Thanks
saheb.

Anonymous said...

Hi Suresh,

Can you please help me in creating a periodic alert. My requirements are:
The alert should be triggered last 10 days of a quarter (every 3 hours). In between the quarter, business used to perform soft quarter closure activities, so for that month too alert should be triggered for the last 10 days of the month(every 3 hours). This alert should send some data extracted by the query(alredy given, but it will only extract the data) given to some users.

Regards,
Arun

Suresh Vaishya said...

Arun,
If you want to pick only last 10 days of data then the query has to be modified to pick data accordingly.

Suresh

Anonymous said...

Suresh,

What you meant is to modify the query what we give in the alert while defining it.
But if I am creating an alert with such a query, how should I schedule it? In Periodic details ->Frequency what should I select? All the values in the Frequency LOV can be used only for a month for max, so the alert will sent mails(without any data) to the user every day or month according to what is give in the query even though it should send mail only the last 10 days of every quarter.

Thanks,
Arun

Anonymous said...

Suresh,

I dont want to pick the data of last 10 days. The alert should be scheduled to send the data extract mail to the users last 10 days of every quarter (every 3 hours).

Thanks,
Arun

Copyright (c) All rights reserved. Presented by Suresh Vaishya