Budgeting Application Tables

Table	Description
dbo27.actual_balances	This user table contains actual opening balances.
dbo27.actual_closed_periods	This user table contains actual closed periods.
dbo27.actual_currency_rates	This user table contains actual currency rates.
dbo27.budget_balances	This user table contains budget opening balances.
dbo27.budget_currency_rates	This user table contains budget currency rates.
dbo27.budget_requests	This user table contains data of budget requests.
dbo27.companies	This user table contains companies that hold budget and accounting records.
dbo27.dimensions	This system table contains dimensions.
dbo27.entry_sides	This system table contains two records: debit and credit.
dbo27.journals	This user table contains data of bookkeeping journals.
dbo27.ledger_account_special_types	This system table contains special system types of ledger accounts.
dbo27.ledger_account_types	This system table contains ledger account types.
dbo27.ledger_accounts	This user table contains ledger accounts.
dbo27.member_groups	This user table contains relations between subsidiary accounts and subsidiary groups.
dbo27.member_permissions	This user table contains member permissions by users.
dbo27.member_relations	This user table contains select and update permissions for cross-dimension members.
dbo27.member_types	This system table contains member types.
dbo27.members	This user table contains dimension members.
dbo27.report_section_accounts	This system table contains report sections of report account types.
dbo27.report_section_total_types	This system table contains types of the total report column.
dbo27.report_section_types	This system table contains built-in report section types.
dbo27.report_section_value_types	This system table contains types of calculating report values.
dbo27.report_sections	This user table contains report sections.
dbo27.report_types	This system table contains a list of built-in budget reports.
dbo27.saved_report_types	This user table contains custom types of saved reports.
dbo27.saved_reports	This user table contains data of saved budget reports.
dbo27.subsidiary_groups	This user table contains subsidiary groups.
dbo27.translated_tables	This system table contains tables that support the translation of elements.
dbo27.translations	This user table contains company-related translations.
dbo27.units	This user table contains units.
dbo27.users	This user table contains application users.
dbo27.vat_rates	This user table contains VAT rates.

dbo27.actual_balances

This user table contains actual opening balances.

Trigger	Type	Comment
tr_actual_balances_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_actual_balances_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	account_id	int		dbo27.ledger_accounts.id
	subsidiary_account_id	int	✓	dbo27.members.id
	balance	money	✓	Opening balance in the account currency
	balance$	money	✓	Opening balance in the functional currency
	debit	money	✓	Debit balance in the account currency (calculated)
	credit	money	✓	Credit balance in the account currency (calculated)
	debit$	money	✓	Debit balance in the functional currency (calculated)
	credit$	money	✓	Credit balance in the functional currency (calculated)
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_actual_balances to edit balances.

Set positive balances for active accounts and negative balances for equity and liability accounts.

dbo27.actual_closed_periods

This user table contains actual closed periods.

Trigger	Type	Comment
tr_actual_closed_periods_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_actual_closed_periods_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	date	date		The first date of the closed month
✓	company_id	int		dbo27.companies.id
	is_closed	bit		1 - the month is closed for changes 0 - the month is opened for changes
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_actual_closed_periods to manage closed periods.

dbo27.actual_currency_rates

This user table contains actual currency rates.

Trigger	Type	Comment
tr_actual_currency_rates_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_actual_currency_rates_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	currency_id	int		dbo27.units.id
✓	date	date		Date
	rate	smallmoney		Currency rate
	is_checked	bit		1 - the rate is checked 0 - the rate is not checked
	is_calc_date	int		1 - calculate gains and losses on currency differences in this date automatically
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_actual_currency_rates to set currency rates.

Note that the application calculates related data automatically.

dbo27.budget_balances

This user table contains budget opening balances.

Trigger	Type	Comment
tr_budget_balances_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_budget_balances_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	category_id	int		dbo27.members.id
	time_id	int		dbo27.members.id
	account_id	int		dbo27.ledger_accounts.id
	debit	money	✓	Debit balance at the beginning
	credit	money	✓	Credit balance at the beginning
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains opening balances on ledger account levels only.

Users may specify detailed balances of subsidiary accounts in the balance00 field on budget requests.

Use xls27.usp_budget_balances to edit data.

dbo27.budget_currency_rates

This user table contains budget currency rates.

Trigger	Type	Comment
tr_budget_currency_rates_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_budget_currency_rates_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	category_id	int		dbo27.members.id
✓	time_id	int		dbo27.members.id
✓	unit_id	int		dbo27.units.id
	balance00	float	✓	Currency rate for initial balances
	period01	float	✓	Currency rate for period 01
	balance01	float	✓	Currency rate for balances 01
	period02	float	✓	Currency rate for period 02
	balance02	float	✓	Currency rate for balances 02
	period03	float	✓	Currency rate for period 03
	balance03	float	✓	Currency rate for balances 03
	period04	float	✓	Currency rate for period 04
	balance04	float	✓	Currency rate for balances 04
	period05	float	✓	Currency rate for period 05
	balance05	float	✓	Currency rate for balances 05
	period06	float	✓	Currency rate for period 06
	balance06	float	✓	Currency rate for balances 06
	period07	float	✓	Currency rate for period 07
	balance07	float	✓	Currency rate for balances 07
	period08	float	✓	Currency rate for period 08
	balance08	float	✓	Currency rate for balances 08
	period09	float	✓	Currency rate for period 09
	balance09	float	✓	Currency rate for balances 09
	period10	float	✓	Currency rate for period 10
	balance10	float	✓	Currency rate for balances 10
	period11	float	✓	Currency rate for period 11
	balance11	float	✓	Currency rate for balances 11
	period12	float	✓	Currency rate for period 12
	balance12	float	✓	Currency rate for balances 12
	row_formulas	nvarchar(max)	✓	Excel formulas used to calculate values. This feature is enabled with the SaveToDB add-in only.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_budget_currency_rates to select and edit data.

Initialize units in the dbo27.units table using the xls27.usp_units procedure before the first use.

dbo27.budget_requests

This user table contains data of budget requests.

Trigger	Type	Comment
tr_budget_requests_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_budget_requests_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	entity_id	int		dbo27.members.id
	category_id	int		dbo27.members.id
	time_id	int		dbo27.members.id
	row_index	int		The row index
	row_format	int	✓	Custom row format
	description	nvarchar(255)	✓	Custom comment
	account_id	int	✓	dbo27.members.id
	subsidiary_account_id	int	✓	dbo27.members.id
	region_id	int	✓	dbo27.members.id
	product_id	int	✓	dbo27.members.id
	vat_rate_id	int	✓	dbo27.vat_rates.id
	unit_id	int	✓	dbo27.units.id
	total_payments	money	✓	Total payments
	total_charges	money	✓	Total charges
	balance00	money	✓	Balance at the beginning
	payments01	money	✓	Payments of period 01
	charges01	money	✓	Charges of period 01
	balance01	money	✓	Balance at the end of period 01
	payments02	money	✓	Payments of period 02
	charges02	money	✓	Charges of period 02
	balance02	money	✓	Balance at the end of period 02
	payments03	money	✓	Payments of period 03
	charges03	money	✓	Charges of period 03
	balance03	money	✓	Balance at the end of period 03
	payments04	money	✓	Payments of period 04
	charges04	money	✓	Charges of period 04
	balance04	money	✓	Balance at the end of period 04
	payments05	money	✓	Payments of period 05
	charges05	money	✓	Charges of period 05
	balance05	money	✓	Balance at the end of period 05
	payments06	money	✓	Payments of period 06
	charges06	money	✓	Charges of period 06
	balance06	money	✓	Balance at the end of period 06
	payments07	money	✓	Payments of period 07
	charges07	money	✓	Charges of period 07
	balance07	money	✓	Balance at the end of period 07
	payments08	money	✓	Payments of period 08
	charges08	money	✓	Charges of period 08
	balance08	money	✓	Balance at the end of period 08
	payments09	money	✓	Payments of period 09
	charges09	money	✓	Charges of period 09
	balance09	money	✓	Balance at the end of period 09
	payments10	money	✓	Payments of period 10
	charges10	money	✓	Charges of period 10
	balance10	money	✓	Balance at the end of period 10
	payments11	money	✓	Payments of period 11
	charges11	money	✓	Charges of period 11
	balance11	money	✓	Balance at the end of period 11
	payments12	money	✓	Payments of period 12
	charges12	money	✓	Charges of period 12
	balance12	money	✓	Balance at the end of period 12
	row_formulas	nvarchar(max)	✓	Excel formulas used to calculate values. This feature is enabled with the SaveToDB add-in only.
	row_comments	nvarchar(max)	✓
	transaction_start_time	datetime	✓	The start time of the transaction used to update budget request rows.
	is_empty	bit	✓	0 - the row is empty 1 - the row has data
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

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

dbo27.companies

This user table contains companies that hold budget and accounting records.

Trigger	Type	Comment
tr_companies_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_companies_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	code	nvarchar(50)		Company code
	name	nvarchar(100)		Company name
	default_language	varchar(10)	✓	The language of company elements like accounts, members, etc.
	sort_order	int	✓	Sort order
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application allows budgeting and accounting for multiple companies in the same database.

All user tables use direct or indirect company_id as a foreign key from this table.

Use xls27.usp_companies to check available companies and add new ones.

dbo27.dimensions

This system table contains dimensions.

Trigger	Type	Comment
tr_dimensions_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimensions_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Dimension id (do not change it)
	code	nvarchar(50)		Dimension code
	name	nvarchar(100)		Dimension name Use the xls.translations table to change the name.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	accounts	Accounts
2	times	Times
3	categories	Categories
4	entities	Entities
5	regions	Regions
6	products	Products
7	sub_accounts	Sub.Accounts

The application has seven built-in dimensions.

Do not change this table. Use the xls.translations table to change names.

dbo27.entry_sides

This system table contains two records: debit and credit.

Trigger	Type	Comment
tr_entry_sides_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_entry_sides_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Id
	code	nvarchar(50)		Code
	name	nvarchar(100)		Name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	Dr	Debit
2	Cr	Credit

Do not change this table.

dbo27.journals

This user table contains data of bookkeeping journals.

Trigger	Type	Comment
tr_journals_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_journals_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo27.companies.id
	month	date	✓	Month
	date	date		Date
	amount	money		Amount
	amount$	money	✓	Amount in the functional currency
	description	nvarchar(255)	✓	Description
	debit_account_id	int	✓	dbo27.ledger_accounts.id
	debit_subsidiary_account_id	int	✓	dbo27.members.id
	credit_account_id	int	✓	dbo27.ledger_accounts.id
	credit_subsidiary_account_id	int	✓	dbo27.members.id
	entity_id	int	✓	dbo27.members.id
	account_id	int	✓	dbo27.members.id
	region_id	int	✓	dbo27.members.id
	product_id	int	✓	dbo27.members.id
	auto_type_id	int	✓	0 - manual operation 10 - automatic calculation of gains and losses from currency translation 20 - automatic calculation of gains and losses from currency translation at the end of month 30 - automatic closing accounts
	ref_record_id	int	✓	Reference id used to synchronize records across multiple databases
	is_draft	bit	✓	0 - confirmed 1 - draft
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_journal to select and edit data.

dbo27.ledger_account_special_types

This system table contains special system types of ledger accounts.

Trigger	Type	Comment
tr_ledger_account_special_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_ledger_account_special_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Id
	code	nvarchar(50)		Code
	name	nvarchar(100)		Name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	cash	Cash
2	net_income	Net Income (BS)
3	vat	VAT Payable
4	input_vat	VAT Receivable
5	currency_exchange_gains	Currency Exchange Gains
6	currency_exchange_losses	Currency Exchange Losses
7	currency_translation_gains	Currency Translation Gains
8	currency_translation_losses	Currency Translation Losses
9	calculation	Calculation

Do not change this table.

dbo27.ledger_account_types

This system table contains ledger account types.

Trigger	Type	Comment
tr_ledger_account_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_ledger_account_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Account type id
	code	nvarchar(50)		Account type code
	name	nvarchar(100)		Account type name
	bs_section	tinyint	✓	Balance sheet section: 1 - Current assets 2 - Non-current assets 3 - Current liabilities 4 - Non-current liabilities 5 - Equity
	bs_sign	int	✓	Balance sheet sign: 1 - Assets -1 - Equity or liabilities 0 - Any
	pl_section	tinyint	✓	Income statement section: 0 - Not included 1 - Revenue 2 - Cost of Revenue 3 - R&D Expenses 4 - Selling Expenses 5 - General Expenses 6 - Administrative Expenses 7 - Interest Expenses 8 - Other Income 9 - Other Expenses 10 - Income Tax Expense
	is_pl	bit	✓	1 - the account is shown in the income statement 0 - the account is not used in the income statement
	is_da	bit	✓	1 - the account is of depreciation or amortization type 0 - the account is not of depreciation or amortization type
	cf_section	tinyint	✓	Cash flow section: 0 - The account is cash or cash equivalent 1 - Cash flows from operating activities 2 - Cash flows from investing activities 3 - Cash flows from financing activities
	specific_type_id	tinyint	✓	dbo27.ledger_account_special_types.id The type of special accounts: 1 - the cash account 2 - the net income account 3 - the VAT payable account 4 - the input VAT account
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains built-in specific ledger account types.

Do not change this table. Use the xls.translations table to change names.

Use the most specific type in related tables.

dbo27.ledger_accounts

This user table contains ledger accounts.

Trigger	Type	Comment
tr_ledger_accounts_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_ledger_accounts_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_ledger_accounts_delete	DELETE	This trigger sets NULL in related columns and removes the element translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo27.companies.id
	code	nvarchar(50)		Account code
	name	nvarchar(100)		Account name
	parent_id	int	✓	dbo27.ledger_accounts.id of the parent account
	account_type_id	tinyint	✓	dbo27.ledger_account_types.id Use the most specific type. For example, use the Cash instead of the Current Assets for the cash account.
	subsidiary_group_id	int	✓	dbo27.subsidiary_groups.id
	closing_child_account_id	int	✓	dbo27.ledger_accounts.id used to close balances of the entire account at once
	closing_debit_account_id	int	✓	dbo27.ledger_accounts.id used to close debit balances
	closing_credit_account_id	int	✓	dbo27.ledger_accounts.id used to close credit balances
	ref_number	int	✓	Reference id used to synchronize records across multiple databases
	is_active	bit		0 - hide the account 1 - show the account
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The ledger accounts are specific for every company.

dbo27.member_groups

This user table contains relations between subsidiary accounts and subsidiary groups.

Trigger	Type	Comment
tr_member_groups_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_groups_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	member_id	int		dbo27.members.id
✓	group_id	int		dbo27.subsidiary_groups.id
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_subsidiary_accounts to select and edit actual groups of subsidiary accounts.

dbo27.member_permissions

This user table contains member permissions by users.

Trigger	Type	Comment
tr_member_permissions_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_permissions_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	member_id	int		dbo27.members.id
✓	username	nvarchar(128)		A username acquired using the USER_NAME() function
	select_permission	bit		SELECT permission values: 1 - yes, 0 - no
	update_permission	bit		UPDATE permission values: 1 - yes, 0 - no
	is_inherited	bit		When you set the permission for the parent member, the application applies this permission to the children. This field contains 1 for such inherited permissions. You may set the direct permission for any child to replace the inherited permissions.
	permission	char(1)	✓	This field contains user input permission values (R - read, W - Read/Write, D - Deny) and inherited member permissions (r - read, w - read/write, d - deny).
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application applies these member permissions for members of the budgeting_app_users role only.

Use the xls27.usp_member_permissions procedure to select and edit permissions.

dbo27.member_relations

This user table contains select and update permissions for cross-dimension members.

Trigger	Type	Comment
tr_member_relations_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_relations_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	member_id	int		dbo27.members.id
✓	partner_id	int		dbo27.members.id
	select_permission	bit	✓	SELECT permission values: 1 - yes, 0 - no
	update_permission	bit	✓	UPDATE permission values: 1 - yes, 0 - no
	permission	char(1)	✓	Calculated permission: R - read-only, W - read/write, D - deny, ? - not set
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This 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_budget_closed_periods procedure to manage such permissions.

dbo27.member_types

This system table contains member types.

Trigger	Type	Comment
tr_member_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		System type id
	code	nvarchar(50)		Member type code
	name	nvarchar(100)		Type description
	cf_sign	int	✓	Cash flow sign: 1 - Inflow 0 - No changes -1 - Outflow
	pl_sign	int	✓	Profit and losses sign: 1 - Profit 0 - No changes -1 - Losses
	asset_sign	int	✓	The sign of balance sheet assets: 1 - Increase assets 0 - No changes -1 - Decrease assets
	vat_sign	int	✓	VAT sign: 1 - Increase VAT payable 0 - no VAT -1 - Increase input VAT or decrease VAT payable
	payment_debit_type_id	tinyint	✓	Type of a debit account of the payment: 0 - account must be empty 1 - cash account 2 - accounts payable
	payment_credit_type_id	tinyint	✓	Type of a credit account of the payment: 0 - account must be empty 1 - cash account 2 - accounts receivable
	charge_debit_type_id	tinyint	✓	Type of a debit account of the charge: 0 - account must be empty 2 - accounts payable 3 - PL or balance sheet account
	charge_credit_type_id	tinyint	✓	Type of a credit account of the charge: 0 - account must be empty 2 - accounts receivable 3 - PL or balance sheet account
	vat_debit_type_id	tinyint	✓	Type of a debit account of the VAT: 0 - account must be empty 2 - accounts receivable 3 - PL or balance sheet account 4 - VAT account
	vat_credit_type_id	tinyint	✓	Type of a credit account of the VAT: 0 - account must be empty 2 - accounts payable 3 - PL or balance sheet account 4 - VAT account
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name	cf_sign	pl_sign	asset_sign	vat_sign	payment_debit_type_id	payment_credit_type_id	charge_debit_type_id	charge_credit_type_id	vat_debit_type_id	vat_credit_type_id
0	none	None	0	0	0	0	0	0	0	0	0	0
1	calculated	Calculated	0	0	0	0	0	0	0	0	0	0
2	income	Income	1	1	0	1	1	2	2	3	3	4
3	income_no_vat	Income, no VAT	1	1	0	0	1	2	2	3	0	0
4	expense	Expense	-1	-1	0	-1	2	1	3	2	4	2
5	expense_no_vat	Expense, no VAT	-1	-1	0	0	2	1	3	2	0	0
6	purchase	Purchase	-1	0	1	-1	2	1	3	2	4	2
7	purchase_no_vat	Purchase, no VAT	-1	0	1	0	2	1	3	2	0	0
8	cost	Cost	0	-1	-1	0	0	0	3	3	0	0
9	depreciation	Depreciation	0	-1	-1	0	0	0	3	3	0	0
10	amortization	Amortization	0	-1	-1	0	0	0	3	3	0	0
11	withholding	Withholding	-1	0	0	0	2	1	2	2	0	0
12	inflow	Inflow	1	0	0	0	1	2	0	0	0	0
13	outflow	Outflow	-1	0	0	0	2	1	0	0	0	0
14	cash_transfer	Cash Transfer	0	0	0	0	1	1	0	0	0	0
15	setoff	Setoff	0	0	0	0	0	0	2	2	0	0

This table contains member types used to calculate reports.

Use the xls.translations table to change type descriptions.

dbo27.members

This user table contains dimension members.

Trigger	Type	Comment
tr_members_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_members_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_members_delete	DELETE	This trigger sets NULL in related columns and removes the element translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int	✓	dbo27.companies.id
	dimension_id	tinyint		dbo27.dimensions.id
	sort_order	int	✓	Sort order except for reporting accounts
	code	nvarchar(50)		Member code
	name	nvarchar(100)		Member name
	parent_id	int	✓	Parent account id
	start_date	date	✓
	unit_id	int	✓	dbo27.units.id Use this field to set a fixed unit id. Otherwise, users may choose the unit in the budget request.
	vat_rate_id	int	✓	dbo27.vat_rates.id Use this field to set a fixed VAT rate id. Otherwise, users may choose the rate in the budget request.
	account_type_id	int	✓	dbo27.member_types.id
	payment_debit_id	int	✓	dbo27.ledger_accounts.id dbo27.ledger_accounts.id of a debit account of the payment
	payment_credit_id	int	✓	dbo27.ledger_accounts.id dbo27.ledger_accounts.id of a credit account of the payment
	charge_debit_id	int	✓	dbo27.ledger_accounts.id dbo27.ledger_accounts.id of a debit account of the charge
	charge_credit_id	int	✓	dbo27.ledger_accounts.id dbo27.ledger_accounts.id of a credit account of the charge
	vat_debit_id	int	✓	dbo27.ledger_accounts.id dbo27.ledger_accounts.id of a debit account of the VAT
	vat_credit_id	int	✓	dbo27.ledger_accounts.id dbo27.ledger_accounts.id of a credit account of the VAT
	is_charge_net	int		1 - exclude VAT in the charge entry 0 - use the full charge amount in the charge entry
	is_translatable	bit	✓	The element supports translation
	is_active	bit		0 - hide the member 1 - show the member
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_members to select and edit members.

Note that members of different dimensions have different specific columns.
Client applications like the SaveToDB add-n use the Р РЋРІР‚В¦ls27.xl_aliases_members procedure to read actual column sets.

dbo27.report_section_accounts

This system table contains report sections of report account types.

Trigger	Type	Comment
tr_report_section_accounts_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_accounts_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	account_type_id	tinyint		dbo27.ledger_account_types.id
	section_type_id	tinyint		dbo27.report_section_types.id
	entry_side_id	tinyint		dbo27.entry_sides.id
	value_type_id	tinyint		dbo27.report_section_value_types.id
	value_sign	int	✓	1 or -1
	pair_account_type_id	tinyint	✓	dbo27.ledger_account_types.id
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains settings of report sections used to calculate reports.

Do not change this table.

dbo27.report_section_total_types

This system table contains types of the total report column.

Trigger	Type	Comment
tr_report_section_total_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_total_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Id
	code	nvarchar(50)		Code
	name	nvarchar(100)		Name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	total	Total
2	opening_balance	Opening Balance
3	closing_balance	Closing Balance

Do not change this table.

dbo27.report_section_types

This system table contains built-in report section types.

Trigger	Type	Comment
tr_report_section_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Report section type id (do not change it)
	report_type_id	tinyint	✓	dbo27.report_types.id
	name	nvarchar(100)		The system name of the report section type Use the xls.translations table to change names.
	total_type_id	tinyint	✓	dbo27.report_section_total_types.id 1 - total 2 - first month 3 - last month
	hide_accounts	bit	✓	1 - do not show underlying records
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains report section types used to calculate reports.

Do not change this table. Use the xls.translations table to change names.

dbo27.report_section_value_types

This system table contains types of calculating report values.

Trigger	Type	Comment
tr_report_section_value_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_value_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Id
	code	nvarchar(50)		Code
	name	nvarchar(100)		Name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	flow	Flow
2	balance	Balance
3	debit_balance	Debit Balance
4	credit_balance	Credit Balance

Do not change this table.

dbo27.report_sections

This user table contains report sections.

Trigger	Type	Comment
tr_report_sections_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_sections_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_report_sections_delete	DELETE	This trigger sets NULL in related columns and removes the element translations.

	Column	DataType	Null	Comment
✓	id	int		Identity Manual Id
	company_id	int		dbo27.companies.id
	report_type_id	tinyint		dbo27.report_types.id
	section	int		Report section order Use odd values. The application uses even numbers for rows before and after.
	row_format	int	✓	Custom row format Initially, the application has the following defined values: 1 - Top-level row 2 - Bold row 8 - Row before the report header 9 - Report header
	ref_number	int	✓	Custom reference number for rows in the reports
	code	nvarchar(50)	✓	Code Usually, this field is empty. However, you may use for acronyms like COGS, SGA, or CFO.
	description	nvarchar(255)	✓	Report row You may change the name here or in the xls.translations table. The last way is preferable as it allows reports in different languages.
	parent_id	int	✓	Parent row
	parent2_id	int	✓	Parent row
	section_type_id	tinyint	✓	dbo27.report_section_types.id System type of the row
	inverse_sign	bit	✓	1 - inverse figures in the report line (usually to show positive figures for expenses) 0 - use the default sign of figures
	line_factor	int		1 - add figures to the parent line -1 - subtract figures from the parent line
	hide_items	bit	✓	1 - hide items (for example data by ledger accounts) 0 - show items
	show_line	bit	✓	1 - show the row 0 - hide the row (use it for item placeholders marked with *)
	show_line_before	bit	✓	1 - add a row before 0 - do not add a row before
	show_line_after	bit	✓	1 - add a row after 0 - do not add a row after
	page_break_before	bit	✓	1 - add the page break before the line 0 - do not add the page break
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains sections used in the xls27.usp_budget_reports procedure.

dbo27.report_types

This system table contains a list of built-in budget reports.

Trigger	Type	Comment
tr_report_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		System id (do not change it)
	code	nvarchar(50)		The report code usually used in the ribbon parameters
	name	nvarchar(100)		Report name You may change the name in the xls.translations table.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	PL	Income Statement
2	CF	Cash Flow
3	BS	Balance Sheet
4	DCF	Direct Cash Flow
5	VAT	VAT

Do not change this table. Use the xls.translations table to change names.

dbo27.saved_report_types

This user table contains custom types of saved reports.

Trigger	Type	Comment
tr_saved_report_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_saved_report_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_saved_report_types_delete	DELETE	This trigger removes the element translations from dbo27.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo27.companies.id
	code	nvarchar(50)		Saved report type code
	name	nvarchar(100)	✓	Saved report type name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Create various types to differentiate saved reports (by source, stage, etc.).

dbo27.saved_reports

This user table contains data of saved budget reports.

Trigger	Type	Comment
tr_saved_reports_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_saved_reports_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	type_id	int		dbo27.saved_report_types.id
✓	category_id	int		dbo27.members.id
✓	time_id	int		dbo27.members.id
✓	row_index	int		The row index
	id1	nvarchar(50)	✓	Custom id1
	id2	int	✓	Custom id2
	ref_number	int	✓	Reference number
	account	nvarchar(50)	✓	Ledger account code
	report	int	✓	Custom form number
	page_break	int	✓	1 - set page break before the line
	row_level	int	✓	Custom row level
	row_format	int	✓	Custom row format
	hide	int	✓	0 - hide the row 1 - show the row
	zero	int	✓	1 - the row has no actual data 0 - the row has actual data
	code1	nvarchar(50)	✓	Custom code1
	code2	nvarchar(50)	✓	Custom code2
	code	nvarchar(50)	✓	Custom code
	description	nvarchar(255)	✓	Custom name
	data00	float	✓	Value of column data00
	data01	float	✓	Value of column data01
	data02	float	✓	Value of column data02
	data03	float	✓	Value of column data03
	data04	float	✓	Value of column data04
	data05	float	✓	Value of column data05
	data06	float	✓	Value of column data06
	data07	float	✓	Value of column data07
	data08	float	✓	Value of column data08
	data09	float	✓	Value of column data09
	data10	float	✓	Value of column data10
	data11	float	✓	Value of column data11
	data12	float	✓	Value of column data12
	transaction_start_time	datetime	✓	The start time of the transaction used to update budget report rows.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains reports saved from Microsoft Excel using the SaveToDB add-in.

Use xls27.usp_budget_report to select saved reports and its related edit procedures to save reports.

dbo27.subsidiary_groups

This user table contains subsidiary groups.

Trigger	Type	Comment
tr_subsidiary_groups_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_subsidiary_groups_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_subsidiary_groups_delete	DELETE	This trigger removes the element translations from dbo27.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo27.companies.id
	code	nvarchar(50)		Group code
	name	nvarchar(100)		Group name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Create subsidiary groups like customers or vendors to filter subsidiary accounts in application forms.

Use xls27.usp_subsidiary_groups to select and edit groups.

Use xls27.usp_subsidiary_accounts to select and edit actual groups of subsidiary accounts.

dbo27.translated_tables

This system table contains tables that support the translation of elements.

Trigger	Type	Comment
tr_translated_tables_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translated_tables_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	tinyint		Id
	code	nvarchar(50)		Code
	name	nvarchar(100)		Name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	members	members
2	companies	companies
3	units	units
4	vat_rates	vat_rates
9	report_sections	report_sections
10	subsidiary_groups	subsidiary_groups
11	ledger_accounts	ledger_accounts
12	saved_report_types	saved_report_types

Do not change this table.

dbo27.translations

This user table contains company-related translations.

Trigger	Type	Comment
tr_translations_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translations_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	company_id	int		dbo27.companies.id
✓	table_id	tinyint		dbo27.translated_tables.id
✓	member_id	int		Id of the translated element in its table
✓	language	varchar(10)		Language
	name	nvarchar(400)	✓	Translated element name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_translations to select and edit data.

dbo27.units

This user table contains units.

Trigger	Type	Comment
tr_units_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_units_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_units_delete	DELETE	This trigger removes the element translations from dbo27.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity Unit id
	company_id	int		dbo27.companies.id
	code	nvarchar(50)		Unit code We recommend using currency codes provided in standard ISO 4217.
	name	nvarchar(100)		Unit name (used in the budget requests)
	sort_order	tinyint		Sort order
	factor	float		Reserved field
	is_currency	bit		1 - currency 0 - non-currency The application builds reports using the currency items only. However, you may use non-currency values in custom reports.
	is_functional_currency	bit		1 - use this item as the functional currency (must be a single item) 0 - all others
	is_base_currency	bit		1 - for the first currency in the currency pair like EUR/USD 0 - for the second currency in the currency pair like USD/EUR Set this flag before setting currency rates using xls27.usp_budget_currency_rates.
	is_translatable	bit	✓	The element supports translation
	is_active	bit		0 - hide the unit 1 - show the unit
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_units to select and edit data.

dbo27.users

This user table contains application users.

Trigger	Type	Comment
tr_users_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_users_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo27.companies.id
	username	nvarchar(128)		A database name of the user acquired using the USER_NAME() function. The application uses this username to check user's permissions.
	name	nvarchar(100)		User name
	is_admin	bit		1 - the user has administrator privileges 0 - the user has no administrator privileges
	is_default	bit		1 - use this company by default when @company_id is NULL 0 - do not use this company by default
	is_active	bit		1 - the user can work with the company data 0 - the user cannot work with the company data
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_users to select and edit data.

dbo27.vat_rates

This user table contains VAT rates.

Trigger	Type	Comment
tr_vat_rates_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_vat_rates_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_vat_rates_delete	DELETE	This trigger removes the element translations from dbo27.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity VAT rate id
	company_id	int		dbo27.companies.id
	code	nvarchar(50)		VAT rate code
	name	nvarchar(100)		VAT rate name (used in the budget requests)
	sort_order	tinyint		Sort order
	factor	float	✓	VAT rate factor The application uses it to calculate VAT like VAT = Charges * Factor. So, use the value 0.2/1.2 for VAT 20%.
	is_translatable	bit	✓	The element supports translation
	is_active	bit		0 - hide the VAT rate 1 - show the VAT rate
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls27.usp_vat_rates to select and edit data.