Performing Audits

After you have completed your tree, you can have PeopleSoft Tree Manager verify that it meets the restrictions you set for it. For example, if you selected the All Detail Values in this Tree option when you created the tree definition, PeopleSoft Tree Manager audit verifies that you have included all detail values.

You can perform a tree audit from the Tree Definition and Properties page, by saving the tree, by running the Repair Tree batch program, or by using the Tree Maintenance page. If you want to audit a closed tree, use the Tree Maintenance page. To get a report of all of the problems that have been found with the tree, use the Repair Tree program.

When you perform an audit and find problems, PeopleSoft Tree Manager displays a warning message. Some examples of the problems are:

  • One or more nodes have no detail values reporting to them.

  • You selected the All Detail Values option, but one or more defined values for the database field do not appear in the tree.

  • If you have not selected the Allow Duplicate Details option, then PeopleSoft Tree Manager checks for duplicate detail values and detail values with overlapping ranges.

Note: Although PeopleSoft Tree Manager attempts to detect overlapping detail values when the user inserts or modifies each detail value, the final audit process may detect overlapping or duplicate detail values that are not caught when the detail value is created or modified.

PeopleSoft Tree Manager also indicates where the problem is.

The warning message flags that the tree is invalid. You can save the tree as a draft tree only, and you cannot use it with other PeopleTools, such as PeopleSoft Query or PeopleSoft nVision, until the tree is valid.

The warning message flags that the tree is invalid. You can save the tree as a draft tree only, and you cannot use it with other PeopleTools, such as PeopleSoft Query, PeopleSoft nVision, and SmartNavigation pages until the tree is valid.

At this point, you can either correct the problem that is causing the tree to be invalid, or use the Save As Draft feature to save the tree in an invalid (unfinished) state.

All audits report the first occurrence found of any problem listed in the table. The audits are performed first on the displayed nodes and details, and then on the nodes in the database.

Node audits are only performed on open trees. If you run an audit from the Tree Maintenance page (from a closed tree), you run only the Detail audit. When you audit a tree branch, the node audits are performed on the nodes in the current branch only, while the detail audits are performed on all branches.

Note: These audits report only problems, not values. Run the Repair Tree program to get a complete list of problems with the tree.

The following table shows a complete list of audit checks.

Check For

Purpose

When Check Occurs

Overlapping Node Numbers

Reports first occurrence of two nodes whose internal node numbering is overlapping.

Always

Nodes Outside of Parent's Range

Reports first occurrence of a node whose internal node number does not fall within the parent node's internal node number range.

Always

Nodes Without Parents

Reports first occurrence of a node whose internal node number for a parent does not have an actual node with that number.

Always

Duplicate Node Names

Reports the first duplicate node name when it finds a node with the same name reporting to a different parent node.

Always

Nodes Without Leaves

In a detail tree, reports when any terminal node has no leaves.

Always

All Detail Values

Reports if you have not included all detail values.

When the All Detail Values In This Tree option is selected on the Tree Definition and Properties page.

Duplicate Detail Values

Reports if there are any duplicate detail values.

When the Allow Duplicate Detail Values option is not selected on the Tree Definition and Properties page.

Skipped Levels

Reports when a level is skipped.

When the All Levels option is selected on the Tree Levels page.

The name of the PeopleSoft SQL object used for this audit is PS_TDM_SQLOVERLAP, and it is stored in the PeopleSoft database. Edit this object in PeopleSoft Application Designer.

To tune the performance of the Overlapping Detail Ranges audit:

  1. Select Start > Programs > PeopleTools 8.x > Application Designer.

  2. Select File > Open in PeopleSoft Application Designer.

  3. Select the SQL option in the Definition drop-down list.

  4. Enter PS_TDM_SQLOVERLAP in the Name field of the Selection Criteria group box.

  5. Click the Open button.

    The default SQL used for detecting overlapping leaves appears in the Definition workspace.

  6. Edit the SQL object as needed.

Specifying Information for Tree Keys

In order for the PS_TDM_SQLOVERLAP_SQL object to work properly, you must insert the following PeopleSoft-delivered tree-specific metavariables into the SQL:

  • #setid# = SETID

  • #efftdatein# = EFFTDATEIN

  • #setcntrlvalue# = SETCNTRLVALUE

  • #treename# = TREENAME

Each time the audit reaches one of these metavariables, the audit inserts the appropriate value.

Note: These metavariables work only for this SQL object. They are not systemwide metavariables.

Using Metavariables in the SQL Object

The following code provides an example of using metavariables:

SELECT /*+ USE_HASH( B ), ORDERED */ A.RANGE_FROM, A.RANGE_TO, B.RANGE_FROM, B.RANGE_TO 
  FROM PSTREELEAF A, PSTREELEAF B 
 WHERE A.SETID = '#setid#' 
   AND A.SETCNTRLVALUE = '#setcntrlvalue#' 
   AND A.TREE_NAME = '#treename#' 
   AND A.EFFDT = %DateIn('#effdtdatein#') 
   AND A.DYNAMIC_RANGE = 'N' 
   AND B.SETID = A.SETID 
   AND B.SETCNTRLVALUE = A.SETCNTRLVALUE 
   AND B.TREE_NAME = A.TREE_NAME 
   AND B.EFFDT = A.EFFDT 
   AND B.DYNAMIC_RANGE = A.DYNAMIC_RANGE 
   AND (A.RANGE_FROM <> B.RANGE_FROM 
    OR A.RANGE_TO <> B.RANGE_TO) 
   AND B.RANGE_FROM <> B.RANGE_TO 
   AND (A.RANGE_FROM BETWEEN B.RANGE_FROM AND B.RANGE_TO 
    OR (A.RANGE_TO >= B.RANGE_FROM 
   AND A.RANGE_TO < B.RANGE_TO))

The code in this example includes #setid# at each point where the SETID value should be inserted. The same applies to the #treename#, #effdtdatein#, and #setcntrlvalue# metavariables.

Using Metavariables Multiple Times Within the SQL Object

The following example shows how to use metavariables multiple times within the PS_TDM_SQLOVERLAP_SQL object. Specifically, the code from the previous example has been rewritten using metavariables in both the top SELECT and bottom SELECT statements in the Union.

SELECT A.RANGE_FROM, A.RANGE_TO, B.RANGE_FROM, B.RANGE_TO 
  FROM PSTREELEAF A, PSTREELEAF B
 WHERE A.SETID = '#setid#'
   AND A.SETCNTRLVALUE = '#setcntrlvalue#'
   AND A.TREE_NAME = '#treename#' 
   AND A.EFFDT = '#effdtdatein#' 
   AND A.DYNAMIC_RANGE = 'N' 
   AND B.SETID = A.SETID 
   AND B.SETCNTRLVALUE = A.SETCNTRLVALUE 
   AND B.TREE_NAME = A.TREE_NAME 
   AND B.EFFDT = A.EFFDT 
   AND B.DYNAMIC_RANGE = A.DYNAMIC_RANGE 
   AND (A.RANGE_FROM <> B.RANGE_FROM 
    OR A.RANGE_TO <> B.RANGE_TO) 
   AND B.RANGE_FROM <> B.RANGE_TO 
   AND A.RANGE_FROM BETWEEN B.RANGE_FROM AND B.RANGE_TO 
  UNION 
 SELECT A.RANGE_FROM, A.RANGE_TO,B.RANGE_FROM, B.RANGE_TO 
  FROM PSTREELEAF A, PSTREELEAF B 
 WHERE A.SETID = '#setid#' 
   AND A.SETCNTRLVALUE = '#setcntrlvalue#' 
   AND A.TREE_NAME = '#treename#' 
   AND A.EFFDT = '#effdtdatein#' 
   AND A.DYNAMIC_RANGE = 'N' 
   AND B.SETID = A.SETID 
   AND B.SETCNTRLVALUE = A.SETCNTRLVALUE 
   AND B.TREE_NAME = A.TREE_NAME 
   AND B.EFFDT = A.EFFDT 
   AND B.DYNAMIC_RANGE = A.DYNAMIC_RANGE 
   AND (A.RANGE_FROM <> B.RANGE_FROM 
    OR A.RANGE_TO <> B.RANGE_TO) 
   AND B.RANGE_FROM <> B.RANGE_TO 
   AND (A.RANGE_TO >= B.RANGE_FROM 
   AND A.RANGE_TO < B.RANGE_TO)