Sample Data template :
XML Publisher Data Template Architecture
Step 1: Design the Data Template XML File.
Step 2: Create the Data Definition in XML Publisher and associate it with Data Template
Step 3: Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report
Below is the sample Code.
<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="STANDPROFORMA" description="Yearly Standing Proforma" Version="1.0">
<parameters>
<parameter name="p_tele" dataType="character" />
<parameter name="p_preparer_name" dataType="character" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT :p_tele telephone
,:p_preparer_name preparer_name
,papf.first_name||' '||papf.last_name employee_name
,SUM(aia.invoice_amount) invoice_amount
,substr(papf.employee_number,2) invoice_num
,TO_CHAR(SYSDATE + 14,'DD-MON-YYYY') check_date
,pav.address_line1
,pav.address_line2
,pav.address_line3
,pav.town_or_city address_line4
,pav.d_country address_line5
,pav.postal_code address_line6
,TO_CHAR(SYSDATE ,'DD-MON-YYYY') letter_date
FROM ap_suppliers ass
,per_all_people_f papf
,ap_invoices_all aia
,ap_invoice_payments_all aipa
,ap_checks_all aca
,per_addresses_v pav
WHERE ass.vendor_id = aia.vendor_id
AND ass.vendor_type_lookup_code = 'EMPLOYEE'
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND DECODE(aia.earliest_settlement_date, '','PERMANENT', 'TEMPORARY') = 'PERMANENT'
AND ass.employee_id = papf.person_id
AND papf.effective_end_date >= TRUNC(SYSDATE)
AND aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND pav.person_id(+) = papf.person_id
GROUP BY papf.first_name
,papf.last_name
,papf.employee_number
,pav.address_line1
,pav.address_line2
,pav.address_line3
,pav.town_or_city
,pav.d_country
,pav.postal_code]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_STANDPROFORMA" source="Q1">
<element name="TELEPHONE" value="TELEPHONE" />
<element name="PREPARER_NAME" value="PREPARER_NAME" />
<element name="EMPLOYEE_NAME" value="EMPLOYEE_NAME" />
<element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT" />
<element name="INVOICE_NUM" value="INVOICE_NUM" />
<element name="CHECK_DATE" value="CHECK_DATE" />
<element name="ADDRESS_LINE1" value="ADDRESS_LINE1" />
<element name="ADDRESS_LINE2" value="ADDRESS_LINE2" />
<element name="ADDRESS_LINE3" value="ADDRESS_LINE3" />
<element name="ADDRESS_LINE4" value="ADDRESS_LINE4" />
<element name="ADDRESS_LINE5" value="ADDRESS_LINE5" />
<element name="ADDRESS_LINE6" value="ADDRESS_LINE6" />
<element name="LETTER_DATE" value="LETTER_DATE" />
</group>
</dataStructure>
</dataTemplate>
XML Publisher Data Template Architecture
Step 2: Create the Data Definition in XML Publisher and associate it with Data Template
Step 3: Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report
Below is the sample Code.
<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="STANDPROFORMA" description="Yearly Standing Proforma" Version="1.0">
<parameters>
<parameter name="p_tele" dataType="character" />
<parameter name="p_preparer_name" dataType="character" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT :p_tele telephone
,:p_preparer_name preparer_name
,papf.first_name||' '||papf.last_name employee_name
,SUM(aia.invoice_amount) invoice_amount
,substr(papf.employee_number,2) invoice_num
,TO_CHAR(SYSDATE + 14,'DD-MON-YYYY') check_date
,pav.address_line1
,pav.address_line2
,pav.address_line3
,pav.town_or_city address_line4
,pav.d_country address_line5
,pav.postal_code address_line6
,TO_CHAR(SYSDATE ,'DD-MON-YYYY') letter_date
FROM ap_suppliers ass
,per_all_people_f papf
,ap_invoices_all aia
,ap_invoice_payments_all aipa
,ap_checks_all aca
,per_addresses_v pav
WHERE ass.vendor_id = aia.vendor_id
AND ass.vendor_type_lookup_code = 'EMPLOYEE'
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND DECODE(aia.earliest_settlement_date, '','PERMANENT', 'TEMPORARY') = 'PERMANENT'
AND ass.employee_id = papf.person_id
AND papf.effective_end_date >= TRUNC(SYSDATE)
AND aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND pav.person_id(+) = papf.person_id
GROUP BY papf.first_name
,papf.last_name
,papf.employee_number
,pav.address_line1
,pav.address_line2
,pav.address_line3
,pav.town_or_city
,pav.d_country
,pav.postal_code]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_STANDPROFORMA" source="Q1">
<element name="TELEPHONE" value="TELEPHONE" />
<element name="PREPARER_NAME" value="PREPARER_NAME" />
<element name="EMPLOYEE_NAME" value="EMPLOYEE_NAME" />
<element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT" />
<element name="INVOICE_NUM" value="INVOICE_NUM" />
<element name="CHECK_DATE" value="CHECK_DATE" />
<element name="ADDRESS_LINE1" value="ADDRESS_LINE1" />
<element name="ADDRESS_LINE2" value="ADDRESS_LINE2" />
<element name="ADDRESS_LINE3" value="ADDRESS_LINE3" />
<element name="ADDRESS_LINE4" value="ADDRESS_LINE4" />
<element name="ADDRESS_LINE5" value="ADDRESS_LINE5" />
<element name="ADDRESS_LINE6" value="ADDRESS_LINE6" />
<element name="LETTER_DATE" value="LETTER_DATE" />
</group>
</dataStructure>
</dataTemplate>
How add formula column in data template?
ReplyDeletePlease guide.
Sagar.sankpal@zensar.com
Hi Sagar,
DeleteFirst we need to create data base package and refer the same in beforeReportTrigger.
The below example is to return vendor name when you pass vendor number.
Anil
Hi Anil,
DeleteReally You have given a very good example how to work with data templates and in the mean time can you give u a sample example using the formula columns it will be very help full and my email id is rippu.189@gmail.com or even you can post in the portal
Dear Sir,
DeleteGood Afternoon@@@
Did you get the solution how add formula column in the data template?
If you know could you please share the same to me.
Thanks
sreenivasulu
Hi Anil,
ReplyDeleteHow add formula column in data template?
Thanks,
Jagan_2962424