...
| Page Properties |
|---|
Summary | 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
| Expand |
|---|
|
| Code Block |
|---|
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_ |
SUBORDINATE <>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
...
#DOCUMENTS_REJECTED – total count of the rejected documents.
Task Parameters
Recipients: can be modified depending on the customer. It can be:
...
