Database setup

You’ll create all the elements in the database that you need to carry out the use case. These are specified in the functional requirements.

Configuring Accounts

You need to start off with a minimal set of accounts, and to do that you will need a couple of account types. You can structure your accounts into a chart at any time (and, in fact, you can structure them into several additional charts at the same time as you’ll see in the chapter Configuring Accounts from A to Z), so you don’t need to be concerned unduly about structure.

Account Types

Create account types using Financial Management ‣ Configuration ‣ Financial Accounting ‣ Financial Accounts ‣ Account Types and then clicking the New button. You’ll need the following four types, the first of which is shown in figure New Account Type.

Defining Account Types

Acc. Type Name	Code	Sequence	Sign on Reports	Deferral Method	Partner Account
View	view	5	Positive	None	unchecked
Income	income	5	Positive	Unreconciled	unchecked
Expense	expense	5	Positive	Unreconciled	unchecked
Cash	cash	5	Positive	Balance	unchecked

New Account Type

Accounts

Create accounts using Financial Management ‣ Configuration ‣ Financial Accounting ‣ Financial Accounts ‣ List of Accounts and then clicking the New button.

You need accounts to handle the purchase and sales orders that haven’t yet been paid, two more for the receipt and shipping of goods, and one for the payment and receipt of funds. And one ‘organizing’ account that’s just a view of the other five. So you’ll need the following six accounts, one of which is shown in New Account.

Defining Accounts

Name	Code	Internal Type	Parent	Account Type	Reconcile
Minimal Chart	0	View		View	unchecked
Payable	AP	Payable	0 Minimal Chart	Expense	checked
Receivable	AR	Receivable	0 Minimal Chart	Income	checked
Cash	C	Others	0 Minimal Chart	Cash	unchecked
Purchases	P	Others	0 Minimal Chart	Expense	unchecked
Sales	S	Others	0 Minimal Chart	Income	unchecked

New Account

The Account Type entry is taken from the list of types that you just created. Although it looks a bit like a text box, it doesn’t behave in quite the same way. A single Del or Backspace keystroke is all you need to delete the whole text, and when you type the name (or part of the name) you still need to associate that text with the entry by clicking the Search icon to the right of the field.

Properties

You now define some default properties so that you don’t have to think about which account is used for which transaction every time you do something. The main new properties are the four that associate accounts payable and receivable to partners, and expenses and income to product categories.

Create properties using Administration ‣ Configuration ‣ Properties ‣ Default Properties and then clicking the New button.

Defining Properties

Name	Company	Fields	Value	Parent Resource
property_account_payable	Tiny sprl	Account Payable	(account.account) AP Payable	(None)
property_account_receivable	Tiny sprl	Account Receivable	(account.account) AR Receivable	(None)
property_account_expense_categ	Tiny sprl	Expense Account	(account.account) P Purchases	(None)
property_account_income_categ	Tiny sprl	Income Account	(account.account) S Sales	(None)

Tip

Mistakes in configuring accounts and properties

It’s easy to make mistakes in configuring the accounts and their properties, but the consequences are not immediately obvious. You’ll mostly discover mistakes when trying to make a Purchase or Sale Order (see later, for example, Purchase Order), where the accounts are required fields or, if you’re diligent, when you set up Partners.

If you configure them correctly at this stage then fields will be completed automatically and you’ll never know a thing. If you don’t configure all this correctly then you won’t be able to save the order form until you have corrected the problem or until you manually set the accounts.

Since this configuration is quite tedious you’d be best finding a certified Chart of Accounts that that has already been set up to meet your needs, if you can find one.

Configuring Journals

You’ll also need to configure some journals, which are used to record the transactions from one account to another when invoices are raised and then paid. Create journals from the menu Financial Management ‣ Configuration ‣ Financial Accounting ‣ Financial Journals and then clicking the New button.

Defining Journals

Journal Name	Code	Type	View	Entry Sequence	Default Debit Account	Default Credit Account
Purchase Journal	PUJ	Purchase	Journal View	Purchase Journal	P Purchases	P Purchases
Sale Journal	SAJ	Sale	Journal View	Sale Journal	S Sales	S Sales
Bank Journal	BNK	Cash	Cash Journal View	Account Journal	C Cash	C Cash

Tip

Mistakes in configuring journals

It’s easy to make mistakes in configuring the journals, too, and the consequences are also not immediately obvious. You’ll mostly discover mistakes when creating an invoice (which happens at different points in the process depending on your configuration). In this example, validating a Purchase Order creates a draft invoice (see later, again for example, Purchase Order), where a journal is required.

As with accounts and properties, if you configure them correctly at this stage then the fields will be completed automatically and you’ll never know a thing. If you don’t configure all this correctly then there will be errors with the order form or corresponding draft invoice until you have corrected the problem or until you manually set the journal.

Configuring the Main Company

Start to configure your database by renaming the Main Company from its default of Tiny sprl to the name of your own company or (in this case) another example company. When you print standard documents such as quotations, orders and invoices you’ll find this configuration information used in the document headers and footers.

To do this, click Partners ‣ Partners and click the name of the only company there, which is Tiny sprl . This gives you a read-only view form view of the company, so make it editable by clicking the Edit button to the upper left of the form.

Tip

Editable form in the web client

When toggling from the list view to the form view of an item, you can generally click its name in the list view to show a non-editable view or the pencil icon by the left-hand edge of the line to open it in an editable view. You can toggle between editable and non-editable once you’re in form view.

Change the following:

• Name : Ambitious Plumbing Enterprises ,
• Contact Name : George Turnbull .

Before you save this, look at the partner’s accounting setup by clicking the fifth tab Accounting. The fields Account Receivable and Account Payable have account values in them that were taken from the account properties you just created. You don’t have to accept those values: you can enter any suitable account you like at this stage, although Open ERP constrains the selection to ones that make accounting sense.

Back at the first tab, General change any other fields you like, such as the address and phone numbers, then Save. This changes one Contact for the Partner, which is sufficient for the example.

From the MAIN MENU, click Administration ‣ Users ‣ Company Structure ‣ Companies and edit the only entry there:

• Company Name : AmbiPlum ,
• Partner : should already show Ambitious Plumbing Enterprises ,
• Report Header : Ambitious Plumbing ,
• Report Footer 1 : Best Plumbing Services, Great Prices ,
• Report Footer 2 : Ambitious – our Registered Company Details .

Figure Changing company details shows the effect of this. You can also change various other company-wide parameters for reports and scheduling in the other tabs, and you can upload a company logo of a specific size for the reports. Click Save to store this.

Changing company details

You can leave the currency at its default setting of EUR for this example. Or you can change it in this Company and the two default Pricelists (Products ‣ Pricelists ‣ Pricelists) if you feel compelled to do that.

Note

Currency

The examples in this book are in USD and EUR. You, the reader, could use your home currency (perhaps CAD, CNY, GBP, or IDR) in their place.

Creating partner categories, partners and their contacts

You’ll now create a suppliers category and a customers category. Partner categories are useful for organizing groups of partners but have no special behavior that affects partners, so you can assign them as you like. Then you’ll define one supplier and one customer, with a contact for each.

To do this use the menu Partners ‣ Configuration ‣ Partner Categories and click New to open a new form for defining Partner Categories. Define the two categories that follow by just entering their Category Name and saving them:

• Suppliers ,
• Customers .

Then create two partners from the menu Partners ‣ Partners. Click on the New button to open a blank form and then add the following data for the first partner first:

• Name : Plumbing Component Suppliers ,
• Customer checkbox : unchecked ,
• Supplier checkbox : checked ,
• Contact Name : Jean Poolley ,
• Address Type : Default ,
• add Suppliers to the Categories field by selecting it from the Search List,
• then save the partner by clicking the Save button.

Figure New Partner Form shows the result.

New Partner Form

Note

Contact Types

If you’ve recorded several contacts for the same partner you can specify which contact is used for various documents by specifying the Address Type.

For example the delivery address can differ from the invoice address for a partner. If the Address Types are correctly assigned, then Open ERP can automatically select the appropriate address during the creation of the document – an invoice is addressed to the contact that’s been assigned the Address Type of Invoice, otherwise to the Default address.

For the second partner, proceed just as you did for the first, with the following data:

• Name : Smith and Offspring,
• Customer checkbox : checked,
• Supplier checkbox : unchecked,
• Contact Name : Stephen Smith,
• Address Type : Default.

Then add Customers in the Categories field. Save the form. To check your work you can go to the menu Partners ‣ Partners ‣ Partners by Categories and click on each category in turn to see the companies in the category.

Note

Multiple Partner Categories

If this partner was also a supplier then you’d add Suppliers to the categories as well, but there’s no need to do so in this example. You can assign a partner to multiple categories at all levels of the hierarchy.

Creating products and their categories

Unlike partner categories and their assigned partners, product categories do have an effect on the products assigned to them – and a product may belong to only one category. Select the menu Products ‣ Configuration ‣ Product Categories and click New to get an empty form for defining a product category.

Enter Radiators in the Name field. You’ll see that other fields, specifically those in the Accounting Properties section, have been automatically filled in with values of accounts and journals. These are the values that will affect products – equivalent fields in a product will take on these values if they, too, are blank when their form is saved. Click Save.

Note

Properties fields

Properties have a rather unusual behavior. They’re defined by parameters in the menus in Administration ‣ Configuration ‣ Properties, and they update fields only when a form is saved, and only when the fields are empty at the time the form is saved. You can manually override any of these properties as you need.

Properties fields are used all over the Open ERP system and particularly extensively in a multi- company environment. There, property fields in a partner form can be populated with different values depending on the user’s company.

For example the payment conditions for a partner could differ depending on the company from which it’s addressed.

Note

UOM

UOM is an abbreviation for Unit of Measure. Open ERP manages multiple units of measure for each product: you can buy in tons and sell in kgs, for example. The conversion between each category is made automatically (so long as you have set up the conversion rate in the product form first).

Tip

Managing double units of measure

The whole management of stock can be carried out with double units of measure (UOM and UOS – for Unit of Sale). For example an agro-food company can stock and sell ham by piece but buy and value it by weight. There’s no direct relationship between these two units so a weighing operation has to be done.

This functionality is crucial in the agro-food industry, and can be equally important in fabrication, chemicals and many other industries.

Now create a new product:

1. Go to the Products ‣ Products menu and click New.

2. Create a product – type Titanium Alloy Radiator in the Name field.

3. Click the Search icon to the right of the Category field to select the Radiators category.

4. The Product Type field should stay as Stockable Product its default value. The fields Procure Method, Supply Method, Default UOM, and Purchase UOM should also stay at their default values: in fact every other field in this tab remains untouched.

   

   Product Form

5. Click on the Prices & Suppliers tab and enter 57.50 into the Cost Price field and 132.50 into the Sale Price field.

6. Click the Accounting tab, then click Save and observe that Accounting Properties here remain empty. When product transactions occur, the Income and Expense accounts that you’ve just defined in the Product Category are used by the Product unless an account is specified here, directly in the product, to override that.

7. Once the product is saved it changes to a non-editable state. If you had entered data incorrectly or left a required field blank, the form would have stayed editable and you’d need to click from tab to tab to find a field colored red, with an error message below it, that would have to be correctly filled in.

Stock locations

Click Stock Management ‣ Stock Locations Structure to see the hierarchy of stock locations. These locations have been defined by the minimal default data loaded when the database was created. You’ll use this default structure in this example.

Open ERP has three predefined top-level location types , Physical Locations and Partner Locations that act as their names suggest, and Virtual Locations that are used by Open ERP for its own purposes.

1. From the Main Menu click on Stock Management ‣ Configuration ‣ Locations to reach a list view of the locations (not the tree view).
2. Click on the name of a location, such as Physical Locations/Tiny SPRL to open a descriptive form view. Each location has a Location type and a Parent Location that defines the hierarchical structure. An Inventory Account can also be assigned to a location. While you’re here you should change the location’s name to Ambitious Plumbing Enterprises , since it was named before you changed the company name.
3. From the Main Menu click Stock Management ‣ Configuration ‣ Warehouses to view a list of warehouses. There’s only the one at the moment, which should also be renamed from Tiny SPRL to Ambitious Plumbing Enterprises .

Tip

Valuation of stock

If you want real-time stock valuation that tracks stock movements you must assign an account to each stock location. As product items are added to and taken from each location Open ERP generates an account entry for that location defined by the configuration of the product being moved – and a stock valuation based (in the current versions of Open ERP) on either Cost Price or Average Price.

For example, if you assign an account to the Supplier location you’ll be able see the value of stock that you’ve taken from the supplier. Its contents can be valued in your accounts and it can manage inventory on consignment.

A Warehouse contains an input location, a stock location and an output location for sold products. You can associate a warehouse with a partner to give the warehouse an address. That doesn’t have to be your own company (although it can be): you can easily specify another partner who may be holding stock on your behalf.

Note

Location Structure

Each warehouse is composed of three locations Location Input, Location Output, and Location Stock. Your available stock is given by the contents of the Location Stock and its child locations.

So the Location Input can be placed as a child of the Location Stock, which means that when Location Stock is interrogated for product quantities, it also takes account of the contents of the Location Input. Location Input could be used as a goods-in QC location. The Location Output must never be placed as a child of Location Stock, since items in Location Output, which can be considered to be packed ready for customer shipment, should not be thought of as available for sale elsewhere.

In the default configuration, Open ERP uses the same Stock location for both Input and Output because it is easier to understand when you’re starting out.

Setting up a chart of accounts

You can set up a chart of accounts during the creation of a database, but for this exercise you’ll start with the minimal chart that you created (just a handful of required accounts without hierarchy, tax or subtotals).

A number of account charts have been predefined for Open ERP, some of which meet the needs of national authorities (the number of those created for Open ERP is growing as various contributors create and freely publish them). You can take one of those without changing it if it’s suitable, or you can take anything as your starting point and design a complete chart of accounts to meet your exact needs, including accounts for inventory, asset depreciation, equity and taxation.

You can also run multiple charts of accounts in parallel – so you can put all of your transaction accounts into several charts, with different arrangements for taxation and depreciation, aggregated differently for various needs.

Before you can use any chart of accounts for anything you need to specify a Fiscal Year. This defines the different time periods available for accounting transactions. An initial Fiscal Year was created during the database setup so you don’t need to do any more on this. You can also create Fiscal year manually from Financial Management ‣ Configuration ‣ Financial Accounting ‣ Periods ‣ Fiscal Years

Click Financial Management ‣ Charts ‣ Charts of Accounts and then click Open Charts to open a new Account charts form where you define exactly what you want to see. Click Open Charts to accept the defaults and see a hierarchical structure of the accounts.

Make a backup of the database

If you know the super-administrator password, make a backup of your database using the procedure described at the very end of Installation and Initial Setup. Then restore it to a new database: testing .

This operation enables you to test the new configuration on testing so that you can be sure everything works as designed. Then if the tests are successful you can make a new database from openerp_ch03 , perhaps called liveor production, for your real work.

From here on, connect to this new testing database logged in as admin if you can. If you have to make corrections, do that on openerp_ch03 and copy it to a new testing database to continue checking it.

Or you can just continue working with the openerp_ch03 database to get through this chapter. You can recreate openerp_ch03 quite quickly if something goes wrong and you can’t recover from it but, again, you’d need to know your super-administrator password for that.