The notification task informs the crew once the planned assignment is confirmed.
Keywords
Crew Change, Assignment
Description
The notification task informs the crew once the planned assignment is confirmed.
Setup and Configuration
Task Settings
Schedule: Daily, every day.
Recipients: affected crew. Can be set to a fixed email list.
SQL Statement
SELECT p01.PIN,
CASE
WHEN P01.SEX='M' THEN 'Mr. '+P01.LASTNAME
WHEN P01.SEX='F' THEN 'Mrs. '+P01.LASTNAME
ELSE 'Mr/Mrs. '+P01.LASTNAME
END AS #LASTNAME,
ves.[NAME] AS #VESSELNAME,
VES.NUMORGID,
p03.DATEFROM AS #DATEFROM,
p03.DATETO AS #DATETO,
p03.TODATEESTIMATED AS #EED,
p03.SEQUENCENO,
pf.[NAME] AS #PLATFORM,
email.TELENO AS #EMAIL
FROM PW001P01 P01
JOIN PW001P03 P03 ON P03.PIN = P01.PIN
AND ISNULL(P03.PLANNED ,'N') = 'Y'
--AND P03.DATEFROM = DATEADD(DAY ,DATEDIFF(DAY ,0 ,GETDATE()) ,30)
AND P03.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')
LEFT JOIN (SELECT NUMORGID,dbo.ad_scanorgtree(NUMORGID ,3) AS VesselID FROM pworg) ou ON ou.NUMORGID = p03.NUMORGID
LEFT JOIN pworg ves ON ves.NUMORGID = ou.VesselID
LEFT JOIN PWCTCCMP pf ON pf.COMPANYID = ves.NUMORGID
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 EXISTS (
SELECT PRT.SEQNO
FROM PWORGVESACT PRT
JOIN PWCCMCHANGECREWLIST CCL
ON CCL.SEQNO = PRT.SEQNO
AND CCL.SIGNON = 1
AND CCL.CHANGEDONE = 0
AND Convert(date,CCL.repl_ModifiedDate) = Convert(date,GetDate())
JOIN PW001P01 P
ON P.PIN = CCL.PIN
WHERE P.PIN = P01.PIN
)
Sample E-mail
Hello [#LASTNAME],
Please note that your next planned assignment on [#PLATFORM] platforms was confirmed. Details can be found below:
Platform name: [#VESSELNAME]
Departure date: [#DATEFROM]
"Go offshore" date: [#DATETO]
Sign-off date: [#EED]
Please let us know as soon as possible in case there is any concern regarding the above planning.
