[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM PA_PROJ_ELEM_VER_STRUCTURE pevs,
FND_USER fu
WHERE pevs.element_version_id = c_version_id
AND pevs.lock_status_code = 'LOCKED'
AND fu.user_id = c_user_id
AND pevs.locked_by_person_id = fu.employee_id
AND pevs.project_id = c_project_id;
SELECT 'Y'
FROM PA_PROJ_ELEM_VER_STRUCTURE
WHERE element_version_id = c_version_id
AND lock_status_code = 'LOCKED'
AND project_id = c_project_id;
SELECT 'Y'
FROM PA_PROJ_ELEM_VER_STRUCTURE
WHERE element_version_id = c_version_id
AND published_date is not null
AND project_id = c_project_id;
SELECT project_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = c_version_id;
/* SELECT project_id
INTO l_project_id
FROM PA_PROJ_ELEM_VER_STRUCTURE
WHERE element_version_id = p_version_id;
,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
,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_api_name CONSTANT VARCHAR(30) := 'CREATE_STRUCTURE';
,p_next_progress_update_date => p_next_progress_update_date
,p_action_set_id => p_action_set_id
,x_structure_id => l_structure_id
,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_api_name CONSTANT VARCHAR(30) := 'UPDATE_STRUCTURE';
pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE begin');
savepoint update_structure;
select 'x' into l_dummy
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 'x' into l_dummy
from PA_PROJ_ELEMENTS
where proj_element_id = p_structure_id
and record_version_number = p_record_version_number;
PA_PROJECT_STRUCTURE_PVT1.Update_Structure(
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_structure_id => p_structure_id
,p_structure_number => p_structure_number
,p_structure_name => p_structure_name
,p_description => p_description
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_record_version_number => p_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_PUB1.UPDATE_STRUCTURE end');
rollback to update_structure;
rollback to update_structure;
p_procedure_name => 'UPDATE_STRUCTURE',
p_error_text => x_msg_data); -- 4537865
rollback to update_structure;
p_procedure_name => 'UPDATE_STRUCTURE',
p_error_text => x_msg_data); -- 4537865
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_api_name CONSTANT VARCHAR(30) := 'Update_Structure_Version_Attr';
pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.Update_Structure_Version_Attr');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE_VERSION_ATTR begin');
savepoint update_structure_version_attr;
PA_PROJECT_STRUCTURE_PVT1.Update_Structure_Version_Attr(
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_pev_structure_id => p_pev_structure_id
,p_structure_version_name => p_structure_version_name
,p_structure_version_desc => p_structure_version_desc
,p_effective_date => p_effective_date
,p_latest_eff_published_flag => p_latest_eff_published_flag
,p_locked_status_code => p_locked_status_code
,p_struct_version_status_code => p_struct_version_status_code
,p_baseline_current_flag => p_baseline_current_flag
,p_baseline_original_flag => p_baseline_original_flag
,p_change_reason_code => p_change_reason_code
,p_record_version_number => p_record_version_number
,p_current_working_ver_flag => p_current_working_ver_flag
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE_VERSION_ATTR END');
rollback to update_structure_version_attr;
rollback to update_structure_version_attr;
p_procedure_name => 'Update_Structure_Version_Attr',
p_error_text => x_msg_data) ; -- 4537865
rollback to update_structure_version_attr;
p_procedure_name => 'Update_Structure_Version_Attr',
p_error_text => x_msg_data); -- 4537865
END Update_Structure_Version_Attr;
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_calling_from IN VARCHAR2 := 'XYZ' ---Added for bug 6023347
,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_Structure_Version';
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.init_err_stack('PA_PROJECT_STRUCTURE_PUB1.Delete_Structure_Version');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION begin');
savepoint delete_structure_version;
PA_PROJECT_STRUCTURE_PUB1.DELETE_PUBLISHED_STRUCTURE_VER(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_project_id => l_project_id
,p_structure_version_id_tbl => l_str
,p_record_version_number_tbl => l_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(l_project_id,
p_structure_version_id,
l_return_status,
l_error_message_code);
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_PUB1.DELETE_TASK_VERSION(p_commit => 'N',
p_debug_mode => p_debug_mode,
p_structure_version_id => l_parent_struc_ver_id,
p_task_version_id => l_task_version_id,
p_record_version_number => l_task_rvn,
p_called_from_api => 'DELETE_STRUCTURE_VERSION', -- Bug 3056077
p_calling_from => p_calling_from, -- Bug 6023347
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 => l_structure_type --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_PUB1.DELETE_STRUCTURE_VERSION end');
rollback to delete_structure_version;
rollback to delete_structure_version;
p_procedure_name => 'Delete_Structure_Version',
p_error_text => x_msg_data); -- 4537865
rollback to delete_structure_version;
p_procedure_name => 'Delete_Structure_Version',
p_error_text => x_msg_data); -- 4537865
END DELETE_STRUCTURE_VERSION;
select record_version_number
from pa_proj_elem_ver_structure
where project_id = c_project_id
and element_version_id = c_structure_version_id;
select project_id
from pa_proj_element_versions
where element_version_id = p_structure_version_id;
-- workflow notification will be initiated from PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_CONC
-- We will get concurrent id here to determine if this is ONLINE or CONCURRENT mode
OPEN c1;
select conc_request_id INTO l_conc_req_id
from pa_proj_elem_ver_structure
where element_version_id = p_structure_version_id
and project_id = l_project_id;
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 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--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 phase 2
,p_cbs_enable_flag IN VARCHAR2 default 'N' --Bug16083858
)
IS
l_api_name CONSTANT VARCHAR(30) := 'UPDATE_STRUCTURES_SETUP_ATTR';
pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr begin');
savepoint update_struc_setup_attr_pub;
PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURES_SETUP_ATTR(
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 => p_project_id
,p_workplan_enabled_flag => p_workplan_enabled_flag
,p_financial_enabled_flag => p_financial_enabled_flag
,p_sharing_enabled_flag => 'N' --p_sharing_enabled_flag
,p_deliverables_enabled_flag => p_deliverables_enabled_flag
,p_sharing_option_code => p_sharing_option_code
,p_sys_program_flag => p_sys_program_flag
,p_allow_multi_prog_rollup => p_allow_multi_prog_rollup
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_cbs_version_id => p_cbs_version_id
,p_cbs_enable_flag => p_cbs_enable_flag -- Bug16083858
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURES_SETUP_ATTR end');
rollback to update_struc_setup_attr_pub;
rollback to update_struc_setup_attr_pub;
p_procedure_name => 'update_structures_setup_attr',
p_error_text => x_msg_data); -- 4537865
rollback to update_struc_setup_attr_pub;
p_procedure_name => 'update_structures_setup_attr',
p_error_text => x_msg_data); -- 4537865
END update_structures_setup_attr;
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_api_name CONSTANT VARCHAR(30) := 'update_workplan_versioning';
pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr begin');
savepoint update_wp_versioning_pub;
PA_PROJECT_STRUCTURE_PVT1.update_workplan_versioning(
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_proj_element_id => p_proj_element_id
,p_enable_wp_version_flag => p_enable_wp_version_flag
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_WORKPLAN_VERSIONING end');
rollback to update_wp_versioning_pub;
rollback to update_wp_versioning_pub;
p_procedure_name => 'update_workplan_versioning',
p_error_text => x_msg_data); -- 4537865
rollback to update_wp_versioning_pub;
p_procedure_name => 'update_workplan_versioning',
p_error_text => x_msg_data); -- 4537865
END update_workplan_versioning;
procedure Delete_Working_Struc_Ver
(
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_Working_Struc_Ver';
select project_id
from pa_proj_element_versions
where element_version_id = p_structure_version_id;
pa_debug.init_err_stack('PA_PROJECT_STRUCTURE_PUB1.Delete_Working_Struc_Ver');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_WORKINGSTRUC_VER begin');
savepoint delete_working_struc_ver;
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
l_project_id
,p_structure_version_id
,l_return_status
,l_error_message_code);
PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_Wo_Val(p_commit => 'N',
p_debug_mode => p_debug_mode,
p_structure_version_id => p_structure_version_id,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION end');
rollback to delete_working_struc_ver;
rollback to delete_working_struc_ver;
p_procedure_name => 'Delete_Working_Struc_Ver',
p_error_text => x_msg_data); -- 4537865
rollback to delete_working_struc_ver;
p_procedure_name => 'Delete_Working_Struc_Ver',
p_error_text => x_msg_data); -- 4537865
END DELETE_WORKING_STRUC_VER;
,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
);
SELECT task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND top_task_id = task_id
AND parent_task_id is NULL;
SELECT element_version_id, record_version_number
FROM pa_proj_element_versions
where proj_element_id = c_structure_id
and project_id = p_project_id;
select a.proj_element_id into l_parent_structure_id
from pa_proj_elements a, pa_proj_structure_types b, pa_structure_types c
where a.proj_element_id = b.proj_element_id
and a.project_id = p_project_id
and b.structure_type_id = c.structure_type_id
and c.structure_type = 'FINANCIAL';
PA_TASK_UTILS.CHECK_DELETE_TASK_OK(x_task_id => l_task_id,
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
select rowid into l_rowid from pa_proj_progress_attr
where project_id = p_project_id
and object_id = l_task_id ;
PA_PROJ_PROGRESS_ATTR_PKG.delete_row(l_rowid);
PA_PROJECT_CORE.DELETE_TASK(x_task_id => l_task_id,
x_validate_flag => 'N',
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET FINANCIAL_TASK_FLAG = 'N'
WHERE PROJECT_ID = p_project_id
AND PROJ_ELEMENT_ID = l_task_id;
PA_PROJ_TASK_STRUC_PUB.DELETE_TASK_STRUCTURE(p_calling_module => 'PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE',
p_project_id => p_project_id,
p_task_id => l_task_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT B.PROJ_ELEMENT_ID INTO l_proj_element_id FROM PA_PROJECTS_ALL A,PA_PROJ_STRUCTURE_TYPES B,PA_PROJ_ELEMENTS C
WHERE A.PROJECT_ID = C.PROJECT_ID
AND B.PROJ_ELEMENT_ID = C.PROJ_ELEMENT_ID
AND B.PROJ_STRUCTURE_TYPE_ID = 6
AND A.PROJECT_ID = P_PROJECT_ID;
PA_WORKPLAN_ATTR_PVT.DELETE_PROJ_WORKPLAN_ATTRS(p_project_id => p_project_id,
p_proj_element_id => l_proj_element_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT parent_structure_id, parent_structure_version_id into l_parent_structure_id, l_parent_structure_version_id
FROM PA_STRUCT_TASK_WBS_V
WHERE task_id = l_task_id;
select rowid into l_rowid from pa_proj_progress_attr
where project_id = p_project_id
-- and object_id = l_task_id Amit
and object_id = l_parent_structure_id --Amit
AND structure_type = 'FINANCIAL'; --Amit
PA_PROJ_PROGRESS_ATTR_PKG.delete_row(l_rowid);
SELECT rowid into l_rowid
FROM pa_proj_structure_types
WHERE proj_element_id = l_parent_structure_id
and STRUCTURE_TYPE_ID = 6;
PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
select record_version_number
INTO l_record_version_number
from pa_proj_workplan_attr
where proj_element_id = l_parent_structure_id;
PA_WORKPLAN_ATTR_PVT.DELETE_PROJ_WORKPLAN_ATTRS(p_validate_only => FND_API.G_FALSE,
p_project_id => p_project_id,
p_proj_element_id => l_parent_structure_id,
p_record_version_number => l_record_version_number,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT record_version_number into l_record_version_number
FROM pa_proj_element_versions
WHERE element_version_id = l_parent_structure_version_id;
is getting deleted .
It means indirectly the WP structure is also getting deleted .This should not happen .
*/
IF (l_check_sharing_enabled <> 'Y') THEN -- For Bug 3938654
--hsiu: 3305199
open get_struc_ver_info(l_parent_structure_id);
PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION(
p_structure_version_id => l_parent_structure_version_id
,p_record_version_number => l_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT record_version_number into l_record_version_number
FROM pa_proj_elements
WHERE proj_element_id = l_parent_structure_id;
PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE(
p_structure_id => l_parent_structure_id
,p_record_version_number => l_record_version_number
,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 = 'N'
WHERE PROJECT_ID = p_project_id
AND PARENT_STRUCTURE_VERSION_ID = p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET FINANCIAL_TASK_FLAG='N'
WHERE PROJECT_ID = p_project_id
AND ELEMENT_VERSION_ID = p_task_version_id;
PROCEDURE Update_Sch_Dirty_Flag
(
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 NUMBER := 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_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_api_name CONSTANT VARCHAR(30) := 'UPDATE_SCH_DIRTY_FLAG';
pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_sch_dirty_flag');
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_sch_dirty_flag begin');
savepoint update_sch_dirty_flag_pub;
PA_PROJECT_STRUCTURE_PVT1.Update_Sch_Dirty_Flag(
p_project_id =>p_project_id
,p_structure_version_id =>p_structure_version_id
,p_dirty_flag =>p_dirty_flag
,x_return_status =>x_return_status
,x_msg_count =>x_msg_count
,x_msg_data =>x_msg_data
);
pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_SCH_DIRTY_FLAG end');
ROLLBACK TO update_sch_dirty_flag_pub;
ROLLBACK TO update_sch_dirty_flag_pub;
p_procedure_name => 'update_sch_dirty_flag',
p_error_text => x_msg_data); -- 4537865
ROLLBACK TO update_sch_dirty_flag_pub;
p_procedure_name => 'update_sch_dirty_flag',
p_error_text => x_msg_data); -- 4537865
END update_sch_dirty_flag;
SAMPLE CODE FOR DELETE MULTIPLE VERSIONS:
FOR i in p_structure_version_id_tbl.FIRST..p_structure_version_id_tbl.LAST LOOP -- Call the validation API
-- If Y is returned then proceed with the
-- progress rollup and
-- actual deletion.
-- l_structure_version_rec := p_structure_version_in_tbl(i);
pa_progress_pvt.UPD_PROG_RECS_STR_DELETE(
p_project_id => p_project_id,
p_str_ver_id_to_del => p_structure_version_id_tbl(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_PUB1.delete_working_struc_ver(
p_api_version => 1.0,
p_structure_version_id => p_structure_version_id_tbl(i),
p_record_version_number => p_record_version_number_tbl(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_PUBLISHED_STRUCTURE_VER;
procedure DELETE_PUBLISHED_STRUCTURE_VER
(
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_project_id IN NUMBER
,p_structure_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PUBLISHED_STRUCTURE_VER';
pa_progress_pvt.UPD_PROG_RECS_STR_DELETE(
p_project_id => p_project_id,
p_str_ver_id_to_del => p_structure_version_id_tbl(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_PROJECT_STRUCTURE_PUB1.delete_working_struc_ver(
p_api_version => 1.0,
p_structure_version_id => p_structure_version_id_tbl(i),
p_record_version_number => p_record_version_number_tbl(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_PUBLISHED_STRUCTURE_VER;
PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURES_SETUP_ATTR(
p_api_version => 1.0
,p_init_msg_list => 'T'
,p_commit => 'F'
--,p_validate_only => 'T'
--,p_validation_level => p_validation_level
--,p_calling_module => p_calling_module
,p_debug_mode => 'N'
--,p_max_msg_count => p_max_msg_count
,p_project_id => p_project_id
,p_workplan_enabled_flag => l_wp_enabled
,p_financial_enabled_flag => l_fin_enabled
,p_deliverables_enabled_flag => l_delv_enabled
,p_sharing_option_code => l_share_code
,p_sys_program_flag => 'Y'
,p_allow_multi_prog_rollup => 'N'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID=p_project_id
AND baseline_funding_flag = 'Y');
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PA_RESOURCE_ASSIGNMENTS
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PA_EXPENDITURE_ITEMS_ALL
WHERE PROJECT_ID=p_project_id);
SELECT SYS_PROGRAM_FLAG
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID=p_project_id;
SELECT BUDGET_TYPE_CODE
FROM PA_BUDGET_VERSIONS
WHERE PROJECT_ID=p_project_id;
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID=p_project_id
and CC_TAX_TASK_ID is not null);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PO_DISTRIBUTIONS_ALL
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM AP_INVOICES_ALL
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM PA_COMMITMENT_TXNS
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM AP_EXP_REPORT_DISTS_ALL
WHERE PROJECT_ID=p_project_id);
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(SELECT NULL
FROM MTL_MATERIAL_TRANSACTIONS
WHERE PROJECT_ID=p_project_id);
SELECT BILL_ANOTHER_PROJECT_FLAG
FROM PA_PROJECT_CUSTOMERS
WHERE PROJECT_ID=p_project_id;
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(Select Null from PA_PROJECT_CUSTOMERS
where RECEIVER_TASK_ID in
(Select Task_id From PA_TASKS Where project_id=p_project_id));
SELECT 1 FROM SYS.DUAL WHERE EXISTS
(Select Null from PA_TASKS where PROJECT_ID=p_project_id
and RECEIVE_PROJECT_INVOICE_FLAG='Y');
select NVL(BURDEN_ACCOUNT_FLAG,'N') from PA_PROJECT_TYPES_ALL where
Project_Type = (Select PROJECT_TYPE from PA_PROJECTS_ALL where project_id=p_project_id);