Log in / create account
Openbravo.com
Issues
Forge
University
View source | View content page | Page history | Printable version   

Projects:Initial Data Load/User Manual 1.1.0/zh

Contents

简介

关于本用户手册

This manual explains how to prepare and format legacy data from existing sources and then import it into Openbravo ERP using the load process offered by this module. As the name of the module suggests, this process is designed for the initial load of data which means the import should be done into a fresh client since the data does not get updated but inserted only!

实体可以被导入

该模块目前支持以下导入的实体：


入门

安装 Openbravo ERP

第一步是安装Openbravo的ERP系统。 根据安装它的基础软硬件环境和不同的具体要求不同，有不同的安装的方式。

在 Openbravo ERP Installation 介绍了把Openbravo ERP 安装到你的工作环境所需的一切步骤。

安装初始数据加载模块

After Openbravo ERP is up and running, the Initial Data Load module should be installed. Log into the system using the System Administrator role and go to General Setup -> Application -> Module Management. Click the Add Modules tab and find the Initial Data Load module within the list of all modules available in the central repository.

Note: Keep in mind that the Initial Data Load module is a commercial module which means your Openbravo ERP instance must be activated before the module can be installed.

Note for Spanish speakers: If you want to use the Initial Data Load module in Spanish, find within the list of all modules available in the central repository the Initial Data Load Spanish (Spain) module and install it.

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

Initial Data Load module

Change to the role used to execute the import process

In order to be able to configure the necessary items and finally import the data into the new client, use the Client Admin role that will be created upon Initial Client Setup.

Collecting Data

All data to be imported must be collected from the legacy system or other sources and stored into flat text CSV format file(s). Most popular tools like Microsoft Excel or OpenOffice Spreadsheet have an option to export data into this format.

Template To Use

To make things easier for you, we have prepared a template (IDLImportTemplate.xls) in Microsoft Excel format that contains sheets for each of the entities that can be imported in the order recommended. This template is only an intermediary step that simplifies preparation of resulting CSV files that can then be imported into Openbravo ERP. Each sheet of this template must be exported into its own CSV file later.

Here are a few rules and recommendations for creating the template/CSV files:

The definition of the fields/columns required for each entity are listed here:

Exporting to CSV

Before the collected data can be imported into Openbravo ERP, it first needs to be exported into CSV files, one for each entity, hence:

Use the Microsoft Excel or OpenOffice Spreadsheet Save As functionality to save each sheet of the template given above into a separate CSV file and make sure you use the comma field delimiter and double quotes as text delimiter:

Save As CSV options in Open Office

Collecting and transforming data

Legacy data stored in previous information systems can have very different formats to the required by IDL process and also can be stored in different data sources, like relational databases, indexed files like DBF, Excel spreedsheets. Data extraction from this data sources and transformation of this data to the format required by the IDL module can be very hard and take a lot of time if has to be done manually.

We suggest here to read all technical documentation of the information system where to extract the data needed by the IDL module and the data source format. Once this is done instead of extracting and transforming all these data manually an ETL tool can help with this task.

An ETL (Extract, Transform and Load) tool is specialized in extracting data from several data sources, transforming all these data to fit the operational needs, which includes the assurance of quality levels of data extracted and load to the end target, in our case generate CSV data files that comply with the requirements of the IDL process.

Most of these ETL tools have a graphical designer that helps the user to design, extract, transform and load steps easily and with a high level or productivity.

Here you have a list of ETL tools available that fit with the requirements previously exposed. And if you want to look for another tool have a look to the Tools, Open-source ETL frameworks, and Proprietary ETL frameworks sections of the ETL document.

Required Configuration Prior to Import

Generic Configurations

There are some generic as well as entity specific items that need to be configured before they can be imported. Here are some generic ones that will enable you to import the sample data given here into a fresh client. Note that the import of your custom data into your custom client is analogous (but not identical) to this process!

Creating a New Client and the Organization(s)

As mentioned previously, the Initial Data Load module should only be used to import data into a fresh and empty client/organization. Hence, create the necessary enterprise structure that will then hold the data. If you are going to import entities that require accounting or you will use the accounting functionality of Openbravo ERP be sure to create legal Organization(s) with accounting. Details on how to model the structure of the enterprise within Openbravo ERP and create the client and the organizations accordingly can be found here. When creating the client, make sure you select the Initial Data Load reference data along with other you might need to be initialized within the new client, for example:
Initial Client Setup
Then, when creating the organization using the Initial organization setup process, remember the name that you give the organization since it will be very important later when importing legacy data into a specific organization. For example:
Initial organization setup

Create fiscal calendar periods

Before items such as bank accounts, their initial balances and open receivables and payables can be imported, the fiscal periods MUST exist.

In case of the template sample data, this is the required configuration:

  1. Login as Openbravo user and switch to TestIdlAdmin role
  2. Go to Financial management || Accounting || Setup || Fiscal calendar || Calendar
  3. Select the fiscal calendar that was automatically created upon Initial Client Setup, in this case TestIdlClient Calendar
  4. Switch to the [Year] tab and find that the current year has already been created.
  5. Press the button Create Periods and click OK, this will create 12 periods within this year corresponding to months

Configure the Organization

Before any organization specific items (such as bank accounts, pending receivables, etc) can be imported into the system, the Organization that they will belong to MUST exist and be properly configured and marked as ready.

In case of the template sample data, this is the required configuration:

  1. Login as Openbravo user and switch to TestIdlAdmin role
  2. Go to General Setup || Enterprise || Organization and select the organization TestIdlOrg
  3. Mark the flag Allow period control and select the calendar TestIdlClient Calendar fiscal calendar
  4. Save
  5. Press Set as ready button and click OK

Open the Current Fiscal Period

Before items such as bank accounts, their initial balances and open receivables and payables can be imported and posted to the general ledger, the fiscal periods MUST exist and be opened.

In case of the template sample data, this is the required configuration:

  1. Go to Financial management || Accounting || Transactions || Open/Close period control
  2. Click new and select:
  3. Organization: TestIdlOrg
  4. Calendar: TestIdlClient Calendar
  5. Until period No.: Dec-current year - this will open all periods for the current year
  6. Period action: Open period
  7. Save
  8. Press Open/Close all and click OK

Defining default values

You might have noticed a few facts/rules by now:

Due to the facts listed, default values can be defined that will be used when a field of a record inside the CSV file does not contain a value (by listing an empty string ,"", or just null, e.g. ,,).

To defined these default values use the Master Data Management -> Initial Data Load -> Setup -> Entity Default Values window. Entities should already be predefined here as part of reference data that belongs to the Initial Data Load module:

Entity Default Values window

For each of the entities, you will also find common mandatory field placeholders for the default values:

Product's Entity Predefined Null Default Values

An Example

Universal Default Values

There is also the possibility to define the same default value for a field across all entities. To do so, follow the steps:

  1. select the 'All Entities' record inside the Entity Default Values window
  2. switch to [Fields] tab
  3. define the default value selected, this default value will then be used for all entities when a record contains a null/empty value of that field

For example, to define our previously defined organization TestIdlOrg as the default organization for transactional records (records that must belong to a specific organization and cannot belong to organization 0-shared one), do as the screenshots suggests:

Define specific organization as the default value for transactional organizations

Import

Because of the dependencies and foreign keys, the entities should be imported and configured in the following sequence:

  1. Products
  2. Price Lists
  3. Bank Accounts
  4. Business Partners
  5. Open Receivables
  6. Open Payables

Find the sample CSV files ( ZIP ) that can be used for learning purposes. Keep in mind that these sample files are only the result of saving each one of the sheets inside the IDLImportTemplate.xls template as a separate CSV file explained earlier.

Sections with details on how to import each of the entities follow.

Description of the Process

Once all prerequisite configurations are complete for a specific entity and the CSV file is prepared, use the Master Data Management || Initial Data Load || Process || Import' form:

Import form

Here you will find two processes:

In order to execute them, select the CSV file that contains the entity's import data and select the correct entity from the dropdown.

Validation

This process validates the correctness of the data contained in the CSV file, reads all rows and verifies that the data values are correct. It checks data types, mandatory fields and that references to fields in other tables are valid (such as 'Country', 'Region', 'Tax Category', etc).

If the validation is successful a message similar to this will be shown:

Successfully validating a CSV file

If the validation fails then a message like this will be shown:

Successfully validating a CSV file

Correct the CSV file according to the message shown and retry the validation until successful.

Process

Before running Process it is highly recommended that each CSV passes the validation described above.

This process tries to import all rows into their final destination inside Openbravo ERP.

In case a row is rejected the process stops and ignores the remaining rows. The steps to follow in this case is:

  1. edit the original CSV file
  2. find the row that has been rejected
  3. remove ALL previous rows because they have already been successfully loaded
  4. fix the error
  5. save the CSV file
  6. start with the load process again

If an import is successful a message similar to this one will be shown:

Successfully importing a CSV file

Interpretation of Messages

At the end of execution of the process a message is shown with the following information:

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 a validation check or because it cannot be imported into the system as part of the Process process.

For rows that have been rejected a message will be displayed in the first log panel with the cause of the rejection(s). If the cause of the rejection is a Java exception, the second log panel will display the full Java stack trace that can be used to discover the cause of the error.

Products

Configuration

The following items MUST BE CONFIGURED before the products can be imported:

  1. Attributes and Attribute Sets used inside the Products.csv. If none are used, this is not a required step.

The following items will be CREATED AUTOMATICALLY if they have not been configured previously and referenced accordingly:

  1. Product categories
  2. Tax categories (but no rates associated)
  3. UOMs
  4. Default Purchase/Sales Price List(s) as well as its versions and the single prices for each product in the CSV file (as part of this, a Default Price List Schema will also be created with no lines)

For the sample data to work, perform the following configurations:

  1. Set up the attributes
    1. Login as TestIdlAdmin/TestIdlAdmin
    2. Go to Master Data Management || Product Setup || Attribute window
    3. Create a new attribute Color
    4. Using the [Atribute Value] tab add two colors, Green and Yellow
    5. Go to Master Data Management || Product Setup || Attribute Set window
    6. Create a new set named Color
    7. Using the [Assigned Attribute] tab add the previously created Color attribute

Additional information on how to create and configure products can be found here: Setting up products.

What has been Created

After the import of Products.csv is successfully completed, the following items will have been created:

Price Lists

Configuration

The following items MUST BE CONFIGURED before price lists can be imported:

Details on how to create and configure price lists by hand are explained in the Pricing document.

What has been Created

After the import of PriceLists.csv is successfully completed, the following items will have been created:

Bank Accounts

Configuration

The following items MUST BE CONFIGURED before the bank accounts can be imported:

  1. G/L Items referenced inside the BankAccounts.csv.

The following items will be CREATED AUTOMATICALLY if they have not been configured previously and referenced accordingly:

  1. Banks
  2. one Business Partner per bank. Each bank must have a business partner associated in order to have transactions associated with, such as bank statements.

For the sample data to work, perform the following configurations:

  1. Create a G/L Item
    1. Login as TestIdlAdmin/TestIdlAdmin
    2. Go to Financial Management || Accounting || Setup || G/L Item window
    3. Create a new record
    4. Name === Initial Bank Balance
    5. Switch to [Accounting] tab
    6. Create a new record
    7. Select the account combinations
    8. If the account combination does not exist first find the account then select the organization and press Save inside the account selection popup

More information on creating accounts can be found here: Setting up accounting.

What has been Created

After the import of BankAccounts.csv is successfully completed, the following items will have been created:

Business Partners

Configuration

The following items MUST BE CONFIGURED before the business partners can be imported:

  1. Payment terms referenced as part of the Vendor or Customer terms inside BusinessPartners.csv
  2. Invoice schedules referenced as part of Customer terms inside BusinessPartners.csv
  3. Country Regions if they do not exist yet for the country of your choice inside BusinessPartners.csv
  4. Price Lists referenced as part of the Vendor or Customer terms inside BusinessPartners.csv
  5. Bank Accounts referenced as part of Customer or Vendor terms inside BusinessPartners.csv

The following items will be CREATED AUTOMATICALLY if they have not been configured previously and referenced accordingly:

  1. Business Partner Category(s)
  2. Tax Category(s) referenced as part of the Vendor or Customer terms

For the sample data to work, perform the following configurations:

  1. Payment terms
    1. Login as IDLAdmin/IDLAdmin
    2. Go to Master Data Management || Business Partner Setup || Payment Term
    3. Click New and fill: Search key === 90D; Name === 90 days; Offset Month Due === 3
    4. Click New and fill: Search key === 30D/5; Name === 30 days/5; Offset Month Due === 1; Check Fixed Due Date and set Maturity day 1 === 5
  2. Invoice schedules
    1. Login as IDLAdmin/IDLAdmin
    2. Go to Master Data Management || Business Partner Setup || Invoice Schedule
    3. Click New and fill: Name === Monthly the first; Invoice frequency === Monthly; Day of the month === 1; Invoice Cut-Off day === 31

More information on how to create and configure Business partners can be found here: Business partners.

What has been Created


How to create more than one Location, Contact and Bank Account

Open Payables

Configuration

The following items MUST BE CONFIGURED before the pending payables can be imported:

  1. G/L Item for Payables

For the sample data to work, perform the following configurations:

What has been Created

Open Receivables

The following items MUST BE CONFIGURED before the products can be imported:

  1. Products used inside the StandardCost.csv

Configuration

The following items MUST BE CONFIGURED before the pending receivables can be imported:

  1. G/L Item for Receivables

For the sample data to work, perform the following configurations:

What has been Created

For more information or Receivables and Payables see the Receivables and Payables article.

Assets

Configuration

The following items MUST BE CONFIGURED before the assets can be imported:

  1. Products used inside the Asset.csv

The following items will be CREATED AUTOMATICALLY if they have not been configured previously and referenced accordingly:

  1. Asset Category

What has been Created

After the import of Asset.csv is successfully completed, the following items will have been created:

  1. Assets (see Financial Management || Assets || Assets window)
  2. Asset Categories if there are ones referenced in the CSV file that had not existed before (see Financial Management || Assets || Assets Group || Asset Category window)
  3. Asset Amortization (see Financial Management || Assets || Amortization window)

Journal Entries

Configuration

The following items MUST BE CONFIGURED before the products can be imported:

  1. Products used inside the Journal_Entries.csv
  2. Accounting Schema used inside the Journal_Entries.csv

The following items will be CREATED AUTOMATICALLY if they have not been configured previously and referenced accordingly:

  1. Accounting Combination: Alias + Account + Product + Business Partner + Project + Campaign + Sales Region

What has been Created

  1. one G/L Journal Batch which contains all G/L Journal Lines (CSV file rows) grouped by G/L Journal Headers. All the entries are posted to the general ledger.

Standard Cost

Configuration

The following items MUST BE CONFIGURED before the products can be imported:

  1. Products used inside the StandardCost.csv

What has been Created

Additional information on how to create and configure products can be found here: Setting up products.

On hand quantity / Stock

Configuration

The following items MUST BE CONFIGURED before the products can be imported:

  1. Products used inside the OnHandQuantity.csv
  2. Costs for each Product used.

The following items will be CREATED AUTOMATICALLY if they have not been configured previously and referenced accordingly:

  1. Warehouses
  2. Locators referenced as part of the Warehouse defined

What has been Created

For more information on Warehouse management see the Warehouse Management article.

Final Verification

After all required entities have been successfully imported into the Openbravo ERP application, it should be ready to accept transactions and show reports based on the imported data. However, we recommend doing a few tests in order to double check the quality and consistency of the imported data.

These are some recommended checks based on the entity they are related to:

Bank Accounts

Open Receivables and Open Payables

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Initial_Data_Load/User_Manual_1.1.0/zh"

This page has been accessed 7,371 times. This page was last modified on 8 June 2012, at 05:28. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.