User Guide‎ > ‎

Sending Business Alert Email Notification according to Email Addresses in the database

Instead of hard coded the email address in the Recipients tab on the Business Alert Wizard, user can define the email destination from the database.

1. Define the query. Make sure the destination email address are presented at 1st column

SELECT
M.EMAIL,
[Employee]=DBO.EENAME(L.EE_NO),
[Leave Date]=L.LEAVE_DATE,
[Leave Type]=T.DESCRIPTION
from plLeaveDayRecord L
INNER JOIN plEmployee E ON E.EE_NO=L.EE_NO
INNER JOIN plDept D ON D.DEPT_CODE=E.DEPT_CODE
INNER JOIN plEmployee M ON M.EE_NO=D.MANAGER
INNER JOIN plLeaveType T ON T.LEAVE_CODE=L.LEAVE_CODE
WHERE
L.LEAVE_DATE>=CONVERT(date, getdate()) AND L.LEAVE_DATE <= dateadd(d, 31, GETDATE())
ORDER BY M.EMAIL, L.LEAVE_DATE, DBO.EENAME(L.EE_NO)

This query will list the coming approved leave of the coming 31 days of each department and is brought the email address of the employee's department manager at the column 1

2. Define the Business Alert Job as usual




Leave blank to the the To: field 



 On  every Monday, each department manager will received the email from business alert at 9:00am something like that