Archive

Author Archive

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.

Advertisements
Categories: 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
FROM DUAL
where exists (select 1 from ad_ep_procedures where ad_extension_points_id = 'CB68FC0E8A4547D9943C785761977E77');
IF (v_count=1) THEN

DECLARE
  v_ep_instance VARCHAR2(32);
  v_extension_point_id VARCHAR2(32) := 'CB68FC0E8A4547D9943C785761977E77';
BEGIN
  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',
    v_User, NULL, NULL, NULL, NULL, NULL, NULL);
  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;
END;
END IF;

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)
...

BEGIN

FOR Cur_Params IN (
SELECT *
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;
END IF;
END LOOP;

...

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';

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('INTERCO_CREATE_ORDER exception') ;
RAISE;

END INTERCO_CREATE_ORDER;

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

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
 OBContext.setOBContext("0");
 User user = OBDal.getInstance().get(User.class, vars.getUser());
 String strBpartner = (user.getBusinessPartner() != null ? user.getBusinessPartner().getId()
 : "");
 OBContext.setOBContext(user.getId());
 if (!vars.getGlobalVariable("inpUser", "ReportExpenseForEmployee|employee", "").equals(
 strBpartner))
   pageError(response);
 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(
 strBpartner))
   pageError(response);
 super.doPost(request, response);