Contents Diagrams Roles Schemas Tables Views Procedures Functions

Gartle Budgeting
Part II. Tables

Version 2.0, July 20, 2020

Tables

TableDescription
dbo27.actual_balancesThis user table contains actual opening balances.
dbo27.actual_closed_periodsThis user table contains actual closed periods.
dbo27.actual_currency_ratesThis user table contains actual currency rates.
dbo27.budget_balancesThis user table contains budget opening balances.
dbo27.budget_currency_ratesThis user table contains budget currency rates.
dbo27.budget_requestsThis user table contains data of budget requests.
dbo27.companiesThis user table contains companies that hold budget and accounting records.
dbo27.dimensionsThis system table contains dimensions.
dbo27.entry_sidesThis system table contains two records: debit and credit.
dbo27.journalsThis user table contains data of bookkeeping journals.
dbo27.ledger_account_special_typesThis system table contains special system types of ledger accounts.
dbo27.ledger_account_typesThis system table contains ledger account types.
dbo27.ledger_accountsThis user table contains ledger accounts.
dbo27.member_groupsThis user table contains relations between subsidiary accounts and subsidiary groups.
dbo27.member_permissionsThis user table contains member permissions by users.
dbo27.member_relationsThis user table contains select and update permissions for cross-dimension members.
dbo27.member_typesThis system table contains member types.
dbo27.membersThis user table contains dimension members.
dbo27.report_section_accountsThis system table contains report sections of report account types.
dbo27.report_section_total_typesThis system table contains types of the total report column.
dbo27.report_section_typesThis system table contains built-in report section types.
dbo27.report_section_value_typesThis system table contains types of calculating report values.
dbo27.report_sectionsThis user table contains report sections.
dbo27.report_typesThis system table contains a list of built-in budget reports.
dbo27.saved_report_typesThis user table contains custom types of saved reports.
dbo27.saved_reportsThis user table contains data of saved budget reports.
dbo27.subsidiary_groupsThis user table contains subsidiary groups.
dbo27.translated_tablesThis system table contains tables that support the translation of elements.
dbo27.translationsThis user table contains company-related translations.
dbo27.unitsThis user table contains units.
dbo27.usersThis user table contains application users.
dbo27.vat_ratesThis user table contains VAT rates.

dbo27.actual_balances

This user table contains actual opening balances.

TriggerTypeComment
tr_actual_balances_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_actual_balances_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
account_idintdbo27.ledger_accounts.id
subsidiary_account_idintdbo27.members.id
balancemoneyOpening balance in the account currency
balance$moneyOpening balance in the functional currency
debitmoneyDebit balance in the account currency (calculated)
creditmoneyCredit balance in the account currency (calculated)
debit$moneyDebit balance in the functional currency (calculated)
credit$moneyCredit balance in the functional currency (calculated)
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_actual_closed_periods_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_actual_closed_periods_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
datedateThe first date of the closed month
company_idintdbo27.companies.id
is_closedbit1 - the month is closed for changes
0 - the month is opened for changes
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls27.usp_actual_closed_periods to manage closed periods.

dbo27.actual_currency_rates

This user table contains actual currency rates.

TriggerTypeComment
tr_actual_currency_rates_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_actual_currency_rates_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
currency_idintdbo27.units.id
datedateDate
ratesmallmoneyCurrency rate
is_checkedbit1 - the rate is checked
0 - the rate is not checked
is_calc_dateint1 - calculate gains and losses on currency differences in this date automatically
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_budget_balances_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_budget_balances_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
category_idintdbo27.members.id
time_idintdbo27.members.id
account_idintdbo27.ledger_accounts.id
debitmoneyDebit balance at the beginning
creditmoneyCredit balance at the beginning
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_budget_currency_rates_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_budget_currency_rates_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
category_idintdbo27.members.id
time_idintdbo27.members.id
unit_idintdbo27.units.id
balance00floatCurrency rate for initial balances
period01floatCurrency rate for period 01
balance01floatCurrency rate for balances 01
period02floatCurrency rate for period 02
balance02floatCurrency rate for balances 02
period03floatCurrency rate for period 03
balance03floatCurrency rate for balances 03
period04floatCurrency rate for period 04
balance04floatCurrency rate for balances 04
period05floatCurrency rate for period 05
balance05floatCurrency rate for balances 05
period06floatCurrency rate for period 06
balance06floatCurrency rate for balances 06
period07floatCurrency rate for period 07
balance07floatCurrency rate for balances 07
period08floatCurrency rate for period 08
balance08floatCurrency rate for balances 08
period09floatCurrency rate for period 09
balance09floatCurrency rate for balances 09
period10floatCurrency rate for period 10
balance10floatCurrency rate for balances 10
period11floatCurrency rate for period 11
balance11floatCurrency rate for balances 11
period12floatCurrency rate for period 12
balance12floatCurrency rate for balances 12
row_formulasnvarchar(max)Excel formulas used to calculate values.

This feature is enabled with the SaveToDB add-in only.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_budget_requests_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_budget_requests_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
entity_idintdbo27.members.id
category_idintdbo27.members.id
time_idintdbo27.members.id
row_indexintThe row index
row_formatintCustom row format
descriptionnvarchar(255)Custom comment
account_idintdbo27.members.id
subsidiary_account_idintdbo27.members.id
region_idintdbo27.members.id
product_idintdbo27.members.id
vat_rate_idintdbo27.vat_rates.id
unit_idintdbo27.units.id
total_paymentsmoneyTotal payments
total_chargesmoneyTotal charges
balance00moneyBalance at the beginning
payments01moneyPayments of period 01
charges01moneyCharges of period 01
balance01moneyBalance at the end of period 01
payments02moneyPayments of period 02
charges02moneyCharges of period 02
balance02moneyBalance at the end of period 02
payments03moneyPayments of period 03
charges03moneyCharges of period 03
balance03moneyBalance at the end of period 03
payments04moneyPayments of period 04
charges04moneyCharges of period 04
balance04moneyBalance at the end of period 04
payments05moneyPayments of period 05
charges05moneyCharges of period 05
balance05moneyBalance at the end of period 05
payments06moneyPayments of period 06
charges06moneyCharges of period 06
balance06moneyBalance at the end of period 06
payments07moneyPayments of period 07
charges07moneyCharges of period 07
balance07moneyBalance at the end of period 07
payments08moneyPayments of period 08
charges08moneyCharges of period 08
balance08moneyBalance at the end of period 08
payments09moneyPayments of period 09
charges09moneyCharges of period 09
balance09moneyBalance at the end of period 09
payments10moneyPayments of period 10
charges10moneyCharges of period 10
balance10moneyBalance at the end of period 10
payments11moneyPayments of period 11
charges11moneyCharges of period 11
balance11moneyBalance at the end of period 11
payments12moneyPayments of period 12
charges12moneyCharges of period 12
balance12moneyBalance at the end of period 12
row_formulasnvarchar(max)Excel formulas used to calculate values.

This feature is enabled with the SaveToDB add-in only.
transaction_start_timedatetimeThe start time of the transaction used to update budget request rows.
is_emptybit0 - the row is empty
1 - the row has data
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_companies_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_companies_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
codenvarchar(50)Company code
namenvarchar(100)Company name
default_languagechar(2)The language of company elements like accounts, members, etc.
sort_orderintSort order
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_dimensions_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimensions_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintDimension id (do not change it)
codenvarchar(50)Dimension code
namenvarchar(100)Dimension name

Use the xls.translations table to change the name.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1accountsAccounts
2timesTimes
3categoriesCategories
4entitiesEntities
5regionsRegions
6productsProducts
7sub_accountsSub.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.

TriggerTypeComment
tr_entry_sides_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_entry_sides_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintId
codenvarchar(50)Code
namenvarchar(100)Name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1DrDebit
2CrCredit

Do not change this table.

dbo27.journals

This user table contains data of bookkeeping journals.

TriggerTypeComment
tr_journals_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_journals_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo27.companies.id
monthdateMonth
datedateDate
amountmoneyAmount
amount$moneyAmount in the functional currency
descriptionnvarchar(255)Description
debit_account_idintdbo27.ledger_accounts.id
debit_subsidiary_account_idintdbo27.members.id
credit_account_idintdbo27.ledger_accounts.id
credit_subsidiary_account_idintdbo27.members.id
entity_idintdbo27.members.id
account_idintdbo27.members.id
region_idintdbo27.members.id
product_idintdbo27.members.id
auto_type_idint0 - 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_idintReference id used to synchronize records across multiple databases
is_draftbit0 - confirmed
1 - draft
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_ledger_account_special_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_ledger_account_special_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintId
codenvarchar(50)Code
namenvarchar(100)Name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1cashCash
2net_incomeNet Income (BS)
3vatVAT Payable
4input_vatVAT Receivable
5currency_exchange_gainsCurrency Exchange Gains
6currency_exchange_lossesCurrency Exchange Losses
7currency_translation_gainsCurrency Translation Gains
8currency_translation_lossesCurrency Translation Losses
9calculationCalculation

Do not change this table.

dbo27.ledger_account_types

This system table contains ledger account types.

TriggerTypeComment
tr_ledger_account_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_ledger_account_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintAccount type id
codenvarchar(50)Account type code
namenvarchar(100)Account type name
bs_sectiontinyintBalance sheet section:
1 - Current assets
2 - Non-current assets
3 - Current liabilities
4 - Non-current liabilities
5 - Equity
bs_signintBalance sheet sign:
1 - Assets
-1 - Equity or liabilities
0 - Any
pl_sectiontinyintIncome 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_plbit1 - the account is shown in the income statement
0 - the account is not used in the income statement
is_dabit1 - the account is of depreciation or amortization type
0 - the account is not of depreciation or amortization type
cf_sectiontinyintCash 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_idtinyintdbo27.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_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_ledger_accounts_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_ledger_accounts_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_ledger_accounts_deleteDELETEThis trigger sets NULL in related columns and removes the element translations.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo27.companies.id
codenvarchar(50)Account code
namenvarchar(100)Account name
parent_idintdbo27.ledger_accounts.id of the parent account
account_type_idtinyintdbo27.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_idintdbo27.subsidiary_groups.id
closing_child_account_idintdbo27.ledger_accounts.id used to close balances of the entire account at once
closing_debit_account_idintdbo27.ledger_accounts.id used to close debit balances
closing_credit_account_idintdbo27.ledger_accounts.id used to close credit balances
ref_numberintReference id used to synchronize records across multiple databases
is_activebit0 - hide the account
1 - show the account
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_member_groups_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_groups_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
member_idintdbo27.members.id
group_idintdbo27.subsidiary_groups.id
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_member_permissions_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_permissions_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
member_idintdbo27.members.id
usernamenvarchar(128)A username acquired using the USER_NAME() function
select_permissionbitSELECT permission values: 1 - yes, 0 - no
update_permissionbitUPDATE permission values: 1 - yes, 0 - no
is_inheritedbitWhen 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.
permissionchar(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_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_member_relations_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_relations_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
member_idintdbo27.members.id
partner_idintdbo27.members.id
select_permissionbitSELECT permission values: 1 - yes, 0 - no
update_permissionbitUPDATE permission values: 1 - yes, 0 - no
permissionchar(1)Calculated permission: R - read-only, W - read/write, D - deny, ? - not set
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_member_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintSystem type id
codenvarchar(50)Member type code
namenvarchar(100)Type description
cf_signintCash flow sign:
1 - Inflow
0 - No changes
-1 - Outflow
pl_signintProfit and losses sign:
1 - Profit
0 - No changes
-1 - Losses
asset_signintThe sign of balance sheet assets:
1 - Increase assets
0 - No changes
-1 - Decrease assets
vat_signintVAT sign:
1 - Increase VAT payable
0 - no VAT
-1 - Increase input VAT or decrease VAT payable
payment_debit_type_idtinyintType of a debit account of the payment:
0 - account must be empty
1 - cash account
2 - accounts payable
payment_credit_type_idtinyintType of a credit account of the payment:
0 - account must be empty
1 - cash account
2 - accounts receivable
charge_debit_type_idtinyintType of a debit account of the charge:
0 - account must be empty
2 - accounts payable
3 - PL or balance sheet account
charge_credit_type_idtinyintType of a credit account of the charge:
0 - account must be empty
2 - accounts receivable
3 - PL or balance sheet account
vat_debit_type_idtinyintType 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_idtinyintType 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_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodenamecf_signpl_signasset_signvat_signpayment_debit_type_idpayment_credit_type_idcharge_debit_type_idcharge_credit_type_idvat_debit_type_idvat_credit_type_id
0noneNone0000000000
1calculatedCalculated0000000000
2incomeIncome1101122334
3income_no_vatIncome, no VAT1100122300
4expenseExpense-1-10-1213242
5expense_no_vatExpense, no VAT-1-100213200
6purchasePurchase-101-1213242
7purchase_no_vatPurchase, no VAT-1010213200
8costCost0-1-10003300
9depreciationDepreciation0-1-10003300
10amortizationAmortization0-1-10003300
11withholdingWithholding-1000212200
12inflowInflow1000120000
13outflowOutflow-1000210000
14cash_transferCash Transfer0000110000
15setoffSetoff0000002200

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.

TriggerTypeComment
tr_members_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_members_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_members_deleteDELETEThis trigger sets NULL in related columns and removes the element translations.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo27.companies.id
dimension_idtinyintdbo27.dimensions.id
sort_orderintSort order except for reporting accounts
codenvarchar(50)Member code
namenvarchar(100)Member name
parent_idintParent account id
unit_idintdbo27.units.id

Use this field to set a fixed unit id.
Otherwise, users may choose the unit in the budget request.
vat_rate_idintdbo27.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_idintdbo27.member_types.id
payment_debit_idintdbo27.ledger_accounts.id

dbo27.ledger_accounts.id of a debit account of the payment
payment_credit_idintdbo27.ledger_accounts.id

dbo27.ledger_accounts.id of a credit account of the payment
charge_debit_idintdbo27.ledger_accounts.id

dbo27.ledger_accounts.id of a debit account of the charge
charge_credit_idintdbo27.ledger_accounts.id

dbo27.ledger_accounts.id of a credit account of the charge
vat_debit_idintdbo27.ledger_accounts.id

dbo27.ledger_accounts.id of a debit account of the VAT
vat_credit_idintdbo27.ledger_accounts.id

dbo27.ledger_accounts.id of a credit account of the VAT
is_charge_netint1 - exclude VAT in the charge entry
0 - use the full charge amount in the charge entry
is_translatablebitThe element supports translation
is_activebit0 - hide the member
1 - show the member
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_report_section_accounts_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_accounts_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
account_type_idtinyintdbo27.ledger_account_types.id
section_type_idtinyintdbo27.report_section_types.id
entry_side_idtinyintdbo27.entry_sides.id
value_type_idtinyintdbo27.report_section_value_types.id
value_signint1 or -1
pair_account_type_idtinyintdbo27.ledger_account_types.id
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_report_section_total_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_total_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintId
codenvarchar(50)Code
namenvarchar(100)Name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1totalTotal
2opening_balanceOpening Balance
3closing_balanceClosing Balance

Do not change this table.

dbo27.report_section_types

This system table contains built-in report section types.

TriggerTypeComment
tr_report_section_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintReport section type id (do not change it)
report_type_idtinyintdbo27.report_types.id
namenvarchar(100)The system name of the report section type

Use the xls.translations table to change names.
total_type_idtinyintdbo27.report_section_total_types.id

1 - total
2 - first month
3 - last month
hide_accountsbit1 - do not show underlying records
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_report_section_value_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_section_value_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintId
codenvarchar(50)Code
namenvarchar(100)Name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1flowFlow
2balanceBalance
3debit_balanceDebit Balance
4credit_balanceCredit Balance

Do not change this table.

dbo27.report_sections

This user table contains report sections.

TriggerTypeComment
tr_report_sections_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_sections_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_report_sections_deleteDELETEThis trigger sets NULL in related columns and removes the element translations.
ColumnDataTypeNullComment
idintIdentity
Manual Id
company_idintdbo27.companies.id
report_type_idtinyintdbo27.report_types.id
sectionintReport section order

Use odd values. The application uses even numbers for rows before and after.
row_formatintCustom 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_numberintCustom reference number for rows in the reports
codenvarchar(50)Code

Usually, this field is empty. However, you may use for acronyms like COGS, SGA, or CFO.
descriptionnvarchar(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_idintParent row
parent2_idintParent row
section_type_idtinyintdbo27.report_section_types.id

System type of the row
inverse_signbit1 - inverse figures in the report line (usually to show positive figures for expenses)
0 - use the default sign of figures
line_factorint1 - add figures to the parent line
-1 - subtract figures from the parent line
hide_itemsbit1 - hide items (for example data by ledger accounts)
0 - show items
show_linebit1 - show the row
0 - hide the row (use it for item placeholders marked with *)
show_line_beforebit1 - add a row before
0 - do not add a row before
show_line_afterbit1 - add a row after
0 - do not add a row after
page_break_beforebit1 - add the page break before the line
0 - do not add the page break
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_report_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_report_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintSystem id (do not change it)
codenvarchar(50)The report code usually used in the ribbon parameters
namenvarchar(100)Report name

You may change the name in the xls.translations table.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1PLIncome Statement
2CFCash Flow
3BSBalance Sheet
4DCFDirect Cash Flow
5VATVAT

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.

TriggerTypeComment
tr_saved_report_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_saved_report_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_saved_report_types_deleteDELETEThis trigger removes the element translations from dbo27.translations.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo27.companies.id
codenvarchar(50)Saved report type code
namenvarchar(100)Saved report type name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_saved_reports_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_saved_reports_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
type_idintdbo27.saved_report_types.id
category_idintdbo27.members.id
time_idintdbo27.members.id
row_indexintThe row index
id1nvarchar(50)Custom id1
id2intCustom id2
ref_numberintReference number
accountnvarchar(50)Ledger account code
reportintCustom form number
page_breakint1 - set page break before the line
row_levelintCustom row level
row_formatintCustom row format
hideint0 - hide the row
1 - show the row
zeroint1 - the row has no actual data
0 - the row has actual data
code1nvarchar(50)Custom code1
code2nvarchar(50)Custom code2
codenvarchar(50)Custom code
descriptionnvarchar(255)Custom name
data00floatValue of column data00
data01floatValue of column data01
data02floatValue of column data02
data03floatValue of column data03
data04floatValue of column data04
data05floatValue of column data05
data06floatValue of column data06
data07floatValue of column data07
data08floatValue of column data08
data09floatValue of column data09
data10floatValue of column data10
data11floatValue of column data11
data12floatValue of column data12
transaction_start_timedatetimeThe start time of the transaction used to update budget report rows.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_subsidiary_groups_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_subsidiary_groups_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_subsidiary_groups_deleteDELETEThis trigger removes the element translations from dbo27.translations.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo27.companies.id
codenvarchar(50)Group code
namenvarchar(100)Group name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_translated_tables_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translated_tables_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idtinyintId
codenvarchar(50)Code
namenvarchar(100)Name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1membersmembers
2companiescompanies
3unitsunits
4vat_ratesvat_rates
9report_sectionsreport_sections
10subsidiary_groupssubsidiary_groups
11ledger_accountsledger_accounts
12saved_report_typessaved_report_types

Do not change this table.

dbo27.translations

This user table contains company-related translations.

TriggerTypeComment
tr_translations_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translations_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
company_idintdbo27.companies.id
table_idtinyintdbo27.translated_tables.id
member_idintId of the translated element in its table
languagechar(2)Language
namenvarchar(400)Translated element name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls27.usp_translations to select and edit data.

dbo27.units

This user table contains units.

TriggerTypeComment
tr_units_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_units_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_units_deleteDELETEThis trigger removes the element translations from dbo27.translations.
ColumnDataTypeNullComment
idintIdentity
Unit id
company_idintdbo27.companies.id
codenvarchar(50)Unit code

We recommend using currency codes provided in standard ISO 4217.
namenvarchar(100)Unit name (used in the budget requests)
sort_ordertinyintSort order
factorfloatReserved field
is_currencybit1 - 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_currencybit1 - use this item as the functional currency (must be a single item)
0 - all others
is_base_currencybit1 - 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_translatablebitThe element supports translation
is_activebit0 - hide the unit
1 - show the unit
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls27.usp_units to select and edit data.

dbo27.users

This user table contains application users.

TriggerTypeComment
tr_users_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_users_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo27.companies.id
usernamenvarchar(128)A database name of the user acquired using the USER_NAME() function.
The application uses this username to check user's permissions.
namenvarchar(100)User name
is_adminbit1 - the user has administrator privileges
0 - the user has no administrator privileges
is_defaultbit1 - use this company by default when @company_id is NULL
0 - do not use this company by default
is_activebit1 - the user can work with the company data
0 - the user cannot work with the company data
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls27.usp_users to select and edit data.

dbo27.vat_rates

This user table contains VAT rates.

TriggerTypeComment
tr_vat_rates_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_vat_rates_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE.
tr_vat_rates_deleteDELETEThis trigger removes the element translations from dbo27.translations.
ColumnDataTypeNullComment
idintIdentity
VAT rate id
company_idintdbo27.companies.id
codenvarchar(50)VAT rate code
namenvarchar(100)VAT rate name (used in the budget requests)
sort_ordertinyintSort order
factorfloatVAT 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_translatablebitThe element supports translation
is_activebit0 - hide the VAT rate
1 - show the VAT rate
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls27.usp_vat_rates to select and edit data.

Contents

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK