Native XML (BI) Publisher Report

This post describes how to create a report using XML Publisher in Oracle E-Business Suite.
Traditionally when a XML Publisher report has been created Oracle Reports have been the engine to
produce the XML. A template has then been developed to use the created XML. This way of working
is only recommended when the Oracle Report is already available and a new layout is created. If a
new report is created from scratch you should use the XML Publisher engine to create both the XML
and the layout. It is possible to use this method in both 11.5.10 and R12.

How to create a native XML Publisher report
The query and parameters is created in a XML-document called Data Template.
Example of a Data Template:

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="CUST_LIST" description="Orderdetail per customer" version="1.0">
<parameters>
<parameter name="p_fr_datum" datatype="character"/>
<parameter name="p_till_datum" datatype="character"/>
<parameter name="p_ordertyp" datatype="character"/>
<parameter name="p_status" datatype="character"/>
<parameter name="p_kundnr" datatype="number"/>
<parameter name="p_kst" datatype="varchar2"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_param">
  SELECT TO_CHAR(TO_DATE(SUBSTR(:p_fr_datum,1,10),'yyyy/mm/dd'),'yyyy-mm-dd') fr_datum,
         TO_CHAR(TO_DATE(SUBSTR(:p_till_datum,1,10),'yyyy/mm/dd'),'yyyy-mm-dd') ti_datum,
        (SELECT name FROM oe_transaction_types_vl WHERE transaction_type_id = :p_ordertyp) otyp,
        (SELECT meaning FROM oe_lookups WHERE lookup_type = 'FLOW_STATUS' AND lookup_code = :p_status) status,
        (SELECT party.party_name||' ('||cust.account_number||')' kund FROM hz_cust_accounts cust, hz_parties party WHERE cust.party_id = party.party_id AND cust.account_number = :p_kundnr) kund
   FROM dual
</sqlStatement>
<sqlStatement name="Q1">
  SELECT h.customer_number kundnr,
         h.sold_to kundnamn,
         h.ship_to_location leveransid,
         h.order_number ordernummer,
         h2.order_number avtalsnummer,
         l.ordered_item artikel,
         i.description artikelnamn,
         l.ship_to_address1 lev_adress_1,
         l.ship_to_address2 lev_adress_2,
         l.ship_to_address3 lev_adress_3,
         l.ship_to_address4 lev_adress_4,
         l.ship_to_address5 lev_adress_5,
         h.attribute7 kund_ref,
         h.attribute17 fakturaref
  FROM oe_order_headers_v h,
       oe_order_lines_v l,
       mtl_system_items_b i,
       oe_order_headers_all h2
  WHERE h.header_id = l.header_id
  AND   l.inventory_item_id = i.inventory_item_id
  ANd   i.organization_id = 101
  AND   h.ordered_date BETWEEN TO_DATE(SUBSTR(:p_fr_datum,1,10),'yyyy/mm/dd') AND TO_DATE(SUBSTR(:p_till_datum,1,10),'yyyy/mm/dd')
  AND   h.order_type_id = :p_ordertyp
  AND   h.source_document_id = h2.header_id (+)
  AND   h.flow_status_code = nvl(:p_status, h.flow_status_code)
  AND   h.customer_number = nvl(:p_kundnr, h.customer_number)
  AND   h2.attribute1 = nvl(:p_kst, h2.attribute1)
  ORDER BY h.order_number
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_PARAM" source="Q_param">
<element name="FR_DATUM" value="FR_DATUM" />
<element name="TI_DATUM" value="TI_DATUM" />
<element name="OTYP" value="OTYP" />
<element name="STATUS" value="STATUS" />
<element name="KUND" value="KUND" />
</group>
<group name="G_CUST" source="Q1">
<element name="KUNDNR" value="KUNDNR" />
<element name="KUNDNAMN" value="KUNDNAMN"/>
<element name="LEVERANSID" value="LEVERANSID" />
<element name="ORDERNUMMER" value="ORDERNUMMER" />
<element name="AVTALSNUMMER" value="AVTALSNUMMER" />
<element name="ARTIKEL" value="ARTIKEL" />
<element name="ARTIKELNAMN" value="ARTIKELNAMN" />
<element name="LEV_ADRESS_1" value="LEV_ADRESS_1" />
<element name="LEV_ADRESS_2" value="LEV_ADRESS_2" />
<element name="LEV_ADRESS_3" value="LEV_ADRESS_3" />
<element name="LEV_ADRESS_4" value="LEV_ADRESS_4" />
<element name="LEV_ADRESS_5" value="LEV_ADRESS_5" />
<element name="KUND_REF" value="KUND_REF" />
<element name="FAKTURAREF" value="FAKTURAREF" />
</group>
</dataStructure>
</dataTemplate>

How to develop a Data Template is described in detail in Oracle XML Publisher Administration and Developers Guide.

The Data Template is uploaded under the Data Definition under the XML Publisher Administrator responsibility.

The next step is to set up the concurrent request so the report can be run with XML output. The XML is needed to develop the template.

Three things are important to set when the request is created:
1. The Short Name must have the same value as the Data Definition Code.
2. The Executable Name must have the value of XDODTEXE.
3. The Output Format must have the value of XML.

Now it is possible to run the concurrent Request and (1) validate that the Data Template is working as expected and (2) verify the produced XML output and use it to develop the template.

When the template is created and uploaded under Templates in the XML Publisher Administrator responsibility it is possible to run the request and see the final result.

One thing is important to set when the Template is created:
1. The Template Code must have the same value as the Data Definition Code.

Sources
• Oracle XML Publisher Administration and Developers Guide

• Oracle XML Publisher Report Designer's Guide