Passports: 7 Months to Expiry (send to the employee and Admin)
- Sigrid Kviteberg
- Helen Ustych
- Daria Chebotaieva
Summary | The notification is sent to the employee and admins when the employee’s passport expires in 7 months. |
|---|---|
Keywords | Passport, Expiry Date |
Description
The notification is sent to the employee and admins when the employee’s passport expires in 7 months.
Sample Task Setup
SQL Statement
--Passports Expiring in 7 months--
select
p01.PIN AS #Pin,
(select stuff((
select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
from (select cast(replace((select p01.FIRSTNAME as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
cross apply T1.X.nodes('text()') as T2(X)
cross apply (select T2.X.value('.', 'varchar(50)')) as T3(V)
for xml path(''), type
).value('text()[1]', 'varchar(50)'), 1, 1, '')) #Firstname,
p0t.TELENO + ',' + 'email1, email2, email3' #Email,
c23.VISATYPE as #DocumentName,
convert (varchar(11),p08.dateto,106) as #ExpiryDate,
case
when p08.dateto = DATEADD(mm,7,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) then 7
end as #Months
from pw001p08 p08
left join PW001P01 p01 on p08.pin = p01.pin
left join pw001p0t p0t on p01.pin = p0t.pin and p0t.teletype=6
join PW001C23 c23 on c23.CODE = p08.CODE
and C23.CODE IN ('PP','PP2')
left join WEB_CP_NOTIFICATION_TASKS lrt
on lrt.sequenceno = 10025168
where p01.employmentenddate is null
AND NOT EXISTS
(
SELECT 1
FROM PW001P08 t
WHERE t.PIN = P01.Pin
AND t.CODE = p08.CODE
AND (
ISNULL(t.DATETO, 0) > ISNULL(p08.DATETO, 0)
OR ISNULL(t.DATETO, 0) = ISNULL(p08.DATETO, 0)
AND t.SEQUENCENO > p08.SEQUENCENO
)
)
--and p08.dateto BETWEEN DATEADD(MM,7,DATEADD(dd, DATEDIFF(dd, 0, lrt.LAST_RUN_TIME), 0)) AND DATEADD(MM, 7, GETDATE())
and p08.dateto = DATEADD(MM,7,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
--and p01.client = 10000067
--and p01.client <> 10000323Sample Email
Hi [#Firstname]
As you know, it is very important that all your documents are valid when joining the ship and throughout your contract. This is a friendly reminder that your [#DocumentName] will expire on [#ExpiryDate].
Please renew your passport and register the renewed document in the Employee Portal as soon as possible.
Add a link to the employee portal.
PS: As a general rule, passports should have at least six months of validity when traveling internationally. Most countries will not permit a traveler to enter their country unless the passport is set to expire at least six months after the final day of travel.
This is an automatically generated email. For any questions, please contact XXX.