| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||||
|---|---|---|---|---|
|
Description
The notification task sends out an email to the crew member when the employment anniversary comes.
The anniversary years considered : 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90.
Setup and Configuration
Schedule: Daily, repeat every day.
Recipient: crew member/employee.
SQL Statement
| Code Block |
|---|
select
p01.Pin as #PIN,
p01.Name as #FullName,
p01.FIRSTNAME as #Firstname,
p01.MIDDLENAME as #Middlename,
p01.LASTNAME as #Lastname,
p01.EmploymentStartDate as #EmploymentStartDate,
Datediff(yy,p01.employmentstartdate,getdate()) as #YearsInService,
org.NAME as #Company,
email.TELENO as #Email
from pw001p01 p01
left join pw001c02 pos on pos.code=p01.rank
left join pw001p0p p0p on p0p.pin=p01.pin and p0p.pnumber='A'
left join pw001p1r p1r on p1r.pin=p01.pin and rateno=1
Join PWORG org on p01.CLIENT = org.NUMORGID and org.ORGTYPE = 2
LEFT JOIN PW001P0T email ON email.PIN = p01.PIN
AND email.TELETYPE = 6
AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM PW001P0T t WHERE t.PIN = email.PIN AND t.TELETYPE = email.TELETYPE AND t.TELEPRIORITY IS NOT NULL
AND (t.TELEPRIORITY < email.TELEPRIORITY OR t.TELEPRIORITY = email.TELEPRIORITY AND t.SEQUENCENO < email.SEQUENCENO))
where p01.EmploymentStartDate is not null and p01.EmploymentEnddate is null
and Datediff(yy,p01.employmentstartdate,getdate()) in (5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90) --Years in Service
and Convert(date,dateadd(yy,datediff(yy,p01.employmentstartdate,getdate()),employmentstartdate)) = Convert(date,GetDate())
|
Fields Specification
Field | Description |
|---|---|
#FullName | The full Name of the person. |
#Firstname | The First Name of the person. |
#Middlename | The Middle Name of the person. |
#Lastname | The Last Name of the person. |
#EmploymentStartDate | The Employment Start Date of the person. |
#YearsInService | The number of years worked for the company. |
#AnniversaryDate | The anniversary celebration date date. |
#Company | The employment company from Personal Details. |
The e-mail of the receiver/manager. |
Sample Email
Dear #Firstname,
| Panel | ||
|---|---|---|
| ||
You are a valuable member of our team and contribute to the success of the company year after year. We are very happy to celebrate your #YearsInService work anniversary. Thank you so much for your continuous effort. Happy anniversary! |
| Panel | ||
|---|---|---|
| ||
On the occasion of your #YearsInService work anniversary, we would like to congratulate you and wish you all the best. It is an important milestone for all of us, and we are happy to celebrate together. Happy anniversary and all the best! |
| Panel | ||
|---|---|---|
| ||
On the occasion of your #YearsInService work anniversary, I wish you success in your career. Congratulations on another year with all of us! |
| Panel | ||
|---|---|---|
| ||
You are an example of hard work and loyalty to all of us. After #YearsInService together, you have earned our utmost respect and appreciation. We are proud to have you on our team. Happy anniversary and all our best wishes! |
| Panel | ||
|---|---|---|
| ||
Another year has passed, and once again, we proudly celebrate one of our best employees. You are inspiring, committed, and hardworking. You are an example for your colleagues and managers. We couldn't have wished for a better employee. Happy Work Anniversary and or best wishes for the future! |
Best wishes,
HR Team