Monday, May 21, 2012

Department Hierarchy Query

Recently, I was asked to create a query to represent our department hierarchy in SQL.  The results of the SQL would be used by our business partners to identify nodes on the tree with no manager assigned and to validate our roll-ups looked correct.  Since we use our Department Security Tree to represent our organizational structure I knew I would need to write a recursive query against that tree and join in the Manager's name by Manager Position on the department table.  I use a custom reporting table that is updated every 15 minutes to get the name of the manager in the position.  If you attempt something like this you will have to modify the SQL below to meet your organization's needs.  In the past I had used ORACLE'S connect by for recursive joins, but recently I had been turned on to Common Table Expressions.(be extra cautious when creating CTE query's in db2, to my knowledge db2 will not break out of an infinite loop for you like Oracle will).  Below is my SQL:

 WITH TREE (TREE_NODE, TREE_NODE2, TREE_NODE3, TREE_LEVEL_NUM, PARENT_NODE_NAME, TREE_NODE_NUM, TREE_NODE_NUM_END, STATUS, MANAGER_POSN, MANAGER_NAME, DESCR ) AS (
 SELECT TREE_NODE
 ,TREE_NODE
 ,TREE_NODE
 , TREE_LEVEL_NUM
 , PARENT_NODE_NAME
 ,TREE_NODE_NUM
 ,TREE_NODE_NUM_END
 , 'A'
 , ' '
 , ' '
 , 'My Corp. Descr'
  FROM pstreenode b
 WHERE b.tree_name = 'DEPT_SECURITY'
   AND B.SETID = 'SHARE'
   AND b.tree_level_num = 1
   AND B.EFFDT=(
 SELECT MAX(EFFDT)
  FROM PSTREENODE PSTREENODE
 WHERE PSTREENODE.SETID=B.SETID
   AND PSTREENODE.SETCNTRLVALUE=B.SETCNTRLVALUE
   AND PSTREENODE.TREE_NAME=B.TREE_NAME
   AND PSTREENODE.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
  UNION ALL
 SELECT B.TREE_NODE
 ,substr('>>>>>>>>>'
 , 1
 , (B.TREE_LEVEL_NUM-1))||B.TREE_NODE
 ,LPAD(' '
 , (B.TREE_LEVEL_NUM-1)) ||B.TREE_NODE
 , B.TREE_LEVEL_NUM
 , B.PARENT_NODE_NAME
 ,B.TREE_NODE_NUM
 , B.TREE_NODE_NUM_END
 , C.EFF_STATUS
 , C.MANAGER_POSN
 , CASE C.MANAGER_POSN WHEN ' ' THEN '' ELSE (
 SELECT X.NAME
  FROM PS_ZZ_IDM_DATA X
 WHERE X.POSITION_NBR = C.MANAGER_POSN
   AND X.HR_STATUS IN ('A', 'L')
   AND ROWNUM = 1) END CASE,C.DESCR
  FROM TREE, pstreenode b, PS_DEPT_TBL C
 WHERE b.tree_name = 'DEPT_SECURITY'
   AND B.SETID = 'SHARE'
   AND TREE.TREE_NODE_NUM = B.PARENT_NODE_NUM
   AND B.TREE_NODE = C.DEPTID
   AND B.SETID = C.SETID
   AND C.EFFDT=(
 SELECT MAX(EFFDT)
  FROM PS_DEPT_TBL DEPT_TBL
 WHERE DEPT_TBL.SETID=C.SETID
   AND DEPT_TBL.DEPTID=C.DEPTID
   AND DEPT_TBL.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
   AND B.EFFDT=(
 SELECT MAX(EFFDT)
  FROM PSTREENODE PSTREENODE
 WHERE PSTREENODE.SETID=B.SETID
   AND PSTREENODE.SETCNTRLVALUE=B.SETCNTRLVALUE
   AND PSTREENODE.TREE_NAME=B.TREE_NAME
   AND PSTREENODE.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) )
 SELECT TREE_NODE
 , TREE_NODE2||'-'||DESCR
 , TREE_NODE3||'-'||DESCR
 ,CASE TREE_LEVEL_NUM WHEN 1 THEN 'Company' WHEN 2 THEN 'Executive' WHEN 3 THEN 'Division' WHEN 4 THEN 'Region' WHEN 5 THEN 'Business' WHEN 6 THEN 'Function' WHEN 7 THEN 'Team' WHEN 8 THEN 'Department' ELSE 'LEVEL9' END CASE
 ,TREE_LEVEL_NUM
 ,MANAGER_NAME
 ,STATUS
 ,PARENT_NODE_NAME
 ,TREE_NODE_NUM
 ,TREE_NODE_NUM_END
 ,DESCR
  FROM TREE
 WHERE status = 'A'
  ORDER BY TREE_NODE_NUM

   The end result of my query returned the Department ID as the tree node, a column representing the hierarchy with node and description indented with greater than signs and one column indented with spaces, the description of the level from the tree, the tree level number,  the manager of the department ( this is from a custom table, you can get the manager's name from the PERSONAL_DATA if you don't use position management), effective status, parent node name, tree node number and the tree node number end and finally the Description from the department table.

Below is an example of the end product in SQL. 

COMPANY-Department Descr  Manager Name
>1018-Department Descr  Manager Name
>>1330-Department Descr  Manager Name
>>>1185-Department Descr  Manager Name
>>>1186-Department Descr  Manager Name
>>>>>>>01000304-Department Descr  Manager Name
>>>1385-Department Descr  Manager Name
>>>1145-Department Descr  Manager Name
>>>>>>>5020916-Department Descr  Manager Name
>>>1031-Department Descr  Manager Name
>>>>1205-Department Descr  Manager Name
>>>>>>>1170980-Department Descr  Manager Name
>>>>1331-Department Descr  Manager Name
>>>>>>>1110102-Department Descr  Manager Name
>>>>>>>1110980-Department Descr  Manager Name

While this is pretty decent for SQL it was still difficult to keep inline what departments reported to other departments.  So I thought about putting this information into a PeopleSoft page that would allow the user to view this data, however be able to collapse nodes and expand nodes to visualize the hierarchy.  I was surprised to find out how easy this was with the use of a jQuery Tree Project found on GITHUB.  I created a page with an HTML area and in the page Activate PeopleCode I loaded a view that I created of off the CTE SQL above.

The PeopleCode was probably the most difficult part of this process.  We use an unstructured tree for our organization structure.  This means that a level three can have a level three as a parent or a level eight department can report to a level three division. So when building the unordered list HTML for the tree I needed to keep track of how many levels to break the list so that the roll-up would work properly.  Below is my page PeopleCode to build my unordered list for the HTML area on my page.

Local Rowset &ORG_STRUCTURE;



Local string &new_line = "<li><span><span class=""KNOCKOUT"">";
Local string &sibling = "</span></span></li>" | Char(12) | Char(13);
Local string &child = "</span></span><ul>" | Char(12) | Char(13);
Local string &End_Family = "</ul>" | Char(12) | Char(13) | "</li>" | Char(12) | Char(13);


<* array of parents is used to figure out then number of end family's I need to put in the HTML area.  If the tree writes down to

rem level 8, a department, and the next row is a level three, a division, then I need to evaluate the parent of this new row against the array

rem to see how many closing ul and li tags are needed to properly close this group.*>


Local array of string &PARENTS = CreateArrayRept("", 0);



rem a view build on my CTE SQL;
&ORG_STRUCTURE = CreateRowset(Record.ZZ_DEPT_SEC_VW);

&ROWS = &ORG_STRUCTURE.Fill("where EFF_STATUS = 'A'");

REM &tree will be our HTML tree data;


Local string &tree = "";
For &X = 1 To &ROWS
  
   If &X = 1 Then
      &tree = &new_line | &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.TREE_NODE.Value;
   Else
      &value = &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.TREE_LEVEL_NUM.Value - &ORG_STRUCTURE(&X - 1).ZZ_DEPT_SEC_VW.TREE_LEVEL_NUM.Value;
     
      Evaluate &value
      When = 0
         REM  NO CHANGE IN TREE LEVEL BUT NEED TO EVALUATE TWO CONDITIONS;
         REM  DOES THE NEW ROW REPORT TO THE SAME LEVEL  EXAMPLE REGION REPORTS TO A REGION;
         REM TWO DOES THIS LEVEL REPORT TO HIGHER LEVEL THAN THE PREVIOUS ROW.
       REM EXAMPLE Division reports to Region next division reports to executive;
         If &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value = &ORG_STRUCTURE(&X - 1).ZZ_DEPT_SEC_VW.TREE_NODE.Value Then
            &tree = &tree | &child;
            &PARENTS.Push(&ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value);
            Break;
         Else
            If &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value <> &ORG_STRUCTURE(&X - 1).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value Then
               &tree = &tree | &sibling;
              
               &LOOP = True;
               While &LOOP
                  &POP = &PARENTS.Pop();
                  If &POP <> &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value Then
                     &tree = &tree | &End_Family;
                  Else
                     &PARENTS.Push(&ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value);
                     &LOOP = False;
                  End-If;
                 
               End-While;
            Else
               &tree = &tree | &sibling;
            End-If;
         End-If;
        
         Break;
      When > 0
         &tree = &tree | &child;
         &PARENTS.Push(&ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value);
         Break;
      When-Other
         &tree = &tree | &sibling;
        
         &LOOP = True;
         While &LOOP
            &POP = &PARENTS.Pop();
            If &POP <> &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value Then
               &tree = &tree | &End_Family;
            Else
               &PARENTS.Push(&ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.PARENT_NODE_NAME.Value);
               &LOOP = False;
            End-If;
           
         End-While;
        
        
      End-Evaluate;
     
      &tree = &tree | &new_line | &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.TREE_NODE.Value | "-" | &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.DESCR.Value | " <span class=""KNOCKOUT""> [" | &ORG_STRUCTURE(&X).ZZ_DEPT_SEC_VW.MANAGER_NAME.Value | "]</span>";
     
   End-If;
End-For;
REM SERVE UP YOU OWN LOCAL VERSION OF THE LIBRARY IS BEST, TO SEE EXAMPLS OF HOW TO DO THIS CHECK OUT Jim Marion's Blog or his book;

<*
&Jq_CookieJS = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_APT_JSL, Field.ISCRIPT1, "FieldFormula", "IScript_Jq_CookieJS");
&Jq_TreeJS = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_APT_JSL, Field.ISCRIPT1, "FieldFormula", "IScript_Jq_TreeJS");
&Jq_LibJS = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_APT_JSL, Field.ISCRIPT1, "FieldFormula", "IScript_jQuery_1_7_1");
*>
ZZ_ORG_WRK.HTMLAREA.Value = GetHTMLText(HTML.ZZ_ORG_STRUCTURE1, &tree);  

 Below is the HTML object I build for my demo:

<script type="text/javascript" src="https://github.com/pioz/jquery-tree/raw/master/jquery.min.js"></script>
<script type="text/javascript" src="https://github.com/pioz/jquery-tree/raw/master/jquery.cookie.js"></script>
<script type="text/javascript" src="https://github.com/pioz/jquery-tree/raw/master/jquery.tree.js"></script>



<script type="text/javascript">
   $(document).ready(function( ) {
     $('ul.org').tree({default_expanded_paths_string : '0/0,0/1,0/2'});
   });
</script>
<table border="0">
<tr>
  <td></td>
</tr>
<tr>
<td><div >
<span class="PAPAGETITLE"> Organizational Structure</span>
</div>
</td>
</tr>
<tr>
<td>
<a  class="PSHYPERLINK" onclick=jQuery('ul.org').expand()>Expand All</a>
<a  class="PSHYPERLINK" onclick=jQuery('ul.org').collapse()>Collapse All</a>
</td>
</tr>
<tr>
<td>
<span class="KNOCKOUT">
<ul  class="org" >
%Bind(:1)
</ul>
</span>
</td>
</tr>
</table>

The use of JavaScript Libraries  from third party websites is not recommended, however they will work for the purpose of this demo. However, if your looking for cools ways to server up JavaScript and JavaScript libraries to the browser, check out  Jim Marion's blog.

  Below is example of how the jQuery tree works with a mocked up tree data.  You can expand the node of the tree by clicking on the Arrow to the left of the Node number.  Other ideas might be to make this tree searchable? 


Organizational Structure


  • Company Level
    • 999-Chief Executive Officer [Manager's Name]
      • 9999-Sales [Manager's Name]
        • 52-C-Sales [Manager's Name]
          • 53-CBD [Manager's Name]
            • 5152-Business [Manager's Name]
          • 62-Commercial [Manager's Name]
        • 52-PTS-Sales [Manager's Name]
          • 2458-Commercial [Manager's Name]
        • 55-BUSINESS - DC [Manager's Name]
          • 62-Strategy [Manager's Name]
            • 58490-Strategy & Admin [Manager's Name]
          • 6-DC Downtown [Manager's Name]
            • 5555-Commercial [Manager's Name]
            • 5559-Campus [Manager's Name]
          • 5136-Commercial [Manager's Name]
      • 55-HR [Manager's Name]
        • 551-HR - DC [Manager's Name]
          • 74-Admin [Manager's Name]
            • 3285-Org Effectiveness [Manager's Name]
            • 25-HR - Diversity [Manager's Name]
          • 73-Talent Mgmt & Dev [Manager's Name]
            • 2254-Emerging Leaders Program [Manager's Name]
            • 3849-HR - Talent Development [Manager's Name]
            • 45165-HR - Talent Acquisition [Manager's Name]
            • 3450490-HR - Talent Acquisition [Manager's Name]
            • 34549-HR - Client Services [Manager's Name]
    • 777-Chief Operating Officer [Manager's Name]
      • 560008-Consumer Services [Manager's Name]
        • 500024-CS - Administration [Manager's Name]
          • 990004900-Retail [Manager's Name]
          • 99040090-Retail [Manager's Name]
          • 26300500-Managers [Manager's Name]
          • 3200770-Retail Admin Dept [Manager's Name]
    • 111-Chief Financial Officer [Manager's Name]
      • 3470550-Operations & Technology Group [Manager's Name]
        • 55463-Administration [Manager's Name]
          • 490-Business Process Engineering [Manager's Name]
          • 28549-Administration [Manager's Name]
          • 28849-Project Management [Manager's Name]
          • 58822633-Process Redesign [Manager's Name]
          • 501494-SAP [Manager's Name]
           
The use of the arrow to expand and collapse the nodes allows you to turn the tree node description into a hyperlink to the department table for end users to to make updates.  Other uses for this type in tree data could be building a reports to tree for position management or even a quick view of supervisor hierarchy.  Can you think of any ideas or uses in your organization?
Posted by at No comments:
Subscribe to: Posts (Atom)