The task to inform approvers/ coordinators if there are any Working Days Accounting documents rejected back to their level.
Keywords
Employee Portal, Working Days Accounting, Rejected
Category
Notification Task
Description
The notification task sends out an automatic email from Notification Service that will inform the approver/ coordinator that there are rejected documents waiting for their action.
The notification works for both multiple-level chains and single setups.
Selection
All the approvers/ coordinators with rejected documents.
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_REJECTED) #DOCUMENTS_REJECTED
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.ORIGINATOR_NODE,
wf.DOCUMENT_ID AS DOCUMENTS_REJECTED,
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')
)
AND (ct.IS_COORDINATOR = 1 OR ct.IS_APPROVER = 1) 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')
AND (ct.IS_COORDINATOR = 1 OR ct.IS_APPROVER = 1) THEN 1
WHEN CC.FILTER_TYPE = 'C'
AND WE.ONBOARD_ACTIVITY IS NOT NULL
AND (ct.IS_COORDINATOR = 1 OR ct.IS_APPROVER = 1) THEN 1
WHEN we.VESSEL_BY_DEPARTURE_DATE IS NULL
AND (ct.IS_COORDINATOR = 1 OR ct.IS_APPROVER = 1) 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.NEXT_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 = 3
) 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_REJECTED – total count of the rejected documents.
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.
