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”

Registration of Oracle PL/SQL Stored Procedure in Oracle Apps

Steps for Registering Oracle PL/SQL Stored Procedure in Oracle Apps

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

Example:

Package Name: XXJS_UPDATE_PKG.XXJS_MAIN_PROC

CREATE OR REPLACE PACKAGE XXJS_UPDATE_PKG AS

   PROCEDURE XXJS_MAIN_PROC (

       x_errbuf          OUT NOCOPY   VARCHAR2

     , x_retcode         OUT NOCOPY   NUMBER

   );

END XXJS_UPDATE_PKG;

/

CREATE OR REPLACE PACKAGE BODY XXJS_UPDATE_PKG AS

   PROCEDURE XXJS_MAIN_PROC (

       x_errbuf          OUT NOCOPY   VARCHAR2

     , x_retcode         OUT NOCOPY   NUMBER

   ) IS

BEGIN

        fnd_file.put_line (fnd_file.LOG,   ' Custom Interface Started ......' );

        BEGIN

                        UPDATE EMPLOYEE SET JOB = 'MANAGER' WHERE JOB IS NULL;

                        EXCEPTION

                                        WHEN OTHER THEN

                                                        fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 250));

                                                        ROLLBACK;

        END;

        fnd_file.put_line (fnd_file.LOG,   ' End ......' );

        COMMIT;

WHEN OTHERS THEN

         x_retcode := 2;

         x_errbuf := SUBSTR (SQLERRM, 1, 150);

END XXJS_MAIN_PROC;

END XXJS_UPDATE_PKG;

/

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

Navigation: System Administrator or Application Development > Concurrent > Executable

Executable: XXJSCUSTPRG

Short Name: XXJSCUSTPRG

Application: XXJS Custom Application

Description: My Custom Update Program

Executable Method: PL/SQL Stored Procedure

Executable File Name: XXJS_UPDATE_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 Update Program

Short Name: XXJSCUSTPRG

Application: XXJS Custom Application

Description: My Custom Update Program

Executable Name: XXJSCUSTPRG

4.      (Optional ) Create “Responsibility”

Navigation: System Administrator > Security > Responsibility > Define

5.      (Optional ) Create “User”  and attach Responsibility to user

Navigation: System Administrator > Security > User > Define

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

Navigation: System Administrator > Security > Responsibility > Request

Search Request Group and Add created concurrent program on Name

7.      Login to Oracle Apps by your user

Navigation: XXJS Custom Application > View > Request

Submit New Request where search you newly created Concurrent Program

After completion of program, you can also able to view log.

Write a pl/sql block to display names of employees with sal in descending order without using order by

DECLARE
   TYPE rec IS RECORD
   (
      ename   emp.ename%TYPE,
      sal     emp.sal%TYPE
   );

   v_rec        rec;
   v_rec2       rec;
   v_rec_temp   rec;

   TYPE assoc IS TABLE OF rec
                    INDEX BY PLS_INTEGER;

   v_assoc      assoc;

   CURSOR c
   IS
      SELECT ename, sal FROM emp;

   --elem   emp.ename%TYPE;
   v_sal        NUMBER;
   v_ename      VARCHAR2 (100);
   i            NUMBER := 1;
BEGIN
   DBMS_OUTPUT.put_line ('Origional List');

   FOR d IN c
   LOOP
      v_rec.ename := d.ename;
      v_rec.sal := d.sal;
      v_assoc (i) := v_rec;
      DBMS_OUTPUT.put_line (i || ': ' || v_rec.ename || ' ' || v_rec.sal);
      i := i + 1;
   END LOOP;

   DBMS_OUTPUT.put_line ('Descending Order List');

   FOR e IN 1 .. v_assoc.COUNT
   LOOP
      v_rec := v_assoc (e);

      --DBMS_OUTPUT.put_line (e || '==>>' || v_rec.ename || ' -----'|| v_rec.sal );

      FOR e2 IN e + 1 .. v_assoc.COUNT
      LOOP
         v_rec2 := v_assoc (e2);

         --DBMS_OUTPUT.put_line (e || '====>>>>' || v_rec2.sal || '>' || v_sal);

         IF v_rec2.sal > v_rec.sal
            OR (v_rec2.sal = v_rec.sal AND v_rec2.ename < v_rec.ename)
         THEN
            v_rec_temp.ename := v_rec.ename;
            v_rec_temp.sal := v_rec.sal;
            v_assoc (e) := v_rec2;
            v_assoc (e2) := v_rec_temp;
            v_rec := v_assoc (e);
         END IF;
      END LOOP;
   END LOOP;

   FOR e IN 1 .. v_assoc.COUNT
   LOOP
      v_rec := v_assoc (e);
      v_ename := v_rec.ename;
      v_sal := v_rec.sal;
      DBMS_OUTPUT.put_line (e || ' : ' || v_ename || ' ' || v_sal);
   END LOOP;
END;

/*
Output:
Origional List
1: SMITH 800
2: ALLEN 1600
3: WARD 1250
4: JONES 2975
5: MARTIN 1250
6: BLAKE 2850
7: CLARK 2450
8: SCOTT 3000
9: KING 5000
10: TURNER 1500
11: ADAMS 1100
12: JAMES 950
13: FORD 3000
14: MILLER 1300
Descending Order List
1 : KING 5000
2 : FORD 3000
3 : SCOTT 3000
4 : JONES 2975
5 : BLAKE 2850
6 : CLARK 2450
7 : ALLEN 1600
8 : TURNER 1500
9 : MILLER 1300
10 : MARTIN 1250
11 : WARD 1250
12 : ADAMS 1100
13 : JAMES 950
14 : SMITH 800
*/