Costing Engine refactoring

January 2, 2012 1 comment

Phew! 2 years since last post! Hi all again.

I’m back with a nice and interesting post. In the following days we’re going to start a new and significant project for Openbravo ERP’s core. We are going to redo from scratch the costing engine. Many of you have asked for other costing algorithms apart from Standard and Average and in few weeks your desires will be real. Isn’t it a good New Year present?

You can find information about the functional specs on the project’s wiki. But let me briefly explain here what we intend to deliver in the following MPs.

Current engine has become obsolete and hard to extend and maintain. So we’re going to develop a new one from scratch using the new technologies provided by Openbravo 3. This new engine will be completely developed using Java. By default, currently available algorithms will be delivered, Standard and Average. But the new engine will accept new algorithms delivered by extension modules, so we plan to include FIFO, LIFO and Specific Identification algorithms soon. The design of the new engine will be inspired on the existing accounting engine. It will include a new background process to calculate the costs of the new transactions. And a new API to get and calculate the cost of a product.

One of the biggest changes of the new engine is that the cost will be deterministic. This is, when you recalculate the cost the result must be the same. It is necessary to keep a proper accounting. To achieve this a big change will be done, currently the transactions are ordered by it’s movement date when the cost is recalculated. If today I enter and process on the ERP a receipt from the last week the cost of the product might change if during the week I’ve created other receipts and calculated the cost. To avoid this scenario the transactions will be ordered by the date they are processed on the ERP. For the technical people, instead of using the Movement Date  of the receipt we’ll use the Creation Date of its record in the M_Transaction table.

Other very interesting and very demanded change are the dimensions to calculate the costs. The new engine will be able to calculate the cost of the products by warehouse and organization (Legal Entity). To facilitate the configuration a new window will be created to enter the rules.

This new engine will force us as well to update the reports and accounting processes to adapt them to the new API.

The project is still on the very first phases and many above statements are open to discussion. We’ll be very pleased to hear your comments and improve our design. To do so, please, leave your comments and feedback on the projects forum.

Categories: Openbravo

Advanced Payment business flow automated

November 2, 2009 2 comments

Openbravo has just released Advanced Payments Pack. This pack supports the management of advanced payments among a number of business partners. It facilitates the practice among trading partners where the seller organization collects an advanced payment at the beginning of a period, provides goods or services during the period, and at the end of the period invoices the buyer for the difference between the advanced payment and the value of the goods and services actually provided.

The module supports the advanced payment flow in generic terms, however, it is designed to  support advanced payments among subsidiaries of the same business group. The case where the holding company collects the advanced payments from a number of subsidiaries is emphasized, but the functionality is not restricted to this scenario.

The feature implemented by this module is already available in Openbravo ERP but requires  manual operations. The pack enables tasks automation, so they are performed in a faster, more efficient and more reliable way.

The Mass Advanced Payment pack is composed of three modules that could be installed and used independently:

  • Mass Advanced Payment: Provides a tool to create bulk advance payments for a number of business partners with a single action.
  • Direct Debit Form of Payment: Provides a new form of payment where the selling organization directly charges the amount of the payment to a bank account of the business partner associated with the payment.
  • Business Partner Debt Consolidation: Provides a tool to automatically consolidate the outstanding payments for a number of business partners and creates a debt payment for the difference of payment dues and payment received closing all intermediary documents.

This pack has been developed to work together with two other modules, which are not included in the pack, but are highly recommended to automate other business processes related to this business flow:

  • Mass Invoicing: Makes possible to generate multiple invoices based on an invoice template. Check out blog…
  • Inter-company Documents: Automates the creation of inter-company documents. An inter-company document is an invoice or order created within subsidiaries of one same business group. Whenever an inter-company document is completed, the corresponding document is created in the target organization matching the original document. Check out blog..

What’s new?

After installing this pack some new items appear in the menu:

  • Financial Management || Receivables and Payables || Transactions:
    • Mass Advanced Payment: Three step wizard. In the first the target values are selected: business partner, payment template, bank account, amount, etc. In the second step, modifications per business partner can be done. If the data is confirmed and the process button pressed, the advanced payments are created. The last step displayed the information of the just generated payments.
    • Direct Debit Create: Pop-up where filter values can be inserted. The corresponding debt payments with form of payment set to direct debit will be processed.
    • BP Debt Consolidation: Form to enter values to filter the payments to consolidate. The target payments form of payment, bank account and due date can be defined. Once the process button is pressed, the result will be displayed in the same window.
  • Financial Management || Receivables and Payables || Setup:
    • Payment Template: Window where payment templates can be defined. This templates will be used in the mass advanced payment wizard. The templates will define a default bank account, form of payment, payment status, etc. There is a flag in the template to set whether inter-company payments should be created or not.

Business Flow

The general business flow can be described as following:

  1. An organization receives multiple advanced payments from a number of business partners. This is registered in the application using Mass Advanced Payment functionality. First of all, there must be a payment template created. This template will have the receipt flag checked. It is recommended to select Direct Debit form of payment. Second, the wizard will be run, selecting the appropriate business partners, amounts, due date, etc.
  2. Transfer the payments to the bank account If direct debit form of payment was selected in the template for the advance payments, this process can be done using Direct Debit Create process.
  3. Goods and services are sold creating sales invoices. During certain period, sales invoices are created to the different business partners. Mass Invoicing process could be a useful tool to automate this process.
  4. Debt Consolidation Finally, the invoices created are consolidated against the advanced payment. A single payment is created to adjust the difference. The payment can be negative or positive depending on whether the amount collected as advanced payment exceeds or not the value of the sales invoices created during the period.

The special case of advanced payments between organizations in the same business group is also supported by this module. In this scenario, an organization receives advanced payments from a subsidiary organization. As for this, it is necessary that each organization is mapped to a business partner. This can be done in General Setup || Enterprise || Organization, in the organization information tab. Also, the payment template must have the inter-company flag activated. This way, the mass advanced payments wizard will detect which business partners represent organizations and will create a matching payments in those organizations.

If inter-company approach is used, the use of inter-company documents module is recommended. This way, matching documents will be created automatically in the subsidiary organizations if an invoice/order addressed to them is completed.

Check out the user manuals for more information:Mass Advanced Payments, Direct Debit and Business Partner Debt Consolidation.

Technical Overview

Mass Advanced Payments: The template window is a regular WAD generated window. The mass advanced wizard is on the other hand manual code. A java servlet controls the navigation between the different windows though session variables. The information is retrieved from the database using Data Access Layer. The drop down fields, the combos, are reloaded using Ajax.

Direct Debit form of payment: Direct debit create process has been developed with a database PL procedure. This procedure and its parameters are registered in the application dictionary. This way, WAD can generate the filter window the user gets when the process is executed.

Business Partner Debt Consolidation: This process has been developed with a manual window that uses Data Access Layer to retrieve database information.

Advanced Payments module is already available in the forge and in the central repository, so please feel free to install it and make the best of it! We are looking forward for your feedback, so every comment on the module will be more than welcome!

Categories: Openbravo

Inter-Company Documents Module

October 29, 2009 Leave a comment

Does your company perform sales – purchases between internal organizations? Do you need to create matching documents for those flows? The Inter-company Documents module is the solution you are looking for!

What are inter-company documents?

An inter-company document is an order or invoice created within two subsidiaries of one same company. Whenever a document of this type is created, a matching document should be created in the target organization. The Inter-company documents module provides a tool to automate the generation of this matching document.

Let me explain the concept with an example. Let’s assume that certain enterprise is formed of several subsidiaries. For instance organization A and organization B. Each time a sales invoice is completed in organization A for organization B, it is desired that a matching purchase invoice is created in the organization B for organization A.

The module supports the following flows:

  • Inter-company invoices initiated either from purchase invoices or sales invoices
  • Inter-company orders initiated either from purchase orders or sales orders.

It provides a mean to drill from the generated document to the original document and vice versa.

How can I take advantage of this?

The inter-company module is already available in the Openbravo Forge for users on Openbravo ERP 2.50MP4 and up. In order to make the most of this module, some configuration must be done. First of all, each inter-company organization have to be represented by a business partner. This way, the documents will be address to the corresponding business partner. This mapping can be done in the Organization window’s Information tab: General Setup || Enterprise || Organization.
For instance, imagine a company with a main organization, called Main, and two subsidiaries called A and B. Each of them should be mapped to a business partner, for instance orgMain, orgA and orgB business partners.

Additionally, the document types used for inter-company transactions have to be defined. This can be done in Financial Management || Accounting || Setup || Document Type. In order to define a document type as inter-company document type, the inter-company flag needs to be checked. Also, some inter-company relations have to be defined for it in the inter-company relations tab. Here, for each document type, a matching document type, a source organization and a target organization can be set. If no matching document is defined, that is, if the target document field is left blank, no matching document will be created in the target organization. The matching document could be created manually in a later stage.

Once the configuration is done, we are ready to go!

At this moment, it is possible to complete orders or invoices using the newly created inter-company document type. And automatically, following to the configuration done, it will create a matching document in the organization of the selected business partner. This matching document can be easily accessed through the linked items icon. Automatically generated matching documents have an original document field that can be used to navigate to it.

Further information is available in the user manual.

Technical Aspects

This module has being developed using Extension Points core enhancement. The functionality has been developed through database PL procedures named Interco_create_order and Interco_create_invoice. These procedures are executed right after C_Order_Post and C_Invoice_Post procedures. These two core procedures have an Extension Point at the end, so we can easily call the inter-company procedures. Find more information about extension points in this blog post recently published!

We have developed the functionality of this module in the following way: first of all, in the PL procedure, we get the necessary parameters from c_invoice_post or c_order_post to create the desired matching document. The new document will be created based on the data of the original invoice and in the default values of the matching business partner. The matching document’s type will be the one defined in the configuration.

Once the new document is created, it is completed calling to c_invoice_post or c_order_post again. This step will complete the new document and – caution – it will call our PL function again! It is important to check in the beginning of our PL whether it is being called by a manual document or by an automatically created document as we don’t want to have deadlocks in our code!

Right before the end, the message returned to the extension point is updated. This way when the completion process of the original document is finished it is shown the document number of the generated matching document.

Feel free to check out the technical documentation for more details!

Inter-company module is publicly available in the central repository, so we would like to encourage you to install it and use it! Do not hesitate to give use feedback, we are willing to hear from you!

Categories: apagola, Openbravo

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

Expense Report for Employees or how to extend a Core report

October 26, 2009 7 comments

Hi all, this is the first post of the Tips & Tricks category. In the articles of this category we will share with all of you the tricks that we have learned over the years working in Openbravo and we’ll give you tips that might make your life easier working with Openbravo ERP.

Lets start with the case that opens this category. Recently we received an improvement request for the instance of Openbravo ERP that we use internally to manage Openbravo.

In the 2.50 version in the Project & Service Management module we can find the Expense Report. Openbravo employees log the time sheet to track the time spent on each project and the expenses that might have had in the Expense Sheet window. The Expense Report is very useful to review this data for each customer’s project. It is also possible to filter by employee so you can view the projects that the employee has work on during the time.

In our internal instance we were asked to give access to employees to this report to view our own expenses. But, we had to somehow force to filter the results by the logged employee. So an employee won’t see the expenses and time sheets of other employees.

As this report belongs to Core we cannot modify it. The usual solution in 2.50 is to duplicate it in a module and give access to the employees to the new one. This also means that we are forced to maintain a new report. And that we’d have to manually apply to the duplicated one in the module the improvements and fixes done to the Core report. But this particular feature request only needed a minor change in the filter window, so we thought on a different approach to don’t have to duplicate the whole report.

The solution that we have chosen is to extend the Core report. This is, in the customizations module that we are using we have added a new report Report Expense for Employee. The servlet of this report instead of extend HttpSecureAppServlet is extending the Core report.

public class ReportExpenseForEmployee extends ReportExpense {

Using this approach the doPost method of the new report only checks the DEFAULT commandIn while all other options are treated by the doPost method of the core report. Below is an extract of the doPost method of the ReportExpenseForEmployee class:

public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
  if (vars.commandIn("DEFAULT")) {
    printPageDataSheet(response, vars, strDateFrom, strDateTo, strcBpartnerId, strProject,
    strExpense, strCurrencyId);
  } else {
    super.doPost(request, response);

So we have only developed the new filter template that it is a copy of the Core one removing the employee combo and defaulting its value to the employee id of the logged users in a hidden input. Each employee of Openbravo has its own user linked to a Business Partner that has the employee flag checked.

We still had a security issue to resolve, to ensure that nobody is able to call the report with a different employee id. To solve this problem, before calling the parent doPost method we check that the given employee id is correct.

// Check given c_bpartner_id is logged employee's one
 User user = OBDal.getInstance().get(User.class, vars.getUser());
 String strBpartner = (user.getBusinessPartner() != null ? user.getBusinessPartner().getId()
 : "");
 if (!vars.getGlobalVariable("inpUser", "ReportExpenseForEmployee|employee", "").equals(
 super.doPost(request, response);

And that’s all. We have given access to the employees to this new Report Expense for Employee report and removed the access to the Core report. At this moment employees are able to check its own reported expenses and time sheets. While managers have access to the Core report to review the expenses and time sheets of other employees.  All of this having to maintain only the filter window in the module. Getting advantage without any extra work of all the improvements and fixes done in the Core’s report template and queries.

NOTE (added 2009-10-28)

As Stefan Huehner has point me, the security issue can be solved in a better way in newer 2.50 mps. To get the business partner associated using DAL we need to have access to the user entity. But in our instance the Employee role doesn’t, so it only can get the user_id and its name. To ensure that we can query that we have to enter in administration mode.

When we did this extension we were using a 2.50MP1, so this was achieved changing the OBContext to the user “0”. And once we have the business partner id the OBContext is set back to the logged user.

In newer MPs a new method has been created to manage these cases. The code (not tested) would be something like:

// Check given c_bpartner_id is logged employee's one
 boolean previousAdminMode = OBContext.getOBContext().setInAdministratorMode(true);
 try {
   User user = OBDal.getInstance().get(User.class, vars.getUser());
   String strBpartner = (user.getBusinessPartner() != null ? user.getBusinessPartner().getId() : "");
 } finally {
   boolean adminMode = OBContext.getOBContext().setInAdministratorMode(previousAdminMode);
 if (!vars.getGlobalVariable("inpUser", "ReportExpenseForEmployee|employee", "").equals(
 super.doPost(request, response);