The following lines contain the word 'select', 'insert', 'update' or 'delete':
select '1'
from pa_proj_structure_types s,
pa_structure_types t
where s.proj_element_id = c_struc_id
and s.structure_type_id = t.structure_type_id
and t.structure_type_class_code = c_struc_type;
select proj_element_id, object_type
from pa_proj_element_versions
where element_version_id = c_elem_ver_id;
select parent_structure_Version_id
from pa_proj_element_versions
where element_version_id = c_elem_ver_id;
select pev.proj_element_id
from pa_proj_element_versions pev,
pa_proj_element_versions pev2,
pa_object_relationships rel
where pev2.project_id = c_project_id
and pev2.object_type = 'PA_STRUCTURES'
and pev2.proj_element_id = c_structure_id
and pev2.element_version_id = rel.object_id_from1
and rel.relationship_type = 'S'
and rel.object_id_to1 = pev.element_version_id;
select pevs.element_version_id
from pa_proj_structure_types s,
pa_structure_types t,
pa_proj_elements pe,
pa_proj_elem_ver_structure pevs
where pe.object_type = 'PA_STRUCTURES'
and pe.project_id = c_project_id
and pe.proj_element_id = s.proj_element_id
and s.structure_type_id = t.structure_type_id
and t.structure_type_class_code = c_struc_type
and c_project_id = pevs.project_id
and pe.proj_element_id = pevs.proj_element_id
and pevs.latest_eff_published_flag = 'Y';
select a.scheduled_start_date, a.scheduled_finish_date
from pa_proj_elem_ver_schedule a, pa_proj_element_versions b
where b.element_version_id = c_element_version_id
and a.project_id = b.project_id
and a.element_version_id = b.element_version_id;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => l_type_from
,p_object_id_from1 => l_id_from
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => l_type_to
,p_object_id_to1 => l_id_to
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => p_relationship_type
,p_relationship_subtype => p_relationship_subtype
,p_lag_day => l_lag_day --Bug 2955589. Use miss num handled local var instead of p_lag_day.
,p_imported_lag => NULL
,p_priority => l_priority --Bug 2955589. Use miss char handled local var instead of p_priority.
,p_pm_product_code => NULL
,x_object_relationship_id => x_object_relationship_id
,x_return_status => x_return_status
--FPM changes bug 3301192
,p_comments => null
,p_status_code => null
--end FPM changes bug 3301192
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => l_type_from
,p_object_id_from1 => l_id_from
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => l_type_to
,p_object_id_to1 => l_id_to
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => p_relationship_type
,p_relationship_subtype => p_relationship_subtype
,p_lag_day => l_lag_day --Bug 2955589. Use miss num handled local var instead of p_lag_day
,p_imported_lag => NULL
,p_priority => l_priority --Bug 2955589. Use miss char handled local var instead of p_priority
,p_pm_product_code => NULL
,p_weighting_percentage => l_weighting_percentage
,x_object_relationship_id => x_object_relationship_id
,x_return_status => x_return_status
--FPM changes bug 3301192
,p_comments => null
,p_status_code => null
--end FPM changes bug 3301192
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
, p_object_type_from => l_type_from
, p_object_id_from1 => l_id_from
, p_object_id_from2 => NULL
, p_object_id_from3 => NULL
, p_object_id_from4 => NULL
, p_object_id_from5 => NULL
, p_object_type_to => l_type_to
, p_object_id_to1 => l_id_to
, p_object_id_to2 => NULL
, p_object_id_to3 => NULL
, p_object_id_to4 => NULL
, p_object_id_to5 => NULL
, p_relationship_type=> p_relationship_type
, p_relationship_subtype =>NULL
, p_lag_day => l_lag_day --Bug 2955589. Use miss num handled local var instead of p_lag_day
, p_imported_lag => NULL
, p_priority => l_priority --Bug 2955589. Use miss char handled local var instead of p_priority
, p_pm_product_code => NULL
, p_weighting_percentage => NULL
, x_object_relationship_id => x_object_relationship_id
, x_return_status => x_return_status
, p_comments => null
, p_status_code => null
);
procedure Update_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_validation_level IN VARCHAR2 := 100
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_object_relationship_id IN NUMBER
,p_project_id_from IN NUMBER
,p_structure_id_from IN NUMBER
,p_structure_version_id_from IN NUMBER
,p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_id_to IN NUMBER
,p_structure_id_to IN NUMBER
,p_structure_version_id_to IN NUMBER
,p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_relationship_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_weighting_percentage IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_record_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 --File.Sql.39 bug 4440895
)
IS
l_id_from pa_object_relationships.object_id_from1%TYPE;
pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_RELATIONSHIP begin');
savepoint update_relationship_pvt;
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
p_object_relationship_id => p_object_relationship_id
,p_object_type_from => NULL
,p_object_id_from1 => NULL
,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 => NULL
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_record_version_number => p_record_version_number
,p_pm_product_code => NULL
,x_return_status => x_return_status
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => l_type_from
,p_object_id_from1 => l_id_from
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => l_type_to
,p_object_id_to1 => l_id_to
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => p_relationship_type
,p_relationship_subtype => p_relationship_subtype
,p_lag_day => p_lag_day
,p_imported_lag => NULL
,p_priority => p_priority
,p_pm_product_code => NULL
,p_weighting_percentage => l_weighting_percentage
,x_object_relationship_id => l_or_id
,x_return_status => x_return_status
--FPM changes bug 3301192
,p_comments => null
,p_status_code => null
--end FPM changes bug 3301192
);
ROLLBACK to update_relationship_pvt;
ROLLBACK to update_relationship_pvt;
p_procedure_name => 'Update_relationship',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_RELATIONSHIP;
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_validation_level IN VARCHAR2 := 100
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_object_relationship_id IN NUMBER
,p_record_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 --File.Sql.39 bug 4440895
)
IS
CURSOR get_link_task_ver_id IS
select object_id_from1, relationship_type
from pa_object_relationships
where object_relationship_id = p_object_relationship_id;
pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_RELATIONSHIP begin');
savepoint delete_relationship_pvt;
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
p_object_relationship_id => p_object_relationship_id
,p_object_type_from => NULL
,p_object_id_from1 => NULL
,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 => NULL
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_record_version_number => p_record_version_number
,p_pm_product_code => NULL
,x_return_status => x_return_status
);
select record_version_number
into l_task_version_rvn
from pa_proj_element_versions
where element_version_id = l_link_task_ver.object_id_from1;
PA_TASK_PUB1.DELETE_TASK_VERSION(p_commit => 'N',
p_debug_mode => p_debug_mode,
p_task_version_id => l_link_task_ver.object_id_from1,
p_record_version_number => l_task_version_rvn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_RELATIONSHIP end');
ROLLBACK to delete_relationship_pvt;
ROLLBACK to delete_relationship_pvt;
p_procedure_name => 'Delete_relationship',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_RELATIONSHIP;
SELECT status_code
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_proj_id
AND ppev.element_version_id = p_src_task_ver_id
AND ppev.parent_structure_version_id = ppevs.element_version_id
AND ppevs.project_id = ppev.project_id
;
SELECT pev2.element_version_id, 'STRUCTURE_WORKING' status_code
FROM pa_proj_element_versions pev,
pa_proj_elem_ver_structure str,
pa_proj_element_versions pev2
WHERE pev.proj_element_id = pev2.proj_element_id
AND pev.project_id = pev2.project_id
AND pev2.parent_structure_version_id = str.element_version_id
AND pev2.project_id = str.project_id
AND str.current_working_flag = 'Y'
AND str.status_code <> 'STRUCTURE_PUBLISHED'
AND pev.element_version_id = p_dest_task_ver_id
UNION ALL
SELECT pev2.element_version_id, 'STRUCTURE_PUBLISHED' status_code
FROM pa_proj_element_versions pev,
pa_proj_elem_ver_structure str,
pa_proj_element_versions pev2
WHERE pev.proj_element_id = pev2.proj_element_id
AND pev.project_id = pev2.project_id
AND pev2.parent_structure_version_id = str.element_version_id
AND pev2.project_id = str.project_id
AND str.status_code = 'STRUCTURE_PUBLISHED'
AND str.latest_eff_published_flag = 'Y'
AND pev.element_version_id = p_dest_task_ver_id
;
SELECT count(1)
FROM dual
WHERE EXISTS ( SELECT 'x'
FROM pa_object_relationships por
WHERE por.object_id_from1 = c_task_ver_id
AND por.object_type_from = 'PA_TASKS'
AND por.relationship_type = 'S');
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => p_src_task_ver_id
,p_object_id_from2 => p_src_proj_id
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_TASKS'
,p_object_id_to1 => p_dest_task_ver_id
,p_object_id_to2 => p_dest_proj_id
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'D'
,p_relationship_subtype => p_type
,p_lag_day => l_lag_days
,p_imported_lag => NULL
,p_priority => null
,p_pm_product_code => NULL
,x_object_relationship_id => l_object_relationship_id
,x_return_status => l_return_status
,p_comments => p_comments
,p_status_code => null /* not applicable for intra dependency */
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => p_src_task_ver_id
,p_object_id_from2 => p_src_proj_id
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_TASKS'
,p_object_id_to1 => get_dest_task_ver_id_rec.element_version_id
,p_object_id_to2 => p_dest_proj_id
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'D'
,p_relationship_subtype => p_type
,p_lag_day => l_lag_days
,p_imported_lag => NULL
,p_priority => null
,p_pm_product_code => NULL
,x_object_relationship_id => l_object_relationship_id
,x_return_status => l_return_status
,p_comments => p_comments
,p_status_code => l_status_code
);
procedure Update_dependency
(
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 VARCHAR2 := 100
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_task_version_id IN NUMBER := NULL
,p_src_task_version_id IN NUMBER := NULL
,p_type IN VARCHAR2 := NULL
,p_lag_days IN NUMBER := NULL
,p_comments IN VARCHAR2 := NULL
,p_record_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 --File.Sql.39 bug 4440895
)
IS
l_api_name CONSTANT VARCHAR(30) := 'UPDATE_DEPENDENCY';
SELECT *
FROM pa_object_relationships
WHERE object_id_to1 = p_task_version_id
AND object_id_from1 = p_src_task_version_id
AND relationship_type = 'D';
pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_DEPENDENCY begin');
savepoint update_dependency_pvt;
PA_OBJECT_RELATIONSHIPS_PKG.UPDATE_ROW
( p_user_id => FND_GLOBAL.USER_ID
,p_object_relationship_id => l_obj_rel_rec.object_relationship_id
,p_relationship_type => l_obj_rel_rec.relationship_type
,p_relationship_subtype => l_rel_subtype
,p_lag_day => l_lag_days
,p_priority => l_obj_rel_rec.priority
,p_pm_product_code => l_obj_rel_rec.pm_product_code
,p_weighting_percentage => l_obj_rel_rec.weighting_percentage
,p_comments => l_comments
,p_status_code => l_obj_rel_rec.status_code
,p_record_version_number => p_record_version_number
,x_return_status => l_return_status
);
pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_DEPENDENCY END');
rollback to update_dependency_pvt;
rollback to update_dependency_pvt;
p_procedure_name => 'UPDATE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_dependency_pvt;
p_procedure_name => 'UPDATE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Dependency;
procedure Delete_Dependency
(
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 VARCHAR2 := 100
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_object_relationship_id IN NUMBER := NULL
,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_api_name CONSTANT VARCHAR(30) := 'DELETE_DEPENDENCY';
SELECT por2.object_relationship_id, por2.record_version_number
FROM pa_object_relationships por1,
pa_object_relationships por2
WHERE por1.object_relationship_id = p_object_relationship_id
AND por1.relationship_type = 'D'
AND por1.object_id_from1 = por2.object_id_from1
AND por2.object_id_to1 IN (
select ppev1.element_version_id
from pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
where ppev2.element_version_id = por1.object_id_to1
and ppev2.project_id = ppev1.project_id
and ppev2.proj_element_Id = ppev1.proj_element_id);
SELECT parent_structure_version_id,project_id
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE por.object_relationship_id = cp_object_relationship_id
AND ppev.element_version_id = por.object_id_from1;
pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_DEPENDENCY begin');
savepoint delete_dependency_pvt;
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW (
p_object_relationship_id => cur_obj_rel_rec.object_relationship_id
,p_object_type_from => null
,p_object_id_from1 => null
,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 => null
,p_object_id_to2 => null
,p_object_id_to3 => null
,p_object_id_to4 => null
,p_object_id_to5 => null
,p_record_version_number => cur_obj_rel_rec.record_version_number
,p_pm_product_code => null
,x_return_status => l_return_status );
pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_DEPENDENCY END');
rollback to delete_dependency_pvt;
rollback to delete_dependency_pvt;
p_procedure_name => 'DELETE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_dependency_pvt;
p_procedure_name => 'DELETE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Dependency;
select --a.element_version_id src_task_ver_id,
b.element_version_id dest_task_ver_id,
--c.element_version_id src_pred_ver_id,
d.element_version_id dest_pred_ver_id,
a.project_id,
r.relationship_subtype,
r.lag_day,
r.comments
from pa_proj_element_versions a,
pa_proj_element_versions b,
pa_proj_element_versions c,
pa_proj_element_versions d,
pa_object_relationships r
where a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and a.parent_structure_version_id = P_source_struc_ver_id
and b.parent_structure_version_id = p_dest_struc_ver_id
and r.relationship_type = 'D'
and r.object_id_from1 = a.element_version_id
and r.object_id_to1 = c.element_version_id
and r.object_id_from2 = r.object_id_to2
and c.project_id = a.project_id
and c.parent_structure_version_id = p_source_struc_ver_id
and d.project_id = b.project_id
and d.proj_element_id = c.proj_element_id
and d.parent_structure_version_id = p_dest_struc_ver_id;
select * from pa_object_relationships
where relationship_type = 'D'
and object_id_from1 = c_suc_ver_id
and object_id_to1 = c_pred_ver_id
and object_id_from2 = object_id_to2
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS';
select parent_structure_version_id, project_id
from pa_proj_element_versions
where element_version_id = c_elem_ver_id;
select 1
from pa_object_relationships
where relationship_type = 'D'
and object_id_from1 = c_elem_ver_id
and object_id_from2 = object_id_to2
and rownum = 1;
select /*+ leading(dt1) use_nl(dt1 rel) */ -- hint added per performance team recommendation for bug 5576900
rel.object_id_from1, dt1.dest_task_ver_id DEST_FROM_ID,
rel.object_id_to1, dt2.dest_task_ver_id DEST_TO_ID,
rel.comments, rel.LAG_DAY, rel.RELATIONSHIP_SUBTYPE
from pa_object_relationships rel,
pa_copy_dep_temp dt1,
pa_copy_dep_temp dt2
where rel.relationship_type = 'D'
and rel.object_id_from1 = dt1.src_task_ver_id
and rel.object_id_to1 = dt2.src_task_ver_id
and rel.object_id_from2 = object_id_to2
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS';
select pcdt.dest_task_ver_id suc_ver_id, rel.object_id_to1 pred_ver_id
, rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
, rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
from pa_object_relationships rel, pa_copy_dep_temp pcdt
where rel.object_id_from1 = pcdt.src_task_ver_id
and rel.relationship_type = 'D'
and rel.object_id_from2 = rel.object_id_to2
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS'
-- This condition prevents the creation of intra-project dependencies between a task and any of
-- its sub-tasks.
and rel.object_id_to1 not in (select por.object_id_from1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_from = 'PA_TASKS'
start with por.object_id_to1 = pcdt.dest_task_ver_id
connect by prior por.object_id_from1 = por.object_id_to1
and prior por.relationship_type = por.relationship_type
union
select por.object_id_to1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_to = 'PA_TASKS'
start with por.object_id_from1 = pcdt.dest_task_ver_id
connect by prior por.object_id_to1 = por.object_id_from1
and prior por.relationship_type = por.relationship_type)
union all
-- select all successor dependencies from other tasks in the project to the source task.
select rel.object_id_from1 suc_ver_id, pcdt.dest_task_ver_id pred_ver_id
, rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
, rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
from pa_object_relationships rel, pa_copy_dep_temp pcdt
where rel.object_id_to1 = pcdt.src_task_ver_id
and rel.relationship_type = 'D'
and rel.object_id_from2 = rel.object_id_to2
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS'
-- This condition prevents the creation of intra-project dependencies between a task and any of \
-- its sub-tasks.
and rel.object_id_from1 not in (select por.object_id_from1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_from = 'PA_TASKS'
start with por.object_id_to1 = pcdt.dest_task_ver_id
connect by prior por.object_id_from1 = por.object_id_to1
and prior por.relationship_type = por.relationship_type
union
select por.object_id_to1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_to = 'PA_TASKS'
start with por.object_id_from1 = pcdt.dest_task_ver_id
connect by prior por.object_id_to1 = por.object_id_from1
and prior por.relationship_type = por.relationship_type);
select pcdt.dest_task_ver_id suc_ver_id, pcdt2.dest_task_ver_id pred_ver_id
, rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
, rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
from pa_object_relationships rel, pa_copy_dep_temp pcdt, pa_copy_dep_temp pcdt2
where rel.object_id_from1 = pcdt.src_task_ver_id
and rel.relationship_type = 'D'
and rel.object_id_to1 = pcdt2.src_task_ver_id
and rel.object_id_from2 = rel.object_id_to2
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS'
-- This condition prevents the creation of intra-project dependencies between a task and any of
-- its sub-tasks.
and pcdt2.dest_task_ver_id not in (select por.object_id_from1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_from = 'PA_TASKS'
start with por.object_id_to1 = pcdt.dest_task_ver_id
connect by prior por.object_id_from1 = por.object_id_to1
and prior por.relationship_type = por.relationship_type
union
select por.object_id_to1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_to = 'PA_TASKS'
start with por.object_id_from1 = pcdt.dest_task_ver_id
connect by prior por.object_id_to1 = por.object_id_from1
and prior por.relationship_type = por.relationship_type)
union
-- select all successor dependencies from other tasks in the project to the source task.
select pcdt2.dest_task_ver_id suc_ver_id, pcdt.dest_task_ver_id pred_ver_id
, rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
, rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
from pa_object_relationships rel, pa_copy_dep_temp pcdt, pa_copy_dep_temp pcdt2
where rel.object_id_to1 = pcdt.src_task_ver_id
and rel.relationship_type = 'D'
and rel.object_id_from1 = pcdt2.src_task_ver_id
and rel.object_id_from2 = rel.object_id_to2
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS'
-- This condition prevents the creation of intra-project dependencies between a task and any of
-- its sub-tasks.
and pcdt2.dest_task_ver_id not in (select por.object_id_from1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_from = 'PA_TASKS'
start with por.object_id_to1 = pcdt.dest_task_ver_id
connect by prior por.object_id_from1 = por.object_id_to1
and prior por.relationship_type = por.relationship_type
union
select por.object_id_to1
from pa_object_relationships por
where por.relationship_type = 'S'
and por.object_type_to = 'PA_TASKS'
start with por.object_id_from1 = pcdt.dest_task_ver_id
connect by prior por.object_id_to1 = por.object_id_from1
and prior por.relationship_type = por.relationship_type);
select count(ppev.element_version_id)
from pa_proj_element_versions ppev
where ppev.parent_structure_version_id = (select ppev2.parent_structure_version_id
from pa_proj_element_versions ppev2
where ppev2.element_version_id = c_task_ver_id)
and ppev.object_type = 'PA_TASKS'
and ppev.element_version_id not in (select pcdt.src_task_ver_id
from pa_copy_dep_temp pcdt);
Delete from PA_COPY_DEP_TEMP; --Bug#8842950
pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'Before BULK insert into PA_COPY_DEP_TEMP table', x_Log_Level=> 3);
INSERT INTO PA_COPY_DEP_TEMP(SRC_TASK_VER_ID, DEST_TASK_VER_ID)
VALUES(p_source_ver_tbl(i), p_destin_ver_tbl(i));
pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'Before BULK insert into PA_OBJECT_RELATIONSHIPS table', x_Log_Level=> 3);
INSERT INTO PA_OBJECT_RELATIONSHIPS(
OBJECT_RELATIONSHIP_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,RELATIONSHIP_TYPE
,OBJECT_TYPE_FROM
,OBJECT_TYPE_TO
,OBJECT_ID_FROM1
,OBJECT_ID_TO1
,OBJECT_ID_FROM2
,OBJECT_ID_TO2
,LAG_DAY
,RELATIONSHIP_SUBTYPE
,COMMENTS
,RECORD_VERSION_NUMBER
)
VALUES (
pa_object_relationships_s.nextval
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,'D'
,'PA_TASKS'
,'PA_TASKS'
,l_suc_ver_id_tbl(i)
,l_pred_ver_id_tbl(i)
,l_suc_proj_id_tbl(i)
,l_pred_proj_id_tbl(i)
,l_lag_days_tbl(i)
,l_subtype_tbl(i)
,l_comment_tbl(i)
,1
);
Select 1, Object_ID_TO1,
Object_ID_From2, Object_ID_From1, Object_ID_To2, Object_ID_To1,
Relationship_SubType, Lag_Day, Comments
INTO l_Found_Flag, l_Object_Task_ID,
l_src_proj_id, l_src_task_ver_id, l_dest_proj_id, l_dest_task_ver_id,
l_Type, l_lag_days, l_comments
From PA_Object_Relationships
Where RELATIONSHIP_TYPE = 'D'
And OBJECT_ID_TO2 <> OBJECT_ID_FROM2
And OBJECT_ID_FROM1 = P_Source_Ver_Tbl(i);
select *
From PA_Object_Relationships
Where RELATIONSHIP_TYPE = 'D'
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS'
And OBJECT_ID_TO2 <> OBJECT_ID_FROM2
and object_id_from1 = c_suc_ver_id;
SELECT project_id
INTO l_src_proj_id
FROM pa_proj_element_versions ppev
WHERE ppev.element_version_id = p_destin_ver_tbl(i);
Select rel.object_relationship_id, rel.Record_Version_Number
From pa_object_relationships rel,
pa_proj_element_versions ppev1
Where rel.relationship_type = 'D'
and rel.object_id_from1 = ppev1.element_version_id
and rel.object_id_to2 <> rel.object_id_from2
and ppev1.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID;
Select rel.object_relationship_id, rel.Record_Version_Number
From pa_object_relationships rel,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
Where rel.relationship_type = 'D'
and rel.object_id_from1 = ppev1.element_version_id
and rel.object_id_to1 = ppev2.element_version_id
and rel.object_id_to2 <> rel.object_id_from2
and ppev1.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID
--
and Not Exists (
Select 1
From pa_object_relationships rel2,
pa_proj_element_versions ppev3,
pa_proj_element_versions ppev4
where rel2.relationship_type = 'D'
and rel2.object_id_to2 <> rel2.object_id_from2
and rel.object_id_from1 = ppev3.element_version_id
and rel.object_id_to1 = ppev4.element_version_id
and rel.object_id_from1 = ppev1.element_version_id
and rel.object_id_to1 = ppev2.element_version_id
and ppev3.parent_structure_version_id = P_Publishing_Struc_Ver_ID);
select ppev2.element_version_id, ppev2.project_id,
rel1.object_id_to1, rel1.object_id_to2, rel1.lag_day, rel1.comments,
rel1.relationship_subtype
from pa_object_relationships rel1,
pa_proj_element_versions ppev,
pa_proj_element_versions ppev2
where rel1.relationship_type = 'D'
and rel1.object_id_to2 <> rel1.object_id_from2
and rel1.object_id_from1 = ppev.element_version_id
and ppev.project_id = ppev2.project_id
and ppev.proj_element_id = ppev2.proj_element_id
and ppev.parent_structure_version_id = c_Version_ID
--
and ppev2.parent_structure_version_id = P_Published_Struc_Ver_ID;
CURSOR Update_Publ_Ver IS
select distinct rel1.object_id_from1, rel1.object_id_from2 -- Fix for Bug # 4349093.
, ppev2.element_version_id, ppev2.project_id
,MAX(rel1.lag_day), MAX(rel1.comments), MAX(rel1.relationship_subtype)
-- , rel1.object_relationship_id -- Fix for Bug # 4349093.
-- , rel1.record_version_number
from pa_object_relationships rel1,
pa_proj_element_versions ppev,
pa_proj_element_versions ppev2
where rel1.relationship_type = 'D'
and rel1.object_id_to2 <> rel1.object_id_from2
and rel1.object_id_to1 = ppev.element_version_id
and ppev.project_id = ppev2.project_id
and ppev.proj_element_id = ppev2.proj_element_id
and ppev.parent_structure_version_id IN (P_Publishing_Struc_Ver_ID, P_Previous_Pub_Struc_Ver_ID)
and ppev2.parent_structure_version_id = P_Published_Struc_Ver_ID
group by
rel1.object_id_from1, rel1.object_id_from2,
ppev2.element_version_id, ppev2.project_id;
Select rel1.object_id_from1, rel1.object_id_from2,
rel1.object_id_to1, rel1.object_id_to2,
ppev2.element_version_id,
ppev2.project_id, rel1.lag_day, rel1.comments, rel1.relationship_subtype
from pa_object_relationships rel1,
pa_proj_element_versions ppev,
pa_proj_element_versions ppev2
where rel1.relationship_type = 'D'
and rel1.object_id_to2 <> rel1.object_id_from2
and rel1.object_id_to1 = ppev.element_version_id
and ppev.project_id = ppev2.project_id
and ppev.proj_element_id = ppev2.proj_element_id
and ppev.parent_structure_version_id = c_version_ID
--
and ppev2.parent_structure_version_id = P_Published_Struc_Ver_ID;
CURSOR Delete_Publ_Ver IS
select rel.object_relationship_id, rel.Record_Version_Number
from pa_object_relationships rel,
pa_proj_element_versions ppev
where rel.relationship_type = 'D'
and rel.object_id_from1 = ppev.element_version_id
and ppev.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID
--
and rel.object_id_from2 <> rel.object_id_to2
UNION
select rel.object_relationship_id, rel.Record_Version_Number
from pa_object_relationships rel,
pa_proj_element_versions ppev
where rel.relationship_type = 'D'
and rel.object_id_to1 = ppev.element_version_id
and ppev.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID
--
and rel.object_id_from2 <> rel.object_id_to2 ;
PA_RELATIONSHIP_PVT.Delete_Relationship (
p_object_relationship_id => l_Relationship_ID
,p_record_version_number => l_Record_Ver_Number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_Object_Relationships_PKG.Insert_Row(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => l_src_task_ver_id
,p_object_id_from2 => l_src_proj_id
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_TASKS'
,p_object_id_to1 => l_dest_task_ver_id
,p_object_id_to2 => l_dest_proj_id
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'D'
,p_relationship_subtype => l_Sub_Type
,p_lag_day => l_Lag_Days
,p_imported_lag => NULL
,p_priority => Null
,p_pm_product_code => NULL
,x_object_relationship_id => l_object_relationship_id
,p_comments => l_comments
,p_status_code => 'PUBLISHED'
,x_return_status => x_return_status
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
);
Open Update_Publ_Ver;
Fetch Update_Publ_Ver
Into l_Obj_ID_From1, l_Obj_ID_From2, l_Obj_ID_To1, l_Obj_ID_To2,
l_Lag_Days, l_Comments, l_Sub_Type;
EXIT WHEN Update_Publ_Ver%NOTFOUND;
PA_Object_Relationships_PKG.Insert_Row(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => l_obj_id_from1
,p_object_id_from2 => l_obj_id_from2
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_TASKS'
,p_object_id_to1 => l_obj_id_to1
,p_object_id_to2 => l_obj_id_to2
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'D'
,p_relationship_subtype => l_Sub_Type
,p_lag_day => l_Lag_Days
,p_imported_lag => NULL
,p_priority => Null
,p_pm_product_code => NULL
,x_object_relationship_id => l_object_relationship_id
,p_comments => l_comments
,p_status_code => 'PUBLISHED'
,x_return_status => x_return_status
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
);
Close Update_Publ_Ver;
PA_RELATIONSHIP_PVT.Delete_Relationship (
p_object_relationship_id => l_del_obj_rel_id
,p_record_version_number => l_Record_Ver_Number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Close Update_Publ_Ver;
Close Update_Publ_Ver;
Open Delete_Publ_Ver;
Fetch Delete_Publ_Ver
INTO l_Relationship_ID, l_Record_Ver_Number;
Exit When Delete_Publ_Ver%NOTFOUND;
PA_RELATIONSHIP_PVT.Delete_Relationship (
p_object_relationship_id => l_Relationship_ID
,p_record_version_number => l_Record_Ver_Number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Close Delete_Publ_Ver;
PROCEDURE Insert_Subproject_Association( 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 VARCHAR2 := 100
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_src_proj_id IN NUMBER
,p_src_struc_wp_or_fin IN VARCHAR2
,p_src_struc_elem_id IN NUMBER
,p_src_struc_elem_ver_id IN NUMBER
,p_dest_proj_id IN NUMBER
,p_dest_struc_elem_id IN NUMBER
,p_dest_struc_elem_ver_id IN NUMBER
,p_src_task_elem_id IN NUMBER
,p_src_task_elem_ver_id IN NUMBER
,p_lnk_task_name_number IN VARCHAR2 --SMukka
,p_relationship_type IN VARCHAR2
,p_comment IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR --Bug No 3668113
,x_lnk_task_elem_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_lnk_task_elem_ver_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_object_relationship_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_pev_schedule_id 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 --File.Sql.39 bug 4440895
)
IS
l_msg_index_out NUMBER;
SELECT 'x'
FROM pa_proj_elem_ver_structure
WHERE project_id=p_dest_proj_id
AND element_version_id = p_dest_struc_elem_ver_id
AND status_code = 'STRUCTURE_PUBLISHED'
;
select ppa.name
from pa_projects_all ppa
where ppa.project_id = c_project_id;
pa_debug.debug('PA_RELATIONSHIP_PVT.Insert_Subproject_Association begin');
savepoint Insert_Subproject_Association;
UPDATE PA_PROJ_ELEMENTS
SET ELEMENT_NUMBER = substr(p_lnk_task_name_number,0,25)||x_lnk_task_elem_id
WHERE PROJ_ELEMENT_ID = x_lnk_task_elem_id;
UPDATE PA_TASKS
SET
CHARGEABLE_FLAG = 'N',
RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER,0)+1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID,
last_update_date = sysdate
WHERE TASK_ID = p_src_task_elem_id;
UPDATE PA_TASKS
SET
CHARGEABLE_FLAG = 'N',
RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER,0)+1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID,
last_update_date = sysdate
WHERE TASK_ID = p_src_task_elem_id;
UPDATE PA_TASKS
SET
CHARGEABLE_FLAG = 'N',
RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER,0)+1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID,
last_update_date = sysdate
WHERE TASK_ID = p_src_task_elem_id;
pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Linking Task Elem Ver Id => '||x_lnk_task_elem_ver_id);
pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Src Proj Id=> '||p_src_proj_id);
pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Dest Struc Elem Ver Id=> '||p_dest_struc_elem_ver_id);
pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Dest Proj Id=> '||p_dest_proj_id);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => x_lnk_task_elem_ver_id
,p_object_id_from2 => p_src_proj_id
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_STRUCTURES'
,p_object_id_to1 => p_dest_struc_elem_ver_id
,p_object_id_to2 => p_dest_proj_id
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => p_relationship_type
,p_relationship_subtype => NULL
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => x_object_relationship_id
,x_return_status => x_return_status
-- ,p_comments => null
,p_comments => p_comment --Bug No 3668113
,p_status_code => null
);
l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(x_object_relationship_id,
'ADD');
pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
p_procedure_name => 'Insert_Subproject_Association',
p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
pa_debug.debug('After Call To PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Return Status => '||x_return_status);
pa_debug.debug('PA_RELATIONSHIP_PVT.Insert_Subproject_Association END');
ROLLBACK to Insert_Subproject_Association;
ROLLBACK to Insert_Subproject_Association;
p_procedure_name => 'Insert_Subproject_Association',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Insert_Subproject_Association;
SELECT imp.period_set_name pa_period_set_name
,imp.pa_period_type
, sob.period_set_name gl_period_set_name
, sob.accounted_period_type
FROM
pa_implementations_all imp
, pa_projects_all prj
, gl_sets_of_books sob
WHERE 1=1
AND prj.org_id = imp.org_id --MOAC Changes: Bug 4363092: removed nvl usage with org_id
AND prj.project_id = cp_project_id
AND sob.set_of_books_id = imp.set_of_books_id
;
select ppa.name
from pa_projects_all ppa
where ppa.project_id = c_project_id;
select ppe.name
from pa_proj_elements ppe, pa_proj_element_versions ppev
where ppe.project_id = ppev.project_id
and ppe.proj_element_id = ppev.proj_element_id
and ppev.element_version_id = c_task_ver_id;
SELECT ppev2.proj_element_id,
ppev1.parent_structure_version_id,
ppev1.FINANCIAL_TASK_FLAG,
ppev1.proj_element_id
INTO l_src_struc_elem_id,
l_src_struc_elem_ver_id,
l_src_task_financial_flag,
l_src_task_elem_id
FROM pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev1.element_version_id = p_task_ver_id
AND ppev1.object_type = 'PA_TASKS'
AND ppev1.project_id = p_src_proj_id
AND ppev2.element_version_id = ppev1.parent_structure_version_id
AND ppev2.project_id = ppev2.project_id
AND ppev2.object_type = 'PA_STRUCTURES';
SELECT proj_element_id
INTO l_dest_wp_struct_element_id
FROM pa_proj_element_versions
WHERE element_version_id = l_dest_wp_str_ver_id;
SELECT proj_element_id
INTO l_dest_fin_struct_element_id
FROM pa_proj_element_versions
WHERE element_version_id = l_dest_fin_str_ver_id;
pa_debug.debug('Before call to Insert_Subproject_Association');
PA_RELATIONSHIP_PVT.Insert_Subproject_Association
( p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,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_max_msg_count => p_max_msg_count
,p_src_proj_id => p_src_proj_id
,p_src_struc_wp_or_fin => 'WORKPLAN'
,p_src_struc_elem_id => l_src_struc_elem_id
,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
,p_src_task_elem_id => l_src_task_elem_id
,p_src_task_elem_ver_id => p_task_ver_id
,p_dest_proj_id => p_dest_proj_id
,p_dest_struc_elem_id => l_dest_wp_struct_element_id
,p_dest_struc_elem_ver_id => l_dest_wp_str_ver_id
,x_lnk_task_elem_id => l_lnk_task_elem_id
,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
,p_lnk_task_name_number => l_task_name_number
,p_relationship_type => 'LW'
,p_comment => p_comment --Bug No 3668113
,x_object_relationship_id => x_object_relationship_id
,x_pev_schedule_id => l_pev_schedule_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_RELATIONSHIP_PVT.Insert_Subproject_Association
( p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,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_max_msg_count => p_max_msg_count
,p_src_proj_id => p_src_proj_id
,p_src_struc_wp_or_fin => 'FINANCIAL'
,p_src_struc_elem_id => l_src_struc_elem_id
,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
,p_src_task_elem_id => l_src_task_elem_id
,p_src_task_elem_ver_id => p_task_ver_id
,p_dest_proj_id => p_dest_proj_id
,p_dest_struc_elem_id => l_dest_fin_struct_element_id
,p_dest_struc_elem_ver_id => l_dest_fin_str_ver_id
,x_lnk_task_elem_id => l_lnk_task_elem_id
,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
,p_lnk_task_name_number => l_task_name_number
,p_relationship_type => 'LF'
,p_comment => p_comment --Bug No 3668113
,x_object_relationship_id => x_object_relationship_id
,x_pev_schedule_id => l_pev_schedule_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_RELATIONSHIP_PVT.Insert_Subproject_Association
( p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,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_max_msg_count => p_max_msg_count
,p_src_proj_id => p_src_proj_id
,p_src_struc_wp_or_fin => 'WORKPLAN'
,p_src_struc_elem_id => l_src_struc_elem_id
,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
,p_src_task_elem_id => l_src_task_elem_id
,p_src_task_elem_ver_id => p_task_ver_id
,p_dest_proj_id => p_dest_proj_id
,p_dest_struc_elem_id => l_dest_wp_struct_element_id
,p_dest_struc_elem_ver_id => l_dest_wp_str_ver_id
,x_lnk_task_elem_id => l_lnk_task_elem_id
,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
,p_lnk_task_name_number => l_task_name_number
,p_relationship_type => 'LW'
,p_comment => p_comment --Bug No 3668113
,x_object_relationship_id => x_object_relationship_id
,x_pev_schedule_id => l_pev_schedule_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_RELATIONSHIP_PVT.Insert_Subproject_Association
( p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,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_max_msg_count => p_max_msg_count
,p_src_proj_id => p_src_proj_id
,p_src_struc_wp_or_fin => 'FINANCIAL'
,p_src_struc_elem_id => l_src_struc_elem_id
,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
,p_src_task_elem_id => l_src_task_elem_id
,p_src_task_elem_ver_id => p_task_ver_id
,p_dest_proj_id => p_dest_proj_id
,p_dest_struc_elem_id => l_dest_fin_struct_element_id
,p_dest_struc_elem_ver_id => l_dest_fin_str_ver_id
,x_lnk_task_elem_id => l_lnk_task_elem_id
,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
,p_lnk_task_name_number => l_task_name_number
,p_relationship_type => 'LF'
,p_comment => p_comment --Bug No 3668113
,x_object_relationship_id => x_object_relationship_id
,x_pev_schedule_id => l_pev_schedule_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => p_src_proj_id
,p_structure_version_id => l_src_struc_elem_ver_id
,p_update_wbs_flag => 'Y'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
Procedure Update_Subproject_Association(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN VARCHAR2 := 100,
p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_debug_mode IN VARCHAR2 := 'N',
p_object_relationship_id IN NUMBER,
p_record_version_number IN NUMBER,
p_comment IN VARCHAR2,
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
-- Bug # 5072032.
cursor l_cur_obj_rel_id(c_object_relationship_id NUMBER) is
select por2.object_relationship_id, por2.record_version_number
from pa_object_relationships por1, pa_object_relationships por2
, pa_object_relationships por3, pa_object_relationships por4
where por1.object_id_to1 = por2.object_id_from1
and por1.relationship_type = 'S'
and por3.object_id_to1 = por4.object_id_from1
and por3.relationship_type = 'S'
and por1.object_id_from1 = por3.object_id_from1
and por2.object_id_from2 = por4.object_id_from2
and por2.object_id_to1 = por4.object_id_to1
and por2.object_id_to2 = por4.object_id_to2
and por2.relationship_type IN ('LW','LF')
and por4.object_relationship_id = c_object_relationship_id;
pa_debug.debug('PA_RELATIONSHIPS_PVT1.UPDATE_SUBPROJECT_ASSOCIATION Begin');
savepoint update_subproject_ass_pvt;
UPDATE pa_object_relationships
SET comments = p_comment
,record_version_number = (l_cur_obj_rel_rec.record_version_number+1) -- p_record_version_number + 1
WHERE object_relationship_id = l_cur_obj_rel_rec.object_relationship_id -- p_object_relationship_id
and record_version_number = l_cur_obj_rel_rec.record_version_number;
pa_debug.debug('Return status before the end of Update_Subproject_Association=> '||x_return_status);
pa_debug.debug('PA_RELATIONSHIPS_PVT1.UPDATE_SUBPROJECT_ASSOCIATION END');
ROLLBACK to update_subproject_ass_pvt;
ROLLBACK TO update_subproject_ass_pvt;
p_procedure_name => 'update_subproject_association',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO update_subproject_association;
END Update_Subproject_Association;
PROCEDURE Delete_SubProject_Association(p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN VARCHAR2 := 100,
p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
p_debug_mode IN VARCHAR2 := 'N',
p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_object_relationships_id IN NUMBER,
p_record_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) --File.Sql.39 bug 4440895
IS
-- Deleting sub-project association
-- Input parameters for this API are
-- object_relationship_id= p_object_relationships_id
l_src_lnk_task_ver_id pa_proj_element_versions.element_version_id%type;
SELECT object_id_from1, --src_lnk_task_ver_id
object_id_to1, --dest_str_ver_id
object_id_from2, --src proj_id
object_id_to2 --dest_proj_id
FROM pa_object_relationships
WHERE object_relationship_id = cp_object_relationships_id
AND relationship_type IN ('LW','LF');
SELECT record_version_number --task_Version_rvn
FROM pa_proj_element_versions
WHERE element_version_id = cp_lnk_task_ver_id;
SELECT object_id_from1 --src_task_ver_id
FROM pa_object_relationships
WHERE object_id_to1 = cp_src_lnk_task_ver_id
AND relationship_type = 'S';
SELECT pora.object_relationship_id obj_rel_id,
pora.object_id_to1 lnk_task_ver_id,
porb.object_relationship_id lnk_obj_rel_id,
porb.object_id_to1 lnk_dest_str_ver_id
, porb.record_version_number lnk_record_ver_number -- Bug # 5072032.
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_project_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 = cp_dest_proj_id
AND porb.object_id_from2 = cp_src_project_id
AND porb.object_type_to = 'PA_STRUCTURES'
AND porb.relationship_type IN ('LW','LF')
AND ppe.link_task_flag = 'Y';
SELECT project_id, parent_structure_version_id
FROM pa_proj_element_versions
WHERE element_version_id = c_src_task_ver_id
;
select 'x'
from pa_proj_elem_ver_structure
where project_id = c_dest_proj_id
and element_version_id = c_dest_struc_elem_ver_id
and status_code = 'STRUCTURE_PUBLISHED';
pa_debug.debug('PA_RELATIONSHIP_PVT.Delete_SubProject_Association begin');
savepoint delete_subproject_ass_pvt;
l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(get_lnk_info_rec.lnk_obj_rel_id,
'DROP');
pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
p_procedure_name => 'Delete_SubProject_Association',
p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
p_object_relationship_id => get_lnk_info_rec.lnk_obj_rel_id
,p_object_type_from => NULL
,p_object_id_from1 => NULL
,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 => NULL
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_record_version_number => get_lnk_info_rec.lnk_record_ver_number -- p_record_version_number -- Bug # 5072032.
,p_pm_product_code => NULL
,x_return_status => x_return_status
);
pa_debug.debug('Return status after call to PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW=> '||x_return_status);
PA_TASK_PUB1.DELETE_TASK_VERSION(p_commit => 'N',
p_debug_mode => p_debug_mode,
p_task_version_id => get_lnk_info_rec.lnk_task_ver_id,
p_record_version_number => get_rec_ver_num_rec.record_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_debug.debug('Return status after call to PA_TASK_PUB1.DELETE_TASK_VERSION=> '||x_return_status);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => l_src_project_id
,p_structure_version_id => l_src_structure_ver_id
,p_update_wbs_flag => 'Y'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.debug('Return status before the end of Delete_SubProject_Association=> '||x_return_status);
pa_debug.debug('PA_RELATIONSHIP_PVT.Delete_SubProject_Association end');
ROLLBACK to delete_subproject_ass_pvt;
ROLLBACK to delete_subproject_ass_pvt;
p_procedure_name => 'Delete_SubProject_Association',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_SubProject_Association;
SELECT ppev.element_version_id lnk_task_ver_id,
ppe.proj_element_id lnk_task_id
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev
WHERE ppe.proj_element_id = ppev.proj_element_id
AND ppe.link_task_flag = 'Y'
AND ppev.parent_structure_version_id = cp_src_str_ver_id
AND ppe.project_id = ppev.project_id;
SELECT por.object_relationship_id,
por.object_id_to1,
por.object_id_from1,
por.object_id_to2,
por.object_id_from2,
por.relationship_type,
por.record_version_number,
por.object_type_to,
por.object_type_from
FROM pa_object_relationships por
WHERE por.relationship_type in ('LW','LF')
AND por.OBJECT_ID_FROM1 = cp_src_Task_ver_id
AND por.object_type_from = 'PA_TASKS'
AND por.object_type_to = 'PA_STRUCTURES'
AND por.object_id_to2 <> por.object_id_from2;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE parent_structure_Version_id = c_dest_str_version_id
AND proj_element_id = c_link_task_id;
SELECT element_version_id
INTO l_new_pub_lnk_task_ver_id
FROM pa_proj_element_versions
WHERE parent_structure_Version_id = p_dest_str_version_id
AND proj_element_id = get_linking_task_info_rec.lnk_task_id;
pa_debug.debug('Value of l_new_pub_lnk_task_ver_id after select=> '||l_new_pub_lnk_task_ver_id);
pa_debug.debug('Value of p_dest_str_version_id after select => '||p_dest_str_version_id);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => l_new_pub_lnk_task_ver_id
,p_object_id_from2 => get_going_out_lnk_info_rec.object_id_from2
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => get_going_out_lnk_info_rec.object_type_to
,p_object_id_to1 => get_going_out_lnk_info_rec.object_id_to1
,p_object_id_to2 => get_going_out_lnk_info_rec.object_id_to2
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => get_going_out_lnk_info_rec.relationship_type
,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_relationship_id
,x_return_status => x_return_status
,p_comments => null
,p_status_code => null
);
pa_debug.debug('Value of x_return_status after call to PA_OBJECT_RELATIONSHIPS_PKG INSERT_ROW=> '||x_return_status);
l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(l_object_relationship_id,
'ADD');
pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
SELECT porb.object_relationship_id,
porb.object_id_to1,
porb.object_id_from1,
porb.object_id_to2,
porb.object_id_from2,
porb.relationship_type,
porb.record_version_number
FROM pa_object_relationships pora,
pa_object_relationships porb
WHERE pora.relationship_type = 'S'
AND pora.object_type_from = 'PA_TASKS'
AND pora.object_id_to1 = porb.object_id_from1
AND pora.object_type_to = porb.object_type_from
AND porb.OBJECT_ID_TO1 = cp_src_str_ver_id
AND porb.object_type_to = 'PA_STRUCTURES'
AND porb.relationship_type IN ('LW','LF');
Select 1 from pa_proj_element_versions a, pa_proj_elem_ver_structure b
where a.element_version_id = c_ver_id
and a.project_id = b.project_id
and a.parent_structure_version_id = b.element_version_id
and b.status_code <> 'STRUCTURE_PUBLISHED';
select ppa.name
from pa_projects_all ppa
where ppa.project_id = c_project_id;
SELECT project_id
INTO l_proj_id
FROM pa_proj_element_versions
WHERE element_Version_id = get_coming_in_lnk_info_rec.object_id_from1;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => get_coming_in_lnk_info_rec.object_id_from1
,p_object_id_from2 => get_coming_in_lnk_info_rec.object_id_from2
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_STRUCTURES'
,p_object_id_to1 => p_dest_str_version_id
,p_object_id_to2 => get_coming_in_lnk_info_rec.object_id_to2
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => get_coming_in_lnk_info_rec.relationship_type
,p_relationship_subtype => NULL
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => x_object_relationship_id
,x_return_status => x_return_status
,p_comments => null
,p_status_code => null
);
l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(x_object_relationship_id,
'ADD');
pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
p_procedure_name => 'Insert_Subproject_Association',
p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(get_coming_in_lnk_info_rec.object_relationship_id,
'DROP');
pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
p_object_relationship_id => get_coming_in_lnk_info_rec.object_relationship_id
,p_object_type_from => NULL
,p_object_id_from1 => NULL
,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 => NULL
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_record_version_number => get_coming_in_lnk_info_rec.record_version_number
,p_pm_product_code => NULL
,x_return_status => x_return_status
);
pa_debug.debug('Return status before the end of Delete_SubProject_Association=> '||x_return_status);
procedure UPDATE_PARENT_WBS_FLAG_DIRTY
(
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 VARCHAR2 := 100
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_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_api_name CONSTANT VARCHAR(30) := 'UPDATE_PARENT_WBS_FLAG_DIRTY';
SELECT *
FROM pa_object_relationships
WHERE object_id_to2 = p_project_id
AND object_id_to1 = p_structure_version_id
AND relationship_type = 'LW'; --Financial links should not be specified here bcoz Process WBS updates can be run only for workplan structures.
pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_DEPENDENCY begin');
savepoint UPDATE_PARENT_WBS_FLAG_DIRTY;
pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY', x_Msg => 'p_project_id: '||p_project_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY', x_Msg => 'p_structure_version_id: '||p_structure_version_id, x_Log_Level=> 3);
UPDATE pa_proj_elem_ver_structure
SET PROCESS_UPDATE_WBS_FLAG = 'Y',
process_code = 'CPI'
WHERE project_id = cur_obj_rel_rec.object_id_from2
AND element_version_id=(select parent_structure_version_id
FROM pa_proj_element_versions
WHERE project_id=cur_obj_rel_rec.object_id_from2
AND element_version_id= cur_obj_rel_rec.object_id_from1
);
pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY', x_Msg => 'Completed', x_Log_Level=> 3);
pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY END');
rollback to UPDATE_PARENT_WBS_FLAG_DIRTY;
rollback to UPDATE_PARENT_WBS_FLAG_DIRTY;
p_procedure_name => 'UPDATE_PARENT_WBS_FLAG_DIRTY',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to UPDATE_PARENT_WBS_FLAG_DIRTY;
p_procedure_name => 'UPDATE_PARENT_WBS_FLAG_DIRTY',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PARENT_WBS_FLAG_DIRTY;