If you need to denormalize or flatten PeopleSoft summer trees (trees with leaves) in your data warehouse, here is an ETL process for you to follow.
Typical PeopleSoft summer tree structure
SQL to flatten the PeopleSoft summer tree
Substitute TREE_NAME with the name(s) of your summer tree(s) on line 54 below.
Result of the SQL
|30000||39999||All||Fund Balance||Fund Balance||Fund Balance|
|40000||47999||All||Revenue||Operating Revenues||Operating Revenues|
|48000||48999||All||Revenue||Nonoperating Revenues||Nonoperating Revenues|
|49000||49999||All||Revenue||Operating Revenues||Operating Revenues|
|50000||58999||All||Expense||Personal Service||Salary and Wage|
|60000||89999||All||Expense||NonPersonal Service||NonPersonal Service|
If you don’t know SQL or need to learn more about SQL, I would recommend that you buy Learning SQL by Alan Beaulieu. It will help you better understand the SQL provided above and give you the tools you need to change it for your needs.