The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from hr_tips t
where upper(t.screen) = upper(p_screen)
and upper(t.field) = upper(p_field)
and upper(t.language_code) = upper(p_language)
and nvl(t.business_group_id, 0) = nvl(p_business_group_id, 0);
if p_mode = 'UPDATE' then
-- if the tip already exists in the database then update it
if csr_exists_tip%found then
-- update it
update hr_tips
set text = p_text
where upper(screen) = upper(p_screen)
and upper(field) = upper(p_field)
and upper(language_code) = upper(p_language)
and nvl(business_group_id, 0) = nvl(p_business_group_id, 0);
insert into hr_tips
(tip_id
,filename
,screen
,field
,language_code
,business_group_id
,text
,enabled_flag
)
values
(hr_tips_s.nextval
,upper(p_filename)
,upper(p_screen)
,upper(p_field)
,upper(p_language)
,p_business_group_id
,p_text
,'Y'
);
elsif (p_mode = 'NOUPDATE' or p_mode = 'DELETE') then
null;
insert into hr_tips
(tip_id
,filename
,screen
,field
,language_code
,business_group_id
,text
,enabled_flag
)
values
(hr_tips_s.nextval
,upper(p_filename)
,upper(p_screen)
,upper(p_field)
,upper(p_language)
,p_business_group_id
,p_text
,'Y'
);
delete from hr_tips
where upper(filename) = upper(p_filename)
and upper(language_code) = upper(p_language)
and nvl(business_group_id, 0) = nvl(p_business_group_id, 0);