Passing Parameters to Stored Procedures in Openbravo

Stored procedures in Openbravo enables us to add business logic to the Application. Whenever some business logic is applied, it is applied based upon certain arguments or parameters. I have presented below, using a simple example, how to use parameters in Stored Procedures in Openbravo.

Consider the process “xyz_update_product_category”. This process just updates the products of a particular category to another new category. In order to achieve this we need two parameters, ‘new_product_category’ and the ‘current_product_category’. When we pass the parameters to stored procedure from the Application Dictionary, it will be bundled in ‘Process Instance Id’ and sent to the procedure. So we need to get the parameter values through the Process Instance which will be there in ad_pinstance table.

The procedure must be as follows.

CREATE OR REPLACE FUNCTION xyz_update_product_category(p_pinstance_id character varying)
RETURNS void AS
$BODY$ DECLARE

-- Logistice
v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2--
v_Result NUMERIC:=1; -- 0=failure
v_NewProductCategory VARCHAR(32); --OBTG:VARCHAR2--
v_CurrentProductCategory VARCHAR(32); --OBTG:VARCHAR2--

--TYPE RECORD IS REFCURSOR;
Cur_Parameter RECORD;

BEGIN
RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
v_ResultStr:='PInstanceNotFound';
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
BEGIN
-- Get Parameters
v_ResultStr:='ReadingParameters';
FOR Cur_Parameter IN
(SELECT 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
IF(Cur_Parameter.ParameterName='new_product_category') THEN
v_NewProductCategory:=Cur_Parameter.P_String;
RAISE NOTICE '%','  New Product Category=' || v_NewProductCategory ;
ELSIF(Cur_Parameter.ParameterName='current_product_category') THEN
v_CurrentProductCategory:=Cur_Parameter.P_String;
RAISE NOTICE '%','  Current Product Category=' || v_CurrentProductCategory ;
ELSE
RAISE NOTICE '%','*** Unknown Parameter=' || Cur_Parameter.ParameterName ;
END IF;
END LOOP; -- Get Parameter

-- Updates the Product with new Product Category
UPDATE m_product
SET m_product_category_id = v_NewProductCategory
WHERE m_product_category_id = v_CurrentProductCategory;

IF(p_PInstance_ID IS NOT NULL) THEN
--  Update AD_PInstance
RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
ELSE
RAISE NOTICE '%','Finished ' || v_Message ;
END IF;

EXCEPTION
WHEN OTHERS THEN
v_ResultStr:= '@ERROR=' || SQLERRM;
RAISE NOTICE '%',v_ResultStr ;
-- ROLLBACK;
IF(p_PInstance_ID IS NOT NULL) THEN
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
END IF;
END;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

After creating the process, it should be defined in the Application Dictionary. The process definition for this process (in Application Dictionary->Reports and Process) is as follows.

This process takes two parameter one for specifying the current product category and other one to specify the new product category that is to be assigned.  These parameters must be defined in Parameters tab of the Application Dictionary->Reports and Process window under the corresponding process. These parameter definition must be as follows.

Note the DB Column Name for the above two parameters. These are the names that should be used in the procedure to get the values of the parameters.

FOR Cur_Parameter IN
(SELECT 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
IF(Cur_Parameter.ParameterName='<strong>new_product_category</strong>') THEN
v_NewProductCategory:=Cur_Parameter.P_String;
RAISE NOTICE '%','  New Product Category=' || v_NewProductCategory ;
ELSIF(Cur_Parameter.ParameterName='<strong>current_product_category</strong>') THEN
v_CurrentProductCategory:=Cur_Parameter.P_String;
RAISE NOTICE '%','  Current Product Category=' || v_CurrentProductCategory ;
ELSE
RAISE NOTICE '%','*** Unknown Parameter=' || Cur_Parameter.ParameterName ;
END IF;
END LOOP; -- Get Parameter

Depending on the data type of the parameter, we can use P_String, P_Number, P_Date columns of the ad_pinstance table to get the values. In our example, since the two parameters are of string data type, we have used v_NewProductCategory:=Cur_Parameter.P_String and v_CurrentProductCategory:=Cur_Parameter.P_String.

As this process is used for bulk update, it cannot be defined as a button in the window. So define it as a separate process and create a menu.

After completing all these steps compile the application and deploy it to see the output.

Process window:

If you click on the menu which we created, it will show up a pop up window as below.

Here we have chose the Current Product Category as ‘Alcoholic’ and the New Product Category as ‘Fruit Juice’. So this process will update all the product which has the product category as ‘Alcoholic’ to ‘Fruit Juice’.

Here you can see the output of this process:

Before executing the process:

After executing the process:

Though I have presented the logic using Stored Procedures, it is better to go for DAL process to add your business logic. For more information on how to pass arguments for DAL Processes refer here. For simple how-to on writing a DAL process, refer here.