Versions Compared

Version Old Version 3 New Version 4
Changes made by

Daria Chebotaieva

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Page Properties

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

Schedule: Daily, repeat every day.

Recipient: crew member/employee.

SQL Statement

Expand
titleSQL 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.

#Email

The e-mail of the receiver/manager.

Sample Email

Dear #Firstname,

Panel
bgColor#EAE6FF

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
bgColor#DEEBFF

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
bgColor#E6FCFF

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

Panel
bgColor#E3FCEF

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
bgColor#FFFAE6

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