SQL – an example of a tree-walking query for approval hierarchy reporting

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…

apprvl_hierarchy_crop

…is stored like this in database table:

apprvl_hierarchy_tbl

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:

apprvl_hierarchy_sql

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s