Create XML Publisher Report using Data Templates


The data templates are useful when you need to create a XML Publisher report without using the RDF. The XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner which you can easily use in your templates.

The data template is the method by which you communicate your request for data to the data engine. It is an XML document whose elements collectively define how the data engine will process the template to generate the XML.

The Data Template Definition:

The data template is an XML document that consists of four basic sections:

 Sections of Data Templates

  • define parameters,
  • define triggers,
  • define data query,
  • define data structure

 Here is a sample data template:

Sample Data Template

1] Parameters Section:

A parameter is a variable whose value you can set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. However, the Parameters section of the data template is optional.

How to Define Parameters:

<parameters>
<parameter name="P_PERIOD_FROM" dataType="character" />
<parameter name="P_PERIOD_TO" dataType="character" />
</parameters>

How to Pass Parameters:

To pass parameters, (for example, to restrict the query), use bind variables in your query. For example:

SELECT *

FROM apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO;

2] Data Query Section:

The <dataQuery> section of the data template is required.

How to Define SQL Queries

The <sqlStatement> element is placed between the open and close dataQuery tags. The <sqlStatement> element has a related attribute, name. It is expressed within the <sqlStatment> tag. The query is entered in the CDATA section.

<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT *
FROM apps.gl_balances glb
WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO]]>
</sqlStatement>
</dataQuery>

Lexical References:

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime.

Create a lexical reference using the following syntax:

&parametername

Define the lexical parameters as follows:

• Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.

• Create your query containing lexical references.

<dataQuery>
<sqlStatement name="Q_Lexical">
<![CDATA[ &p_query ]]>
</sqlStatement>
</dataQuery>

<dataTrigger name="beforeReport" source="xxfin_test_pkg.beforeReport" />

In the function xxfin_test_pkg.beforeReport, you can design your query at runtime as below:

p_query VARCHAR2(2000) :=

‘SELECT * apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO’;

How to Define a Data Link between Queries:

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.

The following example shows a query link using a bind variable:

<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT PARENT_COL FROM XXTEST_LINK_TABLE1]]>
</sqlStatement>
</dataQuery>

<dataQuery>
<sqlStatement name="Q2">
<![CDATA[SELECT CHILD_COL FROM XXTEST_LINK_TABLE2]]>
</sqlStatement>
</dataQuery>

<link name="TEST_LINK" parentQuery="Q1" parentColumn="PARENT_COL"
childQuery="Q2" childColumn="CHILD_COL"/>

The <link> element has a set of attributes. Use these attributes to specify the required link information. You can specify any number of links.

3] Using Data Triggers:

Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.

Data triggers are optional, and you can have as many <dataTrigger> elements as necessary. The <dataTrigger> element has a set of related attributes. These are expressed within the <dataTrigger> tag.

For example:

<dataTrigger name="beforeReport1" source=" xxfin_test_pkg.beforeReport()"/>
<dataTrigger name="beforeReport2" source=" xxfin_test_pkg.beforeReport(:Parameter)"/>
  • Name: The event name to fire this trigger.
  • Source: The PL/SQL <package name>.<function name> where the executable code resides.

4] Data Structure Section:

In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output. You can specify all the columns within each group and break the order of those columns; you can use summaries, and placeholders to further customize within the groups.

Sample Data Structure:

<dataStructure>
<group name="GROUP_1" source="Q1">
  <element name="LEDGER_ID" value="LEDGER_ID" />
  <element name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME" />
  <element name="LEDGER_DESCRIPTION" value="LEDGER_DESCRIPTION" />
  <element name="LEDGER_NAME" value="LEDGER_NAME" />
  <element name="LEDGER_SUM_BAL_DR" value="ACCT_SUM_BAL_DR" function="SUM()" />
  <element name="LEDGER_SUM_BAL_CR" value="ACCT_SUM_BAL_CR" function="SUM()" />
<group name=" GROUP_2" source="Q1">
  <element name="CODE_COMBINATION_ID" value="CODE_COMBINATION_ID" />
  <element name="ACCOUNTING_CODE_COMBINATION" value="ACCOUNTING_CODE_COMBINATION" />
  <element name="CODE_COMBINATION_DESCRIPTION" value="CODE_COMBINATION_DESCRIPTION" />
  <element name="ACCT_SUM_PR_DR" value="PERIOD_NET_DR" function="SUM()" />
  <element name="ACCT_SUM_PR_CR" value="PERIOD_NET_CR" function="SUM()" />
  <element name="ACCT_SUM_BAL_DR" value="BEGIN_BALANCE_DR" function="SUM()" />
  <element name="ACCT_SUM_BAL_CR" value="BEGIN_BALANCE_CR" function="SUM()" />
<group name=" GROUP_3" source="Q1">
  <element name="PERIOD_YEAR" value="PERIOD_YEAR" />
  <element name="PERIOD_NUMBER" value="PERIOD_NUMBER" />
  <element name="PERIOD_NAME" value="PERIOD_NAME" />
  </group>
  </group>
  </group>
</dataStructure>

How to Call a Data Template:

There are two methods for calling the data engine to process your data template:

• Concurrent Manager

• Data Engine Java APIs

Before you can use either of these methods, you must first register your data template in the Template Manager as a Data Definition. After that you attached the RTF template to that data definition.

Data def for data template

Calling a Data Template from the Concurrent Manager:

To use the concurrent manager to execute your data template, you must register a Concurrent Program, using the define Concurrent Programs form:

CP Def for data template

Executable Name Enter the XML Publisher data engine executable: XDODTEXE

Output Format Select “XML” as the output format.

Note: We do not need to create a concurrent executable as we use a seeded executable XDODTEXE

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

Few tips for best practices:

Performing operations in SQL is faster than performing them in the data template or PL/SQL. It is recommended that you use SQL for the following operations:

  • Use a WHERE clause instead of a group filter to exclude records.
  • Perform calculations directly in your query rather than in the template.

 To maximize performance when building data queries in the data template: XML Publisher tests have shown that using bind variables is more efficient than using the link tag.

The dataStructure section is required for multiple queries and optional for single queries. If omitted for a single query, the data engine will generate flat XML.

Number Formatting in XML Publisher


While designing XML Publisher reports, many a times we need to play with numbers and change their formats as part of the client’s requirements. Here is a brief explanation of couple of techniques that should be used while doing any number format change in XML Publisher.

XML Publisher supports two methods for specifying the number format:

  • Oracle’s format-number function
  • Microsoft Word’s Native number format mask

Now…before going into details please remember few things like:

  • To use the Oracle format mask or the Microsoft format mask, the numbers in your data source (ex XML File) must be in a raw format, with no formatting applied.
  • Use only one of these methods. If the number format mask is specified using both methods, the data will be formatted twice and it may cause unexpected behavior.
  • If you are designing a template to be translatable, using currency in the Microsoft format mask is not recommended unless you want the data reported in the same currency for all translations. Using the MS format mask sets the currency in the template so that it cannot be updated at runtime.

Using the Oracle Format Mask:

To apply the Oracle format mask to a form field:

1. Open the Form Field Options dialog box for the placeholder field.

2. Set the Type to “Regular text”.

3. In the Form Field Help Text field, enter the mask definition according to the following example:

<?format-number:fieldname;’999G999D99′?>

Where fieldname is the XML tag name of the data element you are formatting and 999G999D99 is the mask definition.

Symbol Meaning
0 Digit. Each explicitly set 0 will appear, if no other number occupies the position. Format mask: 00.0000 (Ex: Change 1.234 to 01.2340)
9 Digit. Returns value with the specified number of digits with a leading space if positive or a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. Format mask: 99.9999 (Ex: Change 1.234 to 1.234)
C Returns the ISO currency symbol in the specified position.
D Determines the placement of the decimal separator. The decimal separator symbol used will be determined at runtime based on template locale. Format mask: 9G999D99 (Ex: Change 1234.56 to 1,234.56( For English locale:) or to 1.234,56 (For German locale))
EEEE Returns a value in scientific notation.
G Determines the placement of the grouping (thousands) separator. The grouping separator symbol used will be determined at runtime based on template locale. Format mask: 9G999D99 (Ex: Change 1234.56 to 1,234.56( For English locale:) or to 1.234,56 (For German locale))
L Returns the local currency symbol in the specified position.
MI Displays negative value with a trailing “-“.
PR Displays negative value enclosed by <>
PT Displays negative value enclosed by ()
S (before number) Displays positive value with a leading “+” and negative values with a leading “-“
S (after number) Displays positive value with a trailing “+” and negative value with a trailing “-“

Using the Microsoft Number Format Mask:

To format numeric values, use Microsoft Word’s field formatting features available from the Text Form Field Options dialog box. The following graphic displays an example:

To apply a number format to a form field:

  1. Go into the Word Properties for the field.
  2. Change the Type drop-down box to Number (it defaults in as Regular Text)
  3. From the Number format drop-down box choose the format you want.

Native XSL format-number function:

You can also use the native XSL format-number function to format numbers. The native XSL format-number function takes the basic format:

format-number(number,format,[decimalformat])

Where:

number (Required) – Specifies the number to be formatted.

Format (Required)  – Specifies the format pattern. Use the following characters to specify the pattern:

Symbol Meaning
# Denotes a digit. Example: ####
0 Denotes leading and following zeros. Example: 0000.00
. The position of the decimal point Example: ###.##
, The group separator for thousands. Example: ###,###.##
% Displays the number as a percentage. Example: ##%
; Pattern separator. The first pattern will be used for

positive numbers and the second for negative numbers

 

Decimal format (Optional) – For more information on the decimal format please consult any basic XSLT manual.

Currency Formatting:

This is another feature of XML Publisher that enables you to define specific currency format masks to apply to your published data at runtime.

To utilize currency formatting, you must:

1. Define your currency formats in XML Publisher’s Administration interface.

2. Assign the Currency Format Set as a configuration property at the desired level (site, data definition, or template).

3. Enter the format-currency command in your RTF template to apply the format to the field at runtime.

To use the format-currency command:

In the form field dialog of the field you want to format, enter the following syntax:

<?format-currency:ELEMENT_NAME;’currency-format-code’?>

For example:

<?format-currency:BALACE_TOTAL;’USD’?>

The currency code must correspond to a currency format that is defined in the Currency Format Set to be used with this report. The Currency Format Set can be specified at the site level, data definition level, or template level.

For more information, refer Oracle XML Publisher Administration and Developer’s Guide.

Best Implementation Practices with XML Publisher!


 

© Best Implementation Practices with Oracle Business Intelligence Publisher  – by Mohan Dutt and Brent Lowe

XDOLoader Utility to upload XML Templates


The XDOLoader utility is a Java-based command line program to load template (RTF, PDF, and XSL-FO), XML, and XSD files to the XML Publisher database tables. Before uploading any xml template you need to upload your concurrent program (if any) and data definitions through the FNDLOAD utility.

General Syntax to execute the XDOLoader utility as follows:

% java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_con_string> \
-LOB_TYPE <lob_type> \
-APPS_SHORT_NAME <application_short_name> \
-LOB_CODE <lob_code> \
-LANGUAGE  \
-TERRITORY  \
-XDO_FILE_TYPE <xdo_file_type> \
-NLS_LANG <NLS_LANG> \
-FILE_CONTENT_TYPE <file_content_type> \
-FILE_NAME <file_name> \
-OWNER  \
-CUSTOM_MODE [FORCE|NOFORCE] \
-LOG_FILE <log file>

The parameters are described below:

  • UPLOAD (Mandatory): The first parameter: UPLOAD will be implemented in the feature.
  • DB_USERNAME (Mandatory): Database user name (example: apps).
  • DB_PASSWORD (Mandatory): Database user password (example: manager).
  • JDBC_CONNECTION (Mandatory): JDBC database connection string
    (example: ap000sun:1234:ebs).
  • LOB_TYPE (Mandatory):  XDO LOB type. Valid values are: ‘TEMPLATE’, ‘XML_SCHEMA’ and ‘XML_SAMPLE’
  • APPS_SHORT_NAME (Mandatory):  Application short name (example: AR).
  • LOB_CODE (Mandatory):  XDO LOB code. Enter either the Template Code or the Data Definition Code.
  • LANGUAGE (Optional): ISO two-letter language code (example: en).
  • TERRITORY (Optional): ISO two-letter territory code (example: US), default is ’00’.
  • XDO_FILE_TYPE (Mandatory):  Enter the XDO file type, valid values are: PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML, XSLTEXT, XSD, XML, RTF-ETEXT
  • NLS_LANG (Optional): Enter the NLS_LANG environment variable.
  • FILE_CONTENT_TYPE (Optional): Content type of the file (example: text/html, application/pdf)
  • FILE_NAME (Mandatory): Name of the file you want to upload. You can give full path name of the file.
  • OWNER (Optional): Owner of the template. Default is “ORACLE”.
  • CUSTOM_MODE (Optional): Whether to force update. Valid values are FORCE and NOFORCE (default).
  • LOG_FILE (Optional): Enter a file name for the output log file (default: xdotmpl.log).

Sample script to run the XDOLoader utility to upload a xml template:

echo  "----------------------------------------------------------------------"
echo  "Please enter the following details needed for the XML XDOLoad process"
echo  "This details can be found in the TNS entry"
echo  "----------------------------------------------------------------------"

echo "Enter Apps password  =>"
read apps_pw

echo "Enter Host TNS Database Server name  =>"
read v_host

echo "Enter Host Database TNS Port Number =>"
read v_port

echo "Enter Host Database SID name =>"
read v_sid

echo "Uploading rtf(XXFIN_SAMPLE_TEMPLATE.rtf) for the XML Template Definition"

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_pw \
-JDBC_CONNECTION $v_host:$v_port:$v_sid \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME "IBY" \
-LOB_CODE "XXFIN_TEMPLATE_CODE" \
-LANGUAGE en \
-XDO_FILE_TYPE RTF-ETEXT \
-FILE_CONTENT_TYPE 'text/html' \
-FILE_NAME "$XXFIN_TOP/templates/XXFIN_SAMPLE_TEMPLATE.rtf" \
-CUSTOM_MODE FORCE

echo  "Finish uploading rtf(XXFIN_SAMPLE_TEMPLATE.rtf) for the XML Template Definition"

You can run the below query to see the details of your uploaded Template in the database.

SELECT * FROM

XDO_LOBS ORDER BY LAST_UPDATE_DATE DESC;

Format Customization in Oracle Payments using Extensibility Utility Package


From Oracle Accounts Payable, user submits the invoices to Oracle Payments as a Payment Process Request. Oracle Payments uses the invoice information submitted in the Payment Request to create Documents Payable and then groups them into Payments and Payment Instructions for processing payments. This processed information is then recorded in the database tables. The processed payment information is retrieved by Oracle Payments database views to generate the XML extract. The generated XML extract is used in conjunction with the RTF/ETEXT template by Business Intelligence Publisher to generate output.

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments. The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively. These files are located in the $IBY_TOP/patch/115/sql directory. 

The package allows custom elements to be created at following levels.

  • Instruction
  • Payment
  • Document Payable
  • Document Payable Line
  • Payment Process Request

You cannot customize the package specification, but package body contains stubbed functions that you can customize. 

The five functions are as follows:

FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at instruction level and run only once for the instruction.

FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable level and run once for each payment process request. 

First determine which function within IBY_FD_EXTRACT_EXT_PUB should be modified. Then write a PL/SQL block similar to the structure given below into the package body function definition, replacing the lines “BEGIN” and “RETURN NULL”, and retaining all other lines:

   <local_xml_variable>  XMLTYPE;

    CURSOR <cursor_name> (<cursor_parameter_name> IN NUMBER) IS
    SELECT XMLConcat(
             XMLElement("Extend",
               XMLElement("Name", '<xml_tag_name1>'),
               XMLElement("Value", <xml_tag_value2>)),
             XMLElement("Extend",
               XMLElement("Name", '<xml_tag_name2>'),
               XMLElement("Value", < xml_tag_value2>))
           )
     FROM <data_table_name>
   WHERE <table_identifier_column> = <cursor_parameter_name>;

  BEGIN

    OPEN <cursor_name> (<function_parameter_name>);
    FETCH <cursor_name> INTO <local_xml_variable>;
    CLOSE <cursor_name>;

    RETURN <local_xml_variable>;

A sample xml file with extended tags will look like:

Then modify the eText template using the new custom elements. Use the following syntax to select the appropriate Name/Value pair.

…/Extend[Name='<xml_tag_name1>’]/Value

You can use BI Publisher Template Viewer to view the final eText Outputs.

Reference Articles in MOS:

  • Format Customization in Oracle Payments for Oracle Applications Release 12 [ID 787467.1]
  • R12: Can Users Customize XML Extract? [ID 457539.1]
  • R12 Oracle Payments Processing ‘How To’ documents [ID 579132.1]
  • R12: How To Assign/Modify XML Publisher Payment Templates [ID 414336.1]

Advantages of using the XML Publisher Report


Advantages of using the XML Publisher Report

 

Choose Your Design Tool:

Xml Publisher Report formats can be designed using Microsoft Word or Adobe Acrobat – tools most users are already familiar with. There is no proprietary design studio component required, meaning no extra cost and no extra learning curve.

Data from Anywhere:

Xml Publisher can accept and format any well-formed Xml data, as well as generate the Data. It can therefore be integrated with any database. Xml Publisher also allows you to bring data In from Multiple Data Sources into a Single Output Document.

 

Deliver To Anywhere:

Deliver your content via Printer, E-Mail, Fax, Webdav, or publish your report to a portal. The Delivery Manager’s open architecture allows for easy implementation of custom delivery channels, as well.

Communicate With The World:

Report Layouts can be created for any combination of 185 languages and 244 territories. These Layouts are converted to Industry-Standard files for easy In-House translation or delivery to third party translation providers. Because each translation is a separate file, modifications can still be made to the Layout without impacting translations. Xml Publisher also offers Advanced Bidirectional and Font Handling Support.

Unsurpassed Performance:

Xml Publisher is based on the W3c Xsl-Fo Standard and it is the Fastest, Most Scalable Implementation in the world today. It can handle very large data inputs and generate output in less time, using very low levels of CPU time and memory.

Open Standards. Easy Integration:

Xml Publisher is built on Open Standard technologies. It is a J2ee application that can be deployed to any J2ee container. Data is handled as Xml and the Layout Templates created in the desktop applications are internally converted to Xsl-Fo, another W3c standard. Outputs generated by the application are also industry standards such as Pdf, Rtf and Html. The delivery protocols are Internet Printing Protocol (Ipp), Webdav, Ftp, As2.

Steps to create a XML Publisher Report


Steps to create a XML Publisher Report

 

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.

2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.

3] Set the user parameter as p_conc_request_id.

4] Add the default values to the Before Report and After Report triggers(not mandatory)

5] Ftp the Report to the Cust_Top/Report/Us.

6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.

7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.

8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.

9] Design the template in Ms Word(Using the .Rtf file).

10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.

11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.

12] Now go to the Responsibility and run the request.