Tuning the Performance of the Overlapping Detail Ranges Audit

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, and then Programs, and then PeopleTools 8.x, and then Application Designer.

2. Select File, and then 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)