Unzip all files in your Oracle Home, either on your local machine or the server and run eo_install.sql from SQL Plus.
If you prefer using SQLDeveloper, TOAD or similar tool you can directly paste and execute this script: EO_SQL_DEV.sql
Either of these scripts will install the above 'Excel Out' program that you can run immediately from System Administrator.
Before you run it the first time you must mount the concurrent request output directory on the Database Server and point the program to it by betting up the profile option "Concurrent Requests Output DIR".
Most, but not all, of companies already have the directory with concurrent request outputs mounted on the database server. If your company does not then you will need your Linux admins to do this for you. If your DB and application server are running on the same machine, you can skip this step.
If you are not sure if it is mounted, follow steps below and it will become apparent.
The physical location of the directory can be identified by running this simple statement:
SELECT OUTFILE_NAME FROM FND_CONCURRENT_REQUESTS where LAST_UPDATE_DATE>sysdate-1
Once you identified the output directory check if it is set up as a database directory. In my case this was:
select directory_name from all_directories
where directory_path='/dev_oraapp/dev122/fs_ne/inst/DEV122_apdev001/logs/appl/conc/out'
If not you will need to define it as a database directory using the CREATE DIRECTORY syntax.
If the directory is already defined then most likely it is already mounted on the database server
Once the directory is defined you can set up the "Concurrent Requests Output DIR" profile option:
The PROD and test instances will most likely each need a different value for this profile.
Now you can run the Excel Out report.
If the output does not open, but instead you get a message that the output file could not be read that means the directory is not mounted on the database server.
Building a new report
To create a new report copy the Excel Out concurrent request with all of its parameters then click on the attachment icon and add a new SQL attachment with a new query:
DO NOT put a semicolon at the end of the query, this will fail the program.
If your report needs parameters that are specified during runtime put them in the query using the p_parameterXX syntax.
During the runtime each p_parameter value will be replaced with the actual value selected by the user in the parameter.
For dates use syntax:
fnd_date.canonical_to_date(p_parameter01)
if you want to make a parameter optional use the nvl syntax
person_id=nvl(p_parameter03, person_id)
If the order_number is specified in the parameter the query will only return records for that particular person_id, but if the user does not provide a value this will evaluate to :
person_id=person_id
meaning the parameter will have no impact on the query, making it optional.
Troubleshooting errors
If your report failed to execute, copy the query from the concurrent request log and try to execute directly in SQL. This should give you an idea of that the problem is.
Never use a semicolon in the attachment as this will guarantee the report failure.
How it works
The concurrent request grabs the query from the attachment and writes its output into an Excel file. If you want to have a specific naming convention for the output file, specify the prefix in parameter 90.
The file is written using the AS_XSLX PL/SQL package written by Anton Scheffer. The AS_XSLX is an excellent piece of software that you can utilize for a number of other Excel reporting requirements.
I have to slightly modify the procedure query2sheet so I renamed the package to AS_XSLSEBS to prevent an overwrite in case you already have your own copy of AS_XSLX.
My modifications are:
- Remove the underscore for all column names and make them Sentence Case, bold and underlined
- Adjust the column width to the GREATEST(length(header label), max(data length)
This formatting allows the reports to look more user friendly.
