Competence, Travel and Medical expiry notification: 3 and 1 Months to Expiry
Summary | This notification task sends out an expiry notification to the Employee and the Admin User when there are 3 or 1 month before the expiry of Competence, Medical and Travel records |
|---|---|
Keywords | Competence, Travel, Medical, Documents, Expiry Date |
Description
Documents: 3 or 1 Month to Expiry notification works in the following way:
Rule: To send a reminder to a seafarer whose competence, travel and medical documents expire in 3 or 1 month. If there is a record with the same code but a greater expiry date, the record with the earlier expiry date will be ignored.
Selection: Employed seafarers whose competence, travel and medical documents expire in 3 or 1 month.
Recipient: Seafarer's email;
Schedule: Daily
Sample setup
SQL Statement
SELECT
p.PIN,
(select stuff((
select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
from (select cast(replace((select p.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 #Email,
Doc.Doc as #DocumentName,
convert (varchar(11),IsNull(doc.DATETO,doc.EXPIRYDATE),106) as #ExpiryDate,
case
when IsNull(doc.DATETO,doc.EXPIRYDATE) = DATEADD(mm,1,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) then '1 Month'
when IsNull(doc.DATETO,doc.EXPIRYDATE) = DATEADD(mm,3,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) then '3 Months'
end as #Months,doc.TYPE
FROM PW001P01 p
JOIN PW001P0T P0T ON ((p.PIN = P0T.PIN) AND (P0T.TELETYPE = 6)
AND (NOT P0T.TELEPRIORITY IS NULL)
AND NOT EXISTS
(SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
JOIN (
--Travel
select
PIN,
'Travel' as [TYPE],
t.CODE,
tc.[VISATYPE] as [Doc],
DATETO,
DATETO as [EXPIRYDATE],
EXPIRYNOTIFICATIONDATE
from PW001P08 t
join PW001C23 tc on t.CODE = tc.CODE and tc.OPTIONS not like '%P%'
Where
Convert(date, IsNull(t.DATEFROM,GetDate())) <= Convert(date,GetDate())
and t.DATETO =
(Select MAX(q.DATETO)
From PW001P08 q
Where t.PIN = q.PIN
and t.CODE = q.CODE
and Convert(date, IsNull(q.DATEFROM,GetDate())) <= Convert(date,GetDate()))
--Competence
UNION ALL
select
PIN,
'Competence' as [TYPE],
c.CODE,
cc.[TEXT] as [Doc],
DATETO,
EXPIRYDATE,
EXPIRYNOTIFICATIONDATE
from PW001P05 c
join PW001C06 cc on c.CODE = cc.CODE and cc.OPTIONS not like '%P%'
where
c.CODE = dbo.ad_ReturnReplacingCode(c.PIN, c.CODE, 0) and /*Check only root cert ignoring replacing ones*/
Convert(date, IsNull(c.DATEFROM,GetDate())) <= Convert(date,GetDate())
and IsNull(c.EXPIRYDATE,c.DATETO) =
(Select MAX(IsNull(t.EXPIRYDATE,t.DATETO))
From PW001P05 t
Where t.PIN = c.PIN
and t.CODE = c.CODE
and Convert(date, IsNull(t.DATEFROM,GetDate())) <= Convert(date,GetDate()))
--Medical
UNION ALL
select
PIN,
'Medical' as [TYPE],
m.CODE,
mc.[TEXT] as [Doc],
EXPIRYDATE as [DATETO],
EXPIRYDATE,
EXPIRYNOTIFICATIONDATE
from PW001P07 m
join PW001C24 mc on m.CODE = mc.CODE and mc.OPTIONS not like '%P%'
Where
Convert(date, IsNull(m.DATEISSUED,GetDate())) <= Convert(date,GetDate())
and m.EXPIRYDATE =
(Select MAX(t.EXPIRYDATE)
From PW001P07 t
Where t.PIN = m.PIN
and t.CODE = m.CODE
and Convert(date, IsNull(t.DATEISSUED,GetDate())) <= Convert(date,GetDate()))
) Doc
on Doc.PIN = p.PIN
and (Convert(date,Doc.EXPIRYNOTIFICATIONDATE) = Convert(date, GetDate())
or
(IsNull(doc.DATETO,doc.EXPIRYDATE) = DATEADD(mm,1,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
or IsNull(doc.DATETO,doc.EXPIRYDATE) = DATEADD(mm,3,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)))
)Sample 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 document and register it in the Employee Portal as soon as possible.
Add link to the employee portal.
This is an automatically generated email. For any questions, please contact xxx