Merging PeopleSoft trees

This article describes, based on a very simple example, how to merge trees into other trees. This just describes the strategy. If a full-fledged process has to be written to do this, then there will be a lot of extra details to think about, though this is not the aim of this article.

We start with 2 simple trees as in the screenshots below. Tree C_DEPT_TREE1:

Tree C_DEPT_TREE2:

We can merge both trees by creating a new tree with only a root node and no underlying departments:

Create a new tree, named C_MERGE_DEPT as in this example. This will be the new tree in which we merge the other two trees.

We need to update 2 tables in the database namely PSTREELEVEL and PSTREENODE. In PSTREELEVEL we will only have a Level 1 initially. We will have to create the remaining levels here. C_DEPT_TREE1 is 3 levels deep and we are going to link the C_DEPT_TREE1 top level to the new top level (in this example: COLRUYT). This means we will need 4 levels in total. C_DEPT_TREE2 is only 2 levels deep so no extra levels are needed.

You can use something like this to insert the new levels:

INSERT
INTO SYSADM.PSTREELEVEL VALUES
  (
    'CG',
    ' ',
    'C_MERGE_DEPT',
    to_date('25032016','DDMMYYYY'),
    'LEVEL 2',
    2,
    'N'
  );

INSERT
INTO SYSADM.PSTREELEVEL VALUES
  (
    'CG',
    ' ',
    'C_MERGE_DEPT',
    to_date('25032016','DDMMYYYY'),
    'LEVEL 3',
    3,
    'N'
  );

INSERT
INTO SYSADM.PSTREELEVEL VALUES
  (
    'CG',
    ' ',
    'C_MERGE_DEPT',
    to_date('25032016','DDMMYYYY'),
    'LEVEL 4',
    4,
    'N'
  );

This can be made more dynamic in an application engine. The levels should have names like “Level X” with the proper effective date.

The second thing we need to do is do some inserts into the PSTREENODE table. Basically do this:

• Copy the rows from C_DEPT_TREE1 to C_MERGE_DEPT
• Change the EFFDT to the effdt of the C_MERGE_DEPT tree
• Change the SETID to the setid of the C_MERGE_DEPT tree
• Renumber the node values by adding 2000000000
• Increase the tree level by 1 (because we have a new root level)
• Link the original top level to the new top level

The SQL to use for this is:

INSERT
INTO SYSADM.PSTREENODE
  (SELECT 'CG' ,
      ' ' ,
      'C_MERGE_DEPT' ,
      to_date('25032016','DDMMYYYY') ,
      TREE_NODE_NUM + 2000000000 , /* renumber in range above 2000000000 */
      TREE_NODE ,
      TREE_BRANCH ,
      0 , /* leave zero, this will be corrected later */
      TREE_LEVEL_NUM + 1 , /* increase node level */
      TREE_NODE_TYPE ,
      CASE TREE_NODE_NUM
        WHEN 1
        THEN 1
        ELSE PARENT_NODE_NUM + 2000000000
      END , /* renumber parent node and link top node to new top node */
      PARENT_NODE_NAME ,
      OLD_TREE_NODE_NUM ,
      NODECOL_IMAGE ,
      NODEEXP_IMAGE
    FROM SYSADM.PSTREENODE
    WHERE SETID       = 'ZZ00'
    AND SETCNTRLVALUE = ' '
    AND TREE_NAME     = 'C_DEPT_TREE1'
  );

After this is done the node numbering of the tree is not correct anymore. There is a process in PeopleSoft to fix this, start it via: Main Menu > Tree Manager > Tree Utilities > Repair Tree
Here you choose the tree utility called Reset Tree Node Gaps.

After running this process you can open the C_MERGE_DEPT tree and it will look like below. The first tree C_DEPT_TREE1 is inserted into C_MERGE_DEPT:

To also merge the C_DEPT_TREE2, the approach is the same. You don’t have to do anything in the PSTREELEVEL table anymore as we have enough levels. Just copy the PSTREENODE entries again. The SQL is the same, only the parameters are different of course:

INSERT
INTO SYSADM.PSTREENODE
  (SELECT 'CG' ,
      ' ' ,
      'C_MERGE_DEPT' ,
      to_date('25032016','DDMMYYYY') ,
      TREE_NODE_NUM + 2000000000 ,
      TREE_NODE ,
      TREE_BRANCH ,
      0 ,
      TREE_LEVEL_NUM + 1 ,
      TREE_NODE_TYPE ,
      CASE TREE_NODE_NUM
        WHEN 1
        THEN 1
        ELSE PARENT_NODE_NUM + 2000000000
      END ,
      PARENT_NODE_NAME ,
      OLD_TREE_NODE_NUM ,
      NODECOL_IMAGE ,
      NODEEXP_IMAGE
    FROM SYSADM.PSTREENODE
    WHERE SETID       = 'ZZ01'
    AND SETCNTRLVALUE = ' '
    AND TREE_NAME     = 'C_DEPT_TREE2'
  );

Again run the Tree Repair process and the merged tree will look like this:

This is all there is to it.