Notification task for HACCP competence
- Arvin John Salandanan
- Daria Chebotaieva
- Sigrid Kviteberg
Summary | The notification tasks checks for the crew with HACCP competence missing/ expiring in the next 90 days. |
|---|---|
Keywords | HACCP competence |
Description
The notification task sends out an automatic e-mail informing the seafarer if they have a HACCP competence that expires in the next 90 days or if the seafarer is missing the certificate according to the requirements profile so that they can renew the certificate in order to join the vessel with valid documents.
Seafarers must have existing offshore activity in the last 6 months.
Setup
Go to Setup > Notification Service.
Add New, give the task a name, and define time and date settings. For more details on the settings, please check Notification Service#ScheduleTask.
Under the Notification E-mails tab, define the Sender email and name. Recipients will be picked up dynamically from the database. An example of a selection that can be used is shown below, please check with Adonis Support or Project Consultants if you can use the same selection.
Insert Email Subject and Body. Note that you can insert screenshots, ,links and signatures to the mail body.
SQL Statement
;WITH reqdoc AS(
SELECT c02.NAME RankName
,c02.CODE RankCode
,c06.CODE CertCode
,c06.[TEXT] CertName
FROM PW001POSDOC POSDOC
LEFT JOIN PW001C02 C02
ON C02.CODE = POSDOC.POSITION
LEFT JOIN PW001POSDOC_LINK DLINK
ON DLINK.POSITION = POSDOC.POSITION
LEFT JOIN PW001C06 C06
ON DLINK.DOCUMENT = C06.CODE
WHERE c06.CODE LIKE '%haccp%'
),
records AS(
SELECT p01.pin
,p01.NAME
,p01.rank
,p05.code
FROM pw001p01 p01
LEFT JOIN pw001p05 p05
ON p05.pin = p01.pin
AND p05.code LIKE '%haccp%'
WHERE p01.employmentenddate IS NULL
),
cteReq1 AS (
SELECT a.PIN
,a.NAME AS SeafarerName
,'Missing ' AS ExpiryDate
,req.CertName
FROM records a
JOIN reqdoc req
ON req.rankcode = a.rank
AND (req.certcode<>a.code OR a.code IS NULL)
WHERE EXISTS (
SELECT 1
FROM pw001p03
WHERE code = '1'
AND DATEFROM>DATEADD(m ,-6 ,DATEADD(DAY ,DATEDIFF(DAY ,0 ,GETDATE()) ,0))
AND pin = a.pin
)
),
cteReq2 AS(
SELECT p01.PIN
,p01.[NAME] AS SeafarerName
,CONVERT(VARCHAR ,P05.EXPIRYDATE ,103) AS ExpiryDate
,c06.text AS CertName
FROM pw001p01 p01
JOIN pw001p05 p05
ON p05.pin = p01.pin
AND p05.code LIKE '%haccp%'
JOIN pw001c06 c06
ON c06.code = p05.code
AND p05.expirydate BETWEEN GETDATE() AND DATEADD(d ,90 ,DATEADD(DAY ,DATEDIFF(DAY ,0 ,GETDATE()) ,0))
WHERE EXISTS (
SELECT 1
FROM pw001p03
WHERE code = '1'
AND DATEFROM>DATEADD(m ,-6 ,DATEADD(DAY ,DATEDIFF(DAY ,0 ,GETDATE()) ,0))
AND pin = p01.pin
)
),
cteFinal AS(
SELECT *
FROM cteReq1
UNION ALL
SELECT *
FROM cteReq2
)
SELECT (
SELECT ExpiryDate+CHAR(9)+' '+CertName+' '+CHAR(10)
FROM cteFinal z
WHERE z.pin = a.pin
ORDER BY
a.pin
FOR XML PATH('')
)
#DETAILS
,a.PIN AS #PIN
,p01.firstname+' '+p01.lastname AS #SeafarerName
,p0t.TELENO AS #Email
FROM pw001p01 p01
JOIN cteFinal a
ON a.PIN = p01.PIN
LEFT JOIN PW001P0T P0T
ON P0T.PIN = a.PIN
AND P0T.TELETYPE = '6'
AND P0T.TELEPRIORITY = 1