[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select competence_structure
from per_business_groups
where business_group_id = p_business_group_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from per_competence_definitions
where competence_definition_id = l_competence_definition_id;
select name
from per_competences_vl
where competence_definition_id = l_competence_definition_id;
procedure update_competence
(p_validate in boolean default false,
p_effective_date in date,
p_competence_id in number,
p_object_version_number in out nocopy number,
p_language_code in varchar2 default hr_api.userenv_lang,
p_name out nocopy varchar2 ,
p_description in varchar2 default hr_api.g_varchar2,
p_date_from in date default hr_api.g_date,
p_date_to in date default hr_api.g_date,
p_behavioural_indicator in varchar2 default hr_api.g_varchar2,
p_certification_required in varchar2 default hr_api.g_varchar2,
p_evaluation_method in varchar2 default hr_api.g_varchar2,
p_renewal_period_frequency in number default hr_api.g_number,
p_renewal_period_units in varchar2 default hr_api.g_varchar2,
p_rating_scale_id in number default hr_api.g_number,
p_attribute_category in varchar2 default hr_api.g_varchar2,
p_attribute1 in varchar2 default hr_api.g_varchar2,
p_attribute2 in varchar2 default hr_api.g_varchar2,
p_attribute3 in varchar2 default hr_api.g_varchar2,
p_attribute4 in varchar2 default hr_api.g_varchar2,
p_attribute5 in varchar2 default hr_api.g_varchar2,
p_attribute6 in varchar2 default hr_api.g_varchar2,
p_attribute7 in varchar2 default hr_api.g_varchar2,
p_attribute8 in varchar2 default hr_api.g_varchar2,
p_attribute9 in varchar2 default hr_api.g_varchar2,
p_attribute10 in varchar2 default hr_api.g_varchar2,
p_attribute11 in varchar2 default hr_api.g_varchar2,
p_attribute12 in varchar2 default hr_api.g_varchar2,
p_attribute13 in varchar2 default hr_api.g_varchar2,
p_attribute14 in varchar2 default hr_api.g_varchar2,
p_attribute15 in varchar2 default hr_api.g_varchar2,
p_attribute16 in varchar2 default hr_api.g_varchar2,
p_attribute17 in varchar2 default hr_api.g_varchar2,
p_attribute18 in varchar2 default hr_api.g_varchar2,
p_attribute19 in varchar2 default hr_api.g_varchar2,
p_attribute20 in varchar2 default hr_api.g_varchar2,
p_competence_alias in varchar2 default hr_api.g_varchar2,
p_segment1 in varchar2 default hr_api.g_varchar2,
p_segment2 in varchar2 default hr_api.g_varchar2,
p_segment3 in varchar2 default hr_api.g_varchar2,
p_segment4 in varchar2 default hr_api.g_varchar2,
p_segment5 in varchar2 default hr_api.g_varchar2,
p_segment6 in varchar2 default hr_api.g_varchar2,
p_segment7 in varchar2 default hr_api.g_varchar2,
p_segment8 in varchar2 default hr_api.g_varchar2,
p_segment9 in varchar2 default hr_api.g_varchar2,
p_segment10 in varchar2 default hr_api.g_varchar2,
p_segment11 in varchar2 default hr_api.g_varchar2,
p_segment12 in varchar2 default hr_api.g_varchar2,
p_segment13 in varchar2 default hr_api.g_varchar2,
p_segment14 in varchar2 default hr_api.g_varchar2,
p_segment15 in varchar2 default hr_api.g_varchar2,
p_segment16 in varchar2 default hr_api.g_varchar2,
p_segment17 in varchar2 default hr_api.g_varchar2,
p_segment18 in varchar2 default hr_api.g_varchar2,
p_segment19 in varchar2 default hr_api.g_varchar2,
p_segment20 in varchar2 default hr_api.g_varchar2,
p_segment21 in varchar2 default hr_api.g_varchar2,
p_segment22 in varchar2 default hr_api.g_varchar2,
p_segment23 in varchar2 default hr_api.g_varchar2,
p_segment24 in varchar2 default hr_api.g_varchar2,
p_segment25 in varchar2 default hr_api.g_varchar2,
p_segment26 in varchar2 default hr_api.g_varchar2,
p_segment27 in varchar2 default hr_api.g_varchar2,
p_segment28 in varchar2 default hr_api.g_varchar2,
p_segment29 in varchar2 default hr_api.g_varchar2,
p_segment30 in varchar2 default hr_api.g_varchar2,
p_concat_segments in varchar2 default hr_api.g_varchar2,
--
-- bug 2267635 change p_competence_definition_id from an out to an in/out
-- parameter to enable value to be passed into program when known and required
--
p_competence_definition_id in out nocopy number
--
-- BUG3356369 Added unit standard qualification framework
--
,p_competence_cluster in varchar2 default hr_api.g_varchar2
,p_unit_standard_id in varchar2 default hr_api.g_varchar2
,p_credit_type in varchar2 default hr_api.g_varchar2
,p_credits in number default hr_api.g_number
,p_level_type in varchar2 default hr_api.g_varchar2
,p_level_number in number default hr_api.g_number
,p_field in varchar2 default hr_api.g_varchar2
,p_sub_field in varchar2 default hr_api.g_varchar2
,p_provider in varchar2 default hr_api.g_varchar2
,p_qa_organization in varchar2 default hr_api.g_varchar2
,p_information_category in varchar2 default hr_api.g_varchar2
,p_information1 in varchar2 default hr_api.g_varchar2
,p_information2 in varchar2 default hr_api.g_varchar2
,p_information3 in varchar2 default hr_api.g_varchar2
,p_information4 in varchar2 default hr_api.g_varchar2
,p_information5 in varchar2 default hr_api.g_varchar2
,p_information6 in varchar2 default hr_api.g_varchar2
,p_information7 in varchar2 default hr_api.g_varchar2
,p_information8 in varchar2 default hr_api.g_varchar2
,p_information9 in varchar2 default hr_api.g_varchar2
,p_information10 in varchar2 default hr_api.g_varchar2
,p_information11 in varchar2 default hr_api.g_varchar2
,p_information12 in varchar2 default hr_api.g_varchar2
,p_information13 in varchar2 default hr_api.g_varchar2
,p_information14 in varchar2 default hr_api.g_varchar2
,p_information15 in varchar2 default hr_api.g_varchar2
,p_information16 in varchar2 default hr_api.g_varchar2
,p_information17 in varchar2 default hr_api.g_varchar2
,p_information18 in varchar2 default hr_api.g_varchar2
,p_information19 in varchar2 default hr_api.g_varchar2
,p_information20 in varchar2 default hr_api.g_varchar2
) is
--
-- Declare cursors and local variables
--
-- bug 2267635 initialize l_competence_definition_id and segmnt variables with
-- values where these are passed into program.
--
l_proc varchar2(72) := g_package||'update_competence';
select cmp.id_flex_num
from per_competence_definitions cmp
where cmp.competence_definition_id = l_competence_definition_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from per_competence_definitions
where competence_definition_id = l_competence_definition_id;
select name
from per_competences_vl
where competence_definition_id = l_competence_definition_id;
savepoint update_competence;
hr_competences_bk2.update_competence_b (
p_competence_id => p_competence_id,
p_object_version_number => p_object_version_number,
p_effective_date => p_effective_date,
p_description => p_description,
p_date_from => trunc(p_date_from),
p_date_to => trunc(p_date_to),
p_behavioural_indicator => p_behavioural_indicator,
p_certification_required => p_certification_required,
p_evaluation_method => p_evaluation_method,
p_renewal_period_frequency => p_renewal_period_frequency,
p_renewal_period_units => p_renewal_period_units,
p_rating_scale_id => p_rating_scale_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_competence_alias => p_competence_alias,
p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment6 => l_segment6
,p_segment7 => l_segment7
,p_segment8 => l_segment8
,p_segment9 => l_segment9
,p_segment10 => l_segment10
,p_segment11 => l_segment11
,p_segment12 => l_segment12
,p_segment13 => l_segment13
,p_segment14 => l_segment14
,p_segment15 => l_segment15
,p_segment16 => l_segment16
,p_segment17 => l_segment17
,p_segment18 => l_segment18
,p_segment19 => l_segment19
,p_segment20 => l_segment20
,p_segment21 => l_segment21
,p_segment22 => l_segment22
,p_segment23 => l_segment23
,p_segment24 => l_segment24
,p_segment25 => l_segment25
,p_segment26 => l_segment26
,p_segment27 => l_segment27
,p_segment28 => l_segment28
,p_segment29 => l_segment29
,p_segment30 => l_segment30
,p_concat_segments => p_concat_segments
,p_language_code => l_language_code
,p_competence_cluster => p_competence_cluster
,p_unit_standard_id => p_unit_standard_id
,p_credit_type => p_credit_type
,p_credits => p_credits
,p_level_type => p_level_type
,p_level_number => p_level_number
,p_field => p_field
,p_sub_field => p_sub_field
,p_provider => p_provider
,p_qa_organization => p_qa_organization
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_information6 => p_information6
,p_information7 => p_information7
,p_information8 => p_information8
,p_information9 => p_information9
,p_information10 => p_information10
,p_information11 => p_information11
,p_information12 => p_information12
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
);
(p_module_name => 'update_competence',
p_hook_type => 'BP'
);
hr_competences_bk2.update_competence_a (
p_competence_id => p_competence_id,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_name => l_name,
p_description => p_description,
p_date_from => trunc(p_date_from),
p_date_to => trunc(p_date_to),
p_behavioural_indicator => p_behavioural_indicator,
p_certification_required => p_certification_required,
p_evaluation_method => p_evaluation_method,
p_renewal_period_frequency => p_renewal_period_frequency,
p_renewal_period_units => p_renewal_period_units,
p_rating_scale_id => p_rating_scale_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_competence_alias => p_competence_alias,
p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment6 => l_segment6
,p_segment7 => l_segment7
,p_segment8 => l_segment8
,p_segment9 => l_segment9
,p_segment10 => l_segment10
,p_segment11 => l_segment11
,p_segment12 => l_segment12
,p_segment13 => l_segment13
,p_segment14 => l_segment14
,p_segment15 => l_segment15
,p_segment16 => l_segment16
,p_segment17 => l_segment17
,p_segment18 => l_segment18
,p_segment19 => l_segment19
,p_segment20 => l_segment20
,p_segment21 => l_segment21
,p_segment22 => l_segment22
,p_segment23 => l_segment23
,p_segment24 => l_segment24
,p_segment25 => l_segment25
,p_segment26 => l_segment26
,p_segment27 => l_segment27
,p_segment28 => l_segment28
,p_segment29 => l_segment29
,p_segment30 => l_segment30
,p_concat_segments => p_concat_segments
,p_competence_definition_id => l_competence_definition_id
,p_language_code => l_language_code
,p_competence_cluster => p_competence_cluster
,p_unit_standard_id => p_unit_standard_id
,p_credit_type => p_credit_type
,p_credits => p_credits
,p_level_type => p_level_type
,p_level_number => p_level_number
,p_field => p_field
,p_sub_field => p_sub_field
,p_provider => p_provider
,p_qa_organization => p_qa_organization
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_information6 => p_information6
,p_information7 => p_information7
,p_information8 => p_information8
,p_information9 => p_information9
,p_information10 => p_information10
,p_information11 => p_information11
,p_information12 => p_information12
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
);
(p_module_name => 'update_competence',
p_hook_type => 'AP'
);
ROLLBACK TO update_competence;
ROLLBACK TO update_competence;
end update_competence;
procedure delete_competence
(p_validate in boolean default false,
p_competence_id in number,
p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
cursor csr_get_prof_levels(c_competence_id per_competences.competence_id%TYPE)
is
select rating_level_id,
object_version_number
from per_rating_levels
where competence_id = c_competence_id;
l_proc varchar2(72) := g_package||'delete_competence';
savepoint delete_competence;
hr_competences_bk3.delete_competence_b
(
p_competence_id => p_competence_id,
p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_competence',
p_hook_type => 'BP'
);
hr_competences_bk3.delete_competence_a (
p_competence_id => p_competence_id,
p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_competence',
p_hook_type => 'AP'
);
ROLLBACK TO delete_competence;
ROLLBACK TO delete_competence;
end delete_competence;
procedure create_or_update_competence
(p_validate in boolean default false
,p_effective_date in date default trunc(sysdate)
,p_language_code in varchar2 default hr_api.userenv_lang
,p_category in varchar2 default null
,p_name in varchar2 default null
,p_date_from in date default trunc(sysdate)
,p_date_to in date default null
,p_description in varchar2 default null
,p_competence_alias in varchar2 default null
,p_behavioural_indicator in varchar2 default null
,p_certification_required in varchar2 default null
,p_evaluation_method in varchar2 default null
,p_renewal_period_frequency in number default null
,p_renewal_period_units in varchar2 default null
,p_rating_scale_name in varchar2 default null
,p_translated_language in varchar2 default null
,p_source_category_name in varchar2 default null
,p_source_competence_name in varchar2 default null
) is
l_proc varchar2(72) := g_package||'create_or_update_competence';
select competence_id , object_version_number
from per_competences_v
where business_group_id is null
and name = p_competence_name;
select rating_scale_id
from per_rating_scales
where business_group_id is null
and name = p_rating_scale_name;
select flv.lookup_code
from fnd_lookup_values flv
where flv.lookup_type = p_lookup_type
and UPPER(flv.meaning) = UPPER(p_meaning)
and flv.language = p_language_code
and flv.enabled_flag = 'Y';
savepoint create_or_update_competence;
update_competence
(p_effective_date => l_effective_date
,p_language_code => l_language_code
,p_description => p_description
,p_competence_alias => p_competence_alias
,p_date_from => l_date_from
,p_date_to => p_date_to
,p_behavioural_indicator => p_behavioural_indicator
,p_certification_required => l_certification_required
,p_evaluation_method => l_evaluation_method
,p_renewal_period_frequency => p_renewal_period_frequency
,p_renewal_period_units => l_renewal_period_units
,p_rating_scale_id => l_rating_scale_id
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_competence_id => l_competence_id
,p_competence_definition_id => l_competence_definition_id
,p_object_version_number => l_ovn
,p_name => l_conc_name
);
hr_competences_bk2.update_competence_a (
p_competence_id => l_source_competence_id,
p_object_version_number => l_source_ovn,
p_effective_date => l_effective_date,
p_name => p_source_competence_name,
p_description => p_description,
p_date_from => trunc(l_date_from),
p_date_to => trunc(p_date_to),
p_behavioural_indicator => p_behavioural_indicator,
p_certification_required => p_certification_required,
p_evaluation_method => p_evaluation_method,
p_renewal_period_frequency => p_renewal_period_frequency,
p_renewal_period_units => p_renewal_period_units,
p_rating_scale_id => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_competence_alias => p_competence_alias,
p_segment1 => NULL
,p_segment2 => NULL
,p_segment3 => NULL
,p_segment4 => NULL
,p_segment5 => NULL
,p_segment6 => NULL
,p_segment7 => NULL
,p_segment8 => NULL
,p_segment9 => NULL
,p_segment10 => NULL
,p_segment11 => NULL
,p_segment12 => NULL
,p_segment13 => NULL
,p_segment14 => NULL
,p_segment15 => NULL
,p_segment16 => NULL
,p_segment17 => NULL
,p_segment18 => NULL
,p_segment19 => NULL
,p_segment20 => NULL
,p_segment21 => NULL
,p_segment22 => NULL
,p_segment23 => NULL
,p_segment24 => NULL
,p_segment25 => NULL
,p_segment26 => NULL
,p_segment27 => NULL
,p_segment28 => NULL
,p_segment29 => NULL
,p_segment30 => NULL
,p_concat_segments => NULL
,p_competence_definition_id => NULL
,p_language_code => l_translated_language
,p_competence_cluster => NULL
,p_unit_standard_id => NULL
,p_credit_type => NULL
,p_credits => NULL
,p_level_type => NULL
,p_level_number => NULL
,p_field => NULL
,p_sub_field => NULL
,p_provider => NULL
,p_qa_organization => NULL
,p_information_category => NULL
,p_information1 => NULL
,p_information2 => NULL
,p_information3 => NULL
,p_information4 => NULL
,p_information5 => NULL
,p_information6 => NULL
,p_information7 => NULL
,p_information8 => NULL
,p_information9 => NULL
,p_information10 => NULL
,p_information11 => NULL
,p_information12 => NULL
,p_information13 => NULL
,p_information14 => NULL
,p_information15 => NULL
,p_information16 => NULL
,p_information17 => NULL
,p_information18 => NULL
,p_information19 => NULL
,p_information20 => NULL
);
(p_module_name => 'update_competence',
p_hook_type => 'AP'
);
ROLLBACK TO creaet_or_update_competence;
ROLLBACK TO create_or_update_competence;
end create_or_update_competence;