User Tools

Site Tools


This section will cover other (non-Chart of Accounts) aspects to the setup of the SQL-Ledger accounting package. These are generally accessed in the System submenu.


This section will cover other (non-Chart of Accounts) aspects to the setup of the SQL-Ledger accounting package. These are generally accessed in the System submenu.

Taxes, Defaults, and Preferences

Adding A Sales Tax Account

Sales Tax is collected on behalf of a state of national government by the individual store. Thus a sales tax account is a liability- it represents money owed by the business to the government. To add a sales tax account, one would create an account in the COA as a liability account, check all of the “tax” checkboxes, and answer the following question as “yes:” “Include this account on the customer/vendor forms to flag customer/vendor as taxable?” Once this account is created, one can set the tax amount.

Setting a Sales Tax Amount

Go to System→Defaults and the tax account will be listed near the bottom of the page. The rate can be set there.

Default Account Setup

These accounts are the default accounts for part creation and foreign exchange tracking.

Currency Setup

The US accounts list this as USD:CAD:EUR. One can add other currencies in here, such as IDR (Indonesian Rupiah), etc. Currencies are separated by colons.

Sequence Settings

These sequences are used to generate user identifiers for quotations, invoices, and the like. If an identifier is not added, the next number will be used. A common application is to set invoices, etc. to start at 1000 in order to hide the number of issued invoices from a customer.

Audit Control

Auditibility is a core concern of the architects of any accounting system. Such ensures that any modification to the accounting information leaves a trail which can be followed to determine the nature of the change. Audits can help ensure that the data in the accounting system is meaningful and accurate, and that no foul play (such as embezzlement) is occurring.

Explaining transaction reversal

In paper accounting systems, it was necessary to have a means to authoritatively track corrections of mistakes. The means by which this was done was known as “transaction reversal.” When a mistake would be made, one would then reverse the transaction and then enter it in correctly. For example, let us say that an office was renting space for $300 per month. Let us say that they inadvertently entered it in as a $200 expense. The original transaction would be:

Account Debit Credit
5760 Rent $200
2100 Accounts Payable $200

The reversal would be:

Account Debit Credit
5760 Rent $200
2100 Accounts Payable $200

SQL-Ledger has a capability to require such reversals if the business deems this to be necessary. When this option is enabled, existing transactions cannot be modified and one will need to post reversing transactions to void existing transactions before posting corrected ones. Most accountants prefer this means to other audit trails because it is well proven and understood by them.

Close books option

The option to close books requires transaction reversal for any transaction up to a certain date.

Audit Trails

This option stores additional information in the database to help auditors trace individual transactions. The information stored, however, is limited and it is intended to be supplemental to other auditing facilities. The information added includes which table stored the record, which employee entered the information, which form was used, and what the action was. No direct financial information is included.


Departments are logical divisions of a business. They allow for budgets to be prepared for the individual department as well as the business as a whole. This allows larger businesses to use SQL-Ledger to meet their needs.

Cost v Profit Centers.

In general business units are divided into cost and profit centers. Cost centers are generally regarded as business units where the business expects to lose money and profit centers are where they expect to gain money. For example, the legal department in most companies is a cost center. One of the serious misunderstandings people run up against is that SQL-Ledger tends to more narrowly define cost and profit centers than most businesses do. In SQL-Ledger a cost center is any department of the business that does not issue AR transactions. Although many businesses may have cost centers (like technical support) where customer fees may subsidize the cost of providing the service, in SQL-Ledger, these are profit centers. SQL-Ledger will not allow cost centers to be associated with AR transactions. So if you want this functionality, you must create the department as a profit center.


SQL-Ledger has the ability to track inventory by warehouse. Inventory items can be moved between warehouses, and shipped from any warehouse where the item is in stock. We will explore this concept more later.


Languages allow for goods and services to be translated so that one can maintain offices in different countries and allow for different goods and service descriptions to be translated to different languages for localization purposes.

Types of Businesses

One can create types of businesses and then give them discounts across the board. For example, one might give a firm that uses one's services as a subcontractor a 10% discount or more.



GIFI is a requirement for Canadian customers. This feature allows one to link accounts with Canadian tax codes to simplify the reporting process. It also has another use in that non-Canadians can use this functionality to create customized reports by categorizing accounts using this field. This allows for a sort of shallow “account hierarchy” like some users are used to with other products.


Standard Industrial Classification is a way of tracking the type of business that a vendor or customer is in. For example, an accountant would have an SIC of 8721 while a graphic design firm would have an SIC of 7336. The classification is hierarchical so one could use this field for custom reporting and marketing purposes.

Overview of Template Editing

The templates for invoices, orders, and the like can be edited from within SQL-Ledger. The submenus within the System submenu such as HTML Templates, Text Templates and LaTeX templates provide access to this functionality.


Although the Year-end functionality in SQL-Ledger is very useful, it does not entirely make the process simple and painless. One must still manually enter adjustments prior to closing the books. The extent to which these adjustments are necessary for any given business is a matter best discussed with an accountant. The standard way books are normally closed at the end of the year is by moving all adjusted (adjustments would be entered via the General Ledger; the exact process is beyond the scope of this class, however) income and expenses to an equity account usually called “Retained Earnings.” Assets and liabilities are not moved. Equity drawing/dividend accounts are also moved, but the investment accounts are not. The reasoning behind this process is that one wants a permanent record of the amount invested in a business, but any dividends ought not to count against their recipients when new investors are brought on board. SQL-Ledger automatically moves all income and expense into the specified year-end/retained earnings account. It does not move the drawing account, and this must be done manually, nor does it automate the process of making adjustments. Contrary to its name, this function can close the books at any time, though this would likely be of limited use.

Options in the sql-ledger.conf

For those who are unfamiliar with Perl as a programming language, the sql-ledger.conf configures the software by assigning site-wide variables. Most of these should be left alone unless one knows what one is doing. However, on some systems some options might need to be changed, so all options are presented here for reference:

  • $userspath is the directory where SQL-Ledger will store the user accounts. The web server process must be able to read from and write to this directory.
  • $templates is the directory where the templates are stored.
  • $memberfile is the master list of user configuration information
  • $sendmail is the command to use to send a message. It must read the email from standard input.
  • $language allows one to set the language for the login screen and admin page.
  • $latex tells SQL-Ledger whether LaTeX is installed. LaTeX is required for generating Postscript and PDF invoices and the like.
  • Various environmental variables ($ENV…) can be set here too. One can add paths for searching for LaTeX, etc.
  • %printer can be used to set a hash table of printers for the software. The primary example is %printer = ('Default' ⇒'lpr', 'Color' ⇒'lpr -PEpson');
  • However, this can use any program that can accept print documents (in Postscript) from standard input, so there are many more possibilities.
  • I have omitted the variables used to configure Oracle as I do not believe it is still supported (it could be with a small amount of work though).

Next: Goods and Services

(First version from: An Introduction to SQL-Ledger by Chris Travers, 2006)

user_manual/administration.txt · Last modified: 2014/12/30 15:01 by