[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(display_sequence)
into l_max_seq
from PA_TASKS
where project_id = p_project_id;
update PA_TASKS
set
display_sequence =
PA_TASKS_MAINT_UTILS.REARRANGE_DISPLAY_SEQ(display_sequence, l_max_seq, 1, 'INSERT', 'DOWN'),
record_version_number = record_version_number + 1
where project_id = p_project_id
and (display_sequence > -(l_display_seq+1) or display_sequence < 0);
procedure UPDATE_TASK
(
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_project_id IN NUMBER
,p_task_id IN NUMBER
,p_task_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_task_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_long_task_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_task_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_task_manager_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_task_manager_person_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_carrying_out_org_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_carrying_out_organization_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_task_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_priority_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_work_type_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_service_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_milestone_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_critical_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_chargeable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_billable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_receive_project_invoice_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_scheduled_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_estimated_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_estimated_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_actual_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_actual_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_task_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_task_completion_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_baseline_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_baseline_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_obligation_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_obligation_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_estimate_to_complete_work IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_baseline_work IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_scheduled_work IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_actual_work_to_date IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_work_unit IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_progress_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_job_bill_rate_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_emp_bill_rate_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_parent_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_top_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_wbs_level IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_parent_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_address_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_ready_to_bill_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_ready_to_distribute_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_limit_to_txn_controls_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_labor_bill_rate_org_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_labor_std_bill_rate_schdl IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_labor_schedule_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_labor_schedule_discount IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_nl_bill_rate_org_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_nl_std_bill_rate_schdl IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_nl_schedule_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_nl_schedule_discount IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_labor_cost_multiplier_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_cost_ind_rate_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_rev_ind_rate_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_inv_ind_rate_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_cost_ind_sch_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_rev_ind_sch_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_inv_ind_sch_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_labor_sch_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_nl_sch_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_early_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_early_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_late_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_late_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,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_allow_cross_charge_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_cc_process_labor_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_labor_tp_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_labor_tp_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_cc_process_nl_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_nl_tp_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_nl_tp_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_inc_proj_progress_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_taskfunc_cost_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_taskfunc_cost_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_non_lab_std_bill_rt_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_record_version_number IN NUMBER
,p_wbs_record_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_comments IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- FP.K changes msundare
,p_labor_disc_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_non_labor_disc_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--PA L Capital Project Changes 2872708
,p_retirement_cost_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_cint_eligible_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_cint_stop_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
--End PA L Capital Project Changes 2872708
,p_gen_etc_src_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_update_subtasks_end_dt IN VARCHAR2 := 'Y' --bug 4241863
,p_dates_check IN VARCHAR2 := 'Y' --bug 5665772
,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 VARCHAR2(30) := 'UPDATE_TASK';
select 'x'
from PA_TASKS
where project_id = p_project_id
for update of record_version_number NOWAIT;
select 'x'
from PA_TASKS
where project_id = p_project_id;
IS select task_id, start_date, completion_date, parent_task_id from pa_tasks
where project_id = l_project_id
and completion_date is null
start with parent_task_id = l_task_id
connect by parent_task_id = prior task_id
and project_id = l_project_id;
SELECT task_id
FROM pa_tasks
WHERE project_id = l_project_id
START WITH task_id = l_task_id
CONNECT BY PRIOR parent_task_id = task_id
AND project_id = l_project_id;
pa_debug.init_err_stack('PA_TASKS_MAINT_PUB.UPDATE_TASK');
pa_debug.debug('PA_TASKS_MAINT_PUB.UPDATE_TASK begin');
savepoint UPDATE_TASK;
PA_TASKS_MAINT_PVT.UPDATE_TASK
(
p_commit => p_commit
,p_calling_module => p_calling_module
,p_validate_only => p_validate_only
,p_debug_mode => p_debug_mode
,p_project_id => p_project_id
,p_task_id => p_task_id
,p_task_number => p_task_number
,p_task_name => p_task_name
,p_long_task_name => p_long_task_name
,p_task_description => p_task_description
,p_task_manager_person_id => l_task_manager_id
,p_carrying_out_organization_id => l_carrying_out_org_id
,p_task_type_code => p_task_type_code
,p_priority_code => p_priority_code
,p_work_type_id => p_work_type_id
,p_service_type_code => p_service_type_code
,p_milestone_flag => p_milestone_flag
,p_critical_flag => p_critical_flag
,p_chargeable_flag => p_chargeable_flag
,p_billable_flag => p_billable_flag
,p_receive_project_invoice_flag => p_receive_project_invoice_flag
,p_scheduled_start_date => p_scheduled_start_date
,p_scheduled_finish_date => p_scheduled_finish_date
,p_estimated_start_date => p_estimated_start_date
,p_estimated_end_date => p_estimated_end_date
,p_actual_start_date => p_actual_start_date
,p_actual_finish_date => p_actual_finish_date
,p_task_start_date => p_task_start_date
,p_task_completion_date => p_task_completion_date
,p_baseline_start_date => p_baseline_start_date
,p_baseline_end_date => p_baseline_end_date
,p_obligation_start_date => p_obligation_start_date
,p_obligation_end_date => p_obligation_end_date
,p_estimate_to_complete_work => p_estimate_to_complete_work
,p_baseline_work => p_baseline_work
,p_scheduled_work => p_scheduled_work
,p_actual_work_to_date => p_actual_work_to_date
,p_work_unit => p_work_unit
,p_progress_status_code => p_progress_status_code
,p_job_bill_rate_schedule_id =>p_job_bill_rate_schedule_id
,p_emp_bill_rate_schedule_id =>p_emp_bill_rate_schedule_id
,p_pm_product_code =>p_pm_product_code
,p_pm_project_reference =>p_pm_project_reference
,p_pm_task_reference =>p_pm_task_reference
,p_pm_parent_task_reference =>p_pm_parent_task_reference
,p_parent_task_id =>p_parent_task_id
,p_address_id =>p_address_id
,p_ready_to_bill_flag =>p_ready_to_bill_flag
,p_ready_to_distribute_flag =>p_ready_to_distribute_flag
,p_limit_to_txn_controls_flag =>p_limit_to_txn_controls_flag
,p_labor_bill_rate_org_id =>p_labor_bill_rate_org_id
,p_labor_std_bill_rate_schdl =>p_labor_std_bill_rate_schdl
,p_labor_schedule_fixed_date =>p_labor_schedule_fixed_date
,p_labor_schedule_discount =>p_labor_schedule_discount
,p_nl_bill_rate_org_id =>p_nl_bill_rate_org_id
,p_nl_std_bill_rate_schdl =>p_nl_std_bill_rate_schdl
,p_nl_schedule_fixed_date =>p_nl_schedule_fixed_date
,p_nl_schedule_discount =>p_nl_schedule_discount
,p_labor_cost_multiplier_name =>p_labor_cost_multiplier_name
,p_cost_ind_rate_sch_id =>p_cost_ind_rate_sch_id
,p_rev_ind_rate_sch_id =>p_rev_ind_rate_sch_id
,p_inv_ind_rate_sch_id =>p_inv_ind_rate_sch_id
,p_cost_ind_sch_fixed_date =>p_cost_ind_sch_fixed_date
,p_rev_ind_sch_fixed_date =>p_rev_ind_sch_fixed_date
,p_inv_ind_sch_fixed_date =>p_inv_ind_sch_fixed_date
,p_labor_sch_type =>p_labor_sch_type
,p_nl_sch_type =>p_nl_sch_type
,p_early_start_date =>p_early_start_date
,p_early_finish_date =>p_early_finish_date
,p_late_start_date =>p_late_start_date
,p_late_finish_date =>p_late_finish_date
,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_allow_cross_charge_flag =>p_allow_cross_charge_flag
,p_project_rate_date =>p_project_rate_date
,p_project_rate_type =>p_project_rate_type
,p_cc_process_labor_flag =>p_cc_process_labor_flag
,p_labor_tp_schedule_id =>p_labor_tp_schedule_id
,p_labor_tp_fixed_date =>p_labor_tp_fixed_date
,p_cc_process_nl_flag =>p_cc_process_nl_flag
,p_nl_tp_schedule_id =>p_nl_tp_schedule_id
,p_nl_tp_fixed_date =>p_nl_tp_fixed_date
,p_inc_proj_progress_flag => p_inc_proj_progress_flag
,p_taskfunc_cost_rate_type => p_taskfunc_cost_rate_type
,p_taskfunc_cost_rate_date => p_taskfunc_cost_rate_date
,p_non_lab_std_bill_rt_sch_id => p_non_lab_std_bill_rt_sch_id
,p_record_version_number => p_record_version_number
,p_comments => p_comments
,p_labor_disc_reason_code => p_labor_disc_reason_code
,p_non_labor_disc_reason_code => p_non_labor_disc_reason_code
--PA L Capital Project Changes 2872708
,p_retirement_cost_flag => p_retirement_cost_flag
,p_cint_eligible_flag => p_cint_eligible_flag
,p_cint_stop_date => p_cint_stop_date
--End PA L Capital Project Changes 2872708
,p_gen_etc_src_code => p_gen_etc_src_code
,p_dates_check => p_dates_check --bug 5665772
,x_return_status =>l_return_status
,x_msg_count =>x_msg_count
,x_msg_data =>x_msg_data
);
IF p_update_subtasks_end_dt = 'Y' --bug 4241863
THEN
--BUG 4081329, rtarway
--Update Child tasks with the end date passed
if ( p_task_completion_date is not null and p_task_completion_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ) then
open cur_get_child_task_dates (p_project_id,p_task_id);
SELECT start_date
INTO l_parent_task_date
FROM pa_tasks
WHERE task_id = l_parent_task_id_tbl(i);
UPDATE pa_tasks
SET completion_date = p_task_completion_date,
start_date = l_parent_task_date
WHERE task_id = l_task_id_tbl(i);
UPDATE pa_tasks
SET completion_date = p_task_completion_date
WHERE task_id = l_task_id_tbl(i);
--update pa_tasks set completion_date = p_task_completion_date where task_id = l_task_id_tbl(i)
--and project_id = p_project_id;
UPDATE pa_tasks
SET completion_date = p_task_completion_date
WHERE task_id = l_task_id_tbl(i) and project_id = p_project_id;
pa_debug.debug('PA_TASKS_MAINT_PUB.UPDATE_TASK END');
ROLLBACK to UPDATE_TASK;
ROLLBACK to UPDATE_TASK;
p_procedure_name => 'UPDATE_TASK',
p_error_text => substrb(SQLERRM,1,240));
END UPDATE_TASK;
procedure DELETE_TASK
(
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_project_id IN NUMBER
,p_task_id IN NUMBER
,p_record_version_number IN NUMBER
,p_wbs_record_version_number IN NUMBER
,p_called_from_api IN VARCHAR2 := 'ABCD'
,p_bulk_flag IN VARCHAR2 := 'N' -- 4201927
,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 VARCHAR2(30) := 'DELETE_TASK';
select 'x'
from PA_TASKS
where project_id = p_project_id
for update of record_version_number NOWAIT;
select 'x'
from PA_TASKS
where project_id = p_project_id;
/* CURSOR cur_selected_task
IS
SELECT display_sequence
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;*/
pa_debug.init_err_stack('PA_TASKS_MAINT_PUB.DELETE_TASK');
pa_debug.debug('PA_TASKS_MAINT_PUB.DELETE_TASK begin');
savepoint DELETE_TASK;
select count('x')
INTO l_task_cnt
FROM PA_TASKS
WHERE project_id = p_project_id
START WITH task_id = p_task_id
CONNECT BY parent_task_id = prior task_id;
/* --Get the sequence number of the selected task;
OPEN cur_selected_task;
FETCH cur_selected_task INTO selected_seq_num;
CLOSE cur_selected_task;*/
PA_TASKS_MAINT_PVT.DELETE_TASK
(
p_commit => p_commit
,p_calling_module => p_calling_module
,p_validate_only => p_validate_only
,p_debug_mode => p_debug_mode
,p_task_id => p_task_id
,p_record_version_number => p_record_version_number
,p_called_from_api => p_called_from_api
,p_bulk_flag => p_bulk_flag -- 4201927 Passing the value to pvt api
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/* --This fcuntion is moved in PA_TASK_PVT1.DELETE_TASK_VERSION api.
BEGIN
select max(display_sequence)
into l_max_seq
from PA_TASKS
where project_id = p_project_id;
update PA_TASKS
set
display_sequence =
PA_TASKS_MAINT_UTILS.REARRANGE_DISPLAY_SEQ(display_sequence, l_max_seq, l_task_cnt, 'DELETE', 'DOWN'),
record_version_number = record_version_number + 1
where project_id = p_project_id
and (display_sequence > selected_seq_num);
pa_debug.debug('PA_TASKS_MAINT_PUB.DELETE_TASK END');
ROLLBACK to DELETE_TASK;
ROLLBACK to DELETE_TASK;
p_procedure_name => 'DELETE_TASK',
p_error_text => substrb(SQLERRM,1,240));
END DELETE_TASK;
ELSIF p_copy_node_flag = 'T' ----copy selected task and its sub tasks
THEN
PA_TASKS_MAINT_PVT.Copy_Entire_Task(
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_reference_project_id => l_ref_project_id
,p_reference_task_id => l_ref_task_id
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_peer_or_sub => p_peer_or_sub
,p_task_prefix => p_task_prefix
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
ELSIF p_copy_node_flag = 'S' ----copy selected node
THEN
PA_TASKS_MAINT_PVT.Copy_Selected_Task(
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_reference_project_id => l_ref_project_id
,p_reference_task_id => l_ref_task_id
,p_task_id => l_task_id
,p_peer_or_sub => p_peer_or_sub
,p_task_prefix => p_task_prefix
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
SELECT
element_version_id
, financial_task_flag
FROM
pa_proj_element_versions
WHERE
project_id = l_project_id
AND
element_version_id
IN
(--Get the Parents of the passed task
SELECT
object_id_from1 object_id
FROM
pa_object_relationships
WHERE
relationship_type = 'S'
AND
relationship_subtype = 'TASK_TO_TASK'
START WITH object_id_to1 = l_task_version_id
CONNECT BY Object_id_to1 = PRIOR object_id_from1
)
order by display_sequence desc -- Bug 3735089
;*/
SELECT
element_version_id
, financial_task_flag
FROM
pa_proj_element_versions elem,
(--Get the Parents of the passed task
SELECT
object_id_from1 object_id
FROM
pa_object_relationships
WHERE
relationship_type = 'S'
AND
relationship_subtype = 'TASK_TO_TASK'
START WITH object_id_to1 = l_task_version_id
CONNECT BY Object_id_to1 = PRIOR object_id_from1
AND RELATIONSHIP_TYPE = 'S'
) parents
WHERE
elem.project_id = l_project_id
AND elem.object_type = 'PA_TASKS'
AND elem.element_version_id = parents.object_id
order by elem.display_sequence desc;
SELECT
element_version_id
, financial_task_flag
FROM
pa_proj_element_versions
WHERE
project_id = l_project_id
AND
element_version_id
IN
(-- Get the Childs of the mapped task
SELECT
object_id_to1 object_id
FROM
pa_object_relationships
WHERE
relationship_type ='S'
AND
relationship_subtype ='TASK_TO_TASK'
START WITH object_id_from1 = l_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
)
order by display_sequence -- Bug 3735089
;*/
SELECT
element_version_id
, financial_task_flag
FROM
pa_proj_element_versions elem,
(
SELECT
object_id_to1 object_id
FROM
pa_object_relationships
WHERE
relationship_type ='S'
AND relationship_subtype ='TASK_TO_TASK'
START WITH object_id_from1 = l_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type ='S'
) childs
WHERE
elem.project_id = l_project_id
and elem.object_type = 'PA_TASKS'
and elem.element_version_id = childs.object_id
order by elem.display_sequence;
SELECT
OBJECT_ID_FROM1
FROM
pa_object_relationships
WHERE
relationship_type ='S'
AND
relationship_subtype ='TASK_TO_TASK'
AND
OBJECT_ID_TO1 = l_object_id_to1;
SELECT
allTasks.record_version_number
FROM
PA_TASKS allTasks,
pa_proj_element_versions elever
WHERE
elever.element_version_id = l_task_version_id
AND
elever.project_id = l_project_id
AND elever.proj_element_id = allTasks.task_id
AND allTasks.project_id = elever.project_id;
l_task_version_id_tbl.DELETE;
SELECT
elemver.project_id AS PROJECT_ID
, elemver.element_version_id AS ELEMENT_VERSION_ID
, elemver.parent_structure_version_id AS PARENT_STRUCTRE_VERSION_ID
, null AS PARENT_ELEMENT_VERSION_ID
, PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(elemver.element_version_id) AS CHILD_ELEMENT_FLAG
, elem.name AS TASK_NAME
--, elem.element_number AS TASK_NUMBER -- Commented for Bug 5438975
, verstruct.version_number as TASK_NUMBER -- Added for Bug 5438975
, elem.object_type AS OBJECT_TYPE
, elemver.display_sequence AS DISPLAY_SEQUENCE
, elemver.wbs_number AS WBS_NUMBER
, elem.proj_element_id AS PROJ_ELEMENT_ID
, elemver.financial_task_flag AS FINANCIAL_TASK_FLAG
FROM
pa_proj_elements elem
, pa_proj_element_versions elemver
, pa_proj_elem_ver_structure verstruct -- Added for Bug 5438975
WHERE elem.proj_element_id = elemver.proj_element_id
AND elem.project_id = elemver.project_id
AND elemver.element_version_id = l_element_version_id
AND elemver.project_id = l_project_id
-- Added for Bug 5438975
AND verstruct.project_id = elemver.project_id
AND verstruct.ELEMENT_VERSION_ID = elemver.element_version_id
AND verstruct.PROJ_ELEMENT_ID = elemver.proj_element_id;
SELECT
elemver.project_id AS PROJECT_ID
, elemver.element_version_id AS ELEMENT_VERSION_ID
, elemver.parent_structure_version_id AS PARENT_STRUCTRE_VERSION_ID
, por.object_id_from1 AS PARENT_ELEMENT_VERSION_ID
, PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(elemver.element_version_id) AS CHILD_ELEMENT_FLAG
, projelem.name AS TASK_NAME
, projelem.element_number AS TASK_NUMBER
, elemver.object_type AS OBJECT_TYPE
, elemver.display_sequence AS DISPLAY_SEQUENCE
, elemver.wbs_number AS WBS_NUMBER
, elemver.proj_element_id AS PROJ_ELEMENT_ID
, elemver.financial_task_flag AS FINANCIAL_TASK_FLAG
FROM
pa_proj_element_versions elemver
, pa_proj_elements projelem
, pa_object_relationships por
WHERE
projelem.project_id = elemver.project_id
AND projelem.proj_element_id = elemver.proj_element_id
AND elemver.object_type='PA_TASKS'
AND projelem.object_type='PA_TASKS'
AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND por.object_id_to1 = elemver.element_version_id
AND por.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
AND por.relationship_type = 'S'
AND elemver.project_id = l_project_id
AND elemver.element_version_id IN
(--Get the Parents of the passed task
SELECT object_id_from1 object_id
FROM pa_object_relationships
WHERE relationship_type = 'S'
AND relationship_subtype = 'TASK_TO_TASK'
START WITH object_id_to1 = l_task_version_id
CONNECT BY Object_id_to1 = PRIOR object_id_from1
UNION
SELECT l_task_version_id--Get the Passed task itself
FROM dual
)
AND NOT EXISTS -- This is to insure that the same record does not get inserted twice
(
SELECT 'xyz'
FROM pa_preview_fin_tasks_temp temp
WHERE temp.element_version_id = elemver.element_version_id
);
DELETE FROM pa_preview_fin_tasks_temp;
l_project_id_tbl.DELETE;
l_element_version_id_tbl.DELETE;
l_prnt_struct_ver_id_tbl.DELETE;
l_prnt_elem_ver_id_tbl.DELETE;
l_child_element_flag_tbl.DELETE;
l_task_name_tbl.DELETE;
l_task_number_tbl.DELETE;
l_object_type_tbl.DELETE;
l_display_sequence_tbl.DELETE;
l_wbs_number_tbl.DELETE;
l_proj_element_id_tbl.DELETE;
l_fin_task_flag_tbl.DELETE;
INSERT INTO pa_preview_fin_tasks_temp
(
PROJECT_ID
, ELEMENT_VERSION_ID
, PARENT_STRUCTURE_VERSION_ID
, PARENT_ELEMENT_VERSION_ID
, CHILD_ELEMENT_FLAG
, TASK_NAME
, TASK_NUMBER
, OBJECT_TYPE
, DISPLAY_SEQUENCE
, WBS_NUMBER
, PROJ_ELEMENT_ID
, FINANCIAL_TASK_FLAG
)
VALUES
(
l_rec_fin_tasks_temp.PROJECT_ID
, l_rec_fin_tasks_temp.element_version_id
, l_rec_fin_tasks_temp.parent_structure_version_id
, l_rec_fin_tasks_temp.parent_element_version_id
, l_rec_fin_tasks_temp.child_element_flag
, l_rec_fin_tasks_temp.task_name
, l_rec_fin_tasks_temp.task_number
, l_rec_fin_tasks_temp.object_type
, l_rec_fin_tasks_temp.display_sequence
, l_rec_fin_tasks_temp.wbs_number
, l_rec_fin_tasks_temp.proj_element_id
, l_rec_fin_tasks_temp.financial_task_flag
);
INSERT INTO pa_preview_fin_tasks_temp
(
PROJECT_ID
, ELEMENT_VERSION_ID
, PARENT_STRUCTURE_VERSION_ID
, PARENT_ELEMENT_VERSION_ID
, CHILD_ELEMENT_FLAG
, TASK_NAME
, TASK_NUMBER
, OBJECT_TYPE
, DISPLAY_SEQUENCE
, WBS_NUMBER
, PROJ_ELEMENT_ID
, FINANCIAL_TASK_FLAG
)
VALUES
(
l_project_id_tbl(iCounter1)
, l_element_version_id_tbl(iCounter1)
, l_prnt_struct_ver_id_tbl(iCounter1)
, l_prnt_elem_ver_id_tbl(iCounter1)
, l_child_element_flag_tbl(iCounter1)
, l_task_name_tbl(iCounter1)
, l_task_number_tbl(iCounter1)
, l_object_type_tbl(iCounter1)
, l_display_sequence_tbl(iCounter1)
, l_wbs_number_tbl(iCounter1)
, l_proj_element_id_tbl(iCounter1)
, l_fin_task_flag_tbl(iCounter1)
);