Openbravo.com
Wiki
Planet
Log in / create account
View source | Discuss page | Page history | Printable version   
ADVERTISEMENT
Accounting eLearning Courses
Partnerships
SourceForge.net Logo
Openbravo ERP at SourceForge

SourceForge.net Logo
Openbravo POS at SourceForge

Open Solution Alliance Logo
Openbravo at Open Solutions Alliance

Developers Manual

Rating :
2.00/5
(2 votes cast)
You have to be registered to be able to vote
Languages: 

English  |  ‪中文(中国大陆)‬ | Translate this article...

Contents

Introduction

Openbravo ERP 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:


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:


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


Folder structure

Openbravo ERP has a tree directory structure that logically divides different core components (XmlEngine, SQLC, HttpBaseServlet) and WAD (Wizard for Application Dictionary) from the ERP itself (forms, reports, call-outs, combos, work-flows, processes and so on).

Main tree structure 

AppsOpenbravo
   |-attachment   
   |-build
   |-config
   |-database
   |-docs
   |-legal
   |-lib
   |-src
   |-srcAD
   |-srcClient
   |-src-core
   |-src-db
   |-src-trl
   |-src-trl
   |-src-wad
   |-web
   |-WebContent
      |-src-loc

build Compiled classes are copied into these directories. 

AppsOpenbravo
   |-build
      |-classes
         |-org
            |-openbravo
               |-authentication
                  |-basic
                  |-lam
               |-base
                  |-secureApp
               |-erpCommon
               |-erpReports
               |-erpWindows
      |-javasqlc
         |-src
         |-srcAD

config This directory contains various configuration and log files. 

AppsOpenbravo
   |-config
      |-Format.xml
      |-log4j.lcf
      |-Openbravo.properties
      |-setup-properties-linux.bin
      |-...

database Contains model data (structure: tables, constraints, procedures and triggers), sample data (Openbravo ERP data such as products, business partners and so on) and source data (windows and tabs metadata) in plain *.xml files. 

AppsOpenbravo
   |-database
      |-lib
      |-model
         |-functions
         |-sequences
         |-tables
         |-triggers
         |-views
      |-sampledata
      |-sourcedata

docs Openbravo's API documentation, generated at compilation time by Javadoc.

legal License documents.

lib Libraries necessary for 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.

src Source code of Openbravo ERP itself: forms, reports, call-outs, combos, work-flows, processes and so on. 

AppsOpenbravo
   |-src
      |-org
         |-openbravo
            |-base
            |-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
               |-utility
               |-ws
            |-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 in ad_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.

srcAD This folder contains the automatically generated code from the Application Dictionary.

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

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

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

src-db Source code required for dbmanager.jar file creation. 

AppsOpenbravo
   |-src-db
      |-src
         |-org
            |-openbravo
               |-ddlutils

src-trl Source code of the translator. 

AppsOpenbravo
   |-src-db
      |-src
         |-org
            |-openbravo
               |-translate

src-wad Source code of WAD (Wizard for Application Dictionary). 

AppsOpenbravo
   |-src-db
      |-src
         |-org
            |-openbravo
               |-wad

web Cascading Style Sheets (CSS), Javascript code, images and pop-up windows. 

AppsOpenbravo
   |-images
   |-js
   |-popups
   |-skins

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
   |-WebContent
      |-src-loc
         |-design
            |-org
               |-openbravo
                  |-base
                  |-erpCommon
                  |-erpReports
                  |-erpWindows
         |-es_ES
         |-xx_XX
         |-...


Style guidelines

In order to create uniform and clean code, it is recommended to follow the guidelines explained below. This applies to Java, XML, HTML and PL/SQL source code.


Logical comparisons

Do not use spaces. 

WHERE a = b
WHERE a= b

WHERE a=b


Comma separated lists

SELECT a,b,c
SELECT a ,b ,c
SELECT a , b , c

SELECT a, b, c


Spaces in parenthesis

In functions: 

SELECT max( c1 )
SELECT max (c1)

SELECT max(c1)

In comparison operators: 

if(i==0)
if( i==0)

if (i==0)
for (i=0; i<n; i++)


SELECT INTO and INSERT INTO


SQL keywords

SQL keywords in uppercase: 

select * from AD_FIELD

SELECT * FROM AD_FIELD
(SELECT, UPDATE, etc).


Compiling the application

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, yy" It generates the indicated window (those ones containing in their names xx or yy), compiles the modified source files and updates the servlet-mappings in web.xml.

ant compile.development -Dtab="xx, yy" It generates the indicated window (those ones containing in their names xx or yy), 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.source 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 trl.lib
ant wad.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 trl.lib
ant wad.lib

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


Integrated development environment (IDE)

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


Openbravo Datamodel

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 this AD_PInstance. Setting the result at the end, and setting a correct output message to be useful for the user. In the Creating Procedures section of this document is explained in detail how has to be developed a procedure.


Entity-Relationship (E/R) diagram

Openbravo ERP 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.


Creating Procedures

AD_PInstance and AD_PInstance_Para Tables

Each call to a procedure from the application is registered in the table AD_PInstance. The AD_PInstance_Para table stores the values selected for the parameters defined in the Reports and Process window for the correspondent procedure.

AD_PInstance table:

AD_PInstance_Para table:

The _TO suffix is used when the parameter is defined to introduce a range instead of an exact value. The generated popup window with the parameters will include two fields with from and to labels. Later those values can be used to filter queries between those values.


Input parameters of procedures

When the application calls a stored procedure, only one parameter is passed to the database: the correspondent AD_PInstance_ID.

If a stored procedure is going to be called from the application and from a different stored procedure we need an intermediate procedure. The reason is that when it's called from other procedure there isn't an AD_PInstance_ID. See C_Order_Post and C_Order_Post1 as example. The main procedure will have as many input parameter as necessary being one of them the AD_PInstance_ID. The intermediate procedure is the one that is set in the Application Dictionary to be called and only has a parameter for the AD_PInstance. This procedure only has a call to the main one passing as NULL all the parameters except the AD_PInstance. In the main procedure it's necessary to differentiate when the AD_PInstance is null or not to manage the result and the output message as well as to manage correctly the exceptions.


Getting useful information from AD_PInstance

At the beginning of the procedure we might want to retrieve the necessary information from the AD_PInstance and AD_PInstance_Para tables and save it into variables. If there isn't any parameter a SELECT clause might be enough. If there are several parameters a cursor will be needed and the different parameters can be identified by its Parametername. See C_Debt_Payment_Create as example.

The Record_ID stores the id of the record that was active in the window (e.g., the C_Order_ID when processing an Order). If the Procedure has UPDATE and/or INSERT clauses, the AD_User_ID can be retrieved for auditory purposes (CreatedBy and UpdatedBy columns).


AD_Update_PInstance stored procedure

This procedure updates the status of the AD_PInstance record. It has to be called at the beginning and at the end of the procedure.

Parameters of the procedure:

At the beginning the PInstance is set as Processing = 'Y': 

 AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL);

At the end of the procedure and in the exception block it's set back to Processing = 'N' with the result and output message: 

 AD_UPDATE_PINSTANCE(p_PInstance_ID, v_User, 'N', v_Result, v_Message);

The result can have 3 different values. Depending on it the message box with the procedure output message has different styles. Result 0 is used for errors (red box), 1 for success (green box) and 2 for warnings (yellow box). If the message is set to NULL the output message that is shown in the window is defaulted to success.


Exception block and error management

The errors messages of the procedures are managed depending on the result of the correspondent AD_PInstance. So all the exceptions have to be properly managed in the exception block.

When there is a problem use the RAISE_APPLICATION_ERROR to finish the procedure. The error number is set to -20000. And as error message a user friendly description of the problem. To build this description is possible to use message stored in the AD_Message table putting its value between @. It is possible to concatenate several AD_Message.value and/or literal strings.

The Exception block has to differentiate two possibilities. If the procedure has been called directly from the application or by other procedure. Normally this differentiation is known by the existance or not of the AD_PInstance_ID.

If it's called from other procedure the exception is raised to be managed by the parent procedure where exists the AD_PInstance. This does not apply to the Intermediate Procedures explained above. This intermediate procedures don't have the exception block so the exception is managed in the main one where it is also the correspondent AD_PInstance_ID. Before the RAISE it can be used the DBMS_OUTPUT.PUT_LINE(); for debugging purposes. Example of a exception block in a procedure that is called from another one: 

 EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('ERROR MA_Standard_Cost_Sequence, sequence_ID '|| p_Sequence_ID || ', date ' || p_CalcDate || ' at ' ||v_ResultStr);
     RAISE;
 END Ma_Standard_Cost_Sequence;

When exists the AD_PInstance the exception has to be managed to set the correspondent message in the AD_PInstance table. The message is build concatenating @ERROR= with the SQLERRM message that contains the error message that is set in the RAISE_APPLICATION_ERROR or the database error message if the exception has been thrown by the database. Then is done a ROLLBACK; and finally the AD_PInstance is updated with the created message and with result=0 to get the error box. It is also possible to use DBMS_OUTPUT.PUT_LINE(); for debugging purposes. 

 EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
     v_ResultStr:= '@ERROR=' || SQLERRM; 
     DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
     ROLLBACK;
     AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
     RETURN;
 END MA_ProductionRun_Standard;


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.


General rules

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

CASE WHEN variable IS NULL THEN X ELSE Y END

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

SELECT * FROM (SELECT 'X' FROM DUAL) A

SELECT field AS field_name FROM DUAL

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 ..)

COALESCE(variable_integer, )

do 

COALESCE(variable_integer, 0)

to guarantee that it will also work in PostgreSQL. 

RIGHT: COALESCE(movementdate, TO_DATE('01-01-1900', 'DD-MM-YYYY'))


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 below).

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.


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;


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;


 %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.


 %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.


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.


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:


Procedures

There are two ways of invoking procedures from PosgreSQL:


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.


Triggers

Rules that the triggers should follow: 

IF INSERTING OR (UPDATING AND :OLD.FIELD = ) THEN

The correct way of expressing this is: 

IF INSERTING THEN 
 ... IF UPDATING THEN 
 IF :OLD.NAME =  THEN

If you are using the automatic translator consider that:


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.


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


PostgreSQL 8.3 support

In order to support PostgreSQL 8.3, Openbravo 2.40alpha has adapted its source code. The most significant change incorporated to this new PostgreSQL version is the Non-character data types are no longer automatically cast to TEXT.

"Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. For example, these expressions formerly worked: 

substr(current_date, 1, 4)
23 LIKE '2%'

but will now draw "function does not exist" and "operator does not exist" errors respectively. Use an explicit cast instead: 

substr(current_date::text, 1, 4)
23::text LIKE '2%'

(Of course, you can use the more verbose CAST() syntax too.) The reason for the change is that these automatic casts too often caused surprising behavior. An example is that in previous releases, this expression was accepted but did not do what was expected: 

current_date < 2017-11-17

This is actually comparing a date to an integer, which should be (and now is) rejected — but in the presence of automatic casts both sides were cast to text and a textual comparison was done, because the text < text operator was able to match the expression when no other < operator could.

Types char(n) and varchar(n) still cast to text automatically. Also, automatic casting to text still works for inputs to the concatenation (||) operator, so long as least one input is a character-string type. ..."

For this reason, PostgreSQL 8.3 requires explicit casts both in procedural languages and database source code files. This extensive task has been fulfilled in Openbravo 2.40alpha release. For example, the above examples with an Oracle/PostgreSQL compatible: 

substr(TO_CHAR(current_date), 1, 4)
TO_CHAR(23) LIKE '2%'

Many explicit cast have been included into the Openbravo source code and procedural language code to support PostgreSQL 8.3. The changes are mainly: 

...
AD_COLUMN_IDENTIFIER(TO_CHAR('C_Invoice'), TO_CHAR(p.C_INVOICE_ID), TO_CHAR(?)) AS INVOICE
...

...
  SELECT STDPRECISION 
  FROM C_CURRENCY 
  WHERE C_CURRENCY_ID = TO_NUMBER(?) 
  AND CREATED <= TO_DATE(?)
...

Another example of neccessary cast is when you try to operate with dates, for example, to calculate the days between two dates: 

SELECT TO_DATE(DELIVERY_DATE) - TO_DATE(ORDERED_DATE) + 1;

For PostgreSQL 8.3 you will need to add an explicit cast TO_NUMBER since the difference between dates returns a value of type interval and this automatic cast has been removed in this version: 

SELECT TO_NUMBER(TO_DATE(DELIVERY_DATE) - TO_DATE(ORDERED_DATE) ) + 1;

With these easies changes you can make your code compatible with PostgreSQL 8.3.

Openbravo has tested the new 2.40alpha release with PostgreSQL 8.3 and it has passed successfully the [[New_Acceptance_Testing | Acceptance Test].

Guidelines for writing Rhetoric Names and Descriptions

General Guidelines for Rhetoric Descriptions

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

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.

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


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.

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)

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.


Developing Openbravo using the data dictionary

This section shows how to develop Openbravo ERP using Application Dictionary (AD).

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


Extending the data model

First of all, create a new table using a database administration tool (e.g., pgAdmin III or phpPgAdmin for PostgreSQL and Oracle SQL Developer or Toad for Oracle).

To connect to the correct database instance, use the same database parameters values that you used during Openbravo ERP installation.


Create new tables in the database backend

Create a new table named CUS_DOCUMENTS including AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated and UpdatedBy fields since they are mandatory for security and auditory purposes.

Column name Type Length
CUS_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
NAME CHAR 60
LASTEDITED DATE
DOCSIZE NUMBER 15

In this example, even if NAME and LASTEDITED fields could form a key, please use an extra field that is an unique identifier such as CUS_DOCUMENTS_ID. Following name criteria, primary key field is named as the table plus _ID suffix.

Execute the following SQL statement to create CUS_DOCUMENTS table within your database:

Oracle 

CREATE TABLE CUS_DOCUMENTS
(
  CUS_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,
  CONSTRAINT "CUS_DOCUMENTS_KEY" PRIMARY KEY ("CUS_DOCUMENTS_ID")
);

PostgreSQL 

CREATE TABLE cus_documents
(
  cus_documents_id  numeric(10)   NOT NULL,
  ad_client_id  numeric(10)   NOT NULL,
  ad_org_id     numeric(10)   NOT NULL,
  isactive      char(1)       NOT NULL   DEFAULT  'Y'::bpchar,
  created       timestamp     NOT NULL   DEFAULT  now(),
  createdby     numeric(10)   NOT NULL,
  updated       timestamp     NOT NULL   DEFAULT  now(),
  updatedby     numeric(10)   NOT NULL,
  name          varchar(60)   NOT NULL,
  lastedited    timestamp     NOT NULL   DEFAULT  now(),
  docsize       numeric(10)   NOT NULL,
  CONSTRAINT cus_documents_key PRIMARY KEY (cus_documents_id)
);


Register the new tables in the Openbravo ERP dictionary

The following step consists of registering in Openbravo ERP Application Dictionary the newly created table.

For this purpose, first log into Openbravo ERP using an account with access to System Administrator role. Navigate to Application Dictionary || Tables and Columns and create a new record with Name Cus_documents, Description Documents to be saved, Help/Comments Window used to register documents, DB Table Name CUS_DOCUMENTS, Data Access Level Client/Organization and Development Status Ready.

Main fields of this window are:

Save this record then press Create columns from DB button to create column entries automatically.

Once the creation process has finished, you will be informed of the number of columns that have been added to this table.

Move to Column tab to see all the defined columns. You can then additionally customize the properties for each column. Each column is assigned to a reference (which defines the data type) depending on its name and its data type. Run Synchronize Terminology process (Application Dictionary || System Synchronize Terminology).

An Application 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 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 your table in the application dictionary to be successfully compiled.


Creating a new Window

Logged with System Administrator role navigate to Application Dictionary || Windows, Tabs, and Fields || Window. Create a new record with Name User documents, Description Documents to be saved and Help/Comments Window used to register documents.

Main fields of this window are:

Save this record and move to Tab tab. Create a new record with Name User documents, Description Documents to be saved, Help/Comments Window used to register documents, Table CUS_DOCUMENTS and Tab Level 0.

Main fields of this window are:

Save this record then press Create Fields button to create field entries automatically.

Move to Field tab to see all the created fields.

Double click on a field to edit its properties.

Move to Field Sequence tab to define which fields will be displayed (right side) and which not (left side) and the order of displayed ones (up and down arrows).

To end with Application Dictionary part, go back to Application Dictionary || Tables and Columns and select the previously created Cus_documents record. Select User documents in Window drop-down list and save this record.


Compiling a new Window

To compile the new created window, go to application root directory using command line and type: 

ant compile -Dtab="User documents"

If WAR is not used, go to application root directory using command line and type: 

ant compile.development -Dtab="User documents"

Please notice that a new folder named Userdocuments has been created inside srcAD/org/openbravo/erpWindows/ application path. This folder contains Userdocuments_data.xsql (Model), Userdocuments_Edition.html and Userdocuments_Relation.html (View), Userdocuments_Edition.xml and Userdocuments_Relation.xml and Userdocuments.java (Controller).


In case that you wish to compile another window, just substitute User documents by the name of the window to be compiled. Please find more information on compiling tasks at Openbravo Development Environment (ODE) article.

Important note: once the compilation has finished, restart Apache Tomcat server.


Adding the new window to the Openbravo menu

In order to access User documents window through an item in the left side menu, navigate to General Setup || Application || Menu with System Administrator role. Create a new record with Name Documents, Description Folder for documents and check Summary Level in order to add a folder to menu tree.

Create another record with Name User Documents, Description Documents to be saved, Action Window and Window User documents.

Main fields of this window are:

Save this record then click on Tree icon Image:CreateWindow13.png.

Drag and drop Documents folder and User Documents window within the menu tree.

Change your role to Openbravo Admin (or any other Client Admin) and check that User Documents window displays inside Documents folder. Check also that you are able to type information inside and save it.

Click on Help icon Image:CreateWindow16.png to see help information of the window.


Updating database XML files

Finally, changes done inside the database through application dictionary have to be exported to plain XML files within database application folder.

For this purpose, go to application root directory using command line and type: 

ant export.database

Please find more information on export.database Ant task at Openbravo Development Environment (ODE) article.

Notice that CUS_DOCUMENTS.xml file containing the definition of CUS_DOCUMENTS table has been added inside database/model/tables path.

Check also that, inside database/sourcedata path:

Important note: notice that the ID of new created lines corresponds with the development.environment.id defined within Openbravo.properties file. Each new created line ID equals your development.environment.id multiplied by 100.000 plus 0 if it is the first record that you created in this table, plus 1 if it is the second record created, plus 2 if it is the third record created and so on. Please find more information on development.environment.id at Openbravo Development Environment (ODE) article.

This is not applicable from r2.50. This version includes UUIDs as primary keys so development environment is no longer used.

User messages

A further explanation of the Openbravo messages types can be found in Openbravo Messages Style Guide.


Developing Openbravo using the MVC

Basic toolset


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:

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:

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

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

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:

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

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.

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:

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:

The steps involved in creating a new callout are:

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();

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.


Reports using JasperReport's templates

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


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: 

$P{BASE_WEB}/images/image.png

$P{BASE_DESIGN}/org/openbravo/erpCommon/ad_reports/subreport.jasper

$P{ATTACH}/image.png

MAIN_TABLE.AD_CLIENT_ID IN ($P!{USER_CLIENT})
MAIN_TABLE.AD_ORG_ID IN ($P!{USER_ORG})

$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.


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.


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.


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);


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:

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

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

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

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


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.


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.


Internationalization considerations


Security

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:


Interoperability


Additional information

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

Retrieved from "http://wiki.openbravo.com/wiki/Developers_Manual"

This page has been accessed 50,676 times. This page was last modified 10:37, 2 October 2008. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.


Category: