The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
procedure delete_project ( x_project_id IN number
, x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
, x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
, x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_err_stack IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_commit IN VARCHAR2 := FND_API.G_FALSE)
is
old_stack varchar2(630);
l_delete_ok VARCHAR2(1);
select '1'
from dual
where exists (select object_id
from pa_project_parties
where object_id = t_project_id
and object_type = 'PA_PROJECTS');
SELECT template_flag
FROM PA_PROJECTS_ALL
WHERE project_id = x_project_id;
SELECT task_id
FROM PA_TASKS
WHERE project_id = x_project_id;
SAVEPOINT delete_project;
x_err_stack := x_err_stack || '->delete_project';
PA_EGO_WRAPPER_PUB.check_delete_project_ok(
p_api_version => 1.0 ,
p_project_id => x_project_id ,
p_init_msg_list => NULL ,
x_delete_ok => l_delete_ok ,
x_return_status => x_return_status ,
x_errorcode => x_err_code ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
if((x_err_code <> 0) OR (l_delete_ok <> FND_API.G_TRUE)) then
x_err_code := 10;
x_err_stack := x_err_stack || '->check PA_EGO_WRAPPER_PUB.check_delete_project_ok '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_EGO_WRAPPER_PUB.check_delete_project_ok_eng(
p_api_version => 1.0 ,
p_project_id => x_project_id ,
p_init_msg_list => NULL ,
x_delete_ok => l_delete_ok ,
x_return_status => x_return_status ,
x_errorcode => x_err_code ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
if((x_err_code <> 0) OR (l_delete_ok <> FND_API.G_TRUE)) then
x_err_code := 20;
x_err_stack := x_err_stack || '->check PA_EGO_WRAPPER_PUB.check_delete_project_ok_eng '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
pa_project_utils.check_delete_project_ok(
x_project_id => x_project_id,
x_validation_mode => x_validation_mode, --bug 2947492
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack);
rollback to delete_project;
delete from pa_project_options
where project_id = x_project_id;
delete from pa_project_copy_overrides
where project_id = x_project_id;
PA_ASSIGNMENTS_PUB.DELETE_PJR_TXNS
(p_project_id => x_project_id
,p_calling_module => FND_API.G_MISS_CHAR
,p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validate_only => FND_API.G_FALSE
,p_max_msg_count => FND_API.G_MISS_NUM
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->Delete_PJR_Txns: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
FOR i IN (SELECT rowid row_id
,project_subteam_id
FROM pa_project_subteams
WHERE object_type = 'PA_PROJECTS'
AND object_id = x_project_id)
LOOP
PA_PROJECT_SUBTEAMS_PVT.Delete_Subteam
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validate_only => FND_API.G_FALSE
,p_validation_level => FND_API.g_valid_level_full
-- ,p_calling_module => NULL
,p_debug_mode => 'N'
,p_max_msg_count => FND_API.G_MISS_NUM
,p_subteam_row_id => i.row_id
,p_subteam_id => i.project_subteam_id
,p_record_version_number => FND_API.G_MISS_NUM
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->Delete_Subteam: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
PA_PROJECT_PARTIES_PUB.DELETE_PROJECT_PARTY(
p_api_version => 1.0 -- p_api_version
, p_init_msg_list => FND_API.G_FALSE -- p_init_msg_list
, p_commit => FND_API.G_FALSE -- p_commit --before it was passed TRUE.
, p_validate_only => FND_API.G_FALSE -- p_validate_only
, p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
, p_debug_mode => 'N' -- p_debug_mode
, p_record_version_number => v_null_number -- p_record_version_number
, p_calling_module => 'FORM' -- p_calling_module
, p_project_id => x_project_id -- p_project_id
, p_project_party_id => v_null_number -- p_project_party_id
, p_scheduled_flag => 'N' -- p_scheduled_flag
, x_return_status => x_return_status -- x_return_status
, x_msg_count => x_msg_count -- x_msg_count
, x_msg_data => x_msg_data -- x_msg_data
);
x_err_stack := x_err_stack || '->delete_project_party: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
delete from pa_project_classes
where project_id = x_project_id;
delete from pa_project_customers
where project_id = x_project_id;
delete from pa_project_contacts
where project_id = x_project_id;
delete from pa_cost_dist_overrides
where project_id = x_project_id;
delete from pa_credit_receivers
where project_id = x_project_id;
delete from pa_transaction_controls
where project_id = x_project_id;
delete from pa_billing_assignments
where project_id = x_project_id;
DELETE FROM PA_LABOR_MULTIPLIERS
WHERE PROJECT_ID = x_project_id;
DELETE FROM PA_LABOR_MULTIPLIERS
WHERE task_id = l_tasks_tbl(i);
DELETE FROM PA_JOB_BILL_RATE_OVERRIDES
WHERE PROJECT_ID = x_project_id ;
DELETE FROM PA_JOB_BILL_RATE_OVERRIDES
WHERE task_id = l_tasks_tbl(i);
DELETE FROM pa_job_bill_title_overrides
WHERE PROJECT_ID = x_project_id ;
DELETE FROM pa_job_bill_title_overrides
WHERE task_id = l_tasks_tbl(i);
DELETE FROM pa_job_assignment_overrides
WHERE PROJECT_ID = x_project_id ;
DELETE FROM pa_job_assignment_overrides
WHERE task_id = l_tasks_tbl(i);
DELETE FROM pa_emp_bill_rate_overrides
WHERE PROJECT_ID = x_project_id ;
DELETE FROM pa_emp_bill_rate_overrides
WHERE task_id = l_tasks_tbl(i);
DELETE FROM PA_NL_BILL_RATE_OVERRIDES
WHERE PROJECT_ID = x_project_id ;
DELETE FROM PA_NL_BILL_RATE_OVERRIDES
WHERE task_id = l_tasks_tbl(i);
delete from pa_compiled_multipliers
where ind_compiled_set_id in
(select ics.ind_compiled_set_id
from pa_ind_compiled_sets ics,
pa_ind_rate_sch_revisions rev,
pa_ind_rate_schedules sch
where ics.ind_rate_sch_revision_id =
rev.ind_rate_sch_revision_id
and rev.ind_rate_sch_id = sch.ind_rate_sch_id
and sch.project_id = x_project_id);
delete from pa_ind_compiled_sets
where ind_rate_sch_revision_id in
(select rev.ind_rate_sch_revision_id
from pa_ind_rate_sch_revisions rev,
pa_ind_rate_schedules sch
where rev.ind_rate_sch_id = sch.ind_rate_sch_id
and sch.project_id = x_project_id);
delete from pa_ind_cost_multipliers
where ind_rate_sch_revision_id in
(select rev.ind_rate_sch_revision_id
from pa_ind_rate_sch_revisions rev, pa_ind_rate_schedules sch
where rev.ind_rate_sch_id = sch.ind_rate_sch_id
and sch.project_id = x_project_id);
delete from pa_ind_rate_sch_revisions
where ind_rate_sch_id in
(select ind_rate_sch_id
from pa_ind_rate_schedules
where project_id = x_project_id );
delete from pa_ind_rate_schedules
where project_id = x_project_id;
delete from pa_project_asset_assignments
where project_id = x_project_id;
delete from pa_project_assets
where project_id = x_project_id;
delete from pa_resource_list_uses
where resource_list_assignment_id in
(select resource_list_assignment_id
from pa_resource_list_assignments
where project_id = x_project_id );
delete from pa_resource_list_assignments
where project_id = x_project_id ;
PA_PERF_EXCP_UTILS.delete_object_exceptions
( p_object_type => 'PA_PROJECTS'
,p_object_id => x_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->delete_object_exception: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
x_err_stage := 'delete_object_exception: '||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
rollback to delete_project;
PA_OPPORTUNITY_MGT_PVT.delete_project_attributes
( p_project_id => x_project_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->delete_project_attributes: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_RETENTION_UTIL.delete_retention_rules(
p_project_id => x_project_id,
p_task_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->delete_retention_rules: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
delete from pa_percent_completes
where project_id = x_project_id;
for task_rec in (select t.task_id
from pa_tasks t
where t.project_id = x_project_id
and t.task_id = t.top_task_id) loop
x_err_stack := NULL;
pa_project_core.delete_task(
x_task_id => task_rec.task_id,
x_validation_mode => x_validation_mode, --bug 2947492
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack);
rollback to delete_project;
PA_PROJ_TASK_STRUC_PUB.delete_project_structure(
p_calling_module => 'FORMS'
,p_project_id => x_project_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status );
x_err_stack := x_err_stack || '->delete_project_structure: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_CONTROL_ITEMS_PVT.DELETE_ALL_CONTROL_ITEMS(
p_project_id => x_project_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status );
x_err_stack := x_err_stack || '->delete_all_control_items: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_PROJ_STRUCTURE_PUB.DELETE_RELATIONSHIP(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_validate_only => FND_API.G_FALSE,
p_debug_mode => 'N',
p_task_id => null,
p_project_id => x_project_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_err_stack := x_err_stack || '->delete_relationship: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_EGO_WRAPPER_PUB.delete_all_item_assocs(
p_api_version => 1.0 ,
p_project_id => x_project_id ,
p_init_msg_list => NULL ,
p_commit => FND_API.G_TRUE ,
x_errorcode => x_err_code ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->delete_all_item_assocs: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_PROJECT_CTX_SEARCH_PVT.DELETE_ROW (
p_project_id => x_project_id
,p_template_flag => l_template_flag
,x_return_status => x_return_status );
x_err_stack := x_err_stack || '->pa_project_ctx_search_pvt.delete_row: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_USER_ATTR_PUB.DELETE_ALL_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => x_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->delete_all_user_attrs_data: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_PROJECT_SETS_PVT.delete_proj_from_proj_set(
p_project_id => x_project_id
,x_return_status => x_return_status);
x_err_stack := x_err_stack || '->delete_proj_from_proj_set: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_TASK_PUB1.Delete_Proj_To_Task_Assoc(
p_project_id => x_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->Delete_Proj_To_Task_Assoc: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
rollback to delete_project;
PA_DELIVERABLE_PUB.delete_deliverable_structure
(p_project_id => x_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data
);
Pa_Rbs_Utils.Delete_Proj_Specific_RBS( p_project_id => x_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
);
x_err_stack := x_err_stack || '->Delete_Proj_Specific_RBS: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
Pa_Planning_Resource_Utils.Delete_Proj_Specific_Resource( p_project_id => x_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
);
x_err_stack := x_err_stack || '->Delete_Proj_Specific_Resource: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
PA_PERF_EXCP_UTILS.delete_object_exceptions
(
p_object_type =>'PA_PROJECTS'
,p_object_id =>x_project_id
,x_msg_count =>x_msg_count
,x_msg_data =>x_msg_data
,x_return_status =>x_return_status
);
x_err_stack := x_err_stack||'->PA_PERF_EXCP_UTILS.delete_object_exceptions';
x_err_stage := 'PA_PERF_EXCP_UTILS.delete_object_exceptions: '||SUBSTRB(SQLERRM,1,240);
FOR i IN ( SELECT item_type,item_key
FROM pa_wf_processes
WHERE entity_key1 = TO_CHAR(x_project_id))
LOOP
wf_engine.itemstatus ( itemtype => i.item_type,
itemkey => i.item_key,
status => x_status,
result => x_result );
DELETE FROM pa_wf_processes
WHERE entity_key1 = TO_CHAR(x_project_id);
PA_FIN_PLAN_PUB.Delete_Version( p_project_id => x_project_id
,p_budget_version_id => null
,p_record_version_number => null
,p_context => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_err_stack := x_err_stack || '->Delete_Version: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
pa_fin_plan_utils.Delete_Fp_Options( p_project_id => x_project_id,
x_err_code => x_err_code );
x_err_stack := x_err_stack ||'->Delete_Fp_Options: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
PA_CONTROL_ITEMS_UTILS.DELETE_OBJ_STATUS_CHANGES
(
p_object_type =>'PA_PROJECTS'
,p_object_id => x_project_id
,x_msg_count =>x_msg_count
,x_msg_data =>x_msg_data
,x_return_status =>x_return_status);
x_err_stack := x_err_stack || '->Delete_Obj_Status_Changes: '|| x_project_id;
x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_project;
delete pa_projects
where project_id = x_project_id;
x_err_stage := 'DELETE PROJECT: '||SUBSTR( SQLERRM,1,1900);
rollback to delete_project;
end delete_project;
SELECT
P.start_date,
P.completion_date,
P.carrying_out_organization_id,
P.labor_bill_rate_org_id,
P.labor_std_bill_rate_schdl,
P.labor_schedule_discount,
P.labor_schedule_fixed_date,
P.non_labor_bill_rate_org_id,
P.non_labor_std_bill_rate_schdl,
P.non_labor_schedule_discount,
P.non_labor_schedule_fixed_date,
P.labor_sch_type,
P.non_labor_sch_type,
P.cost_ind_rate_sch_id,
P.cost_ind_sch_fixed_date,
P.rev_ind_rate_sch_id,
P.rev_ind_sch_fixed_date,
P.inv_ind_rate_sch_id,
P.inv_ind_sch_fixed_date,
PT.service_type_code,
PT.project_type_class_code
FROM pa_projects P, pa_project_types PT
WHERE P.project_id = x_project_id
AND P.project_type = PT.project_type;
SELECT
DISTINCT A.address_id
INTO x_address_id
FROM ra_addresses A, ra_site_uses SU
WHERE A.address_id = SU.address_id
AND A.customer_id IN
(SELECT customer_id
FROM pa_project_customers
WHERE project_id = x_project_id)
AND NVL(SU.STATUS,'A') = 'A'
AND SU.site_use_code = 'SHIP_TO';
SELECT
DISTINCT acct_site.cust_acct_site_id
INTO x_address_id
FROM
hz_cust_acct_sites_all acct_site,
hz_cust_site_uses su
WHERE
acct_site.cust_acct_site_id = su.cust_acct_site_id
AND acct_site.cust_account_id IN
(SELECT customer_id FROM pa_project_customers WHERE project_id = x_project_id)
AND NVL(SU.STATUS,'A') = 'A'
AND SU.site_use_code = 'SHIP_TO';
x_err_stage := 'update parent task chargeable flag';
update pa_tasks
set chargeable_flag = 'N'
where task_id = x_parent_task_id;
x_err_stage := 'Insert task for project '|| x_project_id;
pa_tasks_pkg.insert_row(
x_rowid,
x_task_id,
x_project_id,
x_task_number,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
x_task_name,
x_task_name, -- long name
x_top_task_id,
x_wbs_level,
'N',
'N',
x_parent_task_id,
x_description,
nvl(x_organization_id, x_org_id),
nvl(x_service_type_code, x_serv_type_code),
x_manager_id,
'Y',
x_billable_flag,
'N',
nvl(x_task_start_date, x_proj_start_date),
nvl(x_task_end_date, x_proj_end_date),
x_address_id,
X_Labor_Bill_Rate_Org_Id,
X_Labor_Std_Bill_Rate_Schd,
X_Labor_Schedule_Fixed_Date,
X_Labor_Schedule_Discount,
X_NL_Bill_Rate_Org_Id,
X_NL_Std_Bill_Rate_Schd,
X_NL_Schedule_Fixed_Date,
X_NL_Schedule_Discount,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
X_Cost_Ind_Rate_Sch_Id,
X_Rev_Ind_Rate_Sch_Id,
X_Inv_Ind_Rate_Sch_Id,
X_Cost_Ind_Sch_Fixed_Date,
X_Rev_Ind_Sch_Fixed_Date,
X_Inv_Ind_Sch_Fixed_Date,
X_Labor_Sch_Type,
X_Non_Labor_Sch_Type,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
-- 01-APR-2001
-- hsiu Added for forecasting changes
NULL,
NULL,
--MCA Sakthi for MultiAgreementCurreny Project
NULL,
NULL,
NULL,
--MCA Sakthi for MultiAgreementCurreny Project
NULL,
NULL,
--PA L Changes 2872708
'N',
'Y',
null,
--End PA L Changes 2872708
/*FPM Dev -Project setup changes */
null,
null,
null
);
x_err_stage := 'PA_NO_ROW_INSERTED';
procedure delete_task ( x_task_id IN number
, x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
, x_validate_flag IN varchar2 DEFAULT 'Y' -- Adding paramater x_validate_flag
, x_bulk_flag IN VARCHAR2 DEFAULT 'N' -- 4201927
, x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
, x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
is
old_stack varchar2(630);
select project_id from
pa_tasks where
task_id = x_task_id;
SELECT template_flag
FROM pa_projects_all
WHERE project_id = c_project_id;
is select cc_tax_task_id from pa_projects_all pj
where pj.project_id = l_project_id;
SAVEPOINT delete_task;
x_err_stack := x_err_stack || '->delete_task';
pa_task_utils.check_delete_task_ok(
x_task_id => x_task_id,
x_validation_mode => x_validation_mode, -- bug 2947492
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack);
rollback to delete_task;
update pa_projects_all
set cc_tax_task_id = null
where project_id = l_project_id;
delete from pa_billing_assignments
where top_task_id = x_task_id
AND project_id = l_project_id;
* Anonymous Block to delete burdening setup starts.
*/
declare
cursor task_cur ( l_start_task_id pa_tasks.task_id%TYPE )
is
select task_id
from pa_tasks
connect by prior task_id = parent_task_id
start with task_id = l_start_task_id
;
select sch.ind_rate_sch_id
from pa_ind_rate_schedules sch
where sch.task_id = l_task_id
;
select rev.ind_rate_sch_revision_id
from pa_ind_rate_sch_revisions rev
where rev.ind_rate_sch_id = l_ind_rate_sch_id
;
select ics.ind_compiled_set_id
from pa_ind_compiled_sets ics
where ics.ind_rate_sch_revision_id = l_ind_rate_sch_revision_id
;
x_err_stage := 'Delete txn controls for task '|| x_task_id;
delete from pa_transaction_controls
where task_id =l_task_id_tab(i)
and project_id = l_project_id;
x_err_stage := 'Delete labor multipliers for task '|| x_task_id;
delete from pa_labor_multipliers
where task_id =l_task_id_tab(i) ;
x_err_stage := 'Delete job bill rate overrides for task '|| x_task_id;
delete from pa_job_bill_rate_overrides
where task_id =l_task_id_tab(i) ;
x_err_stage := 'Delete job assignment overrides for task '|| x_task_id;
delete from pa_job_assignment_overrides
where task_id =l_task_id_tab(i) ;
x_err_stage := 'Delete emp bill rate overrides for task '|| x_task_id;
delete from pa_emp_bill_rate_overrides
where task_id =l_task_id_tab(i) ;
x_err_stage := 'Delete nl bill rate overrides for task '|| x_task_id;
delete from pa_nl_bill_rate_overrides
where task_id =l_task_id_tab(i) ;
x_err_stage := 'Delete project asset assignments for task '|| x_task_id;
delete from pa_project_asset_assignments
where task_id =l_task_id_tab(i) ;
delete
from pa_compiled_multipliers comp_mult
where ind_compiled_set_id = l_ind_comp_set_id_tab(i)
;
delete
from pa_ind_compiled_sets ics
where ind_compiled_set_id = l_ind_comp_set_id_tab(i)
;
delete
from pa_ind_cost_multipliers icm
where icm.ind_rate_sch_revision_id = l_ind_rate_sch_rev_id_tab(i)
;
delete
from pa_ind_rate_sch_revisions rev
where rev.ind_rate_sch_revision_id = l_ind_rate_sch_rev_id_tab(i)
;
delete
from pa_ind_rate_schedules sch
where sch.ind_rate_sch_id = l_ind_rate_sch_id_tab(i)
;
rollback to delete_task;
end; -- end of anonymous block to delete burdening setup.
/* New code to delete burdening setup ends **/
-- Delete project asset assigments
-- 3693197 : Commented and moved above in the anonymous block
--x_err_stage := 'Delete proj asset assignmt for task '|| x_task_id;
PA_RETENTION_UTIL.delete_retention_rules(
p_project_id => l_project_id,
p_task_id => x_TASK_ID,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
x_err_stack := x_err_stack || '->delete_retention_rules: '|| l_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_task ;
x_err_stage := 'Delete task percent complete ';
delete from pa_percent_completes
where project_id = l_project_id
and task_id in
(select task_id
from pa_tasks
CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
START WITH TASK_ID = x_TASK_ID);
x_err_stage := 'Delete project structure relationship for task ';
PA_PROJ_STRUCTURE_PUB.DELETE_RELATIONSHIP(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_validate_only => FND_API.G_FALSE,
p_debug_mode => 'N',
p_task_id => x_TASK_ID,
p_project_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_err_stack := x_err_stack || '->DELETE_RELATIONSHIP: '|| l_project_id;
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
ROLLBACK TO delete_task ;
x_err_stage := 'Delete any task in the subtree of task '|| x_task_id;
delete from pa_tasks
where task_id in
(select task_id
from pa_tasks
CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
START WITH TASK_ID = x_TASK_ID);
x_err_stage := 'update parent task chargeable flag';
update pa_tasks
set chargeable_flag = 'Y'
where task_id = x_parent_task_id;
rollback to delete_task;
end delete_task;
procedure delete_project_type (
x_project_type_id IN number
, x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
, x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
)
is
l_return_status varchar2(1);
SAVEPOINT delete_project_type;
/* PA_PROJECT_UTILS.check_delete_project_type_ok(
p_project_type_id => x_project_type_id
,x_return_status => x_return_status
,x_error_message_code => x_msg_data
);
delete pa_project_types_all --bug 4584792
where project_type_id = x_project_type_id;
rollback to delete_project;
end delete_project_type;
procedure delete_class_category (
x_class_category IN VARCHAR2
, x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
, x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
)
is
l_return_status varchar2(1);
SAVEPOINT delete_class_category;
delete pa_class_categories
where class_category = x_class_category
;
rollback to delete_class_category;
end delete_class_category;
procedure delete_class_code (
x_class_category IN VARCHAR2
, x_class_code IN VARCHAR2
, x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
, x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
)
is
l_return_status varchar2(1);
SAVEPOINT delete_class_code;
delete pa_class_codes
where class_category = x_class_category
and class_code = x_class_code
;
rollback to delete_class_code;
end delete_class_code;