Tuesday 8 December 2015

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;
/

No comments:

Post a Comment