Toolbox

The objective of this how-to is to give you a detailed understanding of how to create your own stored procedure and how to call a stored procedure from a menu or window.

Stored procedures are blocks of code that form some of the business logic of Openbravo ERP. They are stored directly within the database which makes them database engine specific and dependent. Openbravo ERP currently only supports Oracle and PostgreSQL databases which have a very similar Procedural Language syntax.

NOTE: This howto only provides code for a PostgreSQL solution. Details on main Oracle vs Postgres can be found here: Oracle vs Postgres SQL code rules.

To be able to browse the database and view existing stored procedures as well as create new ones use one of the database administration tools (e.g., pgAdmin III or phpPgAdmin for PostgreSQL and Oracle SQL Developer or Toad for Oracle). To connect to the correct database instance, use the same database parameters values that you used during Openbravo ERP installation.

In this article we will be adding a new button that recalculates the standard price of a product based on the average of all purchase or sales orders. Using the Openbravo Admin role, navigate to Master Data Management || Product window, select a product, e.g. Beer and switch to 'Price tab. There you will find all prices (purchase and/or sales) that this particular product has, each one attached to a price list version which in turn is part of a price list. Notice how each product has three prices:

List price - the catalog price, usually the highest one.
Standard price - what actually gets charged to the client within a purchase or sales invoice.
Limit price - what the sales agent can lower the price down to.

Let's say we want to have a button in this tab, that recalculates the standard price based on the average of all purchase or sales orders within the last X days, where X should be a parameter that appears in a pop-up and can be entered by the user, having the default value of 180 days (6 months).

The business logic behind this button will be written in PostgresSQL Procedural Language and be part of a stored procedure that must belong to our custom module.

Modulo

All new developments must belong to a module that is not the core module. Please follow the How to create and package a module section to create a new module.

This article assumes that the HT DB prefix has been defined within the module that is in development. Consequently, the name of the stored procedure will have to start with HT_!

Definirla nel Dizionario Applicazione

We will approach the development of our stored procedure in a reverse step fashion. Let's see first how the user interface relates to a stored procedure call.

For Openbravo ERP to know about a particular stored procedure inside the database it must be defined within the application dictionary. To do that, using System Administrator role, navigate to Application Dictionary || Report and Process window and create a new record as indicated below:

Important fields to note are (for more information see the AD_Process table description):

Search Key - Unique identifier of this process.
Name - A user friendly name of this process.
Data Access Level - indicates who will be able to access this process. By selecting Client/Organization, the System Admin will not be able to access this process.
UI Pattern - since the logic and the user interface behind the button will be generated by Openbravo ERP (a pop-up will be generated with an input field for the Days of History), the Standard option should be selected here versus the Manual where the developer must create the entire user interface (controller, view, etc.).
Procedure - the name of the procedure as it is/will be inside the database. Note the HOWTO prefix.

Since we want to input a parameter (the number of days the system should look back for when calculating the average price), we need to specify it within the Parameter tab as shown below:

Fields to note are (for more information see the AD_Process_Para table description):

Name - user friendly name of the parameter that will appear next to the input field of the user interface.
DB Column Name - this name does not represent the meaning enough but this is the name of the parameter that will have to be parsed inside the stored procedure.
Reference - the data type of the input field. Integer in our case means Openbravo ERP will offer a little input field which a calculator assistant next to it. It will also indicate that the field validation for an integer number needs to be performed.
Reference Search Key - if we had selected a non-basic reference above (such as List, Search or Table), this drop-down lists available sub-references.
Length - the number of characters/numbers that can be input. In our case, we are not expecting more than a 4-digit integer.
Mandatory - fields marked as mandatory must have a value before proceeding with the execution of a process.
Default Value - default value (if any) of this parameter. As defined within our scenario, we would like to calculate history for the last 180 days.

Now, Openbravo ERP knows about a process defined by the stored procedure HT_CALCULATE_AVG_PRICE. Thus, we can call it from a button on a window or through a menu item.

Associarla ad un pulsante

Before we can place a button onto the Price tab of the Product window, we need to have a physical placeholder within the database, even if it is not going to hold any data.

Therefore, we need to add a column to the m_productprice table that the Price tab is based on. To do that, execute the following PostgreSQL statement using a Pgadmin tool or similar:

ALTER TABLE m_productprice ADD COLUMN em_ht_calculate_avg_price CHARACTER(1) NULL DEFAULT '';

Due to modularity conventions, columns added to core tables must be prefixed by EM_ on top of the regular module prefix (HT_ in our case). See Table and Column section of the Modularity Developer's Guide for more information on this requirement.

For Openbravo ERP to know about the new physical table column, it needs to be introduced into the application dictionary. Navigate to Application Dictionary || Table and Column, find the M_ProductPrice table (Name = PricingProductPrice), select the Column tab and add a new record as shown below:

The following fields are of importance when adding a new column to the application dictionary (for more information see the AD_Column table description):

Module - the module this additional column belongs to. Only one module should be marked as In Development so this drop-down should be automatically preselected.
DB Column Name - the name of the table column within the physical database.
Name - user friendly name of this column. Note that it MUST be prefixed by EM (since it is an added column to an existing table) and HT as the DB prefix of the module, hence in the form of EM_HT_<custom name>.
Length - maximum length according to the data type of this column.
Reference - data type of the column. Based on this selection the user interface of this field is rendered to the user. In our case a button will be shown.
Process - when a Button is selected in the Reference drop-down, the list of all processes is given here. Selecting the new Calculate Average Price process will cause it to be executed upon the push of this button.

Finally, in order for the button to actually appear to the user, we must add it to the corresponding window. In our case, the button should appear inside the Price tab of the Product window. Hence, navigate to Application Dictionary > Window, Tab and Field, find the Product window and select the Price within the Tab tab. Then, within the Field tab, add a new record as indicated below:

For more detailed information see the AD_Window, AD_Tabe and AD_Field table descriptions.

Associarla a una voce di menu

Stored procedures can also be called standalone from the menu. However, in that case, the Record_ID column of the AD_PInstance table will be NULL (discussed in the next section) since there is no record where the button is triggered from.

To create a new menu item and associate it with a stored procedure, use the System Administrator role to navigate to General Setup > Application > Menu and create a new item:

By switching the role to Openbravo Admin you should see the new menu item on the root level:

Again, keep in mind that this approach will not work in our case since we plan to develop a stored procedure that will recalculate the average of a specific price (hence a specific Record_ID). But we could extend our procedure so that it checks for the absence of the Record_ID and in that case recalculates all prices for all products.

Also, in order to see the parameter pop-up, the application needs to be recompiled.

Teoria delle chiamate a stored procedure

With the steps taken above, the push of the newly defined button will pop up a window with the parameter field to be entered and upon confirmation trigger the stored procedure we're about to write.

Having said that, there are a few specifics to the stored procedure calls that the developer needs to know about so let us present a bit of theory behind it.

This is what happens:

when a button or the menu item is clicked, a popup shows up, listing all parameter input fields and offering the confirm OK button below. Keep in mind that this popup was/will be automatically generated by the compilation process.
Upon the click of the OK button, Openbravo ERP application will automatically do the following:
1. enter one record into AD_PInstance table, logging the call to the specific process
2. enter as many records as there are parameters defined for this process into the AD_PInstance_Para table, storing the selected values of the parameters entered by the user.
The stored procedure is then called with one and only one parameter: AD_PInstance_ID, indicating which rows of the two tables contain all information about the call

As indicated above, the AD_PInstance and AD_PInstance_Para tables act as an intermediary between the Openbravo ERP generated user interface and the actual procedure. Hence, the stored procedure does not get the parameters passed directly with the call and does not return the result back explicitly. The two tables are used instead and only AD_PInstance_ID is passed. Using this parameter, the stored procedure is responsible for retrieving the corresponding records inside the two tables that belong to that specific call. Moreover, the result of the stored procedure should be saved into the AD_PInstance table as opposed to be returned directly using the RETURN statement. Consequently, the two tables also act as a log of all calls.

Tabelle AD_PInstance e AD_PInstance_Para

Each call to a procedure from the application is registered in the table AD_PInstance. The AD_PInstance_Para table stores the values entered for the parameters defined in the Parameters tab of the Reports and Process window of the correspondent procedure.

AD_PInstance table:

AD_PInstance_ID: Table identifier.
AD_Process_ID: Foreign key to the AD_Process table where the procedure is defined in the application dictionary.
Record_ID: If the procedure is called from a window, this column stores the ID of the active record in that window where the button was pressed from.
IsProcessing: While the procedure is running this column is set to 'Y'. Some procedures may and do check if there is an ongoing instance of a call.
AD_User_ID: ID of the user that triggered the call.
Result: 0 Indicates an error during the call, 1 indicates a successful call of the process and 2 indicates a warning.
ErrorMsg: When the procedure finishes, the resulting message needs to be stored here, success or error. This message will be shown to the user.

AD_PInstance_Para table:

Parametername: This column will contain the name of the parameter corresponding to the DB Column Name value set within the Parameter tab of the Report and Process window.
P_String and P_String_TO: Selected/entered values when the parameter is a text box, a drop down list or a foreign key.
P_Number and P_Number_TO: Selected/entered values when the parameter is a numeric text box.
P_Date and P_Date_TO: Entered values for date type parameters.

The _TO suffix columns are used to contain values for when a parameter is defined as a range as opposed to a single value. The generated pop-up window with the parameters will include two fields with the from and to labels. Later those values can be used to execute queries between those values.

Parametri Input di procedure

When the application calls a stored procedure, only one parameter is passed to the database: the corresponding AD_PInstance_ID.

If a stored procedure is going to be called from the application but also from another stored procedure we need an intermediary procedure since in that case there is usually no corresponding AD_PInstance_ID.

See C_Order_Post and C_Order_Post1 as an example. The main procedure (C_Order_Post1 in this case) will have as many input parameters as it requires to do its job, plus the AD_PInstance_ID. It must then contain the logic that checks for presence of AD_PInstance_ID and if present, take parameters from the two tables. Otherwise, it will take the parameters explicitly passed to it by another stored procedure and bypass the storage of the results into the AD_PInstance.

The intermediary procedure (C_Order_Post in this case) is the one defined withing the Application Dictionary and only has a parameter for the AD_PInstance. This one then calls the main one forwarding the AD_PInstance_ID parameter and setting all the others as NULL.

AD_Update_PInstance stored procedure

This procedure updates a specific AD_PInstance record. It needs to be called at the beginning and at the end of the body of any custom stored procedure.

Parameters to the AD_Update_PInstance:

p_PInstance_ID: AD_PInstance_ID that needs to be updated.
p_AD_User_ID: AD_User_ID that is doing the update.
p_IsProcessing: Status of the procedure ('Y' or 'N').
p_Result: Final result of the proceudre (0 for fail - red alert box, 1 for success - green alert box or 2 for warnings - yellow alert box).
p_Message: Error or success text message of the procedure.

Exception block and error management

Potential error messages of a procedure must be properly managed by an exception block while at the same time registering the outcome into the Result and Errormsg fields of the AD_PInstance table using the AD_PInstance_Update stored procedure.

If a problem occurs within the body of your stored procedure use the RAISE_APPLICATION_ERROR to indicate it and interrupt the procedure. A custom error number -20000 can be used.

To return a user friendly error message correctly translated into the language of the current user do not hardcode messages into your stored procedures. Instead use placeholders within the @ sign that contain the actual text inside the AD_Message table. For example, by saving the @MissingDaysHistoryParameter@ into the Errormsg column of the AD_PInstance table, Openbravo ERP will go into the AD_Message table and try to find the text in the correct language for the key MissingDaysHistoryParameter. Of course, custom messages need to be entered and translated using the Application Dictionary > Messages window.

If the procedure will also be called from other procedure(s) apart from directly by Openbravo ERP, the exception block needs to distinguish between the two possibilities. This condition can be worked out by checking the value of the AD_PInstance_ID parameter (NULL in case of call from another procedure).

If called from another procedure, the exception raised should be managed by the parent procedure where the AD_PInstance_ID is available. This does not apply to the intermediary procedures explained above. These don't have the exception block so the exception is managed by the main one.

Before raising an exception using the RAISE statement, it is a good practice to use a DBMS_OUTPUT.PUT_LINE(); (Oracle) or RAISE NOTICE (Postgres) for debugging purposes.

Example of an exception block in a procedure that is only called from another one:

Oracle:

 
 EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('ERROR MA_Standard_Cost_Sequence, sequence_ID '|| p_Sequence_ID || ', date ' || p_CalcDate || 
                ' at ' ||v_ResultStr);
     RAISE;
 END Ma_Standard_Cost_Sequence;

PostgreSQL:

 
 EXCEPTION
   WHEN OTHERS THEN
     RAISE NOTICE '%','ERROR MA_Standard_Cost_Sequence, sequence_ID '|| p_Sequence_ID || ', date ' || p_CalcDate || 
              ' at ' ||v_ResultStr;
     RAISE EXCEPTION '%', SQLERRM;
 END ; $BODY$

When the AD_PInstance exists (i.e. the stored procedure will always be called from within the application), the exception needs to be managed slightly differently in order to store the result and the message. The message is built by concatenating the text @ERROR= with the SQLERRM variable that contains the error message that is thrown by the RAISE_APPLICATION_ERROR or by the database itself. A ROLLBACK; is done (PostgreSQL does not need it explicitly) and finally the AD_PInstance is updated setting the Result column to 0 (indicating a fail) and Errormsg column to the constructed error message. It is also recommended to use DBMS_OUTPUT.PUT_LINE(); (RAISE NOTICE in PostgreSQL) for debugging purposes.

Oracle:

 
 EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
     v_ResultStr:= '@ERROR=' || SQLERRM; 
     DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
     ROLLBACK;
     AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
     RETURN;
 END MA_ProductionRun_Standard;

PostgreSQL:

 
 EXCEPTION
   WHEN OTHERS THEN
     RAISE NOTICE '%',v_ResultStr ;
     v_ResultStr:= '@ERROR=' || SQLERRM;
     RAISE NOTICE '%',v_ResultStr ;
     PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
     RETURN;
 END ; $BODY$

Developing the actual procedure

The actual procedure that performs the task is listed below in PostgreSQL specific code with comments. One can just run it inside a Pgadmin to create the actual stored procedure inside the Openbravo ERP database.

 
 CREATE OR REPLACE FUNCTION ht_calculate_avg_price(p_pinstance_id character varying)
  RETURNS void AS
 $BODY$ DECLARE 
 
  -- variables that will contain the parameters deriving from the AD_PInstance table
  v_Record_ID VARCHAR(32); -- ID of the record inside m_productprice that we are processing
  v_DaysHistory NUMERIC; -- number of days to consider within the calculation
 
  -- operational variables
  v_ResultStr VARCHAR(2000):=''; -- will contain text describing the stage the stored procedure is in
  v_Message VARCHAR(2000):=''; -- will contain the final message to be logged
  v_MProductId VARCHAR(32); -- ID of the product that we are processing
  v_IsSOPriceList VARCHAR(1); -- flag indicating a sales or a purchase price
  v_SumPrice NUMERIC; -- sum of all line amounts that included the specific product
  v_SumQty NUMERIC; -- sum of all line quantities that included the specific product
  Cur_Parameter RECORD; -- cursor variable to loop through all parameters
    
  BEGIN
 
    --  Update AD_PInstance by setting IsProcessing='Y'
    RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
    PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
 
    -- Get Parameters
    v_ResultStr:='ReadingParameters';
    FOR Cur_Parameter IN
      (SELECT i.Record_ID,
        p.ParameterName, p.P_String, p.P_Number, p.P_Date
      FROM AD_PInstance i
      LEFT JOIN AD_PInstance_Para p
        ON i.AD_PInstance_ID=p.AD_PInstance_ID
      WHERE i.AD_PInstance_ID=p_PInstance_ID
      ORDER BY p.SeqNo)
    LOOP
      v_Record_ID:=Cur_Parameter.Record_ID; -- save the m_productprice primary key
      IF (Cur_Parameter.ParameterName='DaysHistory') THEN
        v_DaysHistory:=Cur_Parameter.P_Number;
      END IF;
    END LOOP;
    RAISE NOTICE '%', 'Record_ID = ' || v_Record_ID ;
    RAISE NOTICE '%', 'DaysHistory = '||v_DaysHistory;
 
  BEGIN --BODY
 
    -- Retrieve missing information regarding which product and what type of transaction (purchase/sales)
    SELECT m_productprice.m_product_id, m_pricelist.issopricelist INTO v_MProductId, v_IsSOPricelist
      FROM m_pricelist, m_pricelist_version, m_productprice 
      WHERE m_pricelist_version.m_pricelist_id=m_pricelist.m_pricelist_id 
        AND m_productprice.m_pricelist_version_id=m_pricelist_version.m_pricelist_version_id
        AND m_productprice_id=v_Record_ID;
 
    RAISE NOTICE '%', 'ProductId = '||v_MProductId;
 
    -- Calculate average sales/purchase price for the product based on the DaysHistory parameter
    SELECT SUM(c_invoiceline.priceactual), SUM(c_invoiceline.qtyinvoiced) INTO v_SumPrice, v_SumQty
      FROM c_invoice, c_invoiceline
      WHERE c_invoice.c_invoice_id=c_invoiceline.c_invoice_id
        AND c_invoice.issotrx=v_IsSOPriceList
        AND c_invoiceline.m_product_id=v_MProductId
        AND c_invoice.dateordered>=(now()-(v_DaysHistory||' days')::INTERVAL)
      GROUP BY c_invoiceline.m_product_id;
 
    RAISE NOTICE '%', 'SumPrice = '||v_SumPrice;
    RAISE NOTICE '%', 'SumQty = '||v_SumQty;
 
    -- Update Standard price with the new number
    IF (v_SumPrice IS NOT NULL AND v_SumQty IS NOT NULL) THEN
      UPDATE m_productprice 
        SET pricestd=(v_SumPrice/v_SumQty)
        WHERE m_productprice_id=v_Record_ID;
      v_Message:='@HT_SPUpdatedPrice@'||v_SumPrice/v_SumQty; 
      -- @HT_SPUpdatedPrice@ should be added to the AD_Message table with some text
      -- like 'Updated price to '
    ELSE
      v_Message:='@HT_SPNoTrx@';
      -- @HT_SPNoTrx@ should be added to the AD_Message table with some text
      -- like 'No transactions found hence no update to price performed.'
    END IF;
 
    -- Successfully finish the process by updating AD_PInstance, setting the 
    -- IsProcessing, ErrorMsg and Result
    RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
    PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
    RETURN;
  END; -- BODY
 
 EXCEPTION
 WHEN OTHERS THEN
   v_ResultStr:= '@ERROR=' || SQLERRM;
   RAISE NOTICE '%',v_ResultStr ;
   PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
   RETURN;
 END ; $BODY$
   LANGUAGE 'plpgsql' VOLATILE;

Compiling the Corresponding Window

Before we can see the button inside the Price tab of the Product window, we need to compile it. To do so from the command line, type:

ant compile.development -Dtab=Product

This will compile the Product window and all its sub-tabs. You might have to restart Tomcat depending on your configuration. If using Eclipse, use the eclipse.compile ant task and enter the name Product inside the pop-up that appears.

The Result - Part I

Log into Openbravo ERP and after choosing the Openbravo Admin role, navigate to Master Data Management > Product window, select the Boots product and switch to the Price tab.

Find the price that belongs to the Sales 2006 Price List Version. Double click it and you should see the button:

Notice the Standard Price which is at this point set to 40. Click on the Calculate Average Price button to pop-up the parameter entry and confirmation of our stored procedure execution. Because the most of demo database (SmallBazaar or BigBazaar) transactions are way in the past (in year 2006 etc), enter 2000 (days) here to make sure they get accounted for.

By confirming the dialog, the stored procedure will be executed with the parameter entered and the result should be similar to:

The Standard Price has been recalculated according to the transaction that exist within the system.

However, the resulting message is not very descriptive since it shows only the placeholder (HT_SPUpdatedPrice) that the stored procedure generated. To see the actual message, we need to enter it into the Messages window.

Entering Custom Messages

Since our stored procedure returns two custom messages depending on the outcome of the process (HT_SPUpdatedPrice and HT_SPNoTrx) we need to enter the actual text for these placeholders. These texts are stored in the AD_Message table.

To do so, navigate to Application Dictionary || Message using the System Administrator role and enter a new one as indicated below:

Note that no @ signs should be included here. Those are only used inside the stored procedure to indicate the message identifier.

Now do the same for the second message, this time using HT_SPNoTrx placeholder for the message.

The Result - Part II

By re-running the Calculate Average Price with the same parameter, you should now see the full message as opposed to the placeholders seen before:

Congratulations!

Languages:

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/How_to_develop_a_stored_procedure/it"

This page has been accessed 4,919 times. This page was last modified on 14 June 2011, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.