The notification task sends out an automatic email to employees when the Travel Expense has been approved or rejected.
Selection
All persons whose travel expense has been approved or rejected.
SQL Statement
--Send email to employee When Travel Expense has been approved and ready to process
SET ARITHABORT ON
SELECT p.firstname #FIRSTNAME
,p.LASTNAME #LASTNAME
,b.DOCUMENT_ID
,LOWER(te.STATE_NAME) #STATE
,CONVERT(VARCHAR ,ISNULL(te.MODIFIED ,te.repl_ModifiedDate) ,104) #UPDATED
,p.FIRSTNAME AS #SECOND
,b.LOCATION #LOCATION
,CONVERT(VARCHAR ,b.TRAVEL_DATETIME_OUT ,104) #FROM
,CONVERT(VARCHAR ,b.TRAVEL_DATETIME_IN ,104)#TO
,--Format(b.TRAVEL_DATETIME_OUT,'MMMM dd, yyyy HH:mm ') #FROM,
--Format(b.TRAVEL_DATETIME_IN,'MMMM dd, yyyy HH:mm') #TO,
--'martin.kviteberg@adonis.no' #Email,
ISNULL(
CASE
WHEN email.TELENO='' THEN NULL
ELSE email.TELENO
END
,a.EMAIL
)+','+
'martin.kviteberg@adonis.no' #Email
,CASE
WHEN te.STATE_NAME='Approved' THEN 'er blitt godkjent og vil bli betalt ut på neste lønn.'
WHEN te.STATE_NAME='Rejected' THEN 'er avvist.'+CASE
WHEN te.COMMENT IS NOT NULL AND te.COMMENT<>'' THEN
' Kommentar: "'+te.COMMENT+'"'
ELSE 'Kontakt Heidi Medhust dersom du har spørsmål.'
END
END AS #Status
,CASE
WHEN te.STATE_NAME='Approved' THEN 'Godkjent'
WHEN te.STATE_NAME='Rejected' THEN 'Avvist'
ELSE 'Avvist'
END AS #Subject
FROM pw001p01 p
JOIN (
SELECT --Approved and ready to process
ORIGINATOR_PIN
,ORIGINATOR_NODE
,APPROVER_NODE
,MODIFIED
,repl_ModifiedDate
,STATE_NAME
,DOCUMENT_ID
,COMMENT
FROM WEB_CP_WORKFLOW wf
WHERE wf.STATE = 2
AND wf.CODE = 'TravInv'
AND NOT EXISTS (
SELECT 1
FROM WEB_CP_WORKFLOW t
WHERE t.DOCUMENT_ID = wf.DOCUMENT_ID
AND t.step>wf.STEP
AND t.STATE IN (2 ,3 ,4 ,5 ,6)
)
UNION ALL
SELECT --Rejected to the employee level
ORIGINATOR_PIN
,ORIGINATOR_NODE
,APPROVER_NODE
,MODIFIED
,repl_ModifiedDate
,STATE_NAME
,DOCUMENT_ID
,COMMENT
FROM WEB_CP_WORKFLOW wf
WHERE wf.STATE = 3
AND wf.CODE = 'TravInv'
AND wf.ORIGINATOR_NODE = wf.NEXT_NODE
AND NOT EXISTS (
SELECT 1
FROM WEB_CP_WORKFLOW t
WHERE t.DOCUMENT_ID = wf.DOCUMENT_ID
AND t.step>wf.STEP
AND t.STATE IN (1 ,2 ,3 ,4 ,5 ,6)
)
) te
ON p.PIN = te.ORIGINATOR_PIN
LEFT JOIN WEB_CP_TRAVEL_BILLS b
ON b.DOCUMENT_ID = te.DOCUMENT_ID
LEFT JOIN WEB_CP_ACCOUNT 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.MODIFIED ,te.repl_ModifiedDate)>(
SELECT LAST_RUN_TIME_EXECUTION_LT
FROM WEB_CP_NOTIFICATION_TASKS
WHERE SEQUENCENO = 10000574
)
Field Specification
All details of Travel Expense, status: approved/rejected.
Sample E-mail Output
Hi [#Firstname],
Your expense report for the travel[#LOCATION] - [#FROM] — [#TO] [#Status] Click here if you want to log in to the portal.
This is an automatically generated email. For any questions, please contact your crewing coordinator.
Other
Recipients can be modified depending on the customer. It can be:
