Log in / create account
View source | Discuss this page | Page history | Printable version   

ERP 2.50:Developers Guide/How to develop a stored procedure/it

Languages: 

English  |  Italiano | Translate this article...

Contents

Obiettivo

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:

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.

Bulbgraph.png   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:


CreateSP1.png


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

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:


CreateSP2.png

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

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:


CreateSP3.png


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

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:


CreateSP4.png


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:


CreateSP4.5.png


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


CreateSP4.6.png


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.


CreateSP5.png

This is what happens:

  1. 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.
  2. 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.
  3. 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_Para table:

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:

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:


CreateSP6.png


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.


CreateSP7.png


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


CreateSP8.png


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:


CreateSP11.png


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:


CreateSP12.png


Congratulations!


Languages: 

English | Translate this article...

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

This page has been accessed 5,138 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.