--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
) |
