Versions Compared

Version Old Version 9 New Version Current
Changes made by

Daria Chebotaieva

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Description

Reminder For Approval per total count (multiple level)

Summary

Auto generated email from Notification Service that will inform the approver/ coordinator that there are travel expense
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Page Properties

Summary

The task to inform approvers/ coordinators if there are any travel expense documents waiting their approval.

Keywords

Employee Portal, Travel Expense,

Approver

Compatibility APM Version

N/A

Compatibility SQL Version

N/A

Waiting Approval

Description

The notification send sends out an automatic email from Notification Service that will inform the approver/ coordinator that there are travel expense expenses waiting for their approval. The e-mail is send sent out weekly on the selected day and provides the total number of the documents waiting for approval.

The notification works only with the multiple level chain setup with any level set up (Rank/ Office, PIN/ Name or Position/ Vessel)

Image Removed

Excerpt
hiddentrue

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

Image RemovedImage Removed

Selection

All approvers

Main data selection

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

SQL Statement

Expand
titleSQL Statement
Code Block
select 
	(case  
		when coord.PIN is NOT NULL THEN coord.PIN
		when appr.PIN is NOT NULL THEN appr.PIN
		ELSE ''
	END) as PIN,
	(case  
		when coord.PIN is NOT NULL THEN coord.NAME
		when appr.PIN is NOT NULL THEN appr.NAME
		ELSE ''
	END) as #MANAGER_NAME,
	(case  
		when coord.PIN is NOT NULL THEN coord.rank
		when appr.PIN is NOT NULL THEN appr.rank
		ELSE ''
	END) as #MANAGER_RANK
, 'christina.kvalsund@olympic.no' as #TEST_APPROVER_EMAIL
,
    --'daria.chebotaieva@adonishr.com' as #TEST_APPROVER_EMAIL,
--
email.TELENO #APPROVER_EMAIL,
    count (wf.DOCUMENT_ID) as #DOCUMENTS_TO_APPROVE
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

group by coord.NAME, appr.NAME, wf.ORIGINATOR_PIN, coord.rank, appr.rank, coord.PIN, appr.PIN, email.TELENO

Field Specification

#PIN - ID of the approver/ coordinator.

#MANAGER_NAME - full name of the approver/ coordinator.

#MANAGER_RANK - rank 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.

#DOCUMENTS_TO_APPROVE - count of the documents waiting for approval;

Sample E-mail

sample:

Output

Hello [#MANAGER_NAME],

This e-mail is to inform you that you have [#DOCUMENTS_TO_APPROVE] persons waiting for the travel expenses 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 It can be:

  • a fixed

email

  • list

or retrieves

  • of emails;

  • or dynamic list retrieved from the database.

Schedule: Weekly, on the selected week days weekdays and at the selected time.