• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Look up a Manager's Manager using AM_Employee Manager_ID
#1
I needed to be able to look up an employees Vice President to manage specific approvals.  I built a custom role with the following SQL.  This will look up an employee's Vice President (including various VP titles) from 1 level to 6 levels away.  

I hope it helps someone else too.

SELECT CASE
WHEN E.MANAGER_ID IN
(SELECT E.EMPLOYEE_ID FROM AM_EMPLOYEE E
INNER JOIN V_TITLE T ON E.FUNCTION_ID = T.REFERENCE_ID
WHERE END_OF_CONTRACT IS NULL
AND (T.REFERENCE_EN LIKE 'DVP%' OR T.REFERENCE_EN LIKE 'SVP%' OR T.REFERENCE_EN LIKE 'DIV%VICE %' OR T.REFERENCE_EN LIKE 'VICE PRES%' OR T.REFERENCE_EN LIKE 'SENIOR VICE %' OR T.REFERENCE_EN LIKE 'VP%'))
THEN E.MANAGER_ID
ELSE
CASE WHEN F.MANAGER_ID IN
(SELECT F.EMPLOYEE_ID FROM AM_EMPLOYEE F
INNER JOIN V_TITLE T ON F.FUNCTION_ID = T.REFERENCE_ID
WHERE END_OF_CONTRACT IS NULL
AND (T.REFERENCE_EN LIKE 'DVP%' OR T.REFERENCE_EN LIKE 'SVP%' OR T.REFERENCE_EN LIKE 'DIV%VICE %' OR T.REFERENCE_EN LIKE 'VICE PRES%' OR T.REFERENCE_EN LIKE 'SENIOR VICE %' OR T.REFERENCE_EN LIKE 'VP%'))
THEN F.MANAGER_ID
ELSE
CASE WHEN G.MANAGER_ID IN
(SELECT G.EMPLOYEE_ID FROM AM_EMPLOYEE G
INNER JOIN V_TITLE T ON G.FUNCTION_ID = T.REFERENCE_ID
WHERE END_OF_CONTRACT IS NULL
AND (T.REFERENCE_EN LIKE 'DVP%' OR T.REFERENCE_EN LIKE 'SVP%' OR T.REFERENCE_EN LIKE 'DIV%VICE %' OR T.REFERENCE_EN LIKE 'VICE PRES%' OR T.REFERENCE_EN LIKE 'SENIOR VICE %' OR T.REFERENCE_EN LIKE 'VP%'))
THEN G.MANAGER_ID
ELSE
CASE WHEN H.MANAGER_ID IN
(SELECT H.EMPLOYEE_ID FROM AM_EMPLOYEE H
INNER JOIN V_TITLE T ON H.FUNCTION_ID = T.REFERENCE_ID
WHERE END_OF_CONTRACT IS NULL
AND (T.REFERENCE_EN LIKE 'DVP%' OR T.REFERENCE_EN LIKE 'SVP%' OR T.REFERENCE_EN LIKE 'DIV%VICE %' OR T.REFERENCE_EN LIKE 'VICE PRES%' OR T.REFERENCE_EN LIKE 'SENIOR VICE %' OR T.REFERENCE_EN LIKE 'VP%'))
THEN H.MANAGER_ID
ELSE
CASE WHEN I.MANAGER_ID IN
(SELECT I.EMPLOYEE_ID FROM AM_EMPLOYEE I
INNER JOIN V_TITLE T ON I.FUNCTION_ID = T.REFERENCE_ID
WHERE END_OF_CONTRACT IS NULL
AND (T.REFERENCE_EN LIKE 'DVP%' OR T.REFERENCE_EN LIKE 'SVP%' OR T.REFERENCE_EN LIKE 'DIV%VICE %' OR T.REFERENCE_EN LIKE 'VICE PRES%' OR T.REFERENCE_EN LIKE 'SENIOR VICE %' OR T.REFERENCE_EN LIKE 'VP%'))
THEN I.MANAGER_ID
ELSE
CASE WHEN J.MANAGER_ID IN
(SELECT J.EMPLOYEE_ID FROM AM_EMPLOYEE I
INNER JOIN V_TITLE T ON J.FUNCTION_ID = T.REFERENCE_ID
WHERE END_OF_CONTRACT IS NULL
AND (T.REFERENCE_EN LIKE 'DVP%' OR T.REFERENCE_EN LIKE 'SVP%' OR T.REFERENCE_EN LIKE 'DIV%VICE %' OR T.REFERENCE_EN LIKE 'VICE PRES%' OR T.REFERENCE_EN LIKE 'SENIOR VICE %' OR T.REFERENCE_EN LIKE 'VP%'))
THEN J.MANAGER_ID
ELSE 0
END
END
END
END
END
END AS MANAGER
FROM AM_EMPLOYEE E
INNER JOIN AM_EMPLOYEE F ON E.MANAGER_ID = F.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE G ON F.MANAGER_ID = G.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE H ON G.MANAGER_ID = H.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE I ON H.MANAGER_ID = I.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE J ON I.MANAGER_ID = J.EMPLOYEE_ID
INNER JOIN SD_REQUEST R ON E.EMPLOYEE_ID = R.RECIPIENT_ID
WHERE R.REQUEST_ID IN (@ID@)
ruth.taylor@redcross.org, proud to be a member of EV CONNECT FORUM since Jan 2017.






Users browsing this thread: 1 Guest(s)