A day in the life of an Oracle Applications Consultant

Application Object Library (AOL)

Sunday, August 22, 2004

Export data directly to Excel by configuring the MIME Type Profile Option

This post outlines how to export your Oracle Applications data directly to Excel by configuring a profile option.

Typically when choosing to export the results of a query or search in Oracle you will choose the File > Export option on the Oracle Applications menu. The data you desire to be exported will by default be exported as a tsv (tab separated values) file which can easily be read using notepad or wordpad. Should you wish to, this file can also easily be imported into Excel by opening the file from within Microsoft Excel and simply clicking Finish when the text import wizard appears.

On a couple of Oracle Applications systems that I worked on I noticed that choosing the File > Export was exporting the results directly to an Excel spreadsheet embedded within a browser. This can often be a better route to take than exporting into a tsv file and then importing into Excel. Subsequent investigation as to why various systems I worked on behaved differently when executing file export revealed that this behaviour is largely controlled by the MIME Type profile option and the parameters that are specified herein.

At this point I believe it’s appropriate to define what MIME is so that readers can understand exactly what they are doing when configuring this profile option. According to the definition of MIME is as follows: -

Short for Multipurpose Internet Mail Extensions, a specification for formatting non-ASCII messages so that they can be sent over the Internet. Many e-mail clients now support MIME, which enables them to send and receive graphics, audio, and video files via the Internet mail system. In addition, MIME supports messages in character sets other than ASCII. In addition to e-mail applications, Web browsers also support various MIME types. This enables the browser to display or output files that are not in HTML format.

Essentially MIME ensures that there is an association between a non-ASCII file and the application that is needed to read it. In our particular instance, within the MIME Type profile option we need to specify a parameter that will tell our default browser to open the file in Excel, Word or another application you would like to see the results in. By default the MIME type profile option has the following parameter: text/tab-separated-values. Changing this value to application/excel or application/winword will result in any export of query results in Oracle being sent directly to Excel or Word respectively. The value of the second portion of this parameter (in this instance excel or winword) depends on what command you need to execute in Windows to run either of these applications. You can determine this by looking at what command you run to open an XLS (Excel) file or DOC (Word) file in Windows. In Windows Explorer, on the file menu, choose Tools > Folder Options > File Types Tab, select the DOC or XLS extension and then click on Advanced. Following on from this, click on the Open Action and then select Edit…The name of the command to run the application associated with this file type will be specified in the Application: box.

As a final test as to whether you have determined the correct command that needs to be used to run Excel or Word, from the Windows Start button choose Run and then type in the command (in this intance excel or winword) and click OK. Your Word or Excel application should run.

Monday, August 16, 2004

Printing Reports in PDF format with Oracle Applications

This post explains how you can modify an Oracle Applications report output format so that it displays as a PDF document. It also outlines how to fix a recent error I received when trying to generate PDF reports.

Oracle Applications allows you to print your reports in a variety of formats. To change the report output format you will need to have access to system administrator responsibility and navigate as follows: - Concurrent > Program > Define. Using the Query or find facilities retrieve the report name you would like to print in PDF format. In the “Output” area of the form as displayed in Fig.1 you will notice a drop down box which gives you the following options: -

  • HTML
  • PCL (HP’s Printer Control Language)
  • PDF
  • PostScript
  • Text
  • XML

It should be noted that if you choose HTML or PDF as the output type with Oracle Reports Programmes, you should use a printer driver that handles HTML or PDF files. Selecting PDF as the format output will ensure that the next time you print the report and view the output, it generates in PDF format (you will only be able to view it if you have Acrobat PDF Reader installed on your PC).

Figure 1: How to modify your report output format to print a PDF Document

Click to view an enlarged image of where you should choose the report output format

Personally, I quite like this option to view output in a PDF document type since it looks a lot “prettier” than looking at the report in plain text or HTML format. You also have the option of saving a copy of the file to your local PC in much the save way as you could save the text or HTML output locally.

One thing I would like to know from any readers that have utilised this feature is whether it’s possible to make use of the security features of Acrobat (such as controlling content copying or extraction) within Oracle Applications so that when the report is generated you have control over the content and form of the document.

Fixing an error received on PDF generation

Last week I received a bizarre error message when trying to view the output of a report whose format was PDF. The error message was - File does not begin with ‘%PDF-’. Considering I was not encountering this message when using other PC’s it became clear to me that the problem was related specifically to my PC. A quick search on Metalink revealed note 206727.1 which made me realise that somewhere along the line I needed to change my browser settings for Internet Explorer. I made the following changes to my brower options which rectified the problem.

Selected Tools > Internet Options > General tab > Settings. Under “Check for newer versions of stored pages” there were four possible selections: -

  • Every visit to the page
  • Every time you start Internet Explorer
  • Automatically
  • Never

I had “Every visit to the page” selected and discovered that choosing any of the other three options would now successfully generate my PDF report within IE when choosing to view report output.

Friday, August 13, 2004

Currency Formatting Profile Options in Oracle Applications

This post talks about various profile options available in Oracle Application to display your numerical values in the desired format.

I’ve spent the last couple of weeks training users at the Government of Botswana on the Development and Recurrent Budgeting Processes we are implementing at this organisation. This week attendees pointed out that the thousand’s separator which is normally a comma was not being displayed for the numbers they were inputting and viewing. A quick search on Metalink revealed this note which points out the profile option that needs to be modified to correct this problem.

I thought it would be useful to mention three fairly useful currency profile options which can be found under the System Administrator responsibility through the following navigation path: Profile > System

1. Currency: Negative Format - Options: (XXXX),

, -XXXX, XXXX- (Hope this doesn’t attract too many adult site surfers)

2. Currency: Positive Format - Options: XXX, +XXX, XXX+

3. Currency: Thousands Separator - Options: Yes or No (setting this to Yes will ensure that the thousands are comma separated)

Friday, July 30, 2004

Fndload is not a command line tool

Fndload is a tool used at patch-upgrade time to migrate data from the Oracle reference instance to your installation. Via fndload seeded responsibilities, menus, profile options and many many more are delivered.

Fndload can also be used at your project by dba’s or technical consultants to migrate-script the setup from one instance to an other. Those people will tell you that fndload requires a first set of mandatory parameters (apps/apps 0 Y) and then some object specific parameters.

But you do not need the unix prompt to use fndload. Make it a functional tool by creating a concurrent program that points to the seeded fndload executable.

Seeded examples you can explore in the instance you’re on now are:

  • Download HRMS Taskflows
  • Forms Configurator - Upload Template
  • Plan Design Copy - Import (Benefit plans)

Forget about the first set of parameters. The concurrent manager handles these for you. The only parameters you have to create and validate and restrict are the functional ones. In the examples above you can see how Oracle implemented it.

Page 2 of 2 pages  < 1 2