View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

ERP 2.50:Developers Guide/How To Create a Table

ERP 2.50:Developers Guide

Index


Contents

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 phpPgAdmin for 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 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to (multitenancy).
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 This 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 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign 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 32 Foreign 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:


CreateWindow1.png


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

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


CreateWindow2.png


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


CreateWindow3.png


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. Run Synchronize Terminology process (Application Dictionary || Synchronize Terminology). Two tricky details before you run it:


CreateWindow4.png


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.


CreateWindow5.png


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. See here for more information.


Bulbgraph.png   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:

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.





ERP 2.50:Developers Guide/How to customize the Openbravo look and feel | ERP 2.50:Developers Guide/How To Add Columns To a Table 

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/How_To_Create_a_Table"

This page has been accessed 18,151 times. This page was last modified on 14 June 2011, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.