The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Bug 3116962 : Added a IF clause to check if the project_role_id is valid or not. Accordingly the SELECT statement below will execute. */
IF l_project_role_id <> -999 AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
SELECT resource_type_id into x_resource_type_id
FROM pa_resource_types
WHERE resource_type_code='HZ_PARTY';
select role_party_class into x_role_party_class
from pa_project_role_types_b
where project_role_id = l_project_role_id;
PROCEDURE UPDATE_PROJECT_PARTY( p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_debug_mode IN VARCHAR2 default 'N',
p_object_id IN NUMBER := FND_API.G_MISS_NUM,
p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_project_role_id IN NUMBER,
p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_resource_type_id IN NUMBER := 101, --EMPLOYEE
p_resource_source_id IN NUMBER,
p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
p_start_date_active IN DATE,
p_scheduled_flag IN VARCHAR2 := 'N',
p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_project_id IN NUMBER := FND_API.G_MISS_NUM,
p_project_end_date IN DATE := FND_API.G_MISS_DATE,
p_project_party_id IN NUMBER,
p_assignment_id IN NUMBER := 0,
p_assign_record_version_number IN NUMBER := 0,
p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
x_assignment_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_wf_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_wf_item_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_wf_process OUT NOCOPY VARCHAR2, --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_error_occured VARCHAR2(1) := 'N';
l_api_name VARCHAR2(30) := 'Update_project_party';
SAVEPOINT update_project_party;
pa_debug.debug('Update_project_party: Checking he api version number.');
pa_debug.init_err_stack('Update_project_party_pub');
pa_debug.debug('Update_project_party: Initializing message stack.');
PA_PROJECT_PARTIES_PVT.UPDATE_PROJECT_PARTY( p_commit => p_commit,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_debug_mode => p_debug_mode,
p_object_id => p_object_id,
p_OBJECT_TYPE => p_object_type,
p_project_role_id => l_project_role_id,
p_resource_type_id => l_resource_type_id,
p_resource_source_id => l_resource_source_id,
p_resource_id => p_resource_id,
p_start_date_active => l_start_date_active,
p_scheduled_flag => p_scheduled_flag,
p_record_version_number => p_record_version_number,
p_calling_module => p_calling_module,
p_project_id => l_project_id,
p_project_end_date => l_project_end_date,
p_project_party_id => p_project_party_id,
p_assignment_id => p_assignment_id,
p_assign_record_version_number => p_assign_record_version_number,
p_end_date_active => p_end_date_active,
p_mgr_validation_type => p_mgr_validation_type,
x_assignment_id => x_assignment_id,
x_wf_type => x_wf_type,
x_wf_item_type => x_wf_item_type,
x_wf_process => x_wf_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_message.set_token('PROCEDURE_NAME','UPDATE_PROJECT_PARTY');
rollback to update_project_party;
end update_project_party;
PROCEDURE DELETE_PROJECT_PARTY( p_api_version IN NUMBER := 1.0,
/* modified the default value for p_init_msg_list from FND_API.G_TRUE to FND_API.G_FALSE, for
the bug#1851096 */
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_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_debug_mode IN VARCHAR2 default 'N',
p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_project_id IN NUMBER := FND_API.G_MISS_NUM,
p_project_party_id IN NUMBER := FND_API.G_MISS_NUM,
p_scheduled_flag IN VARCHAR2 default 'N',
p_assignment_id IN NUMBER := 0,
p_assign_record_version_number IN NUMBER := 0,
p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
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_project_id NUMBER := 0;
l_api_name VARCHAR2(30) := 'delete_project_party';
select count(pt.task_id) from pa_tasks pt where project_id=p_project_id and task_manager_person_id =
(select resource_source_id from pa_project_parties where project_party_id=p_project_party_id
and project_id=p_project_id)
and exists
(select 1 from pa_progress_rollup where project_id=pt.project_id and proj_element_id = pt.task_id
and structure_version_id is null
);
pa_debug.debug('Delete_project_party: Checking he api version number.');
pa_debug.init_err_stack('Delete_project_party_pub');
pa_debug.debug('Delete_project_party: Initializing message stack.');
pa_debug.debug('Delete_project_party : Before delete from pa_project_players ');
PA_ASSIGNMENTS_PUB.Delete_Assignment
( p_assignment_id => p_assignment_id
,p_assignment_type => 'STAFFED_ASSIGNMENT'
,p_record_version_number => p_assign_record_version_number
,p_commit => p_commit
,p_validate_only => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJECT_PARTIES_PVT.DELETE_PROJECT_PARTY( p_commit => p_commit,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_debug_mode => p_debug_mode,
p_record_version_number => p_record_version_number,
p_calling_module => p_calling_module,
p_project_id => p_project_id,
p_project_party_id => p_project_party_id,
p_scheduled_flag => p_scheduled_flag,
p_assignment_id => p_assignment_id,
p_assign_record_version_number => p_assign_record_version_number,
p_mgr_validation_type => p_mgr_validation_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_message.set_token('PROCEDURE_NAME','DELETE_PROJECT_PARTY');
pa_debug.debug('Delete_project_party : Exception NO_DATA_FOUND ');
pa_debug.debug('Delete_project_party : Exception OTHERS ');
END DELETE_PROJECT_PARTY;
/* Bug 2636791 - We will be selecting start date
by calling PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE API*/
/*
CURSOR date_csr IS
SELECT start_date from pa_projects_all
WHERE project_id = p_project_id;
end dates for the existing manager and update the end date of the existing manager to either
(a) new manager's start date -1 or (b) sysdate -1
(being done in check_for_one_manager);