Customization of Reports in Oracle Apps

Customization of Reports in Oracle Apps

Oracle Reports will become a thing of past in Fusion, however it will still demand resources for the next 5yrs or so.

Hence I decided to write this article to fulfill my commitment towards

We learnt in Forms Customization article that customized executables must be registered with Custom Application. This rule applies to Oracle Reports too in Oracle Applications.

Important note: Just like Oracle Forms, there is no place for D2K Reports in Fusion. This will be replaced by XML publisher. I will explain that transition in a latter article very soon.

Question: I have been asked to customize Invoice Print program which happens to be an Oracle Report. What will be the steps, that I must follow.
Answer : Follow the steps below.
1. You will be told the name of the existing report that must be customized. Note down the exact name and query that name in Concurrent Program screen. Click on “Copy Program button” selecting checkbox option “Copy Parameters”. This will help you copy the current program definition to custom version of the program.
Also note down the name of the executable as it appears in concurrent program definition screen.

2. In same responsibility i.e. Application Developer, navigate to screen concurrent executable and query on the field labeled "Executable Short Name".
Note down the application within which it is registered. If the application is Oracle Receivables, then you must go to the database server and get hold the file named RAXINV.rdf in $AR_TOP/reports/US.

3. Copy that file to your custom AR Top directory. Basically that is the directory where custom reports for AR will be deployed..
cd $XXAR_TOP/reports/us
cp $AR_TOP/reports/us/RAXINV.rdf $XXAR_TOP/reports/us

Effectively you have now done the following:-1. Made the custom version of report registered with XXAR application. If you worked for say company named EA, then this might have been $EAAR_TOP/reports/US
2. When you run that report, Oracle concurrent manager will search for that report in $XXAR_TOP/reports/US
The report will be found there, and executed.

Note: We haven’t made any changes as yet. Also, you need to include the new concurrent program name in the relevant request group.

Now you can ftp that report to your pc, make modifications for necessary customizations, and then ftp that piece of rdf back to the server. Run it again, to see it working.


Some important tips:-
1. Avoid writing SQL in format trigger, although in rare cases it becomes necessary to do so.
2. Learn from Oracle's Report, by reverse engineering them.
3. Do not write a formula column for something which can be achieved by amending the query in data group itself.
4. Do not hardcode things like Currency Formatting. Have a look at Oracle's Amount fields, and use the same user exit.
5. srw2.message can be used for minor debugging, as those messages will appear in the log file of the concurrent program.
6. You can set the trace checkbox against the concurrent program definition, to generate SQL Trace. This trace will not produce bind variable values though.
7. Join between two queries in data group will always be outerjoined, by default.
8. Avoid filters on Data Group queries. Try to implement that logic within the query itself.