XML Publisher

Background / Business Requirement for Bursting
In Oracle Purchasing we have PO Print Report which can be used to print new or selected Purchase order. Organizations usually schedule this program on a daily basis to print new purchase orders. If we want to integrate this Oracle Report to XML/BI Publisher to create eye candy layouts and email the PO automatically to the suppliers, the common issue we face is :-

How to split the XML file created by report ? This is because report will create a single XML file consisting of the all the PO selected and if we apply the template on this XML file , it will create a single PDF file consisting of all the PO’s , and we definitely don’t want purchase orders sent to a supplier which is not related to him .If we have to send PO to individual supplier there has to be a way to parse the XML file generated from the PO Print Report.After parsing the XML file we can break the XML into smaller chunks based on the PO Number and then email the PO to respective Suppliers.
Advantages of XMLP bursting

a. No Additional Coding, just need to create XML bursting File. ( Isn’t it exciting , otherwise we would have to write java program to split the XML File and then use XMLP delivery manager API to send emails .
b. Process of Creating XML data Definition and Template remains AS – IS .

Prerequisites

a. Apply patch 5968876 if not already applied
b. Verify XMLP version is 5.6.3

Steps to Verify if XMLP is indeed 5.6.3

1. adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class

Below matrix shows the the version you are and the patch to be applied to come to 5.6.3

Metainfo.class

Metainfo.class	XML Publisher	Patch	Reference
120.6	XDO 5.6.3	Patch 4440000
… for the Oracle E-Business Suite 11i
115.28	XDO 5.6.3	Patch 5472959	Note 422508.1
115.27	XDO 5.6.2 (core)	Patch 5097966	Note 367394.1
115.26	XDO 5.6.1	Patch 4905678	Note 357308.1
115.24	XDO 5.6.0	Patch 4561451	Note 337999.1
115.22	XDO 5.5.0	Patch 4206181	Note 316447.1
115.21	XDO 5.0.1 (core)	Patch 4236958
115.19	XDO 5.0.0	Patch 3822219	Note 295036.1

c. Set Temporary Directory in the XML Publisher Administrator- > Administration- > General to a directory to which you have write permission .
d. After the Patch is applied we will see a new Bursting control file button in the XML Publisher Data Definition Page

To check this, please go to Responsibility “XML Publisher Administrator”, and search for any existing report, and click on Update. While updating an existing data-definition, you should see the button Bursting Control File.

Now since we have finished setting up , now we will see how to implement Bursting . The major component in this is bursting control file
What is a bursting file ?

Its an XML file which tells bursting engine

a) How to split the XML File created ?

b) How to deliver the Documents ?

Lets see a sample bursting file and its components

a) <xapi:request select> tag tells busting engine , bursting should be done at the occurrence of which tag ?
b) <xapi:delivery > tag tells bursting engine , which delivery mechanism to be used and on what condition ?

Sample bursting file

<xapi:requestset xmlns:xapi=”http://xmlns.oracle.com/oxp/xapi&#8221; type=”bursting”>
<xapi:request select=”/xxx_HQ_XML_POXPRPOL/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS”>
<xapi:delivery>
<xapi:email server=”xxxort” port=”25″ from=” xxp@xx.org.uk This e-mail address is being protected from spambots. You need JavaScript enabled to view it ” reply-to=” xx@xx.org.uk This e-mail address is being protected from spambots. You need JavaScript enabled to view it “>
<xapi:message to=”${CF_VENDOR_EMAIL}” attachment=”true” subject=” Purchase Order No: ${POH_PO_NUM}”>

Please review the attached PO ${POH_PO_NUM}
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output-type=”pdf” delivery=”123″>
<xapi:template location=”/u0350/app/apps/UAT/xx/1.0/data/XXHPAPOPRINT.rtf”>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>

Please Note : While doing testing please change the “to address” to your email address , else it will send the documents out to that email address .

Data Definition

Create the Data Definition and Template the normal way, where the Short name of the data definition is same as the short name of concurrent program to which you are integrating XMLP.While creating Data Definition Upload you bursting XML File . If there are any parsing issues in the XML file it will give error , please correct the format of the file and try to upload again .

Template File

Create the Template and Upload your Template file

Concurrent Program Registeration

In the Concurrent Program give the Output fomat as XML and the Concurrent Program Short name should be same as the data definition name .
After Report Trigger Changes

Now the Final Step , in the After report Trigger of your .RDf add the following code to call bursting program . This will lanuch the bursting program from your report , otherwise you will have to run the Bursting program ( which is a Java Concurrent Program ) manually .

Now you are all set to do the testing , run the report , please keep in mind to change “to email address” in the bursting control file to your email id while doing the testing . you will see the PO pdf coming to the email address given in the bursting control file.

Here’s a brisk case study into the power of core BI Publisher functionality in combination with the Oracle eBusiness Suite with a focus on Bursting.

The requirement here is to send Suppliers their Remittance Advice via email with a pixel perfect PDF attachment, of course with minimal coding. I’m limiting the functionality to email remittance advice for EFT payments in order to simplify this tutorial. Plus there are some coding shortcuts – your’re more than welcome to provide free code fixes if you can spot them 😉

Test Data

Firstly, lets look at the test data ingredients used:

• 1 Supplier: say “Virtuate”
• 1 Supplier Site: say “WELLINGTON” with Remittance Email address entered, Payment Method = EFT, Bank Account assigned
• 2 Invoices for the Supplier say INV0001, INV0002
• 1 Payment Batch say BURST_01 where payments have been made and payment batch confirmed

XML Data Source

Now, we need to create the Separate Remittance XML file somehow. Rather than reinvent the wheel, I started with a stock standard Payables Separate Remittance Advice report (APXPBSRA.rdf), and did the following:

1. Setup a new concurrent program definition by copying the existing one, renaming, changing the executable, changing output to XML
2. Added the new concurrent program to Payables “All Reports” request group
3. Copied the report definition $AP_TOP/reports/US/APXPBSRA.rdf to a new report XXV8_APXPBSRA.rdf under modifications top directory
4. Spiced up the new report with a couple of extra fields (Remittance Email, Fax number, etc)
5. Restricted the data returned to only Suppliers Sites with a Remittance Email address (take this out later for fax/print etc).
6. Restricted the data returned to only payments with payment method of EFT (checks have their on remittance advice).
7. Ran to get the XML output. Note the <APXPBSRA><LIST_G_SEL_CHECKS><G_SEL_CHECKS> structure, we will use this later in the Bursting Control File
8. And of course hacked the XML output – cut’n’paste style to add more data rather than having to key it (an additional supplier and invoices). Note this technique includes overwriting the output file $APPLCSF/$APPLOUT/o{REQUEST_ID}.req and follows the principles in my post on masquerading one request as another.

Layout

Okay, that sorts out the base report and data, now onto the fun stuff with a few screenshots:

1. Create a new pretty RTF layout template
2. Register the Data Definition
3. Register the Template
4. Run XML Report Publisher on the request that produced the prior XML data, and all lovely!

Bursting

Righto, now onto the Bursting part. We’re going to:

1. Create a Bursting Control File to email Suppliers
2. Upload the control file to the Data Definition
3. Test it out by calling the XML Publisher Report Bursting Program
4. (Optional) Extend the Report to automatically submit the Bursting program

At this point please make sure you have done the following EBS bursting prerequisite steps:

• (Optional, but highly recommended) Upgrade to 11.5.10.2 / XMLP 5.6.3 or higher (ATG RUP5 or higher is nice)
• Apply 5968876 XDO:EBS-BURSTING INTEGRATION PATCH
• Restarted your applications processes – or the button to upload your bursting control file won’t appear!
• Set the Temporary Directory under XML Publisher Administrator, Administration, General – to e.g. /tmp, or you’ll get error message:

java.lang.NullPointerException at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory(JCP4XDOBurstingEngine.java:413)

• Assign Concurrent Program “XML Publisher Report Bursting Program” to the appropriate Request Group, e.g. “All Reports” / Payables
• Make sure you have an SMTP server that you can send your email through!

Bursting Control File

Next, lets get into the Bursting control file and look at it a bit closer:

1. Create Bursting Control File to email Suppliers custom Separate Remittance Advice

<?xml version=”1.0″ encoding=”UTF-8″?>
<xapi:requestset xmlns:xapi=”http://xmlns.oracle.com/oxp/xapi”&gt;
<xapi:request select=”/APXPBSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS”>
<xapi:delivery>
<xapi:email server=”smtp.yourdomain.com” port=”25″ from=”youremail@yourdomain.com”  reply-to =””>
<xapi:message to=”${C_REMITTANCE_EMAIL}” cc=”yourcc@yourdomain.com” attachment=”true”
subject=”Virtuate – Remittance Advice for Payment ${C_CHECK_NUMBER}”>

Please find attached Remittance Advice for payment ${C_CHECK_NUMBER}.

Regards,

The Payables Team

Virtuate Limited
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output=”Remit_Advice_${C_CHECK_NUMBER}” output-type=”pdf” delivery=”${C_CHECK_ID}”>
<xapi:template location=”xdo://SQLAP.XXV8_APXPBSRA.en.US/?getSource=true” filter=””></xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>

Hmm, what does all this jargon in the control file do? Well, here’s a pretty picture that explains a lot of it:

2. Make sure it all works … gotta make sure its the right flavor!

Navigate into Payables, Submit Request, XML Publisher Report Bursting Program, and specify the request from your last custom Separate Remittance Advice request.

Hey presto, take a look at your email:

Check the output:

3. (Optional) Extend the Report to automatically submit the Bursting program

Automatic Burst

Now, we don’t want to have to manually (or via request set) submit the Bursting program every time we run the report, so let’s automate that. By putting a parameter on the new Separate Remittance Advice report to control whether we Burst, and submitting a new request in the after report trigger, we can achieve this. We’ll implement this is a similar fashion to my post on Beautiful Statements in 1 Easy Step.

So lets do this:

• Add parameter P_BURST to report and concurrent program definition (Yes/No).
• Add code to after report trigger.
• declare
• v_req_id number := 0;
• begin
• if nvl(:p_burst,’N’) = ‘Y’ then
• v_req_id := xxv8_xmlp_burst_pkg.submit_request_burst(‘XXV8_APXPBSRA’,:p_conc_request_id);
• if v_req_id > 0 then
• srw.message(20002, ‘Submitted request_id ‘ || v_req_id);
• commit;
• else
• srw.message(20002, ‘Failed to submit request’);
• end if;
• end if;
• end;

• Create PL/SQL package to do the submit of Bursting Program.
• create or replace package XXV8_XMLP_BURST_PKG AUTHID CURRENT_USER AS
• function submit_request_burst
• ( p_code in varchar2
• , p_request_id in number
• ) return number;
• end XXV8_XMLP_BURST_PKG;
• /
• create or replace package body XXV8_XMLP_BURST_PKG AS
• function submit_request_burst
• ( p_code in varchar2
• , p_request_id in number
• ) return number
• is
• l_req_id number := 0;
• begin
• if p_code = ‘XXV8_APXPBSRA’ then
• l_req_id := fnd_request.submit_request(‘XDO’,’XDOBURSTREP’,NULL,NULL,FALSE,
• p_request_id);
• end if;
• return l_req_id;
• end submit_request_burst;
• end XXV8_XMLP_BURST_PKG;
• /
  • Test it all out!

Sweet, all automatic, working and ready for the primetime!

Issues

But let’s note a few issues to look out for.

1. I’m not very happy about “hardcoding” the SMTP server details in the control file. Would be great if BIP honoured either the Workflow SMTP setup or database smtp_server parameter. However, since the control file is in a structured data field in the database shouldn’t be hard to write a script to update them all. Left to a future exercise though!
2. I’m not very happy about “real” emails being delivered from Test/Development etc. environments. Would be great if BIP honoured the “Test Address” workflow parameter. Left to a future exercise, also related to issue Issue 1.
3. Resolving items higher up the XML tree seems to be an issue with bursting in that they drop out and the solution as in my post on disappearing parameters doesn’t work. Update: This functionality seems to be a known issue. Enhancement Request 6969869 has been logged for this issue. Thanks Lavina! In the meantime looks like XSLT transformation would come in handy, but that’s a separate post!
4. Minor issue that layout is not applied to the Bursting Request so no output, but can just run XML Report Publisher over it.

Bursting Engine

BI Publisher’s bursting engine accepts a data stream and splits it based on multiple criteria, generates output based on a template, then delivers the individual documents through the delivery channel of choice. The engine provides a flexible range of possibilities for document generation and delivery. Example implementations include:

• Invoice generation and delivery based on customer-specific layouts and delivery preference
• Financial reporting to generate a master report of all cost centers, bursting out individual cost center reports to the appropriate manager
• Generation of payslips to all employees based on one extract and delivered via e-mail

Usage

The bursting engine is an extension of the Document Processor Engine and has its own method be called to invoke it. The Document Processor XML structure has been extended to handle the new components required by the bursting engine. It supports all of the delivery functionality that the Document Processor supports using the same format. It accepts the XML data to be burst and a control file that takes the Document Processor XML format (see Hierarchy and Elements of the Document Processor XML File).

Control File

The control file takes the same format as the Document Processor XML with a few extensions:

• Use the attribute select under the request element to specify the element in the XML data that you wish to burst on.
• Use the attribute id under the lowest level of the delivery structure (for example, for the delivery element email, the id attribute belongs to the message element. This assigns an ID to the delivery method to be referenced later in the XML file.
• Use the delivery attribute under the document element. This assigns the delivery method for the generated document as defined in the id attribute for the delivery element. You can specify multiple delivery channels separated by a comma.
• Use the filter attribute on the template element. Use this to apply a layout template based on a filter on your XML data.

<xapi:request select="/EMPLOYEES/EMPLOYEE">

<xapi:message id="123" to="jo.smith@company.com"

<xapi:document output-type="pdf" delivery="123">

·         <xapi:template location="/usr/tmp/empGeneric.rtf">

<xapi:template location="usr\tmp\empDet.rtf" filter=".//EMPLOYEE[ENAME='SMITH']"/>

This will apply the empDet template only to those employees with the name “SMITH”. All other employees will have the empGeneric template applied. This filter can use any XPATH expression to determine the rules for the template application.