Description
The notification task informs the crew once the planned assignment is confirmed.
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.
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
,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'
WHERE EXISTS (
SELECT 1
FROM PWORGVESACT PRT
JOIN PWCCMCHANGECREWLIST CCL
ON CCL.SEQNO = PRT.SEQNO
AND CCL.SIGNON = 1
AND CCL.CHANGEDONE = 0
AND DATEADD(DAY ,DATEDIFF(DAY ,0 ,CCL.repl_ModifiedDate) ,0) = DATEADD(DAY ,DATEDIFF(DAY ,0 ,GETDATE()) ,0)
JOIN PW001P01 P
ON P.PIN = CCL.PIN
WHERE P.PIN = P01.PIN
)