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.

1 Description
2 Main Data Selection
3 Setup and Configuration
- 3.1 Task settings
- 3.2 SQL statement
- 3.3 Field Specification
- 3.4 Sample Email

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

--Send email to crew When Timesheet is ready to be signed
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,
       --'test.email' #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

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:

Click on Electronic Signing > Pending.
Click on the timesheet, and select Open to Sign.
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.
Locate the signature field on the timesheet, click on the timesheet, and drag the mouse cursor to add your signature, as shown below.
Finally, click Sign.

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

Best regards,
HR Team

Reports, Views and Similar

Timesheet: Pending Signature