Description
The notification sends out an automatic email from Notification Service that will inform the approver/ coordinator that there are travel expenses waiting for their approval. The e-mail is sent out weekly on the selected day and provides the total number of documents waiting for approval.
Selection
All the approvers/ coordinators that have some documents waiting for approval with an active account in the Employee Portal.
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,
--'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 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: can be modified depending on the customer. It can be:
a fixed list of emails;
or dynamic list retrieved from the database.
Schedule: Weekly, on the selected weekdays and at the selected time.
