XML Publisher : Oracle XML Publisher Bursting with code

                  

                                      Oracle XML Publisher Bursting

          

           XML bursting control file use  identify deliverable channel and  find out files Destination. this control files split exist xml output into small blocks, These small block upload into files (PDF,EXCEL).

                 There are varies delivery Channel uses to deliver the file

•  FILESYSTEM --- this helps upload files into server Directory
•   SFTP             -- Helps to transfer files to Third party remote serve
•   EMAIL          --- Send file as attachment to email.

        XML Bursting Control file sample:

        1. Rule --- You always keep in mind , 

                      One thumb rule that File system id is always match with Delivery id.

                

            You  needs  design bursting control file with details as below

    

xapi:request-set   ==> define type 

xapi:request         ==>  select =<path where need split tag>

xapi:delivery       ==>   delivery Channel --(Filesystem)

xapi:document    ==>   output-type="PDF"

xapi:template      ==>   type="" and location ="xdo://<app_name>.<rtf_name>.en.00/?getSource=true"

you can find out template location using below query

Sample Bursting Control File

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

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

  <xapi:request select="/XXST_TEST/LIST_G_SUPPLIER_NAME_DETAILS/G_SUPPLIER_NAME_DETAILS">

     <xapi:delivery>

----delivery change is filesystem

---file system id is anything and output is path 

---file system id =delivery id

  <xapi:filesystem  id="filesys"  output="/home/x3062178/Cust/${SNAME}.PDF"/>

     </xapi:delivery>

     <xapi:document output-type="PDF" delivery="filesys">

       <xapi:template type="rtf" location="xdo://XXST.XXST_APSUP.en.00/?getSource=true"/>

     </xapi:document>

  </xapi:request>

 </xapi:requestset>

             We have to follow below steps to developed bursting XML output into PDF or any other format.

•   Step 1     ==>   Find out input data for XML output
•   Step 2     ==>   Convert other format data into XML format
•   Step 3     ===> Prepare bursting control file and find out delivery channel

    STEP 1   ==>Create table Xml_bursting_t 

   STEP 2   ==>Insert Data into table

   Step 3     ==>Define concurrent program with XDO executable.

   Step 4     ==>Assign program to request group --System Administrator Reports

   Step 5      ==>Create PL SQL package and define function in package,

   Step 6       ==>Create XML Data Template and called package function in after report trigger.

                         You always needs to define in trigger.

   Step 7 ==>   Define location (Directory ) in server where output file can store.

   Step 8 ==>  Create XML Bursting control file

   Step 9 ==>  Create Data Definition and XML data Template and XML Bursting file--XXST_APSUP

   Step 10 -->  Create package where called bruting program

   Step 10 ==> Run Program and check brustted file in server.

=================================================================

Sample XML Data Template

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

<!-- $Header: Sensata Supplier Delivery Note 12.2.6 2018/09/20 15:38:00 $ -->

<dataTemplate name="XXST_TEST" defaultPackage="XXST_XML_PUB_PKG" description="Sensata Supplier Report" version="1.0">

<properties>

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

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

</properties>

<lexicals>

</lexicals>

<dataQuery>

<sqlStatement name="Q_SUPPLIER_NAME_DETAILS" >

<![CDATA[

 select SNAME,SCITY from Xml_bursting_t 

 ]]>

</sqlStatement>

</dataQuery>

<dataStructure>

<group name="G_SUPPLIER_NAME_DETAILS" dataType="varchar2" source="Q_SUPPLIER_NAME_DETAILS">

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

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

 </group>

</dataStructure>

<dataTrigger name="afterReport" source="XXST_XML_PUB_PKG.SUBMIT_BURSTING_FUNC(fnd_global.conc_request_id)"/>

</dataTemplate>

===================================================

where we are calling package function where we pass request id as input parameters in package function.

Package code 

CREATE OR REPLACE PACKAGE BODY XXST_XML_PUB_PKG AS

  FUNCTION SUBMIT_BURSTING_FUNC(P_REQUEST_ID IN NUMBER) RETURN BOOLEAN AS

    L_REQ_ID NUMBER;

    L_REQUEST_ID VARCHAR2(20);

  BEGIN

  

    L_REQUEST_ID:=to_char(P_REQUEST_ID);

    L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'XDO',

                                           PROGRAM => 'XDOBURSTREP',

                                           DESCRIPTION => NULL,

                                           START_TIME => SYSDATE,

                                           SUB_REQUEST => FALSE,

                                           ARGUMENT1 => NULL,

                                           ARGUMENT2 => P_REQUEST_ID,

                                           ARGUMENT3 => 'N');

 

insert into xxst_capture_t values(L_REQ_ID);

commit;

                                          

    IF L_REQ_ID = 0 THEN

      RETURN FALSE;

    END IF;

    RETURN TRUE;

  

  EXCEPTION WHEN OTHERS THEN

      RETURN FALSE;

   END SUBMIT_BURSTING_FUNC;

END XXST_XML_PUB_PKG;

===============================================================

SAMPLE XML FILE