ERP 2.50:Developers Guide/How to create a dataset

ERP 2.50:Developers Guide

ERP 2.50:Developers Guide/HowTos ERP 2.50:Developers Guide/How To Create and Package a Module ERP 2.50:Developers Guide/How To Create an Industry Template ERP 2.50:Developers Guide/How to customize the Openbravo look and feel ERP 2.50:Developers Guide/How To Create a Table ERP 2.50:Developers Guide/How To Add Columns To a Table ERP 2.50:Developers Guide/How To Add a Constraint ERP 2.50:Developers Guide/How To Create a Trigger ERP 2.50:Developers Guide/How to add a field to a Window Tab ERP 2.50:Developers Guide/How to change an existing window ERP 2.50:Developers Guide/How to develop a new window ERP 2.50:Developers Guide/How to develop a callout ERP 2.50:Developers Guide/How to develop an alert ERP 2.50:Developers Guide/How to develop a stored procedure ERP 2.50:Developers Guide/How to develop a DAL background process ERP 2.50:Developers Guide/How to change an existing report ERP 2.50:Developers Guide/How to develop a report ERP 2.50:Developers Guide/How To Create a Manual Window ERP 2.50:Developers Guide/How to define users roles privileges menus ERP 2.50:Developers Guide/How to create a dataset ERP 2.50:Developers Guide/How to work with the Data Access Layer ERP 2.50:Developers Guide/How to create testcases ERP 2.50:Developers Guide/How to do a complex query using the DAL-1 ERP 2.50:Developers Guide/How to do a complex query using the DAL-2 ERP 2.50:Developers Guide/How to call a stored procedure from the DAL ERP 2.50:Developers Guide/How to create a new REST webservice ERP 2.50:Developers Guide/How to create build validations and module scripts ERP 2.50:Developers Guide/How to define an oncreatedefault ERP 2.50:Developers Guide/How to develop dbsourcemanager ERP 2.50:Developers Guide/How To Use an Extension Point ERP 2.50:Developers Guide/How To Use a dbsm from a newer version of ob ERP 2.50:Developers Guide/How To Exclude Database Physical Objects From Model ERP 2.50:Developers Guide/How To Create Bank File Import Module ERP 2.50:Developers Guide/How To Create a Client or Organization ERP 2.50:Developers Guide/How to Create Your Own Import Process with IDL

Index

Contents 1 Objective 1.1 Introduction to Dataset Concept 1.2 Reference Data 2 Data structure to define Dataset 2.1 Data Access Level 3 Exporting Module 4 Packaging a Module 5 Examples 5.1 Dataset of roles and accesses 5.2 Dataset of taxes or alerts 5.3 Dataset of regions

Objective

This how-to will focus on creating a dataset in Openbravo ERP and also to explain with some examples in detail under Examples section. Dataset will export both reference data as well as default data.

Introduction to Dataset Concept

First of all we need to understand the dataset concept, which allows to export the sets of data from different tables in one step. This is especially useful to manage and distribute the module along with reference data, for instance tax rates, regions or default data in a new table(s) added by a module.

A dataset is defined by its Dataset Tables and Dataset Columns. This detailed configuration leads to which tables to be exported, and which columns of each table are executed and exported.

Reference Data

The reference data is packaged, distributed and installed together with the program code implementation of the module.

In Openbravo ERP 2.50, the concept of reference data is generalized and any data in the instance can be exported in a module and imported when installing / applying the module.

You can find the Has reference data field / option at the time of module creation.

Data structure to define Dataset

There are mainly 3 tables referred as data structure to define datasets. They are:

1. DataSet with the following columns: Value, Name, Description, Module and DataAccessLevel

• Data sets have a name and a description to describe the content of the data set.
• The value is used to get a dataSet object from the factory provided by DAL (eg. DBSourceManager gets the AD dataSet).
• A data set is owned by a module in the same way that all Application Dictionary components.

If the export allowed column is flagged then an Export Reference Data button is displayed. 

2. DataSet_Table with the following columns: DataSet, Table, fullBusinessObjec, includeAllColumns, excludeAuditInfo and whereClause (HQL expression)

• A data set can have one or many tables from the ones registered in the AD_Table. For each of them developers can decide to include only records in that table or export the full business object using the check fullBusinessObject.
• Developers can also define for each table the columns that are included in the dataset. They can include all columns using the includeAllColumns check and then remove some of them in the column definition or only include the ones that are explicitly defined in the column definition.
• The whereClause is a HQL expression to filter the rows that are included in the DataSet. Details on this expression will be provided in the DAL project.
• Developers can exclude the audit information column like created, createdby, updated, etc. by check the excludeAuditInfo column.

Note: If IsBusinessObject field is flagged then the 'child-records' of the table are exported.
For example if the Dataset Table is defined for the C_Order table and this field is flagged then also the 
related C_OrderLines are exported. 

3. Dataset_column with the following columns with the following columns: DataSet_Table, Column, isExcluded and conditionClause(Java expression).

• For each table in a data set developers can decide what columns to include from the ones registered in the AD for that table.
• They can exclude columns using the isExcluded check if they have marked the table as Include all columns. Typically audit info will be removed from the dataset.

Data Access Level

The Data Access Level is used to define how to import / install the module at various level like System level, Client level, Organization level, etc. These access level value is available at Dataset table. Following is the detailed explanatory at each access level.

• System Only: data will be imported at module installation time at System level without any user interaction.
• Client: data will be imported at Initial Client Setup if the user chooses the module where the DataSet is included.
• Organization: data will be imported at Initial Organization Setup if the user chooses the module where the DataSet is included.
• Client/Organization: data will be imported at Initial Client Setup or Initial Organization Setup if the user chooses the module where the DataSet is included. The module can not be applied at both levels at the same time because it would lead to data redundancy. So if the module is applied to a Client it will not be available to apply in its Organizations and if the module is applied in an Organization it will not be available to apply in its Client.

Exporting Module

Before packaging we need to export the module which creates a directory in the module under Openbravo ERP root directory and the appropriate XML files for inclusion in the finished module. Modules that are not flagged as being in development are not exported so remember that you must select the In Development checkbox when you define a new module.

When the development of the module is finished, open a command window/shell and navigate to the Openbravo ERP development project execute the export.database command.

ant export.database

Packaging a Module

The last step in the process is to package the module and distribute across to the end user.

To package a module, execute the command ant package.module -Dmodule=modulename, where modulename is the Java package name of the module. So in the case of the howto module, the command would be:

ant package.module -Dmodule=org.openbravo.howto

Examples

Please find below the examples to know how to create a dataset and to export it along with the reference data.

Dataset of roles and accesses

In this section you can find the example of a dataset of roles and access. It basically covers the definition of the role in the organization and the privileges they require.

Create a Role and Assign Privileges

• Change to the admin role of your client.
• Click on General Setup and Navigate to Security > Role.
• Create a new record. Fill up the mandatory fileds that required for this record. They are:
  • Name = the name of the role in the customer organization I.E Sales Clerk, Production Manager, Forcaster etc.
  • Active = Select Option to ensure this role appears in the generated application. During development you may require the role only to appear when it is complete.
  • User Level = This controls which organizations the role has access to. There are 4 options the most common are:
    • Organization - the role only has access to organization specific data.
    • Client and Organization - the role has access to organization specific data and client shared data.
  • Manual = The controls if all existing privileges are automatically given to the role or if they are manually associated on a per need basis. Selecting this option for manual control is recommended.
• Save the record.
• Now you need to assign some privileges by clicking on "Grant Access" button.
• select the module and access type to assign the privileges to the newly created role.

Create a User and assign the user to the Role

• Now click on General Setup and navigate to Security > User.
• Create a new record. The Client field will show the name of your client by default.
• Select the Organization (This can be for access to one or all organizations in a client).
  • First Name.
  • Last Name.
  • Name (Default).
  • Select Active (Default).
  • Username (The default is a concatenation of first and last name).
  • Enter the user Password (Remember this).
• Save the record.

• Staying in the User window:
• Select the 'User Roles' tab.
• Create a new record and select a role.
• Save the record.
• Add all roles this new user will be able to have/use (one line for each role).
• Logout from the current role.

Create a new module

• Log into Openbravo ERP as a System Administrator.
• Click on Module menu from the Application Dictionary.
• From the Module Type list, select Module.
• In the Name field, type the java package name of the module(proper naming convention).
• Complete the Description and Help fields. Supply the information about chart of accounts.
• Select the Has reference data option.
• Clear the Has chart of accounts, Translation required and Is translation module options.
• Select the In development option. Remember that you cannot work on a module unless the In development option is selected.
• On the Dependencies tab, select Core.
• Save the module

Create a Dataset of roles and access

1. From the Application menu, select Application Dictionary > Dataset
2. Click New.
3. From the Module list, select the module above created.
4. Specify a search key, name and description.
5. From the Data Access Level list, select the Data access level as Organization.
6. Select the Export allowed option.
7. Select the Table Tab
8. From the Table list, select the table whose content you want to include in the module. For example, ad_role_org_access, ad_role, ad_user_roles
9. In the SQL where clause field, specify the SQL "WHERE" statement that will identify the set of rows to be exported, in DAL notation. For example, adrole.id='2EA831D59184490E9BA858E9745EF89F'
10. Select the Include All Columns option.
11. Select isBusinessObject option
12. Click Save
13. Click the Export Reference Data button to export the reference data to an .xml file that you can include in the module

Exporting and Packaging Module

After completing all the steps successfully. Run the below ant task to

export the module:
ant export.database -Dmodule="org.openbravo.rolesandaccess"

packaging the module:
ant package.module -Dmodule="org.openbravo.rolesandaccess"

On successful execution of the above task an .obx file has been generated.

How to Install - Organization Access Level Reference Data

To install the new module into the running Openbravo ERP then need handle the steps carefully. In this example we have selected the access level as Organization then the installation would be done using Enterprise Module Management find below the steps.

• Log into Openbravo ERP as System Administrator.
• Install the OBX using Module Management under General Setup and rebuild the system.
• But the reference data will not be installed.
• Change the login as Openbravo Admin
• Click on General Setup and navigate to Enterprise > Enterprise module Management.
• Select the Organization type then select the appropriate module and click Ok to install the reference data.

Dataset of taxes or alerts

In this section you can find the example on dataset of taxes or alerts. The process for creating a standard reference data module for taxes and alerts, you have set up Openbravo ERP in a particular way to meet local requirements, you can export this data and convert it to a module, so that you can share it with other users.

Registering a data module for taxes and alerts:

• Log into Openbravo ERP as a System Administrator.
• Click on Module menu from the Application Dictionary.
• From the Module Type list, select Module.
• In the Name field, type the java package name of the module(proper naming convention).
• Complete the Description and Help fields. Supply the information about chart of accounts.
• Select the Has reference data option.
• Clear the Has chart of accounts, Translation required and Is translation module options.
• Select the In development option. Remember that you cannot work on a module unless the In development option is selected.
• On the Dependencies tab, select Core.
• Save the module

Defining and exporting the dataset

1. From the Application menu, select Application Dictionary > Dataset
2. Click New.
3. From the Module list, select the module above created.
4. Specify a search key, name and description.
5. From the Data Access Level list, select the Data access level as System only
6. Select the Export allowed option.
7. Select the Table Tab
8. From the Table list, select the table whose content you want to include in the module.
9. In the SQL where clause field, specify the SQL "WHERE" statement that will identify the set of rows to be exported, in DAL notation. For example, client.id='1000001'
10. To export all columns, select the Include All Columns option. To include only the columns you specify, select the Columns tab and create a new record for each column you want to export.
11. To include the security audit columns (created, createdby, updated and updatedby) in the export, clear the Exclude Audit Info checkbox.
12. Clear the Is Business Object option.
13. Click Save
14. Click the Export Reference Data button to export the reference data to an .xml file that you can include in the module

Exporting and Packaging Module

After completing all the steps successfully. Run the below ant task to

export the module:
ant export.database -Dmodule="org.openbravo.taxesandalerts"

packaging the module:
ant package.module -Dmodule="org.openbravo.taxesandalerts"

On successful execution of the above task an .obx file has been generated.

How to Install - System/Client Access Level Reference Data

To install the new module into the running Openbravo ERP then need handle the steps carefully. In this example we have selected the access level as Organization then the installation would be done using Enterprise Module Management find below the steps.

• Log into Openbravo ERP as System Administrator.
• Install the OBX using Module Management under General Setup and rebuild the system.
• But the reference data will not be installed.
• Click on General Setup and navigate to Client > Initial Client Setup.
• Fill up all the mandatory fields and then select the appropriate module.
• Finally click Ok to install the reference data.

Dataset of regions

In this section you can export the reference data with the examples on regions. Find below the steps to create the Dataset for this module:

• Log into Openbravo ERP as a System Administrator.
• Create a new module called for the this example.
• Make sure that you have selected or flagged for the field Has Reference Data.
• Now expand the Application Dictionary menu.
• Click on Dataset menu and create a new record for the for this module.
• For example here I have given the Name as Indian States. You can give as you want to select the region.
• Fill up the Dataset form using the below mentioned values.

  • Field	value for the field
    Client	:	System
    Organization	:	*
    Active	:	make it flagged/put a tick mark
    Module	:	select the value from the drop down Indian States - 1.0.0
    Search Key	:	Indian States
    Name	:	Indian States
    Data Access Level	:	System Only

• Before assigning the tables to Dataset. Please execute the below query in sqldeveloper or postgres IDE to find the C_country_Id for "INDIA". After executing the below query the result of c_country_id would be 208 for the below query.

select * from c_country where countrycode like 'IN%';

• Navigate to Table Tab and create 2 new records for the dataset.
• One table called C_Country and another table called C_Region it look likes as given below.
  • Fill up the form by using below values for the Table Name: "C_Country"

  • Field	value for the field
    Dataset	:	Indian States
    Table	:	C_Country
    Active	:	Default it is flagged. Leave as it is
    Module	:	Indian States -1.0.0
    SQL Where Clause	:	id='208'
    Include All Columns	:	Remove the flag or tick mark (Note: Need to add the individual columns for this table using Column Tab)
    Exclude Audit Info	:	Mark it as flagged or put tick mark for this check box

• Fill up the form by using below values for the Table Name: "C_Region"

  • Field	value for the field
    Dataset	:	Indian States
    Table	:	C_Region
    Active	:	Default it is flagged. Leave as it is
    Module	:	Indian States -1.0.0
    SQL Where Clause	:	country.id='208'
    Include All Columns	:	Mark it as flagged or put tick mark for this check box
    Exclude Audit Info	:	Mark it as flagged or put tick mark for this check box

  • Select the "C_Country" table from the table grid view and navigate to Column Tab.
  • Click on create a new record button for the above table. You need to select three columns for this table.
  • Those columns are:(1)Name, (2)CountryCode, and (3)HasRegion
  • Fill up the following values in the form.

    • Field	value for the field
      Column 1:
      Dataset Table	:	C_Country
      Column	:	Name
      Active	:	It has benn flagged. Leave as it is
      Module	:	Indian States - 1.0.0
      Column 2:
      Dataset Table	:	C_Country
      Column	:	CountryCode
      Active	:	It has benn flagged. Leave as it is
      Module	:	Indian States - 1.0.0
      Column 3:
      Dataset Table	:	C_Country
      Column	:	HasRegion
      Active	:	It has benn flagged. Leave as it is
      Module	:	Indian States - 1.0.0

• Finally navigate to Dataset Tab of Indian States and Click on Export Reference Data button to export the data.

Exporting and Packaging Module

After completing all the steps successfully. Run the below ant task to

export the module:
ant export.database -Dmodule="org.openbravo.indianstates"

packaging the module:
ant package.module -Dmodule="org.openbravo.indianstates"

On successful execution of the above task an .obx file has been generated.

How to Install - System Only Access Level Reference Data

To install the new module into the running Openbravo ERP then need handle the steps carefully. In this example we have selected the access level as Organization then the installation would be done using Enterprise Module Management find below the steps.

• Log into Openbravo ERP as System Administrator.
• Install the OBX using Module Management under General Setup and rebuild the system.
• It will install along with the reference data.

---

ERP 2.50:Developers Guide/How to define users roles privileges menus | ERP 2.50:Developers Guide/How to work with the Data Access Layer