[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_next_progress_update_date IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_action_set_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_task_weight_basis_code IN VARCHAR2 := 'DURATION'
,x_structure_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_count NUMBER;
select split_cost_from_workplan_flag, SPLIT_COST_FROM_BILL_FLAG
from pa_projects_all
where project_id = p_project_id;
select structure_type_id, structure_type_class_code
from pa_structure_types
where (structure_type_class_code = 'WORKPLAN' and 'Y' = p_workplan)
or (structure_type_class_code = 'FINANCIAL' and 'Y' = p_financial)
or (structure_type_class_code = 'DELIVERABLE' and 'Y' = p_deliverable);
select PA_TASKS_S.NEXTVAL into l_proj_element_id from sys.dual;
PA_PROJ_ELEMENTS_PKG.insert_row(
X_ROW_ID => l_rowid
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_ELEMENT_NUMBER => to_char(l_proj_element_id)
,X_NAME => p_structure_name
,X_DESCRIPTION => l_structure_description--rtarway,3655698
,X_STATUS_CODE => NULL
,X_WF_STATUS_CODE => NULL
,X_PM_PRODUCT_CODE => NULL
,X_PM_TASK_REFERENCE => NULL
,X_CLOSED_DATE => NULL
,X_LOCATION_ID => NULL
,X_MANAGER_PERSON_ID => NULL
,X_CARRYING_OUT_ORGANIZATION_ID => NULL
,X_TYPE_ID => NULL
,X_PRIORITY_CODE => NULL
,X_INC_PROJ_PROGRESS_FLAG => 'N'
,X_REQUEST_ID => NULL
,X_PROGRAM_APPLICATION_ID => NULL
,X_PROGRAM_ID => NULL
,X_PROGRAM_UPDATE_DATE => NULL
,X_LINK_TASK_FLAG => 'N'
,X_ATTRIBUTE_CATEGORY => l_attribute_category
,X_ATTRIBUTE1 => l_attribute1
,X_ATTRIBUTE2 => l_attribute2
,X_ATTRIBUTE3 => l_attribute3
,X_ATTRIBUTE4 => l_attribute4
,X_ATTRIBUTE5 => l_attribute5
,X_ATTRIBUTE6 => l_attribute6
,X_ATTRIBUTE7 => l_attribute7
,X_ATTRIBUTE8 => l_attribute8
,X_ATTRIBUTE9 => l_attribute9
,X_ATTRIBUTE10 => l_attribute10
,X_ATTRIBUTE11 => l_attribute11
,X_ATTRIBUTE12 => l_attribute12
,X_ATTRIBUTE13 => l_attribute13
,X_ATTRIBUTE14 => l_attribute14
,X_ATTRIBUTE15 => l_attribute15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => NULL
,X_UOM_CODE => NULL
,X_WQ_ACTUAL_ENTRY_CODE => NULL
,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
,X_PARENT_STRUCTURE_ID => NULL
,X_PHASE_CODE => NULL
,X_PHASE_VERSION_ID => NULL
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
,p_next_progress_update_date => p_next_progress_update_date
,p_action_set_id => p_action_set_id
,p_task_weight_basis_code => p_task_weight_basis_code
,p_structure_type => l_structure_type -- Amit
,x_proj_progress_attr_id => l_proj_prog_attr_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,p_next_progress_update_date => p_next_progress_update_date
,p_action_set_id => p_action_set_id
,p_task_weight_basis_code => p_task_weight_basis_code
,p_structure_type => l_structure_type -- Amit
,x_proj_progress_attr_id => l_proj_prog_attr_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
, X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
, X_PROJ_ELEMENT_ID => l_proj_element_id
, X_STRUCTURE_TYPE_ID => l_structure_type_id
, X_RECORD_VERSION_NUMBER => 1
, X_ATTRIBUTE_CATEGORY => NULL
, X_ATTRIBUTE1 => NULL
, X_ATTRIBUTE2 => NULL
, X_ATTRIBUTE3 => NULL
, X_ATTRIBUTE4 => NULL
, X_ATTRIBUTE5 => NULL
, X_ATTRIBUTE6 => NULL
, X_ATTRIBUTE7 => NULL
, X_ATTRIBUTE8 => NULL
, X_ATTRIBUTE9 => NULL
, X_ATTRIBUTE10 => NULL
, X_ATTRIBUTE11 => NULL
, X_ATTRIBUTE12 => NULL
, X_ATTRIBUTE13 => NULL
, X_ATTRIBUTE14 => NULL
, X_ATTRIBUTE15 => NULL
);
CURSOR getid is select project_id from pa_proj_elements
where proj_element_id = p_structure_id;
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
PA_PROJ_ELEMENT_VERSIONS_PKG.INSERT_ROW(
X_ROW_ID => l_rowid
--,X_ELEMENT_VERSION_ID => x_structure_version_id * Commenmted for Bug Fix: 4537865
,X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for bug bug Fix: 4537865
,X_PROJ_ELEMENT_ID => p_structure_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => l_project_id
,X_PARENT_STRUCTURE_VERSION_ID => x_structure_version_id
,X_DISPLAY_SEQUENCE => NULL
,X_WBS_LEVEL => NULL
,X_WBS_NUMBER => '0'
,X_ATTRIBUTE_CATEGORY => l_attribute_category
,X_ATTRIBUTE1 => l_attribute1
,X_ATTRIBUTE2 => l_attribute2
,X_ATTRIBUTE3 => l_attribute3
,X_ATTRIBUTE4 => l_attribute4
,X_ATTRIBUTE5 => l_attribute5
,X_ATTRIBUTE6 => l_attribute6
,X_ATTRIBUTE7 => l_attribute7
,X_ATTRIBUTE8 => l_attribute8
,X_ATTRIBUTE9 => l_attribute9
,X_ATTRIBUTE10 => l_attribute10
,X_ATTRIBUTE11 => l_attribute11
,X_ATTRIBUTE12 => l_attribute12
,X_ATTRIBUTE13 => l_attribute13
,X_ATTRIBUTE14 => l_attribute14
,X_ATTRIBUTE15 => l_attribute15
,X_TASK_UNPUB_VER_STATUS_CODE => NULL
,X_SOURCE_OBJECT_ID => l_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
select element_version_id into l_dummy from pa_proj_element_versions where element_version_id = x_structure_version_id;
select p.person_id
from per_all_people_f p, fnd_user f
where f.employee_id = p.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and f.user_id = p_user_id;
cursor getids is select project_id, proj_element_id from pa_proj_element_versions
where element_version_id = p_structure_version_id;
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code = 'STRUCTURE_PUBLISHED';
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code <> 'STRUCTURE_PUBLISHED';
select '1'
from pa_proj_structure_types p, pa_structure_types s
where s.structure_type_class_code IN ('FINANCIAL')
and s.structure_type_id = p.structure_type_id
and p.proj_element_id = c_structure_id;
select '1'
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_structure_id
and status_code <> 'STRUCTURE_PUBLISHED';
update pa_proj_elem_ver_structure
set latest_eff_published_flag = 'N',
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and latest_eff_published_flag = 'Y';
update pa_proj_elem_ver_structure
set current_flag = 'N',
current_baseline_date = NULL,
current_baseline_person_id = NULL,
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and current_flag = 'Y';
update pa_proj_elem_ver_structure
set original_flag = 'N',
original_baseline_date = NULL,
original_baseline_person_id = NULL,
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and original_flag = 'Y';
update pa_proj_elem_ver_structure
set latest_eff_published_flag = 'N',
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and latest_eff_published_flag = 'Y';
update pa_proj_elem_ver_structure
set current_flag = 'N',
current_baseline_date = NULL,
current_baseline_person_id = NULL,
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and current_flag = 'Y';
PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
X_ROWID => l_rowid
, X_PEV_STRUCTURE_ID => x_pev_structure_id
, X_ELEMENT_VERSION_ID => p_structure_version_id
, X_VERSION_NUMBER => l_struc_ver_number
, X_NAME => p_structure_version_name
, X_PROJECT_ID => l_project_id
, X_PROJ_ELEMENT_ID => l_proj_element_id
, X_DESCRIPTION => l_structure_version_desc -- rtarway, 3655698
, X_EFFECTIVE_DATE => l_effective_date
, X_PUBLISHED_DATE => l_published_date
, X_PUBLISHED_BY => l_published_person_id
, X_CURRENT_BASELINE_DATE => l_current_baseline_date
, X_CURRENT_BASELINE_FLAG => l_current_flag
, X_CURRENT_BASELINE_BY => l_cur_baseline_person_id
, X_ORIGINAL_BASELINE_DATE => l_original_baseline_date
, X_ORIGINAL_BASELINE_FLAG => l_original_flag
, X_ORIGINAL_BASELINE_BY => l_orig_baseline_person_id
, X_LOCK_STATUS_CODE => NULL
, X_LOCKED_BY => NULL
, X_LOCKED_DATE => NULL
, X_STATUS_CODE => l_status_code
, X_WF_STATUS_CODE => NULL
, X_LATEST_EFF_PUBLISHED_FLAG => l_latest_eff_published_flag
, X_CHANGE_REASON_CODE => l_change_reason_code
, X_RECORD_VERSION_NUMBER => 1
, X_CURRENT_WORKING_FLAG => l_current_working_ver_flag --FPM bug 3301192
, X_SOURCE_OBJECT_ID => l_project_id
, X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG(
p_project_id => l_project_id,
p_structure_version_id => p_structure_version_id,
p_update_wbs_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
procedure Update_Structure
(
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_structure_id IN NUMBER
,p_structure_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_structure_name IN VARCHAR2
,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,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_rowid VARCHAR2(255);
select rowid,
project_id,
element_number,
name,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from pa_proj_elements
where proj_element_id = p_structure_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURE begin');
savepoint update_structure_pvt;
PA_PROJ_ELEMENTS_PKG.UPDATE_ROW(
X_ROW_ID => l_rowid
,X_PROJ_ELEMENT_ID => p_structure_id
,X_PROJECT_ID => l_project_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_ELEMENT_NUMBER => to_char(p_structure_id)
,X_NAME => l_name
,X_DESCRIPTION => l_description
,X_STATUS_CODE => NULL
,X_WF_STATUS_CODE => NULL
,X_PM_PRODUCT_CODE => NULL
,X_PM_TASK_REFERENCE => NULL
,X_CLOSED_DATE => NULL
,X_LOCATION_ID => NULL
,X_MANAGER_PERSON_ID => NULL
,X_CARRYING_OUT_ORGANIZATION_ID => NULL
,X_TYPE_ID => NULL
,X_PRIORITY_CODE => NULL
,X_INC_PROJ_PROGRESS_FLAG => NULL
,X_RECORD_VERSION_NUMBER => p_record_version_number
,X_REQUEST_ID => NULL
,X_PROGRAM_APPLICATION_ID => NULL
,X_PROGRAM_ID => NULL
,X_PROGRAM_UPDATE_DATE => NULL
,X_ATTRIBUTE_CATEGORY => l_attribute_category
,X_ATTRIBUTE1 => l_attribute1
,X_ATTRIBUTE2 => l_attribute2
,X_ATTRIBUTE3 => l_attribute3
,X_ATTRIBUTE4 => l_attribute4
,X_ATTRIBUTE5 => l_attribute5
,X_ATTRIBUTE6 => l_attribute6
,X_ATTRIBUTE7 => l_attribute7
,X_ATTRIBUTE8 => l_attribute8
,X_ATTRIBUTE9 => l_attribute9
,X_ATTRIBUTE10 => l_attribute10
,X_ATTRIBUTE11 => l_attribute11
,X_ATTRIBUTE12 => l_attribute12
,X_ATTRIBUTE13 => l_attribute13
,X_ATTRIBUTE14 => l_attribute14
,X_ATTRIBUTE15 => l_attribute15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => NULL
,X_UOM_CODE => NULL
,X_WQ_ACTUAL_ENTRY_CODE => NULL
,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
,X_PARENT_STRUCTURE_ID => NULL
,X_PHASE_CODE => NULL
,X_PHASE_VERSION_ID => NULL
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURE end');
rollback to update_structure_pvt;
rollback to update_structure_pvt;
p_procedure_name => 'UPDATE_STRUCTURE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_structure;
procedure Update_Structure_Version_Attr
(
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_pev_structure_id IN NUMBER
,p_structure_version_name IN VARCHAR2
,p_structure_version_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_latest_eff_published_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_locked_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_struct_version_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_baseline_current_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_baseline_original_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_record_version_number IN NUMBER
--FP M changes bug 3301192
,p_current_working_ver_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--end FP M changes bug 3301192
,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_rowid VARCHAR2(255);
select p.person_id
from per_all_people_f p, fnd_user f
where f.employee_id = p.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and f.user_id = p_user_id;
cursor getids is select rowid,
element_version_id,
version_number,
name,
project_id,
proj_element_id,
description,
effective_date,
published_date,
published_by_person_id,
CURRENT_BASELINE_DATE,
CURRENT_FLAG,
CURRENT_BASELINE_PERSON_ID,
ORIGINAL_BASELINE_DATE,
ORIGINAL_FLAG,
ORIGINAL_BASELINE_PERSON_ID,
LOCK_STATUS_CODE,
LOCKED_BY_PERSON_ID,
LOCKED_DATE,
STATUS_CODE,
WF_STATUS_CODE,
LATEST_EFF_PUBLISHED_FLAG,
PM_SOURCE_CODE,
PM_SOURCE_REFERENCE,
CHANGE_REASON_CODE,
CURRENT_WORKING_FLAG
from pa_proj_elem_ver_structure
where pev_structure_id = p_pev_structure_id;
is select por.object_relationship_id, por.record_version_number
, por2.object_id_from1, por.object_id_from2, por.comments, ppa.name -- Bug # 4556844.
from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs
, pa_projects_all ppa, pa_object_relationships por2 -- Bug # 4556844.
where ppevs.element_version_id = ppev.parent_structure_version_id
and ppevs.project_id = ppev.project_id
and ppev.element_version_id = por.object_id_to1
and ppev.project_id = por.object_id_to2
and ppa.project_id = ppev.project_id -- Bug # 4556844.
and por.relationship_type = 'LW'
and ppevs.element_version_id <> c_structure_version_id
and ppevs.project_id = c_project_id
and ppevs.status_code = 'STRUCTURE_WORKING'
and por2.object_id_to1 = por.object_id_from1 -- Bug # 4556844.
and por2.object_type_from in ('PA_STRUCTURES','PA_TASKS') --Bug 6429275
and por2.object_type_to = 'PA_TASKS' -- Bug 6429275
and por2.relationship_type = 'S'; -- Bug # 4556844.
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURE_VERSION_ATTR begin');
savepoint UPDATE_STRUC_VER_ATTR_PVT;
select rowid into l_rowid
from pa_proj_elem_ver_structure
where pev_structure_id = p_pev_structure_id
and record_version_number = p_record_version_number
for update NOWAIT;
update pa_proj_elem_ver_structure
set current_flag = 'N',
current_baseline_date = NULL,
current_baseline_person_id = NULL,
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and pev_structure_id <> p_pev_structure_id
and current_flag = 'Y';
update pa_proj_elem_ver_structure
set original_flag = 'N',
original_baseline_date = NULL,
original_baseline_person_id = NULL,
record_version_number = record_version_number + 1
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and pev_structure_id <> p_pev_structure_id
and original_flag = 'Y';
PA_PROJ_ELEM_VER_STRUCTURE_PKG.update_row(
X_ROWID => l_rowid
, X_PEV_STRUCTURE_ID => p_pev_structure_id
, X_ELEMENT_VERSION_ID => l_structure_version_id
, X_VERSION_NUMBER => l_version_number
, X_NAME => l_name
, X_PROJECT_ID => l_project_id
, X_PROJ_ELEMENT_ID => l_proj_element_id
, X_DESCRIPTION => l_description
, X_EFFECTIVE_DATE => l_effective_date
, X_PUBLISHED_DATE => l_published_date
, X_PUBLISHED_BY => l_published_by_person_id
, X_CURRENT_BASELINE_DATE => l_current_baseline_date
, X_CURRENT_BASELINE_FLAG => l_current_flag
, X_CURRENT_BASELINE_BY => l_current_baseline_person_id
, X_ORIGINAL_BASELINE_DATE => l_original_baseline_date
, X_ORIGINAL_BASELINE_FLAG => l_original_flag
, X_ORIGINAL_BASELINE_BY => l_original_baseline_person_id
, X_LOCK_STATUS_CODE => l_lock_status_code
, X_LOCKED_BY => l_locked_by_person_id
, X_LOCKED_DATE => l_locked_date
, X_STATUS_CODE => l_status_code
, X_WF_STATUS_CODE => l_wf_status_code
, X_LATEST_EFF_PUBLISHED_FLAG => l_latest_eff_published_flag
, X_CHANGE_REASON_CODE => l_change_reason_code
, X_RECORD_VERSION_NUMBER => p_record_version_number
, X_CURRENT_WORKING_FLAG => l_current_working_ver_flag --FPM bug 3301192
);
UPDATE pa_proj_elem_ver_structure
SET current_working_flag = 'N'
where project_id = l_project_id
and proj_element_id = l_proj_element_id
and pev_structure_id <> p_pev_structure_id
and current_working_flag = 'Y';
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_cur_rel_ids_rec.object_relationship_id
,p_record_version_number => l_cur_rel_ids_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('PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURE_VERSION_ATTR end');
rollback to UPDATE_STRUC_VER_ATTR_PVT;
ROLLBACK to UPDATE_STRUC_VER_ATTR_PVT;
ROLLBACK to UPDATE_STRUC_VER_ATTR_PVT;
p_procedure_name => 'Update_Structure_Version_Attr',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Structure_Version_Attr;
procedure Delete_Structure
(
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_structure_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_rowid VARCHAR2(255);
select rowid
from pa_proj_structure_types
where proj_element_id = p_structure_id;
select ppa.project_id, ppa.record_version_number
from pa_projects_all ppa, pa_proj_elements ppe
where ppa.project_id = ppe.project_id
and ppe.proj_element_id = c_structure_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE begin');
savepoint delete_structure_pvt;
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => null
,p_finish_date => null
,p_record_version_number => l_rec_ver_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
select rowid into l_rowid
from pa_proj_elements
where proj_element_id = p_structure_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
select rowid into l_rowid
from pa_proj_elements
where proj_element_id = p_structure_id
and record_version_number = p_record_version_number;
PA_PROJ_ELEMENTS_PKG.DELETE_ROW(
X_ROW_ID => l_rowid
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE end');
rollback to delete_structure_pvt;
rollback to delete_structure_pvt;
p_procedure_name => 'Delete_Structure',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Structure;
procedure Delete_Structure_Version
(
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_structure_version_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_rowid VARCHAR2(255);
select por.object_relationship_id, por.record_version_number
from pa_object_relationships por
where por.object_id_to1 = p_str_ver_id
and por.relationship_type in ('LW', 'LF');
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE_VERSION begin');
savepoint delete_structure_version_pvt;
select rowid into l_rowid
from pa_proj_element_versions
where element_version_id = p_structure_version_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
select rowid into l_rowid
from pa_proj_element_versions
where element_version_id = p_structure_version_id
and record_version_number = p_record_version_number;
/*moved PA_FIN_PLAN_PVT.delete_wp_budget_versions into plsql block */
BEGIN
PA_FIN_PLAN_PVT.delete_wp_budget_versions
(
p_struct_elem_version_id_tbl => l_struct_version_id_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data
);
p_procedure_name => 'Delete_Structure_Version',
p_error_text => SUBSTRB('PA_FIN_PLAN_PVT.delete_wp_budget_versions:'||SQLERRM,1,240));
SELECT project_id INTO l_Project_ID
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id and rownum < 2;
PA_PROGRESS_PUB.delete_working_wp_progress (
P_Project_ID => l_Project_ID
,P_Structure_Version_ID => P_Structure_Version_ID
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data
);
p_procedure_name => 'Delete_Structure_Version',
p_error_text => SUBSTRB('PA_PROGRESS_PUB.delete_working_wp_progress:'||SQLERRM,1,240));
pa_relationship_pub.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 => l_rec_rel_id.object_relationship_id
,p_record_version_number => l_rec_rel_id.record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
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_rec_rel_id.object_relationship_id
,p_record_version_number => l_rec_rel_id.record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW(
X_ROW_ID => l_rowid
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE_VERSION end');
rollback to delete_structure_version_pvt;
rollback to delete_structure_version_pvt;
p_procedure_name => 'Delete_Structure_Version',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_STRUCTURE_VERSION;
procedure Delete_Structure_Version_Attr
(
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_pev_structure_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_rowid VARCHAR2(255);
SELECT proj_element_id, project_id
FROM pa_proj_elem_ver_structure
WHERE pev_structure_id = p_pev_structure_id;
select str.element_version_id
from pa_proj_elem_ver_structure str,
pa_proj_elements ppe
where ppe.proj_element_id = p_structure_id
and ppe.project_id = str.project_id
and ppe.proj_element_id = str.proj_element_id
and str.status_code = 'STRUCTURE_WORKING'
and str.current_working_flag = 'N'
order by str.last_update_date desc;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE_VERSION_ATTR begin');
savepoint delete_structure_ver_attr_pvt;
select rowid into l_rowid
from pa_proj_elem_ver_structure
where pev_structure_id = p_pev_structure_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
select rowid into l_rowid
from pa_proj_elem_ver_structure
where pev_structure_id = p_pev_structure_id
and record_version_number = p_record_version_number;
UPDATE pa_proj_elem_ver_structure
SET current_working_flag = 'Y'
WHERE element_version_id = l_current_working_ver_id
AND project_id = l_project_id;
PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_row(
X_ROWID => l_rowid
);
update PA_PROJ_ELEM_VER_STRUCTURE
set PURGED_FLAG = 'Y',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
conc_request_id = FND_GLOBAL.CONC_REQUEST_ID /* Added for bug 9049425 */
WHERE ROWID = l_rowid;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE_VERSION_ATTR end');
rollback to delete_structure_ver_attr_pvt;
rollback to delete_structure_ver_attr_pvt;
p_procedure_name => 'Delete_Structure',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Structure_Version_Attr;
SELECT scheduled_start_date, scheduled_finish_date
FROM pa_proj_elem_ver_schedule
WHERE project_id = c_project_id
AND element_version_id = c_structure_version_id;
SELECT record_version_number
FROM pa_projects_all
WHERE project_id = c_project_id;
select object_relationship_id, object_id_from1 object_id_from,
object_type_from, record_version_number
from pa_object_relationships
where relationship_type = 'L'
and object_id_to1 = c_element_version_id
and object_type_to IN ('PA_TASKS','PA_STRUCTURES');
select object_relationship_id, object_id_to1 object_id_to, object_id_to2,
object_type_to, record_version_number
from pa_object_relationships
where relationship_type IN ('LW', 'LF')
and object_id_from1 = c_element_version_id
and object_type_from IN ('PA_TASKS','PA_STRUCTURES');
select v1.project_id project_id, v2.proj_element_id structure_id,
v1.parent_structure_version_id structure_version_id,
v1.element_version_id task_version_id
from pa_proj_element_versions v1,
pa_proj_element_versions v2
where v1.element_version_id = c_task_version_id
and v1.parent_structure_version_id = v2.element_version_id;
select v1.project_id project_id, v1.proj_element_id structure_id,
v1.element_version_id structure_version_id
from pa_proj_element_versions v1
where v1.element_version_id = c_structure_version_id;
select pevs.name, pevs.project_id, pevs.proj_element_id,
pevs.element_version_id, pevs.pev_structure_id
from pa_proj_elem_ver_structure pevs,
pa_proj_element_versions pev
where pev.element_version_id = p_structure_version_id
and pevs.project_id = pev.project_id
and pevs.element_version_id = pev.element_version_id;
SELECT *
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = c_structure_version_id;
SELECT *
FROM PA_PROJ_ELEM_VER_STRUCTURE
WHERE ELEMENT_VERSION_ID = c_structure_version_id
AND project_id = c_project_id;
SELECT *
FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE element_version_id = c_element_version_id
AND project_id = c_project_id;
SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
a.project_id, b.object_id_from1 parent_element_version_id,
a.TASK_UNPUB_VER_STATUS_CODE, a.parent_structure_version_id
FROM PA_PROJ_ELEMENT_VERSIONS a,
PA_OBJECT_RELATIONSHIPS b
WHERE a.object_type = 'PA_TASKS'
AND a.parent_structure_version_id = c_structure_version_id
AND a.element_version_id = b.object_id_to1
AND b.relationship_type = 'S'
ORDER BY a.display_sequence;
select a.element_version_id
from pa_proj_element_versions a,
pa_proj_elements b
where a.proj_element_id = b.proj_element_id
and a.parent_structure_version_id = p_structure_version_id
and b.link_task_flag = 'Y';
select b.element_version_id, b.record_version_number
from pa_proj_element_versions a,
pa_proj_element_versions b,
pa_proj_elem_ver_structure c
where a.element_version_id = c_keep_struc_ver_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.element_version_id <> c_keep_struc_ver_id
and b.object_type = 'PA_STRUCTURES'
and b.project_id = c.project_id
and b.element_version_id = c.element_version_id
and c.status_code <> 'STRUCTURE_PUBLISHED';
select weighting_percentage
from pa_object_relationships
where object_id_to1 = p_task_ver_id
and object_type_to = 'PA_TASKS'
and object_type_from in ('PA_STRUCTURES','PA_TASKS')
and object_type_to = 'PA_TASKS' -- Bug 6429275
and relationship_type = 'S';
select parent_structure_version_id, element_version_id,
record_version_number
from pa_proj_element_versions
where element_version_id = c_task_ver_id;
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_task_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT '1'
from pa_object_relationships
where object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
and object_id_from1 = c_elem_ver_id
and relationship_type = 'S';
select element_version_id
from pa_proj_element_versions
where parent_structure_version_id = c_new_struc_ver_id
and object_type = 'PA_TASKS';
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
SELECT proj_element_id
FROM pa_proj_elem_ver_structure
WHERE element_version_id = c_structure_version_id;
select project_id
into l_project_id
from pa_proj_element_versions
where element_version_id = p_structure_version_id;
select meaning
into l_prefix
from pa_lookups
where lookup_type = 'PA_STRUCTURES_PREFIX'
and lookup_code = 'PA_PREFIX_COPY';
update PA_PROJ_ELEM_VER_STRUCTURE
set name = l_dummy_name,
current_working_flag = 'Y'
where pev_structure_id = l_pev_structure_id;
PA_PROJ_ELEMENT_VERSIONS_PKG.INSERT_ROW(
X_ROW_ID => l_rowid
--,X_ELEMENT_VERSION_ID => l_new_struct_ver_id * commented for Bug Fix: 4537865
,X_ELEMENT_VERSION_ID => l_tmp_struct_ver_id -- added for Bug fix: 4537865
,X_PROJ_ELEMENT_ID => l_struc_ver_rec.proj_element_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => l_struc_ver_rec.project_id
,X_PARENT_STRUCTURE_VERSION_ID => l_new_struct_ver_id
,X_DISPLAY_SEQUENCE => NULL
,X_WBS_LEVEL => NULL
,X_WBS_NUMBER => '0'
,X_ATTRIBUTE_CATEGORY => l_struc_ver_rec.attribute_category
,X_ATTRIBUTE1 => l_struc_ver_rec.attribute1
,X_ATTRIBUTE2 => l_struc_ver_rec.attribute2
,X_ATTRIBUTE3 => l_struc_ver_rec.attribute3
,X_ATTRIBUTE4 => l_struc_ver_rec.attribute4
,X_ATTRIBUTE5 => l_struc_ver_rec.attribute5
,X_ATTRIBUTE6 => l_struc_ver_rec.attribute6
,X_ATTRIBUTE7 => l_struc_ver_rec.attribute7
,X_ATTRIBUTE8 => l_struc_ver_rec.attribute8
,X_ATTRIBUTE9 => l_struc_ver_rec.attribute9
,X_ATTRIBUTE10 => l_struc_ver_rec.attribute10
,X_ATTRIBUTE11 => l_struc_ver_rec.attribute11
,X_ATTRIBUTE12 => l_struc_ver_rec.attribute12
,X_ATTRIBUTE13 => l_struc_ver_rec.attribute13
,X_ATTRIBUTE14 => l_struc_ver_rec.attribute14
,X_ATTRIBUTE15 => l_struc_ver_rec.element_version_id
,X_TASK_UNPUB_VER_STATUS_CODE => NULL
,X_SOURCE_OBJECT_ID => l_struc_ver_rec.project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
/* This API insert into planning txn table if wp. Call table hander instead
--Call Create Structure Version
PA_PROJECT_STRUCTURE_PVT1.Create_Structure_Version
( p_validate_only => p_validate_only
,p_structure_id => l_struc_ver_rec.proj_element_id
,p_attribute_category => l_struc_ver_rec.attribute_category
,p_attribute1 => l_struc_ver_rec.attribute1
,p_attribute2 => l_struc_ver_rec.attribute2
,p_attribute3 => l_struc_ver_rec.attribute3
,p_attribute4 => l_struc_ver_rec.attribute4
,p_attribute5 => l_struc_ver_rec.attribute5
,p_attribute6 => l_struc_ver_rec.attribute6
,p_attribute7 => l_struc_ver_rec.attribute7
,p_attribute8 => l_struc_ver_rec.attribute8
,p_attribute9 => l_struc_ver_rec.attribute9
,p_attribute10 => l_struc_ver_rec.attribute10
,p_attribute11 => l_struc_ver_rec.attribute11
,p_attribute12 => l_struc_ver_rec.attribute12
,p_attribute13 => l_struc_ver_rec.attribute13
,p_attribute14 => l_struc_ver_rec.attribute14
,p_attribute15 => l_struc_ver_rec.element_version_id --for performacnce to be used later by new structure version
,x_structure_version_id => l_new_struct_ver_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
l_src_tasks_versions_tbl.delete;
select a.proj_element_id, b.object_id_from1
into l_parent_id, l_parent_ver_id
from pa_proj_element_versions a,
pa_object_relationships b
where a.element_version_id = b.object_id_from1
and a.object_type = b.object_type_from
and relationship_type = 'S'
and object_id_to1 = l_task_versions_rec.element_version_id
and object_type_to = 'PA_TASKS';
IF (l_task_versions_rec.TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED') THEN
PA_PROJ_ELEMENTS_UTILS.Check_Del_all_task_Ver_Ok(
p_project_id => l_task_versions_rec.project_id
,p_task_version_id => l_task_versions_rec.element_version_id
,p_parent_structure_ver_id=> l_task_versions_rec.parent_structure_version_id
,x_return_status => l_return_status
,x_error_message_code => l_error_message_code );
UPDATE pa_proj_elements
set status_code = '128',
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where proj_element_id = l_task_versions_rec.proj_element_id;
update pa_proj_element_versions
set TASK_UNPUB_VER_STATUS_CODE = 'PUBLISHED'
where element_version_id = l_task_versions_rec.element_version_id;
PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk
(
p_task_element_id => l_task_versions_rec.proj_element_id
,p_project_id => l_task_versions_rec.project_id
,p_task_version_id => l_task_versions_rec.element_version_id
,p_delete_or_validate => 'D'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
update pa_proj_element_versions
set TASK_UNPUB_VER_STATUS_CODE = 'PUBLISHED'
where element_version_id = l_task_versions_rec.element_version_id;
/*--maansari --commenting out the following code and replacing it with bulk insert
IF (l_create_task_ver_flag = 'Y') THEN
--dbms_output.put_line('creating task '||l_task_versions_rec.proj_element_id||', '||l_task_versions_rec.element_version_id);
/* maansari commenting the following code and replacing it with bulk insert
l_new_pev_schedule_id := NULL;
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => l_new_pev_schedule_id
,X_ELEMENT_VERSION_ID => l_task_version_id
,X_PROJECT_ID => l_ver_sch_attr_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => l_ver_sch_attr_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_ver_sch_attr_rec.SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_ver_sch_attr_rec.SCHEDULED_FINISH_DATE
,X_OBLIGATION_START_DATE => l_ver_sch_attr_rec.OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_ver_sch_attr_rec.OBLIGATION_FINISH_DATE
,X_ACTUAL_START_DATE => l_ver_sch_attr_rec.ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ver_sch_attr_rec.ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ver_sch_attr_rec.ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ver_sch_attr_rec.ESTIMATED_FINISH_DATE
,X_DURATION => l_ver_sch_attr_rec.DURATION
,X_EARLY_START_DATE => l_ver_sch_attr_rec.EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_ver_sch_attr_rec.EARLY_FINISH_DATE
,X_LATE_START_DATE => l_ver_sch_attr_rec.LATE_START_DATE
,X_LATE_FINISH_DATE => l_ver_sch_attr_rec.LATE_FINISH_DATE
,X_CALENDAR_ID => l_ver_sch_attr_rec.CALENDAR_ID
,X_MILESTONE_FLAG => l_ver_sch_attr_rec.MILESTONE_FLAG
,X_CRITICAL_FLAG => l_ver_sch_attr_rec.CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_ver_sch_attr_rec.wq_planned_quantity
,X_PLANNED_EFFORT => l_ver_sch_attr_rec.planned_effort
,X_ACTUAL_DURATION => l_ver_sch_attr_rec.actual_duration
,X_ESTIMATED_DURATION => l_ver_sch_attr_rec.estimated_duration
,X_ATTRIBUTE_CATEGORY => l_ver_sch_attr_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ver_sch_attr_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => l_ver_sch_attr_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => l_ver_sch_attr_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => l_ver_sch_attr_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => l_ver_sch_attr_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => l_ver_sch_attr_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => l_ver_sch_attr_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => l_ver_sch_attr_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => l_ver_sch_attr_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => l_ver_sch_attr_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => l_ver_sch_attr_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => l_ver_sch_attr_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => l_ver_sch_attr_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => l_ver_sch_attr_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => l_ver_sch_attr_rec.ATTRIBUTE15
,X_SOURCE_OBJECT_ID => l_ver_sch_attr_rec.PROJECT_ID
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_TASK_PVT1.Delete_Task_Ver_wo_val(
p_structure_version_id => l_tbd_tasks_info_rec.parent_structure_version_id
,p_task_version_id => l_tbd_tasks_info_rec.element_version_id
,p_record_version_number => l_tbd_tasks_info_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => l_new_pev_schedule_id
,X_ELEMENT_VERSION_ID => l_new_struct_ver_id
,X_PROJECT_ID => l_ver_sch_attr_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => l_ver_sch_attr_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_ver_sch_attr_rec.SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_ver_sch_attr_rec.SCHEDULED_FINISH_DATE
,X_OBLIGATION_START_DATE => l_ver_sch_attr_rec.OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_ver_sch_attr_rec.OBLIGATION_FINISH_DATE
,X_ACTUAL_START_DATE => l_ver_sch_attr_rec.ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ver_sch_attr_rec.ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ver_sch_attr_rec.ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ver_sch_attr_rec.ESTIMATED_FINISH_DATE
,X_DURATION => l_ver_sch_attr_rec.DURATION
,X_EARLY_START_DATE => l_ver_sch_attr_rec.EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_ver_sch_attr_rec.EARLY_FINISH_DATE
,X_LATE_START_DATE => l_ver_sch_attr_rec.LATE_START_DATE
,X_LATE_FINISH_DATE => l_ver_sch_attr_rec.LATE_FINISH_DATE
,X_CALENDAR_ID => l_ver_sch_attr_rec.CALENDAR_ID
,X_MILESTONE_FLAG => l_ver_sch_attr_rec.MILESTONE_FLAG
,X_CRITICAL_FLAG => l_ver_sch_attr_rec.CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_ver_sch_attr_rec.wq_planned_quantity
,X_PLANNED_EFFORT => l_ver_sch_attr_rec.planned_effort
,X_ACTUAL_DURATION => l_ver_sch_attr_rec.actual_duration
,X_ESTIMATED_DURATION => l_ver_sch_attr_rec.estimated_duration
,X_ATTRIBUTE_CATEGORY => l_ver_sch_attr_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ver_sch_attr_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => l_ver_sch_attr_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => l_ver_sch_attr_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => l_ver_sch_attr_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => l_ver_sch_attr_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => l_ver_sch_attr_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => l_ver_sch_attr_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => l_ver_sch_attr_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => l_ver_sch_attr_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => l_ver_sch_attr_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => l_ver_sch_attr_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => l_ver_sch_attr_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => l_ver_sch_attr_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => l_ver_sch_attr_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => l_ver_sch_attr_rec.ATTRIBUTE15
,X_SOURCE_OBJECT_ID => l_ver_sch_attr_rec.PROJECT_ID
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,attribute15 --this column is used to store structure ver id of the source str to be used to created relationships.
,source_object_id
,source_object_type
,financial_task_flag
)
SELECT
pa_proj_element_versions_s.nextval
,ppev.proj_element_id
,ppev.object_type
,l_project_id
,l_new_struct_ver_id
,PA_STRUCT_UPGR_PUB.get_disp_sequence(ppev.display_sequence)
,ppev.WBS_LEVEL
,PA_STRUCT_UPGR_PUB.get_wbs_number(ppev.WBS_LEVEL, NULL) -- Bug No. 4049574
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppev.RECORD_VERSION_NUMBER
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,ppev.element_version_id
,l_project_id
,'PA_PROJECTS'
,ppev.financial_task_flag
FROM ( SELECT * from pa_proj_element_versions ppev2
--,pa_proj_elements ppe --bug 4573340 commenting out this for bug 4578813
WHERE --bug#3094283 ppev2.project_id = l_project_id
ppev2.parent_structure_version_id = p_structure_version_id
and ppev2.object_type = 'PA_TASKS'
/*
--bug 4573340
and ppe.project_id = ppev2.project_id
and ppe.proj_element_id = ppev2.proj_element_id
and ppe.link_task_flag = 'N'
--bug 4573340
*/
and PA_PROJECT_STRUCTURE_PVT1.copy_task_version( p_structure_version_id,
ppev2.element_version_id ) = 'Y'
order by ppev2.display_sequence ) ppev
;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
SELECT /*+ USE_HASH(ppev2 ppev1)*/
pa_object_relationships_s.nextval,
pobj.object_type_from,
ppev1.element_version_id,
pobj.object_type_to,
ppev2.element_version_id,
pobj.relationship_type,
pobj.relationship_subtype,
pobj.Record_Version_Number,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
pobj.weighting_percentage
FROM ( SELECT object_type_from, object_id_from1,
object_type_to, object_id_to1,
relationship_type, relationship_subtype,
Record_Version_Number, weighting_percentage
FROM pa_object_relationships
--bug#3094283 WHERE RELATIONSHIP_TYPE = 'S'
start with object_id_from1 = p_structure_version_id
and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
connect by object_id_from1 = prior object_id_to1
and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE
--bug#3094283 ppev1.project_id = l_project_id
ppev1.attribute15 = pobj.object_id_from1
--bug#3094283 AND ppev2.project_id = l_project_id
AND ppev2.attribute15 = pobj.object_id_to1
and ppev1.parent_structure_version_id = l_new_struct_ver_id
and ppev2.parent_structure_version_id = l_new_struct_ver_id
;
INSERT INTO pa_proj_elem_ver_schedule(
PEV_SCHEDULE_ID
,ELEMENT_VERSION_ID
,PROJECT_ID
,PROJ_ELEMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SCHEDULED_START_DATE
,SCHEDULED_FINISH_DATE
,OBLIGATION_START_DATE
,OBLIGATION_FINISH_DATE
,ACTUAL_START_DATE
,ACTUAL_FINISH_DATE
,ESTIMATED_START_DATE
,ESTIMATED_FINISH_DATE
,DURATION
,EARLY_START_DATE
,EARLY_FINISH_DATE
,LATE_START_DATE
,LATE_FINISH_DATE
,CALENDAR_ID
,MILESTONE_FLAG
,CRITICAL_FLAG
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,WQ_PLANNED_QUANTITY
,PLANNED_EFFORT
,ACTUAL_DURATION
,ESTIMATED_DURATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,source_object_id
,source_object_type
,CONSTRAINT_TYPE_CODE
,CONSTRAINT_DATE
,FREE_SLACK
,TOTAL_SLACK
,EFFORT_DRIVEN_FLAG
,LEVEL_ASSIGNMENTS_FLAG
,EXT_ACT_DURATION
,EXT_REMAIN_DURATION
,EXT_SCH_DURATION
,DEF_SCH_TOOL_TSK_TYPE_CODE -- 4295770 Added
)
SELECT
pa_proj_elem_ver_schedule_s.nextval
,ppev1.ELEMENT_VERSION_ID
,l_PROJECT_ID
,ppev1.PROJ_ELEMENT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,ppevs.SCHEDULED_START_DATE
,ppevs.SCHEDULED_FINISH_DATE
,ppevs.OBLIGATION_START_DATE
,ppevs.OBLIGATION_FINISH_DATE
,ppevs.ACTUAL_START_DATE
,ppevs.ACTUAL_FINISH_DATE
,ppevs.ESTIMATED_START_DATE
,ppevs.ESTIMATED_FINISH_DATE
,ppevs.DURATION
,ppevs.EARLY_START_DATE
,ppevs.EARLY_FINISH_DATE
,ppevs.LATE_START_DATE
,ppevs.LATE_FINISH_DATE
,ppevs.CALENDAR_ID
,ppevs.MILESTONE_FLAG
,ppevs.CRITICAL_FLAG
,ppevs.RECORD_VERSION_NUMBER
,l_login_id
,ppevs.WQ_PLANNED_QUANTITY
,ppevs.PLANNED_EFFORT
,ppevs.ACTUAL_DURATION
,ppevs.ESTIMATED_DURATION
,ppevs.ATTRIBUTE_CATEGORY
,ppevs.ATTRIBUTE1
,ppevs.ATTRIBUTE2
,ppevs.ATTRIBUTE3
,ppevs.ATTRIBUTE4
,ppevs.ATTRIBUTE5
,ppevs.ATTRIBUTE6
,ppevs.ATTRIBUTE7
,ppevs.ATTRIBUTE8
,ppevs.ATTRIBUTE9
,ppevs.ATTRIBUTE10
,ppevs.ATTRIBUTE11
,ppevs.ATTRIBUTE12
,ppevs.ATTRIBUTE13
,ppevs.ATTRIBUTE14
,ppevs.ATTRIBUTE15
,l_PROJECT_ID
,'PA_PROJECTS'
,ppevs.CONSTRAINT_TYPE_CODE
,ppevs.CONSTRAINT_DATE
,ppevs.FREE_SLACK
,ppevs.TOTAL_SLACK
,ppevs.EFFORT_DRIVEN_FLAG
,ppevs.LEVEL_ASSIGNMENTS_FLAG
,ppevs.EXT_ACT_DURATION
,ppevs.EXT_REMAIN_DURATION
,ppevs.EXT_SCH_DURATION
,ppevs.DEF_SCH_TOOL_TSK_TYPE_CODE -- 4295770 Added
FROM pa_proj_elem_ver_schedule ppevs,
pa_proj_element_versions ppev1
where ppev1.attribute15 = ppevs.element_version_id
and ppevs.project_id = l_project_id
and ppev1.project_id = l_project_id
and ppev1.parent_structure_version_id = l_new_struct_ver_id
and ppev1.object_type = 'PA_TASKS';
Select Element_Version_ID, ATTRIBUTE15 Bulk Collect
INTO l_New_Task_Versions_Tab, l_Old_Task_Versions_Tab
From pa_proj_element_versions
Where parent_structure_version_id = l_new_struct_ver_id
and object_type = 'PA_TASKS'
and PA_PROJECT_STRUCTURE_PVT1.copy_task_version( l_new_struct_ver_id, element_version_id ) = 'Y'
order by display_sequence;
update pa_proj_element_versions ppevs1
set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
where ppevs2.project_id = l_project_id
and parent_structure_version_id = p_structure_version_id
and ppevs2.element_version_id = ppevs1.attribute15
)
where project_id = l_project_id
and parent_structure_version_id = l_new_struct_ver_id;
update pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_WORKING',
LOCKED_BY_PERSON_ID = (select locked_by_person_id
from pa_proj_elem_ver_structure
where project_id = l_project_id
and element_version_id = p_structure_version_id),
LOCK_STATUS_CODE = 'LOCKED'
where project_id = l_project_id and element_version_id = l_new_struct_ver_id;
select start_date, completion_date
into l_proj_start_date, l_proj_completion_date
from pa_projects_all
where project_id = l_project_id;
update pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_PUBLISHED',
LOCKED_BY_PERSON_ID = NULL,
LOCK_STATUS_CODE = 'UNLOCKED'
where project_id = l_project_id and element_version_id = l_new_struct_ver_id;
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => l_scheduled_start_date
,p_finish_date => l_scheduled_finish_date
,p_record_version_number => l_proj_record_ver_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_TASK_PVT1.Delete_Task_Ver_wo_val(
p_structure_version_id => l_tbd_tasks_info_rec.parent_structure_version_id
,p_task_version_id => l_tbd_tasks_info_rec.element_version_id
,p_record_version_number => l_tbd_tasks_info_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
update PA_PROJ_ELEM_VER_STRUCTURE
set status_code = 'STRUCTURE_WORKING',
record_version_number = nvl(record_version_number,0)+1
where pev_structure_id = l_pev_structure_id;
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG(
p_project_id => l_project_id,
p_structure_version_id => p_structure_version_id,
p_update_wbs_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG(
p_project_id => l_project_id,
p_structure_version_id => l_new_struct_ver_id,
p_update_wbs_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
pa_debug.write('PA_PROJECT_STRUCTURE_PVT1.PUBLISH_STRUCTURE', 'Before calling PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_wo_val', 3);
PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_wo_val(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_del_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.write('PA_PROJECT_STRUCTURE_PVT1.PUBLISH_STRUCTURE', 'After calling PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_wo_val l_return_status='||l_return_status||' l_msg_count='||l_msg_count, 3);
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP(
p_calling_context => 'PUBLISH',
p_project_id => l_project_id,
-- p_structure_version_id => l_last_pub_str_ver_id, --SMukka Commented
p_structure_version_id => p_structure_version_id, --Smukka Added line
p_pub_struc_ver_id => l_new_struct_ver_id,
p_pub_prog_flag => p_pub_prog_flag,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
/* 4096218 Commenting , as we have changed the global varray name to G_DELETED_TASK_IDS_FROM_OP
and now we are expected to pass the varray of 'to be deleted' task projelementids ,
not version ids.
PA_PROJECT_PUB.G_DELETED_TASK_VER_IDS_FROM_OP := l_tbd_task_ver_id;
PA_PROJECT_PUB.G_DELETED_TASK_IDS_FROM_OP := l_tbd_task_id;
procedure UPDATE_LATEST_PUB_LINKS
(
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_orig_project_id IN NUMBER
,p_orig_structure_id IN NUMBER
,p_orig_struc_ver_id IN NUMBER
,p_orig_task_ver_id IN NUMBER
,p_new_project_id IN NUMBER
,p_new_structure_id IN NUMBER
,p_new_struc_ver_id IN NUMBER
,p_new_task_ver_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
cursor get_from_id(c_element_version_id NUMBER) IS
select object_relationship_id, object_id_from1 object_id_from,
object_type_from, record_version_number
from pa_object_relationships
where relationship_type = 'L'
and object_id_to1 = c_element_version_id
and object_type_to IN ('PA_TASKS','PA_STRUCTURES');
select v1.project_id project_id, v2.proj_element_id structure_id,
v1.parent_structure_version_id structure_version_id,
v1.element_version_id task_version_id
from pa_proj_element_versions v1,
pa_proj_element_versions v2
where v1.element_version_id = c_task_version_id
and v1.parent_structure_version_id = v2.element_version_id;
select v1.project_id project_id, v1.proj_element_id structure_id,
v1.element_version_id structure_version_id
from pa_proj_element_versions v1
where v1.element_version_id = c_structure_version_id;
select pevs.element_version_id
from pa_proj_element_versions pev,
pa_proj_elem_ver_structure pevs
where pev.element_version_id = c_struc_ver_id
and pev.project_id = pevs.project_id
and pev.proj_element_id = pevs.proj_element_id
and pevs.latest_eff_published_flag = 'Y';
select pev2.element_version_id task_version_id,
pev2.parent_structure_version_id parent_structure_version_id
from pa_proj_element_versions pev,
pa_proj_element_versions pev1,
pa_proj_elem_ver_structure pevs,
pa_proj_element_versions pev2
where pev.element_version_id = c_task_ver_id
and pev.parent_structure_version_id = pev1.element_version_id
and pev1.project_id = pevs.project_id
and pev1.proj_element_id = pevs.proj_element_id
and pevs.latest_eff_published_flag = 'Y'
and pev.proj_element_id = pev2.proj_element_id
and pev.project_id = pev2.project_id
and pev2.parent_structure_version_id = pevs.element_version_id;
cursor can_update(c_element_version_id NUMBER) IS
select '1'
from pa_proj_elem_ver_structure pevs,
pa_proj_element_versions pev
where pev.element_version_id = c_element_version_id
and pev.parent_structure_version_id = pevs.element_version_id
and pev.project_id = pevs.project_id
and pevs.status_code IN ('STRUCTURE_WORKING', 'STRUCTURE_REJECTED');
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_LATEST_PUB_LINKS begin');
savepoint update_latest_pub_links_pvt;
OPEN can_update(l_from_object_info.object_id_from);
FETCH can_update INTO l_dummy;
IF (can_update%FOUND) THEN
--dbms_output.put_line('can update found');
PA_RELATIONSHIP_PVT.Update_Relationship(
p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_debug_mode => p_debug_mode
,p_object_relationship_id => l_from_object_info.object_relationship_id
,p_project_id_from => l_info_struc_ver_rec.project_id
,p_structure_id_from => l_info_struc_ver_rec.structure_id
,p_structure_version_id_from => l_info_struc_ver_rec.structure_version_id
,p_task_version_id_from => NULL
,p_project_id_to => p_new_project_id
,p_structure_id_to => p_new_structure_id
,p_structure_version_id_to => p_new_struc_ver_id
,p_task_version_id_to => p_new_task_ver_id
,p_relationship_type => 'L'
,p_relationship_subtype => 'READ_WRITE'
,p_record_version_number => l_from_object_info.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_RELATIONSHIP_PVT.Update_Relationship(
p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_debug_mode => p_debug_mode
,p_object_relationship_id => l_from_object_info.object_relationship_id
,p_project_id_from => l_info_task_ver_rec.project_id
,p_structure_id_from => l_info_task_ver_rec.structure_id
,p_structure_version_id_from => l_info_task_ver_rec.structure_version_id
,p_task_version_id_from => l_info_task_ver_rec.task_version_id
,p_project_id_to => p_new_project_id
,p_structure_id_to => p_new_structure_id
,p_structure_version_id_to => p_new_struc_ver_id
,p_task_version_id_to => p_new_task_ver_id
,p_relationship_type => 'L'
,p_relationship_subtype => 'READ_WRITE'
,p_record_version_number => l_from_object_info.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
CLOSE can_update;
rollback to update_latest_pub_links_pvt;
rollback to update_latest_pub_links_pvt;
p_procedure_name => 'UPDATE_LATEST_PUB_LINKS',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_latest_pub_links_pvt;
p_procedure_name => 'UPDATE_LATEST_PUB_LINKS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_LATEST_PUB_LINKS;
SELECT *
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = c_structure_version_id;
SELECT a.*
FROM PA_PROJ_ELEM_VER_STRUCTURE a,
PA_PROJ_ELEMENT_VERSIONS b
WHERE b.element_version_id = c_structure_version_id
AND b.project_id = a.project_id
AND b.element_version_id = a.project_id;
SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
b.object_id_from1 parent_element_version_id,
a.TASK_UNPUB_VER_STATUS_CODE
FROM PA_PROJ_ELEMENT_VERSIONS a,
PA_OBJECT_RELATIONSHIPS b
WHERE a.object_type = 'PA_TASKS'
AND a.parent_structure_version_id = c_structure_version_id
AND a.element_version_id = b.object_id_to1
AND b.relationship_type = 'S'
AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
AND b.object_type_to = 'PA_TASKS'
ORDER BY a.display_sequence;
SELECT a.*
FROM PA_PROJ_ELEM_VER_SCHEDULE a,
PA_PROJ_ELEMENT_VERSIONS b
WHERE b.element_version_id = c_element_version_id
AND b.project_id = a.project_id
AND b.element_version_id = a.element_version_id;
SELECT pst.structure_type_class_code
FROM PA_STRUCTURE_TYPES pst,
PA_PROJ_ELEMENT_VERSIONS ppev,
PA_PROJ_STRUCTURE_TYPES ppst
WHERE ppev.element_version_id = c_structure_version_id
AND ppev.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id;
SELECT 'Y'
FROM PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = c_structure_version_id
AND EXISTS
(SELECT 'Y'
FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
PA_PROJ_ELEM_VER_STRUCTURE ppevs
WHERE ppev2.proj_element_id = ppev.proj_element_id
AND ppev2.project_id = ppev.project_id
AND ppevs.project_id = ppev2.project_id
AND ppevs.element_version_id = ppev2.element_version_id
AND ppevs.status_code <> 'STRUCTURE_PUBLISHED');
select object_relationship_id, object_id_to1 object_id_to,
object_type_to, record_version_number
from pa_object_relationships
where relationship_type = 'L'
and object_id_from1 = c_element_version_id
and object_type_from IN ('PA_TASKS','PA_STRUCTURES');
select v1.project_id project_id, v2.proj_element_id structure_id,
v1.parent_structure_version_id structure_version_id,
v1.element_version_id task_version_id
from pa_proj_element_versions v1,
pa_proj_element_versions v2
where v1.element_version_id = c_task_version_id
and v1.parent_structure_version_id = v2.element_version_id;
select WEIGHTING_PERCENTAGE
from pa_object_relationships
where object_id_to1 = c_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT pst.structure_type_class_code
INTO l_structure_type1
FROM PA_STRUCTURE_TYPES pst,
PA_PROJ_STRUCTURE_TYPES ppst
WHERE ppst.proj_element_id = l_structure_ver_to_rec.proj_element_id
AND pst.structure_type_id = ppst.structure_type_id;
SELECT pst.structure_type_class_code
INTO l_structure_type1
FROM PA_STRUCTURE_TYPES pst,
PA_PROJ_STRUCTURE_TYPES ppst
WHERE ppst.proj_element_id = l_info_task_ver_rec.structure_id
AND pst.structure_type_id = ppst.structure_type_id;
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => l_pev_schedule_id
,X_ELEMENT_VERSION_ID => l_new_struct_ver_id
,X_PROJECT_ID => l_ver_schedule_attr_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => l_ver_schedule_attr_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_ver_schedule_attr_rec.SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_ver_schedule_attr_rec.SCHEDULED_FINISH_DATE
,X_OBLIGATION_START_DATE => l_ver_schedule_attr_rec.OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_ver_schedule_attr_rec.OBLIGATION_FINISH_DATE
,X_ACTUAL_START_DATE => l_ver_schedule_attr_rec.ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ver_schedule_attr_rec.ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ver_schedule_attr_rec.ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ver_schedule_attr_rec.ESTIMATED_FINISH_DATE
,X_DURATION => l_ver_schedule_attr_rec.DURATION
,X_EARLY_START_DATE => l_ver_schedule_attr_rec.EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_ver_schedule_attr_rec.EARLY_FINISH_DATE
,X_LATE_START_DATE => l_ver_schedule_attr_rec.LATE_START_DATE
,X_LATE_FINISH_DATE => l_ver_schedule_attr_rec.LATE_FINISH_DATE
,X_CALENDAR_ID => l_ver_schedule_attr_rec.CALENDAR_ID
,X_MILESTONE_FLAG => l_ver_schedule_attr_rec.MILESTONE_FLAG
,X_CRITICAL_FLAG => l_ver_schedule_attr_rec.CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_ver_schedule_attr_rec.wq_planned_quantity
,X_PLANNED_EFFORT => l_ver_schedule_attr_rec.planned_effort
,X_ACTUAL_DURATION => l_ver_schedule_attr_rec.actual_duration
,X_ESTIMATED_DURATION => l_ver_schedule_attr_rec.estimated_duration
,X_ATTRIBUTE_CATEGORY => l_ver_schedule_attr_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ver_schedule_attr_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => l_ver_schedule_attr_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => l_ver_schedule_attr_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => l_ver_schedule_attr_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => l_ver_schedule_attr_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => l_ver_schedule_attr_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => l_ver_schedule_attr_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => l_ver_schedule_attr_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => l_ver_schedule_attr_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => l_ver_schedule_attr_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => l_ver_schedule_attr_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => l_ver_schedule_attr_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => l_ver_schedule_attr_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => l_ver_schedule_attr_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => l_ver_schedule_attr_rec.ATTRIBUTE15
,X_SOURCE_OBJECT_ID => l_ver_schedule_attr_rec.PROJECT_ID
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => l_pev_schedule_id
,X_ELEMENT_VERSION_ID => l_task_version_id
,X_PROJECT_ID => l_ver_schedule_attr_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => l_ver_schedule_attr_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_ver_schedule_attr_rec.SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_ver_schedule_attr_rec.SCHEDULED_FINISH_DATE
,X_OBLIGATION_START_DATE => l_ver_schedule_attr_rec.OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_ver_schedule_attr_rec.OBLIGATION_FINISH_DATE
,X_ACTUAL_START_DATE => l_ver_schedule_attr_rec.ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ver_schedule_attr_rec.ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ver_schedule_attr_rec.ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ver_schedule_attr_rec.ESTIMATED_FINISH_DATE
,X_DURATION => l_ver_schedule_attr_rec.DURATION
,X_EARLY_START_DATE => l_ver_schedule_attr_rec.EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_ver_schedule_attr_rec.EARLY_FINISH_DATE
,X_LATE_START_DATE => l_ver_schedule_attr_rec.LATE_START_DATE
,X_LATE_FINISH_DATE => l_ver_schedule_attr_rec.LATE_FINISH_DATE
,X_CALENDAR_ID => l_ver_schedule_attr_rec.CALENDAR_ID
,X_MILESTONE_FLAG => l_ver_schedule_attr_rec.MILESTONE_FLAG
,X_CRITICAL_FLAG => l_ver_schedule_attr_rec.CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_ver_schedule_attr_rec.wq_planned_quantity
,X_PLANNED_EFFORT => l_ver_schedule_attr_rec.planned_effort
,X_ACTUAL_DURATION => l_ver_schedule_attr_rec.actual_duration
,X_ESTIMATED_DURATION => l_ver_schedule_attr_rec.estimated_duration
,X_ATTRIBUTE_CATEGORY => l_ver_schedule_attr_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ver_schedule_attr_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => l_ver_schedule_attr_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => l_ver_schedule_attr_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => l_ver_schedule_attr_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => l_ver_schedule_attr_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => l_ver_schedule_attr_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => l_ver_schedule_attr_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => l_ver_schedule_attr_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => l_ver_schedule_attr_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => l_ver_schedule_attr_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => l_ver_schedule_attr_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => l_ver_schedule_attr_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => l_ver_schedule_attr_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => l_ver_schedule_attr_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => l_ver_schedule_attr_rec.ATTRIBUTE15
,X_SOURCE_OBJECT_ID => l_ver_schedule_attr_rec.PROJECT_ID
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => l_task_version_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 => 'PA_STRUCTURES'
,p_object_id_to1 => l_structure_ver_to_rec.element_version_id
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'L'
,p_relationship_subtype => 'READ_WRITE'
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => l_new_obj_rel_id
,x_return_status => l_return_status
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => l_task_version_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 => 'PA_TASKS'
,p_object_id_to1 => l_info_task_ver_rec.task_version_id
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'L'
,p_relationship_subtype => 'READ_WRITE'
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => l_new_obj_rel_id
,x_return_status => l_return_status
);
SELECT structure_type_class_code
FROM PA_STRUCTURE_TYPES pst,
PA_PROJ_STRUCTURE_TYPES ppst
WHERE ppst.proj_element_id = c_proj_element_id
AND pst.structure_type_id = ppst.structure_type_id;
SELECT *
FROM PA_PROJ_ELEMENTS
WHERE project_id = c_project_id
AND object_type = 'PA_STRUCTURES';
SELECT *
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = c_structure_version_id;
SELECT a.*
FROM PA_PROJ_ELEM_VER_STRUCTURE a,
PA_PROJ_ELEMENT_VERSIONS b
WHERE b.element_version_id = c_structure_version_id
AND b.element_version_id = a.element_version_id
AND b.proj_element_id = a.proj_element_id
AND b.project_id = a.project_id;
SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
b.object_id_from1 parent_element_version_id
FROM PA_PROJ_ELEMENT_VERSIONS a,
PA_OBJECT_RELATIONSHIPS b,
pa_proj_elements c
WHERE a.object_type = 'PA_TASKS'
AND a.parent_structure_version_id = c_structure_version_id
AND a.element_version_id = b.object_id_to1
AND b.relationship_type = 'S'
and c.link_task_flag <> 'Y'
and c.proj_element_id = a.proj_element_id
ORDER BY a.display_sequence;
select weighting_percentage
from pa_object_relationships
where object_id_to1 = p_task_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT a.*
FROM PA_PROJ_ELEM_VER_SCHEDULE a,
PA_PROJ_ELEMENT_VERSIONS b
WHERE b.element_version_id = c_element_version_id
AND b.element_version_id = a.element_version_id
AND b.project_id = a.project_id;
SELECT a.*, b.task_number PA_TASK_NUMBER
FROM PA_PROJ_ELEMENTS a,
PA_TASKS b
WHERE a.object_type = 'PA_TASKS'
AND a.project_id = c_project_id
AND a.proj_element_id = c_proj_element_id
AND a.LINK_TASK_FLAG <> 'Y'
AND a.proj_element_id = b.task_id(+);
SELECT task_id
FROM PA_TASKS
WHERE project_id = c_project_id
AND task_number = c_task_number;
select a.task_id
from pa_tasks a, pa_tasks b, pa_proj_elements c
where a.project_id = c_dest_project_id
and b.project_id = c_src_project_id
and a.task_number = b.task_number
and b.task_id = c.proj_element_id
and c.link_task_flag = 'Y';*/
select /*+ INDEX(a PA_TASKS_U2) */ a.task_id --Bug No 3634334
from pa_tasks a, pa_tasks b, pa_proj_elements c
where a.project_id = c_dest_project_id
and b.project_id = c_src_project_id
and a.task_number = b.task_number
and c.project_id = c_src_project_id --Bug No 3634334
and b.task_id = c.proj_element_id
and c.object_type = 'PA_TASKS' -- Bug No. 3968095
and c.link_task_flag = 'Y';
l_task_delete NUMBER;
l_task_delete_rvn NUMBER;
l_task_delete_wbs_rvn NUMBER;
SELECT 1 FROM PA_PROJECTS_ALL
WHERE TEMPLATE_FLAG = 'Y'
AND project_id = c_project_id;
SELECT target_start_date, target_finish_date
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = p_dest_project_id;
l_select NUMBER; -- For selecting structure versions:
SELECT distinct(b.element_version_id)
FROM pa_proj_elements a, pa_proj_elem_ver_structure b
WHERE a.project_id = c_project_id
AND a.proj_element_id = c_structure_id
AND a.project_id = b.project_id
AND a.proj_element_id = b.proj_element_id
AND b.status_code <> 'STRUCTURE_PUBLISHED'
--Bug 2643432
--This is a temporary fix. The API returns the first row, but since date
--comparison only compares up to the day, not the second, we can only
--return the first selected row in this API.
AND b.element_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(c_structure_id)
-- AND b.last_update_date = (
-- SELECT MAX(c.last_update_date)
-- FROM pa_proj_elem_ver_structure c
-- WHERE c.project_id = a.project_id
-- AND c.proj_element_id = a.proj_element_id
-- AND c.status_code <> 'STRUCTURE_PUBLISHED')
AND 1 = (c_option)
UNION
SELECT distinct(b.element_version_id)
FROM pa_proj_elements a, pa_proj_elem_ver_structure b
WHERE a.project_id = c_project_id
AND a.proj_element_id = c_structure_id
AND a.project_id = b.project_id
AND a.proj_element_id = b.proj_element_id
AND ((b.status_code = 'STRUCTURE_PUBLISHED' AND
b.latest_eff_published_flag = 'Y')
OR
(b.status_code = 'STRUCTURE_PUBLISHED' AND
b.current_flag = 'Y')
OR
(b.status_code <> 'STRUCTURE_PUBLISHED'))
AND 2 = (c_option)
UNION
SELECT distinct(b.element_version_id)
FROM pa_proj_elements a, pa_proj_elem_ver_structure b
WHERE a.project_id = c_project_id
AND a.proj_element_id = c_structure_id
AND a.project_id = b.project_id
AND a.proj_element_id = b.proj_element_id
AND b.status_code = 'STRUCTURE_PUBLISHED'
AND b.latest_eff_published_flag = 'Y'
AND 3 = (c_option);
select *
from pa_proj_workplan_attr
where proj_element_id = c_proj_element_id;
select *
from pa_proj_progress_attr
where object_type = 'PA_STRUCTURES'
and object_id = c_proj_element_id
and project_id = c_project_id; -- For Bug 3968095
select record_version_number
from pa_projects_all
where project_id = c_project_id;
select INITIAL_STATUS_CODE
from pa_task_types
where task_type_id = c_task_type_id;
SELECT split_cost_from_workplan_flag
INTO l_split_cost_workplan_flag
FROM PA_PROJECTS_ALL
WHERE project_id = p_src_project_id;
select name into l_project_name
from pa_projects_all
where project_id = p_dest_project_id;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
l_select := 1;
l_select := 1;
l_select := 2;
l_select := 3;
l_select := 1;
l_select := 3;
l_select := 1;
l_select := 1;
l_select := 1;
l_select := 2;
l_select := 3;
l_select := 1;
,p_next_progress_update_date =>l_progress_attr_rec.NEXT_PROGRESS_UPDATE_DATE
,x_structure_id => l_structure_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
l_select);
select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
pa_debug.debug('inserting into task with id'||l_task_id);
PA_PROJ_ELEMENTS_PKG.Insert_Row(
X_ROW_ID => l_rowid
,X_PROJ_ELEMENT_ID => l_task_id
,X_PROJECT_ID => p_dest_project_id
,X_OBJECT_TYPE => 'PA_TASKS'
,X_ELEMENT_NUMBER => l_tasks_rec.ELEMENT_NUMBER
,X_NAME => l_tasks_rec.NAME
,X_DESCRIPTION => l_tasks_rec.DESCRIPTION
,X_STATUS_CODE => l_init_status_code
,X_WF_STATUS_CODE => l_tasks_rec.WF_STATUS_CODE
,X_PM_PRODUCT_CODE => l_tasks_rec.PM_SOURCE_CODE
,X_PM_TASK_REFERENCE => l_tasks_rec.PM_SOURCE_REFERENCE
,X_CLOSED_DATE => l_tasks_rec.CLOSED_DATE
,X_LOCATION_ID => l_tasks_rec.LOCATION_ID
,X_MANAGER_PERSON_ID => l_tasks_rec.MANAGER_PERSON_ID
,X_CARRYING_OUT_ORGANIZATION_ID => l_tasks_rec.CARRYING_OUT_ORGANIZATION_ID
,X_TYPE_ID => l_tasks_rec.TYPE_ID
,X_PRIORITY_CODE => l_tasks_rec.PRIORITY_CODE
,X_INC_PROJ_PROGRESS_FLAG => l_tasks_rec.INC_PROJ_PROGRESS_FLAG
,X_REQUEST_ID => l_tasks_rec.REQUEST_ID
,X_PROGRAM_APPLICATION_ID => l_tasks_rec.PROGRAM_APPLICATION_ID
,X_PROGRAM_ID => l_tasks_rec.PROGRAM_ID
,X_PROGRAM_UPDATE_DATE => l_tasks_rec.PROGRAM_UPDATE_DATE
,X_LINK_TASK_FLAG => l_tasks_rec.LINK_TASK_FLAG
,X_ATTRIBUTE_CATEGORY => l_tasks_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_tasks_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => l_tasks_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => l_tasks_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => l_tasks_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => l_tasks_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => l_tasks_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => l_tasks_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => l_tasks_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => l_tasks_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => l_tasks_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => l_tasks_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => l_tasks_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => l_tasks_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => l_tasks_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => l_tasks_rec.ATTRIBUTE15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => l_tasks_rec.WQ_ITEM_CODE
,X_UOM_CODE => l_tasks_rec.WQ_UOM_CODE
,X_WQ_ACTUAL_ENTRY_CODE => l_tasks_rec.WQ_ACTUAL_ENTRY_CODE
,X_TASK_PROGRESS_ENTRY_PAGE_ID =>l_tasks_rec.TASK_PROGRESS_ENTRY_PAGE_ID
,X_PARENT_STRUCTURE_ID => l_structure_id
,X_PHASE_CODE => l_tasks_rec.PHASE_CODE
,X_PHASE_VERSION_ID => l_tasks_rec.PHASE_VERSION_ID
,X_SOURCE_OBJECT_ID => p_dest_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
FETCH l_linking_tasks_csr into l_task_delete;
select a.record_version_number, 0
INTO l_task_delete_rvn, l_task_delete_wbs_rvn
from pa_tasks a
where a.task_id = l_task_delete;
PA_TASKS_MAINT_PUB.DELETE_TASK(
p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_project_id => p_dest_project_id
,p_task_id => l_task_delete
,p_record_version_number => l_task_delete_rvn
,p_wbs_record_version_number => l_task_delete_wbs_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_dest_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => l_struc_scheduled_start_date
,p_finish_date => l_struc_scheduled_finish_date
,p_record_version_number => l_proj_record_ver_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT b.proj_element_id, b.scheduled_start_date,
b.scheduled_finish_date, a.element_version_id, duration,
a.wbs_number, a.wbs_level, a.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B
WHERE a.parent_structure_version_id = p_structure_version_id
AND a.element_version_id = b.element_version_id
AND a.project_id = b.project_id
AND a.project_id = c_project_id
ORDER BY a.display_sequence asc , abs(a.last_updated_by) asc , a.wbs_level asc; -- Huawei changes 14556729
SELECT PROJ_ELEMENT_ID, SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE, ELEMENT_VERSION_ID, DURATION, WBS_NUMBER,
OLD_LEVEL , DISPLAY_SEQUENCE, LEVEL WBS_LEVEL
FROM
(
select /*+ ordered use_nl(b a t) index(t pa_tasks_n8) index(a PA_PROJ_ELEMENT_VERSIONS_N1) index(t PA_PROJ_ELEM_VER_SCHEDULE_U2) */
/* t.project_id , t.task_id , t.parent_task_id , a.display_sequence ,
a.level_sequence, a.parent_structure_version_id , B.PROJ_ELEMENT_ID,
B.SCHEDULED_START_DATE, B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID, DURATION,
A.WBS_NUMBER, A.WBS_LEVEL OLD_LEVEL
from
PA_TASKS T, PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B
where
T.PROJECT_ID = p_project_id AND A.PROJ_ELEMENT_ID = T.TASK_ID
AND A.PROJECT_ID = T.PROJECT_ID --AND A.PARENT_STRUCTURE_VERSION_ID = :B1
AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID
) r
START WITH r.PARENT_TASK_ID IS NULL CONNECT BY
PRIOR r.TASK_ID = r.PARENT_TASK_ID ORDER SIBLINGS BY DISPLAY_SEQUENCE ,
LEVEL_SEQUENCE; */
SELECT PROJ_ELEMENT_ID, SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE, ELEMENT_VERSION_ID, DURATION, WBS_NUMBER,
OLD_LEVEL , DISPLAY_SEQUENCE, LEVEL WBS_LEVEL ,
SYS_CONNECT_BY_PATH(task_id, '/') connect_path,
connect_by_isleaf leaf_node
FROM PA_PROJ_ELEMENTS_TMP r
START WITH r.PARENT_TASK_ID IS NULL CONNECT BY
PRIOR r.TASK_ID = r.PARENT_TASK_ID ORDER SIBLINGS BY DISPLAY_SEQUENCE , LEVEL_SEQUENCE;
SELECT PROJECT_ID
INTO l_project_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE ELEMENT_VERSION_ID = p_structure_version_id;
delete PA_PROJ_ELEMENTS_TMP;
INSERT INTO PA_PROJ_ELEMENTS_TMP(PROJECT_ID, TASK_ID, PARENT_TASK_ID, DISPLAY_SEQUENCE, LEVEL_SEQUENCE, PARENT_STRUCTURE_VERSION_ID,
PROJ_ELEMENT_ID, SCHEDULED_START_DATE, SCHEDULED_FINISH_DATE, ELEMENT_VERSION_ID, DURATION, WBS_NUMBER, OLD_LEVEL, DEFER_CODE, TOP_TASK_ID) /* Modified insert statement for bug#14765516 */
SELECT /*+ ordered use_nl(b a t) index(t pa_tasks_n8) index(a PA_PROJ_ELEMENT_VERSIONS_N1) index(t PA_PROJ_ELEM_VER_SCHEDULE_U2) */
t.project_id , t.task_id , t.parent_task_id , a.display_sequence ,
a.level_sequence, a.parent_structure_version_id , B.PROJ_ELEMENT_ID,
B.SCHEDULED_START_DATE, B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID, DURATION,
A.WBS_NUMBER, A.WBS_LEVEL OLD_LEVEL,a.defer_code, t.top_task_id
from
PA_TASKS T, PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B
where
T.PROJECT_ID = p_project_id AND A.PROJ_ELEMENT_ID = T.TASK_ID
AND A.PROJECT_ID = T.PROJECT_ID --AND A.PARENT_STRUCTURE_VERSION_ID = :B1
AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID;
UPDATE PA_PROJ_ELEMENTS
SET BASELINE_START_DATE=v_SCHEDULED_START_DATE(i),
BASELINE_FINISH_DATE=v_SCHEDULED_FINISH_DATE(i),
BASELINE_DURATION=v_duration(i),
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,0) + 1
WHERE PROJ_ELEMENT_ID = v_PROJ_ELEMENT_ID(i);
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET DISPLAY_SEQUENCE = v_DISPLAY_SEQUENCE(i),
WBS_NUMBER = v_WBS_NUMBER(i),
WBS_LEVEL = v_WBS_LEVEL(i), -- Sridhar Huawei 18-Sept , Add update of wbs_level
last_updated_by = 100
WHERE ELEMENT_VERSION_ID = v_ELEMENT_VERSION_ID(i); /* Code change for bug#14765516 - Ends */
SELECT B.PROJ_ELEMENT_ID, B.SCHEDULED_START_DATE,
B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID , duration
FROM PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B, PA_PROJ_ELEMENTS C
WHERE A.PARENT_STRUCTURE_VERSION_ID = p_structure_version_id
AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID
AND A.PROJ_ELEMENT_ID = C.proj_element_id
AND A.PROJECT_ID = B.PROJECT_ID
AND A.project_id = c_project_id
AND C.PROJECT_ID = A.project_id
AND (B.SCHEDULED_START_DATE <> C.BASELINE_START_DATE OR
B.SCHEDULED_FINISH_DATE <> C.BASELINE_FINISH_DATE);
* bug 13923366 - testing delete_task sceanrio
SELECT B.PROJ_ELEMENT_ID, B.SCHEDULED_START_DATE,
B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID , duration
FROM PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B, PA_PROJ_ELEMENTS C
WHERE A.PARENT_STRUCTURE_VERSION_ID = p_structure_version_id
AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID
AND A.PROJECT_ID = B.PROJECT_ID
AND A.project_id = c_project_id;
SELECT B.SCHEDULED_START_DATE,
B.SCHEDULED_FINISH_DATE, C.record_version_number
FROM PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B,
PA_PROJECTS_ALL C
WHERE A.PARENT_STRUCTURE_VERSION_ID = p_structure_version_id
AND A.ELEMENT_VERSION_ID = p_structure_version_id
AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID
AND A.PROJECT_ID = B.PROJECT_ID
AND A.PROJECT_ID = C.PROJECT_ID
AND A.project_id = c_project_id;
SELECT record_version_number
FROM pa_projects_all
WHERE project_id = c_project_id; */
select a.calendar_id
from pa_projects_all a, pa_proj_element_versions b
where a.project_id = b.project_id
and b.element_version_id = p_structure_version_id;
SELECT PROJECT_ID
INTO l_project_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE ELEMENT_VERSION_ID = p_structure_version_id;
UPDATE PA_PROJ_ELEMENTS
SET BASELINE_START_DATE= NULL ,
BASELINE_FINISH_DATE= NULL ,
RECORD_VERSION_NUMBER=NVL(RECORD_VERSION_NUMBER,0) + 1
WHERE PROJECT_ID = l_project_id; */
IF (NVL(PA_PROJECT_PUB.G_MASS_ADD_TASKS,'N') = 'N' AND NVL(PA_PROJECT_PUB.G_MASS_DELETE_TASKS,'N') = 'N') THEN
OPEN c1( l_project_id );
UPDATE PA_PROJ_ELEMENTS
SET BASELINE_START_DATE=c1_rec.scheduled_start_date,
BASELINE_FINISH_DATE=c1_rec.scheduled_finish_date, -- pa
BASELINE_DURATION=c1_rec.duration,
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,0) + 1
WHERE PROJ_ELEMENT_ID = c1_rec.proj_element_id;
UPDATE PA_PROJ_ELEMENTS
SET BASELINE_START_DATE=l_data(i).SCHEDULED_START_DATE,
BASELINE_FINISH_DATE=l_data(i).SCHEDULED_FINISH_DATE, -- pa
BASELINE_DURATION=l_data(i).duration,
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,0) + 1
WHERE PROJ_ELEMENT_ID = l_data(i).PROJ_ELEMENT_ID;
UPDATE PA_PROJ_ELEMENTS
SET BASELINE_START_DATE=v_SCHEDULED_START_DATE(i),
BASELINE_FINISH_DATE=v_SCHEDULED_FINISH_DATE(i), -- pa
BASELINE_DURATION=v_duration(i),
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,0) + 1
WHERE PROJ_ELEMENT_ID = v_PROJ_ELEMENT_ID(i);
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_project_id
,p_date_type => 'BASELINE'
,p_start_date => l_scheduled_start_date
,p_finish_date => l_scheduled_finish_date
,p_record_version_number => l_proj_record_ver_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = p_project_id
and a.object_type = 'PA_STRUCTURES'
and a.proj_element_id = b.proj_element_id
and b.structure_type_id = c.structure_type_id
and c.structure_type_class_code IN ('FINANCIAL');
select a.rowid
from pa_proj_structure_types a, pa_structure_types b
where a.proj_element_id = c_structure_id
and a.structure_type_id = b.structure_type_id
and b.structure_type_class_code = 'WORKPLAN';
PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
delete from pa_proj_elem_ver_schedule
where project_id = p_project_id;
select '1' from pa_proj_workplan_attr
where project_id = p_project_id
and wp_auto_publish_flag = 'Y';
select name, description
from pa_proj_elem_ver_structure
where project_Id = p_project_id
and element_version_id = p_structure_version_id;
select 'Y' from dual
where exists (
select 1 from pa_product_installation_v
where product_short_code = 'PJT' AND INSTALLED_FLAG = 'Y'); */
select 'Y' from dual;
select enable_wf_flag, workflow_item_type,
workflow_process, wf_success_status_code,
wf_failure_status_code
from pa_project_statuses
where project_status_code = c_status_code;
select b.element_version_id
from pa_proj_element_versions a,
pa_proj_element_versions b
where a.element_version_id = c_keep_struc_ver_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.element_version_id <> c_keep_struc_ver_id
and b.object_type = 'PA_STRUCTURES';
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id => p_project_id
,p_structure_version_id =>l_del_struc_ver_id
,x_return_status => l_return_status
,x_error_message_code => l_msg_data
);
UPDATE PA_PROJ_ELEM_VER_STRUCTURE
set status_code = 'STRUCTURE_SUBMITTED',
lock_status_code = 'UNLOCKED',
locked_by_person_id = NULL,
locked_date = NULL
where project_id = p_project_id
and element_version_id = p_structure_version_id;
PA_WORKFLOW_UTILS.INSERT_WF_PROCESSES
(
p_wf_type_code => 'WORKPLAN'
,p_item_type => l_wf_item_type
,p_item_key => l_item_key
,p_entity_key1 => p_project_id
,p_entity_key2 => p_structure_version_id
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
select 'Y' from dual
where exists (
select 1 from pa_product_installation_v
where product_short_code = 'PJT' AND INSTALLED_FLAG = 'Y'); */
select 'Y' from dual;
select enable_wf_flag, workflow_item_type,
workflow_process, wf_success_status_code,
wf_failure_status_code
from pa_project_statuses
where project_status_code = c_status_code;
select '1' from pa_project_statuses
where project_status_code = c_status_code
and status_type = 'STRUCTURE';
select pev_structure_id into l_pev_struc_id
from pa_proj_elem_ver_structure
where project_id = p_project_id
and element_version_id = p_structure_version_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
update pa_proj_elem_ver_structure
set status_code = p_status_code,
record_version_number = record_version_number + 1
where pev_structure_id = l_pev_struc_id;
PA_WORKFLOW_UTILS.INSERT_WF_PROCESSES
(
p_wf_type_code => 'WORKPLAN'
,p_item_type => l_wf_item_type
,p_item_key => l_item_key
,p_entity_key1 => p_project_id
,p_entity_key2 => p_structure_version_id
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
select nvl(wp_approval_reqd_flag,'N') from pa_proj_workplan_attr
where project_id = p_project_id;
select MAX(pwp.item_key), max(pwp.item_type)
from pa_wf_processes pwp, pa_project_statuses pps
where pwp.item_type = pps.workflow_item_type
and pps.status_type = 'STRUCTURE'
and pps.project_status_code = 'STRUCTURE_SUBMITTED'
and entity_key2 = p_structure_version_id
and pwp.wf_type_code = 'WORKPLAN';
select 'Y'
from wf_item_activity_statuses wias, pa_project_statuses pps
where wias.item_type = pps.WORKFLOW_ITEM_TYPE
and wias.item_key = l_item_key
and wias.activity_status = 'ACTIVE'
and pps.status_type = 'STRUCTURE'
and pps.project_status_code = 'STRUCTURE_SUBMITTED';
PROCEDURE update_structures_setup_old
( 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
,p_workplan_enabled_flag IN VARCHAR2
,p_financial_enabled_flag IN VARCHAR2
,p_sharing_enabled_flag IN VARCHAR2
--FP M changes bug 3301192
,p_deliverables_enabled_flag IN VARCHAR2
,p_sharing_option_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--End FP M changes bug 3301192
,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_ret_stat VARCHAR2(1);
select name, target_start_date, target_finish_date
from pa_projects_all
where project_id = p_project_id;
select template_flag, record_version_number
from pa_projects_all
where project_id = p_project_id;
select b.proj_element_id, a.record_version_number
from pa_proj_workplan_attr a,
pa_proj_elements b,
pa_proj_structure_types c,
pa_structure_types d
where a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.project_id = p_project_id
and b.proj_element_id = c.proj_element_id
and c.structure_type_id = d.structure_type_id
and d.structure_type_class_code = 'WORKPLAN';
select a.rowid
from pa_proj_structure_types a,
pa_structure_types b
where a.proj_element_id = c_structure_id
and a.structure_type_id = b.structure_type_id
and b.structure_type_class_code = 'WORKPLAN';
select element_version_id
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
and project_id = p_project_id
and status_code = 'STRUCTURE_PUBLISHED'
and LATEST_EFF_PUBLISHED_FLAG = 'Y';
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = p_project_id
and a.object_type = 'PA_STRUCTURES'
and a.proj_element_id = b.proj_element_id
and b.structure_type_id = c.structure_type_id
and c.structure_type_class_code = 'WORKPLAN';
select b.element_version_id, b.record_version_number
from pa_proj_element_versions a,
pa_proj_element_versions b
where a.element_version_id = c_keep_struc_ver_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.element_version_id <> c_keep_struc_ver_id
and b.object_type = 'PA_STRUCTURES';
select a.element_version_id, a.record_version_number
from pa_proj_element_versions a,
pa_proj_elements b
where a.proj_element_id = b.proj_element_id
and a.project_id = b.project_id
and b.proj_element_id = c_struc_id;
select PEV_STRUCTURE_ID, record_version_number
from pa_proj_elem_ver_structure
where project_id = p_project_id
and element_version_id = c_struc_ver_id;
select *
from pa_proj_workplan_attr
where proj_element_id = c_struc_id;
select *
from pa_proj_progress_attr
where project_id = p_project_id
and object_type = 'PA_STRUCTURES'
and object_id = c_struc_id;
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = p_project_id
and a.object_type = 'PA_STRUCTURES'
and a.proj_element_id = b.proj_element_id
and b.structure_type_id = c.structure_type_id
and c.structure_type_class_code = 'FINANCIAL';
select structure_type_id
from pa_structure_types
where structure_type_class_code = 'WORKPLAN';
select element_version_id
from pa_proj_element_versions
where project_id = p_project_id
and proj_element_id = c_structure_id
and object_type = 'PA_STRUCTURES';
select pev.object_type, pev.proj_element_id, pev.element_version_id
from pa_proj_element_versions pev, pa_object_relationships rel
where pev.parent_structure_version_id = c_struc_ver_id
and rel.object_id_to1 = pev.element_version_id
and rel.relationship_type = 'S'
and NOT EXISTS (
select 1
from pa_object_Relationships
where object_id_from1 = pev.element_version_id
and relationship_type = 'S'
);
select start_date, completion_date
from pa_tasks
where task_id = c_task_id;
select target_start_date, target_finish_date, calendar_id
from pa_projects_all
where project_id = p_project_id;
select v.element_version_id
from pa_proj_element_versions v,
pa_object_relationships r
where v.element_version_id = r.object_id_to1
and r.object_id_from1 = c_structure_version_id
and r.object_type_from = 'PA_STRUCTURES';
select a.scheduled_start_date, a.scheduled_finish_date
from pa_proj_elem_ver_schedule a
where a.project_id = c_project_id
and a.element_version_id = c_element_version_id;
l_update_proc_wbs_flag VARCHAR2(1);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.update_structures_setup_attr');
savepoint update_struc_setup_attr_pvt;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
l_keep_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(l_structure_id);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_Wo_Val(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE pa_proj_elements
SET baseline_start_date = NULL,
baseline_finish_date = NULL,
record_version_number = record_version_number+1
WHERE project_id = p_project_id;
DELETE FROM pa_proj_elem_ver_schedule
WHERE project_id = p_project_id;
PA_WORKPLAN_ATTR_PUB.DELETE_PROJ_WORKPLAN_ATTRS(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,p_proj_element_id => l_structure_id
,p_record_version_number => l_wp_attr_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROGRESS_PUB.DELETE_PROJ_PROG_ATTR(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,P_OBJECT_TYPE => 'PA_STRUCTURES'
,p_object_id => l_structure_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
UPDATE pa_proj_elem_ver_structure
set status_code = l_status_code,
current_flag = 'N',
current_baseline_date = NULL,
current_baseline_person_id = NULL,
latest_eff_published_flag = l_latest_eff_pub_flag,
effective_date = l_effective_date,
record_version_number = record_version_number + 1
where pev_structure_id = l_pev_structure_id;
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
,X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
,X_PROJ_ELEMENT_ID => l_structure_id
,X_STRUCTURE_TYPE_ID => l_struc_type_id
,X_RECORD_VERSION_NUMBER => 1
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL
);
,p_next_progress_update_date => l_proj_progress_attr_rec.next_progress_update_date
,p_action_set_id => NULL
,p_TASK_WEIGHT_BASIS_CODE => l_proj_progress_attr_rec.TASK_WEIGHT_BASIS_CODE
,x_proj_progress_attr_id => l_proj_prog_attr_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG(
p_project_id => p_project_id,
p_structure_version_id => l_structure_version_id,
p_update_wbs_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
pev_schedule_id,
element_version_id,
project_id,
proj_element_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
scheduled_start_date,
scheduled_finish_date,
milestone_flag,
critical_flag,
calendar_id,
record_version_number,
last_update_login,
source_object_id,
source_object_type
)
SELECT
pa_proj_elem_ver_schedule_s.nextval,
PPEV.element_version_id,
PPEV.project_id,
PPEV.proj_element_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', decode(PT.COMPLETION_DATE, NULL, trunc(l_target_start_date), trunc(PT.START_DATE))),
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_target_finish_date))),
'N',
'N',
l_calendar_id,
0,
FND_GLOBAL.LOGIN_ID,
PPEV.project_id,
'PA_PROJECTS'
FROM PA_TASKS PT,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE
PPEV.parent_structure_version_id = l_structure_version_id
AND PPEV.proj_element_id = PT.task_id (+);
UPDATE pa_proj_elem_ver_structure
set status_code = l_status_code,
current_flag = l_baseline_flag,
current_baseline_date = l_effective_date,
current_baseline_person_id = NULL,
latest_eff_published_flag = l_latest_eff_pub_flag,
effective_date = l_effective_date,
-- PROCESS_UPDATE_WBS_FLAG = l_update_proc_wbs_flag,
record_version_number = record_version_number + 1
where pev_structure_id = l_pev_structure_id;
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => l_get_sch_dates_cur.scheduled_start_date
,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
,p_record_version_number=> l_proj_rec_ver_num
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
l_keep_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(l_structure_id);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE pa_proj_elements
SET baseline_start_date = NULL,
baseline_finish_date = NULL,
record_version_number = record_version_number+1
WHERE project_id = p_project_id;
DELETE FROM pa_proj_elem_ver_schedule
WHERE project_id = p_project_id;
PA_WORKPLAN_ATTR_PUB.DELETE_PROJ_WORKPLAN_ATTRS(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,p_proj_element_id => l_structure_id
,p_record_version_number => l_wp_attr_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROGRESS_PUB.DELETE_PROJ_PROG_ATTR(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,P_OBJECT_TYPE => 'PA_STRUCTURES'
,P_OBJECT_ID => l_structure_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
UPDATE pa_proj_elem_ver_structure
set status_code = l_status_code,
current_flag = 'N',
current_baseline_date = NULL,
current_baseline_person_id = NULL,
latest_eff_published_flag = l_latest_eff_pub_flag,
effective_date = l_effective_date,
record_version_number = record_version_number + 1
where pev_structure_id = l_pev_structure_id;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
,p_next_progress_update_date => l_proj_progress_attr_rec.next_progress_update_date
,p_action_set_id => NULL
,p_task_weight_basis_code => l_proj_progress_attr_rec.TASK_WEIGHT_BASIS_CODE
,x_structure_id => l_structure_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => l_get_sch_dates_cur.scheduled_start_date
,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
,p_record_version_number=> l_proj_rec_ver_num
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
UPDATE PA_PROJECTS_ALL
SET baseline_start_date = NULL,
baseline_finish_date = NULL,
baseline_duration = NULL,
baseline_as_of_date = NULL,
scheduled_start_date = NULL,
scheduled_finish_date = NULL,
scheduled_duration = NULL,
scheduled_as_of_date = NULL
WHERE Project_id = p_project_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.update_structures_setup_attr end');
ROLLBACK to update_struc_setup_attr_pvt;
ROLLBACK to update_struc_setup_attr_pvt;
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_structures_setup_old;
PROCEDURE update_workplan_versioning
( 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_proj_element_id IN NUMBER
,p_enable_wp_version_flag 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_return_status VARCHAR2(1);
select a.element_version_id
from pa_proj_elem_ver_structure a,
pa_proj_elements b
where b.proj_element_id = c_structure_id
and b.project_id = a.project_id
and b.proj_element_id = a.proj_element_id
and a.status_code = 'STRUCTURE_PUBLISHED'
and a.LATEST_EFF_PUBLISHED_FLAG = 'Y';
select a.template_flag
from pa_projects_all a,
pa_proj_elements b
where a.project_id = b.project_id
and b.proj_element_id = p_proj_element_id;
select a.PEV_STRUCTURE_ID, a.record_version_number
from pa_proj_elem_ver_structure a,
pa_proj_element_versions b
where b.project_id = a.project_id
and b.element_version_id = c_struc_ver_id
and a.element_version_id = b.element_version_id;
select b.element_version_id, b.record_version_number
from pa_proj_element_versions a,
pa_proj_element_versions b
where a.element_version_id = c_keep_struc_ver_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.element_version_id <> c_keep_struc_ver_id
and b.object_type = 'PA_STRUCTURES';
select b.project_id, b.element_version_id
from pa_proj_elements a,
pa_proj_element_versions b
where a.proj_element_id = c_structure_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id;
SELECT 'x' from pa_proj_element_versions
WHERE project_id = c_project_id
AND parent_structure_version_id = c_structure_version_id
AND object_type = 'PA_TASKS'
;
SELECT WP_ENABLE_VERSION_FLAG
FROM PA_PROJ_WORKPLAN_ATTR
WHERE PROJ_ELEMENT_ID = l_proj_element_id
AND PROJECT_ID = l_project_id;
SELECT *
from pa_structures_links_v
where parent_project_id= c_project_id
and relationship_type = c_relationship_type
;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.update_workplan_versioning');
savepoint update_wp_versioning_pvt;
PA_RELATIONSHIP_PUB.Delete_SubProject_Association(
p_object_relationships_id => sub_projects_rec.object_relationship_id,
p_record_version_number => sub_projects_rec.record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP(
p_project_id => l_project_id,
p_structure_version_id => l_struc_ver_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
UPDATE pa_proj_elem_ver_structure
SET current_working_flag = 'N'
WHERE element_version_id = l_struc_ver_id
and project_id = l_project_id;
l_keep_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(p_proj_element_id);
PA_PROJECT_STRUCTURE_PUB1.Delete_Structure_Version(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,p_calling_from => 'DEL_WP_STRUC_DISABLE_VERSION' ---Added for bug 6023347
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_PUBLISHED',
published_date = sysdate,
current_flag = 'Y',
current_baseline_date = sysdate,
current_baseline_person_id = NULL,
latest_eff_published_flag = 'Y',
effective_date = sysdate,
LOCK_STATUS_CODE = 'UNLOCKED',
LOCKED_BY_PERSON_ID = NULL,
LOCKED_DATE = NULL,
record_version_number = record_version_number + 1
where pev_structure_id = l_pev_structure_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.update_workplan_versioning end');
ROLLBACK to update_wp_versioning_pvt;
ROLLBACK to update_wp_versioning_pvt;
p_procedure_name => 'update_workplan_versioning',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_workplan_versioning;
PROCEDURE update_wp_calendar
(
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
,p_calendar_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_opt VARCHAR2(1);
select ppevs.element_version_id
from pa_proj_elem_ver_structure ppevs,
pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppevs.status_code <> 'STRUCTURE_PUBLISHED'
and ppevs.project_id = ppe.project_id
and ppevs.proj_element_id = ppe.proj_element_id
and ppe.object_type = 'PA_STRUCTURES'
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN'
and ppe.project_id = p_project_id
and '1' = l_opt
union all
select ppevs.element_version_id
from pa_proj_elem_ver_structure ppevs,
pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppevs.status_code = 'STRUCTURE_PUBLISHED'
and ppevs.project_id = ppe.project_id
and ppevs.proj_element_id = ppe.proj_element_id
and ppe.object_type = 'PA_STRUCTURES'
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN'
and ppe.project_id = p_project_id
and '2' = l_opt;
select template_flag
from pa_projects_all where project_id = p_project_id;
select SCHEDULED_START_DATE, SCHEDULED_FINISH_DATE
from pa_projects_all where project_id = p_project_id;
select ACTUAL_START_DATE, ACTUAL_FINISH_DATE
from pa_projects_all where project_id = p_project_id;
select BASELINE_START_DATE, BASELINE_FINISH_DATE
from pa_projects_all where project_id = p_project_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_WP_CALENDAR BEGIN');
savepoint update_wp_calendar;
update pa_projects_all
/*set SCHEDULED_DURATION = l_sch_hours,
BASELINE_DURATION = l_bsl_hours,
ACTUAL_DURATION = l_act_hours*/
set SCHEDULED_DURATION = l_sch_dur,
BASELINE_DURATION = l_bsl_dur,
ACTUAL_DURATION = l_act_dur
where project_id = p_project_id;
update pa_projects_all
-- set SCHEDULED_DURATION = l_sch_hours
set SCHEDULED_DURATION = l_sch_dur
where project_id = p_project_id;
pa_proj_task_struc_pub.set_update_wbs_flag(
p_project_id => p_project_id
,p_structure_version_id => l_structure_version_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_WP_CALENDAR end');
ROLLBACK to update_wp_calendar;
ROLLBACK to update_wp_calendar;
p_procedure_name => 'Update_wp_calendar',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_wp_calendar;
PROCEDURE update_all_wp_calendar
(
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_calendar_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
Cursor c_calendar_projects
IS
Select project_id
from pa_projects_all
where calendar_id = p_calendar_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_ALL_WP_CALENDAR BEGIN');
savepoint update_all_wp_calendar;
PA_PROJECT_STRUCTURE_PVT1.update_wp_calendar
(
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_project_id => c_rec.project_id
,p_calendar_id => p_calendar_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_ALL_WP_CALENDAR END');
ROLLBACK to update_all_wp_calendar;
p_procedure_name => 'Update_all_wp_calendar',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_ALL_WP_CALENDAR;
select ppvsch.pev_schedule_id,
ppvsch.scheduled_start_date, ppvsch.scheduled_finish_date,
ppvsch.estimated_start_date, ppvsch.estimated_finish_date,
ppvsch.actual_start_date, ppvsch.actual_finish_date
from pa_proj_elem_ver_schedule ppvsch,
pa_proj_element_versions ppv
where ppv.parent_structure_version_id = p_structure_version_id
and ppv.project_id = ppvsch.project_id
and ppv.proj_element_id = ppvsch.proj_element_id
and ppv.element_version_id = ppvsch.element_version_id;
UPDATE PA_PROJ_ELEM_VER_SCHEDULE
SET calendar_id = p_calendar_id
WHERE pev_schedule_id = l_pev_schedule_id;
UPDATE PA_PROJ_ELEM_VER_SCHEDULE
--SET DURATION = l_hours
SET DURATION = l_sch_days
WHERE pev_schedule_id = l_pev_schedule_id;
UPDATE PA_PROJ_ELEM_VER_SCHEDULE
--SET ESTIMATED_DURATION = l_hours
SET ESTIMATED_DURATION = l_est_days
WHERE pev_schedule_id = l_pev_schedule_id;
UPDATE PA_PROJ_ELEM_VER_SCHEDULE
-- SET ACTUAL_DURATION = l_hours
SET ACTUAL_DURATION = l_act_days
WHERE pev_schedule_id = l_pev_schedule_id;
procedure Delete_Struc_Ver_Wo_Val
(
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_structure_version_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_Struc_Ver_Wo_Val';
select pev.project_id, pe.proj_element_id, pe.record_version_number,
pev.element_version_id, pev.record_version_number,
pevs.pev_structure_id, pevs.record_version_number,
pevsh.pev_schedule_id, pevsh.record_version_number,
pevsh.rowid
from pa_proj_elements pe,
pa_proj_element_versions pev,
pa_proj_elem_ver_structure pevs,
pa_proj_elem_ver_schedule pevsh
where pev.element_version_id = p_structure_version_id and
pev.proj_element_id = pe.proj_element_id and
pev.project_id = pevs.project_id and
pev.element_version_id = pevs.element_version_id and
pev.project_id = pevsh.project_id (+) and
pev.element_version_id = pevsh.element_version_id (+);
select 'N'
from pa_proj_element_versions
where proj_element_id = p_structure_id;
select v.element_version_id
from pa_proj_element_versions v,
pa_object_relationships r
where v.element_version_id = r.object_id_to1
and r.object_id_from1 = p_structure_version_id
and r.object_type_from = 'PA_STRUCTURES';
select record_version_number
from pa_proj_workplan_attr
where proj_element_id = c_proj_element_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL begin');
savepoint delete_struc_ver_wo_val;
select record_version_number, parent_structure_version_id
into l_task_rvn, l_parent_struc_ver_id
from pa_proj_element_versions
where element_version_id = l_task_version_id;
PA_TASK_PVT1.DELETE_TASK_VER_WO_VAL(p_commit => 'N',
p_debug_mode => p_debug_mode,
p_calling_module => 'DEL_STRUCT',
p_structure_version_id => l_parent_struc_ver_id,
p_task_version_id => l_task_version_id,
p_record_version_number => l_task_rvn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_PROJ_ELEMENT_SCH_PKG.Delete_Row(l_pevsh_rowid);
PA_PROJECT_STRUCTURE_PVT1.Delete_Structure_Version_Attr(
p_commit => p_commit,
p_debug_mode => p_debug_mode,
p_pev_structure_id => l_pev_structure_id,
p_record_version_number => l_pevs_rvn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_PVT1.Delete_Structure_Version(
p_commit => p_commit,
p_debug_mode => p_debug_mode,
p_structure_version_id => l_element_version_id,
p_record_version_number => l_pev_rvn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('delete non-versioned structure');
PA_WORKPLAN_ATTR_PUB.DELETE_PROJ_WORKPLAN_ATTRS(
p_validate_only => FND_API.G_FALSE
,p_project_id => l_project_id
,p_proj_element_id => l_proj_element_id
,p_record_version_number => l_wp_attr_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROGRESS_PUB.DELETE_PROJ_PROG_ATTR(
p_validate_only => FND_API.G_FALSE
,p_project_id => l_project_id
,P_OBJECT_TYPE => 'PA_STRUCTURES'
,p_object_id => l_proj_element_id
,p_structure_type => 'WORKPLAN' --Amit
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
select record_version_number into l_pe_rvn
from pa_proj_elements where proj_element_id = l_proj_element_id;
PA_PROJECT_STRUCTURE_PVT1.Delete_Structure(
p_commit => p_commit,
p_debug_mode => p_debug_mode,
p_structure_id => l_proj_element_id,
p_record_version_number => l_pe_rvn,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL end');
rollback to delete_struc_ver_wo_val;
rollback to delete_struc_ver_wo_val;
p_procedure_name => 'Delete_Struc_Ver_Wo_Val',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_struc_ver_wo_val;
p_procedure_name => 'Delete_Struc_Ver_Wo_Val',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_STRUC_VER_WO_VAL;
select a.name, a.version_number, c.scheduled_start_date,
c.scheduled_finish_date, b.project_id
from pa_proj_elem_ver_structure a,
pa_proj_element_versions b,
pa_proj_elem_ver_schedule c
where b.element_version_id = p_structure_version_id
and b.project_id = a.project_id
and b.proj_element_id = a.proj_element_id
and b.element_version_id = a.element_version_id
and b.project_id = c.project_id
and b.proj_element_id = c.proj_element_id
and b.element_version_id = c.element_version_id;
select ppa.name name, ppa.segment1,
hou.name carrying_out_org_name, ppl.full_name
from pa_projects_all ppa,
hr_all_organization_units hou,
per_all_people_f ppl,
pa_project_parties ppp
where ppa.carrying_out_organization_id = hou.organization_id
and ppa.project_id = ppp.project_id (+)
and ppa.project_id = c_project_id
and 1 = ppp.project_role_id (+)
and sysdate between ppp.start_date_active(+)
and nvl(ppp.end_date_active(+), sysdate)
and ppp.resource_source_id = ppl.person_id (+)
and sysdate between ppl.effective_start_date(+)
and nvl(ppl.effective_end_date (+), sysdate);
select meaning
from pa_lookups
where lookup_type = 'PA_WORKPLAN_ERROR_NOTIF'
and lookup_code = c_lookup_code;
select page_content
into l_clob
from pa_page_contents
where page_content_id = x_page_content_id for update;
PA_WORKFLOW_UTILS.INSERT_WF_PROCESSES(
p_wf_type_code => 'WORKPLAN'
,p_item_type => 'PAWFPPWP'
,p_item_key => l_item_key
,p_entity_key1 => l_struc_ver_info_rec.project_id
,p_entity_key2 => p_structure_version_id
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT element_version_id
, wbs_level
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id
AND object_type = 'PA_TASKS';
SELECT ptt.prog_entry_enable_flag
FROM pa_task_types ptt
, pa_proj_element_versions ppev
, pa_proj_elements ppe
WHERE ppev.element_version_id = c_element_version_id
AND ppev.proj_element_id = ppe.proj_element_id
AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
AND ppe.TYPE_ID = ptt.task_type_id;
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_element_version_id
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S'
AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
SELECT sum(ppevs.duration)
FROM pa_proj_elem_ver_schedule ppevs
, pa_object_relationships por
, pa_proj_element_versions ppev
, pa_proj_elements ppe
, pa_task_types ptt
WHERE por.object_id_from1 = c_parent_element_version_id
AND por.object_type_to = 'PA_TASKS'
AND por.relationship_type = 'S'
AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND por.object_id_to1 = ppevs.element_version_id
AND por.object_id_to1 = ppev.element_version_id
AND ppev.proj_element_id = ppe.proj_element_id
AND ppevs.project_id = ppe.project_id
AND ppe.TYPE_ID = ptt.task_type_id
AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
AND ptt.prog_entry_enable_flag = 'Y';
SELECT duration
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_element_version_id
AND project_id = c_project_id;
SELECT sum(weighting_percentage)
FROM PA_OBJECT_RELATIONSHIPS
WHERE object_id_from1 = c_parent_element_version_id
AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S';
UPDATE PA_OBJECT_RELATIONSHIPS
SET weighting_percentage = 0
WHERE object_id_from1 = l_parent_element_version_id
AND object_id_to1 = l_element_version_id
AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S';
UPDATE PA_OBJECT_RELATIONSHIPS
SET weighting_percentage = l_task_weight
WHERE object_id_from1 = l_parent_element_version_id
AND object_id_to1 = l_element_version_id
AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S';
SELECT *
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = c_structure_version_id;
SELECT a.*
FROM PA_PROJ_ELEM_VER_STRUCTURE a,
PA_PROJ_ELEMENT_VERSIONS b
WHERE b.element_version_id = c_structure_version_id
AND b.project_id = a.project_id
AND b.element_version_id = a.project_id;
SELECT a.*
FROM PA_PROJ_ELEM_VER_SCHEDULE a,
PA_PROJ_ELEMENT_VERSIONS b
WHERE b.element_version_id = c_element_version_id
AND b.project_id = a.project_id
AND b.element_version_id = a.element_version_id;
SELECT pst.structure_type_class_code
FROM PA_STRUCTURE_TYPES pst,
PA_PROJ_ELEMENT_VERSIONS ppev,
PA_PROJ_STRUCTURE_TYPES ppst
WHERE ppev.element_version_id = c_structure_version_id
AND ppev.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id;
SELECT 'Y'
FROM PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = c_structure_version_id
AND EXISTS
(SELECT 'Y'
FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
PA_PROJ_ELEM_VER_STRUCTURE ppevs
WHERE ppev2.proj_element_id = ppev.proj_element_id
AND ppev2.project_id = ppev.project_id
AND ppevs.project_id = ppev2.project_id
AND ppevs.element_version_id = ppev2.element_version_id
AND ppevs.status_code <> 'STRUCTURE_PUBLISHED');
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
PA_PROJ_ELEMENT_VERSIONS_PKG.INSERT_ROW(
X_ROW_ID => l_rowid
--,X_ELEMENT_VERSION_ID => l_new_struct_ver_id * commented for Bug Fix: 453786
,X_ELEMENT_VERSION_ID => l_tmp_struct_ver_id -- added for bug Fix: 4537865
,X_PROJ_ELEMENT_ID => l_structure_ver_rec.proj_element_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => l_project_id
,X_PARENT_STRUCTURE_VERSION_ID => l_new_struct_ver_id
,X_DISPLAY_SEQUENCE => NULL
,X_WBS_LEVEL => NULL
,X_WBS_NUMBER => '0'
,X_ATTRIBUTE_CATEGORY => l_structure_ver_rec.attribute_category
,X_ATTRIBUTE1 => l_structure_ver_rec.attribute1
,X_ATTRIBUTE2 => l_structure_ver_rec.attribute2
,X_ATTRIBUTE3 => l_structure_ver_rec.attribute3
,X_ATTRIBUTE4 => l_structure_ver_rec.attribute4
,X_ATTRIBUTE5 => l_structure_ver_rec.attribute5
,X_ATTRIBUTE6 => l_structure_ver_rec.attribute6
,X_ATTRIBUTE7 => l_structure_ver_rec.attribute7
,X_ATTRIBUTE8 => l_structure_ver_rec.attribute8
,X_ATTRIBUTE9 => l_structure_ver_rec.attribute9
,X_ATTRIBUTE10 => l_structure_ver_rec.attribute10
,X_ATTRIBUTE11 => l_structure_ver_rec.attribute11
,X_ATTRIBUTE12 => l_structure_ver_rec.attribute12
,X_ATTRIBUTE13 => l_structure_ver_rec.attribute13
,X_ATTRIBUTE14 => l_structure_ver_rec.attribute14
,X_ATTRIBUTE15 => l_structure_ver_rec.element_version_id
,X_TASK_UNPUB_VER_STATUS_CODE => NULL
,X_SOURCE_OBJECT_ID => l_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => l_pev_schedule_id
,X_ELEMENT_VERSION_ID => l_new_struct_ver_id
,X_PROJECT_ID => l_ver_schedule_attr_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => l_ver_schedule_attr_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_ver_schedule_attr_rec.SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_ver_schedule_attr_rec.SCHEDULED_FINISH_DATE
,X_OBLIGATION_START_DATE => l_ver_schedule_attr_rec.OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_ver_schedule_attr_rec.OBLIGATION_FINISH_DATE
,X_ACTUAL_START_DATE => l_ver_schedule_attr_rec.ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ver_schedule_attr_rec.ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ver_schedule_attr_rec.ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ver_schedule_attr_rec.ESTIMATED_FINISH_DATE
,X_DURATION => l_ver_schedule_attr_rec.DURATION
,X_EARLY_START_DATE => l_ver_schedule_attr_rec.EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_ver_schedule_attr_rec.EARLY_FINISH_DATE
,X_LATE_START_DATE => l_ver_schedule_attr_rec.LATE_START_DATE
,X_LATE_FINISH_DATE => l_ver_schedule_attr_rec.LATE_FINISH_DATE
,X_CALENDAR_ID => l_ver_schedule_attr_rec.CALENDAR_ID
,X_MILESTONE_FLAG => l_ver_schedule_attr_rec.MILESTONE_FLAG
,X_CRITICAL_FLAG => l_ver_schedule_attr_rec.CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_ver_schedule_attr_rec.wq_planned_quantity
,X_PLANNED_EFFORT => l_ver_schedule_attr_rec.planned_effort
,X_ACTUAL_DURATION => l_ver_schedule_attr_rec.actual_duration
,X_ESTIMATED_DURATION => l_ver_schedule_attr_rec.estimated_duration
,X_ATTRIBUTE_CATEGORY => l_ver_schedule_attr_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ver_schedule_attr_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => l_ver_schedule_attr_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => l_ver_schedule_attr_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => l_ver_schedule_attr_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => l_ver_schedule_attr_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => l_ver_schedule_attr_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => l_ver_schedule_attr_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => l_ver_schedule_attr_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => l_ver_schedule_attr_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => l_ver_schedule_attr_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => l_ver_schedule_attr_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => l_ver_schedule_attr_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => l_ver_schedule_attr_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => l_ver_schedule_attr_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => l_ver_schedule_attr_rec.ATTRIBUTE15
,X_SOURCE_OBJECT_ID => l_ver_schedule_attr_rec.PROJECT_ID
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,FINANCIAL_TASK_FLAG
,attribute15 --this column is used to store structure ver id of the source str to be used to created relationships.
,source_object_id
,source_object_type
)
SELECT
pa_proj_element_versions_s.nextval
,ppev.proj_element_id
,ppev.object_type
,l_project_id
,l_new_struct_ver_id
,ppev.display_sequence
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppev.RECORD_VERSION_NUMBER
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,ppev.FINANCIAL_TASK_FLAG
,ppev.element_version_id
,l_project_id
,'PA_PROJECTS'
FROM ( SELECT ppev2.* from pa_proj_element_versions ppev2
,pa_proj_elements ppe --bug 4573340
WHERE -- bug#3094283 ppev2.project_id = l_project_id
ppev2.parent_structure_version_id = p_structure_version_id
--bug 4573340
and ppe.project_id = ppev2.project_id
and ppe.proj_element_id = ppev2.proj_element_id
and ppe.link_task_flag = 'N'
--bug 4573340
and ppev2.object_type = 'PA_TASKS'
order by ppev2.display_sequence ) ppev
;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
SELECT /*+ USE_HASH(ppev2 ppev1)*/
pa_object_relationships_s.nextval,
pobj.object_type_from,
ppev1.element_version_id,
pobj.object_type_to,
ppev2.element_version_id,
pobj.relationship_type,
pobj.relationship_subtype,
pobj.Record_Version_Number,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
pobj.weighting_percentage
FROM ( SELECT object_type_from, object_id_from1,
object_type_to, object_id_to1,
relationship_type, relationship_subtype,
Record_Version_Number, weighting_percentage
FROM pa_object_relationships
--bug#3094283 WHERE RELATIONSHIP_TYPE = 'S'
start with object_id_from1 = p_structure_version_id
and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
connect by object_id_from1 = prior object_id_to1
and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE
--bug#3094283 ppev1.project_id = l_project_id
ppev1.attribute15 = pobj.object_id_from1
--bug#3094283 AND ppev2.project_id = l_project_id
AND ppev2.attribute15 = pobj.object_id_to1
and ppev1.parent_structure_version_id = l_new_struct_ver_id
and ppev2.parent_structure_version_id = l_new_struct_ver_id
;
INSERT INTO pa_proj_elem_ver_schedule(
PEV_SCHEDULE_ID
,ELEMENT_VERSION_ID
,PROJECT_ID
,PROJ_ELEMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SCHEDULED_START_DATE
,SCHEDULED_FINISH_DATE
,OBLIGATION_START_DATE
,OBLIGATION_FINISH_DATE
,ACTUAL_START_DATE
,ACTUAL_FINISH_DATE
,ESTIMATED_START_DATE
,ESTIMATED_FINISH_DATE
,DURATION
,EARLY_START_DATE
,EARLY_FINISH_DATE
,LATE_START_DATE
,LATE_FINISH_DATE
,CALENDAR_ID
,MILESTONE_FLAG
,CRITICAL_FLAG
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,WQ_PLANNED_QUANTITY
,PLANNED_EFFORT
,ACTUAL_DURATION
,ESTIMATED_DURATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,source_object_id
,source_object_type
,CONSTRAINT_TYPE_CODE
,CONSTRAINT_DATE
,FREE_SLACK
,TOTAL_SLACK
,EFFORT_DRIVEN_FLAG
,LEVEL_ASSIGNMENTS_FLAG
,EXT_ACT_DURATION
,EXT_REMAIN_DURATION
,EXT_SCH_DURATION
,DEF_SCH_TOOL_TSK_TYPE_CODE -- Fix For Bug # 4321287.
)
SELECT
pa_proj_elem_ver_schedule_s.nextval
,ppev1.ELEMENT_VERSION_ID
,l_PROJECT_ID
,ppev1.PROJ_ELEMENT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,ppevs.SCHEDULED_START_DATE
,ppevs.SCHEDULED_FINISH_DATE
,ppevs.OBLIGATION_START_DATE
,ppevs.OBLIGATION_FINISH_DATE
,ppevs.ACTUAL_START_DATE
,ppevs.ACTUAL_FINISH_DATE
,ppevs.ESTIMATED_START_DATE
,ppevs.ESTIMATED_FINISH_DATE
,ppevs.DURATION
,ppevs.EARLY_START_DATE
,ppevs.EARLY_FINISH_DATE
,ppevs.LATE_START_DATE
,ppevs.LATE_FINISH_DATE
,ppevs.CALENDAR_ID
,ppevs.MILESTONE_FLAG
,ppevs.CRITICAL_FLAG
,ppevs.RECORD_VERSION_NUMBER
,l_login_id
,ppevs.WQ_PLANNED_QUANTITY
,ppevs.PLANNED_EFFORT
,ppevs.ACTUAL_DURATION
,ppevs.ESTIMATED_DURATION
,ppevs.ATTRIBUTE_CATEGORY
,ppevs.ATTRIBUTE1
,ppevs.ATTRIBUTE2
,ppevs.ATTRIBUTE3
,ppevs.ATTRIBUTE4
,ppevs.ATTRIBUTE5
,ppevs.ATTRIBUTE6
,ppevs.ATTRIBUTE7
,ppevs.ATTRIBUTE8
,ppevs.ATTRIBUTE9
,ppevs.ATTRIBUTE10
,ppevs.ATTRIBUTE11
,ppevs.ATTRIBUTE12
,ppevs.ATTRIBUTE13
,ppevs.ATTRIBUTE14
,ppevs.ATTRIBUTE15
,l_PROJECT_ID
,'PA_PROJECTS'
,ppevs.CONSTRAINT_TYPE_CODE
,ppevs.CONSTRAINT_DATE
,ppevs.FREE_SLACK
,ppevs.TOTAL_SLACK
,ppevs.EFFORT_DRIVEN_FLAG
,ppevs.LEVEL_ASSIGNMENTS_FLAG
,ppevs.EXT_ACT_DURATION
,ppevs.EXT_REMAIN_DURATION
,ppevs.EXT_SCH_DURATION
,ppevs.DEF_SCH_TOOL_TSK_TYPE_CODE -- Fix For Bug # 4321287.
FROM pa_proj_elem_ver_schedule ppevs,
pa_proj_element_versions ppev1
where ppev1.attribute15 = ppevs.element_version_id
and ppevs.project_id = l_project_id
and ppev1.project_id = l_project_id
and ppev1.parent_structure_version_id = l_new_struct_ver_id
and ppev1.object_type = 'PA_TASKS';
Select Element_Version_ID, attribute15 BULK COLLECT
INTO New_Versions_Tab, Old_Versions_Tab
From PA_Proj_Element_Versions
Where Project_ID = l_project_id
AND parent_structure_version_id = l_new_struct_ver_id;
UPDATE pa_proj_element_versions ppevs1
SET attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
where ppevs2.project_id = l_project_id
and parent_structure_version_id = p_structure_version_id
and ppevs2.element_version_id = ppevs1.attribute15
)
WHERE project_id = l_project_id
AND parent_structure_version_id = l_new_struct_ver_id
;
procedure update_sch_dirty_flag(
p_project_id IN NUMBER := NULL
,p_structure_version_id IN NUMBER
,p_dirty_flag IN VARCHAR2 := 'N'
,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_dirty_flag VARCHAR2(1);
SELECT project_id
FROM pa_proj_elem_ver_structure
WHERE element_version_id = cp_structure_version_id;*/
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = cp_structure_version_id;
UPDATE pa_proj_elem_ver_structure
SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
WHERE element_version_id = p_structure_version_id
AND project_id = l_proj_id;
UPDATE pa_proj_elem_ver_structure
SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
WHERE element_version_id = p_structure_version_id
AND project_id = p_project_id;
p_procedure_name => 'update_sch_dirty_flag',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_sch_dirty_flag;
PROCEDURE update_structures_setup_attr
( 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
,p_workplan_enabled_flag IN VARCHAR2
,p_financial_enabled_flag IN VARCHAR2
,p_sharing_enabled_flag IN VARCHAR2
--FP M changes bug 3301192
,p_deliverables_enabled_flag IN VARCHAR2
,p_sharing_option_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--End FP M changes bug 3301192
,p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,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
,p_cbs_version_id IN NUMBER default NULL --added for CBS 15834912
,p_cbs_enable_flag IN VARCHAR2 default 'N' --Bug16083858
)
IS
l_ret_stat VARCHAR2(1);
select name, target_start_date, target_finish_date, sys_program_flag, allow_multi_program_rollup
from pa_projects_all
where project_id = p_project_id;
select template_flag, record_version_number
from pa_projects_all
where project_id = p_project_id;
select b.proj_element_id, a.record_version_number
from pa_proj_workplan_attr a,
pa_proj_elements b,
pa_proj_structure_types c,
pa_structure_types d
where a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.project_id = p_project_id
and b.proj_element_id = c.proj_element_id
and c.structure_type_id = d.structure_type_id
and d.structure_type_class_code = 'WORKPLAN';
select a.rowid
from pa_proj_structure_types a,
pa_structure_types b
where a.proj_element_id = c_structure_id
and a.structure_type_id = b.structure_type_id
and b.structure_type_class_code = 'WORKPLAN';
select element_version_id
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
and project_id = p_project_id
and status_code = 'STRUCTURE_PUBLISHED'
and LATEST_EFF_PUBLISHED_FLAG = 'Y';
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = p_project_id
and a.object_type = 'PA_STRUCTURES'
and a.proj_element_id = b.proj_element_id
and b.structure_type_id = c.structure_type_id
--and c.structure_type_class_code = 'WORKPLAN';
select b.element_version_id, b.record_version_number
from pa_proj_element_versions a,
pa_proj_element_versions b
where a.element_version_id = c_keep_struc_ver_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.element_version_id <> c_keep_struc_ver_id
and b.object_type = 'PA_STRUCTURES';
select a.element_version_id, a.record_version_number
from pa_proj_element_versions a,
pa_proj_elements b
where a.proj_element_id = b.proj_element_id
and a.project_id = b.project_id
and b.proj_element_id = c_struc_id;
select PEV_STRUCTURE_ID, record_version_number
from pa_proj_elem_ver_structure
where project_id = p_project_id
and element_version_id = c_struc_ver_id;
select *
from pa_proj_workplan_attr
where proj_element_id = c_struc_id;
select *
from pa_proj_progress_attr
where project_id = p_project_id
and object_type = 'PA_STRUCTURES'
and object_id = c_struc_id;
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = p_project_id
and a.object_type = 'PA_STRUCTURES'
and a.proj_element_id = b.proj_element_id
and b.structure_type_id = c.structure_type_id
--and c.structure_type_class_code = 'FINANCIAL';
select structure_type_id
from pa_structure_types
where structure_type_class_code = 'WORKPLAN';
select element_version_id
from pa_proj_element_versions
where project_id = p_project_id
and proj_element_id = c_structure_id
and object_type = 'PA_STRUCTURES';
select pev.object_type, pev.proj_element_id, pev.element_version_id
from pa_proj_element_versions pev, pa_object_relationships rel
where pev.parent_structure_version_id = c_struc_ver_id
and rel.object_id_to1 = pev.element_version_id
and rel.relationship_type = 'S'
and NOT EXISTS (
select 1
from pa_object_Relationships
where object_id_from1 = pev.element_version_id
and relationship_type = 'S'
);
select start_date, completion_date
from pa_tasks
where task_id = c_task_id;
select target_start_date, target_finish_date, calendar_id
from pa_projects_all
where project_id = p_project_id;
select v.element_version_id
from pa_proj_element_versions v,
pa_object_relationships r
where v.element_version_id = r.object_id_to1
and r.object_id_from1 = c_structure_version_id
and r.object_type_from = 'PA_STRUCTURES';
select a.scheduled_start_date, a.scheduled_finish_date
from pa_proj_elem_ver_schedule a
where a.project_id = c_project_id
and a.element_version_id = c_element_version_id;
l_update_proc_wbs_flag VARCHAR2(1);
select structure_type_id
from pa_structure_types
where structure_type_class_code = 'FINANCIAL';
select 'Y'
from pa_object_relationships por
where (por.object_id_from2 = c_project_id
or por.object_id_to2 = c_project_id)
and por.relationship_type in ('LW', 'LF');
select por1.object_relationship_id obj_rel_id
, por1.object_id_from2 src_proj_id
, por2.object_id_from1 task_ver_id
, c_project_id dest_proj_id
, ppev.parent_structure_version_id src_str_ver_id
, por1.record_version_number rec_ver_number
from pa_object_relationships por1
, pa_object_relationships por2
, pa_projects_all ppa
, pa_proj_element_versions ppev
, pa_proj_elem_ver_structure ppevs
where por1.object_id_to2 = c_project_id
and por1.relationship_type in ('LW', 'LF')
and por1.object_id_from1 = por2.object_id_to1
and por2.relationship_type = 'S'
and por1.object_id_from2 = ppa.project_id
and ppa.structure_sharing_code in ('SHARE_FULL', 'SHARE_PARTIAL')
and por2.object_id_from1 = ppev.element_version_id
-- Bug Fix 4868867
-- Ram Namburi
-- adding the following additional project id join to avoid the Full Table Scan on
-- pa_proj_elem_ver_structure table and to use N1.
and ppev.project_id = ppevs.project_id
and ppev.parent_structure_version_id = ppevs.element_version_id
and ppevs.current_working_flag = 'Y'
union
-- Select links from the working structure version of the given project to all child projects
-- with sharing enabled.
select por1.object_relationship_id obj_rel_id
, c_project_id src_proj_id
, por2.object_id_from1 task_ver_id
, por1.object_id_to2 dest_proj_id
, ppev.parent_structure_version_id src_str_ver_id
, por1.record_version_number rec_ver_number
from pa_object_relationships por1
, pa_object_relationships por2
, pa_projects_all ppa
, pa_proj_element_versions ppev
, pa_proj_elem_ver_structure ppevs
where por1.object_id_from2 = c_project_id
and por1.relationship_type in ('LW', 'LF')
and por1.object_id_from1 = por2.object_id_to1
and por2.relationship_type = 'S'
and por1.object_id_to2 = ppa.project_id
and ppa.structure_sharing_code in ('SHARE_FULL', 'SHARE_PARTIAL')
and por2.object_id_from1 = ppev.element_version_id
-- Bug Fix 4868867
-- Ram Namburi
-- adding the following additional project id join to avoid the Full Table Scan on
-- pa_proj_elem_ver_structure table and to use N1.
and ppev.project_id = ppevs.project_id
and ppev.parent_structure_version_id = ppevs.element_version_id
and ppevs.current_working_flag = 'Y';
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.update_structures_setup_attr');
savepoint update_struc_setup_attr_pvt;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
,X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
,X_PROJ_ELEMENT_ID => l_structure_id
,X_STRUCTURE_TYPE_ID => l_fin_struc_type_id
,X_RECORD_VERSION_NUMBER => 1
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL
);
,P_NEXT_PROGRESS_UPDATE_DATE => to_date(null)
,p_TASK_WEIGHT_BASIS_CODE => 'COST'
,X_PROJ_PROGRESS_ATTR_ID => l_proj_progress_attr_id
,P_ALLOW_COLLAB_PROG_ENTRY => 'N'
,P_ALLW_PHY_PRCNT_CMP_OVERRIDES => 'Y'
,p_structure_type => 'FINANCIAL' --Amit
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
,X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
,X_PROJ_ELEMENT_ID => l_structure_id
,X_STRUCTURE_TYPE_ID => l_struc_type_id
,X_RECORD_VERSION_NUMBER => 1
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL);
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.add_planning_transactions:'||SQLERRM,1,240));
INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
pev_schedule_id,
element_version_id,
project_id,
proj_element_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
scheduled_start_date,
scheduled_finish_date,
milestone_flag,
critical_flag,
calendar_id,
record_version_number,
last_update_login,
source_object_id,
source_object_type)
SELECT
pa_proj_elem_ver_schedule_s.nextval,
PPEV.element_version_id,
PPEV.project_id,
PPEV.proj_element_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
'N',
'N',
NULL,
0,
FND_GLOBAL.LOGIN_ID,
PPEV.project_id,
'PA_PROJECTS'
FROM PA_TASKS PT,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE
PPEV.parent_structure_version_id = l_structure_version_id
AND PPEV.proj_element_id = PT.task_id (+);
no more latest bcoz there was an update done by some other api by now.
selcting latest record version number to avoid concurrency issue. maansari*/
OPEN get_template_flag;
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => l_get_sch_dates_cur.scheduled_start_date
,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
,p_record_version_number=> l_proj_rec_ver_num
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
pa_relationship_pub.delete_subproject_association
(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_relationships_id => l_cur_links_rec.obj_rel_id
,p_record_version_number => l_cur_links_rec.rec_ver_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
update pa_proj_elem_ver_structure ppevs
set ppevs.process_update_wbs_flag = 'Y'
where ppevs.element_version_id = l_cur_links_rec.src_str_ver_id
and ppevs.project_id = l_cur_links_rec.src_proj_id;
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
,X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
,X_PROJ_ELEMENT_ID => l_structure_id
,X_STRUCTURE_TYPE_ID => l_fin_struc_type_id
,X_RECORD_VERSION_NUMBER => 1
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL
);
UPDATE pa_proj_elements
SET name = l_name
,element_number = l_name
WHERE proj_element_id = l_structure_id ;
,P_NEXT_PROGRESS_UPDATE_DATE => to_date(null)
,p_TASK_WEIGHT_BASIS_CODE => 'COST'
,X_PROJ_PROGRESS_ATTR_ID => l_proj_progress_attr_id
,P_ALLOW_COLLAB_PROG_ENTRY => 'N'
,P_ALLW_PHY_PRCNT_CMP_OVERRIDES => 'Y'
,p_structure_type => 'FINANCIAL' --Amit
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
update pa_proj_element_versions
set financial_task_flag = 'Y'
where parent_structure_version_id = l_structure_version_id
and object_type = 'PA_TASKS'
and proj_element_id NOT IN
(select proj_element_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_TASKS'
and link_task_flag = 'Y');
pa_relationship_pub.delete_subproject_association
(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_relationships_id => l_cur_links_rec.obj_rel_id
,p_record_version_number => l_cur_links_rec.rec_ver_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
update pa_proj_elem_ver_structure ppevs
set ppevs.process_update_wbs_flag = 'Y'
where ppevs.element_version_id = l_cur_links_rec.src_str_ver_id
and ppevs.project_id = l_cur_links_rec.src_proj_id;
PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_Wo_Val(
p_structure_version_id => l_structure_version_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_keep_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(l_structure_id);
/*moved PA_FIN_PLAN_PVT.delete_wp_budget_versions into plsql block */
BEGIN
/*Commented call to this API and replaced with pa_fin_plan_pvt.Delete_wp_option
for Bug 3954050
PA_FIN_PLAN_PVT.delete_wp_budget_versions(
p_struct_elem_version_id_tbl => l_struct_version_id_tbl
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data);
PA_FIN_PLAN_PVT.delete_wp_option
(
p_project_id => p_project_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB('PA_FIN_PLAN_PVT.delete_wp_options :'||SQLERRM,1,240));
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROGRESS_PUB.DELETE_PROJ_PROG_ATTR(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,P_OBJECT_TYPE => 'PA_STRUCTURES'
,p_object_id => l_structure_id
,p_structure_type => 'WORKPLAN'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
delete from pa_proj_elem_ver_schedule
where project_id =p_project_id --Bug No 3634334
and element_version_id IN (
select element_version_id
from pa_proj_element_versions
where project_id = p_project_id --Bug No 3634334
and parent_structure_version_id = l_keep_structure_ver_id
and object_type IN ('PA_TASKS', 'PA_STRUCTURES'));
/* delete from pa_object_relationships rel
where rel.relationship_type IN ('D','S')
and EXISTS (
select 1 from pa_proj_element_versions
where (rel.object_id_from1 = element_version_id OR
rel.object_id_to1 = element_version_id ) and
parent_structure_version_id = l_keep_structure_ver_id and
financial_task_flag = 'N'); */
delete from pa_object_relationships rel
where OBJECT_RELATIONSHIP_ID IN (
select OBJECT_RELATIONSHIP_ID
from pa_object_relationships rel,
pa_proj_element_versions
where rel.relationship_type IN ('D','S')
and object_type_to = 'PA_TASKS'
and rel.object_id_to1 = element_version_id
and parent_structure_version_id = l_keep_structure_ver_id
and financial_task_flag = 'N'
UNION
select OBJECT_RELATIONSHIP_ID
from pa_object_relationships rel,
pa_proj_element_versions
where rel.relationship_type IN ('D','S')
and object_type_from = 'PA_TASKS'
and rel.object_id_from1 = element_version_id
and parent_structure_version_id = l_keep_structure_ver_id
and financial_task_flag = 'N');
/* Bug 3906015 Just moved the existing code after the delete statements
to this position
*/
-- NYU
-- delete deliverable associations
PA_DELIVERABLE_PUB.delete_dlv_associations
(p_project_id=>p_project_id,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
delete from pa_proj_elements ppe
where proj_element_ID in (
select proj_element_id
from pa_proj_element_versions
where parent_structure_version_id = l_keep_structure_ver_id
and object_type = 'PA_TASKS'
and financial_task_flag = 'N');
Delete from pa_proj_element_versions
where parent_structure_version_id = l_keep_structure_ver_id
and object_type = 'PA_TASKS'
and financial_task_flag = 'N';
DELETe FROM pa_proj_structure_types
where proj_element_id = l_structure_id
and structure_type_id = (
select structure_type_id from pa_structure_types
where structure_type = 'WORKPLAN');
This is necessary because : delete_dlv_associations API has been modified
in such a way that it retrives the tasks from pa_proj_element_versions table for the passed project_id
and then for those values ,it performs deliverable related validations for Workplan Task Deletion
If this call,is after delete statement on pa_proj_element_versions tables,the logic written
in delete_dlv_associations APi will fail .Hence moved the code up.
-- NYU
-- delete deliverable associations
PA_DELIVERABLE_PUB.delete_dlv_associations
(p_project_id=>p_project_id,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
update pa_proj_workplan_attr
set WP_ENABLE_VERSION_FLAG = 'N'
where proj_element_id = l_structure_id;
update pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_PUBLISHED',
latest_eff_published_flag = 'Y',
published_date = sysdate
where project_id = p_project_id
and element_version_id = l_keep_structure_ver_id;
update pa_proj_elements
set name = l_wp_name,
element_number = l_wp_name
where proj_element_id = l_structure_id;
l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(l_structure_id);
update pa_proj_element_versions
set financial_task_flag = 'Y'
where parent_structure_version_id = l_structure_version_id
and object_type = 'PA_TASKS'
and proj_element_id NOT IN
(select proj_element_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_TASKS'
and link_task_flag = 'Y');
PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_Wo_Val(
p_structure_version_id => l_structure_version_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_keep_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(l_structure_id);
/*moved PA_FIN_PLAN_PVT.delete_wp_budget_versions into plsql block */
BEGIN
/*Commented call to this API and replaced with pa_fin_plan_pvt.Delete_wp_option
for Bug 3954050
PA_FIN_PLAN_PVT.delete_wp_budget_versions(
p_struct_elem_version_id_tbl => l_struct_version_id_tbl
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data);
PA_FIN_PLAN_PVT.delete_wp_option
(
p_project_id => p_project_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB('PA_FIN_PLAN_PVT.delete_wp_option:'||SQLERRM,1,240));
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROGRESS_PUB.DELETE_PROJ_PROG_ATTR(
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,P_OBJECT_TYPE => 'PA_STRUCTURES'
,p_object_id => l_structure_id
,p_structure_type => 'WORKPLAN' --Amit
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
delete from pa_proj_elem_ver_schedule
where project_id =p_project_id --Bug No 3634334
and element_version_id IN (
select element_version_id
from pa_proj_element_versions
where project_id =p_project_id --Bug No 3634334
and parent_structure_version_id = l_keep_structure_ver_id
and object_type IN ('PA_TASKS', 'PA_STRUCTURES'));
DELETe FROM pa_proj_structure_types
where proj_element_id = l_structure_id
and structure_type_id = (
select structure_type_id from pa_structure_types
where structure_type = 'WORKPLAN');
PA_DELIVERABLE_PUB.delete_dlv_associations
(p_project_id=>p_project_id,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
update pa_proj_workplan_attr
set WP_ENABLE_VERSION_FLAG = 'N'
where proj_element_id = l_structure_id;
update pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_PUBLISHED',
latest_eff_published_flag = 'Y',
published_date = sysdate
where project_id = p_project_id
and element_version_id = l_keep_structure_ver_id;
update pa_proj_elements
set name = l_wp_name,
element_number = l_wp_name
where proj_element_id = l_structure_id;
PA_PROJ_STRUC_MAPPING_PUB.DELETE_ALL_MAPPING(
p_project_id => p_project_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJ_STRUC_MAPPING_PUB.DELETE_ALL_MAPPING(
p_project_id => p_project_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
,X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
,X_PROJ_ELEMENT_ID => l_structure_id
,X_STRUCTURE_TYPE_ID => l_struc_type_id
,X_RECORD_VERSION_NUMBER => 1
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL);
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.add_planning_transactions:'||SQLERRM,1,240));
INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
pev_schedule_id,
element_version_id,
project_id,
proj_element_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
scheduled_start_date,
scheduled_finish_date,
milestone_flag,
critical_flag,
calendar_id,
record_version_number,
last_update_login,
source_object_id,
source_object_type)
SELECT
pa_proj_elem_ver_schedule_s.nextval,
PPEV.element_version_id,
PPEV.project_id,
PPEV.proj_element_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
'N',
'N',
NULL,
0,
FND_GLOBAL.LOGIN_ID,
PPEV.project_id,
'PA_PROJECTS'
FROM PA_TASKS PT,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE
PPEV.parent_structure_version_id = l_structure_version_id
AND PPEV.proj_element_id = PT.task_id (+);
update pa_proj_elem_ver_structure ppevs
set ppevs.current_flag = 'Y'
, ppevs.original_flag = 'Y'
, ppevs.record_version_number = (ppevs.record_version_number+1)
where ppevs.project_id = p_project_id
and ppevs.element_version_id = l_structure_version_id;
PA_PROJ_STRUC_MAPPING_PUB.DELETE_ALL_MAPPING(
p_project_id => p_project_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id,
l_del_struc_ver_id,
l_return_status,
l_err_msg_code);
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL(
p_structure_version_id => l_del_struc_ver_id
,p_record_version_number => l_struc_ver_rvn
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
,X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
,X_PROJ_ELEMENT_ID => l_structure_id
,X_STRUCTURE_TYPE_ID => l_struc_type_id
,X_RECORD_VERSION_NUMBER => 1
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL);
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.add_planning_transactions:'||SQLERRM,1,240));
INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
pev_schedule_id,
element_version_id,
project_id,
proj_element_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
scheduled_start_date,
scheduled_finish_date,
milestone_flag,
critical_flag,
calendar_id,
record_version_number,
last_update_login,
source_object_id,
source_object_type
)
SELECT
pa_proj_elem_ver_schedule_s.nextval,
PPEV.element_version_id,
PPEV.project_id,
PPEV.proj_element_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
'N',
'N',
NULL,
0,
FND_GLOBAL.LOGIN_ID,
PPEV.project_id,
'PA_PROJECTS'
FROM PA_TASKS PT,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE
PPEV.parent_structure_version_id = l_structure_version_id
AND PPEV.proj_element_id = PT.task_id (+);
update pa_proj_elem_ver_structure ppevs
set ppevs.current_flag = 'Y'
, ppevs.original_flag = 'Y'
, ppevs.record_version_number = (ppevs.record_version_number+1)
where ppevs.project_id = p_project_id
and ppevs.element_version_id = l_structure_version_id;
update pa_projects_all
set structure_sharing_code = l_new_share_code
where project_id = p_project_id;
PA_DELIVERABLE_PUB.DELETE_DELIVERABLE_STRUCTURE
(p_project_id => p_project_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'DELIVERABLE';
UPDATE pa_projects_all
SET
sys_program_flag = nvl(l_sys_program_flag,'N'),
allow_multi_program_rollup = nvl(l_allow_multi_program_rollup,'N'),
CBS_VERSION_ID=p_cbs_version_id , --added for CBS 15834912
CBS_ENABLE_FLAG=p_cbs_enable_flag --added for CBS 16083858
WHERE project_id = p_project_id;
pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.update_structures_setup_attr end');
ROLLBACK to update_struc_setup_attr_pvt;
ROLLBACK to update_struc_setup_attr_pvt;
p_procedure_name => 'update_structures_setup_attr',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_structures_setup_attr;