[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
l_api_name CONSTANT VARCHAR(30) := 'DELETE_RELATIONSHIP';
pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP');
pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP begin');
savepoint delete_relationship;
PA_RELATIONSHIP_PVT.Delete_Relationship(
p_api_version => p_api_version
,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_object_relationship_id => p_object_relationship_id
,p_record_version_number => p_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
rollback to delete_relationship;
rollback to delete_relationship;
p_procedure_name => 'DELETE_RELATIONSHIP',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_relationship;
SELECT parent_structure_version_id
FROM pa_proj_element_versions
WHERE project_id = p_src_proj_id
AND element_version_id = p_src_task_ver_id
AND object_type = 'PA_TASKS';
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';
pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.UPDATE_DEPENDENCY');
pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_DEPENDENCY begin');
savepoint update_dependency;
PA_RELATIONSHIP_PVT.Update_Dependency
(
p_api_version => p_api_version
,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_task_version_id => p_task_version_id
,p_src_task_version_id => p_src_task_version_id
,p_type => p_type
,p_lag_days => p_lag_days
,p_comments => p_comments
,p_record_version_number => p_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_DEPENDENCY END');
rollback to update_dependency;
rollback to update_dependency;
p_procedure_name => 'UPDATE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_dependency;
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';
pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_DEPENDENCY');
pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_DEPENDENCY begin');
savepoint delete_dependency;
PA_RELATIONSHIP_PVT.Delete_Dependency
(
p_api_version => p_api_version
,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_object_relationship_id => p_object_relationship_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_DEPENDENCY END');
rollback to delete_dependency;
rollback to delete_dependency;
p_procedure_name => 'DELETE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_dependency;
p_procedure_name => 'DELETE_DEPENDENCY',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Dependency;
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
--
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR(30) := 'UPDATE_SUBPROJECT_ASSOCIATION';
pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_SUBPROJECT_ASSOCIATION');
pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_SUBPROJECT_ASSOCIATION begin');
savepoint Update_Subproject_Association;
PA_RELATIONSHIP_PVT.Update_Subproject_Association
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_max_msg_count => p_max_msg_count,
p_commit => p_commit,
p_debug_mode => p_debug_mode,
p_object_relationship_id => p_object_relationship_id,
p_record_version_number => p_record_version_number,
p_comment => p_comment,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_SUBPROJECT_ASSOCIATION END');
rollback to Update_Subproject_Association;
rollback to Update_Subproject_Association;
p_procedure_name => 'UPDATE_SUBPROJECT_ASSOCIATION',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Update_Subproject_Association;
p_procedure_name => 'UPDATE_SUBPROJECT_ASSOCIATION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Subproject_Association;
Procedure Delete_SubProject_Association(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_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
--
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR(30) := 'DELETE_SUBPROJECT_ASSOCIATION';
pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_SUBPROJECT_ASSOCIATION');
pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_SUBPROJECT_ASSOCIATION begin');
savepoint Delete_SubProject_Association;
PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
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_object_relationships_id => p_object_relationships_id,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_SUBPROJECT_ASSOCIATION END');
rollback to Delete_SubProject_Association;
rollback to Delete_SubProject_Association;
p_procedure_name => 'DELETE_SUBPROJECT_ASSOCIATION',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Delete_SubProject_Association;
p_procedure_name => 'DELETE_SUBPROJECT_ASSOCIATION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_SubProject_Association;
function UPDATE_PROGRAM_GROUPS (p_object_relationship_id in number,
p_operation_type in varchar2)
return number is
l_parent_task_version_id number;
select
PARENT_TASK_VERSION_ID,
PARENT_GROUP,
PARENT_LEVEL,
PARENT_PROJECT,
CHILD_STRUCTURE_VERSION_ID,
CHILD_GROUP,
CHILD_LEVEL,
CHILD_PROJECT,
RELATIONSHIP_TYPE
into
l_parent_task_version_id,
l_parent_group,
l_parent_level,
l_parent_project,
l_child_structure_version_id,
l_child_group,
l_child_level,
l_child_project,
l_relationship_type
from
(
select /*+ index(rel, PA_OBJECT_RELATIONSHIPS_U1)
index(ver1, PA_PROJ_ELEMENT_VERSIONS_N3)
index(ver2, PA_PROJ_ELEMENT_VERSIONS_N3) */
rel.OBJECT_ID_FROM1 PARENT_TASK_VERSION_ID,
ver1.PRG_GROUP PARENT_GROUP,
ver1.PRG_LEVEL PARENT_LEVEL,
rel.OBJECT_ID_FROM2 PARENT_PROJECT,
rel.OBJECT_ID_TO1 CHILD_STRUCTURE_VERSION_ID,
ver2.PRG_GROUP CHILD_GROUP,
ver2.PRG_LEVEL CHILD_LEVEL,
rel.OBJECT_ID_TO2 CHILD_PROJECT,
rel.RELATIONSHIP_TYPE RELATIONSHIP_TYPE
from
PA_OBJECT_RELATIONSHIPS rel,
PA_PROJ_ELEMENT_VERSIONS ver1,
PA_PROJ_ELEMENT_VERSIONS ver2
where
rel.OBJECT_RELATIONSHIP_ID = p_object_relationship_id and
ver1.PROJECT_ID = rel.OBJECT_ID_FROM2 and
ver1.OBJECT_TYPE = 'PA_STRUCTURES' and
ver2.PROJECT_ID = rel.OBJECT_ID_TO2 and
ver2.OBJECT_TYPE = 'PA_STRUCTURES'
group by
rel.OBJECT_ID_FROM1,
ver1.PRG_GROUP,
ver1.PRG_LEVEL,
rel.OBJECT_ID_FROM2,
rel.OBJECT_ID_TO1,
ver2.PRG_GROUP,
ver2.PRG_LEVEL,
rel.OBJECT_ID_TO2,
rel.RELATIONSHIP_TYPE
order by
ver1.PRG_GROUP,
ver1.PRG_LEVEL,
ver2.PRG_GROUP,
ver2.PRG_LEVEL
)
where
ROWNUM = 1;
return UPDATE_PROGRAM_GROUPS (l_parent_task_version_id,
l_parent_group,
l_parent_level,
l_parent_project,
l_child_structure_version_id,
l_child_group,
l_child_level,
l_child_project,
l_relationship_type,
p_operation_type);
end UPDATE_PROGRAM_GROUPS;
function UPDATE_PROGRAM_GROUPS (p_parent_task_version_id in number,
p_parent_group in number,
p_parent_level in number,
p_parent_project in number,
p_child_structure_version_id in number,
p_child_group in number,
p_child_level in number,
p_child_project in number,
p_relationship_type in varchar2,
p_operation_type in varchar2)
return number is
l_parent_structure_version_id number;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
savepoint UPDATE_PROGRAM_GROUPS;
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = l_parent_level
where OBJECT_TYPE = 'PA_STRUCTURES' and
PROJECT_ID = l_parent_project and
(PRG_GROUP is null or PRG_LEVEL is null);
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = l_child_level
where OBJECT_TYPE = 'PA_STRUCTURES' and
PROJECT_ID = l_child_project and
(PRG_GROUP is null or PRG_LEVEL is null);
select OBJECT_ID_FROM1
into l_actual_task_version_id
from PA_OBJECT_RELATIONSHIPS
where OBJECT_TYPE_FROM = 'PA_TASKS' and
OBJECT_TYPE_TO = 'PA_TASKS' and
RELATIONSHIP_TYPE = 'S' and
OBJECT_ID_TO1 = p_parent_task_version_id;
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = l_parent_level,
PRG_COUNT = nvl(PRG_COUNT, 0) + 1
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
select PARENT_STRUCTURE_VERSION_ID
into l_parent_structure_version_id
from PA_PROJ_ELEMENT_VERSIONS
where ELEMENT_VERSION_ID = p_parent_task_version_id;
select count(*)
into l_count
from PA_OBJECT_RELATIONSHIPS
where RELATIONSHIP_TYPE = p_relationship_type and
OBJECT_TYPE_FROM = 'PA_TASKS' and
OBJECT_TYPE_TO = 'PA_STRUCTURES' and
OBJECT_ID_FROM2 = l_parent_project and
OBJECT_ID_TO2 = l_child_project and
OBJECT_ID_FROM1 in (select
ver.ELEMENT_VERSION_ID
from
PA_PROJ_ELEMENT_VERSIONS ver
where
ver.PARENT_STRUCTURE_VERSION_ID
= l_parent_structure_version_id) and
OBJECT_ID_TO1 = p_child_structure_version_id and
not(OBJECT_ID_FROM1 = p_parent_task_version_id and
OBJECT_ID_TO1 = p_child_structure_version_id) and
ROWNUM = 1;
rollback to UPDATE_PROGRAM_GROUPS;
select count(*)
into l_count
from PA_OBJECT_RELATIONSHIPS
where RELATIONSHIP_TYPE in ('LF', 'LW') and
OBJECT_TYPE_FROM = 'PA_TASKS' and
OBJECT_TYPE_TO = 'PA_STRUCTURES' and
OBJECT_ID_FROM2 = l_parent_project and
OBJECT_ID_TO2 = l_child_project and
not(OBJECT_ID_FROM1 = p_parent_task_version_id and
OBJECT_ID_TO1 = p_child_structure_version_id) and
ROWNUM = 1;
insert into PA_PROJ_LEVELS_TMP
(
FROM_ID,
TO_ID,
FROM_LEVEL,
TO_LEVEL,
DIRECTION,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6
)
select /*+ ordered
index(ver, PA_PROJ_ELEMENT_VERSIONS_N5)
index(rel, PA_OBJECT_RELATIONSHIPS_U2) use_nl(rel) */
distinct
decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
'UP', rel.OBJECT_ID_TO2) FROM_ID,
decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
'UP', rel.OBJECT_ID_FROM2) TO_ID,
-1 FROM_LEVEL,
-1 TO_LEVEL,
decode(invert.INVERT_ID, 'DOWN', 'D',
'UP', 'U') DIRECTION,
decode(invert.INVERT_ID,
'DOWN',
decode(rel.OBJECT_ID_FROM2,
l_parent_project,
decode(OBJECT_ID_TO2,
l_child_project,
'NEW_ASSOCIATION_DOWN',
'X'),
'X'),
'UP',
decode(rel.OBJECT_ID_FROM2,
l_parent_project,
decode(OBJECT_ID_TO2,
l_child_project,
'NEW_ASSOCIATION_UP',
'X'),
'X'),
'X') ATTRIBUTE1,
null ATTRIBUTE2,
null ATTRIBUTE3,
null ATTRIBUTE4,
null ATTRIBUTE5,
null ATTRIBUTE6
from
PA_PROJ_ELEMENT_VERSIONS ver,
PA_OBJECT_RELATIONSHIPS rel,
pa_proj_structure_types ppst,
pa_proj_elem_ver_structure ppevs,
pa_projects_all ppa,
(
select 'DOWN' INVERT_ID from dual union all
select 'UP' INVERT_ID from dual
) invert
where
ver.OBJECT_TYPE = 'PA_TASKS' and
ver.PRG_GROUP = l_parent_group and
ppa.project_id = ver.project_id and
rel.OBJECT_TYPE_FROM = 'PA_TASKS' and
rel.OBJECT_ID_FROM1 = ver.ELEMENT_VERSION_ID and
rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
rel.RELATIONSHIP_TYPE in ('LF', 'LW')
AND ver.parent_structure_version_id = ppevs.element_version_id
AND ppevs.proj_element_id = ppst.proj_element_id
AND ( (ppst.structure_type_id = 1 and (ppevs.latest_eff_published_flag = 'Y' or ppevs.status_code = 'STRUCTURE_WORKING') )
or ( ppst.structure_type_id = 6 and ppa.structure_sharing_code not in ('SHARE_FULL', 'SHARE_PARTIAL' ) )); -- added last two conditions for bug 7409918
select
count(*)
into
l_count
from
(
select
FROM_ID,
TO_ID
from
PA_PROJ_LEVELS_TMP
group by
FROM_ID,
TO_ID
having
count(*) > 1
);
rollback to UPDATE_PROGRAM_GROUPS;
select
distinct
tmp1.TO_ID PROJECT_ID
from
PA_PROJ_LEVELS_TMP tmp1
start with
tmp1.ATTRIBUTE1 = 'NEW_ASSOCIATION_DOWN'
connect by
tmp1.DIRECTION = 'D' and
tmp1.FROM_ID = prior tmp1.TO_ID and
tmp1.TO_ID <> prior tmp1.FROM_ID
minus
select
distinct
tmp2.FROM_ID PROJECT_ID
from
PA_PROJ_LEVELS_TMP tmp2
where
tmp2.DIRECTION = 'D'
) loop
select
count(*)
into
l_count
from
(
select
tmp3.TO_ID
from
PA_PROJ_LEVELS_TMP tmp3
start with
tmp3.FROM_ID = leaf_node.PROJECT_ID
connect by
tmp3.DIRECTION = 'U' and
tmp3.FROM_ID = prior tmp3.TO_ID and
tmp3.TO_ID <> prior tmp3.FROM_ID
group by
tmp3.TO_ID
having
count(*) > 1
)
where
ROWNUM = 1;
rollback to UPDATE_PROGRAM_GROUPS;
rollback to UPDATE_PROGRAM_GROUPS;
update
PA_PROJ_LEVELS_TMP tmp4
set
tmp4.FROM_LEVEL = 1
where
tmp4.FROM_LEVEL <> 1 and
tmp4.FROM_ID in
(
select
tmp3.PROJECT_ID
from
(
select
tmp2.PROJECT_ID,
tmp2.PROJECT_LEVEL
from
(
select
distinct
tmp1.TO_ID PROJECT_ID,
LEVEL PROJECT_LEVEL
from
PA_PROJ_LEVELS_TMP tmp1
start with
tmp1.DIRECTION = 'U'
connect by
tmp1.DIRECTION = 'U' and
tmp1.FROM_ID = prior tmp1.TO_ID and
tmp1.TO_ID <> prior tmp1.FROM_ID
) tmp2
order by
tmp2.PROJECT_LEVEL desc
) tmp3
where
ROWNUM = 1
);
update
PA_PROJ_LEVELS_TMP
set
TO_LEVEL = 1
where
TO_LEVEL <> 1 and
TO_ID in
(
select
tmp1.FROM_ID
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.FROM_LEVEL = 1
);
update
PA_PROJ_LEVELS_TMP tmp
set
tmp.TO_LEVEL = tmp.FROM_LEVEL + 1
where
tmp.FROM_LEVEL <> -1 and
tmp.TO_LEVEL <> -1 and
tmp.DIRECTION = 'D' and
tmp.FROM_LEVEL + 1 > tmp.TO_LEVEL;
update
PA_PROJ_LEVELS_TMP tmp
set
tmp.TO_LEVEL = decode(tmp.DIRECTION,
'U', tmp.FROM_LEVEL - 1,
'D', tmp.FROM_LEVEL + 1)
where
tmp.FROM_LEVEL <> -1 and
tmp.TO_LEVEL = -1;
update
PA_PROJ_LEVELS_TMP tmp2
set
tmp2.TO_LEVEL =
(
select
max(tmp1.TO_LEVEL)
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.TO_ID
)
where
tmp2.TO_LEVEL <>
(
select
max(tmp1.TO_LEVEL)
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.TO_ID
);
update
PA_PROJ_LEVELS_TMP tmp2
set
tmp2.FROM_LEVEL =
(
select
tmp1.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.FROM_ID and
tmp1.TO_LEVEL <> -1 and
ROWNUM = 1
)
where
tmp2.FROM_LEVEL <>
(
select
tmp1.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.FROM_ID and
tmp1.TO_LEVEL <> -1 and
ROWNUM = 1
);
update
PA_PROJ_ELEMENT_VERSIONS ver
set
ver.PRG_LEVEL =
(
select
tmp.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp
where
tmp.TO_ID = ver.PROJECT_ID and
tmp.TO_LEVEL <> -1 and
ROWNUM = 1
)
where
ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
ver.PRG_GROUP = l_parent_group and
ver.PROJECT_ID in
(
select
distinct
tmp.TO_ID
from
PA_PROJ_LEVELS_TMP tmp
) and
ver.PRG_LEVEL <>
(
select
tmp.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp
where
tmp.TO_ID = ver.PROJECT_ID and
tmp.TO_LEVEL <> -1 and
ROWNUM = 1
);
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = l_child_level - 1,
PRG_COUNT = nvl(PRG_COUNT, 0) + 1
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = l_parent_level + l_level_adjustment
where PROJECT_ID = l_parent_project and
OBJECT_TYPE = 'PA_STRUCTURES';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = PRG_LEVEL + l_level_adjustment
where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
PRG_GROUP = l_parent_group;
select PA_PROJ_ELEMENT_VERSIONS_S1.NEXTVAL
into l_parent_group
from DUAL;
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = l_parent_level,
PRG_COUNT = nvl(PRG_COUNT, 0) + 1
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = decode(PROJECT_ID, l_parent_project,
l_parent_level,
l_child_level +
l_level_adjustment)
where PROJECT_ID in (l_parent_project,
l_child_project) and
OBJECT_TYPE = 'PA_STRUCTURES';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = l_parent_level,
PRG_COUNT = nvl(PRG_COUNT, 0) + 1
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = l_child_level + l_level_adjustment
where PROJECT_ID = l_child_project and
OBJECT_TYPE = 'PA_STRUCTURES';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = PRG_LEVEL + l_level_adjustment
where PRG_GROUP = l_child_group and
OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS');
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = l_child_level + l_level_adjustment - 1,
PRG_COUNT = nvl(PRG_COUNT, 0) + 1
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_child_group,
PRG_LEVEL = l_child_level + l_level_adjustment - 1
where PROJECT_ID = l_parent_project and
OBJECT_TYPE = 'PA_STRUCTURES';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = l_parent_level,
PRG_COUNT = nvl(PRG_COUNT, 0) + 1
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = l_parent_group,
PRG_LEVEL = PRG_LEVEL + l_level_adjustment
where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
PRG_GROUP = l_child_group;
rollback to UPDATE_PROGRAM_GROUPS;
insert into PA_PROJ_LEVELS_TMP
(
FROM_ID,
TO_ID,
FROM_LEVEL,
TO_LEVEL,
DIRECTION,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6
)
select /*+ ordered
index(ver, PA_PROJ_ELEMENT_VERSIONS_N5)
index(rel, PA_OBJECT_RELATIONSHIPS_U2) use_nl(rel) */
decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
'UP', rel.OBJECT_ID_TO2) FROM_ID,
decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
'UP', rel.OBJECT_ID_FROM2) TO_ID,
-1 FROM_LEVEL,
-1 TO_LEVEL,
decode(invert.INVERT_ID, 'DOWN', 'D',
'UP', 'U') DIRECTION,
decode(invert.INVERT_ID,
'DOWN',
decode(rel.OBJECT_ID_FROM2,
l_parent_project,
decode(OBJECT_ID_TO2,
l_child_project,
'DROPPED_ASSOCIATION_DOWN',
'X'),
'X'),
'UP',
decode(rel.OBJECT_ID_FROM2,
l_parent_project,
decode(OBJECT_ID_TO2,
l_child_project,
'DROPPED_ASSOCIATION_UP',
'X'),
'X'),
'X') ATTRIBUTE1,
count(*) ATTRIBUTE2,
'X' ATTRIBUTE3,
null ATTRIBUTE4,
null ATTRIBUTE5,
null ATTRIBUTE6
from
PA_PROJ_ELEMENT_VERSIONS ver,
PA_OBJECT_RELATIONSHIPS rel,
(
select 'DOWN' INVERT_ID from dual union all
select 'UP' INVERT_ID from dual
) invert
where
ver.OBJECT_TYPE = 'PA_TASKS' and
ver.PRG_GROUP = l_parent_group and
rel.OBJECT_TYPE_FROM = 'PA_TASKS' and
rel.OBJECT_ID_FROM1 = ver.ELEMENT_VERSION_ID and
rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
rel.RELATIONSHIP_TYPE in ('LF', 'LW')
group by
decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
'UP', rel.OBJECT_ID_TO2),
decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
'UP', rel.OBJECT_ID_FROM2),
decode(invert.INVERT_ID, 'DOWN', 'D',
'UP', 'U'),
decode(invert.INVERT_ID,
'DOWN',
decode(rel.OBJECT_ID_FROM2,
l_parent_project,
decode(OBJECT_ID_TO2,
l_child_project,
'DROPPED_ASSOCIATION_DOWN',
'X'),
'X'),
'UP',
decode(rel.OBJECT_ID_FROM2,
l_parent_project,
decode(OBJECT_ID_TO2,
l_child_project,
'DROPPED_ASSOCIATION_UP',
'X'),
'X'),
'X');
update PA_PROJ_ELEMENT_VERSIONS
set PRG_GROUP = decode(PRG_COUNT, 1, null, PRG_GROUP),
PRG_LEVEL = decode(PRG_COUNT, 1, null, PRG_LEVEL),
PRG_COUNT = decode(PRG_COUNT, 1, null, PRG_COUNT - 1)
where ELEMENT_VERSION_ID in (p_parent_task_version_id,
l_actual_task_version_id) and
OBJECT_TYPE = 'PA_TASKS';
select
tmp.ATTRIBUTE2
into
l_count
from
PA_PROJ_LEVELS_TMP tmp
where
tmp.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN';
update
PA_PROJ_LEVELS_TMP tmp2
set
tmp2.ATTRIBUTE3 = 'CHILD_SUBGROUP'
where
tmp2.ATTRIBUTE1 not in ('DROPPED_ASSOCIATION_DOWN',
'DROPPED_ASSOCIATION_UP') and
tmp2.ATTRIBUTE3 <> 'CHILD_SUBGROUP' and
exists
(
select
1
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' and
(tmp2.FROM_ID = tmp1.TO_ID or
tmp2.TO_ID = tmp1.TO_ID)
);
update
PA_PROJ_LEVELS_TMP tmp2
set
tmp2.ATTRIBUTE3 = 'CHILD_SUBGROUP'
where
tmp2.ATTRIBUTE1 not in ('DROPPED_ASSOCIATION_DOWN',
'DROPPED_ASSOCIATION_UP') and
tmp2.ATTRIBUTE3 <> 'CHILD_SUBGROUP' and
exists
(
select
1
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.ATTRIBUTE3 = 'CHILD_SUBGROUP' and
(tmp2.FROM_ID = tmp1.TO_ID or
tmp2.TO_ID = tmp1.TO_ID)
);
select
count(*)
into
l_count
from
PA_PROJ_LEVELS_TMP tmp
where
tmp.TO_ID = l_parent_project and
tmp.ATTRIBUTE3 = 'CHILD_SUBGROUP' and
ROWNUM = 1;
update
PA_PROJ_LEVELS_TMP tmp4
set
tmp4.FROM_LEVEL = 1
where
tmp4.FROM_LEVEL <> 1 and
tmp4.FROM_ID in
(
select
tmp3.PROJECT_ID
from
(
select
tmp2.PROJECT_ID,
tmp2.PROJECT_LEVEL
from
(
select
distinct
tmp1.TO_ID PROJECT_ID,
LEVEL PROJECT_LEVEL
from
PA_PROJ_LEVELS_TMP tmp1
start with
tmp1.DIRECTION = 'U' and
tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP'
connect by
tmp1.DIRECTION = 'U' and
tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
tmp1.FROM_ID = prior tmp1.TO_ID and
tmp1.TO_ID <> prior tmp1.FROM_ID
) tmp2
order by
tmp2.PROJECT_LEVEL desc
) tmp3
where
ROWNUM = 1
);
update
PA_PROJ_LEVELS_TMP
set
TO_LEVEL = 1
where
TO_LEVEL <> 1 and
TO_ID in
(
select
tmp1.FROM_ID
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.FROM_LEVEL = 1
);
update
PA_PROJ_LEVELS_TMP tmp
set
tmp.TO_LEVEL = tmp.FROM_LEVEL + 1
where
tmp.FROM_LEVEL <> -1 and
tmp.TO_LEVEL <> -1 and
tmp.DIRECTION = 'D' and
tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
tmp.FROM_LEVEL + 1 > tmp.TO_LEVEL;
update
PA_PROJ_LEVELS_TMP tmp
set
tmp.TO_LEVEL = decode(tmp.DIRECTION,
'U', tmp.FROM_LEVEL - 1,
'D', tmp.FROM_LEVEL + 1)
where
tmp.FROM_LEVEL <> -1 and
tmp.TO_LEVEL = -1 and
tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN';
update
PA_PROJ_LEVELS_TMP tmp2
set
tmp2.TO_LEVEL =
(
select
max(tmp1.TO_LEVEL)
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.TO_ID
)
where
tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
tmp2.TO_LEVEL <>
(
select
max(tmp1.TO_LEVEL)
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.TO_ID
);
update
PA_PROJ_LEVELS_TMP tmp2
set
tmp2.FROM_LEVEL =
(
select
tmp1.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.FROM_ID and
tmp1.TO_LEVEL <> -1 and
ROWNUM = 1
)
where
tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
tmp2.FROM_LEVEL <>
(
select
tmp1.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp1
where
tmp1.TO_ID = tmp2.FROM_ID and
tmp1.TO_LEVEL <> -1 and
ROWNUM = 1
);
update
PA_PROJ_ELEMENT_VERSIONS ver
set
ver.PRG_LEVEL =
(
select
tmp.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp
where
tmp.TO_ID = ver.PROJECT_ID and
tmp.TO_LEVEL <> -1 and
ROWNUM = 1
)
where
ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
ver.PRG_GROUP = l_parent_group and
ver.PROJECT_ID in
(
select
distinct
tmp.TO_ID
from
PA_PROJ_LEVELS_TMP tmp
) and
ver.PRG_LEVEL <>
(
select
tmp.TO_LEVEL
from
PA_PROJ_LEVELS_TMP tmp
where
tmp.TO_ID = ver.PROJECT_ID and
tmp.TO_LEVEL <> -1 and
ROWNUM = 1
);
select PA_PROJ_ELEMENT_VERSIONS_S1.NEXTVAL
into l_child_group
from DUAL;
update PA_PROJ_ELEMENT_VERSIONS ver
set ver.PRG_GROUP = l_child_group
where ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
ver.PRG_GROUP = l_parent_group and
ver.PROJECT_ID in
(
select
tmp.TO_ID
from
PA_PROJ_LEVELS_TMP tmp
where
ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' or
ATTRIBUTE3 = 'CHILD_SUBGROUP'
);
select PRG_GROUP,
PRG_LEVEL
into l_program_group,
l_level_adjustment
from (select PRG_GROUP,
PRG_LEVEL - 1 PRG_LEVEL
from (select PRG_GROUP,
min(PRG_LEVEL) PRG_LEVEL
from PA_PROJ_ELEMENT_VERSIONS
where OBJECT_TYPE = 'PA_STRUCTURES' and
PRG_GROUP in (l_parent_group,
l_child_group)
group by PRG_GROUP)
order by PRG_LEVEL desc)
where ROWNUM = 1;
update PA_PROJ_ELEMENT_VERSIONS
set PRG_LEVEL = PRG_LEVEL - l_level_adjustment
where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
PRG_GROUP = l_program_group;
delete from PA_PROJ_LEVELS_TMP;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
insert into PA_PJI_PROJ_EVENTS_LOG
(
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE1,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
'PRG_CHANGE',
PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
l_parent_group,
'I',
'X',
l_child_group,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
);
insert into PA_PJI_PROJ_EVENTS_LOG
(
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE1,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
'PRG_CHANGE',
PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
-1,
'I',
'X',
l_new_assoc_parent,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
);
insert into PA_PJI_PROJ_EVENTS_LOG
(
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE1,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
'PRG_CHANGE',
PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
-1,
'I',
'X',
l_new_assoc_child,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
);
rollback to UPDATE_PROGRAM_GROUPS;
end UPDATE_PROGRAM_GROUPS;