fnd_conc_req_outputs - To View XML Publisher generated output files

Initial output file generated by Concurrent Request will be stored in FND_CONCURRENT_REQUESTS table (logfile_name and outfile_name) and all remaining output files generated Concurrent Request including XML Publisher generated output will be stored in fnd_conc_req_outputs table.

Script to copy parameters from one concurrent program to other

Input:
l_from_prog_short_name  => From Program short name
l_to_prog_short_name => To Program short name
l_to_prog_appl_short_name => To program application short name


Script:
declare
l_from_prog_short_name varchar2(240) := '<From Prog Name>';
l_to_prog_short_name varchar2(240) := '<to prog name>';
l_to_prog_appl_short_name varchar2(240) := '<to prog appl short name>';

cursor cp_from_program_parms
is
select
 params.column_seq_num sequence
,params.end_user_column_name parameter
,params.description
,params.enabled_flag enabled
,vs.flex_value_set_name value_set
,params.default_type
,params.default_value
,params.required_flag required
,params.security_enabled_flag enable_security
,params.range_code range
,params.display_flag display
,params.display_size
,params.maximum_description_len description_size
,params.concatenation_description_len concatenated_description_size
,params.form_left_prompt prompt
,params.srw_param token
,'N'    cd_parameter  --cp.cd_parameter
from FND_DESCR_FLEX_COL_USAGE_VL params, fnd_flex_value_sets vs
where 1=1
and descriptive_flexfield_name = '$SRS$.'||l_from_prog_short_name
and vs.flex_value_set_id=params.flex_value_set_id(+);
begin
for param_rec in cp_from_program_parms
 loop

  begin
  fnd_program.parameter(
program_short_name    =>  l_to_prog_short_name, -- program short name
application           =>  l_to_prog_appl_short_name, -- application short name
sequence              =>  param_rec.sequence,
  parameter             =>  param_rec.parameter,
--parameter             =>  param_rec.token,
description           =>  param_rec.description,
enabled              =>  param_rec.enabled,
value_set            =>  param_rec.value_set,
default_type           =>  param_rec.default_type,
default_value          =>  param_rec.default_value,
required                 =>  param_rec.required,
enable_security           =>  param_rec.enable_security,
range                      =>  param_rec.range,
display                   =>  param_rec.display,
display_size               =>  param_rec.display_size,
description_size          =>  param_rec.description_size,
concatenated_description_size =>  param_rec.concatenated_description_size,
prompt                       =>  param_rec.prompt,
  token                        =>  param_rec.token,
  --token                        =>  null,
cd_parameter                 =>  param_rec.cd_parameter);
  exception
   when others
    then
    dbms_output.put_line(param_rec.parameter);
    dbms_output.put_line(sqlerrm);
  end;

 end loop;
  --commit;
 dbms_output.put_line('Success');
exception
 when others then
  rollback;
  dbms_output.put_line(SQLERRM);
end;
/

Add a Concurrent Program to a Responsibility using PL/SQL Script

Input:
l_program_name: Give concurrent program name you want to add to a responsibility               
l_responsibility_name: Give responsibility name for which you want add the above program.

Script:
DECLARE
   -- Provide concurrent program name and Responsibility name here:
   l_program_name                VARCHAR2 (240) := '<&>';
   l_responsibility_name         VARCHAR2 (240) := 'XX OKS INQUIRY';

   ---- Private variables to program ----
   l_prog_short_name             VARCHAR2 (240);
   l_prog_appl_short_name        VARCHAR2 (240);

   l_req_group_name              VARCHAR2 (240);
   l_req_group_appl_short_name   VARCHAR2 (240);
BEGIN
   -- Fetch Request group details from Responsibility
   BEGIN
      SELECT rga.application_short_name rg_appl_short_name,
             rg.request_group_name
        INTO l_req_group_appl_short_name, l_req_group_name
        FROM fnd_responsibility_vl resp,
             fnd_request_groups rg,
             fnd_application rga
       WHERE     UPPER (resp.responsibility_name) LIKE
                    UPPER (l_responsibility_name || '%')
             AND resp.group_application_id = rg.application_id
             AND resp.request_group_id = rg.request_group_id
             AND rga.application_id = rg.application_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Exception when fetching request group details:' || SQLERRM);
         RETURN;
   END;

   -- Fetch Program details from Conc program name
   BEGIN
      SELECT cp.concurrent_program_name, cpa.application_short_name
        INTO l_prog_short_name, l_prog_appl_short_name
        FROM fnd_concurrent_programs_vl cp, fnd_application cpa
       WHERE     1 = 1
             AND cp.user_concurrent_program_name LIKE l_program_name || '%'
             AND cp.application_id = cpa.application_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Exception when fetching program details:' || SQLERRM);
         RETURN;
   END;


   FND_PROGRAM.add_to_group (l_prog_short_name           -- program_short_name
                                              ,
                             l_prog_appl_short_name -- application name/ application short name
                                                   ,
                             l_req_group_name            -- Request Group Name
                                             ,
                             l_req_group_appl_short_name); --Request Group Application
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception :' || SQLERRM);
      ROLLBACK;
END;
/

Query to get list of parameters of a concurrent program

Input: 
Replace <&Conc Program Name> in the following query with the concurrent program name:

Query:

select
 params.column_seq_num sequence
,params.end_user_column_name parameter
,params.description
,params.enabled_flag enabled
,vs.flex_value_set_name value_set
,params.default_type
,params.default_value
,params.required_flag required
,params.security_enabled_flag enable_security
,params.range_code range
,params.display_flag display
,params.display_size
,params.maximum_description_len description_size
,params.concatenation_description_len concatenated_description_size
,params.form_left_prompt prompt
,params.srw_param token
,'N'    cd_parameter  --cp.cd_parameter
from FND_DESCR_FLEX_COL_USAGE_VL params, fnd_flex_value_sets vs, fnd_concurrent_programs_vl fcp
where 1=1
and fcp.user_concurrent_program_name like '<&Conc Program Name>'||'%'
and descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
and vs.flex_value_set_id=params.flex_value_set_id(+)

Query to get list of responsibilities that have access to a concurrent program

Background:
Some times you know the concurrent program but you do not know how to access (do not to which responsibility it is attached). In this case, the following query can be helpful

Input:
Concurrent program name

 SELECT frv.responsibility_name,fcp.user_concurrent_program_name, frg.*
  FROM fnd_request_groups frg,
      fnd_request_group_units frgu,
      fnd_concurrent_programs_vl fcp,
      fnd_responsibility_vl frv
      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 frgu.unit_application_id    = fcp.application_id
    AND fcp.user_concurrent_program_name like '<conc program name>'||'%'
    and frv.request_group_id=frg.request_group_id