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