Archive for October 27, 2009

Extending existing Procedures at Openbravo

October 27, 2009 9 comments

Need to modify a core Procedure?

Some times while we are developing a new functionality or customizing an implementation for a customer we get stuck having to deal with existing core procedures that we shouldn’t modify. This happened to us recently while we were developing the Intercompany Documents module. In this module it is needed to generate new matching documents (orders or invoices at this moment) when the main one is being completed. So we somehow had to modify the behavior of the Post process in Openbravo ERP (C_Invoice_Post and C_Order_Post procedures).

Let me put a brief example that explains the purpose of this module. Imagine a client with 2 organizations Main and Europe. The organization Europe has defined a Business Partner Europe BP, and Main has Main BP. With this configuration, when a Sales Order is created and completed in Main organization for Europe BP a matching Purchase Order in Europe organization for Main BP has to be to automatically generated.

To be able to achieve this we developed a core enhancement: the Extension Points

The Extension Points are points that can be set in any PL Procedure and that are able to call other PL Procedures included in any module. At this moment there are 2 Extension Points defined in core procedures:  C_Order_Post – Finish Process and C_Invoice_Post – Finish Process. We expect to include more of them in following maintenance packs.

At first let me explain

How to define a new Extension Point

The Extension Points are very useful in some important core procedures (like mentioned C_Order_Post or C_Invoice_Post) that are very used by the application in a daily basis and that users might want to modify their behavior. But an Extension Point can be defined in any procedure of any module. In this section I’m going to explain how a developer can define a new Extension Point. Core procedures can only be modified by Openbravo developers or accepted contributors, but anyone can send us a patch or ask for a new Extension Point in the Open discussion forums or using the Openbravo-development mailing list.

Lets take the C_Order_Post – Finish Process extension point as example. All the Extension Points have the same structure, some code in the procedure where the hook is added and the declaration in the Extension Points window (Application Dictionary || Setup || Extension Points  ||  Extension Point)

Declaration of a new Extension Point in the Application Dictionary

Each Extension Point added to a procedure needs to be declared in the Extension Points window (Application Dictionary || Setup || Extension Points  ||  Extension Point). Set here information related to the Extension Point itself.

The description should include the list of parameters that will be available by the procedures. In the case of the C_Order_Post – Finish Process:

  • Record_ID: It’s the c_order_id that identifies the order that is being processed.
  • DocAction: It’s the process action (complete, close, void, …).
  • User: It’s the user identifier that is processing the order. Used in the auditory fields (createdby and updatedby) when records are updated or inserted.
  • Message: It’s the message that it is shown in the application at the end of the process. Null means Process completed successfully.
  • Result: Number that defines the result of the process. 0 error, 1 success and 2 warning.

Later you’ll find how to retrieve those parameters.

All the procedures that are called on the extension points are added in the Procedures tab of the same window. Any module can insert new records in this tab.

Be sure that the export.database ant target exports this new record and that it is committed with the change in the procedure where the Extension Point has been added.

The Extension Point in the PL procedure

First of all, we need to decide where to place the Extension Point in the code. The same procedure can have several Extension Points at any step of the procedure.  For example the C_Order_Post – Finish Process is located at the end of the C_Order_Post1 procedure.

Once the place is decided we have to code the extension point, this has 6 differentiated steps:

  1. Checking the existence of procedures to execute. (lines 01-04 in the code below) For performance issues, the extension handler is only called if there are procedures to call. As the Extension Point has to be declared in the application dictionary we can identify it by its uuid. The table AD_EP_Procedures is the table of the Procedures tab in the application dictionary.
  2. Initializing required variables. (lines 06-10) It is useful to declare 2 variables:
    1. v_ep_instance to have a unique identifier of each time that the handler is called (similar concept of ad_pinstance). It’s initialized with a get_uuid().
    2. v_extension_point_id with the identifier of the extension point.
  3. Populating the parameters available in the executed procedures. (lines 12-21) Values of available parameters are stored in the ad_ep_instance_para table for each v_ep_instance. AD_EP_INSTANCE_PARA_INSERT procedure is used for it. This procedure has as arguments the execution identifier (v_ep_instance),  the extension point identifier, the name given to the parameter, the value of the parameter. As the value can be of different types (string, number, date or text) and it can be a range, there are several arguments for each case. There are 5 parameters in the C_Order_Post – Finish Process, so this procedure is called 5 times, one per each parameter. In this case the 5 parameters are strings, except the Message that it is a text and the Result that is a number.
  4. Calling the Extension Point Handler. (line 23) AD_EXTENSION_POINT_HANDLER procedure is used to execute the procedures. This procedure has the execution instance and the extension point identifier as arguments. It searches in the ad_ep_procedures table for all the procedures of the Extension Point and executes them.
  5. Retrieving output parameter values. (lines 25-32) The executed procedures might change the value of a given parameter. This is done updating the ad_ep_instance_para table. So it is necessary to get back those values to update the variables of the main procedure. In the  C_Order_Post – Finish Process two parameters can be updated: the Result, that sets the final result type (success, warning or error) and the Message, that sets the final message shown to the user once the c_order_post1 procedure finishes.
  6. Cleaning up the AD_EP_Instance_Para table. (lines 34-35) Once the extension point execution has finished it is safe to clean that table by deleting the values of the parameters.

Below is the code of the C_Order_Post – Finish Process Extension Point as it can be found in the C_Order_Post procedure:

SELECT count(*) INTO v_count
where exists (select 1 from ad_ep_procedures where ad_extension_points_id = 'CB68FC0E8A4547D9943C785761977E77');
IF (v_count=1) THEN

  v_ep_instance VARCHAR2(32);
  v_extension_point_id VARCHAR2(32) := 'CB68FC0E8A4547D9943C785761977E77';
  v_ep_instance := get_uuid();

  AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Record_ID',
    v_record_id, NULL, NULL, NULL, NULL, NULL, NULL);
  AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'DocAction',
    v_DocAction, NULL, NULL, NULL, NULL, NULL, NULL);
  AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'User',
  AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Message',
    NULL, NULL, NULL, NULL, NULL, NULL, v_Message);
  AD_EP_INSTANCE_PARA_INSERT(v_ep_instance, v_extension_point_id, 'Result',
    NULL, NULL, v_result, NULL, NULL, NULL, NULL);

  AD_EXTENSION_POINT_HANDLER(v_ep_instance, v_extension_point_id);

  SELECT p_number INTO v_Result
  FROM ad_ep_instance_para
  WHERE ad_ep_instance_id = v_ep_instance
    AND parametername LIKE 'Result';
  SELECT p_text INTO v_Message
  FROM ad_ep_instance_para
  WHERE ad_ep_instance_id = v_ep_instance
    AND parametername LIKE 'Message';

  DELETE FROM ad_ep_instance_para
  WHERE ad_ep_instance_id = v_ep_instance;

How to use an Extension Point

Any time that it is desired to modify the behaviour or to extend an already existing process it is possible to take advantage of an Extension Point. In the Inter-company documents are used to create matching documents when orders and invoices are completed. Other usage example could be to generate a work requirement when a sales order for a manufactured product is completed. In this section I’m going to explain how a developer can develop a new procedure to be executed by an existing Extension Point.

To use an Extension Point we just need a PL Procedure following some simple rules:

  1. The procedure only has one input parameter, the execution instance id.
  2. It has to retrieve the needed parameters that are available from the ad_ep_instance_para table.
  3. It has to update the output parameters in the ad_ep_instance_para table.
  4. Possible exceptions are just raised.

And declare the procedure in the Application Dictionary.  To declare you just need to set the name of the procedure in the Procedures tab for the Extension Point where it has to be executed.

Let us look at it based on the example.

Inter-company Documents example

As said above, this module generates a new matching order when another order is completed. To achieve that requirement we developed the INTERCO_CREATE_ORDER procedure. It retrieves the parameter values using the given ad_ep_instance. It does the necessary actions to generate the new order and at the end the Message parameter is updated appending a new message, so the user knows the Document number of the generated matching order.

Below is a summary of the INTERCO_CREATE_ORDER procedure. We can see how are retrieved the param values using the Cur_Params cursor. Later in the code is updated the p_Message variable with the document number of the generated matching order. And finally is updated the Message parameter in the AD_EP_Instance_Para appending the p_Message variable. Notice also how the Exception block just raises the exceptions so they can be caught later in the main procedure that calls the procedure.

create or replace PROCEDURE INTERCO_CREATE_ORDER(p_ep_instance IN VARCHAR2)


FOR Cur_Params IN (
FROM ad_ep_instance_para
WHERE ad_ep_instance_id = p_ep_instance) LOOP
IF (cur_params.parametername LIKE 'DocAction') THEN
p_docaction := Cur_Params.p_string;
ELSIF (cur_params.parametername LIKE 'Record_ID') THEN
p_record_id := cur_params.p_string;
ELSIF (cur_params.parametername LIKE 'User') THEN
p_user := cur_params.p_string;
ELSIF (cur_params.parametername LIKE 'Message') THEN
p_message := cur_params.p_text;
ELSIF (cur_params.parametername LIKE 'Result') THEN
p_result := cur_params.p_number;


p_message:='@INTERCO_ordCreated@' || v_DocumentNo;


UPDATE ad_ep_instance_para
SET p_text = (CASE WHEN p_text IS NULL OR p_text='' THEN p_message ELSE TO_CHAR(p_text) || '<BR>'|| p_message END)
WHERE ad_ep_instance_id = p_ep_instance
AND parametername LIKE 'Message';



We have described how we have made available the Extension Point functionality in core. Now we are able to create new Extension Points and, more important, we have learnt how we can use this Extension Points in our modules. This new functionality bring us an improved flexibility on processes, Orders and Invoice completion, that have been hard to customize in previous versions of Openbravo ERP. Help us to extend this flexibility through other core processes by submitting patches with Extension Points or proposing new ones to be added in following maintenance packs. Use the usual communication channels for this the Open discussion forums or the Openbravo-development mailing list.

DECLARE v_ep_instance VARCHAR2(32); v_extension_point_id VARCHAR2(32) := ‘CB68FC0E8A4547D9943C785761977E77’; BEGIN v_ep_instance := get_uuid();
DECLARE v_ep_instance VARCHAR2(32); v_extension_point_id VARCHAR2(32) := ‘CB68FC0E8A4547D9943C785761977E77’; BEGIN v_ep_instance := get_uuid();
Categories: gorkaion, Openbravo

Human Capital Management available for Openbravo ERP 2.50MP6!

October 27, 2009 1 comment

Human Capital Management capabilities are now available in Openbravo ERP!

Human Capital Management module is intended to provide solid base employee information management capacity so further human capital management functionality can be developed on top of it. The module adds a Human Capital Management folder to the main menu that contains windows to manage employees, salary categories, positions, teams and contract types.

The management structure of the company is modeled with the concept of Teams, that is, areas, subareas, departments, etc. are called Teams. For each team, a parent team can be specified and this way, the managing tree can be defined. Each team can have a manager in charge determined for certain time period. The system records all the activity related to employment information, so, a historical record of team managers is kept within the application. The different jobs an employee could perform are modeled with the Position concept. Each position can be matched with several Salary Categories.

Let’s now define the employee concept.

Employee Management

Employees are a specific type of business partners. In Openbravo ERP, in the business partner window there is a tab named employee where a business partner can be checked as being an employee. After installing the module, this tab will still be operative, but a new specific window to enter further information of employees will be provided. This window will be located in the Human Capital Management menu. The employees entered though that window will still be business partners and will be available through the regular business partner window.

Employee Header Tab

The new employee window has the following tabs:

  • Header Tab: In the header tab, personal information of the employee can be inserted.
  • Contact Tab: In the contact tab the user can insert as many telephones, e-mail addresses, mobile phones, etc. as needed. There is a flag to check whether those contacts are private or business contacts. It is also possible to select which one is the default contact.
  • Location Tab: In the same way, as many addresses as needed can be inserted in this tab and they could be set as private address or business address.
  • Bank Information Tab: Additionally, as many bank account as necessary can be inserted. One of them can be selected as default bank account.
  • Employment Information Tab: This is the most complex tab. Here, the information related to the employees’ activity in the company is entered: the position, the salary, the contract type, etc. The records in this tab are created in a draft status. Once the information is valid, the complete button must be pressed and the record will be saved. The status of the record will be historic, complete or future depending on the data range. The completed records can not be changed, and this way, historical data regarding the employees’ activity in the company is registered.
  • Family Member Tab: This tab is meant to keep information of relatives or next of kin.
  • Operational Resource Tab: This tab allows the setting of the values available in the employee tab of business partner window.
  • Other Citizenship Tab: This tab allows the insertion of more than one citizenship for an employee if needed.

Don’t hesitate to check the user manual for more information!

Human Capital Management Roles

The module provides three roles ready to be assigned to the users: Human Capital Manager, Manager and Employee. The Human Capital Manager will have complete access to all the functionality of the module, and will be the role in charge of every process related to human resource management. The manager role will have access to a specific window for managers, called employee (manager view). Only the records of the employees reporting to the manager will be available in this window. The information in this window will be read only: if a change is required, it should be done via the human capital manager. Finally, the role employee has access to a window called employee self-service where each employee can access his/her information. Except for the employment data, that will be read only, the rest of the information will be editable by the employee.

Technical Notes

For those of you interested in the insides of the code, here are some comments.

All the windows of this module have been generated by WAD. As for that, some new tables had to be created, and some core tables had some columns and constraints added. For instance, the employee window is based on the core table c_bpartner, which had some new columns added.

Along the different windows and tabs, there are some fields that happen to appear and disappear depending on certain fact. If this fact can be found in the same table, this logic is implemented using display logic functionality, which can be defined in the application dictionary when a field is described. If the reason to hide or display a field does not depend on a value of a field of the window itself, auxiliary input functionality must be used. This was used for instance in the employment information tab, to display the field bonus % depending on the bonus type of the selected salary category.

Additionally, the read only logic functionality, available on the column definition in the application dictionary, was used to make the fields read only or not depending on certain value. This was used in the employment information tab to make most fields read only if the record is not in draft status.

Some of the fields, for instance known as field in employee window header, have a callout associated to them. This callout will calculate the value of the field when some other field is changed. In this case, the known as field is changed whenever a change is done in the name or last name fields. Know as field is the concatenation of the previous fields. A more complex callout is used to reload the values of the drop down field salary category, whenever a change is done in the position field. The salary categories available for the selected position are loaded. All this callouts have been developed using Data Access Layer utility.

Data Access Layer has being used as well to develop every single process in the module. Every button in the module is linked to a process defined in the application dictionary. This processes are java files that implement org.openbravo.scheduling.Process. Using DAL utility database information is retrieved and the required modifications done in the data.

Finally, there is one process which is not executed with a button. It is a background process which can be scheduled through the process scheduling functionality to run periodically. This process updates the employment information records of the employees, setting to historic status the ones whose end date is reached.

For further details please have a look at the technical specifications of this project which can be found at Openbravo Forge.

Extending Human Capital Management Module

This module provides the basic functionality to be able to store and maintain employee information. Further developments, such as reports, can be done on top of this to exploit and expand all this information. Extensions to cover a wider area of Human Capital Management such as vacation management, recruitment etc. are more than welcome. Now, feel free – and we encouraged you– to develop any extension for Human Capital Management!

Categories: apagola, Openbravo