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.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.
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.
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).
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:
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):
Then, navigate to html directory and run the following command to compile the JSP file.
If the command above doesn’t work use the following command:
After compilation, restart the apache server and core using the following commands:
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.
At the main menu, click the item “Function” under the path “System Administrator – Application”.
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
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
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
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.
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.
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.
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.
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”.
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:
Run the following DDLs in FS_APPS:
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.
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.
To filter retrieving only the data of one user, go to the “Query” section, enter the following where clause:
Define the bind variable “user_id”:
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:
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.
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:
Go to the main panel that is automatically created with the application and include the two panels into the 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:
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:
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.
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.
6. Running the Application
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.
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.
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.