PROCEDURE get_program_create_sql
(
p_program_name VARCHAR2
)IS
p_conc_prog_rec_type fnd_concurrent_programs_vl%ROWTYPE;
p_executable_rec_type fnd_executables_form_v%ROWTYPE;
v_application_name VARCHAR2(240);
v_err_flag VARCHAR2(1) := 'N';
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
BEGIN
BEGIN
SELECT responsibility_id,
application_id
INTO v_resp_id,
v_resp_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Responsibility not found');
END;
BEGIN
SELECT user_concurrent_program_name,
application_id,
enabled_flag,
concurrent_program_name,
description,
save_output_flag,
print_flag,
output_print_style,
required_style,
srs_flag,
run_alone_flag,
output_file_type,
enable_trace,
restart,
nls_compliant,
icon_name,
executable_id
INTO p_conc_prog_rec_type.user_concurrent_program_name,
p_conc_prog_rec_type.application_id,
p_conc_prog_rec_type.enabled_flag,
p_conc_prog_rec_type.concurrent_program_name,
p_conc_prog_rec_type.description,
p_conc_prog_rec_type.save_output_flag,
p_conc_prog_rec_type.print_flag,
p_conc_prog_rec_type.output_print_style,
p_conc_prog_rec_type.required_style,
p_conc_prog_rec_type.srs_flag,
p_conc_prog_rec_type.run_alone_flag,
p_conc_prog_rec_type.output_file_type,
p_conc_prog_rec_type.enable_trace,
p_conc_prog_rec_type.restart,
p_conc_prog_rec_type.nls_compliant,
p_conc_prog_rec_type.icon_name,
p_conc_prog_rec_type.executable_id
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_name = p_program_name;
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Program not found');
END;
BEGIN
SELECT application_name
INTO v_application_name
FROM fnd_application_vl
WHERE application_id = p_conc_prog_rec_type.application_id;
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Application not found');
END;
BEGIN
SELECT executable_name,
application_name
INTO p_executable_rec_type.executable_name,
p_executable_rec_type.application_name
FROM fnd_executables_form_v
WHERE executable_id = p_conc_prog_rec_type.executable_id;
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Executable NOT FOUND');
END;
IF nvl(v_err_flag, 'N') = 'N' THEN
dbms_output.put_line('
DECLARE
v_cnt NUMBER := 0;
BEGIN'
);
dbms_output.put_line('
fnd_global.apps_initialize(
user_id => ' || fnd_profile.value('USER_ID') || ', --user_id,
resp_id => ' || v_resp_id || ' , --responsibility id,
resp_appl_id => ' || v_resp_appl_id || '
);'
);
dbms_output.put_line('
fnd_program.REGISTER
(
PROGRAM => ''' || p_conc_prog_rec_type.user_concurrent_program_name || ''',
application => ''' || v_application_name || ''',
enabled => ''' || p_conc_prog_rec_type.enabled_flag || ''',
short_name => ''' || p_conc_prog_rec_type.concurrent_program_name || ''',
description => ''' || p_conc_prog_rec_type.description || ''',
executable_short_name => ''' || p_executable_rec_type.executable_name || ''',
executable_application => ''' || p_executable_rec_type.application_name || ''',
execution_options => NULL,
priority => NULL,
save_output => ''' || p_conc_prog_rec_type.save_output_flag || ''',
PRINT => ''' || p_conc_prog_rec_type.print_flag || ''',
cols => NULL,
ROWS => NULL,
style => ''' || p_conc_prog_rec_type.output_print_style || ''',
style_required => ''' || p_conc_prog_rec_type.required_style || ''',
printer => NULL,
request_type => NULL,
request_type_application => NULL,
use_in_srs => ''' || p_conc_prog_rec_type.srs_flag || ''',
allow_disabled_values => ''N'',
run_alone => ''' || p_conc_prog_rec_type.run_alone_flag || ''',
output_type => ''' || p_conc_prog_rec_type.output_file_type || ''',
enable_trace => ''' || p_conc_prog_rec_type.enable_trace || ''',
restart => ''' || p_conc_prog_rec_type.restart || ''',
nls_compliant => ''' || p_conc_prog_rec_type.nls_compliant || ''',
icon_name => ''' || p_conc_prog_rec_type.icon_name || ''',
language_code => ''US'',
mls_function_short_name => NULL,
mls_function_application => NULL,
incrementor => NULL,
refresh_portlet => NULL
);'
);
dbms_output.put_line('
SELECT count(1)
INTO v_cnt
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_name = ''' || p_conc_prog_rec_type.concurrent_program_name || ''';
IF nvl(v_cnt, 0) != 0 THEN
COMMIT;
dbms_output.put_line(''Creation program completed'');
ELSE
ROLLBACK;
dbms_output.put_line(''Creation program failed'');
END IF;'
);
dbms_output.put_line('
END;'
);
END IF;
END get_program_create_sql;
留言列表
Record (2)
