Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

How to execute a VO on an OA Framework page load

by

Before telling you how to execute a VO on page load, I assume that you have your VO created and also have attached the VO to the correct AM (Application Module).

Now, let’s assume that the name of your VO is xxTrialVO and also the name of the AM is xxTrialAM.

Let’s take this stepwise for a better understanding.

Step 1 import the AMImpl into your controller attached to the OA Framework page.
To do this just write the following code.
import oracle.apps…..server.XXTrialAMImpl;
Note: the import path will be different for you and will depend on the folder structure that you have defined.

Step 2 import the VOImpl into your controller attached to the OA Framework page.

import oracle.apps…..server.XXTrialVOImpl;
Note: the import path will be different for you and will depend on the folder structure that you have defined.

Steps 3 before the starting of processRequest method create an instance of the xxTrialAMImpl as shown below.

public xxTrialAMImpl am;

Step 4 in the processRequest method you need to instantiate the application module.

am = (xxTrialAMImpl)pageContext.getApplicationModule(webBean);

Step 5 Now we need to create an instance of the xxTrialVOImpl and then execute it.
XXTrialVOImpl TrialVO = (XXTrialVOImpl)am.getXXTrialVO1();
TrialVO.executeQuery();

Thanks for reading.

How to execute a VO on click of a button on an OAF page

by

Here we will discuss the execution of a VO on click of a submit button.

Now, before starting the discussion I assume that you have created a VO and have the same attached to AM. Also, I assume that you have imported the VOImpl and AMImpl to the controller attached to the page.
One more assumption I would like to make is that the VO name is xxTrialVo, AM name is xxTrialAm and the id of the submit button is xxSubmitButton.

After these assumptions, we are ready to learn how to execute a VO on click of a submit button.

First of all you need to check the click of the submit button. As submit button will post an HTTP request, hence you need to handle the click of the submit button in processFormRequest method. Just see the code below for reference.

if(pageContext.getParameter(“xxSubmitButton”)!=null)
{
pageContext.putSessionValue(“Click”,”Yes”);
pageContext.setForewardURLtoCurrentPage(//pass the required parameters);
}

Now the code above shows that you have caught the event of submit button click.
But our purpose was to execute the VO after submit button click, as we know that once the submit button is clicked the control travels from processFormRequst to processRequest method. Hence, now we will take the advantage of sessionValue that we have put in the code to check whether the control is coming back after submit button click or not. If it is then we will execute the VO.

In order to do this, in the processRequest method, refer to the code below.
if(“Yes”.equals(pageContext.getSessionValue(“Click”))
{
XXTrialVOImpl TrialVO = (XXTrialVOImpl)am.get XXTrialVO1();
TrialVO.executeQuery()
}

Hope you find this informative.
Thanks for reading 🙂

Oracle Apps Framework MDS Repository

by

Heard it already? Yes, it’s one of the most important aspects of OA Framework that mostly remains invisible for the engineers working on OAF. So let’s dig into it and try to find out more about the MDS.

MDS stands for Meta Data Services and it is the declarative metadata repository used with OA Framework applications. Yes, I know that was a hard jargon ;). To be simple MDS is just an oracle database. As it’s an oracle database it can be managed using standard database procedures and tools.

It is essentially a database, which comes pre-seeded with schemas to support Oracle e-business Suite. These stored schemas are used by OA Framework to determine the layout of the OA framework pages.

To maintain the HTML rendering property of an OAF page the OA Framework makes the use of UI XML. The UI XML is used to describe the hierarchy of the components and also the components themselves on an OA Framework page. It’s all the power of UIX that allows metadata to be translated to an HTML page for a web browser or mobile device browser.

The whole OA Framework page is stored in the form of XML files in the exact format as defined by the MDS. Whenever a personalization is created through the OA Personalization Framework that personalization is added on top of the base product Meta data. Hence, the newly created personalization just sits on top of the base product UI and also on the base personalization, therefore the base personalization are not affected during patch application or while doing upgrades.

JDR_UTILS PL/SQL package supports the MDS repository and can be used to query and maintain the repository.
Now let’s see how, all that is mentioned above, happens.
MDS repository has 4 tables in all. Just 4 tables? Yes you heard it right it has only 4 tables and that’s it. These 4 tables can manage all the stuff that we have discussed till now.
These 4 tables are:
1. JDR_PATHS: Stores the path of the documents, OA Framework pages and their parent child relationship.
2. JDR_COMPONENTS: Stores components on documents and OA Framework pages.
3. JDR_ATTRIBUTES: Stores attributes of components on documents and OA Framework pages.
4. JDR_ATTRIBUTES_TRANS: Stores translated attribute values of document components or OA framework pages.

For your understanding I request you to please query these tables to find out how an OA Framework page is stored in MDS. It’s really fun to learn how a whole page along with all the information is just handled in only 4 tables. Just query them.

Thank you for your support.
Till the next post
Happy Learning

How to execute a parameterized PL SQL stored procedure from an OA Framework page?

by

Today, we will discuss on how to call a PL SQL stored procedure, which takes in and returns parameters, from an OA Framework page controller.

Before starting the discussion, I assume that you have a valid PL SQL package created and compiled in the database, also I assume that the name of the PL SQL package is xx_trial_package and the name of the procedure is xx_trial_proceudure, and the procedure takes an integer (person_id as first parameter) and returns two strings (employee_number and full_name as second and third parameter) respectively. Also I assume that the name of the AM is xxTrialAM.

Let’s do this in steps for easy understanding and remembrance.
note Please note that all the lines that come in italics are the java code lines, this is done for easy segregating code from text in the post.

Step 1: First step is to import your application module xxTrialAM into the controller (say xxTrialPGController) and then import the following packages also into your controller.

import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.jdbc.OracleCallableStatement;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;

Step 2: Now, before the processrequest method in the controller create handles for xxTrialAM, OADBTransaction , OracleCallableStatement and also create a new variable of String type as shown below
private xxTrialAM am ;
public OADBTransaction dbtrans;
public OracleCallableStatement callablestatement
String query_String = new String();
String employee_number = new String();
String full_name = new String();

Step 3: In the processrequest method after the
super.processRequest(pageContext, webBean);
line write the following lines of code
am = (xxTrialAmImpl)pageContext.getApplicationModule(webBean);
dbtrans = am.getOADBTransaction();

Step 4: Now, as mentioned earlier the String type that we have created will come into picture.
Just write the code as
query_String = “BEGIN xx_trial_package.xx_trial_procedure(:1,:2,:3);END;”;
callablestatement=(OracleCallableStatement)dbtrans.createCallableStatement(query_String,0);
try
{
callablestatement.setInt(1,pageContext.getEmployeeId());
/*Here the setInt() method is used as the parameter that we have to pass to the procedure is of the type Integer. If the parameter is of String type then use setString method. If the parameter is of some other type then use the appropriate setter method. Also note that in the setInt() method in the example the first parameter is 1 which corresponds to the 1st parameter in the procedure.*/

callablestatement.registerOutParameter(2,OracleTypes.VARCHAR,0);
/*This line registers the parameters that will be given out by the procedure after successful execution. Note that in the registerOutParameter() method the number 2 corresponds to the 2nd parameter from the procedure.*/
callablestatement.registerOutParameter(3,OracleTypes.VARCHAR,0);
callablestatement.execute();
employee_number = callablestatement.getString(2);
/* in this line we are getting the value of the out parameter number 2 into a local variable.*/
full_name = callablestatement.getString(3);
callablestatement.close();
}
catch (SQLException e)
{
System.out.println(“The Error is ” + e);
}

And it’s done.
You have just now executed a PL SQL stored procedure, which takes parameters and return values, from OA Framework page controller.
Till my next post, take care and thanks for reading 🙂

How to execute a PL SQL stored procedure from an OA Framework page?

by

Executing a PL SQL stored procedure from an OA Framework page is a very common requirement and that’s the reason why we will discuss this today. There are many ways to do this, you can use a callablestatment and execute a package or you can also create a concurrent program and then call that concurrent program from OA Framework page to execute the PL SQL stored procedure.
We will take a look at the first method here i.e. calling a PL SQL stored procedure using a callablestatment from OA Framework page.

Before starting the discussion I assume that you have a valid PL SQL package created and compiled in the database, also I assume that the name of the PL SQL package is xx_trial_package and the name of the procedure is xx_trial_proceudure. Also I assume that the name of the AM is xxTrialAM.
Let’s do this in steps for easy understanding and remembrance.

notePlease note that all the lines that come in italics are the java code lines, this is done for easy segregation code from text in the post.

Step 1: First step is to import your application module xxTrialAM into the controller (say xxTrialPGController) and then import the following packages also into your controller.

import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.jdbc.OracleCallableStatement;
import java.sql.SQLException;

Step 2: Now, before the processrequest method in the controller create handles for xxTrialAM, OADBTransaction , OracleCallableStatement and also create a new variable of String type as shown below
private xxTrialAM am ;
public OADBTransaction dbtrans;
public OracleCallableStatement callablestatement
String query_String = new String();

Step 3: In the processrequest method after the
super.processRequest(pageContext, webBean);
line write the following lines of code

am = (xxTrialAmImpl)pageContext.getApplicationModule(webBean);
dbtrans = am.getOADBTransaction();

Step 4: Now, as mentioned earlier the String type that we have created will come into picture.
Just write the code as
query_String = “BEGIN xx_trial_package.xx_trial_procedure();END;”;
callablestatement=(OracleCallableStatement)dbtrans.createCallableStatement(query_String,0);

try
{
callablestatement.execute();
callablestatement.close();
}
catch (SQLException e)
{
System.out.println(“The Error is ” + e);
}

And it’s done.
You have just now executed a PL SQL stored procedure from an OA Framework page. In my next post we will take a look at how to execute a PL SQL procedure, which takes parameters and return values, from OA Framework page.
Till then, take care and thanks for reading 🙂