Skip to end of banner
Go to start of banner

Travel Expense: Approved/ Rejected (per hour)

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

Version 1 Next »

Description

Approved/ Rejected Travel Expense (per hour)

Summary

Auto generated email from Notification Service that will inform the person that their travel expense has been approved or rejected. The notification is send each hour.

Keywords

Travel Expense

Compatibility APM Version

N/A

Compatibility SQL Version

N/A

Description

This is an auto generated notification that will inform the person that their travel expense has been approved or rejected. The notification is send each hour.

Selection

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

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
	'christina.kvalsund@olympic.no' #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_ModifiedDate > DATEADD (HOUR , -1, dateadd(MINUTE,-1,GETDATE()))

Field Specification

#FIRSTNAME- 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 - strat 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

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: affected crew.
Can be set to a fixed email list.

Schedule: Hourly, every 60 minutes.

  • No labels