XML Bursting in Oracle Apps

Hello ,

Please go through the description of XML bursting , means how to send mail to each person .
For example : Send PO related invoice to every supplier

XML Bursting

XML Bursting can be used to split one XML File into multiple XML Blocks. These individual xml blocks can then be used to generate reports and even use different layouts. You can also deliver the reports to multiple destinations based on a XML Element. The steps involved are listed below for your understanding:

• Generate the XML File
• Split the XML into multiple XML Blocks
• Generate Report based on the individual XML Blocks
• Deliver the report

Take a look at the below image and the process will be much more clearer:

XML Bursting Process

Bursting Control File

Bursting control is used to identify:

• How to split the XML file / data
• How to deliver the Report
• Delivery destination details

After defining the XML Bursting Control File, we will upload the same to the Data Definition. But before that, lets take a look at the XML file that we have.

<?xml version="1.0" encoding="UTF-8"?> <INVOICEDATA>   <LIST_G_SUPPLIER>     <G_SUPPLIER>       <SUPPLIER_NUMBER>1001</SUPPLIER_NUMBER>       <SUPPLIER_NAME>VENDOR001</SUPPLIER_NAME>       <SUPPLIER_EMAIL>contact@vendor1.com </SUPPLIER_EMAIL>       <LIST_G_INV>         <G_INV>          <INV_NUMBER>INVOICE_01_001</INV_NUMBER>          <CURRENCY_CODE>USD</CURRENCY_CODE>          <AMOUNT>100.27</AMOUNT>         <G/G_INV>       </LIST_G_INV>     </G_SUPPLIER>     <G_SUPPLIER>       <SUPPLIER_NUMBER>1002</SUPPLIER_NUMBER>       <SUPPLIER_NAME>VENDOR002</SUPPLIER_NAME>       <SUPPLIER_EMAIL>contact@vendor2.com</SUPPLIER_EMAIL>       <LIST_G_INV>         <G_INV>           <INV_NUMBER>INVOICE_02_001</INV_NUMBER>           <CURRENCY_CODE>USD</CURRENCY_CODE>           <AMOUNT>612.99</AMOUNT>         </G_INV>       </LIST_G_INV>     </G_SUPPLIER>   </LIST_G_SUPPLIER> </INVOICEDATA>

As you can see the above XML file has details of two Suppliers (VENDOR001 and VENDOR002). I want the invoice details of VENDOR001 to be emailed to contact@vendor1.com and invoice details of VENDOR002 to be emailed to contact@vendor2.com. In this case, I know that I have to split the XML File into two.

The first thing that we need to do is to identify the Level at which the file has to be split, so that we get two different XML files. If you take a closer look at the XML file, you will see that the <G_SUPPLIER> … </G_SUPPLIER> group is repeated twice. So to get two different XML Files, we will split the actual file at /INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIERLevel. The result is the below XML Files.

XML Files After Splitting

We also know that we need to deliver these reports by email. We will use the G_SUPPLIER/SUPPLIER_EMAIL element to get the email id from the XML file. Now let us take a look at the Bursting Control File.

<?xml version="1.0" encoding="utf-8"?> <xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">   <xapi:request select="/INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER">      <xapi:delivery>        <xapi:email id="${SUPPLIER_NUMBER}" reply-to="ap@quest4apps.com" from="ap@quest4apps.com" port="25" server="ora.q4apps.us">          <xapi:message subject="Invoice Details" attachment="true" to="${SUPPLIER_EMAIL}" id="${SUPPLIER_NUMBER}">           Please find your electronically formatted Invoice Details.          </xapi:message>        </xapi:email>      </xapi:delivery>      <xapi:document delivery="${SUPPLIER_NUMBER}" output-type="pdf" output="/data/test1/interfaces/out/${SUPPLIER_NUMBER}.pdf">        <xapi:template type="rtf" location="xdo://Q4A.XXQ4ASUPINV.en.00/?getSource=true" />      </xapi:document>   </xapi:request> </xapi:requestset>

Please find below details of the XML tags.

xapi:request: Give details of how you want to split the XML File

xapi:email: Give details of the Email

• id: This is a unique value that identifies each group. In our case it is the Supplier Number.
• reply-to: This is the reply to email address
• from: This is the from email address that will be used while sending the email
• port: The port number of the email server. Check with your DBA / System Administrator for this value.
• server: The details of the email server

xapi:message: Give details of the Email Message

• subject: Subject of the Email
• attachment: We are attaching the report output with the email. So the value should be “true”.
• to: The email id of the Supplier, which is the recipient’s email address.
• id: This is a unique value that identifies each group. In our case it is the Supplier Number.

xapi:document: Here we define the output document details

• output-type: The output type of the report output.
• output: The folder in which the output file will be saved.

xapi:template: Give details of the RTF Template

• type: Give the details of the Template Type. In our case it is RTF
• location: Location of the RTF Template.

Lets take a closer look at the location:

location="xdo://Q4A.XXQ4ASUPINV.en.00/?getSource=true"

For simplicity the above statement can be broken down as

location="xdo://Application Short Name.Template Code.Default Language.Default Territory/?getSource=true"

And you can use the below SQL Statement to get the details of the Template.

select   xtb.application_short_name||'.'||          xtb.template_code ||'.'||          xtb.default_language ||'.'||          xtb.default_territory   from   apps.xdo_templates_b xtb  where   xtb.template_code ='<Template Code>';

Uploading the Bursting Control File

Once the bursting control file has been defined, you need to attach the same to the Data Definition. Query for the Data Definition and open the defintion. And then click on “Add File” button and upload the XML file that we have defined. Save the changes and you are all set to test the process.

Check the below image for more details:

Submitting the Bursting Process

Attaching the XML Bursting Control file will not burst the report just yet. You need to submit the “XML Publisher Report Bursting Program” Concurrent program after the XML Publisher Report is completed. You can either manually Submit the program or use the below SQL Code Snippet in the After Report Trigger to submit the bursting program once the XML Publisher Report is completed.

DECLARE    l_conc_id     NUMBER;    g_request_id  NUMBER; BEGIN    l_conc_id :=    fnd_request.submit_request            (              application   => 'XDO'             ,program       => 'XDOBURSTREP'             ,description   => NULL             ,start_time    => SYSDATE             ,sub_request   => FALSE             ,argument1     => NULL                      ,argument2     => g_request_id  -– Request ID of XML Publisher Report             ,argument3     => 'Y'           -– debug Flag            );      COMMIT; END;

In