How to... in Oracle EBS

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

2009-10-25T23:11:00.000-07:00

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

2009-10-25T00:01:00.000-07:00

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

)

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 automatically send an EBS XML Publisher document by email

2009-10-24T00:01:00.000-07:00

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:

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.
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
Write a PL/SQL package that puts the file into a BLOB and attach that package to a workflow attribute of a document type.
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.
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.

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.

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

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

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)

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.

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)

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.

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

Find query behind the Form or OA Framework page

2009-07-10T06:00:00.000-07:00

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

2009-07-09T10:37:00.000-07:00

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

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

2009-07-08T19:52:00.000-07:00

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

2009-07-07T22:08:00.000-07:00

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:

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

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.

How to create a price list based on item cost - Part I

2009-07-05T23:46:00.000-07:00

To create a price list that has prices of all items based on the item cost plus markup you could use the standard functionality to copy the costs into prices and create a price list that has a cost plus markup. While it works, it is not very flexible and as cost changes and new items are added the price list requires constant attention and manual updates.

Instead doing that you can create a price list that has only one line for ALL ITEMS and dynamically sources the current cost and applies markup at the moment of the actual transaction. This does not require any maintenance, other than updating the costs which is done by the costing department anyway. This approach consists of 3 steps:

Map the item cost as a pricing attribute
Use the pricing attribute to create a dynamic pricing formula that adds markup
Create ALL ITEMS price list and assign the pricing formula there.

1. Mapping the cost as a pricing attribute

I could utilize the standard Pricing Attribute context, but I recommend adding a separate one for better transparency. You can later build upon it and create additional attributes such as Material Cost, Overhead cost etc.

Setup -> Attribute Management -> Context and Attributes

Select a column for attribute mapping, i.e. PRICING_ATTRIBUTE1 and a numeric value set such as Number_15. Do not select the seeded ‘Number’ value set as it is not numeric.

Now that we have our new pricing context defined go to

Setup -> Attribute Management -> Context and Attributes

Now, map the attributes on line level only.

In the ‘Global Object Name’ field you can see the name of the record structure that has all the variables that can be used when deriving the price. In this case it is OE_ORDER_PUB.G_LINE

You can go to the package definition of OE_ORDER_PUB and see what fields the G_LINE record contains

Now, if I just typed OE_ORDER_PUB.G_LINE.UNIT_COST in the User Value String then my pricing attribute would return the unit cost and it would be usable. The problem with this approach is that it is limited to Order Management and the UNIT_COST field is not always populated even there.

Lets take a more generic approach that should work for any record type.

In my previous post I used the

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)

function to get the cost of any item.

I will paste this function directly into the ‘User Value String’ of the pricing attribute mapping. The inventory_item_id and organization_id need to be substituted by the OE_ORDER_PUB.G_LINE variables:

Nvl(cst_cost_api.get_item_cost(1, OE_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL),0)

The NVL prevents the pricing engine to error out when no cost is defined. Depending on the situation, you may want it to error out.

This is what needs to be pasted into the ‘User Value String’ field.

Now, go to Tools and select the ‘Build Attribute Mapping’ to complete the definition

This would work fine, except that my shipping warehouse can be in USD and my order in EUR. Since the cost is always returned in the currency of the set of books of the warehouse the above expression needs to be multiplied by the exchange rate that you can also get from my previous post:

gl_currency_api.get_rate(from_currency_code,to_currency_code, conversion_date, Conversion_type)

For transparency it is better to source the exchange rate as a separate pricing attribute such as CONV_RATE and then multiply them in the pricing formula

Now, TO_CURRENCY_CODE is OE_ORDER_PUB.G_HDR.TRANSACTIONAL_CURR_CODE

Note that I took currency from the header record, which is also populated at runtime.

Conversion date is OE_ORDER_PUB.G_LINE. PRICING_DATE

And conversion type is fixed such as ‘Corporate’

The only remaining parameter is

from_currency_code

To find the operating unit of the shipping warehouse in R12 use the below function:

qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID).

Let me know if you find a relevant function in 11i. The ones I found are either not public, or they are not available to every installation, such as the Indian localization function jai_cmn_utils_pkg.get_operating_unit( 'anything', SHIP_FROM_ORG_ID). In 11i you may have end up writing your own function.

Now that the operating unit of the shipping warehouse is known, the CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode function can be used to derive the from_currency. The full expression is:

CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID))

The complete expression that finds the conversion rate between the shipping warehouse and the order operating unit is :

gl_currency_api.get_rate(

CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID)),

OE_ORDER_PUB.G_HDR.TRANSACTIONAL_CURR_CODE,

sysdate,

'Corporate')

Create and map the Conversion Rate Pricing Attribute the same way as Item Cost.

Now that have both item cost and the conversion rate defined we can build our Pricing Formula

2. Pricing Formula

Pricing Formula -> Formula setup

Go to Tools -> Build Formula Package

3. Price List

The Price list needs to be defined for all base units of measure.

Now the price list is ready to be used.

Querying values in foreign currencies

2009-06-17T10:47:00.000-07:00

Here is the API you need to use to query the conversion rate between 2 currencies for a specific date.

gl_currency_api.get_rate(from_currency_code,to_currency_code, conversion_date, Conversion_type)

In a query it would look like this:

select

gl_currency_api.get_rate('USD','EUR', sysdate, 'Corporate')

from dual

Now we can combine that with the cost queries from the previous post to get the value of on-hand quantities in any warehouse in the EUR currency:

select msi.segment1 item,

sob.currency_code warehouse_currency,

cst_cost_api.get_item_cost(1,moq.inventory_item_id, moq.organization_id,NULL,NULL) cost,

gl_currency_api.get_rate(sob.currency_code,'EUR', sysdate, 'Corporate') conversion_rate,

cst_cost_api.get_item_cost(1,moq.inventory_item_id, moq.organization_id,NULL,NULL)*

gl_currency_api.get_rate(sob.currency_code,'EUR', sysdate, 'Corporate') cost_in_eur

from

gl_sets_of_books sob,

mtl_organizations mo,

mtl_onhand_quantities moq,

mtl_system_items_b msi

where

mo.set_of_books_name=sob.name

and moq.organization_id=mo.organization_id

and moq.organization_id=msi.organization_id

and moq.inventory_item_id=msi.inventory_item_id

and mo.organization_code='M1'

The gl_currency_api.get_rate will return exception if the exchange rate is not defined for the day.

How to find item cost

2009-06-16T10:16:00.000-07:00

Another common issue when writing queries is getting an item cost.

Here are the APIs to use in queries:

For discrete manufacturing:

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)

here is a sample query:

select segment1 item,

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL) cost

from mtl_system_items_b

If you are in an OPM organization the above won't work and instead you need to call

select msi.segment1 item,

GMF_CMCOMMON.get_cmpt_cost( inventory_item_id, organization_id, sysdate, cmm.cost_type_id ,0) cost

from mtl_system_items_b msi, CM_MTHD_MST cmm

where cmm.cost_mthd_desc='Standard Cost'

How to query key flexfields

2009-06-15T21:59:00.000-07:00

It is quite common that in Oracle Applications you need to write queries listing specific account combinations. The problem with that is that the account combinations are stored as their ids and you have to go to the GL_CODE_COMBINATIONS table to find the specific segments and concatenate them one by one.

Easy to do but always waste of time. Moreover, each EBS install has a different number of segments and query written for one client needs to be rewritten for another.

Well, not any more. There are Oracle APIs that allow you to get the complete combination as a string by passing the CCID

Here is the syntax:

fnd_flex_ext.get_segs('SQLGL', 'GL#', chart_of_accounts_id, code_combination_id)

Now, if you are on release 12 and want to have the concatenated combination descriptions you can also run this:

xla_oa_functions_pkg.get_ccid_description (chart_of_accounts_id, code_combination_id)

Here is a sample query:

select

fnd_flex_ext.get_segs( 'SQLGL',

'GL#',

(select chart_of_accounts_id

from GL_SETS_OF_BOOKS

where name='Vision Operations (USA)'),

code_combination_id) account,

xla_oa_functions_pkg.get_ccid_description(

(select chart_of_accounts_id

from GL_SETS_OF_BOOKS

where name='Vision Operations (USA)'),

Code_combination_id) description

from GL_JE_LINES

The fnd_flex_ext.get_segs call is universal and will work for any key flexfield. Here is the syntax for HR position hierarchies:

FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id)

and here is a sample query:

select

FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id) position_definition

from per_position_definitions

OM Defaulting rules Part 2

2009-06-09T10:05:00.000-07:00

I worked on a project once where we had different lines of products maintained by different product managers across several sales territories and different currencies.

When an order was placed the price list was not specified on the order header but instead it was selected automatically on lines based on the sales channel, territory and the line of products the item belonged to.

This worked fine with one exception; the currency on the order header defaulted from the order type instead of the customer site. Prices were tailored to a specific market and specific currency and we could not simply create a multicurrency price list. Instead we have created a mandatory flexfield on the customer site and used the PL/SQL defaulting rules to dynamically default it on the order.

In the defaulting order screen you can select PL/SQL as the source type and specify the Package and Function name

The above shows the seeded values where the currency is taken from the Set of Books.

Note that there is no way to pass a specific order number or ID, the values will be taken from the current ONT_HEADER_DEF_HDLR.g_record which is automatically populated by the application for any order.

Here is the code I used to default currency from Attribute11 of site use flexfield:

FUNCTION Get_DEFAULT_CURRENCY

( p_database_object_name IN VARCHAR2

,p_attribute_code IN VARCHAR2)

RETURN VARCHAR2 IS

l_invoice_to_org_id NUMBER := APPS.ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id;

l_currency VARCHAR2(4);

l_debug_level CONSTANT NUMBER := apps.oe_debug_pub.g_debug_level;

BEGIN

IF l_debug_level > 0 THEN

oe_debug_pub.add('ENTER Get_sales_channel');

oe_debug_pub.add('Sold To Org ID :'||l_invoice_to_org_id );

END IF;

IF l_invoice_to_org_id is not null

AND l_invoice_to_org_id <> apps.fnd_api.g_miss_num

THEN

BEGIN

--Here is the actual code

select attribute11

into l_currency

from HZ_cust_SITE_USES_ALL situs

where situs.site_use_id=l_invoice_to_org_id;

RETURN l_currency;

EXCEPTION

-- Return null if there is noinvoice to site

WHEN NO_DATA_FOUND THEN

RETURN NULL';

END;

-- Return null if there is no customer

ELSE

RETURN NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

IF l_debug_level > 0 THEN

apps.oe_debug_pub.add('Error in Get_Currency') ;

apps.oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;

END IF;

RETURN NULL;

END Get_DEFAULT_CURRENCY;

OM Defaulting rules Part 1

2009-06-08T21:24:00.000-07:00

Recently I came across a very annoying issue. Every time we changed the bill-to or ship-to address on the order header the 'Date Ordered' field would re-default to SYSDATE, messing up all the sales reporting.

A short research on metalink revealed note 748269.1 which recommends updating package OE_Dependencies_Extn and changing some seeded dependencies. To make a long story short we did that ending up with a 3 weeks long P1 issue that did not seem related in any way..

There is, however, a trivially simple way of fixing this problem using the OM defaulting rules:

(N) OM -> Setup -> Rules -> Defaulting

Query OM header, scroll down to Ordered Date and change the seeded defaulting rule to:

This will make the date default to itself first and to sysdate only if it was NULL in the first place.

Choosing the right instance

2009-06-03T20:31:00.000-07:00

Working in a multi instance environment with the main PROD instance accompanied by TEST DEV and others can be tricky. While in my previoust post I covered on how to tell the difference if one is looking for it, it is too easy to confuse them and create a test record on the Production instance, or enter a valid customer order into Test, especially if both are opened at the same time.

The best way to avoid it is to change the application color and apperance for each instance.

FORMS

Most of the time, when DBAs make a clone they set the value of the

Site Name

profile by putting the name of the instance there. This changes the name of the application displayed in the bar on top of the window:

This is very useful but often can be overlooked. A good way to make the difference apparent is changing the colors of forms which can be done with the

Java Color Scheme

profile. As long as every instance has a different color, the risk of confusing them is minimized

Another, a bit more radical approach is to remove the oralce graphical interface and display pure Java forms by changing the

Java Look and Feel

profile. This will make your 11i or R12 applications look vary much like the old 11.0.3 version.

OA Framework

The Java profile options will not change the way your Oracle Framework pages look like.

To change them you need to se the

Oracle Applications Look and Feel

profile.

Here is an example of how the pages look after the profile change

If you want to go further than that, you can use the 'Customizing Look and Feel Administrator' responsibility to design your own graphical theme.

There is also a http://oracleskins.com/ company that designs customized skins.

How to find which instance I am using?

2009-06-02T22:10:00.000-07:00

Forms

When in Oracle Applications forms the simplest way to identify whether we are on the right instance is go to:

Help -> Diagnostics -> Examine

from the application toolbar menu and find the field:

TWO_TASK

in the

$ENVIRONMENT$ block.

This will display the database identifier as on the picture below.

Database

In TOAD or plain SQL level the database can be found by running

select name from v$database

There is only one row in that table and it can give you some addtional info, like when the instance was refreshed from production.

OA Framework

In Oracle Framework enable the 'FND:Diagnostics' profile for your user and re-log into the application. In the lower left corner of the page you will see a link to About Page same as on the picture below

This will take you to pages containing detailed information about your installation.

Switch to the 'Page Context' tab and it will not only identify your database but also provide all the information needed to log in via TOAD or SQL, other than the user name and password.

This info can be either used to create TNSNAMES entry or used directly to connect to the database. In SQL*PLUS the syntax would be:

The same syntax can be used in other tools like i.e. Workflow