Plsql Stored Procedure Method

Steps To Create the Plsql Stored Procedure Method:

1.    Create the Package or Procedure in the Database

2.    Create the Executable

Execution Method       :- Plsql Stored Procedure

Executable File Name :- PackageName.ProcedureName

3.    Create Concurrent Program and Attach Executable to Concurrent Program

Executable short Name in Executable window = Executable Name in Concurrent Program Window.

4.    Attach Concurrent Program to the Request Group.

5.    Navigate to Specific Responsibility and run the Program from SRS Window.

SRS= Standard Request Submission

1.Create the Package or Procedure in the Database.

Tool I am using is Sql Developer. Code Attached End of the File.

2. Create the Executable

Execution Method       :- Plsql Stored Procedure

Executable File Name :- PackageName.ProcedureName

Navigation: System Administrator ResponsibilityàConcurrent à Program àExecutable

Enter All Required Fields and Save the Changes.

3.Create Concurrent Program and Attach Executable to Concurrent Program

Executable short Name in Executable window = Executable Name in Concurrent Program Window.

Navigation: System Administrator ResponsibilityàConcurrent à Program àExecutable

4. Attach Concurrent Program to the Request Group.

A. Identify the Responsibility Name to run your Program .

    Navigation: System Administrator ResponsibilityàSecurity à Responsibility àDefine.

Click on Ctrl+F11 to get the Results. 

   Results will be displayed as shown below.

B. Query the Request Group Name in request group Window and Attach the Concurrent Program .

Navigation: System Administrator ResponsibilityàSecurity à Responsibility àRequest.

Click on F11 and enter Request Group Name and Application Name Identified in the Responsibility Window.

Click on Ctrl+F11 to get the results as shown Below.

6.    Navigate to Specific Responsibility and run the Program from SRS Window.

SRS= Standard Request Submission.

Navigate to specific Responsibility by Using the Swich responsibility Button as shown below.

Enter your Concurrent Program Name as shown below.

   Click on Submit Button.

Click on No as shown Below.

It will show the Green colour as you Program is Running as shown below.

Click on refresh Data to the Program Completes.

Once Program Completed Successful as Shown Below.

Procedure :

CREATE OR REPLACE PROCEDURE xxcer_plsql_stored_proc(errbuf out VARCHAR2

                                                                                                      ,retcode out NUMBER)

IS

--//================================================================

--// Declare Cursor to fetch data from mtl_system_items_b

--//================================================================

CURSOR cur_name

       IS

       SELECT segment1,          -- get the Items in the Oracle Apps

                    organization_id -- get the Organizations in Oracle Apps

         FROM mtl_system_items_b

      WHERE ROWNUM <101;

BEGIN

--//================================================================

--//Open the for Loop to fetch data from Cursor

--//================================================================

FOR rec IN cur_name

--//================================================================

--//Open the for Loop to do the row by row Processing

--//================================================================

loop

fnd_file.put_line(fnd_file.LOG,'Item Name is :'||rec.segment1);

fnd_file.put_line(fnd_file.LOG,'Organization Name is :'||rec.organization_id);

END loop;

END xxcer_plsql_stored_proc;