Learning SOA: Report Creation in Oracle EBS using XML publisher

In this exercise we will see a full cycle on how to create a report in Oracle EBS. This will include creation of the tables and fetching the data from table to display the output in pdf format.

Install BI Publisher which will install the plugins in words document in Add-Ins tab.
Go to apps database back end and create a table.

create table employee (name varchar2(30),Employeeid varchar2(20),salary integer, designation varchar2(30),location varchar2(40),department varchar2(40))

Insert some date in to the table.

Now create a data template for the record to retrieve all the data from the database.

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXAMW_TEST_SAMPLE" version="1.0">
<dataQuery>
<sqlStatement name="Q_HEADER">
<![CDATA[select name,employeeId,salary,designation,location,department from employee]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_HEADER" source="Q_HEADER">
<element name="Name" value="name" />
<element name="EmmployeeID" value="employeeId" />
<element name="Salary" value="salary" />
<element name="Designation" value="designation" />
<element name="Location" value="location" />
<element name="Department" value="department" />
</group>
</dataStructure>
</dataTemplate>

Now log in to oracle apps
Go to XML publisher responsibility

Double click on Data Definition
Create a data definition

Application Name=XXX Custom (Created by admin or Functional people)
Specify the name, code and application name

Apply and attach the Template in the next page

Apply the changes.
Define a concurrent program

Go to System Administer responsibility-->Concurrent -->Program-->Define

Executable-XDODTEXE
XXX Custom=Application name
Save the page.

Go to Security-Responsibility-Request
F11
Group- OM Concurrent Programs
Crtl+F11
Will populate the records
Click on one of the program and say new

In order to verify at the back end whether the concurrent program is created or not.
Go to back end database and search for following
select * from fnd_concurrent_programs_VL where concurrent_program_name='XXX_AR'
you will get a record for the same

Switch responsibility to Order Management Super User
Say View request –Submit a new request

Complete and view the output, you will find the sql data retrieved in xml format

Here G_Header in the group name from the data template.
Now in order to create a pdf from this
Save this XML file in your desktop.
It got saved as XXX_AR_240513.xml for me by default
Open a new word document
Go to Add-Inns tab
Click on data and load XML data

Go to insert tab and insert a table with 6 columns and 2 rows as shown

Save this document as .rtf in your local machine
Now go to each row and insert the corresponding field
And save the form now you can actually see the pdf by going to preview button
It will show the first record
Back to

It will not show all record as we have not used for each.
Now in order to see all the columns we will add a for each condition in the starting column “Employee Name” and will end it on the last column “Employee Designation”
Go back to you rtf document.
Copy paste the same name component and double click on it.

and change it to following

If you will observer G_HEADER is the repeating record in the XML document we have created. Also name it to a logical name.
Similarly go to the last column and end the for each loop

So once done your final rtf document will look something like this.

Go to RTF document –Add-Inns and view pdf
Now you should be able to see all the records in the PDF format which means the template is correct one.

Now once we have got the demo pdf we will go and register this rtf document in the EBS and run a concurrent program to see if the output is successfully generated in EBS or not.
Switch to XML Publisher responsibility and double click on Templates