Execute parameterized PL SQL procedure from OAF page

Let us try to call PL/SQL package from OAF page. We will try to send two interger values to one PL/SQL procedure which will do sum of that numbers and will return back sum of that numbers

1. Create a New OA Workspace and Empty OA Project

File> New > General> Workspace Configured for Oracle Applications

File Name -- ParameterizedProcProj

Project Name – ParameterizedProcDemo

Default Package -- parameter.oracle.apps.fnd.parameterizedprocdemo

2. Create Application Module AM

Right click on ParameterizedProcProj > New > ADF Business Components > Application Module

Name -- ParameterizedProcDemoAM

Package -- parameter.oracle.apps.fnd.parameterizedprocdemo.server

Check Application Module Class: ParameterizedProcDemoAMImpl Generate JavaFile(s)

3. Create a New Page

Right click on ParameterizedProcProj > New > Web Tier > OA Components > Page

Name -- ParameterizedProcDemoPG

Package -- parameter.oracle.apps.fnd.parameterizedprocdemo.webui

4. Select region1 and set the following properties:

ID -- PageLayoutRN

Region Style -- PageLayout

AM Definition --parameter.oracle.apps.fnd.parameterizedprocdemo.server.ParameterizedProcDemoAM

Window Title – Execute Paramterized Procedure Demo Page Window

Title – Execute Paramterized Procedure Demo Page Header

Auto Footer – True

5. Add a New Region MainRN

Select PageLayoutRN right click > New > Region

ID -- MainRN

Region Style – messageComponentLayout

6. Create messageTextInput Items

Create item1

Select MainRN > New > messageTextInput

Set following Properties for Text Item1

ID – item1

Item Style – messageTextInput

Data Type -- Number

Prompt – Text Item1

Maximum Length – 20

Length -- 20

Create item2

Select MainRN > New > messageTextInput

Set following Properties for Text Item1

ID – item2

Item Style – messageTextInput

Data Type -- Number

Prompt – Text Item2

Maximum Length – 20

Length -- 20

7. Create a Submit Button

Right Click on MainRN > New > messageLayout

Select newly created messageLayout right click > New > item

Set Following Properties for newly created item

ID – Sum

Item Style – submitButton

Attribute Set -- /oracle/apps/fnd/attributesets/Buttons/Go

Prompt – Sum

8. Run Your Page UI is ready --

9. Let us create a package and package body which we will call from OAF page

This Package takes three parameters all are number. First two are IN parameters and last is OUT as sum of first two numbers

Package Spec

CREATE OR REPLACE PACKAGE APPS.test_package AUTHID CURRENT_USER
IS
PROCEDURE data_sum
(   item1            IN      NUMBER,
    item2            IN      NUMBER,
    data_sum     OUT  NUMBER
);
END test_package;
/
SHOW ERRORS;
EXIT;

Package Body

CREATE OR REPLACE PACKAGE BODY APPS.test_package 
IS

PROCEDURE data_sum
(   item1          IN    NUMBER,
    item2          IN    NUMBER,
    data_sum  OUT NUMBER
)
IS
BEGIN
 data_sum := item1 + item2;
END data_sum;

END test_package;
/
SHOW ERRORS;
EXIT;

10. Add Following Code in your AMImpl Class (ParameterizedProcDemoAMImpl.java)

import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
import oracle.jdbc.OracleCallableStatement;
import java.sql.Types;
import oracle.apps.fnd.framework.OAException; 
...

public String dataSumAction(String item1,String item2)
{ OADBTransaction oadbtransaction = (OADBTransaction)getTransaction();   OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getTransaction();

 String retValues;

 StringBuffer str = new StringBuffer();
 str.append( " BEGIN ");
 str.append( " test_package.data_sum( ");
 str.append( "       item1           => :1, ");
 str.append( "       item2           => :2, ");  str.append( "       data_sum    => :3  ");
 str.append( "    ); ");
 str.append( " END; ");

 OracleCallableStatement oraclecallablestatement = 
  (OracleCallableStatement)oadbtransaction.createCallableStatement(str.toString(), 1);

 try{
  oraclecallablestatement.setInt(1,  Integer.parseInt(item1) );
  oraclecallablestatement.setInt(2,  Integer.parseInt(item2) );

  oraclecallablestatement.registerOutParameter(3, Types.VARCHAR);

  oraclecallablestatement.execute(); 
                  
  retValues = oraclecallablestatement.getString(3);
 }  catch(Exception e)
 {
  throw OAException.wrapperException(e);
 }
 return retValues;
}

11. Add Controller for Page ParameterizedProcDemoPG Select PageLayoutRN right click Set New Controller

Package Name -- prajkumar.oracle.apps.fnd.parameterizedprocdemo.webui

Class Name -- ParameterizedProcDemoCO

Add Following Code in Controller

import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import java.io.Serializable; 
...

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);
  
 OAApplicationModule am = pageContext.getApplicationModule(webBean);
    
 if (pageContext.getParameter("Sum") != null)        
 {          
  Serializable[] parameters1 = { pageContext.getParameter("item1"),
       pageContext.getParameter("item2"),
      };
                   
  String retVals1 = (String)am.invokeMethod("dataSumAction", parameters1);

  String message = "Sum:  " + retVals1;                   
  throw new OAException(message, OAException.INFORMATION);
 }
}

12. Congratulation you have successfully finished. Run Your ParameterizedProcDemoPG page and Test Your Work

a

b

c

END

SPEL In OAF

SPEL stands for “Simplest Possible Expression Language”.

SPEL is used to carry the values dynamically. SPEL will get the value dynamically at runtime. SPEL is the Boolean Value (True / False). The syntax of SPEL is:

Now let us see the previous chapter output which shows the Employee Information when we click on “Get Employee Information” button now the same output we are adding one image icon so that it appears to each and every row as shown in the below figure:

Now we will see the process how to add edit image for each row in a table. For that first we need a page structure as follows:

SPEL stands for “Simplest Possible Expression Language”.

SPEL is used to carry the values dynamically. SPEL will get the value dynamically at runtime. SPEL is the Boolean Value (True / False). The syntax of SPEL is:

Now let us see the previous chapter output which shows the Employee Information when we click on “Get Employee Information” button now the same output we are adding one image icon so that it appears to each and every row as shown in the below figure:

Now we will see the process how to add edit image for each row in a table. For that first we need a page structure as follows:

In the above page structure we created a region Using Wizard and the region style is “Table”.  Item1 is the submit button whenever we click on the Submit Button(Get Employee Information) it displays the employee information as shown in the below figure:

Now go to the table region, right click on the table region select New -> Item.

In the Property Inspector of item do the following changes:

1. Select the Item style as
2. In Image URI give the name of the image for example for edit image we have predefined images in “p9879989_R12_GENERIC\jdevhome\jdev\myhtml\OA_MEDIA” , give the following image in Image URI /OA_MEDIA/detailsicon_enabled.gif.
3. In client action select Action Type as “fireAction“.
4. In client action give Event name as “Edit“

Now the page structure looks like:

Now run the page and click on the button “Get Employee Information” so that the output looks like the above picture what we have seen early.

Now whenever we click on the image item then it must show us the information in different form, for that we need to write the following code in processFormsRequest of controller:

if(“Edit”.equals(pageContext.getParameter(EVENT_PARAM)))

{

pageContext.setForwardURL(“OA.jsp?page=/xxapples/oracle/apps/po/insertingrecords/webui/InsertDataPG”,

null,

OAWebBeanConstants.KEEP_MENU_CONTEXT,

null,

null,

true,

OAWebBeanConstants.ADD_BREAD_CRUMB_YES,

OAWebBeanConstants.IGNORE_MESSAGES);

}

in IF condition “Edit” is the Client Action Event Name which we have given. The remaining code logic is same which we used in previous chapter takes us to create employee page but here we need to carry the data to that page rather than creating employee.

To achieve these requirement we need to create a spell, keep continue reading so that we will come to know how to create a SPELL in OAF.

SPEL creation in OAF, we know the syntax of SPEL now we need to create a SPEL for the image item in a table.

The SPEL value for the above is as follows:

${oa.EmployeeSearchVO1.Empno}

EmployeeSearchVO1 is the name of the VO which is used to get the details of employees.

Empno is the primary key value (attribute name)/ column name of the table in view.

Now we need to pass the above spel to image, for that select the image and then in Property Inspector,

Client Action -> Parameters

Once we click on the icon a Parameters window will get open in that, click on Add Parametersbutton and then give some name (example pempno) in the value pass the SPEL what we created ( ${oa.EmployeeSearchVO1.Empno} ). After giving name and passing SPEL value in the Value field click on OK button.

Now we need to call this parameter in the Create Employee page CO because whenever we click on the image item in the table list showing employee details it takes us to the Create Employee Page but it will not carry any values here our task is to carry values.

So therefore we need to write the code in processRequest of the controller class of Insert Employee details page. The code is as follows:

public void processRequest(OAPageContext pageContext, OAWebBean webBean)

{

super.processRequest(pageContext, webBean);

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

if(pageContext.getParameter(“pempno”)!=null)

{

String hid = pageContext.getParameter(“pempno”).toString();

String whereclause = “EMPNO='”+hid+”‘”;

am.getEmployeeCreateVO1().setWhereClause(null);

am.getEmployeeCreateVO1().setWhereClause(whereclause);

am.getEmployeeCreateVO1().executeQuery();

}

else

{

am.InsertEmployeeRecord();

}

}

Now rebuild the code, rebuild the pages run the Employee Information Page, click on the Get Employee Information button and then click on the image item which is displayed on each available rows so that it takes us to create employee page but here it carries the row values. The sample output is shown in the below figure:

END

Get Host Name and URL of OAF Page

This blog is about to get Host name and URL of OAF page. We will try to implement one button which will give host and URL of OAF page

1. Create a New OA Workspace and Empty OA Project

File> New > General> Workspace Configured for Oracle Applications

File Name -- HostandURL

Project Name – HostandURLProj

Default Package -- host.oracle.apps.fnd.hostandurl

2. Set Run Options in OA Project Setting

Select Your Project in the Navigator and choose Project Properties

Select Oracle Applications > Run Options

Select OADeveloperMode and OADiagnostic, and move them to selected Options List

3. Create Application Module AM

HostandURLProj right click > New > ADF Business Components > Application Module

Name -- HostandURLAM

Package -- host.oracle.apps.fnd.hostandurl.server

Check Generate JavaFile(s)

4. Create a OA components Page

HostandURLProj right click > New > OA Components > Page

Name -- HostandURLPG

Package -- host.oracle.apps.fnd.hostandurl.webui

5. Modify the Page Layout (Top-level) Region

Attribute	Property
ID	PageLayoutRN
Region Style	pageLayout
Form Property	True
Auto Footer	True
Window Title	Get Host and URL Window Title
Title	Get Host and URL Page Header
AM Definition	host.oracle.apps.fnd.hostandurl.server.HostandURLAM

6. Create the Second Region (Main Content Region)

Select PageLayoutRN right click > New > Region

Attribute	Property
ID	MainRN
Region Style	messageComponentLayout

7. Create a container Region for Go-Button

Select MainRN right click > New > messageLayout

Attribute	Property
Region	ButtonLayout

8. Create a Item (Go Button)

Select ButtonLayout > New > Item

Attribute	Property
ID	Go
Item Style	submitButton
Attribute Set	/oracle/apps/fnd/attributesets/Buttons/Go

9. Add a Controller

Select MainRN right click > Set New Controller

Package Name -- host.oracle.apps.fnd.hostandurl.webui

Class Name -- HostandURLCO

10. Edit Your Controller

Add Following Code in processFormRequest

import java.net.*;
import java.io.*;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAPageContext;

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);
 if (pageContext.getParameter("Go") != null)
 {
  // To get Host Name
  try
  {
   InetAddress addr = InetAddress.getLocalHost();
     byte[ ] ipAddr = addr.getAddress();
     String hostname = "Host Name: " +addr.getHostName();
     pageContext.putDialogMessage(new OAException(hostname,
    OAException.WARNING));
  }
  catch (UnknownHostException e)
    {
    }
  // To Get Page URL
 
  String pageURL = "Current Page URL: " + pageContext.getCurrentUrl ();
 
  pageContext.putDialogMessage(new OAException(pageURL,
    OAException.WARNING));
 }
} 

11. Congratulation you have successfully finished. Run Your page and Test Your Work

a

b