Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure’

 

1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));

2. Insert Values

INSERT INTO demo_products
     VALUES (100, ‘TEST DATA’);
3.Commit

1. Create a Package Spec & Body with a single Procedure

Spec:

CREATE OR REPLACE PACKAGE APPS.XX_REPORT_PKG

AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);
END MY_PACKAGE;

 

Body:

CREATE OR REPLACE PACKAGE BODY APPS.XX_REPORT_PKG
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32000);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := ‘SELECT product_code, product_name
         FROM demo_products
       WHERE product_code = ‘ || p_product_id;

      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      — set rowset tag to PRODUCTS and row tag to PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, ‘PRODUCTS’);
      DBMS_XMLGEN.setRowTag (l_qryCtx, ‘PRO_DETAILS’);

      — now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, ‘No of rows processed= ‘ || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, ‘Length= ‘ || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length – l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, ‘Error in procedure XX_REPORT_PKG.report’);
   END REPORT;
END XX_REPORT_PKG;

2. Define Executable

Navigation: Application Developer > Concurrent > Executable

Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: XX_REPORT_PKG.REPORT

3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program

Provide Program, Short name, Application, Description & also
– Executable Name as defined in the above step

– Output Format should be XML
– Define a Parameter p_product_id
– Associate Concurrent Program to the Request Group.

4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

RTF can be created using the XML file from the Output of the concurrent Program.


6. Run the Concurrent Program to see the output

Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure

 

Advertisements

XML Bursting- Mails to multiple mail ids

Mails can be sent to multiple users by separating the mail ids using comas.

Following is a sample Bursting file…

<?xml version=”1.0″ encoding=”UTF-8″ ?>

<xapi:requestset xmlns:xapi=”http://xmlns.oracle.com/oxp/xapi” type=”bursting“>

<xapi:request select=”/MPLNRGPR“>

 <xapi:delivery>

<xapi:email server=”172.20.7.8” port=”25” from=”PQR@XXX.COM” reply-to=””>

  <xapi:message id=”123” to=”ABC@XXX.com,XYZ@XX.com” attachment=”true” subject=”Busting Report“>FYI</xapi:message>

  </xapi:email>

  </xapi:delivery>

<xapi:document output=”NRGP” output-type=”pdf” delivery=”123“>

  <xapi:template type=”rtf” location=”xdo://JA.MPLNRGPR.en.00/?getSource=true” filter=”” />

  </xapi:document>

  </xapi:request>

  </xapi:requestset>

Note :If the address has to be passed dynamically from the tags then at the rdf level concatenate the mail id with a  comma (,) at the mail id with out any space.

XML Bursting

Oracle Bursting Mechanism enables us to deliver a single XML Publisher report/letter to multiple destinations simultaneously. This is a very helpful feature especially if you are trying to send the output of a report to an email address or multiple email address.The output is sent via email. User does not need to login to Oracle to get the report.

Picture1

Pre-requisite

1.SMTP configuration

2.Change the temporary directory location.

  • A temporary directory has to be provided for XML Publisher engine to use for bursting.
  • Responsibility: XML Publisher Administrator
  • Navigation: Administration tab
Picture2

3. Attach Seeded Program to the Responsibility: XML Publisher Report Bursting Program

The following Bursting File needs to be attached to the Data Definition of the BI Publisher report that you are trying to burst :

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<xapi:requestset xmlns:xapi=”http://xmlns.oracle.com/oxp/xapi”&gt;

<xapi:request select=”/PERSON/LIST_G_FIRST_NAME”>

<xapi:delivery>

<xapi:email server=”ATANML01.at.adsonydadc.com” port=”25″ from=“XYZ@XXX.com” reply-to =“ABC@XXX.com”>

<xapi:message id=”123″ to=“${EMAIL}” attachment=”true” subject=“Busting Report”>

Busting Report Body

<!–xapi:message>

<!–xapi:email>

<!–xapi:delivery>

<xapi:document output=”${EMPLOYEE_NUMBER}” output-type=”excel” delivery=”123″>

<xapi:template type=”rtf” location=”xdo://PER.XX_PERSON.en.00/?getSource=true” filter=””>

<!–xapi:document>

<!–xapi:request>

<!–xapi:requestset>

Explanation of the bursting template

XML Tags

Explanation

xapi:request select= This tag in the control file indicates from which tag/node in the concurrent program output XML should separated for bursting
xapi:delivery This node contains all email related attributes
xapi:email This tag contains the email attributes like,server or the email server IP or nameport or the email server portfrom or the email address from which the server should send the emailreply-to or the email address to which the replies should be sent
xapi:message This tag contains the attributes about the email like,To or the email address where the email should be sentSubject or the email subjectattachment , i.e. if the email should contain an attachmentThe mail body will be enclosed within the tags
xapi:document This node contains the attachment document and XML template related tags and attributes.The attributes in this tag are related to the email attachmentOutput or attachment file nameOutput-type or attachment file type
xapi:template The node contains the template information like,Type or the file type of the template

Attach the Bursting File to Data Definition
Responsibility: XML Publisher Administrator
Navigation: Data Definition

XMLBursting021

The bursting process is now complete.
Automatic execution of XML Publisher Report Bursting Program
Since the bursting program, XML Publisher Report Bursting Program, has to be run manually this might not be feasible for all users to use. The bursting program can be executed automatically if we use a rdf report to generate the data for the XML publisher report. In a rdf report we can add the following piece of code in the After Report trigger in the report to execute the bursting program as soon as the output has been generated by the report.

function AfterReport return boolean is

req_id NUMBER;

err varchar2(4000);

BEGIN

SRW.USER_EXIT(‘FND SRWEXIT’);

begin

req_id := fnd_request.submit_request (‘XDO’, — application

‘XDOBURSTREP’,– program short name

”, — description

”, — start time

FALSE, — sub request

‘N’, — argument1

: P_CONC_REQUEST_ID, — argument2

‘N’, — argument3

NULL, — argument4

NULL, — argument5

NULL, — argument6

CHR (0) — represents end of arguments

) ;

exception when others then

err:= sqlerrm;

srw.message(100,’exception ‘ || err);

end ;

if req_id = 0 then

srw.message(101,’failed to submit bursting program ‘);

else

srw.message(102,’request id for bursting program is ‘||req_id);

end if;

return (TRUE);

END;