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.

Usage of TOAD in Oracle PeopleSoft Production Environment

As you the reader might be aware, TOAD is database development and administration tool that helps us to monitor the Database.

Here, we are going to discuss about the usage of TOAD in a PeopleSoft Production Database. Most PeoplSoft geeks knows that all the process which we run in the PoepleSoft Pure Internet Architecture will be scheduled in as a Process/JOB in the Process Scheduler, which we can look by navigating into the following navigation

PeopleTools > Process Scheduler > Process Monitor

Process-Monitor-PeopleSoft

let us assume “PRCSYSPURGE” is a process which over runs in the scheduler.

Note: All the process which we run in the scheduler will have a session in the Database and it will be in Active mode when we run it and it will go to Inactive mode as soon as it ends.

We can view all the Active/InActive sessions of the database by using any one of the Database Administration Tool. Here for instance we are taking TOAD as our Tool to identify, fix the myth behind this process.

So, Basically by navigating into the following location in TOAD, we can able to open all the Database Sessions.

DBA > Session Browser

Toad-Oracle-PeopleSoft

The Status tab will display all the Active/ InActive Sessions which has/had sessions in the Database.

Here we have to choose the suspect and see what could be the reason behind the over run. We could identify it by viewing the following option in the session browser.

IO – To identify whether the process is performing any I/O related activities, When you don’t see a change in their Block reads, physical Reads etc., you can assume that the process has an issue in the IO.

Waits – Will tell you wait cycle for each IO operation that we perform, by using the wait time we can determine the status of the process.

Current Statement – This is used to fetch the current SQL that is executed. by examining the Statement we should able to trace the reason for the overrun.

Note: If you find “SQL BODY UNAVILABLE” which means that you don’t have access to view the SQL.

Locks – will tell you if the process has locks init, in such case, releasing the lock will take the process to the closure.

Long Ops – It is used to differentiate the Time Remaining and Elapsed Time.

Statistics – Will reveal the list of CPU time cycles taken for each operation performed within process.

By analysing the above option, an Admin must able to reveal the myth behind the process overrun.

Setting Date field in Openbravo Callout

Callout in Openbravo is the feature that enables us to do runtime operations with the data. Callout is generally attached to a field and triggered in the onchange event. Openbravo has implemented a java class file called SimpleCallout.java, that we can extend and use it for implementing Callouts. For more information on implementing callouts, refer here and here. To get lastchanged value, refer here.

When manipulating data values, I found it little tough when setting date values. So I have provided the code that I used to set date values in Openbravo Callout.

package org.openbravo.erpCommon.ad_callouts;

import javax.servlet.ServletException;
import java.io.*;
import java.util.*;
import java.text.*;
import java.lang.*;

import org.openbravo.utils.FormatUtilities;
import org.apache.log4j.Logger;
import org.hibernate.criterion.Expression;
import org.openbravo.erpCommon.ad_callouts.SimpleCallout;
import org.openbravo.base.secureApp.VariablesSecureApp;
import org.openbravo.dal.service.OBDal;

public class TrialCallout extends SimpleCallout {

	private static Logger log = Logger.getLogger(TrialCallout.class);
	@Override
	protected void execute(CalloutInfo info) throws ServletException {

		String str="25-10-2010";

		int date=Integer.parseInt(str.substring(0,2));

		int month=Integer.parseInt(str.substring(3,5));

		int year=Integer.parseInt(str.substring(6,10));

		Date d=new Date(year-1900,month-1,date);

		String DATE_FORMAT = "dd-MM-yyyy";
		//Create object of SimpleDateFormat and pass the desired date format.
		SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
		log.info("Date is " + sdf.format(d) );
		info.addResult("inpdateacct",sdf.format(d));

	}
}

Happy Working..:)

Retrieving last changed value in Openbravo Callout

Callout in Openbravo is the feature that enables us to do runtime operations with the data. Callout is generally attached to a field and triggered in the onchange event. Openbravo has implemented a java class file called SimpleCallout.java, that we can extend and use it for implementing Callouts. For more information on implementing callouts, refer here and here.

As Callouts are attached to a field, you can use the field name with the prefix ‘inp’ to retrieve the run time value. Based upon that, you can do manipulations. But using this method, restrict the usage of that callout to a particular field. Say for instance I have a same logic to be implemented across five different fields, then I have to write 5 different callout, as we are hard coding the field names. But there is also a method provided to retrieve the last changed field and then using that, you can retrieve that value.

I have provided below the code, that retrieves the name of the last changed field and then the value of the corresponding field.

package com.fugoconsulting.xxx.erpCommon.ad_callout;

import javax.servlet.ServletException;
import java.util.*;
import java.lang.*;

import org.openbravo.utils.FormatUtilities;
import org.apache.log4j.Logger;
import org.hibernate.criterion.Expression;
import org.openbravo.erpCommon.ad_callouts.SimpleCallout;
import org.openbravo.base.secureApp.VariablesSecureApp;
import org.openbravo.dal.service.OBDal;

public class NameValidation extends SimpleCallout {

	private static final long serialVersionUID = 1L;
	private static Logger log = Logger.getLogger(NameValidation.class);
	@Override
	protected void execute(CalloutInfo info) throws ServletException {

		//Gets the last changed field
		String lastChanged = info.getStringParameter("inpLastFieldChanged",null);

		int length = lastChanged.length();

		//Gets the value of the last changed field
		String lastChangedValue = info.getStringParameter(lastChanged,null);

		log.info("Last field changed:"+lastChanged);
		log.info("Last field length:"+length);

	}
}

Sub-Reports in Openbravo

Report is one of the key USP for any ERP application. The more enhanced and flexible the reports are, more happy is the customer. In Openbravo, jasper reports provides us the flexibility to handle all this. iReport is one  tool that can be used to create jasper reports. I have provided a simple example of how to add sub-report for an Openbravo Report using iReport.

Main Report:

Assume we have created a main report with the following query and layout.

Adding Sub Report:

The Sub Report can be created by adding the sub report icon as follows.

The sub-report can be added by drag and dropping the highlighted portion in the above screen shot. It will open the sub report wizard.

The sub report can be created in any one of the following three ways. Each way has different number of configuration steps.

1. A new sub report can be created using the wizard by using the “Create a new Report” Option. This will ask for all the options needed to create a new report including the report query. This will go through all the 7 steps.

The Sub Report expression can be any one of the following two options.

1. Through SUBREPORT_DIR Parameter

This will create a parameter SUBREPORT_DIR in Main Report. We have to point this parameter to the folder containing the sub report.

2. Through absolute path.

2. We can also call a already existing report as a sub report. This can be done by “Use an existing Report”  option. We have to provide the path of the existing report. This will directly go to step 6 skipping the steps before that.

3. The last option will just create a blank sub report.

Adding Parameter:

In the Report Properties window, there is an option called parameter. This option enables us to add the parameter for that particular report.

Any number of parameters can be provided to the report through this option.

Sub Report Query:

Note that you can see the parameter in the available parameters list in the above screen shot. We can also add the parameter through the “New Parameter” button in the above screen shot.

Importing Report in Openbravo:

For importing the report into openbravo,  the jrxml files should be placed inside your module. For instance, if your module is XYZ with the Java Package com.fugoconsulting.XYZ, then the jrxml file should be inside the folder modules/com.fugoconsulting.XYZ/src/com/fugoconsulting/XYZ/erpReports/.  Before importing the report into openbravo, we need to give the relative path for the sub-report and the images that  we have added. This will make the report work in all systems irrespective of the path of the report. In order to give relative path, you need to add the following two parameters in main report.

1. BASE_DESIGN:

This parameter will be resolved at run time and will be pointing to your module folder(In our case modules/com.fugoconsulting.XYZ/src). The sub report expression must be modified as follows. This can be modified using the property “Sub report Expression”.

2. BASE_WEB:

This parameter will be resolved to point the web folder at run time. This parameter is used to give the relative path for images. The sub report expression must be modified as follows.

Registering in Reports and Process:

Create menu and compile the application. The output will be,

For creating charts using iReport refer here.