Summary	The task fetches employees when the Travel Expense has been approved or rejected. The notification is sent each hour.
Keywords	Employee Portal, Travel Expense, Approved/ Rejected

Description

The notification task sends out an automatic email to employees when the Travel Expense has been approved or rejected. The notification is sent each hour.

Main data selection

All the persons whose travel expense has been approved or rejected within the last hour.

SQL Statement

select
	ORIGINATOR.FIRSTNAME #FIRSTNAME,
	DOC.DOCUMENT_ID #DOCUMENT_ID,
	convert (date, TRAVEL.TRAVEL_DATETIME_OUT) #DATEFROM,
	convert (date, TRAVEL.TRAVEL_DATETIME_IN) #DATETO,
	WF.STATE_NAME #STATE,
	APPROVER.NAME #APPROVER_NAME,
	email.teleno #EMAIL
	--'daria.chebotaieva@adonishr.com' #Email
from WEB_CP_DOCUMENTS DOC
left join WEB_CP_WORKFLOW WF on WF.DOCUMENT_ID=DOC.DOCUMENT_ID
left join WEB_CP_TRAVEL_BILLS TRAVEL on TRAVEL.DOCUMENT_ID=DOC.DOCUMENT_ID
left join PW001P01 ORIGINATOR on ORIGINATOR.PIN=DOC.PIN
left join PW001P01 APPROVER on APPROVER.PIN=WF.APPROVER_PIN
LEFT JOIN PW001P0T email
            ON  email.PIN = ORIGINATOR.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 
	DOC.CODE = 'TravInv'
	AND WF.STATE_NAME IN ('Approved', 'Rejected') 
	AND WF.STATE IN (2, 3)
	AND WF.repl_GMTModifiedDate > DATEADD (HOUR , -1, dateadd(MINUTE,-1,GETDATE()))

Field Specification

#FIRSTNAME- the first name of the person from the Personal Details.

#DOCUMENT_ID - the ID of the Travel Expense document that was either approved or rejected.

#DATEFROM - start date of the Travel Expense document.

#DATETO - end date of the Travel Expense document.

#STATE - document state: approved/ rejected.

#APPROVER_NAME - full name of the approver.

#EMAIL- e-mail address of the person from the Personal Details. The e-mail with the highest priority is taken.

Sample E-mail Output

Hello [#FIRSTNAME],

This e-mail is to inform that your document [#DOCUMENT_ID] for the period [#DATEFROM] - [#DATETO] has been [#STATE] by the manager [#APPROVER_NAME].

For more details connect to the portal:

Best regards,
Daria

Other

Recipients can be modified depending on the customer. It can be:

a fixed list of emails;
or dynamic list retrieved from the database.

Schedule: Hourly, every 60 minutes.

Reports, Views and Similar

Travel Expense: Approved/ Rejected (per hour)