- Creating Concurrent program to execute stored procedure You have a PL/SQL procedure in your database and you want the user to be able to execute it from Oracle Apps front end. To do so, we have to register the procedure in Oracle Apps.
- Challa Pavan Kumar Hyderabad, Telangana, India Hello, I am Challa Pavan Kumar and I hail from India.I was born in Hyderabad, Telangana, India.I am an Oracle Technologist and my topic of blogging is mainly related to Oracle Technologies(Apps).
- Concurrent program return code. Current Time: Fri Jan 04 04:19:37 CST 2019.
- Oracle Applications Concurrent Request phase codes and status codes. « Oracle SQL hints How to submit concurrent program from command promot / shell. Fix for IE crash on launching forms in Oracle Applications 11i (11.5.10).
- Oracle Assets - Version 11.5.10.2 and later: 11i/R12:FA: Concurrent Program FATRACE. Routine &ROUTINE received a return code of failure. 11i/R12:FA: Concurrent Program FATRACE.
- Oracle Concurrent Request
- Oracle Apps Concurrent Program Return Code Free
- What Is A Concurrent Program
PL/SQL APIs for Concurrent Processing Overview. It also includes example PL/SQL code using these concurrent processing APIs. The program application determines the Oracle user name used by the program. Specify either 'Y' or 'N'. Concurrent Request Status and Phase Codes. Concurrent Request Status Codes: SELECT LOOKUP_CODE STATUS_CODE, MEANING. FROM FND_LOOKUP_VALUES. WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'. AND ENABLED_FLAG = 'Y'. AND VIEW_APPLICATION_ID = 0. ORDER BY LOOKUP_CODE.
Oracle Apps Concurrent Processing/Programs works on the principle – Work simultaneously, to efficiently use the available resource like hardware, software, etc. You can submit multiple instances of the same or different concurrent program in parallel. A dedicated concurrent manager controls the execution of programs. Programs run in background asynchronous manner.
Let’s see how to define concurrent programs in Oracle Applications (Apps).
Table of Contents
Create concurrent program execution file
This is a physical file which contains business logic. It can be a PL/SQL stored procedure, Oracle Reports, Host – an Operating system based execution file, C or Pro* C based spawned program, Java file.
To keep it simple, we will use a PL/SQL stored procedure for demonstration.
[sql]
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;
/
[/sql]
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 concurrent manager post completion of the program.
Typical usage of these parameter as shown below.
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
Define Concurrent Program Executable
This is the first step. You need to define executable for the concurrent program. This actually maps excutable file with excutable 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 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
Define Concurrent Programs
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 parameter button to define input parameters.
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 placeholder for those mandatory parameters. These out parameters are internally used by the concurrent program to return error message and status to concurrent manager.
Register concurrent program with responsibility
You can submit concurrent program using below methods in Oracle Apps,
- SRS window
- 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.
Submit concurrent program
Lets test concurrent program by submitting it. Navigate to Inventory-> View -> Request->Submit New Request. Select 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 Reports is the one of execution method available in Oracle Apps while defining concurrent program executable. Oracle Reports is enterprise reporting tool. It enables businesses to give immediate access to information to all levels within and outside of the organization in an unrivaled scalable and secure environment.
In this article, I will cover how to define concurrent program based on Oracle Reports (RDF) executable.
Read:- How to define Concurrent Program in Oracle Apps
Note: This method is obsolete to define concurrent program on Reports. Oracle recommends to use XML publisher instead as Oracle Reports released with Oracle Fusion Middleware’s 12.2.1.3.0 will be its terminal release.
Table of Contents
Pre-requisite
Working knowledge of Oracle Apps and Oracle Reports Builder Tool
Software/Hardware environment
- Oracle apps 11i or R12
- Putty
- Winscp
- Oracle Report Builder 10G or 11G
Step 1 – Create a sample/demo report (RDF)
To demonstrate working, let’s create a sample report on DBA_OBJECTES table to display 20 rows in a tabular format. You can use any table or data source to create a report. This report has an owner as an input parameter. Depending on Owner name respective object data should get displayed.
Start Oracle Reports Builder and using wizard-driven approach simply create a tabular report. Below is the tree layout of the report.
The paper layout of the report.
Report Parameters
We are going to have only one parameter for demo purpose. p_conc_request_id is the important and mandatory parameter you need to create. It holds request id returned by the concurrent manager. If you miss this parameter, your program will end in error.
- P_CONC_REQUEST_ID – This user parameter is mandatory when you run it in Oracle Apps environment. Concurrent Manager returns request_id in this parameter.
- P_OWNER – Placeholder for custom OWNER Parameter.
Report Triggers
Add below code the respective triggers mention.
Before Report
After Report
SRW.USER_EXIT(‘FND SRWINIT’) – This user exit sets up information for use by profile options and other AOL features. Always use it in before report trigger.
SRW.USER_EXIT(‘FND SRWEXIT’) – This user exit frees all the memory allocation done by other AOL exits. Always use it in after report trigger.
Step 2 – Copy/FTP the report file to Unix Server
Copy this RDF file to the UNIX server. If there is a multi-node environment, make sure to copy in each node. RDF is a binary file, so transfer it using Binary FTP mode. Either use WINSCP of FILEZILLA. Both are free to use software.
Step 3 – Define executable
Navigate to Application Developer -> Concurrent -> Executable. Define executable as shown below.
Step 4 – Define Concurrent Program
Navigate to Application Developer -> Concurrent -> Program. Define concurrent program as shown below.
Define concurrent program parameter as shown below. The Token label show at the end of the image should match to the name of your parameter in rdf file.
Oracle Concurrent Request
Step 5 – Register concurrent program
Next register program in valid responsibility. Navigate to System Administrator -> Security -> Responsibility – > Request. Add this program to request group
Step 6 – Submit concurrent program
Navigate to the responsibility where you registered the concurrent program. Submit it and check the output. Below is sample output shown. Oracle Report supports PDF, Text, XML output.