Modules:IDL Development User Manual

Contents 1 Preface 1.1 About this user manual 1.2 Core features 2 Getting started 2.1 Installing Openbravo ERP 2.2 Installing the Initial Data Load module 2.3 Create Client Entity and Organization 2.4 Change to the role used to execute the import process 3 Defining data be imported 3.1 Collecting data 3.2 Defining default values 4 Executing the load process 4.1 Execution Order 4.2 Previous data prior to import 4.2.1 Common previous data 4.2.2 Previous data and steps for each entity process available 4.2.3 Products 4.2.4 Price Lists 4.2.5 Bank Accounts 4.2.6 Business Partners 4.2.7 Open Receivables and Open Payables 4.2.8 Assets 4.2.9 Journal Entries 4.2.10 Standard Cost 4.2.11 On hand quantity / Stock 4.3 Validating input data quality and Importing data 4.4 Verifying process results 5 Final steps 5.1 Verifying imported data 5.1.1 Bank Accounts 5.1.2 Open Receivables and Open Payables

Preface

About this user manual

This Initial Data Load user manual explains how to collect data from a legacy system or other sources, and import this data into Openbravo ERP using the load process defined for the main entities used in Openbravo ERP. These load process are intended to build from a fresh Openbravo ERP installation, a fully functional information system ready to start with all the operation activities and ready to execute all the available reports based on the data imported.

Core features

The Initial Data Load functionality currently covers the following load process.

• Products
• Price Lists
• Bank Accounts
• Business Partners
• Open Receivables
• Open Payables
• Assets
• Journal Entries
• Standard Cost
• On hand quantity / Stock

Getting started

Installing Openbravo ERP

The first step is to Install Openbravo ERP. There are different approaches to install Openbravo ERP that depend on the infrastructure you are using to install Openbravo ERP and the requirements needed.

The Openbravo ERP Installation guide explains all the requirements and steps to follow to sucessfully install and configure Openbravo ERP in your environment.

Installing the Initial Data Load module

After installing Openbravo ERP, the next step is to install the 'Initial Data Load Module'. Access Openbravo ERP through a web browser and as System Administrator go to 'General Setup -> Application -> Module Management'. In this window go to the 'Add Modules' tab and select to install the 'Initial Data Load' module. Perform all the steps needed to finish the installation. After the installation is done you will have access to all the load processes available.

A detailed guide how to install a new module can be found in the Install Module video.

Create Client Entity and Organization

The next step after installing the module is to create the initial client-entity and an organization. Each client-entity is a company managed by the application and each organization is a business unit within an entity. If you are going to import entities that require accounting or you will use the accounting functionality of Openbravo ERP be sure to create the Organization with accounting support.

The entities that require accounting are:

• Bank Accounts
• Open Receivables
• Open Payables
• Assets
• Journal Entries
• Stockholder Equity

All the details to create the initial client-entity and an organization are explained in the General Setup document.

Change to the role used to execute the import process

After the client-entity and the organization has been created you must change to the role corresponding to the client-entity just created. This role will be used to import all entities.

Defining data be imported

Collecting data

All data to be imported must be collected from the legacy management system or other sources and stored to a flat text file with CSV format. Most popular tools like [Excel] or [OpenOffice.org Spread Sheets] have an option to export data to this format.

Each entity to be imported must be defined in one CSV file and each CSV file defined for each entity requires to define a list of fields. The first line of all CSV data files must contain the definition of the fields required for the entity the CSV data file refers to. Each next line contains the field values for one record. Field values must be separated with a comma. And the format for each field is the following:

• Text values can be delimited with quotes.
• Numeric values must be formatted with a decimal point.
• Date values must be formatted with the following template: "yyyy-MM-dd".
• Date Time values must be formatted with the following template: "yyyy-MM-dd hh:mm:ss".

This is an example of a CSV created to import Products:

"Organization","Search key","Name","Description","UPC/EAN ","Product category ","UOM","Product type ","Production","BillofMaterial","Discontinued","CostType","AttributeSet","AttributeSetValue","Stocked","Purchase","Sale","Tax category ","Standard cost ","Price - Sales ","Price - Purchase "
"","rma","Raw material A","Raw material for purchase",3344567,"Raw material","Bg","Item","False","False","FALSE","Average","Serial Number",,"True","True","False","IVA 4%",,,2.5
"","rmb","Raw material B","Raw material for purchase",3344534,"Raw material","Bg","Item","False","False","FALSE","Average","Serial Number",,"True","True","False","IVA 4%",,,3.5
"","rmc","Raw material C","Raw material for purchase",3344523,"Raw material","Bg","Item","False","False","FALSE","Average","Serial Number",,"True","True","False","IVA 4%",,,4.5
"","fga","Final good A","Final goods for selling",3344567,"Final goods","Kg","Item","False","False","FALSE","Average","Lots",,"True","False","True","IVA 16%",,15.5,
"","fgb","Final good B","Final goods for selling",3344567,"Final goods","Kg","Item","False","False","FALSE","Average","Lots",,"True","False","True","IVA 16%",,15.5,
"","fgc","Final good C","Final goods for selling",3344567,"Final goods","Kg","Item","False","False","FALSE","Average","Lots",,"True","False","True","IVA 16%",,15.5,
"","sva","Service A","Consultant services",3344567,"Services","HR","Service","False","False","FALSE","","",,"False","False","True","IVA 16%",,15.5,
"","svb","Service B","Consultant services",3344567,"Services","HR","Service","False","False","FALSE","","",,"False","False","True","IVA 16%",,15.5,

The definition of the fields required and a sample CSV file for each load process and a sample CSV file are listed in the following links:

• Products entity definition. Xidl/Products.csv sample file.
• Price Lists entity definition. Xidl/Price_Lists.csv sample file.
• Bank Accounts entity definition. Xidl/Bank_Accounts.csv sample file.
• Business Partners entity definition. Xidl/Business_Partners.csv sample file.
• Open Receivables entity definition. Xidl/Open_Receivables.csv sample file.
• Open payables entity definition. Xidl/Open_Payables.csv sample file.
• Assets entity definition. Xidl/Assets.csv sample file.
• Journal Entries entity definition. Xidl/Journal_Entries.csv sample file.
• Standard Cost entity definition. Xidl/Standard_Cost.csv sample file.
• On hand quantity / Stock entity definition. Xidl/On_hand_quantity.csv sample file.

Defining default values

Depending on the data needed to import there can be values in every Openbravo ERP entity that are the same for all the records that are going to be imported. In this case, to avoid to duplicate the same value for a field in the CSV data file, there is a defaulting mechanism that allows to define default values for all records.

This window can be accessed going to 'Master Data Management -> Initial Data Load -> Setup -> Entities'. In this window, in the 'Entities' tab first select the entity, then go to the 'Fields' tab and define the default values for the required fields.

If you want to define the same default value for a field across all entities, select in the 'Entities' tab the value '*', then go to the 'Fields' tab and define the default value selected. This default value will be used in all entities but if there is defined a default value for the same field in a concrete entity, this last default value will be used.

For example in the CSV data file used to import Products there exists a mandatory field called 'Tax Category'. If all products you are about to import belong to the same Tax Category, for example 'VAT 16%' then it is not necessary to set this value for all rows in the Products CSV data file. Just go to the Entities window, select the 'Products' entity in the 'Entities' tab, select 'Tax Category' in the 'Fields' tab and write 'VAT 16%' in the 'Default Value' field.

Executing the load process

Execution Order

The available process define entities that have dependencies between them, for this reason, if you are going to import data for more than one entity, it is required to follow one order that entities that have other entities that depend on it, are imported before.

The recommended order to follow to import the available entities is:

• Products
• Price Lists
• Bank Accounts
• Business Partners
• Open Receivables
• Open Payables
• Assets
• Journal Entries
• Standard Cost
• On hand quantity / Stock

Previous data prior to import

Depending on the entity data to be imported, there is required to perform some preparation steps in order to import successfully the data. These preparation steps usually consist in the creation some basic entities and execution of processes that will be used by the process that actually imports the data entities into the Openbravo ERP system. In order to be able to import the sample csv provided in this section please follow the following step:

Common previous data

These are the common steps previous to import the sample csv files

• Initial client setup
  • Login as Openbravo/openbravo
  • Click on icon "user info" and select the role "System Administrator"
  • Go General Setup -> Client-> Initial client setup
  • Fill the client field : IDL
  • Fill the Client Username: IDLAdmin
  • Fill the currency: EUR
  • Unmark the flag Include accounting
  • Press OK button

• Initial organization setup
  • Login as IDLAdmin/IDLAdmin
  • Go to General setup->Enterprise->Initial organization setup
  • Fill the fields:
  • Organization: Pamplona
  • Organization Username: SampleClientUser
  • Organization type: Legal with accounting
  • Parent Organization: *
  • Click on the location icon and create a location selecting Spain as country
  • Mark the flag Include accounting
  • Select the attached COA. You might first need to install a module that contains a COA. For our example: Chart of accounts: PGC 2007 PYMEs.
  • Currency: EUR
  • Account dimensions: Business partner, product, project

• Applying IDL data set
  • Login as IDLAdmin/IDLAdmin
  • Go to General Setup || Enterprise || Enterprise module management || Enterprise module management
  • Select Organization (*)
  • Mark the reference data Initial Data Load
  • Press OK

• Create fiscal calendar periods
  • Login as IDLAdmin/IDLAdmin
  • Go to Financial management->Accounting->Setup->Fiscal calendar->Calendar
  • Click new and fill the name: Sample fiscal calendar. Save
  • Move to Year and click new. Write the current year (i.e 2009). Save
  • Press the button Create Periods and click OK

• Edit organization
  • Login as user with Sample client admin role
  • Go to General Setup->Enterprise->Organization
  • Select organization Pamplona and:
  • Mark the flag Allow control period and select the calendar Sample fiscal calendar. Save
  • Press the button Set as ready.Click OK

• Open/Close period control
  • Go to Financial management->Accounting->Transactions->Open/Close period control
  • Click new and select:
  • Organization: Pamplona
  • Calendar: Sample client fiscal calendar
  • Until period No.: Dec-current year
  • Period action: Open period. Save
  • Press Open/Close all and click OK

Previous data and steps for each entity process available

Products

• Set up the taxes schema
  • Login as Openbravo/openbravo. System administrator role
  • Go to General Setup || Application || Module Management || Module Management || Add modules
  • Install Taxes: Configuration for Spain. Rebuild the system
  • Login as IDLAdmin/IDLAdmin
  • Go to General Setup || Enterprise || Enterprise module management || Enterprise module management
  • Select the Organization (*) and mark the module "Taxes: configuration for Spain". Press OK

• Set up the attributes
  • Login as IDLAdmin/IDLAdmin
  • Go to Master Data Management || Product Setup || Attribute Set || Attribute Set
  • Click New, fill name=Serial Number
  • Mark Serial No. Save
  • Click New again, fill name=Lots
  • Mark the Lot. Save

• Entity definition: Xidl/Products.csv sample file to be imported

All the details to create Products are explained in the Setting up products document.

Price Lists

No preparation steps are required. If needed all the details to create Price Lists are explained in the Pricing document.

• Entity definition: Xidl/Price_Lists.csv sample file.

Bank Accounts

• Create a G/L Item
  • Login as IDLAdmin/IDLAdmin
  • Go to Financial Management || Accounting || Setup || G/L Item || G/L Item
  • Click New and fill name=Initial bank
  • Move to Accounting tab
  • Create a new record
  • Select an account
  • If the account combination does not exist first find the account then select the organization and press Save

• Entity definition. Xidl/Bank_Accounts.csv sample file.

All details to perform these preparation steps required to create Bank Accounts are explained in the Setting up accounting document.

Business Partners

• Create payment terms
  • Login as IDLAdmin/IDLAdmin
  • Go to Master Data Management || Business Partner Setup || Payment Term || Header
  • Click New and fill: Search key=90D; Name= 90 days; Offset Month Due=3. Save
  • Click New and fill: Search key=30D/5; Name= 30 days/5; Offset Month Due=1; Mark fix due date and fill Maturity day 1=5. Save

• Create invoice schedule
  • Login as IDLAdmin/IDLAdmin
  • Go to Master Data Management || Business Partner Setup || Invoice Schedule || Invoice Schedule
  • Click New and fill: Name=Monthly the first; Invoice frequency=Monthly; Day of the month=1; Invoice Cut-Off day=31

• Entity definition. Xidl/Business_Partners.csv sample file.

If needed, all the details to create Business partners are explained in the Business partners document.

Open Receivables and Open Payables

• Create a G/L Item for Open payables
  • Login as IDLAdmin/IDLAdmin
  • Go to Financial Management || Accounting || Setup || G/L Item || G/L Item
  • Click New and fill name=Payables
  • Move to Accounting tab
  • Create a new record
  • Select an account
  • If the account combination does not exist first find the account then select the organization and press Save

• Entity definition. Xidl/Open_Payables.csv sample file

• Create a G/L Item for Open receivables
  • Login as IDLAdmin/IDLAdmin
  • Go to Financial Management || Accounting || Setup || G/L Item || G/L Item
  • Click New and fill name=Receivables
  • Move to Accounting tab
  • Create a new record
  • Select an account
  • If the account combination does not exist first find the account then select the organization and press Save

• Entity definition. Xidl/Open_Receivables.csv sample file

If needed, all the details to create Open Receivables or Open Payables are explained in the Receivables and Payables document.

Assets

No preparation steps are required. If needed all the details to create Assets are explained in the Assets document.

• Entity definition: Xidl/Assets.csv sample file.

Journal Entries

No preparation steps are required.

• Entity definition: Xidl/Journal_Entries.csv sample file.

Standard Cost

No preparation steps are required. If needed all the details to manage Costings are explained in the Setting up products document

• Entity definition. Xidl/Standard_Cost.csv sample file.

On hand quantity / Stock

No preparation steps are required. If needed all the details for Warehouse Management are explained in the Warehouse Management document.

• Entity definition. Xidl/On_hand_quantity.csv sample file.

Validating input data quality and Importing data

After the data has been collected and default values are defined, the load process can be executed. The process window can be accessed going to 'Master Data Management -> Initial Data Load -> Process -> Import'. Here you have to select the entity data process to import and the CSV file with the the data corresponding with the selected entity.

There are two process available.

• Validate:

This process validates the correctness of the data contained in the CSV file, it reads all rows in the CSV data file and verifies that the data values are correct. It checks data types, mandatory fields and that fields that have a relationship with other tables, this relationship exists, for example fields like 'Country', 'Region' and 'Tax Category'.

Before executing the 'Process' process is recommended to run first the 'Validate' process, and when all errors that appear running the 'Validate' process has been fixed execute the 'Process' process that actually imports the entities.

If any of the rows is rejected the process continues after all rows have been read.

• Process:

This process first reads all rows in the CSV data file and verifies that the data values are correct, the same task done by the 'Validate' process and after all the data values of a row have been verified, the data is loaded into the Openbravo ERP information system.

If any of the rows is rejected the process stops and marks the rest of rows as 'not processed'. This way if a row is rejected you can edit the original CSV file, find the row that has been rejected, remove the previous rows because all of them have been successfully loaded, fix the error, save the CSV file, and start with the load process again.

Verifying process results

At the end of the execution of the process it will be displayed the total of rows read, and the number of rows processed, not processed and rejected.

• Total of rows read. This is the total of rows read from the CSV file. It is the sum of the rows processed, not processed and rejected.

• Number of rows validated / Number of rows processed. Depending on the process executed this is the number of rows successfully validated or processed.

• Number of rows not processed. In the 'Process' process, after a row has been rejected the process stops and the rest of rows in the CSV file are marked as not processed.

• Number of rows rejected. This is the number of rows that the process 'Validate' or 'Process' have find an error and cannot be processed.

The result of the execution of any of the two process, 'Validate' or 'Process' can be 'Success' or 'Error'. The result is 'Success' if all rows have been processed successfully and is 'Fail' if any of the rows has been rejected, because it does not pass any of the validation checks in the 'Validate' process or because it cannot be imported into the system in the 'Process' process.

For all rows that has been rejected it will be displayed in the first log panel the row with the values rejected and the cause of the rejection, and if the cause of the rejection has been a java exception, in the second log panel, it will be displayed the full java stack traces that were the cause of the row rejection.

Final steps

Verifying imported data

After all required import process has been executed the Openbravo ERP system is ready to start transactions and execute reports based on imported data, but it is important to perform some tests to verify that all the imported data required has been correctly created and the state of the full Openbravo ERP system is consistent.

Depending on the entity process that has been executed these are the recommended actions to validate the data imported:

Bank Accounts

• Execute the post process for all transactions. This process has to be executed only in the case that the Accounting server has not been started. If the Accounting server is started, it will process automatically all Bank Accounts transactions created during the import process.
• Verify the balance of all Bank Accounts loaded.

Open Receivables and Open Payables

• Execute the post process for all transactions.
• Verify the situation of payables in the Payment Report (Financial Management > Receivables & Payments > Analysis Tools > Payment Report)

Category: User Documentation ERP