Using Tree Flattener

Page Name

Definition Name

Usage

Effective Date Range Map Page

EDR_CF_DEFN

Map each ChartField table to an EDR table

Build Chartfield EDR Request Page

EDR_CF_REQ

Run the build effective date range process, which creates missing effective dated entries in the EDR table

OTBI Tree Fields Page

OI_TFLT_FIELDS

Manage the flattened table for each detail tree field. The delivered metadata supports flattened tree data for General Ledger ChartFields, including Project ID.

Tree Field Tables Page

OI_TFLT_FIELDS

Map ChartFields to flattened tree tables.

Flatten Tree Log Page

FS_TFLT_LOG

View the status of trees that have been flattened.

Tree Flattener Request Page

OI_TFLT_REQ

Run the PS/FS Tree Flattener process (OI_TFLT), which allows you to schedule a request periodically to pick up changes that occur in the tree definitions and ChartField values.

PeopleSoft delivers the Tree Flattener functionality, which is used to flatten tree data (and delete flattened tree data) for specified PeopleSoft trees. Once you have run the PS/FS Tree Flattener process (OI_TFLT), you can use PS Query to include the flattened tree data and filter the data by tree nodes and tree levels.

You can use the Tree Flattener for the following types of trees:

  • Trees with strictly enforced levels (level determines the hierarchy of the nodes).

  • Trees with loosely enforced levels (Node determines the tree hierarchy).

  • Trees with no levels (Node determines the tree hierarchy).

  • Trees with nodes and levels.

    Note: A tree should not have more than 15 nodes and levels. If a tree has 16 or more nodes and levels, then the tree flattener process does not work.

  • GL batch processes such as Allocations, Multi-Currency, Year End Close, ADB Calculations, and Build ChartField security.

  • OTBI

Note: PeopleSoft does not support tree flattener functionality for Tree Definitions that allow duplicate values or for Tree Structures that support dynamic values based on the node value.

ChartField Effective Dated Rows

The Tree Flattener feature maintains a Detail List Table for every ChartField, such as Account (DL_TFLT_ACCOUNT), that saves account, effective date, and effective end date. The effective end date is stored when a new effective date is introduced for an account. This is used for General Ledger processes that use a request date or a transaction date to select ChartField effective dated rows that belong to a tree node and level and ranges.

Note: Maintaining ChartField effective dated row (EDR) data is critical to the Tree Flattener process.

The system stores ChartField Data with effective date ranges in multiple EDR tables such as GL Accounts (GL_ACCOUNT_TBL) and Account EDR (GL_ACCOUNT_EDR). The Effective Date Range Map Page is used to store this information.

Example of Adding, Changing, or Removing ChartField Effective Dated Rows

ChartField online components maintain the effective dated range (EDR) data when an effective date is modified, added, or deleted.

These examples illustrate the department table (DEPT_EDR) when an effective data is added on January 1, 2014:

DEPTID

EFFDT

INEFFDT

12000

01–JAN-1900

31–DEC-2013

12000

01–JAN-2014

31–DEC-9999

This example illustrates the department table (DEPT_EDR) when an effective date is changed from 01/01/1900 to 01/01/2000:

DEPTID

EFFDT

INEFFDT

12000

01–JAN-2000

31–DEC-2013

12000

01–JAN-2014

31–DEC-9999

This example illustrates the department table (DEPT_EDR) when the current effective date is removed 01/01/2014:

DEPTID

EFFDT

INEFFDT

12000

01–JAN-2000

31–DEC-9999

The Tree Definition and Properties page is used to define trees.

This example illustrates the fields and controls on the Tree Definition and Properties page.

Tree Definition and Properites page

The Audits section determines how you want to handle detail values.

When the All Detail Values in this Tree check box is selected, tree log entries are impacted when you add, change, or remove effective dates using the online component. The Tree Flattener process identifies the need to refresh the data, which is performed during the subsequent run of the process.

When the All Detail Values in this Tree check box is not selected, then the Tree Flattener process only refreshes log entries when the modified ChartField value exists in the tree range, or is a value in the tree node or leaf.

For more information about the Tree Definition and Properties page, see PeopleTools: Tree Manager, “Saving and Configuring Trees”.

Use the Effective Date Range Map page (EDR_CF_DEFN) to map each ChartField table to an EDR table.

Navigation:

Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Effective Date Range Data > EDR Mapping Definition

This example illustrates the fields and controls on the Effective Date Range Map page.

 Effective Date Range Map page

The names of the tables that contain ChartField effective-date range data is delivered as system data.

Note: The Project ChartField is not listed because project data is not effective dated.

Use the Chartfield EDR Request page (EDR_CF_REQ) to run the build effective date range process, which creates missing effective dated entries in the EDR table.

Navigation:

Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Effective Date Range Data > Build CF Date Range Rqst

This example illustrates the fields and controls on the Build Chartfield EDR Request page.

 Build ChartField EDR Request page

Note: When the Tree Flattener feature is initially installed, you must run this process. After the initial run, you can run the process when ChartField effective dates are added, changed, or removed.

This process synchronizes the effective dated entries in the EDR table when ChartField effective dates are modified, added, or deleted.

SetID can be blank to run this process for all SetIDs.

Use the OTBI Tree Fields page (OI_TFLT_FIELDS) to manage the flattened table for each detail tree field. The delivered metadata supports flattened tree data for General Ledger ChartFields including Project ID.

Navigation:

Set Up Financials/Supply Chain > Operational Reporting > Tree Flattener > Tree Fields

This example illustrates the fields and controls on the OTBI Tree Fields page.

OTBI Tree Fields page

You can add your own detail tree fields and flatten tables. The PS/FS Tree Flattener process supports any detail tree fields with a field value length that is less thank or equal to 30 characters.

Note: You cannot change the flatten tree table or delete a row if the flattened tree table had data.

Field or Control

Description

Field Name

Detail tree fields upon which the Tree Flattener tables are based. PeopleSoft delivers the ChartFields and respective tables to be used in the PS/FS Tree Flattener process (OI_TFLT). You can add your own fields and respective tables to use in this process.

Flatten Table

PeopleSoft delivers a record for each delivered ChartField to be used in storing the flattened tree data. You can add your own records to be used in the tree flattening process for user-defined ChartFields. It is not recommended that you change the flatten table for the delivered ChartFields.

Use the Tree Field Tables page (OI_TFLT_FIELDS) to map ChartFields to flattened tree tables.

Navigation:

Set Up Financials/Supply Chain > Operational Reporting > Tree Flattener > Assign CF Tree Flattened Tbl

This example illustrates the fields and controls on the Tree Field Tables page.

 Tree Field Tables page

The names of the tables that contain the flattened tree data are delivered as system data.

Use the Flatten Tree Log page (FS_TFLT_LOG) to view the status of trees that have been flattened.

Navigation:

Set Up Financials/Supply Chain > Operational Reporting > Tree Flattener > View Flatten Tree Process Log

Field or Control

Description

(check box)

Select more than one row to perform an action from the Actions drop-down list box on each row at the same time.

Status

Displays one of these status:

  • Current: Flattened data reflects the specified tree and is not updated by the Tree Flattener process (unless the Force Refresh option is selected).

    The tree definition must be valid and active. If the tree definition is active but not valid (saved as draft) then the system sets the status to current. This allows users to maintain tree data in draft mode without having to worry about the impact to the batch processes.

  • No Data: Flattened data does not exist but will be updated by the Tree Flattener process.

  • Stale: Flattened data does not reflect the specified tree and/or ChartField data changes that have been made since the last time the tree was flattened.

    It will be updated by the Tree Flattener process. This can be caused by:

    • User modified the tree definition

    • User added, modified or deleted a ChartField effective dated row.

    • There is an active and valid effective dated tree definition that is more current than the flatten tree data. The logic uses the system date as the relative date for the current effective dated tree definition.

    • There is no tree definition (the user has deleted the tree)

    • Tree definition is inactive

  • Lock: prevents the refresh or deletion of the flattened data for the tree and prevents GL processes from using that data.

The user can delete a log entry from the page but only after the flatten tree data is deleted.

Refresh

Click the Refresh button to initiate the tree flattener process, which flattens tree data.

Delete Data

Click the Delete Data button to delete the flattened data.

Lock

Click the Lock button to inactivate the Refresh and Delete Data buttons. When you click the Lock button, the Refresh and Delete Data buttons cannot be selected.

Actions

Click the drop down box to perform these actions to all selected rows:

  • Delete Data

  • Refresh

Use the Tree Flattener Request page (OI_TFLT_REQ) to run the PS/FS Tree Flattener process (OI_TFLT), which allows you to schedule a request periodically to pick up changes that occur in the tree definitions and ChartField values.

Navigation:

Set Up Financials/Supply Chain > Operational Reporting > Tree Flattener > Tree Flattener Request

This example illustrates the fields and controls on the Tree Flattener Request page.

 Tree Flattener Request page

Field or Control

Description

Process Type

Select one of these values:

  • Flatten Tree: Select to flatten the request data.

  • Delete Values: Select to clear values from the flatten tree tables.

    This option is not necessary before running the Flatten Tree process (Process Type = Flatten Tree) because the process automatically removes old tree data from the flatten tree tables before inserting new flatten tree data.

    When this option is selected, the system displays only the OTBI Source Instance field.

Request Date Option

Select a date option that is used when selecting effective dated tree data.

  • As of Date: Select to enter a user-defined date for the selected tree.

  • System Date: Select to use the system date to select the closest effective data for the selected tree.

This option is not used if you specify a Tree Effective Date.

Field Name

Select a detail tree field to be used when flattening the corresponding tree data.

SetID

Select the tree SetID if applicable. The SetID is required for flattening trees that are keyed by SetID.

Business Unit

Enter a business unit. This field is available if Project Costing is installed to select project trees that are keyed by business unit. You can specify trees that are maintained by SetID or Business Unit. The tree flattener process supports trees that are keyed by SetID or Set Control Value.

Tree Name

Select a tree name, which is required when you select Flatten Tree in the Process Type field.

Tree Effective Date

(Optional) Enter the tree effective date.

OTBI Source Instance

Enter a unique number (process instance plus request sequence number), which is used by the flatten tree process to identify the tree data when Delete Values is selected in the Process Type field.

Force Refresh

Select to refresh the tree data even if the flattened tree data has a status of Current on the Flatten Tree Log Page. This is typically used if the structure of the tree has been changed.

When adding a new ChartField during the ChartField Configuration process, the effective date range and flatten tables are automatically updated. Therefore, when adding a new ChartField, you do not need to update the Tree Field Tables Page or the Effective Date Range Map Page.