Registering PLSQL Stored Procedure in Oracle Apps via Concurrent Program

The purpose of the concurrent program is to drop the table/tables from a specific schema.
The program always holds a default value <Drop all tables older than 1 Month>, which will drop all the tables that are older than one month. Either you can go with the default value or you can provide the program with a valid value (tablename).

STEP 1: Create the Package and Package Body.
create or replace PACKAGE      XXFND_UTILITIES
AS
   PROCEDURE DROP_BKP_TABLES (errbuf           OUT VARCHAR2,
                              retcode          OUT NUMBER,
                              p_bkp_table   IN     VARCHAR2);
END XXFND_UTILITIES;

create or replace PACKAGE BODY      XXFND_UTILITIES
AS
   PROCEDURE DROP_BKP_TABLES (errbuf           OUT VARCHAR2,
                              retcode          OUT NUMBER,
                              p_bkp_table   IN     VARCHAR2)
   IS
      l_found   BOOLEAN := FALSE;

      CURSOR c_bkp_tables
      IS
           SELECT object_name, created, status
             FROM dba_objects
            WHERE object_type = 'TABLE' AND owner = 'XXBKP'
                  AND object_name = UPPER (
                         DECODE (
                            p_bkp_table,
                            '<Drop all tables older than 1 Month>', object_name,
                            p_bkp_table))
                  AND created <
                         DECODE (
                            p_bkp_table,
                            '<Drop all tables older than 1 Month>', SYSDATE- 30,
                            created + 1)
         ORDER BY 1;
   BEGIN
      FND_FILE.
      PUT_LINE (FND_FILE.LOG, ('Parameter: p_bkp_table = ' || p_bkp_table));

      FND_FILE.
      PUT_LINE (FND_FILE.LOG, ('======================================='));

      IF p_bkp_table = '<Drop all tables older than 1 Month>'
      THEN
         FND_FILE.
         PUT_LINE (FND_FILE.LOG,
                   ('Dropping backup tables older than 1 month'));
      END IF;

      FOR c_bkp_table_rec IN c_bkp_tables
      LOOP
         FND_FILE.
         PUT_LINE (FND_FILE.LOG,
                   ('Dropping table XXBKP.' || c_bkp_table_rec.object_name));

         EXECUTE IMMEDIATE 'DROP TABLE XXBKP.' || c_bkp_table_rec.object_name;

         l_found := TRUE;
      END LOOP;

      IF l_found = FALSE
      THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, ('No table(s) found to Drop.'));
      END IF;

      FND_FILE.
      PUT_LINE (FND_FILE.LOG, ('======================================='));
   EXCEPTION
      WHEN OTHERS
      THEN
         errbuf := SQLERRM;
         retcode := '2';
         FND_FILE.
         PUT_LINE (
            FND_FILE.LOG,
            ('Unknown Exception occurred  - ' || SUBSTR (SQLERRM, 1, 100)));
         RAISE;
   END;
END XXFND_UTILITIES;

STEP 2: Register Executable.
System Administrator > Concurrent > Executable



STEP 3: Register Program.
System Administrator > Concurrent > Program


STEP 4: Provide appropriate parameters.
System Administrator > Concurrent > Program:Concurrent Program Parameters


STEP 5: Attach this concurrent program to a Request group.
System Administrator > Security > Responsibility > Request


STEP 6: Submit the Concurrent Program. 
System Administrator >  Concurrent > Requests
 
When Backup Table value is default "<Drop all tables older than 1 Month>"

+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XXFND_DROP_BKP_TABLES module: GP Drop Backup Tables
+---------------------------------------------------------------------------+
Current system time is 03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
**Starts**03-JUL-2014 09:26:27
**Ends**03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Parameter: p_bkp_table = <Drop all tables older than 1 Month>
=======================================
Dropping backup tables older than 1 month
No table(s) found to Drop.
=======================================
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...
Printing output file.
               Request ID : 13313415  
         Number of copies : 0  
                  Printer : noprint 
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 03-JUL-2014 09:26:27
+---------------------------------------------------------------------------+

When Backup Table value is "T1", below is log file.

+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XXFND_DROP_BKP_TABLES module: GP Drop Backup Tables
+---------------------------------------------------------------------------+
Current system time is 03-JUL-2014 09:42:14
+---------------------------------------------------------------------------+
**Starts**03-JUL-2014 09:42:14
**Ends**03-JUL-2014 09:42:14
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Parameter: p_bkp_table = T1
=======================================
Dropping table XXBKP.T1
=======================================
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...
Printing output file.
               Request ID : 13313419  
         Number of copies : 0  
                  Printer : noprint 
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 03-JUL-2014 09:42:15
+---------------------------------------------------------------------------+ 

References:

http://jayantaapps.blogspot.in/2012/10/how-to-registration-pl-sql-stored.html 

http://www.techmandate.com/steps-to-create-a-concurrent-program-of-type-plsql-stored-procedure/