Introduction

Openbravo is a pure web-based extended ERP for SMEs (Small and Midsize Enterprises). The extended ERP solution includes basic CRM (Customer Relationship Management) and BI (Business Intelligence) and a broad range of standard functionalities such as purchasing, warehousing, projects, manufacturing and sales management, financial management and much more. Openbravo is in production in companies spanning several industries, including distribution, services and manufacturing.

Openbravo ERP can be extended to provide functionality and business process specific to certain sectors or industries using verticals. For example, a vertical for real estate companies can provide legal forms and procedures that are specific to the real estate sector.

Openbravo ERP's open source license gives partners and developers the freedom to choose if they want to publish their work under an open source license or prefer to push a revenue model based on a proprietary license. We encourage partners and developers to share their developments with others under an open source license to increase Openbravo ERP readiness for new sectors.

The aim of this manual is to provide guidance to people developing new Openbravo functionalities, verticals or customizations for specific customer needs.

Openbravo Development Concepts

The following list contains a description of the main concepts of Model Driven Development (MDD) used in Openbravo development:

Application Dictionary (MDD): a centralized storage where tables that define windows, tabs and fields are registered.

Application Menu: left portion of Openbravo application main menu where users can find the main application functionality.

Callout (also known as a trigger): automatic action that occurs when a user makes changes to a given window. An example of a callout could be adding or subtracting visible fields, or filling up an order based on information introduced into the system.

Form: A manually generated window where data is introduced, modified, or deleted. Unlike standard windows, forms permit more complex data entry and allow the user to input data to be used in more than one location.

MVC (Model View Controller): An architecture for building applications that separate the data (model) from the user interface (view) and the processing (controller). In Openbravo the MVC is implemented as follows:
- Model part of the framework is achieved using Openbravo's SqlC tool. The input is an XML file that contains standard SQL statements and the parameters used in the statements. These parameters can be optional or not and facilitate the generation of any needed SQL statement.
- View part of the framework uses the XmlEngine utility developed by Openbravo to design the user interface. XMLEngine is a utility used to create XML/HTML documents from a template in XML/HTML format.
- Controller in Openbravo's framework are java files that extend from the HttpBaseServlet. These servlets make the standard control of reading data, interacting with the database with classes generated by SQLC and providing the output with XmlEngine.

Report: A report presents information retrieved from a table or query in a pre-formatted and visually attractive manner. An output in html or pdf with the objective of displaying requested information in a clear and organized manner. Reports may be customized depending on what the user would like to see, and are commonly used to explain a global position or summary.

Standard Window: An automatically generated window where data is introduced, modified, or deleted. Information in a standard window is created by Openbravo based on data introduced into the system.

Windows: Screens used by the application to manage data. On these screens it is possible to insert, update or delete data. Openbravo defines two types of windows: standard windows and forms (see definition). A window is composed of one or many tabs.

Development methodology

Openbravo is a development platform on which Openbravo ERP has been constructed. As it is explained in the architecture overview the development can be realized at two levels:

Application dictionary. It is the central location where the application stores windows, tables, columns, processes, reports and its relationships. Users can develop new functionality simply defining new windows, data elements and reports and registering them in the application dictionary without the need of writing a single line of code. Most of Openbravo ERP code is generated from the information described in the application dictionary information.

Model/View/Controller. When working at this level it is possible to carry out any change using the Openbravo MVC development framework. Whenever a desired utility or a function is not adjustable with Openbravo data dictionary, the development is realized at MVC level. This usually implies
developing new classes in Java language.

Organizing your development

Before starting to develop a new functionality it is very important to get a clear picture of what has to be achieved and which elements are needed to implement that vision. Some points that can help organizing your development effort are:

Which functionality should be provided. Get a very clear idea which functionalities need to be implemented.
Keep the design as simple as possible. Keep the design simple and make it modular to allow a good level of interaction between different components.
Define your data model. Which tables, columns, relations and indexes need to be added or modified to host the data needed to implement the functionality.
Define the user interface. How users are going to interact with the new functionality. Organize the visual elements in a way that follows industry standard usability guidelines.
Define the reports. Think of which data is relevant to your users and what is the best way of representing it on a report.

Identifier naming criteria

Having a clear, standard and consistant name criteria for data dictionary elements is critical in ERP systems. Following a naming criteria when developing functionally guarantees that new Openbravo ERP versions will not conflict with custom developments by using the same names for naming data dictionary elements.

When doing a customized development, use the prefix CUS_ plus the first three characters of the name of the customer to name new dictionary elements. For example, a customization for a company called ACME use the CUS_ACM prefix when naming application dictionary elements.

When developing a vertical, use the prefix VER_ followed by the three letter of the market that it address. For example, a vertical for the real estate market would use the VER_REA prefix for naming new dictionary elements.

Database elements

Name the new tables adding the particular development prefix. For example, if you have a new table called DOCUMENT for a customized development for a customer called ACME (CUS_ACM prefix) you should use CUS_ACM_DOCUMENT. The columns of these new tables can be named following the column naming convention (AD_Client_ID, IsActive, Name, Description, etc).

When you are adding new columns for existing tables, name the new columns with the particular development prefix.

Name the rest of the new database objects (procedures, triggers, etc.) adding the particular development prefix.

The dictionary register IDs are generated by a sequence that establish the ranks in function of development origin, therefore the duplication of ID´s should not occur. Dictionary's entities with unique names (table, window, process, reference, validation, etc.) should be named adding the particular development prefixes.

MVC folders

Name the folders adding the particular development prefix. It is recommended to do that in an independent package (i.e. org\openbravo\erpCommon\cus_myPackage).

Procedure Language

Use the prefix p_ to name variables used as procedure's parameters
Use the prefix v_ to name variables
Use the prefix c_ to name cursors

Folder structure

Openbravo has a directory tree structure that logically divides different core components (XmlEngine, Sqlc, HttpBaseServlet) and WAD from the ERP itself.

Main tree structure

AppsOpenbravo
   |-build
   |-config
   |-docs
   |-gui
   |-legal
   |-lib
   |-setup
   |-src
   |-srcAD
   |-srcClient
   |-src-core
   |-src-loc
      |-design
      |-es_ES
   |-src-trl
   |-src-wad
   |-web

src-core
Source code of the core components: Sqlc (Model), XmlEngine (View), HttpBaseServlet (Controller) and ConnectionPool.

AppsOpenbravo
   |-src-core
      |-org
         |-openbravo
            |-base
               |-HttpBaseServlet.java
            |-data
               |-Sqlc.java
            |-database
               |-ConnectionPool.java
            |-xmlEngine

src

Source code of the ERP itself: forms, reports, call-outs, combos, work-flows, processes, etc.

AppsOpenbravo
   |-src
      |-org
         |-openbravo
            |-base -> implements the security
            |-erpCommon
               |-ad_actionButton
               |-ad_background
               |-ad_callouts
               |-ad_combos
               |-ad_forms
               |-ad_help
               |-ad_process
               |-ad_reports
               |-ad_tasks
               |-ad_workflow
               |-businessUtility
               |-info
               |-reference
               |-security -> login
               |-utility
               |-ws -> Web Services
            |-erpReports

The ad_ prefix means Application Dictionary. The directory-name endings are pretty much self-explanatory.

The difference between ad_reports and erpReports lays in the way we access a report within the application. If it's accessible directly through the menu, then it should be inad_reports. On the other hand some windows (Invoices, orders, etc.) have a Print icon in the toolbar, which generates a report. These reports should be stored in erpReports.

src-wad

Source code of WAD (Wizard for Application Dictionary).

lib

Libraries necessary for the compilation.

AppsOpenbravo
   |-lib
      |-openbravo-core.jar
      |-openbravo-trl.jar
      |-openbravo-wad.jar
      |-openbravo.war
      |-...

openbravo-core.jar, openbravo-trl.jar and openbravo-wad.jar are necessary to perform the ant compile.complete task. openbravo.war is the resulting deployment binary, the one that should be copied into the application server's webapps directory. For more information check the compilation tasks.

config

This directory contains various configuration files.

AppsOpenbravo
   |-config
      |-dbCon5.xml -> database connection used in the compilation
      |-Format.xml -> numeric locale settings
      |-log4j.lcf -> log4j logging library settings
      |-Openbravo.properties -> date format
      |-XmlPool.xml -> defines the database connection-pool used in run-time by the application server (eg. Tomcat)
      |-...

build

The compiled classes are copied into these directories.

AppsOpenbravo
   |-build
      |-classes
         |-core
         |-src
         |-trl
         |-wad
      |-javasqlc
         |-src
         |-srcAD
         |-trl
         |-wad

src-loc

The view elements (documents) of the MVC structure: HTML, XML, FO and srpt (subreports). They are organized into different localizations (one in each directory), where design refers to en_US.

AppsOpenbravo
   |-src-loc
      |-design
      |-es_ES
      |-xx_XX
      |-...

docs

Openbravo's API documentation, generated at compilation-time by javadoc.

gui

Installer's user interface.

legal

License documents.

src-trl

Source code of the translator.

clients

This directory should contain any particular developments for clients.

srcClient

Particular self-developments for one's own application, not for other clients.

web

Cascading Style Sheets (CSS), Javascript code, images and popup windows.

AppsOpenbravo
   |-css
   |-images
   |-js
   |-popups

[edit]

Command line Compilation tasks

ant core.lib
Compiles the core components of the MVC Framework. It generates openbravo-core.jar.

AppsOpenbravo
   |-lib
      |-openbravo-core.jar

ant wad.lib
Compiles WAD. It doesn't generate windows. It just compiles the WAD itself. It generates openbravo-wad.jar.

AppsOpenbravo
   |-lib
      |-openbravo-wad.jar

ant trl.lib
Compiles the translator. It generates openbravo-trl.jar.

AppsOpenbravo
   |-lib
      |-openbravo-wad.jar

ant compile.complete
Compiles the whole application. It generates WAD's windows and compiles application's sources. It depends on core.lib, wad.lib and trl.lib.

ant compile.complete.development
Compiles the whole application and copy classes to the tomcat context. It generates WAD's windows and compiles application's sources. It depends on core.lib, wad.lib and trl.lib.

ant compile -Dtab=xx
It generates the indicated window (xx), compiles the modified source files and updates the servlet-mappings in web.xml.

ant compile.development -Dtab=xx
It generates the indicated window (xx), compiles the modified source files and updates the servlet-mappings in web.xml. Finally, it copies all the classes to the tomcat context.

ant setup
Interface for setting up the database connection and application's paths (defined in build.xml).

ant war
Generates a war file in lib:

AppsOpenbravo
   |-lib
      |-openbravo.war

ant deploy

Copies the war file to the tomcat webapps directory.

ant installWebService
Installs the Web Services onto the application server context folder.

ant install.sources
It runs ant core.lib, ant wad.lib, ant trl.lib, ant compile.complete, ant installWebService and ant war. It should only be run within the installation.

Development environment

In the development environment the context is copied manually by the development tasks. In this environment we do not want to use the war generation/deployment since it is a time consuming process.

The compilation process, for the first time, will be:

ant core.lib
ant wad.lib
ant trl.lib
ant compile.complete.development

The compilation for the whole application:

ant compile.complete.development

The compilation for a specific window:

ant compile.development -Dtab="window name"

The compilation for a manual source:

ant compile.development -Dtab=xx

In order to avoid the translate task from the compilation (that spends much time and can be unnecessary in this phase of development), a parameter can be included to enable it and it can be added to all your commands: -Dtr=no.

ant compile.development -Dtab="window name" -Dtr=no
ant compile.development -Dtab=xx -Dtr=no


Production environment



In the production environment the context is mounted as a war. This lets us set the correct permissions for each application, but we have to deploy the war onto tomcat to have the new application running.

The compilation process, for the first time, will be:
ant core.lib
ant wad.lib
ant trl.lib
ant compile.complete
ant war
ant deploy



The compilation for the whole application:
ant compile.complete
ant war
ant deploy


The compilation for a specific window:
ant compile -Dtab="window name"
ant war
ant deploy


The compilation for a manual source:
ant compile -Dtab=xx
ant war
ant deploy


Building from sources



This section is a quick guide on how to build from sources. You can also see complete instructions at Build Openbravo from Sources.



Installation from Subversion



Subversion is a version control system tool used by Openbravo, which enables the developers to control the file versions.

Before starting the building process it is necessary to install Subversion. It can be downloaded from the following address:

http://subversion.tigris.org *

It is necessary to install Subversion with OpenSSL


Important: currently building from sources does not work for PostgreSQL only works for Oracle. Openbravo 2.3 will add support for building from sources using PostgreSQL.



Getting sources from Subversion



Once installed Subversion - run the following:

svn co https://openbravo.svn.sourceforge.net/svnroot/openbravo/trunk AppsOpenbravo

This process creates a AppsOpenbravo folder, where sourceforge subversion files are saved (with all the libraries distributed by Openbravo).



Quick building guide



Run from folder where the subversion files were created (e.g. /home/subversion/openbravo/trunk - in Linux or c:\subversion\openbravo\trunk - in Windows).

ant -f build.xml.template setup

This process opens the installation variables configuration.

build.xml is created
dbCon5.xml, XmlPool.xml, log4j.lcf and userconfig.xml files are created in config folder
In src folder deploy.wsdd file is created.


All these files are created when working with Oracle.
In that case of using PostgreSQL it is necessary to change the names of dbCon5_PostrgreSQL.xml and XmlPostgreSQL.xml to dbCon5.xml and XmlPool.xml

Once the variables are configured - in the same folder as before - it is necessary to run the following:

ant install.source

This completes the compilation and generates the .jar packages of the application and creates the openbravo.war file in lib folder. It is necessary to copy this file to$CATALINA_HOME/webapps and deploy the context.

Integrated development environment (IDE)



Some 'getting started' tutorials about rapid Openbravo development using Eclipse and Subclipse are found in:

Development with Eclipse IDE


[edit]




Openbravo Datamodel

[edit]






Stored database objects



The database objects (Procedures, Functions and Triggers) must follow some rules to work correctly in Openbravo in some cases.

The Procedures that are called from the application have only the correspondent AD_PInstance_ID as input parameter. The procedure has also to manage correctly thisAD_PInstance. Setting the result at the end, and setting a correct output message to be useful for the user. In the Creating Procedures document is explained in detail how has to be developed a procedure.

[edit]




Entity-Relationship (E/R) diagram



Openbravo entity-relation (E/R) database diagram provides a complete view of the Openbravo datamodel. It includes tables and columns behind the functionalities of Openbravo ERP.

The document has been created with data obtained from the Application Dictionary. The diagram has been separated into several chapters according to the main functional modules of the ERP. The same table might appear in different chapters but only once in detail. This is done to emphasize the relation among the main tables of the chapter with the rest of the diagram. All the relations are represented among the examples, but those not representative in the chapter have been deleted.

[edit]




Procedure Language recommendations



Openbravo supports Oracle and PostgreSQL database engines.

This is a set of recommendations for writing Procedure Language that works on both database backends (when possible) or that can be translated easily. These recommendations assume that you have written code in Oracle and that you want to port it to PostgreSQL.

[edit]




General rules



This a list of general rules that assure that PL runs properly on different database backgrounds.

JOIN statement. Change the code replacing the (+) by LEFT JOIN or RIGHT JOIN
In XSQL we use a questionmark (?) as parameter placeholder. If it is a NUMERIC variable use TO_NUMBER(?). For dates use TO_DATE(?).
Do not use GOTO since PostgreSQL does not support it. To get the same functionality that GOTO use boolean control variables and IF/THEN statements to check if the conditions are TRUE/FALSE.
Replace NVL commands by COALESCE.
Replace DECODE commands by CASE. If the CASE is NULL the format is:
CASE WHEN variable IS NULL THEN X ELSE Y END

If the variable is the result of concatenating several strings () are required.

Replace SYSDATE commands by NOW()
PostgreSQL treats "" and NULL differently. When checking for a null variable special care is required.
When a SELECT is inside a FROM clause a synonym is needed.
SELECT * FROM (SELECT 'X' FROM DUAL) A

When doing SELECT always use AS.
SELECT field AS field_name FROM DUAL

PostgreSQL does not support synonyms of tables in UPDATE, INSERT or DELETE operations.
PostgreSQL 8.1 (but fixed in 8.2 version) does not support updates like:
UPDATE TABLE_NAME SET (A,B,C) = (SELECT X, Y, Z FROM ..

The following order is correctly accepted:
UPDATE TABLE_NAME SET A = (SELECT X FROM ..), B = (SELECT Y FROM .),C = (SELECT Z FROM ..)

PostgreSQL does not support using the table name in the fields to update.
PostgreSQL does not support the DELETE TABLE command. DELETE FROM Table should be used instead.
PostgreSQL does not support parameters like '1' in the ORDER BY or GROUP BY clause. A numeric without commas can be used.
PostgreSQL does not support the CURRENT OF clause. An active checking of register to update should be used.
PostgreSQL does not support COMMIT. It does automatically an explicit COMMIT between BEGIN END blocks. Throwing an exception produces a ROLLBACK.
PostgreSQL does not support SAVEPOINT. BEGIN, END and ROLLBACK should be used instead to achieve the same functionality.
PostgreSQL does not support CONNECT.
Both Oracle and PostgreSQL do not support using variable names that match column table names. For example, use v_IsProcessing instead of IsProcessing.
PostgreSQL does not support EXECUTE IMMEDIATE ... USING. The same functionality can be achieved using SELECT and replacing the variables with parameters manually.
PostgreSQL requires () in calls to functions without parameters.
DBMS.OUTPUT should be done in a single line to enable the automatic translator building the comment.
In PostgreSQL any string concatenated to a NULL string generates a NULL string as result. It's is recommended to use a COALESCE or initialize the variable to ''.
Notice that in Oracle null||'a' will return 'a' but in PostgrSQL null, so the solution would be coalesce(null,'')||'a' that will return the same for both. But if the we are working with Oracle's NVarchar type this will cause an ORA-12704: character set mismatch error, to fix it it is possible to use coalesce(to_char(myNVarCharVariable),)||'a'.


Instead of doing
COALESCE(variable_integer, '') 

do
 COALESCE(variable_integer, 0)

to guarantee that it will also work in PostgreSQL.

PostgreSQL does the SELECT FOR UPDATE at a table level while Oracle does it at a column level.
PostgreSQL does not support INSTR command with three parameters. SUBSTR should be used instead.
In Oracle SUBSTR(text, 0, Y) and SUBSTR(text, 1, Y) return the same result but not in PostgreSQL. SUBSTR(text, 1, Y) should be used to guarantee that it works in both databases.
PostgreSQL does not support labels like <<LABEL>> (but it can be commented out).


[edit]




Cursors



There are two different ways of using cursors: in FETCH clauses and in FOR loops. For FETCH cursor type no changes are required (except for %ISOPEN and %NOTFOUND methods that are explained bellow).

Oracle FETCH cursor declarations:
CURSOR Cur_SR IS

should be translated in PostgreSQL into:
DECLARE Cur_SR CURSOR FOR

For cursors in FOR loops the format suggested is:
TYPE RECORD IS REF CURSOR;
 Cur_Name RECORD;

that is both accepted by Oracle and PostgreSQL.

[edit]




Arrays



In Oracle, arrays are defined in the following way:
TYPE ArrayPesos IS VARRAY(10) OF INTEGER;
  v_pesos ArrayPesos;
v_dc2 := v_dc2 + v_pesos(v_contador)*v_digito;

but in PostgresSQL they are defined as:
v_pesos integer[];
v_dc2 := v_dc2 + v_pesos[v_contador]*v_digito;

[edit]




ROWNUM



To limit the number of registers that a SELECT command returns, a cursor needs to be created and read the registers from there. The code could be similar to:
--Initialize counter
v_counter := initial_value;
--Create the cursor
FOR CUR_ROWNUM IN (SELECT CLAUSE)
LOOP
  -- Some sentences
  --Increment the counter
  v_counter := v_counter + 1;
  --Validate condition
  IF (v_counter = condition_value) THEN
    EXIT;
  END IF;
END LOOP;


[edit]




%ROWCOUNT



SQL%ROWCOUNT cannot be used directly in PostgreSQL. To convert the SQL%ROWCOUNT into PostgreSQL its value should be defined in a variable. For example:
 GET DIAGNOSTICS rowcount := ROW_COUNT;

In place of SQL%ROWCOUNT the previously declared variable should be used.

[edit]




%ISOPEN, %NOTFOUND



PostgreSQL cursors do not support %ISOPEN or %NOTFOUND. To address this problem %ISOPEN can be replaced by a boolean variable declared internally in the procedure and is updated manually when the cursor is opened or closed.

[edit]




Tables



When defining tables the keywords used for naming columns should be within quotation marks ("). For example, to create the AD_TRACE table:
CREATE TABLE AD_TRACE
(
  "WHEN"  TIMESTAMP                   NOT NULL,
  NO      NUMERIC        DEFAULT 0    NOT NULL,
  WHAT    VARCHAR(2000)               NOT NULL
);

The When column is within quotation marks to avoid problems when being converted to PostgresSQL.

The same idea should be applied when referring to columns names in constraints, triggers, procedures, views and functions that use reserved words.

[edit]




Functions



PERFORM and SELECT are the two commands that allow calling a function. Since PostgreSQL does not accept default function parameters we define an overloaded function with default parameters.

To allow the automatic translator to do its job the following recommendations should be followed:

The AS and the IS should not contain spaces to the left
The function name should not be quoted
In functions, the END should go at the beginning of the line without any spaces to the left and with the function name.


[edit]




Procedures



There are two ways of invoking procedures from PosgreSQL:

Using the format variable_name := Procedure_Name(...);
Using a SELECT. This is the method used for procedures that return more than one parameter.


[edit]




Views



PostgreSQL does not support update for the views. If there is the need of updating a view a set of rules should be created for the views that need to be updated.

In PostgreSQL there are no table/views USER_TABLES or USER_TAB_COLUMNS. They should be created from PostgreSQL specific tables like pg_class or pg_attribute.

[edit]




Triggers



Rules that the triggers should follow:

The name should not be quoted (") because PostgreSQL interprets it literally.


All the triggers have a DECLARE before the legal notice. In PostgreSQL it is necessary to do a function declaration first and then the trigger's declaration that executes the function.


PostgreSQL does not support the OF ..(columns).. ON Table definition. It is necessary to include the checking inside the trigger.


PostgreSQL does not support lazy evaluation. For example the following sentence works in Oracle but not in PostgreSQL:IF INSERTING OR (UPDATING AND :OLD.FIELD = '') THEN

. The correct way of expressing this is:
IF INSERTING THEN <br> ... IF UPDATING THEN <br> IF :OLD.NAME = '' THEN



Triggers in PostgreSQL always return something. Depending on the type of operation it returns OLD (DELETE) or NEW (INSERT/UPDATE). It should be placed at the end of the trigger and before the exceptions if there are any.


If you are using the automatic translator consider that:

The last EXCEPTION in the trigger should not have spaces to its left. The translator considers this the last exception and uses it to setup the right return value.


The last END should not have spaces to its left. The indentation is used to determine where function ends.


[edit]




Sequences



PostgresSQL has a limit of 18 digits for bigInts.
Instead of:
sequence_name.NEXTVAL 

you should use
nextval("sequence_name") 

If you use the automatic translator these changes are done automatically.

[edit]




Command



These is a list of statements that should be converted from Oracle to PostgreSQL and viceversa.

Oracle PostgreSQL
SQLCODE SQLSTATE
RAISE; RAISE EXCEPTION ;
RAISE_APPLICATION_ERROR RAISE ERROR ;
DBMS.OUTPUT RAISE NOTICE '%'
ROWNUM LIMIT
MINUS EXCEPT
ISREFCURSOR REFCURSOR
EXECUTE IMMEDIATE EXECUTE
EXCEPTION NO_DATA_FOUND EXCEPTION DATA EXCEPTION
COMMIT 
ROLLBACK RAISE EXCEPTION


[edit]




Guidelines for writing Rhetoric Names and Descriptions

[edit]






General Guidelines for Rhetoric Descriptions



Openbravo field descriptions in the data dictionary carry two main purposes:

To directly explain what the field is being used for (common words, applications, or major areas)
To explain what happens plus directly explain what the field is being used for (common with support help and folders.


Before reading this section, keep in mind that over 5000 description examples are already available in Openbravo ERP. It is highly encouraged to simply copy and paste already existing examples according to related needs.

Tense: Present tense preferred, past tense if required.
Person
Windows and Tabs: 2nd person
Elements: 3rd (actually, avoid I, you, he, or she altogether)


Singular/Plural:
Windows and Tabs: Write in the plural tense
Elements: Write in the singular tense
A/n vs The: Use a when referring to something general, the when referring to something precise.


Window/tab structure description starts ONLY WITH one of the options below
View – use ONLY when something is read only
Create – when something new appears from nothing, and can then be modified.
Add – when something previously defined is inputted into another area, and can later be modifed
Edit – when information is being modified.
Define – when data is being inputted into a setup area or added for a non transactional purpose
Import – when importing data
Translate – when translating
Combinations which are encouraged include create and edit, add and edit, define and edit.


Description length is usually about 20 words


Some words are not allowed in the application as descriptions. Check out the Out/In:


Out In
Generate Create, Add, or Edit
Manage Create, Add, or Edit
Assign Create, Add, or Edit
Apply Create, Add, or Edit
Assign Create, Add, or Edit
Apply Create, Add, or Edit
New x (redundant)
Different x
Variety x
Various x
Period of time Time period
Given, Scheduled, Chosen (time period) Specified




[edit]




General Guidelines for Naming Rhetoric



A name for a rhetoric (e.g Name, Address, City, etc.) which is same for every record. They are always seen by the user or consultant while using Openbravo. Rhetoric types include the following: Column,Element Names, Field Groups, Field Names, Lists,Menu Items, Messages, Processes from Menus or Buttons, Process Parameters, References, Table,Tabs,Text Interfaces, Window

Start with The Data Model Resource Book by Silverston to see if your word is in the book.

Singluar form or plural accepted
Capitalize all major words
In setup, please attempt to use the singlular form.


Verbs
Window or tab name never starts with a verb
Processes are always start with a verb.


Abbreviations
"Quantity" is always left as a whole word
"Number" is currently always changed to "no."
Generally we try to stay away from abbreviations.


Think strongly before doing the following:
Inserting a -, /, or # into the name
Using an “ed” ending.
Words which are not allowed in the application in order to clearly and simply identify products.


Use “Header” and “Line” as tab names when both are used in a window.


Sometimes, users may need help in order to decide what word to use.


Term Described Synonym
Set Group
Total (summary of all), final
Charge Expense or Expenditure
Planned Expected
Plan Strategy, Structure
Schedule Dates, Times
Setup Structure
Type Distinct characteristic
Date Period or time
Cost Charge
Amount Monetary sum
Quantity Number
Key Identification code
From/To Range (unless specified)
Price Value
Account Identification code
Number (no.) Specific number or code


Some words are not allowed in the application as descriptions. Check out the Out/In:


Out In
System Application
Group Category or Set
Rule Setup
"Is" x (no verb starters)


In conclusion, Openbravo repeats that the best practice is simply to copy and paste existing descriptions and rhetoric names and adjust them to required needs.

[edit]




Developing Openbravo using the data dictionary



This section demonstrates how to develop Openbravo using only the data dictionary.

The explanations are illustrated with a sample that is developed progressively throughout the phases described. The example consists of adding a new document registry functionality. It includes new database elements such as windows and reports.

[edit]




Extending the data model



First you have to create the new tables in the database engine that Openbravo uses as backend. You can use Oracle SQL*Plus application provided with your standard Oracle package or PgAdmin III if you are using Postgres database.

Once your database administration tool is started you must use the Openbravo database configuration parameters used during the installation to connect to the correct database instance.

[edit]




Create new tables in the database backend



When creating a new table, the AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated and UpdatedBy fields have to be defined. These fields are used internally by OpenBravo.



Column name Type Length
DOCUMENTS_ID NUMBER 10
AD_CLIENT_ID NUMBER 10
AD_ORG_ID NUMBER 10
ISACTIVE CHAR 1
CREATED DATE 
CREATEDBY NUMBER 10
UPDATED DATE 
UPDATEDBY NUMBER 10


The primary key of the table is not shown in the application at any time. Even though the NAME and LASTEDITED fields in the example could form a key, we use an extra field that is a unique identifier, in this case DOCUMENTS_ID. It is recommended to name the primary key field using the name of the table plus the _ID suffix.

To create the documents table that we use in this tutorial use the following SQL statement:

- for Oracle:
CREATE TABLE DOCUMENTS
(
  DOCUMENTS_ID  NUMBER(10)   NOT NULL,
  AD_CLIENT_ID  NUMBER(10)   NOT NULL,
  AD_ORG_ID     NUMBER(10)   NOT NULL,
  ISACTIVE      CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
  CREATED       DATE DEFAULT SYSDATE          NOT NULL,
  CREATEDBY     NUMBER(10)                    NOT NULL,
  UPDATED       DATE DEFAULT SYSDATE          NOT NULL,
  UPDATEDBY     NUMBER(10)                  NOT NULL,
  NAME         NVARCHAR2(60)                NOT NULL,
  LASTEDITED    DATE                            NOT NULL,
  DOCSIZE NUMBER(15)   NOT NULL
);

- for PostgreSQL
CREATE TABLE DOCUMENTS
(
  DOCUMENTS_ID  SERIAL NOT NULL CONSTRAINT DOCUMENTS_ID PRIMARY KEY,
  AD_CLIENT_ID  INTEGER                         NOT NULL,
  AD_ORG_ID     INTEGER                         NOT NULL,
  ISACTIVE      CHAR(1) DEFAULT 'Y'             NOT NULL,
  CREATED       DATE DEFAULT NOW()              NOT NULL,
  CREATEDBY     INTEGER                         NOT NULL,
  UPDATED       DATE DEFAULT NOW()              NOT NULL,
  UPDATEDBY     INTEGER                         NOT NULL,
  NAME          VARCHAR(60)                     NOT NULL,
  LASTEDITED    DATE DEFAULT NOW()              NOT NULL,
  DOCSIZE       INTEGER                         NOT NULL
);

Once executed, a new table called Documents is created in the database backend.

[edit]




Register the new tables in the Openbravo ERP dictionary



Now you have to register the newly created database tables in the Openbravo ERP dictionary. To achieve this, first log into Openbravo using an account with access to the System Administrator role. Then, go to the Table and Column menu option of the Application directory. Select the New Record icon from the toolbar. The following form is displayed:



Main fields of this form are:

Name. Defines the name which Openbravo uses to name the defined database table.
Description. Gives a small description of the table.
Help/Comments: Defines the text that is displayed in the help Window.
DB Table name: Defines the database table name as it was entered upon creation of the table inside the database.


In our example, the name field should be Documents, the DB table name DOCUMENTS. Once you have filled in all the details you should save the new table definition using the save icon from the toolbar. Now, press the button Create columns from DB to automatically create the column entries previously defined within the database. When the creation process has finished, you will be informed of the number of columns that have been added to this table.

In the column tab of this form you can see all the columns defined for the table that we are introducing. You can then additionally customize the properties for each column.



If you want to see a list of all the created columns you can click the Relation icon.



Each column is assigned a reference (which defines the data type) depending on its name and data type. After the Synchronize Terminology process (under 'Application Dictionary > System Admin') is run, a System Element is automatically assigned to each field. The name of this element is the same as the column name. If an element matching this rule exists it is associated with it otherwise a new one is created.
Note: The columns that are named 'line' or 'seqNo' are used to contain the position in a relation (i.e. the number of a line in an invoice). They take a Default Value like:
@SQL=SELECT COALESCE(MAX(ColumnName),0)+10 AS DefaultValue FROM 
TableName WHERE xxParentColumn=@xxParentColumn@ 


It is necessary to replace the WHERE clause of this value (the red part of code). The code that should appear here is the name of the column which links with the id of the parent one, i.e. for the previously mentioned invoice there is a table named C_Invoice containing the header of the invoices which is the parent table for the lines saved in C_InvoiceLine. This second table has a column named line and the default value that it takes is:
@SQL=SELECT COALESCE(MAX(LINE),0)+10 AS DefaultValue FROM 
C_INVOICELINE WHERE xxParentColumn=@xxParentColumn@ 


This should be modified to:
@SQL=SELECT COALESCE(MAX(LINE),0)+10 AS DefaultValue FROM C_INVOICELINE WHERE C_INVOICE_ID=@C_INVOICE_ID@ 


When the primary key of a new table is going to be referenced with a column in another table as a foreign key, it is necessary to set certain columns as identifiers. The values of these columns for each record will be the data shown in the drop-downs. The foreign key columns are usually referenced as tabledir. By default all columns with column name 'name' are set as an identifier.

Note: you have to define at least one primary key (key column) in you table in the application dictionary so that it will be successfully compiled.

[edit]




Creating a new Window



In order to create a new window you should first log into Openbravo using an account with access to the System Administrator role. Go to the Window, Tab Field menu option under the System Admin and select the New Record icon from the toolbar. The following form is displayed:



Here, descriptions of the main fields of the window form definition are located:

Name. Defines the name which Openbravo for this window.
Description. Gives a short description to the window.
Help/Comments: Defines the text that is displayed in the help Window.


In our example, the name field is User documents.
Once you have filled in all the details you should store the new window definition using the save icon in the toolbar.
Now, you can select the Tab tab, and you get the following form:



Here, descriptions of the main fields of the tab form definition are entered:

Name. Defines the name which Openbravo uses for this tab.
Description. Gives a short description to the window.
Help/Comments: Defines the text that is displayed in the help Window.
Table. Establishes a relation between this tab and the database table.


In our example, the name field is Documents and we select the table name Documents.
Once you have filled in all the details you should save the new tab definition using the save icon from the toolbar. Now, press the button Create fields. When the process is finished, you will be informed of the number of fields that have been added to the dictionary.
You can see the created fields by clicking on the “Field” tab.



You can customize the created fields using the field tab. Selecting the field you want to customize the following form will be displayed:



Descriptions of the main fields of the columns form definition are entered here:

Name. Defines the name which Openbravo for this field.
Description. Gives a short description to the field.
Help/Comments: Defines the text that is displayed in the help Window.
Table. Establishes a relation between this tab and the database table.


Finally, to end the process we have to associate the window that we have created with the table definition that we have previously created. We have to go to the Table and columnmenu option under the System Admin and select the Table tab. Edit the Documents table and associate the window document to this table definition, as seen in the following screen capture.



To correctly finish off the process of window creation we need to compile the created window. To do this, we need to execute a command from the application root directory (AppsOpenbravo by default).
ant compile -Dtab=WindowName


or you can use this command to directly put your changes in the tomcat webapps directory
ant compile.development -Dtab=WindowName


(WindowName is the name of the created window).
Once the compilation has finished we must restart Tomcat.

[edit]




Adding the new window to the Openbravo menu



In order to insert a link to the newly created window to the Openbravo menu you must first log into Openbravo using an account with access to the System Administrator role. Go to theGeneral Setup menu option under the Application and select the Menu item. Now, select the New Record icon from the toolbar. You will see the following form:



Descriptions of the main fields of the Menu form definition will be entered here:

Name. Defines the name which Openbravo for this menu item.
Description. Gives a short description to the menu.
Action. Indicates the action that this menu should execute when selected.
Window/Form/Process/Task/Report/WorkBench/Workflow. Indicates the name of the resource associated to the action.


In our example, the name field is Documents, the action is Window and the Window field is User documents.
Once you have filled in all the details you should save the new menu definition using the save icon from the toolbar. Now, you have to assign a location of the new menu item within the current menu hierarchy. Use the tree icon from the toolbar. The following form is displayed:



Now, click on the menu entry with which you want to associate the newly created menu item, while the top radio button is selected. Select the lower radio button and click on the recently created window icon. Finally check the “Is child” check box and click on the “Assign” button

[edit]




User messages

Describe errors in checkings
Common patterns
How to register them in a table


[edit]






Developing Openbravo using the MVC

[edit]






Basic toolset

SQLC (SQL Compiler) is a utility used to avoid the repetitive task of writing Java classes to interact with the database. The input is an XML file that contains standard SQL statements and the parameters used in the statements. SQLC reads this file and generates a Java class that has all the code necessary to connect to the database, execute the statement, read the record(s) and create standard Java types as return for the methods.


XmlEngine is a utility used to create XML/HTML documents from a template in XML/HTML format and an XML configuration file with the dynamic data to be inserted in the template. XmlEngine is easy to use because the design of the template is the identical to that of the final document desired. The input of data is made identifying the places where data will be displayed by using standard XML/HTML attributes like “id” or tags such as “DIV”. The configuration file maps the data source fields with the places identified in the template. To improve performance, the templates are read and stored in memory. When a page is required, the template creates a document that fills with the data provided by the application. With this tool we generate the forms for editing a record, list a selection of records, create reports with grouping levels or print specific forms of the application


HttpBaseServlet and HttpBaseSecureServlet are the servlets from which all of the servlets of the system that implement the Control component of MVC are derived. These servlets implement common functionalities such as authentication, authorization, database connectivity and error handling. The servlets deriving from HttpBaseSecureServlet make the standard control of reading data, interacting with the database with classes generated by SQLC and providing the output with XmlEngine.




[edit]




Creating a new manual window (form or report)



Within Openbravo framework all the automatic windows as well as forms and reports (which we also call manual windows) have the same structure according to MVC principles:

controller file (Java)
view template file (HTML or FO)
view configuration file (XML)
model file (XSQL)


In case of automatic windows that we previously developed and introduced into the Application Dictionary, the above files are automatically generated by WAD. But in case of a manual window, we need to develop these files entirely by ourselves. Consequently, this usually takes more time. For this reason, we usually try to develop a certain functionality using an automatic window in Application Dictionary. If this is not possible or if we need to develop a report then we will have to resort to developing a manual window from scratch.

Let's first look at an existing Invoice Vendor Report (go to Sales Management > Reports > Invoice Vendor Report). A form opens which provides an interface to input parameters required to generate a report.



By clicking on the first icon, the controller generates a HTML report.



Clicking on the second one generates a PDF report.

From the developer's point of view the following elements needed to be developed:

- Source files involved in this example, located in erpCommon/ad_reports:

Controller:
ReportInvoiceCustomerEdition.java


Model
InvoiceEdition_data.xsql


Template for the filter window:
ReportInvoiceCustomerFilter.xml
ReportInvoiceCustomerFilter.html


Template for the html result page
ReportInvoiceCustomerEdition.xml
ReportInvoiceCustomerEdition.html


Template for the pdf result page
ReportInvoiceCustomerEdition_Pdf.xml
ReportInvoiceCustomerEdition.fo




- Within the application dictionary the report must be entered into:

Report & Process window
Menu window


The location of source files actually depends on what kind of report/form we are designing:

src/org/openbravo/erpCommon/ad_forms - manual windows called from the menu
src/org/openbravo/erpCommon/ad_reports - reports called from the menu
src/org/openbravo/erpReports - reports called by pressing the print button within a window's toolbar


As you can see from the source files we actually have three templates in this example, one for the filter window, one for the HTML report and one for PDF report. It is up to the controller to decide which one is shown when. See this section within the controllers file (ReportInvoiceCustomerEdition.java):
  public void doPost (HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException {
    VariablesSecureApp vars = new VariablesSecureApp(request);

    if (!Utility.hasProcessAccess(this, vars, "", "RV_ReportInvoiceCustomer")) {
      bdError(response, "AccessTableNoView", vars.getLanguage());
      return;
    }

    if (vars.commandIn("DEFAULT")){
        String strdateFrom = vars.getStringParameter("inpdateFrom", "");
        String strdateTo = vars.getStringParameter("inpdateTo", "");
        printPageDataSheet(response, vars, strdateFrom, strdateTo);
    }else if (vars.commandIn("EDIT_HTML")) {
        String strdateFrom = vars.getStringParameter("inpdateFrom");
        String strdateTo = vars.getStringParameter("inpdateTo");
        String strcBpartnetId = vars.getStringParameter("inpcBPartnerId");
        String strcProjectId = vars.getStringParameter("inpcProjectId");
        String strissotrx = "Y";
        printPageHtml(response, vars, strdateFrom, strdateTo, strcBpartnetId, strcProjectId ,strissotrx);
    } else if (vars.commandIn("EDIT_PDF")) {
        if (log4j.isDebugEnabled()) log4j.debug("WE EDIT THE PDF");
        String strdateFrom = vars.getStringParameter("inpdateFrom");
        String strdateTo = vars.getStringParameter("inpdateTo");
        String strcBpartnetId = vars.getStringParameter("inpcBPartnerId");
        String strcProjectId = vars.getStringParameter("inpcProjectId");
        String strissotrx = "Y";
        printPagePdf(response, vars, strdateFrom, strdateTo, strcBpartnetId, strcProjectId ,strissotrx);
    } else pageErrorPopUp(response);
  }



The model file includes all the SQL commands needed to retrieve/update the data. The commands of the resulting java class are called/executed within the controller. After the compilation, you can find the file InvoiceEdition.java in the build/javasqlc/src/org/openbravo/erpCommon/ad_reports folder. This file is the result of SQLC compilation of our .xsql file.

Within the application dictionary, this report must be introduced in order for Openbravo to know of its existence and location. To do this we need to log in as a System Administrator and access the Application Dictionary > Report & Process menu item. In the first tab we enter:

Name. Defines the name which Openbravo uses for this report.
Description. Gives a short description of the report.
Help/Comments: Defines the text that is displayed in the help Window.
Report check: check this to indicate this is a report.




Next, we need to specify where this report is located. We specify the package location in the Process Class tab:

Action. Specifies what type of element this package is. In our case, a report.
Default: yes.
Classname. specifies the package name according to Java package naming conventions.




Last, we need to enter all three mappings of the templates so that Openbravo can generate Tomcat's web.xml file. Enter them in the Process Mapping tab, one after another.

Mapping name: specify the relative URL starting from the erpCommon folder. Make sure the URL has an ending (eg. .pdf or .html) since browsers can sometimes consider it to resolve the type of document.
Default: Make sure you specify one as a default one.




If we were introducing a new Form to the application dictionary we would access Application Dictionary > Forms menu item instead of the Report & Process one. Entering a form is very similar to entering a new report described above.



The last step before compilation is inserting our new manual window within the menu structure so it can be made accessible to the users. The procedure for inserting new items into the menu has already been described above in the Creating new window section.

After the source files have been developed and put into corresponding folders and the report has been introduced to the application dictionary, it is time to compile the application. Since we are in the development stage and we would like to compile only the manual sources we would use the following command:
ant compile.development -Dtab=xxx

The above compilation executes the following tasks:

converts our .xsql model file into a java class file that is used to access the data
compiles all java classes of our report
translates our report into languages that are opened within the database
generates mapping that is entered into web.xml used by Tomcat
only compiles manual sources (not automatic windows)
copies compiled results onto the Openbravo's Tomcat context folder


[edit]




Creating a callout



A callout is a piece of javascript code associated to a field. This code is executed whenever the field changes. It acts as ajax code and is used to update other fields of the window accordingly.

This is how it works:

When the field changes (onchange event), the html form is sent to the callout servlet.
The callout servlet obtains the data needed (database, calculations...).
The response of this servlet is javascript code that is sent to a hidden frame.
This javascript is executed and fills the different fields of the visible frame with the values given by the servlet.


The steps involved in creating a new callout are:

Create the source file(s) of the callout (usually a java file and an xsql file).
Create the new callout in the dictionary (Application Dictionary > Callout).
Associate this callout to the column desired (Application Dictionary > Table and column: callout field in column tab).
Compile the window(s) where this column is used.


The callout java file is similar to other Openbravo servlets. The only thing that should be taken into account is that the response is always the same XmlDocument (CallOut). This document is filled with a javascript array with the name of the fields to be changed and the values to be assigned to this fields. This can be seen in the following example:
  XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/ad_callouts/CallOut").createXmlDocument();

  StringBuffer resultado = new StringBuffer();
  resultado.append("var calloutName='SL_ExampleCallout';\n\n");
  resultado.append("var respuesta = new Array(");
  resultado.append("new Array(\"inpvariable1\", \"" + FormatUtilities.replaceJS(valule1) + "\"),");
  resultado.append("new Array(\"inpvariable2\", \"" + FormatUtilities.replaceJS(valule2) + "\")");
  resultado.append(");");

  xmlDocument.setParameter("array", resultado.toString());
  xmlDocument.setParameter("frameName", "frameAplicacion");

  response.setContentType("text/html;charset=UTF-8");
  PrintWriter out = response.getWriter();
  out.println(xmlDocument.print());
  out.close();



[edit]




Creating reports using Jasper Reports



Openbravo has been integrated with JasperReports, so now there is a new way to develop reports for Openbravo ERP. Both methods are going to be available, each one with its own strengths.

XmlEngine permits total control of the output page as the template is programmed in the output format, html or fop. Also, with XmlEngine it is possible to include links and ajax technology.

JasperReports uses an intermediate format and permits the ability to export the report to several different formats, html, pdf, excel, csv,... It also has a very advanced development tool (iReport) that makes the design of new reports very easy. The management of the computer resources is better resulting in a faster generation of results for large reports.

This integration has been focused on two main goals, the possibility of uploading a Jasper Report template to the application and defining the parameters needed to be able to open it from the application menu. Additionally, the focus included improving the export of the grid from the windows switching to Jasper Reports and including new output formats.

[edit]




Reports using JasperReport's templates



There are 2 ways to add a new report to the application

By creating a servlet that builds the filter window and generates (compiles) the output or developing only the Jasper Report template
Or using a generic servlet to enter parameters and compile the output.


[edit]




The report template



The templates can be done with any development tool, e.g. iReport [1]. The main template added to or stored in Openbravo ERP has to be a 'jrxml' file. In regards to Openbravo integration, some considerations are necessary.
It is possible to set all necessary parameters in the reports. Those parameters can be defined as a java.lang.String or java.util.Date. There are also some predefined parameters that can be used within the template:

BASE_WEB (java.lang.String) Is the context url. Useful when a file stored in the 'web' folder needs to be used/referenced. For example the url of the image ...web/images/image.png in the template will be:$P{BASE_WEB}/images/image.png

BASE_DESIGN (java.lang.String) is the url for files stored in 'src' folder. Useful when a file stored in 'src' folder needs to be referenced. For example the url of the subreport ..src/org/openbravo/erpCommon/ad_reports/subreport.jasper in the template will be:
$P{BASE_DESIGN}/org/openbravo/erpCommon/ad_reports/subreport.jasper

ATTACH (java.lang.String) is the path to the folder where the files attached to the report are stored. If an image file is attached to the report using the attachment utility of the application, the url to that image will be:
$P{ATTACH}/image.png

USER_CLIENT and USER_ORG (java.lang.String) are commonly used to filter the reports according to the client and organization. The clients and organizations that the user has access to are set by these parameters. The usage of these parameters in the query is:
MAIN_TABLE.AD_CLIENT_ID IN ($P!{USER_CLIENT})<br>MAIN_TABLE.AD_ORG_ID IN ($P!{USER_ORG})

LANGUAGE (java.lang.String) Sets the language of the user's session.
LOCALE (java.util.Locale) A Locale based on the selected language
NUMBERFORMAT (java.text.NumberFormat) A NumberFormat object used to format the numeric fields.
$P{NUMBERFORMAT}.format($F{Field})



When date parameters are optional, a default value has to be defined.
(new SimpleDateFormat("dd-MM-yyyy")).parse("01-01-1900")

Optional parameters of java.lang.String class require an auxiliary parameter. The main parameters are defined in the application dictionary and is set by the application. It's default value has to be:
“”

The auxiliary parameter is not prompted for. It takes its value depending on the value passed and its default value:
$P{C_BPartner_ID}.equals("") ? " " : " AND C_ORDER.C_BPARTNER_ID = " + $P{C_BPartner_ID}

Finally, the auxiliary parameter is used in the query as:
AND 1=1 $P!{PARTNER_AUX}

The images in templates have to be defined with isLazy attribute checked (in image tab of iReports). The image expression has to be defined using the provided parameters (BASE_WEB, BASE_DESIGN or ATTACH).
To include subreports in the template, pre-compiled 'jasper' file instead of the template needs to be used. However this disables Openbravo from translating it. We will explain a workaround for this situation.
The subreport expression has to be defined using the provided parameters (BASE_WEB, BASE_DESIGN or ATTACH).
It is also possible to include graphs within the reports with PDF or EXCEL output formats.

[edit]




Usage of subreports



When it is necessary to use subreports within a report we have two options. Either use the template jrxml file or the compiled jasper file. Preferrably, the jrxml file should be used when possible to enable the translation of the subreports to other languages.

[edit]




Using the jasper file



When the subreport is defined in the main jrxml template, the path to the subreport's jasper file needs to be set (as a java.lang.String class). Within this path you can use some predefined parameters such as BASE_DESIGN or ATTACH. The main downside of this method is that the file will not be translated upon Openbravo compilation. When the subreport needs to be modified, you will have to recompile the modified jrxml file and replace the old jasper file with the new one.

[edit]




Using the jrxml file



In this case, you will need to develop the java servlet (controller) that also compiles the jrxml file. The 'Subreport Expression' of the subreport in the main template needs to be a parameter with class 'net.sf.jasperreports.engine.JasperReport'. This parameter must be created in the main template with the same class. The java controller then fills the parameter with the compiled subreport template. See an example of a Purchase Order subreport 'RptC_OrderPO.jrxml' that references a subreport for lines 'RptC_OrderPO_Lines.jrxml'. The parameter for the subreport is named 'SR_LINES'.
 HashMap<String, Object> parameters = new HashMap<String, Object>();
 JasperReport jasperReportLines;
 try { 
   JasperDesign jasperDesignLines = JRXmlLoader.load(strBaseDesign+"/org/openbravo/erpReports/RptC_OrderPO_Lines.jrxml");
   jasperReportLines = JasperCompileManager.compileReport(jasperDesignLines);
 } catch (JRException e){
   e.printStackTrace();
   throw new ServletException(e.getMessage());
 }
 parameters.put("SR_LINES", jasperReportLines);


[edit]




Self made java for the report



There are some cases when the when data filters are complex or the Jasper Report output needs to be built on the fly:

Selection lists or an advanced filter window is required.
Data operations need to be executed before printing the result.
The report has subreports that need to be translated.


To build the output the renderJR() method of HttpSecureAppServlet class is used. This method has 7 parameters:

VariablesSecureApp, object with session data.
HttpServletResponse, response object of the servlet.
String ReportName, string with the template's name and the complete path to it. These can be referenced as '@basedesign@' and '@attach@'. (see next section for more info about the special characters)
String Output, string with the desired output format. Avaliable options are: 'html', 'pdf' or 'xls'.
HashMap<String, object> Design parameters (optional), hashmap with the parameters' values for the template. Usually, at least the TITLE parameter in the template should be set to the name of the report in the database

 HashMap<String, object> p = new HashMap<String, Object>();
 p.put("TITLE", classInfo.name);


FieldProvider report data (optional), fieldprovider object with the report data, built using Openbravo's xsql. If it is null, the template's query is used.
Map<Object, Object> export parameters (optional), Map object with parameters needed to generate the report in the desired output format. The 'html' and 'xls' have some predefined parameters.


HTML:

 exportParameters.put(JRHtmlExporterParameter.IS_USING_IMAGES_TO_ALIGN, Boolean.FALSE);


XLS:

 exportParameters.put(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
 exportParameters.put(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);


[edit]




The report in the Application Dictionary



The reports are introduced in the Reports and Processes window and checked as jasperReport reports. It's also necessary to set the main template file to its complete path. If the file is stored in the src folder the JRName field would be:
@basedesign@/org/openbravo/erpCommon/ad_reports/template.jrxml

And if the file is stored using the attachment utility of the window:
@attach@/template.jrxml

The input parameters are defined using the Parameters tab of the Reports and Processes window. The column name of the parameter must be the same as the parameter name in the template.
To include the parameter for choosing the output format a list type reference ('Output format') is defined and should be used. The column name has to be 'inpoutputformat'. If no format is selected, HTML will be taken as the default.

[edit]




Export grid using JasperReports



In the relation view of any window, we can export the grid data using jasperReports technology. At this moment the available output formats are PDF, EXCEL and CSV. Each format has its own button in the toolbar.

[edit]




Internationalization considerations

How to setup the database in UTF-8
NCHARVAR 2 for national charsets
Date formats and number formats


[edit]






Security

[edit]






Upgrading process



Openbravo regularly publishes new releases of the ERP in which new functions are added and the already existing ones are being debugged or modified.

Openbravo has an automatic installation update system that upgrades the installation with new developments of the new release. In order not to loose the changes you have made to the Openbravo installation (extensions, modifications or personalizations) while upgrading follow the development conventions explained here.

The Openbravo sources consist of:

Database structure and metadata. Those are treated in an increasing mode (the changes between the releases are applied to the installation).


MVC folders. Those are treated in a prioritized mode (the client's sources overwrite the general sources).


[edit]




Interoperability

External Point of Sales web service documentation. Description of Openbravo POS web service interface that allows Openbravo's integration with other systems (for example with a Virtual Shop and a POS Terminal)


[edit]






Additional information



This a list of additional Openbravo developers documentation that complements this Developers Manual.

SQLc documentation. SqlC is a tool that builds a java class from a file with SQL statements. The java class contains the code necessary to connect it to a database, format the statement, execute it and return the resultset from the database.


XmlEngine tutorial. XmlEngine is a tool that produces forms and reports according to templates defined in XML, including FOP (Formatting Objects Processor) or HTML mark-up languages.


E/R model. Description of Openbravo ERP data model.


Openbravo ER Appendix NotImplemented. Documentation of deprecated elements in the data model.

Oracle	PostgreSQL
SQLCODE	SQLSTATE
RAISE;	RAISE EXCEPTION ;
RAISE_APPLICATION_ERROR	RAISE ERROR ;
DBMS.OUTPUT	RAISE NOTICE '%'
ROWNUM	LIMIT
MINUS	EXCEPT
ISREFCURSOR	REFCURSOR
EXECUTE IMMEDIATE	EXECUTE
EXCEPTION NO_DATA_FOUND	EXCEPTION DATA EXCEPTION
COMMIT
ROLLBACK	RAISE EXCEPTION

Out	In
Generate	Create, Add, or Edit
Manage	Create, Add, or Edit
Assign	Create, Add, or Edit
Apply	Create, Add, or Edit
Assign	Create, Add, or Edit
Apply	Create, Add, or Edit
New	x (redundant)
Different	x
Variety	x
Various	x
Period of time	Time period
Given, Scheduled, Chosen (time period)	Specified

Term Described	Synonym
Set	Group
Total	(summary of all), final
Charge	Expense or Expenditure
Planned	Expected
Plan	Strategy, Structure
Schedule	Dates, Times
Setup	Structure
Type	Distinct characteristic
Date	Period or time
Cost	Charge
Amount	Monetary sum
Quantity	Number
Key	Identification code
From/To	Range (unless specified)
Price	Value
Account	Identification code
Number (no.)	Specific number or code

Out	In
System	Application
Group	Category or Set
Rule	Setup
"Is"	x (no verb starters)

Column name	Type	Length
DOCUMENTS_ID	NUMBER	10
AD_CLIENT_ID	NUMBER	10
AD_ORG_ID	NUMBER	10
ISACTIVE	CHAR	1
CREATED	DATE
CREATEDBY	NUMBER	10
UPDATED	DATE
UPDATEDBY	NUMBER	10

Monday, July 8, 2013

Openbravo Developers Manual

Introduction

Openbravo Development Concepts

Development methodology

Organizing your development

Identifier naming criteria

Database elements

MVC folders

Procedure Language

Folder structure

Command line Compilation tasks

Development environment

Production environment

Building from sources

Installation from Subversion

Getting sources from Subversion

Quick building guide

Integrated development environment (IDE)

Openbravo Datamodel

Stored database objects

Entity-Relationship (E/R) diagram

Procedure Language recommendations

General rules

Cursors

Arrays

ROWNUM

%ROWCOUNT

%ISOPEN, %NOTFOUND

Tables

Functions

Procedures

Views

Triggers

Sequences

Command

Guidelines for writing Rhetoric Names and Descriptions

General Guidelines for Rhetoric Descriptions

General Guidelines for Naming Rhetoric

Developing Openbravo using the data dictionary

Extending the data model

Create new tables in the database backend

Register the new tables in the Openbravo ERP dictionary

Creating a new Window

Adding the new window to the Openbravo menu

User messages

Developing Openbravo using the MVC

Basic toolset

Creating a new manual window (form or report)

Creating a callout

Creating reports using Jasper Reports

Reports using JasperReport's templates

The report template

Usage of subreports

Using the jasper file

Using the jrxml file

Self made java for the report

The report in the Application Dictionary

Export grid using JasperReports

Internationalization considerations

Security

Upgrading process

Interoperability

Additional information