18. How to create a simple XML Publisher Report

How to create a simple XML Publisher Report

Assumption:

Oracle Analytics Publisher Desktop 7.6.0 for 64 bit Office on Windows

Office 365 

Notepad++ v8.7.7

1. Login to Oracle EBS instance with operations privilege

2. Navigate to XML Publisher Administrator-> Home-> Template

3. Search of existing xml data defined template using name or application (e.g. Order Management)

3. Select xml file of choice and apply

4. Download the xml template by clicking on the file e.g. 

OEXOEACK_XML.xml

Note: will user the xml strature to developer our custom xml data defined template later

5. We will develop our sql query to get Employee Supervisors using sql developer IDE 

Connect as user apps to the ebs db.

We have user a select user_name from FND_USER table to get a test user name

Develop and test your sql statement

Make sure your sql is working right before copying it to your data define template xml file 

The sql statement is contained in the Q_cust_details tag 

Note from the OEXOEACK_XML.xml that we downloaded from the ebs instance above to our new    xxER_EMPLOYEE_TEMP.xml

6.  Navigate to Application Development->Profile->Application->Validation->Set and create a new value set name as “ER_EM_USER_NAME” 

Click Edit Information

Save and close

7. Switch responsibility to System Administrator

8. avigate to System Administrator->Concurrent->Program->Define

create a new concurrent program as below with Executable as "XDODTEX" and output as "XML".

a. Under Executable look for XDODTEXT

Note i have done some upgrade so the feature restart mode may not be available in your environment.

Then click on parameters button and enter the parameter as below

Search for Value Set created in previous step

Note Token is input parameter from our sql statement we developed in sql developer above

Display size should be minimum 60

9. Navigate to System Administrator->Security->Responsibility->Request,query request group as “System Administrator Reports” & application as “Application Object Library“(Its the request group attached to System Administrator responsibility).Then add the newly created concurrent program to it.

10. Navigate to XML Publisher Administrator->Home->Data Definitions, click on “Create Data Definition” button and create Data Definition as below.Data Definition Code should be same as the Concurrent Program Short name which we created.Then click on Apply button.

Note Code "ER_EM_HIERACHY_TEMP" is the short code of the concurent program we created in previous steps

Attach the Data DefinitionTemplate XML which we created in step 5.

11. Navigate to System Administrator->Concurrent->Requests and submit the newly created concurrent program.

Once the program is completed,click on View output button and save the output in XML format.

Save the file as xxER_EMPLOYEEDAT_TEMP.xml

12. Open MS Word->BI Publisher-> Sample XML->Select XML File. XML Data and load the output XML file which we saved in previous steps.(Ensure BI Publisher Desktop is installed) xxER_EMPLOYEEDAT_TEMP.xml

13. Then click on Oracle BI Publisher->Insert->Table Wizard

• Select report format as Table

• Since we are having only one group that defaults.If multiple group are there in your data definition select the group.

• Select all the fields and click finish button.(If sorting needs to be done,click next button instead of finish and do the settings).

Below is the report format.Save the document as RTF file(Rich Text Format)

14. Navigate to XML Publisher Administrator->Home->Templates and click on Create Template button.
Assign the Data Definition template which we created in step 4 to this template and set Output type as PDF. Also Set the type as RTF and assign the RTF file created in previous step.Create the Template as below and Apply.

10. Navigate to System Administrator->Concurrent->Requests and once again submit the newly created concurrent program.Now if you click the output we can see the report which we created.