Idea of this post is to create a simple alert and test if it is working or not.
Just for example, assume a requirement to send Happy birthday mail to employees in an organization.
As you might already know by now, there are 2 kinds of alerts in oracle apps.
Periodic Alerts and Event based Alerts.
You are correct, we have to choose Periodic alert for our requirement.
Employee Birthday can be any calendar day of the year. So we will tell oracle apps
to check daily once every calender day and see if today is employee's birthday and send email if true.
You need Alert Manager Responsibility to define a new Alert.
Navigate through Alert Manager -> Alert -> Define
Fill the options as given in the screenshot below.
Note that we need to write SQL which satisfies our condition and also to fetch required details. Here is the SQL
select global_name, date_of_birth, email_address
into &emp_name, &dob, &emp_email
from per_all_people_f
where trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');
You can check the SQL for syntax using "verify" button.
One point here, if any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occured (number of rows that satisfied the condition).
Next step is to define action if condition matches. Don't forget to select action level of type "Detail". This is because action should be performed once for every Alert Exception.
Then click on "Action Details" button and define the email message as shown below.
That's not enough, we also need "Action Sets" and attach the action which we just created. Just follow the screenshots to do that.
Now comes the question, how do we test this Alert?
Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]
Once concurrent program is successfully ran, the number of exceptions can be verified from Alert Manager -> History and query for alert.
Which means our Alert is working.
Note that you can do many more than just sending mails. Alert can execute SQL code/function/procedures and it can also run concurrent programs.
Just for example, assume a requirement to send Happy birthday mail to employees in an organization.
As you might already know by now, there are 2 kinds of alerts in oracle apps.
Periodic Alerts and Event based Alerts.
You are correct, we have to choose Periodic alert for our requirement.
Employee Birthday can be any calendar day of the year. So we will tell oracle apps
to check daily once every calender day and see if today is employee's birthday and send email if true.
You need Alert Manager Responsibility to define a new Alert.
Navigate through Alert Manager -> Alert -> Define
Fill the options as given in the screenshot below.
Note that we need to write SQL which satisfies our condition and also to fetch required details. Here is the SQL
select global_name, date_of_birth, email_address
into &emp_name, &dob, &emp_email
from per_all_people_f
where trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');
You can check the SQL for syntax using "verify" button.
One point here, if any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occured (number of rows that satisfied the condition).
Next step is to define action if condition matches. Don't forget to select action level of type "Detail". This is because action should be performed once for every Alert Exception.
Then click on "Action Details" button and define the email message as shown below.
That's not enough, we also need "Action Sets" and attach the action which we just created. Just follow the screenshots to do that.
Now comes the question, how do we test this Alert?
Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]
Once concurrent program is successfully ran, the number of exceptions can be verified from Alert Manager -> History and query for alert.
Which means our Alert is working.
Note that you can do many more than just sending mails. Alert can execute SQL code/function/procedures and it can also run concurrent programs.
