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 XML 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 XXDPDT.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

Step 2: Create Data Definition & Associate with Data Template
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
Screen 1 : Create Data Definition
       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.

References:
http://www.oracle.com/technology/products/xml-publisher/index.htm
http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html
http://xdo.us.oracle.com
Oracle® XML Publisher Administration and Developer’s Guide

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. 

1. First Create a staging table say “MJIL_RCP_HDR_UPL_TBL” as shown below.

CREATE TABLE MJIL_RCP_HDR_UPL_TBL
(
   RECIPE_ID                  NUMBER (15),
   RECIPE_DESCRIPTION         VARCHAR2 (70 BYTE),
   RECIPE_NO                  VARCHAR2 (32 BYTE),
   RECIPE_VERSION             NUMBER (5),
   USER_ID                    NUMBER (15),
   USER_NAME                  VARCHAR2 (70 BYTE),
   OWNER_ORGN_CODE            VARCHAR2 (4 BYTE),
   CREATION_ORGN_CODE         VARCHAR2 (4 BYTE),
   OWNER_ORGANIZATION_ID      NUMBER,
   CREATION_ORGANIZATION_ID   NUMBER,
   FORMULA_ID                 NUMBER (15),
   FORMULA_NO                 VARCHAR2 (32 BYTE),
   FORMULA_VERS               NUMBER,
   ROUTING_ID                 NUMBER,
   ROUTING_NO                 VARCHAR2 (32 BYTE),
   ROUTING_VERS               NUMBER (5),
   PROJECT_ID                 NUMBER (15),
   RECIPE_STATUS              VARCHAR2 (30 BYTE),
   PLANNED_PROCESS_LOSS       NUMBER,
   TEXT_CODE                  NUMBER (10),
   DELETE_MARK                NUMBER (5),
   CONTIGUOUS_IND             NUMBER,
   ENHANCED_PI_IND            VARCHAR2 (1 BYTE),
   RECIPE_TYPE                NUMBER,
   CREATION_DATE              DATE,
   CREATED_BY                 NUMBER (15),
   LAST_UPDATED_BY            NUMBER (15),
   LAST_UPDATE_DATE           DATE,
   LAST_UPDATE_LOGIN          NUMBER (15),
   OWNER_ID                   NUMBER (15),
   OWNER_LAB_TYPE             VARCHAR2 (4 BYTE),
   CALCULATE_STEP_QUANTITY    NUMBER (5),
   FIXED_PROCESS_LOSS         NUMBER,
   FIXED_PROCESS_LOSS_UOM     VARCHAR2 (3 BYTE),
   ATTRIBUTE_CATEGORY         VARCHAR2 (30 BYTE),
   ATTRIBUTE1                 VARCHAR2 (240 BYTE),
   ATTRIBUTE2                 VARCHAR2 (240 BYTE),
   ATTRIBUTE3                 VARCHAR2 (240 BYTE),
   ATTRIBUTE4                 VARCHAR2 (240 BYTE),
   ATTRIBUTE5                 VARCHAR2 (240 BYTE),
   ATTRIBUTE6                 VARCHAR2 (240 BYTE),
   ATTRIBUTE7                 VARCHAR2 (240 BYTE),
   ATTRIBUTE8                 VARCHAR2 (240 BYTE),
   ATTRIBUTE9                 VARCHAR2 (240 BYTE),
   ATTRIBUTE10                VARCHAR2 (240 BYTE),
   ATTRIBUTE11                VARCHAR2 (240 BYTE),
   ATTRIBUTE12                VARCHAR2 (240 BYTE),
   ATTRIBUTE13                VARCHAR2 (240 BYTE),
   ATTRIBUTE14                VARCHAR2 (240 BYTE),
   ATTRIBUTE15                VARCHAR2 (240 BYTE),
   ATTRIBUTE16                VARCHAR2 (240 BYTE),
   ATTRIBUTE17                VARCHAR2 (240 BYTE),
   ATTRIBUTE18                VARCHAR2 (240 BYTE),
   ATTRIBUTE19                VARCHAR2 (240 BYTE),
   ATTRIBUTE20                VARCHAR2 (240 BYTE),
   ATTRIBUTE21                VARCHAR2 (240 BYTE),
   ATTRIBUTE22                VARCHAR2 (240 BYTE),
   ATTRIBUTE23                VARCHAR2 (240 BYTE),
   ATTRIBUTE24                VARCHAR2 (240 BYTE),
   ATTRIBUTE25                VARCHAR2 (240 BYTE),
   ATTRIBUTE26                VARCHAR2 (240 BYTE),
   ATTRIBUTE27                VARCHAR2 (240 BYTE),
   ATTRIBUTE28                VARCHAR2 (240 BYTE),
   ATTRIBUTE29                VARCHAR2 (240 BYTE),
   ATTRIBUTE30                VARCHAR2 (240 BYTE)
);

2. Next create a procedure using the script as shown below.

CREATE OR REPLACE PROCEDURE CONA_RECIPE_UPLOAD_PD (ERRBUF    OUT VARCHAR2,
                                                   RETCODE   OUT NUMBER)
IS
   /******************************************************************************
   NAME: APPS.CONA_RECIPE_UPLOAD_PD
   PURPOSE: Recipe Header Uploading
   REVISIONS:
   Ver Date Author Description
   ——— ———- ————— ————————————
   1.0 8/16/2013 1. Oracle User Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
   Object Name: APPS.CONA_RECIPE_UPLOAD_PD
   ******************************************************************************/

   mjil_rcp_hdr_tbl          gmd_recipe_header.recipe_tbl;
   mjil_rcp_hdr_flex_tbl     gmd_recipe_header.recipe_flex;
   X_status                  VARCHAR2 (1);
   X_msg_cnt                 NUMBER;
   X_msg_dat                 VARCHAR2 (1000);
   X_row                     NUMBER := 1;
   l_user_id                 NUMBER := 1114;
   l_responsibility_id       NUMBER := 22883;
   l_out_index               NUMBER;
   l_responsibility_app_id   NUMBER;

   CURSOR c1
   IS
      SELECT * FROM mjil_rcp_hdr_upl_tbl;
BEGIN
   FND_GLOBAL.
    APPS_INITIALIZE (l_user_id, l_responsibility_id, l_responsibility_app_id);

   FOR i IN c1
   LOOP
      mjil_rcp_hdr_tbl (X_row).recipe_no := i.recipe_no;
      mjil_rcp_hdr_tbl (X_row).recipe_version := i.recipe_version;
      mjil_rcp_hdr_tbl (X_row).recipe_description := i.recipe_description;
      mjil_rcp_hdr_tbl (X_row).RECIPE_STATUS := i.RECIPE_STATUS;
      mjil_rcp_hdr_tbl (X_row).RECIPE_TYPE := i.RECIPE_TYPE;
      mjil_rcp_hdr_tbl (X_row).formula_no := i.formula_no;
      mjil_rcp_hdr_tbl (X_row).formula_vers := i.formula_vers;
      mjil_rcp_hdr_tbl (X_row).routing_no := i.routing_no;
      mjil_rcp_hdr_tbl (X_row).routing_vers := i.routing_vers;

      mjil_rcp_hdr_tbl (X_row).delete_mark := i.delete_mark;
      mjil_rcp_hdr_tbl (X_row).creation_date := SYSDATE;
      mjil_rcp_hdr_tbl (X_row).created_by := i.created_by;
      mjil_rcp_hdr_tbl (X_row).last_updated_by := i.last_updated_by;
      mjil_rcp_hdr_tbl (X_row).last_update_date := SYSDATE;
      mjil_rcp_hdr_tbl (X_row).last_update_login := 1114;

      mjil_rcp_hdr_tbl (X_row).user_name := i.user_name;
      mjil_rcp_hdr_tbl (X_row).owner_orgn_code := i.owner_orgn_code;
      mjil_rcp_hdr_tbl (X_row).OWNER_ORGANIZATION_ID :=
         i.owner_organization_id;
      mjil_rcp_hdr_tbl (X_row).creation_orgn_code := i.creation_orgn_code;
      mjil_rcp_hdr_tbl (X_row).owner_id := i.owner_id;

      mjil_rcp_hdr_flex_tbl (X_row).attribute1 := ‘FLEX1’;

      X_row := X_row + 1;
   END LOOP;

   gmd_recipe_header.
    create_recipe_header (p_api_version          => 1,
                          p_init_msg_list        => FND_API.G_TRUE,
                          p_commit               => FND_API.G_TRUE,
                          p_called_from_forms    => ‘NO’,
                          x_return_status        => X_status,
                          x_msg_count            => X_msg_cnt,
                          x_msg_data             => X_msg_dat,
                          p_recipe_header_tbl    => mjil_rcp_hdr_tbl,
                          p_recipe_header_flex   => mjil_rcp_hdr_flex_tbl);

   DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
   DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

   FOR i IN 1 .. X_msg_cnt
   LOOP
      FND_MSG_PUB.get (p_msg_index       => i,
                       p_encoded         => ‘F’,
                       p_data            => X_msg_dat,
                       P_MSG_INDEX_OUT   => l_out_index);
      DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
      DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

      FOR i IN 1 .. X_msg_cnt
      LOOP
         FND_MSG_PUB.get (p_msg_index       => i,
                          p_encoded         => ‘F’,
                          p_data            => X_msg_dat,
                          P_MSG_INDEX_OUT   => l_out_index);
         DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
      END LOOP;
END;
/

3. Finally register the procedure and run it.