Report Customization Oracle R12

Posted by & filed under .

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. 

  1. Create Report using Oracle Report Builder. For R12 use Oracle Report Builder 10G and for R11 use  Oracle Report Builder6i
  2.   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.

31 Responses to “Report Customization Oracle R12”

  1. Salman

    Faisal,

    Can you help us in giving step by step How to create Martix report without using wizards.

  2. faisal
  3. bensaid fadhel

    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?

  4. faisal

    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

  5. Daniesh Shaikh

    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

  6. faisal

    Hello Danish,

    When i try to connnect using database i am able to connect successfully to report builder but not with Apps user

    it seems OK. if you are able to connect database then you just need Apps pwd. Just connect using Userid: Apps Password: Apps .

  7. Akil

    Hi…

    I also need steps How to create Martix report without using wizards. may i send You my email on yours…??

  8. Luziah

    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.

  9. faisal

    First, sorry for delay response.

    Will you elaborate your question?

  10. sam

    Hi Faisal,
    can i know the process of oracle report(10g) Customization process end to end and BI publisher..

    Thanks
    Sam

  11. faisal

    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.

  12. Rajesh Emani

    Can you tell me what are the customizations possible for any report in real time scenarios…

  13. Rajesh Emani

    It will be helpful for me if u send me some rdf files and the possible customizations for that reports…for the interview purpose…

  14. faisal

    You may need to change only parameter, Add/Remove column from seeded report or develop new customized report based on user requirement.

  15. faisal

    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.

  16. razor

    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.

  17. faisal

    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?

  18. razor

    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.

  19. razor

    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.

  20. faisal

    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.

  21. razor

    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.

  22. faisal

    Hi, you can call procedure and pass token values to procedure parameters.

  23. razor

    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;

  24. faisal

    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.

  25. razor

    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

  26. faisal

    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

  27. razor

    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.

  28. razor

    Good day faisal, any updates on my inquiry thanks…

  29. faisal

    Seems every thing OK, Send me log of concurrent.

  30. razor

    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.

  31. Gagat Rahina

    hi faizal..
    How i should do if i will register path file for report customizations

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>