[Home] [Help]
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 application_id
INTO application_id_ret
FROM fnd_application_tl
WHERE application_name = application_name_in
AND rownum = 1;
SELECT application_short_name
INTO application_sn_ret
FROM fnd_application
WHERE application_short_name = application_name_in;
SELECT application_short_name
INTO application_sn_ret
FROM fnd_application_tl t,
fnd_application b
WHERE t.application_name = application_name_in
AND rownum = 1
AND t.application_id = b.application_id;
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 srs_flag
INTO srs_flag_i
FROM fnd_concurrent_programs
WHERE concurrent_program_name = concurrent_pgm_name_in
AND application_id = application_id_i;
select execution_method_code
into emc
from fnd_concurrent_programs
where application_id_i = application_id
and concurrent_program_name = program_short_name;
SELECT column_name
FROM fnd_columns c, fnd_tables t
WHERE t.table_id = c.table_id
AND t.table_name = 'FND_SRS_MASTER' -- hardwired
AND t.application_id = 0 -- hardwired
AND c.application_id = t.application_id
AND c.flexfield_usage_code = 'D'
AND NOT EXISTS (SELECT NULL
FROM fnd_descr_flex_column_usages cu
WHERE cu.application_id = application_id_i
AND cu.descriptive_flexfield_name
= descriptive_flexfield_name_in
AND cu.application_column_name = c.column_name);
UPDATE fnd_concurrent_programs
SET cd_parameter = parameter
WHERE application_id = application_id_i
AND concurrent_program_name = program_short_name;
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;
SELECT fnd_concurrent_programs_s.NEXTVAL /* get next cp id */
INTO concurrent_program_id_i
FROM sys.dual;
SELECT executable_id, execution_method_code
INTO executable_id_i, execution_method_code_i
FROM fnd_executables
WHERE application_id = executable_application_id_i
AND fnd_executables.executable_name = executable_short_name;
SELECT request_class_id
INTO concurrent_class_id_i
FROM fnd_concurrent_request_class
WHERE application_id = type_application_id_i
AND request_class_name = request_type;
SELECT printer_style_name, width, length
INTO output_print_style_i, swidth, slength
FROM fnd_printer_styles
WHERE printer_style_name = style;
SELECT b.printer_style_name, b.width, b.length
INTO output_print_style_i, swidth, slength
FROM fnd_printer_styles_tl t,
fnd_printer_styles b
WHERE t.user_printer_style_name = style
AND rownum = 1
AND b.printer_style_name = t.printer_style_name;
select 'x' into dummy
from sys.dual
where printer in
(select distinct printer_name
from fnd_printer p
where (output_print_style_i is not null
and p.printer_type in
(select i.printer_type
from fnd_printer_information i,
fnd_printer_styles s
where i.printer_style = output_print_style_i
and s.printer_style_name = i.printer_style))
or (output_print_style_i is null
and p.printer_type in
(select i.printer_type
from fnd_printer_information i,
fnd_printer_styles s
where i.printer_style = s.printer_style_name
and s.width >= nvl(cols, s.width)
and s.length >= nvl(rows, s.length))));
SELECT executable_id
INTO mls_function_exe_id_i
FROM fnd_executables
WHERE application_id = mls_function_application_id_i
AND fnd_executables.executable_name = mls_function_short_name;
select lookup_code
into refresh_portlet_code
from fnd_lookup_values
where upper(meaning) = upper(refresh_portlet)
and lookup_type = 'CONC_REFRESH_PORTLET'
and enabled_flag = 'Y'
and rownum = 1;
INSERT INTO fnd_concurrent_programs(
application_id, concurrent_program_id, concurrent_program_name,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
executable_application_id, executable_id, execution_method_code,
argument_method_code, queue_control_flag, queue_method_code,
request_set_flag, enabled_flag, print_flag,
run_alone_flag, srs_flag,
class_application_id, concurrent_class_id, execution_options,
save_output_flag, required_style, output_print_style,
printer_name, minimum_width, minimum_length,
request_priority, output_file_type, enable_trace,
restart, nls_compliant, icon_name,
mls_executable_app_id, mls_executable_id, increment_proc,
refresh_portlet)
VALUES (application_id_i, concurrent_program_id_i, upper(short_name),
last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
executable_application_id_i, executable_id_i,
execution_method_code_i,
argument_method_code_i, 'N', 'I',
'N', upper(enabled), upper(print),
upper(run_alone), srs_flag_i,
type_application_id_i, concurrent_class_id_i,
execution_options,
upper(save_output), upper(style_required), output_print_style_i,
printer, cols, rows,
priority, output_type, enable_trace,
restart, nls_compliant, null,
mls_function_application_id_i, mls_function_exe_id_i, incrementor,
refresh_portlet_code);
println('inserted into concurrent programs');
insert into FND_CONCURRENT_PROGRAMS_TL (
APPLICATION_ID, CONCURRENT_PROGRAM_ID, USER_CONCURRENT_PROGRAM_NAME,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION,
LANGUAGE, SOURCE_LANG
) select
application_id_i, concurrent_program_id_i, program,
register.creation_date, register.created_by, register.last_update_date,
register.last_updated_by, register.last_update_login,
register.description,
L.LANGUAGE_CODE,
register.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_CONCURRENT_PROGRAMS_TL T
where T.APPLICATION_ID = application_id_i
and T.CONCURRENT_PROGRAM_ID = concurrent_program_id_i
and T.LANGUAGE = L.LANGUAGE_CODE);
println('inserted into concurrent programs tl');
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;
SELECT lookup_code
INTO scope_code_i
FROM fnd_lookup_values
WHERE lookup_type = 'CP_SERIAL_TYPE'
AND upper(meaning) = upper(scope)
AND rownum = 1;
/* update incompatibilies */
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,
scope_code_i,
to_run_concurrent_program_id_i,
to_run_application_id_i,
scope_code_i,
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,
scope_code_i,
concurrent_program_id_i,
application_id_i,
scope_code_i,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inc_type);
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 lookup_code
into execution_method_code
from fnd_lookup_values
where upper(meaning) = upper(execution_method)
and lookup_type = 'CP_EXECUTION_METHOD_CODE'
and enabled_flag = 'Y'
and rownum = 1;
message('Subroutine name must be null for the selected execution method.');
message('Execution File Path not required for the selected execution method.');
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, execution_file_path
) values (
application_id, executable_id, short_name,
execution_method_code, execution_file_name, subroutine_name,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, icon_name, execution_file_path
);
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
application_id, executable_id, fnd_program.executable.creation_date,
fnd_program.executable.created_by, fnd_program.executable.last_update_date,
fnd_program.executable.last_updated_by,
fnd_program.executable.last_update_login, executable,
fnd_program.executable.description,
l.language_code, fnd_program.executable.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;
insert into fnd_request_groups
(application_id,
request_group_id,
request_group_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
request_group_code)
values
(group_application_id,
fnd_request_groups_s.nextval,
request_group,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
code);
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,
'P',
program_application_id,
concurrent_program_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 = 'P'
and unit_application_id = program_application_id
and request_unit_id = concurrent_program_id;
PROCEDURE delete_program(program_short_name IN VARCHAR2,
application IN VARCHAR2) is
program_application_id fnd_application.application_id%TYPE;
select request_id
from fnd_concurrent_requests
where concurrent_program_id = prog_id
and program_application_id = appl_id;
SELECT concurrent_program_id, request_set_flag
INTO program_id, rs_flag
FROM fnd_concurrent_programs
WHERE application_id = program_application_id
AND concurrent_program_name = program_short_name;
SELECT request_set_id
INTO set_id
FROM fnd_request_sets
WHERE application_id = program_application_id
AND concurrent_program_id = program_id;
DELETE FROM fnd_request_group_units
WHERE request_unit_type = 'S'
AND request_unit_id = set_id
AND unit_application_id = program_application_id;
DELETE FROM fnd_request_set_program_args
WHERE application_id = program_application_id
AND request_set_id = set_id;
DELETE FROM fnd_request_set_programs
WHERE set_application_id = program_application_id
AND request_set_id = set_id;
DELETE FROM fnd_request_sets_tl
WHERE application_id = program_application_id
AND REQUEST_SET_ID = set_id;
DELETE FROM fnd_request_sets
WHERE application_id = program_application_id
AND request_set_id = set_id;
DELETE FROM fnd_request_set_program_args
WHERE application_id = program_application_id
AND request_set_program_id in
(SELECT request_set_program_id
FROM fnd_request_set_programs frsp
WHERE frsp.program_application_id
= delete_program.program_application_id
AND concurrent_program_id = program_id);
DELETE FROM fnd_request_set_programs frs
WHERE frs.program_application_id = delete_program.program_application_id
AND concurrent_program_id = program_id;
DELETE fnd_request_group_units
WHERE request_unit_type = 'P'
AND request_unit_id = program_id
AND unit_application_id = program_application_id;
DELETE fnd_concurrent_program_serial
WHERE running_concurrent_program_id = program_id
AND running_application_id = program_application_id;
DELETE fnd_concurrent_program_serial
WHERE to_run_concurrent_program_id = program_id
AND to_run_application_id = program_application_id;
DELETE fnd_concurrent_programs_tl
WHERE application_id = program_application_id
AND concurrent_program_id = program_id;
DELETE fnd_concurrent_programs
WHERE concurrent_program_id = program_id
AND application_id = program_application_id;
fnd_flex_dsc_api.delete_flexfield(
appl_short_name => prog_appl_short_name,
flexfield_name =>desc_flex_name);
delete from fnd_concurrent_requests
where request_id = req.request_id;
delete from fnd_run_requests
where request_id = req.request_id;
delete from fnd_conc_release_periods_tl
where (application_id, concurrent_period_id) in
(select application_id, concurrent_period_id
from fnd_conc_release_periods
where owner_req_id = req.request_id);
delete from fnd_conc_release_periods
where owner_req_id = req.request_id;
delete from fnd_conc_release_states_tl
where (application_id, concurrent_state_id) in
(select application_id, concurrent_state_id
from fnd_conc_release_states
where owner_req_id = req.request_id);
delete from fnd_conc_release_states
where owner_req_id = req.request_id;
delete from fnd_conc_release_classes_tl
where (application_id, release_class_id) in
(select application_id, release_class_id
from fnd_conc_release_classes
where owner_req_id = req.request_id);
delete from fnd_conc_release_classes
where owner_req_id = req.request_id;
delete from fnd_conc_release_disjs_tl
where (application_id, disjunction_id) in
(select application_id, disjunction_id
from fnd_conc_release_disjs
where owner_req_id = req.request_id);
delete from fnd_conc_release_disjs
where owner_req_id = req.request_id;
delete from fnd_conc_rel_disj_members
where owner_req_id = req.request_id;
delete from fnd_conc_rel_conj_members
where owner_req_id = req.request_id;
delete from fnd_conc_pp_actions
where concurrent_request_id = req.request_id;
delete from fnd_run_req_pp_actions
where parent_request_id = req.request_id;
delete from fnd_file_temp
where request_id = req.request_id;
delete from fnd_run_requests
where parent_request_id = req.request_id;
delete from fnd_conc_request_arguments
where request_id = req.request_id;
println('Program '|| program_short_name || 'deleted.');
END delete_program;
PROCEDURE delete_parameter(program_short_name IN VARCHAR2,
application IN VARCHAR2,
parameter IN VARCHAR2) is
program_appl_id
fnd_application.application_id%TYPE;
delete from fnd_request_set_program_args a
where (a.application_id, a.request_set_id, a.request_set_program_id)
in (select sp.set_application_id,
sp.request_set_id,
sp.request_set_program_id
from fnd_request_set_programs sp
where sp.program_application_id = program_appl_id
and sp.concurrent_program_id = 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 = desc_flex_name
and u.descriptive_flex_context_code = 'Global Data Elements'
and u.end_user_column_name = parameter);
fnd_flex_dsc_api.delete_segment(appl_short_name=> prog_appl_short_name,
flexfield_name=>desc_flex_name,
context=>'Global Data Elements',
segment=>parameter);
END delete_parameter;
PROCEDURE delete_executable(executable_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 = executable_short_name;
select 'x' into dummy
from sys.dual
where not exists
(select 1
from fnd_concurrent_programs
where executable_application_id = exec_application_id
and executable_id = exec_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;
END delete_executable;
PROCEDURE delete_group(request_group IN VARCHAR2,
application IN VARCHAR2) is
group_application_id
fnd_application.application_id%TYPE;
delete from fnd_request_group_units
where (request_group_id, application_id) in
(select request_group_id, application_id
from fnd_request_groups
where request_group_name = request_group
and application_id = group_application_id);
delete from fnd_request_groups
where request_group_name = request_group
and application_id = group_application_id;
END delete_group;
PROCEDURE delete_incompatibility(program_short_name IN VARCHAR2,
application IN VARCHAR2,
inc_prog_short_name IN VARCHAR2,
inc_prog_application IN VARCHAR2)
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;
last_update_login_p fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date_p fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by_p fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
SELECT concurrent_program_id
INTO concurrent_program_id_p
FROM fnd_concurrent_programs
WHERE application_id = application_id_p
AND concurrent_program_name = upper(short_name);
/* Now go and update the concurrent program table */
UPDATE fnd_concurrent_programs
SET enabled_flag = enabled,
last_update_date = last_update_date_p,
last_updated_by = last_updated_by_p,
last_update_login = last_update_login_p
WHERE application_id = application_id_p
AND concurrent_program_id = concurrent_program_id_p;
select 'y'
into dummy
from fnd_descr_flex_column_usages
where application_id = program_appl_id
and descriptive_flexfield_name = desc_flex_name
and descriptive_flex_context_code = 'Global Data Elements'
and end_user_column_name = parameter;
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_executables
where application_id = exec_application_id
and executable_name = executable_short_name;
select 'y'
into dummy
from fnd_request_groups
where request_group_name = request_group
and application_id = group_application_id;
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 = 'P'
and unit_application_id = program_application_id
and request_unit_id = concurrent_program_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,
'A',
application_id,
application_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 = 'A'
and request_unit_id = application_id;
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 = 'A'
and request_unit_id = application_id;