The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_operation => 'INSERT',
x_return_status => l_return_status,
x_competence_id => l_competence_id,
x_rating_level_id => l_rating_level_id );
PROCEDURE Update_competence_element
(p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
p_element_rowid IN ROWID := FND_API.G_MISS_CHAR,
p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
p_mandatory_flag IN per_competence_elements.mandatory%TYPE := FND_API.G_MISS_CHAR,
p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_object_version_number IN NUMBER,
x_object_version_number OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_data VARCHAR2(500);
pa_debug.init_err_stack ('pa_competence_pub.update_competence_element');
p_operation => 'UPDATE',
x_return_status => l_return_status,
x_competence_id => l_competence_id,
x_rating_level_id => l_rating_level_id );
pa_competence_pvt.update_competence_element
( p_object_name => p_object_name
,p_object_id => p_object_id
,p_competence_id => l_competence_id
,p_element_rowid => p_element_rowid
,p_element_id => p_element_id
,p_rating_level_id => l_rating_level_id
,p_mandatory_flag => p_mandatory_flag
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_object_version_number => p_object_version_number
,x_object_version_number => x_object_version_number
,x_return_status => l_return_status );
END update_competence_element ;
PROCEDURE delete_competence_element
(p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
p_competence_name IN per_competences.name%TYPE := chr(0),
p_element_rowid IN ROWID := FND_API.G_MISS_CHAR,
p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_data VARCHAR2(500);
pa_debug.init_err_stack ('pa_competence_pub.delete_competence_element');
p_operation => 'DELETE',
x_return_status => l_return_status,
x_competence_id => l_competence_id,
x_rating_level_id => l_rating_level_id );
pa_competence_pvt.delete_competence_element
(p_object_name => p_object_name,
p_object_id => p_object_id,
p_competence_id => l_competence_id,
p_element_rowid => p_element_rowid,
p_element_id => p_element_id,
p_commit => p_commit,
p_validate_only => p_validate_only,
p_object_version_number => p_object_version_number,
x_return_status => l_return_status );
END delete_competence_element;
IF p_operation = 'INSERT' THEN
IF l_in_competence_name is not null AND
l_in_competence_alias is not null THEN
BEGIN
SELECT 'Y'
INTO l_exists
FROM per_competences
WHERE name = l_in_competence_name
AND competence_alias = l_in_competence_alias;
IF p_operation = 'UPDATE' THEN
IF p_element_id is not null THEN
SELECT comp_ele.competence_id,
comp.name,
comp.competence_alias
INTO l_old_competence_id,
l_old_comp_name,
l_old_comp_alias
FROM per_competence_elements comp_ele,
per_competences comp
WHERE COMPETENCE_ELEMENT_ID = p_element_id
AND comp.competence_id = comp_ele.competence_id;
( p_asgn_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_project_id IN pa_project_assignments.project_id%TYPE
,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
,p_competence_id_tbl IN SYSTEM.pa_num_tbl_type
,p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type
,p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type
,p_rating_level_value_tbl IN SYSTEM.pa_num_tbl_type
,p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_wf_mode VARCHAR2(200);
p_operation => 'UPDATE',
x_return_status => l_return_status,
x_competence_id => l_competence_id,
x_rating_level_id => l_rating_level_id_tbl(I));
l_wf_mode := PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES;
then the Update API is called to update the HR Competence
Element record. If the competence does not exist, the
INSERT API is called to create the new competence element.
If even one competence processing errors out for a given
assignment, then all the competence changes are rolled back
for that assignment.
-------------------------------------------------------------------- */
PROCEDURE Mass_Process_Competences
( p_project_id IN pa_project_assignments.project_id%TYPE,
p_assignment_tbl IN SYSTEM.pa_num_tbl_type,
p_competence_id_tbl IN SYSTEM.pa_num_tbl_type,
p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type,
p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type,
p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type,
p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_success_assignment_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, /* Added NOCOPY for bug#2674619 */
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_element_rowid ROWID;
p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES,
p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
p_context1 => p_project_id,
p_context2 => p_assignment_tbl(I-1),
p_context3 => NULL,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status);
SELECT object_version_number,
competence_element_id
INTO l_object_version_number,
l_competence_element_id
FROM per_competence_elements
WHERE object_id = p_assignment_tbl(I)
AND object_name = 'OPEN_ASSIGNMENT'
AND competence_id = p_competence_id_tbl(J);
l_mode := 'UPDATE';
l_mode := 'INSERT';
IF (l_mode = 'UPDATE')
THEN
Update_competence_element
(p_element_id => l_competence_element_id,
p_object_name => 'OPEN_ASSIGNMENT',
p_object_id => p_assignment_tbl(I),
p_competence_id => p_competence_id_tbl(J),
p_competence_alias => p_competence_alias_tbl(J),
p_rating_level_id => p_rating_level_id_tbl(J),
p_mandatory_flag => p_mandatory_flag_tbl(J),
p_object_version_number => l_object_version_number,
-- x_object_version_number => l_object_version_number, * commented for bug: 4537865
x_object_version_number => l_new_object_version_number, -- added for bug: 4537865
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES,
p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
p_context1 => p_project_id,
p_context2 => p_assignment_tbl(p_assignment_tbl.count),
p_context3 => NULL,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status);