XML Publisher Report with PLSQL as Data Source and Known Issues

It is a very old topic but still find it interesting. Few days ago one of my good friend was asking about this, so I thought why not write an article on it?

I generally use this method when I have a single group for my report.

Step-by-step instruction to create a XML report using pl/sql package.

1> Prepare your query based on your business requirement.
2> Write a pl/sql packaged procedure to generate XML data.
3> Register the packaged procedure in Test application (Ex. Concurrent program short code : XXSAMXML).
4> Add to the respective request group.
5> Run the report and save the output as sample.xml
6> Create a rtf template (sample.rtf) on MS Word based on sample.xml (using BI Publisher Desktop)
 
BI Publisher Desktop, a client side tool which consits of a plugin to MS Word for building of RTF templates and the Template Viewer that can help you to test and debug all BI Publisher templates.

   > Check if BI Publisher Desktop patch has been installed on your machine, if not you can download it from here respective to you desktop machine

     http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html

   > After installing this patch a new option with BIpublisher/'Template Builder' will appear in MS Word toolbar.

KNOWN ISSUES:
     > Sometimes it does not, before MS 2010, what you can do is,
       Goto Tools > Templates --> Add-Ins
           > Check the TemplateBuilder.dot in Global templates and Add-ins
     > One more way to do
       Goto Start > All Programs > Oracle BI Publisher Desktop > Template Viewer
           > Working directory would be the directory where your xml file and template is there
             > Select the data and Template and 'start Processing'

7> Registering this template in application.
   > Create Data Definition
     > Go to 'XML Publisher Administrator' > Data Definitions > Create Data definition
       Imp:- Code must be same as the Concurrent Program Short name as XXSAMXML
   > Create Template
     > Click on 'Template' Tab and create the same.
       > Browse and Add the previously created rtf (sample.rtf) as Template File.

KNOWN ISSUES:
1> 'XML Publisher Administrator' not a valid responsibility for the current user. Please contact your System Administrator
        > Change your browser, IE to Mozila or Crome
        > Logon to Functional Administrator responsibility
        > Goto Core services > Caching Framework > Global configuration > Clear All Cache
          > It should solve your issue.

2> "You are trying to access a page that is no longer active. - The referring page may have come  from a previous session. Please select Home to proceed."
        > Avoid using TAB key and use the flashlight icon to open up the List of Values.

3> Excel issue while opening the output file.(Only if you have selected output format as Excel in Data Definition)
        "The file you are trying to open xxx.xlsx is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now?"
        > Goto Start > Run > regedit
        > In registry --> HKEY_CURRENT_USER > SOFTWARE > MICROSOFT > OFFICE > 12.0/14.0 > EXCEL > SECURITY
          > Right click in the right window and choose New -> DWORD with name 'ExtensionHardening' and Value '0'.

Sample Packaged Procedure

1> Package Specification

CREATE OR REPLACE package apps.xx_sample_pkg
as
--//============================================================================
--//
--// object name         :: xx_sample_pkg
--//
--// object type         :: package specification
--//
--// parameters          :: n/a
--//
--// object description  :: This package contains all the procedures and
--//                        functions used for demostrating xml pl/sql report
--//============================================================================
--// vers     author              date               description
--//============================================================================
--// 1.0      Abhay Kumar         24/10/2012         initial build
--//============================================================================


    procedure xx_sample_proc (
                                     p_errbuf    out varchar2
                                    ,p_retcode   out number
                                    ,p_due_date  in  varchar2
                                 );   

end xx_sample_pkg;
/

2> Package Body

CREATE OR REPLACE package body APPS.xx_sample_pkg
as
--//============================================================================
--//
--// object name         :: xx_sample_pkg
--//
--// object type         :: package Body
--//
--// parameters          :: n/a
--//
--// object description  :: This package contains all the procedures and
--//                        functions used for demostrating xml pl/sql report
--//============================================================================
--// vers     author              date               description
--//============================================================================
--// 1.0      Abhay Kumar         24/10/2012         initial build
--//============================================================================

-------------------------------------------------------------------------------------
-- procedure: log_message
-- write message to concurrent log file
-------------------------------------------------------------------------------------
procedure log_message (p_message_in in varchar2)
is
begin
  apps.fnd_file.put_line (apps.fnd_file.log, p_message_in || '.');
end log_message;

-- -------------------------------------------------
-- procedure generate_xml
-- procedure to generate xml from the data in a clob
-- field
-- -------------------------------------------------
procedure generate_xml
                        (
                            p_ref_cur                       in      sys_refcursor,
                            p_row_tag                       in      varchar2,
                            p_row_set_tag                   in      varchar2,
                            x_xml_data                      out     nocopy clob
                        )
is
    l_ctx   dbms_xmlgen.ctxhandle;
begin

    -- create a new context with the sql query
    l_ctx := dbms_xmlgen.newcontext (p_ref_cur);

    -- add tag names for rows and row sets
    dbms_xmlgen.setrowsettag(l_ctx, p_row_tag);
    dbms_xmlgen.setrowtag(l_ctx, p_row_set_tag);

    -- generate xml data
    x_xml_data := dbms_xmlgen.getxml (l_ctx);

    dbms_xmlgen.closecontext(l_ctx);

exception
    when others then
        log_message('Error generating XML ');
        log_message(sqlerrm);
end generate_xml;

-- ----------------------------------------------
-- procedure print_xml_data
-- procedure to print xml data
-- ----------------------------------------------
procedure print_xml_data (p_xml_data in clob) is

      l_amount                        number;
      l_offset                        number;
      l_length                        number;
      l_data                          varchar2(32767);

begin

    l_length := nvl(dbms_lob.getlength(p_xml_data),0);
    l_offset := 1;
    l_amount := 16000;

    loop

        exit when l_length <=0;

        dbms_lob.read(p_xml_data, l_amount, l_offset, l_data);

        apps.fnd_file.put(apps.fnd_file.output, l_data);

        l_length := l_length - l_amount;
        l_offset := l_offset + l_amount;

    end loop;

exception
    when others then
        log_message('Unexpected Error printing XML Output ');
        log_message(sqlerrm);

end print_xml_data;

-------------------------------------------------------------------------------------
-- procedure: xx_sample_proc
-------------------------------------------------------------------------------------
procedure xx_sample_proc (
                                 p_errbuf    out varchar2
                                ,p_retcode   out number
                                ,p_due_date  in  varchar2
                             )
is

    ----------------------------------------------------------------------------------
    -- local variables
    ----------------------------------------------------------------------------------

    v_due_date        date;
    lv_date_to        date;
    lv_xml_data       clob;
    l_data_qry_cur    sys_refcursor;
    l_row_tag         varchar2(4000);
    l_row_set_tag     varchar2(4000);
    l_task_from       varchar2(3);
    l_task_to         varchar2(3);

begin
        l_data_qry_cur := null;

        ---------------------------------------------------------------
        if p_due_date is not null then
           begin           
              select to_date(p_due_date,'YYYY/MM/DD HH24:MI:SS')
                into v_due_date
                from dual;
           exception
              when others then
                 log_message('Error while converting Dates. '||sqlerrm);
           end;
        end if;
       
        log_message('From Date : '||v_due_date);
       
        --------------------------------------------------------------------------------------------------------------
        -- Data Query
        ---------------------------------------------------------------------------------------------------------------
        open l_data_qry_cur for
                              'select invoice_num invoice_number,vendor_name supplier_name,segment1 supplier_number,vendor_site_code supplier_site,name operating_unit, invoice_date, due_date,cancelled_date,
                                      invoice_amount,amount_paid,amount_remaining, invoice_amount,amount_remaining,Message
                                 from
                                     (select i.invoice_num,v.vendor_name,v.segment1,vs.vendor_site_code,hu.name,to_char(i.invoice_date,''DD/MM/YYYY'') invoice_date,to_char(ps.due_date,''DD/MM/YYYY'') due_date,to_char(i.cancelled_date,''DD/MM/YYYY'') cancelled_date,
                                             i.invoice_amount,i.amount_paid,ps.amount_remaining, SUM(i.invoice_amount),sum(ps.amount_remaining),''Bank Details Are Missing On Invoice'' Message
                                        from ap_payment_schedules_all ps,
                                              ap_invoices_all i,
                                              ap_suppliers v,
                                              ap_supplier_sites_all vs,
                                              hr_operating_units hu
                                       where  1=1
                                         and  i.invoice_id = ps.invoice_id
                                         and  i.vendor_id = v.vendor_id
                                         and  i.vendor_site_id = vs.vendor_site_id
                                         and  i.payment_status_flag =''N''
                                         and  hu.organization_id = vs.org_id
                                         and  (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
                                         and  trunc(due_date) <= trunc(nvl(:v_due_date,due_date))
                                         and  i.external_bank_account_id is null
                                         and  exists (select ''Account Exixts''
                                                       from apps.iby_account_owners ao
                                                      where ao.account_owner_party_id = v.party_id)
                                       group by v.vendor_name,v.segment1,vs.vendor_site_code,hu.name,i.invoice_num,i.invoice_date,ps.due_date,i.invoice_amount,i.amount_paid,ps.amount_remaining,i.cancelled_date  
                                       )
                                    order by due_date'
                                using  v_due_date,v_due_date,v_due_date,v_due_date ;

        -------------------------------------------------------------------
       
        l_row_tag := 'XXAPINVOICE';           --> Main Group Name
        l_row_set_tag := 'XXNOBANK_DETAILS';  --> Inner Group Name

        log_message('Call generate_xml procedure');

        generate_xml(l_data_qry_cur, l_row_tag, l_row_set_tag, lv_xml_data);

        log_message('Call print_xml_data procedure');

        print_xml_data (lv_xml_data);

        log_message('End of Processing');

        p_errbuf := 'Program has completed successfully';
        p_retcode := 0;
        -------------------------------------------------------------------
exception
    when others then
        log_message('Error in ap_no_bank_report. '||sqlerrm);
        p_errbuf := substr (ltrim (sqlerrm), 1, 254 );
        p_retcode := 2;
end xx_sample_proc;
  
end xx_sample_pkg;
/

You may like another related post XML Publisher Report with Data Template as Data Source and Known Issues

>> Biru it is for you :)