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:
- 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.
- Initializing required variables. (lines 06-10) It is useful to declare 2 variables:
- 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().
- v_extension_point_id with the identifier of the extension point.
- 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.
- 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.
- 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.
- 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:
- The procedure only has one input parameter, the execution instance id.
- It has to retrieve the needed parameters that are available from the ad_ep_instance_para table.
- It has to update the output parameters in the ad_ep_instance_para table.
- 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.