[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure DELETE
(
errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_operating_unit in number default null,
p_from_project in varchar2 default null,
p_to_project in varchar2 default null,
p_fp_option in varchar2 default null,
p_plan_type in number default null,
p_wp_option in varchar2 default null,
p_rep_only in varchar2 default 'Y'
)
is
l_from_project_num varchar2(25);
select project_id
from pa_projects_all
where segment1 between l_from_project_num and l_to_project_num;
pa_debug.log_message('Argument => Delete Financial Plans ['||p_fp_option||']', 1);
pa_debug.log_message('Argument => Delete Workplans ['||p_wp_option||']', 1);
select min(segment1) ,max(segment1)
into l_from_project_num, l_to_project_num
from pa_projects_all
where segment1 between nvl(p_from_project,segment1) and nvl(p_to_project,segment1)
and decode(p_operating_unit,NULL,org_id,p_operating_unit) = org_id; /* Added for bug 9072943 */
/* Call procedure to delete eligible workplan versions */
DELETE_WP(p_project_id => c1.project_id,
p_rep_only => l_rep_only,
p_return_status => l_return_status);
/* Call procedure to delete eligible financial plan versions */
DELETE_FP(p_project_id => c1.project_id,
p_plan_type_id => l_plan_type_id,
p_rep_only => l_rep_only,
p_return_status => l_return_status);
end DELETE;
select pa.segment1 num,wp.name wp_name
from pa_projects_all pa, pa_proj_elem_ver_structure wp
where pa.segment1 between p_from_proj and p_to_proj
and pa.project_id = wp.project_id
and wp.purged_flag = 'Y'
and wp.conc_request_id = FND_GLOBAL.CONC_REQUEST_ID; /* Modified for bug 9049425 */
select pa.segment1 num,fp.version_name fp_name
from pa_projects_all pa, pa_budget_versions fp
where pa.segment1 between p_from_proj and p_to_proj
and pa.project_id = fp.project_id
and fp.purged_flag = 'Y'
and fp.fin_plan_type_id <> 10
and fp.request_id = FND_GLOBAL.CONC_REQUEST_ID; /* Modified for bug 9049425 */
FND_MESSAGE.SET_NAME('PA', 'PA_CISI_TEXT_DELETE');
procedure DELETE_WP(p_project_id IN number,
p_rep_only IN varchar2,
p_return_status OUT nocopy varchar2) is
l_api_version_number NUMBER(10,3) := 1.0; -- API Version
select element_version_id, record_version_number
from pa_proj_elem_ver_structure struct_ver
where project_id = l_project_id
and status_code = 'STRUCTURE_PUBLISHED'
and current_flag <> 'Y'
and original_flag <> 'Y'
and nvl(purged_flag,'N') <> 'Y'
and latest_eff_published_flag <> 'Y'
and NOT EXISTS (
SELECT por.object_relationship_id
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE por.object_id_from1 = ppev.element_version_id
AND por.relationship_type in ('LW', 'LF')
AND ppev.parent_structure_version_id = struct_ver.element_version_id
UNION ALL
SELECT por. object_relationship_id
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE por.object_id_to1 = ppev.element_version_id
AND por.relationship_type in ('LW', 'LF')
AND ppev.parent_structure_version_id = struct_ver.element_version_id);
pa_debug.log_message('=======Delete Workplans Start =======', 1);
pa_debug.log_message('** Delete only Reporting Data **', 1);
select budget_version_id
into l_budget_version_id
from pa_budget_versions
where project_id = l_project_id
and fin_plan_type_id = 10
and project_structure_version_id = rec1.element_version_id;
pa_debug.log_message('To Delete Plan Version id :'||l_budget_version_id, 1);
delete from pji_fp_xbs_accum_f
where project_id = l_project_id
and plan_type_id = 10
and plan_version_id = l_budget_version_id;
pa_debug.log_message('Records deleted :'||sql%rowcount, 1);
else /* else of Delete only reporting data only */
-- Fetching all the Published Workplan Version except Latest Pubished,
-- Original Baseline and Current Working Versions.
FOR rec IN c1 LOOP
l_structure_version_id_tbl.extend(1);
pa_debug.log_message('Calling Delete API for workplans', 1);
PA_PROJECT_STRUCTURE_PUB1.DELETE_PUBLISHED_STRUCTURE_VER
( p_api_version => l_api_version_number
,p_init_msg_list => l_init_msg_list
,p_project_id => l_project_id
,p_structure_version_id_tbl => l_structure_version_id_tbl
,p_record_version_number_tbl => l_record_version_number_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
end if; /* else of Delete only reporting data only */
pa_debug.log_message('=======Delete Workplans End =======', 1);
END DELETE_WP;
procedure DELETE_FP(p_project_id IN number,
p_plan_type_id IN number,
p_rep_only IN varchar2,
p_return_status OUT nocopy varchar2) is
l_return_status VARCHAR2(1);
SELECT budget_version_id,record_version_number
FROM pa_budget_versions
WHERE project_id = l_project_id
AND fin_plan_type_id <> 10
AND budget_status_code = 'B'
AND current_flag <> 'Y'
AND current_original_flag <> 'Y'
and nvl(purged_flag,'N') <> 'Y';
SELECT budget_version_id,record_version_number
FROM pa_budget_versions
WHERE project_id = l_project_id
AND fin_plan_type_id = l_plan_type_id
AND budget_status_code = 'B'
AND current_flag <> 'Y'
AND current_original_flag <> 'Y'
and nvl(purged_flag,'N') <> 'Y';
pa_debug.log_message('=======Delete Financial Plans Start =======', 1);
pa_debug.log_message('** Delete only Reporting Data **', 1);
pa_debug.log_message('To Delete Plan Version id :'||rec.budget_version_id, 1);
delete from pji_fp_xbs_accum_f
where project_id = l_project_id
and plan_version_id = rec.budget_version_id;
pa_debug.log_message('Records deleted :'||sql%rowcount, 1);
pa_debug.log_message('To Delete Plan Version id :'||rec.budget_version_id, 1);
pa_fin_plan_pub.Delete_Version
(p_project_id => l_project_id,
p_budget_version_id => rec.budget_version_id,
p_record_version_number => rec.record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.log_message('=======Delete Financial Plans End =======', 1);
pa_debug.log_message('** Delete only Reporting Data **', 1);
pa_debug.log_message('To Delete Plan Version id :'||rec.budget_version_id, 1);
delete from pji_fp_xbs_accum_f
where project_id = l_project_id
and plan_version_id = rec.budget_version_id;
pa_debug.log_message('Records deleted :'||sql%rowcount, 1);
pa_debug.log_message('To Delete Plan Version id :'||rec.budget_version_id, 1);
pa_fin_plan_pub.Delete_Version
(p_project_id => l_project_id,
p_budget_version_id => rec.budget_version_id,
p_record_version_number => rec.record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.log_message('=======Delete Financial Plans End =======', 1);
END DELETE_FP;