concurrent program auto commit

If the program completes successfully, it's retcode will be '0' and that session committed automatically i.e., you need not to put explicit commit in the PL/SQL procedure.If your PL/SQL procedure is being called from a concurrent program NO explicit commit is required. 

Which means you need not to put commit at the end of the procedure if your procedure is registered as concurrent program

API to add concurrent program to a request group

API to add concurrent program to a request group 



Here is the way to add a concurrent program to a request group from back end.

 DECLARE

   v_program_short_name    VARCHAR2 (200);
   v_program_application   VARCHAR2 (200);
   v_request_group         VARCHAR2 (200);
   v_group_application     VARCHAR2 (200);
   v_check                 VARCHAR2 (2);

BEGIN

   v_program_short_name  := 'XX_CONC_PROG_SHORT_NAME';
   v_program_application := 'XX Order Management';
   v_request_group       := 'XX Request Group';
   v_group_application   := 'XXFND';


   apps.fnd_program.add_to_group
(program_short_name  => v_program_short_name,
 program_application => v_program_application,
 request_group       => v_request_group,
 group_application   => v_group_application
  );

  --COMMIT;

  END;





  SELECT 'Y'
  INTO   v_check
    FROM   FND_REQUEST_GROUPS FRG,
             FND_REQUEST_GROUP_UNITS FRGU,
               FND_CONCURRENT_PROGRAMS FCP
      WHERE  FRG.request_group_id       = FRGU.REQUEST_GROUP_ID
      AND    FRG.application_id         = FRGU.application_id
    AND    FRGU.request_unit_id       = FCP.concurrent_program_ID
    AND fcp.CONCURRENT_PROGRAM_NAME='XX_CONC_PROG_SHORT_NAME'

FNDLOAD utility in Oracle Apps

In this blog we will see what is FNDLOAD and why/how we use in Oracle Apps.

What is FNDLOAD:

This is utility provided by Oracle to migrate objects/entities from one instance to another.

Why do we need to migrate?

Good question :-) . Generally we need development in one server (let us call it instance hence forth) and business users will test in another instance and finally production would be final instance. So every time, we cannot and should NOT define concurrent programs/Request groups/Flex fields/Menus and messages etc…,
To make our life simple we will use utility.

How it works?

It is 2 step process,

1)      Download

2)      Upload

Download

 Using download command we will generate a .ldt file, which will hold the definition of entity.

Ex: We want to migrate a concurrent program from  instance 'Dev' to another.

First we need to download the definition from 'Dev' instance.

We will see the commands for the same in the later section for now just understand the concept.

Upload

Here we will load the definition of entity into new instance. (You need not to manually define the executable and concurrent program ;-D sounds good..isn't it? )

Using FNDLOAD we can migrate following entities

1. Concurrent Programs
2. Request Groups
3. Profile Values
4. Form Personalizations 
5. Value Set Definitions
6. Fnd Messages 
7. Lookup Values
8. User Definitions 
9. Request Sets 
10. Responsibilities
11. Form Functions

Here are few commands:

Concurrent Programs

Download:

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM_NAME APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"

• This command will create XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt file in your server.
• XXCUST will be your custom Application Shortname where concurrent program is registered
• XX_CUSTOM_ORACLE_INTERFACE_PROG Will be the name of your request group
• PROGRAM_NAME is your conc program short name.

Upload:
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt

References:

For the commands  and usage you can refer below site:

 http://oracle.anilpassi.com/oracle-fndload-script-examples.html
https://blogs.oracle.com/prajkumar/entry/oracle_fndload_scripts

Query to find concurrent program details

Query to find concurrent program details

Query to fetch concurrent program and responsibility details


SELECT frt.responsibility_name,
       frg.request_group_name,
       frg.description,
       fcpt.user_concurrent_program_name
  FROM fnd_request_groups frg,
       fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_responsibility_tl frt,
       fnd_responsibility frs
 WHERE     frgu.unit_application_id = fcp.application_id
       AND frgu.request_unit_id = fcp.concurrent_program_id
       AND frg.request_group_id = frgu.request_group_id
       AND frg.application_id = frgu.application_id
       AND fcpt.source_lang = USERENV ('LANG')
       AND fcp.application_id = fcpt.application_id
       AND fcp.concurrent_program_id = fcpt.concurrent_program_id
       AND frs.application_id = frt.application_id
       AND frs.responsibility_id = frt.responsibility_id
       AND frt.source_lang = USERENV ('LANG')
       AND frs.request_group_id = frg.request_group_id
       AND frs.application_id = frg.application_id
       --AND   fcp.concurrent_program_name = <shortname>
       AND fcpt.user_concurrent_program_name LIKE :p_user_concurrent_program_name

Following query will return concurrent program executable details:

SELECT fcpl.user_concurrent_program_name,
executable_name,
fe.execution_file_name,
fe.execution_method_code,
fa.application_short_name
FROM fnd_concurrent_programs_vl fcpl,
fnd_executables fe,
fnd_application fa
WHERE fcpl.executable_id = fe.executable_id
AND fa.application_id = fcpl.application_id
AND fcpl.user_concurrent_program_name =:p_Conc_prog_name;


This Query Will Return Request Group To Which The concurrent program Has Been Attached

SELECT fcp.USER_CONCURRENT_PROGRAM_NAME,
fcp.CONCURRENT_PROGRAM_NAME,
frg.REQUEST_GROUP_NAME
FROM apps.FND_REQUEST_GROUPS frg,
apps.FND_REQUEST_GROUP_UNITS frgu,
apps.fnd_concurrent_programs_vl fcp
WHERE frgu.REQUEST_UNIT_ID = fcp.CONCURRENT_PROGRAM_ID
AND frg.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
AND fcp.USER_CONCURRENT_PROGRAM_NAME =':p_Conc_prog_name;

Types of executables

There are 11 types of executables

1. Oracle Reports - .rdf
2. SQL * Plus - .SQL
3. PL/SQL Procedures - .pkb / .pks
4. SQL * Loader file - .ctl
5. Java Structured Procedure - .java / .class
6. Host(UNIX) - .sh
7. Spawned—C or Pro*C
8. Immediate
9. Java Concurrent Program
10. Request set stage Function
11. Multi-Language Function.

Concurrent Process: Simultaneously executes program running in the background with online operations to fully utilize the hardware capacity.
Concurrent Program: Can write a program that runs as a concurrent process. Use concurrent program for long running, data-intensive tasks.
Major features: 

1.       On-line requests
2.       Automatic scheduling
3.       Concurrent processing
4.       Online request review
5.       Concurrent manager
6.       Simultaneous queuing

On-line Request: You and end-user can submit requests from forms to start any concurrent program.
Automatic Scheduling: Oracle Application Object Library automatically schedules requests based on when they were submitted, their priority, and their compatibility with programs those are already running.

Concurrent Manager: Cocurrent Managers are components of concurrent processing that monitors and run, time-consuming, non-interactive tasks without tying up your terminal.
Concurrent manager processes requests and does the work in the background, giving the ability to run multiple tasks simultaneously.
Internal Concurrent Manager starts up, verifies the status of, resets, and shuts down the individual managers.

Simultaneous Queuing: It lets requests wait in many queues at once to ensure that the first available concurrent manager starts the request.
Concurrent Program Executable: Links an execution file and the method used to execute it with defined concurrent program. This mathod may be a program written in standard language, a reporting tool, or an operating system language.
A Concurrent program execution file is an operating system file or database stored procedure.

Concurrent Program Definition: A concurrent program is an instance of an execution file, along with parameter definitions and incompatibilities. Concurrent programs use concurrent program executables to locate the correct execution file.
Concurrent Program written in: Oracle reports, PL/SQL package procedures, SQL *Loader, SQL *Plus, Host script.
 
Parent request: A parent request is a concurrent request that submits another concurrent request.

Child request: A child request is a concurrent request submitted by another concurrent request.
Concurrent Manager: Concurrent Managers are components of Concurrent Processing that monitor and run, time-consuming,non-interactive tasks without tying up terminal.
Whenever request submits to run a task, a concurrent manager processes that request and does the work in background, giving the ability to run multiple tasks simultaneously.
  

Concurrent Program and Executable Details

The following SQL query provide you executable file name ,top name , etc...

SELECT b.user_concurrent_program_name, b.concurrent_program_name,

a.user_executable_name,

DECODE (a.execution_method_code,

'I', 'PL/SQL Stored Procedure',

'H', 'Host',

'S', 'Immediate',

'J', 'Java Stored Procedure',

'K', 'Java concurrent program',

'M', 'Multi Language Function',

'P', 'Oracle reports',

'B', 'Request Set Stage Function',

'A', 'Spawned',

'L', 'SQL*Loader',

'Q', 'SQL*Plus',

'E', 'Pearl concurrent Programm',

'Unkown Type'

) TYPE,

a.execution_file_name, a.execution_file_path, a.application_name,

c.basepath

FROM fnd_executables_form_v a,

fnd_concurrent_programs_vl b,

fnd_application c

WHERE a.application_id = c.application_id

AND a.executable_id = b.executable_id

AND a.application_id = b.application_id

AND a.executable_id > 4

AND b.user_concurrent_program_name LIKE &p_conc_prog

Query to find request group details

SELECT DISTINCT user_request_set_name

   FROM FND_REQUEST_SETS_TL

  WHERE request_set_id IN

           (SELECT request_set_id

              FROM FND_REQUEST_SET_PROGRAMS

             WHERE concurrent_program_id =

                      (SELECT CONCURRENT_PROGRAM_ID

                         FROM fnd_concurrent_programs_tl

                        WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( :p_Conc_prog_name)));