1 Description | 2 Setup and Configuration

Summary	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.

Best regards,

Adonis HR Reports, Views and Similar

Next Planned Assignment for the Next 30 days

Description

Setup and Configuration

Task Settings

SQL Statement

Sample E-mail

Related content