The following lines contain the word 'select', 'insert', 'update' or 'delete':
select project_id
from pa_tasks
where task_id = l_task_id;
select start_date, completion_date
from pa_tasks
where task_id = l_task_id;
select start_date, completion_date
from pa_projects_all
where project_id = l_project_id;
select cc_prvdr_flag
from pa_project_types_all t, pa_projects_all p
--Added the org_id join for bug 5561054
where t.org_id = p.org_id
and t.project_type = p.project_type and
p.project_id = l_project_id;
SELECT ppev.element_version_id src_task_ver_id,ppe.project_id src_proj_id
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_tasks pt
WHERE ppe.proj_element_id = cp_src_task_id
AND ppe.project_id = ppev.project_id
AND ppe.proj_element_id = ppev.proj_element_id
AND pt.task_id = ppe.proj_element_id
-- Added for bug 4999937
AND ppev.parent_structure_version_id =
PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(ppev.project_id);
/* Selected task does not belong to a valid project */
PA_UTILS.ADD_Message('PA','PA_STRUCT_CREATE_ERR');
/*pa_object_relationships_pkg.insert_row(
p_user_id => l_user_id,
p_object_type_from => 'PA_TASKS',
p_object_id_from1 => p_task_id,
p_object_id_from2 => l_owning_proj_id,
p_object_id_from3 => null,
p_object_id_from4 => null,
p_object_id_from5 => null,
p_object_type_to => 'PA_PROJECTS',
p_object_id_to1 => p_project_id,
p_object_id_to2 => null,
p_object_id_to3 => null,
p_object_id_to4 => null,
p_object_id_to5 => null,
p_relationship_type => 'H',
p_relationship_subtype => null,
p_lag_day => null,
p_imported_lag => null,
p_priority => null,
p_pm_product_code => null,
X_OBJECT_RELATIONSHIP_ID => l_object_relationships_id,
X_RETURN_STATUS => x_return_status
);*/ --commented the call to implement new functionality
procedure DELETE_RELATIONSHIP
(
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_debug_mode IN VARCHAR2 := 'N',
p_task_id IN NUMBER,
p_project_id 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 --File.Sql.39 bug 4440895
)
IS
l_project_id NUMBER;
select
object_id_to1, object_relationship_id
from
pa_object_relationships
where
object_id_from1 = l_task_id and
OBJECT_TYPE_FROM = 'PA_TASKS' and
relationship_type = 'H';
select
object_id_from1, object_relationship_id
from
pa_object_relationships
where
(object_id_to1 = l_project_id and
relationship_type = 'H')
or
(object_id_from2 = l_project_id and
relationship_type = 'H');
CURSOR get_selected_relationship(l_task_id NUMBER, l_project_id NUMBER) IS
select
object_relationship_id
from
pa_object_relationships
where
object_type_to = 'PA_PROJECTS' and
object_type_from = 'PA_TASKS' and
object_id_to1 = l_project_id and
object_id_from1 = l_task_id and
relationship_type = 'H';
SELECT ppev.element_version_id src_task_ver_id,ppe.project_id src_proj_id
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_tasks pt
WHERE ppe.proj_element_id = cp_src_task_id
AND ppe.project_id = ppev.project_id
AND ppe.proj_element_id = ppev.proj_element_id
AND pt.task_id = ppe.proj_element_id
AND ppev.parent_structure_version_id =
PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(ppev.project_id); -- Added for Bug 5687144
SELECT porb.object_relationship_id lnk_obj_rel_id,
porb.record_version_number lnk_rel_rec_ver_number
FROM pa_proj_element_versions ppev,
pa_object_relationships pora,
pa_object_relationships porb,
pa_proj_elements ppe
WHERE pora.relationship_type = 'S'
AND ppev.project_id = cp_src_proj_id
AND pora.OBJECT_ID_FROM1 = cp_src_task_ver_id
AND pora.object_type_from = 'PA_TASKS'
AND pora.OBJECT_ID_to1 = ppev.ELEMENT_VERSION_ID
AND ppe.proj_element_id = ppev.proj_element_id
AND pora.object_id_to1=porb.object_id_from1
AND porb.object_id_to2 <> porb.object_id_from2
AND porb.object_id_to2 = cp_dest_proj_id -- Added for Bug 5687144
AND porb.object_type_to = 'PA_STRUCTURES'
AND porb.relationship_type = 'LF'
AND ppe.link_task_flag = 'Y';
SELECT porb.object_relationship_id lnk_obj_rel_id,
porb.record_version_number lnk_rel_rec_ver_number
FROM pa_proj_element_versions ppev,
pa_object_relationships pora,
pa_object_relationships porb,
pa_proj_elements ppe
WHERE pora.relationship_type = 'S'
AND ppev.project_id = cp_src_proj_id
AND pora.OBJECT_ID_FROM1 = cp_src_task_ver_id
AND pora.object_type_from = 'PA_TASKS'
AND pora.OBJECT_ID_to1 = ppev.ELEMENT_VERSION_ID
AND ppe.proj_element_id = ppev.proj_element_id
AND pora.object_id_to1=porb.object_id_from1
AND porb.object_id_to2 <> porb.object_id_from2
AND porb.object_id_from2 = cp_src_proj_id
AND porb.object_type_to = 'PA_STRUCTURES'
AND porb.object_type_from = 'PA_TASKS'
AND porb.relationship_type ='LF'
AND ppe.link_task_flag = 'Y';
SELECT porb.object_relationship_id lnk_obj_rel_id,
porb.record_version_number lnk_rel_rec_ver_number
FROM pa_object_relationships porb
WHERE porb.OBJECT_ID_to1 = cp_dest_struct_ver_id
AND porb.object_id_from2 = cp_dest_proj_id
AND porb.object_id_to2 <> porb.object_id_from2
AND porb.object_type_to = 'PA_STRUCTURES'
AND porb.object_type_from = 'PA_TASKS'
AND porb.relationship_type ='LF';
pa_debug.set_err_stack('DELETE_RELATIONSHIP');
/* select project_id
into l_project_from_id
from pa_tasks
where task_id = p_task_id;*/
/*OPEN get_selected_relationship(p_task_id, p_project_id);
FETCH get_selected_relationship INTO l_object_relationship_id;
EXIT WHEN get_selected_relationship%NOTFOUND;
pa_object_relationships_pkg.delete_row(
P_OBJECT_RELATIONSHIP_ID => l_object_relationship_id,
P_OBJECT_TYPE_FROM => null,
P_OBJECT_ID_FROM1 => p_task_id,
P_OBJECT_ID_FROM2 => null,
P_OBJECT_ID_FROM3 => null,
P_OBJECT_ID_FROM4 => null,
P_OBJECT_ID_FROM5 => null,
P_OBJECT_TYPE_TO => null,
P_OBJECT_ID_TO1 => p_project_id,
P_OBJECT_ID_TO2 => null,
P_OBJECT_ID_TO3 => null,
P_OBJECT_ID_TO4 => null,
P_OBJECT_ID_TO5 => null,
P_RECORD_VERSION_NUMBER => null,
P_PM_PRODUCT_CODE => null,
X_RETURN_STATUS => x_return_status
);
PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
p_commit => p_commit
,p_validate_only => p_validate_only
,p_debug_mode => p_debug_mode
,p_object_relationships_id => l_rel_id
,p_record_version_number => l_rec_ver_num
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
/* Can't delete relationship due to other contracts*/
x_return_status := FND_API.G_RET_STS_ERROR;
/* If task_id is not null, then delete all relationships that the task is associated with */
-- Get task owning project
select project_id
into l_project_from_id
from pa_tasks
where task_id = p_task_id;
/*pa_object_relationships_pkg.delete_row(
P_OBJECT_RELATIONSHIP_ID => l_object_relationship_id,
P_OBJECT_TYPE_FROM => null,
P_OBJECT_ID_FROM1 => p_task_id,
P_OBJECT_ID_FROM2 => null,
P_OBJECT_ID_FROM3 => null,
P_OBJECT_ID_FROM4 => null,
P_OBJECT_ID_FROM5 => null,
P_OBJECT_TYPE_TO => null,
P_OBJECT_ID_TO1 => l_project_id,
P_OBJECT_ID_TO2 => null,
P_OBJECT_ID_TO3 => null,
P_OBJECT_ID_TO4 => null,
P_OBJECT_ID_TO5 => null,
P_RECORD_VERSION_NUMBER => null,
P_PM_PRODUCT_CODE => null,
X_RETURN_STATUS => x_return_status
);*/ --Commented the code to implement new functionality for subproject association
PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
p_commit => p_commit
,p_validate_only => p_validate_only
,p_debug_mode => p_debug_mode
,p_object_relationships_id => l_rel_id
,p_record_version_number => l_rec_ver_num
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
/* If project_id is not null, then delete all relationships that the project is associated with */
-- OPEN get_all_tasks_for_project(p_project_id);
/* do not need to check subproject relationship because delete task */
/* will check them. do not need to check current project relationship */
/* with other tasks because we check if contract is associated to it */
/* call table handler api */
BEGIN
/*pa_object_relationships_pkg.delete_row(
P_OBJECT_RELATIONSHIP_ID => l_object_relationship_id,
P_OBJECT_TYPE_FROM => null,
P_OBJECT_ID_FROM1 => l_task_id,
P_OBJECT_ID_FROM2 => null,
P_OBJECT_ID_FROM3 => null,
P_OBJECT_ID_FROM4 => null,
P_OBJECT_ID_FROM5 => null,
P_OBJECT_TYPE_TO => null,
P_OBJECT_ID_TO1 => p_project_id,
P_OBJECT_ID_TO2 => null,
P_OBJECT_ID_TO3 => null,
P_OBJECT_ID_TO4 => null,
P_OBJECT_ID_TO5 => null,
P_RECORD_VERSION_NUMBER => null,
P_PM_PRODUCT_CODE => null,
X_RETURN_STATUS => x_return_status
);*/ --Commented the code to implement new functionality for subproject association
PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
p_commit => p_commit
,p_validate_only => p_validate_only
,p_debug_mode => p_debug_mode
,p_object_relationships_id => l_rel_id
,p_record_version_number => l_rec_ver_num
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
END DELETE_RELATIONSHIP;