Tuesday, August 23, 2016

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"







No comments:

Post a Comment