The notification informs the crew of the next planned assignment for the next 30 days.
Keywords
Activity, Planned Sign-On
Description
The notification informs the crew of the next planned assignment for the next 30 days.
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 as #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,
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))
Sample E-mail
Hello Mr./Mrs. (depending on Gender) [#LASTNAME],
Please note that your next planned assignment on [#PLATFORM] platforms is as follows:
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.
