The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT route_id
INTO l_route_id
FROM ff_routes
WHERE route_name = upper(p_route_name);
hr_utility.trace(' selected route..');
INSERT INTO ff_routes
(route_id,
route_name,
user_defined_flag,
description,
text,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
VALUES
(ff_routes_s.nextval,
upper(p_route_name),
'N',
p_route_description,
p_route_text,
sysdate,
0,
0,
0,
sysdate);
hr_utility.trace('after insert of route');
SELECT ff_routes_s.currval
INTO l_route_id
FROM dual;
INSERT INTO ff_route_context_usages
(route_id,
context_id,
sequence_no)
select l_route_id,
FFC.context_id,
1
from ff_contexts FFC
where context_name = p_context_name1;
hr_utility.trace('after insert of context');
INSERT INTO ff_route_context_usages
(route_id,
context_id,
sequence_no)
select l_route_id,
FFC.context_id,
2
from ff_contexts FFC
where context_name = p_context_name2;
INSERT INTO ff_route_context_usages
(route_id,
context_id,
sequence_no)
select l_route_id,
FFC.context_id,
3
from ff_contexts FFC
where context_name = p_context_name3;
INSERT INTO ff_route_context_usages
(route_id,
context_id,
sequence_no)
select l_route_id,
FFC.context_id,
4
from ff_contexts FFC
where context_name = p_context_name4;
INSERT INTO ff_route_parameters
(route_parameter_id,
route_id,
sequence_no,
parameter_name,
data_type)
VALUES
(ff_route_parameters_s.nextval,
l_route_id,
1,
p_param_name1,
p_param_type1);
hr_utility.trace('after insert of param');
INSERT INTO ff_route_parameters
(route_parameter_id,
route_id,
sequence_no,
parameter_name,
data_type)
VALUES
(ff_route_parameters_s.nextval,
l_route_id,
1,
p_param_name2,
p_param_type2);
SELECT user_entity_id
INTO l_user_entity_id
FROM ff_user_entities
WHERE user_entity_name = upper(p_user_entity_name)
and legislation_code = p_legislation_code ;
hr_utility.trace('selected entity id'||to_char(l_user_entity_id));
hrdyndbi.insert_user_entity (
p_route_name => upper(p_route_name),
p_user_entity_name => p_user_entity_name,
p_entity_description => p_user_entity_description,
p_not_found_flag => 'Y',
p_creator_type => 'SEH',
-- p_creator_type => NULL,
-- p_creator_type => 'B',
p_creator_id => 0,
p_business_group_id => NULL,
p_legislation_code => p_legislation_code,
p_created_by => 0,
p_last_login => 0
);
SELECT user_entity_id
INTO l_user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_user_entity_name
and legislation_code = p_legislation_code;
hr_utility.trace('error in others of select of entity');
INSERT into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value)
SELECT route_parameter_id,
l_user_entity_id,
p_param_value1
FROM ff_route_parameters
where route_id = l_route_id
and sequence_no = 1;
INSERT into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value)
SELECT route_parameter_id,
l_user_entity_id,
p_param_value2
FROM ff_route_parameters
where route_id = l_route_id
and sequence_no = 2;
insert into ff_database_items (
user_name,
user_entity_id,
data_type,
definition_text,
null_allowed_flag,
description,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
--
values (p_name,
l_user_entity_id,
p_data_type,
p_definition_text,
p_null_allowed,
p_description,
sysdate,
0,
0,
0,
sysdate);