BURSTING PROGRAM FOR PO_INV_AMT

USING DATA DEFINITION TEMPLATE IN XML PUBLISHER

Create Concurrent Program to generate the Data XML output.
Navigation: System Administrator-> Concurrent-> Program->Define

AND THEIR EXECUTABLE NAME AS "XDODTEXE"

Concurrent_Program_Name::          JM 820 DSV PO TXN AMT

Concurrent_Program_short_name:: POTNXAMT

Type :: XML

Assignment of concurrent program to request Group (RESPONSIBILITY)

Navigation: System Administrator-> Security-> Responsibility->Request

-----------------------------------------------------------------------------------------------------------------------

Create the data definition template

<?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name="POINVAMT" description="POINVAMT" defaultPackage="AB_BURSTING_CP_SUB_PKG"   Version="1.0">

<dataQuery>

        

 <sqlStatement name="PO_INV_AMT">

  <![CDATA[

  

select distinct rct.purchase_order,rct.trx_number,sum(b.extended_amount) AMT

from ra_customers ac,

ra_customer_trx_all rct,

ra_customer_trx_lines_all b

where ac.customer_id=rct.sold_to_customer_id(+) 

and rct.customer_trx_id = b.customer_trx_id

and purchase_order in(select ltrim(rtrim(po)) from jm_wal_855)

group by rct.purchase_order,rct.trx_number

order by purchase_order 

]]>

</sqlStatement>

</dataQuery>

<dataStructure>

  

<group name="G_PO_INV_AMT" source="PO_INV_AMT">

<element name="purchase_order"   value="purchase_order" />

<element name="trx_number"   value="trx_number" />

<element name="AMT"   value="AMT" />

 </group>

</dataStructure>

<dataTrigger name="afterReport" source="JM_BURSTING_CP_SUB_PKG.AfterReport()"/>

</dataTemplate>

Save the file with name with .xml extension

and attach the file to data defination in xml pulblisher

**************************************************************************

Create data definition in xml publisher and attach the data definition template to the data definition

Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition

************************************************************************

Creation of RTF from MSWORD. by using Genarated xml tags in datadefination

attach the RTF to the program from "data template"

Creation of Control file for bursting.

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

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

<xapi:globalData location="stream" />

<xapi:request select="/POINVAMT/LIST_G_PO_INV_AMT">

<xapi:delivery>

<xapi:email id="123" server="localhost" port="25" from="xxxx@gmail.com" reply-to="xxxx@gmail.com">

<xapi:message id="123" to="xxxx@gmail.com" cc="xxxx@gmail.com"

attachment="true" content-type="html/text" subject="Po INVENTORY AMOUNT">

Hello,

Please find the attached PO_INV_AMT spread sheet.

Thanks,

xxxxx

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document output="PO_INV_AMT.xls" output-type="excel" delivery="123">

<xapi:template type="rtf" location="/var/tmp/TemplateBuilder.rtf" />

</xapi:document>

</xapi:request>

</xapi:requestset>

save the file with .xml extension

Attach the control file to Data Defination "Bursting Control File"