The book has been split up in several parts intended for starting entrepreneurs, (potential) users, application integration developers and administrators, it offers something for everybody who works with or considers working with LedgerSMB.
The first part (’Overview’) is intended for everybody who tries to get an impression of LedgerSMB, with chapters on technology, features, licensing and a bit of project history.
The second part (’Getting started’) leads new users and especially new entrepreneurs step by step through the application. This part also contains additional explanations on good business and accounting process.
The third part (’Configuration’) describes system wide configuration settings such as the LedgerSMB configuration file and those required for dependencies like PostgreSQL. Additionally it describes the company specific settings within the application, such as audit control settings and required special accounts. This part is required material for anybody who wants to set up a LedgerSMB instance.
The fourth part (’Administration’) discusses the different topics regarding application administration such as user management, product definition, taxes and etc. Anybody responsible for maintaining application instances in good health should read this part.
The fifth part discusses work flows; LedgerSMB best practices, so to say. This part is highly advised reading for everybody who is a LedgerSMB user and should be considered required reading for ’business architects’: those in the company who decide about process design and execution.
The sixth part discusses how to customize the application. This part is intended for developers of custom extensions (such as company specific integrations), add-ons and plug-ins.
The last part, the appendices, contain information on various subjects, such as a listing and description of the authorization roles in the application as well as information on migration to or upgrading of LedgerSMB versions.
The sources of this book are being developed in the ledgersmb-book Google Code project at https://code.google.com/p/ledgersmb-book/. Comments and enhancement ideas can be filed in the ticket tracker of the project. Additionally, discussion about the content of the book can be organized on the IRC freenode.net channel #ledgersmb or the ledger-smb-devel for which you can subscribe at https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel.
Håvard Sørli for his efforts to help out develop the book outline.
LedgerSMB is an open source, web based ERP system. An ERP is a system which supports business processes of all disciplines throughout the organization, automating as much of those processes as possible. To illustrate this, consider the process of selling goods to a customer in a trade company. Typically, a customer will request a quote, which Sales will provide. When satisfied he or she will convert the quote to an order. Sales will register the order, leading to order-pickers to collect one or more shipments. Upon completion, Finance sends out an invoice and records the customer’s payment. LedgerSMB supports this process by automating the conversion from quotation to order, order to shipment and shipment to invoice, as well as providing pack lists and other production related documents.
LedgerSMB includes a powerful framework which supports building your own extensions and integrations with other applications. Through this philosophy, it aspires to be the (open source) integrated administration system.
The software is being developed by the LedgerSMB project with its homepage at http://ledgersmb.org/. However, the actual project activity can be witnessed at the SourceForge project site hosted at http://sf.net/projects/ledger-smb/ and the mailing list archive hosted at http://archive.ledgersmb.org/. The project employs the Freenode.net IRC network’s #ledgersmb chat channel to help out users and to discuss development.
Its open source nature allows you to download it and use it with any infrastructure you like. So there’s no vendor lock-in: you can always take your data and set up your system with another hardware vendor or set up your own hardware.
Due to its heritage from SQL Ledger and the on-going process of rewriting the inherited code, the architecture differs between parts of the application: the old parts and the ones which have already been rewritten.
Overall, the application consists of five layers:
The web browser
The web server (as a network traffic handler)
The web server (as an application - CGI)
The database (as an application - PL/SQL)
The database (as storage)
Rewritten parts include the price matrix and the management of customers and vendors as well as batch payments.
The “database as an application” layer is part of the new design, but otherwise both designs share the same basic structure - as any web application.
As part of the new design, database integrity is being enforced much more strictly than before. Much of this enforcement is being done in the database at the “storage” level, by adding constraints to table definitions. Additionally, the new design moves to a model where a large part of the database API as well as business logic is implemented in PL/SQL. The ultimate goal there is to allow easier development of bindings in languages other than Perl. Part of this move is to reduce the CGI layer to become more of a “glue” layer between the web server and the application database layer.
As part of the on-going code restructuring, in 1.4 there will be a REST web service based API for the areas where functionality has been rewritten.
One of the roles that has remained so far for the CGI layer is to generate HTML screens for presentation in the web browser, handling all user interaction. Recently activities have started to change that for 1.4 and 1.5 to create a much heavier client in the web browser with back end interaction through web services.
In the old design everything except storage was handled in the “web server as an application (CGI)” layer.
As most ERP systems LedgerSMB’s functionalities are grouped into modules. Many modules are integrated parts of the base application. New features are implemented in separate modules at first to allow evaluation of the feature set. When a feature set has become sufficiently stable, the new module will be integrated in the base application. As of that time, the existing feature set of the module will be frozen, meaning that the utmost will be done to prevent changes to how the modules operate: to keep them stable.
These separate modules - which are called add-ons - have to be installed separately. After installation they become seamless parts of LedgerSMB with no visible difference from the base application. An additional benefit of having the separation between the base application and add-ons is that it allows for different release schedules and separate maturity levels.
LedgerSMB 1.3 features the following integrated modules:
Payment and Accounts payable
Invoicing and Accounts receivable
Fixed asset accounting
Time registration and invoicing
Point of Sale
Quotation and Order management
Inventory (warehousing) and shipping management
VAT reporting (cash based)
These add-ons can be installed:
Enhanced AR and AP support
VAT reporting (accrual based)
Enhanced trial-balance report
Enhanced recurring transactions
Payroll (to be created - under discussion at the time of writing)
With this list of modules and add-ons LedgerSMB has succesfully been implemented in a wide range of companies of varying types and sizes: shops, manufacturing companies and service oriented businesses up to as big as four thousand (4.000) Accounts payable transactions per week.
Packages to compare to:
The project started out as a fork of SQL-Ledger - the open source ERP system developed by Dieter Simader - somewhere between SQL-Ledger versions 2.6 and 2.8. A fork happens when a group of developers can’t - for whatever reason - continue to work as one group on a project. At that time, the project splits into two or more projects and the fork is in effect.
LedgerSMB split off from the SQL-Ledger project (i.e. forked) because there was disagreement between developers about how to go forward both with respect to handling of security vulnerability reports as well as the general state of the code base.
After the fork, between versions 1.0 and 1.2 a lot of energy was spent on making LedgerSMB more secure (i.e. less vulnerable). In technical terms, measures were taken to fend off (amongst other things):
Cross site scripting attacks
SQL injection attacks
Come version 1.3 the development directed toward improvement of the overall quality of the code base as the old SQL-Ledger code was in very poor state: looking very much like webscripts as they were written in 1998, the code had grown largely outdated in style and was no longer maintainable by 2007.
The 1.3 effort focussed on bringing relief on that front by introducing modern structure into the application. With the new application structure modern and important features were realized: separation of duties (for the accounting part of the application) and authorizations to allow distinguishing different roles in your company.
Unfortunately, by the beginning of 2011 the project looked mostly dead from an outside perspective: the team had not brought forward any releases since 2007, there were no signs of development activity and the mailing lists (a measure for community activity) were completely silent. SVN commits were continuing, but were being made by ever fewer committers and contributors.
Fortunately development activity was showing again in the first half year of 2011, leading to the release of version 1.3 by September. Between September and the year end in total 10 small bug fixes were released, showing active commitment of the developers to maintain the application.
New committers showed up, indicating revived community interest. Other signs of increased interest are the higher number of bug reports and the creation of the linux package for Debian 7, which has been included in Ubuntu 12.04 as of October 2012.
The INSTALL file which comes with every LedgerSMB software release should be considered the authorative source of system requirements. In summary, the following technical components are required:
The Apache web server (others may or may not work)
Perl 5.10 or higher, with additional modules
PostgreSQL 8.3 or higher, with contribs
LaTeXor XeLaTeX from the TeTeX or TexLive TeXdistributions
Other system requirements such as required RAM and number of CPUs and their speed largely depend on the expected system activity. However, any modern VPS should provide enough memory and storage to satisfy a reasonable number of users.
LedgerSMB is being made available under the terms of the GNU Public License version 2, or shorter GPLv2 11http://www.opensource.org/licenses/GPL-2.0.
The project attaches this meaning to the license: The copyright holders grant you the right to copy and redistribute the software. In case you make any modifications to the sofware you’re obligated to make public those changes. You are however, free to use the APIs from your own software without being required to publish your own software.
The project considers the following to be APIs:
URLs with their input and output
Webservices of any kind
Function and object calls
The effect of this interpretation is that changes directly to the code base as well as inheritance of classes defined in the software constitute ”making modifications”.
Jack finds several tools which suit his requirements to some extent or another. After evaluation of his options he decides to use LedgerSMB for the following reasons:
Centralized data storage
Development team with security focus
Access to the application requires only a web browser
Integrated sales, shipping, invoicing, purchasing and accounting
Open source solution, so no vendor lock in
The roadmap appeals to him, because it has web services payrolling on it
There are multiple vendors offering commercial support, including hosted options
The developers envision building a platform out of it: creating the building blocks required to build a company on
Own your own data
Freedom to change
Data storage provider
Application service provider
Internal control22See also http://en.wikipedia.org/wiki/Internal_control helps organizations to prevent and detect fraud by introducing checks and balances to assess effectiveness and validity of transactions in the organization and thereby in its ERP system and accounting system(s).
The accounting guidelines IAS and IFRS describe requirements to financial statements (reports) and the underlying accounting process 33See also http://en.wikipedia.org/wiki/International_Financial_Reporting_Standards. Said requirements include qualitative characteristics:
While some - if not most - of these characteristics relate to the process of accounting, the “Verifiability” item clearly has an impact on the underlaying accounting systems: In order to be verifiable, there must be a clear audit trail to show the origin of the figures. To make sure users leave behind the required audit trail, some actions can’t be performed in LedgerSMB, even though it would seem to be a logical requirement to be able to do so - from the perspective of a non-accountant.
While both the qualitative characteristics from IFRS and the checks and balances from the internal controls are pose restrictions on the accounting process, sometimes these restrictions require support from the underlying accounting software.
One example is the support for creating reliable audit trails by protecting accounting data from deletion. It’s important to realize the scope of accounting data in this respect: because invoices are being registered in the accounts receivable administration - which is summarized in the general ledger - they are part of the data for which the audit trail needs to be recorded.
Another example is separation of duties (also known as the “four eye principle”), where one accountant enters financial transactions and another is responsible for posting them. This procedure protects the company from an accountant single-handedly faking transactions and possibly masking fraud.
The requirements for good accounting processes and internal control have impact on the work flows supported by LedgerSMB. As a consequence some of the work flows described in part V may seem unwieldy; an example being the lack of functionality to delete or correct incorrect invoices (See Section 33.5 on page 33.5 for more details).
This part of the book will run the reader through the LedgerSMB using an example startup company run by Jack: Example Inc, which starts its life as a computer parts store for the business to business market.
Jack just completed incorporation of Example and is ready to start doing business. Before starting his operation Jack was looking for tooling to run his operation efficiently. To that extent he’ll be running LedgerSMB using the domain he acquired for his business: http://example.com/.
The other chapters in this part of the book show you what steps Jack has to go through to get LedgerSMB up and running for Example, as well as the steps he has to take to keep LedgerSMB in good health.
Due to its success Example will grow, posing new challenges to LedgerSMB and we’ll show you how Jack can change the configuration to adapt to his growing business’s needs.
Jack chooses to use a hosted LedgerSMB, so he doesn’t need to concern himself with the technical details of getting the application up and running. Instead he can start by setting up the company database immediately.
In Chapter 5 starting at page 5 and Chapter 6 starting at page 6 Jack goes through the steps of setting up a basic company. The chapters after that may not apply to every business. Chapter 7 starting at page 7, Chapter 8 starting at page 8 and Chapter 9 starting at page 9 apply to businesses dealing with physical goods: buying, selling and shipping. Chapter 33 starting at page 33 discusses how to handle invoicing from LedgerSMB. Chapter 11 starting at page 11, Chapter 12 starting at page 12 and Chapter 13 starting at page 13 discuss how to manage accounts receivable and payable including arrears monitoring.
Not all chapters may be relevant to the reader, e.g. when he or she is starting up or running a services company in which case the chapter “Building up stock” doesn’t apply. Chapters can be skipped based on relevance both to the type of business and its growth phase.
@@@ more chapters??
LedgerSMB comes with a tool called ’setup.pl’. It’s the beginning of a web-based database administration interface to LedgerSMB. This tool can be used to create company databases as well as backups of existing ones. There’s also a command line based Unix-only setup tool - this tool is covered in the next section.
Please note that while executing the steps described in this section, it may take a while for the next screen to appear after clicking on each button: Some buttons involve a large amount of processing on the server before the next screen can be presented.
Jack installed LedgerSMB using the default installation instructions, which means that the url for his setup.pl is https://example.com/ledgersmb/setup.pl. Figure 5.1 on page 5.1 shows the login screen for the tool.
Please note that you can’t be logged into the administration tool (setup.pl) and the webapp through the same browser at the same time in this version of LedgerSMB - due to technicalities.
The login screen shows three fields: (a) a user, (b) a password and (c) a database name.
The user name you use to log in needs to be a PostgreSQL super user: a user you can use to log into the database using the “psql” command as well.
The password must be the same as the one used to log in from the command line using the “psql” tool or the password you used upon creation using the “createdb” tool. Both “psql” and “createdb” are PostgreSQL tools (not LedgerSMB tools).
The third field is the name of the database to be created, which is the same as the name of the company to be logged into later on through the login page shown in Figure 6.1 on page 6.1. The next paragraph discusses this field and its meaning more in-depth.
When creating a company database, there are a few things that are of importance:
The name of the database is the same as the name used at login time and hence will be used by all users - a choice of a suitable, recognizable value is important
The name of the database entered (and hence the company name) is case-sensitive
The name can’t be more than 63 characters long
After choosing “example_inc” as his company name, Jack clicks the Login button at which time the screen from Figure 5.2 on page 5.2 shows up. The screen says the database doesn’t yet exist and offers its creation. The backup buttons being offered are not useful at this stage - they have value if the database exists and setup.pl is being used to upgrade the database; one of its other functionalities.
Jack clicks “Yes” to create the database and load it with LedgerSMB’s database schema, authorization rules and stored procedures44Parts of the program inside the database.. It may take a while (30 seconds or more) for the next screen to appear55Note that during the creation of the database, logs are kept so that if there are errors these can be reviewed either by the person installing the software or by support personnel. On Linux/Unix systems these are stored, by default, in /tmp/ledgersmb/ and named dblog, dblog_stderr and dblog_stdout..
LedgerSMB comes with numerous predefined charts of accounts. These have been grouped per country making the selection of a chart a two-step procedure. setup.pl allows for users wanting to define their own charts by offering a “Skip” button. This button skips the process of loading a chart.
Note that you need to define a chart of accounts before you can meaningfully do anything inside LedgerSMB. If you don’t load a pre-defined one you’ll need to create or import your own later.
Figure 5.3 on page 5.3 shows the first screen in the chart of accounts selection procedure. In this screen you select the country for which you want to use the chart of accounts. Note that charts of accounts are highly country dependent and you may want to consult an accountant if no default chart of accounts is included for your country.
As Jack runs his company in the US, that’s what he selects.
Jack selects the General.sql chart of accounts: that will leave him enough room to specialize the setup later if he has to, but for the time being offers a broadly useable setup.
In the last paragraph the technical part of the company creation procedure was completed. However, it’s not possible to log in to the company yet. This paragraph describes how to create a first user. Figure 5.5 on page 5.5 shows the user creation screen. The fields in this screen have the same meaning as those discussed as part of user management in Section 20.2 on page 20.2.
Jack chooses to create an administrative user called admin who will be authorized to do everything in the application. Later on he will also create a user jack who will be authorized to do everything but changing the configuration and doing application administration. He’ll use the latter user to log in for day to day operations. This will help him prevent changing things by accident.
Note that none of the fields in this screen are optional. If the name of the user being created isn’t already used with other companies, leave the Import option set to No, otherwise please read the chapter on user creation mentioned above.
The setup.pl program offers the user to be created to be one of two types:
Jack’s choice for a fully authorized admin user leads him to select the All permissions option.
Jack succeeded to correctly create his company database. His story continues in the next chapter “The first login”.
To be done.
After the company has been created by executing the procedure described in the last chapter it is still an empty shell which needs to be populated. The correct data needs to be entered for things like bank accounts and company contact data to be used on invoices.
These steps have to be completed before LedgerSMB can be used meaningfully: these settings have to be present for many workflows. A major reason is that with LedgerSMB - as most ERP systems - financial consequences of events in many workflows are directly reflected in the company’s books. Some accounting related settings have to be completed before LedgerSMB can do so.
The login screen shows three fields which need to be filled out as follows:
The user name created during database setup; Jack uses admin
The password - in this case for Jack’s admin user
The name of the database created; Jack uses example_inc
After successful login, the system shows the user preferences screen as depicted in Figure 6.2 on page 6.2 to facilitate the required password change. The initial password has a 24-hour validity limit to prevent unused user accounts from posing a security risk.
The password Jack chooses may be either the same as the one he used before. Not clicking the Save button means the password remains unchanged and the 24-hour limit remains in effect.
The password reset dialog won’t show on subsequent logins until one week before expiry. Login will be denied to users with expired passwords; they can request password resets through user admins.
As part of the start up activities of his company, Jack comes to an agreement with the bank for three products:
A current account with number “C54769”
Deposit account with number “D54990”
Credit card with a number ending with “.7734”
Most accounting systems - LedgerSMB included - use separate GL accounts to represent each bank account. This allows easy reconciliation of the ending balance on the bank account with the balance in the books.
Renames the original account 1060 “Checking account” into “Checking account C54769”
Open the 1060 account again
Change the number to 1061
Change the description to “Cash deposit account D54990”
Click “Save as”
Then Jack repeats the steps 3 to 6 for the credit card with the account number 1062 and description “Credit card xxxx.xxxx.7734” to set up the credit card. Figure 6.4 on page 6.4 shows the screen in which to enter the account details. Section 22.2.1 on page 22.2.1 discusses the options in detail - for now using the settings as configured for the sample checking account will do.
First and foremost the chart of accounts serves to register income, expenses, assets and liabilities in categories which support financial decision making or regulatory requirements. When checking his chart of accounts, this is the first thing Jack checks for.
Many business events in LedgerSMB trigger the creation of financial transactions. If the configuration required for these transactions to be created isn’t in place, users won’t be able to complete their workflows.
Jack wants to make sure his chart of accounts fits his purposes. To perform these checks Jack goes into the System Chart of Accounts List Accounts page. For now, he finds the ledger to be in order. Although the single Sales account stands out a bit against the numerous expense accounts, it turns out that there is also a single Purchases account on which all the expenses for parts purchases are going to be booked.
For LedgerSMB to operate correctly, a number of accounts have to be configured for their specific purpose. He continues on the same screen as the previous section. His checks concern the values in the Link column of the screen. These values have to be present:
|AR||The summary account for accounts receivables; most example charts of accounts have one.|
|AR_overpayment||Receivables overpayments; if a customer pays too much or gets a credit invoice for an invoice already paid, this is where his credit gets recorded if it’s not refunded immediately|
|AR_discount||Sales discounts; if a customer pays within the specified terms, a discount is applied. In order to monitor effectiveness of the discount offered, one probably wants it posted on its own account.|
|AP||Same as AR, except for payables.|
|AP_overpayment||Same as AR_overpayment, except that this registers credits others owe you|
|AP_discount||Same as AR_discount, except that it applies to payables|
|IC||Summary account for inventory; this account doesn’t apply to companies which sell services only.|
Jack’s chart (the US General.sql standard chart) misses the AR and AP_overpayment links as well as the overpayment accounts. He’ll need to create them by going to the Add Accounts under the same submenu as the List Accounts option. The table below shows the settings required for the account types listed in Table LABEL:tbl:special-purpose-account-types-links.
Jack creates two accounts, one for each line marked in italics in Table LABEL:tbl:special-purpose-account-config-summary:
Two special purpose accounts are currently not assigned their special purpose through the Links column in the Chart of Accounts menu. Instead, their special purpose is indicated in the defaults screen as discussed in Section 6.6 on page 6.6.
Each chart of accounts should have
a foreign exchange gain account in the Income part
a foreign exchange loss account in the Expense part
This restriction applies even for companies which don’t expect to be using foreign currencies since the accounts have to be selected in the defaults screen: there’s no option to leave them blank.
First off, Jack asserts that a sales tax account has been provisioned. He finds it in the Current Liabilities section of his chart of accounts. In his jurisdiction there is only one sales tax rate applicable at any one time, which means this single account will suit his needs just fine. If he had been in a jurisdiction with multiple tax rates applicable, e.g. different rates for different types of goods, he would have been required to create more accounts.
The procedure to create more sales tax accounts is the same as the one used in Section 6.3 on page 6.3, with the notable difference that this time the base account to be used is the sales tax account.
With the password renewal out of the way, Jack can start to set up the company for first use. To do so, Jack selects the System Defaults menu item.
A more elaborate description of the parameters in this screen is provided in subsection 18.3.3. However, when setting up a new company, it’s probably good enough to fill out these parameters (and thus skip the rest):
Business number (e.g. Chamber of commerce number) 
Default language [English]
Default accounts (Inventory, Expense, Income) [1510 Inventory / 4010 Sales / 5010 Purchases]
Foreign exchange result accounts (Foreign exchange gain/ loss) [4450 Gain / 5810 Loss]
Default country [US]
Templates directory [demo]
Password Duration 
Default Email From [email@example.com]
Company Name [Example Inc.]
Company Address [215 Example street - Whereitsat]
Company Phone [555 836 22 55]
Company Fax (optional) [N/A]
Jack enters the values mentioned between the square brackets in the list above.
In this chapter Jack goes through the process of setting up LedgerSMB for his trade activities in computer parts, which includes deciding which parts he wants to resell. From there on he goes to contact a vendor to request a quotation, convert that to an order and receive goods into inventory and invoices into accounts payable.
To prepare LedgerSMB for his parts sales and purchases, Jack needs to configure Parts. The system records inventory for parts and assemblies. Jack won’t use them for his business. There’s more on assemblies in @@@.
Once set up, Jack is ready to execute the ordering process. Even though the process is described here from a purchasing perspective sales work the same way with the roles reversed (Jack will act as a vendor in the sales process).
To start his purchase, Jack creates a RFQ document which he sends one or multiple vendors to let them know he’s interested in their products.
The vendor responds to Jack’s request by issuing a Quotation. From a legal perspective a quotation is a document which promises to deliver the requested goods or services at a certain rate - subject to conditions specifically mentioned. If Jack accepts the quotation and meets the conditions, the vendor is obligated to deliver.
In response to the quotation, Jack will place an order with the vendor to indicate acceptance quotation (or he can let the it expire). When he places the order, that legally means he agrees to the terms and conditions in the quotation. If the vendor delivers the goods or services as per the order, Jack has accepted the legal obligation to pay.
The vendor responds to the order by shipping the goods and services as well as sending an invoice. The invoice legally means the vendor considers to have a claim on the assets of Jack’s company. Jack creates a vendor invoice in his system to record the claim on his company by the vendor and the vendor creates a sales invoice in their system to do the same.
As a result of the above it’s considered bad practice to delete or change invoices once created. The accepted process to adjust invoices is to generate a debit invoice (for purchases) or a credit invoice (for sales) to “undo” the effects of the invoice and letting the other party know about it. Then a new invoice can be generated with the appropriate content. However, when the order process is correctly followed from order to invoice chances of sending the wrong invoice are greatly reduced.
Jack needs to enter a large number of items he’ll be offering in his new shop. He starts out with the easy ones: the ones which will be sold as single items.
Jack chooses a 5TB hard drive by Samsung to be entered into the system as the first item. To do so, he goes to the Goods and Services Add part page from the menu as shown in @@@ (figure to be inserted).
|Description||Samsung Hard drive 5TB / 7200rpm|
|Inventory account||1510 - Inventory|
|Income account||4010 - Sales|
|COGS account||5010 - Purchases|
He decides not to include make/model information, drawings or images yet and since he hasn’t entered vendors or customers in his system yet, he decides to leave those sections blank as well.
After having finished setting up the easy parts, Jack now wants to enter the memory modules he’s going to sell. The problem is that they usually go in pairs, since that’s what the systems consuming them need. However, he expects them to be sold as single items as well and he wants to be able to set a separate price for those occasions.
From his reading of Section 21.1.5 on page 21.1.5, it should be possible to support this scenario with a small work around77It’s planned to directly support this use-case in some version higher than 1.3. From the two solutions available, he chooses option (b): to create a part and an assembly and regularly restock the assembly to 0 (zero) in order to remove the stock from the single item.
As Jack continues to enter more parts into the system, he wonders how he’s going to look up the parts efficiently later on. Returning his reading to Section 21.1.1 on page 21.1.1, he understands that ’part groups’ are the solution to that problem. He decides to create the following list of part groups:
Printers and MFPs
After creating these part groups, the “Group” drop down appears on the parts entry screen, allowing him to assign each of his parts to one of these groups.
Since he doesn’t expect to be running more than one or two types of CPUs, he decides not to create a separate group for those and leaves these two parts unassigned.
Jack selects “ABC Parts” to purchase the inventory he needs to run his company. In order to start buying inventory, ABC Parts needs to be entered as a Vendor to LedgerSMB.
Using the work flow detailed in Section 29.2 on page 29.2 Jack starts to do so by going through the menu AP Vendors Add Vendor. He fills out the Company creation form by clicking the Generate control code button and adding the data as shown in Figure 7.1 on page 7.1.
When he’s done filling out and saving the form, he notices the empty “Discount” drop down. Reading more about account configuration check marks in Section 22.2.3 on page 22.2.3 and going back to the checks on his chart of accounts (Section 6.4 on page 6.4), he finds he’s missing the purchase and sales discount accounts. He adds two accounts as listed in Table LABEL:tbl:special-purpose-account-config-summary on page LABEL:tbl:special-purpose-account-config-summary:
Note the top-left corner stating “Company: ABC Parts” and “Account: 2”. The information entered on the “Addresses”, “Contact Info” and “Bank Accounts” tabs will be attached to the account listed, i.e. account number 2 in this case.
After Jack finishes setting up the parts and vendor information, he decides to use LedgerSMB to draw up a list of items he wants to order from this company. To do so he follows the menu path Quotations RFQ which opens up a screen (shown in Figure 30.1 on page 30.1) for entering a new RFQ .
Remark Note that the RFQ entry screen contains prices; this is misleading at least: the printed output to be sent to the vendor does not. The fact that this screen allows entry of prices could be considered a bug.
After filling out the form in accordance with the description in Section 30.1 on page 30.1, Jack expedites his RFQ to his vendor through e-mail by clicking the “E-mail” button. He finds himself in the screen shown in Figure 7.5 on page 7.5.
The From field of the e-mail to be sent out will be filled using the “Default From” setting documented in Section 18.3.3 on page 18.3.3. The other address fields can be entered by the user and may be readily populated if the customer account has the right contact info items attached: if there are Email, Cc and/or Bcc contact items set up, those will be used to fill these fields.
At the bottom there are three selection lists. The first allows selection of the format used to send the RFQ. Available options are HTML, CSV, Postscript (PS) and PDF. The last two require Postscript and PDF support to be correctly set up. The last selection list selects the language to be used for the RFQ. If no value is selected the system default language is used.
Jack’s vendor (ABC Parts) sends him a quotation in response to his RFQ. Jack and his vendor can go back and forth a few times until Jack likes the offer he’s getting, but for the sake of argument let’s assume this is the final quotation.
Since Jack likes the offer, he wants to place an order with his vendor. To do so he looks up the RFQ he sent to the vendor using the menu path Quotations Reports Search. The screen shows additional buttons now that it shows a saved RFQ.
Jack clicks the “Purchase Order” button which creates a new order from the data in the RFQ. He completes it by entering the prices his vendor has quoted and by modifying it to be in accordance with the quotation. See Section 31.2 on page 31.2 for more detail on the order entry screen. When finished he saves the order and mails it to ABC Parts just like he mailed the RFQ in the previous section.
Having ordered his inventory, the vendor starts shipping. There’s too much to ship at once so the vendor ships the goods in batches: every week he ships what’s available at the end of that week - he needed to order some of the products with the manufacturer.
LedgerSMB helps Jack keep track to see if he has received everything he has ordered and that he’s not receiving too much. Jack goes through the menus Shipping Receive. In the search screen, he fills the vendor name (ABC Parts) and clicks “Continue” to be listed all open orders from ABC Parts. By clicking on the order number, the “Receive Merchandise” screen opens as presented in Figure 7.7 on page 7.7. This allows Jack to handle the incoming shipment. LedgerSMB will automatically update inventory based on the amounts entered as received 88To resolve problems in the inventory tracking parts of LedgerSMB (inherited from before the fork), a significant change has been implemented in 1.3.31: inventory changes won’t be recorded until invoices have been posted. .
This section of the book describes how to set up LedgerSMB and its components. Configuration is assumed to be mostly one-off and rather technical in nature. To find out which tasks might need to be performed in order to keep the application in good health the reader is referred to the section “Administration”.
Section about installing on Apache 2+
items to be discussed:
Forwarding of authentication
performance: cgiD configuration: don’t (yet) [but will be supported once all legacy code is gone]
security: suEXEC environment
Explain how to use lsmb with 1.3 instead of 2+.
security: local vs IP connections
to be discussed:
Individual configuration keys; full discussion of possible values in reference appendix?
Directory to store temporary artifacts. E.g. PDF files are stored here before being sent to the browser. The default on unix variants is /tmp/ledgersmb.
Determines if HTML templates used for the LedgerSMB UI will be stored in precompiled form for improved performance. The value 0 (zero) means no caching; the value 1 means caching in the directory $(tempdir)/lsmb_templates; systems running multiple LedgerSMB instances with customized UIs should use separate values for tempdir for that reason
Identifies if the system has LaTeXavailable: 0 means no LaTeX, 1 means LaTeXsupported
This section contains a list of printers and the commands to be executed in order for the output to be sent to the given printer with the document to be printed fed to the program through standard input. The example below shows how to send standard input data to a printer called “laser” when selecting the item “Laser” in the LedgerSMB printer selection drop down.
Laser = lpr -Plaser
Name of the host to connect to. See the documentation of the -h command line option at http://www.postgresql.org/docs/9.2/static/app-psql.html for more information (documentation unchanged since before 8.3, so applies to older versions as well)
Port number to be used to connect to. See the documentation of the -p option at the same URL as the previous item
Database to connect to when the “Company” entry field in the login screen is left blank
The name space the company resides in; expert setting – should not be used (default is “public”)
Path of the directory in which the PostgreSQL contrib modules (extensions) are located; this setting is used by setup.pl when creating new companies or rebuilding (upgrading) existing ones
Selects whether to use SSL over TCP connections or not; can be “require”, “allow”, “prefer” or “disable”
@@@ This is a list of <module.name >= <level >
This is an advanced setting not typically touched.
@@@ no idea what should go in here. to be investigated.
LedgerSMB uses templates mainly for two things:
Output documents such as pick lists and invoices
The HTML User Interface
This setting relates to the former, which are meant to be copied and customized by users. The system supports having multiple sets in a global setup, where companies can be configured to use different sets.
@@@ Should refer to the ’administration’ section???
AR/AP summary accounts
5 other special purpose accounts, see “Defaults” screen discussion
sales tax accounts
This section enumerates the “System” menu’s immediate children. In some cases the functionality is too complex and is referred to a chapter of its own.
This is a Yes/No value which affects the actions which can be performed on posted financial transactions.
No means transactions can be altered or deleted, even after posting them. Note that if a transaction has been posted before the latest closing date, it can never be altered, not even when this value is in effect.
Yes means transactions can’t be altered after posting. This setting is highly preferred and considered the only correct approach to accounting as it assures visible audit trails and thereby supports fraud detection.
@@@ This item isn’t a system setting; shouldn’t it move to “Transaction approval”?? That way system settings (config) and processes are separated.
@@@ My preference is to remove the setting entirely and rely on year-end workflow. We might add an account checkpoint interface as well at some point –Chris T
It’s advisable to regularly close the books after review. This prevents user error changing reviewed numbers: after closing the books, it’s no longer possible to post in the closed period.
There are also performance benefits to closing the books, because LedgerSMB uses the fact that the figures are known-stable as a performance optimization when calculating account balances.
This is a Yes/No value which - when Yes - causes the system to install triggers to register user actions (creation/adjustments/reversals/etc…) executed on financial transactions.
@@@ Once activated, where can we see it the audit trail??
@@@ This setting should go. In 1.3 the audit trails are always enforced via triggers so this setting does nothing. –CT
Each account is listed at least once, but can be listed many times, if it has had different settings applied over different time periods. E.g. if one of the current VAT rates is 19%, today but it used to be 17.5% until last month, there will be 2 rows for the applicable VAT account. See Chapter 23 starting at page 23 for further discussion of how taxes work in LedgerSMB and the choices involved when being required to handle changes in tax rates.
Each row lists the following fields:
The tax rate to be applied when calculating VAT to be posted on this account.
The ending date of the settings in this row. This can apply to the rate as well as the ordering or the tax rules (but usually applies to the rate).
This has to do with cumulative taxes. For example if two taxes exist and one has an ordering of 0 and one of 1, then the second tax will be calculated on a basis that includes the first. One place where this used to be used was in Quebec, where GST was taxable under PST.
This is used to store an arbitrary identification number for the business. It could be used to store a business license number or anything similar.
The unit of measurement for weights. @@@ why don’t we have a unit of measurement for distance as well??? And maybe a unit of measurement for content?
This setting will be used to preselect an account in the listings of the three categories listed below:
When working with foreign currencies, the system needs two special purpose accounts. One to post the gains onto which are caused by foreign currencies increasing in value; the other to post the losses onto which are caused by foreign currencies decreasing in value.
This setting indicates which country needs to be pre-selected in country selection lists.
The language to be used when no other language has been selected. Several parts of the application require language selection, such as customer, vendor and employee entry screens.
This setting indicates which set of templates - stored in the templates/ directory - should be used. In a standard installation, the drop down lists two items:
which contains templates based on LaTeX, which is more commonly installed but has issues dealing with accented characters
which contains the same templates based on TeTeX, which handles UTF-8 input (and thus accented characters) much better than LaTeXand is broadly available, but not usually pre-installed.
Enter a list of all currencies you want to use in your company, identified by their 3-letter codes separated by a colon; i.e. “USD:EUR:CHF”. To ensure correct operation of the application, at least one currency (the company default currency) must be listed. In case of multiple currencies the first is used as the company default currency.
The fields “Company Name”, “Company Address”, “Company Phone” and “Company Fax” will be used on printed/e-mailed invoices.
This is an integer value field measuring the validity period in days for passwords set through the user’s Preferences screen. If this field is empty, passwords set through that method won’t expire.
A user will receive password expiration reminders upon logging starting a week before password expiry. When not acted upon, starting two days before expiry an hourly popup will appear requesting the user to change the password.
The application behaves this way because users with expired passwords won’t be able to log in: their password will need to be reset by a user admin.
Note that passwords set by admins for other users expire within 24 hours after setting them. This value is hard coded and can’t be overruled. This is a security measure taken to make sure as few unused accounts as possible exist: Existence of such accounts could open up security holes.
These addresses will be used to send e-mails from the system. Note that the “Default Email From” address should be configured in order to make sure e-mail doesn’t look like it’s coming from your webserver. The format to be used is ``Name'' <e-mail address> where the e-mail address should be inserted between the “” and “”.
Some elements in the screens may present a drop down. However, drop downs are relatively unwieldy to work with when used to present a large number of values to choose from.
This configuration option sets an upper limit on the number of records to be presented as drop down. When the number is exceeded, no drop down is used. Instead, a multi-step selection procedure will be used.
Many items in the system have sequence numbers: invoices, parts, etc. These sequence numbers can be just a number (i.e. 1 or 37), but they can also be prefixed numbers, e.g. INV0001 for invoices and EMP001 for employees. The numbers shown in the input boxes will be used to generate the next number in the numbering sequence.
|GL Reference number||The default reference number for the next GL transaction.|
|Sales invoice/ AR Transaction number||This number is used to generate an invoice number when none is being filled out by the user.|
|Sales order number||Same as Sales invoice number, except that it’s used for sales orders @@@ layout issue: the label is too big to fit on the page|
|Vendor invoice/ AP Transaction number||Same as Sales invoice, except that the number is used for accounts payable transactions. @@@ layout issue: the label is too big to fit on the page|
|Sales quotation number||Same as sales order number, except that it’s used for quotations.|
|RFQ number||Request for quotation number is like the sales quotation number, except that it is used to track which vendors have been asked for quotes.|
|Part number||All parts, services and assemblies are identified by a unique number. When an item is created and no number is entered by the user, a number is generated from this sequence.|
|Job/project number||Used when creating new projects.|
|Employee number||Same as the sales invoice number, used by new employee entry.|
|Customer number||@@@ is this the control code number? or is this meta_number?? – Meta-number (CT)|
|Vendor number||@@@ same question as customer number|
The prefix to use when printing checks. There’s no check sequence number. That sequence number is requested from the check printing interface, because checks can be created outside the application as well, meaning the numbers can get out of sync.
@@@ Rename “Yearend” in menu interface to “Year end”.
@@@ IMO this section doesn’t belong here, because it’s a process, not config, but does it belong in this menu then? IMO it doesn’t…
@@@ Same as Year end; doesn’t belong here…
@@@ Chart of accounts isn’t exactly a “process”, but it doesn’t feel like being pure config either. At any rate it’s a fact that the CoA discussion is a full chapter in and of itself - so discussion here isn’t necessary anymore.
Warehouses are stocking locations. They don’t have any properties (in the system) other than that they have a name. Warehouses can be added, modified and deleted from the System Warehouses menu item.
Departments can be used to divide a company in smaller pieces. LedgerSMB distinguishes two types of departments:
which can be associated with any type of transaction, including AR transactions.
which can be associated with all types of transactions, except AR transactions.
Departments can be created (added), modified or deleted through the System Departments menu item.
Types of business are used in sales operations where customers can be assigned a type of business. Based on the type of business assignment, quotations, sales orders and invoices will automatically apply discount rates. For each type of business you enter a description and a discount rate to be applied.
The language table is the table users can select languages from, both to present the UI of the application as well as the setting for customers to be used to generate documents.
This listing should correspond to the actual translations of the application being available in the program installation directory.
Languages can be added, modified or deleted through the System Language menu item.
SI codes feature these three fields:
When creating a company you can assign that it an SI code, irrespective of its role (i.e. customer, vendor, lead or anything else). An example of an SI code system is the US’s NAICS code99http://www.census.gov/eos/www/naics/. Other countries have their own coding systems such as ANZSIC1010http://www.abs.gov.au/ausstats/abs@.nsf/mf/1292.0 for Australia and New Zealand and NACE1111http://ec.europa.eu/competition/mergers/cases/index/nace\_all.html for Europe
The SIC field currently doesn’t support a specific function in the application and is there merely for informational purposes. However in the future its role could be extended to include impact on reports, taxes or other functionalities where type of industry could matter.
Same as the last subsection.
Same as the previous subsection.
This part of the book describes the tasks and processes that may need to be carried out on a regular basis in order to keep the application in good health and in line with requirements from end users.
Maintenance may require different types of system access for different types of tasks:
Within application tasks, such as user management, require an appropriately authorized application login
Database administration tasks, such as creation of backups and application upgrades, require a database level login to be used with (amongst others) ’setup.pl’
Other system-level maintenance tasks, such as updating PostgreSQL or Apache, which require user accounts on the server hosting LedgerSMB
Each of the above tasks requires its own type of user account. Creating and managing accounts for the first type of task is the subject of the next chapter.
This chapter deals with management of application level user accounts. This is the first of the three types of system access required to do LedgerSMB administration described in the previous chapter.
Users experienced with LedgerSMB 1.2 or before or SQL Ledger (any version) are referred to appendix A.1 to read about the differences with version 1.3.
In order to create users, the current user must be sufficiently authorized. The user created at application set up time is such a user.
The value entered in the ’Username’ field will cause a database user by that name to be created. Database users are a global resource, meaning that a collision will occur if multiple people try to define the same user in multiple companies. Section 20.5 on page 20.5 describes how to use the same user across multiple companies.
Enter the password to be used for this user into the “Password” field. If you’re importing a user, please leave the field empty – that will prevent the password from being changed. Note that initial passwords (and password resets) are only valid for one day unless the user logs in and changes his/her password.
All of the “First Name”, “Last Name” and “Employee No.” fields are required. However, when no employee number is specified, the system will generate one using the sequence specified in the Defaults screen as documented in Section 18.3.3 on page 18.3.3.
The “Country” field speaks for itself and is required as well.
After filling out all the fields as described in the previous section and clicking “Save user”, you’ll be presented a second screen in the user creation process: the user authorization screen. See Figure 20.2 on page 20.2 for a screenshot of the top of that screen.
The process of assigning user authorizations is the process by which the granted access to specific parts of the application. One can imagine that - in a moderately sized company - sales should not be editing accounting data and accountants should not be editing sales data. Yet, in order to cooperate, both parties need to be given access to the same application. This is where authorizations come in.
In aforementioned screen, which equals the “Edit user” screen, you have to assign the newly created user his application rights. By default, the user doesn’t have any rights. Checking all check marks makes the user an application “super user”, i.e. gives the user all available application rights.
When the role of a user in the company changes, it may be necessary to assign that user new roles and possibly revoke some other roles. This can be done through user search: System Admin Users Search Users Search  which brings you to the same screen as presented in Figure 20.2 on page 20.2.
Similarly, there may be reasons to change the user information, such as a last name (e.g. upon marriage).
Note that if you reset a password, the new password is valid for one day unless changed. The preferred workflow is for the individual with permissions to reset the password, give the new password to the user, they then log in and immediately change it.
From the “User search” result screen, users can be “deleted” from the company: they have their access to the current company revoked.
Note that the user is only revoked access to the current company; the login remains a valid login for the database cluster. Administrators wanting to remove user accounts at the database level need to take additional action.
If a database user already exists, e.g. because this user was created to be used with another LedgerSMB company, it can’t be created a second time. In order to be able to use that user with the current company, it needs to be “imported” instead.
The difference between creating a new user and importing one is that the “Import” radio button should be set to “Yes” and that you should not fill out a password. If you do, the password of that user will be overwritten for all companies.
All other fields are still applicable: the data entered for other companies isn’t copied to the current company.
As mentioned in the introduction, users created through the process documented above don’t have rights to execute work with the setup.pl database administration tool. Note that this is on purpose. You will need access to the server to create such users, or request one from your application service provider (ASP) if you use a hosted solution.
During the set up process such a user is normally being created. This user can later be used to manage the database from the database administration tool setup.pl.
Structure of products in the system.
The part entry screen consists of four parts:
The paragraphs below discuss each of the four sections. The part definition section contains both required and optional fields. The information in the remaining three sections is entirely optional.
Every part requires the following fields to be entered:
The (alphanumeric) code the company uses to identify the item
The (native language) description of the item, used as the default description on sales invoices
The asset account used to maintain the monetary equivalent of the inventory amount
The P&L (income) account to post the sales revenues on
The P&L (expense) Cost of Goods Sold (COGS) account to use to post cost of sold items on
The default selling price used on sales invoices
The other fields in this section of the screen are optional:
Informational; can be used for any (monetary) purpose
Last buying price, updated when a vendor invoice listing the current part is posted
Markup on Last cost to calculate the Sell price
A five-character field shown on the invoice
Informational; can be used by add-ons or customizations
Reorder point - when the inventory drops below this number, the part will show up in “Short parts” reports
The storage location in the warehouse
Apart from these fields, there are also the Make and Model paired fields. Every part can have as many Make/Model lines as required. They are informational, but can be used in customizations of the software.
The Average Cost and On Hand fields are output-only calculated fields. Average Cost is calculated from the historic buying prices. On Hand is the current inventory, which is updated when posting a vendor invoice (increased) or sales invoice (decreased).
Right below the accounts selection section, there is the Tax section, which lists all tax accounts with a check mark. Each account corresponds with a certain tax type and rate. E.g. in the Netherlands, there’s one VAT tax type (BTW) which has two rates, one of which applies to every product. This setup requires two accounts. There’s more on the subject of sales taxes in Chapter 23 starting at page 23.
By checking the checkmark on an account, the system is signalled to calculate that kind of tax for the part if the customer (or vendor) has the appropriate setup as described in section
This section of the screen lists one or more vendors from which the part can be purchased, with purchasing information for the given vendor:
Code used by the vendor to identify the good, to be used by customizations and future enhancements (currently informational only)
Lead time of the part from the vendor in days
Last price at which the good was purchased from the vendor
The currency belonging to the Last cost field
The customer information section specifies sell prices per customer or price group where those are required to deviate from the default sell price. This mechanism exists to support the marketing principle of categorizing customers.
Price for this part to be used for this customer
Start of the applicability window of the price (inclusive)
End of the applicability window of the price (inclusive)
All goods and services can be categorized in ’part groups’. Upon lookup, these can help to limit the number of matches when searching for a partial part number.
As long as no part groups have been defined, the part group assignment field doesn’t show up on the goods entry screens.
There’s no requirement that a good be assigned to any specific part group if part groups have been configured, however, a good can be assigned to more than one part group.
Often times, one may want to sell pre-packaged multiples of a single item, such as Jack in Chapter 7 starting at page 7 who wants to sell memory modules in pairs as well as single items, with the price for the pair set separately from the single-item price.
There are basically two use-cases
Pre-packaged sales which are separately stocked
Single item sales which are achieved by unwrapping the multi-item packages
The former case would be that of a supermarket selling packages of coffee in singles and shrink-wrapped in pairs. These items get stocked, sold and produced separately. Handling these is straight forward: as they are basically separate products from the point of administration and inventory management, they’re handled as separate parts.
The latter case would be Jack’s case where single-item sales is achieved by unwrapping a multi-item package. Basically, there’s a single inventory for both types of packaging. This situation can be handled (a) by creating separate parts or (b) by creating a part and an assembly. To reiterate: The fact that one wants to be able to separately set a pricing strategy for the bundled items is the driver to go look for other solutions than just sell a multiple of the original item.
There is no option which matches the actual practice: one inventory for two parts. The solution always will keeps two separate stocks for the two items, but one may work better in practice than the other.
Option a (separate parts) keeps the inventory of both items strictly separate, meaning there’s no way to convert between the two, other than selling and buying.
Option b (a part and an assembly) mismatches reality in so far that it will require one to stock the single items and update the assembly stock regularly while in practice the multi-item packages are stocked and unwrapped upon single-item sales. This procedure works to have the assembly stock go negative on sales and restock regularly (e.g. daily) to update assembly stock back to 0 (zero). This removes inventory from the single-item stock, allowing for a semi-automated way to convert stock from one type of inventory to another.
The system allows ordering accounts into groups by assigning accounts to headers. Headers can themselves be assigned to other headers resulting in trees of account groups.1212Although the database structure supports this type of account hierarchy doesn’t the 1.3 user take advantage of it yet: in 1.3 accounts can be assigned a header, but headers can’t be assigned to headers themselves.
Headers don’t have any configuration, other than their number and description. Accounts also have a number and description, but require additional configuration for the application to work correctly. The settings are described in the sections that follow.
Contra This checkmark identifies the account as a contra account, which means that the account is going to hold the opposite of an account it’s associated with. A good example of this kind would be the depreciation account associated with a fixed asset account where the depreciation account contains the credit amount to be added to the original asset (debit) value to get the current asset value.
There are currently three types of summary accounts:
AR Marking an account as a summary account for AR means that all outstanding receivable amounts will be posted to this account. The Accounts Receivable administration will contain the details of which amount is owed by which customer.
AP Same as the AR account, except for amounts owed to vendors.
Inventory This account holds the monetary value equal to the items on stock.
@@@ Add summary
This check mark adds the account to the list of accounts in the transaction and invoice screens which are used to post income on.
Adds the account to the customer entry screen’s selection list for accounts to post
The payables UI works the same way as the receivables UI. The difference is that the technical names of the configuration identifiers are prefixed by AP_ instead of AR_.
The items on this line relate to stocked items, i.e. those tracked for inventory: parts and assemblies.
Adds the account to the selection list of income accounts on the part and assembly definition screens.
Adds the account to the selection list of COGS @@@ accounts on the part, assembly and overhead definition screen.
@@@ Question: Labor/Overhead accounts == inventory accounts??
The items on this line relate to untracked (non stocked) items, i.e. services.
Adds the account to the income account selection list in the service definition screen.
Adds the account to the expense account selection list in the service definition screen.
Marks the account as holding the original asset value for the fixed assets module, for some classes of fixed assets.
Marks the account as holding the cumulative depreciation amount for the fixed assets module, for some classes of fixed assets.
Account to hold book value gain upon disposal of a fixed asset.
Account to hold book value loss upon disposal of a fixed asset.
Next to the regular account numbering scheme, LedgerSMB supports a second numbering scheme: GIFI numbering. is a standardized account coding system used in Canada by the Canada Revenue Agency for processing corporate Tax Returns forms.
Other countries (e.g. France) may have required numbering schemes for corporate accounting as well.
If you use GIFI account numbers, each account is associated with a GIFI account. Multiple accounts may map to a single GIFI account.
Many General Ledger reports exist in two variants: a variant using the normal G/L accounts and one with the GIFI numbering scheme. In the GIFI variant, when a single GIFI has multiple accounts, the total reported under GIFI is the sum of the mapped accounts.
GIFI accounts should be created before being assigned to a standard G/L account. GIFI accounts can be maintained through the System Chart of accounts Add GIFI and List GIFI menu items. Existing accounts can be edited by selecting them from the List GIFI menu, which opens a page where individual GIFI items can have their number or description adjusted.
The account will be shown in the customer and vendor account screens with a check mark to mark it “relevant for the customer”
The account will be shown in the part and service configuration screens with a check mark to mark it “relevant for the part/service item”
The account will be shown in the tax configuration screen in order to set a tax percentage on the account as discussed in the next section
By marking an account relevant for a customer, taxes will be calculated when creating a sales invoice for the given customer which includes parts which also have the specific account marked as relevant.
The screen (shown in Figure 23.2 on page 23.2) presents a table with the full history of the percentage rates, their dates of applicability and the tax accounts they apply to. An account can show up any number of times, with different Valid To dates.
The first column; not an input field
The rate to be applied to the sales amount
The minimum amount for which the tax is applicable
The tax reporting number to be used for this tax
Date until which the tax rate is applicable
The meaning of this field is described below
To determine which line is applicable at any time, order the lines by their validity date and select the first one for which the validity date is later than the date being checked for. E.g. if you have two lines - one with a Valid To date of 2010-01-01 and one with a Valid To date of “infinity” - the 2010 line would be selected when checking for a date in 2009 while the “infinity” line would be selected for any date later than Jan 1st, 2010. This facilitates advance entry of a new tax rate in case of a rate change if a tax rate changes: the new rate will automatically be selected beyond the validity date of the old rate.
The order field is used to layer taxes, e.g. Canada where the province of Quebec taxes the taxes collected by the national authority, which works like this numerically: National tax with order 0 and rate 10% and Quebec tax with order 1 and rate 5% applied to a 10$ amount charges 1$ of national tax and 0.55$ of Quebec tax (5% of 11$).
Note that - as pointed out in the Overview section of this chapter - the customer related tax calculation on invoices is triggered by the settings in this chapter as well as the customer/vendor settings described in Section 29.2 on page 29.2 and part settings and service settings described in Section 21.1.1 on page 21.1.1 and Section 21.2.1 on page 21.2.1 respectively.
The tax calculation system has been designed to handle the most complex tax system thinkable. Because the tax calculation rules for most set ups aren’t really all that complex, LedgerSMB comes with a single tax calculation plug in out of the box: the “Simple” tax calculation rule.
For more complex needs, more complex routines can be developed and plugged into LedgerSMB side-by-side with the simple rule.
The tax forms facility exists to help file sales tax forms. It’s primarily modelled after the 1099 US tax reporting forms functionality, which means that it’s cash based. The fact that it’s cash based means that invoices will be included in the tax report as soon as cash for the invoice is received. Accrual based reporting means that the tax is reported based on the invoice date instead of the payment date.
This system can only be used in some EU countries and by some companies at that: the country needs to allow cash based reporting and the company needs to have filed for cash based reporting as well.
Before using this functionality be sure to check with an accountant or the tax authorities if cash based reporting is appropriate.
Tax forms are meant to support tax reporting requirements in the countries where the company has tax reporting obligations. This means that tax forms are country specific: each country for which reporting obligations exist needs to have its own form associated.
When associations have been set up between companies and tax forms, the “Tax form” check mark can be used in invoices to include (or exclude) invoice lines from the tax form. See Section 33.2.1 on page 33.2.1 for entry of invoices.
Note that there’s a module available for 1.3 which implements accrual based tax reporting by the name of EU VAT reporting. This module replaces the default tax reporting functionality meaning one can only have accrual based or cash based reports in a single company at this time, but not both.
Tax forms can be created (or edited) using the menu options available under the “Taxforms” main menu item.
Tax forms have three fields.
Country to which the tax form applies
Name of the tax form to be displayed in drop down lists
In the application there are four reasons for an invoice to include a discount:
Because a discount is entered by the sales person creating the quotation, sales order or sales invoice
Because the customer’s payment terms include a discount for paying early
Because the customer belongs to a type of business which is entitled to discounts
Because the customer has been assigned a price matrix leading to discounts
@@@ types of business are ’old school’; price groups have been introduced to replace types of business with a more fine-grained structure.
Types of business are really straight forward: they feature a description which allows them to be identified in the customer account screen and a discount percentage which is applied across the board. I.e. all invoices to the customer will have that discount applied.
The remainder of the chapter discusses the steps to be executed. Please note that the instructions below mean a window where the application can’t be used during the entire execution of the procedure.
Backup database roles (authorizations)
Backup database content (structure and data)
Backup software and settings files
@@@ Untar / use package manager
@@@ go to setup.pl and “Rebuild”
As business requirements change sometimes it may be necessary to add or remove some of the optional features of LedgerSMB. This chapter describes how these optional features work, how to troubleshoot them of things go wrong, and how to enable or disable them.
LedgerSMB can create PDF and Postscript documents for orders, invoices, and more. This is an optional feature and requires some additional software not included with LedgerSMB, including a LaTeX distribution and extensions to Perl’s TemplateToolkit framework.
The PDF and PS invoices are generated using a program called LaTeX which handles the layout and typesetting. The actual LaTeX files are creating using Template Toolkit with extensions for LaTeX. These extensions are in the Template::Latex package available from CPAN. The software then generates a LaTeX file which is then processed to create a PDF or PS.
Typically the first thing to do is to install a LaTeX distribution like TexLive (distributed with many Linux distributions and available for OSX and Windows). This provides LaTeX and many of the modules needed. In general I recommend that if your distro has a texlive-extras package that you install this too.
After this is installed, you must then install Template::Latex. This can be done by typing on the command line:
# cpan Template::Latex
This will also install a number of dependencies including LaTeX::Driver, which will need to know where your LaTeX binaries are. It is usually pretty good at finding them.
If things go wrong and you can’t get it to work, the following commands may provide useful diagnostic information when requesting help:
From the LedgerSMB application directory: $ perl -MLedgerSMB::Template::Latex -e print
From the doc/manual directory in the LedgerSMB application directory: # pdflatex LedgerSMB-manual
In order to understand how to use customers and vendors, this section describes how LedgerSMB implements these concepts.
When creating a customer or vendor in LedgerSMB, you have to create a company 1313The LedgerSMB database model allows much more flexible constructs involving humans as well as companies or legal entities, but this chapter discusses the web interface exposed functionality only.. However, this company can’t itself be used as a one. Instead, you have to create an “account” 1414These “accounts” are referred to as (entity) credit accounts as well as customers or vendors. which is linked to the company. An account can have either the role of vendor or customer. Due to this construct, a single company can both be vendor and customer which is sometimes desirable.
One company can have multiple customer and/or vendor type accounts. Each account has its own language settings, shipping address, contact data and payment conditions. Basically, you will create multiple accounts when you have to record different data for any of the items listed above.
The procedure to create customers and vendors works exactly alike:
Creation of a company
Creation of a credit entity account (”account”)
Attaching addresses, contact info and notes to the account
A company has the following fields:
Code to uniquely identify the company
Legal name of the company
Country of incorporation
Tax (VAT/Sales tax) number of the company
Code used to identify the type of business of the company
The “Generate Control code” button generates a new control code upon user request when the user is entering a company which isn’t yet known in the system in any other role.
When a company of class “Customer” or “Vendor” has been created, accounts of that type can be added. The account entry screen lists the following fields1515To simplify the interface if they’re unused, some fields are not shown in case their selection lists are empty:
Number to identify this account among all other accounts in the company; when left empty, the system will generate one when you click “Save New”
Textual representation of the account, usually a name
Date from which the account is valid
Date until which the account is valid, or empty if there’s no known end date
Number of days within which invoices have to be paid
Percentage discount the account is entitled to when payment is within the given number of days
Unused - deprecated and removed as of 1.3.24
Account to post created invoices on
Account used as default to accept payments on
Account used to post discounts as calculated based on discount conditions
Deprecated. Should not be used anymore.
Minimum amount for invoices to be sent out
This field classifies a company for the right
The language parameter is used to select templates for communication with the customer
The (default) currency to be used with the customer
Remark Note that the RFQ entry screen contains prices; this is misleading at least: the printed output to be sent to the vendor does not. The fact that this screen allows entry of prices could be considered a bug.
The listing below describes the meaning of the per RFQ fields presented in the screen.
Name of the company the quotation is requested from (issued to)
Currency for the intended transaction
Address to ship to ???
Shipping method ???
The number of the document (automatically generated when left empty)
Date the document is issued
Date of the intended delivery of the goods and services requested ???
Notes to be included on the printed RFQ document sent to the vendor
Notes to be kept internal to the company - not disclosed through the RFQ document
The following per item fields are listed.
Order number of the item
Part number of the item to be ordered
Description or name of the item to be ordered
The number of items to be ordered
The unit in which the quantity is measured; e.g. ’each’ or ’6pack’
Number of items currently on hand (i.e. in stock)
Discount percentage rate
Item price after discount
Stocking unit - code used to track stock
Required date of delivery; can be used to specify a different date than the date in the header
Item specific remarks
(Not shown in the screen shot) Part group for the item
Note that the quotation entry screen offers a number of extra buttons after the data has been saved.
In this chapter the options for creating orders will be discussed. After successful order creation, there are two possible next steps. The first applies to cases where actual goods have to be handled and goes through shipping and receiving as discussed in Chapter 32 starting at page 32. The other skips handling of goods and directly proceeds to invoicing in Chapter 33 starting at page 33.
@@@ What about from time sheets??
Orders fully shipped and invoiced are automatically closed. However, this isn’t always an option.
In case an order ends up being partially shipped and parties agree not to ship the remaining items in the order, the order stays open and incomplete. In situations like these, the order needs to be marked “Closed” in the order entry screen. A note stating why the order was manually closed can be put in the “Internal Notes” input box. Clicking the “Save” button stores the changes in the application database.
@@ Order picking is the work(flow), not the picklist!
@@ Order packing is the work(flow), not the packlist!
The reception process starts by going through the menu to the “Receive” order lookup screen (Shipping Receive). This will show the order search screen which will help finding any orders which have items available for reception, see Figure 32.2 on page 32.2.
After filling order selection criteria and hitting the “Continue” button (or doing so immediately to see all orders with outstanding items in the system), the system will return a listing of orders with matching the selection criteria as in Figure 32.3 on page 32.3.
Upon selection of one of the orders by clicking the order number the next screen is loaded as shown in Figure 32.4 on page 32.4. In this screen you can enter the amounts received in the current lot. The entered data is to be confirmed with the “Done” button. After confirmation of the receipt screen, items will be added to inventory.
LedgerSMB supports warehouse management on the Receipt screen by offering the ability to print a “Bin List”. This list contains the bin (storage) locations configured for each part.
Both shipping and receiving support partial shipments and per-lot invoicing. If the vendor ships multiple lots to a single order (and charges a single invoice), you can simply call up the order and enter items into inventory on the same order. The outstanding items are automatically decremented by the amounts entered before.
Invoices can come from multiple sources. When the quotation and order management functionalities in LedgerSMB aren’t used, they will usually be entered manually. This work flow is covered in Section 33.2. When order management is being used they mostly originate from orders which is covered in Section 33.1.
When a business decides not to use the order management as per the previous chapter it may find itself in need to manually enter invoices. But even if it does use order management, it may be necessary to enter an invoice directly.
When creating a transaction to record that the company owes another entity (a vendor invoice) or that it has outstanding receivables, LedgerSMB offers two options:
Transactions have very limited functionality: they allow a user to enter a debt owed or owned into the AR and AP subsystems. They also require the user to think how the other side of the transaction should be registered; i.e. which cost account the AP transaction should be posted against, or which income account the AR transaction should be posted against. If there are sales taxes applicable, the user is required to manually calculate and enter them.
Invoices offer a much more clever set of functionalities. First of all, it allows the user to create a document to be sent to the vendor or customer. Second, invoices take advantage of parts and services to automate calculation of sales taxes. Third, invoices update inventory for items held in stock (parts, assemblies). Transactions offer none of this.
As mentioned in the previous paragraph, invoices can perform automatic sales tax calculations, maintain inventory and post income (or expense) to the correct GL accounts.
Transactions serve an important purpose not handled by invoices: payroll calculations are often too difficult to fit in the simple “amount times price” model offered by invoices. In order to still be able to track which “vendor” was paid which amount such payment obligations can be recorded in the AP subsystem with a transaction.
Likewise it’s often more hassle than it’s worth to create the parts and services required to correctly calculate the utility bill. In such cases the transaction (possibly with a linked document as supporting evidence) offers good per-vendor traceable history records.
Sometimes, it’s necessary to invalidate an invoice. When an invoice has been posted, this also means derived administrations have been updated, such as inventory for the items on the invoice.
To undo the effects of an invoice, i.e. to reduce the amount outstanding with a customer, use the VOID button on the invoice screen as shown in @@@figref . This creates a new invoice by the same number as the original, except that the new invoice has a suffix -VOID.
There’s only one way to persist an invoice in LedgerSMB: posting it. This means the invoice becomes part of the accounting information. One of the primary properties of an accounting system is to record full audit trails and help enforce internal controls as detailed in Section 2.2 on page 2.2. Because of that fact there’s no way to delete or edit invoices after they have been posted 1616LedgerSMB currently does not support saving an invoice without posting it. This functionality is on the roadmap for addition when the AR/AP functionality is being rewritten - currently 1.5 or 1.6..
The only way to “undo” an invoice is by voiding it. This is important for several reasons:
Invoices can’t be deleted (because they’re accounting data)
Invoices pose a claim on the assets of a customer
Specifically item 2 is important: when you sent the invoice to your customer, you effectively sent them a claim. When you decide to refrain from pursuing that claim, you should notify them of that fact so they have the documentation to update their accounting system to reflect that fact: they need your documentation to void their vendor invoice, instead of paying it.
For the same reason it’s ill-advised (and no longer supported) to edit invoices: when a customer has multiple invoices, each stating a different amount, all using the same invoice number; how is that customer supposed to document (verifiably) that the claim has been settled satisfactorily by paying the one he did?
See the Remarks section at the end of this chapter for details on how to handle the draft invoice requirement.
You can’t edit invoices any more in LedgerSMB 1.3 because it breaks the audit trail in financial accounting. But in fact there’s functionality available which is meant exactly for this purpose. It’s called “Sales order” and its details are in Chapter 31 starting at page 31. Sales orders can be converted - upon customer approval - into an invoice with a click of a button.
@@ section misnomer “Work orders” is not a workflow or workflow step
- this bit is about credit notes and debit notes
–¿ this bit is about credit (sales) and debit (vendor) invoices
A company runs credit risk when it gives credit: it runs the risk of the creditor not paying off its debts. LedgerSMB features two ways to manage the risks involved:
The former tries to limit the risk involved by making sure no customer receives more credit than a certain limit while the latter tries to make sure any over due payments get cashed.
Limit management should prevent a company on one hand from delivering too much to its customers at once and on the other from taking (and delivering) new orders to customers with too high amount of unpaid invoices.
@@@ Where to set up limits
@@@ How to monitor limits
As mentioned before, the process of managing arrears is directed toward detecting arrears positions with customers early and taking appropriate action.
In order to find out about over due invoices, a company should run the AR aging report available under AR Reports AR Aging. The initial screen presents parameters for the aging report to be generated.
@@@ discuss parameters
This report shows customers and their outstanding invoices categorised as:
Invoices not over due
Invoice amounts over due by 30 days or less
Invoice amounts over due by 60 days or less (but more than 30)
Invoice amounts over due by 90 days or less (but more than 60)
@@@ Printing / mailing aging reports to customers
After an invoice becomes over due a process will be started to remind the customer of the outstanding amount requiring payment.
In order to keep records of actions taken to chase customer payment, the invoice screen has an “Internal Notes” field which can be edited after the invoice has been posted.
In order to save any edits to that field, hit the “Save Info” button.
Note that the “Save Info” button also saves any changes to the “TaxForm” column or rather, any information that’s not accounting information (posted to the books and thereby fixed) nor information which appears on the invoice - which also should remain unedited in order to be able to generate an exact copy at a later date.
There may be good reasons to treat some over due invoices differently. E.g. in case payment arrangements have been made with the customer and further standard arrears management would not be appropriate any more.
In this case, you can put an invoice “On Hold”. The opposite of being on hold is being active. The AR Aging report allows selection of all invoices, only active invoices (those not on hold) or only invoices on hold.
Receipt (incoming payments for sales invoices) and payments (outgoing payments for purchase invoices) use the same process. This chapter describes the steps using receipts only for brevity but be equally applied to payments - except when explicitly stated.
LedgerSMB provides two ways to process receipts (and payments). One for single transactions, the other for batches. The next section discusses the steps to do batch processing.
To record an amount received from a customer as an invoice payment, go through the menus Cash Receipt and fill out the search criteria to find the customer from whom the payment has been received. After clicking “Continue” the application lists all matching customers.
After selecting a customer, the cash entry screen as depicted in ¡figure reference¿ is shown. This screen looks the same for single receipts or payments. This screen consists of three parts. The upper block lists customer details on the left and transaction details on the right. The middle block lists any (partially) unpaid invoices. The lower block handles overpayments.
Note that the cash transaction amount isn’t being entered explicitly in this screen: the total is shown on the right of last line above the buttons.
The receipt entry screen can be used to register payment of any outstanding invoices. This is the normal scenario. However, if there are no outstanding invoices, or the amount paid is too high, the transaction should be (partially) recorded as overpayment. Overpayments can be used to pay off invoices at a later date.
@@@ Discuss the single receipt screen
Reversal of overpayments is a weak spot in LedgerSMB 1.3: there’s no way to reverse or “undo” an overpayment incorrectly entered. By consequence this section describes the workaround that’s required to achieve the same effect.
This workaround needs an account which can be used to temporarily book income on.
Please note that the income will immediately be reversed, so technically any account can be used. To be able to assert that the entire process has been executed correctly, it’s advisable to create a separate account, however, since it can be checked to be zero at the end.
With the prerequisites in place, you should execute the following steps - assuming the amount of the overpayment needs to be placed back into a cash account.
Create an AR transaction for the company the overpayment has been entered on
Add a single line to the transaction, with the selected account
Put the overpayment amount to be canceled out in the Amount field for the line
Save and post the transaction
Pay the transaction from the overpayment
Create a “General Journal” transaction debiting the income account and crediting the cash account the overpayment was entered from
Steps 1 trough 4 prepare the Accounts Receivable module with a transaction which allows the overpayment to be used. After step 5, the overpayment has been cleared, but the amount is in the wrong place, since it sits in the income account instead of the cash account, which is what step 6 corrects.
The side-effect from this workaround is an AR transaction registered against a customer which can’t be reversed: doing so, would result in the reversed amount ending up in the AR summary account. Using a dummy company isn’t an option, because overpayments are registered to a specific customer. An overpayment can only be used to clear open items on that specific customer.
Note that the above procedure applies to an AR overpayment. However, the same steps apply to AP overpayments, replacing “customer” with “vendor”, “AR” with “AP”, “Income” with “Expense” and “debit” with “credit”.
@@@ Probably belongs into the Overview part of the book?
Separation of duties is a method to help prevent fraud where one employee can’t modify the ledger by himself - such access could be used to blur or erase tracks of fraud.
Even though the application handles many general ledger postings as consequences from work flows elsewhere in the system - thus not requiring separate postings - sometimes the need may occur to create manual postings not resulting from AR or AP transactions or till and inventory adjustments.
One example of a case like that is the calculation, and posting of corporate taxes presumably at the end of each accounting period but at least at the end of the book year.
Period closing is a concept used by accountants to ensure that audited and known-correct accounting data stays correct by “freezing” it: by closing an accounting period no modifications can be made to the accounting data before a certain date.
LedgerSMB supports this concept through the System Audit Control menu, where you’ll find the “Close Books up to” item. By filling out a date in the input field and hitting Continue posting to dates before the entered date will be disallowed.
Note that due to a design limitation in LedgerSMB 1.3 - to be lifted with the general AR/AP redesign - invoices in foreign currencies can’t be reversed on other dates than their original posting date. That is: they can, but their reversal will result in P&L and balance sheet effects which presumably isn’t desirable. Since period closing disables posting before a certain date this functionality may have negative side effects in some set ups.
Also note that this pertains exclusively to invoices and transactions in foreign currencies and has no effect in case of invoices and transactions in the default currency.
Year end closing is a concept which prepares the accounting books for the next accounting year. Note that this is unrelated to the calendar year but to the accounting year of the company instead. To muddy the waters even more: there’s no inherent requirement for this process to be run at least once a year. If the first book year of the company spans more than a year, then this procedure will be run more than a year after starting up the company.
This procedure freezes the accounting data in the year to be closed as described in the previous section. Additionally it clears out the profit and loss accounts: setting all the account balances to zero by posting their balance to the retained earnings account. Some businesses prefer to create a retained earnings account for each book year they close. LedgerSMB supports that use-case by allowing the user to select which retained earnings account the balance should be posted to.
Some companies want may to include additional transactions related to dividend payment regarding the current year: reduce the equity by the amount paid as dividends in transactions marked as “year-end transaction”. Support for this use case isn’t available in LedgerSMB 1.3.
1099 (cash based)
EU VAT (accrual based)
LedgerSMB 1.3 doesn’t have batch data imports built in out of the box, except for bank statement imports for reconciliation purposes. This chapter starts to explain how to use the one import that is supported out of the box. It then goes on to highlight some often-used customizations: It is quite easy to customize a LedgerSMB 1.3 instance using the LedgerSMB 1.4-built-in data import routines to create a variety of file-upload based data imports.
This function allows bulk import of an entire chart of accounts using a single CSV file.
The first line of the file contains the headers of the columns. The import routine expects the columns as presented in (table name).
Number of the account or header
Description for the account or header
“H” for heading record, “A” for account record
“I” for Income, “E” for Expense, “A” for Asset, “L” for Liability
“0” if not a contra-account, “1” if it is.
“0” if it is not a tax-account, “1” if it is.
Id of the heading to group the account (or heading) under
GIFI account number
All lines after the first one are considered to be data lines.
The way users are defined and used differs greatly between LedgerSMB 1.3 and older versions. In version 1.3 user access to the database is enforced by the database itself. This means that users logging in to the LedgerSMB web application are in reality logging into the PostgreSQL database. In older versions, the web app would verify the user’s credentials (using a common database connection used for all users).
The difference between these approaches is that security is no longer (solely) maintained by the web application - with all inherent risks. Instead, the database now plays an important role as well. The effect is that the LedgerSMB team now leverages the experience of the PostgreSQL community - a highly respected community well known for its security focus - to make sure your data stays secure.
This structure also enables LedgerSMB 1.3 to offer separation of duties and authorizations throughout the application without being required to do a full rewrite of the application.
It’s this shift in paradigm that makes it impossible to meaningfully migrate users from older LedgerSMB and SQL-Ledger versions to LedgerSMB 1.3.
From a mail by Chris Travers on the LedgerSMB mailing list:
Suppose I buy 10 widgets for $1 each and then 1 more widget for $10 each. My FIFO cost queue looks like this:
$1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $10
My inventory account shows $20 in debits and I have credited my AP account to compensate.
I then sell the 11 parts to 11 different people.
The first 10 invoice show a $1 credit against inventory and a $1 debit against COGS
The 11th invoice shows a $10 credit against inventory and a $10 credit against COGS
and now inventory is down to 0.
Now the 5th customer invoice turns out to be in error. We never shipped this one. The customer never ordered it. it was a data entry error. Translation, we now have one in stock.
If we void the invoice properly, we will reverse the last sale, and put $10 back in inventory.
If we delete the invoice, we will just remove the $1 removed. But we don’t really know which one was sold and so we de-allocate the $10 sale.
So now our books are $9 off of what they should be. They show a balance of $1 in inventory, and $19 in cogs. They should show $10 in each. The worse is still to come however.
Now we sell the item we had in stock. This brings our (empty!) inventory value to -$9 and our COGS value to $29. Our books are still $9 off and we now have impossible, nonsensical values. Delete and re-enter a few more invoices and you can inflate the COGS as far as you’d like. This doesn’t work.
Worse, this can’t be fixed. You can’t make a deletion behave just like a reversal and still keep your books transparent auditability-wise. Even if it could be fixed mathematically (which it can’t), there isn’t any agreement as to what the proper behavior should be (except ’don’t do that’). So it isn’t possible to support the workflow ”properly” because ”properly” can’t be defined.
So unless someone can show that the above issues are incorrect, I don’t see how we can support deleting invoices after they are posted to the books.
The alternative is the draft/voucher system which is supported in 1.3 for all non-inventory transactions and will be supported for inventory transactions in 1.4. In this system, in the paper world, the clerk fills out a piece of paper with the information that will be entered as an invoice, and this is eventually gets entered and checked by someone else. Both papers are kept in paper systems for reconciliation purposes but typically we tend to assume they are the same (this may be changing and we may be keeping both copies if they are changed in the future). In this model, the voucher is not an invoice. It is simply a piece of paper that represents what will be on the invoice. It is subject to review, and may ultimately be denied.
So in this system we do *not* calculate extrinsic financial movements for documents until they post. This includes COGS. If a draft invoice or invoice voucher is deleted before it is approved it has none of the problems above. Once approved though, it is a part of the permanent record. This guards against data entry errors because a second person can review the data before it is posted (either in bulk or individually). Additionally this guards against theft by ensuring that a single individual cannot individually enter everything necessary to cover for theft, etc.
There are plenty of reasons to want to migrate to LedgerSMB 1.3:
Separation of duties
Security better integrated into the application
Better, more strict data model
Some important sources of user error eliminated
Better workflows for cash reconciliation
Yet, while item 3 is a good reason to want to switch, it’s also a reason why migration from older versions to 1.3 can be harder than earlier migrations: when the data in your older version is not consistent, it won’t fit into the new data model and will need to be fixed first.
Especially if your database has a very long line of history, being migrated trough lots of SQL-Ledger and LedgerSMB versions, you may want to consider asking for help from a professional party. It could save you a lot of time.
However, don’t be discouraged and have a go yourself first. Just be sure to run your upgrade on a backup database: the migration process is non-destructive, but in case accounting data is involved: better safe than sorry!
Also it is worth noting that a number of automatic checks are performed on your data prior to migration, and to the extent possible, you are given an opportunity to fix those issues identified. Because these checks are pre-migration checks, they are written to your old data and will persist after backing out of a migration to 1.3.
While accounting and ERP solution have wildly differing structures to record their data, this sections uses data with a relatively simple structure as a show case of how this problem may be dealt with.
Note that the encoding you use to transfer to the database depends on the settings used to create the PostgreSQL database with. A migration is a good moment to think about encodings an solve older encoding issues. Now would be a good moment to anticipate the requirement for accented characters and non-western alphabets: set up a UTF-8 encoded database and recode your data accordingly.
The source system for this section uses a structure where every company has one contact person, one address, one phone number and e-mail.
In order to understand how to migrate this data structure to LedgerSMB, it’s important to understand that:
The company from the source maps to the Company and Entity entities
The contact person maps to the Entity Credit Account entity
The address maps to the Location entity - and requires a location class: Sales, Billing or Shipping
The phone number, fax number and e-mail map to Contact items
The reason behind the separation between the Company and Entity entities is that every customer is an Entity, but not all entities are companies, since some entities are Persons - natural persons.
@@@ How to
The strategy that I used when migrating to LedgerSMB is the following:
create customers and vendors as needed
create at least the parts and services for which there are open AR or AP items in the closing balance
create the open AR/AP items in the closing balance by posting them on the original opening date (this allows you to do your aging management in LSMB from day 1)
create in addition the parts for which you have stock in the closing balance
import your stock by posting invoices against the inventory entity
make sure you ”pay” the invoices - e.g. by paying them from equity
draw up the incomplete balance you have so far
calculate the delta of the partially imported balance against the closing balance
import the delta balance on top of what you already had to make a full balance
What I did is post the transaction from the last step on the day before I wanted to start my books; say you want to start your books on 2014-01-01, then you’d post it on 2013-12-31. That way, the opening balance of your books on the starting date is exactly what you had on the closing balance of the books you left behind.
If you’re migrating between PostgreSQL versions, there are a few things to take into account.
LedgerSMB 1.3 uses some extension modules for versions 8.3 and before for functionality that has been built into 8.4 and later. To make use of the (faster) built in version of that functionality, the following restore procedure should be used.
Migrate the database to the new function as described in section @@@ TODO
If you’re using 9.1 and up, issue the command “CREATE EXTENSION tablefunc FROM UNPACKAGED” from a psql prompt when connected to the company database
Run ’setup.pl’ from your browser to upgrade the database’s routines; this command will install routines optimized for your version of PostgreSQL
Run the command
$ psql … -f uninstall_tablefunc.sql
“DROP EXTENSION tablefunc;” from a psql session connected to the company database
to clean up functions and procedures in the database which are no longer used
Application roles specify the right to execute one or more tasks in the application. LedgerSMB enforces these roles by allowing a user to select (list, read) data from or to insert (create), update (edit) or delete (delete) data in the tables holding the data related to the execution of these tasks.
Allows the user to both create new and edit existing GL accounts.
Allows the user to create (but not edit) new GL accounts.
Allows the user to edit (but not create) GL accounts.
Allows the user to create and update sales invoices. If the user needs to be able to enter invoices in foreign currencies, the exchangerate_edit role must be assigned as well.
Superseeded. This role has been replaced by backup functionality in setup.pl
Allows the user to create new batches.
Allows the user to post batches; this authorization includes the right to search for batches (and therefore includes batch_list)
Copyright (c) 2011, 2012 Erik Hülsmann.
This work is licensed under the Creative Commons Attribution License. To view a copy of this license, visit http://creativecommons.org/licenses/by/3.0/ or send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA.
A summary of the license is given below, followed by the full legal text.
You are free: * to share -- to copy, distribute and transmit the work * to remix -- to adapt the work Under the following condition: You must attribute the work in the manner specified by the author or licensor (but in a way that suggests that they endorse you or your use of the work). With the understanding that: Waiver -- Any of the above conditions can be waived if you get permission from the copyright holder. Other rights -- In no way are any of the following rights affected by the license: * Your fair dealing or fair use rights, or other applicable copyright exceptions and limitations; * The author's moral rights; * Rights other persons may have either in the work itself or in how the work is used, such as publicity or privacy rights.
License THE WORK (AS DEFINED BELOW) IS PROVIDED UNDER THE TERMS OF THIS CREATIVE COMMONS PUBLIC LICENSE ("CCPL" OR "LICENSE"). THE WORK IS PROTECTED BY COPYRIGHT AND/OR OTHER APPLICABLE LAW. ANY USE OF THE WORK OTHER THAN AS AUTHORIZED UNDER THIS LICENSE OR COPYRIGHT LAW IS PROHIBITED. BY EXERCISING ANY RIGHTS TO THE WORK PROVIDED HERE, YOU ACCEPT AND AGREE TO BE BOUND BY THE TERMS OF THIS LICENSE. TO THE EXTENT THIS LICENSE MAY BE CONSIDERED TO BE A CONTRACT, THE LICENSOR GRANTS YOU THE RIGHTS CONTAINED HERE IN CONSIDERATION OF YOUR ACCEPTANCE OF SUCH TERMS AND CONDITIONS. 1. Definitions "Adaptation" means a work based upon the Work, or upon the Work and other pre-existing works, such as a translation, adaptation, derivative work, arrangement of music or other alterations of a literary or artistic work, or phonogram or performance and includes cinemato- graphic adaptations or any other form in which the Work may be recast, transformed, or adapted including in any form recognizably derived from the original, except that a work that constitutes a Collection will not be considered an Adaptation for the purpose of this License. For the avoidance of doubt, where the Work is a musical work, performance or phonogram, the synchronization of the Work in timed-relation with a moving image ("synching") will be considered an Adaptation for the purpose of this License. "Collection" means a collection of literary or artistic works, such as encyclopedias and anthologies, or performances, phonograms or broadcasts, or other works or subject matter other than works listed in Section 1(f) below, which, by reason of the selection and arrangement of their contents, constitute intellectual creations, in which the Work is included in its entirety in unmodified form along with one or more other contributions, each constituting separate and independent works in themselves, which together are assembled into a collective whole. A work that constitutes a Collection will not be considered an Adaptation (as defined above) for the purposes of this License. "Distribute" means to make available to the public the original and copies of the Work or Adaptation, as appropriate, through sale or other transfer of ownership. "Licensor" means the individual, individuals, entity or entities that offer(s) the Work under the terms of this License. "Original Author" means, in the case of a literary or artistic work, the individual, individuals, entity or entities who created the Work or if no individual or entity can be identified, the publisher; and in addition (i) in the case of a performance the actors, singers, musicians, dancers, and other persons who act, sing, deliver, declaim, play in, interpret or otherwise perform literary or artistic works or expressions of folklore; (ii) in the case of a phonogram the producer being the person or legal entity who first fixes the sounds of a performance or other sounds; and, (iii) in the case of broadcasts, the organization that transmits the broadcast. "Work" means the literary and/or artistic work offered under the terms of this License including without limitation any production in the literary, scientific and artistic domain, whatever may be the mode or form of its expression including digital form, such as a book, pamphlet and other writing; a lecture, address, sermon or other work of the same nature; a dramatic or dramatico-musical work; a choreographic work or entertainment in dumb show; a musical composition with or without words; a cinematographic work to which are assimilated works expressed by a process analogous to cinematography; a work of drawing, painting, architecture, sculpture, engraving or lithography; a photographic work to which are assimilated works expressed by a process analogous to photography; a work of applied art; an illustration, map, plan, sketch or three-dimensional work relative to geography, topography, architecture or science; a performance; a broadcast; a phonogram; a compilation of data to the extent it is protected as a copyrightable work; or a work performed by a variety or circus performer to the extent it is not otherwise considered a literary or artistic work. "You" means an individual or entity exercising rights under this License who has not previously violated the terms of this License with respect to the Work, or who has received express permission from the Licensor to exercise rights under this License despite a previous violation. "Publicly Perform" means to perform public recitations of the Work and to communicate to the public those public recitations, by any means or process, including by wire or wireless means or public digital performances; to make available to the public Works in such a way that members of the public may access these Works from a place and at a place individually chosen by them; to perform the Work to the public by any means or process and the communication to the public of the performances of the Work, including by public digital performance; to broadcast and rebroadcast the Work by any means including signs, sounds or images. "Reproduce" means to make copies of the Work by any means including without limitation by sound or visual recordings and the right of fixation and reproducing fixations of the Work, including storage of a protected performance or phonogram in digital form or other electronic medium. 2. Fair Dealing Rights. Nothing in this License is intended to reduce, limit, or restrict any uses free from copyright or rights arising from limitations or exceptions that are provided for in connection with the copyright protection under copyright law or other applicable laws. 3. License Grant. Subject to the terms and conditions of this License, Licensor hereby grants You a worldwide, royalty-free, non-exclusive, perpetual (for the duration of the applicable copyright) license to exercise the rights in the Work as stated below: to Reproduce the Work, to incorporate the Work into one or more Collections, and to Reproduce the Work as incorporated in the Collections; to create and Reproduce Adaptations provided that any such Adaptation, including any translation in any medium, takes reasonable steps to clearly label, demarcate or otherwise identify that changes were made to the original Work. For example, a translation could be marked "The original work was translated from English to Spanish," or a modification could indicate "The original work has been modified."; to Distribute and Publicly Perform the Work including as incorporated in Collections; and, to Distribute and Publicly Perform Adaptations. For the avoidance of doubt: Non-waivable Compulsory License Schemes. In those jurisdictions in which the right to collect royalties through any statutory or compulsory licensing scheme cannot be waived, the Licensor reserves the exclusive right to collect such royalties for any exercise by You of the rights granted under this License; Waivable Compulsory License Schemes. In those jurisdictions in which the right to collect royalties through any statutory or compulsory licensing scheme can be waived, the Licensor waives the exclusive right to collect such royalties for any exercise by You of the rights granted under this License; and, Voluntary License Schemes. The Licensor waives the right to collect royalties, whether individually or, in the event that the Licensor is a member of a collecting society that administers voluntary licensing schemes, via that society, from any exercise by You of the rights granted under this License. The above rights may be exercised in all media and formats whether now known or hereafter devised. The above rights include the right to make such modifications as are technically necessary to exercise the rights in other media and formats. Subject to Section 8(f), all rights not expressly granted by Licensor are hereby reserved. 4. Restrictions. The license granted in Section 3 above is expressly made subject to and limited by the following restrictions: You may Distribute or Publicly Perform the Work only under the terms of this License. You must include a copy of, or the Uniform Resource Identifier (URI) for, this License with every copy of the Work You Distribute or Publicly Perform. You may not offer or impose any terms on the Work that restrict the terms of this License or the ability of the recipient of the Work to exercise the rights granted to that recipient under the terms of the License. You may not sublicense the Work. You must keep intact all notices that refer to this License and to the disclaimer of warranties with every copy of the Work You Distribute or Publicly Perform. When You Distribute or Publicly Perform the Work, You may not impose any effective technological measures on the Work that restrict the ability of a recipient of the Work from You to exercise the rights granted to that recipient under the terms of the License. This Section 4(a) applies to the Work as incorporated in a Collection, but this does not require the Collection apart from the Work itself to be made subject to the terms of this License. If You create a Collection, upon notice from any Licensor You must, to the extent practicable, remove from the Collection any credit as required by Section 4(b), as requested. If You create an Adaptation, upon notice from any Licensor You must, to the extent practicable, remove from the Adaptation any credit as required by Section 4(b), as requested. If You Distribute, or Publicly Perform the Work or any Adaptations or Collections, You must, unless a request has been made pursuant to Section 4(a), keep intact all copyright notices for the Work and provide, reasonable to the medium or means You are utilizing: (i) the name of the Original Author (or pseudonym, if applicable) if supplied, and/or if the Original Author and/or Licensor designate another party or parties (e.g., a sponsor institute, publishing entity, journal) for attribution ("Attribution Parties") in Licensor's copyright notice, terms of service or by other reasonable means, the name of such party or parties; (ii) the title of the Work if supplied; (iii) to the extent reasonably practicable, the URI, if any, that Licensor specifies to be associated with the Work, unless such URI does not refer to the copyright notice or licensing information for the Work; and (iv), consistent with Section 3(b), in the case of an Adaptation, a credit identifying the use of the Work in the Adaptation (e.g., "French translation of the Work by Original Author," or "Screenplay based on original Work by Original Author"). The credit required by this Section 4 (b) may be implemented in any reasonable manner; provided, however, that in the case of a Adaptation or Collection, at a minimum such credit will appear, if a credit for all contributing authors of the Adaptation or Collection appears, then as part of these credits and in a manner at least as prominent as the credits for the other contributing authors. For the avoidance of doubt, You may only use the credit required by this Section for the purpose of attribution in the manner set out above and, by exercising Your rights under this License, You may not implicitly or explicitly assert or imply any connection with, sponsorship or endorsement by the Original Author, Licensor and/or Attribution Parties, as appropriate, of You or Your use of the Work, without the separate, express prior written permission of the Original Author, Licensor and/or Attribution Parties. Except as otherwise agreed in writing by the Licensor or as may be otherwise permitted by applicable law, if You Reproduce, Distribute or Publicly Perform the Work either by itself or as part of any Adaptations or Collections, You must not distort, mutilate, modify or take other derogatory action in relation to the Work which would be prejudicial to the Original Author's honor or reputation. Licensor agrees that in those jurisdictions (e.g. Japan), in which any exercise of the right granted in Section 3(b) of this License (the right to make Adaptations) would be deemed to be a distortion, mutilation, modification or other derogatory action prejudicial to the Original Author's honor and reputation, the Licensor will waive or not assert, as appropriate, this Section, to the fullest extent permitted by the applicable national law, to enable You to reasonably exercise Your right under Section 3(b) of this License (right to make Adaptations) but not otherwise. 5. Representations, Warranties and Disclaimer UNLESS OTHERWISE MUTUALLY AGREED TO BY THE PARTIES IN WRITING, LICENSOR OFFERS THE WORK AS-IS AND MAKES NO REPRESENTATIONS OR WARRANTIES OF ANY KIND CONCERNING THE WORK, EXPRESS, IMPLIED, STATUTORY OR OTHERWISE, INCLUDING, WITHOUT LIMITATION, WARRANTIES OF TITLE, MERCHANTIBILITY, FITNESS FOR A PARTICULAR PURPOSE, NONINFRINGEMENT, OR THE ABSENCE OF LATENT OR OTHER DEFECTS, ACCURACY, OR THE PRESENCE OF ABSENCE OF ERRORS, WHETHER OR NOT DISCOVERABLE. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO SUCH EXCLUSION MAY NOT APPLY TO YOU. 6. Limitation on Liability. EXCEPT TO THE EXTENT REQUIRED BY APPLICABLE LAW, IN NO EVENT WILL LICENSOR BE LIABLE TO YOU ON ANY LEGAL THEORY FOR ANY SPECIAL, INCIDENTAL, CONSEQUENTIAL, PUNITIVE OR EXEMPLARY DAMAGES ARISING OUT OF THIS LICENSE OR THE USE OF THE WORK, EVEN IF LICENSOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. 7. Termination This License and the rights granted hereunder will terminate automatically upon any breach by You of the terms of this License. Individuals or entities who have received Adaptations or Collections from You under this License, however, will not have their licenses terminated provided such individuals or entities remain in full compliance with those licenses. Sections 1, 2, 5, 6, 7, and 8 will survive any termination of this License. Subject to the above terms and conditions, the license granted here is perpetual (for the duration of the applicable copyright in the Work). Notwithstanding the above, Licensor reserves the right to release the Work under different license terms or to stop distributing the Work at any time; provided, however that any such election will not serve to withdraw this License (or any other license that has been, or is required to be, granted under the terms of this License), and this License will continue in full force and effect unless terminated as stated above. 8. Miscellaneous Each time You Distribute or Publicly Perform the Work or a Collection, the Licensor offers to the recipient a license to the Work on the same terms and conditions as the license granted to You under this License. Each time You Distribute or Publicly Perform an Adaptation, Licensor offers to the recipient a license to the original Work on the same terms and conditions as the license granted to You under this License. If any provision of this License is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this License, and without further action by the parties to this agreement, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable. No term or provision of this License shall be deemed waived and no breach consented to unless such waiver or consent shall be in writing and signed by the party to be charged with such waiver or consent. This License constitutes the entire agreement between the parties with respect to the Work licensed here. There are no understandings, agreements or representations with respect to the Work not specified here. Licensor shall not be bound by any additional provisions that may appear in any communication from You. This License may not be modified without the mutual written agreement of the Licensor and You. The rights granted under, and the subject matter referenced, in this License were drafted utilizing the terminology of the Berne Convention for the Protection of Literary and Artistic Works (as amended on September 28, 1979), the Rome Convention of 1961, the WIPO Copyright Treaty of 1996, the WIPO Performances and Phonograms Treaty of 1996 and the Universal Copyright Convention (as revised on July 24, 1971). These rights and subject matter take effect in the relevant jurisdiction in which the License terms are sought to be enforced according to the corresponding provisions of the implementation of those treaty provisions in the applicable national law. If the standard suite of rights granted under applicable copyright law includes additional rights not granted under this License, such additional rights are deemed to be included in the License; this License is not intended to restrict the license of any rights under applicable law.