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.