E-Business Suite & Formspider Integration

1. Introduction

Oracle E-Business Suite (EBS) covers a wide range of business processes. However, there are use cases where you want to extend your information system beyond the range of Oracle EBS. To achieve this, you can easily create complementary applications with Formspider which are securely integrated with Oracle EBS and its data.

This tutorial explains how to build a Formspider application using EBS data and how to call a Formspider application from EBS main menu.

2. Architecture

2.1 Formspider Architecture

In a Formspider application, a client request goes to the database through the middle tier. By default, Formspider uses Apache Tomcat and it can also be deployed into any JEE compatible web application servers. Formspider middle tier uses JDBC connection and connects to the Formspider schema in the database. Formspider schema is the engine of the Formspider framework.

Each Formspider application has its own schema. Formspider runs an application by running its queries, delete-update-insert statements, PL/SQL procedures in the application’s own schema. For instance, if your application data model (tables, packages…) are in SCHEMA1, then Formspider runs the queries of the application in SCHEMA1. In Formspider jargon, SCHEMA1 is called as the “datasource schema” of the application.

Formspider Architecture



2.2 EBS Architecture

EBS has a middle tier, as well. EBS middle tier connects to the APPS schema in the database. EBS has a separate schema for each of its modules. APPS is the main schema which has the public API’s of EBS database tier. APPS contains synonyms referencing objects of the other EBS schemas.

EBS Architecture



3. Integrating Architectures

To make Formspider work with EBS data, install Formspider database tier to the same database that EBS lives. Application Servers can stay separate. You can find detailed information about installing Formspider here.

In the scenario presented in this tutorial, a user calls a custom Formspider Application from the EBS main menu.

To achieve this, create a new Formspider application and a new datasource schema for this application called “FS_APPS”.

3.1 Calling Formspider Application From EBS Main Menu

To call an external application from the EBS menu, define the menu item and its function. This function opens a URL pointing to the Formspider application. In order to bypass the EBS authentication for the Formspider application, (since the user is already logged in to the EBS) pass several EBS session parameters like user id, application id, etc… to the Formspider application and use these parameters for authorizing the Formspider application automatically.

The EBS session parameters should not be passed to the Formspider application in the URL string because of security reasons. Therefore, instead of calling the Formspider application directly, the EBS function calls a custom JSP page, named “fs.jsp” added to EBS middle tier. When a request to open the Formspider application arrives to fs.jsp, it validates the user session and connects to the APPS schema to create access token which is essentially a key-value pair, in the database. This access token is stored in a table. The key is a randomly generated string that is returned to the JSP. The value is kept in the database because it contains the EBS application session parameters that will be passed to the Formspider application. When fs.jsp receives the key, it appends the key to the URL string and redirects the page to the Formspider application. The Formspider application retrieves the key from the URL and validates it against the table in which the key value pair is kept. If the key is not valid, Formspider application does not start (there are several ways to prevent starting the Formspider application. For instance, it can show an empty screen).

Integration of Formspider and EBS Architectures



3.2 Authentication and Authorization

If the key is valid Formspider application uses the value associated with it to authenticate itself to EBS.

After the Formspider application is opened and user is authenticated, it checks which parts of the application user can access with EBS public APIs, fnd_global.apps_initialize and fnd_function.test. The examples of these APIs are below.

4. Creating and Calling Custom JSP From EBS

4.1 Creating the JSP File

The source of fs.jsp:

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="oracle.apps.fnd.common.WebRequestUtil"%>
<%@ page import="oracle.apps.fnd.common.WebAppsContext"%>
<%@ page import="java.sql.CallableStatement"%>
<%@ page import="java.sql.Types"%>
<%@ page import="java.sql.SQLException"%>

<html>

<%

WebAppsContext ctx = WebRequestUtil.validateContext(request, response);

String userId = "" + ctx.getUserId();
String respId = "" + ctx.getRespId();

String sql = "begin ?:= fs_apps.ebs_main_pkg.createToken(?,?); end;";
String token = "";
CallableStatement stmt =null;

try
{
  	stmt = (ctx.getJDBCConnection()).prepareCall(sql);
  	stmt.registerOutParameter(1, Types.VARCHAR);
  	stmt.setString(2, userId);
  	stmt.setString(3, respId);
  	stmt.execute();
  	token = stmt.getString(1);
}
catch (SQLException e)
{
  	e.printStackTrace();
}

//Enter the URL of your Formspider Application
String redirectURL = "http://127.0.0.1/fs_ebs/main.jsp?name=EBS%20Test#" + token;
response.sendRedirect(redirectURL);

%>

</html>

fs.jsp file


Note that “ebs_main_pkg.createToken” function is owned by FS_APPS schema and it returns the key of the token.

Copy fs.jps file to EBS middle tier to the target path below (the path may be different depending on the EBS version):

/u01/E-BIZ/apps/apps_st/comn/webapps/oacore/html

The path of fs.jsp


Then, navigate to html directory and run the following command to compile the JSP file.

$FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'fs.jsp' -p 2

Compiling fs.jsp


If the command above doesn’t work use the following command:

perl -x /u01/E-BIZ/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/ojspCompile.pl --compile -s fs.jsp

Compiling fs.jsp


After compilation, restart the apache server and core using the following commands:

adstpall.sh apps/apps
adstrtal.sh apps/apps

Restarting the apache server and core


The JSP file is deployed to the EBS middle tier. Next, define necessary objects to call this JSP from the EBS main menu.

4.2 Calling the JSP

Login to EBS with SYSADMIN user.

Login to EBS with SYSADMIN


At the main menu, click the item “Function” under the path “System Administrator – Application”.

Open the Function Menu


4.2.1 Creating The Function

Create a new function. Enter the following values for the fields:

  • “FS_FUNCTION” for Function and User Function Name
  • “SSWA jsp function” for Type
  • “fs.jsp” for HTML Call

Defining the EBS Function - 1


Defining the EBS Function - 2


Defining the EBS Function - 3


4.2.2 Creating The Menu

Next, create the menu item that calls the function created in the step above by selecting “System Administrator – Application – Menus” from the EBS menu. Enter the following values to the fields:

  • “FS_MENU” for Menu and User Menu Name fields
  • “Standard” for Menu Type
  • “Start FS App” for Prompt
  • “FS_FUNCTION” for Function

Creating the EBS Menu


4.2.3 Creating The Responsibility

Create a new responsibility by selecting “System Administrator – Application – Responsibilities” from the EBS menu. Enter the following values to the fields:

  • “FS_RESPONSIBILITY” for Responsibility Name and Responsibility Key fields
  • “Application Object Library” for Application field
  • “Oracle Self Service Web Applications” for Available From
  • “FS_MENU” for Menu field
  • “Standard” for Data Group Name
  • “Application Object Library” Data Group Application

Creating the EBS Responsibility


4.2.4 Creating The User

Create a new user from by selecting “System Administrator – Application – Users” from the EBS menu. Enter the following values:

  • “FORMSPIDER” for User Name
  • Enter a password
  • Create a new record for Direct Responsibilities block and enter values “FS_RESPONSIBILITY”, “Application Object Library”, “Standard” for fields Responsibility, Application, Security Group respectively.

Creating EBS User "FORMSPIDER"



5. Developing a Formspider Application Using EBS Data

5.1 Creating FS_APPS Schema

Connect to the EBS database with “SYS” user and run the following scripts.

CREATE USER FS_APPS
IDENTIFIED BY FS_APPS
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
/
GRANT CREATE PROCEDURE TO FS_APPS
/
GRANT CREATE SEQUENCE TO FS_APPS
/
GRANT CREATE SESSION TO FS_APPS
/
GRANT CREATE SYNONYM TO FS_APPS
/
GRANT CREATE TABLE TO FS_APPS
/
GRANT CREATE TRIGGER TO FS_APPS
/
GRANT CREATE TYPE TO FS_APPS
/
GRANT CREATE VIEW TO FS_APPS
/
GRANT UNLIMITED TABLESPACE TO fs_apps
/

User FS_APPS


Then, connect to APPS schema and create the package “formspider_global”. This package wraps APPS APIs which will be used by the Formspider application. Please note the “authid definer” keyword added to the package specification in order to run it with the APPS schema privileges.

CREATE OR REPLACE
PACKAGE formspider_global AUTHID DEFINER AS

procedure apps_initialize(user_id number,
                          resp_id number,
                          resp_appl_id number,
                          security_group_id number := 0,
                          server_id number := -1);

function function_test(function_name in varchar2) return boolean;

function createToken(in_user_id varchar2, in_resp_id varchar2) return varchar2;

procedure updateUser(in_userName_tx varchar2, in_email_tx varchar2);

end;

end;
/

CREATE OR REPLACE
PACKAGE BODY formspider_global AS
--------------------------------------------------------------------------------
procedure apps_initialize(user_id in number,
                          resp_id in number,
                          resp_appl_id in number,
                          security_group_id number := 0,
                          server_id in number := -1) is
begin
  fnd_global.apps_initialize(user_id, resp_id, resp_appl_id,
  security_group_id, server_id);
end;
--------------------------------------------------------------------------------
function function_test(function_name varchar2) return boolean is
begin
  return fnd_function.test(function_name);
end;
--------------------------------------------------------------------------------
function createToken(in_user_id varchar2, in_resp_id varchar2) return varchar2 is
begin
  return fs_apps.ebs_main_pkg.createToken(in_user_id,in_resp_id);
end;
--------------------------------------------------------------------------------
procedure updateUser(in_userName_tx varchar2, in_email_tx varchar2) is
  pragma autonomous_transaction;
begin
  fnd_user_pkg.updateuser(
    x_user_name => in_userName_tx,
    x_owner => in_userName_tx,
    x_email_address => in_email_tx);
  commit;
end;
--------------------------------------------------------------------------------
END;
/

grant execute on formspider_global to FS_APPS
/

Package Formspider_Global


Next, connect to FS_APPS and run the following scripts to create “t_token” table and “ebs_main_pkg” package. T_token table is used to keep key value pairs. The package is used by Formspider application.

create table t_token(token_id varchar2(200) primary key, user_id number, resp_id number)
/

Table t_token


CREATE OR REPLACE
PACKAGE ebs_main_pkg IS

procedure postOpen;
procedure populate;
procedure save;
function createToken(in_user_id varchar2, in_resp_id varchar2) return varchar2;

END;
/

CREATE OR REPLACE
PACKAGE BODY ebs_main_pkg IS
--------------------------------------------------------------------------------
function appsAuthorise(in_user_id number, in_resp_id number) return boolean is
begin
  if in_resp_id is null then
    return false;
  end if;

  -- Init user and resp
  apps.formspider_global.apps_initialize(in_user_id, in_resp_id, 0);

  -- Check if user has permission on EBS Function
  return apps.formspider_global.function_test('FS_FUNCTION');

end;
--------------------------------------------------------------------------------
procedure postOpen is
  v_bookmark varchar2(4000);
  v_user_id number;
  v_resp_id number;

  --Get user_id and resp_id values from the t_token table and delete the row.
  --So that token will be used only once
  procedure useToken(in_token_id varchar2) is
    pragma autonomous_transaction;
  begin
    delete from t_token
    where token_id = in_token_id
    returning user_id, resp_id into v_user_id, v_resp_id;
    commit;
  end;

begin

  v_bookmark := api_application.getBookmark;
  useToken(v_bookmark);

  if appsAuthorise(v_user_id, v_resp_id) then
    api_session.add('user_id',v_user_id);
    api_session.add('resp_id',v_resp_id);
    api_frame.addPanel('mainFrame','centerCell','mainPanel');
  end if;

end;
--------------------------------------------------------------------------------
procedure populate is
begin
  api_datasource.setBindvar('EBS_USER1.user_id',api_session.getValueNR('user_id'));
  api_datasource.executeQuery('EBS_USER1');
end;
--------------------------------------------------------------------------------
procedure save is
begin
  api_application.doCommit;
end;
--------------------------------------------------------------------------------
function createToken(in_user_id varchar2, in_resp_id varchar2) return varchar2 is
  v_token_id varchar2(255);
  pragma autonomous_transaction;
begin

  --create a random string as token id
  v_token_id := utl_raw.cast_to_raw( DBMS_RANDOM.STRING('p', 10) ||
                  to_char(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF6'));

  insert into t_token(token_id,user_id,resp_id)
  values (v_token_id,in_user_id,in_resp_id);

  commit;

  return v_token_id;
end;
--------------------------------------------------------------------------------
END;
/

Package Ebs_main_pkg


5.2 Creating Formspider Application

Open Formspider IDE and click “New Application” under the “File” menu. The “New Application” dialog will show up. Enter the application name and datasource schema name and then press “OK”.

Creating new Formspider application, "EBS Test"


5.3 Creating the Model

Create the view that queries the EBS data in the FS_APPS schema. Connect to the APPS schema and grant the necessary object privileges to FS_APPS:

grant select, insert, update, delete on apps.fnd_user to fs_apps;

Grants for FS_APPS object


Run the following DDLs in FS_APPS:

create or replace view ebs_user (
  user_id, user_name, email_address,
  constraint ebs_user_pk primary key (user_id) rely disable novalidate
) as
select user_id, user_name, email_address
from apps.fnd_user
/

CREATE OR REPLACE TRIGGER ebs_user_iud
INSTEAD OF UPDATE ON ebs_user
REFERENCING NEW AS NEW OLD AS OLD
begin
  if updating then
    apps.formspider_global.updateUser(:new.user_name,:new.email_address);
  end if;
end;
/

View EBS_User


After creating the view, create the Formspider objects; datasource definition and datasource which will be used to query the “EBS_USER” view. Go to the Formspider IDE again and expand the “Datasource Definitions” accordion, right click the “Datasource Definitions” node and select “New” from the pop up menu.

Creating a new datasource definition


The “New Datasource Definition” dialog shows up. Select “View” radio button from “Based On” section. Choose USER_ID column as the Primary Key and enable “Delete, Insert, Update” from “DML Permissions” section.

Creating "EBS_USER" datasource definition


To filter retrieving only the data of one user, go to the “Query” section, enter the following where clause:

user_id = :user_id

Where clause of EBS_USER datasource definition


Defining where clause


Define the bind variable “user_id”:

Defining bind variable


5.4 Creating the User Interface

Create a simple form with user id, user name and email fields.

Expand the “Containers” accordion, select the “Panels” node in the navigation tree and click the “+” button to create a new panel. Alternatively, you may right click the “Panels” node and select the“New” menu item from the pop up menu. The “New Panel” dialog shows up. Enter “userPanel” as the name of the panel and click “OK”. Edit the “userPanel” XML so that it looks like the following:

<panel preferredHeightPolicy="Dynamic">
  <tableLayout cellSpacing="5">
    <row heightPolicy="Dynamic">
      <cell width="100" hAlign="Full" vAlign="Full">
        <textLabel label="USER_ID"/>
      </cell>
      <cell hAlign="Full" vAlign="Full">
        <textField editable="N" column="USER_ID" dataSource="EBS_USER1"/>
      </cell>
    </row>
    <row heightPolicy="Dynamic">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="USER_NAME"/>
      </cell>
      <cell hAlign="Full" vAlign="Full">
        <textField editable="N" column="USER_NAME" dataSource="EBS_USER1"/>
      </cell>
    </row>
    <row heightPolicy="Dynamic">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="EMAIL_ADDRESS"/>
      </cell>
      <cell hAlign="Full" vAlign="Full">
        <textField column="EMAIL_ADDRESS" dataSource="EBS_USER1"/>
      </cell>
    </row>
  </tableLayout>
</panel>

Panel userPanel


Add two buttons to the application named populate and save. The Populate button will fill in the user data fields in the application with the current user’s information. Save button commits the changes of the form to the EBS system. Please see ebs_main_pkg.populate and ebs_main_pkg.save procedures above.
Define action objects to use these procedures in the Formspider application. Go to the Formspider IDE and expand the “Actions” accordion, select the “Actions” node, click the “+” button to create a new action. Alternatively, you may right click the “Actions” node and select “New” from the pop up menu. The “New Action” dialog shows up. Enter “populate” as the action name and “ebs_mainl_pkg.populate” as the procedure. Click “OK” to save your action. Similarly, create a new action for “ebs_main_pgk.save” procedure. Open the “New Action” dialog, enter “save” as the action name and “ebs_mainl_pkg.save” as the procedure.

New action "populate"


New action "save"


You are now ready to create the buttons. Create a new panel, enter “buttonPanel” as the name of the panel and save the new panel pressing “OK” button. Then go to “buttonPanel” XML. Edit the panel XML so that it looks like the following:

<panel>
  <tableLayout cellSpacing="10">
    <row height="20">
      <cell>
        <button label="query">
          <events>
            <buttonPress action="populate"/>
          </events>
        </button>
      </cell>
      <cell>
        <button label="save">
          <events>
            <buttonPress action="save"/>
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Panel buttonPanel


Go to the main panel that is automatically created with the application and include the two panels into the mainPanel:

<panel>
  <borderLayout>
    <cell docking="North" height="40" >
      <include panelName="buttonPanel"/>
    </cell>
    <cell docking="Center">
      <include panelName="userPanel"/>
    </cell>
  </borderLayout>
</panel>

Panel mainPanel


When the application runs, the populate button does not retrieve any data because in the populate procedure the user data is received from the Formspider session. However, the session is not yet populated with this information yet. Set it by reading the key that is sent from the EBS through the URL. If there is not a key in the URL, show an empty screen. To achieve this, create a new panel and name it “emptyPanel”. Edit the “emptyPanel” so that its XML definition is as the following:

<panel>
  <tableLayout cellSpacing="30">
    <row>
      <cell hAlign="Full" vAlign="Center">
        <textLabel font-color="Red" font-style="Bold" name="errorLabel" label="Invalid token!" text-align="Center"/>
      </cell>
    </row>
  </tableLayout>
</panel>

Panel emptyPanel


Add the emptyPanel to the main frame. Replace emptyPanel with mainPanel if the key is validated. To find the main frame, expand the “Containers” accordion, expand “Windows” and “Mainframe” nodes. Edit the mainframe to look like the following:

<mainframe title="EBS Test" width="500" height="500">
  <borderLayout>
    <cell docking="Center" name="centerCell">
      <include panelName="emptyPanel"/>
    </cell>
  </borderLayout>
</mainframe>

The mainframe


5.5 Using the Key-Value Pair

As mentioned before, the key is present in the URL string. Use the api_application.getBookmark API to retrieve the key in the Post-Open event of the Formspider application. Query the t_token table to find the key-value pair in the table. Get corresponding user id and responsibility id values. Please see ebs_main_pkg.postOpen procedure for more details.

To define the Post Open action, go to Formspider IDE and create a new action. Enter “postOpen” as the action name and “ebs_mainl_pkg.postOpen” as the procedure.

New action "postOpen"


Open “Appliaction Edit Dialog” from the File menu or using edit button right to the Application combo box. In the dialog, open the “Events” tab and select postOpen action for the Post Open event.

Defining "Post Open" event



6. Running the Application

Login to EBS with FORMSPIDER user.

Login to EBS with FORMSPIDER user


Expand the menu “FS_RESPONSIBILITY” and click “Start FS App”. Please note that, when you try to expand the menu FS_RESPONSIBIILTY, EBS may open the Formspider application since there is just one menu item defined under FS_RESPONSIBIILTY.

Click "Start FS App"


The Formspider application starts. Press the query button in order to populate the data of the user FORMSPIDER. The application retrieves FORMSPIDER user id from the access token in the database. You can edit email field and save the changes.

Formspider application started


Try to open the application outside of the context of the EBS (from the browser or IDE without creating a key-value pair). Note that the application only displays an empty panel.

Refresh the application opened from the EBS menu, to see that you get the same result. This is because the key-value pair was designed to be single use.

Formspider application started but not authenticated



7. Conclusion

Formspider enables PL/SQL developers build great enterprise WEB applications with just PL/SQL. Formspider applications run using 100% AJAX, DHTML and JavaScript without the need to know any of them. You can easily integrate Formspider applications with EBS to extend the capabilities of your information system.