XML Publisher Report creation with DATA Template

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