Versions Compared

Version Old Version 7 New Version Current
Changes made by

Daria Chebotaieva

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Page Properties

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

Expand
titleSQL Statement
Code Block
languagesql
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,