The following lines contain the word 'select', 'insert', 'update' or 'delete':
select customization_level
into x_customization_level
from wf_ntf_rules
where owner_tag = x_owner_tag
and rule_name = x_rule_name;
procedure insert_row(x_owner_tag in varchar2,
x_rule_name in varchar2,
x_user_rule_name in varchar2,
x_description in varchar2,
x_customization_level in varchar2,
x_phase in number,
x_status in varchar2)
is
begin
insert into wf_ntf_rules (
owner_tag,
creation_date,
rule_name,
customization_level,
phase,
status
) values (
x_owner_tag,
sysdate,
x_rule_name,
x_customization_level,
x_phase,
x_status);
insert into wf_ntf_rules_tl (
rule_name,
user_rule_name,
description,
language,
source_lang,
creation_date)
select x_rule_name,
x_user_rule_name,
x_description,
l.code,
userenv('LANG'),
sysdate
from wf_languages l
where l.installed_flag = 'Y'
and not exists
(select null
from wf_ntf_rules_tl t
where t.rule_name = x_rule_name
and t.language = l.code);
wf_core.context('Wf_ntf_rules_pkg', 'Insert_Row', x_owner_tag, x_rule_name);
end insert_row;
procedure delete_row(x_owner_tag in varchar2,
x_rule_name in varchar2)
is
begin
delete from wf_ntf_rules_tl
where rule_name = x_rule_name;
delete from wf_ntf_rules
where owner_tag = x_owner_tag
and rule_name = x_rule_name;
wf_core.context('Wf_ntf_rules_pkg', 'Delete_Row', x_owner_tag, x_rule_name);
end delete_row;
procedure update_row(x_owner_tag in varchar2,
x_rule_name in varchar2,
x_user_rule_name in varchar2,
x_description in varchar2,
x_customization_level in varchar2,
x_phase in number,
x_status in varchar2)
is
l_custom_level varchar2(10);
l_update_allowed varchar2(1);
l_update_allowed := is_update_allowed(x_customization_level,l_custom_level);
update wf_ntf_rules
set status = x_status,
customization_level = x_customization_level,
phase = x_phase
where owner_tag = x_owner_tag
and rule_name = x_rule_name;
update wf_ntf_rules_tl
set user_rule_name = x_user_rule_name,
description = x_description,
source_lang = userenv('LANG')
where rule_name = x_rule_name
and userenv('LANG') in (language, source_lang);
if l_update_allowed = 'N' then
wf_core.context('WF_NTF_RULES_PKG','UPDATE_ROW',
x_rule_name,
l_custom_level,
X_CUSTOMIZATION_LEVEL);
update wf_ntf_rules
set status = x_status
where owner_tag = x_owner_tag
and rule_name = x_rule_name;
update wf_ntf_rules
set status = x_status,
customization_level = x_customization_level,
phase = x_phase
where owner_tag = x_owner_tag
and rule_name = x_rule_name;
update wf_ntf_rules_tl
set user_rule_name = x_user_rule_name,
description = x_description,
source_lang = userenv('LANG')
where rule_name = x_rule_name
and userenv('LANG') in (language, source_lang);
wf_core.context('Wf_ntf_rules_pkg', 'Update_Row', x_owner_tag, x_rule_name);
end update_row;
wf_ntf_rules_pkg.update_row(x_owner_tag,
x_rule_name,
x_user_rule_name,
x_description,
x_customization_level,
x_phase,
x_status);
wf_ntf_rules_pkg.insert_row(x_owner_tag,
x_rule_name,
x_user_rule_name,
x_description,
x_customization_level,
x_phase,
x_status);
function is_update_allowed(X_CUSTOM_LEVEL_NEW in varchar2,
X_CUSTOM_LEVEL_OLD in varchar2) return varchar2
is
begin
-- Cannot overwrite data with a higher customization level
if X_CUSTOM_LEVEL_NEW = 'U' then
if X_CUSTOM_LEVEL_OLD in ('C','L') then
-- Error will be logged
return ('N');
end is_update_allowed;
insert into wf_ntf_rules_tl (
rule_name,
user_rule_name,
description,
language,
source_lang,
creation_date
) select
b.rule_name,
b.user_rule_name,
b.description,
l.code,
b.source_lang,
sysdate
from WF_ntf_rules_tl b, wf_languages l
where l.installed_flag = 'Y'
and b.language = userenv('LANG')
and (b.rule_name,l.code) not in
(select /*+ hash_aj index_ffs(T,WF_NTF_RULES_TL_PK) */
t.rule_name,t.language
from wf_ntf_rules_tl t) ;
update wf_ntf_rules_tl
set user_rule_name=x_user_rule_name,
description=x_description,
source_lang=userenv('LANG')
where rule_name=x_rule_name
and userenv('LANG') in (language, source_lang);