This blog post covers (with a fairly simple example) a way of having a menu item in Oracle "punch out" to a SQL Server Reporting Services Report (or any other URL).
The reasoning behind doing this is that if you have a process driven by Oracle and need a report users don't want to then have to bring up Internet Explorer, browse to the Report website, and finally select their report. Using this process makes the join practically seemless to the end user.
This process requires three parts. The first is setting up and deploying a SQL Server 2008 report (I've included a small, and fairly pointless, example which just displays Invoices/Day from the AP_INVOICES_ALL table in e-Business Suite). Of course if you already have your own report just substitute your existing report for the example and proceed to the "Configuring e-Business Suite" stage.
The second stage is adding a simple HTML redirect script to the Apache webserver that comes with Oracle.
And the final step is to configure Oracle so that wen you click on a menu item it takes you to a SQL Server Reporting Services Report (SSRS).
Stage 1: Setting Up And Deploying a SQL Server 2008 Report
In order to speed things along I've created a report and a Shared Data Source (that you will need to re-point to your database). In order to use these files you need to open Visual Studio (I'm using 2008 but I don't see any reason why other versions wouldn't work), go to File > New > Project and select "Report Server Project".
At the right-hand side of the screen (in the treeview) you will see Shared Data Sources. Right-click this and select "Add > Existing Item" and then download the EBUSINESSSUITE.rds file (from Google) into your project directory and select it.
Double click the EBUSINESSUITE data source and change the connection string so that it points to your server. Click on "Credentials" and enter a username/password that has access to the AP_INVOICES_ALL table (the APPS account will, as will the AP account - if you are using something else then make sure that if you login using SQL * Plus and enter the SQL "select * from ap_invoices_all where rownum < 10" and make sure this works).
Next right-click the "Reports" node in the same tree view and select "Add > Existing Item" and then download the Invoice Totals By Day.rdl file (also from Google) into your project directory and select it.
Your Project should now look something like this;
|Figure 1: Visual Studio 2008 Project Setup|
|Figure 2: Invoice Totals By Day (Sample Output)|
Next deploy your solution by choosing "Build > Deploy Solution".
Finally when you visit the SQL Server and go into your deployment folder you will see something like;
|Figure 3: New Report Visible on SQL Server|
Deploying a SQL Server 2008 Example report is now complete.
Stage 2: Adding a Simple HTML Re-direct Script to Oracle
By far the most complicated part of this process is actually finding the correct directory into which to deploy the script. I'm not a UNIX person, I do my work in Windows and Oracle. I have a nice friendly UI and when that doesn't work I have the familiarity of PL/SQL ... Under UNIX I'm afraid I get a little lost.
The directory you're looking for contains lots of files. I created a simple text file called "test.txt" which contained the word "test" and then went to the URL;
http://XXXX:9999/OA_HTML/test.txt (XXX is the name of your server, and 9999 is the port number)
And confirmed that the word "test" appeared in my web browser.
I then created a small text file called verysimpleredirect.html containing this code (via Google Drive);
http://XXXX:9999/OA_HTML/verysimpleredirect.html?redirect=www.google.co.uk (XXX is the name of your server, and 9999 is the port number)
You should end up at Google.
Once you have copied this file to the server (and tested it) this stage is complete.
Stage 3: Configuring Oracle to Punch-Out to the Report
Log into Oracle and select the "Application Developer" responsibility.
Scroll down the list and under "Application" select "Function" (everything will slow down while Java starts up and the screens load). Eventually the "Form Functions" dialog will appear;
|Figure 4: Oracle "Form Functions" Dialog|
Complete the form as follows;
**- In this field you need to enter the URL of your report. This will look something like;
(where XXX is the name of your SQL Server)
I have listed all the fields for completeness but for the huge bulk you can just accept the default.
Now this is where things become a little fiddlie. For whatever reason the Type > SSWA jsp function doesn't appear to have saved correctly into the database (if anyone knows why, or what I'm doing wrong!) please let me know via the comments bit below!
Anyway running the following SQL will "fix" the problem;
update applsys.fnd_form_functions t
set type = 'JSP'
where function_name in ('SSRS_INVTOTBYDAY')
For some reason the type stored in the table is "UNKNOWN" . Until you have specified the type correctly you'll not be able to see your function anywhere.
Now switch responsibility to "System Administrator".
Under "Application" select "Menu" and the "Menus" form will be displayed. For the purposes of this example we'll be adding the report as a menu option to the "Application Developer" user (simply because this is likely to interfear less with other users using the system).
|Figure 5: Oracle Menus Dialog|
For the purposes of this new menu I'll just add new item at the bottom;
The values are;
You should end up with something like this;
|Figure 6: Completed Menus Dialog (for Application Developer GUI)|
Now if you switch back to the "Application Developer" responsibility you will see a new option on the menu called "Reports";
|Figure 7: New "Reports" Item for "Application Developer" Responsibility|
|Figure 8: Clicking on an Oracle Menu Items Triggers a SSRS Report|