eBS R11/R12 Programmatically submit concurrent programs

Very often you need to submit a concurrent program in your code. Either in Forms or PL/SQL. The following code can be used as quick start to build this into your code.

Set Layout

If you want to run your program using BI Publisher and you want to add a specific layout, it's best to specify that in your coding using.
Language will be US or NL for example. Output format ofcourse depends on your program, but can be PDF.
The variable v_result_r is a boolean.

v_result_r := fnd_request.add_layout
         (
           template_appl_name => l_application_short_name
         , template_code      => l_my_template_code
         , template_language  => l_language
         , template_territory => null
         , output_format      => l_output_format
         );

Set Printer

If you want the output to be printed,you set the print options just before you submit your program.
The variable v_print_opt is a boolean.

 v_print_opt := fnd_request.set_print_options
                  ( PRINTER        => l_printer
                  , STYLE          => NULL
                  , COPIES         => l_nr_copies
                  , SAVE_OUTPUT    => TRUE
                  , PRINT_TOGETHER => 'N'
                  );  

Submit Program

After setting the print options and layout options you submit your program. You can use current time or have it wait a little if necessary.
The variable l_request_id is a number.

You can hard code the application short name or fetch it on forehand. Add this in a cursor or subprocedure for example

            select a.application_short_name
            into l_application_short_name
            from   fnd_application a
            , fnd_concurrent_Programs p
            where p.concurrent_program_name = l_conc_prog_name
            and p.application_id = a.application_id
            ;

Then submit your program

l_request_id := fnd_request.submit_request 
( application => l_application_short_name
, program     => l_conc_prog_name
, start_time  => sysdate
, sub_request => false
, argument1   => l_param1
, argument2   => l_param2
, ...
);   

-- Important to commit!! In forms you may want to set the 
-- message level very high to avoid getting a message about it.

v_message_level := :system.message_level;
:system.message_level := 20;

COMMIT;

:system.message_level := v_message_level;

-- Usually you also display a message if l_request_id = 0, 
-- because in that case it could not submit your program.
-- Either because it lost focus (lost login information)
-- or your program/application combination does not exist.

Wait for request to finish

In some cases you want to wait until your submitted program has finished. Either to display results or submit another program.
The dev_status returns ERROR, CANCELLED, TERMINATED in case of errors. You add a max wait to make sure your program does not keep waiting for ever. Don't wait for your current program (FND_GLOBAL.Conc_Request_Id) to finish ;-).

        l_phase                VARCHAR2(240);
        l_dev_phase            VARCHAR2(240);
        l_dev_status        VARCHAR2(240);
        l_message            VARCHAR2(240);
        l_conc_status        BOOLEAN;
        l_status            VARCHAR2(240);


 l_conc_status := APPS.FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id => l_request_id
,interval   => 5            -- Sleep 5 seconds between checks.
,max_wait   => 600           
,phase      => l_phase
,status     => l_status
,dev_phase  => l_dev_phase
,dev_status => l_dev_status
,message    => l_message
); 

Query to find programs run today

This query shows you all concurrent programs that have run today.

SELECT fcp.user_concurrent_program_name,

                fcp.concurrent_program_name,

                fcr.request_id,

                fcr.request_date,

                flv.meaning status,

                fcr.status_code,

                fcr.completion_text,

                fcr.logfile_name,

                fcr.outfile_name,

                fcr.argument_text

  FROM fnd_concurrent_programs_vl fcp,

       fnd_concurrent_requests    fcr,

       fnd_lookup_values          flv

 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

   AND trunc(fcr.last_update_date) = trunc(SYSDATE)

   AND flv.lookup_code = fcr.status_code

   AND flv.lookup_type = 'CP_STATUS_CODE'

   AND flv.language = USERENV ('LANG')

 ORDER BY fcr.request_date,

          fcr.request_id DESC;