[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_last_update_date in date
, p_seeded_flag in varchar2
, p_key_column in varchar2
, p_data_column in varchar2
, p_name_number_column in varchar2
, p_from_clause in varchar2
, p_where_clause in varchar2
, p_order_by_clause in varchar2
, p_object_version_number in number
, p_attribute1 in varchar2
, p_attribute2 in varchar2
, p_attribute3 in varchar2
, p_attribute4 in varchar2
, p_attribute5 in varchar2
, p_attribute6 in varchar2
, p_attribute7 in varchar2
, p_attribute8 in varchar2
, p_attribute9 in varchar2
, p_attribute10 in varchar2
, p_attribute11 in varchar2
, p_attribute12 in varchar2
, p_attribute13 in varchar2
, p_attribute14 in varchar2
, p_attribute15 in varchar2
, p_attribute_category in varchar2
, p_name in varchar2
, p_description in varchar2 )
IS
cursor skillType_cur (b_skill_type_id number) is
select skill_type_id, last_update_date
from csf_skill_types_b
where skill_type_id = b_skill_type_id;
l_last_update_date date;
fetch skillType_cur into l_skill_type_id, l_last_update_date;
if p_last_update_date >= l_last_update_date then
update csf_skill_types_b
set rating_scale_id = p_rating_scale_id
, start_date_active = p_start_date_active
, end_date_active = p_end_date_active
, seeded_flag = p_seeded_flag
, object_version_number = object_version_number + 1
, key_column = p_key_column
, data_column = p_data_column
, name_number_column = p_name_number_column
, from_clause = p_from_clause
, where_clause = p_where_clause
, order_by_clause = p_order_by_clause
, attribute1 = p_attribute1
, attribute2 = p_attribute2
, attribute3 = p_attribute3
, attribute4 = p_attribute4
, attribute5 = p_attribute5
, attribute6 = p_attribute6
, attribute7 = p_attribute7
, attribute8 = p_attribute8
, attribute9 = p_attribute9
, attribute10 = p_attribute10
, attribute11 = p_attribute11
, attribute12 = p_attribute12
, attribute13 = p_attribute13
, attribute14 = p_attribute14
, attribute15 = p_attribute15
, attribute_category = p_attribute_category
, last_update_date = p_last_update_date
, last_updated_by = g_user_id
, last_update_login = g_login_id
where skill_type_id = l_skill_type_id;
update csf_skill_types_tl
set name = p_name
, description = p_description
, last_update_date = p_last_update_date
, last_updated_by = g_user_id
, last_update_login = g_login_id
, source_lang = userenv('LANG')
where skill_type_id = l_skill_type_id
and userenv('LANG') in (language, source_lang);
select rowid
from csf_skill_types_b
where skill_type_id = x_skill_type_id;
select 1
from csf_skill_types_tl a, csf_skill_types_b b
where a.skill_type_id = b.skill_type_id
and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
and language = userenv ('lang');
select csf_skill_types_b_s1.nextval
into x_skill_type_id
from dual;
insert into csf_skill_types_b
( skill_type_id
, rating_scale_id
, start_date_active
, end_date_active
, seeded_flag
, key_column
, data_column
, name_number_column
, from_clause
, where_clause
, order_by_clause
, object_version_number
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
values
( x_skill_type_id
, x_rating_scale_id
, x_start_date_active
, x_end_date_active
, nvl(x_seeded_flag, 'N')
, l_key_column
, l_data_column
, l_name_number_column
, l_from_clause
, l_where_clause
, l_order_by_clause
, x_object_version_number
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, sysdate
, fnd_global.user_id
, sysdate
, g_user_id
, g_login_id );
insert into csf_skill_types_tl
( skill_type_id
, name
, description
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, language
, source_lang )
select x_skill_type_id
, x_name
, x_description
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, l.language_code
, userenv('LANG')
from fnd_languages l
where l.installed_flag in ('I', 'B')
and not exists
( select null
from csf_skill_types_tl t
where t.skill_type_id = x_skill_type_id
and t.language = l.language_code );
select object_version_number
from csf_skill_types_b
where skill_type_id = x_skill_type_id
for update of skill_type_id nowait;
select name
, description
, decode(language, userenv('LANG'), 'Y', 'N') baselang
from csf_skill_types_tl
where skill_type_id = x_skill_type_id
and userenv('LANG') in (language, source_lang)
for update of skill_type_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_skill_type
( x_skill_type_id in number
, x_object_version_number in out nocopy number
, x_rating_scale_id in number
, x_start_date_active in date
, x_end_date_active in date
, x_seeded_flag in varchar2 default null
, x_attribute1 in varchar2 default null
, x_attribute2 in varchar2 default null
, x_attribute3 in varchar2 default null
, x_attribute4 in varchar2 default null
, x_attribute5 in varchar2 default null
, x_attribute6 in varchar2 default null
, x_attribute7 in varchar2 default null
, x_attribute8 in varchar2 default null
, x_attribute9 in varchar2 default null
, x_attribute10 in varchar2 default null
, x_attribute11 in varchar2 default null
, x_attribute12 in varchar2 default null
, x_attribute13 in varchar2 default null
, x_attribute14 in varchar2 default null
, x_attribute15 in varchar2 default null
, x_attribute_category in varchar2 default null
, x_name in varchar2
, x_description in varchar2 )
IS
-- cursor to check for duplicate skill type
cursor c_dup_task_type is
select 1
from csf_skill_types_tl a, csf_skill_types_b b
where a.skill_type_id = b.skill_type_id
and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
and language = userenv ('lang')
and a.skill_type_id <> x_skill_type_id;
update csf_skill_types_b
set rating_scale_id = x_rating_scale_id
, start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, seeded_flag = nvl(x_seeded_flag, 'N')
, object_version_number = object_version_number + 1
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where skill_type_id = x_skill_type_id
returning object_version_number into l_ovn;
update csf_skill_types_tl
set name = x_name
, description = x_description
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, source_lang = userenv('LANG')
where skill_type_id = x_skill_type_id
and userenv('LANG') in (language, source_lang);
END update_skill_type;
PROCEDURE delete_skill_type ( x_skill_type_id in number )
IS
BEGIN
delete from csf_skill_types_tl
where skill_type_id = x_skill_type_id;
delete from csf_skill_types_b
where skill_type_id = x_skill_type_id;
END delete_skill_type;
delete from csf_skill_types_tl t
where not exists
( select null
from csf_skill_types_b b
where b.skill_type_id = t.skill_type_id );
update csf_skill_types_tl t
set ( name, description ) = ( select b.name
, b.description
from csf_skill_types_tl b
where b.skill_type_id = t.skill_type_id
and b.language = t.source_lang )
where ( t.skill_type_id, t.language ) in
( select subt.skill_type_id
, subt.language
from csf_skill_types_tl subb
, csf_skill_types_tl subt
where subb.skill_type_id = subt.skill_type_id
and subb.language = subt.source_lang
and ( subb.name <> subt.name
or subb.description <> subt.description
or (subb.description is null and subt.description is not null)
or (subb.description is not null and subt.description is null)));
insert into csf_skill_types_tl
( skill_type_id
, name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang )
select b.skill_type_id
, b.name
, b.description
, b.created_by
, b.creation_date
, b.last_updated_by
, b.last_update_date
, b.last_update_login
, l.language_code
, b.source_lang
from csf_skill_types_tl b
, fnd_languages l
where l.installed_flag in ('I', 'B')
and b.language = userenv('LANG')
and not exists
( select null
from csf_skill_types_tl t
where t.skill_type_id = b.skill_type_id
and t.language = l.language_code );
select rowid
from csf_skills_b
where skill_id = x_skill_id;
select 1
from csf_skills_b a, csf_skills_tl b
where a.skill_id = b.skill_id
and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
and skill_type_id = x_skill_type_id
and language = userenv ('lang');
select csf_skills_b_s1.nextval
into x_skill_id
from dual;
insert into csf_skills_b
( skill_id
, skill_type_id
, skill_alias
, start_date_active
, end_date_active
, seeded_flag
, object_version_number
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
values
( x_skill_id
, x_skill_type_id
, x_skill_alias
, x_start_date_active
, x_end_date_active
, nvl(x_seeded_flag, 'N')
, x_object_version_number
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, sysdate
, fnd_global.user_id
, sysdate
, g_user_id
, g_login_id );
insert into csf_skills_tl
( skill_id
, name
, description
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, language
, source_lang )
select x_skill_id
, x_name
, x_description
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, l.language_code
, userenv('LANG')
from fnd_languages l
where l.installed_flag in ('I', 'B')
and not exists
( select null
from csf_skills_tl t
where t.skill_id = x_skill_id
and t.language = l.language_code );
select object_version_number
from csf_skills_b
where skill_id = x_skill_id
for update of skill_id nowait;
select name
, description
, decode(language, userenv('LANG'), 'Y', 'N') baselang
from csf_skills_tl
where skill_id = x_skill_id
and userenv('LANG') in (language, source_lang)
for update of skill_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_skill
( x_skill_id in number
, x_object_version_number in out nocopy number
, x_skill_type_id in number
, x_skill_alias in varchar2
, x_start_date_active in date
, x_end_date_active in date
, x_attribute1 in varchar2 default null
, x_attribute2 in varchar2 default null
, x_attribute3 in varchar2 default null
, x_attribute4 in varchar2 default null
, x_attribute5 in varchar2 default null
, x_attribute6 in varchar2 default null
, x_attribute7 in varchar2 default null
, x_attribute8 in varchar2 default null
, x_attribute9 in varchar2 default null
, x_attribute10 in varchar2 default null
, x_attribute11 in varchar2 default null
, x_attribute12 in varchar2 default null
, x_attribute13 in varchar2 default null
, x_attribute14 in varchar2 default null
, x_attribute15 in varchar2 default null
, x_attribute_category in varchar2 default null
, x_name in varchar2
, x_description in varchar2 )
IS
-- cursor to check for duplicate skills
cursor c_dup_skill_type is
select 1
from csf_skills_b a, csf_skills_tl b
where a.skill_id = b.skill_id
and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
and skill_type_id = x_skill_type_id
and a.skill_id <> x_skill_id
and language = userenv ('lang');
update csf_skills_b
set skill_type_id = x_skill_type_id
, skill_alias = x_skill_alias
, start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, object_version_number = object_version_number + 1
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where skill_id = x_skill_id
returning object_version_number into l_ovn;
update csf_skills_tl
set name = x_name
, description = x_description
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, source_lang = userenv('LANG')
where skill_id = x_skill_id
and userenv('LANG') in (language, source_lang);
END update_skill;
PROCEDURE delete_skill ( x_skill_id in number )
IS
BEGIN
delete from csf_skills_tl
where skill_id = x_skill_id;
delete from csf_skills_b
where skill_id = x_skill_id;
END delete_skill;
delete from csf_skills_tl t
where not exists
( select null
from csf_skills_b b
where b.skill_id = t.skill_id );
update csf_skills_tl t
set ( name
, description ) = ( select b.name
, b.description
from csf_skills_tl b
where b.skill_id = t.skill_id
and b.language = t.source_lang )
where ( t.skill_id, t.language ) in
( select subt.skill_id
, subt.language
from csf_skills_tl subb
, csf_skills_tl subt
where subb.skill_id = subt.skill_id
and subb.language = subt.source_lang
and ( subb.name <> subt.name
or subb.description <> subt.description
or (subb.description is null and subt.description is not null)
or (subb.description is not null and subt.description is null)));
insert into csf_skills_tl
( skill_id
, name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang )
select b.skill_id
, b.name
, b.description
, b.created_by
, b.creation_date
, b.last_updated_by
, b.last_update_date
, b.last_update_login
, l.language_code
, b.source_lang
from csf_skills_tl b
, fnd_languages l
where l.installed_flag in ('I', 'B')
and b.language = userenv('LANG')
and not exists
( select null
from csf_skills_tl t
where t.skill_id = b.skill_id
and t.language = l.language_code );
select rowid
from csf_rating_scales_b
where rating_scale_id = x_rating_scale_id;
select 1
from csf_rating_scales_tl a, csf_rating_scales_b b
where a.rating_scale_id = b.rating_scale_id
and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
and language = userenv ('lang');
select csf_rating_scales_b_s1.nextval
into x_rating_scale_id
from dual;
insert into csf_rating_scales_b
( rating_scale_id
, start_date_active
, end_date_active
, seeded_flag
, object_version_number
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
values
( x_rating_scale_id
, x_start_date_active
, x_end_date_active
, nvl(x_seeded_flag, 'N')
, x_object_version_number
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, sysdate
, fnd_global.user_id
, sysdate
, g_user_id
, g_login_id );
insert into csf_rating_scales_tl
( rating_scale_id
, name
, description
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, language
, source_lang )
select x_rating_scale_id
, x_name
, x_description
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, l.language_code
, userenv('LANG')
from fnd_languages l
where l.installed_flag in ('I', 'B')
and not exists
( select null
from csf_rating_scales_tl t
where t.rating_scale_id = x_rating_scale_id
and t.language = l.language_code );
select object_version_number
from csf_rating_scales_b
where rating_scale_id = x_rating_scale_id
for update of rating_scale_id nowait;
select name
, description
, decode(language, userenv('LANG'), 'Y', 'N') baselang
from csf_rating_scales_tl
where rating_scale_id = x_rating_scale_id
and userenv('LANG') in (language, source_lang)
for update of rating_scale_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_rating_scale
( x_rating_scale_id in number
, x_object_version_number in out nocopy number
, x_start_date_active in date
, x_end_date_active in date
, x_seeded_flag in varchar2 default null
, x_attribute1 in varchar2 default null
, x_attribute2 in varchar2 default null
, x_attribute3 in varchar2 default null
, x_attribute4 in varchar2 default null
, x_attribute5 in varchar2 default null
, x_attribute6 in varchar2 default null
, x_attribute7 in varchar2 default null
, x_attribute8 in varchar2 default null
, x_attribute9 in varchar2 default null
, x_attribute10 in varchar2 default null
, x_attribute11 in varchar2 default null
, x_attribute12 in varchar2 default null
, x_attribute13 in varchar2 default null
, x_attribute14 in varchar2 default null
, x_attribute15 in varchar2 default null
, x_attribute_category in varchar2 default null
, x_name in varchar2
, x_description in varchar2 )
IS
-- cursor to check for duplicate scale names
cursor c_dup_scale_type is
select 1
from csf_rating_scales_tl a, csf_rating_scales_b b
where a.rating_scale_id = b.rating_scale_id
and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
and language = userenv ('lang')
and b.rating_scale_id <> x_rating_scale_id;
update csf_rating_scales_b
set start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, seeded_flag = nvl(x_seeded_flag, 'N')
, object_version_number = object_version_number + 1
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rating_scale_id = x_rating_scale_id
returning object_version_number into l_ovn;
update csf_rating_scales_tl
set name = x_name
, description = x_description
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, source_lang = userenv('LANG')
where rating_scale_id = x_rating_scale_id
and userenv('LANG') in (language, source_lang);
END update_rating_scale;
PROCEDURE delete_rating_scale ( x_rating_scale_id in number )
IS
BEGIN
delete from csf_rating_scales_tl
where rating_scale_id = x_rating_scale_id;
delete from csf_rating_scales_b
where rating_scale_id = x_rating_scale_id;
END delete_rating_scale;
delete from csf_rating_scales_tl t
where not exists
( select null
from csf_rating_scales_b b
where b.rating_scale_id = t.rating_scale_id );
update csf_rating_scales_tl t
set ( name
, description ) = ( select b.name
, b.description
from csf_rating_scales_tl b
where b.rating_scale_id = t.rating_scale_id
and b.language = t.source_lang )
where ( t.rating_scale_id, t.language ) in
( select subt.rating_scale_id
, subt.language
from csf_rating_scales_tl subb
, csf_rating_scales_tl subt
where subb.rating_scale_id = subt.rating_scale_id
and subb.language = subt.source_lang
and ( subb.name <> subt.name
or subb.description <> subt.description
or (subb.description is null and subt.description is not null)
or (subb.description is not null and subt.description is null)));
insert into csf_rating_scales_tl
( rating_scale_id
, name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang )
select b.rating_scale_id
, b.name
, b.description
, b.created_by
, b.creation_date
, b.last_updated_by
, b.last_update_date
, b.last_update_login
, l.language_code
, b.source_lang
from csf_rating_scales_tl b
, fnd_languages l
where l.installed_flag in ('I', 'B')
and b.language = userenv('LANG')
and not exists
( select null
from csf_rating_scales_tl t
where t.rating_scale_id = b.rating_scale_id
and t.language = l.language_code );
select rowid
from csf_skill_levels_b
where skill_level_id = x_skill_level_id;
select 1
from csf_skill_levels_b a, csf_skill_levels_tl b
where a.skill_level_id = b.skill_level_id
and (upper (rtrim (ltrim (b.name))) = upper (rtrim (ltrim (x_name)))
)
and a.rating_scale_id = x_rating_scale_id
and b.language = userenv ('LANG');
select 1
from csf_skill_levels_b a, csf_skill_levels_tl b
where a.skill_level_id = b.skill_level_id
and a.step_value in (
select c.step_value
from csf_skill_levels_b c
where c.skill_level_id = a.skill_level_id
and rating_scale_id = x_rating_scale_id
and c.step_value = x_step_value)
and a.rating_scale_id = x_rating_scale_id
and b.language = userenv ('LANG');
select csf_skill_levels_b_s1.nextval
into x_skill_level_id
from dual;
insert into csf_skill_levels_b
( skill_level_id
, rating_scale_id
, step_value
, correction_factor
, start_date_active
, end_date_active
, seeded_flag
, object_version_number
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
values
( x_skill_level_id
, x_rating_scale_id
, x_step_value
, x_correction_factor
, x_start_date_active
, x_end_date_active
, nvl(x_seeded_flag, 'N')
, x_object_version_number
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, sysdate
, fnd_global.user_id
, sysdate
, g_user_id
, g_login_id );
insert into csf_skill_levels_tl
( skill_level_id
, name
, description
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, language
, source_lang )
select x_skill_level_id
, x_name
, x_description
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, l.language_code
, userenv('LANG')
from fnd_languages l
where l.installed_flag in ('I', 'B')
and not exists
( select null
from csf_skill_levels_tl t
where t.skill_level_id = x_skill_level_id
and t.language = l.language_code );
select object_version_number
from csf_skill_levels_b
where skill_level_id = x_skill_level_id
for update of skill_level_id nowait;
select name
, description
, decode(language, userenv('LANG'), 'Y', 'N') baselang
from csf_skill_levels_tl
where skill_level_id = x_skill_level_id
and userenv('LANG') in (language, source_lang)
for update of skill_level_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_skill_level
( x_skill_level_id in number
, x_object_version_number in out nocopy number
, x_rating_scale_id in number
, x_step_value in number
, x_correction_factor in number
, x_start_date_active in date
, x_end_date_active in date
, x_seeded_flag in varchar2 default null
, x_attribute1 in varchar2 default null
, x_attribute2 in varchar2 default null
, x_attribute3 in varchar2 default null
, x_attribute4 in varchar2 default null
, x_attribute5 in varchar2 default null
, x_attribute6 in varchar2 default null
, x_attribute7 in varchar2 default null
, x_attribute8 in varchar2 default null
, x_attribute9 in varchar2 default null
, x_attribute10 in varchar2 default null
, x_attribute11 in varchar2 default null
, x_attribute12 in varchar2 default null
, x_attribute13 in varchar2 default null
, x_attribute14 in varchar2 default null
, x_attribute15 in varchar2 default null
, x_attribute_category in varchar2 default null
, x_name in varchar2
, x_description in varchar2 )
IS
-- cursor to check for duplicate level name
cursor c_dup_name is
select 1
from csf_skill_levels_b a, csf_skill_levels_tl b
where a.skill_level_id = b.skill_level_id
and (upper (rtrim (ltrim (b.name))) = upper (rtrim (ltrim (x_name)))
)
and a.rating_scale_id = x_rating_scale_id
and b.language = userenv ('LANG')
and a.skill_level_id <> x_skill_level_id;
select 1
from csf_skill_levels_b a, csf_skill_levels_tl b
where a.skill_level_id = b.skill_level_id
and a.step_value in (
select c.step_value
from csf_skill_levels_b c
where c.skill_level_id = a.skill_level_id
and rating_scale_id = x_rating_scale_id
and c.step_value = x_step_value)
and a.rating_scale_id = x_rating_scale_id
and b.language = userenv ('LANG')
and a.skill_level_id <> x_skill_level_id;
update csf_skill_levels_b
set rating_scale_id = x_rating_scale_id
, step_value = x_step_value
, correction_factor = x_correction_factor
, start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, seeded_flag = nvl(x_seeded_flag, 'N')
, object_version_number = object_version_number + 1
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where skill_level_id = x_skill_level_id
returning object_version_number into l_ovn;
update csf_skill_levels_tl
set name = x_name
, description = x_description
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, source_lang = userenv('LANG')
where skill_level_id = x_skill_level_id
and userenv('LANG') in (language, source_lang);
END update_skill_level;
PROCEDURE delete_skill_level ( x_skill_level_id in number )
IS
BEGIN
delete from csf_skill_levels_tl
where skill_level_id = x_skill_level_id;
delete from csf_skill_levels_b
where skill_level_id = x_skill_level_id;
END delete_skill_level;
delete from csf_skill_levels_tl t
where not exists
( select null
from csf_skill_levels_b b
where b.skill_level_id = t.skill_level_id );
update csf_skill_levels_tl t
set ( name
, description ) = ( select b.name
, b.description
from csf_skill_levels_tl b
where b.skill_level_id = t.skill_level_id
and b.language = t.source_lang )
where ( t.skill_level_id, t.language ) in
( select subt.skill_level_id
, subt.language
from csf_skill_levels_tl subb
, csf_skill_levels_tl subt
where subb.skill_level_id = subt.skill_level_id
and subb.language = subt.source_lang
and ( subb.name <> subt.name
or subb.description <> subt.description
or (subb.description is null and subt.description is not null)
or (subb.description is not null and subt.description is null)));
insert into csf_skill_levels_tl
( skill_level_id
, name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang )
select b.skill_level_id
, b.name
, b.description
, b.created_by
, b.creation_date
, b.last_updated_by
, b.last_update_date
, b.last_update_login
, l.language_code
, b.source_lang
from csf_skill_levels_tl b
, fnd_languages l
where l.installed_flag in ('I', 'B')
and b.language = userenv('LANG')
and not exists
( select null
from csf_skill_levels_tl t
where t.skill_level_id = b.skill_level_id
and t.language = l.language_code );
select rowid
from csf_resource_skills_b
where resource_skill_id = x_resource_skill_id;
select 1
from csf_resource_skills_b
where resource_id = x_resource_id
and skill_id = x_skill_id
and resource_type = x_resource_type
and skill_type_id = x_skill_type_id;
select csf_resource_skills_b_s1.nextval
into x_resource_skill_id
from dual;
insert into csf_resource_skills_b
( resource_skill_id
, skill_type_id
, skill_id
, resource_type
, resource_id
, skill_level_id
, start_date_active
, end_date_active
, object_version_number
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
values
( x_resource_skill_id
, x_skill_type_id
, x_skill_id
, x_resource_type
, x_resource_id
, x_skill_level_id
, x_start_date_active
, x_end_date_active
, x_object_version_number
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, sysdate
, fnd_global.user_id
, sysdate
, g_user_id
, g_login_id );
select object_version_number
from csf_resource_skills_b
where resource_skill_id = x_resource_skill_id
for update of resource_skill_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_resource_skill
( x_resource_skill_id in number
, x_object_version_number in out nocopy number
, x_skill_type_id in number
, x_skill_id in number
, x_resource_type in varchar2
, x_resource_id in number
, x_skill_level_id in number
, x_start_date_active in date
, x_end_date_active in date
, x_attribute1 in varchar2 default null
, x_attribute2 in varchar2 default null
, x_attribute3 in varchar2 default null
, x_attribute4 in varchar2 default null
, x_attribute5 in varchar2 default null
, x_attribute6 in varchar2 default null
, x_attribute7 in varchar2 default null
, x_attribute8 in varchar2 default null
, x_attribute9 in varchar2 default null
, x_attribute10 in varchar2 default null
, x_attribute11 in varchar2 default null
, x_attribute12 in varchar2 default null
, x_attribute13 in varchar2 default null
, x_attribute14 in varchar2 default null
, x_attribute15 in varchar2 default null
, x_attribute_category in varchar2 default null )
IS
-- cursor to check for duplicate skills assigned to a resource
cursor c_resSkill is
select 1
from csf_resource_skills_b
where resource_id = x_resource_id
and skill_id = x_skill_id
and resource_type = x_resource_type
and skill_type_id = x_skill_type_id
and resource_skill_id <> x_resource_skill_id;
update csf_resource_skills_b
set skill_type_id = x_skill_type_id
, skill_id = x_skill_id
, resource_type = x_resource_type
, resource_id = x_resource_id
, skill_level_id = x_skill_level_id
, start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, object_version_number = object_version_number + 1
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where resource_skill_id = x_resource_skill_id
returning object_version_number into l_ovn;
END update_resource_skill;
PROCEDURE delete_resource_skill ( x_resource_skill_id in number )
IS
BEGIN
delete from csf_resource_skills_b
where resource_skill_id = x_resource_skill_id;
END delete_resource_skill;
select rowid
from csf_required_skills_b
where required_skill_id = x_required_skill_id;
select 1
from csf_required_skills_b
where skill_id = x_skill_id
and skill_type_id = x_skill_type_id
and has_skill_type = x_has_skill_type
and has_skill_id = x_has_skill_id;
select csf_required_skills_b_s1.nextval
into x_required_skill_id
from dual;
insert into csf_required_skills_b
( required_skill_id
, skill_type_id
, skill_id
, has_skill_type
, has_skill_id
, skill_level_id
, skill_required_flag
, level_required_flag
, disabled_flag
, start_date_active
, end_date_active
, object_version_number
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
values
( x_required_skill_id
, x_skill_type_id
, x_skill_id
, x_has_skill_type
, x_has_skill_id
, x_skill_level_id
, x_skill_required_flag
, x_level_required_flag
, x_disabled_flag
, x_start_date_active
, x_end_date_active
, x_object_version_number
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_attribute_category
, sysdate
, fnd_global.user_id
, sysdate
, g_user_id
, g_login_id );
select object_version_number
from csf_required_skills_b
where required_skill_id = x_required_skill_id
for update of required_skill_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_required_skill
( x_required_skill_id in number
, x_object_version_number in out nocopy number
, x_skill_type_id in number
, x_skill_id in number
, x_has_skill_type in varchar2
, x_has_skill_id in number
, x_skill_level_id in number
, x_skill_required_flag in varchar2
, x_level_required_flag in varchar2
, x_disabled_flag in varchar2
, x_start_date_active in date
, x_end_date_active in date
, x_attribute1 in varchar2 default null
, x_attribute2 in varchar2 default null
, x_attribute3 in varchar2 default null
, x_attribute4 in varchar2 default null
, x_attribute5 in varchar2 default null
, x_attribute6 in varchar2 default null
, x_attribute7 in varchar2 default null
, x_attribute8 in varchar2 default null
, x_attribute9 in varchar2 default null
, x_attribute10 in varchar2 default null
, x_attribute11 in varchar2 default null
, x_attribute12 in varchar2 default null
, x_attribute13 in varchar2 default null
, x_attribute14 in varchar2 default null
, x_attribute15 in varchar2 default null
, x_attribute_category in varchar2 default null )
IS
l_ovn number;
update csf_required_skills_b
set skill_type_id = x_skill_type_id
, skill_id = x_skill_id
, has_skill_type = x_has_skill_type
, has_skill_id = x_has_skill_id
, skill_level_id = x_skill_level_id
, skill_required_flag = x_skill_required_flag
, level_required_flag = x_level_required_flag
, disabled_flag = x_disabled_flag
, start_date_active = x_start_date_active
, end_date_active = x_end_date_active
, object_version_number = object_version_number + 1
, attribute1 = x_attribute1
, attribute2 = x_attribute2
, attribute3 = x_attribute3
, attribute4 = x_attribute4
, attribute5 = x_attribute5
, attribute6 = x_attribute6
, attribute7 = x_attribute7
, attribute8 = x_attribute8
, attribute9 = x_attribute9
, attribute10 = x_attribute10
, attribute11 = x_attribute11
, attribute12 = x_attribute12
, attribute13 = x_attribute13
, attribute14 = x_attribute14
, attribute15 = x_attribute15
, attribute_category = x_attribute_category
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where required_skill_id = x_required_skill_id
returning object_version_number into l_ovn;
END update_required_skill;
PROCEDURE delete_required_skill ( x_required_skill_id in number )
IS
BEGIN
delete from csf_required_skills_b
where required_skill_id = x_required_skill_id;
END delete_required_skill;
update csf_rating_scales_tl
set name = p_name,
description = p_description,
last_update_date = sysdate,
last_updated_by = decode(p_owner, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
where rating_scale_id = to_number(p_rating_scale_id)
and userenv('LANG') in (language, source_lang);
update csf_skills_tl
set name = p_name,
description = p_description,
last_update_date = sysdate,
last_updated_by = decode(p_owner, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
where skill_id = to_number(p_skill_id)
and userenv('LANG') in (language, source_lang);
update csf_skill_levels_tl
set name = p_name,
description = p_description,
last_update_date = sysdate,
last_updated_by = decode(p_owner, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
where skill_level_id = to_number(p_skill_level_id)
and userenv('LANG') in (language, source_lang);
update csf_skill_types_tl
set name = p_name,
description = p_description,
last_update_date = sysdate,
last_updated_by = decode(p_owner, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
where skill_type_id = to_number(p_skill_type_id)
and userenv('LANG') in (language, source_lang);