Tree Integrity

The following table describes the audit queries and resolutions for this area:

Query Description Resolution

TREE-01

Tree Structure table contains Level Record name that does not exist in Record Definition table

Use Tree Manager to open the structure and fix the invalid fields.

TREE-02

Tree Structure table contains Level Page name that does not exist in Page Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-03

Tree Structure table contains Node Record name that does not exist in Record Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-04

Tree Structure table contains Node Field name that does not exist in RecordField table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-05

Tree Structure table contains Node Page name that does not exist in Page Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-06

Tree Structure table contains Detail Record name that does not exist in Record Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-07

Tree Structure table contains Detail Record name that does not exist in Record Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-08

Tree Structure table contains Detail Page name that does not exist in Page Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-09

Tree Structure table contains Summary Tree that does not exist in Tree Level table.

See the TREE-09 Resolution section following this table.

TREE-10

Tree Structure table contains Level Menu-Menu Bar combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-11

Tree Structure table contains Node Menu-Menu Bar combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-12

Tree Structure table contains Detail Menu-Menu Bar combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-13

Tree Structure table contains Level Menu-Page combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-14

Tree Structure table contains Node Menu-Page combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-15

Tree Structure table contains Detail Menu-Page combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-16

Tree Definition Level count does not match the record count in Tree Level table.

Set the Count in the Tree Definition table to reflect the actual number of records that are in the PSTREELEVEL table for this tree branch. Note that a problem may occur if some levels are missing and there are still nodes referencing them. In this case, the nodes do not open the tree correctly. The third SELECT checks for the previous situation. If this is the problem, run PSTED, and define the missing levels, save the tree, and then close and reopen it.

SELECT COUNT(*)
FROM PSTREELEVEL
WHERE TREE_NAME = 'tree_name'
 AND SETID = 'setid'
 AND EFFDT = 'effdt';

UPDATE PSTREEDEFN
SET LEVEL_COUNT = $count
WHERE TREE_NAME = 'tree_name'
 AND SETID = 'setid'
 AND EFFDT = 'effdt';

TREE-17

Tree Definition Node count does not match the record count in Tree Node table.

See the TREE-17 Resolution section following this table.

TREE-18

Tree Definition Leaf count does not match the record count in Tree Leaf table.

Set the Count in the Tree Definition table to reflect the actual number of records that are in the PSTREELEAF table for this branch.

SELECT COUNT (*)
FROM PSTREELEAF
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'
AND TREE_BRANCH =
 'tree_branch_name';

UPDATE PSTREEDEFN
SET LEAF_COUNT = $count
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'
AND TREE_BRANCH =
 'tree_branch_name';

TREE-19

Tree Definition contains Structure ID that does not exist in Tree Structure table

Use Tree Manager to create the structure that you desire by using the name that is reported in this audit.

TREE-20

Tree Definition contains Query Access Group structure with undefined levels and leaves.

Query trees should have no leaves and no levels. This audit finds exceptions to that case in the definition counts.

UPDATE PSTREEDEFN
SET LEVEL_COUNT = 0,
 LEAF_COUNT = 0
WHERE TREE_STRCT_ID =
 'ACCESS_GROUP' AND
 (LEVEL_COUNT <> 0 OR
  LEAF_COUNT <> 0);

TREE-21

Tree Selector Control contains Tree name that is not defined in Tree Definition table.

This audit flags records in the PSTREESELCTL tables for records that don't have a corresponding record in the PSTREEDEFN table.

DELETE FROM PSTREESELCTL A
WHERE NOT EXISTS
 (SELECT 'X' FROM  PSTREEDEFN B
  WHERE B.SETID = A.SETID
  AND B.TREE_NAME = A.TREE_NAME
  AND B.EFFDT = A.EFFDT)

TREE-22

Tree Definition Level count does not match level use.

See the TREE-22 Resolution section following this table.

TREE-23

Tree Level does not exist in Tree Definition table.

Tree Level records in the PSTREELEVEL table exist for trees that don't exist in the PSTREEDEFN table.

DELETE FROM PSTREELEVEL A
WHERE NOT EXISTS
 (SELECT 'X'
  FROM PSTREEDEFN B
  WHERE B.SETID = A.SETID
  AND B.TREE_NAME =
   A.TREE_ NAME AND
  B.EFFDT = A.EFFDT)

TREE-24

Tree Node does not exist in Tree Definition table.

Tree Node records in the PSTREENODE table exist for trees that don't exist in the PSTREEDEFN table.

DELETE FROM PSTREENODE A
WHERE NOT EXISTS
 (SELECT 'X' FROM PSTREEDEFN B
  WHERE  B.SETID = A.SETID
  AND B.TREE_NAME =
   A.TREE_NAME AND
  B.EFFDT = A.EFFDT)

TREE-25

Tree Leaf does not exist in Tree Definition table.

Tree Leaf records in the PSTREELEAF table exist for trees that don't exist in the PSTREEDEFN table.

DELETE FROM PSTREELEAF A
 WHERE NOT EXISTS
 (SELECT 'X' FROM PSTREEDEFN B
  WHERE B.SETID = A.SETID
  AND B.TREE_NAME = A.TREE_NAME
  AND B.EFFDT = A.EFFDT)

TREE-26

Tree Leaf ranges are not valid in Tree Definition table.

Finds records in the PSTREELEAF table where RANGE_FROM is less than RANGE_TO.

Use Tree Manager to open the tree and correct the invalid range values.

TREE-27

Tree Leaf does not have parent Tree Node in Tree Definition table.

Run this SQL:

DELETE FROM PSTREELEAF A
WHERE NOT EXISTS
 (SELECT 'X' FROM PSTREENODE B
  WHERE  B.SETID = A.SETID
  AND B.TREE_NAME =
   A.TREE_NAME AND
  B.EFFDT = A.EFFDT AND
  B.TREE_NODE_NUM =
   A.TREE_NODE_NUM)

TREE-28

Tree Branch does not exist in Tree Branch table.

Refer to the "Tree Audit and Repair Utilities" topic in the PeopleSoft Tree Manager documentation and run the Unbranch Tree Repair Utility so that all branches are removed from the tree.

TREE-29

Tree Branch does not exist in Tree Branch table.

Refer to the "Tree Audit and Repair Utilities" topic in the PeopleSoft Tree Manager documentation and run the Unbranch Tree Repair Utility so that all branches are removed from the tree.

TREE-30

Tree Branch Node count does not match the record count in Tree Node table.

See Resolution for Tree-29.

TREE-31

Tree Branch Leaf count does not match the record count in Tree Leaf table

See Resolution for Tree-29.

TREE-32

Tree Node Num, Node Num End, or Level Num is invalid in Tree Branch table.

See Resolution for Tree-29.

TREE-33

Identify all orphan access group definitions as well as invalid access group definitions in the access group security.

Open Query Access Group Tree in Query Access Group Manager and update the identified Access Group so that a record is created in the Access Group Table.

TREE-34

Tree Definition Node Count Does Not Equal 0 for a Branched Tree.

See Resolution for Tree-29.

TREE-35

Tree Definition Leaf Count Does Not Equal 0 for a Branched Tree.

See Resolution for Tree-29.

TREE-09 Resolution

Lists any Summary Tree Structures that reference a level number that is on a Detail Tree that does not exist in the Tree Level table. Since a Summary Tree is a tree that is built off of the nodes from an existing Detail Tree at a given level, the level that is specified on the Summary Tree Structure must exist in the detail tree's PSTREELEVEL table. In this case, the Summary Tree is not usable from nVision or other reporting tools.

The situation could occur from several possible causes:

  • Summary Tree is moved or imported into a new database but Detail Tree is not.

  • The levels on the Detail Tree are deleted after the Summary Tree structure is created.

To correct this :

  1. First determine if Detail Tree exists and is in a valid state. This can be done by checking the name of the Detail Tree on the Summary Tree's Structure record; check the Summary Tree tab on the Tree Structure record for the Summary Tree. Note the tree name, setID, and level number.

  2. If Detail Tree exists, check to see if the level number that is defined on the Summary Tree Structure (step 1) exists.

  3. To correct the situation, either add missing level to detail tree or update Summary Tree Structure to refer to a valid detail tree and level number.

TREE-17 Resolution

This audit identifies Tree Definition Node counts that do not match the record count in the Tree Node table. Use these methods to correct the issue.

Set the count in the Tree Definition table to reflect the actual number of the records that are in the PSTREENODE table for this tree branch.

SELECT COUNT(*) FROM PSTREENODE
 WHERE TREE_NAME = 'tree_name'
 AND SETID = 'setid'
 AND EFFDT = 'effdt' AND
 TREE_BRANCH = 'tree_branch_name';
UPDATE PSTREEDEFN
SET NODE_COUNT =  $count
WHERE TREE_NAME = 'tree_name'
 AND SETID = 'setid'
 AND EFFDT = 'effdt' AND
 TREE_BRANCH = 'tree_branch_name';

For branched trees, use following SQL for each branch in the tree.

Note:

For trees with branches, the UPDATE also uses a different TABLE NAME and there are two UPDATE statements.

SELECT COUNT(*)
FROM PSTREENODE
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'
AND TREE_BRANCH =
 'tree_branch_name';
UPDATE PSTREEBRANCH
SET NODE_COUNT = $count
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'
AND TREE_BRANCH =
 'tree_branch_name'
UPDATE PSTREEDEFN
SET NODE_COUNT = 0,
WHERE TREE_NAME = 'tree_name'
  AND SETID = 'setid'
  AND EFFDT = 'effdt'
  AND TREE_BRANCH =
 'tree_branch_name'

For trees without branches, do not include the "TREE_BRANCH=" lines. For example:

SELECT COUNT(*)
FROM PSTREENODE
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'
SELECT COUNT(*)
FROM PSTREENODE
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'
UPDATE PSTREEDEFN
SET NODE_COUNT = 0,
WHERE TREE_NAME = 'tree_name'
AND SETID = 'setid'
AND EFFDT = 'effdt'

TREE-22 Resolution

This audit flags the Level Use type with the Level Count for conflicts. When the Level Use is N, there should be no levels defined, and when it is not N, levels should be defined. A problem in this audit may also report problems in the TREE-16 audit.

When the Level Use is N and the Level Count is 0 and TREE-16 does not indicate an error on the same tree, run the following SQL:

UPDATE PSTREEDEFN SET USE_LEVELS = 'S' 
WHERE TREE_NAME =  'tree_name' AND SETID = 'setid' 
AND EFFDT = 'effdt'

When the Level Use is S and the Level Count is 0 and TREE-16 does not indicate an error on the same tree, run the following SQL (after checking the resolution on TREE-16 to clean up any level records):

UPDATE PSTREEDEFN SET LEVEL_COUNT = 0 
WHERE TREE_NAME =  'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'

When the Level Use is not N and the Level Count is 0 and TREE-16 does not indicate an error on the same tree, run the following SQL (after checking the resolution on TREE-16 to clean up any level records):

UPDATE PSTREEDEFN SET USE_LEVELS = 'N' 
WHERE TREE_NAME =  'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'

UPDATE PSTREENODE SET TREE_LEVEL_NUM = 0 
WHERE TREE_NAME =  'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'

When TREE-23 indicates an error on the same Tree with the Level Count on the PSTREEDEFN = number of PSTREELEVEL records (when the PSTREELEVEL has no levels for this tree, count is 0 ), run the following SQL:

SELECT COUNT(*) FROM PSTREELEVEL 
WHERE TREE_NAME =  'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'

UPDATE PSTREEDEFN SET LEVEL_COUNT = 0 
WHERE TREE_NAME =  'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'

UPDATE PSTREEDEFN SET USE_LEVELS = 'N' 
WHERE TREE_NAME = 'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'

UPDATE PSTREENODE SET TREE_LEVEL_NUM = 0 
WHERE TREE_NAME =  'tree_name' 
AND SETID = 'setid' 
AND EFFDT = 'effdt'