Versions Compared

Version Old Version 3 New Version 4
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 e-mail is to be sent to the crew members whose timesheet documents were approved by the manager and are pending signing by the crew.

Keywords

Timesheet, E-signing

Category

Notification Task

Description

The notification e-mail is to be sent to the crew members whose timesheet documents were approved by the manager and are pending signing by the crew.

Main Data Selection

All crew members whose timesheets are pending signing.

Table of Contents
minLevel1
maxLevel2
separatorpipe

Setup and Configuration

Task settings

Schedule: Daily, repeat every 30 mins from 7 - 23.59.
Note: The timesheets signed from 00:00 to 07:00 will be included in the first run at 07:00.

Recipient: crew member.

Field identifying e-mail address: #Email

E-mail Subject: Your timesheet for [#PERIOD] is ready to be signed

SQL statement

Expand
titleSQL statement
Code Block
--Send email to crew When Timesheet is ready to be signed
--AHSD2656 Modified SQL Condition
--AHSD-6217 Removed WEB_CP_ACCOUNT
SET ARITHABORT ON
SELECT DISTINCT
       p.firstname                           #FIRSTNAME,
       p.LASTNAME                            #LASTNAME,
       te.originator_pin                     #PIN,
       LOWER(te.STATE_NAME)                  #STATE,
       per.name                              #PERIOD,
       --convert (varchar, Isnull(te.MODIFIED,te.repl_ModifiedDate), 104) #UPDATED,
       CONVERT(VARCHAR, t.DATEFROM, 104)     #FROM,
       CONVERT(VARCHAR, t.dateto, 104)       #TO,
       --'martin.kviteberg@adonis.no' #Email
       ISNULL(
           CASE 
                WHEN email.TELENO = '' THEN NULL
                ELSE email.TELENO
           END,
           a.EMAIL
       )                                     #Email
FROM   pw001p01 p
       JOIN (
                SELECT --Ready to be signed
                       ORIGINATOR_PIN,
                       ORIGINATOR_NODE,
                       APPROVER_NODE,
                       MODIFIED,
                       repl_ModifiedDate,
                       STATE_NAME,
                       DOCUMENT_ID,
                       COMMENT
                FROM   WEB_CP_WORKFLOW wf
                WHERE  wf.STATE = 8
                       AND wf.CODE = 'HrsReg'
                       AND wf.step = (
                               SELECT MAX(w2.step)
                               FROM   WEB_CP_WORKFLOW w2
                               WHERE  w2.document_id = wf.document_id
                           )
            ) te
            ON  p.PIN = te.ORIGINATOR_PIN
       LEFT JOIN WEB_CP_TIMESHEETS t
            ON  t.DOCUMENT_ID = te.DOCUMENT_ID
       LEFT JOIN WEB_CP_TIMESHEETS_PERIOD per
            ON  per.SEQUENCENO = t. SEQUENCENO
       LEFT JOIN WEB_CP_ACCOUNT_CREW a
            ON  a.PIN = te.ORIGINATOR_PIN
       LEFT JOIN PW001P0T email
            ON  email.PIN = p.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
                               )
                )
WHERE  (
           ISNULL(te.repl_ModifiedDate, te.MODIFIED) > DATEADD(MINUTE, -30, GETDATE())
           --consolidate all timesheets approved between 00:00 - 07:00. So when the notification runs at 07:00, script consolidates it and notify the crews accordingly.
           OR (
                  ISNULL(te.repl_ModifiedDate, te.MODIFIED) BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND DATEADD(hh, 7, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
                  AND GETDATE() BETWEEN DATEADD(hh, 7, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AND DATEADD(
                          MINUTE,
                          30,
                          DATEADD(hh, 7, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
                      )
              )
       )

Field Specification

Field

Description

#Firstname

The First Name of the person.

#Lastname

The Last Name of the person.

#PIN

PIN number of the person.

#State

The status of the timesheet document.

#Period

The timesheet period.

#From

The date from of the timesheet document.

#To

The date to of the timesheet document.

#Email

The e-mail of the crew memeber.

Sample Email

Hi [#Firstname],

Your timesheet for the period [#PERIOD]
from [#FROM] to [#TO] is ready to be signed in the Employee Portal.

Click here to log in to the portal.

PS: If you have not yet registered your account in the portal, click on First Time Login. You will need your PIN to register your account. Your PIN is [#PIN]

Once you are logged in to the portal:

  1. Click on Electronic Signing > Pending.

  2. Click on the timesheet, and select Open to Sign.

  3. If it is your first time using the electronic signing module, click Add Signature. Add your signature by either typing your name (recommended), drawing your signature, or uploading an image of your signature.

  4. Locate the signature field on the timesheet, click on the timesheet, and drag the mouse cursor to add your signature, as shown below.

  5. Finally, click Sign.

This is an automatically generated email. For any questions, please contact your crew coordinator.

Best regards,
HR Team