| Urgent--regarding alerts [message #170261] |
Tue, 02 May 2006 23:57  |
live2learn
Messages: 34
Registered: February 2006
|
Member |
|
|
Hi gurus,
I am working with alerts.
While sending an email through the alert i am facing a problem.
The e-mail id is not hard-coded in the "TO" field rather it will be generated from a query like:
select mail_id
into &mail_id
from table_name
where col1=&col1;
And i am writing in the "TO" section &mail_id.
This is working fine if there exists only one mail id.
And even i knew that if it gives more than one value it will error out while copying into the variable &mail_id
so will anyone please guide me as to how to catch (>1) mail_id's so that i can send to N number of recipients.
Thanks & Regards,
Saritha
|
|
|
|
| Re: Urgent--regarding alerts [message #170278 is a reply to message #170261] |
Wed, 03 May 2006 01:50  |
adragnes
Messages: 241
Registered: February 2005
Location: Oslo, Norway
|
Senior Member |
|
|
Saritha,
In order to send an email to multiple recipients in Oracle Alerts you just separate the recipients using a space, or a comma or a combination of the two. So your task is really to find a way of creating such a list using a query. I suggest you take a look at Tom Kyte's STRAGG function.
Keep in mind that Alerts do not handle a string longer than 240 characters so you cannot really send the alert to that many people using this strategy.
--
Aleksander Dragnes
|
|
|
|
|
|
|
|
| Re: Urgent--regarding alerts [message #170365 is a reply to message #170261] |
Wed, 03 May 2006 08:12 |
jsweeney
Messages: 8
Registered: October 2001
|
Junior Member |
|
|
Maybe the code at the bottom of this note will give you some ideas. We use this code for an alert that captures MULTIPLE notifications and sends them in ONE single email per user/email address.
The Action Level for the alert is SUMMARY.
The To line of the email is populated using &EMAIL_ID.
The text of the email looks like this:
The following EAS notifications require your attention.
Date Sent Due Date Description
------------- --------- ------------
=**= Enter summary template below this line =**=
**&BEGIN_DATE &DUE_DATE &SUBJECT
=**= Enter summary template above this line =**=
&DB_BODY
ALERT SQL:
SELECT distinct fu.email_address,
wnv.subject,
wnv.priority,
wnv.due_date,
wnv.begin_date,
GWU_UTILS.GWU_DATABASE_SUBJECT,
GWU_UTILS.GWU_DATABASE_BODY
INTO &EMAIL_ID,
&SUBJECT,
&PRIORITY,
&DUE_DATE,
&BEGIN_DATE,
&db_subject,
&db_body
FROM wf_notifications_view wnv,
wf_user_roles wur,
fnd_user fu
WHERE status = 'OPEN'
AND message_name IN ('GWU_PO_REQ_APPROVE','GWU_PO_BLNKT_REL_APPROVE','PO_REQ_REJECT','PO_REQ_RETURNED_BY_BUYER','MSG_NOTIFY_PI')
AND wnv.message_type not like 'GLBATCH%'
AND wur.role_name = wnv.recipient_role
AND fu.user_name = wur.user_name
AND fu.email_address is NOT NULL
ORDER BY fu.email_address
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
