Oracle Concurrent Program

 A concurrent program is an executable file that runs simultaneously with online operations and with other concurrent programs.

• We need a concurrent program for ..

1. Any long running data intensive program
2. Operating system script
3. Oracle Reports

• The various steps to define and register a concurrent program are.
  • Define concurrent program executable
  • Define concurrent program
  • Include the concurrent program in a request group
  • Run concurrent program through submit request form.

 

A] DefineConcurrent Program Executable:

   

Enter the execution method as

1. Flex Rpt                                  written using the Flex Report API.
2. Flex Sql                                  written using the Flex Sql API.
3. Host                                         a host script.
4. Oracle Reports                        an Oracle Reports file.
5. PL/SQL Stored Procedure   The execution file is a stored procedure.
6. SQL*Loader                          a SQL script.
7. SQL*Plus                                a SQL*Plus script.
8. SQL*Report                           a SQL*Report script.
9. Spawned                                a C or Pro*C program.
10. Immediate                           is a program written to run as a subroutine of the concurrent manager.

• Enter the execution file name without the file extension.
• For PL/SQL stored procedures enter the <package>.<procedure name> in the execution        file name. This procedure must have 2 out parameters of type varchar2 preferably with names errbuf and errout. These two parameters should be added before adding any other parameters.

 

=============================================
CREATE CONCURRENT PROGRAM EXECUTABLE FROM BACKEND
==============================================
SELECT * FROM FND_APPLICATION_VL
WHERE 1=1
AND APPLICATION_SHORT_NAME LIKE 'XXCUST'
DECLARE
   v_executable   VARCHAR2 (50) := 'XXAA_AR_AGING';
BEGIN
   fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 21623,
                               resp_appl_id      => 660
                              );                              
   fnd_program.executable (executable               => 'XXAA Ar Aging Report',                           application              =>'XXCUST Custom Application',
                           short_name               => 'XXAA_AR_AGING',
                           execution_method         => 'Oracle Reports',
                           execution_file_name      => 'XXAA_AR_AGING'
                          );                         
   COMMIT;
   DBMS_OUTPUT.put_line (   'succeefully created executable name is '|| v_executable );
END;

=============================================
DELETE CONCURRENT PROGRAM EXECUTABLE FROM BACKEND
==============================================*/ 
DECLARE
   v_short_name   VARCHAR2 (50) := 'XXAA_AR_AGING';
BEGIN
   fnd_program.delete_executable (executable_short_name      => v_short_name,
                                  application                => 'XXCUST Custom Application'
                                 );
   COMMIT;
   DBMS_OUTPUT.put_line('Concurrent Program Executable succeefully Deleted'|| v_short_name );
END;

SELECT * FROM fnd_executables
WHERE execution_file_name = 'XXAA_AR_AGING'

B] Define Concurrent Program:

• Define a concurrent program,
• Choose an executable created, which will be executed once this concurrent program is scheduled to run.

Output formats of a concurrent program:

• HTML.
• PDF.
• TEXT.
• PS (Post Script).
• PCL (HP’s Printer Control Language).

/*=============================================
CREATE CONCURRENT PROGRAM DEFINE FROM BACKEND
==============================================*/ 
SELECT * FROM FND_APPLICATION_VL
WHERE 1=1
AND APPLICATION_NAME = 'XXCUST Custom Application'

DECLARE
   v_name   VARCHAR2 (50) := 'XXAA Ar Aging Report';
   v_short_name   VARCHAR2 (50) := 'XXAA_AR_AGING';
BEGIN
   fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 21623,
                               resp_appl_id      => 660
                              );
   fnd_program.REGISTER (program                     => v_name,
                         application                 => 'XXCUST Custom Application',
                         enabled                     => 'Y',
                         short_name                  => v_short_name,
                         executable_short_name       => v_short_name,
                         executable_application      => 'XXCUST Custom Application',
                         style                       => 'A4',
                         output_type                 => 'TEXT',
                         use_in_srs                  => 'Y'
                        );
   COMMIT;
   DBMS_OUTPUT.put_line ('succeefully created concurrent program '|| v_short_name );
END;

SELECT * FROM fnd_concurrent_programs_vl
WHERE concurrent_program_name='XXAA_AR_AGING'

=============================================
DELETE CONCURRENT PROGRAM FROM BACKEND
==============================================*/
DECLARE
   v_short_name   VARCHAR2 (50) := 'XXAA_AR_AGING';
BEGIN
   fnd_program.delete_program (program_short_name      => v_short_name,
                               application             => 'XXCUST Custom Application'
                              );
   COMMIT;
   DBMS_OUTPUT.put_line ( 'Concurrent Program succeefully Deleted ' || v_short_name );
END;

SELECT * FROM fnd_concurrent_programs_vl
WHERE concurrent_program_name='XXAA_AR_AGING'

c] Define Concurrent Parameters:

• The parameters are the placeholders for the input values to a concurrent program.
• If the execution method of a concurrent program is Oracle Reports then each parameter is linked to the actual report parameter via the “Token” field in the parameters window
• For PL/SQL stored procedures these parameters are passed to the program by position.

/*=============================================
CREATE PARAMETERS TO THE CONCURRENT PROGRAM FROM BACKEND
==============================================*/
DECLARE
   v_cprogram   VARCHAR2 (50) := 'XX_APPS88_HRMS';
BEGIN
   fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 21623,
                               resp_appl_id      => 660
                              );
   fnd_program.parameter (program_short_name                 => v_cprogram,
                          application                        => 'Human Resources',
                          SEQUENCE                           => 10,
                          parameter                          => 'Employee Number',
                          enabled                            => 'Y',
                          value_set                          => '240 char',
                          display_size                       => 50,
                          description_size                   => 20,
                          concatenated_description_size      => 20,
                          prompt                             => 'P_EMPLOYEE_NUM',
                          token                              => 'P_EMPLOYEE_NUM'
                         );
   COMMIT;
   DBMS_OUTPUT.put_line (' successfully created parameter for concurrent program ' || v_cprogram );
EXCEPTION
   WHEN OTHERS
        THEN DBMS_OUTPUT.put_line ('error out here' || SQLCODE || SQLERRM);
END;
=============================================
DELETE PARAMETERS IN THE CONCURRENT PROGRAM FROM BACKEND
==============================================*/ 
DECLARE
   v_short_name   VARCHAR2 (50) := 'XXAA_AR_AGING'; --> Conc Program Short Name
BEGIN
   fnd_program.DELETE_PARAMETER (program_short_name      => v_short_name,
                                 application             => 'XXCUST Custom Application',
                                 parameter               => 'P_DATE'
                                );
   COMMIT;
   DBMS_OUTPUT.put_line (   'Parameter succeefully Deleted For The Concurrent Program ' || v_short_name );
END;

D] Define Request Group :

• Query the request group and add your concurrent program to the group.
• A request group is a collection of reports and concurrent programs.
• A system administrator defines request group in order to control user access to reports and concurrent programs.
• 

=============================================
ADD CONCURRENT PROGRAM TO THE REQUEST GROUP FROM BACKEND
==============================================*/ 
SELECT * FROM FND_APPLICATION_VL
WHERE 1=1
AND APPLICATION_NAME = 'XXCUST Custom Application'

SELECT * FROM FND_RESPONSIBILITY_VL
WHERE 1=1
AND RESPONSIBILITY_NAME = 'Receivables, Vision Operations (USA)'

SELECT * FROM fnd_request_groups
WHERE 1=1
AND request_group_name like 'Receivables All'

DECLARE
   v_short_name   VARCHAR2 (50) := 'XXAA_AR_AGING'; --> Conc Program Short Name
BEGIN
   fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 21623,
                               resp_appl_id      => 660
                              );
   fnd_program.add_to_group (program_short_name       => v_short_name,
                             program_application      => 'XXCUST Custom Application',
                             request_group            => 'Receivables All',
                             group_application        => 'Receivables'
                            );
   COMMIT;
   DBMS_OUTPUT.put_line (   'succeefully attached concurrent program  to request group' || v_short_name );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('error out here' || SQLCODE || SQLERRM);
END;

=============================================
DELETE CONCURRENT PROGRAM FROM THE REQUEST GROUP FROM BACKEND
==============================================
DECLARE
   v_short_name   VARCHAR2 (50) := 'XXAA_AR_AGING'; --> Conc Program Short Name
BEGIN
   fnd_global.apps_initialize (user_id           => 1318,
                               resp_id           => 21623,
                               resp_appl_id      => 660
                              );
   fnd_program.remove_from_group (program_short_name       => v_short_name,
                                  program_application      => 'XXCUST Custom Application',
                                  request_group            => 'Receivables All',
                                  group_application        => 'Receivables'
                                 );
   COMMIT;
   DBMS_OUTPUT.put_line (   'Successfully Deleted executable name is ' || v_short_name );
END;

Standard Report Submission Form:

•   form for running and monitoring your application’s reports/concurrent programs at specific time interval.
• This lets user specify run and print options and parameter value for reports and concurrent programs.
• Use fnd_file.put_line(fnd_file.log, ‘any message’) to show message in conc program log file.

Switch Responsibility to System Administrator > Requets