Search This Blog

Tuesday, April 23, 2013

XML PUBLISHER REPORT WITHOUT USING RDF


Please find the below step:

Create XML Publisher report without RDF

 1. Create a Data Definition using XML Publisher Administration Responsibility As shown below.



2. Click on Create Data Definition button and the details (Make sure you enter the concurrent program short name in Code) and click on Apply button



3. Go back to Data definition and add the Data Template file as shown below (attached sample template file for your reference , make necessary changes to template for your report





<?xml version = "1.0" encoding = "UTF-8"?>
<dataTemplate name="XXAR_CLIENT_DET_CASH_PYMT" version="1.0">
<properties>
<property name="debug_mode" value="on"/>
</properties>
 <parameters>
  </parameters>
<dataQuery>
   <sqlStatement name="Q_CLNT_DET">
       <![CDATA[
       select   distinct hca.account_number,account_name,
         hl.address4 Legacy_num,hca.attribute2 LOB,
         decode(hcsua.org_id,2,'US',22,'CAN') org_id,
         decode(hca.attribute12,'Y','CASH') Payment_Mode,
         trunc(TO_DATE(acrmv.start_date,'DD-MON-YY')) START_DATE,trunc(TO_DATE(acrmv.end_date,'DD-MON-YY')) END_DATE,
         acrm.name PAYMENT_METHOD,
         hcsua.site_use_code,
         hl.address1||','||hl.address2||','||hl.address3||','||hl.address4||','||hl.CITY||','||hl.state||','||hl.POSTAL_CODE||','||hl.country BILL_TO_ADDRESS
from
hz_locations hl,
HZ_PARTY_SITES hps,
hz_parties party,
HZ_CUST_ACCOUNTS hca,
HZ_CUST_ACCT_SITES_ALL hcasa,
HZ_CUST_SITE_USES_ALL hcsua,
HZ_CUSTOMER_PROFILES hcp,
ar_cust_receipt_methods_v acrmv,
ar_receipt_methods acrm
where
hps.location_id=hl.location_id
and party.party_id=hps.party_id
and party.party_id=hca.party_id
and hcasa.CUST_ACCOUNT_ID=hca.CUST_ACCOUNT_ID
and hcsua.CUST_ACCT_SITE_ID=hcasa.CUST_ACCT_SITE_ID
and hcp.SITE_USE_ID=hcsua.SITE_USE_ID
and hps.party_site_id=hcasa.party_site_id
and hca.cust_account_id = acrmv.customer_id
and acrmv.receipt_method_id = acrm.receipt_method_id
and hca.attribute12 = 'Y'
and hcsua.site_use_code = 'BILL_TO'
AND hcasa.BILL_TO_FLAG = 'P'
--and rownum = 1
      ]]>
   </sqlStatement>

</dataQuery>
<dataStructure>
   
         <group name="G_CLNT_DET" source="Q_CLNT_DET">
                    <element name ="ACCOUNT_NAME" value="ACCOUNT_NAME"/>
                    <element name ="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>
                    <element name ="LEGACY_NUM" value="LEGACY_NUM"/>           
                    <element name ="LOB" value="LOB"/>
                    <element name ="ORG_ID" value="ORG_ID"/>
                    <element name ="PAYMENT_MODE" value="PAYMENT_MODE"/>
                    <element name ="START_DATE" value="START_DATE"/>
                    <element name ="END_DATE" value="END_DATE"/>
                    <element name ="PAYMENT_METHOD" value="PAYMENT_METHOD"/>
                    <element name ="SITE_USE_CODE" value="SITE_USE_CODE"/>
                    <element name ="BILL_TO_ADDRESS" value="BILL_TO_ADDRESS"/>
                                     
   </group>
</dataStructure>
</dataTemplate>








4. Once you create the Data definition, please create the respective concurrent program for this report if one does not already exists. Make sure the execute for this concurrent program should XDODTEXE






5.    Make sure to enter the Token in the parameters screen. Also noted that this token should match to your parameters name in Data Template file.








6.    Once finished creation of this, please go ahead and create Template for this concurrent program using xml file generated from output of this concurrent request.

No comments:

Post a Comment