PL-SQL code infrastructure

Introduction

This document discusses about the Openbravo ERP infrastructure for stored PL/SQL procedure. These procedures define processes in Application Dictionary.

They are implemented by standard PL/SQL (for Oracle) or PL/pgSQL (for PostgreSQL) stored procedures. It is just required to take into account some particularities about how these procedures are integrated with the rest of the application. It is also necessary to follow some coding rules in order to make it possible to export/import to xml files using DBSourceMangar.

Infrastructure

AD_PInstance and AD_PInstance_Para tables

Before starting coding the procedure is important to understand how it will be called from the application.

When a procedure is called a new record is created in AD_PInstance table, this record contains the information about the ID for the record that the procedure was called from (in case the process is invoked by a button). This record in AD_PInstance table is also used to get the message the procedure left on it to be shown to the user after the process is completed.

In case the process has parameters defined a new record is created for each of them in AD_PInstance_Para this record contains the information related to the param such as its name (DB Column name) and the value the user assigned to it.

The ID for the newly created record in AD_PInstance table is the only parameter passed to the stored procedure.

Procedure definition

The header for a procedure implementing a process looks like:

Oracle <source lang="sql"> CREATE OR REPLACE PROCEDURE HR_TEST(p_PInstance_ID IN VARCHAR2) </source>

PostgreSQL <source lang="sql"> CREATE OR REPLACE FUNCTION HR_TEST(p_PInstance_ID character varying) RETURNS void </source>

First of all take a look to the procedure name, it follows the modularity naming rules, this is, it starts with the module's DBPrefix.

Note that the only parameter the procedure receives is a string one, it will contain the UUID for the key of the AD_PInstance record generated for its invocation.

Retrieving parameters

Oracle and PostgreSQL

<source lang="sql">

   SELECT Record_ID, CreatedBy
     INTO v_Record_ID, v_User_ID
     FROM AD_PInstance
    WHERE AD_PInstance_ID = p_PInstance_ID;
    
   FOR Cur_Parameter IN
             (SELECT p.ParameterName,
                     p.P_String,
                     p.P_Number,
                     p.P_Date
             FROM AD_PInstance_Para p
             WHERE AD_PInstance_ID=p_PInstance_ID
             ORDER BY p.SeqNo)
   LOOP
     IF(Cur_Parameter.ParameterName='DateFrom') THEN
       v_DateFrom:=Cur_Parameter.P_Date;
     ELSIF(Cur_Parameter.ParameterName='Activate') THEN
       v_Activate:=Cur_Parameter.P_String;
     END IF;
   END LOOP; -- Get Parameter

</source>

The piece of code above is an example about how to parameters can be retrieved.

The first select obtains from AD_PInstance the IDs (UUIDs) for the user that invoked the process and the record it was called from. The record only makes sense in case the process is called using a button in a tab, in this case this is the way to identify that record, this is used for processes that affect to the current record.

After that there is a loop to obtain all the parameters, this loop iterates only in case the process has parameters defined. Here notice that the parameter is identified by ParameterName which matches DB Column name defined in the paramter. Depending on its reference the actual value is stored in one of the following columns: P_String, P_Number or P_Date.

Updating AD_PInstance

AD_PInstance table has a IsProcessing column, which indicates whether an instance is currently being processed or not. At the beginning of the process the instance should be set as processing with:

<source lang="sql">

 AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL);

</source>

Once the process is finished, the instance should be set again to not processing. Not only that, this instance will be used to display the result of the process which will be shown in the UI. This is done with:

<source lang="sql">

 AD_UPDATE_PINSTANCE(p_PInstance_ID, v_User_ID, 'N', v_Result, v_Message);

</source>

Here the v_Result parameter is a numeric value, it can be 0 for error or 1 for success. The v_Message is the message that will be shown, for further information about how to manage messages read the section below.

Managing exceptions

The exceptions in a process should be captured in order to insert the proper message in the PInstance table to be correctly displayed to the user. Thus is a good practice to have an EXCEPTION section to catch all the exceptions in the body of the procedure.

This would be a complete procedure with the EXCEPTION section

Oracle

  CREATE OR REPLACE PROCEDURE HR_TEST(p_PInstance_ID IN VARCHAR2) AS
  
  BEGIN
  
  --Your code here
  
  EXCEPTION
    WHEN OTHERS THEN
       v_ResultStr:= '@ERROR=' || SQLERRM;
        DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
        IF(p_PInstance_ID IS NOT NULL) THEN
          ROLLBACK;
          AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
        ELSE
          RAISE;
        END IF;
  END HR_TEST;

PostgreSQL

  CREATE OR REPLACE FUNCTION HR_TEST(p_PInstance_ID character varying) RETURNS void AS
  $BODY$ 
  
  BEGIN
    --Your code here
  
  EXCEPTION
    WHEN OTHERS THEN
      v_ResultStr:= '@ERROR=' || SQLERRM;
      RAISE NOTICE '%',v_ResultStr ;
      IF(p_PInstance_ID IS NOT NULL) THEN
        PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
      END IF;
      RETURN;
    END ; $BODY$
  LANGUAGE 'plpgsql' VOLATILE

As all exceptions in the body are caught to correctly take the message from it and add it to the PInstance setting it to result 0 (error), it is possible to raise custom exceptions when some logical error happens during the execution, because they will also be caught. For example the follow piece of code does some checks and in case they fail an exception is raised.

Oracle

  IF checkFails THEN
    RAISE_APPLICATION_ERROR(-20000, '@HR_SomeNiceMessage@');
  END IF;

PostgreSQL

  
  IF checkFails THEN
    RAISE EXCEPTION '%', '@HR_SomeNiceMessage@'; 
  END IF;

For further explanations on messages read the Messages documentation.

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. This is useful to extend the functionality of existing procedures that have an extension point. For example, using the C_Order_Post - Finish Process extension point. It is possible for any module to add a procedure to be run whenever an Order is processed. This has been used in the Intercompany Documents module to generate a matching order in the organization of the original order's business partner.

There is a complete list of available extension points in the Reference section of the Developers Guide

How to create an extension point

The objective of this how-to is to show how you can create a new extension point on an existing PL Function.

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;