PeopleSoft Tree Manager 7: The secret of the PSTREENODE table

The key table in the PeopleSoft Tree Manager data model is the PSTREENODE table.

The first three columns are the composite key of the table.

• TREE_NAME
• SETID
• EFFDT

This table looks like a normal parent child self-join table.

• TREE_NODE_NUM
• TREE_NODE
• PARENT_NODE_NUM
• PARENT_NODE_NAME

It seems nothing special. However, if you take a closer look, you will find that PeopleSoft Tree Manager have built the intelligence into the TREE_NODE_NUM number generation.



Accounting to the posting from Grey Sparling PeopleSoft Expert's Corner, you can directly report at a given parent node without using a flatten hierarchy table or use connect-by query. PeopeSoft accomplishes this by adding the TREE_NODE_NUM_END column to the PSTREENODE table. This column helps you to get all children of a given node. The TREE_NODE_NUM and TREE_NODE_NUM_END forms the range of the TREE_NODE_NUM that can group all child nodes within the branch.

If your department tree is a winter tree, I can list all the department under a given parent department in a single SQL:

 1  select d.setid, d.deptid, d.Descr
 2  from ps_Dept_tbl d
 3  , pstreenode t1
 4  , pstreenode t2
 5  where t1.tree_name = 'DEPT_SECURITY'
 6  and t2.tree_name = t1.tree_name
 7  and t1.SETID = 'FEDPS'
 8  and t2.SETID = t1.SETID
 9  and d.SETID = t1.SETID
10  and t1.EFFDT = TO_DATE('01-01-1980', 'DD-MM-RRRR')
11  and t2.EFFDT = t1.EFFDT
12  and t1.tree_node = 'FRG'
13  and t2.tree_node_num between t1.tree_node_num and t1.tree_node_num_end
14* and t2.tree_node = d.deptid

This query will return all child departments under the parent department 'FRG'.

If the department tree is a summer tree, we can just join the PSTREELEAF. It is also mentioned in Grey Sparling's blog.