Report Customization Oracle R12
In this article I will explain that How to Develop/Customize a Report in Oracle EBS R12 using Oracle Report Builder 10.1.2.0.2.
One thing I want to clear here that I have read on different website that Oracle Developer/Report Builder will no more in future. But I think it’s not true. I been using different Report Builder tools XML/BI Publisher, Oracle Report Builder, COGNOS, Crystal Reports since last 10 years, I found Oracle Report Builder is a very powerful tool specially for Oracle Applications.
- Create Report using Oracle Report Builder. For R12 use Oracle Report Builder 10G and for R11 use Oracle Report Builder6i
- I have developed Employee report based with department number parameter. Parameter name is P_Deptno. Note that Oracle EBS also has Scott Schema by default. You can query “select * from scott.emp”
3. Save the RDF file in local PC say “XX_TEST_EMP” and make sure there is not any compilation error. For compile Press Ctrl+Shift+K.
4. Then copy the “rdf” file to concern Responsibility path. e.g. for Purchasing report copy it $PO_TOP/reports/US.
5. Then Navigate to Application Developer Responsibility concurrent à Executable and fill fields as displayed.
6. Copy the Executable name “XX_TEST_EMP” and navigate to Concurrent à Program and fill the fields as displayed and save.
7. For Parameter Click the Parameters Button and fill the fields as displayed.
8. The assign the Request group. You should have access on Request Group responsibility. Normally it is under System Administrator Responsibility.
9 . If you don’t have access in of System Administrator you can use FND_PROGRAM.ADD_TO_GROUP
DECLARE
BEGIN
FND_PROGRAM.ADD_TO_GROUP
(
PROGRAM_SHORT_NAME =>’XX_TEST_EMP’
,PROGRAM_APPLICATION => ‘PO’
, REQUEST_GROUP => ‘Purchasing Reports’
, GROUP_APPLICATION => ‘PO’
);
COMMIT;
EXCEPTION
When Others then
Dbms_output.put_line(‘Object already exist’);
End;
/
10. Now your Report is ready to Run. Just navigate to Purchasing responsibility and Run the report
This article is for those who are looking answer of following Questions:
1. How to Develop a Report in Oracle Report Developer.
2. How to Customize Report in Oracle EBS R12/R11.
3. How to attach report in Oracle EBS. (Purchasing Module).
4. How to add new paremerts in Oracle R12 Reports.
Faisal,
Can you help us in giving step by step How to create Martix report without using wizards.
HI Salman,
Send me your Email ID orver faisal@oracleport.com . i will send you steps.
hi,
could you please help me, i al trying to open E-business suite r12 reports with oracle reports developper 10.1.2.0.2 but it is not working, unable to open
do you have an idea?
Hi Bensaid,
Mak sure, that you are opening rdf file. Repot builder version is right Report Builder 10.1.2.0.2 for R12 reports. Send me screen shots of error or message.
Faisal
Hi Faisal,
I have installed Oracle developer suite 10.1.2 on windows. My database and application is on linux OEL5.
When i try to connnect using database i am able to connect successfully to report builder but not with Apps user.
Could you please help me in integrating report/forms server with EBS R12 so that i can connect using APPS user and able to create a report.
This is the first i am working with developer.
Please help.
Thanks and Regards,
Daniesh
Hello Danish,
it seems OK. if you are able to connect database then you just need Apps pwd. Just connect using Userid: Apps Password: Apps .
Hi…
I also need steps How to create Martix report without using wizards. may i send You my email on yours…??
hi? run into your blog, thing is the technical team created a Position Listing Report for me (I’m focused on the functional side of the system), thing is some of the subsections on the hierarchy are not being printed on the report.
First, sorry for delay response.
Will you elaborate your question?
Hi Faisal,
can i know the process of oracle report(10g) Customization process end to end and BI publisher..
Thanks
Sam
1)First Generate File as XML.
In Report builder File–>Generate to File–> XML
2) Import XMP in MS word, Design RTF and View output.
its very easy but lengthy procedure, once you go through it, you’ll be in ease.
if you need more help, I ll send you screen shots or post on oracleport.
Can you tell me what are the customizations possible for any report in real time scenarios…
It will be helpful for me if u send me some rdf files and the possible customizations for that reports…for the interview purpose…
You may need to change only parameter, Add/Remove column from seeded report or develop new customized report based on user requirement.
Its very difficult to explain for interview questions. But i can share some customized report. Better you go for 2/3 days training regarding subject.
hi faisal
need help, unable to open existing rdf file using report builder oracle report developer 10g. In the past it works properly but now nothing happens, it seems not responding when i’m opening an existing rdf file. thanks in advance.
What is the Old rdf version? Reports build in 6, 6i, 9g versions are compatible with 10G.
Which operating system are you using, 32 or 64 bit?
Good day and thanks faisal just saw your reply, i’m new in report builder how can i know what version the rdf is built in. I think it was already built in 10g because the desktop unit that i’m using has a pre installed oracle suite 10g and the report was built only recent. i’ve modify that report already but now it wont open and the rest of other rdf files. I’m using a 32 bit operating system. thanks for the response.
Hi faisal it was my mistake it really opened. The problem is that the window after i have selected rdf file and hit open nothing happens because the window is in minimized mode with size of the icon in the desktop i noticed that there was a small like icon highlighted and when i resized it, it was the report very truly sorry faisal just user error. Thanks any way can you send me screen shot process of oracle report(10g) Customization process end to end and BI publisher. Sorry for the lengthy post, another question how can i call a function in the rtf file of bi publisher created in toad using oracle sql because i have been customizing a purchase standard report for a new layout and i want to include the route for the approvers wich is the function itself i called get approvers so it will appear in the report. thanks again.
Nice to know that you have done it. As for as i know you cant call pl-sql function in rtf file further you never say impossible in IT word.
you need to customize report, Customization is very easy if you have basic knowledge of report builder and follow the steps as mentioned above.
Thanks.
Thanks for the quick response, i have another inquiry hope its not too much. I have 4 customized reports budget funds flow which means 4 rdf files they are all related related. Each report will output the designated quarter namely for (1st Quarter, 2nd Quarter, 3rd Quarter and 4th Quarter) so all in all 4 rdf files. The said reports are already defined in oracle concurrent program and has a only 2 parameters which are the reference no. & the quarter or period.. What i want is to create a stored procedure in oracle so that i would run only 1 concurrent request and the parameter would be the Refefernce No. and the Quarters. If i choose 1st Quarter it will output only the 1st Qtr and so on. My problem is the token for the defined stored procedure in the concurrent program is disabled. Any thoughts on this thanks faisal.
Hi, you can call procedure and pass token values to procedure parameters.
This is my stored proc, i’ve just remove the null arguments for your viewing purpose. Can you help me if there is something wrong with stored proc thanks:
CREATE OR REPLACE PROCEDURE APPS.process_budget_ff (errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_ff_number IN VARCHAR2
,p_ff_qtr IN VARCHAR2) IS
nReqId1 NUMBER := 0;
err_exception EXCEPTION;
BEGIN
IF p_ff_qtr = ‘First’ then
nReqId1 := Fnd_Request.submit_request (application => ‘XXPPA’
,program => ‘XXPPA_FF_Proj_FQtr’
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => p_ff_number
,argument2 => p_ff_qtr);
IF nReqId1 = 0 THEN
RAISE err_exception;
END IF;
elsif p_ff_qtr = ‘Second’ then
nReqId1 := Fnd_Request.submit_request (application => ‘XXPPA’
,program => ‘XXPPA_FF_Proj_SQtr’
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => p_ff_number
,argument2 => p_ff_qtr);
IF nReqId1 = 0 THEN
RAISE err_exception;
END IF;
elsif p_ff_qtr = ‘Third’ then
nReqId1 := Fnd_Request.submit_request (application => ‘XXPPA’
,program => ‘XXPPA_FF_Proj_TQtr’
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => p_ff_number
,argument2 => p_ff_qtr);
IF nReqId1 = 0 THEN
RAISE err_exception;
END IF;
elsif p_ff_qtr = ‘Fourth’ then
nReqId1 := Fnd_Request.submit_request (application => ‘XXPPA’
,program => ‘XXPPA_FF_Proj_FoQtr’
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => p_ff_number
,argument2 => p_ff_qtr);
IF nReqId1 = 0 THEN
RAISE err_exception;
END IF;
END IF;
EXCEPTION
WHEN err_exception THEN
DBMS_OUTPUT.PUT_LINE (‘ERROR EH!!!!!’);
END;
Make sure you have selected “PL/SQL Stored Procedure” in executable of concurrent, hence token field be disabled and follow the sequence of parameters as per stored procedure.
Yes i have selected “PL/SQL Stored Procedure” in executable of concurrent. is it alright that the token field be empty as it shows in my parameter and the field is protected from editing. when submit the request for generating the report, there is no output but the log is successfully executed. Am i missing something? thanks
Have you added four parameters and same sequence in concurrent program as per your quoted following code
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_ff_number IN VARCHAR2
,p_ff_qtr IN VARCHAR2
Sorry for the late reply, in the concurrent program:
Seq. Parameter Prompt
10 Funds Flow Reference No. Funds Flow Reference No.
20 Period (Quarter) Period (Quarter)
Token Field: (disabled and empty)
* the p_ff_number is the Funds Flow Reference No
and the p_ff_qtr is the Period (Quarter)
thanks faisal for being patient with me.
Good day faisal, any updates on my inquiry thanks…
Seems every thing OK, Send me log of concurrent.
Thanks faisal solved already, the problem is in the parameter entry in the concurrent request. It runs perfectly now thanks again for your help ok.
hi faizal..
How i should do if i will register path file for report customizations
Hi,,
When I build a report in builder 10g and attached to oracle Apps the arabic worlds comming as unknown world
Can you please help me in getting correct arabic letters
Please set NLS_lang parameter for database server. Further reg: https://www.oracleport.com/enable-arabic-for-reports/
hi faisal, i am currently working on a customized AR invoice report. i am trying to add fields on the oracle standard report (RAXINV_NEW). i was able to have a copy of its rdf file and transformed it into xml file. but upon checking it, it was quite complicated to modify. can you give me an advice regarding customization? am i doing the wrong approach on it? please, your help would be greatly appreciated.
Hi mike,
Your approach is right but yes you need extra ordinary expertise to customize standard reports.
Good luck.
Hi Faisal,
I have 50 reports, query built in RDF reports and designed in RTF files and working fine in Oracle EBS R11, I am moving all custom forms and report from EBS R11 to stand alone weblogic-forms and reports, and I want to import the designs from the RTF files into the RDF without rebuilding the RDF all over again especially that the RTF design is complicated, how can I do that ?
Regards,
good all context