The Feature

How to Use XML Publisher to Generate Oracle Reports in Excel and PDF

In the past, Oracle E-Business Suite Customers seldom used outputs of the seeded Oracle reports because they were unfriendly, and frankly, hard to use. Unfriendly because the generated output was always fixed width courier only font, contained no graphics, and could only be in black and white. Hard to use because the only output was text. In the business world, we all know how important the final output is and text only is not even an option. Businesses require PDF and XLS output that support tables, charts, graphics, color, etc.

Today, Oracle offers a solution that enables our Oracle E-Business Suite Customers to turn the once unfriendly and useless Oracle seeded reports into PDF and XLS outputs of color, graphics, and tables. The solution is XML Publisher. XML Publisher is a java based reporting tool that is bundled for free within the technology stack of the Oracle E-Business Suite. The concept is similar to that of “mail-merge” where the data and template are maintained separately until the two are “married” to generate the desired document. XML Publisher separates a report’s data and layout component into two manageable pieces while it supports the desired outputs of PDF, HTML, XLS and RTF. The prerequisites to use XML Publisher include:

• XML Data
• Report Template
• Registration of Data Definition and Template at XML Publisher

How does XML Publisher leverage on Oracle seeded reports? An Oracle seeded report is a report definition file that integrates both the data query and the report layout into one file. XML Publisher will use Oracle Reports for only the data query portion and will ignore the defined report layout. This will fulfill one of XML Publisher’s prerequisites: XML Data. So the question is, how do you set up the Oracle system to only use the data query portion of the Oracle report?

• Log into Oracle with System Administrator Responsibility
• Navigate to > Concurrent > Program > Define
• Search for the desired Oracle Report
• Change the Output Format from the default ‘Text’ to ‘XML’
• Take special note of the Program Short Name (you’ll need it later to register the data at XML Publisher)

How does it benefit Oracle E-Business Suite customers? XML Publisher provides full control of the layout and look and feel of the report to the Customers. This means the Customer will fulfill the second prerequisite, the Report Layout. Customers will leverage on familiar tools like Microsoft Word and Adobe Acrobat to create the report layout with reference to the XML Data Tags. So the question is, how does the Customer create the Report Layout? PDF Forms template are intended for Customers that require a fixed look and feel such as completed IRS forms and are created in Adobe Acrobat. RTF templates are intended for all other purposes and are created in Microsoft Word. In this example, we’ll go over how to create an RTF Template.To create an RTF Template, you’ll want to download the XML Publisher Desktop Utility and its prerequisite Java Runtime Environment. This enables you to create and test your Report Template in a localized environment, your computer, before registering it at Oracle XML Publisher. So where to download the XML Publisher Desktop Utility?

• Navigate to Oracle eDelivery Site
• Select Product Pack: Oracle Application Server Products
• Select Platform: MS 32 bit
• Click on the GO Button
• Select and Download Oracle XML Publisher Release 5.6.2 Media Pack for Microsoft Windows

The XML Publisher Desktop Utility is a plug-in to Microsoft Word. To create the RTF template, you must first load the XML Data File by clicking on the Data button and selecting Load XML Data. To get a sample XML data file, you can run the Oracle seeded report and download the output. Once the data file is loaded, you can make references to the XML Data elements. For example, if you want to reference the ‘Project_Number’ XML Data Element, the format you would type would be … The common used tags include:

• for-each | end for-each
• if | end if
• choose | when | end when | otherwise | end otherwise | end choose

Alternatively, you can use the Desktop Wizard functionality by clicking the Insert button and selecting Table/Form to drag and drop the desired XML Data Elements into the Template window. This will automatically generate a table for you.

You can test your RTF template with the loaded XML Data File by click on the Preview button and selecting one of the supported output formats. Once satisfied with the created RTF Template, the final steps is to register both the Data Definition and the Template at XML Publisher. The steps are:

Register the Data Definition:

• Log into Oracle with XML Publisher Administrator responsibility
• Navigate to > Home > Data Definitions
• Click on the Create Data Definition button
• The Code must be the Concurrent Program Shortname from above

Register the Template

• From Data Definitions, Navigate to > Templates
• Click on the Create Template button
• The Data Definition value should be the Data Definition you registered above

When the Customer runs this configured report through the Concurrent Manager, the Customer will follow the same steps to run the standard Oracle Report by selecting the report in the Single Request Screen and entering the desired run parameters. Now, the Customer has one additional option to select the desired output format of the report. From the Single Request Screen, click on the Options button and select the desired output.

Christmas Is Over - Get To Work On Daylight Savings Time

I hope everyone’s holiday was at least as wonderful as mine. Now forget those visions of dancing sugarplums and start working on an upcoming issue with Oracle products - Daylight Saving Time.

Background

Under the Energy Policy Act of 2005, Daylight Saving Time begins 3 weeks earlier and ends one month later in 2007, running from March 11 to Nov. 4. The US Department of Transportation, which is responsible for administering daylight savings time, has published a reminder that can be found here. Oracle software does not support the extended periods of Daylight Saving Time.

Impact to E-Business Suite Users

>In the E-Business Suite, there are two types of date fields:

1) Dates with a time component to show a specific point in time.

2) Dates without a time component, showing date but not a specific time

The impact to E-Business customers on Daylight Saving Time will be that time values, whether recorded or displayed, will be offset by one hour from the actual time. Incorrect time values will appear in all date fields with a time component. In addition, there is a possibility of incorrect dates (for example, in the event that a transaction takes actually takes place at 12:55 a.m. but the system time lags the actual time by one hour) . Under certain circumstances, incorrect dates could appear in either type of date field.

The E-Business Suite customers impacted will fall into one or more of the following scenarios:

1) an EBS database running in an affected time zone (in other words, all EBS customers in the U.S. and Canada)

2) an EBS user with the “Client Timezone” profile option set to an affected time zone

3) Communications or data transfers between time zones, at least one of which falls into Daylight Saving Time.

At this point, Oracle has not heard much feedback from EBS customers so they’ve assessed this issue as a “minor inconvenience”. Nevertheless, they are working the issue.

Oracle Research

Although I’ve only discussed the impact on E-Business users, this issue also impacts the Oracle database and middleware. Although this is a developing issue, there are several sources of information available:

Oracle Metalink

• Notes 403311.1 and 399375.1 discusses the implications for Oracle E-Business Suite users.
• Notes 397281.1 and 359145.1 discuss the impact on and the patches for the Oracle database, Oracle middleware, and various supporting operating systems - many patches called for between these two notes. The latter note will also lead the reader to Metalink Note 396387.1, which covers workarounds when DST-related database patches are not available for a particular configuration.

Other Sources

• Oracle’s Director of E-Business Technology, Steven Chan, has written articles on this subject that can be found on the Oracle E-Business Suite Technology blog. The articles can be found here and here.

OAUG is also tracking this issue. Their progress can be followed here.

Next Steps

I suggest that every Oracle user in the U.S. and Canada should at least give some brief consideration to this Daylight Saving Time issue. Maybe it doesn’t impact your organization or perhaps you can live with the glitch. On the other hand, you may have a problem to resolve before March. If you fall in the latter category, you’ll want to start planning now.

Gee, don’t you wish it was still Christmas!

A Politically Correct Holiday Wish

Let me wish you Merry Christmas with this politically correct holiday wish from an anonymous source…

Please accept with no obligation, implied or implicit, my best wishes for an environmentally conscious, socially responsible, low stress, non-addictive, gender neutral celebration of the winter solstice holiday, practiced with the most enjoyable traditions of religious persuasion or secular practices of your choice with respect for the religious/secular persuasions and/or traditions of others, or their choice not to practice religious or secular traditions at all.

I also wish you a fiscally successful, personally fulfilling and medically uncomplicated recognition of the onset of the generally accepted Gregorian calendar year 2007, but not without due respect for the calendars of choice of other cultures whose contributions to society have helped make our country great and without regard to the race, creed, color, age, physical ability, religious faith or sexual preference of the wishee.

By accepting this greeting, you are accepting these terms: This greeting is subject to clarification or withdrawal. It is freely transferable with no alteration to the original greeting. It implies no promise by the wisher to actually implement any of the wishes for her/himself or others and is void where prohibited by law, and is revocable at the sole discretion of the wisher. This wish is warranted to perform as expected within the usual application of good tidings for a period of one year or until the issuance of a subsequent holiday greeting, whichever comes first, and warranty is limited to replacement of this wish or issuance of a new wish at the sole discretion of the wisher.

How to Reclass Projects and Tasks En Masse

Darn it. You manually set up your projects and you found out some attributes were configured incorrectly. Now what? There is good news. You can use the Project Administration feature (Oracle Projects 11i) to update Project and Task Organizations in one, easy update.

The way it works is that you create an ‘update batch’ of changes you want to apply. As Project Billing Super User [or Costing Super User], navigate to Project Administration > Mass Update Batches and generate a mass update batch:

• Enter a Batch Name, Description, and Effective Date for the batch.
• In the Generate Detail Lines region of the window, enter the selection criteria to select the projects and tasks you want to update.
• Choose Generate Detail Lines to generate the mass update batch lines.
• Review and/or revise the mass update batch by choosing Details.
• When ready to execute the mass update, Submit the batch. The system submits a concurrent request, which completes the update.

The system will also update all related burdening and cross-charge schedules and take the new organizations into account after the change.

Enhancement Tip

Unfortunately, the Mass Update functionality is only available to update Project and Task Organizations at this point (11i). It would be great if additional attributes were available, including Start Date and Completion Date, Key Members, Project Classifications, Service Type, etc. Judging by the fact that the ‘Attribute’ field has a drop-down menu, that was probably an original intention, but it was never implemented.

Also, the batch control functions are currently not arranged in any logical order and were confusing to the users. Arranging the functions left-to-right, or right-to-left according to follow their logical work flow would make more sense.

New Features in Oracle HRMS Release 12

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remain at the sole discretion of Oracle. For more information, refer to the Metalink Note: 400061.1.

R12 will deliver a new user interface, which translates to a standard process flow, standard fonts, colors, and user-modifiable skins, and consistent navigation across all product families. R12 will also use Oracle’s 10g technology.

New User Experience

• iRecruitment - Completely re-designed Candidate Registration
• Absence Management - Updated business flows and entire look-and-feel
• Salary Administration - Vastly improved UX for Salary Information, including high-interactivity embedded analytics
• Competency Profile - Completely re-designed user experience

Oracle’s Top 5 Reasons to Upgrade to Release 12

• Supports HR Best Practices
• Reduces Administrative Costs
• Improves Productivity
• Key New Features/Functions
• Extended Maintenance & Support

The following are few selected new features that are planned for HCM in R12.

Checklists

Some HR actions, such as employee hiring, transfer, or termination, require the completion of standard tasks, such as printing contracts, reassigning resources, and creating users. The checklist functionality in Oracle HRMS enables you to link the HR action to a life event and generate ‘to do’ lists or checklists when the life event is initiated.

Global Deployment

This has been a long-awaited feature. Oracle HRMS Global Deployments function controls the temporary or permanent transfer of an employee to a different business group by means of a deployment proposal. In the case of secondments, which is a temporary transfer from the home business group to the host business group, at the end of which the employee will return to the Home business group, the employee in the home BG is not terminated and he/she will continue to be an employee.

Workforce Performance Management

Combines objective setting and appraisal processes into a manageable plan to track. Allowing consistency enforcement via corporate objectives and eligibility criteria, tracking objectives with copying and duplicating objectives for multiple appraisals during a period.

Salary Administration

New UI with the ability to delete, allows for gaps, end dating, retroactive and future changes in salary proposals.Display current grade and step on assignment with FTE on salary proposal. Viewing salary history of terminated employees.

Compensation Workbench

Manage budget awards across multiple currencies, business groups and compensation types in a single worksheet with a new task oriented interface with worksheet that can be personalized. Integration points between Oracle Incentive Compensation and Compensation Workbench are established so that target amounts, actual amounts, and commissions can be displayed on the worksheet. You now have the ability to change a manager’s location, organization, and supervisor within Compensation Workbench.

Sub-Ledger Accounting (SLA)

Sub-ledger accounting is an intermediate step between the sub-ledger product (e.g., Payroll) and the Oracle General Ledger. It allows multiple accounting representations for a single business event, resolving conflicts between corporate and local fiscal accounting requirements. It retains the most granular level of details in the Subledger accounting model, with different summarization options in the General Ledger, allowing full audit ability and reconciliation.

Applications Unlimited For Oracle E-Business Suite

Oracle’s plan to continue providing ongoing enhancements to current Oracle applications beyond the delivery of Oracle Fusion Applications

Learn to Love the Folders

I love the folders in Oracle Applications 11i. They are a great way to modify the forms without customizing them. It is too bad they are so poorly advertised or documented. As a result, only few users actually understand and use them. If you haven’t learned yet how to love the folders, this article is for you.

What is a Folder?

First of all, how do you even know the window you are using is a folder form? Folders are provided only with the core Oracle application forms. They allow you to change the form layout and content, define and run queries, export data to Excel without customizing the forms. Look for the yellow folder icon in the upper-left corner of the screen:

How to Define a Folder

When you open a folder form, the Folder option on the application top menu is enabled. You can use this option to configure the layout and settings of the folder. The Folder Tools option will give you a floating menu, from which you can execute some of the folder tasks. A default folder with default settings and seeded columns appears.

Creating a New Folder Version

To create a new folder, or update an existing one, use the options from the folder menu (not from the main applications menu. You can save a new folder as private, or public (other users can see it) version. Each public version must have a unique name! After you created your new folder, hide the columns you don’t need, show the ones you do, change column titles and size, and more. Hit Save As in the Folder Menu. The Save Folder options will be displayed. Here are few tips on how to set these options.

Autoquery

Make sure to select Never (unless you want to save your existing query - the data you currently see in the window). If you do save this query, make sure you reflect that in the folder name and don’t make this folder your default folder.

Open as Default

Do you want the current folder to be the folder that opens as default when you come back? If this flag is enabled, then this will be the default. It is a good idea to save the seeded folder first before your customized folder as default. Otherwise, you’d lose the seeded folder and can’t use it again.

Public

If enabled then the folder version can be used by other users with access to this form, not only the user who created the folder.

Customizing a Folder

You create or update versions of a folder by changing the following options as appropriate:

• What fields are displayed
• Size of fields
• Order fields are displayed

This can be done from the Folder menu or by using the Folder tools. When you are satisfied with the appearance of the folder, save the definition. Hide all the fields you do not need. This will dramatically improve the performance of the data queries and exports in your folder.

Defining a Folder Query

To update the query run by the folder, enter and run the required query and then save the folder. It is possible to view the new query by going to Folder > View Query. The query can be reset to the default by Folder > Reset Query.

For more complex queries it is possible to use a Clever Query. To do this:

• Put the folder in query mode
• Enter :a in any displayed field
• Run the query

This will display a query/where window. Enter the Where Clause you want to execute in the window. You can reference any field associated with the folder, not just those displayed. To check available fields use Help > Tools > Examine, then Block = FOLDER] and [Field = Use pick list to display available fields.

Folder Administration

Folders can be administered using the System Administrator responsibility. Navigate Application > Administer Folders > Find Default Folders.

From here you can determine which folders are used as default by users and responsibilities. When a folder is created and set as default, it is set for all responsibilities of that user. This can be changed so that different responsibilities, under the same user will invoke different default folders.

The custom folder definitions are referenced under a Folder Set. It is possible to change the owner of a folder and set folder options, such as Autoquery. This is very useful when you save as default a folder with a query, which now prevents you from selecting other data in the form.

Folder versions and details are stored in the tables FND_FOLDERS and FND_FOLDER_COLUMNS. see (7 TABLE DEFINITIONS). When a user invokes a folder, the user_name on the table FND_USERS is compared to the Created_by column in the FND_FOLDERS table. This will determine what folder versions are available.

Enhancement Tip:

It would be a great benefit to users if the applications came seeded with meaningful folders reflecting actual transaction workflows. The required columns should be displayed on the left (unlike in AP Payment Batches); the columns that do not make business sense together should not be displayed in the same folders (e.g. Revenue and Invoice Amounts for indirect projects). When exporting, it would be great to be able to export directly into Excel (instead of having to save the file first). Providing more logical choices to the users will encourage a wider use of this great application functionality.

Get More from Apps with More4Apps

I am excited to introduce John O’Keeffe, a marketing director from More4Apps, an Oracle Partner and system integrator based in Hamilton, New Zealand. John and I conducted and quick online chat about the More4Apps company and its solutions for Oracle Applications users. Read few excerpts from our conversation below.

John, thank you very much for joining us. If you agree, let’s get right to it.

Yes! Let’s get into it.

So tell me who is More4Apps? Can you give me a brief history of the company?

More4Apps was started by 6 Oracle consultants in 2000. We saw we were repeating many of the same integration tasks, and also how cumbersome the available tools were. We decided to provide shrink-wrapped interface tools that really streamlined the process. No more clunky, unfriendly interfaces based on SQL*Loader and various scripting languages. We started with Budget Wizard for Project Budgets and expanded to include AP, AR, Purchasing and Fixed Assets modules.

Are you providing consulting services also, or do you mostly focus on developing new products?

No, we concentrate on developing and supporting our software products. Our belief is that consulting organizations are in a good position to introduce our products and deliver a valuable solution to their clients. If we also delivered consulting services, that would confuse the market. There is so much more to do we don’t have time for consulting!

I see your list of customers includes companies from all over the world. Being based in NZ, how do you manage those customer relationships?

We are expanding rapidly and now have staff in the UK and the US. As for technical support we do have a good daytime overlap with USA and customers find it very helpful that issues logged today will be worked on overnight and fixed by their morning.

Yeah, there are benefits in being a day ahead.

Yes! We work Saturday morning to service US customers.

What are your most popular products?

Budget Wizard was our first, and is still our most popular product. As many organizations complete their budgets in Excel then re-key them into Project Accounting, Budget Wizard does a superb job of eliminating re-keying. AP Invoice wizard is becoming our most popular product. This is because of 2 things: 1) Oracle Payables has a larger customer base than Projects or AR etc., and 2) AP Invoice Wizard is ideal for automating the vendor invoice entry process.

Being an Oracle Partner you fill in a gap in Oracle’s solution offering. How do you respond to situations when Oracle suddenly becomes your competitor? I am thinking about Project Connect (for uploading projects, and project budgets), or WebADI for example.

Any ERP solution won’t be a 100% fit. Oracle does come out with new tools like Project Connect and that allows customers to have a choice. Also there are many gaps and priorities, so we see ourselves filling a gap that leaves Oracle to concentrate on their bigger picture.

What is the best way for new customers and users to get started with More4Apps?

New customers should visit our website. There are a few automated demos available to be viewed. That will give them a good understanding of what they can expect from More4Apps tools. Then they can contact us to arrange a webex demonstration. This demo will also act as a training session. They will then have enough information to download the trial version and get it working against their test system. Being Excel-based people find them pretty easy to pick up.

For what Oracle EBS releases can More4Apps products be used?

The versions of our tools on our website will run on all versions of Oracle Applications from R11i. For Applications versions 11.0.3 and earlier an ODBC version is required and you may request it. ODBC versions are not available for all tools.

Are you getting ready for Release 12 and Fusion?

Yes. Our technical team is in contact with Oracle and we will be ready with changes to our connection technology where required.

Are you planning to integrate with the packages acquired by Oracle – PeopleSoft, Siebel, and JD Edwards?

We have considered integration with Other ERP systems. But our core experience is Oracle and at the moment we are busy satisfying Oracle users’ requirements.

How about on the other side: MS Windows and MS Office. Are there any constraints as far as Windows and Excel versions you support at this point. I am mainly thinking about an impact of newly released Vista and Office on your customers…

Our tools run on all versions of Excel from 97. The operating system is not important. We will of course thoroughly test our software on Office 2007.

What new products are you working on?

We are currently building a Sales Order Wizard for Order Management and a Supplier Wizard is about to be released. Our new Fixed Asset tools and Bill of Materials Wizard were released in November and December 2006 respectively. There are many others in the pipeline. We are always interested in our clients’ requirements to help us drive new product development.

Perfect. Is there anything else you would like to talk about? Do you have any questions for me?
Not at this point. Thanks for your time.

Thank you! And good luck.