Using TreeMover

This chapter provides an overview of TreeMover and discusses how to:

• Import and export PeopleSoft 8 trees.

• Import and export with PeopleSoft 7.x trees.

• Customize TreeMover for additional node and level data records.

Note. The flat files for both the import and export processes are located in the working directory for PeopleSoft Application Engine as specified on the Process Type Definition page in PeopleSoft Process Scheduler. You can override this and create another location using the Process Definition Override page.

See Also

Defining Process Type Definitions

Understanding TreeMover

This section discusses:

• Purpose of TreeMover.

• Populated record types.

• TreeMover file formats.

• TreeMover file rules.

• File layout details.

Purpose of TreeMover

TreeMover enables you to move PeopleSoft trees between different PeopleSoft application databases. It enables you to export a tree to a flat file and to import a tree from a flat file. TreeMover moves all types of tree data, including the tree definition, tree structure, tree nodes, tree leaves, tree node data, and tree level data.

TreeMover does not support branched trees. If you import a branched tree, the branches become regular tree nodes.

Advantages of using TreeMover instead of other methods of moving trees, such as Data Mover or custom SQR, include:

• TreeMover can move trees between different versions of PeopleSoft databases, for example, from a PeopleSoft Financials 7.5 database to a PeopleSoft EPM 8 database.

• Tree Mover can move tree node data (description, setID, effective date, and effective status) for trees that have node data stored in the PS_TREE_NODE_TBL (the table that stores the tree node data is specified as part of the tree structure).

• Tree Mover can move tree level data (long description, short description, setID, effective date, and effective status) for trees that have level data stored in the PS_TREE_LEVEL_TBL—the table that stores the tree level data is specified as part of the tree structure.

• TreeMover uses the tree APIs, which means the same logic used for the PeopleSoft Tree Manager application is used to load and unload trees using TreeMover.

All the same edit checks are performed on a tree created with TreeMover as are performed on a tree created manually with PeopleSoft Tree Manager because both use the same functionality to create the tree. Because TreeMover processes are initiated from standard PeopleSoft pages, the ability to import and export tree data can be restricted by using PeopleSoft standard security mechanisms for limiting access to pages. If a source tree uses branches, you need to recreate them after TreeMover finishes its import operation.

Here are some example tasks that you can use with TreeMover:

• Load a winter tree with nodes from a legacy financial database quickly and easily into PeopleSoft 8 databases.

• Automatically replace an account tree every month with a new tree based on current information.

Note. TreeMover currently supports fixed-format files only.

TreeMover does not support related language tables.

When you export a tree containing text data, the data is saved in the codepage of the server—for example, CP1252 Western European on English Windows, or CP932 Shift-JIS on Japanese Windows. Any characters which are not valid in the server's codepage are converted into question mark characters.

TreeMover consists of three parts: a utility to export a tree from a PeopleSoft 8 database, a utility to import a tree into a PeopleSoft 8 database, and a utility to export a tree from a PeopleSoft 7.x database. The import and export functionality for a PeopleSoft 8 database is implemented as a PeopleSoft Application Engine application process. As with other Application Engine processes, they can be scheduled to run automatically from the Process Scheduler. The Application Engine process uses PeopleCode Tree APIs, PeopleCode File APIs, and file layout definitions. The utility that enables you to export trees from a PeopleSoft 7.x database is implemented as an SQR program (TMDOWNLD.SQR).

Populated Record Types

TreeMover uses the following PeopleTools system tables for trees during both the tree import and tree export processes:

• PSTREEDEFN

• PSTREENODE

• PSTREELEAF

• PSTREESTRCT

• PSTREELEVEL

• PS_TREE_LEVEL_TBL

• PS_TREE_NODE_TBL

TreeMover also reads the PSSTATUS table during the tree export process to identify the version of the tree data exported.

TreeMover File Formats

As delivered, TreeMover uses seven different file layouts. All the data for an exported tree is contained in a single file. Prior to a change in the file layout used for the export file, TreeMover writes a header record to the file to indicate what the next file layout is. The following table lists the standard file formats, along with their actual file layout name and the text of the header record that precedes a given layout data:

The TreeMover export program processes the data in the order listed in the preceding table.

If you write your own export file, then you must adhere to the following rules:

• The Tree Structure record, if present, must always come first in the file.

  If it is not present, the TreeMover import program defaults to the record layouts used prior to release 8.1.

• The Tree Definition record, if present, must come after the Tree Structure record.

• The Tree Level records—only for trees with levels—must come before the Tree Node and Leaf records.

• The Tree Node and Leaf records are required for any export file.

• Prior to the change in the record layout, you must precede the next data record with the appropriate header record for that file format.

The TreeMover file formats and contents are:

• 999TREE_VERSION

  Tree Version Data

• 999TREE_STRCT

  Tree Structure Data

• 999TREE_DEFN

  Tree Definition Data

• 999TREE_USERLVL

  Tree Level Data 1

  Tree Level Data 2

• Tree Level Data N

• 999TREE_LEVEL

  Tree Level 1

  Tree Level 2

• Tree Level N

• 999TREE_USERND

  Tree Node Data 1

  Tree Node Data 2

• Tree Node Data N

• 999TREE_NODE

  Tree Node 1

  Tree Node 2

• Tree Detail 1

• Tree Detail 2

• Tree Node 3

• Tree Node 4

• Tree Node N

• Tree Detail 5

• Tree Detail N

This is an example of TreeMover:

TreeMover File Rules

TreeMover reads these records in the following strict order during an import:

1. PeopleTools Version (optional).

   This record is needed only if loading data from an 8.1 or later PeopleSoft database.

2. Tree Structure (optional).

   This record is needed only if the structures do not already exist. One or more structure records can be loaded.

3. Tree Definition (required).

   The information needed to create a tree. Only one tree definition record can exist in the input file.

4. Tree Level Data (optional).

   The detailed level data for a tree. One or more level data records can exist in the input file.

5. Tree Levels (required for leveled trees).

   The levels defined for the tree. Must include enough to define all the nodes. One or more level records can exist in the input file.

6. Tree Node Data (optional).

   The detailed tree node information: description, effective date, and so on.

7. Tree nodes and leaves (required).

   Tree node and detail information. Each node must contain either a parent node or a previous sibling node. The referenced node—parent or sibling—must precede the node that references it in the input file.

   Detail values for a tree must exist in the database before importing the tree.

   One or more node records listed in level plus sibling order—that is, the root node on the first level, nodes on the second level, and so on—can exist in the input file.

File Layout Details

Each record starts with a unique identifier for that record type, followed by the associated column data. All data is in a fixed format.

Tree Structure Layout (TREE_STRUCTURE)

For the Tree Structure layout, most column names map directly to the PSTREESTRCT record.

Note. The tree structure record is optional if the database already has the structure defined for the new tree.

The following table provides file layout details:

TreeMover Tree Definition Layout (TREE_DEFN)

The tree definition record columns require either a tree structure defined in the database or a previous tree structure record in the file. For the Tree Definition layout, most column names map directly to the PSTREEDEFN record.

The following table provides tree definition layout details:

Tree User Level Data Layout (TREE_USERLEVEL)

The columns in this layout correspond to the columns in the TREE_LEVEL_TBL record.

The following table provides tree user level data layout details:

Tree Level Record Layout (TREE_LEVEL)

The tree level layout requires a tree definition record in the file previous to this record type. The columns in this layout correspond to the columns in the PSTREELEVEL record.

Note. No-level records should be defined for no-level trees.

The following table provides tree level layout details:

Tree Node Data Layout (TREE_USERNODE)

The columns in this layout correspond to the columns in the TREE_NODE_TBL record.

The following table provides tree node data layout details:

Tree Node Record Columns (TREE_NODE)

Tree node record columns require a tree definition record in the file previous to this record type and level records if the tree has levels. Node records must have parents defined before their children. Detail records can be interspersed between node records.

Note. Node type is either G or R. G is for standard nodes, and R is for Query Access Group record nodes. Branch nodes are not supported at this time.

The following table provides tree node record details:

Importing and Exporting PeopleSoft 8 Trees

This section provides an overview of using TreeMover with PeopleSoft 8 databases and discusses how to:

• Export PeopleSoft 8 trees to an external file.

• Import PeopleSoft 8 trees from an external file.

Understanding TreeMover and PeopleSoft 8.x Trees

As with other PeopleSoft Application Engine processes, you initiate the TreeMover process from PeopleSoft pages. You need to submit a few required parameters at runtime, including the file name for each tree import (load) or tree export (unload). You can run TreeMover each time you need to load a tree, or you can set PeopleSoft Process Scheduler to run a tree load process automatically.

To view the results of TreeMover processes, perform the following steps from the Tree Import or Tree Export page:

1. Click the Process Monitor link.

   The Process List page appears. The run status for your process will appear as Success, No Success, or Warning.

   Note. If you receive a No Success or Warning status, you should also review the TreeMover log file. This file may include additional information that the Process Monitor does not include.

2. Click the Details link.

   The Process Detail page appears.

3. Click the Message Log button.

4. Click the Explain button for further details on each message.

You can also view the log output file. To find this file, navigate to the log_output directory. The location of the log_output directory is specified in the Process Scheduler configuration file. After you are accessed the log_output directory, navigate to the following path: /AE_TREEMOVER_<PROCESS_ID>/TreeMover-<Date-Time>.log.

This example shows the full path to the log output file – the italicized portion of the path is specified in the Process Scheduler configuration file:

C:\Documents and Settings\admin\psft\pt\8.50-810-R1\appserv\prcs\QEDMO\log_output\AE_TREEMOVER_59\TreeMover-2009-06-18-09.44.59.log

This is a sample of the log file:

The delivered TreeMover application can move only tree node data that is associated with the PS_TREE_NODE_TBL and only tree level data that is associated with the PS_TREE_LEVEL_TBL. You can modify TreeMover to support other tables for node and level data.

Note. Creating TreeMover customization for moving non-standard node and detail user data is a complicated process. Oracle recommends using PeopleSoft Data Mover or other database tool when you move user data from a source database to a target database.

When a tree replaces an existing tree using the Tree Import Utility, the system import a new tree using a unique name and check if the tree creation process is successful. If the tree creation process turns out to be successful, the system delete the existing tree and rename the newly created tree. If the import file from which you are importing is corrupted, you don't lose your existing tree.

Also, if the existing tree was secured by Definition Security, it is automatically removed from the Definition Security Group at the time of deletion. Because the removal of the tree from the Definition Security Group occurs prior to the import, the utility has no knowledge of the tree being secured, and therefore, the new tree will have to be manually secured after the import is complete.

See Also

Modifying the TreeMover Application Engine Program

Pages Used to Import and Export PeopleSoft 8 Trees

Exporting PeopleSoft 8 Trees to an External File

Access the Tree Export page by selecting Tree Manager, Tree Utilities, Export Tree.

This is an example of the Tree Export page:

To export a tree to an external file:

1. In the Tree Export page, enter the correct output file name.

   The flat file is sent to the working directory for PeopleSoft Application Engine as specified on the Process Type Definition page in Process Scheduler. If this is not the location you want, enter the valid path name for the directory on the application server that runs the process.

2. Enter the tree name, effective date, and tree key value.

   Note. The tree key value applies to only setID, business unit, or user-defined key trees (tree key type is set in the tree structure). For trees that have no additional key value, the field is not available.

3. Select the tree data to export.

   You can export the tree structure data, the tree definition, the tree levels, the tree level data, the tree nodes and leaves, and the tree node data. You also can export the tree definition, tree level, and tree node/leaf data.

   Note. You need to select Tree Structure if the tree structure does not already exist in the target database. You can also select the Tree User Level option to move related data from the PS_TREE_LEVEL_TBL table, and select the Tree User Nodes option to move related data from the PS_TREE_NODE_TBL.

4. Click the Run button to move the tree.

5. Verify that the settings on the Process Scheduler Request page are correct, and click OK to run the process.

6. Check the run status in the Process Monitor.

   If an Error or Warning status exists for the process, check the TreeMover log file which may contain additional information that describes the problem. Potential problems are not limited to – but may include – the following:

   • You tried to export tree level data and the tree has no levels.

   • You tried to export tree user level data and the tree does not use the PS_TREE_LEVEL_TBL for level data.

   • You tried to export tree user node data and the tree does not use the PS_TREE_NODE_TBL.

Importing PeopleSoft 8 Trees from an External File

Access the Tree Import page by selecting Tree Manager, Tree Utilities, Import Tree.

Note. PeopleSoft Tree Manager does not accept wildcard characters. To prevent creating a corrupted tree, the Tree Import utility checks for invalid tree node names.

This is an example of the Tree Import page:

To import an existing PeopleSoft tree:

1. In the Tree Import page, enter the correct input file name.

   The flat file is sent to the working directory for PeopleSoft Application Engine as specified on the Process Type Definition page in PeopleSoft Process Scheduler. If this is not the location you want, enter the valid path name for the directory on the application server that runs the process.

2. Select a save method.

   By default, you should use the Save option. However, if you think the tree might not pass all the PeopleSoft tree audits, then select the Save Draft option, which saves the tree in draft mode and functions just as it does in PeopleSoft Tree Manager.

3. If the tree already exists in your database, select the Replace Tree if Exists option.

   Note. If the tree already exists in your database and you do not select the Replace Tree if Exists option, the tree will not be imported.

4. Select the Load Tree Defn from File (load tree definition from file) option if the tree definition is contained in the input file and you want to load the tree with the same tree definition values.

   Note. In most cases, you want to load the tree definition from the file. If you select this option and the tree definition does not exist in the input file, then the load process fails.

5. Enter the basic tree information if you are not loading the tree definition from the input file.

   If you are not loading the tree definition from the input file, then you are required to specify all the basic tree information on the run control page. These fields correspond to the same values that you’d have to enter if you were creating the tree using PeopleSoft Tree Manager. TreeMover skips the input record that contains the tree definition information and instead use the values that you enter.

   You must load the tree definition from the file if you’re also loading the tree structure from the file. If you try to load the structure from the file, but have overridden the definition values on the page, then the process will not run completely.

   TreeMover loads a new tree structure only if the structure doesn't already exist.

   Note. If your TreeMover data file contains user level data or user node data and you are changing the setID of your tree on import, the user data will retain the original setID. TreeMover import does not support the changing of user data setIDs.

6. Click the Run button to move the tree.

7. Verify the successful completion of the process by checking the run status of the process in the Process Monitor page.

   If you receive either a run status of Warning or No Success, view the message log explanations for details and recommendations. You should also view the TreeMover log for more information.

   Note. You can also check the log file. If the process completed successfully, the log file states PeopleSoft TreeMover Completed Successfully.
   
   If you’re loading a tree that contains detail values, then those detail values must exist in the target database before you load the tree. If they do not exist, then the tree import process fails.

Importing and Exporting PeopleSoft 7.x Trees

This section provides an overview of the TMDOWNLD SQR program and discusses how to:

• Install the SQR program.

• Run the SQR program.

Understanding the TMDOWNLD SQR Program

TreeMover provides the TMDOWNLD SQR program for unloading a PeopleSoft tree from a PeopleSoft 7 or 7.5 database. (The SQR should also work against a PeopleSoft 6 database, but it has not been tested against that version and is not certified for it.) This program exports most of the same tree information as the Tree Export functionality provided with PeopleTools 8. Differences include:

• Tree categories are not exported; the category defaults to DEFAULT.

• Tree performance options are not exported.

• Tree structures are not exported.

Installing the SQR Program

To install the TMDOWNLD SQR program, copy the TMDOWNLD.SQR file from your PeopleTools 8 PS_HOME/SQR directory to the SQR directory for your PeopleTools 7 or 7.5 install.

Note. If you do not have a copy of TMDOWNLD.SQR, contact PeopleSoft Customer Service.

When you run this SQR against a given version of a PeopleSoft database, you must run it with the common SQC files that are delivered for that PeopleTools version. For example, if you run it against a PeopleSoft 7 database, you should run it so that it uses the SQC files delivered with PeopleTools 7. Otherwise, the process will not run correctly.

Running the SQR Program

To run the TMDOWNLD SQR program:

1. Run the TMDOWNLD SQR program from the SQR command line.

2. Follow the system prompts.

   Note. There is no associated run control page with this SQR for PeopleSoft 7 or 7.5 databases. In order to schedule this process to run without manual intervention, you need to create a run control for the process. See your PeopleTools manual for more information on creating run controls.

See Also

SQR documentation.

Introducing the Sample SQR Program

Customizing TreeMover for Additional Node and Level Data Records

This section discusses how to:

• Modify the TreeMover Application Engine program.

• Modify the TreeMover SQR program.

Note. Creating TreeMover customization for moving non-standard node and detail user data is a complicated process. Oracle recommends using PeopleSoft Data Mover or other database tool when you move user data from a source database to a target database.

The TreeMover program only moves data in the standard PeopleSoft tree tables. PeopleSoft trees generally have application node data stored in the PS_TREE_NODE_TBL table and application level data stored in the PS_TREE_LEVEL_TBL table.

However, the tables used to hold the application data for the detail values, such as leaves, are always application-specific; there are no default tables for storing that application data. For example, you might have trees that reference employees as leaf values, and the employee information is stored in the PERSONAL_DATA_TBL table. TreeMover does not handle moving data from application tables. Generally, when you move trees between different PeopleSoft databases, you should move the application data using other means, such as with DataMover. However, you can also customize the TreeMover program to copy other application data. As with any PeopleSoft application, you should avoid performing any customizations whenever possible.

Note. Before customizing TreeMover, you should consider the tables for which you intend to add support. Application tables that hold data used by other parts of the PeopleSoft system should be moved by other means, such as DataMover.

Modifying the TreeMover Application Engine Program

The TreeMover process is a PeopleSoft Application Engine process called TREEMOVER. To add support for additional node and level tables, you must perform two basic steps: create the file layout to be used for the new data and modify the TREEMOVER_AET PeopleCode according to the following steps.

Note. You should not need to make any modifications to the actual Application Engine code.

To modify the TreeMover Application Engine program:

1. Create file layouts for your desired tree node data record or tree level data record using Application Designer file layout functionality.

   Use one of the existing TreeMover file layouts as an example (TREE_DEFN, TREE_NODE, and so on). Each file layout you add for TreeMover must have a unique file record ID, which is specified as part of the file layout record properties; the existing TreeMover file layouts use numbers 1 through 6.

   Each file layout you add must also have the UPDATE_ACTION field.

2. In the TREEMOVER_AET record, modify the PROCESS_INSTANCE FieldFormula PeopleCode as follows:

   • In the isValidUserNodeRec function, add your desired PeopleSoft records for node data to the condition check.

   • In the isValidUserLevelRec function, add your desired PeopleSoft records for level data to the condition check.

   • In the setUserNodeRecLayout function, add code to set the correct file layout based on the node data record.

   • In the setUserLevelRecLayout function, add code to set the correct file layout based on the level data record.

   • In the getUserNodeData function, add code to get data from your new node records.

   • In the getUserLevelData function, add code to get data from your new level records.

Note. In the steps above, do not modify the original code in the functions. You should add new code only within new “if” conditions. If you change any of the existing code, trees based on the PS_TREE_LEVEL_TBL and PS_TREE_NODE_TBL will no longer be handled with TreeMover.

Modifying the TreeMover SQR Program

If you have modified the TREEMOVER Application Engine program, then you also need to make similar changes to the TMDOWNLD.SQR, assuming you’re using that SQR to download trees from a PeopleSoft 7.x database.

To modify the TMDOWNLD SQR program:

1. Change the Download-TreeUserLevel procedure to handle the new tree level data tables.

   Be sure to add conditional logic so that the existing logic is still executed when the tree structure uses PS_TREE_LEVEL_TBL. The tree level record name is stored in the variable &TS.LEVEL_RECNAME.

2. Change the Download-TreeUserNode procedure to handle the new tree node data tables.

   Be sure to add conditional logic so that the existing logic is still executed when the tree structure uses PS_TREE_NODE_TBL. The tree node record name is stored in the variable &TS.NODE_RECNAME.