Oracle Apps R12 Concurrent Programs: The Ultimate Guide with Practical Examples

Concurrent Program in Oracle Apps R12 are executable files that run in the background, processing tasks such as report generation, data loads, or batch jobs. Their role is essential for automating repetitive tasks, improving efficiency, and maintaining system performance in Oracle Apps R12.

Learn the power of background processing in your Oracle EBS environment with this comprehensive guide to Concurrent Program in Oracle Apps R12. Learn how to automate tasks, improve system efficiency, and manage your batch processes effectively.

What is a Concurrent Program in Oracle EBS?

A Concurrent Program in Oracle E-Business Suite (EBS) is a server-side batch process designed to execute resource-intensive or scheduled tasks without direct user interaction. This asynchronous processing allows users to continue working within the application while long-running operations complete in the background.

A concurrent program is an instance of an execution file. Concurrent programs use a concurrent program executable to locate the correct execution file. Several concurrent programs may use the same execution file to perform specific tasks, each having different parameter set. The concurrent manager runs in the background, waiting for concurrent programs to be submitted. Once a concurrent program is submitted, it is placed into an execution queue and managed for concurrent execution. Concurrent programs associated with Open Interface Tables move data from interface tables to base tables, while others execute business logic and application-level processes in Oracle E-Business Suite.

Example: Generating monthly financial reports is a common use case, ensuring users can continue with their daily activities without waiting for the report to finish.

Key Concepts and Terminology

Concurrent Request

When a user submits a concurrent program, a request to run the program is created. The command to run the program is a concurrent request. Concurrent Request is a single instance of a concurrent program execution.

Concurrent Manager

A background service that executes concurrent requests. Concurrent programs are started by a concurrent manager.

Request Set

A request set is a collection of concurrent programs that are grouped together. You can thus submit the concurrent programs in a request set all at once using a single transaction.

Submission Parameters

User inputs required by the concurrent program. Concurrent program parameters, also referred to as arguments, are assigned to standard submission concurrent programs.

Output Post Processor (OPP)

Processes and formats the output. The OPP is a specialized service that takes the raw output generated by concurrent programs and performs additional formatting or delivery tasks, depending on the request configuration.

Log Files and Output Files

After completion of the concurrent request execution details are captured in log file.Also program output are stored in output files.

Architecture Overview

• Users submit requests from the application tier.
• Requests are queued in database tables.
• Concurrent Managers fetch requests and execute them.
• OPP formats the output.
• Status is updated post execution.

Understanding Concurrent Programs in Oracle Apps R12

The concurrent processing framework (in Oracle Apps R12) supports various execution methods, including:

• Oracle Reports: For generating structured reports.
• SQL*Plus scripts: To execute SQL statements and procedures.
• SQL*Loader: To execute SQL*Loader command
• Host scripts (Shell): For running unix shell scripts or commands.
• Java Stored Procedures: Leveraging Java code within the Oracle database.
• PL/SQL Stored Procedures: Executing Oracle PL/SQL stored procedure logic.

These programs are managed and executed through the robust Concurrent Manager infrastructure in Oracle R12.

Key Applications of Oracle Concurrent Programs

Oracle Concurrent Programs are crucial for a wide range of tasks, including:

• Report Generation: Creating financial, operational, and analytical reports.
• Data Loading (Interface Programs): Importing data from external systems (SQL*Loader)
• Batch Updates: Performing bulk modifications to data.
• Period-End Processing: Executing critical month-end, quarter-end, and year-end processes like costing and inventory closure.

Step-by-Step Guide: How to Create a Concurrent Program in Oracle Apps R12

Creating custom concurrent programs in Oracle R12 involves these key steps:

1. Create Executable: Define the program’s execution method and the actual script, reports or program to run.
2. Create Concurrent Program: Register the program within Oracle Applications, linking it to the executable.
3. Attach Parameters (if needed): Define input parameters to make the program dynamic.
4. Attach to Request Group: Assign the program to a request group, controlling which users can access it.
5. Attach to Responsibility: Link the request group to specific user responsibilities.

Defining and Registering Concurrent Programs

Create Executable

Navigation: Application Developer Responsibility –> Concurrent –> Executable

Important fields:

• Executable: User defined Executable Name
• Short Name: This is unique executable name. Short name to be used in link program with executable
• Application: The application name to be used to register the concurrent program
• Description: Concurrent program executable description
• Execution Method: The type of the concurrent program. Based on this execution method the script/report has to be placed in respective directory(or database)
• Execution File Name: This is the actual script or report name. Give packagename.procedure for PL/SQL Procedure

Define Concurrent Program

Navigation: Application Developer Responsibility ->Concurrent -> Program -> Define

Program Details:

• Program: Define a user friendly name.
• Application: Application name under which the program falls.
• Short Name: Internal unique program name.
• Description: Description displayed to users.
• Executable Name: Executable short name defined in earlier step
• Execution Method: PL/SQL, Oracle Reports, Host Script, Java Stored Procedure, SQL*Plus, SQL*Loader, BI Publisher
• Output Type: TEXT, HTML, PDF, XML, etc.

Assign Concurrent Program to a Request Group

After you register the concurrent program you need to assign the program to a request group. Follow below steps to assign concurrent program to a request group to run the program.

Navigation: System Administrator Responsibility –> Security –> Responsibility –> Request

• Seach for Group “All inclusive” and application “Inventory”
• In the requests section add the program with type “Program” and name as the program we just defined.

Finally Concurrent Program is ready to run from Inventory responsibility.

Detailed Registration Steps for All Program Types

1. Oracle Reports

• Executable Method: Oracle Reports
• Executable File: Place the report file (.rdf) under custom top reports directory: $XXAP_TOP/reports/US
• Registration:
  • Concurrent > Program > Executable
  • Method: Oracle Reports
  • Execution File Name: Without .rdf
  • Concurrent > Program > Define to link executable.
  • Define Parameters as needed

2. SQL*Plus Scripts

• Executable Method: SQL*Plus
• Executable File: place the sql script under custom top/sql.
• Registration:
  • Concurrent > Program > Executable
  • Method: SQL*Plus
  • Execution File Name: Without .sql

3. Steps to Register Shell Script as a Concurrent Program

In order to register a shell script as a concurrent program you need to follow below step before the executable creation.

Processing of Shell Script

• Rename the .sh file to .prog file
• Place the .prog script under the bin directory for your applications top directory.
• dos2unix XXENO_TEST_SHELL.prog in $XXCUST_TOP/bin
• Set executable permission (chmod 777 XXENO_TEST_SHELL.prog)
• Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
• ln -s $FND_TOP/bin/fndcpesr $XXCUST_TOP/bin/XXENO_TEST_SHELL
• Ensure the link as executable permission

This link to be named the same as your script without the .prog extension. Put the link for your script in the same directory where the script is located.

Define the Host Concurrent Program

Now you are ready to create the executable.

• Executable Method: Host
• Executable File: .prog placed under $CUSTOM_TOP/bin
• Registration:
  • Concurrent > Program > Executable
  • Method: Host
  • Execution File Name: Without .prog
  • Ensure script has execute permissions.
  • Concurrent > Program > Define
  • Create the concurrent Program linking the executable
  • Define the parameters as per the shell script

4. Java Stored Procedures

• Executable Method: Java Stored Procedure
• Registration:
  • Concurrent > Program > Executable
  • Method: Java Stored Procedure
  • Execution File Name: Package.Procedure

5. PL/SQL Stored Procedures

You need to create the PL/SQL procedure with two mandatory out parameters as the first two parameters in the procedure

• x_err_buf OUT VARCHAR2
• x_ret_code OUT NUMBER

• Executable Method: PL/SQL Stored Procedure
• Registration:
  • Concurrent > Program > Executable
  • Method: PL/SQL Stored Procedure
  • Execution File Name: Package.Procedure
  • Concurrent > Program > Define
  • Create the concurrent Program linking the executable
  • Define the parameters as per the PL/SQL procedure
  • Consider the third parameter in the procedure as first parameter in the program
    (as you need to neglect the first two out parameters)

6. Registering SQL*Loader as a Concurrent Program

You can register a SQL*Loader control file as a concurrent program,

7. BI Publisher Reports

You do not need to create any executable. You need to use seeded BI Publisher(Java Concurrent Program) executable XDODTEXE for registering BI (XML) Publisher concurrent program.

• Executable Name: XDOEXE
• Executable Method: Java Concurrent Program
• Registration:
  • Define a Data Definition: Use Data Defination code as the short name of the concurrent program
  • Upload Template in XML Publisher Administrator.
  • Concurrent Program links to XML Data Definition.

Example: Creating a BI Publisher Concurrent Program in Oracle R12

BI Publisher (XML Publisher) program registration is a two steps process. It involves first with XML Publisher Administrator configration. Then you need to do the standard concurrent program registration process.

To create a BI Publisher concurrent program, follow these organized steps:

Step-by-Step Process

1. Prepare BI Publisher Data Source

• Develop the report data defination logic using any of the below method:
  • A PL/SQL Procedure
  • A View
  • Or a SQL Query that outputs the data in XML format

Example PL/SQL for data extraction:

SELECT fu.user_name,
       frt.responsibility_name,
       fur.start_date,
       fur.end_date
  FROM fnd_user fu,
       fnd_user_resp_groups_direct fur,
       fnd_responsibility_tl frt,
       fnd_responsibility fr 
 WHERE fu.user_name = :P_USER_NAME
   AND fu.user_id = fur.user_id
   AND fur.responsibility_id = fr.responsibility_id
   AND fr.responsibility_id = frt.responsibility_id
   AND frt.language=userenv('Lang')

2. Create the Data Definition

Before preparing the data defination you need to create the data defination XML file based on the above SQL and attaching in the data defination.

<?xml version="1.0" encoding="UTF-8" ?> 
 <dataTemplate name="UserResponsibilities" description="Responsibilities attached to User" 
			   Version="1.0" defaultPackage="xxfnd_user_responsibilities_pkg">
 <properties>
  <property name="debug_mode" value="on"/>
 </properties> 
 <parameters>
  <parameter name="P_USER_NAME" dataType="varchar2" /> 
 </parameters>
 <dataTrigger name="beforeReport" source="xxfnd_user_responsibilities_pkg.print_params()"/>
 <dataQuery>
  <sqlStatement name="Q1">
  <![CDATA[ 
   SELECT fu.user_name,
          frt.responsibility_name,
          fur.start_date,
          fur.end_date
     FROM fnd_user fu,
          fnd_user_resp_groups_direct fur,
          fnd_responsibility_tl frt,
          fnd_responsibility fr 
    WHERE fu.user_name = :p_user_name
      AND fu.user_id = fur.user_id
      AND fur.responsibility_id = fr.responsibility_id
      AND fr.responsibility_id = frt.responsibility_id
      AND frt.language=userenv('Lang')
   ]]> 
   </sqlStatement>
  </dataQuery>
  <dataStructure>
  <group name="G_USR_RESP" source="Q1">
   <element name="USER_NAME" value="user_name" /> 
   <element name="RESPONSIBILITY_NAME" value="responsibility_name" /> 
   <element name="START_DATE" value="start_date" /> 
   <element name="END_DATE" value="end_date" /> 
  </group>
  </dataStructure>
 </dataTemplate>

Responsibility: XML Publisher Administrator
Navigation: Home > Data Definitions

• Name: XXFND User Responsibilities DD
• Code: XXFND_USER_RESP
• Application: Application Object Library

• Press “Apply” and next form to open

• Press “Add File” and below form to open

select Data Template File XXFND_USER_RESP_DD.XML and press “Apply” button to upload the xml file.

📌 This links your data source to BI Publisher.

3. Create the Report Layout

You need use MS Word with BI Publisher Desktop Plugin (RTF template) to design and generate a report layout. You can download below template for this example.

Now create a Template using XML Publisher Administrator responsibility and upload the above craeted rtf template.

Responsibility: XML Publisher Administrator
Navigation: Home > Templates

Press “Create Template” to create the layout of the report

• Template Name: XXFND User Responsibilities Template
• Code: XXFND_USER_RESP_TMP
• Application: Application Object Library
• Data Definition: XXFND User Responsibilities DD
• Type: RTF
• Default Output Type: PDF
• Template File: Upload the RTF file prepared generated using BI Publisher Desktop.
• Language: English

Press “Apply” to save the template

📌 Defines how the data will be presented to the user.

4. Register the Executable

For BI Publisher concurrent program you need to select executable as XDOEXE. For PL/SQL sourced or other type of Report you need to create respective executable to link the data source.

5. Register the Concurrent Program

Responsibility: System Administrator
Navigation: Concurrent > Program > Define

• Program Name: FND User Responsibilities Program
• Short Name: XXFND_USER_RESP (Same as data template code)
• Application: Application Object Library
• Executable: XDODTEXE (or link to the executable created above)
• Output:XML

Add necessary Parameters User Name with token P_USER_NAME. Token must be same as parameter defined in the data definitions xml file.

6. Attach the Concurrent Program to a Request Group

Responsibility: System Administrator
Navigation: Security > Responsibility > Request

• Add the new program to a relevant Request Group assigned to a responsibility.

7. Submit and Test

Navigate to responsibility System Administrator and follow below steps to run and varify the program output.

• Navigation View > Requests > Submit a New Request
• Select program as “FND User Responsibilities Program”
• Enter a user for parameter for “User Name”
• Review the layout as “XXFND User Responsibilities Template”
• Submit and verify output

You can easily migrate the data defination and template and concurrent program into different Oracle ebs test instances and finally migrate into live production environment.

Submit a Concurrent Program From Backend

In Oracle EBS R12, concurrent programs are usually submitted via the Standard Request Submission(SRS) form. However, for automation, testing, or integrations, you need to submit the program directly from backend using PL/SQL.

🛠️ Step-by-Step PL/SQL Example

Below is a working example that uses the FND_REQUEST.SUBMIT_REQUEST API to launch a BI Publisher concurrent program from SQL*Plus or a PL/SQL block.

Before submiting the request you need to add the XML Publisher template using api fnd_request.add_layout.

DECLARE
   l_request_id   	    NUMBER;
   l_user_id      	    NUMBER         := 1012; -- User
   l_resp_id      	    NUMBER         := 20634;-- Inventory
   l_resp_appl_id 	    NUMBER         := 401;  -- Inventory 
   l_phase              VARCHAR2(50);
   l_status             VARCHAR2(50);
   l_dev_phase          VARCHAR2(50);
   l_dev_status         VARCHAR2(50);
   l_message            VARCHAR2(50);
   l_user_name          VARCHAR2(30) := 'SYSADMIN';
   l_xml_layout         BOOLEAN;
BEGIN 
   --Check if the program not submitted from EBS Front end
   IF fnd_global.conc_request_id <= 0
   THEN 
	    fnd_global.apps_initialize(
	         user_id      => l_user_id,
		   		 resp_id      => l_resp_id,
		   		 resp_appl_id => l_resp_appl_id
					 );
   END IF;
   --Adding the template before submiting the concurrent program
   l_xml_layout  := 
      fnd_request.add_layout('FND','XXFND_USER_RESP_TMP','en','US','PDF');
       
	 l_request_id := fnd_request.submit_request
	       (application => 'FND',
          program     => 'XXFND_USER_RESP',
          description => 'FND User Responsibilities',
          sub_request => FALSE,
          argument1   => l_user_name
         );
	 --Commit the request id
   IF l_request_id != 0
	 THEN
	    DBMS_OUTPUT.PUT_LINE('Program FND User Responsibilities submitted');
	    DBMS_OUTPUT.PUT_LINE('Request id is '||l_request_id);
      COMMIT;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Failed to submit program XXFND User Responsibilities');
   END IF;
END;

Program XXFND User Responsibilities submitted
Request id is 477724806

How to Delete a Concurrent Program in Oracle Apps R12

Due to auditing requirements in Oracle EBS, a hard deletion of concurrent programs via the front-end is typically not allowed. The recommended approach is to:

1. End Date the Concurrent Program: Set an end date for the program, preventing it from being used in the future.
2. Remove from Request Groups: Detach the program from all associated request groups.

Caution: While backend deletion is possible, it should be performed with extreme caution and only in non-production environments after a full backup. The pvt simply delete the program and executable without varifying.

--USE WITH EXTREME CAUTION AND AFTER BACKUP
BEGIN  
   fnd_program.delete_program('XXENO_TST_PROG','INV');
   fnd_program.delete_executable('XXENO_TST_EXE','INV');
   COMMIT;
END;

Note: Always perform a full database backup before attempting any manual data manipulation using SQL.

Concurrent Program Migration in Oracle Apps R12

FNDLOAD is a utility used to migrate configuration data, including concurrent programs, between different Oracle Apps R12 instances.

Download Command:

FNDLOAD apps/apps_pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PROG.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XX_CONC_PROG"

Upload Command:

FNDLOAD apps/apps_pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PROG.ldt

This is essential for deploying custom Oracle R12 concurrent programs across environments. Review our article if you want to explore more on this.

Troubleshooting: Concurrent Program Running for a Long Time in Oracle Apps R12

If an Oracle Apps R12 concurrent program is running longer than expected, common causes include:

• Poor SQL Performance: Inefficient SQL queries within the program.
• Database Locks: Blocking issues within the database.
• Resource Issues: Insufficient system resources (CPU, memory, I/O).

Solutions:

Check Session: Use the following SQL to monitor the database session:

    SELECT * FROM v$session WHERE program LIKE '%FNDCRM%';
    

• Tune SQL Queries: Analyze and optimize SQL statements within the concurrent program.
• Analyze AWR Reports: Review Automatic Workload Repository (AWR) reports for database performance insights.

Understanding Oracle Apps Concurrent Program Incompatibility

Oracle Apps R12 allows you to define incompatible concurrent programs to prevent specific programs from running simultaneously. This is crucial for maintaining data integrity and avoiding conflicts.

Example: A “Close Period” program should typically not run while a “Mass Additions Create” program is active in Oracle Assets.

How to Set a Program Incompatible?

Navigate to: Concurrent → Program → Incompatible Programs and define the conflicting programs.

What is the Concurrent Manager?

The Concurrent Manager is a crucial background process in Oracle Apps R12 responsible for managing and executing concurrent programs and requests. It controls the scheduling, prioritization, and execution of these batch processes.

Understanding the Cost Manager Concurrent Program in Oracle Apps

The Cost Manager concurrent program in Oracle Apps (specifically within Oracle Inventory and Costing modules) is responsible for updating inventory transaction costs. It’s a critical program that should always be running for accurate costing and inventory adjustments.

Program Name: Cost Manager

Check its status with:

SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE program_application_id = 707
  AND concurrent_program_id = (SELECT concurrent_program_id FROM fnd_concurrent_programs WHERE concurrent_program_name = 'CSTMSCCC');

Troubleshooting: Oracle Concurrent Program Running for a Long Time (Revisited)

As mentioned earlier, address long-running Oracle concurrent programs by tuning SQL, checking database sessions, and potentially restarting the concurrent manager if necessary.

How to Schedule Concurrent Programs in Oracle Apps R12

Automating the execution of concurrent programs in Oracle R12 is done through scheduling:

1. Change Responsibility where the program can be run
2. Navigate to: View → Requests → Submit New Request.
3. Click the “Schedule” button to define the schedule:
   • Once: Specify the day and time of the single execution of the program
   • Periodically: Set up recurring execution (e.g., daily, weekly, monthly).
   • Specific Days/Time: Define exact dates and times for the program to run.
4. Submit the concurrent progarm

Troubleshooting: Oracle Concurrent Program Incompatibility Not Working

If Oracle concurrent program incompatibility rules are not working as expected, check the following:

• Setup Verification: Ensure the incompatible program rules are correctly defined in the “Incompatible Programs” form.
• Scheduling Conflicts: Review the schedules of the conflicting programs to ensure they are indeed attempting to run simultaneously.
• Conflict Resolution Manager Status: Verify that the “Conflict Resolution Manager” is active and functioning correctly.

Detailed Oracle R12 Concurrent Program Query

To retrieve detailed information about a specific concurrent program in Oracle R12:

SELECT fcpt.user_concurrent_program_name concurrent_program_name,
       fcp.concurrent_program_name concurrent_short_name, 
       fe.executable_name,
       fe.execution_file_name, 
       flv.meaning execution_method
  FROM fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_executables fe,
       fnd_lookup_values_vl flv
 WHERE fcp.executable_id = fe.executable_id
   AND fcpt.concurrent_program_id=fcp.concurrent_program_id
   AND fcpt.language=userenv('lang')
   AND flv.lookup_type='CP_EXECUTION_METHOD_CODE'
   AND flv.lookup_code=fe.execution_method_code
   AND fcp.concurrent_program_name = 'XXENO_TST_PROG';

Querying Oracle Apps Concurrent Program Responsibility Access

To identify which Oracle Apps R12 responsibilities have access to a specific concurrent program, you can use the following SQL query:

SELECT fr.responsibili ty_name, fcpt.user_concurrent_program_name
  FROM fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_request_group_units frgu,
       fnd_request_groups frg,
       fnd_responsibility fr
 WHERE fcp.concurrent_program_id = fcpt.concurrent_program_id
   AND fcp.concurrent_program_id = frgu.request_unit_id
   AND frgu.request_group_id = frg.request_group_id
   AND fr.request_group_id = frg.request_group_id
   AND fcpt.user_concurrent_program_name LIKE '%Invoice%';

This query helps in understanding user access control for Oracle concurrent programs.

Simple Concurrent Program Query in Oracle Apps R12

To get a basic list of concurrent programs in Oracle R12, use this query:

SELECT concurrent_program_name, 
       executable_name, 
       description
  FROM fnd_concurrent_programs
 WHERE concurrent_program_name LIKE '%XX%';

Key Oracle Apps R12 Concurrent Program Tables

Understanding the underlying database tables is crucial for advanced analysis and troubleshooting. Key concurrent program in Oracle Apps R12 tables include:

• FND_CONCURRENT_PROGRAMS: Stores the definition of concurrent programs.
• FND_EXECUTABLES: Defines the execution method and file/procedure name.
• FND_CONCURRENT_REQUESTS: Tracks the status and details of submitted concurrent requests.
• FND_REQUEST_GROUPS: Groups concurrent programs for assignment to responsibilities.
• FND_RESPONSIBILITY: Defines user responsibilities and their associated request groups.

Final Thoughts on Oracle Apps R12 Concurrent Programs

Mastering Oracle Apps R12 Concurrent Programs is essential for efficiently automating business processes, optimizing system performance, and ensuring the smooth operation of your Oracle E-Business Suite environment. By understanding the concepts, creation process, management techniques, and troubleshooting steps outlined in this guide, you can effectively leverage the power of background processing in Oracle R12.

FAQs