Description	Missing date or overlapping dates of Vessel Location for the current payroll period
Summary	Auto generated email from Notification Service that will inform the Captains/Payroll Managers for the missing date or overlapping dates of Vessel Location for the current payroll period.
Keywords	Vessel Location
File / Script Link
Compatibility APM Version	N/A
Compatibility SQL Version	N/A

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 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 script

;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
       LEFT JOIN PWORGTEL TEL
            ON  TEL.NUMORGID = ves.NUMORGID
                AND TEL.TELETYPE = '6'
WHERE  ves.ORGTYPE = 3
       AND ISNULL(ves.PASSIVE ,'')<>'T'

Missing date or overlapping dates of Vessel Location for the current payroll period

Setup

Sample Output

SQL script