[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'INSERT',
l_project_id,
p_project_end_date,
p_end_date_active,
l_project_party_id,
x_call_overlap,
x_assignment_action,
x_return_status);
SELECT 'Y' INTO l_is_valid_denorm FROM pa_resources_denorm
WHERE person_id = p_resource_source_id
AND rownum=1;
SELECT resource_id
INTO l_resource_id
FROM pa_resource_txn_attributes
WHERE person_id = p_resource_source_id
and rownum=1;
select resource_id into l_resource_id
from pa_resource_txn_attributes
where party_id = p_resource_source_id
and rownum=1;
SELECT max(resource_effective_end_date)
INTO l_date
FROM pa_resources_denorm
WHERE resource_id = l_resource_id ;
l_job_schedulable := PA_HR_UPDATE_API.check_job_schedulable
( p_person_id => p_resource_source_id
,p_date => l_date );
pa_debug.debug('Create_project_party: Calling update_row.');
pa_debug.G_err_stage := 'Calling update_row from create_project_party';
PA_PROJECT_PARTIES_PKG.UPDATE_ROW (
X_PROJECT_PARTY_ID => l_project_party_id,
X_PROJECT_ID => l_project_id,
X_RESOURCE_SOURCE_ID => p_resource_source_id,
X_RESOURCE_TYPE_ID => p_resource_type_id,
X_PROJECT_ROLE_ID => p_project_role_id,
X_START_DATE_ACTIVE => trunc(p_start_date_active),
X_END_DATE_ACTIVE => trunc(p_end_date_active),
X_GRANT_ID => null,
X_SCHEDULED_FLAG => NVL(p_scheduled_flag, 'N'),
X_RECORD_VERSION_NUMBER => l_record_version_number,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_RETURN_STATUS => x_return_status);
pa_debug.debug('Create_project_party: Calling insert_row.');
pa_debug.G_err_stage := 'Calling insert_row';
PA_PROJECT_PARTIES_PKG.INSERT_ROW (
X_PROJECT_PARTY_ID => x_project_party_id,
X_OBJECT_ID => p_object_id,
X_OBJECT_TYPE => p_object_type,
X_PROJECT_ID => l_project_id,
X_RESOURCE_ID => l_resource_id,
X_RESOURCE_TYPE_ID => p_resource_type_id,
X_RESOURCE_SOURCE_ID => p_resource_source_id,
X_PROJECT_ROLE_ID => p_project_role_id,
X_START_DATE_ACTIVE => trunc(p_start_date_active),
X_END_DATE_ACTIVE => trunc(p_end_date_active),
X_SCHEDULED_FLAG => NVL(p_scheduled_flag, 'N'),
X_GRANT_ID => l_grant_id,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID) ;
PROCEDURE UPDATE_PROJECT_PARTY( 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_resource_type_id IN NUMBER := 101,
p_resource_source_id IN NUMBER,
p_resource_id IN NUMBER,
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,
p_project_party_id IN NUMBER,
p_assignment_id IN NUMBER,
p_assign_record_version_number IN NUMBER,
p_mgr_validation_type IN VARCHAR2 := FND_API.G_MISS_CHAR,/*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(50) := 'N';
SELECT * FROM pa_project_assignments
WHERE project_id = p_project_id
AND ASSIGNMENT_TYPE = 'STAFFED_ASSIGNMENT'
AND PROJECT_ROLE_ID = 1
AND start_date = p_start_date_active
AND resource_id = p_resource_id
AND APPRVL_STATUS_CODE NOT IN -- This condition added after bug 7023082
('ASGMT_APPRVL_REJECTED','ASGMT_APPRVL_CANCELED');
pa_debug.debug('Update_project_party: Calling validate_project_party.');
'UPDATE',
p_project_id,
p_project_end_date,
p_end_date_active,
l_project_party_id,
x_call_overlap,
x_assignment_action,
x_return_status);
SELECT max(resource_effective_end_date)
INTO l_date
FROM pa_resources_denorm
WHERE resource_id = l_resource_id ;
l_job_schedulable := PA_HR_UPDATE_API.check_job_schedulable
( p_person_id => p_resource_source_id
,p_date => l_date );
elsif x_assignment_action = 'DELETE' then
-- call delete assignments api
--MT Only call the api if assignment_id is passed in
IF p_assignment_id > 0 THEN
pa_project_parties_pvt.l_delete_proj_party := 'N';
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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_project_parties_pvt.l_delete_proj_party := 'Y';
PA_SCHEDULE_PUB.update_schedule (
p_project_id => p_project_id
-- ,p_mass_update_flag => FND_API.G_FLASE
,p_exception_type_code => 'CHANGE_DURATION'
,p_record_version_number => l_staff_assn_exists_rec.record_version_number
,p_assignment_id => l_staff_assn_exists_rec.assignment_id
,p_change_start_date => p_start_date_active
,p_change_end_date => p_end_date_active
,p_assignment_status_code => l_staff_assn_exists_rec.status_code
,p_non_working_day_flag => 'N'
,p_called_by_proj_party => 'Y'
,p_commit => p_commit
,p_validate_only => FND_API.G_FALSE--'F'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
pa_debug.debug('Update_project_party: Calling update_row.');
pa_security_pvt.update_role(p_grant_guid => l_grant_id,
p_project_role_id_old => p_project_role_id,
p_object_name_old => p_object_type,
p_object_key_type_old => 'INSTANCE',
p_object_key_old => p_object_id,
p_party_id_old => p_resource_source_id,
p_source_type_old => l_source_type,
p_start_date_old => to_date(null),
p_start_date_new => p_start_date_active,
p_end_date_new => p_end_date_active,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage := 'Calling Update_row';
PA_PROJECT_PARTIES_PKG.UPDATE_ROW (
X_PROJECT_PARTY_ID => p_project_party_id,
X_PROJECT_ID => p_project_id,
X_RESOURCE_SOURCE_ID => p_resource_source_id,
X_RESOURCE_TYPE_ID => p_resource_type_id,
X_PROJECT_ROLE_ID => p_project_role_id,
X_START_DATE_ACTIVE => trunc(p_start_date_active),
X_END_DATE_ACTIVE => trunc(p_end_date_active),
X_SCHEDULED_FLAG => NVL(p_scheduled_flag, 'N'),
X_GRANT_ID => l_grant_id,
X_record_version_number => p_record_version_number,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_RETURN_STATUS => x_return_status);
pa_debug.debug('Update_project_party: Commiting data.');
fnd_message.set_token('PROCEDURE_NAME','UPDATE_PROJECT_PARTY');
end update_project_party;
PROCEDURE DELETE_PROJECT_PARTY( 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 := 'N',
/* code commented for the bug#1851096, starts here */
/* p_assignment_id IN NUMBER := FND_API.G_MISS_NUM,
p_assign_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
*/
/* code commented for the bug#1851096, end here */
/* code added for the bug#1851096, starts here */
p_assignment_id IN NUMBER := 0,
p_assign_record_version_number IN NUMBER := 0,
/* code added for the bug#1851096, end here */
p_mgr_validation_type IN VARCHAR2 := FND_API.G_MISS_CHAR,/*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;
SELECT pp.project_party_id project_party_id,
pp.record_version_number record_version_number
FROM pa_project_parties po,
pa_project_parties pp,
hz_party_relationships hzr
WHERE po.resource_type_id = 112
AND po.project_party_id = p_project_party_id
AND pp.resource_type_id = 112
AND pp.object_type = po.object_type
AND pp.object_id = po.object_id
AND hzr.party_relationship_type IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
AND hzr.subject_id = pp.resource_source_id
AND hzr.object_id = po.resource_source_id;
SELECT pp.project_party_id project_party_id,
pp.record_version_number record_version_number
FROM pa_project_parties po,
pa_project_parties pp,
hz_relationships hzr
WHERE po.resource_type_id = 112
AND po.project_party_id = p_project_party_id
AND pp.resource_type_id = 112
AND pp.object_type = po.object_type
AND pp.object_id = po.object_id
AND hzr.relationship_code IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
AND hzr.subject_id = pp.resource_source_id
AND hzr.object_id = po.resource_source_id
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.subject_type = 'PERSON'
AND hzr.subject_table_name = 'HZ_PARTIES';
SELECT customer_id, record_version_number
FROM pa_project_customers
WHERE project_id = p_project_id
AND project_party_id = p_project_party_id;
pa_debug.debug('Delete_project_party : Lock Key Members ');
pa_debug.debug('Delete_project_party : Before delete from pa_project_players ');
SELECT project_id
INTO l_project_id
FROM pa_project_parties
WHERE project_party_id = p_project_party_id;
if (pa_project_parties_pvt.l_delete_proj_party = 'Y') then
if pa_project_parties_utils.validate_delete_party_ok(l_project_id,p_project_party_id) = 'Y' then
--Deleting all external people before deleting the org.
FOR rec IN c_ext_people LOOP
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 => rec.record_version_number,
p_calling_module => p_calling_module,
p_project_id => l_project_id,
p_project_party_id => rec.project_party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_customers_contacts_pub.delete_project_customer(
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_debug_mode => p_debug_mode,
p_project_id => l_project_id,
p_customer_id => rec.customer_id,
p_record_version_number => rec.record_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Calling delete_row';
pa_project_parties_pkg.delete_row(x_project_id => l_project_id,
x_project_party_id => p_project_party_id,
x_record_version_number => p_record_version_number);
pa_debug.debug('Delete_project_party: Commiting 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 TIMEOUT_ON_RESOURCE ');
pa_debug.debug('Delete_project_party : Exception OTHERS ');
END DELETE_PROJECT_PARTY;