The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null into l_dummy from dual where exists
(select null
from ff_formulas_f a
where a.formula_name = l_tmp_name
and a.formula_type_id = p_formula_type_id
and
( l_startup_mode = 'MASTER'
or
( l_startup_mode = 'SEED'
and
( a.legislation_code = p_legislation_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_legislation_code =
(select b.legislation_code
from per_business_groups_perf b
where b.business_group_id = a.business_group_id)
)
)
or
( l_startup_mode = 'NON-SEED'
and
( a.business_group_id = p_business_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_legislation_code)
)
)
));
procedure insert_formula(
p_rowid in out nocopy varchar2,
p_formula_id in out nocopy varchar2,
p_effective_start_date in date,
p_effective_end_date in date,
p_business_group_id in number,
p_legislation_code in varchar2,
p_formula_type_id in varchar2,
p_formula_name in out nocopy varchar2,
p_description in varchar2,
p_formula_text in long,
p_sticky_flag in varchar2,
p_last_update_date in out nocopy date,
p_return_status out nocopy varchar2
)
is
--
begin
--
if g_debug then
hr_utility.set_location('Entering ff_formula_webui_pkg.insert_formula',10);
ff_formulas_f_pkg.insert_row(
x_rowid => p_rowid,
x_formula_id => p_formula_id,
x_effective_start_date => p_effective_start_date,
x_effective_end_date => p_effective_end_date,
x_business_group_id => p_business_group_id,
x_legislation_code => p_legislation_code,
x_formula_type_id => p_formula_type_id,
x_formula_name => p_formula_name,
x_description => p_description,
--x_formula_text => l_long_formula_text,
x_formula_text => p_formula_text,
--x_sticky_flag => p_sticky_flag,
x_sticky_flag => 'Y',
x_last_update_date => p_last_update_date
);
hr_utility.set_location('Leaving ff_formula_webui_pkg.insert_formula',20);
end insert_formula;
procedure update_formula(
p_rowid in varchar2,
p_formula_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_business_group_id in number,
p_legislation_code in varchar2,
p_formula_type_id in varchar2,
p_formula_name in varchar2,
p_description in varchar2,
p_formula_text in long,
p_sticky_flag in varchar2,
p_last_update_date in out nocopy date,
p_return_status out nocopy varchar2
)
is
--
begin
--
if g_debug then
hr_utility.set_location('Entering ff_formula_webui_pkg.update_formula',10);
ff_formulas_f_pkg.update_row(
x_rowid => p_rowid,
x_formula_id => p_formula_id,
x_effective_start_date => p_effective_start_date,
x_effective_end_date => p_effective_end_date,
x_business_group_id => p_business_group_id,
x_legislation_code => p_legislation_code,
x_formula_type_id => p_formula_type_id,
x_formula_name => p_formula_name,
x_description => p_description,
--x_formula_text => l_long_formula_text,
x_formula_text => p_formula_text,
x_sticky_flag => p_sticky_flag,
x_last_update_date => p_last_update_date
);
hr_utility.set_location('Leaving ff_formula_webui_pkg.update_formula',20);
end update_formula;
procedure delete_formula(
p_rowid in varchar2,
p_formula_id in number,
p_dt_delete_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date,
p_effective_date in date,
p_return_status out nocopy varchar2
)
is
begin
--
if g_debug then
hr_utility.set_location('Entering ff_formula_webui_pkg.delete_formula',10);
ff_formulas_f_pkg.delete_row(
x_rowid => p_rowid,
x_formula_id => p_formula_id,
x_dt_delete_mode => p_dt_delete_mode,
x_validation_start_date => p_validation_start_date,
x_validation_end_date => p_validation_end_date,
x_effective_date => p_effective_date
);
hr_utility.set_location('Leaving ff_formula_webui_pkg.delete_formula',20);
end delete_formula;
p_last_update_date in date,
p_return_status out nocopy varchar2
)
is
begin
--
if g_debug then
hr_utility.set_location('Entering ff_formula_webui_pkg.lock_formula',10);
x_last_update_date => p_last_update_date
);
procedure do_autonomous_insert(
p_rowid in out nocopy varchar2,
p_formula_id in out nocopy number,
p_effective_start_date date,
p_effective_end_date date,
p_business_group_id number,
p_legislation_code varchar2,
p_formula_type_id number,
p_formula_name in out nocopy varchar2,
p_formula_text varchar2,
p_last_update_date in out nocopy date
)
is
--
pragma autonomous_transaction;
ff_formulas_f_pkg.insert_row (
x_rowid => p_rowid,
x_formula_id => p_formula_id,
x_effective_start_date => p_effective_start_date,
x_effective_end_date => p_effective_end_date,
x_business_group_id => p_business_group_id,
x_legislation_code => p_legislation_code,
x_formula_type_id => p_formula_type_id,
x_formula_name => p_formula_name,
x_description => null,
x_formula_text => p_formula_text,
x_sticky_flag => 'Y',
x_last_update_date => p_last_update_date
);
end do_autonomous_insert;
procedure do_autonomous_delete(
p_rowid varchar2,
p_formula_id number,
p_effective_start_date date,
p_effective_end_date date
)
is
--
pragma autonomous_transaction;
ff_formulas_f_pkg.delete_row(
x_rowid => p_rowid,
x_formula_id => p_formula_id,
x_dt_delete_mode => 'ZAP',
x_validation_start_date => fnd_date.canonical_to_date('0001/01/01'),
x_validation_end_date => p_effective_end_date,
x_effective_date => p_effective_start_date
);
end do_autonomous_delete;
l_last_update_date date;
do_autonomous_insert (
p_rowid => l_rowid,
p_formula_id => l_formula_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_business_group_id => p_business_group_id,
p_legislation_code => p_legislation_code,
p_formula_type_id => p_formula_type_id,
p_formula_name => l_formula_name,
p_formula_text => p_formula_text,
p_last_update_date => l_last_update_date
);
do_autonomous_delete(
p_rowid => l_rowid,
p_formula_id => l_formula_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date
);
select 'Y'
from ff_compiled_info_f
where formula_id = p_formula_id
and p_effective_date between effective_start_date and effective_end_date;
select f.name
from ff_functions f
where f.function_id = p_fn_id;
select fp.name, lu.meaning data_type
from ff_function_parameters fp,
hr_lookups lu
where fp.function_id = p_fn_id
and fp.data_type = lu.lookup_code
and lu.lookup_type = 'DATA_TYPE'
order by fp.sequence_number;