[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code <> 'STRUCTURE_PUBLISHED';
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => X_row_id
--,X_ELEMENT_VERSION_ID => x_struc_version_id * Commented for Bug Fix: 4537865
,X_ELEMENT_VERSION_ID =>l_new_struc_version_id -- added for Bug fix: 4537865
,X_PROJ_ELEMENT_ID => x_structure_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => p_dest_project_id
,X_PARENT_STRUCTURE_VERSION_ID => x_struc_version_id
,X_DISPLAY_SEQUENCE => null
,X_WBS_LEVEL => null
,X_WBS_NUMBER => null
,X_ATTRIBUTE_CATEGORY => null
,X_ATTRIBUTE1 => null
,X_ATTRIBUTE2 => null
,X_ATTRIBUTE3 => null
,X_ATTRIBUTE4 => null
,X_ATTRIBUTE5 => null
,X_ATTRIBUTE6 => null
,X_ATTRIBUTE7 => null
,X_ATTRIBUTE8 => null
,X_ATTRIBUTE9 => null
,X_ATTRIBUTE10 => null
,X_ATTRIBUTE11 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_TASK_UNPUB_VER_STATUS_CODE => null
,X_SOURCE_OBJECT_ID => p_dest_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
X_ROWID => x_row_id
, X_PEV_STRUCTURE_ID => x_pev_structure_id
, X_ELEMENT_VERSION_ID => x_struc_version_id
, X_VERSION_NUMBER => l_struc_ver_number
, X_NAME => l_name
, X_PROJECT_ID => p_dest_project_id
, X_PROJ_ELEMENT_ID => x_structure_id
, X_DESCRIPTION => p_dest_description
, X_EFFECTIVE_DATE => null
, X_PUBLISHED_DATE => null
, X_PUBLISHED_BY => null
, X_CURRENT_BASELINE_DATE => null
, X_CURRENT_BASELINE_FLAG => 'N'
, X_CURRENT_BASELINE_BY => null
, X_ORIGINAL_BASELINE_DATE => null
, X_ORIGINAL_BASELINE_FLAG => 'N'
, X_ORIGINAL_BASELINE_BY => null
, X_LOCK_STATUS_CODE => NULL
, X_LOCKED_BY => NULL
, X_LOCKED_DATE => NULL
, X_STATUS_CODE => l_status_code
, X_WF_STATUS_CODE => NULL
, X_LATEST_EFF_PUBLISHED_FLAG => 'N'
, X_CHANGE_REASON_CODE => NULL
, X_RECORD_VERSION_NUMBER => 1
, X_CURRENT_WORKING_FLAG => 'N' /* Not applicable to default financial str. bug 3301192 */
, X_SOURCE_OBJECT_ID => p_dest_project_id
, X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT distinct top_task_id
FROM pa_tasks pt
WHERE project_id = p_project_id;
SELECT task_id, wbs_level, parent_task_id, task_number, task_name, description, carrying_out_organization_id
FROM pa_tasks pt
WHERE project_id = p_project_id
START WITH task_id = c_top_task_id
CONNECT BY PRIOR task_id = parent_task_id;
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_task_version_id
and object_type_from in ('PA_STRUCTURES','PA_TASKS') -- bug 6429275
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT 'x'
FROM pa_projects_all
WHERE split_cost_from_workplan_flag = 'Y'
AND project_id = p_project_id;
SELECT 'x'
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = 'WORKPLAN'
AND element_version_id = c_strcu_ver_id;
SELECT *
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_element_version_id
AND project_id = p_project_id
;
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = c_project_id
and c.structure_type_class_code = c_struc_type
and c.structure_type_id = b.structure_type_id
and b.proj_element_id = a.proj_element_id;
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppev.project_id = p_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.proj_element_id = ppst.proj_element_id
AND pst.structure_type_id = ppst.structure_type_id
AND pst.structure_type = p_struc_type;
SELECT pa_tasks_s.nextval INTO l_proj_element_id
FROM sys.dual;
PA_PROJ_ELEMENTS_PKG.Insert_Row(
X_ROW_ID => X_ROW_ID
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_TYPE => 'PA_TASKS'
,X_ELEMENT_NUMBER => cur_pa_tasks_rec.task_number||l_postfix
,X_NAME => cur_pa_tasks_rec.task_name
,X_DESCRIPTION => cur_pa_tasks_rec.description
,X_STATUS_CODE => '124' --- NOT_STARTED -- 124 bug 2826235
,X_WF_STATUS_CODE => null
,X_PM_PRODUCT_CODE => null
,X_PM_TASK_REFERENCE => null
,X_CLOSED_DATE => null
,X_LOCATION_ID => null
,X_MANAGER_PERSON_ID => null
,X_CARRYING_OUT_ORGANIZATION_ID => cur_pa_tasks_rec.carrying_out_organization_id
,X_TYPE_ID => 1
,X_PRIORITY_CODE => null
,X_INC_PROJ_PROGRESS_FLAG => 'N'
,X_REQUEST_ID => null
,X_PROGRAM_APPLICATION_ID => null
,X_PROGRAM_ID => null
,X_PROGRAM_UPDATE_DATE => null
,X_LINK_TASK_FLAG => 'N'
,X_ATTRIBUTE_CATEGORY => null
,X_ATTRIBUTE1 => null
,X_ATTRIBUTE2 => null
,X_ATTRIBUTE3 => null
,X_ATTRIBUTE4 => null
,X_ATTRIBUTE5 => null
,X_ATTRIBUTE6 => null
,X_ATTRIBUTE7 => null
,X_ATTRIBUTE8 => null
,X_ATTRIBUTE9 => null
,X_ATTRIBUTE10 => null
,X_ATTRIBUTE11 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_TASK_WEIGHTING_DERIV_CODE => NULl
,X_WORK_ITEM_CODE => NULL
,X_UOM_CODE => NULL
,X_WQ_ACTUAL_ENTRY_CODE => NULL
,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
,X_PARENT_STRUCTURE_ID => l_structure_id
,X_PHASE_CODE => NULL
,X_PHASE_VERSION_ID => NULL
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
IS select 'Y'
from pa_projects_all
where project_id = p_project_id
and template_flag = 'Y';
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
AND ( ( proj_element_id = ( select top_task_id
from pa_tasks
where task_id = p_ref_task_id ) AND p_structure_type = 'FINANCIAL' )
--OR ( proj_element_id = p_ref_task_id AND p_structure_type = 'WORKPLAN' )
)
--bug 2852891
UNION
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
AND p_structure_type = 'WORKPLAN'
AND display_sequence = ( SELECT max( display_sequence )
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
AND display_sequence <= ( SELECT display_sequence
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
AND proj_element_id = p_ref_task_id )
)
--bug 2852891
;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
AND proj_element_id = p_ref_task_id
;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
and element_version_id in ( SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = c_parent_task_version_id
and relationship_type = 'S'
)
AND display_sequence = ( SELECT max( display_sequence )
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = c_structure_version_id
and element_version_id in ( SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = c_parent_task_version_id
and relationship_type = 'S'
)
);
SELECT 'x'
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = 'WORKPLAN'
AND element_version_id = ( SELECT parent_structure_version_id
FROM pa_proj_element_versions
WHERE element_version_id = c_task_version_id );
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND proj_element_id = c_task_id
AND parent_structure_version_id = c_structure_version_id
AND object_type = 'PA_TASKS';
select 'Y'
FROM pa_object_relationships
where object_id_from1 = c_task_ver_id
AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES') --Corrected 'PA_TASK' TO 'PA_TASKS' for Bug 4035526
and relationship_type = 'S';
SELECT *
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_element_version_id
and project_id = p_project_id
;
SELECT 'X' from PA_TEMP_IMPORT_TASKS
WHERE project_id = p_project_id;
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_PUBLISHED'
AND d.latest_eff_published_flag = 'Y'
;
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_WORKING'
;
select a.proj_element_id
from pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
where a.project_id = c_project_id
and c.structure_type_class_code = c_struc_type
and c.structure_type_id = b.structure_type_id
and b.proj_element_id = a.proj_element_id;
select object_id_from1
from pa_object_relationships
where object_id_to1 = c_elem_ver_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S';
SELECT wbs_level
FROM pa_proj_element_versions
WHERE element_version_id = c_element_version_id;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
WHERE parent_structure_version_id = c_structure_version_id
AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
WHERE element_version_id = c_element_version_id )
AND wbs_level = c_wbs_level )
AND parent_structure_version_id = c_structure_version_id;
select AUTO_PUB_UPON_CREATION_FLAG
from pa_proj_workplan_attr
where project_id = p_project_id;
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND (d.status_code = 'STRUCTURE_SUBMITTED' OR d.status_code='STRUCTURE_REJECTED' OR d.status_code= 'STRUCTURE_APPROVED')
;
select type_id
from pa_proj_elements
where proj_element_id = c_proj_element_id;
UPDATE pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_WORKING'
where project_id = p_project_id
and proj_element_id = l_structure_id;
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_PUBLISHED'
;
update pa_proj_elem_ver_schedule
set WQ_PLANNED_QUANTITY = null
where element_version_id = l_ref_task_version_id
and project_id = p_project_id
and proj_element_id = p_parent_task_id;
update pa_proj_elem_ver_schedule
set WQ_PLANNED_QUANTITY = null
where element_version_id = l_ref_task_version_id
and project_id = p_project_id
and proj_element_id = p_parent_task_id;
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
( p_calling_context => p_calling_module
,p_project_id => p_project_id
,p_structure_version_id => l_structure_version_id
,p_update_wbs_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
select parent_structure_version_id
into l_parent_struc_ver_id
from pa_proj_element_versions
where element_version_id = l_task_version_id;
SELECT type_id INTO l_task_type_id
FROM pa_proj_elements
WHERE proj_element_id = l_task_id
AND project_id = p_project_id;
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_del_task_ver_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.DELETE_PLANNING_TRANSACTIONS:'||SQLERRM,1,240));
PROCEDURE update_task_structure
(
p_calling_module IN VARCHAR2 := 'FORMS'
,p_ref_task_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,p_task_number IN VARCHAR2
,p_task_name IN VARCHAR2
,p_task_description IN VARCHAR2
,p_carrying_out_organization_id IN NUMBER
,p_structure_type IN VARCHAR2 := 'FINANCIAL'
,p_task_manager_id IN NUMBER
,p_pm_product_code IN VARCHAR2
,p_pm_task_reference IN VARCHAR2
,p_location_id IN NUMBER
,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_early_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_early_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_late_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_late_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_scheduled_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_OBLIGATION_START_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_OBLIGATION_FINISH_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_ESTIMATED_START_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_ESTIMATED_FINISH_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_BASELINE_START_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_BASELINE_FINISH_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_CLOSED_DATE IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,P_WQ_UOM_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_WQ_ITEM_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_STATUS_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_WF_STATUS_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_PRIORITY_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_MILESTONE_FLAG IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_CRITICAL_FLAG IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_INC_PROJ_PROGRESS_FLAG IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_LINK_TASK_FLAG IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,P_CALENDAR_ID IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,P_PLANNED_EFFORT IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,P_DURATION IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,P_PLANNED_WORK_QUANTITY IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,P_TASK_TYPE 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_parent_structure_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_phase_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,P_PHASE_CODE IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- (begin venkat) new params for bug #3450684 ----------------------------------------------
,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
-- (end venkat) new params for bug #3450684 -------------------------------------------------
-- (begin) add new params bug - 3654243 -----
,p_base_percent_comp_deriv_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_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
,p_invoice_method IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_customer_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_gen_etc_source_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- (end) add new params bug - 3654243 -----
-- Progress Management Changes. Bug # 3420093.
,p_etc_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_percent_complete IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
-- Progress Management Changes. Bug # 3420093.
-- rtarway, BUG 3908013
,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
--Bug 6153503
,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
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
IS select 'Y'
from pa_projects_all
where project_id = p_project_id
and template_flag = 'Y';
SELECT record_version_number
FROM pa_proj_elements
WHERE proj_element_id = p_task_id
AND object_type = 'PA_TASKS';
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_PUBLISHED'
AND d.latest_eff_published_flag = 'Y'
;
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_WORKING'
;
SELECT parent_task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = c_task_id;
SELECT element_version_id, record_version_number
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND proj_element_id = c_task_id
AND parent_structure_version_id = c_structure_version_id
AND object_type = 'PA_TASKS';
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = c_structure_version_id
AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
WHERE parent_structure_version_id = c_structure_version_id
AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
WHERE element_version_id = c_task_version_id )
AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
WHERE element_version_id = c_task_version_id )
)
AND object_type = 'PA_TASKS';
SELECT pev_schedule_id, record_version_number
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_task_version_id
AND project_id = p_project_id
AND proj_element_id = p_task_id; --This will give u unique record.
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = c_task_version_id
and relationship_type = 'S';
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND element_version_id = c_task_version_id
AND object_type = 'PA_TASKS';
SELECT 'x'
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = 'WORKPLAN'
AND element_version_id = ( SELECT parent_structure_version_id
FROM pa_proj_element_versions
WHERE element_version_id = c_task_version_id );
SELECT *
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_element_version_id
and project_id = p_project_id;
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = p_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND (d.status_code = 'STRUCTURE_SUBMITTED' OR d.status_code='STRUCTURE_REJECTED' OR d.status_code= 'STRUCTURE_APPROVED')
;
SELECT pe.ELEMENT_NUMBER, pe.NAME
FROM pa_proj_elements pe
WHERE pe.proj_element_id = p_task_id
AND pe.object_type = 'PA_TASKS';
/* Bug # 3420093 - Adding Progress Management modifications to update_task flow. */
--Bug 5256359. Changed the type of these pl-sql tbls to SYSTEM.pa_xxx_tbl_type from PA_xxx_1000_xxx.
--New tbls (suffixed with _prm), created below will be used as parameters in the call to
--pa_task_pub1.call_add_planning_txns
l_add_task_ver_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
select
element_version_id,planned_effort,actual_start_date,actual_finish_date
from pa_proj_elem_ver_schedule ppevs, pa_proj_elements ppe
where ppevs.project_id = ppe.project_id
and ppevs.proj_element_id = ppe.proj_element_id
and ppe.object_type = 'PA_TASKS'
and ppevs.project_id = p_project_id;
SELECT
ppv.element_version_id
,ppevs.planned_effort
,ppevs.actual_start_date
,ppevs.actual_finish_date
FROM
pa_proj_elem_ver_schedule ppevs
,pa_proj_element_versions ppv
WHERE
ppevs.project_id = ppv.project_id
and ppevs.proj_element_id = ppv.proj_element_id
and ppv.object_type = 'PA_TASKS'
and ppv.element_version_id = ppevs.element_version_id
and ppv.project_id = p_project_id
and ppv.parent_structure_version_id = p_struct_ver_id;
/* Bug # 3420093 - Adding Progress Management modifications to update_task flow. */
--rtarway, 3908013
l_attribute_category VARCHAR2(150);
SELECT 'Y'
FROM pa_resource_assignments ra
WHERE ra.ta_display_flag = 'N'
AND ra.wbs_element_version_id = p_element_version_id
AND rownum = 1;
SAVEPOINT update_task_struc;
After updating all the tasks in the script, code again updates the task_number, trimming the '-' added. **/
END IF;
PA_TASK_PUB1.Update_Task(
p_calling_module => p_calling_module
,p_init_msg_list => l_init_msg_list
--rtarway for BUG 3666023
--,p_task_id => p_task_id
,p_task_id => l_task_id
,p_task_number => l_task_number /* Modified p_task_number to l_task_number for 3290037 */
,p_task_name => l_task_name /* Modified p_task_name to l_task_name for 3290037 */
,p_task_description => p_task_description
,p_task_manager_id => p_TASK_MANAGER_ID
,p_carrying_out_org_id => p_carrying_out_organization_id
,p_pm_product_code => p_pm_product_code
,p_pm_task_reference => p_pm_task_reference
,p_location_id => p_location_id
,p_priority_code => p_priority_code
,p_TYPE_ID => p_task_type
,p_status_code => P_STATUS_CODE
,p_inc_proj_progress_flag => p_inc_proj_progress_flag
,p_closed_date => p_closed_date
,p_record_version_number => l_record_version_number
,P_UOM_CODE => P_WQ_UOM_CODE
,p_work_item_code => P_WQ_ITEM_CODE
----- begin Bug 3654243 --------------------------------------------
,p_Base_Perc_Comp_Deriv_Code => p_base_percent_comp_deriv_code
,p_gen_etc_src_code => p_gen_etc_source_code
----- end Bug 3654243 ----------------------------------------------
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
,p_msg_name => 'PA_PS_TASK_NOT_UPDATED'
/* ADDED PROJECT_ID AND TASK_ID TOKENS FOR BUG 4219451 */
,p_token1 => 'PROJECT'
,p_value1 => p_project_id
,p_token2 => 'TASK'
,p_value2 => l_task_id);
, p_context => 'UPDATE_AMG'
, x_message_name => l_message_name
, x_message_type => l_message_type
, x_structure_version_id => l_str_ver_id
, x_conc_request_id => l_conc_request_id);
,p_msg_name => 'PA_PS_TASK_NOT_UPDATED'
/* ADDED PROJECT_ID AND TASK_ID TOKENS FOR BUG 4219451 */
,p_token1 => 'PROJECT'
,p_value1 => p_project_id
,p_token2 => 'TASK'
,p_value2 => l_task_id);
SELECT type_id INTO l_task_type_id
FROM pa_proj_elements
--WHERE proj_element_id = p_task_id, rtarway, BUG 3666023, replace by l_task_id
WHERE proj_element_id = l_task_id
AND project_id = p_project_id;
PA_TASK_PUB1.Update_Schedule_Version(
p_calling_module => p_calling_module
,p_init_msg_list => l_init_msg_list
,p_pev_schedule_id => l_pev_schedule_id
,p_calendar_id => p_calendar_id
,p_scheduled_start_date => p_scheduled_start_date
,p_scheduled_end_date => p_scheduled_finish_date
,p_obligation_start_date => p_obligation_start_date
,p_obligation_end_date => p_obligation_finish_date
,p_actual_start_date => p_actual_start_date
,p_actual_finish_date => p_actual_finish_date
,p_estimate_start_date => p_estimated_start_date
,p_estimate_finish_date => p_estimated_finish_date
,p_duration => p_duration
,p_early_start_date => p_early_start_date
,p_early_end_date => p_early_finish_date
,p_late_start_date => p_late_start_date
,p_late_end_date => p_late_finish_date
,p_milestone_flag => p_milestone_flag
,p_critical_flag => p_critical_flag
,p_WQ_PLANNED_QUANTITY => l_wq_planned_quantity --bug 2824612
,p_PLANNED_EFFORT => p_planned_effort
,p_record_version_number => l_record_version_number
--Changed the param names to l_....rtarway, BUG 3908013
,p_attribute_category => l_attribute_category
,p_attribute1 => l_attribute1
,p_attribute2 => l_attribute2
,p_attribute3 => l_attribute3
,p_attribute4 => l_attribute4
,p_attribute5 => l_attribute5
,p_attribute6 => l_attribute6
,p_attribute7 => l_attribute7
,p_attribute8 => l_attribute8
,p_attribute9 => l_attribute9
,p_attribute10 => l_attribute10
--end rtarway, BUG 3908013
--Bug 6153503
,p_attribute11 => l_attribute11--l_ver_sch_attr_rec.attribute11
,p_attribute12 => l_attribute12--l_ver_sch_attr_rec.attribute12
,p_attribute13 => l_attribute13--l_ver_sch_attr_rec.attribute13
,p_attribute14 => l_attribute14--l_ver_sch_attr_rec.attribute14
,p_attribute15 => l_attribute15--l_ver_sch_attr_rec.attribute15
-- (begin venkat) new params for bug #3450684 ----------------------------------------------
,p_ext_act_duration => p_ext_act_duration
,p_ext_remain_duration => p_ext_remain_duration
,p_ext_sch_duration => p_ext_sch_duration
-- (end venkat) new params for bug #3450684 ------------------------------------------------
-- begin Bug 3654243 -----------------------------------------------------------------------
,p_def_sch_tool_tsk_type_code => p_sch_tool_tsk_type_code
,p_constraint_type_code => p_constraint_type_code
,p_constraint_date => p_constraint_date
,p_free_slack => p_free_slack
,p_total_slack => p_total_slack
,p_effort_driven_flag => p_effort_driven_flag
,p_level_assignments_flag => p_level_assignments_flag
-- end Bug 3654243 -------------------------------------------------------------------------
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
,p_msg_name => 'PA_PS_TASK_NOT_UPDATED'
/* ADDED PROJECT_ID AND TASK_ID TOKENS FOR BUG 4219451 */
,p_token1 => 'PROJECT'
,p_value1 => p_project_id
,p_token2 => 'TASK'
,p_value2 => l_task_id);
/* Bug # 3420093 - Adding Progress Management modifications to update_task flow. */
-- Bug#3747312
-- Added PA_PROJ_TASK_STRUC_PUB.IS_WP_SEPARATE_FROM_FN = 'N'
-- as the add_planning_txn/delete_planning_txn should be called in
-- SHARED case and for SPLIT case if p_structure_type is passed as
-- WORKPLAN.
/* Commenting for bug 7358214
if (p_structure_type = 'WORKPLAN' OR nvl(PA_PROJ_TASK_STRUC_PUB.IS_WP_SEPARATE_FROM_FN( p_project_id ),'N') = 'N')then
for l_task_cur_rec in l_task_cur(p_project_id, l_structure_version_id)
loop
if (PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(l_task_cur_rec.element_version_id) = 'Y') then
l_add_task_ver_ids.extend(1);
pa_fp_planning_transaction_pub.delete_planning_transactions
(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_del_task_ver_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('PA_FP_PLANNING_TRANSACTION_PUB.DELETE_PLANNING_TRANSACTIONS:'||SQLERRM,1,240));
/* Bug # 3420093 - Adding Progress Management modifications to update_task flow. */
-- Progress Management Changes. Bug # 3420093.
/* Commenting out the condition to check if workplan versioning is enabled for the project
as PA_TASK_PUB1.update_task_det_sch_info() should be called for both versioning enabled
and versioning disabled projects. */
/* if (PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_project_id) = 'Y') then */
/* Calling API: pa_task_pub1.update_task_det_sch_info() only if calling module is not 'FORMS'. */
if p_calling_module <> 'FORMS' then
pa_task_pub1.update_task_det_sch_info(
p_calling_module => p_calling_module
,p_task_ver_id => l_task_version_id
,p_project_id => p_project_id
,p_planned_effort => p_planned_effort
,p_ETC_effort => p_etc_effort
,p_structure_version_id => p_structure_version_id
,p_percent_complete => p_percent_complete
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
rollback to update_task_struc;
p_procedure_name => 'update_task_structure',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_task_struc;
END update_task_structure;
PROCEDURE delete_task_structure
(
p_calling_module IN VARCHAR2
,p_task_id IN NUMBER
,p_task_version_id IN NUMBER := -9999 --mandatory for workplan task version
,p_project_id IN NUMBER := -9999 --bug 2765115
,p_structure_type IN VARCHAR2 := 'FINANCIAL' --bug 3301192
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
IS
l_return_status VARCHAR2(1);
SELECT element_version_id, record_version_number
FROM pa_proj_element_versions ppev
WHERE proj_element_id = p_task_id
AND object_type = 'PA_TASKS'
AND parent_structure_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID( p_project_id ); -- bug 2765115
SELECT record_version_number
FROM pa_proj_element_versions ppev
WHERE element_version_id = p_task_version_id
AND object_type = 'PA_TASKS';
SELECT template_flag from pa_projects_all where project_id = p_project_id;
SELECT element_version_id, record_version_number
FROM pa_proj_element_versions ppev
WHERE proj_element_id = p_task_id
AND object_type = 'PA_TASKS'
AND project_id = p_project_id;
SAVEPOINT delete_task_struc;
PA_TASK_PUB1.Delete_Task_Version(
p_calling_module => p_calling_module
,p_structure_type => p_structure_type --bug 3301192
,p_task_version_id => l_task_version_id
,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
);
,p_msg_name => 'PA_PS_TASK_NOT_DELETED');
rollback to delete_task_struc;
p_procedure_name => 'delete_task_structure',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_task_struc;
END delete_task_structure;
PROCEDURE delete_project_structure
(
p_calling_module IN VARCHAR2
,p_project_id IN NUMBER
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(1);
SELECT rowid, element_version_id
FROM pa_proj_element_versions
WHERE OBJECT_TYPE = 'PA_STRUCTURES'
AND project_id = p_project_id;
SELECT rowid, element_version_id
FROM pa_proj_element_versions
WHERE OBJECT_TYPE = 'PA_TASKS'
AND project_id = p_project_id
AND parent_structure_version_id = c_parent_structure_version_id;
SELECT rowid, proj_element_id
FROM pa_proj_elements
WHERE project_id = p_project_id;
SAVEPOINT delete_project_struc;
PA_PROJ_ELEMENTS_UTILS.Check_Delete_task_Ver_Ok(
p_project_id => p_project_id
,p_task_version_id => cur_task_ver_rec.element_version_id
,p_parent_structure_ver_id => cur_str_ver_rec.element_version_id
,x_return_status => l_return_status
,x_error_message_code => l_error_message_code
);
DELETE FROM pa_object_relationships
WHERE relationship_type = 'S' AND
(object_id_from1 = cur_task_ver_rec.element_version_id OR
object_id_to1 = cur_task_ver_rec.element_version_id);
PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW( cur_task_ver_rec.rowid );
PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
p_project_id => p_project_id
,p_structure_version_id => cur_str_ver_rec.element_version_id
,x_return_status => l_return_status
,x_error_message_code => l_error_message_code
);
DELETE FROM pa_object_relationships
WHERE relationship_type = 'S' AND
(object_id_from1 = cur_str_ver_rec.element_version_id OR
object_id_to1 = cur_str_ver_rec.element_version_id);
PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW( cur_str_ver_rec.rowid );
DELETE FROM pa_proj_structure_types
WHERE proj_element_id = cur_proj_elems_rec.proj_element_id;
PA_PROJ_ELEMENTS_PKG.DELETE_ROW( cur_proj_elems_rec.rowid );
DELETE FROM pa_proj_elem_ver_schedule where project_id = p_project_id;
DELETE FROM pa_proj_elem_ver_structure where project_id = p_project_id;
/* Code to delete project records from tables: pa_progress_rollup, pa_proj_progress_attr
and pa_proj_workplan_attr. */
BEGIN
PA_PROGRESS_PUB.delete_working_wp_progress (
p_project_id => p_project_id
,p_calling_context => 'PROJECT_STRUCTURE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_Msg_data => x_msg_data
);
p_procedure_name => 'delete_project_structure',
p_error_text => SUBSTR('PA_PROGRESS_PUB.delete_working_wp_progress:'||SQLERRM,1,240));
rollback to delete_project_struc;
p_procedure_name => 'delete_project_structure',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to delete_project_struc;
END delete_project_structure;
select 'Y'
from pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppevs.project_id = p_project_id
and ppevs.STATUS_CODE = 'STRUCTURE_PUBLISHED'
and ppevs.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN';
SELECT 'X'
FROM pa_projects_all
WHERE project_id = p_project_id
AND split_cost_from_workplan_flag = 'N';
select 'Y'
from pa_proj_elem_ver_structure
where project_id = p_project_id
and STATUS_CODE = 'STRUCTURE_PUBLISHED';
PROCEDURE create_delete_workplan_struc(
p_calling_module IN VARCHAR2
,p_project_id IN NUMBER
,p_project_number IN VARCHAR2
,p_project_name IN VARCHAR2
,p_project_description IN VARCHAR2
,p_split_workplan IN VARCHAR2
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
SELECT split_cost_from_workplan_flag
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT proj_element_id
FROM pa_proj_elements
WHERE project_id = p_project_id
AND object_type = 'PA_STRUCTURES';
SELECT element_version_id, record_version_number
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND object_type = 'PA_STRUCTURES'
AND proj_element_id = c_structure_id;
select structure_type_id
from pa_structure_types
where structure_type_class_code = 'WORKPLAN';
SELECT element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = 'WORKPLAN';
SELECT element_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = c_parent_struc_ver_id
AND object_type = 'PA_TASKS';
SAVEPOINT create_delete_wp_struc;
UPDATE pa_projects_all
SET split_cost_from_workplan_flag = p_split_workplan
WHERE project_id = p_project_id;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
ELSE --delete the workplan structure including all its structure versions.
FOR cur_pa_proj_elems_rec IN cur_pa_proj_elems LOOP
--should obly be WORKPLAN structure
IF PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure( cur_pa_proj_elems_rec.proj_element_id, 'WORKPLAN' ) = 'Y' AND
PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure( cur_pa_proj_elems_rec.proj_element_id, 'FINANCIAL' ) = 'N'
THEN
FOR cur_pa_proj_elem_vers_rec IN cur_pa_proj_elem_vers( cur_pa_proj_elems_rec.proj_element_id ) LOOP
PA_PROJECT_STRUCTURE_PUB1.Delete_Structure_Version(
p_structure_version_id => cur_pa_proj_elem_vers_rec.element_version_id
,p_record_version_number => cur_pa_proj_elem_vers_rec.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_rowid
, X_PROJ_STRUCTURE_TYPE_ID => l_proj_structure_type_id
, X_PROJ_ELEMENT_ID => cur_pa_proj_elems_rec.proj_element_id
, X_STRUCTURE_TYPE_ID => l_structure_type_id
, X_RECORD_VERSION_NUMBER => 1
, X_ATTRIBUTE_CATEGORY => NULL
, X_ATTRIBUTE1 => NULL
, X_ATTRIBUTE2 => NULL
, X_ATTRIBUTE3 => NULL
, X_ATTRIBUTE4 => NULL
, X_ATTRIBUTE5 => NULL
, X_ATTRIBUTE6 => NULL
, X_ATTRIBUTE7 => NULL
, X_ATTRIBUTE8 => NULL
, X_ATTRIBUTE9 => NULL
, X_ATTRIBUTE10 => NULL
, X_ATTRIBUTE11 => NULL
, X_ATTRIBUTE12 => NULL
, X_ATTRIBUTE13 => NULL
, X_ATTRIBUTE14 => NULL
, X_ATTRIBUTE15 => NULL
);
rollback to create_delete_wp_struc;
p_procedure_name => 'create_delete_workplan_struc',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to create_delete_wp_struc;
END create_delete_workplan_struc;
SELECT element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
WHERE c.project_id = p_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = 'FINANCIAL';
SELECT element_version_id, proj_element_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND parent_structure_version_id = c_parent_str_ver_id
AND object_type = 'PA_TASKS';
DELETE FROM pa_object_relationships WHERE relationship_type = 'S' AND
(object_id_to1 = cur_task_vers_rec.element_version_id OR
object_id_from1 = cur_task_vers_rec.element_version_id);
DELETE FROM pa_proj_elem_ver_schedule WHERE element_version_id = cur_task_vers_rec.element_version_id and project_id = p_project_id;
DELETE FROM pa_proj_element_versions WHERE element_version_id = cur_task_vers_rec.element_version_id;
DELETE FROM pa_proj_elements WHERE proj_element_id = cur_task_vers_rec.proj_element_id;
SELECT proj_element_id
FROM pa_proj_elements
WHERE PM_SOURCE_REFERENCE = p_pm_parent_task_reference
AND project_id = p_project_id
AND OBJECT_TYPE = 'PA_TASKS';
PROCEDURE delete_structure_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 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_structure_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
BEGIN
x_return_status := 'S';
PA_PROJECT_STRUCTURE_PUB1.Delete_Structure_Version(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_validation_level => p_validation_level
,p_calling_module => p_calling_module
,p_debug_mode => p_debug_mode
,p_max_msg_count => p_max_msg_count
,p_structure_version_id => p_structure_version_id
,p_record_version_number => p_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END delete_structure_version;
select segment1, name from pa_projects_all
where project_id = p_project_id;
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code <> 'STRUCTURE_PUBLISHED';
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => X_row_id
--,X_ELEMENT_VERSION_ID => x_structure_version_id * commented got Bug Fix: 4537865
,X_ELEMENT_VERSION_ID => l_new_structure_version_id --added for Bug Fix: 4537865
,X_PROJ_ELEMENT_ID => x_structure_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID=> x_structure_version_id
,X_DISPLAY_SEQUENCE => null
,X_WBS_LEVEL => null
,X_WBS_NUMBER => '0'
,X_ATTRIBUTE_CATEGORY => null
,X_ATTRIBUTE1 => null
,X_ATTRIBUTE2 => null
,X_ATTRIBUTE3 => null
,X_ATTRIBUTE4 => null
,X_ATTRIBUTE5 => null
,X_ATTRIBUTE6 => null
,X_ATTRIBUTE7 => null
,X_ATTRIBUTE8 => null
,X_ATTRIBUTE9 => null
,X_ATTRIBUTE10 => null
,X_ATTRIBUTE11 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_TASK_UNPUB_VER_STATUS_CODE => null
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
X_ROWID => x_row_id
, X_PEV_STRUCTURE_ID => x_pev_structure_id
, X_ELEMENT_VERSION_ID => x_structure_version_id
, X_VERSION_NUMBER => l_struc_ver_number
, X_NAME => l_name
, X_PROJECT_ID => p_project_id
, X_PROJ_ELEMENT_ID => x_structure_id
, X_DESCRIPTION => p_description
, X_EFFECTIVE_DATE => null
, X_PUBLISHED_DATE => null
, X_PUBLISHED_BY => null
, X_CURRENT_BASELINE_DATE => null
, X_CURRENT_BASELINE_FLAG => 'N'
, X_CURRENT_BASELINE_BY => null
, X_ORIGINAL_BASELINE_DATE => null
, X_ORIGINAL_BASELINE_FLAG => 'N'
, X_ORIGINAL_BASELINE_BY => null
, X_LOCK_STATUS_CODE => NULL
, X_LOCKED_BY => NULL
, X_LOCKED_DATE => NULL
, X_STATUS_CODE => l_status_code
, X_WF_STATUS_CODE => NULL
, X_LATEST_EFF_PUBLISHED_FLAG => l_latest_eff_published_flag
, X_RECORD_VERSION_NUMBER => 1
, X_CHANGE_REASON_CODE => NULL
, X_CURRENT_WORKING_FLAG => l_current_working_ver_flag /* bug 3301192 */
, X_SOURCE_OBJECT_ID => p_project_id
, X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
procedure create_update_struct_ver(
p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_structure_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_structure_version_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_structure_version_id IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,x_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
cursor c1 is
select pev_structure_id, name, record_version_number
from pa_proj_elem_ver_structure
where project_id = p_project_id
and element_version_id = p_structure_version_id;
select b.proj_element_id
from pa_proj_elements a, pa_proj_structure_types b,
pa_structure_types c
where a.project_id = p_project_id
and a.object_type = 'PA_STRUCTURES'
and a.proj_element_id = b.proj_element_id
and b.structure_type_id = c.structure_type_id
and c.structure_type = p_structure_type;
select wp_enable_version_flag
from pa_proj_workplan_attr
where project_id = p_project_id;
select '1' from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id = c_proj_elem_id
and status_code <> 'STRUCTURE_PUBLISHED';
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code <> 'STRUCTURE_PUBLISHED';
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => X_row_id
--,X_ELEMENT_VERSION_ID => x_structure_version_id * Commented for Bug Fix: 4537865
,X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for Bug fix: 4537865
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID=> x_structure_version_id
,X_DISPLAY_SEQUENCE => null
,X_WBS_LEVEL => null
,X_WBS_NUMBER => '0'
,X_ATTRIBUTE_CATEGORY => null
,X_ATTRIBUTE1 => null
,X_ATTRIBUTE2 => null
,X_ATTRIBUTE3 => null
,X_ATTRIBUTE4 => null
,X_ATTRIBUTE5 => null
,X_ATTRIBUTE6 => null
,X_ATTRIBUTE7 => null
,X_ATTRIBUTE8 => null
,X_ATTRIBUTE9 => null
,X_ATTRIBUTE10 => null
,X_ATTRIBUTE11 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_TASK_UNPUB_VER_STATUS_CODE => null
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
X_ROWID => x_row_id
, X_PEV_STRUCTURE_ID => x_pev_structure_id
, X_ELEMENT_VERSION_ID => x_structure_version_id
, X_VERSION_NUMBER => l_struc_ver_number
, X_NAME => p_structure_version_name
, X_PROJECT_ID => p_project_id
, X_PROJ_ELEMENT_ID => l_proj_element_id
, X_DESCRIPTION => p_description
, X_EFFECTIVE_DATE => null
, X_PUBLISHED_DATE => null
, X_PUBLISHED_BY => null
, X_CURRENT_BASELINE_DATE => null
, X_CURRENT_BASELINE_FLAG => 'N'
, X_CURRENT_BASELINE_BY => null
, X_ORIGINAL_BASELINE_DATE => null
, X_ORIGINAL_BASELINE_FLAG => 'N'
, X_ORIGINAL_BASELINE_BY => null
, X_LOCK_STATUS_CODE => NULL
, X_LOCKED_BY => NULL
, X_LOCKED_DATE => NULL
, X_STATUS_CODE => 'STRUCTURE_WORKING'
, X_WF_STATUS_CODE => NULL
, X_LATEST_EFF_PUBLISHED_FLAG => 'N'
, X_RECORD_VERSION_NUMBER => 1
, X_CHANGE_REASON_CODE => NULL
, X_CURRENT_WORKING_FLAG => 'Y' /* Since this is a shared + versioned senario and only one struc version then it should be CWV. bug 3301192 */
, X_SOURCE_OBJECT_ID => p_project_id
, X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => X_row_id
--,X_ELEMENT_VERSION_ID => x_structure_version_id * commented for Bug fix: 4537865
, X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for Bug fix: 4537865
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_OBJECT_TYPE => 'PA_STRUCTURES'
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID=> x_structure_version_id
,X_DISPLAY_SEQUENCE => null
,X_WBS_LEVEL => null
,X_WBS_NUMBER => '0'
,X_ATTRIBUTE_CATEGORY => null
,X_ATTRIBUTE1 => null
,X_ATTRIBUTE2 => null
,X_ATTRIBUTE3 => null
,X_ATTRIBUTE4 => null
,X_ATTRIBUTE5 => null
,X_ATTRIBUTE6 => null
,X_ATTRIBUTE7 => null
,X_ATTRIBUTE8 => null
,X_ATTRIBUTE9 => null
,X_ATTRIBUTE10 => null
,X_ATTRIBUTE11 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_TASK_UNPUB_VER_STATUS_CODE => null
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
X_ROWID => x_row_id
, X_PEV_STRUCTURE_ID => x_pev_structure_id
, X_ELEMENT_VERSION_ID => x_structure_version_id
, X_VERSION_NUMBER => l_struc_ver_number
, X_NAME => p_structure_version_name
, X_PROJECT_ID => p_project_id
, X_PROJ_ELEMENT_ID => l_proj_element_id
, X_DESCRIPTION => p_description
, X_EFFECTIVE_DATE => null
, X_PUBLISHED_DATE => null
, X_PUBLISHED_BY => null
, X_CURRENT_BASELINE_DATE => null
, X_CURRENT_BASELINE_FLAG => 'N'
, X_CURRENT_BASELINE_BY => null
, X_ORIGINAL_BASELINE_DATE => null
, X_ORIGINAL_BASELINE_FLAG => 'N'
, X_ORIGINAL_BASELINE_BY => null
, X_LOCK_STATUS_CODE => NULL
, X_LOCKED_BY => NULL
, X_LOCKED_DATE => NULL
, X_STATUS_CODE => 'STRUCTURE_WORKING'
, X_WF_STATUS_CODE => NULL
, X_LATEST_EFF_PUBLISHED_FLAG => 'N'
, X_RECORD_VERSION_NUMBER => 1
, X_CHANGE_REASON_CODE => NULL
, X_CURRENT_WORKING_FLAG => l_current_working_ver_flag /* bug 3301192 */
, X_SOURCE_OBJECT_ID => p_project_id
, X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
p_procedure_name => 'CREATE_UPDATE_STRUCT_VER',
p_error_text => SUBSTRB('call of pa_fp_planning_transaction_pub.add_planning_transactions failed. sqlerrm= :'||SQLERRM,1,240));
PA_PROJECT_STRUCTURE_PVT1.update_structure_version_attr(
p_pev_structure_id => l_pev_structure_id
,p_structure_version_name => l_name
,p_structure_version_desc => p_description
,p_record_version_number => l_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
p_procedure_name => 'CREATE_UPDATE_STRUCT_VER',
p_error_text => SUBSTRB(SQLERRM,1,240));
END create_update_struct_ver;
select start_date
from pa_projects
where project_id = x_orig_project_id;
select min(start_date) min_start
from pa_tasks
where project_id = x_orig_project_id;
PROCEDURE update_task_structure2
(
p_calling_module IN VARCHAR2 := 'FORMS'
,p_ref_task_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,p_task_number IN VARCHAR2
,p_task_name IN VARCHAR2
,p_task_description IN VARCHAR2
,p_carrying_out_organization_id IN NUMBER
,p_structure_type IN VARCHAR2 := 'FINANCIAL'
,p_task_manager_id IN NUMBER
,p_pm_product_code IN VARCHAR2
,p_pm_task_reference IN VARCHAR2
,p_location_id IN NUMBER
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
BEGIN
update_task_structure(
p_calling_module => p_calling_module
,p_ref_task_id => p_ref_task_id
,p_project_id => p_project_id
,p_task_id => p_task_id
,p_task_number => p_task_number
,p_task_name => p_task_name
,p_task_description => p_task_description
,p_carrying_out_organization_id => p_carrying_out_organization_id
,p_structure_type => p_structure_type
,p_task_manager_id => p_task_manager_id
,p_pm_product_code => p_pm_product_code
,p_pm_task_reference => p_pm_task_reference
,p_location_id => p_location_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status
);
END update_task_structure2;
SELECT element_version_id, parent_structure_version_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND proj_element_id = p_task_id
AND parent_structure_version_id = c_parent_struc_ver_id
AND object_type = 'PA_TASKS';
SELECT element_version_id
FROM pa_proj_elem_ver_structure
WHERE project_id = p_project_id
AND status_code = 'STRUCTURE_WORKING';
/* SELECT proj_element_id
FROM pa_proj_elements
WHERE project_id = p_project_id
AND object_type = 'PA_STRUCTURES';
SELECT 'x'
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE ppe.project_id = p_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = 1; --'WORKPLAN'
SELECT ppe.proj_element_id
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE ppe.project_id = p_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = 1; --'WORKPLAN'
PROCEDURE update_trans_dates(
p_project_id IN NUMBER
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
BEGIN
x_return_status := 'S';
END update_trans_dates;
PROCEDURE update_wp_calendar(
p_project_id IN NUMBER
,p_calendar_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
BEGIN
PA_PROJECT_STRUCTURE_PVT1.update_wp_calendar(
p_project_id => p_project_id
,p_calendar_id => p_calendar_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END update_wp_calendar;
SELECT proj_element_id
FROM pa_proj_elements pelem
WHERE project_id = c_project_id
AND pm_source_reference = c_pm_source_reference
AND exists(select 1
from pa_proj_structure_types pstype, pa_structure_types types
where pstype.proj_element_id = pelem.parent_structure_id
and pstype.structure_type_id = types.structure_type_id
and types.structure_type = p_structure_type);
SELECT wbs_level
FROM pa_proj_element_versions
WHERE element_version_id = c_task_ver_id;
l_tasks_ver_ids.DELETE;
l_outline_level.DELETE;
select project_id
from pa_proj_element_versions
where element_version_id = p_structure_version_id;
select MAX(a.last_update_date)
from pa_proj_element_versions b,
pa_proj_elem_ver_schedule a,
pa_proj_elem_ver_structure c
where p_structure_version_id = c.element_version_id
and l_project_id = c.project_id
and c.status_code <> 'STRUCTURE_PUBLISHED'
and b.parent_structure_version_id = c.element_version_id
and b.project_id = c.project_id
and a.element_version_id (+)= b.element_version_id
and a.project_id (+) = b.project_id
and a.proj_element_id (+) = b.proj_element_id;
SELECT MAX(a.last_update_date)
FROM pa_proj_element_versions b,
pa_proj_elem_ver_schedule a,
pa_proj_elem_ver_structure c
WHERE a.element_version_id (+)= b.element_version_id
AND a.project_id (+) = b.project_id
AND a.proj_element_id (+) = b.proj_element_id
AND b.parent_structure_version_id = c.element_version_id
AND b.project_id = c.project_id
AND c.status_code <> 'STRUCTURE_PUBLISHED'
AND b.parent_structure_version_id = p_structure_version_id
;
SELECT ppwa.WP_ENABLE_VERSION_FLAG
FROM pa_proj_workplan_attr ppwa
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppwa.project_id = p_src_project_id
AND ppwa.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'WORKPLAN' ;
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id
AND ppevs.latest_eff_published_flag = 'Y';
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id
AND ppevs.current_working_flag = 'Y';
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'FINANCIAL' ;
SELECT scheduled_start_date
FROM pa_proj_elem_ver_schedule
WHERE project_id = c_project_id
AND element_version_id = c_struc_ver_id;
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --This column stores the stucture version id in the src project, to be used to create relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,FINANCIAL_TASK_FLAG
,source_object_id
,source_object_type
)
SELECT
pa_proj_element_versions_s.nextval
,p_dest_fn_structure_id --This would be the WP/FIN structure id in shared case and FIN structure id in split case
,ppev.object_type
,p_dest_project_id
,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,ppev.FINANCIAL_TASK_FLAG
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_element_versions ppev
WHERE ppev.project_id = p_src_project_id
AND ppev.element_version_id = l_src_fin_version_id ;
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
PEV_STRUCTURE_ID
,ELEMENT_VERSION_ID
,VERSION_NUMBER
,NAME
,PROJECT_ID
,PROJ_ELEMENT_ID
,DESCRIPTION
,EFFECTIVE_DATE
,PUBLISHED_DATE
,PUBLISHED_BY_PERSON_ID
,CURRENT_BASELINE_DATE
,CURRENT_FLAG
,CURRENT_BASELINE_PERSON_ID
,ORIGINAL_BASELINE_DATE
,ORIGINAL_FLAG
,ORIGINAL_BASELINE_PERSON_ID
,LOCK_STATUS_CODE
,LOCKED_BY_PERSON_ID
,LOCKED_DATE
,STATUS_CODE
,WF_STATUS_CODE
,LATEST_EFF_PUBLISHED_FLAG
,RECORD_VERSION_NUMBER
,WBS_RECORD_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,change_reason_code
,PROCESS_UPDATE_WBS_FLAG
,current_working_flag
,source_object_id
,source_object_type
)
SELECT
pa_proj_elem_ver_structure_s.nextval
,ppev.element_version_id
,ppevs.version_number
,ppevs.name
,p_dest_project_id
,p_dest_fn_structure_id --This would be WP/FIN structure id in shared case and FIN structure id in split case
,ppevs.DESCRIPTION
,ppevs.EFFECTIVE_DATE
,decode(dest_latest_eff_pub_flag,'Y',sysdate,to_date(null) ) --PUBLISHED_DATE
,decode(dest_latest_eff_pub_flag,'Y',p_publish_person_id,null ) --PUBLISHED_BY_PERSON_ID
,decode(dest_current_flag,'S',ppevs.current_baseline_date,
decode(dest_current_flag,'Y',sysdate,to_date(null) ) ) --CURRENT_BASELINE_DATE
,decode(dest_current_flag,'S',ppevs.current_flag,dest_current_flag) --CURRENT_FLAG
,decode(dest_current_flag,'S',ppevs.current_baseline_person_id,
decode(dest_current_flag,'Y',p_publish_person_id,null) ) --CURRENT_BASELINE_PERSON_ID
,ppevs.original_baseline_date --ORIGINAL_BASELINE_DATE
,ppevs.original_flag --ORIGINAL_FLAG
,ppevs.original_baseline_person_id --ORIGINAL_BASELINE_PERSON_ID
,'UNLOCKED'
,null
,null
,decode(dest_latest_eff_pub_flag,'Y','STRUCTURE_PUBLISHED','STRUCTURE_WORKING') --STATUS_CODE
,ppevs.WF_STATUS_CODE
,dest_latest_eff_pub_flag --LATEST_EFF_PUBLISHED_FLAG
,ppevs.RECORD_VERSION_NUMBER
,ppevs.WBS_RECORD_VERSION_NUMBER
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppevs.change_reason_code
,ppevs.process_update_wbs_flag
,decode(p_shared,'N',ppevs.current_working_flag,'Y') --CURRENT_WROKING_FLAG
--If SPLIT and fin enabled case, copy cw from source, else populate it as Y
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_element_versions ppev
WHERE ppevs.project_id = p_src_project_id
AND ppevs.element_version_id = l_src_fin_version_id
AND ppev.attribute15 = ppevs.element_version_id
AND ppev.project_id = p_dest_project_id ;
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id
AND ppevs.latest_eff_published_flag = 'Y';
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id
AND ppevs.current_working_flag = 'Y';
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'FINANCIAL' ;
SELECT scheduled_start_date
FROM pa_proj_elem_ver_schedule
WHERE project_id = c_project_id
AND element_version_id = c_struc_ver_id;
SELECT old.carrying_out_organization_id,
new.carrying_out_organization_id
FROM pa_projects_all old,
pa_projects_all new
WHERE old.project_id = p_src_project_id
AND new.project_id = p_dest_project_id;
SELECT
pa_object_relationships_s.nextval,
pobj.object_type_from,
ppev1.element_version_id,
pobj.object_type_to,
ppev2.element_version_id,
pobj.relationship_type,
pobj.relationship_subtype,
pobj.Record_Version_Number,
pobj.weighting_percentage
FROM ( SELECT object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
weighting_percentage
FROM pa_object_relationships
START WITH object_id_from1 = c_src_fin_version_id
AND RELATIONSHIP_TYPE = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND RELATIONSHIP_TYPE = 'S' ) pobj,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev1.attribute15 = pobj.object_id_from1
AND ppev2.attribute15 = pobj.object_id_to1
AND ppev1.project_id = c_dest_project_id
AND ppev2.project_id = c_dest_project_id ;
INSERT INTO pa_proj_elements(
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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LINK_TASK_FLAG
,BASELINE_START_DATE
,BASELINE_FINISH_DATE
,progress_outdated_flag
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,parent_structure_id
,TYPE_CODE
,INC_PROJ_PROGRESS_FLAG
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,BASELINE_DURATION
,PHASE_CODE
,PHASE_VERSION_ID
,source_object_id
,source_object_type
)
SELECT
pt.task_id
,p_dest_project_id
,ppe.object_type
,ppe.element_number
,ppe.name
,ppe.DESCRIPTION
,INITIAL_STATUS_CODE
,ppe.WF_STATUS_CODE
,ppe.PM_SOURCE_CODE
,ppe.PM_SOURCE_REFERENCE
,ppe.CLOSED_DATE
,ppe.LOCATION_ID
,ppe.MANAGER_PERSON_ID
,decode(ppe.CARRYING_OUT_ORGANIZATION_ID,l_old_proj_org_id, l_new_proj_org_id,
ppe.CARRYING_OUT_ORGANIZATION_ID) -- Bug 3387963
,ppe.TYPE_ID
,ppe.PRIORITY_CODE
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppe.RECORD_VERSION_NUMBER
,ppe.REQUEST_ID
,ppe.PROGRAM_APPLICATION_ID
,ppe.PROGRAM_ID
,ppe.PROGRAM_UPDATE_DATE
,ppe.LINK_TASK_FLAG
,ppe.BASELINE_START_DATE
,ppe.BASELINE_FINISH_DATE
,'N' --,ppe.progress_outdated_flag
,ppe.ATTRIBUTE_CATEGORY
,ppe.ATTRIBUTE1
,ppe.ATTRIBUTE2
,ppe.ATTRIBUTE3
,ppe.ATTRIBUTE4
,ppe.ATTRIBUTE5
,ppe.ATTRIBUTE6
,ppe.ATTRIBUTE7
,ppe.ATTRIBUTE8
,ppe.ATTRIBUTE9
,ppe.ATTRIBUTE10
,ppe.ATTRIBUTE11
,ppe.ATTRIBUTE12
,ppe.ATTRIBUTE13
,ppe.ATTRIBUTE14
,ppe.proj_element_id --attribute 15 is used to store old ids.
,p_dest_fn_structure_id --This would be WP/FIN structure id in shared case. FIN structure id in split case
,ppe.TYPE_CODE
,ppe.INC_PROJ_PROGRESS_FLAG
,ppe.WQ_ITEM_CODE
,ppe.WQ_UOM_CODE
,ppe.WQ_ACTUAL_ENTRY_CODE
,ppe.TASK_PROGRESS_ENTRY_PAGE_ID
,ppe.BASELINE_DURATION
,ppe.PHASE_CODE
,ppe.PHASE_VERSION_ID
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_task_types ptt,
pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_tasks pt
WHERE pt.project_id = p_dest_project_id
AND ppe.project_id = p_src_project_id --Added by Sunkalya For the fix of Bug#4600825. This change is done for R12
--Merging of code fix done thru Bug#4589176(115.315) for performance issues.
AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
AND ptt.object_type = 'PA_TASKS'
AND ppe.type_id = ptt.task_type_id
AND ppe.object_type = 'PA_TASKS'
AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
AND ppe.parent_structure_id = ppev.proj_element_id
AND ppev.element_version_id = l_src_fin_version_id ;
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --this column is used to store task ver id of the source project's task versions to be used to created relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,FINANCIAL_TASK_FLAG
,source_object_id
,source_object_type
)
SELECT
pa_proj_element_versions_s.nextval
,ppe.proj_element_id
,ppev.object_type
,p_dest_project_id
,ppev2.element_version_id
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id -- to help create relationships from source
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,decode( ppevs.status_code, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
,ppev.FINANCIAL_TASK_FLAG
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe,
pa_proj_element_versions ppev2,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_TASKS'
AND ppev.parent_structure_version_id = l_src_fin_version_id
AND ppev.proj_element_id = ppe.attribute15
AND ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_TASKS'
AND ppev.parent_structure_version_id = ppev2.attribute15
AND ppev2.project_id = p_dest_project_id
AND ppev2.object_type = 'PA_STRUCTURES'
AND ppevs.project_id = p_dest_project_id
AND ppevs.element_version_id = ppev2.element_version_id ;
l_obj_rel_id.delete;
l_obj_typ_from.delete;
l_obj_from_id.delete;
l_obj_typ_to.delete;
l_obj_to_id.delete;
l_rel_typ.delete;
l_rel_subtyp.delete;
l_rec_ver_num.delete;
l_wt_percent.delete;
insert into PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
values(
l_obj_rel_id(i),
l_obj_typ_from(i),
l_obj_from_id(i),
l_obj_typ_to(i),
l_obj_to_id(i),
l_rel_typ(i),
l_rel_subtyp(i),
l_rec_ver_num(i),
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
l_wt_percent(i)
);
l_obj_rel_id.delete;
l_obj_typ_from.delete;
l_obj_from_id.delete;
l_obj_typ_to.delete;
l_obj_to_id.delete;
l_rel_typ.delete;
l_rel_subtyp.delete;
l_rec_ver_num.delete;
l_wt_percent.delete;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,relationship_subtype
,Record_Version_Number
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,weighting_percentage
)
SELECT
pa_object_relationships_s.nextval
,pobj.object_type_from
,ppev1.element_version_id
,pobj.object_type_to
,ppev2.element_version_id
,pobj.relationship_type
,pobj.relationship_subtype
,pobj.Record_Version_Number
,l_user_id
,SYSDATE
,l_user_id
,SYSDATE
,l_login_id
,pobj.weighting_percentage
FROM
( SELECT object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
weighting_percentage
FROM pa_object_relationships
START WITH object_id_from1 = l_src_fin_version_id
AND RELATIONSHIP_TYPE = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND RELATIONSHIP_TYPE = 'S' ) pobj,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev1.attribute15 = pobj.object_id_from1
AND ppev2.attribute15 = pobj.object_id_to1
AND ppev1.project_id = p_dest_project_id
AND ppev2.project_id = p_dest_project_id ;
INSERT INTO pa_proj_elem_ver_schedule(
PEV_SCHEDULE_ID
,ELEMENT_VERSION_ID
,PROJECT_ID
,PROJ_ELEMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SCHEDULED_START_DATE
,SCHEDULED_FINISH_DATE
,OBLIGATION_START_DATE
,OBLIGATION_FINISH_DATE
,ACTUAL_START_DATE
,ACTUAL_FINISH_DATE
,ESTIMATED_START_DATE
,ESTIMATED_FINISH_DATE
,DURATION
,EARLY_START_DATE
,EARLY_FINISH_DATE
,LATE_START_DATE
,LATE_FINISH_DATE
,CALENDAR_ID
,MILESTONE_FLAG
,CRITICAL_FLAG
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,WQ_PLANNED_QUANTITY
,PLANNED_EFFORT
,ACTUAL_DURATION
,ESTIMATED_DURATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,source_object_id
,source_object_type
)
SELECT
pa_proj_elem_ver_schedule_s.nextval
,ppev1.ELEMENT_VERSION_ID
,p_dest_project_id
,ppev1.PROJ_ELEMENT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,PA_PROJ_TASK_STRUC_PUB.get_adjusted_dates( p_target_start_date,
p_target_finish_date,
l_delta,
ppevs.SCHEDULED_START_DATE,
null )
,PA_PROJ_TASK_STRUC_PUB.get_adjusted_dates( p_target_start_date,
p_target_finish_date,
l_delta,
ppevs.SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE )
,ppevs.OBLIGATION_START_DATE
,ppevs.OBLIGATION_FINISH_DATE
,null
,null
,null
,null
,ppevs.DURATION
,ppevs.EARLY_START_DATE
,ppevs.EARLY_FINISH_DATE
,ppevs.LATE_START_DATE
,ppevs.LATE_FINISH_DATE
,p_calendar_id
,ppevs.MILESTONE_FLAG
,ppevs.CRITICAL_FLAG
,ppevs.RECORD_VERSION_NUMBER
,l_login_id
,ppevs.WQ_PLANNED_QUANTITY
,ppevs.PLANNED_EFFORT
,ppevs.ACTUAL_DURATION
,ppevs.ESTIMATED_DURATION
,ppevs.ATTRIBUTE_CATEGORY
,ppevs.ATTRIBUTE1
,ppevs.ATTRIBUTE2
,ppevs.ATTRIBUTE3
,ppevs.ATTRIBUTE4
,ppevs.ATTRIBUTE5
,ppevs.ATTRIBUTE6
,ppevs.ATTRIBUTE7
,ppevs.ATTRIBUTE8
,ppevs.ATTRIBUTE9
,ppevs.ATTRIBUTE10
,ppevs.ATTRIBUTE11
,ppevs.ATTRIBUTE12
,ppevs.ATTRIBUTE13
,ppevs.ATTRIBUTE14
,ppevs.ATTRIBUTE15
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_elem_ver_schedule ppevs,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev1.project_id = p_dest_project_id
AND ppev1.attribute15 = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id
AND ppev1.attribute15 = ppev2.element_version_id
AND ppev2.project_id = p_src_project_id
AND ppev2.parent_structure_version_id = l_src_fin_version_id ;
SELECT ppwa.WP_ENABLE_VERSION_FLAG
FROM pa_proj_workplan_attr ppwa
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppwa.project_id = p_src_project_id
AND ppwa.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'WORKPLAN';
SELECT 'Y'
FROM dual
WHERE EXISTS( SELECT 'Y'
FROM PA_PROJECT_COPY_OPTIONS_TMP
WHERE CONTEXT = 'WORKPLAN'
AND VERSION_ID IS NOT NULL) ;
SELECT FLAG
FROM PA_PROJECT_COPY_OPTIONS_TMP
WHERE CONTEXT = c_flag_name ;
SELECT ppe.proj_element_id,
ppe.attribute15
FROM pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = c_structure_type ;
SELECT p.person_id
FROM per_all_people_f p,
fnd_user f
WHERE f.employee_id = p.person_id
AND sysdate between p.effective_start_date and p.effective_end_date
AND f.user_id = c_user_id;
SELECT ppev.element_version_id
,ppev.attribute15
,ppcot.flag
FROM pa_proj_element_versions ppev,
pa_project_copy_options_tmp ppcot
WHERE ppev.project_id = p_dest_project_id
AND ppev.proj_element_id = c_dest_wp_struc_id
AND ppev.attribute15 = ppcot.version_id
AND ppcot.context = 'WORKPLAN' ;
SELECT scheduled_start_date
FROM pa_proj_elem_ver_schedule
WHERE project_id = c_project_id
AND element_version_id = c_struc_ver_id;
SELECT nvl(max(version_number),0)+1
FROM pa_proj_elem_ver_structure
WHERE project_id = c_project_id
AND proj_element_id = c_proj_element_id
AND status_code <> 'STRUCTURE_PUBLISHED';
SELECT ppe.proj_element_id,
ppe.attribute15
FROM pa_proj_elements ppe
WHERE ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppe.proj_element_id ) = 'Y' ;
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev
WHERE ppev.project_id = p_dest_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppev.proj_element_id ) = 'Y' ;
SELECT dest.attribute15 src_ppe_id,
dest.proj_element_id dest_ppe_id
FROM pa_proj_elements dest
WHERE dest.project_id = p_dest_project_id
AND dest.object_type = 'PA_TASKS'
AND dest.parent_structure_id = c_dest_parent_structure_id ;
SELECT old.carrying_out_organization_id,
new.carrying_out_organization_id
FROM pa_projects_all old,
pa_projects_all new
WHERE old.project_id = p_src_project_id
AND new.project_id = p_dest_project_id;
SELECT template_flag
FROM pa_projects_all
WHERE project_id = l_prj_id ;
SELECT ppv.element_version_id
FROM pa_proj_element_versions ppv ,
pa_proj_structure_types pps,
pa_structure_types pst
WHERE ppv.proj_element_id = pps.proj_element_id
AND ppv.object_type = 'PA_STRUCTURES'
AND pps.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'WORKPLAN'
AND ppv.project_id = l_proj_id ;
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code = 'STRUCTURE_PUBLISHED';
select nvl(max(version_number),0)+1
from pa_proj_elem_ver_structure
where project_id = c_project_id
and proj_element_id = c_proj_element_id
and status_code <> 'STRUCTURE_PUBLISHED';
SELECT wp_approval_reqd_flag, wp_auto_publish_flag, wp_approver_source_id
FROM PA_PROJ_WORKPLAN_ATTR ppwa
, PA_PROJ_STRUCTURE_TYPES ppst
, PA_STRUCTURE_TYPES pst
WHERE ppwa.PROJECT_ID = p_src_project_id
AND ppwa.PROJ_ELEMENT_ID = ppst.PROJ_ELEMENT_ID
AND ppst.structure_type_id = pst.structure_type_id
and pst.structure_type = 'WORKPLAN';
l_current_working_selected NUMBER(15) := NULL;
SELECT /*+ USE_HASH(ppev2 ppev1)*/
pa_object_relationships_s.nextval,
pobj.object_type_from,
ppev1.element_version_id,
pobj.object_type_to,
ppev2.element_version_id,
pobj.relationship_type,
pobj.relationship_subtype,
pobj.Record_Version_Number,
pobj.weighting_percentage
FROM ( SELECT object_type_from, object_id_from1,
object_type_to, object_id_to1,
relationship_type, relationship_subtype,
Record_Version_Number, weighting_percentage
FROM pa_object_relationships
START WITH object_id_from1 = c_src_str_version_id
AND RELATIONSHIP_TYPE = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND RELATIONSHIP_TYPE = 'S' ) pobj,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev1.attribute15 = pobj.object_id_from1
AND ppev2.attribute15 = pobj.object_id_to1
AND ppev1.parent_structure_version_id = c_struc_version_id
AND ppev2.parent_structure_version_id = c_struc_version_id ;
UPDATE pa_proj_elements ppe1
SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
WHERE ppe2.project_id = p_src_project_id
AND ppe2.proj_element_id = ppe1.attribute15 )
WHERE project_id = p_dest_project_id ;
UPDATE pa_proj_element_versions ppevs1
SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
WHERE ppevs2.project_id = p_src_project_id
AND ppevs2.element_version_id = ppevs1.attribute15 )
WHERE project_id = p_dest_project_id ;
/**** THE FOLLOWING SECTION INSERTS WORKPLAN AND FINANCIAL STRUCTURES AND RELATED ATTRIBUTES INTO THE
DESTINATION PROJECT ****/
-------------------------------------------------------------------------------------------------------
l_name := substr(p_dest_project_name, 1, 240);
Pa_Debug.WRITE(G_PKG_NAME, 'Atleast one WP ver selected : '||l_min_one_wp_ver_sel, l_debug_level3);
INSERT INTO pa_proj_elements(
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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LINK_TASK_FLAG
,BASELINE_START_DATE
,BASELINE_FINISH_DATE
,PROGRESS_OUTDATED_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,PARENT_STRUCTURE_ID
,TYPE_CODE
,INC_PROJ_PROGRESS_FLAG
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,BASELINE_DURATION
,PHASE_CODE
,PHASE_VERSION_ID
,PROGRESS_WEIGHT
,BASE_PERCENT_COMP_DERIV_CODE
,FUNCTION_CODE
,ENABLE_WF_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_START_LEAD_DAYS
,source_object_id
,source_object_type
)
SELECT
pa_tasks_s.nextval
,p_dest_project_id
,ppe.object_type
,pa_tasks_s.nextval --Set element_number same as proj_element_id
,l_name
,ppe.DESCRIPTION
,ppe.STATUS_CODE
,ppe.WF_STATUS_CODE
,NULL --bug 3810263 replacing ppe.PM_SOURCE_CODE
,NULL --bug 3810263 replacing ppe.PM_SOURCE_REFERENCE
,ppe.CLOSED_DATE
,ppe.LOCATION_ID
,ppe.MANAGER_PERSON_ID
,ppe.CARRYING_OUT_ORGANIZATION_ID
,ppe.TYPE_ID
,ppe.PRIORITY_CODE
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppe.RECORD_VERSION_NUMBER
,ppe.REQUEST_ID
,ppe.PROGRAM_APPLICATION_ID
,ppe.PROGRAM_ID
,ppe.PROGRAM_UPDATE_DATE
,ppe.LINK_TASK_FLAG
,ppe.BASELINE_START_DATE
,ppe.BASELINE_FINISH_DATE
,'N' --,ppe.progress_outdated_flag
,ppe.ATTRIBUTE_CATEGORY
,ppe.ATTRIBUTE1
,ppe.ATTRIBUTE2
,ppe.ATTRIBUTE3
,ppe.ATTRIBUTE4
,ppe.ATTRIBUTE5
,ppe.ATTRIBUTE6
,ppe.ATTRIBUTE7
,ppe.ATTRIBUTE8
,ppe.ATTRIBUTE9
,ppe.ATTRIBUTE10
,ppe.ATTRIBUTE11
,ppe.ATTRIBUTE12
,ppe.ATTRIBUTE13
,ppe.ATTRIBUTE14
,ppe.proj_element_id --attribute 15 is used to store old structure id to identify the structure type
,null --parent_structure_id is NULL in case of structures
,ppe.TYPE_CODE
,ppe.INC_PROJ_PROGRESS_FLAG
,ppe.WQ_ITEM_CODE
,ppe.WQ_UOM_CODE
,ppe.WQ_ACTUAL_ENTRY_CODE
,ppe.TASK_PROGRESS_ENTRY_PAGE_ID
,ppe.BASELINE_DURATION
,ppe.PHASE_CODE
,ppe.PHASE_VERSION_ID
,ppe.PROGRESS_WEIGHT
,ppe.BASE_PERCENT_COMP_DERIV_CODE
,ppe.FUNCTION_CODE
,ppe.ENABLE_WF_FLAG --'N' Bug 3616964
,ppe.WF_ITEM_TYPE
,ppe.WF_PROCESS
,ppe.WF_START_LEAD_DAYS
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_elements ppe
WHERE ppe.project_id = p_src_project_id
AND ppe.proj_element_id IN
( SELECT ppe2.proj_element_id
FROM pa_proj_elements ppe2
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppe2.project_id = p_src_project_id
AND ppe2.object_type = 'PA_STRUCTURES'
AND ppe2.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type IN ('WORKPLAN','FINANCIAL') );
SELECT meaning INTO l_suffix
FROM pa_lookups
WHERE lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
AND lookup_code = 'WORKPLAN';
UPDATE pa_proj_elements ppe
SET name = substr(p_dest_project_name||l_append||l_suffix, 1, 240)
WHERE project_id = p_dest_project_id
AND object_type = 'PA_STRUCTURES'
AND EXISTS ( SELECT proj_element_id FROM pa_proj_structure_types
WHERE proj_element_id = ppe.proj_element_id
AND structure_type_id = 1 );
INSERT INTO PA_PROJ_STRUCTURE_TYPES(
PROJ_STRUCTURE_TYPE_ID
,PROJ_ELEMENT_ID
,STRUCTURE_TYPE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER )
SELECT
pa_proj_structure_types_s.nextval
,ppe.proj_element_id
,ppst.structure_type_id --structure type
,sysdate
,l_user_id
,sysdate
,l_user_id
,l_login_id
,1
FROM pa_proj_elements ppe,
pa_proj_structure_types ppst
WHERE ppe.project_id = p_dest_project_id
AND ppe.attribute15 IN --DoNot use PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppe.attribute15 ) = 'Y'
( SELECT ppe2.proj_element_id
FROM pa_proj_elements ppe2
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppe2.project_id = p_src_project_id
AND ppe2.object_type = 'PA_STRUCTURES'
AND ppe2.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type IN ('WORKPLAN','FINANCIAL') )
AND ppe.attribute15 = ppst.proj_element_id ; --proj_element_id is unique in pa_proj_structure_types
INSERT INTO pa_proj_workplan_attr(
PROJ_ELEMENT_ID
,PROJECT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,WP_APPROVAL_REQD_FLAG
,WP_AUTO_PUBLISH_FLAG
,WP_DEFAULT_DISPLAY_LVL
,WP_ENABLE_VERSION_FLAG
,AUTO_PUB_UPON_CREATION_FLAG
,AUTO_SYNC_TXN_DATE_FLAG
,WP_APPROVER_SOURCE_ID
,WP_APPROVER_SOURCE_TYPE
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,TXN_DATE_SYNC_BUF_DAYS
,LIFECYCLE_VERSION_ID
,CURRENT_PHASE_VERSION_ID
,SCHEDULE_THIRD_PARTY_FLAG
,ALLOW_LOWEST_TSK_DEP_FLAG
-- ,PROGRAM_FLAG
,AUTO_ROLLUP_SUBPROJ_FLAG
,THIRD_PARTY_SCHEDULE_CODE
,source_object_id
,source_object_type
)
SELECT
ppe.PROJ_ELEMENT_ID
,ppe.PROJECT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,ppwa.WP_APPROVAL_REQD_FLAG
,ppwa.WP_AUTO_PUBLISH_FLAG
,ppwa.WP_DEFAULT_DISPLAY_LVL
,ppwa.WP_ENABLE_VERSION_FLAG
,ppwa.AUTO_PUB_UPON_CREATION_FLAG
,ppwa.AUTO_SYNC_TXN_DATE_FLAG
,ppwa.WP_APPROVER_SOURCE_ID
,ppwa.WP_APPROVER_SOURCE_TYPE
,ppwa.RECORD_VERSION_NUMBER
,l_login_id
,ppwa.TXN_DATE_SYNC_BUF_DAYS
,ppwa.LIFECYCLE_VERSION_ID
,ppwa.CURRENT_PHASE_VERSION_ID
,ppwa.SCHEDULE_THIRD_PARTY_FLAG
,ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
-- ,ppwa.PROGRAM_FLAG
,ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
,ppwa.THIRD_PARTY_SCHEDULE_CODE
,ppe.PROJECT_ID
,'PA_PROJECTS'
FROM pa_proj_elements ppe,
pa_proj_workplan_attr ppwa
WHERE ppwa.project_id = p_src_project_id
AND ppe.attribute15 = ppwa.proj_element_id
AND PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppe.proj_element_id ) = 'Y'
AND ppe.project_id = p_dest_project_id --bug 3939786
;
INSERT INTO pa_proj_progress_attr(
PROJ_PROGRESS_ATTR_ID
,OBJECT_TYPE
,OBJECT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PROJECT_ID
,PROGRESS_CYCLE_ID
,WQ_ENABLE_FLAG
,REMAIN_EFFORT_ENABLE_FLAG
,PERCENT_COMP_ENABLE_FLAG
,NEXT_PROGRESS_UPDATE_DATE
,RECORD_VERSION_NUMBER
,TASK_WEIGHT_BASIS_CODE
,ALLOW_COLLAB_PROG_ENTRY
,ALLOW_PHY_PRCNT_CMP_OVERRIDES
,STRUCTURE_TYPE
)
SELECT
PA_PROJ_PROGRESS_ATTR_S.nextval
,ppe.OBJECT_TYPE
,ppe.proj_element_ID
,sysdate
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,p_dest_project_id
,pppa.PROGRESS_CYCLE_ID
,pppa.WQ_ENABLE_FLAG
,pppa.REMAIN_EFFORT_ENABLE_FLAG
,pppa.PERCENT_COMP_ENABLE_FLAG
,pppa.NEXT_PROGRESS_UPDATE_DATE
,pppa.RECORD_VERSION_NUMBER
,pppa.TASK_WEIGHT_BASIS_CODE
,pppa.ALLOW_COLLAB_PROG_ENTRY
,pppa.ALLOW_PHY_PRCNT_CMP_OVERRIDES
,pppa.STRUCTURE_TYPE
FROM pa_proj_progress_attr pppa,
pa_proj_elements ppe
WHERE ppe.project_id = p_dest_project_id
AND PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppe.proj_element_id ) = 'Y'
AND ppe.attribute15 = pppa.object_id
AND pppa.project_id = p_src_project_id
AND pppa.object_type = 'PA_STRUCTURES' ;
Pa_Debug.WRITE(G_PKG_NAME, 'After inserting WP/FIN structures and related attributes', l_debug_level3);
/**** THE FOLLOWING SECTION COPIES/INSERTS WORKPLAN VERSIONS AND TASKS INTO THE DESTINATION PROJECT ****/
---------------------------------------------------------------------------------------------------------
--IF 'ATLEAST ONE WP VER IS SELECTED' FOR COPYING THEN
-- Copy selected version(s) and tasks belonging to these version(s)
-- Call API to sync up pa_tasks with pa_proj_elements in case of SHARED structure
--ELSE (if no WP ver selected for copying)
-- IF 'SHARED' AND 'FINANCIAL TASKS FLAG IS CHECKED' THEN
-- Copy financial structure version in the source project and tasks belonging to this version
-- ENF IF;
Pa_Debug.WRITE(G_PKG_NAME, 'Inserting versions and tasks for selected WP version(s)', l_debug_level3);
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --This column stores the stucture version id in the src project, to be used to create relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,FINANCIAL_TASK_FLAG
,PRG_GROUP
,PRG_LEVEL
,PRG_COUNT
,source_object_id
,source_object_type
)
SELECT
pa_proj_element_versions_s.nextval
,l_dest_wp_structure_id
,ppev.object_type
,p_dest_project_id
,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,ppev.FINANCIAL_TASK_FLAG
,NULL --bug 4261419
,NULL --bug 4261419
,NULL --bug 4261419
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_element_versions ppev,
pa_project_copy_options_tmp ppcot
WHERE ppcot.context = 'WORKPLAN'
AND ppev.element_version_id = ppcot.version_id
AND ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES' ;
SELECT version_id
FROM pa_project_copy_options_tmp ppcot
WHERE context = 'WORKPLAN'
AND flag = 'Y';
CURSOR get_current_working_selected IS
SELECT ppevs.element_version_id
FROM pa_proj_elem_ver_structure ppevs,
pa_project_copy_options_tmp ppcot
WHERE ppevs.current_working_flag = 'Y'
AND ppevs.status_code = 'STRUCTURE_WORKING'
AND ppevs.project_id = p_src_project_id
AND ppevs.element_version_id = ppcot.version_id
AND ppcot.context = 'WORKPLAN' ;
SELECT element_version_id FROM
( SELECT ppevs.element_version_id
FROM pa_proj_elem_ver_structure ppevs,
pa_project_copy_options_tmp ppcot
WHERE ppevs.element_version_id = ppcot.version_id
AND ppevs.project_id = p_src_project_id
AND ppevs.status_code = 'STRUCTURE_WORKING'
AND ppcot.context = 'WORKPLAN'
AND ppcot.flag = 'N'
ORDER BY ppevs.last_update_date desc)
WHERE rownum = 1;
SELECT ppevs.element_version_id
FROM pa_proj_elem_ver_structure ppevs,
pa_project_copy_options_tmp ppcot
WHERE ppevs.latest_eff_published_flag = 'Y'
AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
AND ppevs.element_version_id = ppcot.version_id
AND ppcot.context = 'WORKPLAN'
AND ppevs.project_id= p_src_project_id --bug 4868867 sql id = 14909148
AND ppcot.flag = 'N' ;
SELECT element_version_id FROM
( SELECT ppevs.element_version_id
FROM pa_proj_elem_ver_structure ppevs,
pa_project_copy_options_tmp ppcot
WHERE ppevs.element_version_id = ppcot.version_id
AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
AND ppevs.project_id = p_src_project_id
AND ppcot.context = 'WORKPLAN'
AND ppcot.flag = 'N'
ORDER BY ppevs.published_date DESC )
WHERE ROWNUM = 1;
OPEN get_current_working_selected;
FETCH get_current_working_selected INTO l_current_working_selected;
pa_debug.write(G_PKG_NAME, 'l_current_working_selected='||l_current_working_selected, l_debug_level3);
IF l_current_working_selected IS NOT NULL AND l_current_working_selected <> nvl(l_puc_version_id,-88) THEN
l_cw_version_id := l_current_working_selected;
CLOSE get_current_working_selected;
IF l_current_working_selected = nvl(l_puc_version_id,-88) OR 'Y' = l_shared OR 'Y' = p_dest_template_flag THEN
l_dest_current_working_flag := 'Y';
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
PEV_STRUCTURE_ID
,ELEMENT_VERSION_ID
,VERSION_NUMBER
,NAME
,PROJECT_ID
,PROJ_ELEMENT_ID
,DESCRIPTION
,EFFECTIVE_DATE
,PUBLISHED_DATE
,PUBLISHED_BY_PERSON_ID
,CURRENT_BASELINE_DATE
,CURRENT_FLAG
,CURRENT_BASELINE_PERSON_ID
,ORIGINAL_BASELINE_DATE
,ORIGINAL_FLAG
,ORIGINAL_BASELINE_PERSON_ID
,LOCK_STATUS_CODE
,LOCKED_BY_PERSON_ID
,LOCKED_DATE
,STATUS_CODE
,WF_STATUS_CODE
,LATEST_EFF_PUBLISHED_FLAG
,RECORD_VERSION_NUMBER
,WBS_RECORD_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,change_reason_code
,PROCESS_UPDATE_WBS_FLAG
,current_working_flag
,source_object_id
,source_object_type
)
SELECT
pa_proj_elem_ver_structure_s.nextval
,rec_dest_wp_struc_versions.element_version_id
--,ppevs.version_number --bug 3907862, insert the new version number, intead of coying the old.
,l_struct_version_number
,ppevs.name
,p_dest_project_id
,ppe.proj_element_id
,ppevs.DESCRIPTION
,ppevs.EFFECTIVE_DATE
,decode(l_dest_status_code, 'STRUCTURE_PUBLISHED', sysdate, to_date(NULL))
,decode(l_dest_status_code, 'STRUCTURE_PUBLISHED', l_publish_person_id, NULL)
,decode(l_dest_latest_eff_pub_flag, 'Y', trunc(sysdate), to_date(NULL))
,l_dest_latest_eff_pub_flag
,decode(l_dest_latest_eff_pub_flag, 'Y', l_publish_person_id, NULL)
,decode(l_dest_latest_eff_pub_flag, 'Y', trunc(sysdate), to_date(NULL))
,l_dest_latest_eff_pub_flag
,decode(l_dest_latest_eff_pub_flag, 'Y', l_publish_person_id, NULL)
,'UNLOCKED'
,null
,null
,l_dest_status_code
,ppevs.WF_STATUS_CODE
,l_dest_latest_eff_pub_flag --If version is 'publish upon creation', it is the latest eff published version
--In case of version disabled, it'll always be 'publish upon creation'
,ppevs.RECORD_VERSION_NUMBER
,ppevs.WBS_RECORD_VERSION_NUMBER
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppevs.change_reason_code
,ppevs.process_update_wbs_flag
,decode(l_versioning_enabled,'N','Y',l_dest_current_working_flag)
--If versioning is disabled, both LATEST PUBLISHED AND CURRENT WORKING FLAGS are 'Y'
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_elements ppe
WHERE ppevs.project_id = p_src_project_id
AND ppevs.element_version_id = rec_dest_wp_struc_versions.attribute15
AND ppevs.proj_element_id = ppe.attribute15
AND ppe.proj_element_id = l_dest_wp_structure_id;
SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
FROM dual;
SELECT meaning INTO l_prefix
FROM pa_lookups
WHERE lookup_type = 'PA_STRUCTURES_PREFIX'
AND lookup_code = 'PA_PREFIX_COPY';
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --This column stores the stucture version id in the src project, to be used to create relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,FINANCIAL_TASK_FLAG
,source_object_id
,source_object_type
)
SELECT
l_new_wp_ver_id
,ppev.proj_element_id
,ppev.object_type
,ppev.project_id
,l_new_wp_ver_id
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.attribute15
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,ppev.FINANCIAL_TASK_FLAG
,ppev.project_id
,'PA_PROJECTS'
FROM pa_proj_element_versions ppev
WHERE ppev.project_id = p_dest_project_id
AND ppev.element_version_id = l_shared_make_working_copy ;
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
PEV_STRUCTURE_ID
,ELEMENT_VERSION_ID
,VERSION_NUMBER
,NAME
,PROJECT_ID
,PROJ_ELEMENT_ID
,DESCRIPTION
,EFFECTIVE_DATE
,PUBLISHED_DATE
,PUBLISHED_BY_PERSON_ID
,CURRENT_BASELINE_DATE
,CURRENT_FLAG
,CURRENT_BASELINE_PERSON_ID
,ORIGINAL_BASELINE_DATE
,ORIGINAL_FLAG
,ORIGINAL_BASELINE_PERSON_ID
,LOCK_STATUS_CODE
,LOCKED_BY_PERSON_ID
,LOCKED_DATE
,STATUS_CODE
,WF_STATUS_CODE
,LATEST_EFF_PUBLISHED_FLAG
,RECORD_VERSION_NUMBER
,WBS_RECORD_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,change_reason_code
,PROCESS_UPDATE_WBS_FLAG
,current_working_flag
,source_object_id
,source_object_type
)
SELECT
pa_proj_elem_ver_structure_s.nextval
,l_new_wp_ver_id
,ppevs.version_number
,substr(l_prefix||ppevs.name, 1, 240)
,ppevs.project_id
,ppe.proj_element_id
,ppevs.DESCRIPTION
,ppevs.EFFECTIVE_DATE
,to_date(NULL)
,NULL
,to_date(NULL)
,'N'
,NULL
,to_date(NULL)
,'N'
,NULL
,'UNLOCKED'
,null
,null
,'STRUCTURE_WORKING'
,ppevs.WF_STATUS_CODE
,'N'
,ppevs.RECORD_VERSION_NUMBER
,ppevs.WBS_RECORD_VERSION_NUMBER
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppevs.change_reason_code
,ppevs.process_update_wbs_flag
,'Y'
,ppevs.project_id
,'PA_PROJECTS'
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_elements ppe
WHERE ppevs.project_id = p_dest_project_id
AND ppevs.element_version_id = l_shared_make_working_copy
AND ppevs.proj_element_id = ppe.proj_element_id
AND ppe.proj_element_id = l_dest_wp_structure_id;
INSERT INTO pa_proj_elements(
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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LINK_TASK_FLAG
,BASELINE_START_DATE
,BASELINE_FINISH_DATE
,progress_outdated_flag
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,parent_structure_id
,TYPE_CODE
,INC_PROJ_PROGRESS_FLAG
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,BASELINE_DURATION
,PHASE_CODE
,PHASE_VERSION_ID
,PROGRESS_WEIGHT
,BASE_PERCENT_COMP_DERIV_CODE
,FUNCTION_CODE
,ENABLE_WF_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_START_LEAD_DAYS
,source_object_id
,source_object_type
)
SELECT
nvl(pt.task_id,pa_tasks_s.nextval)
,p_dest_project_id
,ppe.object_type
,ppe.element_number
,ppe.name
,ppe.DESCRIPTION
,INITIAL_STATUS_CODE
,ppe.WF_STATUS_CODE
,NULL --bug 3810263 replacing ppe.PM_SOURCE_CODE
,NULL --bug 3810263 replacing ppe.PM_SOURCE_REFERENCE
,ppe.CLOSED_DATE
,ppe.LOCATION_ID
,ppe.MANAGER_PERSON_ID
,decode(ppe.CARRYING_OUT_ORGANIZATION_ID,l_old_proj_org_id, l_new_proj_org_id,
ppe.CARRYING_OUT_ORGANIZATION_ID) -- Bug 3387963
,ppe.TYPE_ID
,ppe.PRIORITY_CODE
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppe.RECORD_VERSION_NUMBER
,ppe.REQUEST_ID
,ppe.PROGRAM_APPLICATION_ID
,ppe.PROGRAM_ID
,ppe.PROGRAM_UPDATE_DATE
,ppe.LINK_TASK_FLAG
,ppe.BASELINE_START_DATE
,ppe.BASELINE_FINISH_DATE
,'N' --,ppe.progress_outdated_flag
,ppe.ATTRIBUTE_CATEGORY
,ppe.ATTRIBUTE1
,ppe.ATTRIBUTE2
,ppe.ATTRIBUTE3
,ppe.ATTRIBUTE4
,ppe.ATTRIBUTE5
,ppe.ATTRIBUTE6
,ppe.ATTRIBUTE7
,ppe.ATTRIBUTE8
,ppe.ATTRIBUTE9
,ppe.ATTRIBUTE10
,ppe.ATTRIBUTE11
,ppe.ATTRIBUTE12
,ppe.ATTRIBUTE13
,ppe.ATTRIBUTE14
,ppe.proj_element_id --attribute 15 is used to store old ids.
,l_dest_wp_structure_id
,ppe.TYPE_CODE
,ppe.INC_PROJ_PROGRESS_FLAG
,ppe.WQ_ITEM_CODE
,ppe.WQ_UOM_CODE
,ppe.WQ_ACTUAL_ENTRY_CODE
,ppe.TASK_PROGRESS_ENTRY_PAGE_ID
,ppe.BASELINE_DURATION
,ppe.PHASE_CODE
,ppe.PHASE_VERSION_ID
,ppe.PROGRESS_WEIGHT
,ppe.BASE_PERCENT_COMP_DERIV_CODE
,ppe.FUNCTION_CODE
,'N'
,ppe.WF_ITEM_TYPE
,ppe.WF_PROCESS
,ppe.WF_START_LEAD_DAYS
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_task_types ptt,
pa_tasks pt,
pa_proj_elements ppe
WHERE ptt.object_type = 'PA_TASKS'
AND ppe.type_id = ptt.task_type_id
AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
AND pt.task_number (+) = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
AND pt.project_id (+) = p_dest_project_id
AND ppe.proj_element_id IN
( SELECT distinct ppev.proj_element_id
FROM PA_PROJ_ELEMENT_VERSIONS ppev,
PA_PROJECT_COPY_OPTIONS_TMP ppcot
WHERE ppcot.CONTEXT = 'WORKPLAN'
AND ppev.parent_structure_version_id = ppcot.version_id
AND ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_TASKS' ) ;
INSERT INTO pa_proj_elements(
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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LINK_TASK_FLAG
,BASELINE_START_DATE
,BASELINE_FINISH_DATE
,progress_outdated_flag
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,parent_structure_id
,TYPE_CODE
,INC_PROJ_PROGRESS_FLAG
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,BASELINE_DURATION
,PHASE_CODE
,PHASE_VERSION_ID
,PROGRESS_WEIGHT
,BASE_PERCENT_COMP_DERIV_CODE
,FUNCTION_CODE
,ENABLE_WF_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_START_LEAD_DAYS
,source_object_id
,source_object_type
)
SELECT
pa_tasks_s.nextval
,p_dest_project_id
,ppe.object_type
,ppe.element_number
,ppe.name
,ppe.DESCRIPTION
,INITIAL_STATUS_CODE
,ppe.WF_STATUS_CODE
,NULL --bug 3810263 replacing ppe.PM_SOURCE_CODE
,NULL --bug 3810263 replacing ppe.PM_SOURCE_REFERENCE
,ppe.CLOSED_DATE
,ppe.LOCATION_ID
,ppe.MANAGER_PERSON_ID
,decode(ppe.CARRYING_OUT_ORGANIZATION_ID,l_old_proj_org_id, l_new_proj_org_id,
ppe.CARRYING_OUT_ORGANIZATION_ID) -- Bug 3387963
,ppe.TYPE_ID
,ppe.PRIORITY_CODE
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppe.RECORD_VERSION_NUMBER
,ppe.REQUEST_ID
,ppe.PROGRAM_APPLICATION_ID
,ppe.PROGRAM_ID
,ppe.PROGRAM_UPDATE_DATE
,ppe.LINK_TASK_FLAG
,ppe.BASELINE_START_DATE
,ppe.BASELINE_FINISH_DATE
,'N' --,ppe.progress_outdated_flag
,ppe.ATTRIBUTE_CATEGORY
,ppe.ATTRIBUTE1
,ppe.ATTRIBUTE2
,ppe.ATTRIBUTE3
,ppe.ATTRIBUTE4
,ppe.ATTRIBUTE5
,ppe.ATTRIBUTE6
,ppe.ATTRIBUTE7
,ppe.ATTRIBUTE8
,ppe.ATTRIBUTE9
,ppe.ATTRIBUTE10
,ppe.ATTRIBUTE11
,ppe.ATTRIBUTE12
,ppe.ATTRIBUTE13
,ppe.ATTRIBUTE14
,ppe.proj_element_id --attribute 15 is used to store old ids.
,l_dest_wp_structure_id
,ppe.TYPE_CODE
,ppe.INC_PROJ_PROGRESS_FLAG
,ppe.WQ_ITEM_CODE
,ppe.WQ_UOM_CODE
,ppe.WQ_ACTUAL_ENTRY_CODE
,ppe.TASK_PROGRESS_ENTRY_PAGE_ID
,ppe.BASELINE_DURATION
,ppe.PHASE_CODE
,ppe.PHASE_VERSION_ID
,ppe.PROGRESS_WEIGHT
,ppe.BASE_PERCENT_COMP_DERIV_CODE
,ppe.FUNCTION_CODE
,'N'
,ppe.WF_ITEM_TYPE
,ppe.WF_PROCESS
,ppe.WF_START_LEAD_DAYS
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_task_types ptt,
pa_proj_elements ppe
WHERE ptt.object_type = 'PA_TASKS'
AND ppe.type_id = ptt.task_type_id
AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
AND ppe.proj_element_id IN
( SELECT distinct ppev.proj_element_id
FROM PA_PROJ_ELEMENT_VERSIONS ppev,
PA_PROJECT_COPY_OPTIONS_TMP ppcot
WHERE ppcot.CONTEXT = 'WORKPLAN'
AND ppev.parent_structure_version_id = ppcot.version_id
AND ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_TASKS' ) ;
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --this column is used to store task ver id of the source project's task versions to be used to created relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,FINANCIAL_TASK_FLAG
,source_object_id
,source_object_type
)
SELECT
pa_proj_element_versions_s.nextval
,ppe.proj_element_id
,ppev.object_type
,p_dest_project_id
,ppev2.element_version_id
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id -- to help create relationships from source
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,decode( ppevs.status_code, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
,ppev.FINANCIAL_TASK_FLAG --Check if this is correct ?
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_proj_element_versions ppev2,
pa_proj_elem_ver_structure ppevs
WHERE ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_TASKS'
AND ppe.attribute15 = ppev.proj_element_id
AND ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_TASKS'
AND ppev.parent_structure_version_id = ppev2.attribute15
AND ppev2.project_id = p_dest_project_id
AND ppev2.object_type = 'PA_STRUCTURES'
AND ppevs.element_version_id = ppev2.element_version_id
AND ppevs.project_id = p_dest_project_id
AND ppevs.project_id = ppe.project_id -- Bug 4141027
AND ppev2.project_id = ppe.project_id -- Bug 4141027
;
Pa_Debug.WRITE(G_PKG_NAME, 'Going to insert object relationships',
l_debug_level3);
l_obj_rel_id.delete;
l_obj_typ_from.delete;
l_obj_from_id.delete;
l_obj_typ_to.delete;
l_obj_to_id.delete;
l_rel_typ.delete;
l_rel_subtyp.delete;
l_rec_ver_num.delete;
l_wt_percent.delete;
insert into PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
values(
l_obj_rel_id(i),
l_obj_typ_from(i),
l_obj_from_id(i),
l_obj_typ_to(i),
l_obj_to_id(i),
l_rel_typ(i),
l_rel_subtyp(i),
l_rec_ver_num(i),
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
l_wt_percent(i)
);
l_obj_rel_id.delete;
l_obj_typ_from.delete;
l_obj_from_id.delete;
l_obj_typ_to.delete;
l_obj_to_id.delete;
l_rel_typ.delete;
l_rel_subtyp.delete;
l_rec_ver_num.delete;
l_wt_percent.delete;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,relationship_subtype
,Record_Version_Number
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,weighting_percentage
)
/*Added the hint for the bug 3513853*/
/* SELECT /*+ USE_HASH(ppev2 ppev1)*/
/* pa_object_relationships_s.nextval
,pobj.object_type_from
,ppev1.element_version_id
,pobj.object_type_to
,ppev2.element_version_id
,pobj.relationship_type
,pobj.relationship_subtype
,pobj.Record_Version_Number
,l_user_id
,SYSDATE
,l_user_id
,SYSDATE
,l_login_id
,pobj.weighting_percentage
FROM
( SELECT object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
weighting_percentage
FROM pa_object_relationships
START WITH object_id_from1 = rec_dest_wp_struc_versions.attribute15
AND RELATIONSHIP_TYPE = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND RELATIONSHIP_TYPE = 'S' ) pobj,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev1.attribute15 = pobj.object_id_from1
AND ppev2.attribute15 = pobj.object_id_to1
AND ppev1.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
AND ppev2.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id ;
INSERT INTO pa_proj_elem_ver_schedule(
PEV_SCHEDULE_ID
,ELEMENT_VERSION_ID
,PROJECT_ID
,PROJ_ELEMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SCHEDULED_START_DATE
,SCHEDULED_FINISH_DATE
,OBLIGATION_START_DATE
,OBLIGATION_FINISH_DATE
,ACTUAL_START_DATE
,ACTUAL_FINISH_DATE
,ESTIMATED_START_DATE
,ESTIMATED_FINISH_DATE
,DURATION
,EARLY_START_DATE
,EARLY_FINISH_DATE
,LATE_START_DATE
,LATE_FINISH_DATE
,CALENDAR_ID
,MILESTONE_FLAG
,CRITICAL_FLAG
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,WQ_PLANNED_QUANTITY
,PLANNED_EFFORT
,ACTUAL_DURATION
,ESTIMATED_DURATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,source_object_id
,source_object_type
,CONSTRAINT_TYPE_CODE --Bug 3762437
,CONSTRAINT_DATE
,FREE_SLACK
,TOTAL_SLACK
,EFFORT_DRIVEN_FLAG
,LEVEL_ASSIGNMENTS_FLAG
,EXT_ACT_DURATION
,EXT_REMAIN_DURATION
,EXT_SCH_DURATION --Bug 3762437
)
SELECT
pa_proj_elem_ver_schedule_s.nextval
,ppev.ELEMENT_VERSION_ID
,p_dest_PROJECT_ID
,ppev.PROJ_ELEMENT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,PA_PROJ_TASK_STRUC_PUB.get_adjusted_dates( p_target_start_date,
p_target_finish_date,
l_delta,
ppevs.SCHEDULED_START_DATE,
null )
,PA_PROJ_TASK_STRUC_PUB.get_adjusted_dates( p_target_start_date,
p_target_finish_date,
l_delta,
ppevs.SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE )
,ppevs.OBLIGATION_START_DATE
,ppevs.OBLIGATION_FINISH_DATE
,null
,null
,null
,null
,ppevs.DURATION
,ppevs.EARLY_START_DATE
,ppevs.EARLY_FINISH_DATE
,ppevs.LATE_START_DATE
,ppevs.LATE_FINISH_DATE
,p_calendar_id
,ppevs.MILESTONE_FLAG
,ppevs.CRITICAL_FLAG
,ppevs.RECORD_VERSION_NUMBER
,l_login_id
,ppevs.WQ_PLANNED_QUANTITY
,ppevs.PLANNED_EFFORT
,ppevs.ACTUAL_DURATION
,ppevs.ESTIMATED_DURATION
,ppevs.ATTRIBUTE_CATEGORY
,ppevs.ATTRIBUTE1
,ppevs.ATTRIBUTE2
,ppevs.ATTRIBUTE3
,ppevs.ATTRIBUTE4
,ppevs.ATTRIBUTE5
,ppevs.ATTRIBUTE6
,ppevs.ATTRIBUTE7
,ppevs.ATTRIBUTE8
,ppevs.ATTRIBUTE9
,ppevs.ATTRIBUTE10
,ppevs.ATTRIBUTE11
,ppevs.ATTRIBUTE12
,ppevs.ATTRIBUTE13
,ppevs.ATTRIBUTE14
,ppevs.ATTRIBUTE15
,p_dest_PROJECT_ID
,'PA_PROJECTS'
,CONSTRAINT_TYPE_CODE --Bug 3762437
,CONSTRAINT_DATE
,FREE_SLACK
,TOTAL_SLACK
,EFFORT_DRIVEN_FLAG
,LEVEL_ASSIGNMENTS_FLAG
,EXT_ACT_DURATION
,EXT_REMAIN_DURATION
,EXT_SCH_DURATION --Bug 3762437
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_schedule ppevs
WHERE ppev.project_id = p_dest_project_id
AND ppev.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
AND ppev.attribute15 = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id ;
ELSE --IF THERE IS NO WP VERSION SELECTED FOR COPYING
--If shared and copy financial tasks flag is checked
IF 'Y' = l_shared AND 'Y' = l_fin_tasks_flag THEN
--The following API call will copy the financial version from the source project to the destination project
copy_src_financial_version( p_init_msg_list => p_init_msg_list
,p_src_project_id => p_src_project_id
,p_dest_project_id => p_dest_project_id
,p_dest_fn_structure_id => l_dest_wp_structure_id
,p_shared => l_shared
,p_dest_template_flag => p_dest_template_flag
,p_publish_person_id => l_publish_person_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
FROM dual;
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PARENT_STRUCTURE_VERSION_ID
,WBS_NUMBER
,RECORD_VERSION_NUMBER
,FINANCIAL_TASK_FLAG
,source_object_id
,source_object_type
)
VALUES (
l_new_wp_ver_id
,l_dest_wp_structure_id
,'PA_STRUCTURES'
,p_dest_project_id
,sysdate
,l_user_id
,sysdate
,l_user_id
,l_login_id
,l_new_wp_ver_id
,'0'
,1
,'N'
,p_dest_project_id
,'PA_PROJECTS'
);
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
PEV_STRUCTURE_ID
,ELEMENT_VERSION_ID
,VERSION_NUMBER
,NAME
,PROJECT_ID
,PROJ_ELEMENT_ID
,PUBLISHED_DATE
,LATEST_EFF_PUBLISHED_FLAG
,PUBLISHED_BY_PERSON_ID
,CURRENT_BASELINE_DATE
,CURRENT_FLAG
,CURRENT_BASELINE_PERSON_ID
,ORIGINAL_BASELINE_DATE
,ORIGINAL_FLAG
,ORIGINAL_BASELINE_PERSON_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,STATUS_CODE
,RECORD_VERSION_NUMBER
,WBS_RECORD_VERSION_NUMBER
,CURRENT_WORKING_FLAG
,SOURCE_OBJECT_ID
,source_object_type)
VALUES (
pa_proj_elem_ver_structure_s.nextval
,l_new_wp_ver_id
,l_struc_ver_number
,substr(p_dest_project_name||l_append||l_suffix, 1, 240)
,p_dest_project_id
,l_dest_wp_structure_id
,decode(l_default_lat_pub_and_cw_flag,'Y',sysdate,to_date(null))
,l_default_lat_pub_and_cw_flag
,decode(l_default_lat_pub_and_cw_flag, 'Y', l_publish_person_id, NULL)
,decode(l_default_lat_pub_and_cw_flag,'Y',sysdate,to_date(null))
,l_default_lat_pub_and_cw_flag
,decode(l_default_lat_pub_and_cw_flag, 'Y', l_publish_person_id, NULL)
,decode(l_default_lat_pub_and_cw_flag,'Y',sysdate,to_date(null))
,l_default_lat_pub_and_cw_flag
,decode(l_default_lat_pub_and_cw_flag, 'Y', l_publish_person_id, NULL)
,sysdate
,l_user_id
,sysdate
,l_user_id
,l_login_id
,decode(l_default_lat_pub_and_cw_flag,'Y','STRUCTURE_PUBLISHED','STRUCTURE_WORKING')
,1
,1
,'Y'
,p_dest_project_id
,'PA_PROJECTS'
);
END IF;--If there is atleast one WP version selected
/**** THE FOLLOWING SECTION COPIES/INSERTS FINANCIAL VERSIONS AND TASKS INTO THE DESTINATION PROJECT ****/
---------------------------------------------------------------------------------------------------------
--If split and financial is enabled
IF 'N' = l_shared AND 'Y' = l_fin_enabled THEN
IF l_debug_mode = 'Y' THEN
Pa_Debug.WRITE(G_PKG_NAME, 'Creating financial version in split case', l_debug_level3);
/*UPDATE pa_proj_elem_ver_schedule
SET duration = PA_PROJ_TASK_STRUC_PUB.calc_duration( p_calendar_id,
scheduled_start_date,
scheduled_finish_date )
WHERE project_id = p_dest_project_id;*/
UPDATE pa_proj_elem_ver_schedule
SET duration = trunc(scheduled_finish_date) - trunc(scheduled_start_date) + 1
WHERE project_id = p_dest_project_id;
UPDATE PA_PROJ_ELEMENTS ppe
SET ( BASELINE_START_DATE
,BASELINE_FINISH_DATE
,BASELINE_DURATION
) = ( SELECT ppevs.scheduled_start_date
,ppevs.scheduled_finish_date
,ppevs.duration
FROM pa_proj_elem_ver_schedule ppevs,
pa_proj_element_versions ppev
WHERE ppevs.project_id = p_dest_project_id
AND ppevs.proj_element_id = ppev.proj_element_id
AND ppevs.element_version_id = ppev.element_version_id
AND ppev.project_id = p_dest_project_id
AND ppevs.proj_element_id = ppe.proj_element_id
AND ppev.proj_element_id = ppe.proj_element_id
AND ppev.parent_structure_version_id =
( SELECT ppevst.element_version_id --Changed the inner query for bug 4215666.
FROM pa_proj_elem_ver_structure ppevst,
pa_proj_structure_types struc,
pa_structure_types stype
WHERE ppevst.project_id = p_dest_project_id
AND ppevst.current_flag = 'Y'
AND ppevst.status_code = 'STRUCTURE_PUBLISHED'
AND ppevst.proj_element_id = struc.proj_element_id
AND struc.structure_type_id = stype.structure_type_id
AND stype.structure_type = 'WORKPLAN'
)
)
WHERE project_id = p_dest_project_id;
UPDATE pa_projects_all
SET ( BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration, baseline_as_of_date ) =
( SELECT BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration,
decode( BASELINE_START_DATE, null, null, sysdate)
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE project_id = p_dest_project_id
AND object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = 1
)
WHERE project_id = p_dest_project_id;
UPDATE pa_projects_all
SET ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE, duration,
decode( SCHEDULED_START_DATE, null, null, sysdate )
FROM pa_proj_elem_ver_schedule
WHERE project_id = p_dest_project_id
AND element_version_id = l_dest_latest_pub_ver_id
)
WHERE project_id = p_dest_project_id;
So,In that case,Select the Only Workplan Version Id that would be available for the template*/
OPEN c_template_flag(p_dest_project_id) ;
UPDATE pa_projects_all
SET ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE, duration,
decode( SCHEDULED_START_DATE, null, null, sysdate )
FROM pa_proj_elem_ver_schedule
WHERE project_id = p_dest_project_id
AND element_version_id = l_only_wp_ver_id
)
WHERE project_id = p_dest_project_id;
SELECT Element_Version_ID, attribute15 BULK COLLECT
INTO New_Versions_Tab, Old_Versions_Tab
FROM PA_Proj_Element_Versions
WHERE Project_ID = p_dest_project_id;
SELECT structure_sharing_code
FROM pa_projects_all
WHERE project_id = p_dest_project_id;
SELECT Element_Version_ID, attribute15 BULK COLLECT
INTO dest_versions_tab, src_Versions_Tab
FROM PA_Proj_Element_Versions
WHERE Project_ID = p_dest_project_id
AND ( PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(element_version_id, 'WORKPLAN') = 'Y')
AND object_type = 'PA_STRUCTURES';
/* Now update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project
UPDATE pa_proj_elements ppe1
SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
WHERE ppe2.project_id = p_src_project_id
AND ppe2.proj_element_id = ppe1.attribute15 )
WHERE project_id = p_dest_project_id ;
UPDATE pa_proj_element_versions ppevs1
SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
WHERE ppevs2.project_id = p_src_project_id
AND ppevs2.element_version_id = ppevs1.attribute15 )
WHERE project_id = p_dest_project_id ; */
select scheduled_start_date
from pa_proj_elem_ver_schedule
where project_id = c_project_id
and element_version_id = c_struc_ver_id;
select *
from pa_proj_workplan_attr
where project_id = p_src_project_id; */
select * from pa_proj_workplan_attr pppa where pppa.project_id=p_src_project_id
and exists (select null from pa_proj_structure_types pst, pa_structure_types ps
where pst.structure_type_id=ps.structure_type_id
and ps. STRUCTURE_TYPE ='WORKPLAN'
and pst.proj_element_id=pppa.proj_element_id);
/* SELECT *
FROM PA_PROJ_ELEM_ver_structure ppevs
WHERE project_id = p_src_project_id;*//*Commented below query and following criteria by SMUKKA */
select ppevs.*
from pa_structure_types pst,
pa_proj_structure_types ppst,
pa_proj_elem_ver_structure ppevs
where ppevs.project_id = p_src_project_id
and ppevs.proj_element_id= ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type in ('WORKPLAN','FINANCIAL');
SELECT ppevs.element_version_id, ppevs.status_code, ppevs.LATEST_EFF_PUBLISHED_FLAG, ppevs.current_flag,
ppst.structure_type_id
/* FROM PA_PROJ_ELEM_ver_structure ppevs,
pa_proj_structure_types ppst
WHERE project_id = p_src_project_id
AND ppst.proj_element_id(+) = ppevs.proj_element_id;*//*Commented below query and following criteria by SMUKKA */
SELECT pa_proj_element_versions_s.nextval
FROM sys.dual;
last_update_date DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
copy_flag VARCHAR2(1) := 'N',
dest_str_status VARCHAR2(150) := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
src_stru_type_id NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
);
SELECT /*+ USE_HASH(ppev2 ppev1)*/
pa_object_relationships_s.nextval,
pobj.object_type_from,
ppev1.element_version_id,
pobj.object_type_to,
ppev2.element_version_id,
pobj.relationship_type,
pobj.relationship_subtype,
pobj.Record_Version_Number,
pobj.weighting_percentage
FROM ( SELECT object_type_from, object_id_from1,
object_type_to, object_id_to1,
relationship_type, relationship_subtype,
Record_Version_Number, weighting_percentage
FROM pa_object_relationships
--bug#3094283WHERE RELATIONSHIP_TYPE = 'S'
start with object_id_from1 = c_src_str_version_id
and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
connect by object_id_from1 = prior object_id_to1
and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE
--bug#3094283 ppev1.project_id = p_dest_project_id
ppev1.attribute15 = pobj.object_id_from1
--bug#3094283 AND ppev2.project_id = p_dest_project_id
AND ppev2.attribute15 = pobj.object_id_to1
AND ppev1.parent_structure_version_id = c_struc_version_id
AND ppev2.parent_structure_version_id = c_struc_version_id;
SELECT ppe.proj_element_id, ppe.attribute15, ppst.structure_type_id
/* FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE ppe.proj_element_id = ppst.proj_element_id
AND ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
SELECT orig.proj_element_id orig_task_id,
new.proj_element_id new_task_id
FROM pa_proj_elements orig, pa_proj_elements new
WHERE orig.project_id = p_src_project_id
AND new.element_number = orig.element_number
AND new.project_id = p_dest_project_id
AND new.object_type = 'PA_TASKS'
AND orig.object_type = 'PA_TASKS'
AND orig.parent_structure_id = c_orig_parent_structure_id
AND new.parent_structure_id = c_new_parent_structure_id
;
select ppe.proj_element_id
/* from pa_proj_elements
where project_id = p_dest_project_id
and object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
select p.person_id
from per_all_people_f p, fnd_user f
where f.employee_id = p.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and f.user_id = p_user_id;
select meaning
into l_suffix
from pa_lookups
where lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
and lookup_code = 'WORKPLAN';
INSERT INTO pa_proj_elements(
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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LINK_TASK_FLAG
,BASELINE_START_DATE
,BASELINE_FINISH_DATE
,progress_outdated_flag
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,parent_structure_id
,TYPE_CODE
,INC_PROJ_PROGRESS_FLAG
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,BASELINE_DURATION
,PHASE_CODE
,PHASE_VERSION_ID
,source_object_id
,source_object_type
,PROGRESS_WEIGHT --Bug 3616964
,BASE_PERCENT_COMP_DERIV_CODE
,FUNCTION_CODE
,ENABLE_WF_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_START_LEAD_DAYS
)
SELECT
pa_tasks_s.nextval
,p_dest_project_id
,ppe.object_type
,ppe.element_number
,DECODE( ppe.object_type, 'PA_STRUCTURES',l_name, ppe.name )
,ppe.DESCRIPTION
,ppe.STATUS_CODE
,ppe.WF_STATUS_CODE
,ppe.PM_SOURCE_CODE
,ppe.PM_SOURCE_REFERENCE
,ppe.CLOSED_DATE
,ppe.LOCATION_ID
,ppe.MANAGER_PERSON_ID
,ppe.CARRYING_OUT_ORGANIZATION_ID
,ppe.TYPE_ID
,ppe.PRIORITY_CODE
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppe.RECORD_VERSION_NUMBER
,ppe.REQUEST_ID
,ppe.PROGRAM_APPLICATION_ID
,ppe.PROGRAM_ID
,ppe.PROGRAM_UPDATE_DATE
,ppe.LINK_TASK_FLAG
,ppe.BASELINE_START_DATE
,ppe.BASELINE_FINISH_DATE
,'N' --,ppe.progress_outdated_flag
,ppe.ATTRIBUTE_CATEGORY
,ppe.ATTRIBUTE1
,ppe.ATTRIBUTE2
,ppe.ATTRIBUTE3
,ppe.ATTRIBUTE4
,ppe.ATTRIBUTE5
,ppe.ATTRIBUTE6
,ppe.ATTRIBUTE7
,ppe.ATTRIBUTE8
,ppe.ATTRIBUTE9
,ppe.ATTRIBUTE10
,ppe.ATTRIBUTE11
,ppe.ATTRIBUTE12
,ppe.ATTRIBUTE13
,ppe.ATTRIBUTE14
,ppe.proj_element_id --attribute 15 is used to store old structure id to identify the structure type
,ppe.parent_structure_id
,ppe.TYPE_CODE
,ppe.INC_PROJ_PROGRESS_FLAG
,ppe.WQ_ITEM_CODE
,ppe.WQ_UOM_CODE
,ppe.WQ_ACTUAL_ENTRY_CODE
,ppe.TASK_PROGRESS_ENTRY_PAGE_ID
,ppe.BASELINE_DURATION
,ppe.PHASE_CODE
,ppe.PHASE_VERSION_ID
,p_dest_project_id
,'PA_PROJECTS'
,ppe.PROGRESS_WEIGHT --Bug 3616964
,ppe.BASE_PERCENT_COMP_DERIV_CODE
,ppe.FUNCTION_CODE
,ppe.ENABLE_WF_FLAG
,ppe.WF_ITEM_TYPE
,ppe.WF_PROCESS
,ppe.WF_START_LEAD_DAYS
/* FROM pa_proj_elements ppe
where project_id = p_src_project_id
and ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
and ppe.proj_element_id IN (select ppe2.proj_element_id
from pa_proj_elements ppe2,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppe2.project_id = p_src_project_id
and ppe2.object_type = 'PA_STRUCTURES'
and ppe2.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type in ('WORKPLAN','FINANCIAL'));
update pa_proj_elements
set element_number = proj_element_id
WHERE project_id = p_dest_project_id
AND object_type = 'PA_STRUCTURES'
;
INSERT INTO PA_PROJ_STRUCTURE_TYPES(
PROJ_STRUCTURE_TYPE_ID
,PROJ_ELEMENT_ID
,STRUCTURE_TYPE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER )
SELECT
pa_proj_structure_types_s.nextval
, ppe.proj_element_id
, ppst.structure_type_id --structure type
, sysdate
, l_user_id
, sysdate
, l_user_id
, l_login_id
, 1
/*from pa_proj_elements ppe, pa_proj_structure_types ppst
where ppe.project_id = p_dest_project_id
and ppe.attribute15 = ppst.proj_element_id ---join with copy_from_proj_element_id to get the structure types
and ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
INSERT INTO pa_proj_elements(
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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LINK_TASK_FLAG
,BASELINE_START_DATE
,BASELINE_FINISH_DATE
,progress_outdated_flag
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,parent_structure_id
,TYPE_CODE
,INC_PROJ_PROGRESS_FLAG
,WQ_ITEM_CODE
,WQ_UOM_CODE
,WQ_ACTUAL_ENTRY_CODE
,TASK_PROGRESS_ENTRY_PAGE_ID
,BASELINE_DURATION
,PHASE_CODE
,PHASE_VERSION_ID
,source_object_id
,source_object_type
,PROGRESS_WEIGHT --Bug 3616964
,BASE_PERCENT_COMP_DERIV_CODE
,FUNCTION_CODE
,ENABLE_WF_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_START_LEAD_DAYS
)
SELECT
pt.task_id
,p_dest_project_id
,ppe.object_type
,ppe.element_number
,ppe.name
,ppe.DESCRIPTION
,INITIAL_STATUS_CODE --bug 2827090
,ppe.WF_STATUS_CODE
,ppe.PM_SOURCE_CODE --update the source code later
,decode(ppe.PM_SOURCE_REFERENCE, null,pt.task_number, ppe.PM_SOURCE_REFERENCE )
,ppe.CLOSED_DATE
,ppe.LOCATION_ID
,ppe.MANAGER_PERSON_ID
,ppe.CARRYING_OUT_ORGANIZATION_ID
,ppe.TYPE_ID
,ppe.PRIORITY_CODE
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppe.RECORD_VERSION_NUMBER
,ppe.REQUEST_ID
,ppe.PROGRAM_APPLICATION_ID
,ppe.PROGRAM_ID
,ppe.PROGRAM_UPDATE_DATE
,ppe.LINK_TASK_FLAG
,ppe.BASELINE_START_DATE
,ppe.BASELINE_FINISH_DATE
,'N' --,ppe.progress_outdated_flag
,ppe.ATTRIBUTE_CATEGORY
,ppe.ATTRIBUTE1
,ppe.ATTRIBUTE2
,ppe.ATTRIBUTE3
,ppe.ATTRIBUTE4
,ppe.ATTRIBUTE5
,ppe.ATTRIBUTE6
,ppe.ATTRIBUTE7
,ppe.ATTRIBUTE8
,ppe.ATTRIBUTE9
,ppe.ATTRIBUTE10
,ppe.ATTRIBUTE11
,ppe.ATTRIBUTE12
,ppe.ATTRIBUTE13
,ppe.ATTRIBUTE14
,ppe.proj_element_id --attribute 15 is used to store old ids
,cur_pa_struct_rec.proj_element_id
,ppe.TYPE_CODE
,ppe.INC_PROJ_PROGRESS_FLAG
,ppe.WQ_ITEM_CODE
,ppe.WQ_UOM_CODE
,ppe.WQ_ACTUAL_ENTRY_CODE
,ppe.TASK_PROGRESS_ENTRY_PAGE_ID
,ppe.BASELINE_DURATION
,ppe.PHASE_CODE
,ppe.PHASE_VERSION_ID
,p_dest_project_id
,'PA_PROJECTS'
,ppe.PROGRESS_WEIGHT --Bug 3616964
,ppe.BASE_PERCENT_COMP_DERIV_CODE
,ppe.FUNCTION_CODE
,'N'
,ppe.WF_ITEM_TYPE
,ppe.WF_PROCESS
,ppe.WF_START_LEAD_DAYS
FROM pa_proj_elements ppe,
pa_tasks pt,
pa_task_types ptt --bug 2827090
WHERE ppe.project_id = p_src_project_id
AND pt.project_id = p_dest_project_id
AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
AND ppe.parent_structure_id = cur_pa_struct_rec.attribute15
and ppe.object_type = 'PA_TASKS'
and ppe.type_id = ptt.task_type_id --bug 2827090
;
update pa_tasks
set PM_TASK_REFERENCE = task_number
where project_id = p_dest_project_id
and pm_task_reference IS NULL
;
Update pa_proj_elements
set PM_SOURCE_CODE = ( SELECT PM_SOURCE_CODE FROM pa_proj_elements
WHERE project_id = p_dest_project_id
AND object_type = 'PA_STRUCTURES'
AND proj_element_id = cur_pa_struct_rec.proj_element_id
AND PM_SOURCE_CODE IS NOT NULL
)
WHERE proj_element_id = l_financial_structure_id
AND project_id = p_dest_project_id
AND PM_SOURCE_CODE IS NULL;
Update pa_tasks
set PM_PRODUCT_CODE = ( SELECT PM_SOURCE_CODE FROM pa_proj_elements
WHERE project_id = p_dest_project_id
AND object_type = 'PA_STRUCTURES'
AND proj_element_id = cur_pa_struct_rec.proj_element_id
AND PM_SOURCE_CODE IS NOT NULL
)
WHERE project_id = p_dest_project_id
AND PM_PRODUCT_CODE IS NULL;
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --this column is used to store structure ver id of the source project to be used to created relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,source_object_id
,source_object_type
)
SELECT
l_struc_version_id
,ppe.proj_element_id
,ppev.object_type
,p_dest_project_id
,l_struc_version_id
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,p_dest_project_id
,'PA_PROJECTS'
/*from pa_proj_element_versions ppev,
pa_proj_elements ppe
where ppev.project_id = p_src_project_id
and ppe.project_id = p_dest_project_id
and ppev.proj_element_id = ppe.attribute15
and ppe.object_type = 'PA_STRUCTURES'
and ppev.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
(select ppe2.proj_element_id
from pa_proj_elements ppe2,
pa_proj_structure_types ppst,
pa_structure_types pst
-- where ppe2.project_id = p_src_project_id
where ppe2.project_id = p_dest_project_id --Bug 3585699 SMukka Replaced p_src_project_id by p_dest_project_id
and ppe2.object_type = 'PA_STRUCTURES'
and ppe2.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type in ('WORKPLAN','FINANCIAL'));
SELECT element_version_id INTO l_src_str_version_id
/*FROM pa_proj_element_versions
WHERE project_id = p_src_project_id
AND object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
and proj_element_id IN (select ppe2.proj_element_id
from pa_proj_elements ppe2,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppe2.project_id = p_src_project_id
and ppe2.object_type = 'PA_STRUCTURES'
and ppe2.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type in ('WORKPLAN','FINANCIAL'));
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
PEV_STRUCTURE_ID
,ELEMENT_VERSION_ID
,VERSION_NUMBER
,NAME
,PROJECT_ID
,PROJ_ELEMENT_ID
,DESCRIPTION
,EFFECTIVE_DATE
,PUBLISHED_DATE
,PUBLISHED_BY_PERSON_ID
,CURRENT_BASELINE_DATE
,CURRENT_FLAG
,CURRENT_BASELINE_PERSON_ID
,ORIGINAL_BASELINE_DATE
,ORIGINAL_FLAG
,ORIGINAL_BASELINE_PERSON_ID
,LOCK_STATUS_CODE
,LOCKED_BY_PERSON_ID
,LOCKED_DATE
,STATUS_CODE
,WF_STATUS_CODE
,LATEST_EFF_PUBLISHED_FLAG
,RECORD_VERSION_NUMBER
,WBS_RECORD_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,change_reason_code
,PROCESS_UPDATE_WBS_FLAG
,current_working_flag /* FPM bug 3301192 */
,source_object_id
,source_object_type
)
SELECT
pa_proj_elem_ver_structure_s.nextval
,l_struc_version_id
,1
--,ppevs.name, rtarway, 3655698, replaced ppevs.name with ppe.name
,ppe.name
,p_dest_project_id
,ppe.proj_element_id
--,ppevs.DESCRIPTION , rtarway, 3655698 replaced ppevs.DESCRIPTION with ppe.DESCRIPTION
,ppe.description
,ppevs.EFFECTIVE_DATE
,decode(p_dest_template_flag, 'N', sysdate, to_date(NULL)) --bug 2838547
,decode(p_dest_template_flag, 'N', l_publish_person_id, NULL) --bug 2838547
,ppevs.CURRENT_BASELINE_DATE
,ppevs.CURRENT_FLAG
,ppevs.CURRENT_BASELINE_PERSON_ID
,ppevs.ORIGINAL_BASELINE_DATE
,ppevs.ORIGINAL_FLAG
,ppevs.ORIGINAL_BASELINE_PERSON_ID
,'UNLOCKED'
,null
,null
,decode( p_dest_template_flag, 'N', 'STRUCTURE_PUBLISHED', ppevs.STATUS_CODE )
,ppevs.WF_STATUS_CODE
,decode( p_dest_template_flag, 'N', 'Y', ppevs.LATEST_EFF_PUBLISHED_FLAG )
,ppevs.RECORD_VERSION_NUMBER
,ppevs.WBS_RECORD_VERSION_NUMBER
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppevs.change_reason_code
,'N'
,ppevs.current_working_flag /* FPM bug 3301192 */
,p_dest_project_id
,'PA_PROJECTS'
/*FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
WHERE ppevs.project_id = p_src_project_id
AND ppe.project_id = p_dest_project_id
and ppevs.proj_element_id = ppe.attribute15
AND ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
and ppe.proj_element_id IN (SELECT ppe2.proj_element_id
FROM pa_proj_elements ppe2,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppe2.project_id = p_dest_project_id
AND ppe2.object_type = 'PA_STRUCTURES'
AND ppe2.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type in ('WORKPLAN','FINANCIAL'));
INSERT INTO pa_proj_workplan_attr(
PROJ_ELEMENT_ID
,PROJECT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,WP_APPROVAL_REQD_FLAG
,WP_AUTO_PUBLISH_FLAG
,WP_DEFAULT_DISPLAY_LVL
,WP_ENABLE_VERSION_FLAG
,AUTO_PUB_UPON_CREATION_FLAG
,AUTO_SYNC_TXN_DATE_FLAG
,WP_APPROVER_SOURCE_ID
,WP_APPROVER_SOURCE_TYPE
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,TXN_DATE_SYNC_BUF_DAYS
,LIFECYCLE_VERSION_ID
,CURRENT_PHASE_VERSION_ID
,SCHEDULE_THIRD_PARTY_FLAG
,ALLOW_LOWEST_TSK_DEP_FLAG
,AUTO_ROLLUP_SUBPROJ_FLAG
,THIRD_PARTY_SCHEDULE_CODE
,source_object_id
,source_object_type
)
SELECT
ppe.PROJ_ELEMENT_ID
,ppe.PROJECT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,ppwa.WP_APPROVAL_REQD_FLAG
,ppwa.WP_AUTO_PUBLISH_FLAG
,ppwa.WP_DEFAULT_DISPLAY_LVL
,ppwa.WP_ENABLE_VERSION_FLAG
,ppwa.AUTO_PUB_UPON_CREATION_FLAG
,ppwa.AUTO_SYNC_TXN_DATE_FLAG
,ppwa.WP_APPROVER_SOURCE_ID
,ppwa.WP_APPROVER_SOURCE_TYPE
,ppwa.RECORD_VERSION_NUMBER
,l_login_id
,ppwa.TXN_DATE_SYNC_BUF_DAYS
,ppwa.LIFECYCLE_VERSION_ID
,ppwa.CURRENT_PHASE_VERSION_ID
,ppwa.SCHEDULE_THIRD_PARTY_FLAG
,ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
,ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
,ppwa.THIRD_PARTY_SCHEDULE_CODE
,ppe.PROJECT_ID
,'PA_PROJECTS'
FROM pa_proj_elements ppe,
pa_proj_workplan_attr ppwa
WHERE ppwa.project_id = p_src_project_id
AND ppe.attribute15 = ppwa.proj_element_id
AND ppe.project_id = p_dest_project_id
AND ppe.object_type='PA_STRUCTURES';
INSERT INTO pa_proj_progress_attr(
PROJ_PROGRESS_ATTR_ID
,OBJECT_TYPE
,OBJECT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PROJECT_ID
,PROGRESS_CYCLE_ID
,WQ_ENABLE_FLAG
,REMAIN_EFFORT_ENABLE_FLAG
,PERCENT_COMP_ENABLE_FLAG
,NEXT_PROGRESS_UPDATE_DATE
,RECORD_VERSION_NUMBER
,TASK_WEIGHT_BASIS_CODE
,ALLOW_COLLAB_PROG_ENTRY
,ALLOW_PHY_PRCNT_CMP_OVERRIDES
,STRUCTURE_TYPE
)
SELECT
PA_PROJ_PROGRESS_ATTR_S.nextval
,ppe.OBJECT_TYPE
,ppe.proj_element_ID
,sysdate
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,p_dest_project_id
,pppa.PROGRESS_CYCLE_ID
,pppa.WQ_ENABLE_FLAG
,pppa.REMAIN_EFFORT_ENABLE_FLAG
,pppa.PERCENT_COMP_ENABLE_FLAG
,pppa.NEXT_PROGRESS_UPDATE_DATE
,pppa.RECORD_VERSION_NUMBER
,pppa.TASK_WEIGHT_BASIS_CODE
,pppa.ALLOW_COLLAB_PROG_ENTRY
,pppa.ALLOW_PHY_PRCNT_CMP_OVERRIDES
,pppa.STRUCTURE_TYPE
FROM pa_proj_progress_attr pppa,
pa_proj_elements ppe
WHERE ppe.project_id = p_dest_project_id
AND ppe.attribute15 = pppa.object_id
AND pppa.project_id = p_src_project_id
AND pppa.object_type = 'PA_STRUCTURES' ;
IF NVL( l_copy_flag_set, 'N' ) = 'N' --latest published was not found, now look for latest updated unpublished str ver
THEN
l_max_date := null;
FOR j in 1..l_dest_str_versions_tbl.count LOOP --look for last updated unpublished version
IF l_dest_str_versions_tbl(j).src_str_status <> 'STRUCTURE_PUBLISHED'
THEN
--Find out which unpublihsed version is last updated.
IF l_max_date IS NULL
THEN
l_latst_upd_wrkng_str_ver := l_dest_str_versions_tbl(j).src_str_version_id;
END LOOP; --look for last updated unpublished version
END IF; --NVL( l_copy_flag_set, 'N' ) := 'N' --latest published was not found, now look for latest updated unpublished str ver
IF NVL( l_copy_flag_set, 'N' ) = 'N' --latest published was not found, now look for latest updated unpublished str ver
THEN
l_max_date := null;
FOR j in 1..l_dest_str_versions_tbl.count LOOP --look for last updated unpublished version
IF l_dest_str_versions_tbl(j).src_stru_type_id = 1 --WORKPLAN
THEN
IF l_dest_str_versions_tbl(j).src_str_status <> 'STRUCTURE_PUBLISHED'
THEN
--Find out which unpublihsed version is last updated.
IF l_max_date IS NULL
THEN
l_latst_upd_wrkng_str_ver := l_dest_str_versions_tbl(j).src_str_version_id;
END LOOP; --look for last updated unpublished version
END IF; --NVL( l_copy_flag_set, 'N' ) := 'N' --latest published was not found, now look for latest updated unpublished str ver
UPDATE pa_proj_elements ppe
set name = substr(l_project_name||l_append||l_suffix, 1, 240)
WHERE project_id = p_dest_project_id
AND object_type = 'PA_STRUCTURES'
AND EXISTS ( SELECT proj_element_id FROM pa_proj_structure_types
WHERE proj_element_id = ppe.proj_element_id
AND structure_type_id = 1 );
INSERT INTO pa_proj_workplan_attr(
PROJ_ELEMENT_ID
,PROJECT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,WP_APPROVAL_REQD_FLAG
,WP_AUTO_PUBLISH_FLAG
,WP_DEFAULT_DISPLAY_LVL
,WP_ENABLE_VERSION_FLAG
,AUTO_PUB_UPON_CREATION_FLAG
,AUTO_SYNC_TXN_DATE_FLAG
,WP_APPROVER_SOURCE_ID
,WP_APPROVER_SOURCE_TYPE
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,TXN_DATE_SYNC_BUF_DAYS
,LIFECYCLE_VERSION_ID
,CURRENT_PHASE_VERSION_ID
--bug 4061876
,SCHEDULE_THIRD_PARTY_FLAG
,ALLOW_LOWEST_TSK_DEP_FLAG
,AUTO_ROLLUP_SUBPROJ_FLAG
,THIRD_PARTY_SCHEDULE_CODE
,source_object_id
,source_object_type
)
SELECT
ppe.PROJ_ELEMENT_ID
,ppe.PROJECT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,ppwa.WP_APPROVAL_REQD_FLAG
,ppwa.WP_AUTO_PUBLISH_FLAG
,ppwa.WP_DEFAULT_DISPLAY_LVL
,ppwa.WP_ENABLE_VERSION_FLAG
,ppwa.AUTO_PUB_UPON_CREATION_FLAG
,ppwa.AUTO_SYNC_TXN_DATE_FLAG
,ppwa.WP_APPROVER_SOURCE_ID
,ppwa.WP_APPROVER_SOURCE_TYPE
,ppwa.RECORD_VERSION_NUMBER
,ppwa.LAST_UPDATE_LOGIN
,ppwa.TXN_DATE_SYNC_BUF_DAYS
,ppwa.LIFECYCLE_VERSION_ID
,ppwa.CURRENT_PHASE_VERSION_ID
--bug 4061876
,ppwa.SCHEDULE_THIRD_PARTY_FLAG
,ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
,ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
,ppwa.THIRD_PARTY_SCHEDULE_CODE
,ppe.PROJECT_ID
,'PA_PROJECTS'
/*FROM pa_proj_elements ppe,
pa_proj_workplan_attr ppwa
WHERE ppe.project_id = p_dest_project_id
and object_type = 'PA_STRUCTURES'
and ppwa.project_id = p_src_project_id
and ppwa.proj_element_id = ppe.attribute15;*//*Commented below query and following criteria by SMUKKA */
INSERT INTO pa_proj_progress_attr(
PROJ_PROGRESS_ATTR_ID
,OBJECT_TYPE
,OBJECT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PROJECT_ID
,PROGRESS_CYCLE_ID
,WQ_ENABLE_FLAG
,REMAIN_EFFORT_ENABLE_FLAG
,PERCENT_COMP_ENABLE_FLAG
,NEXT_PROGRESS_UPDATE_DATE
,RECORD_VERSION_NUMBER
,TASK_WEIGHT_BASIS_CODE
/* FP-M Bug # 3420093. Modified: copy_structures_bulk. */
,ALLOW_COLLAB_PROG_ENTRY
,ALLOW_PHY_PRCNT_CMP_OVERRIDES
,STRUCTURE_TYPE
/* FP-M Bug # 3420093. Modified: copy_structures_bulk. */
) SELECT
PA_PROJ_PROGRESS_ATTR_S.nextval
,ppe.OBJECT_TYPE
,ppe.proj_element_ID
,sysdate
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,p_dest_project_id
,pppa.PROGRESS_CYCLE_ID
,pppa.WQ_ENABLE_FLAG
,pppa.REMAIN_EFFORT_ENABLE_FLAG
,pppa.PERCENT_COMP_ENABLE_FLAG
,pppa.NEXT_PROGRESS_UPDATE_DATE
,pppa.RECORD_VERSION_NUMBER
,pppa.TASK_WEIGHT_BASIS_CODE
/* FP-M Bug # 3420093. Modified: copy_structures_bulk. */
,pppa.ALLOW_COLLAB_PROG_ENTRY
,pppa.ALLOW_PHY_PRCNT_CMP_OVERRIDES
,pppa.STRUCTURE_TYPE
/* FP-M Bug # 3420093. Modified: copy_structures_bulk. */
/*FROM pa_proj_progress_attr pppa,
pa_proj_elements ppe
WHERE ppe.project_id = p_dest_project_id
and ppe.attribute15 = pppa.object_id --no need to join with
and pppa.object_type = 'PA_STRUCTURES'
and ppe.object_type = 'PA_STRUCTURES'
and pppa.project_id = p_src_project_id;*//*Commented below query and following criteria by SMUKKA */
PA_PROJ_TASK_STRUC_PUB.create_update_struct_ver(
p_project_id => p_dest_project_id
,p_structure_type => 'FINANCIAL'
,p_structure_version_name => l_structure_ver_name
,p_structure_version_id => null
,x_structure_version_id => l_structure_version_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status
);
PA_PROJ_TASK_STRUC_PUB.create_update_struct_ver(
p_project_id => p_dest_project_id
,p_structure_type => 'WORKPLAN'
,p_structure_version_name => l_structure_ver_name
,p_structure_version_id => null
,x_structure_version_id => l_structure_version_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status
);
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --this column is used to store structure ver id of the source project to be used to created relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,source_object_id
,source_object_type
)
SELECT
l_struc_version_id
,ppe.proj_element_id
,ppev.object_type
,p_dest_project_id
,l_struc_version_id
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,ppev.TASK_UNPUB_VER_STATUS_CODE
,p_dest_project_id
,'PA_PROJECTS'
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
WHERE ppev.project_id = p_src_project_id
and ppe.project_id = p_dest_project_id
and ppev.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
and ppev.proj_element_id = ppe.attribute15
and ppe.object_type = 'PA_STRUCTURES'
and ppev.object_type = 'PA_STRUCTURES';
INSERT INTO pa_proj_element_versions(
ELEMENT_VERSION_ID
,PROJ_ELEMENT_ID
,OBJECT_TYPE
,PROJECT_ID
,PARENT_STRUCTURE_VERSION_ID
,DISPLAY_SEQUENCE
,WBS_LEVEL
,WBS_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,RECORD_VERSION_NUMBER
,attribute15 --this column is used to store task ver id of the source project's task versions to be used to created relationships.
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,TASK_UNPUB_VER_STATUS_CODE
,source_object_id
,source_object_type
,financial_task_flag --Bug no 3651255
)
SELECT
pa_proj_element_versions_s.nextval
,ppe.proj_element_id
,ppev.object_type
,p_dest_project_id
,l_struc_version_id --from previous sql
,ppev.DISPLAY_SEQUENCE
,ppev.WBS_LEVEL
,ppev.WBS_NUMBER
,SYSDATE ------CREATION_DATE
,l_user_id ------CREATED_BY
,SYSDATE ------LAST_UPDATE_DATE
,l_user_id ------LAST_UPDATED_BY
,l_login_id ------LAST_UPDATE_LOGIN
,ppev.RECORD_VERSION_NUMBER ------RECORD_VERSION_NUMBER
,ppev.element_version_id -- to help create relationships from source
,ppev.ATTRIBUTE_CATEGORY
,ppev.ATTRIBUTE1
,ppev.ATTRIBUTE2
,ppev.ATTRIBUTE3
,ppev.ATTRIBUTE4
,ppev.ATTRIBUTE5
,ppev.ATTRIBUTE6
,ppev.ATTRIBUTE7
,ppev.ATTRIBUTE8
,ppev.ATTRIBUTE9
,ppev.ATTRIBUTE10
,ppev.ATTRIBUTE11
,ppev.ATTRIBUTE12
,ppev.ATTRIBUTE13
,ppev.ATTRIBUTE14
,decode( l_dest_str_versions_tbl(k).dest_str_status, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
,p_dest_project_id
,'PA_PROJECTS'
,ppev.financial_task_flag --Bug no 3651255
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
WHERE ppev.project_id = p_src_project_id
and ppe.project_id = p_dest_project_id
and ppev.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
and ppev.proj_element_id = ppe.attribute15
and ppe.object_type = 'PA_TASKS'
and ppev.object_type = 'PA_TASKS';
l_obj_rel_id.delete;
l_obj_typ_from.delete;
l_obj_from_id.delete;
l_obj_typ_to.delete;
l_obj_to_id.delete;
l_rel_typ.delete;
l_rel_subtyp.delete;
l_rec_ver_num.delete;
l_wt_percent.delete;
insert into PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
values(
l_obj_rel_id(i),
l_obj_typ_from(i),
l_obj_from_id(i),
l_obj_typ_to(i),
l_obj_to_id(i),
l_rel_typ(i),
l_rel_subtyp(i),
l_rec_ver_num(i),
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
l_wt_percent(i)
);
l_obj_rel_id.delete;
l_obj_typ_from.delete;
l_obj_from_id.delete;
l_obj_typ_to.delete;
l_obj_to_id.delete;
l_rel_typ.delete;
l_rel_subtyp.delete;
l_rec_ver_num.delete;
l_wt_percent.delete;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_type_to,
object_id_to1,
relationship_type,
relationship_subtype,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
/*Added the hint for the bug 3513853*/
/* SELECT /*+ USE_HASH(ppev2 ppev1)*/
/* pa_object_relationships_s.nextval,
pobj.object_type_from,
ppev1.element_version_id,
pobj.object_type_to,
ppev2.element_version_id,
pobj.relationship_type,
pobj.relationship_subtype,
pobj.Record_Version_Number,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
pobj.weighting_percentage
FROM ( SELECT object_type_from, object_id_from1,
object_type_to, object_id_to1,
relationship_type, relationship_subtype,
Record_Version_Number, weighting_percentage
FROM pa_object_relationships
--bug#3094283WHERE RELATIONSHIP_TYPE = 'S'
start with object_id_from1 = l_dest_str_versions_tbl(k).src_str_version_id
and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 Added this condition */
/* connect by object_id_from1 = prior object_id_to1
and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 Added this condition */
/* pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE
--bug#3094283 ppev1.project_id = p_dest_project_id
ppev1.attribute15 = pobj.object_id_from1
--bug#3094283 ppev2.project_id = p_dest_project_id
AND ppev2.attribute15 = pobj.object_id_to1
AND ppev1.parent_structure_version_id = l_struc_version_id
AND ppev2.parent_structure_version_id = l_struc_version_id
;
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
PEV_STRUCTURE_ID
,ELEMENT_VERSION_ID
,VERSION_NUMBER
,NAME
,PROJECT_ID
,PROJ_ELEMENT_ID
,DESCRIPTION
,EFFECTIVE_DATE
,PUBLISHED_DATE
,PUBLISHED_BY_PERSON_ID
,CURRENT_BASELINE_DATE
,CURRENT_FLAG
,CURRENT_BASELINE_PERSON_ID
,ORIGINAL_BASELINE_DATE
,ORIGINAL_FLAG
,ORIGINAL_BASELINE_PERSON_ID
,LOCK_STATUS_CODE
,LOCKED_BY_PERSON_ID
,LOCKED_DATE
,STATUS_CODE
,WF_STATUS_CODE
,LATEST_EFF_PUBLISHED_FLAG
,RECORD_VERSION_NUMBER
,WBS_RECORD_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,change_reason_code
,PROCESS_UPDATE_WBS_FLAG
,current_working_flag /* FPM bug 3301192 */
,source_object_id
,source_object_type
)
SELECT
pa_proj_elem_ver_structure_s.nextval
,l_struc_version_id
,ppevs.version_number --replaced k with source version_number bug 2829385
--,ppevs.name 3717026, replaced ppevs.name with ppe.name
,ppe.name
,p_dest_project_id
,ppe.proj_element_id
,ppevs.DESCRIPTION
,ppevs.EFFECTIVE_DATE
,decode(l_dest_str_versions_tbl(k).dest_str_status, 'STRUCTURE_PUBLISHED', sysdate, to_date(null)) --bug 2838547
,decode(l_dest_str_versions_tbl(k).dest_str_status, 'STRUCTURE_PUBLISHED', l_publish_person_id, NULL) --bug 2838547
,decode(l_dest_str_versions_tbl(k).current_flag, 'Y', trunc(sysdate), to_date(NULL)) --bug 2838547
,l_dest_str_versions_tbl(k).current_flag
,decode(l_dest_str_versions_tbl(k).current_flag, 'Y', l_publish_person_id, NULL) --bug 2838547
,ppevs.ORIGINAL_BASELINE_DATE
,ppevs.ORIGINAL_FLAG
,ppevs.ORIGINAL_BASELINE_PERSON_ID
,'UNLOCKED'
,null
,null
,l_dest_str_versions_tbl(k).dest_str_status
,ppevs.WF_STATUS_CODE
,l_LATEST_EFF_PUBLISHED_FLAG
,ppevs.RECORD_VERSION_NUMBER
,ppevs.WBS_RECORD_VERSION_NUMBER
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,ppevs.change_reason_code
,'N'
,ppevs.current_working_flag /* FPM bug 3301192 */
,p_dest_project_id
,'PA_PROJECTS'
FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
WHERE ppevs.project_id = p_src_project_id
AND ppe.project_id = p_dest_project_id
and ppevs.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
and ppevs.proj_element_id = ppe.attribute15
AND ppe.object_type = 'PA_STRUCTURES';
INSERT INTO pa_proj_elem_ver_schedule(
PEV_SCHEDULE_ID
,ELEMENT_VERSION_ID
,PROJECT_ID
,PROJ_ELEMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SCHEDULED_START_DATE
,SCHEDULED_FINISH_DATE
,OBLIGATION_START_DATE
,OBLIGATION_FINISH_DATE
,ACTUAL_START_DATE
,ACTUAL_FINISH_DATE
,ESTIMATED_START_DATE
,ESTIMATED_FINISH_DATE
,DURATION
,EARLY_START_DATE
,EARLY_FINISH_DATE
,LATE_START_DATE
,LATE_FINISH_DATE
,CALENDAR_ID
,MILESTONE_FLAG
,CRITICAL_FLAG
,RECORD_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,WQ_PLANNED_QUANTITY
,PLANNED_EFFORT
,ACTUAL_DURATION
,ESTIMATED_DURATION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,source_object_id
,source_object_type
,CONSTRAINT_TYPE_CODE --Bug 3762437
,CONSTRAINT_DATE
,FREE_SLACK
,TOTAL_SLACK
,EFFORT_DRIVEN_FLAG
,LEVEL_ASSIGNMENTS_FLAG
,EXT_ACT_DURATION
,EXT_REMAIN_DURATION
,EXT_SCH_DURATION --Bug 3762437
)
SELECT
pa_proj_elem_ver_schedule_s.nextval
,ppev1.ELEMENT_VERSION_ID
,p_dest_PROJECT_ID
,ppev1.PROJ_ELEMENT_ID
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,PA_PROJ_TASK_STRUC_PUB.get_adjusted_dates( p_target_start_date, --fix bug 2831973
p_target_finish_date,
l_delta,
ppevs.SCHEDULED_START_DATE,
null ) --sch start date
,PA_PROJ_TASK_STRUC_PUB.get_adjusted_dates( p_target_start_date, --fix bug 2831973
p_target_finish_date,
l_delta,
ppevs.SCHEDULED_START_DATE,
SCHEDULED_FINISH_DATE ) --sch end date
,ppevs.OBLIGATION_START_DATE
,ppevs.OBLIGATION_FINISH_DATE
,null --ppevs.ACTUAL_START_DATE bug 2827090
,null --ppevs.ACTUAL_FINISH_DATE
,null --ppevs.ESTIMATED_START_DATE
,null --ppevs.ESTIMATED_FINISH_DATE
,ppevs.DURATION
,ppevs.EARLY_START_DATE
,ppevs.EARLY_FINISH_DATE
,ppevs.LATE_START_DATE
,ppevs.LATE_FINISH_DATE
,p_calendar_id --,ppevs.CALENDAR_ID Bug # 2955589. Use the calendar id passed to
--this API.
,ppevs.MILESTONE_FLAG
,ppevs.CRITICAL_FLAG
,ppevs.RECORD_VERSION_NUMBER
,l_login_id
,ppevs.WQ_PLANNED_QUANTITY
,ppevs.PLANNED_EFFORT
,ppevs.ACTUAL_DURATION
,ppevs.ESTIMATED_DURATION
,ppevs.ATTRIBUTE_CATEGORY
,ppevs.ATTRIBUTE1
,ppevs.ATTRIBUTE2
,ppevs.ATTRIBUTE3
,ppevs.ATTRIBUTE4
,ppevs.ATTRIBUTE5
,ppevs.ATTRIBUTE6
,ppevs.ATTRIBUTE7
,ppevs.ATTRIBUTE8
,ppevs.ATTRIBUTE9
,ppevs.ATTRIBUTE10
,ppevs.ATTRIBUTE11
,ppevs.ATTRIBUTE12
,ppevs.ATTRIBUTE13
,ppevs.ATTRIBUTE14
,ppevs.ATTRIBUTE15
,p_dest_PROJECT_ID
,'PA_PROJECTS'
,CONSTRAINT_TYPE_CODE --Bug 3762437
,CONSTRAINT_DATE
,FREE_SLACK
,TOTAL_SLACK
,EFFORT_DRIVEN_FLAG
,LEVEL_ASSIGNMENTS_FLAG
,EXT_ACT_DURATION
,EXT_REMAIN_DURATION
,EXT_SCH_DURATION --Bug 3762437
FROM pa_proj_elem_ver_schedule ppevs,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
where ppev1.attribute15 = ppevs.element_version_id
and ppevs.project_id = p_src_project_id
and ppev2.project_id = p_src_project_id
and ppev2.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
and ppev2.element_version_id = ppevs.element_version_id
and ppev1.attribute15 = ppev2.element_version_id
and ppev1.object_type = 'PA_STRUCTURES'
and ppev2.object_type = 'PA_STRUCTURES'
and ppev1.project_id = p_dest_project_id;
/*UPDATE pa_proj_elem_ver_schedule
SET duration = PA_PROJ_TASK_STRUC_PUB.calc_duration( p_calendar_id,
scheduled_start_date,
scheduled_finish_date )
WHERE project_id = p_dest_project_id;*/
UPDATE pa_proj_elem_ver_schedule
SET duration = trunc(scheduled_finish_date) - trunc(scheduled_start_date) + 1
WHERE project_id = p_dest_project_id;
UPDATE PA_PROJ_ELEMENTS ppe
SET ( BASELINE_START_DATE ,BASELINE_FINISH_DATE,
BASELINE_DURATION ) =
( Select ppevs.scheduled_start_date, ppevs.scheduled_finish_date,
ppevs.duration
from pa_proj_elem_ver_schedule ppevs,
pa_proj_elem_ver_structure ppevst
WHERE ppevs.project_id = p_dest_project_id
and ppevst.project_id = p_dest_project_id
and ppevst.current_flag = 'Y' --assuming this is the flag used to identify the baselined structure ver.
and ppevst.STATUS_CODE = 'STRUCTURE_PUBLISHED'
and ppevs.element_version_id = ppevst.element_version_id
)
WHERE project_id = p_dest_project_id;
update pa_projects_all
set ( BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration, baseline_as_of_date) =
( SELECT BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration,
decode(BASELINE_START_DATE, null, null, sysdate)
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE project_id = p_dest_project_id
and object_type = 'PA_STRUCTURES'
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = 1 )
where project_id = p_dest_project_id;
update pa_projects_all
set ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE,duration,
decode(SCHEDULED_START_DATE, null, null, sysdate)
FROM pa_proj_elem_ver_schedule
WHERE project_id = p_dest_project_Id
and element_version_id = l_latest_pub_str_ver_id)
where project_id = p_dest_project_id;
Select Element_Version_ID, attribute15 BULK COLLECT
INTO dest_versions_tab, src_Versions_Tab
From PA_Proj_Element_Versions
Where Project_ID = p_dest_project_id
AND ( PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(element_version_id, 'WORKPLAN') = 'Y')
AND object_type = 'PA_STRUCTURES';
update pa_proj_elements ppe1
set attribute15 = ( select attribute15 from pa_proj_elements ppe2
where ppe2.project_id = p_src_project_id
and ppe2.proj_element_id = ppe1.attribute15 )
where project_id = p_dest_project_id
;
update pa_proj_element_versions ppevs1
set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
where ppevs2.project_id = p_src_project_id
and ppevs2.element_version_id = ppevs1.attribute15 )
where project_id = p_dest_project_id
;
SELECT ppe.proj_element_id, ppst.structure_type_id
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
;
SELECT ppevs.element_version_id
FROM pa_proj_element_versions ppevs
WHERE ppevs.project_id = p_dest_project_id
AND ppevs.object_type = 'PA_STRUCTURES'
AND ppevs.proj_element_id = c_structure_id
;
UPDATE pa_proj_elem_ver_structure
SET PROCESS_UPDATE_WBS_FLAG = 'Y'
WHERE proj_element_id = cur_strs_n_types_rec.proj_element_id
AND project_id = p_dest_project_id
AND PROCESS_UPDATE_WBS_FLAG = 'N'
;
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'COPY_PROJECT'
,p_project_id => p_dest_project_id
,p_structure_version_id => l_wp_str_ver_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'COPY_PROJECT'
,p_project_id => p_dest_project_id
,p_structure_version_id => l_wp_str_ver_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'COPY_PROJECT'
,p_project_id => p_dest_project_id
,p_structure_version_id => l_fn_str_ver_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
SELECT parent_structure_version_id
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id;
SELECT element_version_id, parent_structure_version_id
FROM pa_proj_element_versions
WHERE proj_element_id = p_task_id
AND project_id = p_project_id
;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE proj_element_id = p_task_id
AND parent_structure_version_id = p_structure_version_id
AND project_id = p_project_id;
This API is called from pa_project_pub.update_project which passes
the structure version id through p_structure_version_id parameter.
The complete WBS is passed to this API in p_tasks_in_tbl. Depending
on whether the task is an existing task or a new task, task creation
or task updation is performed.
==================================================================*/
PROCEDURE PROCESS_TASK_STRUCTURE_BULK
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_calling_module IN VARCHAR2
,p_project_id IN pa_projects_all.project_id%TYPE
,p_source_project_id IN pa_projects_all.project_id%TYPE
,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
,p_create_task_version_only IN VARCHAR2
,p_wp_str_exists IN VARCHAR2
,p_is_wp_separate_from_fn IN VARCHAR2
,p_is_wp_versioning_enabled IN VARCHAR2
,p_structure_version_id IN pa_proj_elem_ver_structure.element_version_id%TYPE --IUP: Populated only in case of Update_project context
-- Included NOCOPY for the following parameter.
-- PA L Changes 3010538
,p_process_mode IN VARCHAR2
-- Bug 3075609. To identify create task version only context.
,p_create_task_versions_only IN VARCHAR2
,px_tasks_out_tbl IN OUT NOCOPY pa_project_pub.task_out_tbl_type
,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
AS
-- Cursors Definition
-- This cursor is used to get the auto publish flag on creation for a given project_id
/* Bug 2955589. This cursor is no longer used.
CURSOR cur_auto_pub_flag(c_project_id pa_projects_all.project_id%TYPE)
IS
SELECT AUTO_PUB_UPON_CREATION_FLAG
FROM pa_proj_workplan_attr
WHERE project_id = c_project_id;
select pelem.proj_element_id
from pa_proj_elements pelem,
pa_proj_structure_types ppstr,
pa_structure_types pstr
where pelem.project_id = c_project_id
and pstr.structure_type_class_code = c_struc_type
and pstr.structure_type_id = ppstr.structure_type_id
and ppstr.proj_element_id = pelem.proj_element_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = c_project_id;
SELECT task_number, long_task_name, description,
parent_task_id,carrying_out_organization_id,
pm_task_reference, address_id, task_manager_person_id
FROM pa_tasks
WHERE project_id = c_project_id
AND task_id = c_task_id;
select NVL(template_flag,'N')
from pa_projects
where project_id = c_proj_id;
SELECT record_version_number
FROM pa_proj_elements
WHERE proj_element_id = c_task_id
AND object_type = 'PA_TASKS';
SELECT element_version_id, record_version_number
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND proj_element_id = c_task_id
AND parent_structure_version_id = c_structure_version_id
AND object_type = 'PA_TASKS';
SELECT object_id_from1 -- parent task id
,object_relationship_id, -- relationship id reqd for update relationship API
record_version_number,
weighting_percentage -- Bug 3075609. We need to get hold of the weightage of the task in db.
FROM pa_object_relationships
WHERE object_id_to1 = c_task_version_id
AND relationship_type = 'S';
SELECT *
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_element_version_id
AND project_id = p_project_id;
SELECT type_id
FROM pa_proj_elements
WHERE proj_element_id = c_task_id
AND project_id = p_project_id;
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE project_id = p_project_id
AND element_version_id = c_task_version_id;
Select element_version_id
from pa_proj_element_versions
where parent_structure_version_id = c_structure_version_id
and object_type = 'PA_TASKS';
SELECT p1.element_number
FROM pa_proj_elements p1, pa_proj_element_versions pv1
WHERE pv1.element_version_id = c_task_version_id
AND p1.proj_element_id = pv1.proj_element_id;
l_update_task_security VARCHAR2(1);
l_new_structure_version varchar2(1) := 'Y'; -- In the update context, we could be either updating an existing
l_proj_elem_ver_rowid VARCHAR2(255); -- rowid of the task being updated.
l_update_wbs_flag VARCHAR2(1);
/* Bug # 3420093 - Adding Progress Management modifications to create_project and update_project flow. */
-- Amit Bug 3964548 Begin
-- Changed l_add_task_ver_ids, l_add_planned_effort, l_add_start_date, l_add_end_date
-- to SYSTEM.pa_num_tbl_type from PA_NUM_1000_NUM
--l_add_task_ver_ids PA_NUM_1000_NUM := PA_NUM_1000_NUM();
SELECT proj_element_id
FROM pa_proj_elements pelem
WHERE project_id = c_project_id
AND pm_source_reference = c_pm_source_reference
AND exists(select 1
from pa_proj_structure_types pstype, pa_structure_types types
where pstype.proj_element_id = pelem.parent_structure_id
and pstype.structure_type_id = types.structure_type_id
and types.structure_type = p_structure_type);
select
element_version_id,planned_effort,actual_start_date,actual_finish_date
from pa_proj_elem_ver_schedule ppevs, pa_proj_elements ppe
where ppevs.project_id = ppe.project_id
and ppevs.proj_element_id = ppe.proj_element_id
and ppe.object_type = 'PA_TASKS'
and ppevs.project_id = p_project_id;
/** cursor added by aditi for bug 4120380. This is added to verify if the task_number is being modified by AMG Update_project script,
Only then we need to prefix the task_number with a '-', so as to prevent primary key violation, and sucessful update.**/
/*bug#5243018 : Reverting the fix in Bug 4120380
CURSOR cur_proj_elements_info(c_task_id pa_proj_elements.proj_element_id%TYPE)
IS
SELECT element_number, name
FROM pa_proj_elements
WHERE proj_element_id = c_task_id;
SELECT 'Y'
FROM pa_resource_assignments ra
WHERE ra.ta_display_flag = 'N'
AND ra.wbs_element_version_id = p_element_version_id
AND rownum = 1;
/* Bug # 3420093 - Adding Progress Management modifications to create_project and update_project flow. */
-- Begin fix for Bug # 4373055.
l_str_ver_id NUMBER:= null;
select
planned_effort, est_remaining_effort, completed_percentage
from PA_TASK_PROGRESS_AMG_V
where
project_id = p_project_id
and OBJECT_ID = p_object_id
and OBJECT_VERSION_ID = p_object_version_id
and STRUCTURE_TYPE_CLASS_CODE='WORKPLAN';
l_update_task_det_flag varchar2(1);
SELECT c.element_version_id
FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = c_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = c_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_WORKING';
, p_context => 'UPDATE_AMG'
, x_message_name => l_message_name
, x_message_type => l_message_type
, x_structure_version_id => l_str_ver_id
, x_conc_request_id => l_conc_request_id);
Unable to delete the task from MSP.
After succesful deletion of a task and the publishing of the structure, a new task is added to the current
working version. And that the task was deleted and the MSP was returning a message saying 'Mark this task for deletion?'.
Ideally this should be used for tasks which are present in the published versions as well. Only those tasks will be marked
for deletion and during publishing those marked tasks will be deleted. All the other tasks which dont have any transactions
and are not present in the published versions should be deleted right away and the MSP should not show any message.
Analysis:
---------
This is happening due to value 'M' being returned from the PA_PROJECT_PUB.check_task_mfd APIs OUT param p_check_task_mfd_flag.
This was happening due to the value 'PUBLISHED' was populated in the task_unpub_ver_status_code column.
When ever a task is created in MSP and sent to oracle projects the pa_project_pub.load_project and
pa_project_pub.execute_update_project are called.
The pa_project_pub.execute_update_project -> update_project API ...> PA_PROJ_TASK_STRUC_PUB.PROCESS_TASK_STRUCTURE_BULK is called.
Inside this API the following code is used to derive the value for task_unpub_ver_status_code column.
This is correct. But after this piece of code all the passed in tasks are processed in a loop. In the
loop the same variable l_task_unpub_ver_status_code is used to get the other tasks status. Hence the
earlier derived value for the new task is getting overridden with the other published task status.
Had the tasks processing happned in such a way that (working) the newly added task is processed
first then this is not an issue at all. Due to the published tasks presence the other value is overwritten
and the same got stored for the new task.
Solution:
---------
Moved the task status derivation code into the loop right before the insert into the element versions table.
--IUP:get the status of newly created task under the passed structure version
IF p_is_wp_separate_from_fn = 'N'
THEN
IF p_is_wp_versioning_enabled = 'Y'
THEN
l_task_unpub_ver_status_code := 'WORKING';
p_function_name => 'PA_PM_UPDATE_TASK',
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_return_status => x_return_status,
p_function_allowed => l_update_task_security);
pa_debug.g_err_stage := 'Error obtaining the value of update task function allowed' ;
/* Bug 2955589. As the update is taken care in Copy_Structures_bulk API, the following
cursor need not be opened.
open cur_auto_pub_flag(p_project_id);
UPDATE pa_proj_elem_ver_structure
set status_code = 'STRUCTURE_WORKING'
where project_id = p_project_id
and proj_element_id = l_structure_id;
pa_debug.g_err_stage:= 'No of updated records :' || sql%rowcount || ' for structure : ' || l_structure_id;
task_ver_id_passed_tbl.delete;
SELECT proj_element_id
FROM pa_proj_elements pelem
WHERE project_id = c_project_id
AND pm_source_reference = c_pm_source_reference
AND exists(select 1
from pa_proj_structure_types pstype, pa_structure_types types
where pstype.proj_element_id = pelem.parent_structure_id
and pstype.structure_type_id = types.structure_type_id
and types.structure_type = p_structure_type);
IF (PA_PROJECT_PUB.G_TASK_STR_UPDATE_MODE = 'PA_UPD_TASK_ATTR') THEN --bug 4534919
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_WBS_CANT_CHANGE');
IF l_update_task_security = 'N'
THEN
pa_interface_utils_pub.map_new_amg_msg(
p_old_message_code => 'PA_FUNCTION_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
If we insert a new top task , specifying its task_number as 1.1, and update 1.1 to 2.0 and 2.0 to 3.0.*/
END IF;
SELECT element_number, name
FROM pa_proj_elements
WHERE proj_element_id = c_task_id;
END IF; -- before calling create or update TASK
PA_TASK_PUB1.Update_Task(
p_calling_module => 'AMG'
,p_init_msg_list => FND_API.G_FALSE
,p_task_id => l_task_in_rec.pa_task_id
,p_task_number => l_task_in_rec.pa_task_number
,p_task_name => l_task_in_rec.long_task_name
,p_task_description => l_task_in_rec.task_description
,p_task_manager_id => l_task_in_rec.task_manager_person_id
,p_carrying_out_org_id => l_task_in_rec.carrying_out_organization_id
,p_pm_product_code => l_task_in_rec.PM_SOURCE_CODE
,p_pm_task_reference => l_task_in_rec.pm_task_reference
,p_location_id => l_task_in_rec.address_id
,p_priority_code => l_task_in_rec.PRIORITY_CODE
,p_TYPE_ID => l_task_in_rec.TASK_TYPE
,p_status_code => l_status_code -- l_task_in_rec.STATUS_CODE bug 6717386
,p_inc_proj_progress_flag => l_task_in_rec.INC_PROJ_PROGRESS_FLAG
,p_closed_date => l_task_in_rec.CLOSED_DATE
,p_record_version_number => l_proj_elem_rvn
,P_UOM_CODE => l_task_in_rec.WQ_UOM_CODE
,p_work_item_code => l_task_in_rec.WQ_ITEM_CODE
----- begin Bug 3654243 --------------------------------------------
,p_Base_Perc_Comp_Deriv_Code => l_task_in_rec.base_percent_comp_deriv_code
,p_gen_etc_src_code => l_task_in_rec.gen_etc_source_code
----- end Bug 3654243 ----------------------------------------------
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_shared => l_shared -- Added for Bug#3451073
);
,p_msg_name => 'PA_PS_TASK_NOT_UPDATED'
/* ADDED PROJECT_ID AND TASK_ID TOKENS FOR BUG 4219451 */
,p_token1 => 'PROJECT'
,p_value1 => p_project_id
,p_token2 => 'TASK'
,p_value2 => l_task_in_rec.pa_task_id);
PA_PROJECT_PVT.Update_One_Task
(
p_api_version_number => p_api_version_number,
p_commit => FND_API.G_FALSE,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_return_status => x_return_status,
p_pm_product_code => p_pm_product_code,
p_pa_project_id => p_project_id,
p_pa_task_id => l_task_in_rec.pa_task_id,
p_pm_task_reference => l_task_in_rec.pm_task_reference,
p_task_number => l_task_in_rec.pa_task_number,
p_task_name => l_task_in_rec.task_name,
p_long_task_name => l_task_in_rec.long_task_name,
p_task_description => l_task_in_rec.task_description,
p_task_start_date => l_task_in_rec.task_start_date,
p_task_completion_date => l_task_in_rec.task_completion_date,
p_early_start_date => l_task_in_rec.early_start_date,
p_early_finish_date => l_task_in_rec.early_finish_date,
p_late_start_date => l_task_in_rec.late_start_date,
p_late_finish_date => l_task_in_rec.late_finish_date,
p_pm_parent_task_reference => l_task_in_rec.pm_parent_task_reference,
p_pa_parent_task_id => l_task_in_rec.pa_parent_task_id,
p_address_id => l_task_in_rec.address_id,
p_carrying_out_organization_id => l_task_in_rec.carrying_out_organization_id,
p_service_type_code => l_task_in_rec.service_type_code,
p_task_manager_person_id => l_task_in_rec.task_manager_person_id,
p_billable_flag => l_task_in_rec.billable_flag,
p_chargeable_flag => l_task_in_rec.chargeable_flag,
p_ready_to_bill_flag => l_task_in_rec.ready_to_bill_flag,
p_ready_to_distribute_flag => l_task_in_rec.ready_to_distribute_flag,
p_limit_to_txn_controls_flag => l_task_in_rec.limit_to_txn_controls_flag,
p_labor_bill_rate_org_id => l_task_in_rec.labor_bill_rate_org_id,
p_labor_std_bill_rate_schdl => l_task_in_rec.labor_std_bill_rate_schdl,
p_labor_schedule_fixed_date => l_task_in_rec.labor_schedule_fixed_date,
p_labor_schedule_discount => l_task_in_rec.labor_schedule_discount,
p_nl_bill_rate_org_id => l_task_in_rec.non_labor_bill_rate_org_id,
p_nl_std_bill_rate_schdl => l_task_in_rec.non_labor_std_bill_rate_schdl,
p_nl_schedule_fixed_date => l_task_in_rec.non_labor_schedule_fixed_date,
p_nl_schedule_discount => l_task_in_rec.non_labor_schedule_discount,
p_labor_cost_multiplier_name => l_task_in_rec.labor_cost_multiplier_name,
p_cost_ind_rate_sch_id => l_task_in_rec.cost_ind_rate_sch_id,
p_rev_ind_rate_sch_id => l_task_in_rec.rev_ind_rate_sch_id,
p_inv_ind_rate_sch_id => l_task_in_rec.inv_ind_rate_sch_id,
p_cost_ind_sch_fixed_date => l_task_in_rec.cost_ind_sch_fixed_date,
p_rev_ind_sch_fixed_date => l_task_in_rec.rev_ind_sch_fixed_date,
p_inv_ind_sch_fixed_date => l_task_in_rec.inv_ind_sch_fixed_date,
p_labor_sch_type => l_task_in_rec.labor_sch_type,
p_nl_sch_type => l_task_in_rec.non_labor_sch_type,
p_allow_cross_charge_flag => l_task_in_rec.allow_cross_charge_flag,
p_project_rate_type => l_task_in_rec.project_rate_type,
p_project_rate_date => l_task_in_rec.project_rate_date,
p_cc_process_labor_flag => l_task_in_rec.cc_process_labor_flag,
p_labor_tp_schedule_id => l_task_in_rec.labor_tp_schedule_id,
p_labor_tp_fixed_date => l_task_in_rec.labor_tp_fixed_date,
p_cc_process_nl_flag => l_task_in_rec.cc_process_nl_flag,
p_nl_tp_schedule_id => l_task_in_rec.nl_tp_schedule_id,
p_nl_tp_fixed_date => l_task_in_rec.nl_tp_fixed_date,
p_receive_project_invoice_flag => l_task_in_rec.receive_project_invoice_flag,
p_work_type_id => l_task_in_rec.work_type_id,
p_emp_bill_rate_schedule_id => l_task_in_rec.emp_bill_rate_schedule_id,
p_job_bill_rate_schedule_id => l_task_in_rec.job_bill_rate_schedule_id,
p_non_lab_std_bill_rt_sch_id => l_task_in_rec.non_lab_std_bill_rt_sch_id,
p_taskfunc_cost_rate_type => l_task_in_rec.taskfunc_cost_rate_type,
p_taskfunc_cost_rate_date => l_task_in_rec.taskfunc_cost_rate_date,
p_labor_disc_reason_code => l_task_in_rec.labor_disc_reason_code,
p_non_labor_disc_reason_code => l_task_in_rec.non_labor_disc_reason_code,
p_retirement_cost_flag => l_task_in_rec.retirement_cost_flag,
p_cint_eligible_flag => l_task_in_rec.cint_eligible_flag,
p_cint_stop_date => l_task_in_rec.cint_stop_date,
p_gen_etc_source_code => l_task_in_rec.gen_etc_source_code,
p_invoice_method => l_task_in_rec.invoice_method,
p_customer_id => l_task_in_rec.customer_id,
p_out_pa_task_id => l_fin_task_id,
p_out_pm_task_reference => l_fin_task_reference
);
,p_msg_name => 'PA_PS_TASK_NOT_UPDATED'
/* ADDED PROJECT_ID AND TASK_ID TOKENS FOR BUG 4219451 */
,p_token1 => 'PROJECT'
,p_value1 => p_project_id
,p_token2 => 'TASK'
,p_value2 => l_task_in_rec.pa_task_id);
END IF; -- After calling create or update TASK
SELECT count(rel.object_id_to1)
FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
WHERE rel.object_id_from1 = c_parent_element_version_id
AND rel.object_type_to = 'PA_TASKS'
AND rel.relationship_type = 'S'
AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND rel.object_id_to1 = pev.element_version_id
AND pev.proj_element_id = pe.proj_element_id
AND pe.type_id = tt.task_type_id
AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
AND tt.prog_entry_enable_flag = 'Y';
IF p_structure_version_id is not null THEN -- update context
open get_child_count(l_parent_version_id);
ELSE -- update task case
DECLARE
--Cursor to get the versioned information of the task
CURSOR cur_proj_element_version_info(c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
IS
SELECT rowid,element_version_id,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 project_id = p_project_id
AND proj_element_id = c_task_id
AND parent_structure_version_id = c_structure_version_id
AND object_type = 'PA_TASKS';
END IF; -- before calling PA_PROJ_ELEMENT_VERSIONS_PKG Insert_Row or Update_Row
IF ( PA_PROJECT_PUB.G_TASK_STR_UPDATE_MODE = 'PA_UPD_TASK_ATTR' ) THEN --bug 4534919
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_WBS_CANT_CHANGE');
pa_debug.g_err_stage := 'Calling API PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row';
Unable to delete the task from MSP.
*/
IF p_is_wp_separate_from_fn = 'N'
THEN
IF p_is_wp_versioning_enabled = 'Y'
THEN
l_task_unpub_ver_status_code := 'WORKING';
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
(
X_ROW_ID => l_proj_elem_ver_rowid
,X_ELEMENT_VERSION_ID => l_task_version_id
,X_PROJ_ELEMENT_ID => l_task_id
,X_OBJECT_TYPE => 'PA_TASKS'
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID=> l_structure_version_id
,X_DISPLAY_SEQUENCE => l_display_sequence_tbl(l_task_id)
,X_WBS_LEVEL => l_wbs_level_tbl(l_task_id)
,X_WBS_NUMBER => l_wbs_number_tbl(l_task_id)
,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 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_TASK_UNPUB_VER_STATUS_CODE => l_task_unpub_ver_status_code
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
--,P_Financial_Task_Flag => PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_task_id)
--commented and added following for bug 4059962 reopen, rtarway
,P_Financial_Task_Flag => l_financial_task_flag
);
ELSE -- in update task case
-- check for locking
BEGIN
SELECT 'x' INTO l_dummy_char
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE element_version_id = l_task_version_id
AND record_version_number = l_proj_elem_ver_rvn
FOR UPDATE OF record_version_number NOWAIT;
pa_debug.g_err_stage := 'Calling API PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row';
PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
X_ROW_ID => l_proj_elem_ver_rowid
,X_ELEMENT_VERSION_ID => l_task_version_id
,X_PROJ_ELEMENT_ID => l_task_id
,X_OBJECT_TYPE => 'PA_TASKS'
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID=> l_structure_version_id
,X_DISPLAY_SEQUENCE => l_display_sequence_tbl(l_task_in_rec.pa_task_id)
,X_WBS_LEVEL => l_wbs_level_tbl(l_task_in_rec.pa_task_id)
,X_WBS_NUMBER => l_wbs_number_tbl(l_task_in_rec.pa_task_id)
,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 => null
,X_ATTRIBUTE12 => null
,X_ATTRIBUTE13 => null
,X_ATTRIBUTE14 => null
,X_ATTRIBUTE15 => null
,X_record_version_number => l_proj_elem_ver_rvn
,X_TASK_UNPUB_VER_STATUS_CODE => l_task_unpub_ver_status_code
);
END IF; /* After calling PA_PROJ_ELEMENT_VERSIONS_PKG Insert_Row or Update_Row*/
ELSE -- in update task case
-- Set the following variables after calling PA_RELATIONSHIP_PUB.Create_Relationship
-- px_tasks_out_tbl(i).return_status
--rtarway, for DHI ER, BUG 4413568
/*
IF ( PA_PROJECT_PUB.G_TASK_STR_UPDATE_MODE = 'PA_UPD_TASK_ATTR' ) THEN --bug 4534919
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_WBS_CANT_CHANGE');
PA_RELATIONSHIP_PVT.Update_Relationship
(
p_api_version => p_api_version_number
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validate_only => FND_API.G_FALSE
,p_calling_module => p_calling_module
,p_debug_mode => l_debug_mode
,p_object_relationship_id => l_relationship_id
,p_project_id_from => null
,p_structure_id_from => null
,p_structure_version_id_from => l_structure_version_id_from
,p_task_version_id_from => l_task_version_id_from
,p_project_id_to => null
,p_structure_id_to => null
,p_structure_version_id_to => null
,p_task_version_id_to => l_task_version_id
,p_relationship_type => 'S'
,p_relationship_subtype => l_relationship_subtype
,p_weighting_percentage => l_weighting_percentage_tbl(l_task_id)
,p_record_version_number => l_obj_rel_rvn
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END IF; -- After calling PA_RELATIONSHIP_PUB Create_Relationship or Update_Relationship
END; -- create/update relationship record.
ELSE /* in update task case */
-- Added for DFF attributes and to get pev_schedule_id and record version number
--Bug 6153503 Need to reopen again because changes to schedule version records by the
-- which is in between earlier open of the cursor and till this place.
OPEN cur_ver_sch_attr_rec(l_task_version_id);
END IF; /* Before calling PA_TASK_PUB1 Create_Schedule_Version or Update_Schedule_Version*/
l_add_task_ver_ids.delete;
l_add_planned_effort.delete;
l_add_start_date.delete;
l_add_end_date.delete;
l_pm_product_code.delete;
ELSE -- in update task case
-- set the following after calling api PA_TASK_PUB1.Update_Schedule_Version
-- px_tasks_out_tbl(i).return_status
PA_TASK_PUB1.G_CALL_PJI_ROLLUP := 'N'; --bug 4145893 do not call PJI.plan_update api
PA_TASK_PUB1.Update_Schedule_Version(
p_calling_module => p_calling_module
,p_init_msg_list => FND_API.G_FALSE
,p_pev_schedule_id => l_ver_sch_attr_rec.pev_schedule_id
,p_calendar_id => l_task_in_rec.calendar_id
,p_scheduled_start_date => l_task_in_rec.scheduled_start_date
,p_scheduled_end_date => l_task_in_rec.scheduled_finish_date
,p_obligation_start_date => l_task_in_rec.obligation_start_date
,p_obligation_end_date => l_task_in_rec.obligation_finish_date
,p_actual_start_date => l_task_in_rec.actual_start_date
,p_actual_finish_date => l_task_in_rec.actual_finish_date
,p_estimate_start_date => l_task_in_rec.estimated_start_date
,p_estimate_finish_date => l_task_in_rec.estimated_finish_date
,p_duration => l_task_in_rec.duration
,p_early_start_date => l_task_in_rec.early_start_date
,p_early_end_date => l_task_in_rec.early_finish_date
,p_late_start_date => l_task_in_rec.late_start_date
,p_late_end_date => l_task_in_rec.late_finish_date
,p_milestone_flag => l_task_in_rec.milestone_flag
,p_critical_flag => l_task_in_rec.critical_flag
,p_WQ_PLANNED_QUANTITY => l_wq_planned_quantity
,p_PLANNED_EFFORT => l_task_in_rec.planned_effort
,p_record_version_number => l_ver_sch_attr_rec.record_version_number
--Bug 6153503
,p_attribute_category => l_attribute_category
,p_attribute1 => l_attribute1
,p_attribute2 => l_attribute2
,p_attribute3 => l_attribute3
,p_attribute4 => l_attribute4
,p_attribute5 => l_attribute5
,p_attribute6 => l_attribute6
,p_attribute7 => l_attribute7
,p_attribute8 => l_attribute8
,p_attribute9 => l_attribute9
,p_attribute10 => l_attribute10
,p_attribute11 => l_attribute11
,p_attribute12 => l_attribute12
,p_attribute13 => l_attribute13
,p_attribute14 => l_attribute14
,p_attribute15 => l_attribute15
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
-- (Begin Venkat) new params for bug #3652460 ----------------------------------------------
,p_ext_act_duration => l_task_in_rec.ext_act_duration
,p_ext_remain_duration => l_task_in_rec.ext_remain_duration
,p_ext_sch_duration => l_task_in_rec.ext_sch_duration
-- (End Venkat) new params for bug #3652460 -------------------------------------------------
-- begin Bug 3654243 -----------------------------------------------------------------------
,p_def_sch_tool_tsk_type_code => l_task_in_rec.sch_tool_tsk_type_code
,p_constraint_type_code => l_task_in_rec.constraint_type_code
,p_constraint_date => l_task_in_rec.constraint_date
,p_free_slack => l_task_in_rec.free_slack
,p_total_slack => l_task_in_rec.total_slack
,p_effort_driven_flag => l_task_in_rec.effort_driven_flag
,p_level_assignments_flag => l_task_in_rec.level_assignments_flag
-- end Bug 3654243 -------------------------------------------------------------------------
);
pa_debug.g_err_stage := 'Error calling update schedule version : ' || l_task_version_id ;
,p_msg_name => 'PA_PS_TASK_NOT_UPDATED'
/* ADDED PROJECT_ID AND TASK_ID TOKENS FOR BUG 4219451 */
,p_token1 => 'PROJECT'
,p_value1 => p_project_id
,p_token2 => 'TASK'
,p_value2 => l_task_in_rec.pa_task_id
);
l_update_task_det_flag := 'Y';
l_update_task_det_flag:= 'N';
l_update_task_det_flag := 'Y';
l_update_task_det_flag := 'Y';
l_update_task_det_flag := 'Y';
l_update_task_det_flag :='Y';
IF p_calling_module <> 'FORMS' and l_update_task_det_flag = 'Y' THEN /* Added flag check for Bug 6458891 */
pa_task_pub1.update_task_det_sch_info(
p_calling_module => p_calling_module
,p_task_ver_id => l_task_version_id
,p_project_id => p_project_id
,p_planned_effort => l_task_in_rec.planned_effort
,p_ETC_effort => l_task_in_rec.etc_effort
,p_structure_version_id => l_structure_version_id
,p_percent_complete => l_task_in_rec.percent_complete
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/** The code below will update the task_numbers of the tasks passed via update_project script
back to their original value viz. it'll crop the '-' added to aviod the constraint check in
PA_TASKS and PA_PROJ_ELEMENTS. **/
/* bug#5243018 : Reverting the fix in Bug 4120380
IF nvl(p_tasks_in_tbl.last,0) > 0 THEN --IF Added by rtarway for BUG 4336701
FOR i in p_tasks_in_tbl.first..p_tasks_in_tbl.last LOOP
UPDATE pa_proj_elements
SET element_number = p_tasks_in_tbl(i).pa_task_number
WHERE element_number = '-'||p_tasks_in_tbl(i).pa_task_number
AND PROJECT_ID = p_project_id;
UPDATE pa_tasks
SET task_number = p_tasks_in_tbl(i).pa_task_number
WHERE task_number = '-'||p_tasks_in_tbl(i).pa_task_number
AND PROJECT_ID = p_project_id;
SELECT rel.OBJECT_RELATIONSHIP_ID,tt.prog_entry_enable_flag
FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
WHERE rel.object_id_from1 = c_parent_element_version_id
AND rel.object_type_to = 'PA_TASKS'
AND rel.relationship_type = 'S'
AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
AND rel.object_id_to1 = pev.element_version_id
AND pev.proj_element_id = pe.proj_element_id
AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
AND pe.type_id = tt.task_type_id;
UPDATE pa_object_relationships
set weighting_percentage = l_task_weightage_tbl(i)
where object_relationship_id = l_obj_rel_id_tbl(i);
PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
(
p_calling_context => 'AMG'
,p_project_id => p_project_id
,p_structure_version_id => l_structure_version_id
,p_update_wbs_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
l_update_wbs_flag := PA_PROJECT_STRUCTURE_UTILS.GET_UPDATE_WBS_FLAG(p_project_id => p_project_id
,p_structure_version_id => l_structure_version_id ) ;
IF nvl(l_update_wbs_flag,'N') = 'Y' THEN
IF p_process_mode = 'ONLINE' THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage := 'Calling API PROCESS_WBS_UPDATES';
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES(p_calling_context => 'ONLINE_UPDATE'
,p_project_id => p_project_id
,p_structure_version_id => l_structure_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
pa_debug.g_err_stage := 'Error in process_wbs_updates : ';
pa_debug.g_err_stage := 'Calling API PROCESS_WBS_UPDATES_CONC_WRP';
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_CONC_WRP(p_calling_context => 'CONC_UPDATE'
,p_project_id => p_project_id
,p_structure_version_id => l_structure_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
pa_debug.g_err_stage := 'Error in process_wbs_updates_conc_wrp : ';
IF p_structure_version_id is not null THEN -- Update Project Context
OPEN cur_wp_task_csr_duplicate(p_project_id,p_tasks_in_tbl(l_i).pm_task_reference);
Pa_Debug.WRITE(G_PKG_NAME,'Preparing Tables to call FP delete_plan_txn',l_debug_level3);
ELSE -- Update Task Context
IF l_debug_mode = 'Y' THEN
Pa_Debug.WRITE(G_PKG_NAME,'Update Task Context',l_debug_level3);
Pa_Debug.WRITE(G_PKG_NAME,'Preparing Tables to call FP delete_plan_txn for summary task in update flow',l_debug_level3);
Pa_Debug.WRITE(G_PKG_NAME,'Preparing Tables to call FP delete_plan_txn',l_debug_level3);
Pa_Debug.WRITE(G_PKG_NAME,'Preparing Tables to call FP delete_plan_txn for summary task in update flow',l_debug_level3);
pa_fp_planning_transaction_pub.delete_planning_transactions(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_del_task_ver_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
l_del_task_ver_ids.delete;
pa_fp_planning_transaction_pub.delete_planning_transactions(
p_context => 'WORKPLAN'
,p_task_or_res => 'TASKS'
,p_element_version_id_tbl => l_del_task_ver_ids
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.delete_planning_transactions:'||SQLERRM,1,240));
PROCEDURE delete_fin_plan_from_task(
p_task_id NUMBER
,p_project_id NUMBER
,p_calling_module VARCHAR2 := 'FORMS'
,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) IS --File.Sql.39 bug 4440895
l_plannable_tasks_tbl PA_FP_ELEMENTS_PUB.l_impacted_task_in_tbl_typ;
SELECT parent_task_id, top_task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
l_plannable_tasks_tbl(1).action := 'DELETE';
p_procedure_name => 'delete_fin_plan_from_task',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_fin_plan_from_task;
process wrapper or the online process to process the WBS updates. If
the profile is set to Online, the process is done online always.
Otherwise when the profile is standard the processing is done online/
concurrent based on the number of tasks in the structure version and
the threshold profile.
Bug 3010538 : New API for the Task Weighting Enhancement.
==================================================================*/
PROCEDURE PROCESS_WBS_UPDATES_WRP
( p_api_version_number IN NUMBER := 1.0
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_calling_context IN VARCHAR2 := 'UPDATE'
,p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_pub_struc_ver_id IN NUMBER := NULL
,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
,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
AS
-- Cursors used in this Procedure.
-- This cursor obtains the count of the task versions in the structure version.
CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
IS
Select count(element_version_id)
From pa_proj_element_versions
Where project_id = c_project_id
And object_type = 'PA_TASKS'
And parent_structure_version_id = c_structure_version_id;
SELECT process_code
FROM pa_proj_elem_ver_structure
WHERE element_version_id = c_structure_version_id;
IF p_calling_context = 'UPDATE' AND l_process_code = 'PUE'
THEN
PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
,p_msg_name => 'PA_PS_PUBLISH_ERROR');
pa_debug.g_err_stage:= 'Entering PROCESS_WBS_UPDATES_WRP';
pa_debug.set_curr_function( p_function => 'PROCESS_WBS_UPDATES_WRP',
p_debug_mode => l_debug_mode );
l_update_option VARCHAR2(30);
l_update_option := pa_project_structure_utils.get_process_wbs_updates_option(p_task_count => l_task_count,
p_project_id => p_project_id ---bug 4370533
);
IF l_update_option = 'ONLINE' THEN
l_calling_context := 'ONLINE_PUBLISH';
ELSIF p_calling_context = 'UPDATE' THEN
IF l_update_option = 'ONLINE' THEN
l_calling_context := 'ONLINE_UPDATE';
l_calling_context := 'CONC_UPDATE';
IF l_update_option = 'ONLINE' THEN
l_calling_context := 'ONLINE_APPLY_PROGRESS';
l_update_option := 'CONCURRENT'; ---should always launch conc pgm
pa_debug.g_err_stage := 'Update option is '||l_update_option;
IF l_update_option = 'ONLINE' THEN
pa_proj_task_struc_pub.process_wbs_updates(
p_commit => p_commit
,p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_pub_struc_ver_id => p_pub_struc_ver_id
,p_pub_prog_flag => p_pub_prog_flag --bug 4019845
,p_calling_context => l_calling_context
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.g_err_stage := 'Error calling online wbs update process ';
ELSIF l_update_option = 'CONCURRENT' THEN
pa_proj_task_struc_pub.process_wbs_updates_conc_wrp(
p_commit => p_commit
,p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_pub_struc_ver_id => p_pub_struc_ver_id
,p_pub_prog_flag => p_pub_prog_flag --bug 4019845
,p_calling_context => l_calling_context
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.g_err_stage := 'Error calling concurrent wbs update process ';
pa_debug.g_err_stage:= 'Exiting PROCESS_WBS_UPDATES_WRP';
,p_procedure_name => 'PROCESS_WBS_UPDATES_WRP'
,p_error_text => x_msg_data);
END PROCESS_WBS_UPDATES_WRP;
PROCEDURE PROCESS_WBS_UPDATES_CONC_WRP
( p_api_version_number IN NUMBER := 1.0
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_calling_context IN VARCHAR2 := 'UPDATE'
,p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_pub_struc_ver_id IN NUMBER := NULL
,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
,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
AS
-- Cursors used.
-- This cursor is used to decide if the structure version corresponds to a particular
-- Structure type. Depending on this the plannable tasks api and the task weightage calculation
-- api will be called.
Cursor cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
c_structure_type pa_structure_types.structure_type%TYPE)
IS
select 'Y'
from pa_proj_element_versions ver,
pa_proj_structure_types pst,
pa_structure_types st
where ver.element_version_id = c_structure_version_id
and ver.project_id = c_project_id
and pst.proj_element_id = ver.proj_element_id
and pst.structure_type_id = st.structure_type_id
and st.structure_type = c_structure_type;
l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
SELECT process_code
FROM pa_proj_elem_ver_structure
WHERE element_version_id = c_structure_version_id
and project_id = p_project_id -- Bug # 4868867.
;
select org_id
from pa_projects_all
where project_id = p_project_id;
pa_debug.g_err_stage:= 'Entering PROCESS_WBS_UPDATES_CONC_WRP';
pa_debug.set_curr_function( p_function => 'PROCESS_WBS_UPDATES_CONC_WRP',
p_debug_mode => l_debug_mode );
l_update_wbs_flag := pa_project_structure_utils.GET_UPDATE_WBS_FLAG(p_project_id => p_project_id,
p_structure_version_id => p_structure_version_id);
IF l_update_wbs_flag = 'N' THEN
-- No further processing is required for this structure version. Return.
-- TWUT.
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'No updates required for this structure version.';
l_update_wbs_flag := pa_project_structure_utils.GET_UPDATE_WBS_FLAG(p_project_id => p_project_id,
p_structure_version_id => p_structure_version_id);
nvl(l_update_wbs_flag,'N') = 'N')
THEN
IF l_debug_mode = 'Y' THEN
-- pa_debug.g_err_stage:= 'split + workplan + MANUAL or flag = N. No further processing required';
description => 'PRC: Process Structure Updates', -- Fix for Bug # 4409337.
start_time => NULL,
sub_request => false,
argument1 => p_calling_context,
argument2 => p_project_id,
argument3 => p_structure_version_id,
argument4 => p_pub_struc_ver_id,
argument5 => p_pub_prog_flag --bug 4019845
,argument6 => l_rerun_flag --bug 4589289
);
update pa_proj_elem_ver_structure
set process_code = 'WUP',
conc_request_id = l_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id;
pa_debug.g_err_stage:= 'Exiting PROCESS_WBS_UPDATES_CONC_WRP';
,p_procedure_name => 'PROCESS_WBS_UPDATES_CONC_WRP'
,p_error_text => x_msg_data);
END PROCESS_WBS_UPDATES_CONC_WRP;
PROCEDURE PROCESS_WBS_UPDATES_CONC
( errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_calling_context IN VARCHAR2 := 'UPDATE'
,p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_pub_struc_ver_id IN NUMBER := NULL
,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
,p_rerun_flag IN VARCHAR2 := null --bug 4589289
)
AS
l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
savepoint process_wbs_updates_conc;
pa_debug.set_curr_function( p_function => 'PROCESS_WBS_UPDATES_CONC',
p_debug_mode => l_debug_mode );
pa_debug.g_err_stage:= 'Entering PROCESS_WBS_UPDATES_CONC';
pa_proj_task_struc_pub.process_wbs_updates(
p_calling_context => p_calling_context
,p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_pub_struc_ver_id => p_pub_struc_ver_id
,p_pub_prog_flag => p_pub_prog_flag --bug 4019845
,p_rerun_flag => p_rerun_flag -- bug 4589289
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.g_err_stage := 'Error calling process wbs updates API ';
update pa_proj_elem_ver_structure
set process_code = NULL,
conc_request_id = l_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id;
update pa_proj_elem_ver_structure
set process_code = NULL
, conc_request_id = l_request_id
where element_version_id = p_pub_struc_ver_id
and project_id = p_project_id;
pa_debug.g_err_stage:= 'Exiting PROCESS_WBS_UPDATES_CONC';
rollback to process_wbs_updates_conc;
update pa_proj_elem_ver_structure
set process_code = 'WUE',
conc_request_id = l_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id;
if (p_calling_context = 'CONC_UPDATE') then
pa_proj_task_struc_pub.set_update_wbs_flag
(p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_update_wbs_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
,p_procedure_name => 'PROCESS_WBS_UPDATES_CONC'
,p_error_text => errbuf);
rollback to process_wbs_updates_conc;
update pa_proj_elem_ver_structure
set process_code = 'WUE',
conc_request_id = l_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id;
if (p_calling_context = 'CONC_UPDATE') then
pa_proj_task_struc_pub.set_update_wbs_flag
(p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_update_wbs_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END PROCESS_WBS_UPDATES_CONC;
PROCEDURE PROCESS_WBS_UPDATES
( p_api_version_number IN NUMBER := 1.0
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_calling_context IN VARCHAR2 := 'UPDATE'
,p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_pub_struc_ver_id IN NUMBER := NULL
,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
,p_rerun_flag IN VARCHAR2 := null --bug 4589289
,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
AS
-- Cursors Required.
-- This cursor is used to decide if the structure version corresponds to a particular
-- Structure type. Depending on this the plannable tasks api and the task weightage calculation
-- api will be called.
CURSOR cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
c_structure_type pa_structure_types.structure_type%TYPE)
IS
SELECT 'Y'
FROM pa_proj_element_versions ver,
pa_proj_structure_types pst,
pa_structure_types st
WHERE ver.element_version_id = c_structure_version_id
AND ver.project_id = c_project_id
AND pst.proj_element_id = ver.proj_element_id
AND pst.structure_type_id = st.structure_type_id
AND st.structure_type = c_structure_type;
l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
select a.element_version_id
from pa_proj_elem_ver_structure a,
pa_proj_element_versions b
WHERE a.project_id = p_project_id
AND a.proj_element_id = b.proj_element_id
AND b.element_version_id = c_struc_ver_id
AND a.LATEST_EFF_PUBLISHED_FLAG <> 'Y'
AND a.STATUS_CODE = 'STRUCTURE_PUBLISHED'
ORDER BY published_date desc;
SELECT scheduled_start_date, scheduled_finish_date
FROM pa_proj_elem_ver_schedule
WHERE project_id = c_project_id
AND element_version_id = c_structure_version_id;
SELECT record_version_number
FROM pa_projects_all
WHERE project_id = c_project_id;
select pevs.proj_element_id
from pa_proj_elem_ver_structure pevs
where pevs.element_version_id = c_struc_ver_id
and pevs.project_id = c_project_id;
SELECT 'Y'
FROM pji_pjp_wbs_header h,
pa_fin_plan_types_b fin
WHERE h.wbs_version_id= p_pub_struc_ver_id
AND fin.use_for_workplan_flag='Y'
AND h.plan_type_id=fin.fin_plan_type_id;
SELECT 'Y'
FROM pji_pjp_wbs_header
WHERE wbs_version_id= p_pub_struc_ver_id;
savepoint process_wbs_updates;
pa_debug.write(G_PKG_NAME, 'Entering PROCESS_WBS_UPDATES', 3);
pa_debug.set_curr_function( p_function => 'PROCESS_WBS_UPDATES', p_debug_mode => l_debug_mode );
l_update_wbs_flag := pa_project_structure_utils.GET_UPDATE_WBS_FLAG(p_project_id => p_project_id,
p_structure_version_id => p_structure_version_id);
pa_debug.write(G_PKG_NAME, 'l_update_wbs_flag = '|| l_update_wbs_flag, 3);
select max(as_of_date)
into l_as_of_date
from pa_progress_rollup ppr
where ppr.project_id = p_project_id
and ppr.structure_version_id is null
and ppr.structure_type = 'WORKPLAN'
and ppr.current_flag <> 'W'
;
IF l_update_wbs_flag = 'Y' THEN
IF l_debug_mode = 'Y' THEN
pa_debug.write(G_PKG_NAME, 'Calling PJI_FM_XBS_ACCUM_MAINT.WBS_MAINT with l_online_flag='||l_online_flag, 3);
END IF; -- l_update_wbs_flag = 'Y'
IF NVL(l_update_wbs_flag,'N') = 'N' OR NVL(l_out_online_flag, 'Y') <> 'N' THEN
IF l_debug_mode = 'Y' THEN
pa_debug.write(G_PKG_NAME, 'Calling PA_PROGRESS_PUB.APPLY_LP_PROG_ON_CWV', 3);
IF l_update_wbs_flag = 'N' THEN
-- No further processing is required for this structure version. Return.
-- TWUT.
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'No updates required for this structure version.';
AND p_calling_context IN ( 'ONLINE_UPDATE', 'CONC_UPDATE' ) --bug 4472071
AND ( l_share_flag = 'N' OR l_workplan_type = 'N' ) --call WBS maint only if the project is either split financial or financial only.
then
x_return_status := FND_API.G_RET_STS_SUCCESS;
IF l_update_wbs_flag = 'N' THEN
-- No further processing is required for this structure version. Return.
-- TWUT.
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'No updates required for this structure version.';
UPDATE pa_proj_elem_ver_structure
SET status_code = 'STRUCTURE_WORKING',
LOCKED_BY_PERSON_ID = (select locked_by_person_id
from pa_proj_elem_ver_structure
where project_id = l_project_id
and element_version_id = p_structure_version_id),
LOCK_STATUS_CODE = 'LOCKED'
WHERE project_id = l_project_id
AND element_version_id = p_pub_struc_ver_id;
ELSIF p_calling_context = 'ONLINE_UPDATE' THEN
--bug 3574457: changed to p_structure_version_id
l_pub_ver_id := p_structure_version_id;
UPDATE pa_proj_elem_ver_structure
SET status_code = 'STRUCTURE_WORKING',
LOCKED_BY_PERSON_ID = (select locked_by_person_id
from pa_proj_elem_ver_structure
where project_id = l_project_id
and element_version_id = p_structure_version_id),
LOCK_STATUS_CODE = 'LOCKED'
WHERE project_id = l_project_id
AND element_version_id = p_pub_struc_ver_id;
ELSIF p_calling_context = 'CONC_UPDATE' THEN
l_pub_ver_id := p_structure_version_id;
pa_debug.write(G_PKG_NAME, 'l_plan_version_id in UPDATE mode for working version='||l_plan_version_id, 3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of Pa_Fp_wp_gen_amt_utils.get_wp_version_id is failed for PUBLISH Mode for working '||
'verison: SQLERRM='||SQLERRM,1,120));
pa_debug.write(G_PKG_NAME, 'Calling PLAN_UPDATE in PUBLISH mode for working version', 3);
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (x_msg_code => l_msg_code,
p_plan_version_id => l_plan_version_id, --bug 4587145
x_return_status => x_return_status);
pa_debug.write(G_PKG_NAME,'After PLAN_UPDATE in PUBLISH mode for working verison x_return_status='||
x_return_status,l_debug_level3);
PA_TASK_PUB1.G_CALL_PJI_ROLLUP := 'N'; --Set the flag back to 'N' to bypass any PJI rollup api call untill explicit plan_update gets called.
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE failed in PUBLISH mode for'||
' working verison:SQLERRM='||SQLERRM,1,120));
SELECT start_date, completion_date
INTO l_proj_start_date, l_proj_completion_date
FROM pa_projects_all
WHERE project_id = l_project_id;
UPDATE pa_proj_elem_ver_structure
SET status_code = 'STRUCTURE_PUBLISHED',
LOCKED_BY_PERSON_ID = NULL,
LOCK_STATUS_CODE = 'UNLOCKED'
WHERE project_id = l_project_id
AND element_version_id = l_pub_ver_id;
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.WBS_MAINT Failed for Publish Mode: SQLERRM='||SQLERRM,1,240));
pa_debug.write(G_PKG_NAME, 'Calling UPDATE_PROJECT_DATES', 3);
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 => l_scheduled_start_date
,p_finish_date => l_scheduled_finish_date
,p_record_version_number => l_proj_record_ver_number
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
pa_debug.write(G_PKG_NAME, 'After UPDATE_PROJECT_DATES x_return_status='|| x_return_status, 3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of Pa_Fp_wp_gen_amt_utils.get_wp_version_id is failed for Publish Mode: SQLERRM='||SQLERRM,1,120));
pa_debug.write(G_PKG_NAME, 'Calling PLAN_UPDATE in PUBLISH mode', 3);
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (x_msg_code => l_msg_code,
p_plan_version_id => l_plan_version_id, --bug 4587145
x_return_status => x_return_status);
pa_debug.write(G_PKG_NAME, 'After PLAN_UPDATE in PUBLISH mode x_return_status='|| x_return_status, 3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE is failed for Publish Mode: SQLERRM='||SQLERRM,1,120));
UPDATE pa_proj_elem_ver_structure
set date_prog_applied_on_wver = sysdate
WHERE project_id = l_project_id
AND element_version_id = p_structure_version_id;
pa_debug.write(G_PKG_NAME, 'Calling UPDATE_PARENT_WBS_FLAG_DIRTY', 3);
PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY(
p_project_id => l_Project_ID
,p_structure_version_id => l_pub_ver_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.write(G_PKG_NAME, 'After UPDATE_PARENT_WBS_FLAG_DIRTY x_return_status='|| x_return_status, 3);
pa_debug.write(G_PKG_NAME, 'Before calling pa_proj_task_struc_pub.set_update_wbs_flag for published version', 3);
pa_proj_task_struc_pub.set_update_wbs_flag(
p_project_id => p_project_id
,p_structure_version_id => l_pub_ver_id
,p_update_wbs_flag => 'N'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.write(G_PKG_NAME, 'After pa_proj_task_struc_pub.set_update_wbs_flag for published version x_return_status='|| x_return_status, 3);
pa_debug.write(G_PKG_NAME, 'Calling WBS_MAINT in UPDATE mode', 3);
pa_debug.write(G_PKG_NAME, 'After WBS_MAINT in UPDATE mode x_return_status='|| x_return_status, 3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.WBS_MAINT Failed in Update Mode: SQLERRM='||SQLERRM,1,240));
pa_debug.write(G_PKG_NAME, 'Calling Pa_Fp_wp_gen_amt_utils.get_wp_version_id in UPDATE mode', 3);
pa_debug.write(G_PKG_NAME, 'l_plan_version_id in UPDATE mode='||l_plan_version_id, 3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of Pa_Fp_wp_gen_amt_utils.get_wp_version_id is failed for UPDATE Mode: SQLERRM='||SQLERRM,1,120));
pa_debug.write(G_PKG_NAME, 'Calling PLAN_UPDATE in UPDATE mode', 3);
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (x_msg_code => l_msg_code,
p_plan_version_id => l_plan_version_id, --bug 4587145
x_return_status => x_return_status);
pa_debug.write(G_PKG_NAME,'After PLAN_UPDATE in UPDATE mode x_return_status='|| x_return_status,l_debug_level3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE failed in Update mode:SQLERRM='||SQLERRM,1,120));
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('PA_PROGRESS_PUB.populate_pji_tab_for_plan:'||SQLERRM,1,240));
pa_debug.write(G_PKG_NAME, 'Before calling PA_STRUCT_TASK_ROLLUP_PUB.Program_Schedule_dates_rollup for entire grid in UPDATE mode', 3);
pa_debug.write(G_PKG_NAME, 'After calling PA_STRUCT_TASK_ROLLUP_PUB.Program_Schedule_dates_rollup for entire grid in UPDATE mode x_return_status='||x_return_status, 3);
SELECT ppevs2.element_version_id
FROM pa_proj_element_versions ppevs1, pa_proj_element_versions ppevs2
WHERE ppevs1.proj_element_id = ppevs2.proj_element_id
AND ppevs1.project_id = p_project_id
AND ppevs2.project_id = p_project_id
AND ppevs1.element_version_id = p_structure_version_id
AND ppevs1.object_type = 'PA_STRUCTURES'
AND ppevs2.object_type = 'PA_STRUCTURES'
;
pa_debug.write(G_PKG_NAME, 'Calling WBS_MAINT in UPDATE mode', 3);
pa_debug.write(G_PKG_NAME, 'After WBS_MAINT in UPDATE mode x_return_status='
|| x_return_status, 3);
p_procedure_name => 'PROCESS_WBS_UPDATES',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.WBS_MAINT
Failed in Update Mode: SQLERRM='||SQLERRM,1,240));
pa_proj_task_struc_pub.set_update_wbs_flag(
p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,p_update_wbs_flag => 'N'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.write(G_PKG_NAME, 'After set_update_wbs_flag x_return_status='|| x_return_status, 3);
pa_debug.write(G_PKG_NAME, 'Exiting PROCESS_WBS_UPDATES', l_debug_level3);
rollback to process_wbs_updates;
,p_procedure_name => 'PROCESS_WBS_UPDATES'
,p_error_text => x_msg_data);
rollback to process_wbs_updates;
END PROCESS_WBS_UPDATES;
Select rel.object_relationship_id,
rel.object_id_from1,
-- NVL(pfxat.labor_hours,0) + NVL(pfxat.equipment_hours,0),
NVL(pxpv.labor_effort,0) + NVL(pxpv.equipment_effort,0),
sch.duration,
ptt.prog_entry_enable_flag
from pa_proj_element_versions ver,
pa_object_relationships rel,
pa_proj_elem_ver_schedule sch,
pa_proj_elements ele,
pa_task_types ptt,
pji_xbs_plans_v pxpv
-- pji_fm_xbs_accum_tmp1 pfxat
where ver.project_id = c_project_id
and ver.parent_structure_version_id = c_structure_version_id
and ver.object_type = 'PA_TASKS'
and ver.element_version_id = rel.object_id_to1
and rel.relationship_type = 'S'
and rel.relationship_subtype in ( 'STRUCTURE_TO_TASK','TASK_TO_TASK')
and sch.element_version_id = ver.element_version_id
and sch.project_id = c_project_id
and ele.proj_element_id = ver.proj_element_id
and ptt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
and ele.type_id = ptt.task_type_id
and pxpv.project_id (+)= ver.project_id
and pxpv.structure_version_id (+)= ver.parent_structure_version_id
and pxpv.proj_element_id (+)= ver.proj_element_id
and pxpv.plan_version_id (+)= c_plan_version_id --bug 4726889
-- and pfxat.project_id (+)= ver.project_id
-- and pfxat.project_element_id (+)= ver.proj_element_id
-- and pfxat.struct_version_id (+)= ver.parent_structure_version_id
-- and pfxat.calendar_type (+)= 'A'
;
select 'X'
from pa_proj_element_versions ver,
pa_proj_elements ele,
pa_task_types ptt
where ver.project_id = c_project_id
and ver.parent_structure_version_id = c_structure_version_id
and ver.object_type = 'PA_TASKS'
and ele.proj_element_id = ver.proj_element_id
and ele.type_id = ptt.task_type_id
and ptt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
and nvl(ptt.prog_entry_enable_flag,'N') = 'Y'
for update of ver.record_version_number NOWAIT;
l_child_info_tbl.delete;
l_object_rel_id_tbl.delete;
l_duration_tbl.delete;
l_effort_tbl.delete;
l_parent_task_ver_id_tbl.delete;
UPDATE pa_object_relationships
SET weighting_percentage = l_task_weightage_tbl(i)
WHERE object_relationship_id = l_object_rel_id_tbl(i);
pa_debug.g_err_stage:= 'Updated ' || sql%rowcount || ' Records in PA_OBJECT_RELATIONSHIPS';
This API will be called to set the update_required_flag to Y or N.
The default value of the flag is Y. The passed in flag value is
used to update the structure version record in elem_ver_structure
table.
Bug 3010538 : New API for the Task Weighting Enhancement.
==================================================================*/
PROCEDURE SET_UPDATE_WBS_FLAG
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_calling_context IN VARCHAR2
,p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_update_wbs_flag IN pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE
,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
AS
l_msg_count NUMBER := 0;
savepoint set_update_wbs_flag;
pa_debug.g_err_stage:= 'Entering SET_UPDATE_WBS_FLAG';
pa_debug.set_curr_function( p_function => 'SET_UPDATE_WBS_FLAG',
p_debug_mode => l_debug_mode );
pa_debug.g_err_stage:= 'p_update_wbs_flag = '|| p_update_wbs_flag;
(p_update_wbs_flag not in ('Y','N'))
THEN
PA_UTILS.ADD_MESSAGE
(p_app_short_name => 'PA',
p_msg_name => 'PA_INV_PARAM_PASSED');
Update pa_proj_elem_ver_structure
Set process_update_wbs_flag = p_update_wbs_flag
Where project_id = p_project_id
And element_version_id = p_structure_version_id;
pa_debug.g_err_stage:= 'Exiting SET_UPDATE_WBS_FLAG';
rollback to set_update_wbs_flag;
,p_procedure_name => 'SET_UPDATE_WBS_FLAG'
,p_error_text => x_msg_data);
rollback to set_update_wbs_flag;
END SET_UPDATE_WBS_FLAG;
PROCEDURE PROCESS_WBS_UPDATES_WRP_FORM
( p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_pub_struc_ver_id IN NUMBER := NULL
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER := 0;
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
( p_commit => FND_API.G_TRUE
,p_calling_context => 'UPDATE'
,p_project_id => p_project_id
,p_structure_version_id => p_structure_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data) ;
pa_debug.g_err_stage := 'Error calling concurrent wbs update process ';
,p_procedure_name => 'PROCESS_WBS_UPDATES_WRP'
,p_error_text => x_msg_data);
END PROCESS_WBS_UPDATES_WRP_FORM ;
PROCEDURE call_process_WBS_updates(
p_dest_project_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
) AS
CURSOR cur_strs_n_types
IS
SELECT ppe.proj_element_id, ppst.structure_type_id
FROM pa_proj_elements ppe, pa_proj_structure_types ppst, pa_structure_types pst
WHERE ppe.project_id = p_dest_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type in ('WORKPLAN','FINANCIAL')
;
SELECT ppevs.element_version_id
FROM pa_proj_element_versions ppevs
WHERE ppevs.project_id = p_dest_project_id
AND ppevs.object_type = 'PA_STRUCTURES'
AND ppevs.proj_element_id = c_structure_id
;
Update the Process_WBS_flag only for WORKPLAN enabled structures
if task weighting basis is DURATION.
Call the concurrent wrapper for all structure versions */
l_task_weight_basis := PA_PROGRESS_UTILS.GET_TASK_WEIGHTING_BASIS( p_dest_project_id );
UPDATE pa_proj_elem_ver_structure
SET PROCESS_UPDATE_WBS_FLAG = 'Y'
WHERE proj_element_id = cur_strs_n_types_rec.proj_element_id
AND project_id = p_dest_project_id
AND PROCESS_UPDATE_WBS_FLAG = 'N'
;
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'COPY_PROJECT'
,p_project_id => p_dest_project_id
,p_structure_version_id => l_wp_str_ver_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Error calling concurrent wbs update process ';
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'COPY_PROJECT'
,p_project_id => p_dest_project_id
,p_structure_version_id => cur_str_vers_rec.element_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Error calling concurrent wbs update process ';
PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_WRP
(
p_calling_context => 'COPY_PROJECT'
,p_project_id => p_dest_project_id
,p_structure_version_id => l_fn_str_ver_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Error calling update WBS WRP ';
,p_procedure_name => 'CALL_PROCESS_WBS_UPDATES'
,p_error_text => x_msg_data);
END call_process_WBS_updates;
PROCEDURE Update_Current_Phase
( p_api_version_number IN NUMBER := 1.0
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_validation_level IN VARCHAR2 := 100
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_user_id IN NUMBER := FND_GLOBAL.USER_ID
,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_current_lifecycle_phase_id IN NUMBER := FND_API.G_MISS_NUM
,p_current_lifecycle_phase IN VARCHAR2 := FND_API.G_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_get_wp_attr(c_project_id NUMBER)
IS
select ppwa.proj_element_id,
ppwa.wp_approval_reqd_flag,
ppwa.wp_auto_publish_flag,
ppwa.wp_default_display_lvl,
ppwa.wp_enable_version_flag,
ppwa.auto_pub_upon_creation_flag,
ppwa.auto_sync_txn_date_flag,
ppwa.wp_approver_source_id,
ppwa.wp_approver_source_type,
ppwa.record_version_number,
ppwa.txn_date_sync_buf_days,
ppwa.lifecycle_version_id
from pa_proj_workplan_attr ppwa,
pa_proj_elements ppe,
pa_proj_structure_types ppst
where ppwa.project_id = c_project_id
and ppwa.project_id = ppe.project_id
and ppwa.proj_element_id = ppe.proj_element_id
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = 1;
SAVEPOINT update_current_phase;
PA_WORKPLAN_ATTR_PVT.UPDATE_PROJ_WORKPLAN_ATTRS(
p_commit => p_commit
,p_validate_only => p_validate_only
,p_validation_level => 50
,p_debug_mode => p_debug_mode
,p_max_msg_count => p_max_msg_count
,p_project_id => l_project_id
,p_proj_element_id => l_proj_element_id
,p_approval_reqd_flag => l_wp_approval_reqd_fg
,p_auto_publish_flag => l_wp_auto_publish_fg
,p_approver_source_id => l_wp_approver_source_id
,p_approver_source_type => l_wp_approver_source_type
,p_default_display_lvl => l_wp_default_display_lvl
,p_enable_wp_version_flag => l_wp_enable_version_fg
,p_auto_pub_upon_creation_flag => l_auto_pub_upon_creation_fg
,p_auto_sync_txn_date_flag => l_auto_sync_txn_date_fg
,p_txn_date_sync_buf_days => l_txn_date_sync_buf_days
,p_lifecycle_version_id => l_life_cycle_version_id
,p_current_phase_version_id => l_current_phase_version_id
,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
);
rollback to update_current_phase;
p_procedure_name => 'update_current_phase',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to update_current_phase;
END Update_Current_Phase;
select ppev1.project_id, ppev1.element_version_id
from pa_proj_elements ppe,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
where ppev2.element_version_id = P_Task_Version_Id
and ppev2.parent_structure_version_id = ppev1.parent_structure_version_id
and ppev1.proj_element_id = ppe.proj_element_id
and ppe.pm_source_reference = c_pred_ref_id;
Select Instr(P_String, l_Delimeter, 1, i) Into l_End from dual;
Select substr(P_String, l_begin, Decode(l_End, 0, 1000, l_End-l_begin))
Into l_Strings_Tab(i) From Dual;
Select To_Number(SubStr(l_Strings_Tab(Rec), 1, Decode(l_Type_Position, 0, 100, l_Type_Position-2 )))
Into l_Col1 From Dual;
For i IN ( Select PPE.PM_Source_Reference SourceRef,
POR.Relationship_Subtype SubType,
decode(sign(NVL(POR.Lag_Day,0)),
'0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/(10*8*60) LagDays --bug 4348156
From PA_Proj_Elements PPE,
PA_Proj_Element_Versions PEV,
PA_Object_Relationships POR,
PA_PROJ_ELEMENT_VERSIONS PEV2
Where PPE.project_id = PEV.project_id
and PPE.proj_element_id = PEV.proj_element_id
and PEV.element_version_id = POR.object_id_to1
and PEV2.ELEMENT_VERSION_ID = POR.object_id_from1
and PEV2.ELEMENT_VERSION_ID = P_ELEMENT_VERSION_ID
and POR.Relationship_Type = 'D'
and POR.Object_ID_From2 = POR.Object_ID_To2 )
Loop
l_String := l_String||i.SourceRef||i.SubType||i.LagDays||P_Delimeter;
For i IN ( Select PPE.proj_element_id ProjElemId,
POR.Relationship_Subtype SubType,
decode(sign(NVL(POR.Lag_Day,0)),
'0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/(10*8*60) LagDays --bug 4348156
From PA_Proj_Elements PPE,
PA_Proj_Element_Versions PEV,
PA_Object_Relationships POR,
PA_PROJ_ELEMENT_VERSIONS PEV2
Where PPE.project_id = PEV.project_id
and PPE.proj_element_id = PEV.proj_element_id
and PEV.element_version_id = POR.object_id_to1
and PEV2.ELEMENT_VERSION_ID = POR.object_id_from1
and PEV2.ELEMENT_VERSION_ID = P_ELEMENT_VERSION_ID
and POR.Relationship_Type = 'D'
and POR.Object_ID_From2 = POR.Object_ID_To2 )
Loop
l_String := l_String||i.ProjElemId||i.SubType||i.LagDays||P_Delimeter;
SELECT proj.structure_sharing_code FROM pa_projects_all proj where proj.project_id = P_Project_ID;
procedure delete_intra_dependency (p_element_version_id IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_debug_mode IN VARCHAR2 := 'N',
x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_object_type VARCHAR2(80);
pa_debug.init_err_stack('PA_PROJ_TASK_STRUC_PUB.DELETE_INTRA_DEPENDENCY');
pa_debug.debug('PA_PROJ_TASK_STRUC_PUB.DELETE_INTRA_DEPENDENCY Begin');
savepoint delete_intra_dependency;
SELECT object_type
INTO l_object_type
FROM pa_proj_element_versions
WHERE ELEMENT_VERSION_ID = p_element_version_id;
DELETE FROM pa_object_relationships por1
WHERE por1.object_relationship_id IN
(SELECT por.object_relationship_id
FROM pa_object_relationships por
WHERE por.relationship_type = 'D'
AND por.object_id_from1 = p_element_version_id
AND por.object_id_from2 = por.object_id_to2
AND por.object_type_from = 'PA_TASKS'
AND por.object_type_to = 'PA_TASKS');
DELETE FROM pa_object_relationships por1
WHERE por1.object_relationship_id IN
(SELECT por.object_relationship_id
FROM pa_proj_element_versions ppev,
pa_object_relationships por
WHERE relationship_type ='D'
AND ppev.parent_structure_version_id = p_element_version_id
AND ppev.object_type = 'PA_TASKS'
AND por.object_id_from2 = por.object_id_to2
AND por.object_type_from = 'PA_TASKS'
AND por.object_type_to = 'PA_TASKS'
AND por.object_id_from1 = ppev.ELEMENT_VERSION_ID);
pa_debug.debug('PA_PROJ_TASK_STRUC_PUB.DELETE_INTRA_DEPENDENCY END');
rollback to delete_intra_dependency;
ROLLBACK TO delete_intra_dependency;
p_procedure_name => 'delete_intra_dependency',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO delete_intra_dependency;
END delete_intra_dependency;