The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION last_updated_by_f RETURN NUMBER IS
BEGIN
RETURN created_by_f;
FUNCTION last_update_date_f RETURN DATE IS
BEGIN
RETURN creation_date_f;
FUNCTION last_update_login_f RETURN NUMBER IS
BEGIN
return 0;
SELECT application_id
INTO application_id_ret
FROM fnd_application
WHERE application_short_name = application_name_in;
SELECT concurrent_program_id
INTO program_id
FROM fnd_concurrent_programs
WHERE application_id = program_application_id
AND concurrent_program_name = program_short_name;
SELECT request_group_id
INTO group_id
FROM fnd_request_groups
WHERE application_id = group_application_id
AND request_group_name = request_group;
SELECT request_set_id
INTO set_id
FROM fnd_request_sets
WHERE application_id = set_application_id
AND request_set_name = request_set;
SELECT request_set_stage_id
INTO stage_id
FROM fnd_request_set_stages
WHERE set_application_id = set_appl_id
AND request_set_id = set_id
AND stage = stage_name;
SELECT executable_id
INTO fn_id
FROM fnd_executables
WHERE application_id = fn_appl_id
AND executable_name = name;
SELECT parameter_id
INTO param_id
FROM fnd_stage_fn_parameters_vl
WHERE application_id = fn_appl_id
AND function_id = fn_id
AND parameter_name = name;
last_update_login fnd_concurrent_programs.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_concurrent_programs.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_concurrent_programs.last_updated_by%TYPE
:= last_updated_by_f;
select fnd_concurrent_programs_s.nextval
into program_id
from sys.dual;
select argument_method_code, queue_control_flag, queue_method_code,
run_alone_flag, enabled_flag, srs_flag,
print_flag, output_print_style, required_style, execution_options,
class_application_id, concurrent_class_id,
executable_application_id, executable_id,
save_output_flag, printer_name
into amc, qc, qmc, ra, enabled, srs, print, style, rs, eo,
cl_app_id, cl_id, ex_app_id, ex_id, so, printer
from fnd_concurrent_programs
where application_id = 0
and concurrent_program_name = program_name;
insert into fnd_concurrent_programs
(application_id, concurrent_program_id, concurrent_program_name,
last_update_date, last_updated_by, execution_method_code,
queue_method_code, argument_method_code, queue_control_flag,
run_alone_flag, enabled_flag,
print_flag, output_print_style, required_style, execution_options,
class_application_id, concurrent_class_id, srs_flag,
created_by, creation_date, last_update_login,
executable_application_id, executable_id, save_output_flag,
printer_name, request_set_flag, restart, nls_compliant, enable_trace,
output_file_type)
values( set_application_id, program_id,
new_name, last_update_date, last_updated_by,
'I', qmc, 'D', qc, ra, enabled, print, style, rs, eo,
cl_app_id, cl_id, srs,
created_by, creation_date, last_update_login,
ex_app_id, ex_id, so, printer, 'Y', 'Y', 'N', 'N', 'TEXT');
insert into fnd_concurrent_programs_tl
(application_id,
concurrent_program_id,
last_update_date,
last_updated_by,
description,
user_concurrent_program_name,
created_by,
creation_date,
last_update_login,
language,
source_lang
)
select
set_application_id,
program_id,
create_program.last_update_date,
create_program.last_updated_by,
create_program.description,
user_program_name,
create_program.created_by,
create_program.creation_date,
create_program.last_update_login,
l.language_code,
lang
from fnd_languages l
where l.installed_flag in ('I', 'B');
select concurrent_program_id
into prog_id
from fnd_request_set_stages
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = stage_id;
select concurrent_program_id
into prog_id
from fnd_request_sets
where application_id = set_appl_id
and request_set_id = set_id;
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
/* update program definitions */
UPDATE fnd_concurrent_programs SET
queue_method_code = 'B'
WHERE ((application_id = application_id_i
AND concurrent_program_id = concurrent_program_id_i)
OR (application_id = to_run_application_id_i
AND concurrent_program_id = to_run_concurrent_program_id_i));
INSERT INTO fnd_concurrent_program_serial(running_concurrent_program_id,
running_application_id,
running_type,
to_run_concurrent_program_id,
to_run_application_id,
to_run_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
incompatibility_type)
VALUES(concurrent_program_id_i,
application_id_i,
'S',
to_run_concurrent_program_id_i,
to_run_application_id_i,
'S',
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inc_type);
INSERT
INTO fnd_concurrent_program_serial(running_concurrent_program_id,
running_application_id,
running_type,
to_run_concurrent_program_id,
to_run_application_id,
to_run_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
incompatibility_type)
VALUES(to_run_concurrent_program_id_i,
to_run_application_id_i,
'S',
concurrent_program_id_i,
application_id_i,
'S',
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inc_type);
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
select fnd_request_sets_s.nextval into set_id from sys.dual;
select user_id
into owner_id
from fnd_user
where user_name = owner;
insert into fnd_request_sets
(application_id, request_set_id, request_set_name,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
allow_constraints_flag, print_together_flag,
start_date_active, end_date_active,
concurrent_program_id, owner)
values
(set_appl_id, set_id, short_name,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
upper(incompatibilities_allowed), upper(print_together), start_date,
end_date, program_id, owner_id);
insert into FND_REQUEST_SETS_TL (
application_id, request_set_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, user_request_set_name, description,
language, source_lang
) select
set_appl_id, set_id, create_set.creation_date,
create_set.created_by, create_set.last_update_date,
create_set.last_updated_by,
create_set.last_update_login,
create_set.name, create_set.description,
L.LANGUAGE_CODE,
create_set.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
select executable_id
into function_id
from fnd_executables
where application_id = function_appl_id
and executable_name = function_short_name;
select fnd_request_set_stages_s.nextval
into stage_id
from sys.dual;
select user_request_set_name
into set_name
from fnd_request_sets_vl
where request_set_id = set_id
and application_id = set_appl_id;
insert into fnd_request_set_stages
(set_application_id, request_set_id, request_set_stage_id,
stage_name,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
allow_constraints_flag,
concurrent_program_id, critical, outcome,
function_id, function_application_id,
display_sequence)
values
(set_appl_id, set_id, stage_id, short_name,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
upper(incompatibilities_allowed),
program_id,
upper(critical), 'C', function_id, function_appl_id,
display_sequence);
insert into FND_REQUEST_SET_STAGES_TL (
set_application_id, request_set_id, request_set_stage_id,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, user_stage_name,
description, language, source_lang
) select
set_appl_id, set_id, stage_id,
add_stage.creation_date, add_stage.created_by,
add_stage.last_update_date,
add_stage.last_updated_by, add_stage.last_update_login, name,
add_stage.description, l.language_code, add_stage.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
update fnd_request_sets
set start_stage = stage_id
where request_set_id = set_id
and application_id = set_appl_id;
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
update fnd_request_set_stages
set success_link = to_stage_id
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = from_stage_id;
update fnd_request_set_stages
set warning_link = to_stage_id
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = from_stage_id;
update fnd_request_set_stages
set error_link = to_stage_id
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = from_stage_id;
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
SELECT printer_style_name
INTO print_style_name
FROM fnd_printer_styles
WHERE printer_style_name = style;
SELECT printer_style_name
INTO print_style_name
FROM fnd_printer_styles_tl
WHERE user_printer_style_name = style
AND rownum = 1;
select 'x'
into dummy
from fnd_printer
where printer_name = printer;
insert into fnd_request_set_programs
(set_application_id, request_set_id, request_set_program_id,
request_set_stage_id,
sequence, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
program_application_id, concurrent_program_id,
number_of_copies, save_output_flag,
printer, print_style, critical)
values
(set_appl_id, set_id, fnd_request_set_programs_s.nextval,
stage_id,
program_sequence, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
program_appl_id, program_id,
number_of_copies, save_output,
printer, print_style_name, upper(add_program.critical));
delete from fnd_request_set_program_args
where (application_id, request_set_id, request_set_program_id) in
(select p.set_application_id, p.request_set_id,
p.request_set_program_id
from fnd_request_set_programs p
where p.set_application_id = set_appl_id
and p.request_set_id = set_id
and p.request_set_stage_id = stage_id);
delete from fnd_request_set_programs
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = stage_id;
select concurrent_program_name
into program_short_name
from fnd_concurrent_programs p,
fnd_request_set_stages r
where r.set_application_id = set_appl_id
and r.request_set_id = set_id
and r.request_set_stage_id = stage_id
and r.set_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id;
fnd_program.delete_program(program_short_name, set_application);
delete from fnd_stage_fn_parameter_values
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = stage_id;
delete from fnd_request_set_stages_tl
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = stage_id;
delete from fnd_request_set_stages
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = stage_id;
update fnd_request_set_stages
set success_link = null
where set_application_id = set_appl_id
and request_set_id = set_id
and success_link = stage_id;
update fnd_request_set_stages
set warning_link = null
where set_application_id = set_appl_id
and request_set_id = set_id
and warning_link = stage_id;
update fnd_request_set_stages
set error_link = null
where set_application_id = set_appl_id
and request_set_id = set_id
and error_link = stage_id;
delete from fnd_request_set_program_args
where (application_id, request_set_id, request_set_program_id) in
(select p.set_application_id, p.request_set_id,
p.request_set_program_id
from fnd_request_set_programs p
where p.set_application_id = set_appl_id
and p.request_set_id = set_id
and p.program_application_id = program_appl_id
and p.concurrent_program_id = program_id
and p.request_set_stage_id = stage_id
and p.sequence = program_sequence);
delete from fnd_request_set_programs
where set_application_id = set_appl_id
and request_set_id = set_id
and program_application_id = program_appl_id
and concurrent_program_id = program_id
and request_set_stage_id = stage_id
and sequence = program_sequence;
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
select request_set_program_id
into set_program_id
from fnd_request_set_programs
where request_set_id = set_id
and set_application_id = set_appl_id
and concurrent_program_id = program_id
and program_application_id = program_appl_id
and request_set_stage_id = stage_id
and sequence = program_sequence;
select lookup_code
into default_type_code
from fnd_lookup_values l
where l.lookup_type = 'FLEX_DEFAULT_TYPE'
and l.enabled_flag = 'Y'
and sysdate between nvl(l.start_date_active, Sysdate)
and nvl(l.end_date_active, Sysdate)
and upper(meaning) = upper(default_type)
and rownum = 1;
select application_column_name
into appl_column_name
from fnd_descr_flex_column_usages
where descriptive_flexfield_name = '$SRS$.'||program
and descriptive_flex_context_code = 'Global Data Elements'
and end_user_column_name = parameter
and application_id = program_appl_id;
insert into fnd_request_set_program_args
(application_id, request_set_id, request_set_program_id,
descriptive_flex_appl_id, descriptive_flexfield_name,
application_column_name, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, display_flag,
insert_flag, update_flag, default_type, default_value,
shared_parameter_name)
values
(set_appl_id, set_id, set_program_id,
program_appl_id, '$SRS$.'||program,
appl_column_name, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, upper(display),
upper(modify), upper(modify), default_type_code, default_value,
shared_parameter);
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
insert into Fnd_Request_Group_Units( APPLICATION_ID,
REQUEST_GROUP_ID,
REQUEST_UNIT_TYPE,
UNIT_APPLICATION_ID,
REQUEST_UNIT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values( group_application_id,
group_id,
'S',
set_application_id,
set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login);
delete from fnd_request_group_units u
where application_id = group_application_id
and request_group_id = group_id
and request_unit_type = 'S'
and unit_application_id = set_application_id
and request_unit_id = set_id;
PROCEDURE delete_program_parameter(program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2 DEFAULT NULL,
stage IN VARCHAR2,
set_application IN VARCHAR2,
program_sequence IN NUMBER,
parameter IN VARCHAR2)
is
set_id
fnd_request_sets.request_set_id%TYPE;
select request_set_program_id
into set_program_id
from fnd_request_set_programs s,
fnd_concurrent_programs p
where p.concurrent_program_name = program
and p.application_id = program_appl_id
and p.concurrent_program_id = s.concurrent_program_id
and p.application_id = s.program_application_id
and s.request_set_id = set_id
and s.set_application_id = set_appl_id
and s.request_set_stage_id = stage_id
and s.sequence = program_sequence;
delete from fnd_request_set_program_args a
where a.application_id = set_appl_id
and a.request_set_id = set_id
and a.request_set_program_id = set_program_id
and (a.descriptive_flex_appl_id,
a.descriptive_flexfield_name,
a.application_column_name) in
(select u.application_id,
u.descriptive_flexfield_name,
u.application_column_name
from fnd_descr_flex_column_usages u
where u.application_id = program_appl_id
and u.descriptive_flexfield_name = '$SRS$.'||program
and u.descriptive_flex_context_code = 'Global Data Elements'
and u.end_user_column_name = parameter);
END delete_program_parameter;
PROCEDURE delete_set(request_set IN VARCHAR2,
application IN VARCHAR2) is
set_appl_id
fnd_application.application_id%TYPE;
select request_set_stage_id
from fnd_request_set_stages
where request_set_id = set_id
and set_application_id = set_appl_id;
select concurrent_program_name
into program_short_name
from fnd_concurrent_programs p,
fnd_request_sets r
where r.application_id = set_appl_id
and r.request_set_id = set_id
and r.application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id;
fnd_program.delete_program(program_short_name, application);
DELETE FROM fnd_request_group_units
WHERE request_unit_type = 'S'
AND request_unit_id = set_id
AND unit_application_id = set_appl_id;
DELETE FROM fnd_request_set_program_args
WHERE application_id = set_appl_id
AND request_set_id = set_id;
DELETE FROM fnd_request_set_programs
WHERE set_application_id = set_appl_id
AND request_set_id = set_id;
select concurrent_program_name
into program_short_name
from fnd_concurrent_programs p,
fnd_request_set_stages r
where r.set_application_id = set_application_id
and r.request_set_id = set_id
and r.request_set_stage_id = rec.request_set_stage_id
and r.set_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id;
fnd_program.delete_program(program_short_name, application);
DELETE from fnd_stage_fn_parameter_values
where set_application_id = set_appl_id
and request_set_id = set_id;
DELETE FROM fnd_request_set_stages_tl
WHERE set_application_id = set_appl_id
AND request_set_id = set_id;
DELETE FROM fnd_request_set_stages
WHERE set_application_id = set_appl_id
AND request_set_id = set_id;
DELETE FROM fnd_request_sets_tl
WHERE application_id = set_appl_id
AND request_set_id = set_id;
DELETE FROM fnd_request_sets
WHERE application_id = set_appl_id
AND request_set_id = set_id;
END delete_set;
PROCEDURE delete_incompatibility(request_set IN VARCHAR2,
application IN VARCHAR2,
stage IN VARCHAR2 DEFAULT NULL,
inc_prog IN VARCHAR2 DEFAULT NULL,
inc_prog_application IN VARCHAR2 DEFAULT NULL,
inc_request_set IN VARCHAR2 DEFAULT NULL,
inc_set_application IN VARCHAR2 DEFAULT NULL,
inc_stage IN VARCHAR2 DEFAULT NULL)
IS
application_id_i fnd_application.application_id%TYPE;
delete from fnd_concurrent_program_serial
where to_run_application_id = to_run_application_id_i
and to_run_concurrent_program_id = to_run_concurrent_program_id_i
and running_application_id = application_id_i
and running_concurrent_program_id = concurrent_program_id_i;
delete from fnd_concurrent_program_serial
where to_run_application_id = application_id_i
and to_run_concurrent_program_id = concurrent_program_id_i
and running_application_id = to_run_application_id_i
and running_concurrent_program_id = to_run_concurrent_program_id_i;
/* update incompatibilies */
BEGIN
update fnd_concurrent_programs
set queue_method_code = 'I'
WHERE application_id = application_id_i
AND concurrent_program_id = concurrent_program_id_i
AND NOT EXISTS
(select 'x'
from fnd_concurrent_program_serial
where running_application_id = application_id_i
and running_concurrent_program_id =
concurrent_program_id_i);
update fnd_concurrent_programs
set queue_method_code = 'I'
WHERE application_id = to_run_application_id_i
AND concurrent_program_id = to_run_concurrent_program_id_i
AND NOT EXISTS
(select 'x'
from fnd_concurrent_program_serial
where running_application_id = to_run_application_id_i
and running_concurrent_program_id =
to_run_concurrent_program_id_i);
END delete_incompatibility;
select 'Y'
into dummy
from fnd_concurrent_program_serial
where to_run_application_id = to_run_application_id_i
and to_run_concurrent_program_id = to_run_concurrent_program_id_i
and running_application_id = application_id_i
and running_concurrent_program_id = concurrent_program_id_i;
select 'y'
into dummy
from fnd_request_set_stages f
where set_application_id = set_appl_id
and request_set_id = set_id
and f.stage_name = stage;
select 'y'
into dummy
from fnd_request_set_programs
where set_application_id = set_appl_id
and request_set_id = set_id
and request_set_stage_id = stage_id
and program_application_id = program_appl_id
and concurrent_program_id = program_id
and sequence = program_sequence;
select request_set_program_id
into set_program_id
from fnd_request_set_programs s,
fnd_concurrent_programs p
where p.concurrent_program_name = program
and p.application_id = program_appl_id
and p.concurrent_program_id = s.concurrent_program_id
and p.application_id = s.program_application_id
and s.request_set_id = set_id
and s.request_set_stage_id = stage_id
and s.set_application_id = set_appl_id
and s.sequence = program_sequence;
select 'y'
into dummy
from fnd_request_set_program_args a
where a.application_id = set_appl_id
and a.request_set_id = set_id
and a.request_set_program_id = set_program_id
and (a.descriptive_flex_appl_id,
a.descriptive_flexfield_name,
a.application_column_name) in
(select u.application_id,
u.descriptive_flexfield_name,
u.application_column_name
from fnd_descr_flex_column_usages u
where u.application_id = program_appl_id
and u.descriptive_flexfield_name = '$SRS$.'||program
and u.descriptive_flex_context_code = 'Global Data Elements'
and u.end_user_column_name = parameter);
select 'y'
into dummy
from fnd_request_group_units u
where application_id = group_application_id
and request_group_id = group_id
and request_unit_type = 'S'
and unit_application_id = set_application_id
and request_unit_id = set_id;
last_update_date fnd_executables.last_update_date%TYPE;
last_updated_by fnd_executables.last_updated_by%TYPE;
last_update_login fnd_executables.last_update_login%TYPE;
last_update_date := last_update_date_f;
last_updated_by := last_updated_by_f;
last_update_login := last_update_login_f;
select fnd_executables_s.nextval into executable_id from sys.dual;
insert into FND_EXECUTABLES (
APPLICATION_ID, EXECUTABLE_ID, EXECUTABLE_NAME,
EXECUTION_METHOD_CODE, EXECUTION_FILE_NAME, SUBROUTINE_NAME,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, icon_name
) values (
application_id, executable_id, short_name,
'B', plsql_name, null,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, icon_name
);
insert into FND_EXECUTABLES_TL (
APPLICATION_ID, EXECUTABLE_ID, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, USER_EXECUTABLE_NAME, DESCRIPTION,
LANGUAGE, SOURCE_LANG
) select
stage_function.application_id, executable_id,
stage_function.creation_date,
stage_function.created_by, stage_function.last_update_date,
stage_function.last_updated_by,
stage_function.last_update_login, function_name,
stage_function.description,
l.language_code, stage_function.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
select 'y'
into dummy
from fnd_executables
where application_id = exec_application_id
and executable_name = function_short_name;
PROCEDURE delete_function(function_short_name IN VARCHAR2,
application IN VARCHAR2) is
exec_application_id
fnd_application.application_id%TYPE;
SELECT executable_id
INTO exec_id
FROM fnd_executables
WHERE application_id = exec_application_id
AND executable_name = function_short_name;
select 'x' into dummy
from sys.dual
where not exists
(select 1
from fnd_request_set_stages
where function_application_id = exec_application_id
and function_id = exec_id);
delete from fnd_stage_fn_parameters_tl
where function_id = exec_id
and application_id = exec_application_id;
delete from fnd_executables_tl
where executable_id = exec_id
and application_id = exec_application_id;
delete from fnd_executables
where executable_id = exec_id
and application_id = exec_application_id;
delete from fnd_request_group_units
where request_unit_id = exec_id
and unit_application_id = exec_application_id
and request_unit_type = 'F';
end delete_function;
select 'y'
into dummy
from fnd_request_group_units u
where application_id = group_application_id
and request_group_id = group_id
and request_unit_type = 'F'
and unit_application_id = fn_application_id
and request_unit_id = fn_id;
last_update_login fnd_request_groups.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_request_groups.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_request_groups.last_updated_by%TYPE
:= last_updated_by_f;
insert into Fnd_Request_Group_Units( APPLICATION_ID,
REQUEST_GROUP_ID,
REQUEST_UNIT_TYPE,
UNIT_APPLICATION_ID,
REQUEST_UNIT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values( group_application_id,
group_id,
'F',
fn_application_id,
fn_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login);
delete from fnd_request_group_units u
where application_id = group_application_id
and request_group_id = group_id
and request_unit_type = 'F'
and unit_application_id = fn_application_id
and request_unit_id = fn_id;
last_update_date fnd_executables.last_update_date%TYPE;
last_updated_by fnd_executables.last_updated_by%TYPE;
last_update_login fnd_executables.last_update_login%TYPE;
last_update_date := last_update_date_f;
last_updated_by := last_updated_by_f;
last_update_login := last_update_login_f;
select fnd_stage_fn_parameters_s.nextval into param_id from sys.dual;
insert into FND_STAGE_FN_PARAMETERS_TL (
APPLICATION_ID, FUNCTION_ID, PARAMETER_ID, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PARAMETER_NAME, USER_PARAMETER_NAME, DESCRIPTION,
LANGUAGE, SOURCE_LANG
) select
fn_appl_id,fn_id, param_id, function_parameter.creation_date,
function_parameter.created_by, function_parameter.last_update_date,
function_parameter.last_updated_by,
function_parameter.last_update_login, parameter_short_name,
parameter_name, function_parameter.description,
l.language_code, function_parameter.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
select 'y'
into dummy
from fnd_stage_fn_parameters_vl
where application_id = fn_appl_id
and function_id = fn_id
and parameter_name = parameter;
PROCEDURE delete_function_parameter(function_short_name IN VARCHAR2,
application IN VARCHAR2,
parameter IN VARCHAR2) is
fn_appl_id fnd_application.application_id%TYPE;
select parameter_id
into param_id
from fnd_stage_fn_parameters_vl
where application_id = fn_appl_id
and function_id = fn_id
and parameter_name = parameter;
delete from fnd_stage_fn_parameters_tl
where function_id = fn_id
and application_id = fn_appl_id
and parameter_id = param_id;
delete from fnd_stage_fn_parameter_values
where function_id = fn_id
and function_application_id = fn_appl_id
and parameter_id = param_id;
end delete_function_parameter;
last_update_date fnd_executables.last_update_date%TYPE;
last_updated_by fnd_executables.last_updated_by%TYPE;
last_update_login fnd_executables.last_update_login%TYPE;
last_update_date := last_update_date_f;
last_updated_by := last_updated_by_f;
last_update_login := last_update_login_f;
select s.function_id, s.function_application_id, p.parameter_id
into fn_id, fn_appl_id, param_id
from fnd_request_set_stages s, fnd_stage_fn_parameters_vl p
where s.request_set_id = set_id
and s.set_application_id = set_appl_id
and s.request_set_stage_id = stage_id
and s.function_id = p.function_id
and s.function_application_id = p.application_id
and p.parameter_name = parameter;
insert into FND_STAGE_FN_PARAMETER_VALUES (
SET_APPLICATION_ID, REQUEST_SET_ID, REQUEST_SET_STAGE_ID,
FUNCTION_APPLICATION_ID, FUNCTION_ID,
PARAMETER_ID, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, VALUE
) values
(set_appl_id, set_id, stage_id, fn_appl_id, fn_id,
param_id, function_parameter_value.creation_date,
function_parameter_value.created_by,
function_parameter_value.last_update_date,
function_parameter_value.last_updated_by,
function_parameter_value.last_update_login,
function_parameter_value.value );
rset_last_updated_date date;
select phase_code, status_code, queue_method_code, request_type,
REQUEST_DATE, to_number(argument2),to_number(argument1), req_information, RUN_NUMBER
into phasecode, statuscode, queuemethod, req_type,
req_request_date, rset_id, appl_id, req_data, current_run_number
from fnd_concurrent_requests
where request_id = requestId;
select LAST_UPDATE_DATE into rset_last_updated_date from FND_REQUEST_SETS
where REQUEST_SET_ID = rset_id AND application_id = appl_id;
SELECT max(last_update_date) INTO tmpDate FROM fnd_request_set_stages
WHERE request_set_id = rset_id AND set_application_id = appl_id;
IF( tmpDate > rset_last_updated_date) THEN
rset_last_updated_date := tmpDate;
SELECT max(last_update_date) INTO tmpDate FROM fnd_request_set_programs
WHERE request_set_id = rset_id AND set_application_id = appl_id;
IF( tmpDate > rset_last_updated_date) THEN
rset_last_updated_date := tmpDate;
if( rset_last_updated_date > req_request_date ) then
return false;
/*check whether any errored out program got deleted */
select count(r.request_set_program_id) INTO tmp_number
from fnd_run_requests r,
fnd_concurrent_requests fcr1, fnd_concurrent_requests fcr2
where r.parent_request_id = requestId
and fcr1.parent_request_id = fcr2.request_id
and fcr1.concurrent_program_id = r.concurrent_program_id
and r.request_id = fcr1.request_id
and fcr1.status_code = 'E'
and fcr2.parent_request_id = requestId
and fcr2.run_number = current_run_number
and error_stage_id = to_number(fcr2.argument3)
and r.request_set_program_id IS NOT NULL
AND r.request_set_program_id NOT IN
(
SELECT REQUEST_SET_PROGRAM_ID FROM FND_REQUEST_SET_PROGRAMS WHERE SET_APPLICATION_ID = appl_id
AND REQUEST_SET_ID = rset_id AND REQUEST_SET_STAGE_ID = error_stage_id
);
update fnd_concurrent_requests set phase_code = 'P', status_code = 'Q' where request_id = requestId;
update fnd_concurrent_requests set phase_code = 'P', status_code = 'I' where request_id = requestId;