[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
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 NUMBER :=FND_API.G_VALID_LEVEL_FULL,
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_ref_task_id IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_peer_or_sub IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_id IN NUMBER,
p_task_number IN VARCHAR2,
p_task_name IN VARCHAR2,
p_task_description IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_location_id IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_country IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_territory_code IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_state_region IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_city IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_manager_id IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_task_manager_name IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_carrying_out_org_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_priority_code IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_TYPE_ID IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_status_code IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_inc_proj_progress_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_pm_product_code IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
P_PM_SOURCE_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, -- Added for Huawei 15876400
p_pm_task_reference IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_closed_date IN DATE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_transaction_start_date IN DATE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_transaction_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_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_address_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_address1 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_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_task_weighting_deriv_code IN VARCHAR2:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_work_item_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_uom_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_wq_actual_entry_code IN VARCHAR2:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_progress_entry_page_id IN NUMBER:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_task_progress_entry_page IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_parent_structure_id IN NUMBER:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_phase_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_phase_version_id IN NUMBER:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_record_version_number IN NUMBER,
-- xxlu added task DFF attributes
p_tk_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_tk_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
-- end xxlu changes
p_Base_Perc_Comp_Deriv_Code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_gen_etc_src_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
-- Bug#3491609 : Workflow Chanegs FP M
p_wf_item_type IN pa_proj_elements.wf_item_type%TYPE :=NULL,
p_wf_process IN pa_proj_elements.wf_process%TYPE :=NULL,
p_wf_lead_days IN pa_proj_elements.wf_start_lead_days%TYPE :=NULL,
p_wf_enabled_flag IN pa_proj_elements.enable_wf_flag%TYPE :=NULL,
-- Bug#3491609 : Workflow Chanegs FP M
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_shared IN VARCHAR2 := 'X' -- Bug 3451073
,p_dates_check in VARCHAR2 := 'Y' --bug 8301015
) IS
l_api_name CONSTANT VARCHAR(30) := 'UPDATE_TASK';
select ppe.MANAGER_PERSON_ID, papf.FULL_NAME
from pa_proj_elements ppe, per_all_people_f papf
where ppe.proj_element_id = p_task_id
AND ppe.manager_person_id = papf.person_id
AND trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1); --Included by avaithia for Bug # 3448680
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT project_id
FROM pa_proj_elements
WHERE proj_element_id = p_task_id;
SELECT START_DATE, COMPLETION_DATE
FROM pa_tasks
WHERE task_id = c_task_id;
SELECT pe.ELEMENT_NUMBER, pe.NAME, pe.DESCRIPTION, hou.name
FROM pa_proj_elements pe, hr_all_organization_units hou
WHERE pe.proj_element_id = p_task_id
AND pe.carrying_out_organization_id = hou.organization_id;
SELECT task_id
FROM PA_TASKS
WHERE task_id = p_task_id ;
pa_debug.init_err_stack ('PA_TASK_PUB1.UPDATE_TASK');
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK begin');
savepoint update_Task;
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
IF l_update_task_num_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_CHG_TASK_NUM');
IF l_update_task_name_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_CHG_TASK_NAME');
IF l_update_task_dates_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_CHG_TASK_DATES');
IF l_update_task_desc_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_CHG_TASK_DESC');
IF l_update_task_org_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_CHG_TASK_ORG');
select 'x' into l_dummy
from PA_PROJ_ELEMENTS
where proj_element_id = p_task_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_task_id
and record_version_number = p_record_version_number;
select project_id into l_project_id
from PA_PROJ_ELEMENTS
where proj_element_id = p_task_id;
select MANAGER_PERSON_ID into l_cur_task_mgr_person_id
from pa_proj_elements
where proj_element_id = p_task_id;
select project_id into l_project_id
from PA_PROJ_ELEMENTS
where proj_element_id = p_task_id;
PA_TASK_PVT1.Update_Task(
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_ref_task_id => p_ref_task_id
,p_peer_or_sub => p_peer_or_sub
,p_task_id => p_task_id
,p_task_number => rtrim(p_task_number)
,p_task_name => rtrim(p_task_name)
,p_task_description => rtrim(p_task_description)
,p_location_id => p_location_id
,p_country => p_country
,p_territory_code => p_territory_code
,p_state_region => p_state_region
,p_city => p_city
,p_task_manager_id => l_task_manager_id
,p_carrying_out_org_id => l_carrying_out_org_id
,p_priority_code => p_priority_code
,p_TYPE_ID => p_TYPE_ID
,p_status_code => p_status_code
,p_inc_proj_progress_flag => p_inc_proj_progress_flag
,p_pm_product_code => p_pm_product_code
,P_PM_SOURCE_CODE => P_PM_SOURCE_CODE -- Huawei 15876400 source code
,p_pm_task_reference => p_pm_task_reference
,p_closed_date => p_closed_date
,p_transaction_start_date => p_transaction_start_date
,p_transaction_finish_date => p_transaction_finish_date
,p_attribute_category => p_attribute_category
,p_attribute1 => rtrim(p_attribute1)
,p_attribute2 => rtrim(p_attribute2)
,p_attribute3 => rtrim(p_attribute3)
,p_attribute4 => rtrim(p_attribute4)
,p_attribute5 => rtrim(p_attribute5)
,p_attribute6 => rtrim(p_attribute6)
,p_attribute7 => rtrim(p_attribute7)
,p_attribute8 => rtrim(p_attribute8)
,p_attribute9 => rtrim(p_attribute9)
,p_attribute10 => rtrim(p_attribute10)
,p_attribute11 => rtrim(p_attribute11)
,p_attribute12 => rtrim(p_attribute12)
,p_attribute13 => rtrim(p_attribute13)
,p_attribute14 => rtrim(p_attribute14)
,p_attribute15 => rtrim(p_attribute15)
,p_address_id => p_address_id
,p_address1 => p_address1
,p_work_type_id => p_work_type_id
,p_service_type_code => p_service_type_code
,p_chargeable_flag => p_chargeable_flag
,p_billable_flag => p_billable_flag
,p_receive_project_invoice_flag => p_receive_project_invoice_flag
,p_task_weighting_deriv_code => p_task_weighting_deriv_code
,p_work_item_code => p_work_item_code
,p_uom_code => p_uom_code
,p_wq_actual_entry_code => p_wq_actual_entry_code
,p_task_progress_entry_page_id => l_task_progress_entry_page_id
,p_task_progress_entry_page => p_task_progress_entry_page
,p_parent_structure_id => p_parent_structure_id
,p_phase_code => p_phase_code
,p_phase_version_id => p_phase_version_id
,p_record_version_number => p_record_version_number
,p_tk_attribute_category => p_tk_attribute_category
,p_tk_attribute1 => p_tk_attribute1
,p_tk_attribute2 => p_tk_attribute2
,p_tk_attribute3 => p_tk_attribute3
,p_tk_attribute4 => p_tk_attribute4
,p_tk_attribute5 => p_tk_attribute5
,p_tk_attribute6 => p_tk_attribute6
,p_tk_attribute7 => p_tk_attribute7
,p_tk_attribute8 => p_tk_attribute8
,p_tk_attribute9 => p_tk_attribute9
,p_tk_attribute10 => p_tk_attribute10
-- Begin : Added for FP_M changes Bug 3305199
,p_Base_Perc_Comp_Deriv_Code => p_Base_Perc_Comp_Deriv_Code
-- End : Added for FP_M changes Bug 3305199
,p_gen_etc_src_code => p_gen_etc_src_code
,p_wf_item_type => p_wf_item_type
,p_wf_process => p_wf_process
,p_wf_lead_days => p_wf_lead_days
,p_wf_enabled_flag => p_wf_enabled_flag
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_shared => p_shared -- Bug 3451073
,p_dates_check =>p_dates_check --bug 8301015
);
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK END');
rollback to update_task;
rollback to update_task;
p_procedure_name => 'UPDATE_TASK',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_task;
p_procedure_name => 'UPDATE_TASK',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Task;
PROCEDURE Update_Task_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 NUMBER :=FND_API.G_VALID_LEVEL_FULL,
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_ref_task_version_id IN NUMBER,
p_peer_or_sub IN VARCHAR2,
p_task_version_id IN NUMBER,
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,
p_action IN VARCHAR2 := 'NULL',
p_WEIGHTING_PERCENTAGE IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_TASK_UNPUB_VER_STATUS_CODE IN VARCHAR2 := NULL,
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_TASK_VERSION';
pa_debug.init_err_stack ('PA_TASK_PUB1.UPDATE_TASK_VERSION');
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK_VERSION begin');
savepoint update_Task_version;
select 'x' into l_dummy
from PA_PROJ_ELEMENT_VERSIONS
where element_version_id = p_task_version_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
select 'x' into l_dummy
from PA_PROJ_ELEMENT_VERSIONS
where element_version_id = p_task_version_id
and record_version_number = p_record_version_number;
PA_TASK_PVT1.Update_Task_Version(
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_ref_task_version_id => p_ref_task_version_id
,p_peer_or_sub => p_peer_or_sub
,p_task_version_id => p_task_version_id
,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
,p_action => p_action
,p_WEIGHTING_PERCENTAGE => p_WEIGHTING_PERCENTAGE
,p_TASK_UNPUB_VER_STATUS_CODE => p_TASK_UNPUB_VER_STATUS_CODE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK_VERSION END');
rollback to update_task_version;
rollback to update_task_version;
p_procedure_name => 'UPDATE_TASK_VERSION',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_task_version;
p_procedure_name => 'UPDATE_TASK_VERSION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_task_version;
PROCEDURE Delete_Task_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 NUMBER :=FND_API.G_VALID_LEVEL_FULL,
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 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_task_version_id IN NUMBER,
p_record_version_number IN NUMBER,
p_called_from_api IN VARCHAR2 := 'ABCD',
p_structure_type IN VARCHAR2 := 'WORKPLAN', --bug 3301192
p_calling_from IN VARCHAR2 := 'XYZ', -- Bug 6023347
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_TASK_VERSION';
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id;
select a.project_id, b.proj_element_id, parent_structure_version_id,
TASK_UNPUB_VER_STATUS_CODE, b.link_task_flag
from pa_proj_element_versions a,
pa_proj_elements b --bug 4501280
where element_version_id = c_task_version_id
--bug 4501280
AND a.proj_element_id = b.proj_element_id
AND a.project_id = b.project_id
--bug 4501280
;
SELECT 'Y'
FROM DUAL
WHERE EXISTS(
SELECT pors.object_relationship_id
FROM pa_object_relationships pors,
pa_object_relationships pors1
WHERE pors1.object_id_from1 = c_task_version_id
AND pors.object_id_from1 = pors1.object_id_to1
AND pors1.relationship_type = 'S'
AND pors.relationship_type IN ('LW','LF')
AND pors.object_id_from2 <> pors.object_id_to2
AND pors.object_type_from = 'PA_TASKS'
AND pors.object_type_to = 'PA_STRUCTURES');
SELECT max(notification_id) ntf_id
FROM WF_NOTIFICATIONS WFN
WHERE message_type = 'PATASKWF'
AND status = 'OPEN'
--bug 13395163
--bug 14284485
AND EXISTS (
SELECT /*+ NO_UNNEST */ 1
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE notification_id = wfn.notification_id
AND name = 'TASK_NUMBER'
AND text_value like (select element_number from pa_proj_elements
where proj_element_id = c_task_id)
)
AND EXISTS (
SELECT /*+ NO_UNNEST */ 1
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE notification_id = wfn.notification_id
AND name = 'PROJECT_NUMBER'
AND text_value like (select segment1 from pa_projects_all
where project_id = c_project_id)
);
pa_debug.init_err_stack ('PA_TASK_PUB1.DELETE_TASK_VERSION');
pa_debug.debug('PA_TASK_PUB1.DELETE_TASK_VERSION begin');
savepoint delete_Task_version;
select 'x' into l_dummy
from PA_PROJ_ELEMENT_VERSIONS
where element_version_id = p_task_version_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
select 'x' into l_dummy
from PA_PROJ_ELEMENT_VERSIONS
where element_version_id = p_task_version_id
and record_version_number = p_record_version_number;
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => x_err_code,
P_ERROR_STACK => x_err_stack,
P_ERROR_STAGE => x_err_stage );
IF l_delete_task_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_CANNOT_DELETE');
/* Bug 4068685 : If p_structure_version_id is not passed to this Delete_Task_Version API
then it will be defaulted to MISS_NUM .In that case ,Use the retrieved value of
parent_structure_version_id from l_task_info_rec
*/
--anurag
open task_ntf(l_task_info_rec.proj_element_id,l_task_info_rec.project_id);
update WF_NOTIFICATIONS
set status = 'CLOSED'
where notification_id = l_ntf_id ;
l_task_info_rec.task_unpub_ver_status_code = 'TO_BE_DELETED')
AND p_called_from_api <> 'DELETE_STRUCTURE_VERSION' THEN -- Bug 3056077. We need to delete the task versions
--change status to TO_BE_DELETED -- when the structure version is to be deleted.
-- 3955848 Added following code to validate task to deliverable association deletion
-- related validations , p_delete_or_validate is passed as 'V' because only validation will be done
PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk
(
p_task_element_id => l_task_info_rec.proj_element_id
,p_project_id => l_task_info_rec.project_id
,p_task_version_id => p_task_version_id
,p_delete_or_validate => 'V'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_TASK_PVT1.update_task_ver_delete_status(
p_task_version_id => p_task_version_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
NULL; --continue to delete
NULL; --continue to delete
NULL; --continue to delete
l_task_info_rec.task_unpub_ver_status_code = 'TO_BE_DELETED')
AND p_called_from_api <> 'DELETE_STRUCTURE_VERSION' THEN -- Bug 3056077. We need to delete the task versions
--change status to TO_BE_DELETED -- when the structure version is to be deleted.
-- 3955848 Added following code to validate task to deliverable association deletion
-- related validations , p_delete_or_validate is passed as 'V' because only validation will be done
PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk
(
p_task_element_id => l_task_info_rec.proj_element_id
,p_project_id => l_task_info_rec.project_id
,p_task_version_id => p_task_version_id
,p_delete_or_validate => 'V'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_TASK_PVT1.update_task_ver_delete_status(
p_task_version_id => p_task_version_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
NULL; --delete task version
NULL; --delete task version
PA_TASK_PVT1.Delete_Task_Version(
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_version_id => l_structure_version_id -- Old Value : p_structure_version_id : 4068685
,p_task_version_id => p_task_version_id
,p_record_version_number => p_record_version_number
,p_called_from_api => p_called_from_api
,p_structure_type => p_structure_type -- FP_M changes 3305199
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.debug('PA_TASK_PUB1.DELETE_TASK_VERSION END');
rollback to delete_task_version;
rollback to delete_task_version;
p_procedure_name => 'DELETE_TASK_VERSION',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_task_version;
p_procedure_name => 'DELETE_TASK_VERSION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_task_version;
PROCEDURE Update_Schedule_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 NUMBER :=FND_API.G_VALID_LEVEL_FULL,
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_schedule_id IN NUMBER,
p_calendar_id IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_calendar_name 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_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_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_estimate_start_date IN DATE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_estimate_finish_date IN DATE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_duration IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_early_start_date IN DATE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_early_end_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_end_date IN DATE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_milestone_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, -- Bug 2791410
p_critical_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, -- Bug 2791410
p_WQ_PLANNED_QUANTITY IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_PLANNED_EFFORT IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_PLANNED_EQUIP_EFFORT IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, --bug 3305199
--bug 3305199 schedule options
p_def_sch_tool_tsk_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_def_sch_tool_tsk_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_constraint_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_constraint_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_free_slack IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_total_slack IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_effort_driven_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_level_assignments_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
--end bug 3305199
p_record_version_number IN NUMBER,
p_ext_act_duration IN NUMBER:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, --Bug no 3450684
p_ext_remain_duration IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, --Bug no 3450684
p_ext_sch_duration IN NUMBER:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, --Bug no 3450684
p_attribute_category IN pa_proj_elem_ver_schedule.attribute_category%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute1 IN pa_proj_elem_ver_schedule.attribute1%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute2 IN pa_proj_elem_ver_schedule.attribute2%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute3 IN pa_proj_elem_ver_schedule.attribute3%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute4 IN pa_proj_elem_ver_schedule.attribute4%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute5 IN pa_proj_elem_ver_schedule.attribute5%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute6 IN pa_proj_elem_ver_schedule.attribute6%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute7 IN pa_proj_elem_ver_schedule.attribute7%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute8 IN pa_proj_elem_ver_schedule.attribute8%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute9 IN pa_proj_elem_ver_schedule.attribute9%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute10 IN pa_proj_elem_ver_schedule.attribute10%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute11 IN pa_proj_elem_ver_schedule.attribute11%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute12 IN pa_proj_elem_ver_schedule.attribute12%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute13 IN pa_proj_elem_ver_schedule.attribute13%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute14 IN pa_proj_elem_ver_schedule.attribute14%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute15 IN pa_proj_elem_ver_schedule.attribute15%TYPE :=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
) IS
l_api_name CONSTANT VARCHAR(30) := 'Update_Schedule_Version';
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT project_id
FROM pa_proj_elem_ver_schedule
WHERE pev_schedule_id = p_pev_schedule_id;
SELECT SCHEDULED_START_DATE, SCHEDULED_FINISH_DATE
FROM pa_proj_elem_ver_schedule
WHERE pev_schedule_id = p_pev_schedule_id;
pa_debug.init_err_stack ('PA_TASK_PUB1.update_schedule_VERSION');
pa_debug.debug('PA_TASK_PUB1.update_SCHEDULE_VERSION begin');
savepoint update_schedule_version;
Select a.Parent_Structure_Version_ID
Into l_Structure_Version_ID
From PA_Proj_Element_Versions a, pa_proj_elem_ver_schedule b
Where a.Element_Version_ID = b.Element_Version_ID
and b.pev_schedule_id = p_pev_schedule_id;
PA_PROJECT_STRUCTURE_PVT1.update_sch_dirty_flag(
p_structure_version_id => l_Structure_Version_ID
,p_dirty_flag => 'Y' --bug 3902282
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
IF l_update_task_dates_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_CHG_TASK_DATES');
select 'x' into l_dummy
from PA_PROJ_ELEM_VER_SCHEDULE
where pev_schedule_id = p_pev_schedule_id
and record_version_number = p_record_version_number
for update of record_version_number NOWAIT;
select 'x' into l_dummy
from PA_PROJ_ELEM_VER_SCHEDULE
where pev_schedule_id = p_pev_schedule_id
and record_version_number = p_record_version_number;
PA_TASK_PVT1.Update_Schedule_Version(
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_schedule_id => p_pev_schedule_id
,p_calendar_id => p_calendar_id
,p_scheduled_start_date => p_scheduled_start_date
,p_scheduled_end_date => p_scheduled_end_date
,p_obligation_start_date => p_obligation_start_date
,p_obligation_end_date => p_obligation_end_date
,p_actual_start_date => p_actual_start_date
,p_actual_finish_date => p_actual_finish_date
,p_estimate_start_date => p_estimate_start_date
,p_estimate_finish_date => p_estimate_finish_date
,p_duration => p_duration
,p_early_start_date => p_early_start_date
,p_early_end_date => p_early_end_date
,p_late_start_date => p_late_start_date
,p_late_end_date => p_late_end_date
,p_milestone_flag => p_milestone_flag
,p_critical_flag => p_critical_flag
,p_WQ_PLANNED_QUANTITY => p_WQ_PLANNED_QUANTITY
,p_PLANNED_EFFORT => p_PLANNED_EFFORT
,p_PLANNED_EQUIP_EFFORT => p_PLANNED_EQUIP_EFFORT
,p_def_sch_tool_tsk_type_code => p_def_sch_tool_tsk_type_code
,p_constraint_type_code => p_constraint_type_code
,p_constraint_date => p_constraint_date
,p_free_slack => p_free_slack
,p_total_slack => p_total_slack
,p_effort_driven_flag => p_effort_driven_flag
,p_level_assignments_flag => p_level_assignments_flag
,p_record_version_number => p_record_version_number
,p_ext_act_duration => p_ext_act_duration
,p_ext_remain_duration => p_ext_remain_duration
,p_ext_sch_duration => p_ext_sch_duration
,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
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.debug('PA_TASK_PUB1.UPDATE_SCHEDULE_VERSION END');
rollback to update_schedule_version;
rollback to update_schedule_version;
p_procedure_name => 'UPDATE_SCHEDULE_VERSION',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_schedule_version;
p_procedure_name => 'UPDATE_SCHEDULE_VERSION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Schedule_Version;
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = c_dest_task_version_id and project_id=c_project_id;
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_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';
/* Bug 2680486 -- Performance changes -- Selected project_id also in the following cursor, which will
be used in other cursors. Also Restructured it to avoid Non-mergable view issue */
CURSOR cur_obj_rel( x_element_version_id NUMBER )
IS
SELECT b.element_version_id task_version_id, b.display_sequence display_sequence, b.proj_element_id,
b.parent_Structure_version_id parent_Structure_version_id, b.wbs_level wbs_level,
b.project_id,
b.attribute_category,
b.attribute1,
b.attribute2,
b.attribute3,
b.attribute4,
b.attribute5,
b.attribute6,
b.attribute7,
b.attribute8,
b.attribute9,
b.attribute10,
b.attribute11,
b.attribute12,
b.attribute13,
b.attribute14,
b.attribute15,
Financial_Task_Flag -- FP_M changes 3305199 Bhumesh
FROM pa_proj_element_versions b,
pa_proj_elements c
WHERE b.proj_element_id = c.proj_element_id
AND c.link_task_flag = 'N'
AND p_copy_option IN ( 'PA_TASK_SUBTASK', 'PA_ENTIRE_VERSION' )
/*
-- Added for FP_M changes : Bug 3305199
and ( p_structure_type = 'WORKPLAN' OR
( p_structure_type = 'FINANCIAL' and Financial_Task_Flag = 'Y')
)
-- End of FP_M changes : Bug 3305199
*/
AND b.element_version_id IN ( SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = x_element_version_id AND relationship_type = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1 AND relationship_type = prior relationship_type AND relationship_type = 'S' )
UNION
SELECT element_version_id task_version_id, display_sequence display_sequence, ppev.proj_element_id proj_element_id,
parent_Structure_version_id parent_Structure_version_id, wbs_level wbs_level,
project_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
Financial_Task_Flag -- FP_M changes 3305199 Bhumesh
FROM pa_proj_element_versions ppev
WHERE ppev.element_version_id = p_src_task_version_id
/* not required any more
-- Added for FP_M changes : Bug 3305199
and ( p_structure_type = 'WORKPLAN' OR
( p_structure_type = 'FINANCIAL' and Financial_Task_Flag = 'Y')
)
-- End of FP_M changes : Bug 3305199
*/
AND p_copy_option IN( 'PA_TASK_ONLY', 'PA_TASK_SUBTASK' ) --here PA_TASK_SUBTASK is included in the in list to
--include the task version id since it will not have any peer
--relationship record in object rel table.
ORDER BY 2;
SELECT
PROJ_ELEMENT_ID
,PROJECT_ID
,OBJECT_TYPE
,ELEMENT_NUMBER
,NAME
,DESCRIPTION
,STATUS_CODE
,WF_STATUS_CODE
,PM_SOURCE_CODE
,PM_SOURCE_REFERENCE
,CLOSED_DATE
,LOCATION_ID
,MANAGER_PERSON_ID
,CARRYING_OUT_ORGANIZATION_ID
,TYPE_ID
,PRIORITY_CODE
,INC_PROJ_PROGRESS_FLAG
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,PARENT_STRUCTURE_ID
,PHASE_CODE
,PHASE_VERSION_ID
,WF_ITEM_TYPE
,WF_PROCESS
,WF_START_LEAD_DAYS
,BASE_PERCENT_COMP_DERIV_CODE
FROM pa_proj_elements
WHERE proj_element_id = p_proj_element_id;
SELECT a.object_id_from1 parent_task_id
FROM( SELECT object_id_from1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH OBJECT_ID_TO1 = x_child_task_id
--hsiu: bug 2669388
-- START WITH object_id_from1 = x_child_task_id
--traverse reverse
CONNECT BY object_id_to1 = PRIOR object_id_from1 AND relationship_type = 'S' ) a, pa_proj_element_versions b
WHERE a.object_id_from1 = b.element_version_id
AND b.wbs_level = x_wbs_level;
SELECT b.element_version_id parent_task_id
FROM pa_proj_element_versions b
WHERE b.wbs_level = x_wbs_level
AND b.element_version_id IN ( SELECT object_id_from1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH OBJECT_ID_TO1 = x_child_task_id
--hsiu: bug 2669388
-- START WITH object_id_from1 = x_child_task_id
--traverse reverse
CONNECT BY object_id_to1 = PRIOR object_id_from1 AND relationship_type = 'S'
AND relationship_type = PRIOR relationship_type) ;
SELECT proj_element_id
FROM pa_proj_elem_ver_structure
WHERE element_version_id = x_structure_version_id
AND project_id = x_project_id;
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = x_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = 'WORKPLAN';
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code IN( 'FINANCIAL' );
select 'Y'
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
AND project_id = c_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
select 'Y'
from pa_proj_elem_ver_structure
where element_version_id = c_structure_version_id
and project_id = c_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_dest_task_version_id;
SELECT proj_element_id
FROM pa_proj_element_versions
where element_version_id = c_element_version_id
AND project_id = c_project_id;
SELECT *
FROM pa_tasks
WHERE task_id = c_task_id;
select '1'
from pa_object_relationships
where object_id_from1 = l_parent_task_version_id
and object_type_from IN ('PA_TASKS','PA_STRUCTURES')
and object_type_to IN ('PA_TASKS','PA_STRUCTURES')
and relationship_type = 'S';
IS select 'Y'
from pa_projects_all
where project_id = c_project_id
and template_flag = 'Y';
SELECT * FROM pa_proj_elem_ver_schedule
WHERE project_id = c_project_id
AND element_version_id = c_element_version_id;
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_elem_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
select WBS_LEVEL
from pa_proj_element_versions
where element_version_id = c_element_version_id;
select 'Y' from dual where
EXISTS
(SELECT *
FROM pa_projects_all
WHERE project_id = c_project_id
AND CBS_VERSION_ID IS NOT NULL
AND CBS_ENABLE_FLAG = 'Y');
select 'Y' from dual where
EXISTS
(SELECT *
FROM pa_projects_all
WHERE project_id = c_src_project_id
AND nvl(CBS_VERSION_ID,-1) = nvl((select CBS_VERSION_ID from pa_projects_all
WHERE project_id = c_trgt_project_id),-1)); -- added nvl condition bug#16713476
SELECT
'x'
FROM
pa_object_relationships obj
WHERE
obj.object_id_to1 = task_ver_id
AND obj.object_id_from1 = struct_ver_id
AND object_type_from = 'PA_STRUCTURES'
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S'
AND relationship_subtype = 'STRUCTURE_TO_TASK';
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
-- If we copy t1.1 and select the Placement as "1", then t1.1.2(wbs_level is 3) was created as peer of
-- t1.1.1.1(wbs_level is 4) because of wbs_level for the task t1.1.2 is 3 and ref_tk_wbs_level is 1 (because we selected 1 as placement).
-- and system calculated wbs_level for the new task (t1.1.2) as 4.
-- Below code will ensure that task t1.1.2 should be peer of t1.1.1 not t1.1.1.1
-- Task wbs_level from db Relative wbs_level by below code
-- t1.1 2 1
-- t1.1.1 3 2
-- t1.1.1.1 4 3
-- t1.1.1.1.1 5 4
-- t1.1.2 3 2
-- t1.1.2.1 4 3
OPEN cur_rev_parent_task( l_ref_task_version_id, l_current_tk_wbs_level - l_diff_bw_tk_wbs_level + l_ref_tk_wbs_level);
/* Bug 2623999 -- added the following select to get the ref_task_id*/
--bug 2846700
--modified sql to get correct reference task
--mwasowic: handle a case when we're copying the top task and
-- l_ref_task_version_id is really a structure version id, bug 3587047
IF p_dest_structure_version_id = p_dest_task_version_id THEN
-- 3905123 added below code
-- check whether source task is top task or not
-- if source task is top task
-- in destination structure, check the task is getting created as PEER or SUB
-- if task is getting created as SUB
-- pass l_ref_task_id as NULL
-- else
-- derive proj_element_id for the destination task and pass as l_ref_task_id
-- end if
-- else
-- if copy_option is 'PA_TASK_ONLY' or 'PA_TASK_SUBTASK' and source task is not the top task
-- and first task is getting created in destination structure then
-- pass l_ref_task_id as NULL
-- ( this code will be only called once while creating the first top task in dest struct,
-- which is not the top task in source structure , copy option is PA_TASK_ONLY or PA_TASK_SUBTASK )
-- else
-- derive proj_element_id for the destination and pass as l_ref_task_id
-- end if
-- end if
-- Exp : Copy Option is 'ENTIRE_STRUCTURE' and source structure is
-- |_T1 ( l_ref_task_id will be passed as NULL )
-- | |_T1.1 ( l_ref_task_id will be passed as detn task T1's proj_element_id )
-- |_T2 ( l_ref_task_id will be passed as detn task T1's proj_element_id )
is_top_task := null;
SELECT proj_element_id
INTO l_ref_task_id
FROM pa_proj_element_versions
WHERE element_version_id = l_ref_task_version_id;
SELECT proj_element_id
INTO l_ref_task_id
FROM pa_proj_element_versions
WHERE element_version_id = l_ref_task_version_id;
SELECT proj_element_id
INTO l_ref_task_id
FROM pa_proj_element_versions
WHERE element_version_id = l_ref_task_version_id;
SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_dest_structure_version_id
AND object_type = 'PA_STRUCTURES';
SELECT proj_element_id, project_id
INTO l_ref_task_id, l_ref_project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_dest_task_version_id;
SELECT proj_element_id, project_id
INTO l_task_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_src_task_version_id;
l_copy_node_flag := 'S'; ---copy selected task only
l_copy_node_flag := 'T'; ---copy selected task and sub tasks
* COPY PLANNING TXNS MOVED BEFORE DELETE PLANNING TXNS CALL
* =======================================================*/
-- START OF BLOCK MOVED
IF l_wp_type = 'Y' AND l_src_wp_type='Y' -- 4223490 : Included l_src_wp_type='Y' because both source and destination shud be WP
THEN
/* 4201927 : l_copy_external_flag is already derived as in <>
IF p_src_project_id = l_dest_project_id
THEN
l_copy_external_flag := 'N';
l_lowest_task_flag2 = 'N' /* reference task is no more a lowest task therefore call delete planning api */
THEN
l_task_ver_ids2.extend(1); /* Venky */
/*moved pa_fp_planning_transaction_pub.delete_planning_transactions into plsql block */
DECLARE
--p1 bug 3888432
l_assign_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE wbs_element_Version_id = c_task_ver_id
AND ta_display_flag = 'N';
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'ASSIGNMENT'
-- ,p_element_version_id_tbl => l_task_ver_ids2
-- ,p_maintain_reporting_lines => 'Y'
,p_resource_assignment_tbl => l_assign_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => l_dest_project_id
,p_structure_version_id => p_dest_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);
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code IN( 'FINANCIAL' );
select 'Y'
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
AND project_id = c_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
select b.object_id_to1 object_id_to1
from pa_object_relationships a,
pa_object_relationships b
where a.object_id_to1 = p_task_version_id
and a.object_type_to = 'PA_TASKS'
and a.relationship_type = 'S' -- Bug # 4622939.
and a.object_id_from1 = b.object_id_from1
and a.object_type_from = b.object_type_from
and b.object_type_to = 'PA_TASKS'
and b.relationship_type = 'S' -- Bug # 4622939.
and b.object_id_to1 <> p_task_version_id;
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_elem_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id;
SELECT parent_task_id, top_task_id FROM pa_tasks
WHERE project_id = c_project_id and task_id = c_task_id;
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
and object_type_to = 'PA_TASKS'
start with object_id_from1 = c_task_ver_id
and object_type_from = 'PA_TASKS'
connect by prior object_id_to1 = object_id_from1
and prior object_type_to = object_type_from
and prior relationship_type = relationship_type
UNION
select element_version_id
from pa_proj_element_versions
where element_version_id = c_task_ver_id;
select object_id_to1
from pa_object_relationships
where relationshiP_type = 'S'
and object_type_to = 'PA_TASKS'
start with object_id_to1 = c_task_ver_id
and object_type_to = 'PA_TASKS'
connect by prior object_id_from1 = object_id_to1
and prior object_type_from = object_type_to
and prior relationship_type = relationship_type
intersect
select a.object_id_to1
from pa_object_relationships a, pa_proj_element_versions b
where a.relationshiP_type = 'S'
and a.object_id_from1 = b.parent_structure_version_id
and b.element_version_id = c_task_ver_id
and a.object_type_from = 'PA_STRUCTURES';
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
IF l_update_parent_task_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_MOVE_TASK');
SELECT proj_element_id
INTO l_proj_element_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
SELECT display_sequence, parent_structure_version_id
INTO l_ref_display_sequence, l_ref_parent_struct_ver_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_ref_task_version_id;
SELECT display_sequence, parent_structure_version_id
INTO l_display_sequence, l_parent_struct_ver_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
SELECT object_id_from1, object_type_from
INTO l_parent_id, l_parent_object_type
FROM PA_OBJECT_RELATIONSHIPS
WHERE object_type_to = 'PA_TASKS'
AND object_id_to1 = p_task_version_id
AND relationship_type = 'S'
AND object_type_from in ('PA_STRUCTURES', 'PA_TASKS');
p_msg_name => 'PA_PS_CHK_DELIV_UPDATE');
PA_TASK_PUB1.Update_Task_Version
( p_validate_only => FND_API.G_FALSE,
p_ref_task_version_id => p_ref_task_version_id,
p_peer_or_sub => p_peer_or_sub,
p_task_version_id => p_task_version_id,
p_record_version_number => p_record_version_number,
p_action => 'MOVE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
/* SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id
AND object_type = 'PA_STRUCTURES';*/
SELECT proj_element_id, project_id, element_version_id INTO l_structure_id, l_project_id, l_structure_ver_id
FROM pa_proj_element_versions
WHERE element_version_id = ( Select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = p_task_version_id )
AND object_type = 'PA_STRUCTURES';
SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
FROM PA_TASKS pt,
pa_proj_elem_ver_structure ppa,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = p_task_version_id
AND ppev.project_id = ppa.project_id
AND ppev.parent_structure_version_id = ppa.element_version_id
AND ppev.proj_element_id = pt.task_id;
SELECT proj_element_id
INTO l_ref_task_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_ref_task_version_id;
SELECT record_version_number, parent_task_id --get old parent id for bug 2947492 (move )
,top_task_id --bug 2967204
INTO l_record_version_number, l_old_parent_task_id
,l_old_top_task_id --bug 2967204
FROM pa_tasks
WHERE task_id = l_task_id
AND project_id = l_project_id;
/* commented for process update flag changes
IF (l_shared = 'N') AND
(l_wp_type = 'Y') AND
(l_weighting_basis_Code = 'MANUAL') THEN
--do not set the flag to 'Y'
NULL;
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => l_project_id
,p_structure_version_id => l_parent_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_PROJECT_STRUCTURE_PVT1.update_sch_dirty_flag(
p_structure_version_id => P_Structure_Version_ID
,p_dirty_flag => 'Y' --bug 3902282
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*moved pa_fp_planning_transaction_pub.delete_planning_transactions into plsql block */
DECLARE
--p1 bug 3888432
l_assign_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE wbs_element_Version_id = c_task_ver_id
AND ta_display_flag = 'N';
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'ASSIGNMENT'
-- ,p_element_version_id_tbl => l_task_ver_ids2
-- ,p_maintain_reporting_lines => 'Y'
,p_resource_assignment_tbl => l_assign_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code IN( 'FINANCIAL' );
select 'Y'
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
AND project_id = c_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_elem_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT proj_element_id,project_id
INTO src_proj_element_id,l_project_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id
AND parent_structure_version_id = p_structure_version_id;
SELECT proj_element_id, wbs_level, display_sequence, parent_structure_version_id
INTO l_proj_element_id, l_wbs_level, l_display_sequence, l_parent_structure_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
SELECT wbs_level, display_sequence, parent_structure_version_id
INTO l_wbs_level, l_display_sequence, l_parent_structure_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
SELECT wbs_level, element_version_id
INTO l_prev_wbs_level, l_ref_task_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE display_sequence = l_display_sequence - 1
AND parent_structure_version_id = l_parent_structure_version_id;
SELECT element_version_id
INTO l_ref_task_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE wbs_level = l_wbs_level + 1
AND object_type = 'PA_TASKS'
AND display_sequence < l_display_sequence
AND parent_structure_version_id = l_parent_structure_version_id
AND display_sequence =
(SELECT max(display_sequence)
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE wbs_level = l_wbs_level + 1
AND object_type = 'PA_TASKS'
AND display_sequence < l_display_sequence
AND parent_structure_version_id = l_parent_structure_version_id);
PA_TASK_PUB1.Update_Task_Version
( p_validate_only => FND_API.G_FALSE,
p_ref_task_version_id => l_ref_task_version_id,
p_peer_or_sub => l_peer_or_sub,
p_task_version_id => p_task_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 );
SELECT parent_structure_version_id
INTO l_parent_structure_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => l_parent_structure_version_id
,p_task_id => p_task_version_id
,p_display_seq => l_display_sequence
,p_action => 'INDENT'
,p_parent_task_id => NULL
,x_return_status => l_return_status );
SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = ( Select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = p_task_version_id )
AND object_type = 'PA_STRUCTURES';
SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
FROM PA_TASKS pt,
pa_proj_elem_ver_structure ppa,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = p_task_version_id
AND ppev.parent_structure_version_id = ppa.element_version_id
AND ppev.project_id = ppa.project_id
AND ppev.proj_element_id = pt.task_id;
SELECT proj_element_id
INTO l_ref_task_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = l_ref_task_version_id;
SELECT record_version_number INTO l_task_record_version_number
FROM pa_tasks
WHERE task_id = l_task_id
AND project_id = l_project_id;
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code IN( 'FINANCIAL' );
select 'Y'
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
AND project_id = c_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
select b.object_id_to1 object_id_to1
from pa_object_relationships a,
pa_object_relationships b
where a.object_id_to1 = p_task_version_id
and a.object_type_to = 'PA_TASKS'
and a.object_id_from1 = b.object_id_from1
and a.object_type_from = b.object_type_from
and b.object_type_to = 'PA_TASKS'
and b.object_id_to1 <> p_task_version_id;
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_elem_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT wbs_level, display_sequence, parent_structure_version_id
INTO l_wbs_level, l_display_sequence, l_parent_structure_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
SELECT element_version_id
INTO l_ref_task_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE wbs_level = l_wbs_level - 1
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = l_parent_structure_version_id
AND display_sequence < l_display_sequence
AND display_sequence =
(SELECT max(display_sequence)
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE wbs_level = l_wbs_level - 1
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = l_parent_structure_version_id
AND display_sequence < l_display_sequence);
PA_TASK_PUB1.Update_Task_Version
( p_validate_only => FND_API.G_FALSE,
p_ref_task_version_id => l_ref_task_version_id,
p_peer_or_sub => 'PEER',
p_task_version_id => p_task_version_id,
p_record_version_number => p_record_version_number,
p_action => 'OUTDENT',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT parent_structure_version_id
INTO l_parent_structure_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id;
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => l_parent_structure_version_id
,p_task_id => p_task_version_id
,p_display_seq => l_display_sequence
,p_action => 'OUTDENT'
,p_parent_task_id => NULL
,x_return_status => l_return_status );
/* SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id
AND object_type = 'PA_STRUCTURES';*/
SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = ( Select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = p_task_version_id )
AND object_type = 'PA_STRUCTURES';
SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
FROM PA_TASKS pt,
pa_proj_elem_ver_structure ppa,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = p_task_version_id
AND ppev.parent_structure_version_id = ppa.element_version_id
AND ppev.project_id = ppa.project_id
AND ppev.proj_element_id = pt.task_id;
SELECT proj_element_id
INTO l_ref_task_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = l_ref_task_version_id;
SELECT record_version_number INTO l_task_record_version_number
FROM pa_tasks
WHERE task_id = l_task_id
AND project_id = l_project_id;
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = p_ref_task_ver_id
AND object_type = 'PA_TASKS';
IS select 'Y'
from pa_projects_all
where project_id = c_project_id
and template_flag = 'Y';
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_elem_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
select object_type
from pa_proj_element_versions
where element_version_id = p_ref_task_version_id;
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_update_wbs_flag => l_wp_process_flag
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
/*moved pa_fp_planning_transaction_pub.delete_planning_transactions into plsql block */
DECLARE
--p1 bug 3888432
l_assign_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE wbs_element_Version_id = c_task_ver_id
AND ta_display_flag = 'N';
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'ASSIGNMENT'
--,p_element_version_id_tbl => l_task_ver_ids
,p_resource_assignment_tbl => l_assign_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
SELECT count(object_id_to1)
FROM PA_OBJECT_RELATIONSHIPS
WHERE object_id_from1 = c_parent_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 ppe.project_id = ppevs.project_id /* for bug 2822963 */
AND ppe.TYPE_ID = ptt.task_type_id
AND ptt.object_type = 'PA_TASKS' /* for 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; /* for bug 2822963 */
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 ptt.prog_entry_enable_flag, ppe.project_id /* for bug 2822963 */
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' /* for bug 3279978 FP M Enhancement */
AND ppe.TYPE_ID = ptt.task_type_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';
SELECT count(rel.object_id_to1)
FROM PA_OBJECT_RELATIONSHIPS rel,pa_proj_element_versions ver,pa_proj_elements ele,pa_task_types tt
WHERE rel.object_id_from1 = c_parent_element_version_id
AND rel.object_type_to = 'PA_TASKS'
AND rel.relationship_type = 'S'
AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND rel.object_id_to1 = ver.element_version_id
AND ver.proj_element_id = ele.proj_element_id
AND ele.type_id = tt.task_type_id
AND tt.object_type = 'PA_TASKS' /* for bug 3279978 FP M Enhancement */
AND tt.prog_entry_enable_flag = 'Y';
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 = 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 = 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';
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';
procedure Update_Task_Weighting
(
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_object_relationship_id IN NUMBER
,p_weighting_percentage 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
BEGIN
pa_debug.init_err_stack ('PA_TASK_PUB1.UPDATE_TASK_WEIGHTING');
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK_WEIGHTING begin');
savepoint update_task_weighting;
PA_TASK_PVT1.Update_Task_Weighting(
p_object_relationship_id => p_object_relationship_id
,p_weighting_percentage => p_weighting_percentage
,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
);
rollback to update_task_weighting;
rollback to update_task_weighting;
p_procedure_name => 'UPDATE_TASK_WEIGHTING',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_task_weighting;
p_procedure_name => 'UPDATE_TASK_WEIGHTING',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Task_Weighting;
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code IN( 'FINANCIAL' );
SELECT 'Y'
FROM dual
WHERE EXISTS(
SELECT 'xyz'
FROM pa_proj_elem_ver_structure
WHERE proj_element_id = c_structure_id
AND project_id = c_project_id
AND STATUS_CODE = 'STRUCTURE_PUBLISHED'
);
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_elem_ver_id
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S';
SELECT object_id_from1
, object_relationship_id
, record_version_number
, relationship_subtype
FROM pa_object_relationships
WHERE object_id_to1 = p_child_version_id
AND relationship_type = 'S';
SELECT a.object_id_from1
, a.weighting_percentage
FROM pa_object_relationships a
WHERE a.object_id_to1 = c_task_version_id
AND a.object_type_to = 'PA_TASKS'
AND a.relationship_type = 'S'
AND a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
SELECT '1'
FROM dual
WHERE EXISTS
(SELECT 'xyz'
FROM pa_object_relationships
WHERE object_id_from1 = c_task_version_id
AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
AND relationship_type = 'S'
);
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' /* for 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_object_id_to1
and relationship_type = 'S';
select wbs_number
from pa_proj_element_versions
where element_version_id = c_elem_ver_id
and object_type ='PA_TASKS';
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id;
SELECT parent_task_id, top_task_id FROM pa_tasks
WHERE project_id = c_project_id and task_id = c_task_id;
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
select object_id_to1
from pa_object_relationships
where relationshiP_type = 'S'
and object_type_to = 'PA_TASKS'
start with object_id_to1 = c_task_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S'
connect by prior object_id_from1 = object_id_to1
and prior object_type_from = object_type_to
and prior relationship_type = relationship_type
intersect
select a.object_id_to1
from pa_object_relationships a, pa_proj_element_versions b
where a.relationshiP_type = 'S'
and a.object_id_from1 = b.parent_structure_version_id
and b.element_version_id = c_task_ver_id
and a.object_type_from = 'PA_STRUCTURES';
SELECT element_version_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = p_structure_version_id
AND project_id = p_project_id
AND wbs_level = c_src_wbs_level
AND object_type = 'PA_TASKS'
AND display_sequence < src_seq_number
AND display_sequence =
(SELECT max (display_sequence)
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id
AND wbs_level = c_src_wbs_level
AND display_sequence < src_seq_number
AND object_type = 'PA_TASKS');
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
IF l_update_parent_task_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_MOVE_TASK');
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO src_elem_ver_id, src_proj_element_id, src_wbs_number, src_wbs_level, src_seq_number, src_parent_str_ver_id, src_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id
AND project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id;
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO src_elem_ver_id, src_proj_element_id, src_wbs_number, src_wbs_level, src_seq_number, src_parent_str_ver_id, src_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id
AND project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id;
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO ref_elem_ver_id, ref_proj_element_id, ref_wbs_number, ref_wbs_level, ref_seq_number, ref_parent_str_ver_id, ref_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE display_sequence = src_seq_number - 1
AND parent_structure_version_id = p_structure_version_id
AND project_id = p_project_id
AND object_type = 'PA_TASKS';
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO ref_elem_ver_id, ref_proj_element_id, ref_wbs_number, ref_wbs_level, ref_seq_number, ref_parent_str_ver_id, ref_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = p_structure_version_id
AND project_id = p_project_id
AND wbs_level = src_wbs_level+1
AND object_type = 'PA_TASKS'
AND display_sequence < src_seq_number
AND display_sequence =
(SELECT max (display_sequence)
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id
AND wbs_level = src_wbs_level+1
AND display_sequence < src_seq_number
AND object_type = 'PA_TASKS');
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO ref_elem_ver_id, ref_proj_element_id, ref_wbs_number, ref_wbs_level, ref_seq_number, ref_parent_str_ver_id, ref_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = p_structure_version_id
AND project_id = p_project_id
AND (wbs_level = src_wbs_level OR wbs_level = src_wbs_level+1) -- Sub or Peer
AND object_type = 'PA_TASKS'
AND display_sequence < src_seq_number
AND display_sequence =
(SELECT max (display_sequence)
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id
AND (wbs_level = src_wbs_level OR wbs_level = src_wbs_level+1) -- Sub or Peer
AND display_sequence < src_seq_number
AND object_type = 'PA_TASKS');
p_msg_name => 'PA_PS_CHK_DELIV_UPDATE');
PA_TASK_PUB1.Update_Task_Version
( p_validate_only => FND_API.G_FALSE,
p_ref_task_version_id => l_ref_task_version_id,
p_peer_or_sub => l_peer_or_sub,
p_task_version_id => p_task_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_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => l_parent_structure_version_id
,p_task_id => p_task_version_id
,p_display_seq => l_display_sequence
,p_action => 'INDENT'
,p_parent_task_id => NULL
,x_return_status => l_return_status );
/*** The following part should do the same task as done by PA_TASK_PUB1.Update_Task_Version and Update_wbs_numbers ***/
-- Logic Added for plsql table
-- Basically earlier this was done thru update_task_version and update_wbs_numbers
l_element_version_id_tab.delete;
l_proj_element_id_tab.delete;
l_object_type_tab.delete;
l_project_id_tab.delete;
l_parent_str_version_id_tab.delete;
l_display_sequence_tab.delete;
l_wbs_level_tab.delete;
l_wbs_number_tab.delete;
l_record_version_tab.delete;
l_changed_flag_tab.delete;
SELECT element_version_id, proj_element_id, object_type,
project_id, parent_structure_version_id,
display_sequence, wbs_level, wbs_number,
record_version_number, 'N' changed_flag
BULK COLLECT INTO l_element_version_id_tab,
l_proj_element_id_tab, l_object_type_tab,
l_project_id_tab, l_parent_str_version_id_tab,
l_display_sequence_tab, l_wbs_level_tab,
l_wbs_number_tab, l_record_version_tab,
l_changed_flag_tab
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'
AND(
element_version_id = p_task_version_id -- Source task itself
OR (element_version_id IN -- All tasks below the source task with wbs_level >= src_wbs_level-1
(select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
and object_type_to = 'PA_TASKS'
start with object_id_from1 = l_ref_parent_ver_id
connect by object_id_from1 = PRIOR object_id_to1
and relationship_type = PRIOR relationship_type
and relationship_type = 'S')
)
and display_sequence > src_seq_number)
ORDER BY display_sequence ;
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET
wbs_level = l_wbs_level_tab(j) ,
wbs_number = l_wbs_number_tab(j) ,
last_update_date = sysdate ,
last_updated_by = l_user_id ,
last_update_login = l_login_id ,
record_version_number = l_record_version_tab(j)+1
WHERE element_version_id = l_element_version_id_tab(j)
AND l_changed_flag_tab(j)='Y';
PA_RELATIONSHIP_PVT.Update_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 => v_cur_obj_rel_rec.object_relationship_id
,p_project_id_from => null
,p_structure_id_from => null
,p_structure_version_id_from => l_struc_version_from
,p_task_version_id_from => l_task_version_from
,p_project_id_to => null
,p_structure_id_to => null
,p_structure_version_id_to => null
,p_task_version_id_to => p_task_version_id
,p_relationship_type => 'S'
,p_relationship_subtype => l_relationship_subtype
,p_weighting_percentage => l_new_weighting
,p_record_version_number => v_cur_obj_rel_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
/*** End Update_task_version and Update_wbs_number code ***/
SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = ( Select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = p_task_version_id )
AND object_type = 'PA_STRUCTURES';
SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
FROM PA_TASKS pt,
pa_proj_elem_ver_structure ppa,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = p_task_version_id
AND ppev.parent_structure_version_id = ppa.element_version_id
AND ppev.project_id = ppa.project_id
AND ppev.proj_element_id = pt.task_id;
SELECT record_version_number, parent_task_id --get old parent id for bug 2947492 (indent )
,top_task_id --bug 2967204
INTO l_task_record_version_number, l_old_parent_task_id
,l_old_top_task_id --bug 2967204
FROM pa_tasks
WHERE task_id = l_task_id
AND project_id = l_project_id;
PA_PROJECT_STRUCTURE_PVT1.update_sch_dirty_flag(
p_structure_version_id => P_Structure_Version_ID
,p_dirty_flag => 'Y' --bug 3902282
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => p_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);
/*moved pa_fp_planning_transaction_pub.delete_planning_transactions into plsql block */
DECLARE
--p1 bug 3888432
l_assign_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE wbs_element_Version_id = c_task_ver_id
AND ta_display_flag = 'N';
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'ASSIGNMENT'
--- ,p_element_version_id_tbl => l_task_ver_ids2
--- ,p_maintain_reporting_lines => 'Y'
,p_resource_assignment_tbl => l_assign_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code IN( 'FINANCIAL' );
SELECT 'Y'
FROM dual
WHERE EXISTS(
SELECT 'xyz'
FROM pa_proj_elem_ver_structure
WHERE proj_element_id = c_structure_id
AND project_id = c_project_id
AND STATUS_CODE = 'STRUCTURE_PUBLISHED'
);
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_elem_ver_id
AND object_type_to = 'PA_TASKS'
AND relationship_type = 'S';
SELECT object_id_from1
, object_relationship_id
, record_version_number
, relationship_subtype
FROM pa_object_relationships
WHERE object_id_to1 = p_child_version_id
AND relationship_type = 'S';
SELECT a.object_id_from1
, a.weighting_percentage
FROM pa_object_relationships a
WHERE a.object_id_to1 = c_task_version_id
AND a.object_type_to = 'PA_TASKS'
AND a.relationship_type = 'S'
AND a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
SELECT '1'
FROM dual
WHERE EXISTS
(SELECT 'xyz'
FROM pa_object_relationships
WHERE object_id_from1 = c_task_version_id
AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
AND relationship_type = 'S'
);
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 ppe.TYPE_ID = ptt.task_type_id;
SELECT b.object_id_to1 object_id_to1
FROM pa_object_relationships a
, pa_object_relationships b
WHERE a.object_id_to1 = p_task_version_id
AND a.object_type_to = 'PA_TASKS'
AND a.object_id_from1 = b.object_id_from1
AND a.object_type_from = b.object_type_from
AND b.object_type_to = 'PA_TASKS'
AND b.object_id_to1 <> p_task_version_id
AND a.relationship_type = 'S'
AND b.relationship_type = 'S';
SELECT por.object_id_to1, ppev.display_sequence, por.record_version_number, por.object_relationship_id
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE object_id_from1 = c_ref_task_version_id
AND object_id_to1 = element_version_id
AND display_sequence > c_display_sequence
AND relationship_type = 'S'
order by display_sequence;
l_update_new_child_rels VARCHAR2(1) := 'N';
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_object_id_to1
and relationship_type = 'S';
select wbs_number
from pa_proj_element_versions
where element_version_id = c_elem_ver_id
and object_type ='PA_TASKS';
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id;
SELECT parent_task_id, top_task_id
FROM pa_tasks
WHERE project_id = c_project_id
AND task_id = c_task_id;
SELECT pt.task_id, pt.top_task_id, pt.parent_task_id
FROM pa_tasks pt, pa_proj_element_versions ppev
WHERE pt.wbs_level = c_wbs_level
AND parent_task_id = c_parent_task_id
AND pt.project_id = c_project_id
AND pt.task_id = ppev.proj_element_id
AND ppev.display_sequence > ( SELECT display_sequence FROM pa_proj_element_versions
WHERE project_id = c_project_id
AND object_type = 'PA_TASKS'
AND proj_element_id = c_task_id );
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
select object_id_to1
from pa_object_relationships
where relationshiP_type = 'S'
and object_type_to = 'PA_TASKS'
start with object_id_to1 = c_task_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S'
connect by prior object_id_from1 = object_id_to1
and prior object_type_from = object_type_to
and prior relationship_type = relationship_type
intersect
select a.object_id_to1
from pa_object_relationships a, pa_proj_element_versions b
where a.relationshiP_type = 'S'
and a.object_id_from1 = b.parent_structure_version_id
and b.element_version_id = c_task_ver_id
and a.object_type_from = 'PA_STRUCTURES';
SELECT 'Y'
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE object_id_from1 = c_ref_task_version_id
AND object_id_to1 = element_version_id
AND display_sequence > c_display_sequence
AND relationship_type = 'S'
AND financial_task_flag='Y';
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
IF l_update_parent_task_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_MOVE_TASK');
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO src_elem_ver_id, src_proj_element_id, src_wbs_number, src_wbs_level, src_seq_number, src_parent_str_ver_id, src_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = p_task_version_id
AND project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id;
SELECT element_version_id, proj_element_id, wbs_number, wbs_level, display_sequence, parent_structure_version_id, object_type
INTO ref_elem_ver_id, ref_proj_element_id, ref_wbs_number, ref_wbs_level, ref_seq_number, ref_parent_str_ver_id, ref_object_type
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = p_structure_version_id
AND project_id = p_project_id
AND (wbs_level = src_wbs_level-1)
AND object_type = 'PA_TASKS'
AND display_sequence =
(SELECT max (display_sequence)
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = p_structure_version_id
AND (wbs_level = src_wbs_level-1)
AND display_sequence < src_seq_number
AND object_type = 'PA_TASKS');
PA_TASK_PUB1.Update_Task_Version
( p_validate_only => FND_API.G_FALSE,
p_ref_task_version_id => l_ref_task_version_id,
p_peer_or_sub => 'PEER',
p_task_version_id => p_task_version_id,
p_record_version_number => p_record_version_number,
p_action => 'OUTDENT',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => l_parent_structure_version_id
,p_task_id => p_task_version_id
,p_display_seq => l_display_sequence
,p_action => 'OUTDENT'
,p_parent_task_id => NULL
,x_return_status => l_return_status );
/*** The following part should do the same task as done by PA_TASK_PUB1.Update_Task_Version and Update_wbs_numbers ***/
-- Logic Added for plsql table
-- Basically earlier this was done thru update_task_version and update_wbs_numbers
l_element_version_id_tab.delete;
l_proj_element_id_tab.delete;
l_object_type_tab.delete;
l_project_id_tab.delete;
l_parent_str_version_id_tab.delete;
l_display_sequence_tab.delete;
l_wbs_level_tab.delete;
l_old_wbs_level_tab.delete;
l_wbs_number_tab.delete;
l_record_version_tab.delete;
l_changed_flag_tab.delete;
SELECT distinct element_version_id, proj_element_id, object_type,
project_id, parent_structure_version_id,
display_sequence, wbs_level, wbs_number,
record_version_number, 'N' changed_flag
BULK COLLECT INTO l_element_version_id_tab,
l_proj_element_id_tab, l_object_type_tab,
l_project_id_tab, l_parent_str_version_id_tab,
l_display_sequence_tab, l_wbs_level_tab,
l_wbs_number_tab, l_record_version_tab,
l_changed_flag_tab
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'
AND(
element_version_id = p_task_version_id -- Source task itself
OR element_version_id IN -- All tasks below the source task
(select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
and object_type_to = 'PA_TASKS'
start with object_id_from1 = l_ref_parent_ver_id
connect by object_id_from1 = PRIOR object_id_to1
and relationship_type = prior relationship_type
and relationship_type = 'S')
)
ORDER BY display_sequence ;
p_msg_name => 'PA_PS_CHK_DELIV_UPDATE');
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET
wbs_level = l_wbs_level_tab(j) ,
wbs_number = l_wbs_number_tab(j) ,
last_update_date = sysdate ,
last_updated_by = l_user_id ,
last_update_login = l_login_id ,
record_version_number = l_record_version_tab(j)+1
WHERE element_version_id = l_element_version_id_tab(j)
AND l_changed_flag_tab(j)='Y';
l_update_new_child_rels := 'Y';
PA_RELATIONSHIP_PVT.Update_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 => v_cur_obj_rel_rec.object_relationship_id
,p_project_id_from => null
,p_structure_id_from => null
,p_structure_version_id_from => l_struc_version_from
,p_task_version_id_from => l_task_version_from
,p_project_id_to => null
,p_structure_id_to => null
,p_structure_version_id_to => null
,p_task_version_id_to => p_task_version_id
,p_relationship_type => 'S'
,p_relationship_subtype => l_relationship_subtype
,p_weighting_percentage => l_new_weighting
,p_record_version_number => v_cur_obj_rel_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF l_update_new_child_rels = 'Y' -- AND p_action IN( 'OUTDENT' )
THEN
FOR cur_new_child_rec in cur_new_child(ref_elem_ver_id, src_seq_number) LOOP
OPEN get_sub_tasks(p_task_version_id);
PA_RELATIONSHIP_PVT.Update_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 => cur_new_child_rec.object_relationship_id
,p_project_id_from => null
,p_structure_id_from => null
,p_structure_version_id_from => null
,p_task_version_id_from => p_task_version_id
,p_project_id_to => null
,p_structure_id_to => null
,p_structure_version_id_to => null
,p_task_version_id_to => cur_new_child_rec.object_id_to1
,p_relationship_type => 'S'
,p_relationship_subtype => 'TASK_TO_TASK'
,p_weighting_percentage => l_new_weighting
,p_record_version_number => cur_new_child_rec.record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*** End Update_task_version and Update_wbs_number code ***/
SELECT proj_element_id, project_id INTO l_structure_id, l_project_id
FROM pa_proj_element_versions
WHERE element_version_id = ( Select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = p_task_version_id )
AND object_type = 'PA_STRUCTURES';
SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
FROM PA_TASKS pt,
pa_proj_elem_ver_structure ppa,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.element_version_id = p_task_version_id
AND ppev.parent_structure_version_id = ppa.element_version_id
AND ppev.project_id = ppa.project_id
AND ppev.proj_element_id = pt.task_id;
SELECT record_version_number, parent_task_id, wbs_level --get old parent id for bug 2947492 (outdent )
,top_task_id
INTO l_task_record_version_number, l_old_parent_task_id, l_old_wbs_level
,l_old_top_task_id
FROM pa_tasks
WHERE task_id = l_task_id
AND project_id = l_project_id;
PA_PROJECT_STRUCTURE_PVT1.update_sch_dirty_flag(
p_structure_version_id => P_Structure_Version_ID
,p_dirty_flag => 'Y' --bug 3902282
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'SELF_SERVICE'
,p_project_id => p_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);
/*moved pa_fp_planning_transaction_pub.delete_planning_transactions into plsql block */
DECLARE
--p1 bug 3888432
l_assign_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE wbs_element_Version_id = c_task_ver_id
AND ta_display_flag = 'N';
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'ASSIGNMENT'
-- ,p_element_version_id_tbl => l_task_ver_ids2
-- ,p_maintain_reporting_lines => 'Y'
,p_resource_assignment_tbl => l_assign_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
This API is called from Multiple Tasks Delete Page.
The API expects the task version id in this fashion
1. If both parent and child below it is selected for
deletetion the only parent task id should be pass
-ed
2. If top task is selected and child below it is sel
--ected, API expects only top task id as input
3. If only child is selected then API expexts only
child task id.
Note :
Since the self service page expects the error message
to be displayed in following order :
Task name (task number) : Proper Error Message ..
i.e Task name/number and corresponding error
message to that task version. in one line
Both Task Name /Number and error message set as
token for message PA_PS_TASK_NAME_NUM_ERR
*/
PROCEDURE DELETE_TASK_VERSION_IN_BULK
(p_task_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_structure_version_id IN NUMBER
,p_structure_type IN VARCHAR2 :='WORKPLAN' -- 3305199
,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_debug_mode VARCHAR2(1) ;
SELECT project_id
FROM pa_proj_elem_ver_structure
WHERE element_version_id = c_structure_version_id;
SELECT 'Y'
FROM pa_resource_assignments
WHERE wbs_element_version_id = c_element_version_id
AND total_plan_quantity > 0
AND rownum = 1;
SELECT object_id_to1 task_ver_id
FROM pa_object_relationships pors
START WITH object_id_from1 = c_task_version_id
AND relationship_type = 'S'
AND object_type_from = 'PA_TASKS'
AND object_type_to = 'PA_TASKS'
CONNECT BY relationship_type = PRIOR relationship_type
AND PRIOR object_type_to = object_type_from
AND PRIOR object_id_to1 = object_id_from1
UNION
SELECT element_version_id task_ver_id
FROM pa_proj_element_versions
WHERE element_version_id = c_task_version_id;
PA_TASK_PUB1.Delete_Task_Version(
p_task_version_id => p_task_version_id_tbl(i)
,p_record_version_number => p_record_version_number_tbl(i)
,p_structure_type => p_structure_type
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_structure_version_id => p_structure_version_id
,p_called_from_api => 'BULK_DELETE' -- Bug 9535723
);
SELECT e.element_number
,e.name
INTO l_element_number
,l_element_name
FROM pa_proj_elements e
,pa_proj_element_versions v
WHERE v.element_version_id = p_task_version_id_tbl(i)
AND e.proj_element_id = v.proj_element_id ;
pa_debug.debug('DELETE_TASK_VERSION_IN_BULK: l_planned_effort_exists - ' || l_planned_effort_exists);
pa_debug.debug('DELETE_TASK_VERSION_IN_BULK: Calling PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP');
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'ASGMT_PLAN_CHANGE'
,p_project_id => l_project_id
,p_structure_version_id => p_structure_version_id
,p_pub_struc_ver_id => p_structure_version_id
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
pa_debug.debug('Error after PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP');
,p_procedure_name => 'DELETE_TASK_VERSION_IN_BULK' );
pa_debug.write('DELETE_TASK_VERSION_IN_BULK' || G_PKG_NAME,SQLERRM,4);
pa_debug.write('DELETE_TASK_VERSION_IN_BULK' || G_PKG_NAME,pa_debug.G_Err_Stack,4);
END DELETE_TASK_VERSION_IN_BULK ;
PROCEDURE Update_Task_Association (
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 :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=NULL,
p_associated_project_id IN NUMBER := NULL,
p_associated_task_id IN NUMBER := NULL,
p_associated_project_name IN VARCHAR2 :=NULL,
p_associated_task_name IN VARCHAR2 :=NULL,
p_task_id IN NUMBER ,
p_relationship_type IN VARCHAR2 :='A',
p_relationship_id IN NUMBER := NULL,
p_record_version_number IN NUMBER := NULL,
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
x_relationship_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK_ASSOCIATION START');
savepoint update_task_association;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => p_task_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 => l_type_to
,p_object_id_to1 => l_id_to
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => p_relationship_type
,p_relationship_subtype => NULL
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,p_comments => NULL
,p_status_code => NULL
,x_object_relationship_id => x_relationship_id
,x_return_status => x_return_status
);
UPDATE PA_OBJECT_RELATIONSHIPS
SET object_id_to1 = l_id_to
,object_type_to = l_type_to
WHERE object_relationship_id = p_relationship_id;
Delete_Association(p_relationship_id,p_record_version_number, x_return_status);
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK_ASSOCIATION END');
ROLLBACK to update_task_association;
ROLLBACK to update_task_association;
p_procedure_name => 'update_task_association',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Task_Association;
PROCEDURE Delete_Task_Associations(
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 :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=NULL,
p_relationship_type IN VARCHAR2 :='A',
p_relationship_id IN NUMBER := NULL,
p_task_id IN NUMBER := NULL,
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 task_associations( p_task_id NUMBER )
IS
SELECT object_relationship_id
FROM PA_OBJECT_RELATIONSHIPS
WHERE relationship_type = p_relationship_type
AND ( (object_type_from = 'PA_TASKS' AND object_id_from1 = p_task_id)
OR (object_type_to = 'PA_TASKS' AND object_id_to1 = p_task_id));
pa_debug.debug('PA_TASK_PUB1.DELETE_TASK_ASSOCIATION START');
savepoint delete_task_association;
Delete_Association(p_relationship_id,null,x_return_status);
Delete_Association(task_associations_rec.object_relationship_id,null, x_return_status);
pa_debug.debug('PA_TASK_PUB1.DELETE_TASK_ASSOCIATIONS END');
ROLLBACK to delete_task_association;
ROLLBACK to delete_task_association;
p_procedure_name => 'delete_all_task_associations',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Task_Associations;
PROCEDURE Delete_Association(
p_relationship_id IN NUMBER,
p_record_version_number IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
BEGIN
IF p_relationship_id is not null THEN
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
p_object_relationship_id => p_relationship_id
,p_object_type_from => NULL
,p_object_id_from1 => NULL
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => NULL
,p_object_id_to1 => NULL
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_record_version_number =>NULL
,p_pm_product_code => NULL
,x_return_status => x_return_status
);
p_procedure_name => 'delete_association',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Association;
select '1'
from pa_object_relationships
WHERE relationship_type = p_relationship_type
AND ( (object_type_from = 'PA_TASKS' AND object_id_from1 = p_task_id
AND object_type_to in ( 'PA_STRUCTURES', 'PA_TASKS')) --bug 4091647
OR (object_type_to = 'PA_TASKS' AND object_id_to1 = p_task_id
AND object_type_from = 'PA_TASKS')); --bug 4091647
select '1'
from pa_object_relationships
WHERE relationship_type = p_relationship_type
AND object_type_from = 'PA_TASKS'
AND object_type_to = 'PA_PROJECTS' AND object_id_to1 = p_project_id;
PROCEDURE Delete_Proj_To_Task_Assoc(
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 :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=NULL,
p_relationship_type IN VARCHAR2 :='A',
p_relationship_id IN NUMBER := NULL,
p_project_id IN NUMBER := NULL,
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 task_associations( p_task_id NUMBER )
IS
SELECT object_relationship_id
FROM PA_OBJECT_RELATIONSHIPS
WHERE relationship_type = p_relationship_type
AND object_type_from = 'PA_TASKS'
AND object_type_to = 'PA_PROJECTS' AND object_id_to1 = p_project_id;
pa_debug.debug('PA_TASK_PUB1.Delete_Proj_To_Task_Assoc START');
savepoint delete_prj_to_task_assoc;
Delete_Association(p_relationship_id,null,x_return_status);
Delete_Association(task_associations_rec.object_relationship_id,null, x_return_status);
pa_debug.debug('PA_TASK_PUB1.Delete_Proj_To_Task_Assoc END');
ROLLBACK to delete_prj_to_task_assoc;
ROLLBACK to delete_prj_to_task_assoc;
p_procedure_name => 'delete_proj_to_task_assoc',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Proj_To_Task_Assoc;
Select source.proj_element_id old_task_id
,destination.proj_element_id new_task_id
from pa_proj_elements source --Bug#3693794
,pa_proj_elements destination --Bug#3693794
where source.project_id = p_project_id_from
and source.element_number = destination.element_number
and destination.project_id = p_project_id_to;
Select object_type_to, object_id_to1
from pa_object_relationships
where object_type_from = 'PA_TASKS'
and object_id_from1 = task_id
and relationship_type = p_relationship_type;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => task_list_rec.new_task_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 => l_object_type_to
,p_object_id_to1 => l_object_id_to1
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => p_relationship_type
,p_relationship_subtype => NULL
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,p_comments => NULL
,p_status_code => NULL
,x_object_relationship_id => x_relationship_id
,x_return_status => x_return_status);
PROCEDURE update_task_det_sch_info(
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_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_task_ver_id IN NUMBER,
p_project_id IN NUMBER,
p_planned_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_ETC_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_structure_version_id IN NUMBER,
p_object_type IN VARCHAR2 := 'PA_TASKS',
p_etc_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_actual_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_percent_complete IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_res_assign_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
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 scheduled_start_date, scheduled_finish_date
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = p_task_ver_id
AND project_id = p_project_id
;
select *
from pa_progress_rollup ppr
where ppr.project_id = p_project_id
and ppr.object_id = p_object_id
and ppr.object_version_id = p_object_version_id
and ppr.structure_version_id = p_structure_version_id
and ppr.object_type in ('PA_TASKS','PA_ASSIGNMENTS','PA_STRUCTURES') -- 4498610 : Added PA_STRUCTURES also
and ppr.current_flag = 'Y'
and ppr.as_of_date = (select max(as_of_date) from pa_progress_rollup ppr2
where ppr2.project_id = p_project_id
and ppr2.object_id = p_object_id
and ppr2.object_version_id = p_object_version_id
and ppr2.structure_version_id = p_structure_version_id
and ppr2.object_type in ('PA_TASKS','PA_ASSIGNMENTS','PA_STRUCTURES'));
select *
from pa_progress_rollup ppr
where ppr.project_id = p_project_id
and ppr.object_id = p_object_id
and ppr.object_version_id = p_object_version_id
and ppr.structure_version_id IS NULL
and ppr.current_flag = p_current_flag
and ppr.object_type in ('PA_TASKS','PA_ASSIGNMENTS','PA_STRUCTURES'); -- 4498610 : Added PA_STRUCTURES also
/*and ppr.as_of_date = (select max(as_of_date) from pa_progress_rollup ppr2
where ppr2.project_id = p_project_id
and ppr2.object_id = p_object_id
and ppr2.object_version_id = p_object_version_id
and ppr2.structure_version_id IS NULL
and ppr2.current_flag = p_current_flag --bug 3708948
and ppr2.object_type in ('PA_TASKS','PA_ASSIGNMENTS'));*/
select (labor_effort+equipment_effort)
from pji_xbs_plans_v
where project_id = p_project_id
and structure_version_id = p_structure_version_id
and proj_element_id = p_proj_element_id
and structure_type = 'WORKPLAN';
select planned_quantity
from pa_task_assignments_v
where project_id = p_project_id
and structure_version_id = p_structure_version_id
and resource_assignment_id = p_res_assign_id
and task_id = p_task_id;
select (nvl(estimated_remaining_effort,0)+nvl(eqpmt_etc_effort,0)
+nvl(subprj_ppl_etc_effort,0)+nvl(subprj_eqpmt_etc_effort,0))
from pa_progress_rollup
where project_id = p_project_id
and object_id = p_object_id
and structure_version_id = p_structure_version_id
and structure_type = 'WORKPLAN';
select (nvl(estimated_remaining_effort,0)+nvl(eqpmt_etc_effort,0)
+nvl(subprj_ppl_etc_effort,0)+nvl(subprj_eqpmt_etc_effort,0))
from pa_progress_rollup
where project_id = p_project_id
and object_id = p_object_id
and structure_version_id is null
and current_flag = 'Y'
and structure_type = 'WORKPLAN';
SELECT task_weight_basis_code
FROM pa_proj_progress_attr
WHERE project_id = p_project_id
AND structure_type = 'WORKPLAN';
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
and parent_structure_version_id = p_structure_version_id
and element_version_id = p_task_ver_id
and object_type = decode(p_object_type, 'PA_ASSIGNMENTS','PA_TASKS', p_object_type); -- Bug 3856161 : Added Decode
select max(as_of_date)
from pa_progress_rollup
where project_id = p_project_id
-- and object_version_id = p_task_ver_id Bug 3856161 : It shd always do rollup if record exists
-- and object_type = p_object_type
and structure_type = 'WORKPLAN'
and structure_version_id = p_structure_version_id;
select ptt.initial_progress_status_code
from pa_task_types ptt, pa_proj_elements ppe, pa_proj_element_versions ppev
where ppev.project_id = ppe.project_id
and ppev.proj_element_id = ppe.proj_element_id
and ppe.type_id = ptt.task_type_id
and ppe.project_id = p_project_id
and ppev.element_version_id = p_task_ver_id
and ppev.parent_structure_version_id = p_structure_version_id;
SELECT decode( ppe.base_percent_comp_deriv_code, null, ptt.base_percent_comp_deriv_code, '^', ptt.base_percent_comp_deriv_code, ppe.base_percent_comp_deriv_code )
from pa_proj_elements ppe
,pa_task_types ptt
where ppe.project_id = p_project_id
and ppe.proj_element_id = c_proj_element_id
and ppe.type_id = ptt.task_type_id;
select nvl(BRDN_COST, 0), nvl(ACT_LABOR_BRDN_COST, 0)
from pji_xbs_plans_v
where project_id = p_project_id
and proj_element_id = p_object_id
and structure_version_id = p_structure_version_id;
select ppr.EFF_ROLLUP_PERCENT_COMP,ppr.completed_percentage, actual_start_date, actual_finish_date -- 4498610 : Added completed_percentage, actual_start_date, actual_finish_date
from pa_progress_rollup ppr
where ppr.project_id = p_project_id
and ppr.object_id = p_object_id
and ppr.object_version_id = p_object_version_id
and ppr.structure_version_id = p_structure_version_id
and ppr.object_type IN ('PA_TASKS','PA_STRUCTURES') -- -- Bug 4498610 : Added PA_STRUCTURES
and ppr.current_flag = 'Y';
select ppr.EFF_ROLLUP_PERCENT_COMP,ppr.completed_percentage, actual_start_date, actual_finish_date -- 4498610 : Added completed_percentage, actual_start_date, actual_finish_date
from pa_progress_rollup ppr
where ppr.project_id = p_project_id
and ppr.object_id = p_object_id
and ppr.object_version_id = p_object_version_id
and ppr.structure_version_id IS NULL
and ppr.current_flag = 'Y'
and ppr.object_type IN ('PA_TASKS','PA_STRUCTURES') -- Bug 4498610 : Added PA_STRUCTURES
;
select 'Y'
from pa_progress_rollup ppr
where ppr.project_id = c_project_id
and ppr.object_id = c_object_id
and ((c_version_enabled_flag = 'N' AND ppr.structure_version_id IS NULL) OR (c_version_enabled_flag = 'Y' AND ppr.structure_version_id = p_structure_version_id))
and ppr.object_type = 'PA_TASKS'
and ppr.structure_type = 'WORKPLAN';
select nvl(ACT_LABOR_HRS, 0)+nvl(ACT_EQUIP_HRS,0)
from pji_xbs_plans_v
where project_id = p_project_id
and proj_element_id = p_object_id
and structure_version_id = p_structure_version_id ;
pa_debug.debug('PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO begin');
savepoint UPDATE_task_det_sch;
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO Start : Passed Parameters :', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_calling_module='||p_calling_module, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_task_ver_id='||p_task_ver_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_planned_effort='||p_planned_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_ETC_effort='||p_ETC_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_structure_version_id='||p_structure_version_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_object_type='||p_object_type, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_etc_cost='||p_etc_cost, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_actual_effort='||p_actual_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_percent_complete='||p_percent_complete, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_res_assign_id='||p_res_assign_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_version_enabled='||l_version_enabled, x_Log_Level=> 3);
select proj_element_id
into l_task_id
from pa_proj_element_versions
where element_version_id = p_task_ver_id
and parent_structure_version_id = p_structure_version_id;
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_task_id='||l_task_id, x_Log_Level=> 3);
SELECT project_currency_code INTO l_prj_currency_code FROM pa_projects_all WHERE project_id = p_project_id;
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_object_id='||l_object_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_prev_planned_effort='||l_prev_planned_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_prev_etc_effort='||l_prev_etc_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_actual_effort='||l_actual_effort, x_Log_Level=> 3);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG(
p_project_id => p_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_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_planned_effort='||l_planned_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_etc_effort='||l_etc_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_lowest_level_task='||l_lowest_level_task, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_assignment_exists='||l_assignment_exists, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_SCHEDULED_START_DATE='||l_SCHEDULED_START_DATE, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_SCHEDULED_END_DATE='||l_SCHEDULED_END_DATE, x_Log_Level=> 3);
/*moved pa_fp_planning_transaction_pub.update_planning_transactions into plsql block */
BEGIN
IF NVL(l_prev_planned_effort,0) <> NVL(l_planned_effort,0)
THEN
IF l_debug_mode = 'Y' THEN
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'Calling update_planning_transactions', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_task_ver_ids2='||p_task_ver_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_planned_effort2='||l_planned_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_start_dates='||l_SCHEDULED_START_DATE, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_end_dates='||l_SCHEDULED_END_DATE, x_Log_Level=> 3);
pa_fp_planning_transaction_pub.update_planning_transactions
(
p_context => 'WORKPLAN'
,p_struct_elem_version_id => p_structure_version_id
,p_task_elem_version_id_tbl => l_task_ver_ids2
,p_planned_people_effort_tbl => l_planned_effort2
,p_start_date_tbl => l_start_dates
,p_end_date_tbl => l_end_dates
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_procedure_name => 'update_task_det_sch_info',
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.update_planning_transactions:'||SQLERRM,1,240));
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_next_prog_cycle_date='||l_next_prog_cycle_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_BASE_PERCENT_COMP_DERIV_CODE='||l_BASE_PERCENT_COMP_DERIV_CODE, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_ppl_act_cost='||l_ppl_act_cost, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_planned_cost='||l_planned_cost, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_percent_complete='||l_percent_complete, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_override_pc='||l_override_pc, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_eff_rollup_pc='||l_eff_rollup_pc, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_curr_override_pc='||l_curr_override_pc, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_earned_value='||l_earned_value, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_actual_START_DATE='||l_actual_START_DATE, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_actual_finish_DATE='||l_actual_finish_DATE, x_Log_Level=> 3);
update pa_progress_rollup set
--as_of_date = l_next_prog_cycle_date,
estimated_remaining_effort = null --l_etc_effort
,completed_percentage = l_percent_complete
,eff_rollup_percent_comp = l_eff_rollup_pc
,earned_value = l_earned_value
,actual_start_date = l_actual_start_date
,actual_finish_date = l_actual_finish_date
,last_update_date = sysdate
,last_updated_by = l_user_id
--,creation_date = sysdate
--,created_by = l_user_id
,last_update_login = l_login_id
where progress_rollup_id = cur_progress_rec.progress_rollup_id;
update pa_percent_completes set completed_percentage = l_percent_complete
where project_id = p_project_id
and object_id = l_task_id
and date_computed =l_rollup_as_of_date
and current_flag = 'Y'
and published_flag = 'Y';
PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
X_PROGRESS_ROLLUP_ID => l_progress_rollup_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_ID => l_object_id
,X_OBJECT_TYPE => p_object_type
,X_AS_OF_DATE => l_next_prog_cycle_date
,X_OBJECT_VERSION_ID => p_task_ver_id
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => l_user_id
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => l_user_id
,X_PROGRESS_STATUS_CODE => l_init_prog_status_code -- Bug # 3879658 -- 'PROGRESS_STAT_ON_TRACK' --maansari5/11
,X_LAST_UPDATE_LOGIN => l_login_id
,X_INCREMENTAL_WORK_QTY => null
,X_CUMULATIVE_WORK_QTY => null
,X_BASE_PERCENT_COMPLETE => null
,X_EFF_ROLLUP_PERCENT_COMP => l_eff_rollup_pc
,X_COMPLETED_PERCENTAGE => l_percent_complete
,X_ESTIMATED_START_DATE => null
,X_ESTIMATED_FINISH_DATE => null
,X_ACTUAL_START_DATE => l_actual_start_DATE
,X_ACTUAL_FINISH_DATE => l_actual_finish_DATE
,X_EST_REMAINING_EFFORT => null --l_etc_effort
,X_BASE_PERCENT_COMP_DERIV_CODE => null
,X_BASE_PROGRESS_STATUS_CODE => null
,X_EFF_ROLLUP_PROG_STAT_CODE => null
,x_percent_complete_id => null
,X_STRUCTURE_TYPE => 'WORKPLAN'
,X_PROJ_ELEMENT_ID => l_task_id
,X_STRUCTURE_VERSION_ID => null
,X_PPL_ACT_EFFORT_TO_DATE => null
,X_EQPMT_ACT_EFFORT_TO_DATE => null
,X_EQPMT_ETC_EFFORT => null
,X_OTH_ACT_COST_TO_DATE_TC => null
,X_OTH_ACT_COST_TO_DATE_FC => null
,X_OTH_ACT_COST_TO_DATE_PC => null
,X_OTH_ETC_COST_TC => null
,X_OTH_ETC_COST_FC => null
,X_OTH_ETC_COST_PC => null
,X_PPL_ACT_COST_TO_DATE_TC => null
,X_PPL_ACT_COST_TO_DATE_FC => null
,X_PPL_ACT_COST_TO_DATE_PC => null
,X_PPL_ETC_COST_TC => null
,X_PPL_ETC_COST_FC => null
,X_PPL_ETC_COST_PC => null
,X_EQPMT_ACT_COST_TO_DATE_TC => null
,X_EQPMT_ACT_COST_TO_DATE_FC => null
,X_EQPMT_ACT_COST_TO_DATE_PC => null
,X_EQPMT_ETC_COST_TC => null
,X_EQPMT_ETC_COST_FC => null
,X_EQPMT_ETC_COST_PC => null
,X_EARNED_VALUE => l_earned_value
,X_TASK_WT_BASIS_CODE => null
,X_SUBPRJ_PPL_ACT_EFFORT => null
,X_SUBPRJ_EQPMT_ACT_EFFORT => null
,X_SUBPRJ_PPL_ETC_EFFORT => null
,X_SUBPRJ_EQPMT_ETC_EFFORT => null
,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
,X_SUBPRJ_PPL_ACT_COST_TC => null
,X_SUBPRJ_PPL_ACT_COST_FC => null
,X_SUBPRJ_PPL_ACT_COST_PC => null
,X_SUBPRJ_EQPMT_ACT_COST_TC => null
,X_SUBPRJ_EQPMT_ACT_COST_FC => null
,X_SUBPRJ_EQPMT_ACT_COST_PC => null
,X_SUBPRJ_OTH_ETC_COST_TC => null
,X_SUBPRJ_OTH_ETC_COST_FC => null
,X_SUBPRJ_OTH_ETC_COST_PC => null
,X_SUBPRJ_PPL_ETC_COST_TC => null
,X_SUBPRJ_PPL_ETC_COST_FC => null
,X_SUBPRJ_PPL_ETC_COST_PC => null
,X_SUBPRJ_EQPMT_ETC_COST_TC => null
,X_SUBPRJ_EQPMT_ETC_COST_FC => null
,X_SUBPRJ_EQPMT_ETC_COST_PC => null
,X_SUBPRJ_EARNED_VALUE => null
,X_CURRENT_FLAG => 'Y'
,X_PROJFUNC_COST_RATE_TYPE => null
,X_PROJFUNC_COST_EXCHANGE_RATE => null
-- ,X_PROJFUNC_COST_RATE_DATE_TYPE => null
,X_PROJFUNC_COST_RATE_DATE => null
,X_PROJ_COST_RATE_TYPE => null
,X_PROJ_COST_EXCHANGE_RATE => null
-- ,X_PROJ_COST_RATE_DATE_TYPE => null
,X_PROJ_COST_RATE_DATE => null
,X_TXN_CURRENCY_CODE => null
,X_PROG_PA_PERIOD_NAME => PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_next_prog_cycle_date) --maansari5/11
,X_PROG_GL_PERIOD_NAME => PA_PROGRESS_UTILS.Prog_Get_gl_Period_Name(l_next_prog_cycle_date) --maansari5/11
,X_OTH_QUANTITY_to_date => null --maansari5/9
,X_OTH_ETC_QUANTITY => null
--bug 3621404
,X_OTH_ACT_RAWCOST_TO_DATE_TC => null
,X_OTH_ACT_RAWCOST_TO_DATE_FC => null
,X_OTH_ACT_RAWCOST_TO_DATE_PC => null
,X_OTH_ETC_RAWCOST_TC => null
,X_OTH_ETC_RAWCOST_FC => null
,X_OTH_ETC_RAWCOST_PC => null
,X_PPL_ACT_RAWCOST_TO_DATE_TC => null
,X_PPL_ACT_RAWCOST_TO_DATE_FC => null
,X_PPL_ACT_RAWCOST_TO_DATE_PC => null
,X_PPL_ETC_RAWCOST_TC => null
,X_PPL_ETC_RAWCOST_FC => null
,X_PPL_ETC_RAWCOST_PC => null
,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => null
,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => null
,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => null
,X_EQPMT_ETC_RAWCOST_TC => null
,X_EQPMT_ETC_RAWCOST_FC => null
,X_EQPMT_ETC_RAWCOST_PC => null
,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
);
update pa_progress_rollup set
--as_of_date = l_next_prog_cycle_date,
estimated_remaining_effort = null --l_etc_effort
,completed_percentage = l_percent_complete
,eff_rollup_percent_comp = l_eff_rollup_pc
,earned_value = l_earned_value
,actual_start_date = l_actual_start_date
,actual_finish_date = l_actual_finish_date
,last_update_date = sysdate
,last_updated_by = l_user_id
--,creation_date = sysdate
--,created_by = l_user_id
,last_update_login = l_login_id
where progress_rollup_id = cur_progress_rec.progress_rollup_id;
PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
X_PROGRESS_ROLLUP_ID => l_progress_rollup_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_ID => l_object_id
,X_OBJECT_TYPE => p_object_type
,X_AS_OF_DATE => l_as_of_date
,X_OBJECT_VERSION_ID => p_task_ver_id
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => l_user_id
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => l_user_id
,X_PROGRESS_STATUS_CODE => l_init_prog_status_code -- Bug # 3879658 -- 'PROGRESS_STAT_ON_TRACK' --maansari5/11
,X_LAST_UPDATE_LOGIN => l_login_id
,X_INCREMENTAL_WORK_QTY => null
,X_CUMULATIVE_WORK_QTY => null
,X_BASE_PERCENT_COMPLETE => null
,X_EFF_ROLLUP_PERCENT_COMP => l_eff_rollup_pc
,X_COMPLETED_PERCENTAGE => l_percent_complete
,X_ESTIMATED_START_DATE => null
,X_ESTIMATED_FINISH_DATE => null
,X_ACTUAL_START_DATE => l_actual_start_DATE
,X_ACTUAL_FINISH_DATE => l_actual_finish_DATE
,X_EST_REMAINING_EFFORT => null --l_etc_effort
,X_BASE_PERCENT_COMP_DERIV_CODE => null
,X_BASE_PROGRESS_STATUS_CODE => null
,X_EFF_ROLLUP_PROG_STAT_CODE => null
,x_percent_complete_id => null
,X_STRUCTURE_TYPE => 'WORKPLAN'
,X_PROJ_ELEMENT_ID => l_task_id
,X_STRUCTURE_VERSION_ID => p_structure_version_id
,X_PPL_ACT_EFFORT_TO_DATE => null
,X_EQPMT_ACT_EFFORT_TO_DATE => null
,X_EQPMT_ETC_EFFORT => null
,X_OTH_ACT_COST_TO_DATE_TC => null
,X_OTH_ACT_COST_TO_DATE_FC => null
,X_OTH_ACT_COST_TO_DATE_PC => null
,X_OTH_ETC_COST_TC => null
,X_OTH_ETC_COST_FC => null
,X_OTH_ETC_COST_PC => null
,X_PPL_ACT_COST_TO_DATE_TC => null
,X_PPL_ACT_COST_TO_DATE_FC => null
,X_PPL_ACT_COST_TO_DATE_PC => null
,X_PPL_ETC_COST_TC => null
,X_PPL_ETC_COST_FC => null
,X_PPL_ETC_COST_PC => null
,X_EQPMT_ACT_COST_TO_DATE_TC => null
,X_EQPMT_ACT_COST_TO_DATE_FC => null
,X_EQPMT_ACT_COST_TO_DATE_PC => null
,X_EQPMT_ETC_COST_TC => null
,X_EQPMT_ETC_COST_FC => null
,X_EQPMT_ETC_COST_PC => null
,X_EARNED_VALUE => l_earned_value
,X_TASK_WT_BASIS_CODE => null
,X_SUBPRJ_PPL_ACT_EFFORT => null
,X_SUBPRJ_EQPMT_ACT_EFFORT => null
,X_SUBPRJ_PPL_ETC_EFFORT => null
,X_SUBPRJ_EQPMT_ETC_EFFORT => null
,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
,X_SUBPRJ_PPL_ACT_COST_TC => null
,X_SUBPRJ_PPL_ACT_COST_FC => null
,X_SUBPRJ_PPL_ACT_COST_PC => null
,X_SUBPRJ_EQPMT_ACT_COST_TC => null
,X_SUBPRJ_EQPMT_ACT_COST_FC => null
,X_SUBPRJ_EQPMT_ACT_COST_PC => null
,X_SUBPRJ_OTH_ETC_COST_TC => null
,X_SUBPRJ_OTH_ETC_COST_FC => null
,X_SUBPRJ_OTH_ETC_COST_PC => null
,X_SUBPRJ_PPL_ETC_COST_TC => null
,X_SUBPRJ_PPL_ETC_COST_FC => null
,X_SUBPRJ_PPL_ETC_COST_PC => null
,X_SUBPRJ_EQPMT_ETC_COST_TC => null
,X_SUBPRJ_EQPMT_ETC_COST_FC => null
,X_SUBPRJ_EQPMT_ETC_COST_PC => null
,X_SUBPRJ_EARNED_VALUE => null
,X_CURRENT_FLAG => 'Y' -- /* Bug # 3755089. */
,X_PROJFUNC_COST_RATE_TYPE => null
,X_PROJFUNC_COST_EXCHANGE_RATE => null
-- ,X_PROJFUNC_COST_RATE_DATE_TYPE => null
,X_PROJFUNC_COST_RATE_DATE => null
,X_PROJ_COST_RATE_TYPE => null
,X_PROJ_COST_EXCHANGE_RATE => null
-- ,X_PROJ_COST_RATE_DATE_TYPE => null
,X_PROJ_COST_RATE_DATE => null
,X_TXN_CURRENCY_CODE => null
,X_PROG_PA_PERIOD_NAME => PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_as_of_date) --maansari5/11
,X_PROG_GL_PERIOD_NAME => PA_PROGRESS_UTILS.Prog_Get_gl_Period_Name(l_as_of_date) --maansari5/11
,X_OTH_QUANTITY_to_date => null --maansari5/9
,X_OTH_ETC_QUANTITY => null
--bug 3621404
,X_OTH_ACT_RAWCOST_TO_DATE_TC => null
,X_OTH_ACT_RAWCOST_TO_DATE_FC => null
,X_OTH_ACT_RAWCOST_TO_DATE_PC => null
,X_OTH_ETC_RAWCOST_TC => null
,X_OTH_ETC_RAWCOST_FC => null
,X_OTH_ETC_RAWCOST_PC => null
,X_PPL_ACT_RAWCOST_TO_DATE_TC => null
,X_PPL_ACT_RAWCOST_TO_DATE_FC => null
,X_PPL_ACT_RAWCOST_TO_DATE_PC => null
,X_PPL_ETC_RAWCOST_TC => null
,X_PPL_ETC_RAWCOST_FC => null
,X_PPL_ETC_RAWCOST_PC => null
,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => null
,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => null
,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => null
,X_EQPMT_ETC_RAWCOST_TC => null
,X_EQPMT_ETC_RAWCOST_FC => null
,X_EQPMT_ETC_RAWCOST_PC => null
,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'Calling Rollup For Tasks', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_task_id='||l_task_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_task_ver_id='||p_task_ver_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_rollup_method='||l_rollup_method, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_rollup_as_of_date='||l_rollup_as_of_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_rollup_as_of_date='||l_rollup_as_of_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'Entered For Assignments', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_task_id '||l_task_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_progress_exists '||l_progress_exists, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'Calling Rollup For Assignments', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_task_id='||l_task_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'p_task_ver_id='||p_task_ver_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_rollup_method='||l_rollup_method, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_rollup_as_of_date='||l_rollup_as_of_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_TASK_PUB1.UPDATE_TASK_DET_SCH_INFO', x_Msg => 'l_rollup_as_of_date='||l_rollup_as_of_date, x_Log_Level=> 3);
ROLLBACK to UPDATE_task_det_sch;
ROLLBACK to UPDATE_task_det_sch;
p_procedure_name => 'update_task_det_sch_info',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_task_det_sch_info;
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
IS select 'Y'
from pa_projects_all
where project_id = c_project_id
and template_flag = 'Y';
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_dest_task_version_id;
SELECT proj_element_id
FROM pa_proj_elem_ver_structure
WHERE element_version_id = x_structure_version_id
AND project_id = x_project_id;
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
l_delete_project_allowed VARCHAR2(1);
l_update_proj_num_allowed VARCHAR2(1);
l_update_proj_name_allowed VARCHAR2(1);
l_update_proj_desc_allowed VARCHAR2(1);
l_update_proj_dates_allowed VARCHAR2(1);
l_update_proj_status_allowed VARCHAR2(1);
l_update_proj_manager_allowed VARCHAR2(1);
l_update_proj_org_allowed VARCHAR2(1);
l_delete_task_allowed VARCHAR2(1);
l_update_task_num_allowed VARCHAR2(1);
l_update_task_name_allowed VARCHAR2(1);
l_update_task_dates_allowed VARCHAR2(1);
l_update_task_desc_allowed VARCHAR2(1);
l_update_parent_task_allowed VARCHAR2(1);
l_update_task_org_allowed VARCHAR2(1);
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id;
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = c_project_id;
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = c_structure_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = 'FINANCIAL' ;
select 'Y'
from pa_proj_elem_ver_structure
where proj_element_id = c_structure_id
AND project_id = c_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
P_DELETE_PROJECT_ALLOWED => l_delete_project_allowed,
P_UPDATE_PROJ_NUM_ALLOWED => l_update_proj_num_allowed,
P_UPDATE_PROJ_NAME_ALLOWED => l_update_proj_name_allowed,
P_UPDATE_PROJ_DESC_ALLOWED => l_update_proj_desc_allowed,
P_UPDATE_PROJ_DATES_ALLOWED => l_update_proj_dates_allowed,
P_UPDATE_PROJ_STATUS_ALLOWED => l_update_proj_status_allowed,
P_UPDATE_PROJ_MANAGER_ALLOWED => l_update_proj_manager_allowed,
P_UPDATE_PROJ_ORG_ALLOWED => l_update_proj_org_allowed,
P_ADD_TASK_ALLOWED => l_add_task_allowed,
P_DELETE_TASK_ALLOWED => l_delete_task_allowed,
P_UPDATE_TASK_NUM_ALLOWED => l_update_task_num_allowed,
P_UPDATE_TASK_NAME_ALLOWED => l_update_task_name_allowed,
P_UPDATE_TASK_DATES_ALLOWED => l_update_task_dates_allowed,
P_UPDATE_TASK_DESC_ALLOWED => l_update_task_desc_allowed,
P_UPDATE_PARENT_TASK_ALLOWED => l_update_parent_task_allowed,
P_UPDATE_TASK_ORG_ALLOWED => l_update_task_org_allowed,
P_ERROR_CODE => l_err_code,
P_ERROR_STACK => l_err_stack,
P_ERROR_STAGE => l_err_stage );
IF l_update_parent_task_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PR_PM_NO_MOVE_TASK');
SELECT proj_element_id INTO l_structure_id
FROM pa_proj_element_versions
WHERE element_version_id = ( Select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = l_task_version_id )
AND object_type = 'PA_STRUCTURES';
END IF; -- End If Atleast one task has been selected for moving
PROCEDURE Update_Task_All_Info(
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 :=FND_API.G_VALID_LEVEL_FULL,
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_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
--Begin:5867373:p_task_number_tbl's data-type has been changed to varchar2(100)
p_task_number_tbl IN SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE(),
--End:5867373:
p_task_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE(),
p_task_description_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE(),
p_task_manager_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_task_manager_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE(),
p_carrying_out_org_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_carrying_out_org_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE(),
p_priority_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
p_TYPE_ID_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_status_code_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE(),
p_inc_proj_progress_flag_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE(),
p_transaction_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_transaction_finish_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_work_type_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_service_type_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
p_work_item_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
p_uom_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
-- Update_Schedule_Version
p_scheduled_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_scheduled_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_pev_schedule_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_milestone_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM. PA_VARCHAR2_1_TBL_TYPE(),
p_critical_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM. PA_VARCHAR2_1_TBL_TYPE(),
p_WQ_PLANNED_QUANTITY_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_early_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_early_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_late_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_late_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_constraint_type_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
p_constraint_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(),
p_sch_rec_ver_num_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
-- update_task_det_sch_info
p_task_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_percent_complete_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_ETC_effort_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_structure_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_project_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_planned_effort_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_actual_effort_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
-- Update_Task_Weighting
p_object_relationship_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_weighting_percentage_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_obj_rec_ver_num_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
p_task_weight_method IN VARCHAR2,
-- common
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_Task_All_Info';
pa_debug.init_err_stack ('PA_TASK_PUB1.Update_Task_All_Info');
pa_debug.debug('PA_TASK_PUB1.Update_Task_All_Info begin');
savepoint update_Task_all_info;
SELECT pt.project_id,
pt.parent_task_id,
pt.start_date,
pt.completion_date
INTO l_project_id,
l_parent_task_id,
l_tstart_date,
l_tend_date
FROM pa_tasks pt
WHERE pt.task_id = p_task_id_tbl(i);
PA_TASK_PUB1.Update_Task
(
p_task_id => p_task_id_tbl(i)
,p_task_number => p_task_number_tbl(i)
,p_task_name => p_task_name_tbl(i)
,p_task_manager_id => p_task_manager_id_tbl(i)
,p_task_manager_name => p_task_manager_name_tbl(i)
,p_record_version_number => p_record_version_number_tbl(i)
,P_TASK_DESCRIPTION => P_TASK_DESCRIPTION_tbl(i)
,P_CARRYING_OUT_ORG_NAME => P_CARRYING_OUT_ORG_NAME_tbl(i)
,P_PRIORITY_CODE => P_PRIORITY_CODE_tbl(i)
,P_STATUS_CODE => P_STATUS_CODE_tbl(i)
,P_INC_PROJ_PROGRESS_FLAG => P_INC_PROJ_PROGRESS_FLAG_tbl(i)
,p_transaction_start_date => p_transaction_start_date_tbl(i)
,p_transaction_finish_date => p_transaction_finish_date_tbl(i)
,p_service_type_code => p_service_type_code_tbl(i)
,p_work_type_id => p_work_type_id_tbl(i)
,p_work_item_code => p_work_item_code_tbl(i)
,p_uom_code => p_uom_code_tbl(i)
,p_type_id => p_type_id_tbl(i)
,p_carrying_out_org_id => p_carrying_out_org_id_tbl(i)
,p_dates_check => 'N' --bug 8301015
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_TASK_PUB1.Update_Schedule_Version
(
p_scheduled_start_date => p_scheduled_start_date_tbl(i)
,p_scheduled_end_date => p_scheduled_end_date_tbl(i)
,p_record_version_number => p_sch_rec_ver_num_tbl(i)
,p_pev_schedule_id => p_pev_schedule_id_tbl(i)
,P_MILESTONE_FLAG => P_MILESTONE_FLAG_tbl(i)
,P_CRITICAL_FLAG => P_CRITICAL_FLAG_tbl(i)
,p_WQ_PLANNED_QUANTITY => p_WQ_PLANNED_QUANTITY_tbl(i)
,p_early_start_date => p_early_start_date_tbl(i)
,p_early_end_date => p_early_end_date_tbl(i)
,p_late_start_date => p_late_start_date_tbl(i)
,p_late_end_date => p_late_end_date_tbl(i)
,p_constraint_date => p_constraint_date_tbl(i)
,p_constraint_type_code => p_constraint_type_code_tbl(i)
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_TASK_PUB1.update_task_det_sch_info
(
p_task_ver_id => p_task_version_id_tbl(i)
,p_percent_complete => p_percent_complete_tbl(i)
,p_ETC_effort => p_ETC_effort_tbl(i)
,p_structure_version_id => p_structure_version_id_tbl(i)
,p_project_id => p_project_id_tbl(i)
,p_planned_effort => p_planned_effort_tbl(i)
,p_actual_effort => p_actual_effort_tbl(i)
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_TASK_PUB1.Update_Task_Weighting
(
p_object_relationship_id => p_object_relationship_id_tbl(i)
,p_weighting_percentage => p_weighting_percentage_tbl(i)
,p_record_version_number => p_obj_rec_ver_num_tbl(i)
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.debug('PA_TASK_PUB1.Update_Task_All_Info END');
rollback to update_Task_all_info;
rollback to update_Task_all_info;
p_procedure_name => 'Update_Task_All_Info',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_Task_all_info;
p_procedure_name => 'Update_Task_All_Info',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Task_All_Info;
SELECT ver.element_version_id, str.status_code
FROM pa_proj_element_versions ver
, pa_proj_elem_ver_structure str
WHERE ver.project_id = c_project_id
AND ver.proj_element_id = c_task_id
AND ver.project_id = str.project_id
and ver.parent_structure_version_id = str.element_version_id
;
SELECT
ppv2.project_id sub_project_id
,ppv2.element_version_id sub_structure_ver_id
,ppv1.project_id parent_project_id
,ppv1.parent_structure_version_id parent_structure_ver_id
,ppv1.element_version_id parent_task_version_id
,ppv1.wbs_number parent_wbs_number
,por1.object_id_from1 link_task_ver_id
,por1.object_relationship_id object_relationship_id
,por1.record_version_number record_version_number
,ppv1.task_unpub_ver_status_code task_unpub_ver_status_code
FROM
pa_proj_element_versions ppv1 -- linking task
,pa_proj_element_versions ppv2 -- linked project
,pa_object_relationships por1
,pa_object_relationships por2
,(SELECT object_id_from1, object_id_to1
FROM pa_object_relationships
START WITH object_id_from1 = c_task_version_id
and relationship_type = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
and relationship_type = 'S'
UNION
SELECT to_number(null) object_id_from1, c_task_version_id object_id_to1
FROM DUAL
) pobj
WHERE
ppv2.element_version_id = por1.object_id_to1
AND por1.object_id_from1 = por2.object_id_to1
AND por2.object_id_from1 = ppv1.element_version_id
AND ppv1.element_version_id = pobj.object_id_to1
AND ppv2.object_type = 'PA_STRUCTURES'
AND por1.relationship_type in ( 'LW', 'LF' )
AND ppv1.project_id=c_project_id
;
SELECT
ppv.project_id sub_project_id
,ppv.element_version_id sub_structure_ver_id
,por.object_id_from1 link_task_ver_id
,por.object_relationship_id object_relationship_id
,por.record_version_number record_version_number
,por.relationship_type relationship_type
FROM
pa_proj_element_versions ppv -- linked project
,pa_object_relationships por
,(SELECT object_id_from1, object_id_to1 -- Get all sub tasks including linking tasks
FROM pa_object_relationships
START WITH object_id_from1 = c_task_version_id
and relationship_type = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
and relationship_type = 'S'
) pobj
WHERE
pobj.object_id_to1 = por.object_id_from1
AND por.relationship_type = 'LW' -- 4533534 : It shd be LW only otherwsie it will give error PA_NO_RECORD_VERSION_NUMBER
AND por.object_id_to1 = ppv.element_version_id
AND ppv.object_type = 'PA_STRUCTURES'
;
pa_debug.write(l_module_name,'Call Delete_SubProject_Association l_version_enabled='||l_version_enabled, 3);
PA_RELATIONSHIP_PUB.Delete_SubProject_Association
( p_init_msg_list => FND_API.G_FALSE,
p_calling_module => p_calling_module,
p_object_relationships_id => l_all_links.object_relationship_id,
p_record_version_number => l_all_links.record_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.write(l_module_name,'After Call Delete_SubProject_Association x_return_status='||x_return_status, 3);
select ppe.name, ppev.wbs_number --Bug 6878138
from pa_proj_elements ppe, pa_proj_element_versions ppev
where ppe.project_id = ppev.project_id
and ppe.proj_element_id = ppev.proj_element_id
and ppev.project_id = c_project_id
and ppev.element_version_id = c_element_version_id;
select ppe.name, ppev.wbs_number --Bug 6878138
from pa_proj_elements ppe, pa_proj_element_versions ppev
where ppe.project_id = ppev.project_id
and ppe.proj_element_id = ppev.proj_element_id
and ppev.project_id = c_project_id
and ppev.element_version_id = c_element_version_id;
SELECT max(notification_id) ntf_id
FROM WF_NOTIFICATIONS WFN
WHERE message_type = 'PATASKWF'
AND status = 'OPEN'
--bug 13395163
--bug 14284485
AND EXISTS (
SELECT /*+ NO_UNNEST */ 1
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE notification_id = wfn.notification_id
AND name = 'TASK_NUMBER'
AND text_value like (select element_number from pa_proj_elements
where proj_element_id = c_task_id)
)
AND EXISTS (
SELECT /*+ NO_UNNEST */ 1
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE notification_id = wfn.notification_id
AND name = 'PROJECT_NUMBER'
AND text_value like (select segment1 from pa_projects_all
where project_id = c_project_id)
);
select task_status,ppe.proj_element_id from pa_proj_elements ppe where
ppe.proj_element_id =
(select ppev1.proj_element_id from pa_proj_element_versions ppev1,pa_object_relationships por
where por.object_id_to1 = (select element_version_id from pa_proj_element_versions ppev2
where proj_element_id = c_task_id)
and por.relationship_type = 'S'
and por.relationship_subtype = 'TASK_TO_TASK'
and ppev1.element_version_id = por.object_id_from1);
SELECT pci.ci_id,
pcia.ci_action_id action_id
FROM pa_control_items pci, pa_ci_actions pcia
WHERE pci.project_id = p_project_id
AND pcia.ci_id = pci.ci_id
AND pcia.ci_action_number = pci.open_action_num
AND EXISTS (SELECT 1 FROM pa_budget_versions pbv, pa_resource_assignments pra
WHERE pbv.project_id = pci.project_Id
AND pbv.ci_id = pci.ci_id
AND pra.budget_version_id = pbv.budget_version_id
AND pra.project_id = p_project_id
AND pra.task_id = p_task_id)
AND pci.status_code in ('CI_SUBMITTED');
select carrying_out_organization_id
into l_org_id
from pa_projects_all
where project_id = l_project_id;
select ppvsch.scheduled_start_date,ppvsch.scheduled_finish_date
into l_task_start_date,l_task_finish_date
from pa_proj_elem_ver_schedule ppvsch,pa_proj_elements ppe,pa_proj_element_versions ppv
where ppe.proj_element_id = ppv.proj_element_id
and ppv.element_version_id = ppvsch.element_version_id
and ppe.proj_element_id = p_task_id_tbl(i);
update WF_NOTIFICATIONS
set status = 'CLOSED'
where notification_id = l_ntf_id ;
UPDATE PA_PROJ_ELEMENTS SET link_task_flag = 'N', task_status = ''
WHERE proj_element_id = l_task_id_tbl(i);