Developing XML/BI Publisher report based on PL/SQL Stored Procedure in Oracle Apps

Steps for Developing XML/BI Publisher report based on PL/SQL Stored Procedure

1.  Develop PL/SQL program in DEV instance of Oracle Apps as per user requirements by using SQL*Plus or TOAD or any other Oracle database Client

Example:

Package Name: XXJS_PLSQL_XML_PKG

CREATE OR REPLACE PACKAGE xxjs_plsql_xml_pkg

AS

   PROCEDURE xxjs_main_proc (x_errbuf       OUT VARCHAR2, x_retcode      OUT NUMBER);

END xxjs_plsql_xml_pkg;

/

CREATE OR REPLACE PACKAGE BODY xxjs_plsql_xml_pkg

AS

   PROCEDURE xxjs_main_proc (x_errbuf       OUT VARCHAR2, x_retcode      OUT NUMBER)

   IS

      l_qryCtx    DBMS_XMLGEN.ctxHandle;

      l_result    CLOB;

      l_no_rows   NUMBER;

   BEGIN

      FND_FILE.put_line (FND_FILE.LOG, 'XML Generating Started ..................................');

      l_qryCtx :=DBMS_XMLGEN.newContext (

            'SELECT * FROM all_objects WHERE object_type=''TABLE'' AND object_name LIKE ''GL_JE%''');

      LOOP

         -- save the XML into the CLOB field

         l_result := DBMS_XMLGEN.getXML (l_qryCtx);

         EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed (l_qryCtx) = 0;

         -- store the XML as an output

         fnd_file.put_line (fnd_file.output, l_result);

      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);

      FND_FILE.put_line (FND_FILE.LOG, 'XML Generating End ..................................');

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Errors: ' || SQLERRM);

   END xxjs_main_proc;

END xxjs_plsql_xml_pkg;

/

2.  Create “Executable” for the report (XXJSPLSXMLREP)

Navigation: System Administrator or Application Development > Concurrent > Executable

Executable: XXJSPLSXMLREP

Short Name: XXJSPLSXMLREP

Application: XXJS Custom Application

Description: My Custom Report Using PL/SQL & BI Publisher

Executable Method: PL/SQL Stored Procedure

Executable File Name: XXJS_PLSQL_XML_PKG.XXJS_MAIN_PROC

3.  Create “Concurrent Program” and attach this executable to this concurrent program and define parameters if required

Navigation: System Administrator or Application Development > Concurrent > Program

Program: My Custom Report Using PL/SQL & BI Publisher

Short Name: XXJSPLSXMLREP

Application: XXJS Custom Application

Description: My Custom Report Using PL/SQL

Executable Name: XXJSPLSXMLREP

Output Format: XML

4.  Create “Request Group” or Attach Concurrent Program to existing Request Group

Navigation: System Administrator > Security > Responsibility > Request

Search Request Group (Ex. Applications: XXJS Custom Application) and Add created concurrent program on Name

5.  Log in to Oracle Apps by your user

Navigation: XXJS Custom Application > View > Request

Submit New Request for concurrent program "My Custom Report Using PL/SQL & BI Publisher"

After Completion, Click on Output button and save output into your local PC as .xml file (test.xml)

6.  Create Template file using BI Publisher (XML/BI Publisher must be installed on local PC to create template file)

      i. Lunch MS Word & Load XML file (test.xml)

        Navigation: MS Word > Add-Ins > Data > Load XML Data

      ii. Create tabular report

        Navigation: MS Word > Add-Ins > Insert > Table/Forms > Wizard

  Here create tabular report using wizard and save it as XXJSPLSXMLREP.rtf

7.  Register this template using XML Publisher Administrator

      i. Create Data Definition (Navigation: XML Publisher Administrator> Data Definition > Create Data Definition)

     Name: My Custom Report Using PL/SQL & BI Publisher

     Application: XXJS Custom Application

     Code: XXJSPLSXMLREP

     ii. Create Template (Navigation: XML Publisher Administrator> Data Definition > Create Data Definition)

     Name: My Custom Report Using PL/SQL & BI Publisher

     Application: XXJS Custom Application

     Default File: XXJSPLSXMLREP.rtf (need to upload this file)

     Default File Language: English

     Code: XXJSPLSXMLREP

     Data Definition: My Custom Report Using PL/SQL & BI Publisher

     Default Output Type: PDF

8.  Log in to Oracle Apps

Navigation: XXJS Custom Application > View > Request

Submit New Request for concurrent program “My Custom Report Using PL/SQL & BI Publisher”