Home > gorkaion, Openbravo > Extending existing Procedures at Openbravo

Extending existing Procedures at Openbravo

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();
Advertisements
Categories: gorkaion, Openbravo
  1. Francesco
    October 28, 2009 at 5:28 pm

    If the procedure of extension points fails and raise an error , the system launches a rollback of c_orderpost (or c_invoicepost) or the order is completed anyway ?

  2. gorkaion
    October 28, 2009 at 5:36 pm

    Hi Francesco,

    The exception will be caught by the exception block of the procedure that has the extension point. It will have the same behavior than the exceptions raised in the C_order_post itself. So it will be launched a rollback.

  3. Francesco
    October 29, 2009 at 9:03 am

    Hi

    Great ,
    for now this mechanism there is only on c_orderpost and c_invoicepost,
    I hope it will be extended at all the post procedures
    (c_cashpost,m_inoupost, c_settlementpost , c_bankstatement_post…)

  4. goldwin
    February 16, 2010 at 4:02 pm

    Hi,

    Is it possible to change the existing trigger or add using this functionality?

    • gorkaion
      February 18, 2010 at 10:33 am

      Hi goldwin,

      It is technically possible to add extension points to triggers. But you could also add a new trigger to the table without the need of an extension point.

  5. Ankit Aggarwal
    August 28, 2012 at 8:49 am

    I want to call this procedure(C_Order_post()) in one of my class(by calling this).
    So what all the parameters are required to call the procedure and book the specific order.
    The scenario is that i created purchase order through sales order in my class and now want to book that through java coding. How do i proceeded?
    “CallStoredProcedure.getInstance().call(procedureForCompletingPurchaseOrder,
    parametersForCompletePurchaseOrder, null); ”
    Is this is the right way 2 use that? Or else suggest me some way

  1. October 29, 2009 at 4:04 pm
  2. November 16, 2010 at 9:00 pm
  3. November 4, 2013 at 4:41 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: