Create a Report that Executes a Query

Purpose
The Purpose of this of this configuration, is to create a report that shows which active responsibilities are assigned to which active users. The same procedure can be followed to create a report that runs whatever query.

Description
The implementation of this report will be divided in 5 steps:
1. Create the Executable on the EBS
2. Create the Concurrent Program
3. Create the SqlPlus file
4. Upload the SqlPlus file on the Application Server
5. Enable the Concurrent Program to one existing Responsibility

Create an Executable on the EBS
Lets start opening the Executable form:
System Administrator -> Concurrent -> Program -> Executable
In this form we can create a new executable called ‘XX_MY_REPORT’, setting ‘Application Object Library’ as Application, ‘XX_MY_REPORT’ as Short Name, and ‘Sql*Plus’ as Execution Method. The other parameters can be set as described in the image below.

Create the Concurrent Program
Once the Executable is ready, we can create a new Concurrent Program:
System Administrator -> Concurrent -> Program -> Define
The program will be named ‘XX: This is My Report’, the Executable name will be ‘XX_MY_REPORT’
The other parameters can be configured as was done in the image below.

Create the SqlPlus file
We are now ready to create the file that will containing the query to be executed. In this example the query will extract the email addresses of all the users.
The file will be named XX_MY_REPORT.sql
The name of the file and of the executable created on the EBS have to be the same.

SET HEADING ON
SET FEEDBACK OFF
SET VERIFY OFF

column user_id format 99999 HEADING 'USER ID'
column user_name format A20 HEADING 'USER NAME' word_wrapped
column email_address format A30 HEADING 'EMAIL ADDRESS'

SELECT user_id, user_name, email_address
FROM fnd_user fu
WHERE user_name LIKE '%'
ORDER BY fu.user_name

/

SET FEEDBACK ON
SET VERIFY ON
EXIT


Uploading the SqlPlus file on the Application Server
After connecting the Application Server, we can upload the file XX_MY_REPORT.sql in the directory $FND_TOP/sql. If the executable would have been assigned to another application, the directory would have been $APPLICATION_NAME_TOP/sql.

Enable the Concurrent Program to one existing Responsibility
After having assigned the report to an existing responsibility, the report is ready to be ran.