Description
The notification informs the Captains/ Payroll Managers of the missing/ overlapping vessel locations within the current payroll period.
Setup
Go to Setup > Notification Service
Add New, give the task a name, 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
;WITH cteSrc AS(
SELECT VESLOC.CODE
,CONVERT(VARCHAR ,VESLOC.STARTDATE ,110) AS STARTDATE
,CONVERT(VARCHAR ,VESLOC.ENDDATE ,110) AS ENDDATE
,C44.[NAME] AS VESLOCNAME
,VES.[NAME] AS VESSELNAME
,VES.NUMORGID AS VESSELNUMORGID
,PAY.COMPANYID
,CMP.[NAME] AS COMPANYNAME
,PAY.PREPARATIONDATE
,PAY.COMPLETIONDATE
,VESPAY.PERIODE
FROM PWVESCOSTPLACE VESLOC
JOIN PW001C44 C44
ON C44.CODE = VESLOC.CODE
JOIN PWORGVES VESPAY
ON VESPAY.NUMORGID = VESLOC.NUMORGID
LEFT JOIN PWORG VES
ON VES.NUMORGID = VESLOC.NUMORGID
JOIN PWCMPPAY PAY
ON PAY.COMPANYID = VES.NUMORGIDABOVE
AND PAY.[PERIOD] = VESPAY.PERIODE
AND (
(
VESLOC.STARTDATE BETWEEN PAY.PREPARATIONDATE AND PAY.COMPLETIONDATE
AND VESLOC.ENDDATE>PAY.COMPLETIONDATE
)
OR (
VESLOC.ENDDATE BETWEEN PAY.PREPARATIONDATE AND PAY.COMPLETIONDATE
AND VESLOC.STARTDATE<PAY.PREPARATIONDATE
)
OR (
VESLOC.STARTDATE<=PAY.PREPARATIONDATE
AND VESLOC.ENDDATE>=PAY.COMPLETIONDATE
)
)
LEFT JOIN PWORG CMP
ON CMP.NUMORGID = PAY.COMPANYID
UNION ALL
--start missing dates
SELECT VESLOC.CODE
,CASE
WHEN VESLOC.STARTDATE IS NULL THEN 'Missing Date'
ELSE CONVERT(VARCHAR ,VESLOC.STARTDATE ,110)
END AS STARTDATE
,CASE
WHEN VESLOC.ENDDATE IS NULL THEN 'Missing Date'
ELSE CONVERT(VARCHAR ,VESLOC.ENDDATE ,110)
END AS ENDDATE
,C44.[NAME] AS VESLOCNAME
,VES.[NAME] AS VESSELNAME
,VES.NUMORGID AS VESSELNUMORGID
,PAY.COMPANYID
,CMP.[NAME] AS COMPANYNAME
,PAY.PREPARATIONDATE
,PAY.COMPLETIONDATE
,VESPAY.PERIODE
FROM PWVESCOSTPLACE VESLOC
JOIN PW001C44 C44
ON C44.CODE = VESLOC.CODE
AND (VESLOC.STARTDATE IS NULL OR VESLOC.ENDDATE IS NULL)
JOIN PWORGVES VESPAY
ON VESPAY.NUMORGID = VESLOC.NUMORGID
LEFT JOIN PWORG VES
ON VES.NUMORGID = VESLOC.NUMORGID
JOIN PWCMPPAY PAY
ON PAY.COMPANYID = VES.NUMORGIDABOVE
AND PAY.[PERIOD] = VESPAY.PERIODE
LEFT JOIN PWORG CMP
ON CMP.NUMORGID = PAY.COMPANYID
)
SELECT (
(
SELECT STARTDATE+CHAR(9)+' '+ENDDATE+' '+CHAR(9)+VESLOCNAME+' '+CHAR(10)
FROM cteSrc z
WHERE z.VESSELNUMORGID = ves.NUMORGID
ORDER BY
STARTDATE
FOR XML PATH('')
,TYPE
).value('.' ,'varchar(max)')
) AS #DETAILS
,(
SELECT DISTINCT PERIODE
FROM cteSrc z
WHERE z.VESSELNUMORGID = ves.NUMORGID
) AS #CurrentPeriod
,ves.[NAME] AS #VesselName
,tel.teleno AS #Email
FROM PWORG ves
JOIN PWORGTEL TEL
ON TEL.NUMORGID = ves.NUMORGID
AND TEL.TELETYPE = '6'
WHERE ves.ORGTYPE = 3
AND ISNULL(ves.PASSIVE ,'')<>'T'