by Vishal Kumar – Lead Consultant
The Oracle Applications ERP is continuously evolving with the use of new technologies. Trends are shifting from traditional form-based applications to self-service web-based applications using the Oracle Applications Framework (OAF). As a result of the current demand, every application requires easy integration of reports and reporting tools.
In the Oracle EBS world, it’s a common requirement for users to want to generate reports in PDF, MS Word, MS Excel, and HTML format from an OAF web page without submitting any concurrent program.
Below are the high-level steps for generating reports in PDF or other desired report output formats.
1. XML Data Definition, registered with APPS. Template, registered with APPS.
2. XML Publisher APIs to process the template and generate the output in the required format.
In this blog, we will explain the integration steps of OA Framework and XML Publisher with examples. Prior basic knowledge and understanding of OA Framework and XML Publisher is assumed.
Overview of XML Publisher
XML Publisher, which is also called Oracle Business Intelligence Publisher (BI Publisher), is a template-based reporting tool that leverages standard technologies for data extraction and display. Business users can build the layout and rules for the report themselves by using common desktop tools. Development engineers can concentrate on extracting data in the most efficient manner. Deployment time is faster. Also, business users with minimal training can design templates and reports based on the data sources provided by engineers, thus greatly reducing the cost of ownership.
Designing OAF Page and Generating XML Data
Consider we are trying to print PO information on the Report output. Usually, this kind of report would be critical for business users, and would typically be generated as a PDF file directly from the OAF Web Page. The following steps discuss how to design an OAF Page and generate XML data to complete the above requirements.
1. Open JDeveloper and create a new project.
2. Create a custom VO for our custom PO Information Page with the below query.
SELECT POH.SEGMENT1 PO_NUMBER, POH.TYPE_LOOKUP_CODE PO_TYPE, (SELECT APS.VENDOR_NAME FROM AP_SUPPLIERS APS WHERE 1=1 AND APS.VENDOR_ID = POH.VENDOR_ID) SUPPLIER_NAME,
(SELECT FU.USER_NAME FROM FND_USER FU WHERE FU.USER_ID= POH.CREATED_BY)CREATED_BY,
(SELECT FU.USER_NAME FROM FND_USER FU WHERE FU.USER_ID= POH.LAST_UPDATED_BY)LAST_UPDATED_BY
3. Create an Application Module (AM) and select the previously created VO, then move it to the right.
4. Create a new OA Page from the project –> Create a new OA Component.
5. Go to the structure and layout of the page, then set the AM and provide the window Title.
|AM Definition||xxcen.oracle.apps.fnd.xmlPublisherReport.server. XxcenPoHeadersAM|
|Window Title||PO Headers|
6. Right-click on the ‘pageLayoutRN‘ and then set the controller class for this page.
7. Now open the AMImpl.java file and write the below code.
8. On the controller class, you can write the below code in the processRequest() method to initialize the query execution (VO) and reveal the data on the page load.
9. Execute the page to print XML data on the console log. Save this XML file for further use in the development of the template.
Designing an RTF Template
With the XML file saved in the previous step, create an RTF template and register it under your desired application.
Invoke the Report from the OAF Page
After completing all previous steps, the final step is to invoke the report from the OAF web page.
1. We need to create a Print button on the OAF table so that when the user clicks on the button it prints the necessary information into a PDF File.
2. Write the below code in AMImpl.java to execute the VO query and return the XML Node Data.
3. Create a submit button labeled “Export to PDF” under the VO Table actions.
4. Write the below code in the processFormRequest() method of the page controller to invoke this report upon button selection on the webpage.
5. Slightly modify the processRequest() method in the page controller to show the data automatically when the page is loaded.
6. Now that the logic/code is complete, we are ready to test the page. Proceed by executing the page, where you should see the “Export to PDF” button previously created. Ensure that when you select the button, a PDF file is generated with complete details from the desired table.
Report Data Output:
The purpose of this blog is to provide easy-to-follow steps and examples so that one can better understand the process of integrating XML Publisher Reports and Oracle EBS ERP through the OAF web page in order to extract data in the most efficient manner.
- Creating Reports in Oracle E-Business Suite Using XML Publisher
- Steps to Develop a custom PO Output Template
If you have any further questions, you can contact the author directly or contact our office.
Vishal Kumar – Lead Consultant
LinkedIn – www.linkedin.com/in/vishalsomisetty/