| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||||
|---|---|---|---|---|
|
Description
Sends The notification task sends out an email to selected email addresses when an the employment anniversary is coming up.comes.
The notification is sent to the manager 30 days before the anniversary.
The anniversary years considered 5,10,15,20,25,30,35,40,45,50,55,60,65,70
Sample Set Up
...
SQL Statement
| Code Block |
|---|
DecDeclareDeclare @Days INT = 30 --Notify before the date of Anniversary SELECT P01.PIN, p01.NAME as #Name#FullName, p01.FIRSTNAME as #Firstname, p01.LASTNAME as #Lastname, p01.EMPLOYMENTSTARTDATE as #EmploymentStartDate, (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000 as #YearsInService, CONVERT(VARCHAR(15),DATEADD(YYYY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000, DATEDIFF(day, 0, p01.employmentstartdate)),107) #AnniversaryDate, org.Name as #Company, -- email.TELENO as #Email 'sigrid.kviteberg@adonis.no,camilla.emmerhoff@adonis.no,per.ove.kviteberg@adonis.notest.e-mail' as #Email FROM PW001P01 P01 Join PWORG org on p01.CLIENT = org.NUMORGID and org.ORGTYPE = 2 --and org.NUMORGID in (23,10000606,10000607,10000643,10000648,10000649,10000650,10000662,10000668,10000669) --Include the Numorgid of the companies 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 (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000 in (5,10,15,20,25,30,35,40,45,50,55,60,65,70) --Years in Service and DATEADD(YY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000, DATEDIFF(day, 0, p01.employmentstartdate)) = DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())) and p01.EMPLOYMENTENDDATE is null |
Fields Specification
Field | Description |
|---|---|
#FullName | The full Name of the person. |
#Firstname | The First 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 Receiver Name,
Please be informed that [#Firstname] [#Lastname] will have a [#YearsInService] years Employment Anniversary coming up on the [#AnniversaryDate]
...