Description
The notification informs the crew of the next planned assignment for the next 30 days.
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.
Sample Output
SQL Statement
SELECT 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
,P0T.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
JOIN PW001P0T P0T
ON P0T.PIN = P01.PIN
AND P0T.TELETYPE = '6'
AND P0T.TELEPRIORITY = '1'