[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT page_name
from pa_page_layouts
where page_id = c_page_id
and page_type_code = 'AI';
SELECT carrying_out_organization_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT 'Y'
FROM pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppst.proj_element_id = p_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 = p_structure_id
AND project_id = p_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED');
select 'Y'
from pa_proj_elem_ver_structure
where element_version_id = p_structure_version_id
and project_id = p_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
SELECT 'x'
FROM pa_tasks pt, pa_proj_elements ppe
WHERE pt.project_id = p_project_id
AND pt.task_id = ppe.proj_element_id
AND ppe.link_task_flag = 'N';
SELECT 'x'
FROM pa_tasks pt
WHERE pt.project_id = p_project_id
AND EXISTS(SELECT 1
FROM PA_PROJ_ELEMENTS ppe
WHERE ppe.link_task_flag = 'N'
AND ppe.project_id = p_project_id
);
SELECT 'x'
FROM pa_proj_element_versions
WHERE object_type = 'PA_STRUCTURES'
AND element_version_id = p_ref_task_id; --Just making sure ref_task_id does not contain
SELECT location_id from pa_proj_elements where proj_element_id = p_ref_task_id;
select WORK_ITEM_CODE, UOM_CODE,
ACTUAL_WQ_ENTRY_CODE, TASK_PROGRESS_ENTRY_PAGE_ID,
INITIAL_STATUS_CODE, BASE_PERCENT_COMP_DERIV_CODE,
wf_item_type,wf_process,wf_start_lead_days
from pa_task_types
where task_type_id = c_task_type_id;
SELECT parent_task_id, top_task_id FROM pa_tasks
WHERE project_id = p_project_id and task_id = c_task_id;
SELECT task_name, task_number
FROM pa_tasks
WHERE project_id = c_project_id
AND task_id = c_task_id;
SELECT template_flag
FROM pa_projects_all
WHERE project_id = p_project_id;
select element_version_id
into l_ref_parent_task_ver_id
from pa_proj_element_versions
where proj_element_id = p_ref_task_id
and parent_structure_version_id =
PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(p_project_id);
select element_version_id
into l_ref_parent_task_ver_id
from pa_proj_element_versions
where proj_element_id = p_ref_task_id
and parent_structure_version_id =
PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(p_project_id);
PA_PROJ_ELEMENTS_PKG.Insert_Row(
X_ROW_ID => X_ROW_ID
,X_PROJ_ELEMENT_ID => x_task_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_TYPE => p_OBJECT_TYPE
,X_ELEMENT_NUMBER => l_task_NUMBER --Bug 3705333 Changed from p_task_number to l_task_number
,X_NAME => la_task_NAME --Bug 3705333 Changed from p_task_name to la_task_name
,X_DESCRIPTION => l_task_DESCRIPTION
,X_STATUS_CODE => l_STATUS_CODE
,X_WF_STATUS_CODE => null --per Sakthi
,X_PM_PRODUCT_CODE => nvl(l_pm_source_code, l_PM_PRODUCT_CODE) --bug 13923366
,X_PM_TASK_REFERENCE => l_PM_TASK_REFERENCE
,X_CLOSED_DATE => l_CLOSED_DATE
,X_LOCATION_ID => l_LOCATION_ID
,X_MANAGER_PERSON_ID => l_task_MANAGER_ID
,X_CARRYING_OUT_ORGANIZATION_ID => l_carrying_out_org_id
,X_TYPE_ID => l_TYPE_ID
,X_PRIORITY_CODE => l_PRIORITY_CODE
,X_INC_PROJ_PROGRESS_FLAG => l_INC_PROJ_PROGRESS_FLAG
,X_REQUEST_ID => null --p_REQUEST_ID --per Sakthi
,X_PROGRAM_APPLICATION_ID => null --p_PROGRAM_APPLICATION_ID --per Sakthi
,X_PROGRAM_ID => null --p_PROGRAM_ID --per Sakthi
,X_PROGRAM_UPDATE_DATE => null --p_PROGRAM_UPDATE_DATE --per Sakthi
,X_LINK_TASK_FLAG => NVL(l_link_task_flag,'N' )
,X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ATTRIBUTE1
,X_ATTRIBUTE2 => l_ATTRIBUTE2
,X_ATTRIBUTE3 => l_ATTRIBUTE3
,X_ATTRIBUTE4 => l_ATTRIBUTE4
,X_ATTRIBUTE5 => l_ATTRIBUTE5
,X_ATTRIBUTE6 => l_ATTRIBUTE6
,X_ATTRIBUTE7 => l_ATTRIBUTE7
,X_ATTRIBUTE8 => l_ATTRIBUTE8
,X_ATTRIBUTE9 => l_ATTRIBUTE9
,X_ATTRIBUTE10 => l_ATTRIBUTE10
,X_ATTRIBUTE11 => l_ATTRIBUTE11
,X_ATTRIBUTE12 => l_ATTRIBUTE12
,X_ATTRIBUTE13 => l_ATTRIBUTE13
,X_ATTRIBUTE14 => l_ATTRIBUTE14
,X_ATTRIBUTE15 => l_ATTRIBUTE15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => l_work_item_code
,X_UOM_CODE => l_uom_code
,x_wq_actual_entry_code => l_wq_actual_entry_code
,x_task_progress_entry_page_id => l_task_progress_entry_page_id
,x_phase_version_id => l_phase_version_id
,x_PARENT_STRUCTURE_ID => p_STRUCTURE_ID
,x_phase_code => l_phase_code
,x_Base_Perc_Comp_Deriv_Code => l_Base_Perc_Comp_Deriv_Code
-- Added for FP_M changes : 3305199
-- Bug#3491609 : Workflow Chanegs FP M
,x_wf_item_type => l_wf_item_type
,x_wf_process => l_wf_process
,x_wf_lead_days => l_wf_lead_days
,x_wf_enabled_flag => 'N'
-- Bug#3491609 : Workflow Chanegs FP M
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
,X_TASK_STATUS_CODE => l_task_status --Changes for 8566495 anuragag
);
/*SELECT nvl( wbs_record_version_number, 1 )
INTO l_wbs_record_version_number
-- HY: changed from pa_projects_all to pa_proj_elem_ver_structure
FROM pa_proj_elem_ver_structure
-- HY FROM pa_projects_all
WHERE project_id = p_project_id
AND element_version_id = p_structure_version_id;*/
l_plannable_tasks_tbl(1).action := 'INSERT';
l_plannable_tasks_tbl(1).action := 'INSERT';
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_carrying_out_org_id IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
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_phase_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
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_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,
-- This param added for FP_M changes 3305199
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_msg_count NUMBER;
SELECT page_name
from pa_page_layouts
where page_id = c_page_id
and page_type_code = 'AI';
SELECT object_page_layout_id, record_version_number
from pa_object_page_layouts
where object_id = c_object_id
and object_type = 'PA_TASKS'
and page_type_code = 'AI';
SELECT rowid
--project_id, object_type, record_version_number
,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
,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
,PHASE_VERSION_ID
,PHASE_CODE
,PARENT_STRUCTURE_ID
,BASE_PERCENT_COMP_DERIV_CODE
,ENABLE_WF_FLAG --Bug 14213385
,WF_PROCESS --Bug 14213385
,WF_ITEM_TYPE --Bug 14213385
,WF_START_LEAD_DAYS --Bug 14213385
FROM PA_PROJ_ELEMENTS
WHERE proj_element_id = p_task_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 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 a.proj_element_id
FROM pa_proj_element_versions a, pa_proj_element_versions b
WHERE a.element_version_id = b.parent_structure_version_id
AND b.proj_element_id = p_task_id;
select WORK_ITEM_CODE, UOM_CODE,
ACTUAL_WQ_ENTRY_CODE, TASK_PROGRESS_ENTRY_PAGE_ID,
prog_entry_enable_flag,BASE_PERCENT_COMP_DERIV_CODE --Jun 28th
from pa_task_types
where task_type_id = c_task_type_id;
select b.parent_structure_version_id, b.element_version_id
from pa_proj_elements a,
pa_proj_element_versions b,
pa_proj_elem_ver_structure c
where a.proj_element_id = p_task_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.project_id = c.project_id
and b.parent_structure_version_id = c.element_version_id
and c.LATEST_EFF_PUBLISHED_FLAG = 'Y';
l_update_WBS_flag VARCHAR2(1) := 'N';
SELECT template_flag
FROM pa_projects_all
WHERE project_id = c_project_id;
l_update_working_ver_weight VARCHAR2(1);
SELECT ppev.element_Version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = cp_project_id
and ppev.proj_element_id = cp_task_id
and ppev.project_id = ppevs.project_id
and ppevs.element_version_id = ppev.parent_structure_version_id
and ppevs.status_code <> 'STRUCTURE_PUBLISHED';
SELECT ppev.element_Version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = cp_project_id
and ppev.proj_element_id = cp_task_id
and ppev.project_id = ppevs.project_id
and ppevs.element_version_id = ppev.parent_structure_version_id
and ppevs.status_code = 'STRUCTURE_PUBLISHED';
SELECT task_name, task_number
FROM pa_tasks
WHERE project_id = c_project_id
AND task_id = c_task_id;
pa_debug.debug('PA_TASK_PVT1.UPDATE_TASK begin');
savepoint UPDATE_TASK_PRIVATE;
l_update_working_ver_weight := 'Y';
/* PA_TASK_PVT1.UPDATE_WORKING_VER_WEIGHT(
p_task_id => p_task_id
,p_weighting => 0
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
UPDATE pa_proj_elem_ver_structure
SET process_update_wbs_flag = 'Y'
WHERE project_id = l_project_id
AND proj_element_id = l_structure_id;
UPDATE pa_proj_elem_ver_structure
SET process_update_wbs_flag = 'Y'
WHERE project_id = l_project_id
AND proj_element_id = l_structure_id
AND status_code = 'STRUCTURE_WORKING';
p_msg_name => 'PA_PS_CHK_DELIV_UPDATE');
p_msg_name => 'PA_PS_CHK_DELIV_UPDATE');
PA_PROJ_ELEMENTS_PKG.Update_Row(
X_ROW_ID => v_cur_proj_elems_rec.rowid
,X_PROJ_ELEMENT_ID => p_task_id
,X_PROJECT_ID => v_cur_proj_elems_rec.PROJECT_ID
,X_OBJECT_TYPE => 'PA_TASKS'
,X_ELEMENT_NUMBER => l_task_number --Bug 3705333 changed from p_task_number to l_task_number
,X_NAME => la_task_name --Bug 3705333 changed from p_task_namer to la_task_name
,X_DESCRIPTION => l_task_DESCRIPTION
,X_STATUS_CODE => l_STATUS_CODE
,X_WF_STATUS_CODE => l_wf_status_code
,X_PM_PRODUCT_CODE => NVL(P_PM_SOURCE_CODE,l_PM_PRODUCT_CODE) -- Huawei 15876400 source code
,X_PM_TASK_REFERENCE => l_PM_TASK_REFERENCE
,X_CLOSED_DATE => l_CLOSED_DATE
,X_LOCATION_ID => l_LOCATION_ID
,X_MANAGER_PERSON_ID => l_task_MANAGER_ID
,X_CARRYING_OUT_ORGANIZATION_ID => l_carrying_out_org_id
,X_TYPE_ID => l_TYPE_ID
,X_PRIORITY_CODE => l_PRIORITY_CODE
,X_INC_PROJ_PROGRESS_FLAG => l_INC_PROJ_PROGRESS_FLAG
,X_REQUEST_ID => null --p_REQUEST_ID
,X_PROGRAM_APPLICATION_ID => null --p_PROGRAM_APPLICATION_ID
,X_PROGRAM_ID => null --p_PROGRAM_ID
,X_PROGRAM_UPDATE_DATE => null --p_PROGRAM_UPDATE_DATE
,X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ATTRIBUTE1
,X_ATTRIBUTE2 => l_ATTRIBUTE2
,X_ATTRIBUTE3 => l_ATTRIBUTE3
,X_ATTRIBUTE4 => l_ATTRIBUTE4
,X_ATTRIBUTE5 => l_ATTRIBUTE5
,X_ATTRIBUTE6 => l_ATTRIBUTE6
,X_ATTRIBUTE7 => l_ATTRIBUTE7
,X_ATTRIBUTE8 => l_ATTRIBUTE8
,X_ATTRIBUTE9 => l_ATTRIBUTE9
,X_ATTRIBUTE10 => l_ATTRIBUTE10
,X_ATTRIBUTE11 => l_ATTRIBUTE11
,X_ATTRIBUTE12 => l_ATTRIBUTE12
,X_ATTRIBUTE13 => l_ATTRIBUTE13
,X_ATTRIBUTE14 => l_ATTRIBUTE14
,X_ATTRIBUTE15 => l_ATTRIBUTE15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => l_work_item_code
,X_UOM_CODE => l_uom_code
,X_WQ_ACTUAL_ENTRY_CODE => l_wq_actual_entry_code
,X_TASK_PROGRESS_ENTRY_PAGE_ID => l_task_progress_entry_page_id
,X_PHASE_VERSION_ID => l_phase_version_id
,X_PHASE_CODE => l_phase_code
,X_PARENT_STRUCTURE_ID => v_cur_proj_elems_rec.PARENT_STRUCTURE_ID
,X_RECORD_VERSION_NUMBER => p_record_version_number
,x_Base_Perc_Comp_Deriv_Code => l_Base_Perc_Comp_Deriv_Code
-- Added for FP_M changes : 3305199
-- Bug#3491609 : Workflow Chanegs FP M
,x_wf_item_type => l_wf_item_type
,x_wf_process => l_wf_process
,x_wf_lead_days => l_wf_lead_days
,x_wf_enabled_flag => l_wf_enabled_flag
-- Bug#3491609 : Workflow Chanegs FP M
);
PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => v_cur_proj_elems_rec.project_id
,p_old_classification_id => v_cur_proj_elems_rec.type_id
,p_new_classification_id => l_type_id
,p_classification_type => 'TASK_TYPE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
IF nvl(l_update_working_ver_weight,'N') = 'Y' THEN
PA_TASK_PVT1.UPDATE_WORKING_VER_WEIGHT(
p_task_id => p_task_id
,p_weighting => 0
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
sELECT wbs_level, parent_task_id, top_task_id
INTO l_wbs_level, l_parent_task_id, l_top_task_id
FROM pa_tasks
WHERE task_id = p_ref_task_id;
PA_TASKS_MAINT_PUB.UPDATE_TASK
(
p_project_id => l_project_id
,p_task_id => p_task_id
,p_task_number => SUBSTRB( l_task_number, 1, 25 ) --Bug 3705333 changed from p_task_number to l_task_number
,p_task_name => SUBSTRB( la_task_name, 1, 20 ) --Bug 3705333 changed from p_task_name to la_task_name
,p_long_task_name => la_task_name --Bug 3705333 changed from p_task_name to la_task_nam
-- Bug#5227374.Corrected the substrb syntax below which was introduced thru Bug#3935874
,p_task_description => SUBSTRB(l_task_DESCRIPTION,1,250) -- Bug 3935874
,p_task_manager_person_id => l_task_MANAGER_ID
,p_carrying_out_organization_id => l_carrying_out_org_id
,p_task_type_code => l_TYPE_ID
,p_priority_code => l_PRIORITY_CODE
,p_pm_product_code => l_PM_PRODUCT_CODE
,p_pm_task_reference => l_PM_TASK_REFERENCE
,p_task_start_date => p_transaction_start_date
,p_task_completion_date => p_transaction_finish_date
,p_inc_proj_progress_flag => l_INC_PROJ_PROGRESS_FLAG
,p_record_version_number => p_record_version_number
,p_wbs_record_version_number => 1
,p_top_task_id => l_top_task_id
,p_parent_task_id => l_parent_task_id
,p_wbs_level => l_wbs_level
,p_address_id => p_address_id
,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_attribute_category => l_tk_attribute_category
,p_attribute1 => l_tk_attribute1
,p_attribute2 => l_tk_attribute2
,p_attribute3 => l_tk_attribute3
,p_attribute4 => l_tk_attribute4
,p_attribute5 => l_tk_attribute5
,p_attribute6 => l_tk_attribute6
,p_attribute7 => l_tk_attribute7
,p_attribute8 => l_tk_attribute8
,p_attribute9 => l_tk_attribute9
,p_attribute10 => l_tk_attribute10
,p_gen_etc_src_code => p_gen_etc_src_code
,p_dates_check => p_dates_check --bug 8301015
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
ELSE --Added for transaction dates update
--there is a publish version; update task that have financial attribute
PA_TASKS_MAINT_PUB.UPDATE_TASK
(
p_project_id => l_project_id
,p_task_id => p_task_id
,p_task_number => SUBSTRB( l_task_NUMBER, 1, 25 ) --Bug 3705333 changed from p_task_number to l_task_number
,p_task_name => SUBSTRB( la_task_NAME, 1, 20 ) --Bug 3705333 changed from p_task_name to la_task_name
,p_long_task_name => la_task_name --Bug 3705333 changed from p_task_name to la_task_name
-- Bug#5227374.Corrected the substrb syntax below which was introduced thru Bug#3935874
,p_task_description => SUBSTRB(l_task_DESCRIPTION,1,250) -- Bug 3935874
,p_task_manager_person_id => l_task_MANAGER_ID
,p_carrying_out_organization_id => l_carrying_out_org_id
,p_task_type_code => l_TYPE_ID
,p_priority_code => l_PRIORITY_CODE
,p_pm_product_code => l_PM_PRODUCT_CODE
,p_pm_task_reference => l_PM_TASK_REFERENCE
,p_task_start_date => p_transaction_start_date
,p_task_completion_date => p_transaction_finish_date
,p_inc_proj_progress_flag => l_INC_PROJ_PROGRESS_FLAG
,p_record_version_number => p_record_version_number
,p_wbs_record_version_number => 1
--Commented for Bug 3746669 ,p_top_task_id => l_top_task_id
--Commented for Bug 3746669 ,p_parent_task_id => l_parent_task_id
--Commented for Bug 3746669 ,p_wbs_level => l_wbs_level
,p_address_id => p_address_id
,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_attribute_category => l_tk_attribute_category
,p_attribute1 => l_tk_attribute1
,p_attribute2 => l_tk_attribute2
,p_attribute3 => l_tk_attribute3
,p_attribute4 => l_tk_attribute4
,p_attribute5 => l_tk_attribute5
,p_attribute6 => l_tk_attribute6
,p_attribute7 => l_tk_attribute7
,p_attribute8 => l_tk_attribute8
,p_attribute9 => l_tk_attribute9
,p_attribute10 => l_tk_attribute10
,p_gen_etc_src_code => p_gen_etc_src_code
,p_dates_check => p_dates_check --bug 8301015
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
ROLLBACK to UPDATE_TASK_PRIVATE;
ROLLBACK to UPDATE_TASK_PRIVATE;
p_procedure_name => 'update_Task',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Task;
SELECT *
FROM pa_proj_element_versions
WHERE element_version_id = p_ref_task_version_id;
/* SELECT max( b.display_sequence )
FROM( SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = p_ref_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = 'S' ) a, pa_proj_element_versions b
WHERE a.object_id_to1 = b.element_version_id;
SELECT max( b.display_sequence )
FROM pa_proj_element_versions b
WHERE b.element_version_id IN
( SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = p_ref_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = PRIOR relationship_type --bug 3919266
AND relationship_type = 'S' ) ;
SELECT object_id_from1, object_type_from
FROM pa_object_relationships
WHERE object_id_to1 = p_ref_task_version_id
AND relationship_type = 'S';
SELECT base_percent_comp_deriv_code
FROM pa_proj_elements
WHERE proj_element_id = cp_task_id;
SELECT proj_element_id INTO l_structure_id_from
FROM pa_proj_element_versions
WHERE element_version_id = v_cur_proj_elems_rec.parent_structure_version_id;
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => X_row_id
,X_ELEMENT_VERSION_ID => x_task_version_id
,X_PROJ_ELEMENT_ID => p_task_id
,X_OBJECT_TYPE => 'PA_TASKS'
,X_PROJECT_ID => v_cur_proj_elems_rec.project_id
,X_PARENT_STRUCTURE_VERSION_ID => l_parent_struc_ver_id
,X_DISPLAY_SEQUENCE => l_DISPLAY_SEQUENCE
,X_WBS_LEVEL => l_WBS_LEVEL
,X_WBS_NUMBER => l_WBS_NUMBER
,X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ATTRIBUTE1
,X_ATTRIBUTE2 => l_ATTRIBUTE2
,X_ATTRIBUTE3 => l_ATTRIBUTE3
,X_ATTRIBUTE4 => l_ATTRIBUTE4
,X_ATTRIBUTE5 => l_ATTRIBUTE5
,X_ATTRIBUTE6 => l_ATTRIBUTE6
,X_ATTRIBUTE7 => l_ATTRIBUTE7
,X_ATTRIBUTE8 => l_ATTRIBUTE8
,X_ATTRIBUTE9 => l_ATTRIBUTE9
,X_ATTRIBUTE10 => l_ATTRIBUTE10
,X_ATTRIBUTE11 => l_ATTRIBUTE11
,X_ATTRIBUTE12 => l_ATTRIBUTE12
,X_ATTRIBUTE13 => l_ATTRIBUTE13
,X_ATTRIBUTE14 => l_ATTRIBUTE14
,X_ATTRIBUTE15 => l_ATTRIBUTE15
,X_TASK_UNPUB_VER_STATUS_CODE => p_task_unpub_ver_status_code
,p_financial_task_flag => p_financial_task_flag -- FP_M changes 3305199
,X_SOURCE_OBJECT_ID => v_cur_proj_elems_rec.project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
Update PA_PROJ_ELEMENT_VERSIONS
set display_sequence = l_ref_seq_no,
last_updated_by = -ABS(FND_LOG_MESSAGES_S.NEXTVAL)
where element_version_id = x_task_version_id;
UPDATE pa_proj_element_versions
SET display_sequence = display_sequence + 1
WHERE display_sequence > l_ref_seq_no
AND element_version_id <> x_task_version_id
AND parent_structure_version_id = v_cur_proj_elems_rec.parent_structure_version_id
AND object_type = 'PA_TASKS'
-- AND PA_PROJ_ELEMENTS_UTILS.link_flag ( proj_element_id ) = 'N' commenitng out for bug 4180390
;
UPDATE pa_proj_element_versions
SET display_sequence = display_sequence + 1
WHERE display_sequence > l_ref_seq_no
AND element_version_id <> x_task_version_id
AND parent_structure_version_id = v_cur_proj_elems_rec.parent_structure_version_id
AND object_type = 'PA_TASKS'
--AND PA_PROJ_ELEMENTS_UTILS.link_flag ( proj_element_id ) = 'N' commenitng out for bug 4180390
;
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS (
p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => l_parent_struc_ver_id
,p_task_id => x_task_version_id
,p_display_seq => l_display_sequence
,p_action => 'INSERT'
,p_parent_task_id => l_task_version_from
,x_return_status => x_return_status );
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_msg_count NUMBER;
SELECT *
FROM pa_proj_element_versions
WHERE element_version_id = p_ref_task_version_id;
SELECT rowid, object_type, project_id, proj_element_id, record_version_number,
display_sequence, wbs_number, wbs_level, parent_structure_version_id,
attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15, TASK_UNPUB_VER_STATUS_CODE
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id
AND object_type = 'PA_TASKS';
IS SELECT a.object_id_from1, a.object_id_to1, b.parent_structure_version_id, b.display_sequence
FROM
( SELECT object_id_from1, object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = c_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = prior relationship_type --bug 3919266
AND relationship_type = 'S' ) a, pa_proj_element_versions b
WHERE a.object_id_to1 = b.element_version_id
ORDER BY 4;
IS SELECT max( display_sequence )
FROM pa_proj_element_versions
WHERE element_version_id in ( SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = c_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = PRIOR relationship_type --bug 3919266
AND relationship_type = 'S' );
IS SELECT max( display_sequence )
FROM pa_proj_element_versions
WHERE element_version_id in ( SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
and object_id_to1 <> p_task_version_id
START WITH object_id_from1 = c_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1 --bug 3919266
AND relationship_type = prior relationship_type
AND relationship_type = 'S' )
AND display_sequence <= c_ref_task_disp_seq; -- Bug 6628382
IS SELECT max( display_sequence)
FROM pa_proj_element_versions
WHERE element_version_id in ( SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
and object_id_to1 <> p_task_version_id
START WITH object_id_from1 = c_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1 --bug 3919266
AND relationship_type = prior relationship_type
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 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 = p_ref_task_version_id
AND object_id_to1 = element_version_id
AND display_sequence > l_display_sequence
AND relationship_type = 'S'
order by display_sequence;
l_update_new_child_rels VARCHAR2(1) := 'N';
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 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 PROJECT_ID INTO L_PROJECT_ID FROM pa_proj_element_versions WHERE element_version_id = p_task_version_id ;
select project_id, wbs_level,display_sequence
into l2_project_id , l2_ref_task_wbs_level, l2_ref_task_display_seq
from pa_proj_element_versions
where element_version_id = p_ref_task_version_id;
select wbs_level
into l2_task_wbs_level
from pa_proj_element_versions
where element_version_id = p_task_version_id;
pa_debug.debug('PA_TASK_PVT1.UPDATE_TASK_VERSION begin');
savepoint UPDATE_TASK_VER_PRIVATE;
SELECT rel.object_id_from1 INTO l_parent_task_id
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE pev.element_version_id = p_task_version_id
AND pev.object_type = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND rel.relationship_type = 'S'
AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
SELECT rel.object_id_from1 INTO l_ref_parent_task_id
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE pev.element_version_id = p_ref_task_version_id
AND pev.object_type = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND rel.relationship_type = 'S'
AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
l_update_new_child_rels := 'Y';
PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
X_ROW_ID => v_cur_proj_elems_rec.rowid
,X_ELEMENT_VERSION_ID => p_task_version_id
,X_PROJ_ELEMENT_ID => v_cur_proj_elems_rec.proj_element_id
,X_OBJECT_TYPE => v_cur_proj_elems_rec.object_type
,X_PROJECT_ID => v_cur_proj_elems_rec.project_id
,X_PARENT_STRUCTURE_VERSION_ID => v_cur_proj_elems_rec.parent_structure_version_id
,X_DISPLAY_SEQUENCE => l_new_display_sequence
,X_WBS_LEVEL => l_wbs_level
,X_WBS_NUMBER => v_cur_proj_elems_rec.wbs_number
,X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ATTRIBUTE1
,X_ATTRIBUTE2 => l_ATTRIBUTE2
,X_ATTRIBUTE3 => l_ATTRIBUTE3
,X_ATTRIBUTE4 => l_ATTRIBUTE4
,X_ATTRIBUTE5 => l_ATTRIBUTE5
,X_ATTRIBUTE6 => l_ATTRIBUTE6
,X_ATTRIBUTE7 => l_ATTRIBUTE7
,X_ATTRIBUTE8 => l_ATTRIBUTE8
,X_ATTRIBUTE9 => l_ATTRIBUTE9
,X_ATTRIBUTE10 => l_ATTRIBUTE10
,X_ATTRIBUTE11 => l_ATTRIBUTE11
,X_ATTRIBUTE12 => l_ATTRIBUTE12
,X_ATTRIBUTE13 => l_ATTRIBUTE13
,X_ATTRIBUTE14 => l_ATTRIBUTE14
,X_ATTRIBUTE15 => l_ATTRIBUTE15
,X_record_version_number => p_record_version_number
,X_TASK_UNPUB_VER_STATUS_CODE => l_task_unpub_ver_status_code
);
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = p_task_version_id
AND relationship_type = 'S';
update pa_proj_element_versions b
SET b.display_sequence = l2_ref_task_display_seq,
b.last_updated_by = decode( b.element_version_id,p_task_version_id,0,-1)
where b.element_version_id in ( p_task_version_id,p_ref_task_version_id);
update pa_proj_element_versions b
SET b.display_sequence = l2_ref_task_display_seq,
b.last_updated_by = decode( b.element_version_id,p_task_version_id,0,-1)
where b.element_version_id in ( p_task_version_id,l2_peer_task_version_id);
update pa_proj_element_versions b
SET b.display_sequence = l2_ref_task_display_seq,
b.wbs_level =
decode(p_peer_or_sub,
'SUB',l2_ref_task_wbs_level+(b.wbs_level- l2_task_wbs_level)+1,
l2_ref_task_wbs_level+(b.wbs_level - l2_task_wbs_level)),
b.last_updated_by = b.display_sequence
where b.element_version_id = cur_child_tasks_rec.object_id_to1;
UPDATE pa_proj_element_versions
SET wbs_level = wbs_level + l_wbs_level_diff,
display_sequence = decode( p_action, 'MOVE', l_new_display_sequence, display_sequence )
WHERE element_version_id = cur_child_tasks_rec.object_id_to1;
UPDATE pa_proj_element_versions
SET wbs_level = wbs_level + l_wbs_level_diff,
display_sequence = decode( p_action, 'MOVE', l_new_display_sequence, display_sequence )
WHERE element_version_id = cur_child_tasks_rec.object_id_to1;
CURSOR cur_update_with_null
IS
SELECT element_version_id, display_sequence
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
AND display_sequence < 0;
--update display sequence of the tasks with NULL to update WBS_NUMBER.
FOR cur_update_with_null_rec in cur_update_with_null LOOP
UPDATE pa_proj_element_versions
SET display_sequence = null
WHERE element_version_id = cur_update_with_null_rec.element_version_id;
l_TaskTab(i).task_version_id := cur_update_with_null_rec.element_version_id;
l_TaskTab(i).display_sequence := cur_update_with_null_rec.display_sequence;
-- if p_peer_or_sub is Sub or parent task is not same for source and destination task, then call PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS.
IF p_peer_or_sub = 'SUB' or (p_peer_or_sub = 'PEER' and l_parent_task_id <> l_ref_parent_task_id)
then
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => l_parent_struc_ver_id
,p_task_id => NULL
,p_display_seq => l_task_seq_num
,p_action => 'DELETE'
,p_parent_task_id => l_parent_of_task_version
,x_return_status => l_return_status );
UPDATE pa_proj_element_versions
SET display_sequence = l_TaskTab(k).display_sequence
WHERE element_version_id = l_TaskTab(k).task_version_id;
SELECT element_version_id, wbs_number
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
ORDER BY display_sequence;
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 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
);
DELETE FROM PA_PROJ_ELEM_RELATIONSHIP_T;
INSERT INTO PA_PROJ_ELEM_RELATIONSHIP_T (PARENT_STRUCTURE_VERSION_ID, CHILD_VER_ID,
PARENT_VER_ID, DISPLAY_SEQUENCE, OBJECT_ID_TO1, OBJECT_TYPE_TO, RELATIONSHIP_TYPE,
OBJECT_ID_FROM1, OBJECT_TYPE_FROM)
SELECT pev.parent_structure_version_id,
pev.element_version_id child_ver_id,
parent.element_version_id parent_ver_id,
pev.display_sequence, rel.object_id_to1, rel.object_type_to,
rel.relationship_type, rel.object_id_from1, rel.object_type_from
FROM pa_proj_element_versions pev, pa_object_relationships rel,
pa_proj_element_versions parent
WHERE rel.object_type_from = 'PA_TASKS'
AND rel.relationship_type = 'S'
AND rel.object_type_to = 'PA_TASKS'
AND rel.object_id_from1 = parent.element_version_id
AND rel.object_id_to1 = pev.element_version_id
AND pev.parent_structure_version_id = l_parent_struc_ver_id
and parent.PARENT_STRUCTURE_VERSION_ID = l_parent_struc_ver_id
order by parent.element_version_id;
--Update the display sequence of the affected tasks other than
--the p_task_version and p_task_version children.
/*--The following block is added for debugging.
DECLARE
CURSOR cur_print_wbs
IS
SELECT element_version_id, wbs_number
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
ORDER BY display_sequence;
-- Sridhar Huawei 18-Sept Moved the DELETE and insert above.
/*DELETE FROM PA_PROJ_ELEM_RELATIONSHIP_T;
INSERT INTO PA_PROJ_ELEM_RELATIONSHIP_T (PARENT_STRUCTURE_VERSION_ID, CHILD_VER_ID,
PARENT_VER_ID, DISPLAY_SEQUENCE, OBJECT_ID_TO1, OBJECT_TYPE_TO, RELATIONSHIP_TYPE,
OBJECT_ID_FROM1, OBJECT_TYPE_FROM)
SELECT pev.parent_structure_version_id,
pev.element_version_id child_ver_id,
parent.element_version_id parent_ver_id,
pev.display_sequence, rel.object_id_to1, rel.object_type_to,
rel.relationship_type, rel.object_id_from1, rel.object_type_from
FROM pa_proj_element_versions pev, pa_object_relationships rel,
pa_proj_element_versions parent
WHERE rel.object_type_from = 'PA_TASKS'
AND rel.relationship_type = 'S'
AND rel.object_type_to = 'PA_TASKS'
AND rel.object_id_from1 = parent.element_version_id
AND rel.object_id_to1 = pev.element_version_id
AND pev.parent_structure_version_id = l_parent_struc_ver_id
and parent.PARENT_STRUCTURE_VERSION_ID = l_parent_struc_ver_id
order by parent.element_version_id;*/
SELECT element_version_id, parent_structure_version_id, display_sequence
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
AND ( ( l_move_direction = 'DOWN' and p_peer_or_sub = 'SUB' and
display_sequence > nvl( l_task_last_child_seq_num, l_task_seq_num ) and display_sequence <= l_ref_task_seq_num ) OR
( l_move_direction = 'DOWN' and p_peer_or_sub = 'PEER' and
display_sequence > nvl( l_task_last_child_seq_num, l_task_seq_num ) and display_sequence <= l_ref_task_last_child_seq_num ) OR
( l_move_direction = 'UP' and p_peer_or_sub = 'SUB' and
display_sequence > l_ref_task_seq_num and display_sequence < l_task_seq_num ) OR
( l_move_direction = 'UP' and p_peer_or_sub = 'PEER' and
display_sequence > l_ref_task_last_child_seq_num and display_sequence < l_task_seq_num ) OR
--**
( l_move_direction = 'UP' and l_ref_task_last_child_seq_num > l_task_seq_num and
display_sequence > l_task_seq_num and display_sequence <= l_ref_task_last_child_seq_num )
--**
)
ORDER BY display_sequence;
--Update the affcted tasks sequence numbers
IF l_move_direction = 'UP'
THEN
l_new_display_sequence := cur_affected_tasks_rec.display_sequence + l_no_of_tasks;
UPDATE pa_proj_element_versions
SET display_sequence = l_new_display_sequence
WHERE element_version_id = cur_affected_tasks_rec.element_version_id;
-- Update new display sequence
UPDATE PA_PROJ_ELEM_RELATIONSHIP_T
SET display_sequence = l_new_display_sequence
WHERE child_ver_id = cur_affected_tasks_rec.element_version_id;
--update wbs number for affected tasks
-- Insert
--update WBS NUMBER properly now for the affected rows.
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => cur_affected_tasks_rec.parent_structure_version_id
,p_task_id => cur_affected_tasks_rec.element_version_id
,p_display_seq => l_new_display_sequence
,p_action => 'INSERT'
,p_parent_task_id => NULL
,p_task_version_id => p_task_version_id
,p_ref_task_version_id => p_ref_task_version_id
,p_peer_or_sub => p_peer_or_sub
,x_return_status => l_return_status );
--Update the sequence number and WBS number of the tasks ( p_task_version and its children ) being updated.
DECLARE
CURSOR cur_actual_tasks
IS
SELECT element_version_id, display_sequence, parent_structure_version_id
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
AND display_sequence < 0
ORDER BY display_sequence desc;
UPDATE pa_proj_element_versions
SET display_sequence = l_new_display_sequence
WHERE element_version_id = cur_actual_tasks_rec.element_version_id;
-- Update new display sequence
UPDATE PA_PROJ_ELEM_RELATIONSHIP_T
SET display_sequence = l_new_display_sequence
WHERE child_ver_id = cur_actual_tasks_rec.element_version_id;
--update wbs number for actual tasks
-- Insert
--update WBS NUMBER properly now
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => cur_actual_tasks_rec.parent_structure_version_id
,p_task_id => cur_actual_tasks_rec.element_version_id
,p_display_seq => l_new_display_sequence
,p_action => 'INSERT'
,p_parent_task_id => NULL
,p_task_version_id => p_task_version_id
,p_ref_task_version_id => p_ref_task_version_id
,p_peer_or_sub => p_peer_or_sub
,x_return_status => l_return_status );
SELECT element_version_id, parent_structure_version_id, display_sequence
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
AND display_sequence > nvl( l_ref_task_last_child_seq_num, l_ref_task_seq_num )
ORDER BY display_sequence;
SELECT element_version_id, parent_structure_version_id, display_sequence
FROM pa_proj_element_versions
WHERE parent_structure_version_id = l_parent_struc_ver_id
AND display_sequence between c_start_display_seq and c_end_display_seq
ORDER BY display_sequence;
--update wbs number for affected tasks
-- Insert
--update WBS NUMBER properly now for the affected rows.
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => cur_affected_tasks_rec.parent_structure_version_id
,p_task_id => cur_affected_tasks_rec.element_version_id
,p_display_seq => cur_affected_tasks_rec.display_sequence
,p_action => 'INSERT'
,p_parent_task_id => NULL
,p_task_version_id => p_task_version_id
,p_ref_task_version_id => p_ref_task_version_id
,p_peer_or_sub => p_peer_or_sub
,x_return_status => l_return_status );
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS
( p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => cur_affected_tasks_rec.parent_structure_version_id
,p_task_id => cur_affected_tasks_rec.element_version_id
,p_display_seq => cur_affected_tasks_rec.display_sequence
,p_action => 'INSERT'
,p_parent_task_id => NULL
,p_task_version_id => p_task_version_id
,p_ref_task_version_id => p_ref_task_version_id
,p_peer_or_sub => p_peer_or_sub
,x_return_status => l_return_status );
ROLLBACK to UPDATE_TASK_VER_PRIVATE;
ROLLBACK to UPDATE_TASK_VER_PRIVATE;
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
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;
l_delete_flag VARCHAR2(1) := 'N';
SELECT a.object_relationship_id object_relationship_id, a.object_id_to1 object_id_to1,
a.record_version_number record_version_number, b.wbs_level wbs_level, b.display_sequence
FROM (
SELECT object_relationship_id, object_id_to1, record_version_number
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = p_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = prior relationship_type -- bug 3919266
AND relationship_type = 'S' ) A, pa_proj_element_versions b
,pa_proj_elements c --bug 4573340
WHERE a.object_id_to1 = b.element_version_id
--bug 4573340
and b.proj_element_id = c.proj_element_id
and b.project_id = c.project_id
and c.link_task_flag = 'N'
--bug 4573340
UNION
SELECT a.object_relationship_id, element_version_id object_id_to1,
a.record_version_number, wbs_level, b.display_sequence
FROM pa_object_relationships a, pa_proj_element_versions b
WHERE element_version_id = p_task_version_id
AND object_id_to1 = p_task_version_id
AND element_version_id = object_id_to1
AND relationship_type = 'S'
ORDER BY 4 desc;
SELECT rowid,record_version_number, project_id, parent_structure_version_id, proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = p_task_id;
SELECT rowid
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = p_task_id
and project_id = p_project_id;
SELECT wbs_record_version_number
-- HY: changed from pa_projects_all to pa_proj_elem_ver_structure
FROM pa_proj_elem_ver_structure
-- HY FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT record_version_number
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT 'X'
FROM pa_proj_element_versions
WHERE proj_element_id = x_proj_element_id
AND element_version_id <> x_task_version;
SELECT rowid
FROM pa_proj_elements
WHERE proj_element_id = x_proj_element_id;
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_task_version_id
AND relationship_type = 'S';
select b.object_id_to1 object_id_to1
from pa_object_relationships a,
pa_object_relationships b,
pa_proj_element_versions c,
pa_proj_elements d
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'
and b.object_id_to1 = c.element_version_id
and c.project_id = d.project_id
and c.proj_element_id = d.proj_element_id
and d.link_task_flag <> 'Y';
select object_id_from1
from pa_object_relationships
where object_id_to1 = p_task_version_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S'
and object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
l_selected_seq_num NUMBER;
select b.parent_structure_version_id, b.element_version_id
from pa_proj_element_versions b,
pa_proj_elem_ver_structure c
where b.element_version_id = p_task_version_id
and b.project_id = c.project_id
and b.parent_structure_version_id = c.element_version_id
and c.LATEST_EFF_PUBLISHED_FLAG = 'Y';
SELECT parent_task_id, top_task_id, record_version_number 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
-- bug# 3766336 Satish 19/07/04
--WHERE object_id_from1 = p_task_version_id
WHERE object_id_from1 = c_task_version_id
AND relationship_type = 'M';
SELECT pors.object_relationship_id,pors.record_version_number
FROM pa_object_relationships pors
--bug 4573340
,pa_object_relationships pors1
WHERE
--pors.object_id_from1= cp_task_ver_id
pors1.object_id_from1 = cp_task_ver_id
AND pors.object_id_from1 = pors1.object_id_to1
AND pors1.relationship_type = 'S'
--bug 4573340
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 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;
select count(1) from pa_proj_element_versions
where object_type = 'PA_TASKS'
and parent_structure_version_id = c_struc_ver_id;
select ppa.template_flag, ppa.record_version_number
from pa_projects_all ppa
where ppa.project_id = c_project_id;
SELECT project_id
Into l_Project_ID
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id and rownum < 2;
select proj_element_id, project_id into l_task_id , l_Project_ID -- 4201927
from pa_proj_element_versions
where element_version_id = p_task_version_id;
pa_debug.debug('PA_TASK_PVT1.DELETE_TASK_VERSION begin');
savepoint DELETE_TASK_VER_PRIVATE;
IF p_called_from_api <> 'DELETE_STRUCTURE_VERSION' AND ( l_wp_str_exists = 'Y' OR l_shared = 'Y' ) THEN -- 4201927 added
FOR i IN get_all_subtasks(p_task_version_id) LOOP
l_element_version_id_tbl.extend(1);
IF p_called_from_api = 'BULK_DELETE' THEN
l_calling_context := 'PA_DELETE_TASKS';
PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions(
p_context => 'WORKPLAN'
,p_calling_context => l_calling_context -- Bug 9535723
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_element_version_id_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
p_procedure_name => 'delete_Task_version',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions:'||SQLERRM,1,240));
l_delete_flag := 'Y';
SELECT proj_element_id INTO l_structure_id
FROM pa_proj_element_versions
WHERE element_version_id = p_structure_version_id
AND project_id = l_project_id;
l_delete_flag := 'Y';
l_delete_flag := 'Y';
IF l_delete_flag = 'Y' AND PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_task_id )= 'Y'
-- Added for FP_M changes Bug 3305199 Bhumesh
THEN
-- Added for FP_M changes
If PA_Project_Structure_Utils.Check_Third_Party_Sch_Flag(l_Project_ID) = 'Y' then
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_TASKS_MAINT_PUB.DELETE_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_project_id => l_project_id
,p_task_id => l_task_id
,p_record_version_number => x_task_rec_version_number
,p_wbs_record_version_number => x_wbs_rec_version_number
,p_called_from_api => p_called_from_api
-- 4201927 pass p_bulk_flag as 'Y' to avoid delete_task_ok api validation in
-- pa_proj_maint_pvt and pa_project_core.delete_task apis
,p_bulk_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END IF; --delete flag chk
PA_PROJ_ELEMENTS_UTILS.check_delete_task_ver_ok(
p_project_id => x_project_id
,p_task_version_id => cur_child_tasks_rec.object_id_to1
,p_parent_structure_ver_id => x_parent_struc_ver_id
--bug 3010538 ,p_validation_mode => l_validation_mode --bug 2947492
,x_return_status => l_return_status
,x_error_message_code => l_error_message_code );
PA_PROJ_ELEMENT_SCH_PKG.Delete_row( x_sch_row_id );
DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE ELEMENT_VERSION_ID = cur_child_tasks_rec.object_id_to1
AND PROJECT_ID = x_project_id;
PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_element_version_id_tbl
-- ,p_maintain_reporting_lines => 'Y'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
p_procedure_name => 'delete_Task_version',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions:'||SQLERRM,1,240));
PA_PROJ_STRUC_MAPPING_PUB.delete_mapping
(
p_wp_task_version_id => cur_child_tasks_rec.object_id_to1
, p_fp_task_version_id => l_fp_task_version_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
l_delete_flag := 'Y';
SELECT proj_element_id INTO l_structure_id
FROM pa_proj_element_versions
WHERE element_version_id = x_parent_struc_ver_id
AND project_id = x_project_id;
l_delete_flag := 'Y';
l_delete_flag := 'Y';
IF l_delete_flag = 'Y' AND PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id )= 'Y'
-- Added for FP_M changes Bug 3305199 Bhumesh
THEN
-- Added for FP_M changes
If PA_Project_Structure_Utils.Check_Third_Party_Sch_Flag(x_Project_ID) = 'Y' then
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
);
l_plannable_tasks_tbl(1).action := 'DELETE';
PA_TASKS_MAINT_PUB.DELETE_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_project_id => x_project_id
,p_task_id => l_proj_element_id
,p_record_version_number => x_task_rec_version_number
,p_wbs_record_version_number => x_wbs_rec_version_number
,p_called_from_api => p_called_from_api
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END IF; --delete flag chk
pa_debug.write('PA_TASK_PVT1.DELETE_TASK_VERSION', 'Before PA_RELATIONSHIP_PUB.Delete_SubProject_Association get_subproj_relation_rec.object_relationship_id='
||get_subproj_relation_rec.object_relationship_id, 3);
pa_debug.write('PA_TASK_PVT1.DELETE_TASK_VERSION', 'Before PA_RELATIONSHIP_PUB.Delete_SubProject_Association get_subproj_relation_rec.record_version_number='
||get_subproj_relation_rec.record_version_number, 3);
PA_RELATIONSHIP_PUB.Delete_SubProject_Association --bug 4573340 replaced the call with delete_subproject
(
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_relationships_id => get_subproj_relation_rec.object_relationship_id
,p_record_version_number => get_subproj_relation_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.write('PA_TASK_PVT1.DELETE_TASK_VERSION', 'After PA_RELATIONSHIP_PUB.Delete_SubProject_Association l_return_status='||l_return_status, 3);
PA_RELATIONSHIP_PUB.Delete_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_child_tasks_rec.object_relationship_id
,p_record_version_number => cur_child_tasks_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_ELEMENT_VERSIONS_PKG.Delete_Row( x_row_id );
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS (
p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => x_parent_struc_ver_id
,p_task_id => null
,p_display_seq => cur_child_tasks_rec.display_sequence
,p_action => 'DELETE'
,p_parent_task_id => l_parent_task_verion_id
,x_return_status => l_return_status );
PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk(
p_task_element_id => l_proj_element_id
,p_project_id => x_project_id
,p_task_version_id => cur_child_tasks_rec.object_id_to1
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
select alt_task_id from pa_alternate_tasks
where proj_element_id = l_task_id
;
IF l_valid_status_tbl(i) <> 'V' THEN -- Delete records which are in-valid
l_msg_count :=1;
PA_ALTERNATE_TASK_PVT.Delete_Alt_Task(
p_Alt_Task_Id => l_alt_task_Ids,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
PA_PROJ_ELEMENTS_PKG.delete_row( x_row_id_elem );
DELETE FROM PA_PROJ_ELEMENTS WHERE PROJ_ELEMENT_ID = l_proj_element_id;
PA_USER_ATTR_PUB.DELETE_ALL_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => x_project_id
,p_proj_element_id => l_proj_element_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_TASK_PUB1.Delete_Task_Associations(
p_task_id => l_proj_element_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_selected_seq_num := cur_child_tasks_rec.display_sequence;
/* Code to delete records from tables: pa_progress_rollup and pa_precent_completes. */
/*
BEGIN
PA_PROGRESS_PUB.delete_working_wp_progress (
p_project_id => x_project_id -- Included for Better Performance : 4046005
,p_task_version_id => cur_child_tasks_rec.object_id_to1
,p_calling_context => 'TASK_VERSION'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data
);
p_procedure_name => 'Delete_Task_Version',
p_error_text => SUBSTRB('PA_PROGRESS_PUB.delete_working_wp_progress:'||SQLERRM,1,240));
PA_PROGRESS_PUB.delete_working_wp_progress (
p_project_id => x_project_id -- Included for Better Performance : 4046005
,p_task_version_id => l_task_version_id_tbl -- cur_child_tasks_rec.object_id_to1
,p_calling_context => 'TASK_VERSION'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data
);
p_procedure_name => 'Delete_Task_Version',
p_error_text => SUBSTRB('PA_PROGRESS_PUB.delete_working_wp_progress:'||SQLERRM,1,240));
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET display_sequence = PA_TASKS_MAINT_UTILS.REARRANGE_DISPLAY_SEQ(display_sequence, 0, l_task_cnt, 'DELETE', 'DOWN')
-- record_version_number = record_version_number + 1
WHERE parent_structure_version_id = x_parent_struc_ver_id
AND (display_sequence > l_selected_seq_num)
AND PA_PROJ_ELEMENTS_UTILS.link_flag ( proj_element_id ) = 'N';
update pa_proj_elem_ver_schedule
set SCHEDULED_START_DATE = trunc(sysdate),
SCHEDULED_FINISH_DATE = trunc(sysdate),
DURATION = 1,
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where project_id = l_project_id and element_version_id = p_structure_version_id;
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_project_id
,p_date_type => 'SCHEDULED'
,p_start_date => trunc(sysdate)
,p_finish_date => trunc(sysdate)
,p_record_version_number => l_rec_ver_number
,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 => p_calling_module
,p_project_id => x_project_id
,p_structure_version_id => x_parent_struc_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);
ROLLBACK to DELETE_TASK_VER_PRIVATE;
ROLLBACK to DELETE_TASK_VER_PRIVATE;
p_procedure_name => 'delete_Task_version',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_task_version;
SELECT rowid, object_type, project_id, proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = p_element_version_id;
select a.calendar_id
from pa_projects_all a, pa_proj_element_versions b
where a.project_id = b.project_id
and b.element_version_id = p_element_version_id;
SELECT project_id,proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = p_element_version_id;
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
Select Parent_Structure_Version_ID INTO l_Structure_Version_ID
From PA_Proj_Element_Versions
Where Element_Version_ID = P_Element_Version_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
);
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => x_pev_schedule_id
,X_ELEMENT_VERSION_ID => p_ELEMENT_VERSION_ID
,X_PROJECT_ID => cur_proj_elem_ver_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => cur_proj_elem_ver_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_SCHEDULED_END_DATE
,X_OBLIGATION_START_DATE => l_OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_OBLIGATION_end_DATE
,X_ACTUAL_START_DATE => l_ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ESTIMATED_FINISH_DATE
,X_DURATION => l_DURATION
,X_EARLY_START_DATE => l_EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_EARLY_end_DATE
,X_LATE_START_DATE => l_LATE_START_DATE
,X_LATE_FINISH_DATE => l_LATE_end_DATE
,X_CALENDAR_ID => l_CALENDAR_ID
,X_MILESTONE_FLAG => l_MILESTONE_FLAG
,X_CRITICAL_FLAG => l_CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_wq_planned_quantity
,X_PLANNED_EFFORT => l_planned_effort
,X_ACTUAL_DURATION => l_act_duration
,X_ESTIMATED_DURATION => l_est_duration
,X_def_sch_tool_tsk_type_code => l_def_sch_tool_tsk_type_code
,X_constraint_type_code => l_constraint_type_code
,X_constraint_date => l_constraint_date
,X_free_slack => l_free_slack
,X_total_slack => l_total_slack
,X_effort_driven_flag => l_effort_driven_flag
,X_level_assignments_flag => l_level_assignments_flag
,X_ext_act_duration => l_ext_act_duration
,X_ext_remain_duration => l_ext_remain_duration
,X_ext_sch_duration => l_ext_sch_duration
,X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ATTRIBUTE1
,X_ATTRIBUTE2 => l_ATTRIBUTE2
,X_ATTRIBUTE3 => l_ATTRIBUTE3
,X_ATTRIBUTE4 => l_ATTRIBUTE4
,X_ATTRIBUTE5 => l_ATTRIBUTE5
,X_ATTRIBUTE6 => l_ATTRIBUTE6
,X_ATTRIBUTE7 => l_ATTRIBUTE7
,X_ATTRIBUTE8 => l_ATTRIBUTE8
,X_ATTRIBUTE9 => l_ATTRIBUTE9
,X_ATTRIBUTE10 => l_ATTRIBUTE10
,X_ATTRIBUTE11 => l_ATTRIBUTE11
,X_ATTRIBUTE12 => l_ATTRIBUTE12
,X_ATTRIBUTE13 => l_ATTRIBUTE13
,X_ATTRIBUTE14 => l_ATTRIBUTE14
,X_ATTRIBUTE15 => l_ATTRIBUTE15
,X_SOURCE_OBJECT_ID => cur_proj_elem_ver_rec.PROJECT_ID
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
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_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_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
CURSOR cur_proj_elem_ver_sch
IS
SELECT rowid, element_version_id, project_id, proj_element_id, record_version_number
FROM pa_proj_elem_ver_schedule
WHERE pev_schedule_id = p_pev_schedule_id;
select ppru.CUMULATIVE_WORK_QUANTITY
from pa_progress_rollup ppru, pa_percent_completes ppc
where ppc.project_id = c_project_id
and ppc.task_id = c_element_id
and ppc.current_flag = 'Y'
and ppc.project_Id = ppru.project_id
and ppc.task_id = ppru.object_id
and ppru.object_type = 'PA_TASKS';
select proj_element_id, project_id
from pa_proj_elem_ver_schedule
where pev_schedule_id = p_pev_schedule_id;
select *
from pa_proj_elem_ver_schedule
where pev_schedule_id = p_pev_schedule_id;
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
l_update_flag VARCHAR2(1) := 'N';
select parent_structure_version_id
from pa_proj_element_versions
where element_version_id = c_elem_ver_id;
l_update_effort_flag VARCHAR2(1) := 'N';
l_schedule_update_flag VARCHAR2(1) := 'N';
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION begin',3);
savepoint UPDATE_SCH_VER_PRIVATE;
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'Performing validations',3);
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
IF PA_PROJECT_PUB.G_IS_SUB_PROJ_ASSOCIATED IS NULL THEN
l_task_has_sub_proj_flag := PA_RELATIONSHIP_UTILS.check_task_has_sub_proj( cur_val_rec.project_id,
cur_val_rec.proj_element_id, cur_val_rec.element_version_id);
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'l_task_has_sub_proj_flag='||l_task_has_sub_proj_flag,3);
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'p_calling_module='||p_calling_module,3);
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'cur_val_rec.scheduled_START_DATE='|| cur_val_rec.scheduled_START_DATE,3);
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'cur_val_rec.scheduled_FINISH_DATE='||cur_val_rec.scheduled_FINISH_DATE,3);
pa_debug.write('PA_TASK_PVT1.UPDATE_SCHEDULE_VERSION', 'cur_val_rec.PLANNED_EFFORT='||cur_val_rec.PLANNED_EFFORT,3);
l_schedule_update_flag := 'Y';
l_update_flag := 'Y';
l_schedule_update_flag := 'Y';
l_update_flag := 'Y';
l_update_flag := 'Y';
l_update_effort_flag := 'Y';
l_update_flag := 'Y';
l_update_effort_flag := 'Y'; --06/03
l_update_flag := 'Y';
IF (l_update_flag = 'Y' OR l_update_effort_flag = 'Y' ) THEN -- 4299499 Included OR l_update_effort_flag = 'Y'
--update the process flag
open get_parent_struc_ver_id(cur_val_rec.element_version_id);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG(
p_project_id => cur_val_rec.project_id,
p_structure_version_id => l_parent_struc_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 => 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
);
SELECT financial_task_flag into l_financial_task_flag_tmp
FROM pa_proj_element_versions
WHERE element_version_id = cur_proj_elem_ver_sch_rec.element_version_id;
SELECT a.wp_enable_version_flag
INTO l_workplan_version_enable_flag
FROM pa_proj_workplan_attr a, pa_proj_structure_types b
WHERE a.proj_element_id = b.proj_element_id
AND a.project_id = cur_proj_elem_ver_sch_rec.project_id
AND structure_type_id = 1;
SELECT proj_element_id into l_proj_element_id_temp
FROM pa_proj_element_versions
WHERE element_version_id = PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(cur_proj_elem_ver_sch_rec.project_id);
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
IF PA_PROJECT_PUB.G_STRUC_ELEMENT_ID IS NULL THEN
SELECT pev.proj_element_id
INTO l_proj_element_id_temp
FROM pa_proj_element_versions pev, pa_proj_structure_types pst
WHERE pev.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = 1
AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
SELECT pev.proj_element_id
INTO l_proj_element_id_temp
FROM pa_proj_element_versions pev, pa_proj_structure_types pst
WHERE pev.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = 1
AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
SELECT pev.proj_element_id
INTO l_proj_element_id_temp
FROM pa_proj_element_versions pev, pa_proj_structure_types pst
WHERE pev.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = 1
AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
SELECT pev.proj_element_id
INTO l_proj_element_id_temp
FROM pa_proj_element_versions pev, pa_proj_structure_types pst
WHERE pev.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = 1
AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
PA_PROJ_ELEMENT_SCH_PKG.Update_Row(
X_ROW_ID => cur_proj_elem_ver_sch_rec.rowid
,X_PEV_SCHEDULE_ID => p_pev_schedule_id
,X_ELEMENT_VERSION_ID => cur_proj_elem_ver_sch_rec.ELEMENT_VERSION_ID
,X_PROJECT_ID => cur_proj_elem_ver_sch_rec.PROJECT_ID
,X_PROJ_ELEMENT_ID => cur_proj_elem_ver_sch_rec.PROJ_ELEMENT_ID
,X_SCHEDULED_START_DATE => l_SCHEDULED_START_DATE
,X_SCHEDULED_FINISH_DATE => l_SCHEDULED_END_DATE
,X_OBLIGATION_START_DATE => l_OBLIGATION_START_DATE
,X_OBLIGATION_FINISH_DATE => l_OBLIGATION_end_DATE
,X_ACTUAL_START_DATE => l_ACTUAL_START_DATE
,X_ACTUAL_FINISH_DATE => l_ACTUAL_FINISH_DATE
,X_ESTIMATED_START_DATE => l_ESTIMATED_START_DATE
,X_ESTIMATED_FINISH_DATE => l_ESTIMATED_FINISH_DATE
,X_DURATION => l_DURATION
,X_EARLY_START_DATE => l_EARLY_START_DATE
,X_EARLY_FINISH_DATE => l_EARLY_end_DATE
,X_LATE_START_DATE => l_LATE_START_DATE
,X_LATE_FINISH_DATE => l_LATE_end_DATE
,X_CALENDAR_ID => l_CALENDAR_ID
,X_MILESTONE_FLAG => l_MILESTONE_FLAG
,X_CRITICAL_FLAG => l_CRITICAL_FLAG
,X_WQ_PLANNED_QUANTITY => l_wq_planned_quantity
,x_planned_effort => l_planned_effort
,X_ACTUAL_DURATION => l_act_duration
,X_ESTIMATED_DURATION => l_est_duration
,X_def_sch_tool_tsk_type_code => l_def_sch_tool_tsk_type_code
,X_constraint_type_code => l_constraint_type_code
,X_constraint_date => l_constraint_date
,X_free_slack => l_free_slack
,X_total_slack => l_total_slack
,X_effort_driven_flag => l_effort_driven_flag
,X_level_assignments_flag => l_level_assignments_flag
,X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER
,X_ext_act_duration => l_ext_act_duration
,X_ext_remain_duration => l_ext_remain_duration
,X_ext_sch_duration => l_ext_sch_duration
,X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => l_ATTRIBUTE1
,X_ATTRIBUTE2 => l_ATTRIBUTE2
,X_ATTRIBUTE3 => l_ATTRIBUTE3
,X_ATTRIBUTE4 => l_ATTRIBUTE4
,X_ATTRIBUTE5 => l_ATTRIBUTE5
,X_ATTRIBUTE6 => l_ATTRIBUTE6
,X_ATTRIBUTE7 => l_ATTRIBUTE7
,X_ATTRIBUTE8 => l_ATTRIBUTE8
,X_ATTRIBUTE9 => l_ATTRIBUTE9
,X_ATTRIBUTE10 => l_ATTRIBUTE10
,X_ATTRIBUTE11 => l_ATTRIBUTE11
,X_ATTRIBUTE12 => l_ATTRIBUTE12
,X_ATTRIBUTE13 => l_ATTRIBUTE13
,X_ATTRIBUTE14 => l_ATTRIBUTE14
,X_ATTRIBUTE15 => l_ATTRIBUTE15
);
('Y' = l_schedule_update_flag ) THEN
--bug 4153366
IF (p_calling_module = 'AMG')
THEN
l_assgn_context := 'INSERT_VALUES';
l_assgn_context := 'UPDATE';
l_update_effort_flag := 'N';
/*moved pa_fp_planning_transaction_pub.update_planning_transactions into plsql block */
BEGIN
IF l_update_effort_flag = 'Y'
THEN
pa_fp_planning_transaction_pub.update_planning_transactions
(
p_context => 'WORKPLAN'
-- ,p_maintain_reporting_lines => 'Y'
,p_struct_elem_version_id => l_parent_struc_ver_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_schedule_version',
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.update_planning_transactions:'||SQLERRM,1,240));
ROLLBACK to UPDATE_SCH_VER_PRIVATE;
ROLLBACK to UPDATE_SCH_VER_PRIVATE;
p_procedure_name => 'update_schedule_version',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Schedule_Version;
select c.TYPE_ID , c.WQ_ITEM_CODE, c.WQ_UOM_CODE, c.WQ_ACTUAL_ENTRY_CODE,
c.TASK_PROGRESS_ENTRY_PAGE_ID
from pa_proj_element_versions a,
pa_object_relationships b,
pa_proj_elements c
where b.object_id_to1 = p_task_version_id
and b.object_type_to = 'PA_TASKS'
and b.relationship_type = 'S'
and b.object_type_from = 'PA_TASKS'
and b.object_id_from1 = a.element_version_id
and a.project_id = c.project_id
and a.proj_element_id = c.proj_element_id;
select c.TYPE_ID , c.WQ_ITEM_CODE, c.WQ_UOM_CODE, c.WQ_ACTUAL_ENTRY_CODE,
c.TASK_PROGRESS_ENTRY_PAGE_ID
from pa_proj_elements c
where c.proj_element_id = p_task_id;
UPDATE pa_proj_elements set
-- task_weighting_deriv_code = l_parent_type_attr_rec.TASK_WEIGHTING_DERIV_CODE
WQ_ITEM_CODE = l_parent_type_attr_rec.WQ_ITEM_CODE
,WQ_UOM_CODE = l_parent_type_attr_rec.WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE = l_parent_type_attr_rec.WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID = l_parent_type_attr_rec.TASK_PROGRESS_ENTRY_PAGE_ID
where proj_element_id = p_task_id;
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
l_weighting_percentage NUMBER;
SELECT
OBJECT_RELATIONSHIP_ID
,RELATIONSHIP_TYPE
,RELATIONSHIP_SUBTYPE
,LAG_DAY
,PRIORITY
,PM_PRODUCT_CODE
FROM pa_object_relationships
WHERE OBJECT_RELATIONSHIP_ID = c_object_relationship_id;
pa_debug.debug('PA_TASK_PVT1.Update_Task_Weighting begin');
savepoint update_task_weighting_pvt;
PA_OBJECT_RELATIONSHIPS_PKG.UPDATE_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_relationship_id => p_object_relationship_id
,p_relationship_type => cur_obj_relationships_rec.relationship_type
,p_relationship_subtype => cur_obj_relationships_rec.relationship_subtype
,p_lag_day => cur_obj_relationships_rec.lag_day
,p_priority => cur_obj_relationships_rec.priority
,p_pm_product_code => cur_obj_relationships_rec.pm_product_code
,p_weighting_percentage => l_weighting_percentage
,p_comments => NULL
,p_status_code => NULL
,p_record_version_number => p_record_version_number
,x_return_status => x_return_status
);
ROLLBACK to update_task_weighting_pvt;
ROLLBACK to update_task_weighting_pvt;
p_procedure_name => 'Update_Task_Weighting',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_TASK_WEIGHTING;
select sum(weighting_percentage)
from pa_object_relationships
where object_id_from1 = p_task_version_id
and object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
and relationship_type = 'S';
select object_relationship_id, weighting_percentage,
record_version_number, object_id_to1
from pa_object_relationships
where object_id_from1 = p_task_version_id
and object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
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;
PA_TASK_PVT1.Update_Task_Weighting(
p_object_relationship_id => l_object_relationship_id
,p_weighting_percentage => l_weighting
,p_record_version_number => l_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_TASK_PVT1.Update_Task_Weighting(
p_object_relationship_id => l_current_obj_rel_id
,p_weighting_percentage => l_current_weighting + l_remainder
,p_record_version_number => l_current_rec_ver_number + 1
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PROCEDURE update_task_ver_delete_status(
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_task_version_id 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
CURSOR get_task_versions IS
select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
and object_type_from = 'PA_TASKS'
and object_type_to = 'PA_TASKS'
and relationship_type = 'S'
start with object_id_to1 = p_task_version_id
connect by prior object_id_to1 = object_id_from1
and relationship_type = prior relationship_type --bug 3919266
and relationship_type = 'S'
and prior object_type_to = object_type_from;
UPDATE pa_proj_element_versions
set TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED',
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where element_version_id = p_task_version_id;
UPDATE pa_proj_element_versions
set TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED',
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where element_version_id = l_task_version_id;
p_procedure_name => 'update_task_ver_delete_status',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_task_ver_delete_status;
procedure delete_task_ver_wo_val
(
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,
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;
l_delete_flag VARCHAR2(1) := 'N';
SELECT a.object_relationship_id object_relationship_id, a.object_id_to1 object_id_to1,
a.record_version_number record_version_number, b.wbs_level wbs_level, b.display_sequence
FROM (
SELECT object_relationship_id, object_id_to1, record_version_number
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = p_task_version_id
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = PRIOR relationship_type --bug 3919266
AND relationship_type = 'S' ) A, pa_proj_element_versions b
,pa_proj_elements c --bug 4573340
WHERE a.object_id_to1 = b.element_version_id
--bug 4573340
and b.proj_element_id = c.proj_element_id
and b.project_id = c.project_id
and c.link_task_flag = 'N'
--bug 4573340
UNION
SELECT a.object_relationship_id, element_version_id object_id_to1,
a.record_version_number, wbs_level, b.display_sequence
FROM pa_object_relationships a, pa_proj_element_versions b
WHERE element_version_id = p_task_version_id
AND object_id_to1 = p_task_version_id
AND element_version_id = object_id_to1
AND relationship_type = 'S'
ORDER BY 4 desc;
SELECT rowid,record_version_number, project_id, parent_structure_version_id, proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = p_task_id;
SELECT rowid
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = p_task_id
AND project_id = p_project_id;
SELECT wbs_record_version_number
-- HY: changed from pa_projects_all to pa_proj_elem_ver_structure
FROM pa_proj_elem_ver_structure
-- HY FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT record_version_number
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT 'X'
FROM pa_proj_element_versions
WHERE proj_element_id = x_proj_element_id
AND element_version_id <> x_task_version;
SELECT rowid
FROM pa_proj_elements
WHERE proj_element_id = x_proj_element_id;
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_task_version_id
AND relationship_type = 'S';
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 object_id_from1
from pa_object_relationships
where object_id_to1 = p_task_version_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S'
and object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
l_selected_seq_num NUMBER;
select b.parent_structure_version_id, b.element_version_id
from pa_proj_element_versions b,
pa_proj_elem_ver_structure c
where b.element_version_id = p_task_version_id
and b.project_id = c.project_id
and b.parent_structure_version_id = c.element_version_id
and c.LATEST_EFF_PUBLISHED_FLAG = 'Y';
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
SELECT object_id_to1
FROM pa_object_relationships
--WHERE object_id_from1 = p_task_version_id --4173785
WHERE object_id_from1 = c_task_version_id
AND relationship_type = 'M';
SELECT pors.object_relationship_id,pors.record_version_number,pors.object_id_to2 --pors.object_id_to2 is added for the Bug 14591859
FROM pa_object_relationships pors
--bug 4573340
,pa_object_relationships pors1
WHERE
--pors.object_id_from1= cp_task_ver_id
pors1.object_id_from1 = cp_task_ver_id
AND pors.object_id_from1 = pors1.object_id_to1
AND pors1.relationship_type = 'S'
--bug 4573340
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'
order by pors.object_id_to2; --order by clause is added for the Bug 14591859
pa_debug.debug('PA_TASK_PVT1.DELETE_TASK_VER_WO_VAL begin');
savepoint DELETE_TASK_VER_WO_VAL;
PA_PROJ_ELEMENT_SCH_PKG.Delete_row( x_sch_row_id );
Here,the call to delete planning txn has been moved out of the loop
and is done after call to pa_project_core.delete_task .
This is problematic as described in 4317547
--bug Bug # : 4172646
-- Moved the call delete planning txn after the loop once for all tasks.
l_call_del_plan_txn := 'Y';
PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_element_version_id_tbl
-- ,p_maintain_reporting_lines => 'Y'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
p_procedure_name => 'delete_Task_ver_wo_val',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions:'||SQLERRM,1,240));
PA_PROJ_STRUC_MAPPING_PUB.delete_mapping
(
p_wp_task_version_id => cur_child_tasks_rec.object_id_to1
, p_fp_task_version_id => l_fp_task_version_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
SELECT proj_element_id INTO l_structure_id
FROM pa_proj_element_versions
WHERE element_version_id = x_parent_struc_ver_id
AND project_id = x_project_id;
l_delete_flag := 'Y';
IF l_delete_flag = 'Y' THEN
OPEN cur_pa_projs( x_project_id );
PA_PROJECT_CORE.Delete_Task(
x_task_id => l_proj_element_id,
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
END IF; --delete flag chk
pa_debug.write('PA_TASK_PVT1.DELETE_TASK_VER_WO_VAL', 'Before PA_RELATIONSHIP_PUB.Delete_SubProject_Association get_subproj_relation_rec.object_relationship_id='
||get_subproj_relation_rec.object_relationship_id, 3);
pa_debug.write('PA_TASK_PVT1.DELETE_TASK_VER_WO_VAL', 'Before PA_RELATIONSHIP_PUB.Delete_SubProject_Association get_subproj_relation_rec.record_version_number='
||get_subproj_relation_rec.record_version_number, 3);
PA_RELATIONSHIP_PUB.Delete_SubProject_Association --bug 4573340 replaced the call with delete_subproject
(
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_relationships_id => get_subproj_relation_rec.object_relationship_id
,p_record_version_number => get_subproj_relation_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.write('PA_TASK_PVT1.DELETE_TASK_VER_WO_VAL', 'After PA_RELATIONSHIP_PUB.Delete_SubProject_Association l_return_status='||l_return_status, 3);
PA_RELATIONSHIP_PUB.Delete_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_child_tasks_rec.object_relationship_id
,p_record_version_number => cur_child_tasks_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_ELEMENT_VERSIONS_PKG.Delete_Row( x_row_id );
PA_PROJ_ELEMENTS_UTILS.UPDATE_WBS_NUMBERS (
p_commit => p_commit
,p_debug_mode => p_debug_mode
,p_parent_structure_ver_id => x_parent_struc_ver_id
,p_task_id => null
,p_display_seq => cur_child_tasks_rec.display_sequence
,p_action => 'DELETE'
,p_parent_task_id => l_parent_task_verion_id
,x_return_status => l_return_status );
PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk(
p_task_element_id => l_proj_element_id
,p_project_id => x_project_id
,p_task_version_id => cur_child_tasks_rec.object_id_to1
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
PA_PROJ_ELEMENTS_PKG.delete_row( x_row_id_elem );
PA_USER_ATTR_PUB.DELETE_ALL_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => x_project_id
,p_proj_element_id => l_proj_element_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_TASK_PUB1.Delete_Task_Associations(
p_task_id => l_proj_element_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_selected_seq_num := cur_child_tasks_rec.display_sequence;
PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_element_version_id_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
p_procedure_name => 'delete_Task_ver_wo_val',
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions:'||SQLERRM,1,240));
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET display_sequence = PA_TASKS_MAINT_UTILS.REARRANGE_DISPLAY_SEQ(display_sequence, 0, l_task_cnt, 'DELETE', 'DOWN')
-- record_version_number = record_version_number + 1
WHERE parent_structure_version_id = x_parent_struc_ver_id
AND (display_sequence > l_selected_seq_num)
AND PA_PROJ_ELEMENTS_UTILS.link_flag ( proj_element_id ) = 'N';
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => p_calling_module
,p_project_id => x_project_id
,p_structure_version_id => x_parent_struc_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);
ROLLBACK to DELETE_TASK_VER_WO_VAL;
ROLLBACK to DELETE_TASK_VER_WO_VAL;
p_procedure_name => 'delete_Task_ver_wo_val',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_task_ver_wo_val;
procedure UPDATE_WORKING_VER_WEIGHT(
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_task_id IN NUMBER
,p_weighting 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
CURSOR get_working_ver(c_option NUMBER) IS
select a.element_version_id, c.object_id_from1
from pa_proj_element_versions a,
pa_proj_elem_ver_structure b,
pa_object_relationships c
where a.parent_structure_version_id = b.element_version_id
and a.project_id = b.project_id
and a.proj_element_id = p_task_id
and a.element_version_id = c.object_id_to1
and c.object_type_to = 'PA_TASKS'
and c.relationship_type = 'S'
and b.status_code <> 'STRUCTURE_PUBLISHED'
and 1 = c_option
UNION
select a.element_version_id, c.object_id_from1
from pa_proj_element_versions a,
pa_proj_elem_ver_structure b,
pa_object_relationships c
where a.parent_structure_version_id = b.element_version_id
and a.project_id = b.project_id
and a.proj_element_id = p_task_id
and a.element_version_id = c.object_id_to1
and c.object_type_to = 'PA_TASKS'
and c.relationship_type = 'S'
and b.status_code = 'STRUCTURE_PUBLISHED'
and 2 = c_option;
select proj.project_id,proj.template_flag -- the project template flag also.
from pa_proj_elements ele, pa_projects_all proj
where ele.proj_element_id = p_task_id
and ele.project_id = proj.project_id;
update pa_object_relationships
set WEIGHTING_PERCENTAGE = p_weighting
where object_id_to1 = l_working_ver_id
and relationship_type = 'S'
and object_type_to = 'PA_TASKS';
p_procedure_name => 'UPDATE_WORKING_VER_WEIGHT',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_WORKING_VER_WEIGHT;
SELECT proj_element_id, element_version_id
FROM pa_proj_element_versions ppev,
( SELECT object_id_to1 from pa_object_relationships
WHERE relationship_type = 'S'
start with object_id_from1 = ( SELECT element_version_id
FROM pa_proj_element_versions ppev
WHERE ppev.proj_element_id = p_task_id
AND ppev.parent_structure_version_id = c_parent_structure_version_id
AND project_id = p_project_id
AND object_type = 'PA_TASKS')
connect by object_id_from1 = prior object_id_to1
and relationship_type = prior relationship_type --bug 3919266
and relationship_type = 'S'
) pobj
WHERE ppev.element_version_id = pobj.object_id_to1;
SELECT element_version_id
FROM pa_proj_elem_ver_structure
WHERE project_id = p_project_id
AND status_code <> 'STRUCTURE_PUBLISHED';
SELECT element_version_id FROM pa_proj_element_versions WHERE parent_structure_version_id = c_parent_structure_version_id;
UPDATE pa_proj_element_versions
SET TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED'
WHERE element_version_id = wrk_task_ids(j).task_version_id
;
PROCEDURE Update_Dates_To_All_Versions(
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_project_id IN NUMBER,
p_element_version_id 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
CURSOR cur_proj_elem_ver_sch(c_elem_ver_id number, c_project_id NUMBER)
IS
SELECT element_version_id, project_id, proj_element_id, actual_start_date, actual_finish_date,
estimated_start_date, estimated_finish_date, actual_duration, estimated_duration
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_elem_ver_id
and project_id = c_project_id;
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
AND object_type_to = 'PA_TASKS'
AND relationship_type ='S'
CONNECT BY PRIOR OBJECT_ID_FROM1 = OBJECT_ID_TO1
AND PRIOR relationship_type = relationship_type --bug 3919266
AND relationship_type = 'S'
START WITH OBJECT_ID_TO1 = c_elem_ver_id ;
pa_debug.debug('PA_TASK_PVT1.Update_Dates_To_All_Versions begin');
savepoint UPDATE_SCH_VER_PRIVATE_DATES;
UPDATE pa_proj_elem_ver_schedule
SET actual_start_date = cur_proj_elem_ver_sch_rec.actual_start_date
, actual_finish_date = cur_proj_elem_ver_sch_rec.actual_finish_date
, estimated_start_date = cur_proj_elem_ver_sch_rec.estimated_start_date
, estimated_finish_date = cur_proj_elem_ver_sch_rec.estimated_finish_date
, actual_duration = cur_proj_elem_ver_sch_rec.actual_duration
, estimated_duration = cur_proj_elem_ver_sch_rec.estimated_duration
WHERE proj_element_id = cur_proj_elem_ver_sch_rec.proj_element_id
and project_id = cur_proj_elem_ver_sch_rec.project_id;
UPDATE pa_proj_elem_ver_schedule
SET actual_start_date = cur_proj_elem_ver_sch_rec.actual_start_date
, actual_finish_date = cur_proj_elem_ver_sch_rec.actual_finish_date
, estimated_start_date = cur_proj_elem_ver_sch_rec.estimated_start_date
, estimated_finish_date = cur_proj_elem_ver_sch_rec.estimated_finish_date
, actual_duration = cur_proj_elem_ver_sch_rec.actual_duration
, estimated_duration = cur_proj_elem_ver_sch_rec.estimated_duration
WHERE proj_element_id = cur_proj_elem_ver_sch_rec.proj_element_id
and project_id = cur_proj_elem_ver_sch_rec.project_id;
ROLLBACK to UPDATE_SCH_VER_PRIVATE_DATES;
ROLLBACK to UPDATE_SCH_VER_PRIVATE_DATES;
p_procedure_name => 'Update_Dates_To_All_Versions',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Dates_To_All_Versions;
SELECT project_id ,
task_id ,
parent_task_id ,
element_version_id ,
display_sequence ,
level_sequence ,
sub_level_seq
FROM pa_proj_elem_ver_tmp
ORDER BY display_sequence ,
level_sequence ,
sub_level_Seq;
SELECT -1 * ( PA_PROJ_ELEMENT_VERSIONS_S.nextval ) INTO t_seq FROM dual;
UPDATE pa_proj_element_versions a
SET a.display_sequence =
(SELECT b.display_sequence
FROM pa_proj_element_versions b
WHERE b.element_version_id = p_ref_elem_ver_id
),
a.level_sequence = t_seq
WHERE a.element_version_id = p_elem_ver_id ;
SELECT b.display_sequence ,
b.level_sequence
INTO t_disp_seq,
t_level_seq
FROM pa_proj_element_versions b
WHERE b.element_version_id = p_ref_elem_ver_id ;
UPDATE pa_proj_element_versions a
SET a.display_sequence = t_disp_seq ,
a.level_sequence = t_level_seq
WHERE a.element_version_id = p_elem_ver_id ;
delete from pa_proj_elem_ver_tmp;
INSERT
INTO pa_proj_elem_ver_tmp
(
project_id ,
task_id ,
parent_task_id ,
element_version_id ,
display_sequence ,
level_sequence ,
sub_level_seq
)
SELECT a.project_id ,
a.task_id ,
a.parent_task_id ,
b.element_version_id,
b.display_sequence ,
b.level_sequence,
DECODE( b.element_version_id, P_ELEM_VER_ID , 1 , 0 )
FROM pa_tasks a,
pa_proj_element_versions b
WHERE a.task_id = b.proj_element_id
AND b.parent_structure_version_id = p_parent_struct_ver_id
AND b.display_sequence = t_disp_seq;
UPDATE pa_proj_element_versions a
SET a.level_sequence = t_seq
WHERE a.element_version_id = c1_rec.element_version_id ;
SELECT
/*+ ordered */
ppevs.element_version_id elem_ver_id ,
por.object_id_from1 parent_elem_ver_id ,
ppevs.duration ,
ptt.prog_entry_enable_flag prog_flag ,
ppev.object_type
FROM pa_proj_element_versions ppev,
pa_object_relationships por,
pa_proj_elem_ver_schedule ppevs,
pa_proj_elements ppe,
pa_task_types ptt
WHERE ppev.parent_structure_version_id = c_parent_struc_ver_id -- 377522
AND por.object_type_to = 'PA_TASKS'
AND por.relationship_type = 'S'
AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND ppev.element_version_id = por.object_id_to1
AND ppev.proj_element_id = ppe.proj_element_id
AND ppev.element_version_id = ppevs.element_version_id
--AND ppe.project_id = 27049
AND ppe.TYPE_ID = ptt.task_type_id
AND ptt.object_type = 'PA_TASKS' ;
l_data.delete;
UPDATE pa_object_relationships por
SET weighting_percentage = l_arr_elem_weight_02(i)
-- SET weighting_percentage = ( l_arr_elem_duration(l_arr_elem_ver_id(i)) / l_arr_parent_sum_duration(l_arr_parent_ver_id(l_arr_elem_ver_id(i)))) * 100
WHERE object_id_to1 = l_arr_elem_ver_id(i)
AND por.relationship_type = 'S'
AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS') ;