Log in / create accountOpenbravo.comPartner PortalIssuesBlogsForgeExchangeUniversityDownloads

View source | Discuss page | Page history | Printable version   Hot documentation news

[ERP 2.50] Module demonstrations (videos)

How-to create localization modules

[ERP 2.50] Fundamentals and Concepts

[ERP] Upgrading to 2.50

[ERP] Mercurial manual

[POS] Openbravo POS integration

[ERP 2.50] New user manual

[ERP 2.50] Modularity video tutorials

[ERP 2.50] Build tasks

[ERP 2.50] Environment installation

[ERP 2.50] InstallationADVERTISEMENT

Toolbox

 Main Page

 Create new article

 Upload file

 What links here

 Recent changes

 Help

Google Search

Participate Communicate

 Report a bug

Search

Search

 Contribute

 Talk to us

now!Partnerships

Openbravo ERP at SourceForge

Openbravo POS at SourceForge

Openbravo at Open Solutions Alliance

ERP/2.50/Developers Guide/How to develop a new window

Developers Guide

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

Index

Contents

 [hide]

1   Objective 2   Module 3   Creating new tables in the database 4   Registering the table within the Application Dictionary 5   Creating a New Window 6   Creating the Menu Item 7   Compiling the Application with the New Window 8   The Result

Objective

The objective of this how-to is to show how you can create a new window from scratch. The how-to starts with creating a new table and then takes you on to creating the window itself and adding it to a module.

The how-to is based on the following scenario: imagine we are developing an HR module and we need a window that will enable the user to input salaries of employees. We also need to track the employee's salary so history records need to be preserved. Each salary record needs to have a Valid From Date field that indicates when a particular salary came into being. The record belonging to a particular employee with the latest Valid From Date is the salary that is valid today. Note that employees are already inside the system contained in the C_BPARTNER database table and indicated by the C_BPARTNER.ISMEPLOYEE column. Therefore, we only need to create a database table that will hold the actual salaries.

Module

All new developments must belong to a module that is not the core module. Please follow the How to create and package a module section to create a new module.

  Note the DB Prefix defined there is HT which will explicitely indicate the prefix of our new database table!

Creating new tables in the database

Let's introduce a new database table called HT_SALARY that will hold the required data. Notice the HT prefix of the table name indicating the module this table belongs to.

The new HT_SALARY table must include the AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated and UpdatedBy fields that are mandatory and required for security and auditory purposes of the application.

Column name Type Length Note

HT_SALARY_ID CHAR 32The primary key of the table that must follow the table name followed by the _ID.

AD_CLIENT_ID CHAR 32Indicates which client (company) the record belongs to (multitenancy).

AD_ORG_ID CHAR 32Indicates which organization (city/department/location) within a client a record belongs to.

ISACTIVE CHAR 1This is intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.

CREATED DATE Date/time of creation of a record.

CREATEDBY CHAR 32Foreign key to AD_USER indicating the user that created this record.

UPDATED DATE Date/time of last update of a record.

UPDATEDBY CHAR 32Foreign key to AD_USER indicating the user that last updated this record.

C_BPARTNER_ID CHAR 32 Employee this salary belongs to.

AMOUNT NUMBER 10 The actual amount of the salary.

C_CURRENCY_ID CHAR 32Foreign key to C_CURRENCY indicating the currency the amount is in.

VALIDFROM DATE Date that this salary is valid from.

To create the above table within the database, use one of the following CREATE TABLE statements depending on the DB you are using:

PostgreSQL

CREATE TABLE HT_SALARY( HT_SALARY_ID CHARACTER VARYING(32) NOT NULL, AD_CLIENT_ID CHARACTER VARYING(32) NOT NULL, AD_ORG_ID CHARACTER VARYING(32) NOT NULL, ISACTIVE CHARACTER(1) NOT NULL DEFAULT 'Y', CREATED TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), CREATEDBY CHARACTER VARYING(32) NOT NULL, UPDATED TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), UPDATEDBY CHARACTER VARYING(32) NOT NULL, C_BPARTNER_ID CHARACTER VARYING(32) NOT NULL, AMOUNT NUMERIC NOT NULL, C_CURRENCY_ID VARCHAR(32) NOT NULL, VALIDFROM TIMESTAMP WITHOUT TIME ZONE NOT NULL, CONSTRAINT HT_SALARY_ISACTIVE_CHECK CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT HT_SALARY_KEY PRIMARY KEY (HT_SALARY_ID), CONSTRAINT HT_SALARY_AD_ORG FOREIGN KEY (AD_ORG_ID) REFERENCES AD_ORG (AD_ORG_ID), CONSTRAINT HT_SALARY_AD_CLIENT FOREIGN KEY (AD_CLIENT_ID) REFERENCES AD_CLIENT (AD_CLIENT_ID), CONSTRAINT HT_SALARY_C_BPARTNER FOREIGN KEY (C_BPARTNER_ID) REFERENCES C_BPARTNER (C_BPARTNER_ID), CONSTRAINT HT_SALARY_C_CURRENCY FOREIGN KEY (C_CURRENCY_ID) REFERENCES C_CURRENCY (C_CURRENCY_ID));

Registering the table within the Application Dictionary

The following steps register the newly created table within the Openbravo ERP Application Dictionary.

For this purpose, first log into Openbravo ERP using a username with access to System Administrator role. Navigate to Application Dictionary || Tables and Columns and create a new record as shown in the screenshot below:

Main fields of this window are (for more information see the AD_Table table description):

Data Package specifies to which java data package within the module the table will belong when used within DAL (Data Access Layer).

Name Defines the name that Openbravo ERP uses to recognize the defined database table.

Description Gives a small description of the table. Help/Comments Defines the text that is displayed in

Help window. DB Table name Defines database table name as it

was defined by the CREATE TABLE during its creation. Java Class Name This will be the actual Java class

within the Data Package of the module through which you will be able to access this table when using DAL.

Data Access Level determines what kind of data will the table contain due to the multitenancy functionality

o System only: Only system records can be

inserted into this table (AD_CLIENT_ID=0, AD_ORG_ID=0), for example AD_TABLE.

o System/Client: System or client specific

records can be inserted here

(AD_CLIENT_ID=anything, AD_ORG_ID=0), for example AD_ROLE

o Organization: Only client and organization

specific data can be inserted into this table (AD_CLIENT_ID<>0, AD_ORG_ID<>0), for example C_INVOICE

o Client/Organization: Only client specific data

can be inserted here, however, it can belong to a specific organizations within that client or be shared among all (AD_CLIENT_ID<>0, AD_ORG_ID=anything), for example C_BPARTNER

o All: Any combination of AD_CLIENT_ID and

AD_ORG_ID can be inserted into this table.

Save this record then press Create columns from DB button to create columns within the Column tab automatically.

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

Switch to Column tab to see all the columns that were created according to their definition within the database. You can now additionally alter the properties of each column. Each column is assigned a reference (which defines the data type) depending on its name and its data type. Run Synchronize Terminology process (Application Dictionary || Synchronize Terminology). For more information see the AD_Column table description.

This process tries to find an existing application element (within the currently developed module) and thus its label, help and description and if one is not found, a new one is created. This enables a centralized translation of the application/module.

Each table must have at least one column marked as an identifier. The actual values of identifier

columns later get concatenated to be shown to the user as a representation of a particular record (see the link to the Sales Order within the Sales Invoice window). These identifiers will also be used to construct dropdown lists of records of that particular table. By default all columns with column name Name are set as an identifier. In case there is no column with this Name, no identifier is set and needs to be done so manually or compilation will fail.

  NOTE: The columns that are named line or seqNo are used to contain the sequence number of a record (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@

The WHERE part of this clause needs to be replaced with the required values. The code that should appear here is the name of the column which links with the id of the parent one. For example, each record of the C_InvoiceLine belongs to a particular C_Invoice record and they are all sequenced. C_Invoice is the parent table for the lines saved in C_InvoiceLine. This 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 C_INVOICE_ID=@C_INVOICE_ID@

Most of the columns in our specific HT_SALARY case will be automatically detected correctly, however, some need revising:

Amount: Reference = Amount, Length = 10 C_BPartner_ID: Reference = Search, Reference

Search Key = Business Partner, Link To Parent Column = Y

Valid From: Identifier = Y Amount: Identifier = Y, Date Fields (Updated,UpdatedBy...) = Date

Openbravo ERP now knows about the new HT_SALARY database table and how to treat it in terms of its definition and the representation to the user.

Creating a New Window

Using the System Administrator role navigate to Application Dictionary || Windows, Tabs and Fields. Create a new record as indicated by the screenshot below:

Although in the image the name used is Employee Salaries, it is an error and should be Employee Salary

Main fields of this window are (for more information see the AD_Window table description):

Name Defines the name that Openbravo ERP uses to recognize this window.

Description Gives a small description of the table. Help/Comments Defines the text that is displayed in

Help window. Window Type Defines some user interface specifics

for a window:o Maintain: is used for windows with few entries.o Transaction: for transactional windows.

The header tab's underlying table must contain the PROCESSED and UPDATED columns

by default this window filters out old (n days – General Setup > Application > Session Preferences window setting) and processed documents.

o Query Only: for read-only windows that only

enable viewing of data.

Save this record and move to Tab tab. Create a new record as shown below, creating the first tab to show the employee information:

Main fields of this window are (for more information see the AD_Tab table description):

Name Defines the name that Openbravo ERP uses to recognize this tab.

Description Gives a small description of the table. Help/Comments Defines the text that is displayed in

Help window. Table Specifies the table that the tab will show the

data from. Table Level Defines the hierarchy of tabs, 0 being the

highest level. UI Pattern This dropdown offers the following options:

o Standard - standard interface where multiple

records can be added, viewed and editedo Read Only - this option disables any

editing/creating capabilities for any user within this tab

o Single Record - this option enforces a one-to-

one relationship between a parent and a child tab, allowing the user to enter maximum one record in the tab

SQL Where Clause By using this SQL filter, the user will never be able to see data that does not fit the

criteria. In our case, we use it to display only business partners that are our employees.

Save this record and then click the Copy Tab Fields button to copy fields from the existing main tab of the Business Partner window into our new one. Select the Business Partner-Business Partner Tab - Window combination and confirm the dialog with OK.

Move to Field tab to see all the created fields.

If required, changes to these fields could be made or new ones could be added manually. For more information see the AD_Field table description. However, in our case we are happy with the way they are.

Now, go back to Tab tab and create a new record that will represent the child tab of the Employee tab where salaries will be managed:

Most importantly, make sure you select:

Table = HT_Salary Tab Level = 1

For more information see the AD_Tab table description.

By clicking and confirming the Create Fields dialog, the application will automatically insert the columns of the selected table into the fields tab of the Salary one.

Then, 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). See the snapshot below to order them according to common look and feel of other windows and Save.

For Openbravo to create links (labels that appear blue) to table elements, the system needs to know which window represents the table where a certain element resides. In our case, the Employee Salary window is used to manage the content of the HT_Salary database table. Hence, all salary records need to be shown within that window. To indicate that go to the Application Dictionary || Tables and Columns window, find our HT_Salary table and set the Window as indicated below:

Creating the Menu Item

A menu item is required for the user to be able to call up the new window we developed. Using the System Administrator role navigate to General Setup || Application || Menu and create a new record:

Main fields of this window are (for more information see the AD_Menu table description):

Name Defines the name that Openbravo ERP uses to recognize this menu item.

Description Gives a small description of the table. Summary level Defines a folder containing menu

items (windows, processes, reports and so on). Action Defines the type of menu item. URL If Action is External link or Internal link, defines

the URL to be linked. Special Form If Action is Form, defines the form to be

linked. Process If Action is Process, defines the process to be

launched. Report If Action is Report, defines the report to be

linked. OS Task If Action is Task, defines the operating task to

be launched. Window If Action is Window, defines the window to be

linked. Workflow If Action is Workflow, defines the workflow

to be linked.

Save this record then click on Tree icon  .

Her you can drag and drop the new Employee Salary menu item to any of the other menu groups.

Compiling the Application with the New Window

Finally, the application needs to be recompiled in order to generate the new window's code and deploy it to Tomcat. If using Eclipse, use the eclipse.compile ant task and enterEmployee Salary into the dialog that pops up. If manually compiling Openbravo, use the ant compile.development -Dtab='Employee Salary'

Important note: once the compilation has finished, restart Apache Tomcat server. In Windows, it is best to stop the Tomcat before running the build task and the start it again afterwards since Windows locks certain files the the compile.development build task might not be able to copy over.

See more on Build Tasks.

The Result

Using the Openbravo Admin role, select the link to the new window from the menu. Notice the new window and the two tabs hierarchically positioned one above another (one Employe can have one or more salary records):

By double clicking John Moneymaker, details of this employee appear, however in a read-only mode (notice all fields are gray).

Then by moving on to the Salary tab, you will see an empty grid since we have not entered any salaries yet. Use the create new icon to create a new salary record as indicated below.

Save.

You have now successfully created your own new window and seen how it came to life within Openbravo ERP. Congratulations!

How to change an existing window | How to develop a callout

 

This page has been accessed 6,966 times. This page was last modified 13:59, 14 September 2010. Content is available under Creative Commons

Attribution-ShareAlike 2.5 Spain License.To report an error or for administrative issues about this site go to Wiki

Administration Page

Openbravo ERP Advanced Development

Chapter 4 – Advanced Application Dictionary

v2.50.18

© 2008-2010 Openbravo S.L. All rights reserved. The information in this document is confidential and may not be disseminated or disclosed to third parties (either in digital form or on paper) without the

prior written consent of Openbravo S.L.

1. Reference 2. Introduction 3. Creating Tables 1. Creating Tables Inside the Database 2. New References 3. Introducing New Tables to the Application Dictionary 4. New Windows 1. New Menu Items 2. Compilation 3. Using New Windows 5. Customizing an Existing Window 1. Compilation 6. Finishing Touches 1. 1. Calculated Default Values 2. 2. Validation 3. 3. Display Logic 4. 4. Document Sequences 5. 5. Read Only Logic 6. 6. Changing Label(s) 7. Compiling 7. Final Result 8. Exporting Developments 9. Additional Exercises 1. Adding a Read-Only Stay Tab to the Room Window 10.Further Reading

Reference

Before starting with this chapter reading the following articles: http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/

How_to_develop_a_new_window

Introduction

Since hotel functionality is entirely new to Openbravo ERP, we would like to create some new windows and structures that will hold hotel specific data.

Creating Tables

Windows can be defined on top of existing or new tables. Since Openbravo ERP does not posses any hotel specific functionality, we need to create the tables that will hold the data. Let's recall our ER diagram of the hotel scenario:

According to this diagram, you should be able to create the underlying database tables.

 Creating Tables Inside the Database

Let us give you the CREATE TABLE statements (Postgres specific) for the first two tables:

CREATE TABLE hotel_guest(  hotel_guest_id character varying(32) NOT NULL,  ad_client_id character varying(32) NOT NULL,  ad_org_id character varying(32) NOT NULL,  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,  created timestamp without time zone NOT NULL DEFAULT now(),  createdby character varying(32) NOT NULL,  updated timestamp without time zone NOT NULL DEFAULT now(),  updatedby character varying(32) NOT NULL,  documentno character varying(32) NOT NULL,  first_name character varying(100) NOT NULL,  last_name character varying(100) NOT NULL,  c_bpartner_id character varying(32) NOT NULL,  guest_rate varchar(60) NOT NULL DEFAULT 'C'::bpchar,

  CONSTRAINT hotel_guest_key PRIMARY KEY (hotel_guest_id),  CONSTRAINT hotel_guest_adclient FOREIGN KEY (ad_client_id)      REFERENCES ad_client (ad_client_id),  CONSTRAINT hotel_guest_adorg FOREIGN KEY (ad_org_id)      REFERENCES ad_org (ad_org_id),  CONSTRAINT hotel_guest_cbpartner FOREIGN KEY (c_bpartner_id)      REFERENCES c_bpartner (c_bpartner_id),  CONSTRAINT hotel_guest_unique UNIQUE(ad_client_id, documentno));

CREATE TABLE hotel_room(

  hotel_room_id character varying(32) NOT NULL,  ad_client_id character varying(32) NOT NULL,  ad_org_id character varying(32) NOT NULL,  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,  created timestamp without time zone NOT NULL DEFAULT now(),  createdby character varying(32) NOT NULL,  updated timestamp without time zone NOT NULL DEFAULT now(),  updatedby character varying(32) NOT NULL,  "number" character varying(10) NOT NULL,  arate numeric NOT NULL DEFAULT 0,  brate numeric NOT NULL DEFAULT 0,  crate numeric NOT NULL DEFAULT 0,  room_type varchar(60) NOT NULL DEFAULT 'S'::bpchar,  smoking character(1) NOT NULL DEFAULT 'N'::bpchar,

  CONSTRAINT hotel_room_key PRIMARY KEY (hotel_room_id),  CONSTRAINT hotel_room_adclient FOREIGN KEY (ad_client_id)      REFERENCES ad_client (ad_client_id),  CONSTRAINT hotel_room_adorg FOREIGN KEY (ad_org_id)      REFERENCES ad_org (ad_org_id),  CONSTRAINT hotel_room_unique UNIQUE(ad_client_id, ad_org_id, number));

We are counting on you to produce the CREATE TABLE SQL for the hotel_stay table according to the following field specification:

hotel_stay_id varchar(32)

ad_client_id varchar(32)

ad_org_id varchar(32)

isactive char(1)

created timestamp without timezone

createdby varchar(32)

updated timestamp without timezone

updatedby varchar(32)

hotel_room_id varchar(32)

hotel_guest_id varchar(32)

date_in timestamp without timezone

planned_nights numeric(2)

date_out non mandatory timestamp without timezone

room_rate varchar(60) – will be a list reference

final_sum non mandatory numeric(10)

Remember to:1. include the primary and foreign keys2. pay attention to NULL and NOT NULL fields3. prefix all constraints with the DBPrefix!4. execute the statements inside the database

New References

Note: References are an essential way of defining the user interface on a metadata level and hence very important for a consultant to know. Brush up on them by reading this detailed article explaining individual references: http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/Concepts/AD/Data_Model#References. 

In our case, columns Guest Rate, Room Rate and Room Type will have predefined values that will be selectable from a drop down. Therefore, we need to define two new List Referencesfor them using the Application Dictionary || Reference window:

Notice how the new list reference automatically belongs to the Hotel Module that we have created previously. Here is why:

1. Each new element must belong to a module2. New elements can only be assigned to modules that are in development3. New elements cannot be assigned to packs or templates, it must be a simple

module4. Hotel Module is a simple module and we have set it in development

Now, add the three list items inside the [List Reference]:

Add another list reference Room Type with Parent Reference = List and the following items:

Single - S Double - D Suite - U

Introducing New Tables to the Application Dictionary

For Openbravo ERP to know about the new tables, introduce them inside the application dictionary using the Application Dictionary || Table and Column window according to the HOWTO article listed in the beginning.

A few things to be careful of: Data Access Level - since our tables will contain company specific data this

should be Client/Organization (Guest) or Organization (Stay, Room). Java Class Name - normally we use the table name stripped of underscores, e.g.

Hotel_Stay becomes HotelStay. Set references of Guest Rate and Room Rate columns as Lists of the first

reference entered above Set reference of the Room Type column as a List of the second new list reference

entered above ARate, BRate and CRate references as Amount of Length 8 Hotel_Guest_ID column of the Hotel_Stay table should be marked as Link to

Parent column Final_Sum column should have the Amount reference with Length = 10 Date_In and Date_Out need to be of Date reference Planned_Nights should be of Integer reference with Length = 2 Documentno column of the Hotel_Guest table should be renamed to

DocumentNo (note the capital N) - this is due to automatic sequence generation rule

Make sure each table has at least one column marked as Used as Record Identifier

o Hotel_Guest: First_Name + Last_Nameo Hotel_Room: Numbero Hotel_Stay: Date_In + Hotel_Guest_ID + Hotel_Room_ID

Make sure that the Data Package of each of your table definitions is 'Hotel Data Package'

Execute Application Dictionary || Synchronize Terminology after all tables and columns have been defined

New Windows

Let's create two new windows based on the previously created tables.

Our first window should be named Guest/Stay and have two tabs: Guest Stay

Our second window will be named Room with one tab only: Room.

To create the 1st window, navigate to Application Dictionary || Window, Tab and Field and create a new record with the first tab:

Use the Create Fields button to create the fields based on the Hotel_Guest tables introduced earlier. Rearrange them using the [Field Sequence]:

Add the second tab based on Hotel_Stay, this time on the Tab Level = 1:

Use the Create Fields again and rearrange them as required. Some fields in need of attention are:

Hotel_Guest_ID should be made Read Only since it automatically gets selected based on the Guest parent tab

Secondly, create the Room window following the same procedure, however, only one tab needs to be created.

New Menu Items

In order to be able to access the new windows from the menu, the menu group and the items need to be created. Using the General Setup || Application || Menu window, create the following menu structure:

Compilation

To see and use the newly created window, we need to compile and deploy them first. To do both, use the following ant task (make sure you first switch the current folder to /opt/OpenbravoERP!):

    $ ant smartbuild

Above command detects all changes that have been done to the application code, compiles them and deploys them to the application server, Tomcat.  Upon finish, restart Tomcat, e.g.:

    $ su    $ /etc/init.d/tomcat restart    $ exit

you should be able to login again and navigate to the new windows from the menu.

Note 1: If using Eclipse, use the eclipse.compile task, entering 'Guest,Room' into the popup.

Note 2: Do NOT compile the application as a superuser (su)!!! It will mix su and openbravo user privileges within the AppsOpenbravo folder, making the folder structure unusable.

Using New Windows

To use the new window, login and switch to Green Terrace Hotel Admin role. Navigate to Hotel || Room window and try to enter a new room and then a few more so that we have a bit of data to play with:

To be able to enter a new guest, we need to have at least one business partner. To be able to enter a business partner, we need to enter a business partner category. Use the Master Data Management || Business Partner Setup || Business Partner Category window to create a new category (e.g. Generic Category) Then navigate to Master Data Management || Business Partner window to enter a Generic Guest business partner to which we'll be able to link guests inside the Guest window:

Then, add a stay for a particular guest by using [Stay]:

Customizing an Existing Window

Our hotel application uses the existing Business Partner window but requires less functionality than is originally provided by Openbravo ERP. The following changes are required:

Hide (deactivate) these tabs:o [Withholding]o [Product Template]o [Volume Discount]o [Shipment Route]

Within the main [Business Partner], hide these fields:o Summary Levelo Expected Lifetime Revenueso Shareo Volume of Saleso Acquisition Cost

Within [Contact], move the Email field up so that it will be located just after the User field

Using the Window, Tab and Field window, make the necessary changes, thus changing the core. Disabling the first tab [Withholding] should be done like:

Do the rest of the listed above.

Because we have an existing template in development, changing the core is allowed and the changes made will be exported to an XML file called a configuration script. Upon exporting and packaging the template, this configuration script will also be included.

Compilation

To reflect the changes, the changed window needs to be regenerated, recompiled and redeployed. The following tasks get the job done:

    $ ant smartbuild

Then, restart Tomcat, e.g.:

    $ su    $ /etc/init.d/tomcat restart    $ exit

Log in again and navigate to the Business Partner window from the menu and see the

changes:

Finishing Touches

You might have noticed a couple of things:1. The guest rate inside [Stay] does not get inherited from the guest's rate2. The dropdown of rooms shows all rooms instead of just the ones that

are available3. The Final Sum field doesn't need to be shown until Date Out is entered since it

cannot be calculated beforehand.4. The DocumentNo for each guest should be an automatic sequence5. The Guest Rate field in the [Guest] can be edited by anyone. While anyone

should be able to see this field we want only the manager to have the option of editing it.

6. The Document No. label inside [Guest] doesn't represent the field well within this context. Let's change it to something more meaningful.

Let's fix the above issues one by one.

1. Calculated Default Values

In order for the Room_Rate field in [Stay] to inherit the value from the guest, we need to define it's default value. To do so, switch back to System Administrator role and find the field definition within the Table and Column window. Then enter the following as its Default Value: @SQL=SELECT Guest_Rate AS Room_Rate FROM HOTEL_GUEST WHERE HOTEL_GUEST_ID=@Hotel_Guest_ID@

We will wait with the compilation until all fixes are applied to save some time not having to recompile each time.

2. Validation

For filtering the dropdown of rooms, a new Validation must be created and associated with the field that the dropdown represents. First, let's add a new validation using the Application Dictionary || Setup || Validation Setup:

Having the SQL statement 'Hotel_Room_Id NOT IN (SELECT Hotel_Room_Id FROM Hotel_Stay WHERE Date_Out IS NULL)', the dropdown is filtered to only rooms for which no stay exists with an empty Date Out.

Secondly, we need to associate this new validation with the correct field of the Hotel_Stay table:

Again, we will wait with the compilation until the end of all fixes.

3. Display Logic

Until Date_Out field is entered, the final sum cannot be calculated thus the Final_Sum field would be better not shown (hidden). It is easy to do that by setting the Display Logic of this particular field inside the Window, Tab and Field window:

By setting the condition @Date_Out@!'' (two single quotations) the Final_Sum field will only be shown when Date_Out is not empty. Referencing other fields of the underlying table of a tab is done using the name of the column, capitalizing the first and any character that  follows the underscore sign (_) , prefixed and suffixed by the @ character. This way, the date_out table column is referenced by using @Date_Out@. The rest of the expression is NOT EQUAL (!) comparison with an empty string ''.

The following syntax elements can be used: @Column_Name@ - reference to another field/column on the same tab or to a

session variable ! - NOT = - EQUALS & - AND | - OR

4. Document Sequences

We would like the DocumentNo field inside [Guest] to be filled in automatically with a generated sequence number. The first condition is already met by calling the table column DocumentNo. The final step is to double check for an existing sequence called DocumentNo_Hotel_Guest inside the Financial Management || Accounting || Setup || Document Sequence window. First of all, switch to the Green Terrace Hotel Admin role since sequences apply to a specific client and not the system. If one does not exist yet, create it by hand as indicated:

Openbravo ERP automatically recognizes the column name DocumentNo (it's a naming convention you must stick with) and the document sequence with the name indicated above and will automatically enter the next value inside the DocumentNo field when creating a new guest (in this particular case, the next Document No for a new guest would be G1).

5. Read Only Logic

Finally, setting conditional read-only privileges for a field based on a role is a bit of a trick. First of all, we need to add an Auxiliary Input that will make AD_ROLE_ID of the currently logged in user available to the Guest window. Using the System Administrator role navigate to the Application Dictionary || Setup || Auxiliary Input and create a new record as shown below:

This will make the #AD_ROLE_ID session variable available to the [Guest] tab of the Guest window through the @HOTEL_ROLE_ID@ session variable.

Secondly, you need to find out what the AD_ROLE_ID of the Manager role is. Use the PgAdmin to query the AD_ROLE table and find that out. A simple query reveals the following:

 openbravo=# select ad_role_id, name from ad_role; ad_role_id | name ----------------------------------+--------------------------- 0                                 | System Administrator 1000000                           | Big Bazaar Admin 1000004                           | Sales&BOM 1000001                           | Big Bazaar User C1C164370D044C5BBE24745C957A10D7  | Green Terrace Hotel Admin

 054A32701D6D4CE6BF4F695DAB23EDB3  | Manager(6 rows)

The primary key (AD_ROLE_ID) of the Manager role is 054A32701D6D4CE6BF4F695DAB23EDB3. This will clearly be different in your case.

With this information, we can now find the Guest Rate field definition and set its Read Only Logic to @HOTEL_ROLE_ID@!'054A32701D6D4CE6BF4F695DAB23EDB3' as shown below:

6. Changing Label(s)

The DocumentNo field of the Guest entity has been "abused" in order for the auto-sequencing to work. However, upon synchronizing the terminology, the label that was inherited does not really fit the context where this field represents the Guest ID presented to the user. In order to change this label, use the Window, Tab and Field window, find the Guest window and within the first tab, find the DocumentNo field. To detach this label from centralized translation deriving from an application element, uncheck the Central Maintenance check and enter the desired label into the Name field:

Whereas the DocumentNo field inherited its label from an existing application element from which we have detached it since the label in our guest context should be different, the columns First_Name and Last_Name were nonexistent prior the creation of the Hotel_Guest table. Upon triggering the Synchronize Terminology process, two new corresponding application elements were created for the two columns. To translate these two fields, navigate to the underlying column of the field:

and from there to the Application Element where the label should be changed:

Do the same for Last_Name field.

Compiling

To reflect the changes, the application (only the Guest window) needs to be recompiled

    $ ant smartbuild

and Tomcat restarted.

Final Result

Our four finishing touches should now be reflected within the Guest window:1. When entering a new record inside [Stay] the Room_Rate gets inherited from

the guest's rate2. The dropdown of rooms shows only available rooms3. The Final Sum field doesn't show until Date Out is entered4. The Guest_Rate field in the [Guest] is read-only unless Manager role is used5. Adding a new guest automatically enters the next DocumentNo

Exporting Developments

By now we have made quite a few new developments as well as changed an existing Business Partner window for the new Hotel Management module. At this point, all our changes have been done to the application dictionary which means they are located inside the AD tables of the physical database. They are difficult to source control or distribute this way. To overcome this issue, Openbravo ERP comes with ant tasks that export all changes made as part of the module currently marked as In Development to XML files:

1. Export definition and data of all modules that are set as In Development, in our case, the Hotel Module and Hotel Template's definition and the new developments that belong to the Hotel Module

    $ ant export.database

2. Export changes to the core belonging to the template currently In Development

    $ ant export.config.script

As a result of executing these commands, two subfolders will be created inside the modules folder

1. com.yourcompany.hotel.module - this contains the module's definition and all the new elements you have developed so far (such as new tables, windows, fields, labels, etc)

2. com.yourcompany.hotel.template - contains only the template definition and the configuration script (modules/com.yourcompany.hotel.template/src-db/database/configScript.xml) describing changes to the core you have made (such as changes to the Business Partner window):

This way, alll our developments are clearly separated from the core or any other modules.

Additional Exercises

Adding a Read-Only Stay Tab to the Room Window

Say we would like to see all stays for a particular room. The easiest way to implement this feature in our Hotel scenario is to add a read-only tab to the existing Room window that right now only contains one tab. To do so, use the Window, Tab & Field window, find the Room window definition and add a new tab, based on Hotel_Stay table, marking it as Read Onlywith the UI Pattern dropdown:

Use the Create Fields button to automatically insert fields into the new tab based on the underlying column definitions. Use the [Field Sequence] to arrange the fields in the usual and friendly way.

Since our Hotel_Stay table does not know yet about this use in a child tab of the [Room], we need to make sure we mark the link between Hotel_Room and Hotel_Stay as a Link to Parent Column. Navigate to the Hotel_Stay table definition, find column Hotel_Room_ID and mark it as Link to Parent Column.

To reflect the additional tab, the Room window needs to be recompiled:

    $ ant smartbuild

After restarting Tomcat and logging in again, our Room window should now also contain the new read-only tab, listing all stays of the room selected:

Further Reading

http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/ How_to_change_a_current_window

http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/ How_To_Create_a_Table

How to create a new table

First of all, the underlying database structures that will hold the data need to be created. In other words, using a database administration tool (e.g., pgAdmin III or phpPgAdminfor PostgreSQL and Oracle SQL Developer or Toad for Oracle) one needs to first CREATE TABLEs that will be used to hold data of the new window/tabs.

Objective

Imagine we are developing an HR module and we need a window that will enable the user to input salaries of employees. We also need to track the employee's salary so history records need to be preserved. Each salary record needs to have a Valid From Date field that indicates when a particular salary came into being. The record belonging to a particular employee with the latest Valid From Date is the salary that is valid today. Note that employees are already inside the system contained in the C_BPARTNER database table and indicated by the C_BPARTNER.ISMEPLOYEE column. Therefore, we only need to create a database table that will hold the actual salaries.

Modularity

All new developments must belong to a module that is not the core module. Please follow the How to create a new module section of the Modularity Developer's Manual to create a new module.

Once you have registered the module, you need to decide on the database prefix that will indicate DB items that belong to this module. This is done by adding DB prefix(es) to the module. That way, any database artefact(table, trigger, stored procedure) that belongs to that module will need to have the name prefixed with it. In our case, add the HR DB prefix.

Finally, the data package needs to be entered in the Data Package tab of the Module window. Enter a new record there with HR Data as the Name and {modulePackage}.data(note that this package must be a subpackage of the one you entered on the level of module), for example org.openbravo.howtos.data in case org.openbravo.howtos is the package of the module.

Create new tables in the database

Let's introduce a new database table called HR_SALARY that will hold the required data. Notice the HR prefix of the table name indicating the module this table belongs to.

The new HR_SALARY table must include the AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated and UpdatedBy fields that are mandatory and required for security and auditory purposes of the application.

Column name Type Length Note

HR_SALARY_ID CHAR 32The primary key of the table that must follow the table name followed by the _ID.

AD_CLIENT_ID CHAR 32Indicates which client (company) the record belongs to (multitenancy).

AD_ORG_ID CHAR 32Indicates which organization (city/department/location) within a client a record belongs to.

ISACTIVE CHAR 1This is intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.

CREATED DATE Date/time of creation of a record.

CREATEDBY CHAR 32Foreign key to AD_USER indicating the user that created this record.

UPDATED DATE Date/time of last update of a record.

UPDATEDBY CHAR 32Foreign key to AD_USER indicating the user that last updated this record.

C_BPARTNER_ID CHAR 32 Employee this salary belongs to.

AMOUNT NUMBER 10 The actual amount of the salary.

C_CURRENCY_ID CHAR 32Foreign key to C_CURRENCY indicating the currency the amount is in.

VALIDFROM DATE Date that this salary is valid from.

To create the above table within the database, use one of the following CREATE TABLE statements depending on the DB you are using:

PostgreSQL

CREATE TABLE HR_SALARY(

HR_SALARY_ID CHARACTER VARYING(32) NOT NULL, AD_CLIENT_ID CHARACTER VARYING(32) NOT NULL, AD_ORG_ID CHARACTER VARYING(32) NOT NULL, ISACTIVE CHARACTER(1) NOT NULL DEFAULT 'Y', CREATED TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), CREATEDBY CHARACTER VARYING(32) NOT NULL, UPDATED TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), UPDATEDBY CHARACTER VARYING(32) NOT NULL, C_BPARTNER_ID CHARACTER VARYING(32) NOT NULL, AMOUNT NUMERIC NOT NULL, C_CURRENCY_ID VARCHAR(32) NOT NULL, VALIDFROM TIMESTAMP WITHOUT TIME ZONE NOT NULL, CONSTRAINT HRSALARY_ISACTIVE_CHECK CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT HR_SALARY_KEY PRIMARY KEY (HR_SALARY_ID), CONSTRAINT AD_ORG_CUS_PROJECT FOREIGN KEY (AD_ORG_ID) REFERENCES AD_ORG (AD_ORG_ID), CONSTRAINT AD_CLIENT_CUS_PROJECT FOREIGN KEY (AD_CLIENT_ID) REFERENCES AD_CLIENT (AD_CLIENT_ID), CONSTRAINT C_BPARTNER_HR_SALARY FOREIGN KEY (C_BPARTNER_ID) REFERENCES C_BPARTNER (C_BPARTNER_ID), CONSTRAINT C_BPARTNER_C_CURRENCY FOREIGN KEY (C_CURRENCY_ID) REFERENCES C_CURRENCY (C_CURRENCY_ID));

Registering the table within the Application Dictionary

The following steps register the newly created table within the Openbravo ERP Application Dictionary.

For this purpose, first log into Openbravo ERP using a username with access to System Administrator role. Navigate to Application Dictionary || Tables and Columns and create a new record as shown in the screenshot below:

Main fields of this window are (for more information see the AD_Table table description):

Data Package specifies to which java data package within the module the table will belong when used within [ERP/2.50/Developers_Guide/Concepts/Data_Access_Layer DAL] (Data Access Layer).

Name Defines the name that Openbravo ERP uses to recognize the defined database table. This name is used in REST webservices and in the Data Access Layer. Seehere for more information.

Description Gives a small description of the table. Help/Comments Defines the text that is displayed in Help window. DB Table name Defines database table name as it was defined by the CREATE TABLE during its

creation. Java Class Name This will be the actual Java class within the Data Package of the module

through which you will be able to access this table when using DAL. Data Access Level determines what kind of data will the table contain due to the multitenancy

functionalityo System only: Only system records can be inserted into this table (AD_CLIENT_ID=0,

AD_ORG_ID=0), for example AD_TABLE.o System/Client: System or client specific records can be inserted here

(AD_CLIENT_ID=anything, AD_ORG_ID=0), for example AD_ROLEo Organization: Only client and organization specific data can be inserted into this table

(AD_CLIENT_ID<>0, AD_ORG_ID<>0), for example C_INVOICEo Client/Organization: Only client specific data can be inserted here, however, it can

belong to a specific organizations within that client or be shared among all (AD_CLIENT_ID<>0, AD_ORG_ID=anything), for example C_BPARTNER

o All: Any combination of AD_CLIENT_ID and AD_ORG_ID can be inserted into this table.

Save this record then press Create columns from DB button to create columns within the Column tab automatically.

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

Switch to Column tab to see all the columns (for more information see the AD_Column table description) that were created according to their definition within the database. You can now additionally alter the properties of each column. Each column is assigned a reference (which defines the data type) depending on its name and its data type. RunSynchronize Terminology process (Application Dictionary || Synchronize Terminology).

This process tries to find an existing application element (within the currently developed module) and thus its label, help and description and if one is not found, a new one is created. This enables a centralized translation of the application/module.

Each table must have at least one column marked as an identifier. The actual values of identifier columns later get concatenated to be shown to the user as a representation of a particular record (see the link to the Sales Order within the Sales Invoice window). These identifiers will also be used to construct dropdown lists of records of that particular table. By default all columns with column name Name are set as an identifier. In case there is no column with this Name, no identifier is set and needs to be done so manually or compilation will fail.

The name is used by the Data Access Layer and in REST webservices. For specific columns (audit info, client/organization, active) it is important to be precise in the naming. Seehere for more information.

  NOTE: The columns that are named line or seqNo are used to contain the sequence number of a record (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@

The WHERE part of this clause needs to be replaced with the required values. The code that should appear here is the name of the column which links with the id of the parent one. For example, each record of the C_InvoiceLine belongs to a particular C_Invoice record and they are all sequenced. C_Invoice is the parent table for the lines saved in C_InvoiceLine. This 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 C_INVOICE_ID=@C_INVOICE_ID@

Most of the columns in our specific HR_SALARY case will be automatically detected correctly, however, some need revising:

Amount: Reference = Amount, Length = 10 C_BPartner_ID: Reference = Search, Reference Search Key = Business Partner, Length = 32,

Link To Parent Column = Y Valid From: Identifier = Y Amount: Identifier = Y

Openbravo ERP now knows about the new HR_SALARY database table and how to treat it in terms of its definition and the representation to the user.

Introduction

Application elements (windows, tabs and fields) are liable to change repeatedly during the development or maintenance phases of a project. Openbravo ERP is able to cope with these changes because its architecture is suited to iterative development. The definitions of all generated Windows, tabs and fields are stored as metadata in the Application Dictionary (AD).

Changing the window of an existing application is a simple 2 step process comprising of two tasks: AD definition and then WAD generation.

1. Use Openbravo's declarative UI to make changes to AD definition.2. Generate the working application using the Wizard for Application Development (WAD).

The generation process can be performed for the whole application or at a more granular level for individual windows.

Objective

The objective of this how-to is to illustrate how to make changes to existing generated windows in terms of appearance and behavior. The window used in the example is the Physical Inventory window and the changes illustrated will be:

Hide a field Re-sequence the layout

Physical Inventory is a window that belongs to the Openbravo ERP core module. It comprises of:

1 Window - Physical Inventory. 2 Tabs - Header and Lines. A Header Tab has 16 Fields 13 of which are displayed.

Before any changes the header tabs has the following appearance:

  NOTE: Through modularity included in 2.50 release you can adapt Openbravo core to your unique needs through external modules, without the need to customize the code. Openbravo strongly recommends this way to highly improve maintenance. In this example, to do changes in the metadata of other module (including core) you have to create an Industry Template, put it in development, make it dependent on the core module and put the core also in development. Its configuration script will hold these changes in an external, decoupled manner so patches can be applied without any risk of conflict. The easiest way to do it is to go to General Setup/Application/System Info window and check the "Customization Allowed" field. It will automatically create an Industry Template in your system where customizations will be saved

Changing the window

Navigate to the 'Windows, Tabs and Fields' window and select the record for 'Physical Inventory'.

Navigate to the 'Field Sequence' tab.

Hide a field

Hide the 'Description' field by moving the field from the right panel (displayed) to the left panel (hidden) using the horizontal control buttons.

Save your change. You should now see the following. Alternatively you can control the display of a field through the 'Active' field in the 'Field' tab:

Re-sequence the layout

To change the sequence so that 'Movement Date' becomes the first field displayed use the vertical control buttons.

Save your change. Your window should display:

Compiling the Window

Finally, for the callout to take effect, the window that uses it needs to be recompiled and deployed to Tomcat. If using Eclipse, use the eclipse.compile ant task and enterProduct into the dialog that pops up. If manually compiling Openbravo ERP, use the ant compile.development -Dtab='Physical Inventory'

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

To make generate the script with the changes execute the export.config.script task. Open a command window/shell and go to the Openbravo ERP development project and then execute the following task:

ant export.config.script

See more on build tasks.

The Result

You can see in the resulting window the 'Description' field is no longer displayed and the the 'Movement Date' is the first field displayed. It's as easy as that! No code changes are needed to make these sort of window layout changes.

http://wiki.openbravo.com/wiki/Image:PhysicalInventoryAfter.png

Objective

The objective of this how-to is to give you a detailed understanding of how to create your own stored procedure and how to call a stored procedure from a menu or window.

Stored procedures are blocks of code that form some of the business logic of Openbravo ERP. They are stored directly within the database which makes them database engine specific and dependent. Openbravo ERP currently only supports Oracle and PostgreSQL databases which have a very similar Procedural Language syntax.

NOTE: This howto only provides code for a PostgreSQL solution. Details on main Oracle vs Postgres can be found here: Oracle vs Postgres SQL code rules.

To be able to browse the database and view existing stored procedures as well as create new ones use one of the database administration tools (e.g., pgAdmin III or phpPgAdminfor 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.

In this article we will be adding a new button that recalculates the standard price of a product based on the average of all purchase or sales orders. Using the Openbravo Adminrole, navigate to Master Data Management || Product window, select a product, e.g. Beer and switch to 'Price tab. There you will find all prices (purchase and/or sales) that this particular product has, each one attached to a price list version which in turn is part of a price list. Notice how each product has three prices:

List price - the catalog price, usually the highest one. Standard price - what actually gets charged to the client within a purchase or sales invoice. Limit price - what the sales agent can lower the price down to.

Let's say we want to have a button in this tab, that recalculates the standard price based on the average of all purchase or sales orders within the last X days, where X should be a parameter that appears in a pop-up and can be entered by the user, having the default value of 180 days (6 months).

The business logic behind this button will be written in PostgresSQL Procedural Language and be part of a stored procedure that must belong to our custom module.

Module

All new developments must belong to a module that is not the core module. Please follow the How to create and package a module section to create a new module.

  This article assumes that the HT DB prefix has been defined within the module that is in development. Consequently, the name of the stored procedure will have to start with HT_!

Defining it inside the application dictionary

We will approach the development of our stored procedure in a reverse step fashion. Let's see first how the user interface relates to a stored procedure call.

For Openbravo ERP to know about a particular stored procedure inside the database it must be defined within the application dictionary. To do that, using System Administratorrole, navigate to Application Dictionary || Report and Process window and create a new record as indicated below:

Important fields to note are (for more information see the AD_Process table description):

Search Key - Unique identifier of this process. Name - A user friendly name of this process. Data Access Level - indicates who will be able to access this process. By selecting

Client/Organization, the System Admin will not be able to access this process. UI Pattern - since the logic and the user interface behind the button will be generated by

Openbravo ERP (a pop-up will be generated with an input field for the Days of History), the Standard option should be selected here versus the Manual where the developer must create the entire user interface (controller, view, etc.).

Procedure - the name of the procedure as it is/will be inside the database. Note the HOWTO prefix.

Since we want to input a parameter (the number of days the system should look back for when calculating the average price), we need to specify it within the Parameter tab as shown below:

Fields to note are (for more information see the AD_Process_Para table description):

Name - user friendly name of the parameter that will appear next to the input field of the user interface.

DB Column Name - this name does not represent the meaning enough but this is the name of the parameter that will have to be parsed inside the stored procedure.

Reference - the data type of the input field. Integer in our case means Openbravo ERP will offer a little input field which a calculator assistant next to it. It will also indicate that the field validation for an integer number needs to be performed.

Reference Search Key - if we had selected a non-basic reference above (such as List, Search or Table), this drop-down lists available sub-references.

Length - the number of characters/numbers that can be input. In our case, we are not expecting more than a 4-digit integer.

Mandatory - fields marked as mandatory must have a value before proceeding with the execution of a process.

Default Value - default value (if any) of this parameter. As defined within our scenario, we would like to calculate history for the last 180 days.

Now, Openbravo ERP knows about a process defined by the stored procedure HT_CALCULATE_AVG_PRICE. Thus, we can call it from a button on a window or through a menu item.

Associating it with a button

Before we can place a button onto the Price tab of the Product window, we need to have a physical placeholder within the database, even if it is not going to hold any data.

Therefore, we need to add a column to the m_productprice table that the Price tab is based on. To do that, execute the following PostgreSQL statement using a Pgadmin tool or similar:

ALTER TABLE m_productprice ADD COLUMN em_ht_calculate_avg_price CHARACTER(1) NULL DEFAULT '';

Due to modularity conventions, columns added to core tables must be prefixed by EM_ on top of the regular module prefix (HT_ in our case). See Table and Column section of the Modularity Developer's Guide for more information on this requirement.

For Openbravo ERP to know about the new physical table column, it needs to be introduced into the application dictionary. Navigate to Application Dictionary || Table and Column, find the M_ProductPrice table (Name = PricingProductPrice), select the Column tab and add a new record as shown below:

The following fields are of importance when adding a new column to the application dictionary (for more information see the AD_Column table description):

Module - the module this additional column belongs to. Only one module should be marked as In Development so this drop-down should be automatically preselected.

DB Column Name - the name of the table column within the physical database. Name - user friendly name of this column. Note that it MUST be prefixed by EM (since it is an

added column to an existing table) and HT as the DB prefix of the module, hence in the form of EM_HT_<custom name>.

Length - maximum length according to the data type of this column. Reference - data type of the column. Based on this selection the user interface of this field is

rendered to the user. In our case a button will be shown. Process - when a Button is selected in the Reference drop-down, the list of all processes is

given here. Selecting the new Calculate Average Price process will cause it to be executed upon the push of this button.

Finally, in order for the button to actually appear to the user, we must add it to the corresponding window. In our case, the button should appear inside the Price tab of theProduct window. Hence, navigate to Application Dictionary > Window, Tab and Field, find the Product window and select the Price within the Tab tab. Then, within the Fieldtab, add a new record as indicated below:

For more detailed information see the AD_Window, AD_Tabe and AD_Field table descriptions.

Associating it with a menu item

Stored procedures can also be called standalone from the menu. However, in that case, the Record_ID column of the AD_PInstance table will be NULL (discussed in the next section) since there is no record where the button is triggered from.

To create a new menu item and associate it with a stored procedure, use the System Administrator role to navigate to General Setup > Application > Menu and create a new item:

By switching the role to Openbravo Admin you should see the new menu item on the root level:

Again, keep in mind that this approach will not work in our case since we plan to develop a stored procedure that will recalculate the average of a specific price (hence a specific Record_ID). But we could extend our procedure so that it checks for the absence of the Record_ID and in that case recalculates all prices for all products.

Also, in order to see the parameter pop-up, the application needs to be recompiled.

Theory of stored procedure calls

With the steps taken above, the push of the newly defined button will pop up a window with the parameter field to be entered and upon confirmation trigger the stored procedure we're about to write.

Having said that, there are a few specifics to the stored procedure calls that the developer needs to know about so let us present a bit of theory behind it.

This is what happens:

1. when a button or the menu item is clicked, a popup shows up, listing all parameter input fields and offering the confirm OK button below. Keep in mind that this popup was/will be automatically generated by the compilation process.

2. Upon the click of the OK button, Openbravo ERP application will automatically do the following:1. enter one record into AD_PInstance table, logging the call to the specific process2. enter as many records as there are parameters defined for this process into

the AD_PInstance_Para table, storing the selected values of the parameters entered by the user.

3. The stored procedure is then called with one and only one parameter: AD_PInstance_ID, indicating which rows of the two tables contain all information about the call

As indicated above, the AD_PInstance and AD_PInstance_Para tables act as an intermediary between the Openbravo ERP generated user interface and the actual procedure. Hence, the stored procedure does not get the parameters passed directly with the call and does not return the result back explicitly. The two tables are used instead and onlyAD_PInstance_ID is passed. Using this parameter, the stored procedure is responsible for retrieving the corresponding records inside the two tables that belong to that specific call. Moreover, the result of the stored procedure should be saved into the AD_PInstance table as opposed to be returned directly using the RETURN statement. Consequently, the two tables also act as a log of all calls.

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 entered for the parameters defined in theParameters tab of the Reports and Process window of the correspondent procedure.

AD_PInstance table:

AD_PInstance_ID: Table identifier. AD_Process_ID: Foreign key to the AD_Process table where the procedure is defined in the

application dictionary. Record_ID: If the procedure is called from a window, this column stores the ID of the active

record in that window where the button was pressed from. IsProcessing: While the procedure is running this column is set to 'Y'. Some procedures may

and do check if there is an ongoing instance of a call. AD_User_ID: ID of the user that triggered the call. Result: 0 Indicates an error during the call, 1 indicates a successful call of the process

and 2 indicates a warning. ErrorMsg: When the procedure finishes, the resulting message needs to be stored here,

success or error. This message will be shown to the user.

AD_PInstance_Para table:

Parametername: This column will contain the name of the parameter corresponding to the DB Column Name value set within the Parameter tab of the Report and Process window.

P_String and P_String_TO: Selected/entered values when the parameter is a text box, a drop down list or a foreign key.

P_Number and P_Number_TO: Selected/entered values when the parameter is a numeric text box.

P_Date and P_Date_TO: Entered values for date type parameters.

The _TO suffix columns are used to contain values for when a parameter is defined as a range as opposed to a single value. The generated pop-up window with the parameters will include two fields with the from and to labels. Later those values can be used to execute queries between those values.

Input parameters of procedures

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

If a stored procedure is going to be called from the application but also from another stored procedure we need an intermediary procedure since in that case there is usually no corresponding AD_PInstance_ID.

See C_Order_Post and C_Order_Post1 as an example. The main procedure (C_Order_Post1 in this case) will have as many input parameters as it requires to do its job, plus the AD_PInstance_ID. It must then contain the logic that checks for presence of AD_PInstance_ID and if present, take parameters from the two tables. Otherwise, it will take the parameters explicitly passed to it by another stored procedure and bypass the storage of the results into the AD_PInstance.

The intermediary procedure (C_Order_Post in this case) is the one defined withing the Application Dictionary and only has a parameter for the AD_PInstance. This one then calls the main one forwarding the AD_PInstance_ID parameter and setting all the others as NULL.

AD_Update_PInstance stored procedure

This procedure updates a specific AD_PInstance record. It needs to be called at the beginning and at the end of the body of any custom stored procedure.

Parameters to the AD_Update_PInstance:

p_PInstance_ID: AD_PInstance_ID that needs to be updated. p_AD_User_ID: AD_User_ID that is doing the update. p_IsProcessing: Status of the procedure ('Y' or 'N'). p_Result: Final result of the proceudre (0 for fail - red alert box, 1 for success - green alert box

or 2 for warnings - yellow alert box). p_Message: Error or success text message of the procedure.

Exception block and error management

Potential error messages of a procedure must be properly managed by an exception block while at the same time registering the outcome into the Result and Errormsg fields of the AD_PInstance table using the AD_PInstance_Update stored procedure.

If a problem occurs within the body of your stored procedure use the RAISE_APPLICATION_ERROR to indicate it and interrupt the procedure. A custom error number -20000 can be used.

To return a user friendly error message correctly translated into the language of the current user do not hardcode messages into your stored procedures. Instead use placeholders within the @ sign that contain the actual text inside the AD_Message table. For example, by saving the @MissingDaysHistoryParameter@ into the Errormsg column of the AD_PInstance table, Openbravo ERP will go into the AD_Message table and try to find the text in the correct language for the key MissingDaysHistoryParameter. Of course, custom messages need to be entered and translated using the Application Dictionary > Messages window.

If the procedure will also be called from other procedure(s) apart from directly by Openbravo ERP, the exception block needs to distinguish between the two possibilities. This condition can be worked out by checking the value of the AD_PInstance_ID parameter (NULL in case of call from another procedure).

If called from another procedure, the exception raised should be managed by the parent procedure where the AD_PInstance_ID is available. This does not apply to the intermediary procedures explained above. These don't have the exception block so the exception is managed by the main one.

Before raising an exception using the RAISE statement, it is a good practice to use a DBMS_OUTPUT.PUT_LINE(); (Oracle) or RAISE NOTICE (Postgres) for debugging purposes.

Example of an exception block in a procedure that is only called from another one:

Oracle:

  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;

PostgreSQL:

  EXCEPTION

WHEN OTHERS THEN RAISE NOTICE '%','ERROR MA_Standard_Cost_Sequence, sequence_ID '|| p_Sequence_ID || ', date ' || p_CalcDate || ' at ' ||v_ResultStr; RAISE EXCEPTION '%', SQLERRM; END ; $BODY$

When the AD_PInstance exists (i.e. the stored procedure will always be called from within the application), the exception needs to be managed slightly differently in order to store the result and the message. The message is built by concatenating the text @ERROR= with the SQLERRM variable that contains the error message that is thrown by theRAISE_APPLICATION_ERROR or by the database itself. A ROLLBACK; is done (PostgreSQL does not need it explicitly) and finally the AD_PInstance is updated setting the Resultcolumn to 0 (indicating a fail) and Errormsg column to the constructed error message. It is also recommended to use DBMS_OUTPUT.PUT_LINE(); (RAISE NOTICE in PostgreSQL) for debugging purposes.

Oracle:

  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;

PostgreSQL:

  EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%',v_ResultStr ; v_ResultStr:= '@ERROR=' || SQLERRM; RAISE NOTICE '%',v_ResultStr ; PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ; RETURN; END ; $BODY$

Developing the actual procedure

The actual procedure that performs the task is listed below in PostgreSQL specific code with comments. One can just run it inside a Pgadmin to create the actual stored procedure inside the Openbravo ERP database.

  CREATE OR REPLACE FUNCTION ht_calculate_avg_price(p_pinstance_id character varying) RETURNS void AS $BODY$ DECLARE -- variables that will contain the parameters deriving from the AD_PInstance table v_Record_ID VARCHAR(32); -- ID of the record inside m_productprice that we are processing

v_DaysHistory NUMERIC; -- number of days to consider within the calculation -- operational variables v_ResultStr VARCHAR(2000):=''; -- will contain text describing the stage the stored procedure is in v_Message VARCHAR(2000):=''; -- will contain the final message to be logged v_MProductId VARCHAR(32); -- ID of the product that we are processing v_IsSOPriceList VARCHAR(1); -- flag indicating a sales or a purchase price v_SumPrice NUMERIC; -- sum of all line amounts that included the specific product v_SumQty NUMERIC; -- sum of all line quantities that included the specific product Cur_Parameter RECORD; -- cursor variable to loop through all parameters BEGIN -- Update AD_PInstance by setting IsProcessing='Y' RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ; PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ; -- Get Parameters v_ResultStr:='ReadingParameters'; FOR Cur_Parameter IN (SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date FROM AD_PInstance i LEFT JOIN AD_PInstance_Para p ON i.AD_PInstance_ID=p.AD_PInstance_ID WHERE i.AD_PInstance_ID=p_PInstance_ID ORDER BY p.SeqNo) LOOP v_Record_ID:=Cur_Parameter.Record_ID; -- save the m_productprice primary key IF (Cur_Parameter.ParameterName='DaysHistory') THEN v_DaysHistory:=Cur_Parameter.P_Number; END IF; END LOOP; RAISE NOTICE '%', 'Record_ID = ' || v_Record_ID ; RAISE NOTICE '%', 'DaysHistory = '||v_DaysHistory; BEGIN --BODY -- Retrieve missing information regarding which product and what type of transaction (purchase/sales) SELECT m_productprice.m_product_id, m_pricelist.issopricelist INTO v_MProductId, v_IsSOPricelist FROM m_pricelist, m_pricelist_version, m_productprice WHERE m_pricelist_version.m_pricelist_id=m_pricelist.m_pricelist_id AND m_productprice.m_pricelist_version_id=m_pricelist_version.m_pricelist_version_id AND m_productprice_id=v_Record_ID;

RAISE NOTICE '%', 'ProductId = '||v_MProductId; -- Calculate average sales/purchase price for the product based on the DaysHistory parameter SELECT SUM(c_invoiceline.priceactual), SUM(c_invoiceline.qtyinvoiced) INTO v_SumPrice, v_SumQty FROM c_invoice, c_invoiceline WHERE c_invoice.c_invoice_id=c_invoiceline.c_invoice_id AND c_invoice.issotrx=v_IsSOPriceList AND c_invoiceline.m_product_id=v_MProductId AND c_invoice.dateordered>=(now()-(v_DaysHistory||' days')::INTERVAL) GROUP BY c_invoiceline.m_product_id; RAISE NOTICE '%', 'SumPrice = '||v_SumPrice; RAISE NOTICE '%', 'SumQty = '||v_SumQty; -- Update Standard price with the new number IF (v_SumPrice IS NOT NULL AND v_SumQty IS NOT NULL) THEN UPDATE m_productprice SET pricestd=(v_SumPrice/v_SumQty) WHERE m_productprice_id=v_Record_ID; v_Message:='@HT_SPUpdatedPrice@'||v_SumPrice/v_SumQty; -- @HT_SPUpdatedPrice@ should be added to the AD_Message table with some text -- like 'Updated price to ' ELSE v_Message:='@HT_SPNoTrx@'; -- @HT_SPNoTrx@ should be added to the AD_Message table with some text -- like 'No transactions found hence no update to price performed.' END IF; -- Successfully finish the process by updating AD_PInstance, setting the -- IsProcessing, ErrorMsg and Result RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ; PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ; RETURN; END; -- BODY EXCEPTION WHEN OTHERS THEN v_ResultStr:= '@ERROR=' || SQLERRM; RAISE NOTICE '%',v_ResultStr ; PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ; RETURN; END ; $BODY$ LANGUAGE 'plpgsql' VOLATILE;

Compiling the Corresponding Window

Before we can see the button inside the Price tab of the Product window, we need to compile it. To do so from the command line, type:

ant compile.development -Dtab=Product

This will compile the Product window and all its sub-tabs. You might have to restart Tomcat depending on your configuration. If using Eclipse, use the eclipse.compile ant task and enter the name Product inside the pop-up that appears.

The Result - Part I

Log into Openbravo ERP and after choosing the Openbravo Admin role, navigate to Master Data Management > Product window, select the Boots product and switch to the Price tab.

Find the price that belongs to the Sales 2006 Price List Version. Double click it and you should see the button:

Notice the Standard Price which is at this point set to 40. Click on the Calculate Average Price button to pop-up the parameter entry and confirmation of our stored procedure execution. Because the most of demo database (SmallBazaar or BigBazaar) transactions are way in the past (in year 2006 etc), enter 2000 (days) here to make sure they get accounted for.

By confirming the dialog, the stored procedure will be executed with the parameter entered and the result should be similar to:

The Standard Price has been recalculated according to the transaction that exist within the system.

However, the resulting message is not very descriptive since it shows only the placeholder (HT_SPUpdatedPrice) that the stored procedure generated. To see the actual message, we need to enter it into the Messages window.

Entering Custom Messages

Since our stored procedure returns two custom messages depending on the outcome of the process (HT_SPUpdatedPrice and HT_SPNoTrx) we need to enter the actual text for these placeholders. These texts are stored in the AD_Message table.

To do so, navigate to Application Dictionary || Message using the System Administrator role and enter a new one as indicated below:

Note that no @ signs should be included here. Those are only used inside the stored procedure to indicate the message identifier.

Now do the same for the second message, this time using HT_SPNoTrx placeholder for the message.

The Result - Part II

By re-running the Calculate Average Price with the same parameter, you should now see the full message as opposed to the placeholders seen before:

Congratulations!

Openbravo ERP Advanced Development

Chapter 5 – Stored Proceduresv2.50.17

© 2008-2010 Openbravo S.L. All rights reserved. The information in this document is confidential and may not be disseminated or disclosed to third parties (either in digital form or on paper) without the

prior written consent of Openbravo S.L.

1. Reference

2. Introduction 3. Introducing the Procedure to the Application Dictionary 4. The Menu Item 5. The Procedure 6. Recompiling the Application 7. The Result 8. Improvements 1. 1. Return a Custom Success Message 2. 2. Check Threshold Parameter Values

Reference

Before starting with this chapter read the following article(s): http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/

How_to_develop_a_stored_procedure http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/Concepts/

DB_Fundamentals

Introduction

Much of the Openbravo ERP's business logic and functionality is implemented as stored procedures. They can act as processes behind buttons on a window, menu items or be called from background processes.

In our specific case, we would like to develop a stored procedure that will be called from the menu and will loop through all guests, calculating their rating (Guest_Rate) based on how many nights they have stayed in the hotel within the last 6 months (e.g. 10 nights or more in the past 3 months is A rating, 5-10 is B and less is C). The call needs to take two parameters which act as thresholds (in nights stayed) for Rate A and Rate B. Let's call these parameters TresholdA and TresholdB. If the number of nights stayed within the last 6 months for a particular guest does not reach ThresholdA or ThresholdB, C rate is applied.

Introducing the Procedure to the Application Dictionary

Even though we have not yet developed the procedure itself, let's tell Openbravo ERP about it and its parameters. Using the System Administrator role, navigate to Application Dictionary || Report and Process and add a new record:

Fields above will indicate: Procedure - the name of the stored procedure as named within the database.

Note that the name MUST be prefixed with the DB Prefix that we have selected upon creating our Hotel template.

Data Access Level - Client/Organization indicates that this process will only be available to real clients (as opposed to System client)

Secondly, let's enter the parameters that the automatically generated UI will ask for before running the stored procedure:

Fields important to note are: DB Column Name - the name of the parameter as will be expected inside the

actual stored procedure (no spaces here!) Reference and Length - the type of the field that should appear in the UI,

Integer of length 3 is a good choice for entering the number of nights in our case Default Value - the default values of each parameter so we do not have to enter

them each time since most of the time we would use 5 and 10 nights respectively

Run the Synchronize Terminology process due to new parameters that are not yet linked to Application Elements.

The Menu Item

To be able to call the stored procedure from the menu, we need to add a menu item that links to the newly created process created previously. Using the General Setup || Application || Menu window, add a new menu item and place it inside the Hotel node:

The Procedure

Finally, we need to develop the actual stored procedure within the database. Since we are using Postgres, the code is specific for this type of database. We will only give you the actual core of the stored procedure code, the rest should be figured out on your own using the howto and other stored procedures as examples.

 -- Loop through all guests v_ResultStr:='Looping through all guests'; FOR Cur_Parameter IN   ( SELECT Hotel_Guest_Id FROM Hotel_Guest ) LOOP   v_Guest_ID:=Cur_Parameter.Hotel_Guest_ID;   RAISE NOTICE '%',' GuestID=' || v_Guest_ID;   SELECT to_number(sum(date_out-date_in)) INTO v_Nights FROM Hotel_Stay WHERE Date_In > v_DateFrom AND Hotel_Guest_Id = v_Guest_ID GROUP BY Hotel_Guest_Id;   RAISE NOTICE '%',' Nights=' || v_Nights;   -- Set guest rating based on nights stayed within the last X months indicated by v_DateFrom   IF (v_Nights >= v_Threshold_A) THEN     UPDATE Hotel_Guest SET Guest_Rate='A' WHERE Hotel_Guest_Id=v_Guest_ID;   ELSE     IF (v_Nights >= v_Threshold_B) THEN       UPDATE Hotel_Guest SET Guest_Rate='B' WHERE Hotel_Guest_Id=v_Guest_ID;     ELSE       UPDATE Hotel_Guest SET Guest_Rate='C' WHERE Hotel_Guest_Id=v_Guest_ID;     END IF;   END IF; END LOOP;

We'll also give you a hint of how to define and set the v_DateFrom variable:

 v_DateFrom TIMESTAMP := now() - interval '6 months';

Good luck!

Recompiling the Application

In order for the stored procedure's UI to enter parameters and the call itself to be generated, we need to recompile the manual code of the application. This is again done in the same way:

    $ ant smartbuild

This compiles the window named XXX which doesn't exist, hence no windows are compiled. What is compiled is all the manual code which gets compiled every time, regardless of the windows. Restart Tomcat.

The Result

Before we try out our procedure, let's make sure we actually have some data that it can operate on. Let's have at least two guests and entering a two week (13 nights) stay for one of them and a short one night stay for the other one.

Then, by clicking the Calculate Guest Rates menu item the parameter window pops up:

Confirming the above dialogue and reloading the list of guests in grid mode reflects the calculation of our stored procedure. Note how Jane Jensson now has Guest Rate C.

Improvements

Our stored procedure currently has the following flaws:1. No success message is generated2. No checking is done for the threshold parameter values

Let's improve it and solve the issues just mentioned.

1. Return a Custom Success Message

Unless you have done so already, the call to AD_UPDATE_PINSTANCE before exiting the stored procedure can also save a custom message to the AD_PINSTANCE table that is then shown to the user. The following code snippet shows how this can be done:

  v_Message := '@HOTEL_CalculateGuestRates_NoOfGuestsUpdated@' || v_GuestCounter;  RAISE NOTICE '%','Updating PInstance - Finished - ' || v_Message ;  PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, v_User_ID, 'N', v_Result, v_Message) ;  RETURN;

Upon success the @HOTEL_CalculateGuestRates_NoOfGuestsUpdated@ placeholder will be

replaced by the message in the correct language defined inside the Application Dictionary || Message window. There, create a new record:

Then run the process from the menu to see a nice custom success message:

You might have noticed that no recompilation was required. That is because the change was done directly inside the stored procedure and the message is replaced at runtime.

2. Check Threshold Parameter Values

To check for the presence of the threshold values, check the local variables in which you have stored the parameters after querying the AD_PINSTANCE_PARAM table. Setting the initial values of these local variables to 0, you could do the following check before going and processing all the guests:

  IF (v_Threshold_A = 0 OR v_Threshold_B = 0 OR v_Threshold_A IS NULL OR v_Threshold_B IS NULL) THEN    RAISE EXCEPTION '%', '@HOTEL_CalculateGuestRates_MissingParameter@';  END IF;

This will raise an exception with a custom message that still needs to be defined inside the Application Dictionary || Message window:

This way we are not hard coding language specific messages into the stored procedure. The place holder (@HOTEL_CalculateGuestRates_MissingParameter@) will be replaced at runtime with the message in the correct language selected by the user. Try running the stored procedure from the menu, entering zero for the value of one of the parameters to receive the following message: