Creation of XML Report | XML Report | XML Publisher Report |XML P Report | BI Publisher | Oracle Apps R12 | Oracle EBS | EBS

                                                                  XML REPORT

An XML report definition typically refers to a structured document that outlines the layout, structure, and content of a report using XML (extensible Markup Language). XML is a popular format for defining such structures due to its flexibility and ease of parsing.

The specifics of an XML report definition can vary widely based on the reporting system or tool being used. Different systems might have their own XML schemas or structures to define reports.

In a generic sense, an XML report definition might include elements to describe:

1. Report Metadata: Information about the report, such as its title, author, description, creation date, etc.

2. Report Layout: Defines the visual layout of the report, specifying sections, headers, footers, columns, rows, and other formatting details.

3. Data Source Information: Specifies the source of data for the report, including database connections, queries, or data files.

4. Parameters and Filters: Describes any parameters or filters that can be applied to the report, allowing users to customize the data displayed.

5. Formatting and Styling: Contains information regarding styles, fonts, colors, and other formatting options for different report elements.

Steps creation XML report

          1. First we need to understand the requirement and build the SQL query.

SELECT

               E.EMPNO,

               E.ENAME,

               E.DEPTNO,

               E.JOB,         

               D.DEPTNO,

               D.DNAME, 

               D.LOC

FROM DEPT D,

              EMP E

WHERE D.DEPTNO=E.DEPTNO;

2. Next create data definition or modify existing data definition.

<!--   $Header: XXSDS_EMP_DETAILS_DD.XML  $   -->

<!--   GSCC_Category="Xdo Data Definition"   -->

<dataTemplate name="XXSDS_EMP_DETAILS_DD" version="1.0">

<properties>

<property name="xml_tag_case" value="upper"/>

</properties>

<parameters> </parameters>

<dataQuery>

<sqlStatement name ="Emp_Details">

<![CDATA[

         SELECT E.EMPNO,

                E.ENAME,

                E.DEPTNO

                E.JOB,

                D.DEPTNO,

                D.DNAME,

                D.LOC

                FROM DEPT D,

                     EMP E

                WHERE D.DEPTNO=E.DEPTNO

]]>

</sqlStatement>

</dataQuery>

<dataStructure>

<group name ="G_Q1" dataType="varchar2" source="Emp_Details">

<element name="DEPTNO" dataType ="number" value="DEPTNO"/>

<element name ="DNAME" dataType ="varchar2" value ="DNAME"/>

<element name ="LOC" dataType ="varchar2" value ="LOC"/>

<group name ="G_Q2" dataType ="varchar2" source ="EMP_Details">

<element name ="ENAME" dataType ="varchar2" value ="ENAME"/>

<element name ="DEPTNO" dataType ="number" value ="DEPTNO"/>

<element name ="JOB" dataType ="varchar2" value ="JOB"/>

</group>

</group>

</dataStructure>

</dataTemplate>

 

TAG	EXPLANATION
<parameters> <parameter name= "p_user_name" datatype "="CHARACTER"> </parameters>	p_user_name is token set for the input parameter of concurrent program
<Lexicals ></LexicaL>	Lexical parameters are defined in this section. But we are not using it
<data query> <sql Statement name="Q1"> <![CDATA[Sql query]]> </sql Statement > </dataQuery>	The SQL query is written within the CDATA tag If you have multiple SQL queries then each should be written under separate <sqlStatement> like <sqlStatement name="Q1">&<sqlStatement name="Q2">
<group name "GOV_TEST_GROUP" source-"Q1">	The data generated as part of each SQL query is grouped using group name.
<element name= "PERSON ID" value="PERSON_ID"/>	Each XML element inside a group corresponds to each data retrieved from that query. This means PERSON_ID will have values from column PERSON_ID

          Save data definition local server in xml format

3. Go application click on responsibilities  enter in concurrent program

               Navigation --->Application Developer --->Concurrent --->Program

ü         4. Create concurrent program with condition.

•  We are not creating the executables. The stranded executable name XDODTEXE.
• Based on requirement add the parameters.
• Save (ctrl+s)

     5. Go xml publisher responsibility upload the data definition.

Navigation --->XML publisher Administrator--->Data Definition

• Enter the code (concurrent program short name), name (concurrent program short name), and application

• Click on Data Template upload the file(data definition).

ü       6. Assign the concurrent program request group submit concurrent program.

          Navigation --->System Administrator --->Responsibility --->Request  

• Next enter group name
• Enter the concurrent program name click on tab.
• Save (ctrl+s)

     7. Submit  the concurrent program

          Navigation Based on which responsibility to assign select that responsibility   

                                              ----> View  ---> Requests

           The concurrent program submits the concurrent program.

• Once submit the concurrent program generate the XML tags.

<XXSDS_EMP_DETAILS_DD>

-

<LIST_G_Q1>

-

<G_Q1>

<DEPTNO>10</DEPTNO>

<DNAME>ACCOUNTING</DNAME>

<LOC>NEW YORK</LOC>

-                  

<LIST_G_Q2>

-

<G_Q2>

<ENAME>CLARK</ENAME>

<DEPTNO>10</DEPTNO>

<JOB>MANAGER</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>MILLER</ENAME>

<DEPTNO>10</DEPTNO>

<JOB>CLERK</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>KING</ENAME>

<DEPTNO>10</DEPTNO>

<JOB>PRESIDENT</JOB>

</G_Q2>

</LIST_G_Q2>

</G_Q1>

-

<G_Q1>

<DEPTNO>20</DEPTNO>

<DNAME>RESEARCH</DNAME>

<LOC>DALLAS</LOC>

-

<LIST_G_Q2>

-

<G_Q2>

<ENAME>FORD</ENAME>

<DEPTNO>20</DEPTNO>

<JOB>ANALYST</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>SCOTT</ENAME>

<DEPTNO>20</DEPTNO>

<JOB>ANALYST</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>JONES</ENAME>

<DEPTNO>20</DEPTNO>

<JOB>MANAGER</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>SMITH</ENAME>

<DEPTNO>20</DEPTNO>

<JOB>CLERK</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>ADAMS</ENAME>

<DEPTNO>20</DEPTNO>

<JOB>CLERK</JOB>

</G_Q2>

</LIST_G_Q2>

</G_Q1>

-

<G_Q1>

<DEPTNO>30</DEPTNO>

<DNAME>SALES</DNAME>

<LOC>CHICAGO</LOC>

-

<LIST_G_Q2>

-

<G_Q2>

<ENAME>WARD</ENAME>

<DEPTNO>30</DEPTNO>

<JOB>SALESMAN</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>MARTIN</ENAME>

<DEPTNO>30</DEPTNO>

<JOB>SALESMAN</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>TURNER</ENAME>

<DEPTNO>30</DEPTNO>

<JOB>SALESMAN</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>JAMES</ENAME>

<DEPTNO>30</DEPTNO>

<JOB>CLERK</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>ALLEN</ENAME>

<DEPTNO>30</DEPTNO>

<JOB>SALESMAN</JOB>

</G_Q2>

-

<G_Q2>

<ENAME>BLAKE</ENAME>

<DEPTNO>30</DEPTNO>

<JOB>MANAGER</JOB>

</G_Q2>

</LIST_G_Q2>

</G_Q1>

</LIST_G_Q1>

</XXSDS_EMP_DETAILS_DD>

•  Once the XML tags generated, download the tags save local server.

ü       8. After that we have to design the RTF file in word (2007).

• Go to 
• Add-ins ---> Click on data ---> Load XML data click
• Upload file
• Once upload file show the pop message data successfully.

• Navigation --->Insert --->All fields.

                                                        (OR)

• After that we have to design the RTF file in word (2019).
• In Word 2019 version: Using Bi Publisher. For that you need to install Bi Publisher in your machine.
• After successfully installed, when you open your Word, you can see Bi Publisher in Ribbon.

• Next, click on Simple XML to load XML data.

• After, loading data you need to create your data template.
• In Bi Publisher Template creation can be done in 2 ways.
•  Manual and Table Wizard.

 

• In this example, I’m using Table wizard to create a data template.
• Follow the below steps to create a data template.

•   After creating Data template.
•   To view the output format, click on PDF to view Output.

•    It is preview sample

•  Next requirement-based design rtf file.
•  After that save the file local server in RTF format.

ü       9. Next go to XML publisher administrator responsibility.

             Navigation --->XML Publisher Administrator --->Home --->Templates

• Enter Name (concurrent program short name)
• Code (concurrent program short name)
•  Data definition
•  Application
•  Type (Upload file which format)

• Upload the file
• Select the language
• Select the Territory

ü       10. After that once again submit run the concurrent program generate the XML report.

.

Back-End

Data definition details

SELECT XDDV.DATA_SOURCE_CODE    DATA_DEFINITION_CODE,

 XDDV.DATA_SOURCE_NAME   DATA_DEFINITION,

 XDDV.DESCRIPTION        DATA_DEFINITION_DESCRIPTION,

 XLDD.FILE_NAME          DATA_TEMPLATE_FILE_NAME,

 XLDD.LOB_TYPE           TYPE

FROM XDO_DS_DEFINITIONS_VL XDDV,

XDO_LOBS XLDD

WHERE 1 = 1

AND   XLDD.LOB_CODE = XDDV.DATA_SOURCE_CODE

AND   XDDV.DATA_SOURCE_CODE= 'XXX_EMP';

 

Data definition and data template details                     

SELECT XDDV.DATA_SOURCE_CODE    DATA_DEFINITION_CODE,

XDDV.DATA_SOURCE_NAME   DATA_DEFINITION,

XDDV.DESCRIPTION        DATA_DEFINITION_DESCRIPTION,

XTB.TEMPLATE_CODE       TEMPLATE_CODE,

XTT.TEMPLATE_NAME       TEMPLATE_NAME,

XTT.description         Template_Description,

XTB.TEMPLATE_TYPE_CODE  TYPE,

XTB.DEFAULT_OUTPUT_TYPE DEFAULT_OUTPUT_TYPE,

XLDD.FILE_NAME          DATA_TEMPLATE_FILE_NAME,

XLTEMP.FILE_NAME        TEMPLATE_FILE_NAME

FROM XDO_DS_DEFINITIONS_VL XDDV,

XDO_TEMPLATES_B       XTB,

XDO_TEMPLATES_TL      XTT,

XDO_LOBS              XLTemp,

XDO_LOBS              XLDD,

XDO_LOBS              XLBUR,

FND_APPLICATION_TL    FAT,

FND_APPLICATION       FA

WHERE         XDDV.application_short_name = FA.application_short_name

AND FAT.application_id = FA.application_id

AND XTB.application_short_name = XDDV.application_short_name

AND XDDV.data_source_code = XTB.data_source_code

AND XTT.TEMPLATE_CODE = XTB.TEMPLATE_CODE

AND XTT.language = 'US'

AND fat.language = 'US'

AND XLtemp.LOB_CODE = XTB.TEMPLATE_CODE

AND XLtemp.XDO_FILE_TYPE = XTB.TEMPLATE_TYPE_CODE

AND XLDD.LOB_CODE = XDDV.DATA_SOURCE_CODE

AND xldd.LOB_TYPE = 'DATA_TEMPLATE'

AND XLBUR.LOB_CODE(+) = XDDV.DATA_SOURCE_CODE

AND XDDV.DATA_SOURCE_CODE='XXX_EMP'

AND XTT.TEMPLATE_CODE = 'XXX_EMP_TEM';

Data definition , data template and bursting files  details

SELECT XDDV.DATA_SOURCE_CODE    DATA_DEFINITION_CODE,

 XDDV.DATA_SOURCE_NAME   DATA_DEFINITION,

 XDDV.DESCRIPTION        DATA_DEFINITION_DESCRIPTION,

 XTB.TEMPLATE_CODE       TEMPLATE_CODE,

 XTT.TEMPLATE_NAME       TEMPLATE_NAME,

 XTT.description         Template_Description,

 XTB.TEMPLATE_TYPE_CODE  TYPE,

 XTB.DEFAULT_OUTPUT_TYPE DEFAULT_OUTPUT_TYPE,

 XLDD.FILE_NAME          DATA_TEMPLATE_FILE_NAME,

 XLTEMP.FILE_NAME        TEMPLATE_FILE_NAME,

 XLBUR.FILE_NAME         Bursting_File_Name

FROM XDO_DS_DEFINITIONS_VL XDDV,

XDO_TEMPLATES_B       XTB,

XDO_TEMPLATES_TL      XTT,

XDO_LOBS              XLTemp,

XDO_LOBS              XLDD,

XDO_LOBS              XLBUR,

FND_APPLICATION_TL    FAT,

FND_APPLICATION       FA

WHERE       XDDV.application_short_name = FA.application_short_name

AND FAT.application_id = FA.application_id

AND XTB.application_short_name = XDDV.application_short_name

AND XDDV.data_source_code = XTB.data_source_code

AND XTT.TEMPLATE_CODE = XTB.TEMPLATE_CODE

AND XTT.language = 'US'

AND fat.language = 'US'

AND XLtemp.LOB_CODE = XTB.TEMPLATE_CODE

AND XLtemp.XDO_FILE_TYPE = XTB.TEMPLATE_TYPE_CODE

AND XLDD.LOB_CODE = XDDV.DATA_SOURCE_CODE

AND xldd.LOB_TYPE = 'DATA_TEMPLATE'

AND XLBUR.LOB_CODE(+) = XDDV.DATA_SOURCE_CODE

AND XDDV.DATA_SOURCE_CODE= 'XXX_EMP'

AND XLBUR.LOB_TYPE(+) = 'BURSTING_FILE'

AND XTT.TEMPLATE_CODE = 'XXX_EMP_TEM';

DELETE XML DATA DEFINITION

BEGIN

DBMS_OUTPUT.PUT_LINE ('START');

XDO_DS_DEFINITIONS_PKG.DELETE_ROW (

       X_APPLICATION_SHORT_NAME => 'XX_CP',

       X_DATA_SOURCE_CODE => 'XX_EMP_A'

);

DBMS_OUTPUT.PUT_LINE ('DELETE SUCCESS');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('WRITE MESSAGE');

DBMS_OUTPUT.PUT_LINE (SQLERRM||SQLCODE);

END;

DELETE XML TEMPLATE

BEGIN

DBMS_OUTPUT.PUT_LINE('START');

xdo_templates_pkg.DELETE_ROW (X_APPLICATION_SHORT_NAME => 'SQLAP',

    X_TEMPLATE_CODE => 'XX_EMP_CODE') ;

    DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULLY');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('WRITE MESSAGE');

DBMS_OUTPUT.PUT_LINE (SQLERRM||SQLCODE);

END;

Delete the data definition file (XML file)

SELECT * FROM XDO_LOBS WHERE LOB_CODE LIKE 'X%';

 

DELETE FROM XDO_LOBS

WHERE LOB_TYPE = 'TEMPLATE'

              AND APPLICATION_SHORT_NAME = 'PO'

             AND LOB_CODE = 'XX_EMP'

              AND FILE_NAME = 'xxsv.xsl';

 COMMIT;

Delete the data template file (RTFfile)

DELETE FROM XDO_LOBS

WHERE LOB_TYPE = 'TEMPLATE_SOURCE'

               AND APPLICATION_SHORT_NAME = 'PO'

              AND LOB_CODE = 'XX_EMP'

               AND FILE_NAME = 'xxsv.rtf';

COMMIT;