Technical‎ > ‎BI Publisher‎ > ‎

Oracle Applications - Creating a BI Publisher report for pl/sql concurrent program using XMLFOREST and XMLELEMENT


You might have a requirement in Oracle Application development whereby you do a lot of processing in a pl/sql based concurrent program. Once the processing is done you might need to publish the output as a PDF/XL document based on queries. This article deals with one of the possible approaches to do this.

Let us assume you have a global temporary table A from which you need to take the output and publish it.

You can write a query in a cursor as :
      cursor C as
      SELECT xmlelement("XX_TOP_ELEMENT",
                        xmlforest(colA , 
                                  colB, 
                                  nvl(colC,-1)as colC
                        ) || '' AS "RESULT"
        FROM tableA;
This will create a top level element as XX_TOP_ELEMENT with children defined by colA , colB and colC.

In your plsql code loop through cursor C and print the output to the outfile of the concurrent program as:
    FOR rec IN C
    LOOP
      fnd_file.put_line(fnd_file.output,
                            rec.result);
    END LOOP;
Please make sure that you do not write any non xml content to your output file.

This will ensure that the output file is a valid xml file.

As a final act you need to launch the XML Publisher report for the current concurrent program using the following piece of code:
    l_req_id   := fnd_request.submit_request('XDO',
                                             'XDOREPPB',
                                             NULL,
                                             NULL,
                                             FALSE,
                                             'Y',
                                             fnd_global.conc_request_id,
                                             20003, -- xx
                                             p_template_name, -- TEMPLATE
                                             NULL,
                                             'en-US', -- English
                                             'RTF',
                                             'EXCEL');
This will launch the XML Publisher Report concurrent program which takes the output file of the current pl/sql concurrent program and applies a template to it to produce the desired output.










Free Hit Counters