Replacing DEPT_CRx with "in tree" logic

This article provides the steps to replace the legacy DEPT_CRx fields with "In Tree" logic.

Detailed Information

The legacy DEPT_CRx fields are still in use. It has been many years since finance switched to using a tree structure; however, the descriptions of the departments are not shown.

Steps to replace DEPT_CRx Fields

1. When DEPT_CRx fields are used in the SQL in a Discoverer report or a PS Query, you need to use "in tree" criteria instead when rebuilding the query in MaineStreet Financials.

• The "DEPT_CR"  or "Department - Campus Responsibility" Fields are legacy data from the FAST System, which PeopleSoft replaced.

• Extract the departments returned by the "DEPT_CR" Discoverer logic to find the best replacement criteria using DEPTID = "in tree".

• The match should return all departments, and as new departments are added, it will return more.

2. Go to the "Criteria" tab on Query Manager and choose "in tree" under Condition Type

3. Click "New Node List" and search "UMS_DEPT_ORG" for the tree name. Pick the most recent tree from the list. (The tree gets updated every year.)

4.  Find "5NSFA" by expanding the path: ALLDEPTS> UMS05> 5EXC_VP_PROVOST_LVL3 > 5NSFA. Click the little icon next to "5NSFA- Natural Sci, Forestry & Agric" to add to the node selection list.

5. Click "OK," and the window will be closed. Click "OK" again for the Edit Criteria page. This process adds the condition under the criteria.
6. Don't add extra criteria using the UM_DEPT_CR_# field.
7. Show the tree to the user and make sure the path is correct. The path is campus-specific, so don't put a prompt for campus in the query.

Financials Reporting table and tree

Some fields from the tree are on the UM_GLQ_REPRTING table (Financials reporting table). The articles below explain the relationship between the reporting table and the tree.

• Tree: UMS_DEPT_F
• Tree: UMS_DEPT_ORG
• Tree: UMS_PROJECT
• Tree: UMS_PROGRAM

Environment

• PeopleSoft Financials (FNPRD)