top of page

Recursive selects to rollup financial data

In this post I am going to describe the financial roll-up procedure that was developed using Postgres that is a part of an enterprise system platform that has an inherent capability to exchange data between systems.

The financial roll-up procedure is used to roll-up journal entry amounts for the Account Hierarchy and the Cost Centre hierarchy.

This is a small but powerful procedure that uses multiple recursive selects to accomplish this task.

The Account and Cost Center are hierarchical structures so they can meet the needs of large and small organizations alike. Large organizations can have thousands of Accounts with many levels in the hierarchy and hundreds of Cost Centers. Small organizations will only have tens of accounts and only one Cost Center that represents their organization.

The financial enquiry function allows users to view the hierarchical Account structure with balances at every level for the entire organization. From there a user could pick an Account like office expenses and see it broken down by the Cost Center hierarchy. If one Cost Center was abnormally high, the user could navigate back to the Account structure for that Cost Center to see which Account(s) was causing the difference. The user can endlessly navigate back and forth between these hierarchies to answer any questions they may have. Furthermore, at any time they can drill into the balances for an Account/Cost Center combination to see a monthly or annual history. All of these features are possible because of the way the financial roll-up procedure rolls up GL Entries.

This procedure also rolls up budget entries and rolls-up reporting amounts. If you are interested in the source code, please click on the following link and look up the spGLAccountBalanceUpdate procedure.

You should also check out the 020.120 Financial Module video in to learn more about the rationale for the financial system design.

I hope the source code for this procedure will be useful to you in your next project. If you are interested in learning more about the 3D Enterprise System platform, please go to

Thank you for your attention. I appreciate your comments, questions and likes.

4 views0 comments

Recent Posts

See All

Need help creating an application framework

In this post, I have a #QuestionForGroup about creating an application framework to connect to the open-source platform I created using Postgres. The platform allows us to create enterprise systems th


bottom of page