| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||||
|---|---|---|---|---|
|
Description
The notification sends out an automatic email that will inform the approver/ coordinator that there are travel expenses waiting for their approval. It sends one e-mail per document. The e-mail is sent out only once when the document is sent for approval. The check for the document state change is performed hourly.
The notification works only with the multiple-level chain setup with any level set up (Rank/ Office, PIN/ Name, or Position/ Vessel)
Selection
All the approvers/ coordinators with documents waiting for approval with an active account in the Employee Portal.
| Code Block |
|---|
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 for 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.
Sample E-mail Output
Hello [#MANAGER_NAME],
This e-mail is to inform you 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: can be modified depending on the customer. It can be:
a fixed list of emails;
or dynamic list retrieved from the database.
Schedule: every 60 min during the day.