XML Publisher Report Using Data Template

Introduction:

Oracle publisher is a template based publishing solution delivered with oracle E-Business Suite. It provides a new approach to report design and publishing by integrated familiar desktop word processing tools with existing E-Business Suite data reporting.

XML Publisher provides the template manager for E-Business Suite user to register and maintain report templates and link them to data sources. Once both have been registered, simply run the report using standard request submission and XML Publisher will merge the template and its data source into customized report.

Steps to create XML Publisher Report

Step 1: Create the table and insert records in the table from which you want to print the data

CREATE TABLE APPS.EMP

  (

    EMPNO NUMBER(4) NOT NULL,

    ENAME VARCHAR2(10),

    JOB   VARCHAR2(9),

    MGR   NUMBER(4),

    HIREDATE DATE,

    SAL    NUMBER(7, 2),

    COMM   NUMBER(7, 2),

    DEPTNO NUMBER(2)

  );

INSERT INTO APPS.EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

INSERT INTO APPS.EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

INSERT INTO APPS.EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

INSERT INTO APPS.EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

INSERT INTO APPS.EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO APPS.EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

INSERT INTO APPS.EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

Step 2: Create the data definition template

<dataTemplate name="Purchase_Order" description="template description" version="1.1">

        <parameters>

               <parameter name="dept_no" datatype="number"/>

        </parameters>

        <dataQuery>

<sqlStatement name="a"><![CDATA[select empno,ename,job,mgr,hiredate,sal,comm,deptno    from emp where deptno=nvl(:dept_no,deptno)]]>

</sqlStatement>

          </dataQuery>

         <dataStructure>

               <group name="new" source="a">

                                <element name="Employee_NUMBER" value="empno"/>

                                <element name="EMPLOYEE_NAME" value="ename"/>

                                <element name="JOB" value="job"/>

                                <element name="MGR" value="mgr"/>

                                <element name="HIREDATE" value="hiredate"/>

                                <element name="SALARY" value="sal"/>

                                <element name="COMMISION" value="comm"/>

                                <element name="DEPT_NO" value="deptno"/>

                </group>

         </dataStructure>

</dataTemplate>

Save the file with name emp.xml

Step 3: Create data definition in xml publisher and attach the data definition template to the data definition

Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition

Step 4: Create Concurrent Program to generate the Data XML output. 

Navigation: System Administrator-> Concurrent-> Program->Define

Step 5: Assignment of concurrent program to responsibility

Navigation: System Administrator-> Security-> Responsibility->Request

Step 6: Execute the concurrent program

Navigation: Application Developer-> Concurrent-> Submit a New Request->Single Request

Output will come like this .click on page source and save the page as EMP_Data.xml

Step 7: Creation of RTF Template

             Prerequisite: You have to install XMLP Template Builder

 a) Open Microsoft-word file.

 b) Click on Add-Ins and then Load XML Data. Give the path of EMP_Data.xml file.

Save the file as EMP_Template.rtf

Note :The designing of the report we are doing in this section.

Step 8: Registering the RTF Template

Navigation: XML Publisher Administrator -> Template -> Create Template

Note: The output format of the report we can change in default output type section.It may be in excel,html, pdf, rtf.

Step 9: Repeat Step 4 again and see the output