Traditionally, Oracle Alerts are used to deliver key information from EBS, in the format of your choice.
There are many different options in defining an alert to meet a business need.
One such option is to create alerts with "Summary Actions". This option is useful to consolidate and
distribute the data to few groups instead of entire population like people managers, business heads or administrators.
In this article, I will discuss the steps involved in defining a Summary Alert.
Want to read more about Oracle Alert basics? Read my older article to know more.
Let's assume a business need of informing work managers every day about the team birthdays.
Firing one email for each team member birthday won't make sense for larger teams.
Sending them one summary email would be neat and clean.
Step-1) First step, obviously, to create a periodic alert, which runs on every calendar day.
Here is the SQL to get the list of all employee birthdays and their managers (who are configured as managers in EBS).
SELECT EMP.global_name employee_name,
EMP.date_of_birth employee_dob,
EMP.email_address employee_email,
MGR.global_name manager_name,
MGR.email_address manager_email
FROM APPS.per_all_people_f EMP,
APPS.per_all_assignments_f ASG,
APPS.per_all_people_f MGR
WHERE trunc(sysdate) between EMP.effective_start_date and EMP.effective_end_date
AND to_char(to_date(EMP.date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(EMP.date_of_birth),'mm') = to_char(to_date(sysdate),'mm')
AND ASG.person_id = EMP.person_id
AND trunc(sysdate) between ASG.effective_start_date and ASG.effective_end_date
AND ASG.primary_flag = 'Y'
AND ASG.assignment_type='E'
AND MGR.person_id = ASG.supervisor_id
AND ASG.supervisor_id IS NOT NULL
AND trunc(sysdate) between MGR.effective_start_date and MGR.effective_end_date;
Defining an alert with the above SQL. Note the INTO clause: it is added to capture the column data into alert variables (alert variables start with "&" sign).
Syntax for SQL can be checked by clicking on "Verify" button.
And the number of exceptions can be tested, by clicking on 'Run' button.
"Exceptions" -> mean the number of matching rows
By the way, did you notice the "Keep - 7 days" option in the above screenshot?
This option keeps the alert execution history for a week in the system without purging.
Step-2) Defining a Summary Action
Message Full Text:
Dear &manager_name,
Here is the list of team members celebrating
birthdays today (&employee_dob)
Employee Email
-------- ---------
=**= Enter summary template below this line =**=
** &employee_name &employee_email
=**= Enter summary template above this line =**=
Regards
HR
Notice the use of static text as highlighted in the above message.
This does the trick of grouping all the employees, who report to a manager.
Oracle Alert automatically group the rows (exceptions) by the alert variables mentioned outside the static text.
Alert variables mentioned inside the static text are added for each unique combination of outside variables.
In our business case, Alert engine prepares one distinct message, for each work manager and with the list of employee birthdays included.
Step-3) Almost done.... last step is to attach the action that was defined in Step-2, to an action set.
Notice, "Suppress Duplicates" option on Action Sets screen is also selected, to help avoiding multiple emails, if any. (For extra caution to avoid duplicate emails to managers)
Here is the screenshot of sample email fired by this summary alert.
Hope you find this article useful.
Please leave your comments/feedback below.
Reference
R12 Oracle Alert - User Guide
