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