Friday 7 September 2012

Oracle Apps XML Publisher and Data Template - SQL Query to develop BI Publisher Reports

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>

5 comments:

  1. How add formula column in data template?
    Please guide.
    Sagar.sankpal@zensar.com

    ReplyDelete
    Replies
    1. Hi Sagar,

      First 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

      Delete
    2. Hi Anil,
      Really 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

      Delete
    3. Dear Sir,
      Good 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

      Delete
  2. Hi Anil,

    How add formula column in data template?

    Thanks,
    Jagan_2962424

    ReplyDelete