Script to copy concurrent program including executable, parameter and request groups
Usage: 1) Replace l_prefix value with desired value
2) Replace 'Financial Tax Register' with the program that you want to copy or replicate
DECLARE
l_prefix VARCHAR2 (30) := 'XX '; -- new program pre-fix
CURSOR c_program_details IS
SELECT (SELECT APPLICATION_NAME
FROM apps.fnd_application_vl
WHERE application_id = e.APPLICATION_ID)
exec_application,
e.EXECUTABLE_NAME,
e.USER_EXECUTABLE_NAME,
e.DESCRIPTION
exec_description,
(SELECT meaning
FROM apps.fnd_lookup_values_vl
WHERE 1 = 1
AND lookup_code = e.EXECUTION_METHOD_CODE
AND lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND enabled_flag = 'Y')
exec_method,
e.EXECUTION_FILE_NAME,
e.SUBROUTINE_NAME,
e.EXECUTION_FILE_PATH,
(SELECT APPLICATION_NAME
FROM apps.fnd_application_vl
WHERE application_id = P.APPLICATION_ID)
prog_application,
p.CONCURRENT_PROGRAM_NAME,
p.USER_CONCURRENT_PROGRAM_NAME,
p.description
prog_description,
p.PRINT_FLAG,
p.RUN_ALONE_FLAG,
p.SRS_FLAG,
p.SAVE_OUTPUT_FLAG,
p.REQUIRED_STYLE,
p.OUTPUT_FILE_TYPE,
p.enable_trace,
p.restart,
p.nls_compliant,
(SELECT application_short_name
FROM apps.fnd_application_vl
WHERE application_id = p.application_id)
prog_appl_short_name
FROM apps.fnd_executables_vl e, apps.fnd_concurrent_programs_vl p
WHERE 1 = 1
AND p.USER_CONCURRENT_PROGRAM_NAME = 'Financial Tax Register'
--and
e.EXECUTABLE_NAME='XX..'
AND p.EXECUTABLE_ID = e.EXECUTABLE_ID
AND p.EXECUTABLE_APPLICATION_ID = e.application_id;
BEGIN
FOR r_program IN c_program_details
LOOP
-- Register Executable
BEGIN
FND_PROGRAM.executable (
executable => l_prefix || r_program.USER_EXECUTABLE_NAME -- user executable name
,
application => r_program.EXEC_APPLICATION -- application
,
short_name => l_prefix || r_program.EXECUTABLE_NAME -- short_name
,
description => l_prefix || r_program.EXEC_DESCRIPTION -- description
,
execution_method => r_program.exec_method -- execution_method
,
execution_file_name => r_program.EXECUTION_FILE_NAME -- execution_file_name
,
subroutine_name => r_program.SUBROUTINE_NAME -- subroutine_name
,
icon_name => '' -- icon
,
language_code => 'US' -- language_code
,
execution_file_path => r_program.EXECUTION_FILE_PATH --execution_file_path
);
--COMMIT;
DBMS_OUTPUT.put_line ('Executable created');
END;
-- Register CP
BEGIN
--
Register Concurrent program
APPS.FND_PROGRAM.register (
program =>
l_prefix || r_program.USER_CONCURRENT_PROGRAM_NAME,
application => r_program.PROG_APPLICATION,
enabled => 'Y',
short_name => l_prefix || r_program.CONCURRENT_PROGRAM_NAME,
description => l_prefix || r_program.PROG_DESCRIPTION,
executable_short_name =>
l_prefix || r_program.EXECUTABLE_NAME,
executable_application => r_program.EXEC_APPLICATION,
execution_options => '',
priority => NULL,
save_output => r_program.SAVE_OUTPUT_FLAG,
PRINT => r_program.PRINT_FLAG,
cols => NULL,
rows => NULL,
style => NULL,
style_required => r_program.REQUIRED_STYLE,
printer => NULL,
request_type => NULL,
request_type_application => NULL,
use_in_srs => r_program.SRS_FLAG,
allow_disabled_values => 'N',
run_alone => r_program.RUN_ALONE_FLAG,
output_type => r_program.OUTPUT_FILE_TYPE,
enable_trace => r_program.ENABLE_TRACE,
restart
=> r_program.RESTART,
nls_compliant => r_program.NLS_COMPLIANT,
icon_name => NULL,
language_code => 'US',
mls_function_short_name => NULL,
mls_function_application => NULL,
incrementor => NULL,
refresh_portlet => NULL);
--COMMIT;
DBMS_OUTPUT.put_line ('CP created');
END;
-- Copy Parameters
FOR param_rec
IN (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$.' || r_program.CONCURRENT_PROGRAM_NAME
AND vs.flex_value_set_id = params.flex_value_set_id(+))
LOOP
BEGIN
fnd_program.parameter (
program_short_name =>
l_prefix || r_program.CONCURRENT_PROGRAM_NAME, -- program short name
application => r_program.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);
END;
END LOOP;
DBMS_OUTPUT.put_line
('Parameters copied');
-- Assign program to request
group(s)
-- Copy request groups
FOR r_req_group
IN (SELECT rg.REQUEST_GROUP_NAME,
(SELECT APPLICATION_NAME
FROM apps.fnd_application_vl
WHERE application_id = rg.application_id)
rg_application
FROM apps.fnd_request_groups rg,
apps.Fnd_Request_Group_Units u,
apps.fnd_concurrent_programs_vl p
WHERE 1 = 1
AND p.USER_CONCURRENT_PROGRAM_NAME =
r_program.USER_CONCURRENT_PROGRAM_NAME
AND u.REQUEST_UNIT_TYPE = 'P'
AND u.UNIT_APPLICATION_ID = p.application_id
AND u.REQUEST_UNIT_ID = p.concurrent_program_id
AND u.REQUEST_GROUP_ID = rg.request_group_id
AND u.application_id = rg.application_id)
LOOP
BEGIN
FND_PROGRAM.add_to_group (
l_prefix || r_program.CONCURRENT_PROGRAM_NAME,
r_program.PROG_APPLICATION -- application
,
r_req_group.REQUEST_GROUP_NAME
--
Request Group Name
,
r_req_group.rg_application); --Report Group Application
--COMMIT;
END;
END LOOP;
DBMS_OUTPUT.put_line
('Assignment completed');
EXIT; -- exit after one round of loop
END LOOP;
--commit;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
NULL;
END;
/
