Implement Compiere ERP Chart of Accounts

Compiere ERP needs a set of accounts for system operations, even though some may never be posted to (example: if you don’t have foreign currency transactions, accounts like realized gains will never be used). When creating a client, Compiere ERP needs to create a set of default accounts. 

AccountingXX.xls / AccountingXX.csv 

The file AccountingXX.xls is the basis for importing and maintaining your chart of accounts. The XX in the file name represent the country code – so the file AccountingUS.xls is a good basis for a US style chart of accounts. 

Note that the file name is just a suggestion. An early version of the structure was the NaturalAccount.xls/csv file. The AccountingXX.xls/csv file structure is a super set. 

The file is required with unmodified structure when you initially create a new Client (Initial Client Setup). In this phase, only the mandatory accounts are imported/created (indicated by having a value in the Account_Default – column H). 

After the initial setup, you use it for importing your account hierarchy or reports. For that you can change the import format to adjust for your needs. 

If you modify the file, you must export your .xls file to .csv (comma separated values) format as Compiere ERP only reads the .csv file. The resulting file should be in the “data/import” sub-directory of %COMPIERE_HOME%. 

Terminology 

• Account or Combination or Valid Combination = value of accounting segment combination; the segments are defined per accounting schema e.g.: 01-1800 – HQ-Accounts Payables (here Company and Natural Account) 
• Natural Account = The account segment – an element in your account structure; this is what you import in the AccountingXX file e.g.: 1800 – Accounts Payables 
• Chart Of Accounts (CoA) = List of Natural Accounts – this is what you provide with the Accounting*.xls/csv file 

Columns 

In the following, the spreadsheet columns are explained in detail. 

Note: Column names are for documentation purposes only and can be modified / translated. 

A – Account_Value The mandatory key for your natural account – completely user defined – numeric or alpha-numeric up to 20 characters. Please note that every line HAS to be unique! 

B – Account_Name The mandatory name of the natural account – completely user defined – up to 60 characters. 

C – Account_Description The optional description of the natural account – completely user defined – up to 255 characters. 

D – Account_Type The mandatory Account Type (e.g. Asset – Liability – Owner’s Equity – Expense – Revenue – Memo). Please note that the first letter has to be A, L, O, E, R or M. So, if you translate it, the first letter MUST be one of the above. Example: “L – Verbindlichkeiten” as Verbindlichkeiten is the German translation of Liabilities. 

E – Account_Sign The optional Account Sign Flag (currently not implemented – planned to be used in financial reports). The options are Natural (default), Debit, Credit. Please note that the first letter has to be N, D, C or Empty. So, if you translate it, the first letter MUST be one of the above. Example: “D – Soll” as Soll is the German translation of Debit. 

F – Account_Document The optional Account Document Flag (i.e. this account can only be posted by the sub-ledger – no manual journal entry – this is recommended for easier reconciliation of accounts). Please note that the first letter has to be Y, N or Empty. So, if you translate it, the first letter MUST be one of the above. Example: “Y – Ja” as Ja is the German translation of Yes. 

G – Account_Summary The mandatory Summary Account Flag (i.e. you cannot post to summary accounts, they are used for reporting only). Please note that the first letter has to be Y, N or Empty. So, if you translate it, the first letter MUST be one of the above. Example: “Y – Ja” as Ja is the German translation of Yes. 

H – Account_Default The optional default Account Assignment; This is the column name of the default assignment for the account – this is the natural account for this account combination for default accounts. As this is an actual column name DO NOT MODIFY the content. You need to have a separate line for all default accounts and you need to have all default accounts listed. Example: B_INTRANSIT_ACCT indicates that this account is the default bank In-Transit account. You may not use the feature or want to have a different In-Transit account for every bank – you still need one (and only one) line in the file with this value. 

I – Account_Parent The optional parent account value (column A). It needs to be an exact match. It is used to build the account hierarchy when you import your chart of accounts. Note that this column is ignored during the Initial Client Setup. 

J/K – Balance_Sheet, Balance_Sheet_Name Optional Balance Sheet Line Value/Key and Name. Used for creating Financial Reporting Line Sets. The import format is pre-defined. 

L/M – US 1120 Balance_Sheet, US 1120 Balance_Sheet_Name Optional secondary Balance Sheet Line Value/Key and Name. Used for creating Financial Reporting Line Sets. The import format is pre-defined. In this case, it is based on Form 1120 of the US tax code, which is different than the standard US Balance Sheet structure. 

N/O – Profit & Loss, Profit & Loss_Name Optional Profit & Loss Statement Line Value/Key and Name. Used for creating Financial Reporting Line Sets. The import format is pre-defined. 

P/Q – US 1120 Income Statement, US 1120 Income Statement_Name Optional secondary Profit & Loss Statement Sheet Line Value/Key and Name. Used for creating Financial Reporting Line Sets. The import format is pre-defined. In this case, it is based on Form 1120 of the US tax code, which is different than the standard US Profit & Loss Statement structure. 

R/S – Cash Flow, Cash Flow_Name Optional Cash Flow Statement Line Value/Key and Name. Used for creating Financial Reporting Line Sets. The import format is pre-defined. 

Creating your own Chart of Accounts 

• Compiere needs a minimum set of accounts. These accounts are identified by the internal column name in the “Default Account” column – example: B_ASSET_ACCT 
• You need to have a different account for each of the default accounts. For example, if you don’t want/need to differentiate between currency gain and currency loss, Compiere ERP still requires two accounts. You can consolidate the accounts, e.g. via GL Journal entries. 
• You can change, add or delete the lines as long as you have one line per default account. 

If you don’t like the example account structure (CoA), here are the steps: 

• Delete all lines with the exception of the ones, which have Default Accounts.
• Change the lines (value/key, name, description) to fit your Chart of Accounts 
• Add the other accounts in your Chart of Accounts. 

Contact TenthPlanet, For your Compiere ERP Upgrade and Support needs!