Description
The notification sends out an automatic email from Notification Service that will inform the approver/ coordinator that there are documents waiting for their approval.
The notification works for both multiple-level chains and single setups.
Selection
All the approvers/ coordinators with documents waiting for approval.
SQL statement
SELECT wda.APPROVER_PIN,
wda.APPROVER_NAME AS #APPROVER_NAME,
wda.APPROVER_RANK AS #APPROVER_RANK,
wda.APPROVER_EMAIL AS #APPROVER_EMAIL,
COUNT(wda.DOCUMENTS_TO_APPROVE) #DOCUMENTS_TO_APPROVE
FROM (
SELECT (
CASE
WHEN coord.PIN IS NOT NULL THEN coord.PIN
WHEN appr.PIN IS NOT NULL THEN appr.PIN
ELSE ''
END
) AS APPROVER_PIN,
(
CASE
WHEN coord.PIN IS NOT NULL THEN coord.NAME
WHEN appr.PIN IS NOT NULL THEN appr.NAME
ELSE ''
END
) AS APPROVER_NAME,
(
CASE
WHEN coord.PIN IS NOT NULL THEN coord.rank
WHEN appr.PIN IS NOT NULL THEN appr.rank
ELSE ''
END
) AS APPROVER_RANK,
--'julius.marck.velasco@adonishr.com' as #TEST_APPROVER_EMAIL,
email.TELENO APPROVER_EMAIL,
wf.DOCUMENT_ID AS DOCUMENTS_TO_APPROVE,
CASE
WHEN EXISTS(
SELECT 1
FROM WEB_CP_COA_APPROVER_VESSELS AS wccav
WHERE (
wccav.VESSEL_NUMORGID = we.VESSEL_BY_DEPARTURE_DATE
AND wccav.node_sequenceno = nd.sequenceno
)
AND CC.FILTER_TYPE NOT IN ('C')
) THEN 1
WHEN EXISTS(
SELECT 1
FROM WEB_CP_COA_APPROVER_VESSELS wccav2
INNER JOIN [WEB_CP_COA2_TREE] AS ct
ON nd.[NODE_SEQUENCENO] = ct.[NODE_SEQUENCENO]
AND CT.IS_MULTIPLE = 0
)
AND CC.FILTER_TYPE NOT IN ('C') THEN 1
WHEN CC.FILTER_TYPE = 'C'
AND WE.ONBOARD_ACTIVITY IS NOT NULL THEN 1
WHEN we.VESSEL_BY_DEPARTURE_DATE IS NULL THEN 1
ELSE 0
END AS isFiltered
FROM WEB_CP_WORKFLOW wf
LEFT JOIN WEB_CP_DOCUMENTS doc
ON doc.DOCUMENT_ID = wf.DOCUMENT_ID
LEFT JOIN WEB_CP_WDA_EXPENSE we
ON doc.DOCUMENT_ID = we.DOCUMENT_ID
LEFT JOIN WEB_CP_COA2_NODE_DETAILS nd
ON wf.NEXT_NODE = nd.NODE_SEQUENCENO
LEFT JOIN WEB_CP_COA2_TREE ct
ON wf.ORIGINATOR_NODE = ct.NODE_SEQUENCENO
LEFT JOIN [WEB_CP_COA_CHAIN] AS cc
ON ct.[CHAIN_SEQUENCENO] = cc.[SEQUENCENO]
LEFT JOIN PW001P01 coord
ON coord.PIN = nd.PIN
LEFT JOIN PW001P01 appr
ON appr.rank = nd.rank
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 (
ISNULL(COORD.EMPLOYMENTENDDATE, APPR.EMPLOYMENTENDDATE) IS NULL
OR ISNULL(COORD.EMPLOYMENTENDDATE, APPR.EMPLOYMENTENDDATE) > GETDATE()
)
) wda
WHERE wda.IsFiltered = 1
GROUP BY
wda.APPROVER_PIN,
wda.APPROVER_NAME,
wda.APPROVER_RANK,
wda.APPROVER_EMAIL
ORDER BY
wda.APPROVER_PIN
Field Specification
#APPROVER_PIN – PIN of the approver/ coordinator
#APPROVER_NAME – Full name of the approver/ coordinator.
#APPROVER_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 – total count of the documents waiting for approval
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: The script works for Daily and Weekly schedules.