PL/SQL Stored Procedure Concurrent Program in Oracle Apps

Concurrent programs in Oracle Apps are batch jobs.  You can define a concurrent program on PL/SQL stored procedure, Oracle Reports, Host – an Operating system based execution file, Perl, C or Pro* C based spawned program, Java file.

Here, I will show you how to define concurrent programs in Oracle Applications (Apps). We will use PL/SQL procedure to demonstrate the same.

You need an Application Developer responsibility to define the concurrent programs.

1. Create PL/SQL Execution file

CREATE OR REPLACE PACKAGE concurrent_demo
AS
   PROCEDURE main(
         o_chr_errbuf OUT VARCHAR2,
         o_chr_retcode OUT VARCHAR,
         p_custom_parameter IN VARCHAR2);
END concurrent_demo;
/
CREATE OR REPLACE PACKAGE body concurrent_demo
AS
   PROCEDURE main(
         o_chr_errbuf OUT VARCHAR2,
         o_chr_retcode OUT VARCHAR,
         p_custom_parameter IN VARCHAR2)
   IS
   BEGIN
      --Write business logic here. 
      ---I am putting only MESSAGE FOR demo purpose
      Fnd_file.put_line(fnd_file.log, 'Concurrent program execution demo starts');
      Fnd_file.put_line(fnd_file.output, 'Concurrent program execution demo starts');
	  
	  Fnd_file.put_line(fnd_file.log, 'Input Parameter :- ' || p_custom_parameter);
      Fnd_file.put_line(fnd_file.output, 'Input Parameter :- ' || p_custom_parameter );
      
      dbms_lock.sleep(120);
	  
	  
	  o_chr_retcode := '0';  -- 0 - Normal 1 - Warning 2 - Error
	  o_chr_errbuf := 'No Error';
      
      Fnd_file.put_line(fnd_file.log, 'Concurrent program execution demo ends');
      Fnd_file.put_line(fnd_file.output, 'Concurrent program execution demo ends');
      
   END main;
END concurrent_demo;
/

 PROCEDURE main(
         o_chr_errbuf OUT VARCHAR2,
         o_chr_retcode OUT VARCHAR,
         p_custom_parameter IN VARCHAR2);

 

The main procedure has two out parameter and one custom in the parameter. Two out parameters o_chr_errbuf, o_chr_retcode are mandatory and should be defined in the same sequence. You can return the status code to the concurrent manager post completion of the program.

Typical usage of these parameters as shown below.

o_chr_errbuf := "Error while executing the program  ' || SQLERRM
o_chr_retcode = "2"

Depending on retcode programs end is normal, warning or error.

• 0 – Program complete in normal
• 1 – Program completed in warning
• 2 – Program completed in error

2. Define Concurrent Program Executable

This is the first step. You need to define an executable for the concurrent program. This actually maps excutable file with executable AOL in Oracle Apps. Login  to Oracle Applications and navigate to Application Developer -> Concurrent -> Executable. Define executable in Oracle Apps as shown below.

• Executable: Valid executable name
• Short Name: Valid short name
• Application: application name which owns the concurrent program
• Description: Valid description
• Execution Method: PL/SQL stored procedure, Host, Oracle Reports
• Execution File Name: Actual name of file, database package, or Oracle report

3. Define Concurrent Programs

The next step is defining a concurrent program. Navigate to Application Developer -> Concurrent -> Program

Define concurrent program in Oracle Apps as shown below. Select executable as defined above. Click on the parameter button to define input parameters.

4. Define Concurrent Program Parameters

These are the placeholder for the custom parameter. Here you need to define the only custom parameter. Note our procedure is having three parameters, two are mandatory.

Do not define placeholders for those mandatory parameters. These out parameters are internally used by the concurrent program to return error message and status to the concurrent manager.

5. Register concurrent program with responsibility

You can submit concurrent program using the below methods in Oracle Apps,

• SRS window
• PL/SQL API FND_REQUEST API
• UNIX CONCSUB utility

We are going to use the first method. So we need to register the program with a request group in Oracle Apps. A request group, in turn, is attached to responsibility.

Let’s register it with Inventory Responsibility.

Navigate to System Administrator -> Security -> Responsibility – > Define to get Request group for inventory responsibility. All Inclusive GUI is a request group.

Navigate to System Administrator -> Security -> Responsibility – > Request. Add your program to request group.

6. Submit concurrent program

Let’s test the concurrent program by submitting it. Navigate to Inventory-> View -> Request->Submit New Request. Select the concurrent program and click on submit.

You can submit multiple instances of the same program as shown below.

I hope you found this article on defining concurrent program in Oracle Apps helpful. Please share and do let me know your feedback in the comments section below.

Reference and further reading:

• Oracle E-Business Suite Developer’s Guide

Summary

PL/SQ or stored procedure based program are the most used program in Oracle apps and we have covered you here and explained it very well.