Reference Types in Openbravo

Reference is the way a field or column is presented to the User. For the list of references supported by Openbravo, refer here. These are mainly bundled inside the WAD. I have presented a basic overview of few references that could be of more use.


This reference is used when we want to represent currency values. Different countries will have different currency representations. For instance, Indian representation of currency will have two decimal places. This can be configured in format.xml file. Once this is configured, the decimal places will be globally affected for all the amount fields.


Price and Amount references are almost same. Only difference is we cannot give the minimum and maximum range for the field when it is Price References.


Button Reference is used when we want to execute a process. Either a Dal process or a stored procedure can be attached to a button. This can be done by defining the process in Reports and Process.


DateTime reference is used to capture the time in hh:mm:ss format. The validation for hours,minutes and seconds are already done in the reference.


ID refers to the primary key field of the Table. This is set up by Openbravo. It uses the Tablename_ID notation to find out what is the primary key for the table.


Images are saved in the AD_Image table in Openbravo. You can refer to the Images saved there, as a dropdown field here. i.e only the name of the image will come in the dropdown.


When you want to display an image in a field, this datatype is used. For more information on setting Images in Openbravo, refer here. The images will be displayed as follows.


The Link reference is used when you want to refer to an external link from Openbravo, or to any link within Openbravo. You can navigate to the link using the button provided at the end of the field.


Presents a list of predefined values as a dropdown. This list can be configured in the Reference Window using the System Administrator role.


TableDir is the abbreviation for TableDirect. In essence, it points out to the foreign key referenced here. For eg., if you refer a field called ad_image_id, Openbravo identifies by itself that there is a table by the name ad_image and pulls all the values of that table and provides as a dropdown in this field. The values that appears in the dropdown is the field that was set as identifier in that Table. Note that, the field name has to be the exact primary column name of the referenced table. There is a small issue with regards to this, which is discussed here.


Table reference is similar to TableDir, but here the name of the referenced field need not be the same as the corresponding ID field of the referred Table. Also in some cases using Table references allows us to add extra filtering conditions to the column. A table reference created for Client is provided below. Even this is configured in the reference window using the System Administrator role.

We can also add our own references in addition to the references provided. Some examples are presented here and here.

Would be happy to hear comments and questions.


Changing Timezone in Postgresql, Ubuntu

Timezone and locale are integral concepts that one should be aware of when implementing a System. For example. if you create a database in PostgreSql, it will use the default System settings for the database. Lets say you have a server in UK and your client is US. If the client tries to retrieve the current date or time, it is going to show the UK time and not the US one.

Today I encountered a different issue in our client place where the Ubuntu timezone and PostgreSql timezone were showing a incorrect values. Instead of Indian Standard Time (IST) it was showing Pacific Daylight Time(PDT). These are the steps that I followed to change the timezone to IST in PostgreSql and Ubuntu.

Changing timezone in Ubuntu:

sudo dpkg-reconfigure tzdata

You will be prompted to choose the continent and then the area. Provided screen shots where we configure the location.

Use TAB key to navigate to the OK button and then press Enter.

The changes will get reflected immediately. To check it you can open a new terminal and type ‘date’. The output is provided below.

Changing timezone in PostgreSql Database:

1. Select the name of the timzone you want to set.

SELECT * FROM pg_timezone_nam<wbr>es;</wbr>

2. Update the corresponding timezone, in my case ‘Asia/Kolkata’.

ALTER DATABASE postgres SET timezone TO 'Asia/Kolkata';

3. Use Select now() to check the current timezone setting.

For more such articles on Ubuntu, Postgres and Openbravo Subscribe here or here.

Creating database in oracle

In oracle, database creation can be done in two ways. We can create database automatically along with the installation and the other way is to create the database Manually. Automatic creation is the easiest way.Because all  files and folders  are maintained under the ORACLE_HOME directory,But for administrating all the things  as a adminstrator most of the people  prefer to create database by manaually.Let me explain, how to create the database manaully.

Here are  the steps to create database manually,

1)Create a folder with the name of database name.

2)Create five folder under the database folder as pfile, bdump, udump,cdump, control.

3)Create a file name called init.ora in the pfile folder.

4) Create init.ora file using the following paramter and  also don’t forget to save along with its extension.

remote_login_passwordfile= exclusive

5)Connect to the sql prompt using sqlplus.

SQL> Conn sys/sys as sysdba.

6)Before creating the database.Intilize the memory using the pfile at no mount stage using

SQL>startup pfile='<drive>:\<foldername>\pfile\init<dbname>.ora’ nomount.

7)And then Create the database using this statements.Edit the statement according to your creation diretory.
    maxdatafiles  1021
    maxinstances  1
    maxlogfiles   8
    maxlogmembers 4
    DATAFILE ‘<drive>:\oradata\<SID>\system01.dbf’ SIZE 2000M
   NEXT 10240K
   SYSAUX DATAFILE ‘<drive>:\oradata\<SID>\sysaux01.dbf’ SIZE 120M
  NEXT  10240K
  LOGFILE GROUP 1 (‘<drive>:\oradata\<SID>\redo01.log’) SIZE 100M,
        GROUP 2 (‘<drive>:\oradata\<SID>\redo02.log’) SIZE 100M,
        GROUP 3 (‘<drive>:\oradata\<SID>\redo03.log’) SIZE 100M;

It will automatically comes to the open state once when the creation gets completed.Check with the below query to conform it.

SQL> Select open_mode from v$database.

It show that the database is in read-write mode.

I hope it will be more useful to create database manaually .

changing DBID in oracle database

While cloning the database using RMAN. We usually face the problem in DBID.After cloning a database it change only the name of the database but  DBID remains as same before.It reflects in various concern. I have faced this issue while  exporting table from cloned database to another database.In order to change the dbname along with DBID we have option.We can use oracle utilities called as NID.Using NID we can change database name without changing the DBID

otherwise we can change only the database name. But there is no option to change only the DBID using the NID utility.

If we want to change only the DBID. Follow these steps which is given below

NOTE: Before any major or significant changes to a database, you should perform a cold (closed) backup.

Steps to change the DBID.
To Change an Oracle Database Identifier (DBID)

1. Connect to the database in SYSDBA mode.
2. Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.

shutdown immediate

3. Startup the database in mount mode

statup mount

4. Check the present (soon to be old) database ID (DBID)

select dbid, name from v$database;

DBID                    NAME

——————   ————-

578456994  HR9DEMO

5. Generate the create controlfile statement into a trace file.

alter database backup controlfile to trace;

6. Generate a new DBID using the zeroDbid function of dbms_backup_restore

execute dbms_backup_restore.zeroDbid(0)


NOTE: The value zero (0) is a special case, which is the case we need.

7. Get another clean shutdown.

shutdown normal

8. Delete the controlfiles .

9. Start up the database in nomount mode.

startup nomount

10. Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).

Change from:

Change to:

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

11. Open the database.

alter database open resetlogs;

12. Check the new DBID. It should be different.

select dbid, name from v$database;

Now you can find the different DBID in the database.

HTMLArea in Openbravo

References are a key feature that one should be aware of in OpenbravoERP. References provides us options to change the way a field is displayed in the Application. There are many standard reference types like, Table, TableDir, Date, Integer, ImageBlob, etc. These are mainly bundled inside the WAD. In addition to the existing list of references listed here, one can add more references like RadioButton, FileDialog, etc. For more information on how to extend a base reference to your module, refer here.

HtmlArea Reference:

The use of this reference type  it to display the value based on the html code provided. It can be used in many scenarios. I have provided a basic few scenarios here.

  • To display different colour on the field based on the condition.
  • To display remote content in the field.
  • To embed HTML code available elsewhere.
  • Used when you want to have a link to any other openbravo window or process. This is can done by using <a href=”link”/> where link is the url of that corresponding window or process.
  • For simple formatting like Font Color, Background Image, etc.

To achieve this, you can type a HTML code for that and save it in the default value of  the Column in ‘Tables and Columns’ so it at run type, it will display the colour. The definition should be as follows.


Html Area Definition

Compile the application and the output will be as follows.




Add the following files in Openbravo-Source/src-wad/src/org/openbravo/wad/controls

1. WADHtml.html

<?xml version="1.0" encoding="UTF-8" ?>
@author Pandeeswari
<html xmlns="" xml:lang="en" lang="en">
<script type="text/javascript">
function DisplayHtml(/*String */ id) {
var htmlValue = document.getElementById(id);
var displayText = document.getElementById('yy');
displayText.innerHTML = htmlValue.value; = 'none';
<div id="xx_inp">
<table border="0" cellpadding="0" cellspacing="0">
<table style="border:0px;border-collapse:collapse;">
<td style="padding-top: 0px;">
<textarea id="xx" required="false" maxlength='xx' name="inpxx" onkeyup="" onkeydown="changeToEditingMode('onkeydown');" onkeypress="changeToEditingMode('onkeypress');" oncut="changeToEditingMode('oncut');" onpaste="changeToEditingMode('onpaste');" oncontextmenu="changeToEditingMode('oncontextmenu');" onchange="validateTextArea(;logChanges(this);xx();return true;" onfocus="DisplayHtml(;"></textarea>
<b id="yy" required="false" maxlength='xx' onkeyup="" onkeydown="" onkeypress="" oncut="" onpaste="" oncontextmenu="" onchange=""></b>
<span style="display: none;" id="xxmissingSpan">
<td><div /></td><td></td>
<td colspan="2" class='missing'><div id="xxmissingSpanText" name="missingText">xx</div></td>


@author Pandeeswari
package org.openbravo.wad.controls;

import java.util.Properties;
import java.util.Vector;

import javax.servlet.ServletException;

import org.openbravo.wad.EditionFieldsData;
import org.openbravo.wad.FieldsData;
import org.openbravo.wad.WadUtility;
import org.openbravo.xmlEngine.XmlDocument;

public class WADHtml extends WADControl {

public WADHtml() {

public WADHtml(Properties prop) {

public void initialize() {

private void generateJSCode() {
addImport("ValidationTextArea", "../../../../../web/js/default/ValidationTextArea.js");
if (getData("IsMandatory").equals("Y")) {
XmlDocument xmlDocument = getReportEngine().readXmlTemplate(

xmlDocument.setParameter("columnNameInp", getData("ColumnNameInp"));

public String getType() {
return "TextArea";

public String editMode() {
double rowLength = ((Integer.valueOf(getData("FieldLength")).intValue() * 20) / 4000);
if (rowLength < 3.0)
rowLength = 3.0;
XmlDocument xmlDocument = getReportEngine().readXmlTemplate(

xmlDocument.setParameter("columnName", getData("ColumnName"));
xmlDocument.setParameter("columnNameInp", getData("ColumnNameInp"));
xmlDocument.setParameter("size", getData("CssSize"));
xmlDocument.setParameter("maxlength", getData("FieldLength"));

boolean isDisabled = (getData("IsReadOnly").equals("Y") || getData("IsReadOnlyTab").equals("Y") || getData(
xmlDocument.setParameter("disabled", (isDisabled ? "Y" : "N"));
if (!isDisabled && getData("IsMandatory").equals("Y")) {
xmlDocument.setParameter("required", "true");
xmlDocument.setParameter("requiredClass", " required");
} else {
xmlDocument.setParameter("required", "false");
xmlDocument.setParameter("requiredClass", (isDisabled ? " readonly" : ""));

xmlDocument.setParameter("callout", getOnChangeCode());

return replaceHTML(xmlDocument.print());

public String newMode() {
double rowLength = ((Integer.valueOf(getData("FieldLength")).intValue() * 20) / 4000);
if (rowLength < 3.0)
rowLength = 3.0;
XmlDocument xmlDocument = getReportEngine().readXmlTemplate(

xmlDocument.setParameter("columnName", getData("ColumnName"));
xmlDocument.setParameter("columnNameInp", getData("ColumnNameInp"));
xmlDocument.setParameter("size", getData("CssSize"));
xmlDocument.setParameter("maxlength", getData("FieldLength"));

boolean isDisabled = (getData("IsReadOnly").equals("Y") || getData("IsReadOnlyTab").equals("Y"));
xmlDocument.setParameter("disabled", (isDisabled ? "Y" : "N"));
if (!isDisabled && getData("IsMandatory").equals("Y")) {
xmlDocument.setParameter("required", "true");
xmlDocument.setParameter("requiredClass", " required");
} else {
xmlDocument.setParameter("required", "false");
xmlDocument.setParameter("requiredClass", (isDisabled ? " readonly" : ""));

xmlDocument.setParameter("callout", getOnChangeCode());

return replaceHTML(xmlDocument.print());

public String toXml() {
String[] discard = { "xx_PARAM", "xx_PARAMHIDDEN", "xx_HIDDEN" };
if (getData("IsDisplayed").equals("N")) {
if (getData("IsParameter").equals("Y"))
discard[1] = "xx";
discard[2] = "xx";
} else {
if (getData("IsParameter").equals("Y"))
discard[0] = "xx";
XmlDocument xmlDocument = getReportEngine().readXmlTemplate(
"org/openbravo/wad/controls/WADMemoXML", discard).createXmlDocument();
xmlDocument.setParameter("columnName", getData("ColumnName"));
return replaceHTML(xmlDocument.print());

public String toJava() {
return "";

public void processTable(String strTab, Vector<Object> vecFields, Vector<Object> vecTables,
Vector<Object> vecWhere, Vector<Object> vecOrder, Vector<Object> vecParameters,
String tableName, Vector<Object> vecTableParameters, FieldsData field,
Vector<String> vecFieldParameters, Vector<Object> vecCounters) throws ServletException,
IOException {
// Override this to do nothing

3. WADHtml.xml

<?xml version="1.0" encoding="UTF-8"?>
@author Pandeeswari
<template file="WADHtml.html"/>
<PARAMETER id="paramChecked" name="checked" default="Y"/>

<PARAMETER id="xx_inp" name="columnName" attribute="id" replace="xx"/>
<PARAMETER id="xx" name="columnName" attribute="id"/>
<PARAMETER id="xx" name="columnNameInp" attribute="name" replace="xx"/>
<PARAMETER id="xx" name="size" attribute="class" replace="yy"/>
<PARAMETER id="xx" name="callout" attribute="onchange" replace="xx();"/>
<PARAMETER id="xx" name="maxlength" attribute="maxlength"/>
<PARAMETER id="xx" name="disabled" boolean="readonly=true" withId="paramChecked"/>
<PARAMETER id="xx" name="required" attribute="required"/>
<PARAMETER id="xx" name="requiredClass" attribute="class" replace="xx"/>
<PARAMETER id="xxmissingSpan" name="columnName" attribute="id" replace="xx"/>
<PARAMETER id="xxmissingSpanText" name="columnName" attribute="id" replace="xx"/>

<DISCARD id="discard"/>

Using the above code, create the reference as follows.

Reference Definition

Reference Definition

Specify the reference type as Html Area in columns for the desired column(It should be character varying in Database). Give the html code and compile. Then when you open the window in classic mode, and based upon the code provided in HTML, the value will be displayed. There are some points to be noted here.

  • This reference type works currently in Classic mode.
  • In the form view, the HTML output gets displayed, but in the Grid view, the HTML code gets displayed.
  • Only if the corresponding field is the First field in the page or the first focussed field, then HTML output gets displayed. Or else the HTML code alone appears and once the focus is changed from that field, the HTML output appears as I have written the function in the onblur event.
  • The same code can be extended to support scripts and also Data Operations.
Comments and suggestions are very much welcome.

PeopleSoft PIA pages have a pink background

Whenever any page is opened in PIA, the pages are displayed with a  pink background shown below.

This started happening right after a tools upgrade and I found that the issue is related to the webprofile configuration.
Steps to solve this issue:

1)Check with the webprofile domain in which we have configured our current setting.

2)Go to Peopletools »» Web-profile »» Web Profile Configuration
open the domain in the webprofile configuration and select debugging tab
clear out the following options
a)Trace Monitoring Server
b)Trace PPM Agent
c)Show Trace Link at Signon
d)Show Layout
e)Create File from PIA HTML Page

3)Check with the default style sheet in PeopleTools option :

PeopleTools »» utilities »» Administration »» People Tools Option
4)Check with the default style sheet in System Option.

PeopleTools »»Portal»» Portal Utilities »»System Option
DEFAULT style sheet name —(EOPP_SCSTYLEDEF)

5) shutdown the Appserver and Webserver.

6)After finishing these step clear the cache area in application server as well as in the Web server in order for the  new settings to take effect

Webserver cache area in PeopleTools is present in

7)Reboot the Appserver and Webserver

Now check with the pages in PIA.

SQR 6458 – Unsupported Grafcap device

When I tried running a custom developed SQR program, which has a bar chart, the error ”Unsupported Grafcap device –SQR 6458” was reported by the SQR engine. Even when I tried bar100.sqr program, which is a delivered SQR, the same error persisted. With a little bit of digging around, I found that there are three things to be considered while running any program with bar/pie/histogram etc.

  • stimages.dll
  • Libsti.ini
  • SQR File Path


Stimages.dll  is the library file  needed to run any program with bar/pie/histogram etc .It is usually  present in the PS_HOME directory. Check this file in the below path
This file is made available when you install PeopleTools. If it is not present in your PS_HOME try re-installing it.


There is an INI file called Libsti.ini that should be in the C:\WINDOWS directory on the machine that the SQR is being run on. The ini file contains certain settings that need to be valid in order to avoid this error and view the desired chart results. Below are the contents of the file. Make sure they are set correctly. If the Libsti.ini is not present in the directory manually create the file using the following parameter and save it as Libsti.ini.

SQR File Path

Bar100.sqr is the sample SQR program which is present in the PS_HOME\bin\sqr\ora\samplew directory. It should be placed in the binw directory as follows


After placing the custom SQR in this path, the sqr runs successfully.