An approval hierarchy can be concisely represented in a 2-column table – one column each for subordinate and supervisor. A “subordinate” with no supervisor indicates that this person is at the top of the approval hierarchy. Conversely, subordinates who don’t appear in the supervisor column must be at the foot of the hierarchy – the “leaf nodes” in a hierarchical tree.
So the following hierarchy…
…is stored like this in database table:
The business reporting requirement is to generate lists of users corresponding to each unique path up the hierarchy from the lowest users (the leaf nodes) to the highest (SMITH – the “root node”). In the diagram above, the leaf nodes are coloured orange.
An SQL query to generate this information can be constructed as follows…
Firstly, the users in supervisors/subordinates table could be validated to ensure that they are current users, with employee records in Oracle HRMS:
SELECT xh.subordinate user_name
, xh.supervisor supervisor_name
FROM xx_hierarchy xh
, fnd_user fu_super
, fnd_user fu_sub
WHERE 1 = 1
AND fu_super.user_name = xh.supervisor
AND NVL(fu_super.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND fu_super.employee_id IS NOT NULL -- Is Employee
AND fu_sub.user_name = xh.subordinate
AND NVL(fu_sub.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND fu_sub.employee_id IS NOT NULL -- Is Employee
The approval hierarchy can be traversed using an SQL query and the START WITH and CONNECT BY clauses. However, it can be troublesome to join to other tables when these clauses are used, and therefore a series of in-line views can be used in a “master” SQL statement.
WITH bw_approvers AS
(
SELECT xh.subordinate user_name
, xh.supervisor supervisor_name
FROM xx_hierarchy xh
, fnd_user fu_super
, fnd_user fu_sub
WHERE 1 = 1
AND fu_super.user_name = xh.supervisor
AND NVL(fu_super.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND fu_super.employee_id IS NOT NULL -- Is Employee
AND fu_sub.user_name = xh.subordinate
AND NVL(fu_sub.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND fu_sub.employee_id IS NOT NULL -- Is Employee
)
SELECT ba.user_name user_name
, ba.supervisor_name supervisor_name
, LEVEL lev
, CONNECT_BY_ISLEAF is_leaf
, SYS_CONNECT_BY_PATH(ba.user_name,'|') || '|' connect_path
FROM bw_approvers ba
START WITH ba.supervisor_name IS NULL
CONNECT BY PRIOR ba.user_name = ba.supervisor_name
ORDER BY level
, ba.user_name
Here we start at the root node of the hierarchy (START WITH), and use CONNECT BY PRIOR to join the previous subordinate to the current supervisor.
A few more keywords yield some useful information about the hierarchy:
LEVEL – “1” for the root node, “2” for the next subordinate level down, and so on;
CONNECT_BY_ISLEAF – “1” for a leaf node, otherwise “0”;
SYS_CONNECT_BY_PATH – a pipe-separated list of users between the current user and the root node.
The above query yields the following results:
So-far-so-good. Next, we want to identify the leaf nodes, so that we can list the users in the corresponding paths through the hierarchy to the root node.
WITH bw_approvers AS
(
SELECT xh.subordinate user_name
, xh.supervisor supervisor_name
FROM xx_hierarchy xh
, fnd_user fu_super
, fnd_user fu_sub
WHERE 1 = 1
AND fu_super.user_name = xh.supervisor
AND NVL(fu_super.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND fu_super.employee_id IS NOT NULL -- Is Employee
AND fu_sub.user_name = xh.subordinate
AND NVL(fu_sub.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND fu_sub.employee_id IS NOT NULL -- Is Employee
)
, bw_hierarchy AS
(
SELECT user_name user_name
, supervisor_name supervisor_name
, LEVEL lev
, CONNECT_BY_ISLEAF is_leaf
, SYS_CONNECT_BY_PATH(ba.user_name,'|') || '|' connect_path
FROM bw_approvers ba
START WITH ba.supervisor_name IS NULL
CONNECT BY PRIOR ba.user_name = ba.supervisor_name
ORDER BY level
, ba.user_name
)
, bw_leaf AS
-- List users with no subordinates - leaf nodes;
(
SELECT bh.user_name
, bh.supervisor_name
, bh.connect_path
FROM bh.bw_hierarchy bh
WHERE bh.is_leaf = 1
)
SELECT ba.user_name USER_NAME
, bl.user_name LEAF_USER
, ROW_NUMBER() OVER
(
PARTITION BY bl.user_name
ORDER BY bl.user_name ASC
, INSTR('|'||bl.connect_path,ba.user_name||'|') DESC
) PRIORITY
, bl.connect_path CONNECT_PATH
FROM bw_leaf bl
, bw_approvers ba
WHERE 1 = 1
AND -- the approver is in the connect_path
INSTR('|'||bl.connect_path,ba.user_name||'|') > 0
ORDER BY bl.user_name ASC
, INSTR('|'||bl.connect_path,ba.user_name||'|') DESC
;
Bingo! Job done.
Incidentally, the ROW_NUMBER() pseudo-column yields a count of the level from leaf to root, relative to the leaf node.
