1 Description | 2 Setup and Configuration

Summary	The notification task sends out an email to the crew member when the employment anniversary comes.
Keywords	Employment, Anniversary, Seniority

Description

The notification task sends out an email to the crew member when the employment anniversary comes.

The anniversary years: 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90.

Setup and Configuration

Task settings

Schedule: Daily, repeat every day.

Recipient: crew member/employee.

SQL Statement

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

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.
#Email	The e-mail of the receiver/manager.

Sample Email

Dear #Firstname,

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!

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!

On the occasion of your #YearsInService work anniversary, I wish you success in your career. Congratulations on another year with all of us!

Best wishes,
HR Team

Adonis HR Reports, Views and Similar