XML Bursting Program in Oracle Apps

XML Bursting Example

Data Template

<?xml version = '1.0' encoding = 'UTF-8'?>

<!-- $Header: DATA_TEMPLATE_ERPC.xml 115.5 2014/04/08 04:57:52 xdouser noship $ -->

<!-- dbdrv: none -->

<dataTemplate name="XXERPCBURST" defaultPackage="XXERPC_BURSTING_PKG" version="1.0">

<properties>

<property name="xml_tag_case" value="upper" />

<property name="debug_mode" value="on" />

</properties>

<parameters/>

<lexicals/>

<dataQuery>

<sqlStatement name="Q_ORDER_DETAILS">

<![CDATA[

select poh.segment1 po_number,org_id,agent_id agent_no,decode(fu.email_address,

'lavinia.stratulat@erpcircles.com','MYRANJIT@erpcircles.com',

'Erika_Varga@erpcircles.com','MYPRASAD@erpcircles.com') email

from po_headers_all poh, fnd_user fu

where fu.employee_id = poh.agent_id

AND fu.email_address IS NOT NULL

and rownum < 10

]]>

</sqlStatement>   

</dataQuery>

<!-- <dataTrigger name="beforeReportTrigger" source="APPS.XXARS_ARS_ELYRIA_COA_PKG.beforereport"/> -->

<dataStructure>

<group name="G_ORDER_DETAILS" dataType="varchar2" source="Q_ORDER_DETAILS">

<element name="po_number"     dataType="varchar2" value="po_number"/>

<element name="agent_no"      dataType="NUMBER"   value="agent_no"/>

<element name="email"         dataType="varchar2" value="email"/>

</group>

</dataStructure>

  

<dataTrigger name="afterReportTrigger" source="XXERPC_BURSTING_PKG.AFTERREPORT"/>

</dataTemplate>

Bursting Template

<?xml version="1.0" encoding="UTF-8"?>

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">

    <xapi:request select="/XXERPCBURST/LIST_G_ORDER_DETAILS/G_ORDER_DETAILS">

  <xapi:delivery>

    <xapi:email id="${PO_NUMBER}" server="xls001cn03.intranet.local" port="22"

    from="erpcircles@erpc.com" reply-to="erpcircles@erpc.com">

        <xapi:message id="${PO_NUMBER} " to="${EMAIL}" attachment="true"

          subject=" Purchase Order No: ${PO_NUMBER}">

           Please review the attached PO ${PO_NUMBER}

        </xapi:message>

        </xapi:email>

   <!--<xapi:filesystem id="FILE_DELIVERY" output="/linuxhome/myprasad/${PO_NUMBER}.pdf"/>-->

   </xapi:delivery>

  

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

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

    <xapi:template type="rtf" location="/home/myprasad/XXERPCBURST.rtf" />

 </xapi:document>

    </xapi:request>

</xapi:requestset>

Bursting Package 

CREATE OR REPLACE PACKAGE "APPS"."XXERPC_BURSTING_PKG" AS

-- Global constants

   p_header_id NUMBER;  

   p_line_id  NUMBER;

   P_ORGANIZATION_ID NUMBER;

   p_item_id  NUMBER;

   p_lot_number VARCHAR2(100);

   l_batch_clause VARCHAR2(250);

   lp_exclude VARCHAR2(100);  

-- Global exceptions

--FUNCTION BEFOREREPORT RETURN BOOLEAN;

FUNCTION AFTERREPORT RETURN BOOLEAN;

END XXERPC_BURSTING_PKG;

/

CREATE OR REPLACE PACKAGE BODY "APPS"."XXERPC_BURSTING_PKG" AS

-- Global constants

   p_header_id NUMBER;  

   p_line_id  NUMBER;

   P_ORGANIZATION_ID NUMBER;

   p_item_id  NUMBER;

   p_lot_number VARCHAR2(100);

   l_batch_clause VARCHAR2(250);

   lp_exclude VARCHAR2(100);  

-- Global exceptions

--FUNCTION BEFOREREPORT RETURN BOOLEAN;

FUNCTION AFTERREPORT RETURN BOOLEAN IS

 l_request_id      NUMBER;

      l_main_request_id NUMBER;

      l_error_msg       VARCHAR2(2000);

      i_debug           VARCHAR2(2):='Y';                 

      g_data_exists BOOLEAN := TRUE;

     

      BEGIN    

       FND_FILE.PUT_LINE(FND_FILE.log,'Checking whether the query fetched any data...');

         IF g_data_exists

         THEN

            FND_FILE.PUT_LINE(FND_FILE.log,'Query fetched the data.');

            l_main_request_id := FND_GLOBAL.CONC_REQUEST_ID;

            FND_FILE.PUT_LINE(FND_FILE.log,'Request ID: '||l_main_request_id);

            FND_FILE.PUT_LINE(FND_FILE.log,'Submitting the Bursting Program...');

            BEGIN

            l_request_id := 

            FND_REQUEST.SUBMIT_REQUEST(application   => 'XDO',

                                       program       => 'XDOBURSTREP',      

                                       description   => ' -||l_main_request_id,  

                                       start_time    => NULL,   

                                       sub_request   => FALSE,  

                                       argument1     => 'Y',

                                       argument2     => l_main_request_id,    

                                       argument3     => i_debug   

                                       );

                COMMIT;

            EXCEPTION

               WHEN OTHERS

               THEN

                  l_error_msg := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;       

                   FND_FILE.PUT_LINE(FND_FILE.log,'Error while Exception');

                  RETURN TRUE;

            END;

            IF l_request_id <> 0

            THEN

                FND_FILE.PUT_LINE(FND_FILE.log,'Bursting Program submitted successfully with request ID: '||l_request_id);

            ELSE

                FND_FILE.PUT_LINE(FND_FILE.log,'Bursting Program failed due to the error - '||l_error_msg);

            END IF;

           

            RETURN TRUE;

         ELSE

            FND_FILE.PUT_LINE(FND_FILE.log,'Report does not have any data. Hence Bursting program is not submitted.');           

           

            RETURN TRUE;

         END IF;           

          RETURN TRUE;

         END;

        

END XXERPC_BURSTING_PKG;

Sample File

  <?xml version="1.0" encoding="UTF-8" ?>

- <XXERPCBURST>

- <LIST_G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>67</PO_NUMBER>

  <AGENT_NO>327</AGENT_NO>

  <EMAIL>MYPRASAD@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>116</PO_NUMBER>

  <AGENT_NO>313</AGENT_NO>

  <EMAIL>MYRANJIT@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>129</PO_NUMBER>

  <AGENT_NO>313</AGENT_NO>

  <EMAIL>MYRANJIT@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>12</PO_NUMBER>

  <AGENT_NO>313</AGENT_NO>

  <EMAIL>MYRANJIT@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>84</PO_NUMBER>

  <AGENT_NO>313</AGENT_NO>

  <EMAIL>MYRANJIT@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>20</PO_NUMBER>

  <AGENT_NO>327</AGENT_NO>

  <EMAIL>MYPRASAD@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>21</PO_NUMBER>

  <AGENT_NO>327</AGENT_NO>

  <EMAIL>MYPRASAD@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>45</PO_NUMBER>

  <AGENT_NO>327</AGENT_NO>

  <EMAIL>MYPRASAD@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

- <G_ORDER_DETAILS>

  <PO_NUMBER>46</PO_NUMBER>

  <AGENT_NO>327</AGENT_NO>

  <EMAIL>MYPRASAD@erpcircles.com</EMAIL>

  </G_ORDER_DETAILS>

  </LIST_G_ORDER_DETAILS>

  </XXERPCBURST>