Budgeting Application Diagrams

Diagram 01. Budget Requests

This diagram highlights the dbo27.budget_requests table used to store data of budget requests.

The dbo27.budget_requests table has a unique key that includes the following columns: entity_id, category_id, time_id, and row_index.

So, budget requests may have multiple lines with the same members but different row_index.

Use xls27.usp_budget_request1 and xls27.usp_budget_request2 to edit data.

Diagram 02. Budget Currency Rates

This diagram highlights the dbo27.budget_currency_rates table.

Use xls27.usp_budget_currency_rates to edit data.

Diagram 03. Budget Opening Balances

This diagram highlights the dbo27.budget_balances table used to store budget opening balances.

Use xls27.usp_budget_balances to edit data.

The current version does not use the subsidiary_account_id values as it uses opening balances for such accounts from the balance00 field of the dbo27.budget_requests table.

Diagram 04. Journals

This diagram highlights the dbo27.journals table used to store accounting journal data.

Use xls27.usp_journal to edit data.

Diagram 05. Actual Currency Rates

This diagram highlights the dbo27.actual_currency_rates table.

Use xls27.usp_actual_currency_rates to edit data.

Diagram 06. Actual Opening Balances

This diagram highlights the dbo27.actual_balances table used to store actual opening balances.

Use xls27.usp_actual_balances to edit data.

Diagram 07. Configuration Tables

This diagram highlights the primary tables used to configure the application.

The dbo27.members table contains analytic members grouped by dimensions.

The first dimension is the Accounts. It contains members like Revenue or Expenses.

Most of the member attributes are actual for this dimension.

Analysts may define units and VAT rates for account members. Otherwise, users may choose values themselves in the budget requests.

The dbo27.ledger_accounts table contains ledger accounts used in accounting and financial reports.

The dbo27.members, dbo27.ledger_accounts, and dbo27.report_sections tables have properties used to calculate built-in financial reports like Income Statement, Cash Flow, and Balance Sheet.

Diagram 08. Report Sections

This diagram highlights tables used to configure built-in reports executed using the xls27.usp_budget_reports stored procedure.

The application contains default settings to build standard reports.

Analysts may change the following features:

- Row name text (we recommend using dbo27.translations for this purpose)
- Row format
- Row order (using the section column)
- Row reference number
- Lines before and after
- Row parents

Diagram 09. Reporting Engine

This diagram highlights tables used to produce financial reports executed using xls27.usp_budget_reports, xls27.usp_actual_reports, and other stored procedures.

Usually, you do not need to change these system tables.

You use the dbo27.ledger_account_types table to customize the dbo27.ledger_accounts table in the setup phase. Also, you use the dbo27.report_section_types table to customize the dbo27.report_sections table.

It is enough to build financial reports using this engine.

This diagram shows the rs_ prefix instead of report_sections_ and the la_ prefix instead of the ledger_accounts_ one.

Diagram 10. Saved Reports

The SaveToDB add-in for Microsoft Excel allows saving named ranges to a database, into tables directly, or using stored procedures.

The application uses this feature to save Excel reports to a database through the xls27.usp_saved_reports store procedure.

As a result, users may save and load saved reports from a database to compare reports or build consolidated statements.

The dbo27.saved_reports table contains data of such saved reports.

Diagram 11. Member Permissions

The dbo27.member_permissions table contains user permissions on members.

Use xls27.usp_member_permissions to check and change permissions.

Note that the application checks these permissions for users who are the members of the budgeting_app_users role.

Members of other roles have access to all members.

Diagram 12. Member Relations

The dbo27.member_relations table allows setting permissions for member pairs.

For example, you may deny the update for the budget of 2020 but allow the update for the forecast of 2020.

Use the xls27.usp_member_relations and xls27.usp_budget_closed_periods procedures to manage such permissions.