[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update_flag = 'N'
in Create_format_item function
30-NOV-1999 110.3 jgoswami Update = 'N' for
ROE_TAX_UNIT_CITY
17-FEB-2000 115.0 P.Ganguly Changed the date format for
11i.
10-APR-2000 115.1 P.Ganguly Changed the route create
function, it updates if route
already exists (route text).
14-MAY-2000 115.2 P.Ganguly Changed the report_format_item
procedure.
29-JUN-2000 115.3 P.Ganguly Corrected the procedure to
create_user_entities, if the
user_entity already exists and
creator_type is 'SEH' then
update it to 'X', else create a
new one with 'X'.
15-JAN-2004 115.4 P.Ganguly 3353868 Fixed the statement which was
flagged in the 11.5.9 Drop 18
perf Repository.
29-Mar-2006 115.5 ssmukher 5042797 Fixed the performance issue by
removing the Full table scan
from the cursor query cur_database_item_exists.
Removed the use of UPPER function from the cursor query.
====================================================================*/
function create_route(
p_route_name varchar2,
p_description varchar2,
p_text varchar2) return number is
begin
declare
cursor cur_route_exists is
select route_id from ff_routes
where route_name = p_route_name;
select ff_routes_s.nextval
from dual;
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
(l_route_id,
p_route_name,
'N',
p_description,
p_text,
sysdate,
0,
0,
0,
sysdate);
update ff_routes
set text = p_text
where route_id = l_route_id;
select user_entity_id ,
creator_type
from ff_user_entities
where user_entity_name = p_user_entity_name
and legislation_code = 'CA';
select ff_user_entities_s.nextval
from dual;
update ff_user_entities
set creator_type = 'X'
where user_entity_id = l_user_entity_id
and creator_type = 'SEH';
insert into ff_user_entities
(
user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
)
values
(
l_user_entity_id,
null,
'CA',
p_route_id,
'N',
p_user_entity_name ,
0,
'X',
p_entity_description,
sysdate,
0,
0,
0,
sysdate
);
select route_parameter_id from ff_route_parameters
where route_id= p_route_id
and parameter_name = p_parameter_name;
select ff_route_parameters_s.nextval
from dual;
insert into ff_route_parameters
(route_parameter_id,
route_id,
data_type,
parameter_name,
sequence_no )
values
(l_route_parameter_id,
p_route_id,
p_data_type,
p_parameter_name,
p_sequence_no );
select 'x' from ff_route_parameter_values
where route_parameter_id = p_route_parameter_id
and user_entity_id = p_user_entity_id;
insert into ff_route_parameter_values
(
route_parameter_id,
user_entity_id,
value,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
)
values
(
p_route_parameter_id,
p_user_entity_id,
p_parameter_value,
sysdate,
0,
0,
0,
sysdate
);
select context_id from ff_contexts
where ltrim(rtrim(context_name)) = ltrim(rtrim(p_context_name));
select 'x'
from ff_route_context_usages
where route_id = p_route_id
and context_id = l_context_id;
insert into ff_route_context_usages
(
route_id,
context_id,
sequence_no
)
values
(
p_route_id,
l_context_id,
p_sequence_no
);
select 'x'
from ff_database_items fdi
where fdi.user_name = p_user_name;
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_user_name,
p_user_entity_id,
p_data_type,
p_definition_text,
p_null_allowed_flag,
p_description,
sysdate,
0,
0,
0,
sysdate);
select fue.user_entity_id
from ff_user_entities fue
where upper(fue.user_entity_name) = upper(p_user_name);
select 'x'
from pay_report_format_items_f
where report_type = 'ROE' and
report_qualifier = 'ROEQ' and
report_category = 'ROEC' and
user_entity_id = l_user_entity_id and
effective_start_date = start_of_time and
effective_end_date = end_of_time;
update pay_report_format_items_f
set updatable_flag = l_updatable_flag,
archive_type = l_archive_type,
display_sequence = p_display_sequence
where
report_type = 'ROE' and
report_qualifier = 'ROEQ' and
report_category = 'ROEC' and
user_entity_id = l_user_entity_id and
effective_start_date = start_of_time and
effective_end_date = end_of_time;
insert into pay_report_format_items_f
(
report_type,
report_qualifier,
report_category,
user_entity_id,
effective_start_date,
effective_end_date,
archive_type,
updatable_flag,
display_sequence
)
values
(
'ROE',
'ROEQ',
'ROEC',
l_user_entity_id,
start_of_time,
end_of_time,
l_archive_type,
l_updatable_flag,
p_display_sequence
);