FEB

14

Developing XML Publisher Report - using Data Source as PL/SQL Stored Procedure

Background:

Developing sample XML Publisher Report with Executable Method as 'PL/SQL Stored Procedure'

In my previous post https://oracleappscenter.blogspot.com/2016/07/xml-data-template.html i have explained developing XML Publisher Report using Data Template as Data Source

Prerequisite for the below Example:

1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
     VALUES (569, 'Oracle Cost Management');
3. Issue Commit

1. Create a Package Spec & Body with a single Procedure
Spec:

CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);
END MY_PACKAGE;
/ 
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32765);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := 'SELECT product_code, product_name 
         FROM demo_products 
       WHERE product_code = ' || p_product_id;

      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      -- set rowset tag to PRODUCTS and row tag to PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
      DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');

      -- now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length - l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, 'Error in procedure MY_PACKAGE.report');
   END REPORT;
END MY_PACKAGE;

/

2. Define Executable
Navigation: Application Developer > Concurrent > Executable

Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT

3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program

Provide Program, Short name, Application, Description & also
- Executable Name as defined in the above step

- Output Format should be XML
- Define a Parameter p_product_id
- Associate Concurrent Program to the Request Group.

4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

6. Run the Concurrent Program to see the output

Note:
If you need any extra help from Step2 refer my previous post

https://oracleappscenter.blogspot.com/2016/07/xml-data-template.html

XML Data Template

Developing XML Publisher Report - Using Data Template as Data Source

Background:
Developing XML Publisher Report - Using Data Template(.xml) as Data Source and Template(.rtf) as Layout.

Note that, we can use .rdf file as data source. But for this demo we are using Date Template.

 

Prerequisite for the below Example:

1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
     VALUES (569, 'Oracle Cost Management');
3. Issue Commit
Step1: Define Data Template:

The data template is the method by which you communicate your request for data to the data engine.
The data template is an XML document that consists of four basic sections:  

• Define parameters: In which parameters are declared in child <parameter> elements
• Define triggers:
• Define data query: In which the SQL queries are defined in child <sqlStatement> elements
• Define data structure: In which the output XML structure is defined

 Create Data Template: (Save this file as DPDT.xml)

<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="demoProductsDT" description="Demo Products Details" version="1.0">
   <parameters>
     <parameter name="p_product_id" datatype="number"/>
   </parameters>
  <dataQuery>
    <sqlStatement name="DQ">
         <![CDATA[ SELECT product_code, product_name FROM demo_products 
                            WHERE product_code = NVL(:p_product_id,product_code) ]]>
    </sqlStatement>
  </dataQuery>
  <dataStructure>
    <group name="G_DP" source="DQ">
      <element name="PRODUCT_CODE" value="product_code"/>
      <element name="PRODUCT_NAME" value="product_name"/>
    </group>
  </dataStructure>
</dataTemplate>

• This Data Template selects the product details from the demo_products table. It uses a bind parameter to find the product name against the product code.
• For each bind parameter in the query , we need to define a Parameter in the Concurrent Program

Step2: Create Data Definition & Associate with Data Template
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition

Screen 1 : Create Data Defintion
       Enter the data definition Details and click on Apply. Note down the Code.
       The code should be used as the short name of the concurrent program.

Screen 2: Associate Data Template
       View Data Definition

 Click on ‘Add File’ button to upload Data Template file that was created through step 1

Screen 3: Data Definiton
       Data Template is associated with Data Definition

Step 3: Define a Concurrent Program to generate the Data XML output. 

Note:

1. Output format should be XML
2. Short Name in the concurrent program and Code in the data definition should be same.

Screen 2: Concurrent Program - Parameters

For each parameter in the Data Template, define a parameter in the concurrent program.
The Data Template parameter name should match the concurrent program parameter token

Note:

Token is p_product_id. This is the bind parameter we have used in date template. For every bind parameter used in the data template, we have to define parameter in the concurrent program.

Screen 3: Associate the Concurrent Program to a request group.

Screen 4: Execute the concurrent program "Product Demo Report"and click on the output button get the Data XML. Save the XML file. We will use it to generate the RTF Template.

Screen 5: Concurrent Program Output

Note:

We are getting the output in xml because we didn’t define template & associated yet.

Step 4: Define the RTF Template using the Generated Data XML

Pre-requisite : Install XML Publisher Desktop
After installation following Menus & Toolbars gets added to the MS Word.

Load XML Data generated by Concurrent Program
Data -> Load XML Data

 Message after loading the data

Using the Table Wizard as below to create the 'Table Report Format' with the columns of demo_products

Final Output layout look like this.

Save this file with .rtf extension

Step 5: Registering the Template with BI Publisher

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

Step 6: Run the concurrent program to see the output

Note:

As already mentioned output format can be anything. Here it is pdf. We can select format that we want at the runtime.


FAQ:
What is XDODTEXE used in the Executable section of Concurrent Program?

XDODTEXE is a BI Publisher Data Template Executable. The purpose of this executable is to identify data template file (.xml) and execute the data template to generate the raw xml data, that later can be used by BI Publisher formatting engine to format as as per the layout (RTF, PDF etc).
This executable will be used by all the BI Publisher reports (Concurrent Program) which are using Data Template to generate the xml data.