Skip to end of banner
Go to start of banner

Travel Expense: Reminder For Approval per person (multiple level)

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

« Previous Version 2 Next »

Description

Reminder For Approval per person (multiple level)

Summary

Auto generated email from Notification Service that will inform the approver/ coordinator that there are travel expense waiting their approval.

Keywords

Travel Expense, Approver

Compatibility APM Version

N/A

Compatibility SQL Version

N/A

Description

The notification send out an automatic email from Notification Service that will inform the approver/ coordinator that there are travel expense waiting their approval. It sends one one e-mail per document. The e-mail is send out only once when the document is send for approval. The check for the document state change is performed hourly.

The notification works only with the multiple level chain setup with “Documents (vessel link)” option ON when either coordinator or approver level is set by PIN/ Name or Position/ Vessel .

Selection

All approvers that have some documents waiting approval with active account in the Employee Portal.

select 
	doc.DOCUMENT_ID #DOCUMENT_ID, 
	wf.ORIGINATOR_PIN #CREW_PIN,
	wf.STATE_NAME #STATUS,
	(case  
		when coord.FIRSTNAME is NOT NULL THEN coord.FIRSTNAME
		when appr.FIRSTNAME is NOT NULL THEN appr.FIRSTNAME
		ELSE ''
	END) as #MANAGER_NAME,
    --'daria.chebotaieva@adonishr.com' #TEST_APPROVER_EMAIL
	email.TELENO #APPROVER_EMAIL 
	/* , wf.STEP,
	nd.PIN COORDINARO_PIN,
	coord.NAME COORDINATOR_NAME,
	nd.RANK APPROVER_RANK,
	nd.DESCRIPTION CURRENT_NODE_TITLE,
	docves.NAME as DOCUMENT_VESSEL,
	apprves.NAME CHAIN_NODE_VESSEL */
from WEB_CP_DOCUMENTS doc
left join WEB_CP_TRAVEL_BILLS tb on doc.DOCUMENT_ID=tb.DOCUMENT_ID
left join PWORG docves on tb.VESSEL=docves.NUMORGID
left join WEB_CP_WORKFLOW wf on doc.DOCUMENT_ID=wf.DOCUMENT_ID
left join WEB_CP_COA2_NODE_DETAILS nd on wf.NEXT_NODE=nd.NODE_SEQUENCENO
left join WEB_CP_COA_APPROVER_VESSELS av on av.NODE_SEQUENCENO=nd.SEQUENCENO
left join PWORG apprves on av.VESSEL_NUMORGID=apprves.NUMORGID
left join PW001P01 coord on coord.PIN=nd.PIN
left join PW001P01 appr on appr.rank=nd.rank
right join WEB_CP_ACCOUNT_EMPLOYEE acc on acc.pin = appr.pin
LEFT JOIN PW001P0T email
            ON  email.PIN in (coord.PIN, appr.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 
	wf.step =(select max(t.STEP) from WEB_CP_WORKFLOW t where t.DOCUMENT_ID=wf.DOCUMENT_ID group by t.DOCUMENT_ID)
	and wf.state=1
	and tb.VESSEL=av.VESSEL_NUMORGID
	and wf.repl_ModifiedDate > DATEADD (HOUR , -1, dateadd(MINUTE,-1,GETDATE()))

Field Specification

#DOCUMENT_ID - ID of the document waiting approval.

#CREW_PIN - ID of the originator PIN.

#STATUS - status of the document (submitted).

#MANAGER_NAME - full name of the approver/ coordinator.

#APPROVER_EMAIL - e-mail address of the approver/ coordinator.

#TEST_APPROVER_EMAIL - the e-mail to be used for testing purposes instead of the #APPROVER_EMAIL, the condition should be un-commented.

E-mail sample:

Hello [#MANAGER_NAME],

This e-mail is to inform that the document [#DOCUMENT_ID] for [#CREW_PIN] has been [#STATUS] for approval.

For more details connect to the portal: http://…/employee.

Best regards,

Task Parameters

Recipients: Notification’s recipient can be modified depending on the customer. Can be fixed email list or retrieves from the database.

Schedule: Weekly, during workdays, every 60 min during the day.

  • No labels