[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION last_updated_by_f(l_updated_by IN NUMBER DEFAULT NULL)
RETURN NUMBER IS
BEGIN
IF(l_updated_by IS NOT NULL) THEN
RETURN l_updated_by;
FUNCTION last_update_date_f(l_update_date IN DATE DEFAULT NULL)
RETURN DATE IS
BEGIN
IF(l_update_date IS NOT NULL) THEN
RETURN l_update_date;
FUNCTION created_by_f(c_updated_by IN NUMBER DEFAULT NULL)
RETURN NUMBER IS
BEGIN
RETURN last_updated_by_f(c_updated_by);
FUNCTION creation_date_f(c_update_date IN DATE DEFAULT NULL)
RETURN DATE IS
BEGIN
IF(c_update_date IS NOT NULL) THEN
RETURN c_update_date;
FUNCTION last_update_login_f RETURN NUMBER IS
BEGIN
return 0;
SELECT application_id
INTO application_id_ret
FROM fnd_application
WHERE upper(application_short_name) = upper(application_name_in);
SELECT application_id
INTO application_id_ret
FROM fnd_application_tl
WHERE upper(application_name) = upper(application_name_in)
AND rownum = 1;
SELECT concurrent_program_id
INTO program_id
FROM fnd_concurrent_programs
WHERE application_id = program_application_id
AND upper(concurrent_program_name) = upper(program_short_name);
select concurrent_processor_id
into lib_id
from fnd_concurrent_processors
where application_id = lib_appl_id
and lib_name = concurrent_processor_name;
select concurrent_queue_id
into mgr_id
from fnd_concurrent_queues
where application_id = mgr_appl_id
and upper(mgr_name) = upper(concurrent_queue_name);
select SERVICE_ID
into svc_id
from FND_CP_SERVICES
where upper(svc_handle) = upper(SERVICE_HANDLE);
select node_name
into nname
from FND_NODES
where upper(nodename) = upper(NODE_NAME);
select complex_rule_id
into obj_id
from fnd_concurrent_complex_rules
where application_id = obj_appl_id
and complex_rule_name = obj_name;
select oracle_id
into obj_id
from fnd_oracle_userid
where oracle_username = obj_name;
select request_class_id
into obj_id
from fnd_concurrent_request_class
where application_id = obj_appl_id
and request_class_name = obj_name;
select user_id
into obj_id
from fnd_user
where user_name = obj_name;
last_update_date IN DATE DEFAULT NULL,
last_updated_by IN NUMBER DEFAULT NULL
) is
mgr_appl_id fnd_application.application_id%TYPE;
last_update_login fnd_concurrent_queues.last_update_login%TYPE
:= last_update_login_f;
l_update_date fnd_concurrent_queues.last_update_date%TYPE
:= last_update_date_f(last_update_date);
l_updated_by fnd_concurrent_queues.last_updated_by%TYPE
:= last_updated_by_f(last_updated_by);
:= creation_date_f(last_update_date);
:= created_by_f(last_updated_by);
select fnd_concurrent_queues_s.nextval
into mgr_id
from sys.dual;
select lookup_code
into type_code
from fnd_lookup_values
where lookup_type = 'CP_MANAGER_TYPE'
and upper(meaning) = upper(type)
and rownum = 1;
select fdg.data_group_id
into dg_id
from fnd_data_groups fdg
where fdg.data_group_id = register.data_group_id;
select fdg.data_group_id
into dg_id
from fnd_data_groups fdg
where data_group = data_group_name;
delete from fnd_concurrent_queues_tl
where application_id = mgr_appl_id
and language in (select l.language_code
from fnd_languages l
where l.installed_flag in ('I', 'B'))
and (concurrent_queue_name = short_name
or user_concurrent_queue_name = manager);
/* Do the insert */
insert into fnd_concurrent_queues
(application_id, concurrent_queue_id, concurrent_queue_name,
processor_application_id, concurrent_processor_id,
running_processes, max_processes, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
cache_size, control_code, manager_type,
node_name, node_name2, os_queue, os_queue2, data_group_id,
enabled_flag)
values
(mgr_appl_id, mgr_id, short_name,
lib_appl_id, lib_id,
0, 0, creation_date, created_by,
l_update_date, l_updated_by, last_update_login,
cache_size, 'E', type_code,
primary_node, secondary_node, primary_queue, secondary_queue,
dg_id, 'Y');
insert into FND_CONCURRENT_QUEUES_TL (
user_concurrent_queue_name, application_id, concurrent_queue_id,
concurrent_queue_name, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
description, language, source_lang
) select
manager, mgr_appl_id, mgr_id, short_name,
register.creation_date, register.created_by, l_update_date,
l_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_QUEUES_TL T
where T.APPLICATION_ID = mgr_appl_id
and T.CONCURRENT_QUEUE_ID = mgr_id
and T.LANGUAGE = L.LANGUAGE_CODE);
last_updated_by IN NUMBER DEFAULT NULL) is
svc_id number;
LAST_UPDATE_DATE_v date;
LAST_UPDATED_BY_v number;
LAST_UPDATE_LOGIN_v number;
LAST_UPDATE_DATE_v := LAST_UPDATE_DATE_f;
LAST_UPDATED_BY_v := last_updated_by_f(last_updated_by);
LAST_UPDATE_LOGIN_v := LAST_UPDATE_LOGIN_f;
select fnd_cp_services_s.nextval
into svc_id
from sys.dual;
select (NVL(max(OAM_DISPLAY_ORDER),0) + 10)
into oam_d_o
from FND_CP_SERVICES;
select APPLICATION_ID
into cart_appl_id
from FND_APPLICATION
where APPLICATION_SHORT_NAME = CARTRIDGE_APPLICATION;
/* Do the insert */
insert into fnd_cp_services
(SERVICE_ID, SERVICE_HANDLE, ALLOW_MULTIPLE_PROC_INSTANCE,
ALLOW_MULTIPLE_PROC_NODE, MIGRATE_ON_FAILURE, SERVER_TYPE,
ALLOW_SUSPEND, ALLOW_VERIFY, ALLOW_PARAMETER, ALLOW_START,
ALLOW_RESTART, PARAMETER_CHANGE_ACTION, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY,
ENV_FILE_NAME, CARTRIDGE_HANDLE, DEVELOPER_PARAMETERS,
ALLOW_RCG, ALLOW_CREATE, ALLOW_EDIT, SERVICE_CLASS,
SERVICE_INSTANCE_CLASS, OAM_DISPLAY_ORDER, DEBUG_CHANGE_ACTION,
ENABLED, CARTRIDGE_APPLICATION_ID,DEBUG_TYPE,
ALLOW_MULTIPLE_PROC_SI, DEFAULT_DEBUG_LEVEL)
Select svc_id, SERVICE_HANDLE, ALLOW_MULTIPLE_PROC_INSTANCE,
ALLOW_MULTIPLE_PROC_NODE, MIGRATE_ON_FAILURE, SERVER_TYPE,
ALLOW_SUSPEND, ALLOW_VERIFY, ALLOW_PARAMETER, ALLOW_START,
ALLOW_RESTART, PARAMETER_CHANGE_ACTION, LAST_UPDATE_DATE_v,
LAST_UPDATED_BY_v, LAST_UPDATE_LOGIN_v, CREATION_DATE_v,
CREATED_BY_v,
ENV_FILE_NAME, CARTRIDGE_HANDLE, DEVELOPER_PARAMETERS,
ALLOW_RCG, ALLOW_CREATE, ALLOW_EDIT, SERVICE_CLASS,
SERVICE_INSTANCE_CLASS, oam_d_o, DEBUG_CHANGE_ACTION,
ENABLED, cart_appl_id, DEBUG_TYPE, ALLOW_MULTIPLE_PROC_SI,
DEFAULT_DEBUG_LEVEL
from sys.dual;
insert into fnd_cp_services_tl (
SERVICE_ID, LANGUAGE, SOURCE_LANG, SERVICE_NAME, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, DESCRIPTION,
SERVICE_PLURAL_NAME)
select
svc_id, l.language_code, register_svc.language_code, SERVICE_NAME,
LAST_UPDATE_DATE_v, LAST_UPDATED_BY_v,LAST_UPDATE_LOGIN_v,
CREATION_DATE_v, CREATED_BY_v, DESCRIPTION,SERVICE_PLURAL_NAME
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from fnd_cp_services_tl T
where T.SERVICE_ID = svc_id
and T.LANGUAGE = L.LANGUAGE_CODE);
last_update_date IN DATE DEFAULT NULL,
last_updated_by IN VARCHAR2 DEFAULT NULL) is
svc_id number;
last_update_date, last_updated_by);
update fnd_concurrent_queues
set manager_type = svc_id
where APPLICATION_ID = mgr_appl_id
and CONCURRENT_QUEUE_ID = si_id;
last_update_date IN DATE DEFAULT NULL,
last_updated_by IN VARCHAR2 DEFAULT NULL) is
mgr_appl_id fnd_application.application_id%TYPE;
last_update_login fnd_concurrent_queue_size.last_update_login%TYPE
:= last_update_login_f;
l_update_date fnd_concurrent_queue_size.last_update_date%TYPE
:= last_update_date_f(last_update_date);
l_updated_by fnd_concurrent_queue_size.last_updated_by%TYPE
:= last_updated_by_f(last_updated_by);
:= creation_date_f(last_update_date);
:= created_by_f(last_updated_by);
if (last_update_date IS NOT NULL) then
l_update_date := last_update_date;
creation_date := last_update_date;
if (last_updated_by IS NOT NULL) then
l_updated_by := last_updated_by;
created_by := last_updated_by;
select manager_type
into mgr_type
from fnd_concurrent_queues
where application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
select concurrent_time_period_id
into ws_id
from fnd_concurrent_time_periods
where concurrent_time_period_id = work_shift_id
and application_id = 0;
select concurrent_time_period_id
into ws_id
from fnd_concurrent_time_periods
where concurrent_time_period_name = work_shift
and application_id = 0;
/* do the insert */
insert into fnd_concurrent_queue_size
(queue_application_id, concurrent_queue_id,
period_application_id, concurrent_time_period_id,
last_update_date, last_updated_by, creation_date,
last_update_login, created_by, min_processes, sleep_seconds,
SERVICE_PARAMETERS)
values
(mgr_appl_id, mgr_id, 0, ws_id,
l_update_date, l_updated_by, creation_date,
last_update_login, created_by, processes, sleep_seconds,
svc_params);
last_update_login fnd_concurrent_queues.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_concurrent_queues.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_concurrent_queues.last_updated_by%TYPE
:= last_updated_by_f;
select lookup_code
into type_code
from fnd_lookup_values
where lookup_type = 'CP_PROGRAM_LIBRARY_TYPE'
and upper(meaning) = upper(type)
and rownum = 1;
select fnd_concurrent_processors_s.nextval
into lib_id
from sys.dual;
insert into fnd_concurrent_processors
(application_id, concurrent_processor_id,
concurrent_processor_name, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, description, library_type)
values
(lib_appl_id, lib_id, library, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, description, type_code);
last_update_login fnd_concurrent_queues.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_concurrent_queues.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_concurrent_queues.last_updated_by%TYPE
:= last_updated_by_f;
insert into fnd_conc_processor_programs
(processor_application_id, concurrent_processor_id,
program_application_id, concurrent_program_id,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login)
values
(lib_appl_id, lib_id, prg_appl_id, prg_id,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login);
last_update_date in DATE DEFAULT NULL,
last_updated_by in NUMBER DEFAULT NULL) is
mgr_appl_id fnd_application.application_id%TYPE;
last_update_login fnd_concurrent_queue_content.last_update_login%TYPE
:= last_update_login_f;
l_update_date fnd_concurrent_queue_content.last_update_date%TYPE
:= last_update_date_f(last_update_date);
l_updated_by fnd_concurrent_queue_content.last_updated_by%TYPE
:= last_updated_by_f(last_updated_by);
:= creation_date_f(last_update_date);
:= created_by_f(last_updated_by);
if (last_update_date IS NOT NULL) then
l_update_date := last_update_date;
creation_date := last_update_date;
if (last_updated_by IS NOT NULL) then
l_updated_by := last_updated_by;
created_by := last_updated_by;
select manager_type
into mgr_type
from fnd_concurrent_queues
where application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
select lookup_code
into action_code
from fnd_lookup_values
where lookup_type = 'INCLUDE_EXCLUDE'
and upper(meaning) = upper(action)
and rownum = 1;
select lookup_code
into type_code
from fnd_lookup_values
where lookup_type = 'CP_SPECIAL_RULES'
and upper(meaning) = upper(object_type)
and lookup_code in ('C','O','P','R','U')
and rownum = 1;
insert into fnd_concurrent_queue_content
(queue_application_id, concurrent_queue_id, type_code,
type_application_id, type_id, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, include_flag)
values (mgr_appl_id, mgr_id, type_code,
type_appl_id, type_id, l_update_date,
l_updated_by, creation_date, created_by,
last_update_login, action_code);
select 'Y'
into dummy
from fnd_concurrent_queues
where application_id = mgr_appl_id
and concurrent_queue_name = manager_short_name;
select 'Y'
into dummy
from fnd_cp_services
where svc_handle = SERVICE_HANDLE;
select 'Y'
into dummy
from fnd_concurrent_queue_size qs,
fnd_concurrent_time_periods tp
where tp.concurrent_time_period_name = work_shift
and tp.application_id = 0
and tp.concurrent_time_period_id = qs.concurrent_time_period_id
and qs.concurrent_queue_id = mgr_id
and qs.queue_application_id = mgr_appl_id
and rownum = 1;
select 'Y'
into dummy
from fnd_concurrent_queue_size
where work_shift_id = concurrent_time_period_id
and concurrent_queue_id = mgr_id
and queue_application_id = mgr_appl_id
and rownum = 1;
select 'Y'
into dummy
from fnd_concurrent_processors
where application_id = lib_appl_id
and concurrent_processor_name = library;
select 'Y'
into dummy
from fnd_conc_processor_programs pp,
fnd_concurrent_programs cp
where pp.processor_application_id = lib_appl_id
and pp.concurrent_processor_id = lib_id
and pp.concurrent_program_id = cp.concurrent_program_id
and cp.application_id = pp.program_application_id
and cp.application_id = prg_appl_id
and cp.concurrent_program_name = program;
select lookup_code
into obj_code
from fnd_lookup_values
where lookup_type = 'CP_SPECIAL_RULES'
and upper(meaning) = upper(object_type)
and lookup_code in ('C','O','P','R','U')
and rownum = 1;
select complex_rule_id
into obj_id
from fnd_concurrent_complex_rules
where application_id = obj_appl_id
and complex_rule_name = object_name;
select oracle_id
into obj_id
from fnd_oracle_userid
where oracle_username = object_name;
select request_class_id
into obj_id
from fnd_concurrent_request_class
where application_id = obj_appl_id
and request_class_name = object_name;
select user_id
into obj_id
from fnd_user
where user_name = object_name;
select 'Y'
into dummy
from fnd_concurrent_queue_content
where concurrent_queue_id = mgr_id
and queue_application_id = mgr_appl_id
and type_code = obj_code
and ((obj_id is null and type_id is null)
or type_id = obj_id)
and ((obj_appl_id is null and type_application_id is null)
or obj_appl_id = type_application_id);
PROCEDURE delete_manager (manager_short_name IN VARCHAR2,
application IN VARCHAR2) is
mgr_appl_id fnd_application.application_id%TYPE;
when others then -- No manager to delete.
message_init;
message('Cannot delete the Internal or Standard managers.');
/* Bug 2048187: Removed deletes for request and process history */
/* from tables fnd_run_requests, fnd_concurrent_requests, */
/* and fnd_concurrent_processes. */
/* ------------------------------------------------------------ */
/* Specialization Rules */
delete from fnd_concurrent_queue_content
where concurrent_queue_id = mgr_id
and queue_application_id = mgr_appl_id;
delete from fnd_concurrent_queue_size
where queue_application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
delete from fnd_concurrent_queues_tl
where concurrent_queue_id = mgr_id
and application_id = mgr_appl_id;
delete from fnd_concurrent_queues
where concurrent_queue_id = mgr_id
and application_id = mgr_appl_id;
end delete_manager;
PROCEDURE delete_library(library IN VARCHAR2,
application IN VARCHAR2) is
lib_appl_id fnd_application.application_id%TYPE;
when others then -- No library to delete
message_init;
select count(*)
into i
from fnd_concurrent_queues
where concurrent_processor_id = lib_id
and processor_application_id = lib_appl_id;
/* Delete programs */
delete from fnd_conc_processor_programs
where concurrent_processor_id = lib_id
and processor_application_id = lib_appl_id;
/* Delete library */
delete from fnd_concurrent_processors
where concurrent_processor_id = lib_id
and application_id = lib_appl_id;
end delete_library;
select 1 into dummy
from sys.dual
where not exists
(select 1
from fnd_concurrent_queues
where concurrent_queue_id = manager_id);
update fnd_concurrent_processes
set concurrent_queue_id = manager_id
where queue_application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
update fnd_concurrent_queue_size
set concurrent_queue_id = manager_id
where queue_application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
update fnd_concurrent_queue_content
set concurrent_queue_id = manager_id
where queue_application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
update fnd_concurrent_queues_tl
set concurrent_queue_id = manager_id
where application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
update fnd_concurrent_queues
set concurrent_queue_id = manager_id
where application_id = mgr_appl_id
and concurrent_queue_id = mgr_id;
PROCEDURE update_node(short_name IN VARCHAR2,
application IN VARCHAR2,
primary_node IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
secondary_node IN VARCHAR2 DEFAULT fnd_api.g_miss_char) IS
mgr_appl_id fnd_application.application_id%TYPE;
/* Update */
if (snode = fnd_api.g_miss_char) then /* No secondary */
update fnd_concurrent_queues
set node_name = pnode
where concurrent_queue_id = mgr_id
and application_id = mgr_appl_id;
update fnd_concurrent_queues
set node_name2 = snode
where concurrent_queue_id = mgr_id
and application_id = mgr_appl_id;
update fnd_concurrent_queues
set node_name = pnode,
node_name2 = snode
where concurrent_queue_id = mgr_id
and application_id = mgr_appl_id;
END update_node;