XML Bursting with Data Templates

This post explains on how to use the XML Bursting feature with Data Templates. This needs the below steps to be done.

1. Create Package and Package body to define trigger information. This will define when the code will be called from the Data template.

2. Make sure to include the package name, created in step1 above in the "DefaultPackage" section of the data template

3. After report trigger will be the new section in the Data Template. This will have reference to the package created in step1.

Sample code:

CREATE OR REPLACE PACKAGE APPS.XX_BURST_PKG
AS
   p_from_date    DATE;
   P_TO_DATE      DATE;
   P_REQUEST_ID   VARCHAR2 (15);
   P_CONC_REQUEST_ID   NUMBER ;

   FUNCTION AfterReport
      RETURN BOOLEAN;
   FUNCTION AfterReport (p_request_id NUMBER)
      RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXGSI_BURST_PKG
AS
   FUNCTION AfterReport
      RETURN BOOLEAN
   IS
      l_request_id   NUMBER;
   BEGIN
      P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
      Fnd_File.PUT_LINE (
         Fnd_File.LOG,
         'Submitting : XML Publisher Report Bursting Program');
      l_request_id :=
         FND_REQUEST.SUBMIT_REQUEST ('XDO',
                                     'XDOBURSTREP',
                                     NULL,
                                     NULL,
                                     FALSE,
                                     'Y',
                                     P_CONC_REQUEST_ID,
                                     'Y');
      Fnd_File.PUT_LINE (Fnd_File.LOG, ' ');
      COMMIT;

      RETURN (TRUE);
   EXCEPTION
      WHEN OTHERS
      THEN
         Fnd_File.PUT_LINE (Fnd_File.LOG,
                            'Unable to submit request ' || SQLERRM);
         RAISE;
   END AfterReport;
  
FUNCTION AfterReport (p_request_id NUMBER)
      RETURN BOOLEAN
   IS
      l_request_id   NUMBER;
   BEGIN
      P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
      Fnd_File.PUT_LINE (
         Fnd_File.LOG,
         'Submitting : XML Publisher Report Bursting Program');
      l_request_id :=
         FND_REQUEST.SUBMIT_REQUEST ('XDO',
                                     'XDOBURSTREP',
                                     NULL,
                                     NULL,
                                     FALSE,
                                     'Y',
                                     p_request_id,
                                     'Y');
      Fnd_File.PUT_LINE (Fnd_File.LOG, ' ');
      COMMIT;

      RETURN (TRUE);
   EXCEPTION
      WHEN OTHERS
      THEN
         Fnd_File.PUT_LINE (Fnd_File.LOG,
                            'Unable to submit request ' || SQLERRM);
         RAISE;
   END AfterReport;  
END;
/
Data Template -
The below are the 2 lines that will be different from the traditional data template that doesn't use triggers defined in a Package.

<dataTemplate name = "XXGSI_SEND_EMAIL_DT" defaultPackage = "XXGSI_BURST_PKG" version = "1.0">
<dataTrigger name="afterReport" source="XXGSI_BURST_PKG.After_report"/>

<!-- $Header: DATA_TEMPLATE_XXGSI_SEND_EMAIL.xml 115.1 2013/01/15 17:15:36 xdouser noship $ -->
<!-- dbdrv: none -->
<dataTemplate name = "XXGSI_SEND_EMAIL_DT" defaultPackage = "XXGSI_BURST_PKG" version = "1.0">
  <parameters>
  <parameter name="P_REQUEST_ID" dataType="character" />
  <parameter name="P_FROM_DATE" dataType="date"/>
  <parameter name="P_TO_DATE" dataType="date"/>
  </parameters>
  <properties>
  <property name="debug_mode" value="on" />
  </properties>
    <dataQuery>
        <sqlStatement name = "Q1">
            <![CDATA[
              query here
            ]]>
        </sqlStatement>
    </dataQuery>
    <dataStructure>
        <group name = "TRANSACTIONS" source = "Q1">
            <element name = "TRX_NUMBER" value = "TRX_NUMBER"/>
            <element name = "AMOUNT_DUE_ORIGINAL" value = "AMOUNT_DUE_ORIGINAL"/>
            <element name = "AMOUNT_DUE_REMAINING" value = "AMOUNT_DUE_REMAINING"/>
            <element name = "ORDER_NUMBER" value = "ORDER_NUMBER"/>
        </group>
    </dataStructure>
    <dataTrigger name="afterReport" source="XXGSI_BURST_PKG.After_report"/>
</dataTemplate>


Bursting file

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request select="/XX_SEND_EMAIL_DT/LIST_TRANSACTIONS/TRANSACTIONS">
<xapi:delivery>
<xapi:email server="<Email Server Name>" port="25" from="xxx@xxx.com"  reply-to ="xxx@xxx.com">
<xapi:message id="${TRX_NUMBER}" to="xxxxx@gmail.com" attachment="true"
subject="Your Invoice# ${TRX_NUMBER} ">
Dear ,

Please find your attached invoice <B> #${TRX_NUMBER} </B>


Regards,

Accounts Receivable
Email: xxx@xxx.com

</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output-type="pdf" delivery="${TRX_NUMBER}" output="Guidance_Software_Invoice_${TRX_NUMBER}">
<xapi:template type="rtf" location="xdo://XX.XX_SEND_EMAIL.en.US/?getSource=true" translation=""></xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>