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.
Apps2Fusion Articles 
Comments
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.
please ignore that message
orac le reports are backward compatible
tha nks
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.
please ignore that message
orac le reports are backward compatible
tha nks
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.
H ere 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
Bu t 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
Please add the entry to $APPL_TOP/APPSO RA.env
Ideal ly your DBA should do this for you.
Thanks,
Anil Passi
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.
H ere 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
Bu t 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
Please add the entry to $APPL_TOP/APPSO RA.env
Ideal ly your DBA should do this for you.
Thanks,
Anil Passi
would u plz tell me some standard reports customization free materials available in internet
would u plz tell me some standard reports customization free materials available in internet
http://www.oracle.com/technology/documentation/applications.html
http://www.oracle.com/technology/documentation/applications.html
This blog is really impressive. I really admire your intention of sharing knowledge.
C an 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.
Re gards,
Amita va Ghosh
This blog is really impressive. I really admire your intention of sharing knowledge.
C an 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.
Re gards,
Amita va Ghosh
Its is very useful info to me, lot of help which is in the Oracle Apps.
Thank you.
Its is very useful info to me, lot of help which is in the Oracle Apps.
Thank you.
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.
Than ks,
Pardhu
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.
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.
Followi ng 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.
Th anks
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.
Th anks
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
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
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]
thank s
anil
please let me know to build a new report manually then steps to attach the report to concurrent program
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
Hope you are well.
User Exits are programs written in c/pro-c language.
Thes e days Oracle no longer creates new user exits, as java is available.
S ource code of the user exits is not available
howe ver you can go to the relevant bin directory and do strings -a filename
thi s will show you the SQLs used by user exits.
Thank s
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
Ho wever, 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
anil
Plz add information about report & forms customization with proper screenshots & examples.
re gards,
biranch i
Ple ase try to follow the steps, and if you get stuck, then feel free to raise your questions.
T hanks,
Anil Passi
Sir, I am beggener Oracle developer and need your help pls provide me some queries Oracle Apps 11i Order Mangement to build Reports.
Tha nks
M.Kaleem
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
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!
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??
als o, this table is not registered in frond end, so how do i know where to put this report in top directory??
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/repor ts/US [your dba shall provide you with specifics]
Thi s custom application is also attached to the executable of the concurrent program
Thanks,
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
Ti rumala
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
Your site is quite informative. Thanks for your contribution.
C ould 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.
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
Alternativ e 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.
Tha nks,
Anil Passi
p_con c_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
An il
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 ó.
Afte r 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,
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..
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.
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/$APPLO UT 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 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.
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.
Your explanation is very clear but can you provide videos with some sample report and form customizations in financial modules or manufacturing modules.
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,inc h,char) details while developing a new report in apps.
Thanks,
meensu
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.
T hanks,
meensu
I have created a custom report in AR. Client want that report through Receivables -> Reports -> Accounting.
Ple ase guide me through the steps.
Thanks,
Nidhi
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
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('------',1 0,' ') ||''||
RPAD('--------- ---------',30,' ') ||''||
RPAD('--------- -----',20,' ')||''||
RPAD('--------- ---------',70,' ')||''||
RPAD('--------- ---------',30,' ')||''||
LPAD('--------- -----',20,' ')||''||
LPAD('--------- -----',20,' ');
PHEAD2 := RPAD('PART ',22,' ')||''||
RPAD('UIT',10,' ') ||''||
RPAD('Descripti on',30,' ') ||''||
RPAD('WH Manager',20,' ') ||''||
RPAD('Specifica tion',70,' ')||''||
RPAD('Buyer',30 ,' ')||''||
LPAD('Balance', 20,' ')||''||
LPAD('Value',20 ,' ');
Fnd_File.put_li ne(Fnd_File.out put,PHEAD22);
Fnd_File.put_li ne(Fnd_File.out put,PHEAD2);
Fnd_File.put_li ne(Fnd_File.out put,PHEAD22);
END TEST1;
======== =
Thanks,
Saur abh
Thanks,
Sa urabh
Please Provide us the video information of each technical and functional articles.
Please Provide us the video information of each technical and functional articles.
Siva Prabhu
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
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
so that there wont be wrapping of lines.if any one has got experience on it.
Please share it with me.
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?
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
Can anyone guide me for generating apps report (using report 6i ) in Indian Languages.
Tha nks in advance.
Regar ds,
Kamlesh
Please I am looking for Help...
I am custmizing standerd report...That is "All Inventories Value Report"
issues are....
1. i need to add Account fileld.
2. Roll back value.
3.roll back date
When i will try to add account flex filed .report out put is not commming..
last two weeks I am unable to solve this issue...
if possible please solve my issue or give me a solution...
Is there is any front end report to find out all Oracle application custom reports for all responsibility ?
I have created one report and it wll print the output automatically once the concurrent programs runs,but the case is for some customer's there is no data ,so the concurrent program will not create an output for such cases,even though its printing an empty page.Please do the needful and reply me as soon as possible
I have created one report and it wll print the output automatically once the concurrent programs runs,but the case is for some customer's there is no data ,so the concurrent program will not create an output for such cases,even though its printing an empty page.Please do the needful and reply me as soon as possible Please help me to resolve this .I am waiting for ur reply.
Sir, I am beginner Oracle developer and need your help pls provide me some queries Oracle Apps 11i/R12 Order Mangement to build Reports.
Thank s
Please check http://oracleprasan.blogspot.com/2010/11/query-to-find-responsibility-from.html
You need to change this query slighlty as per your requirement.
R egards,
Sandip Jadhav
I am using 10g to build the Reports for my Oracle apps instance R12.1.3.
I am getting strange issues while running the reports.
After opening the Report Builder,
1. Opening the existing report.
2. Connecting to an instance
3. Compiling the report.
4. Running the paper layout
5. Entering the parameter values and then clicking on run
6. But it shows black output page.
After this closing the blank output page, then re-running the report then it shows the correct output.
Could you please tell me what causing the problem in 1st run?
Thanks,
Santhosh
RSS feed for comments to this post