ERP 2.50:Developers Guide/How To Create a Table/it

Contents 1 Come creare una nuovat tabella 1.1 Obiettivo 1.2 Modularità 1.3 Creare nuove tabelle nel database 1.4 Registrare la tabella nel Dizionario Applicazione

Come creare una nuovat tabella

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.

Obiettivo

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.

Modularità

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.

Creare nuove tabelle nel 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)
);

Registrare la tabella nel Dizionario Applicazione

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. See here 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 functionality
  • System only: Only system records can be inserted into this table (AD_CLIENT_ID=0, AD_ORG_ID=0), for example AD_TABLE.
  • System/Client: System or client specific records can be inserted here (AD_CLIENT_ID=anything, AD_ORG_ID=0), for example AD_ROLE
  • 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
  • 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
  • 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. Run Synchronize Terminology process (Application Dictionary || Synchronize Terminology). Two tricky details before you run it:

• Check you have defined dependencies from your module to core and any other needed module. Synchronize Terminology will link your columns to Elements in your module or in modules it depends on. If you don't declare the dependency to core the system will create new elements for standard columns like IsActive, CreatedBy, etc.
• Do not set the Elements linked to your columns, let the Synchronize terminology to do the job. This way the process will find the appropiate elements and will set the proper column names for the standard columns (IsActive, CreatedBy, etc.).

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

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.