The simplest way of turning SQL queries to Excel reports in Oracle EBS

Over the years of working with Oracle EBS I was highly frustrated with its sluggish reporting. Oracle is a great transactional system but it is often underutilized when it comes to aiding decision making. Throughout most of my career if one needed a report, Oracle Report Builder was required. This tool provides good flexibility but is extremely cumbersome to work with, and most of the time needed to write a report is used for servicing the need of the Oracle Report Builder rather than focusing on the data itself. Anybody who encountered the ‘rep-1212 object is not fully enclosed by its enclosing object’ error can fully appreciate that. To create a report was always a big project that usually took several days of development and then the output was provided in a text format which was not easy to read or manipulate.

With XML publisher things become a little easier at least in the way of the output. One could finally create a report that provides a nice graphical format or that opens directly in Excel. Unfortunately the XML still has to be generated by Oracle Reports or some other tool, which usually requires creating and placing the RDF report file on the server, developing a template in RTF with a special MS add-on, then uploading it trough the front end and associating it with a report. Lots of servicing the needs of development tools and way too complex for me, and very annoying if I don't have the UNIX access. When the sales manager asks me for a report that shows sales figures broken down by sales territories and time periods, I need to give him that report in 2 hours rather than tell him that:

- it is a week long development project

- we can only submit a budget for its approval next week

- if it gets approved in the next 2 weeks we can start development and testing

- after a month or so he can have his already outdated report and he can ask for a change request to add some additional columns he didn't think about in the first place ....

If you are on a hosted platform it may be significantly more difficult than that.

How can a company be successful if they can't instantly react to the changing business environment?

Simply, I needed an easy and quick way to create required reports in Excel, right out of Oracle. There are tools out there that enable that, such as Discoverer or OBIEE, but then they require a few months long development project to put them into place and create underlying data objects, servicing them when they are installed, and most difficult of all – they require user adoption. I have seen companies, where despite of having those tools, users moved away from them towards the solution I describe below because it was so much more convenient. They don’t want to learn another application separate from EBS where they have to to go to each time they want their report. Ideally, it should be just another concurrent request that provides nicely formatted Excel report. And not a CSV file.

I looked for a tool like that for several years and found none, so decided to create one myself.

It is called Excel-Out, takes SQL query as an IN parameter to provide a nicely formatted output that opens directly in Excel. Creating a new report is as simple as copying the concurrent request and pasting a new query into it.

Here is a demonstration of my tool hosted on the more4apps web site

http://www.more4apps.com/Demonstrations/Video+Demonstrations/Excel-Out.html

Click on the below icon when viewing the demo, to go to full screen format. It is much easier to watch it that way.

The tool is really only a concurrent request based on a PL/SQL package and you should be able to install it in 5 minutes. The package parses the query and converts it into native Excel format, so all the report outputs open directly in Excel.

Now, new report development and deployment can take only as much time as it takes to develop the query, and other than having TOAD or other SQL tool, no development environment is required. No user learning curve either.

To install it you need to run 2 scripts.

The first one is different depending on whether you are on 11i or R12. It can be found here:

http://www.more4apps.com/installation-scripts/

Put all the installation files (all that end with sql, plbs and plbb) into the bin directory of your oracle home and then run the R12_install.sql (or install.sql for the 11i or earlier versions) script from your SQL client.

The second script is not release specific and it can be downloaded here:

http://www.more4apps.com/downloads?ProdID=5273

To get to the download page you need to register, which is to satisfy my curiosity on who is downloading it. Every time you register I get an e-mail. This is also the only way for me to email you back.

Like with the first installation script, put all the installation files into your bin directory of your Oracle home and run the eo_install.sql

This script will install the concurrent request Excel-Out.

You can find the concurrent request by going to

System Administrator -> Concurrent -> Program -> Define

Now you can add it to the System Administrator Reports request group and it is ready to run as a report.

When the report completes click on the Output and it will open directly in Excel

Now, depending on your operating system and the version of browser and Excel, the output window may not open at all, due to your security settings.

To fix this follow the 3 steps below:

1. Turn off all the pop-up blockers or hold CTRL key when opening the output to bypass them. If you are not sure on how to do that, check the online user manual.

2. Add the application URL to your trusted sites. Check the user manual for details.

3. Go to My Computer -> Folder options

Go go File Types tab, select file type XLS and click on Advanced

Different options are checked there depending on your Excel version.

Uncheck all checkboxes. This will cause Excel to open in a separate window,rather than embedded in the browser.

If you are on Excel 2007 you may get an annoying pop-up message that your file format does not match the extension. This message has no significance other than being annoying but if you want to suppress it check metalink notes 780817.1 and 759410.1 or the online user manual.

Now your report should be running smoothly. Keep the Excel-Out as your template but copy it and update the query sitting in parameter 991 to get a brand new report. When you copy this report into a new one do not change the application. It needs to stay as Application Object Library, even if you assign this report to request groups in different modules.

Watch the 5 minutes demonstration video or check the screenshots in the user manual on how to create query parameters, especially the section on dates. When creating dates as parameters you need to format them with fnd_date.date_to_cannonical. When you output dates they will work in your default format, but for user convenience it is better to format them with with to_char(date,'dd-mon-yyyy') or any other format the users are used to. Check out the dates in the seeded Excel-Out report.

Don't hesitate to ask me a question if you get stuck at any point.

Most of the issues are due to an invalid query. Check the concurrent request log, it displays your query after all the parameters were substituted for actual values Run it directly from SQL to see if it works.

This is a 45 days trial version. After that time it will still work but limit the output to 70 rows.

Create and SFTP files with a concurrent request

The standard FTP package include the EBS installation allows you to send files to remote servers. FTP is not a secure protocol and it is increasingly replaced with SFTP. There is no handy out-of-the-box package to handle SFTP from PL/SQL.

I have created one that creates a file based on a query passed as an IN parameter, and then SFTPs it to a remote server. The file created form the query contains both the column headers and rows, delimited by a separator selected as a parameter of the concurrent request. Since the query, separator, and destination directory are variables, this can be used as an universal package for interfacing via SFTP.

This works if you are authenticated by an authentication key for your server and user so you son't have to log to the remote server using username and password.

Here is an example on the authentication key setup:

http://myracle.wordpress.com/2009/06/01/automating-sftp-between-two-servers/

Make sure that the authentication key includes all the UNIX users that you use on both the test and prod servers.

Before you go into the below code there some things you need to check:

• this solution will work on database 10.2.0.4 or higher. It will not work on DB 9. You may be able to make it work on lower versions of 10G, but that will require heavy DB patching. The most common issue in lower 10G versions is the UNIX job getting hanged.
• you must grant CREATE JOB and CREATE EXTERNAL JOB to the APPS user. I have seen the grant properly executed and the job would work fine for SYS user but still hang up on APPS user. The only fix it to upgrade to latest 10.2.0.4 with all proper patches.

Here is the code, optimized to have it run as a PL/SQL concurrent request from Oracle EBS.

procedure SFTP_FILE(

ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER,

p_separator in varchar2 ,

p_dir in varchar2 ,

p_desdir in varchar2,

p_filename in varchar2,

p_query in varchar2, ,

parameter1 in varchar2 default null,

parameter2 in varchar2 default null,

parameter3 in varchar2 default null

)

is

l_output utl_file.file_type;

l_theCursor integer default dbms_sql.open_cursor;

l_desc_tab dbms_sql.desc_tab;

l_columnValue varchar2(2000);

l_status integer;

l_colCnt number default 0;

l_separator varchar2(10) default '';

l_cnt number default 0;

l_query varchar2(2000);

l_program varchar2(50);

l_descTbl dbms_sql.desc_tab;

l_database varchar2(20);

l_conn UTL_TCP.connection;

l_filename varchar2(200);

v_to_file varchar2(2000);

status number;

begin

select name

into l_database

from v$database;

-- this part is to prevent accidental execution of the file uploaded from a test server

if l_database='PROD' then

-- the file can be comma, pipe or any other speparator delimited. This depends on the delimiter user selects

-- in the concurrent request parameter when submitting the job

l_separator :=p_separator;

-- this is the file name, it is taken from the IN parameters. Its first 9 characters are always replaced with

-- current date so the concurrent request can be scheduled to run daily

l_filename:=to_char(sysdate,'yyyymmdd')||substr(p_filename,9,length(p_filename));

-- this is the actual query that will be used to build the file that needs to be SFTP-ed

-- you could just write it here but I pass it as a parameter, and have a custom form where

-- i can adjust it in the application front end

l_query:=p_query;

-- if you want to manipulate the query for each run and add addtional AND conditions

-- there can be passed for the concurrent request parameters 1-3

-- those parameters can have default values

-- this is more convenient than changing the source query each time.

l_query:=l_query||' '||parameter1||' '||parameter2||' '||parameter3;

-- write to the concurrent request log to see the parameters and the passed query

Fnd_File.Put_Line(Fnd_File.LOG,'l_query = '||l_query);

Fnd_File.Put_Line(Fnd_File.LOG,'p_separator = '||p_separator);

Fnd_File.Put_Line(Fnd_File.LOG,'p_dir = '||p_dir);

Fnd_File.Put_Line(Fnd_File.LOG,'l_filename = '||l_filename);

-- I have taken core of this part for asktom.oracle com

-- it writes the headers and lines of the query into a delimited file

-- p_dir is directory where the file will be written

-- the directory is passed as the request parameter

-- make sure the request has the write privilege to that directory

-- if you do not have any specific directory in mind you can use one of the directories returned by

--select value from v$parameter where name='utl_file_dir'

l_output := utl_file.fopen( p_dir, l_filename, 'w' );

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

-- the separator is passed as variable l_separator variable to ensure flexibility of the solution

for i in 1 .. l_colCnt loop

utl_file.put( l_output, l_descTbl(i).col_name||l_separator );

dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );

end loop;

utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

for i in 1 .. l_colCnt loop

dbms_sql.column_value( l_theCursor, i, l_columnValue );

utl_file.put( l_output, l_columnValue||l_separator );

end loop;

utl_file.new_line( l_output );

end loop;

dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );

-- write to the concurrent request output

fnd_file.put_line( FND_FILE.OUTPUT,'Your file '||l_filename ||' has been successfully created in staging directory at '||p_dir);

-- most likely this part will work only if you have database 10.2.0.4 or higher

-- it will not work on 9 or any database lower than 10G

--dbms_scheduler will start an OS (UNIX) job directly from the PL/SQL session

-- depending on yout database configuration the session can use different names to log in into UNIX

-- most likely it will be a user called oracle

--if the database has out-of-the-box configuration the user used to log in to UNIX is called 'nobody'

-- the nobody user does not have sufficient permissions to execute shell scripts

-- each instance (TEST, DEV, PROD, is most likely to be set up with a different UNIX user)

-- take that into consideration when testing

-- create sftp.sh file with the following syntax

--#!/bin/bash

--scp $1 $2

-- $1 and $2 are parameters that will be passed by DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE

dbms_scheduler.create_job(

job_name=>'SFTP_UPLOAD',

job_type=>'executable',

number_of_arguments => 2,

job_action=>'/u01/oracle/PROD/xx/sftp.sh',--this is a sample directory where the sftp shell script resides

enabled=>FALSE

);

-- make sure that the file sftp.sh has proper permissions so

-- the oracle (or any other user that the database uses to log in into UNIX) can execute it

-- test it by opening an UNIX session and executing the file from the command prompt

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

job_name => 'SFTP_UPLOAD'

,argument_position => 1

,argument_value => p_dir||'/'||l_filename

);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

job_name => 'SFTP_UPLOAD'

,argument_position => 2

,argument_value => p_desdir

);

dbms_scheduler.enable('SFTP_UPLOAD');

-- write to the output of the concurrent request

fnd_file.put_line( FND_FILE.OUTPUT,'scp '|| p_dir||'/'||l_filename ||' '||p_desdir);

fnd_file.put_line( FND_FILE.OUTPUT,'Connection to remote server has been successfully established');

-- check if the job has successfuly completed

select status ,nvl(substr(additional_info,1,32000), 'SUCCESS')

into r_status, r_additional_info

from USER_SCHEDULER_JOB_RUN_DETAILS where job_name='SFTP_UPLOAD'

and log_date = (select max(log_date) from USER_SCHEDULER_JOB_RUN_DETAILS where job_name='SFTP_UPLOAD');

if r_status <> 'SUCCEEDED' then

-- complete the concurrent request with warning if the job did not complete with success

l_completion:=fnd_concurrent.set_completion_status('WARNING','The SFTP process may have failed. Check the destination server to see if the file has been uploaded. '||r_additional_info);

else

fnd_file.put_line( FND_FILE.OUTPUT,'File upload success' );

End if;

-- this is the end of the IF from the beginning of the package. It checks for the test database

else

Fnd_File.Put_Line(Fnd_File.OUTPUT,'This is a test database, no action was taken');

end if;

EXCEPTION

when others then

Fnd_File.Put_Line(Fnd_File.OUTPUT,SQLERRM);

end ;

END;

How to automatically send an EBS XML Publisher document by email

One of my clients was preparing quotes in EBS. The quote resulted in a PDF document prepared by an XML publisher based concurrent request. They were printing the quote and faxing it to the customer. It was pretty cumbersome process because they had to print it, then pick it up from the printer and only then fax it. When the fax was presented to the decision making executive it did not have the professional appeal of the nicely prepared colorful quote. But after all Oracle EBS is one of the leading enterprise systems so it should offer a way of emailing the documents automatically to the clients, shouldn’t it?

Well, not really…

I started browsing trough metalink and user forums on how to have an output of a concurrent request emailed from either a workflow or a nice button right on the quote and I found nothing. It seems like there were no good ways to do that so I had to build myself one.

Here is what needed to be done:

1. Modify the quote workflow and to add the 'concurrent request execution' and 'wait for the request completion' into the flow. This is added after a special event such as the quote approval.
2. Identify the directory where the concurrent request file, usually with the PDF or Excel output, is dumped and the name of the specific file itself
3. Write a PL/SQL package that puts the file into a BLOB and attach that package to a workflow attribute of a document type.
4. Use API from PO emailing to create ad hoc role. Workflow will not mail anything just to an e-mail address, it has to be mailed to a performer.
5. Create a notification and a message. Create a message attribute and point it to the global attribute with the PL/SQL BLOB containing the PDF file into the body of the message. This will create an attachment to your e-mail containing the PDF file. Assign the notification to a performer role created in the previous step.

This gets a bit more complex when your application and database run on 2 different servers. Your PL/SQL logic will not be able to get a file that resides on a different server. You can resolve it either by creating a link on the DB server to point to a directory on the application server, or FTP the file from the application server using the standard FTP package.

1. Modify the quote workflow and to add the concurrent request execution. Add the ‘Execute Concurrent Program’ (EXECUTECONCPROG) function to your workflow. This function can be found under the seeded ‘Concurrent Manager Functions’ (FNDCMSTD) item type. Go to the Node Attributes and specify:

i. Application Short Name -Short name of the application to which the concurrent program is registered.

ii. Program Short Name -Short name of the concurrent program to run. You will find this in the program definition screen.

iii. Number of Arguments -Number of arguments required for the concurrent program.

iv. Item Attribute Name- Name of the item attribute to store the concurrent program request ID. This is very important as it will be used to determine the name and location of the output file. In this example I use the REQUEST_ID as the attribute name.

v. Argument1, Argument2,...Argument100--Value of each concurrent program argument, ordered to match the correct syntax of the concurrent program. Up to 100 arguments are allowed, but you should only specify as many argument values as you define in the Number of Arguments activity attribute.

2. Identify the directory. The directory where the output is located and the file name can be queried from FND_CONC_REQ_OUTPUTS table, using the concurrent request id

3. Write a PL/SQL package that puts the file into a BLOB. This is where the hard part starts. First we need to create a workflow attribute of a document type:

In theory we can create an attribute of a ‘Document’ type and populate it by a PL/SQL code referenced in the Value field of the attribute definition

The syntax would include the itemtype and itemkey as variables

PLSQLBLOB:XX_ATTACHMENT.GET_DOCUMENT/'||itemtype||'|'||itemkey

It worked for me only if I passed the itemkey and itemtype as static values but never as variables. To work around that I left the value blank and populated it with a workflow function executed just after the concurrent request output. Here is the sample code:

PROCEDURE SET_ATTACHMENT_ID

IS

BEGIN

(itemtype in varchar2,

itemkey in varchar2,

actid in number,

funcmode in varchar2,

resultout in out nocopy varchar2)

wf_engine.setitemattrdocument

(itemtype=>itemtype

, itemkey=>itemkey

, aname=>'ATTACHMENT'

, documentid => 'PLSQLBLOB:XX_ATTACHMENT.GET_DOCUMENT/'||itemtype||'|'||itemkey);

END;

Now that we instructed workflow to populate the document attribute with the PL/SQL document it is time to define the execution of the document itself. Before I go to the execution package XX_ATTACHMENT.GET_DOCUMENT there is one pre-requisite step:

The directory where the output files are stored needs to be created in the database as the directory variable recognizable by PL/SQL. This is achieved with the following command

CREATE OR REPLACE DIRECTORY ‘CONC_OUTPUT’ AS ‘operating system path here’. You can find the path in the FND_CONC_REQ_OUTPUTS table.

Make sure the directory you are creating has all the proper permissions so the file can be accessed

Now we can go to the body that will get the concurrent request file put it into BLOB and associate with the workflow attribute

PROCEDURE GET_DOCUMENT

( document_id in varchar2,

display_type in varchar2,

document in out NOCOPY BLOB,

document_type in out NOCOPY varchar2)

IS

v_itemtype varchar2(200);

v_itemkey varchar2(200);

v_request_id fnd_conc_req_outputs.concurrent_request_id%TYPE;

src_lob BFILE;

dest_lob BLOB;

v_lob_len INTEGER;

v_offset NUMBER := 1;

v_buffer RAW(20);

v_buffer_size BINARY_INTEGER := 20;

v_FILEISOPEN INTEGER;

v_FILEEXISTS INTEGER;

get_file_name fnd_conc_req_outputs.file_name%TYPE;

BEGIN

-- since I put the itemtype/itemkey as the document ID this will get the item type out of it

v_itemtype := substr(document_id,1,instr(document_id,'|')-1);

-- this part will get the itemkey

v_itemkey := substr(document_id,instr(document_id,'|')+1,length(document_id)-2);

-- itemkey and itemtype are needed to get the value of the concurrent request id

v_request_id:= Wf_Engine.GetItemAttrText( itemtype => v_itemtype,

itemkey => v_itemkey,

aname => 'REQUEST_ID',

ignore_notfound => FALSE);

--which is needed to get the file name

SELECT substr(file_name,instr(file_name,'/',-1,1)+1,length(file_name))

INTO get_file_name

FROM fnd_conc_req_outputs

WHERE concurrent_request_id = v_request_id;

src_lob := BFILENAME('CONC_OUTPUT', get_file_name);

-- now that we know the file name and location we can load it to BLOB

DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE, DBMS_LOB.SESSION);

DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);

DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,

SRC_LOB => src_lob,

AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );

DBMS_LOB.CLOSE(src_lob);

-- Set the MIME type as a part of the document_type in the workflow attribute

document_type := 'application/pdf' || ';name=' || get_file_name;

-- write the BLOB to the workflow attribute

dbms_lob.copy(document, dest_lob, dbms_lob.getlength(dest_lob));

END;

The PDF output file has been located and added as a workflow attribute in the form of BLOB.

4. Use API from PO emailing to create ad hoc role. Now we have the file but still don’t have the role where we can email it. Here is a package that uses the standard API to add the ad hoc role based on the email address:

PROCEDURE SET_PERFORMER_ROLE

(itemtype in varchar2,

itemkey in varchar2,

actid in number,

funcmode in varchar2,

resultout in out nocopy varchar2)

IS

v_to_email varchar2(300);

v_header_id number;

l_adhocuser_lang WF_LANGUAGES.NLS_LANGUAGE%TYPE;

l_adhocuser_territory WF_LANGUAGES.NLS_TERRITORY%TYPE;

l_quote_email_performer WF_USERS.NAME%TYPE;

l_quote_email_add WF_USERS.EMAIL_ADDRESS%TYPE;

l_display_name WF_USERS.DISPLAY_NAME%TYPE;

l_quote_email_performer_prof WF_USERS.NAME%TYPE;

l_quote_email_add_prof WF_USERS.EMAIL_ADDRESS%TYPE;

l_display_name_prof WF_USERS.DISPLAY_NAME%TYPE;

l_performer_exists number;

l_notification_preference varchar2(20) := 'MAILHTM2';

l_when_to_archive varchar2(80);

l_archive_result varchar2(2);

BEGIN

-- Here I am getting the email address from a workflow attribute where I put it in one of my prior steps. Depending on your flow you can use a query here to get it

v_to_email:= WF_ENGINE.GETITEMATTRTEXT( itemtype => itemtype,

itemkey => itemkey,

aname => 'EMAIL_TO'),

ignore_notfound => FALSE);

-- I took a shortcut here and fixed the language to US to simplify my code

SELECT wfl.nls_language, wfl.nls_territory INTO l_adhocuser_lang, l_adhocuser_territory

FROM wf_languages wfl -- fnd_languages_vl flv

WHERE wfl.code ='US'; --flv.language_code AND flv.nls_language = L_contact language;

--* Bug 2989951. AdHocUser Name should be concatenation of the E-mail Address and the language */

l_quote_email_performer := v_to_email||'.'||l_adhocuser_lang;

l_quote_email_performer := upper(l_quote_email_performer);

l_display_name := l_quote_email_performer;

select count(*) into l_performer_exists

from wf_users where name = l_quote_email_performer;

/* Bug 2864242 The wf_local_users table is obsolete after the patch 2350501. So used the

wf_users view instead of wf_local_users table */

if (l_performer_exists = 0) then

-- This is the actual API call to add the ad hoc user to WF_ROLES

-- Pass in the correct adhocuser language and territory for CreateAdHocUser and SetAdhocUserAttr instead of null

-- if a doesn’t exists it will create it

WF_DIRECTORY.CreateAdHocUser(l_quote_email_performer, l_display_name, l_adhocuser_lang, l_adhocuser_territory, null, l_notification_preference, v_to_email, null, 'ACTIVE', null);

else

-- If a role with this email address already exists then find it

WF_DIRECTORY.SETADHOCUSERATTR(l_quote_email_performer, l_display_name, l_notification_preference, l_adhocuser_lang, l_adhocuser_territory, v_to_email, null);

end if;

wf_engine.SetItemAttrText ( itemtype => itemtype,

itemkey => itemkey,

aname => 'EMAIL_RECIPIENT_ROLE'),

avalue => l_quote_email_performer);

resultout:='COMPLETE:SUCCESS';

END;

In the above package I picked the email from 'EMAIL_TO' attribute and created a role from it which I assigned to the 'EMAIL_RECEIPIENT_ROLE' attribute.

5. Create a notification and a message.

Create Notification. In the Node tab select the performer from the item attribute from the previous step

Create a message

The &MESSAGE_ATTACHMENT attribute is included in the message body and needs to be defined and a message attribute sourced from the global attribute that contains our BLOB with the PDF quote

When creating attribute select its type as ‘Document’ and mark the ‘Attach Content’ checkbox. Point the value of the attribute to the global attribute containing the BLOB. This will create attachment to the email with the PDF or Excel document, depending on what your XML Publisher request generated.

Now attach your message to the notification that already has the performer created from the email address.

Here is how the addition to your workflow would look like