XML Publisher Data Template

Step by Step Guide to create a sample XML Data Template:

Decide on the SQL Query

In the example, we are trying to get the following data from the AP_INVOICES_ALL Table

1. INVOICE_NUM
2. INVOICE_CURRENCY_CODE
3. INVOICE_AMOUNT

The query for the same will be like below:

SELECT  invoice_num         ,invoice_currency_code         ,invoice_amount   FROM  ap_invoices_all;

Decide on the parameters

We will be using two parameters to limit the data being retrieved:

1. ORG_ID
2. VENDOR_ID

The resulting query will be like:

SELECT  invoice_num         ,invoice_currency_code         ,invoice_amount   FROM  ap_invoices_all  WHERE  org_id = :p_OrgId    AND  vendor_id = :p_VendorId;

That’s all we need to construct the XML Data Template.

<?xml version="1.0" encoding="UTF-8" ?> <dataTemplate name ="invoiceData" description="Invoice Data" Version="1.0" > <parameters> <parameter name="p_OrgId" dataType="number" /> <parameter name="p_VendorId" dataType="number" /> </parameters> <dataQuery> <sqlStatement name="Q1"> <![CDATA[ SELECT invoice_num,invoice_currency_code,invoice_amount   FROM ap_invoices_all  WHERE org_id = :p_OrgId    AND vendor_id = :p_VendorId ]]> </sqlStatement> </dataQuery> <dataStructure> <group name="G_INV" source="Q1"> <element name="INV_NUMBER" value="invoice_num" /> <element name="CURRENCY_CODE" value="invoice_currency_code" /> <element name="AMOUNT" value="invoice_amount" /> </group> </dataStructure> </dataTemplate> Save the same as “.xml” file in your local system. After saving the file, you should be able to open it in the browser. Double click the xml file and the file should open. The file will look as shown below: Open MS Word. Go to Add-Ins. You will be able to see the BI Publisher Menu. Under the Menu, Select Data > Load XML Schema. Select the xml file that we just created. Press OK. We will get the below template in the Word Document. Save the same as XXINV_DETAILS.rtf. Go to XML Publisher Administrator responsibility and create a new Data Definition. Give the following for the fields: Name – XXINV_DETAILS Code – XXINV_DETAILS Application – Provisioning (or your custom application) Start Date – Automatically Populated Press “Add File” next to Data Template. Browse and upload the XML Data Template that we have created here. Go to Data Templates and create a new Data Template. Give the following details: The Executable should always be XDODTEXE. Click on Parameters and give the following details: