View Suresh Vaishya's profile
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.
Search This Blog
Sunday, April 5, 2009
Introduction to Oracle Alerts
Posted by Suresh Vaishya at 9:58 PM
Labels: Oracle Alerts
Subscribe to:
Post Comments (Atom)
37 Comments:
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.
Thanks Vijayakumar for such an appreciating and encouraging message.
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.
Thanks your kind words Vijaya Kumar.
dude, when is your next post on this subject? plz provide it with snapshots, especially the Event Alert!!
I will soon post how to create event alert with screenshots.
Dear Suresh,
me too eagerly waiting for ur next post..
Thanks and Regards,
venkat
your next post never came .....
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
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
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.
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
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
Hi Suresh,
Could you help me in writing event based alert when order is BOOKED?
Thanks & Regards,
Suresh.
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
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
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
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.
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;
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
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
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...
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.
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
Joohi,
can you please elabborate more on your requirement.
Thanks
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
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
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
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...
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.
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
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.
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.
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
Arun,
If you want to pick only last 10 days of data then the query has to be modified to pick data accordingly.
Suresh
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
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
Post a Comment