Main Menu
| Home |
| Technical Articles |
| Training Articles |
| Receive Email for New Articles |
| Contributors |
| Apps Book |
Related Items
- Restart or Bounce Apache in Oracle Apps 11i
- Customization and Development Book
- XMLImporter in Oracle Applications Framework
- Read Only Schema in Oracle APPS 11i
- Technical Interview Questions in Oracle Apps
- A New Custom Form in Oracle Apps
- Install tools for Oracle Apps Development
- Concurrent Programs in Oracle Apps
- Concurrent manager in Oracle Apps
- Lookup Types and Lookup codes in Oracle Apps
- Audit Trail in Oracle Apps - EBS
- List of Business Events in 11.5.10
- Oracle Technical and Apps Functional Demo
- Training Seminar in Denmark
- Debug Forms in Oracle eBusiness Suite
- Factors effecting SQL Results in Oracle Apps
Home 
Technical Articles Miscellaneous Technology Customization of Reports in Oracle Apps
Technical Articles Miscellaneous Technology Customization of Reports in Oracle Apps 
Also see here fully verifiable feedbacks/testimonials
| Customization of Reports in Oracle Apps | | Print | |
| Written by Anil Passi | |
| Monday, 20 November 2006 | |
|
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 http://getappstraining.blogspot.com 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. Comments
(67)
 written by venkat , November 21, 2006 written by Anil Passi , November 21, 2006
Hi venky
please ignore that message oracle reports are backward compatible thanks written by venkat , November 21, 2006
I have copied ARXAGE.rdf from AR_TOP/reports/US
and opened it with reports6i. It gives me the following Warning: Opening a report saved with a newer version of Report Builder. Functionality may be lost. Do you want to continue? I clicked continue and opened it. So if I do any customizations will it be effective or get voided because i am using a lower version of reports builder. written by Anil Passi , November 21, 2006
Hi venky
please ignore that message oracle reports are backward compatible thanks written by ajay singh , November 26, 2006
good sites
written by ajay singh , November 26, 2006
good sites
written by Habeeb , November 29, 2006
Dear Anil
First of all I thank you a lot for doing a wonderful job which helps so many Oracle apps newbies like me. God Bless You. In the above reference can you please explain how to set up a Custom Application. Here I did To create a Custom Application.. 1. I Created a application through the Applications Forms 2. Then I registered my Application in the Standard data Group with APPS User in the Oracle Id Then I run the report which is put in my custom application But I get the error APP-FND-00362 I know i have to define my custom application path. But I dont know how to do it. Can You please explain how to create and set up a custom application written by Anil Passi , November 29, 2006
You need to make the Oracle Environment aware of the Custom Environment.
Please add the entry to $APPL_TOP/APPSORA.env Ideally your DBA should do this for you. Thanks, Anil Passi written by Habeeb , November 29, 2006
Dear Anil
First of all I thank you a lot for doing a wonderful job which helps so many Oracle apps newbies like me. God Bless You. In the above reference can you please explain how to set up a Custom Application. Here I did To create a Custom Application.. 1. I Created a application through the Applications Forms 2. Then I registered my Application in the Standard data Group with APPS User in the Oracle Id Then I run the report which is put in my custom application But I get the error APP-FND-00362 I know i have to define my custom application path. But I dont know how to do it. Can You please explain how to create and set up a custom application written by Anil Passi , November 29, 2006
You need to make the Oracle Environment aware of the Custom Environment.
Please add the entry to $APPL_TOP/APPSORA.env Ideally your DBA should do this for you. Thanks, Anil Passi written by shravan , December 08, 2006
hi,
would u plz tell me some standard reports customization free materials available in internet written by shravan , December 08, 2006
hi,
would u plz tell me some standard reports customization free materials available in internet written by Anil Passi , December 09, 2006
Hi all the oracle apps documentation is available free on below link
http://www.oracle.com/technology/documentation/applications.html written by Anil Passi , December 09, 2006
Hi all the oracle apps documentation is available free on below link
http://www.oracle.com/technology/documentation/applications.html written by Amitava Ghosh , December 29, 2006
Hi Anil,
This blog is really impressive. I really admire your intention of sharing knowledge. Can you tell me where I can get brief documentation of process flow in Oracle OM and Shipping Execution modules. It should be both technical and functional overview. Regards, Amitava Ghosh written by Amitava Ghosh , December 29, 2006
Hi Anil,
This blog is really impressive. I really admire your intention of sharing knowledge. Can you tell me where I can get brief documentation of process flow in Oracle OM and Shipping Execution modules. It should be both technical and functional overview. Regards, Amitava Ghosh written by Omkar , January 23, 2007
hi Anil,
Its is very useful info to me, lot of help which is in the Oracle Apps. Thank you. written by Omkar , January 23, 2007
hi Anil,
Its is very useful info to me, lot of help which is in the Oracle Apps. Thank you. written by Pardhu , February 08, 2007
Hi Anil,
I find this blog very useful.Thanks for taking time to give useful information in this blog. Could you let me know any GL report customization steps? Let me know the requirement for the customization and exact scenario from start till end of the GL report covering the functional and technical aspects. I would be grateful to you in this regard. Thanks, Pardhu written by ajay , February 21, 2007
good article
written by sankar , February 22, 2007
i am customizing ACCOUNT ANALYSIS(132 CHARACTERS) in Genral Ledger.They asked me
to add GL_DATE,VOUCHER_NUM FROM AP_INVOICES_ALL. CAN U GIVE ME HOW TO LINK AP_INVOICES_ALL AND GENERAL LEDGER.ACTUALLY I AM GIVING ALL LINKS BUT I AM NOT GETTING DATA FROM THE DATABASE.BUT I AM GETTING IN TOAD.WHEN I INTO REPORT BUILDER I AM NOT GETTING VOUCHER_NUM,GL_DATE THOSE COLUMNS ARE SHOWING NULL.WHAT IS THE WRONG. written by Anil Passi , March 12, 2007
Hi Kedar,
Yes, one must not blindly use the ScreenViews for Reporting purposes. A view on which scheen is based will featch as many columns and lookups as possible. Hence there will be various joins in that view which may not be needed for your actual report. Hence blindly using the views from screen, for bulk data reporting is not a good practice at all. Following events can happen:- 1. You implement a new system in Oracle Apps 2. You use screen views to develop reports 3. Given that you have a new implementation, there is not much data in the Oracle Apps Tables. 4. Due to low volume of data in tables, your reports run quickly. 5. As time progresses, data increases, and reports begin to give performance nightmare. I have seen this happen more than once. Hence yes, the best practice that you mentioned is indeed a good practice. Thanks Anil written by Praveen , March 28, 2007
Hello Anil,
Thanks For providing suach a greate information about oracle apps. is report customisation this much? Plz add more infor mation about report & forms customization with proper screenshots & examples. Thanks written by Anil Passi , March 31, 2007
Hi Praveen,
Of course the customizations are not restricted to just this much. Extensive set of customizations can be done, but those are covered within the toolsets themselves. This article was to help you understand how reports toolset integrates with Oracle Apps. Thanks, Anil written by Ashish , April 04, 2007
Kedar, where are you these days.
written by uma , April 04, 2007
Hi Anil,
can you tell me the steps involved in creating a poplist(not using LOV wizard) in apps. i need to display values from a table. Thanks for your help uma written by Anil Passi , May 11, 2007
Hi Ryan
This should be pretty straighforward. First identify the report group to which your column has been added. Then identify the repeating frame Within the repeating frame copy and paste and existing field and then alter the property of the pasted field[to make it reference your new column from report group] thanks anil written by anonymous , June 07, 2007
Thanks for all ur postings...excellent..hats off
please let me know to build a new report manually then steps to attach the report to concurrent program written by Aman Khan , June 08, 2007
Hi Anil,
How are you. Was very busy for a while and so couldnt visit ur website. I have a small question for you anil. What are user exits and whats the use of user-exits in oracle applications 11i reports. Also, what are different kinds of user-exits. Help Appreciated. THanks Aman Khan written by Anil Passi , June 10, 2007
Hey Aman
Hope you are well. User Exits are programs written in c/pro-c language. These days Oracle no longer creates new user exits, as java is available. Source code of the user exits is not available however you can go to the relevant bin directory and do strings -a filename this will show you the SQLs used by user exits. Thanks Anil written by Sam , June 20, 2007
Hi Anil,
I want to customize a standard report of PO and AP module Reports, my question is where should I place respective reports , isit in po_top/reports/us or custom top folder. do we need to have custom_top for all modules for which standard reports are being customized. Thanks Sam written by Anil Passi , June 20, 2007
Usually there is a individual custom top, and this is the ideal approach.
However, some DBAs setup a common top directory tree for all customizations. You need to see what applies to the environments built by your DBAs Thanks Anil Passi written by biranchi , June 22, 2007
hi
anil Plz add information about report & forms customization with proper screenshots & examples. regards, biranchi written by Anil Passi , June 22, 2007
Hi Biranchi
Please try to follow the steps, and if you get stuck, then feel free to raise your questions. Thanks, Anil Passi written by KALEEM , June 24, 2007
Hello Mr. Anil
Sir, I am beggener Oracle developer and need your help pls provide me some queries Oracle Apps 11i Order Mangement to build Reports. Thanks M.Kaleem written by Tony , June 25, 2007
Hi Anil,
Can you explain these little more clearly FND SRWINIT Օ FND SRWEXIT FND FORMAT_CURRENCY Օ FND FLEXIDVAL FND FLEXSQL Why it is used? when it is used etc Thanks Tony
Hello Anil,
Can you tell me please where exactly can I see SQL used by user exits? For example, I need it for modul of Assets.......... Tnx in advance!
am a fresher and having problems with oracle apps
i was asked to generate a reprt based on certain fields from apps using report builder i found out the table name and selected the columns from the back end. no, how to generate scripts for values sets and concurrent programs?? also, this table is not registered in frond end, so how do i know where to put this report in top directory??
Hi
The value set and concurrent program can be created from a responsibility named Application Developer This table should have a synonym in apps schema, hence no need to register this anywhere in front end for its usageg in reports. Also, your DBA's should tell you the directory for customized programs. if it is AP related, then probably $XXAP_TOP/reports/US [your dba shall provide you with specifics] This custom application is also attached to the executable of the concurrent program Thanks, Anil
Hi Anil
you done a greate job, there is no word appriciate to you. i am very new to the Oracle Apps i have one doubt, what is diff between noraml report and Oracle Report. why we need to register in Oracle Apps. what is the diff between after registring report in Oracle Apps Thanks Tirumala
There is no much difference except:-
1. Security of data For apps, reports need to be secured to a responsibility/menu Because, in apps, org data and some other data [say hrms] is restricted depending upon the responsibility the person has logged into. Some reports have a requirement to apply the same filters 2. Audit An audit is maintained automatically in apps, i.e. who ran the report, when and from which responsibility 3. Integration with XMLP [XML Publisher ] XMLP is integrated with concurrent managers in Oracle apps, letting you generate RTF/Excel/PDF outputs, and much more. Thanks, Anil Passi
Hi Anil,
Your site is quite informative. Thanks for your contribution. Could you please say me if we can have date parameter in calendar format while calling reports as a concurrent program in Oracle Apps. Thanks, Shrishti.
Hi Shrishti
I am afraid, you can't do that when it comes to using Value Sets. This might become a feature in latter versions of Oracle ERP Alternative is to submit your request from a custom screen that has fields for Date Entry and also a submit button. But such approach is not advisable. Thanks, Anil Passi
What is the Use of User Exit in Oracle apps and when we are developeing the new report you need to pass one parameter called P_CONC_REQUEST_ID,what is the use of this parameter and is it mandatory.Can't we develop report without this User exit's and P_CONC_REQUEST_ID parameter?
How you will Diaplay the Company LOGO In report.Can anyone knows please give solution?
Hi Krishnab
p_conc_request_id is passed to the report by concurrent manager. This is for informative purposes, in case you were to create some records in some table with the request_id srw UserExit sets the orgid amonst initializing other session variables to display logo, either use pre-printed stationary or use bitmap report or use XMLP Thanks Anil
Hi Anil,first of all thanks for sharing knowledge.
here a small doubt,plz clarify me I am opening the file using 'utl_file.fopen ()' and writing text data into that. There are no issues as far as writing data into file. We have some data that contains special chars (other than English). For example we have data like this "Prol López Mateos" When I am writing this data into text file, Its getting printed as "Prol López Mateos" Here the character ó is getting printed as ó. After some investigation found that it is happening because the ó is getting stored as multi byte character. The file opening is in ASCII format. When writing into the text file, the two byte character is being treated as 2 single byte ASCII characters. So it is printing two characters à and ³. The solution for this is opening file in 'UTF8' or any Unicode format. After searching oracle documentation and other resources we got following info. When we are trying to read or write data (contains multi bytes), we have to use 'utl_file.fopen_nchar ()' (available only on ORACLE-9i) instead of ordinary 'utl_file.fopen'. Similarly the data we are going to write into text file must be 'NVARCHAR2' or 'NCHAR' instead of 'varchar'. It is also said that, the oracle automatically opens file in Unicode format when we use 'utl_file.fopen_nchar'. I modified our code and incorporate all those changes (NVARCHAR2, fopen_nchar and put_line_nchar). But still the problem is same and the output file is in ANSI format. In documentation, it is mentioned that oracle automatically converts the file format based on the character data i am writing into the file. But it is not happening and output file is in ANSI format. I also tried another way. Before writing text into output file, I converted into Unicode string (by using UNISTR() function. But still the output files in ANSI format. I used another function CONVERT (string, source_char_set,dest_char_set). But the output file is still in 'ANSI' format. As per client suggestion, if I can open text file in 'UTF-8' format, the issue will be resolved. But how to open a file in 'UTF-8' format instead of 'ANSI' format? Is there any way to specify encoding scheme while opening the file? The default character set of database (9i) I am using is 'UTF-8'. As per Oracle suggestion I have tried using FND_FILES instead of UTF_FILE. But still it is opening in ANSI format. It's a very critical issue from my client. Any help will be much appreciated. Regards,
Hi Anil,first of all thanks for sharing knowledge.
my doubt is 1.Can't we develop report without this User exit's...thing is that right now i am facing interview i have some doubts .. 2. WHAT IS USE OF APPS API? what r they API's in AP, Po,Gl,om.. can u pls send me the details.. on this..
Hello,
I have submitted a report in apps as PDF. while i am going to see the output its giving me error " File is damaged and could not be recovered". Please tell me the solution of this problem.
Hi Aman
There could be varying reasons for this error, but most common cause is that there is not much space left on the hard disk. Is this report producing a huge amount of output? You can go to $APPLCSF/$APPLOUT ad FTP the file output manually to the PC and try to open. Also try a non-PDF concurrent program report to see if that works Thanks, Anil Passi
I anil it's a privilege to be a member of your forum, i m totatly new to apps,i use to be a d2k developer.Please Anil what do i do if a customer is unable to print an invoice and i want to see the sql statement generating the invoice or any query genrating the invoice, let us assume we are using toad or sql developer.please help me i m learning.
Hi Anil,
I am a frequent visitor to this site. It is a very good one and gives lots of Information. I have a small Problem. I need to modify the Standard AR Aging report to add one more paramter from the Accounting flexfield to run the report using the high and low ranges. We have in total 7 segments. Co and Account are already available in most of the standard Reports. I need to add Profitcenter which we use as . We have a valueset already set up. How and where should i modify the Report using SRW.USER_EXIT . Or is there any other way to modify the report. Any information provided on this will be helpful.
Does any one have any suggestions for the above problem. Thanks
Hi Anil,
can you help me, what are the steps of data conversion and I need to change the data form english to chanish language, for that what kind of api am i use please help me.
Hi Anil,
Your explanation is very clear but can you provide videos with some sample report and form customizations in financial modules or manufacturing modules.
Hi Anil,
I am developing a new report in using oracle report builder 6.0.8.8.3. When I run the repot in report builder the report is running fine. But when I run in Oracle Applications the report is ending up successfully but the the field size are compressed and I am getting only stars instead of values. Assume that for a field I have given 3 inch width horizontally, when I run in report builder the data is printed correctly and say it takes about 2 inches. But in apps it is getting printer in 5 lines since I have goven the vertical elasticity expand. Please help me out to sort the issue. Also kindly give some information regarding setting up the report builder system variables and layout size(points,inch,char) details while developing a new report in apps. Thanks, meensu
Hi Anil,
If I give mode asd CHARACTER in the system parameters then the output comes only as 80X66, in the report builder live previewer. I opent the dflt.prt file and saw that the definition says only 80X66. So I replaced with PD.prt which is the prt file of "BACS" style in apps, in the system param desformat and ran the report and still it prints only 80X66. Is there any workaroud to achieve the char mode report in apps which exceed the size of 180X66. Waiting for your valuable suggestions. Thanks, meensu
Hi Anil,
I have created a custom report in AR. Client want that report through Receivables -> Reports -> Accounting. Please guide me through the steps. Thanks, Nidhi
Hi Anil,
Thanks for the detailed explanation for custmosing report ,it seems I am the most dumb working becoz I was not able to open the report thru Report builder ,I get all sort of errors like 'Before report trigger failed' etc etc..can you please help me with it. Which documents will help me for custmosing the report... I'll explain in detail what my requirement is: 1.I have concurrent progm that send the Fax to supplier,issue is the PO number is too small to see 2.My requirement is to increase the font or atleast bold the PO number..Iam not sure wheather it is pssible or not.. Please help.. Thabnks in Advance AP
Dear Sir,
In my Previous company i had make lot of pl/sql excel report means concurrent prog. which output come in excel when i click on output button one IE window open and in that window my output come in excel. But now here at another client side i am not be able to open my output in excel my output open in IE as a text only., is there any setting at databas level or application level. for example i will show you how my code was working ============================================= Hint: ||''|| between the single quote there is tab not space which enable to put my data in excel next column CREATE OR REPLACE PROCEDURE TEST1(RETCODE OUT VARCHAR2, ERRBUF OUT VARCHAR2) as phead2 VARCHAR2(1000); phead222 VARCHAR2(1000); BEGIN phead22 := RPAD('---------------',22,' ')||''|| RPAD('------',10,' ') ||''|| RPAD('------------------',30,' ') ||''|| RPAD('--------------',20,' ')||''|| RPAD('------------------',70,' ')||''|| RPAD('------------------',30,' ')||''|| LPAD('--------------',20,' ')||''|| LPAD('--------------',20,' '); PHEAD2 := RPAD('PART ',22,' ')||''|| RPAD('UIT',10,' ') ||''|| RPAD('Description',30,' ') ||''|| RPAD('WH Manager',20,' ') ||''|| RPAD('Specification',70,' ')||''|| RPAD('Buyer',30,' ')||''|| LPAD('Balance',20,' ')||''|| LPAD('Value',20,' '); Fnd_File.put_line(Fnd_File.output,PHEAD22); Fnd_File.put_line(Fnd_File.output,PHEAD2); Fnd_File.put_line(Fnd_File.output,PHEAD22); END TEST1; ========= Thanks, Saurabh Thanks, Saurabh
Dear Anil,
Please Provide us the video information of each technical and functional articles.
Dear Anil,
Please Provide us the video information of each technical and functional articles. Siva Prabhu  written by Sreekanth munagala , February 01, 2008
Hai Anil,
First of all thanks for sharing yuor knowledge. When i run a report in Reports Builder,it is running fine.But when i register it in apps and run it,the output gets destroyed.The space occupied by fields in Reports Builder and oracle apps is not the same.why is it so?Are there any settings i need to change? I am using Reports Builder 6i version. For ex in reports builder i get like this empno empname 100 ABC where as in apps i get like this empno empname 100 ABC
Hey Anil,
Thnks for sharing ur knowledge. I'm very new to Apps. Can u give me some tips on table links between tables in AP modules? With Regards Shobhan
I would like to decrease the font size in oracle apps report output.
so that there wont be wrapping of lines.if any one has got experience on it. Please share it with me.
Hi
my rdf report output looks fine except for a blank line every 1000th line. but first to 1000th line is perfect and 1001th to 1999th line is perfect ..bt every 1000th line is blank. how can i get rid of these blank lines?  written by Darshini , March 08, 2008
Hi,
Is there any way to avoid printing the Standard Report(both parameter and report page) when there is no data retrieved by the report. The report displays "No data found". Thanks in advance You must be logged in to a comment. Please register if you do not have an account yet.
|
Subscribe to this comment's feed
and opened it with reports6i. It gives me the following
Warning: Opening a report saved with a newer version of Report Builder. Functionality may be lost. Do you want to continue?
I clicked continue and opened it. So if I do any customizations will it be effective or get voided because i am using a lower version of reports builder.