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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s