How to create concurrent programs from database?

Here are couple of APIs useful for creating any concurrent programs from the backend database.

1)    Registering the Executable from back-end:

Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is the PL/SQL code to create an executable from back-end.

BEGIN
  FND_PROGRAM.executable(executable => 'XXFIN TEST EXECUTABLE' , -- Executable Name
  application=>'XXFIN' , -- Application Short Name
  short_name=>'XXFINTSTEXE' , -- Executable Short Name
  description=>'Test Executable created from Backend' ,     -- Description,DEFAULT NULL
  execution_method=>'PL/SQL Stored Procedure',              -- Execution Method
  execution_file_name=>'XXFIN_TEST_PROC' ,                  -- Execution File Name,DEFAULT NULL
  subroutine_name=>NULL ,                                   -- Subroutine Name,DEFAULT NULL
  icon_name=>NULL ,                                         -- Icon Name,DEFAULT NULL
  language_code=>'US' ,                                     -- Language Code,DEFAULT 'US'
  execution_file_path=>NULL                                 -- Execution File Path, DEFAULT NULL
  );
  COMMIT;
END;

View from Frontend:

Notes:

1] The above API inserts the new records in FND_EXECUTABLES and FND_EXECUTABLES_TL table.

2] You can use the below query to get all the Execution Methods available:

SELECT MEANING “Execution Method”

FROM fnd_lookup_values

WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’

AND enabled_flag  = ‘Y’;

2)    Registering the Concurrent program from back-end:     

Usually we create Concurrent program in the front-end, but this can be done from the database tier too. Below is the program to create a Concurrent program from back-end.

BEGIN
  FND_PROGRAM.register(program =>'Test CP from DB', -- CP Name
  application =>'XXFIN' , -- Application Short Name
  enabled =>'Y',                                    -- Flag to Enable/Disable a CP
  short_name =>'XXFINTSTCPDB', -- CP Short Name
  description =>'Test CP created from Backend' ,    -- Description,DEFAULT NULL
  executable_short_name =>'XXFINTSTEXE', -- Executable Short Name
  executable_application =>'XXFIN' , -- Executable Application Short Name
  execution_options => NULL,                        -- Execution Options,DEFAULT NULL,
  priority => NULL,                                 -- Priority,DEFAULT NULL,
  save_output =>'Y',                                -- Save Output,DEFAULT 'Y',
  PRINT =>'Y' ,                                     -- Print,DEFAULT 'Y',
  cols => NULL, -- DEFAULT NULL,
  rows => NULL, -- DEFAULT NULL,
  style => NULL,                                    -- DEFAULT NULL,
  style_required =>'N' ,                            -- DEFAULT 'N',
  printer => NULL,                                  -- DEFAULT NULL,
  request_type => NULL,                             -- DEFAULT NULL,
  request_type_application => NULL,                 -- DEFAULT NULL,
  use_in_srs =>'N' ,                                -- DEFAULT 'N',
  allow_disabled_values =>'N' ,                     -- DEFAULT 'N',
  run_alone =>'N' ,                                 -- DEFAULT 'N',
  output_type =>'TEXT',                             -- DEFAULT 'TEXT'
  enable_trace =>'N' ,                              -- DEFAULT 'N',
  restart =>'Y' ,                                   -- DEFAULT 'Y',
  nls_compliant =>'Y' ,                             -- DEFAULT 'Y',
  icon_name => NULL,                                -- DEFAULT NULL,
  language_code => 'US',                            -- DEFAULT 'US',
  mls_function_short_name => NULL,                  -- DEFAULT NULL,
  mls_function_application => NULL,                 -- DEFAULT NULL,
  incrementor => NULL, -- DEFAULT NULL,
  refresh_portlet => NULL                           -- DEFAULT NULL,
  );
  COMMIT;
END;

View from Frontend:

Notes:

1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.

2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

3)    Attaching the concurrent program to the request group

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too. Below is the program to Attach Concurrent program to the request group from back-end.

BEGIN
  FND_PROGRAM.add_to_group('XXFINTSTCPDB', -- Concurrent Program Short Name
  'XXFIN' , -- Application Short Name
  'All Reports',                           -- Report Group Name
  'SQLAP'); -- Report Group Application
  COMMIT;
END;

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all.

Oracle Apps Concurrent Processing:An Introduction

This article gives an introduction of  Concurrent Processing in Oracle Application.

Concurrent Program:

An instance of an execution file, along with parameter definitions and incompatibilities. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilities.

Concurrent Program Executable:

An executable file that performs a specific task. The file may be a program written in a standard language, a reporting tool or an operating system language.

An execution method can be a PL/SQL Stored Procedure, an Oracle Tool such as Oracle Reports or SQL*Plus, a spawned process, or an operating system host language.

Concurrent Request:

A request to run a concurrent program as a concurrent process.

Concurrent Process:

An instance of a running concurrent program that runs simultaneously with other concurrent processes.

Concurrent Manager:

A program that processes user’s requests and runs concurrent programs. System Administrators define concurrent managers to run different kinds of requests.

There are many concurrent managers each monitoring the flow within each apps area.

But there are 3 MASTER CONCURRENT MANAGERS:

1. Internal Concurrent Manager (ICM): This is the one which monitors all other CMs

2. Standard Manager (SM) : This takes care of report running and batch jobs

3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatibility checks.

We cannot delete a concurrent manager… but we can disable it… but it’s not recommended.

Concurrent Queue:

List of concurrent requests awaiting to be processed by a concurrent manager.

Phases and Statuses through which a concurrent request runs:

A concurrent request proceeds through three, possibly four, life cycle stages or phases: 

• Pending                                       Request is waiting to be run
• Running                                       Request is running
• Completed                                   Request has finished
• Inactive                                       Request cannot be run

Within each phase, a request’s condition or status may change.  Below appears a listing of each phase and the various states that a concurrent request can go through. 

Concurrent Request Phase and Status   

Phase: PENDING        

• Normal: Request is waiting for the next available manager.
• Standby: Program to run request is incompatible with other program(s) currently running.
• Scheduled: Request is scheduled to start at a future time or date.
• Waiting: A child request is waiting for its Parent request to mark it ready to run.   

Phase:RUNNING         

• Normal: Request is running normally.
• Paused: Parent request pauses for all its child requests to complete. 
• Resuming: All requests submitted by the same parent request have completed running.  The  Parent   request is waiting to be restarted.
• Terminating: Running request is terminated, by selecting Terminate in the Status field of the Request Details zone.

Phase:COMPLETED      

• Normal: Request completes normally.
• Error: Request failed to complete successfully.
• Warning: Request completes with warnings.  For example, a report is generated successfully but fails to print.
• Cancelled: Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
• Terminated: Running request is terminated, by selecting Terminate in the Status field of the Request Details zone.

 Phase:INACTIVE            

• Disabled: Program to run request is not enabled.  Contact your system administrator.
• On Hold: Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
• No Manager: No manager is defined to run the request.  Check with your system administrator.

 Different execution methods of executabls:

• FlexRpt                             The execution file is written using the FlexReport API.
• FlexSql                             The execution file is written using the FlexSql API.
• Host                                 The execution file is a host script.
• Oracle Reports                  The execution file is an Oracle Reports file.
• PL/SQL Stored Procedure   The execution file is a stored procedure.
• SQL*Loader                      The execution file is a SQL script.
• SQL*Plus                          The execution file is a SQL*Plus script.
• SQL*Report                      The execution file is a SQL*Report script.
• Spawned                          The execution file is a C or Pro*C program.
• Immediate                       The execution file is a program written to run as a subroutine of the concurrent manager.

 Output formats of a concurrent program:

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