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