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 setting 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 to have your Linux admins do this for you. If your DB and application server are running on the same machine, you don't need the mount, but still need to set up the profile value.
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 concurrent output 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 person_id 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, go the the request log, copy the query 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 Excel 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 had to slightly modify the procedure query2sheet so I renamed the package to AS_XSLXEBS 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.
This is free to use and modify as you please. For details see the disclaimers in the code.
Enjoy!
No comments:
Post a Comment