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 tool itself 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 a file on the server, then 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. 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 it is a week long development project and we can only submit a budget for its approval next week so if it gets approved in the next 2 weeks we can start development and testing so after a month or so he can have his already outdated report so he can ask for a change request .... If you are on a hosted platform it may be significantly more difficult than that.

Simply, I needed an easy and quick way to create needed 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 and then have to login into it 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 and provides 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/demos?DemoName=EO&ProductName=Excel-Out

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. The package parses the query and converts it into native Excel format, so all the report outputs open directly in Excel.

Now, development and deployment can take only as much time as it takes to develop the query, and other than 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 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.

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 PDF user manual. It is included in the installation package.

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 will be 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 does nothing, but if you want to suppress it check metalink notes 780817.1 and 759410.1 or the PDF user manual that is part of the installation download.

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 any format, but for user convenience it is better to format them with with to_char(date,'dd-mon-yyyy') or any other format the user is 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 flie upload 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 from 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 on 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 send an EBS 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 into it and wait for the request completion. This is added after a special event such as the quote approval.
2. Identify the directory where the concurrent request file 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 one DB to point to a directory on the application server of 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

For me it only worked if I passed the itemkey and itemtype as static values but never as variables. To work around that I leave the value blank and populate 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 use a shortcut here and fixed the language to US

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 with 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 document.

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

Here is how the addition to your workflow would look like

Find query behind the Form or OA Framework page

FORMS

The simplest way to find a query in a form is to go to

Help -> Diagnostics -> Examine from the navigation toolbar

select SYSTEM block

and LAST_QUERY field

This approach has the disadvantage that it only shows the last query run by the form while there can be multiple queries run in different blocks.

To see a query in a block, first check the name of the block.

Now go to Help -> Diagnostics -> Item

Change the Object to 'Block'

and in 'Object1' select the name of the block.

The 'Value' field will automatically display the query that was run to retrieve the data in the block.

OA FRAMEWORK

The OA framework is not as flexible as Forms, but there is still a way to see the queries run by OA framework pages.

Forst set the prodile FND: Diagnostics to Yes for your user.

Now, when you log to any JSP page you will see 'About Page' in the bottom left corner.
Click on it and then click on 'Expand all' as on the picture below.

When you scroll down you will see the views on which the records are based

Click on the view name to see the query.

Switch a single segment in before insert trigger for an account combination

Some time ago I had an interesting problem in AP. The withholding tax distribution in AP was automatically created based on the tax rules, but the tax distribution was sourced from the tax setups and was inflexible. We wanted to have the last segment (intercompany) of the tax distribution equal to the last segment of the AP line distribution, so we could split the taxes between the companies, the same way that we were splitting expenses.

To resolve the problem I have created a BEFORE INSERT trigger on the AP_DISTRIBUTIONS_ALL table which called a custom function that:

1. Translated the expense line CCID into the segment combination

2. Translated the tax CCID to the segment combination

3. Determined the last segment in the line combination

4. Replaced the last segment of the tax combination with the last segment of the expense line combination.

5. Translate the new segment combination into a CCID.

The last part proved to be tricky because the CCID for the new combination may or may not exist and I didn’t want to get into building a logic that would check it. In addition to that there was always risk that an invalid combination can be created, so it had to be validated against GL cross validation rules. Under other circumstances I might have used account generator workflow but I wanted to keep it as simple as possible since it was done in a database trigger and too much code could affect the performance. This was resolved with a call to

FND_FLEX_EXT.get_ccid('SQLGL','GL#', v_coa_id,fnd_date.date_to_canonical(sysdate),v_tax_combination);

which

- retrieved the combination if it existed

- created it if it didn’t

- validated it against GL cross validation rules

Lets translate the above points into APIs used:

1. FND_FLEX_EXT.get_segs('SQLGL', 'GL#',v_coa_id,p_CCID) -- concatenated tax account

2. FND_FLEX_EXT.get_segs('SQLGL', 'GL#',v_coa_id, il.DIST_CODE_COMBINATION_ID) – concatenated expense account

3. FND_FLEX_EXT.breakup_segments(concatenated_segs => v_tax_combination

,delimiter => '.'

,segments => v_tax_array); -- number of segments

4. v_tax_array(last_segment):=v_line_array(last_segment); -- switch last segment of the tax segments array to last segment of the expense array

5. FND_FLEX_EXT.get_ccid('SQLGL','GL#', v_coa_id, fnd_date.date_to_canonical(sysdate), v_tax_combination); --recreate the new array as CCID. This is returned to the before insert trigger and entered into the table.

For reference here is the actual package body that I used:

CREATE OR REPLACE package body APPS.XX_TAX AS

Function MATCH_INTERCO_SEGMENT

(p_CCID IN NUMBER,

p_invoice_id IN NUMBER,

p_line_type in Varchar2,

p_tax_name varchar2

) return number

IS

v_new_account number;

v_coa_id number;

v_tax_combination varchar2(100);

v_line_combination varchar2(100);

v_tax_array FND_FLEX_EXT.SegmentArray;

v_line_array FND_FLEX_EXT.SegmentArray;

o_number_of_segments number;

V_ERROR VARCHAR2(2000);

Begin

-- get the chart of accounts

select gls.chart_of_accounts_id

into v_coa_id

from financials_system_parameters fsp, gl_sets_of_books gls

where fsp.set_of_books_id = gls.set_of_books_id;

-- get the combination from the ccid

v_tax_combination := fnd_flex_ext.get_segs('SQLGL', 'GL#',v_coa_id,p_CCID) ;

commit;

-- this returns the number of segments but also feeds the segments to the v_tax_array

o_number_of_segments:=FND_FLEX_EXT.breakup_segments(concatenated_segs => v_tax_combination

,delimiter => '.'

,segments => v_tax_array);

-- get the account combination for the invoice expense line that has the tax group with the tax for our tax line

-- this logic is specific to the withholding tax and establishes link between the tax and the expense line

select fnd_flex_ext.get_segs('SQLGL', 'GL#',v_coa_id,il.DIST_CODE_COMBINATION_ID)

into v_line_combination

from ap_invoice_distributions_all il, AP_AWT_GROUPS ag, AP_AWT_GROUP_TAXES at, AP_TAX_CODES tc

where 1=1

and il.AWT_GROUP_ID=ag.group_id

and ag.group_id=AT.GROUP_ID

and tc.name=at.tax_name

and at.org_id=fnd_profile.value('ORG_ID')

and at.org_id=tc.org_id

--and tc.tax_id=p_tax_code_id

and tc.name=p_tax_name

and il.invoice_id=p_invoice_id

and rownum=1; -- this is in case if they are more than one line with the same tax group. The first line is sufficient.

-- this returns the number of segments but also feeds the segments to the v_line_array

o_number_of_segments:=FND_FLEX_EXT.breakup_segments(concatenated_segs=>v_line_combination

,delimiter=>'.'

,segments => v_line_array);

v_tax_array(o_number_of_segments):=v_line_array(o_number_of_segments); -- last segment of the tax combination is switched to the last segment of the line combination

v_tax_combination:=FND_FLEX_EXT.concatenate_segments(o_number_of_segments,v_tax_array,'.'); -- concatenate the new combination into string

v_new_account:=FND_FLEX_EXT.get_ccid('SQLGL','GL#', v_coa_id,fnd_date.date_to_canonical(sysdate),v_tax_combination);

return v_new_account;

EXCEPTION

When others then

-- in case of failure return the original account

v_new_account:=p_CCID;

return v_new_account;

END;

END XX_TAX;

How to find a report

Quite often business users ask me on how to get some specific data from Oracle. Before I start sketching any functional design for a new report, I check the list of all concurrent requests in Oracle to see if there isn't already one available. When I do find it, it is difficult to determine the responsibility where it is assigned to. To work around this inconvenience I prepare the below query that lists all the reports containing a specific string in the name, their responsibilities and application. Quite often this makes life a bit easier.

select resp.responsibility_name,

pr.user_concurrent_program_name,

ex.executable_name,

ex.application_name

from FND_EXECUTABLES_FORM_V ex,

FND_CONCURRENT_PROGRAMS_VL pr,

FND_REQUEST_GROUP_UNITS rgu,

FND_REQUEST_GROUPS rg,

FND_RESPONSIBILITY_VL resp

where 1=1

and pr.executable_id=ex.executable_id

and rgu.request_group_id=rg.request_group_id

and rgu.request_unit_id=pr.concurrent_program_id

and resp.request_group_id=rg.request_group_id

and pr.user_concurrent_program_name like '%Invoice Audit%'

This will not work if the report is assigned to a menu function, rather than the responsibility.

How to create a price list based on item cost - Part II, Intercompany Price List in Oracle

In my previous post I described how to create a price list based on cost. Why would we want to have a price list based on cost? From my experience there are 2 common scenarios where this comes handy:

1. RMA price list for obsolete items that are no longer priced, but customers keep returning them.


2. For intercompany transactions where the transfer price between organizations is cost plus markup, and it would be cumbersome to have a manually maintained price list where all the costs are already defined.

The RMA situation is not that common, but all multinational companies have intercompany transfers. I have seen one company where the number of internal price list lines exceeded 180,000 while the regular sales price lists had only 42,000. They could no longer maintain it and had to write custom concurrent requests to do it for them.

The beauty of the below solution is that it requires no customizations and it will price all intercompany transactions based on ‘Cost + markup’ logic, with no maintenance required at all.
To begin with, I recommend creating separate responsibility for Intercompany pricing, to keep the IC and Sales price lists, modifiers, formulas, qualifiers and modifiers separate. This is not required, as alternatively you can set up the relevant profile options on a dedicated user level, separate responsibility is by far a cleaner solution.

Go to Application Developer -> Profiles and check the responsibility checkboxes for the below:

Now create your custom responsibility
(N) System Administrator -> Security -> Responsibility -> Define

Set the QP: Pricing Transaction Entity and QP: Source System Code profiles on the Responsibility Level.

Add the responsibility to your user. You have successfully set up your tools to begin working with Intercompany Pricing.
In addition to the above, set the following profiles on the site level

- CST: Transfer Pricing Option - Yes, Price As Incoming Cost (read more about other settings)
- INV: Advanced Pricing for Inter-Org Transfers - Yes
- INV:Advanced Pricing for Intercompany Invoice - Yes
- INV: Always suffix inter-Company AP Invoice number – Yes (optional)
- INV: Inter-Organization Currency Conversion – Corporate
- INV: Intercompany Currency Conversion - Corporate
- INV:Intercompany Invoice for Internal Orders – Yes (optional)

Set up shipping networks in inventory responsibility.
Log into the Intercompany Pricing responsibility -> Setup -> Attribute Management -> Attribute Linking and Mapping. Select Intercompany Transaction as the Pricing Entity and Pricing Context, then map the same COST attributes here as for the Order Fulfillment Entity in the previous post.

Map both pricing attributes using the PL/SQL logic, the same as in previous post.
For cost:

cst_cost_api.get_item_cost(1, INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL)
Note that record OE_ORDER_PUB.G_LINE in Intercompany pricing entity is replaced with INV_IC_ORDER_PUB.G_LINE

For Exchange Rate:

gl_currency_api.get_rate(CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(INV_IC_ORDER_PUB..G_LINE.SHIP_FROM_ORG_ID)),'USD', sysdate,'Corporate')

The 'USD' needs to be replaced with the currency of the intercompany transactions for your installation.

This will work on R12 as long as you don’t use Global Purchase Agreements nor have Oracle Process Manufacturing enabled for any of your organizations that do intercompany pricing. In those cases you will need to use additional logic, which I am omitting here to keep the case simple. As I mentioned in my previous post additional coding may be needed for 11i where the qa_moac_pkg.derive_ou_id function does not exist.
After you are done mapping run the Build Attribute Mapping Rules Concurrent request .
I think that all of the above should be seeded, but it isn’t. The enhancement requests to do so are sitting out on metalink for the last couple of years. On top of that the All Items Product Attribute is not mapped for the ‘Intercompany Transaction’ pricing entity.

Lets fix this obvious omission.

Press Link Attributes button.

Run Build Attribute Mapping request again.

Now that all the core setups are done lets create an Intercompany pricing formula.

Because this formula is created in the Intercompany Pricing responsibility, it is visible, but not updateable in the regular Oracle Pricing Manager or any OM responsibility With the formula ready we can create the Intercompany Price List.

Make sure to list all basic units of measure that can be transacted. In the above example I had 4.

Note the Pricing Transaction Entity for your price list. It will not be usable in Order Management or any other sales applications.

There is a bug in EBS, that Oracle Support did not fix since I first discovered it in 2006. While the sales applications residing in Order Fulfillment Pricing Entity do not see the Intercompany Transactions Entity, the opposite is not true and regular sales Price lists are visible in the Intercompany Transactions entity. To work around this problem assign precedence =1 to your intercompany price list lines.

There is another bug that prevents an Internal Order form being booked if the Intercompany price list is not assigned to the Internal Order type. To work around it assign it to the Internal Order type and also to the internal customer/customer site on the customer definition. From the logical perspective this is not really needed since the intercompany pricing is done by the Trip Stop interface which happens AFTER the order is booked and shipped and all the prices on the intercompany order are dummy values.

Now that you have your intercompany price list set up, you can create Internal Requisitions, Internal Sales Orders, ship them, create Intercompany AP and AR invoices and never worry about updating your intercompany price list.

The Global Purchase Agreement, transfers originating from multi node transactions, and different markups between different organizations, including logical nodes can be also set up to work, but this is a too long topic to cover in this post.